Oracle Scratchpad

August 19, 2014

LOB Length

Filed under: Infrastructure,LOBs,Oracle,Performance — Jonathan Lewis @ 6:06 pm BST 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:

rem
rem     Script:         lob_oddity.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Aug 2014
rem

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 using autotrace to check stats on 
-- the 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 (using no_merge hint)
----------------------------------------------------------
         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 (using 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 value for “consistent gets” in the no_merge() approach is roughly double that for the rownum approach – and since we have 128 rows/LOBs in the table the numbers looks suspiciously like 2 gets vs. 1 get per LOB depending on the method, and that suggests two calls to the function in the no_merge() case. This hypothesis is 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 with no_merge() but it hasn’t been pushed inside the view that uses the rownum instantiation 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 (JPPD) and our code is using a simple filter pushdown. 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.

Update (April 2019)

A quick test with Oracle 18.3 showed two significant differences. The number of consistent gets reported both versions of the code dropped to a number suitable for just a simple tablescan with no lob access (with a small variation – the rownum version reported one extra consistent get: 23 vs. 22).

One of the other tests I set up at the same time replaced the dbms_lob.getlength() function call with the built-in length() call – which didn’t work with LOBs in the very earliest versions. Basically this made no difference to any of the plans or statistics – until 18.3 where there was a small change in the reported predicate for the version with the no_merge hint.  With the length() function and the hint in place the predicate in 12.2.0.1 looked like this:

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

While in 18.3 the predicate was just a little more explicit about what it was doing:

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(LENGTH("C1" /*+ LOB_BY_VALUE */ )>3960)

This strange pseudo-“hint” in the function is presumably related to the query option for LOB processing that allows you to specify whether you want to fetch LOBs by locator or by value so perhaps it; or maybe it’s just there as a prompt to the next Oracle internal coder that visits the code path.

 

6 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 BST 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 BST 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 BST 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 BST 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 BST Aug 20,2014 | Reply

  4. […] details of LOBs – here’s the article that has a footnote reporting another interesting change in LOB handling in 18.3; and a very old note about analysing the contents of a LOB segment with a view to assessing […]

    Pingback by LOB length | Oracle Scratchpad — May 2, 2019 @ 11:41 am BST May 2,2019 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.