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; or maybe it’s just there as a prompt to the next Oracle internal coder that visits the code path.
Another approach – I didn’t know the rownum approach you showed – would be
Comment by Matthias Rogel — August 19, 2014 @ 8:24 pm BST Aug 19,2014 |
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 |
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 |
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:
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 |
Randolf,
there is also 3rd option:
Comment by Sayan Malakshinov — August 20, 2014 @ 1:34 pm BST Aug 20,2014 |
[…] 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 |