Oracle Scratchpad

August 19, 2014

LOB Length

Filed under: Infrastructure,LOBs,Oracle,Performance — Jonathan Lewis @ 6:06 pm GMT Aug 19,2014

It’s funny how you can make little savings in work all over the place in Oracle if you’re prepared to look a little closely at what’s going on. Here’s a quirky little example with LOBs and function calls that might just have some greater relevance in other situations. Here’s a little data set, and two queries that I might run against it:


create table tbl(
	c1      clob
)
lob (c1) store as c_lob(
	disable storage in row
	nocache nologging
)
;

begin
	for i in 1..128 loop
		insert into tbl values(rpad('x',4000));
		commit;
	end loop;
end;
/

commit;

-- collect stats, prime dictionary cache and library cache
-- run both queries twice and check stats on second run

select
	round(sum(ceil(len/8100)) * 8/1024,0)    used_mb
from
	(
	select
		/*+ no_merge */
		dbms_lob.getlength(c1) len
	from
		tbl
	)
where
	len > 3960
;

select
	round(sum(ceil(len/8100)) * 8/1024,0)    used_mb
from
	(
	select
		rownum rn, dbms_lob.getlength(c1) len
	from
		tbl
	)
where
	len > 3960
;

The question that you might ask yourselves when you see these queries is: will they do similar amounts of work. Of course, I wouldn’t be asking the question if the answer were yes. Despite the no_merge() hint, which you might think would have the same effect as the rownum approach, Oracle seems to execute the call to dbms_lob.getlength() twice for each row in the first query, but only once per row for the second query. Here are the stats (from autotrace) on the second run of the two queries when autotrace is enabled:


Statistics (for no_merge)
----------------------------------------------------------
         40  recursive calls
          0  db block gets
        271  consistent gets
          0  physical reads
          0  redo size
        541  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Statistics (for rownum)
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        131  consistent gets
          0  physical reads
          0  redo size
        541  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

As you can see, the consistent gets for the no_merge() approach is roughly double that for the rownum approach – and since we have 128 rows/LOBs in the table that looks suspiciously like 2 gets vs. 1 get per LOB depending on the approach – which suggests two calls to the function. This is further corroborated by the execution plans, and especially by the predicate sections (how often have I said “always check the predicates”) which show that the predicate has been pushed inside the view that’s been hinted to be non-mergeable, but it hasn’t been pushed inside the view that uses the rownum instantion trick:


Execution Plan for no_merge()
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |    13 |            |          |
|   2 |   VIEW              |      |     6 |    78 |     2   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| TBL  |     6 |   522 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("DBMS_LOB"."GETLENGTH"("C1")>3960)

Execution Plan for rownum
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE      |      |     1 |    13 |            |          |
|*  2 |   VIEW               |      |   128 |  1664 |     2   (0)| 00:00:01 |
|   3 |    COUNT             |      |       |       |            |          |
|   4 |     TABLE ACCESS FULL| TBL  |   128 | 11136 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("LEN">3960)

My first thought on seeing this difference was to apply the /*+ no_push_pred */ hint to block predicate pushing – but then I remembered that the hint is about join predicate pushing and this is a simple filter push. A quick search of the hidden parameters, though, revealed this:

_optimizer_filter_pushdown : enable/disable filter predicate pushdown

Setting this parameter to false – either through a call to ‘alter session’ or through an /*+ opt_param( opt_param(‘_optimizer_filter_pushdown’ , ‘false’) */ hint – allowed the no_merge approach to produce the same plan and resource usage as the rownum approach. Of course, for a production system, I’d probably use the rownum approach rather than mess around with hidden parameters.

Footnote:

I don’t know why the code with the no_merge() approach reported 40 recursive calls (on its first execution with autotrace). A couple of variations on the experiment suggested that it had something to do with the number of rows (or consequential buffer visits) that survived the predicate call – for a sufficiently small number of rows the recursive call count happened to drop to zero; but the phenomenon needs further investigation.

5 Comments »

  1. Another approach – I didn’t know the rownum approach you showed – would be

    select
        round(sum(ceil(len/8100)) * 8/1024,0)    used_mb
    from
        (
        select (select dbms_lob.getlength(c1) from dual) len
        from
            tbl
        )
    where
        len > 3960
    

    Comment by Matthias Rogel — August 19, 2014 @ 8:24 pm GMT Aug 19,2014 | Reply

  2. Jonathan,
    i noticed that by default recursive calls disappear only at third execution, but if we disable “_optimizer_use_feedback”=false, already second execution will be without recursive calls

    PS. About same thing I wrote previously: http://orasql.org/2013/06/10/too-many-function-executions/

    Comment by Sayan Malakshinov — August 19, 2014 @ 8:42 pm GMT Aug 19,2014 | Reply

    • Sayan,

      I should have googled the parameter before bothering to write; thanks for the link – which also links onwards to an interesting item from Randolf Geist about the effects with aggregates and the HAVING clause and a comment discussion on further effects due to subquery factoring.

      Comment by Jonathan Lewis — August 20, 2014 @ 8:52 am GMT Aug 20,2014 | Reply

  3. Jonathan,

    in that post you mention in your reply to Sayan I also mention some of the side effects of the ROWNUM / optimizer parameter approaches:

    – ROWNUM does have odd side effects when it comes to Parallel Execution
    – The “_optimizer_fiter_pushdown” parameter is only supported from 11.2.0.3 on, and it prevents filter pushdown on global statement level rather than only for a specific query block, so might prevent some desired filter pushdowns in case of more complex statements. With ROWNUM you have more control over the filters being not pushed.

    It’s also a pity that the optimizer isn’t really that clever in optimizing expression evaluation, which applies to both projection and restriction. In such cases the NO_MERGE hint can help to save resources, which can be seen with this simple example based on your setup above:

    set echo on timing on time on
    
    set autotrace traceonly statistics
    
    select
      case 
      when len <= 1000 then '1K'
      when len > 1000 and len <= 2000 then '2K'
      when len > 2000 and len <= 3000 then '3K'
      when len > 3000 then '4K'
      end as len_hist
    from
      (
      select
        /*+ no_merge */
        dbms_lob.getlength(c1) len
      from
        tbl
      )
    ;
    

    Removing the NO_MERGE hint in this example increases the consistent gets by a factor of 5 simply because Oracle does a straightforward replacement of the “LEN” expressions in the outer query with the “DBMS_LOB.GETLENGTH” function call from the inner query when merging the two query blocks – for costly expressions like REGEXP or (user-defined) PL/SQL functions this can make a significant difference.

    This rather non-optimized replacement approach still seems to apply to 12.1, but I haven’t tested it yet on the latest 12.1.0.2 version.

    Randolf

    Comment by Randolf Geist — August 20, 2014 @ 12:58 pm GMT Aug 20,2014 | Reply

    • Randolf,

      there is also 3rd option:

      select
          round(sum(ceil(len/8100)) * 8/1024,0)    used_mb
      from
          (
          select
              /*+ no_merge */
              dbms_lob.getlength(c1) len
          from
              tbl
          )
        , (select/*+ no_merge */ 3960 n from dual)
      where
          len > n
      ;
      

      Comment by Sayan Malakshinov — August 20, 2014 @ 1:34 pm GMT Aug 20,2014 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,523 other followers