Multipart cursor subexecution and PRECOMPUTE_SUBQUERY hint

Tanel Poder

2009/01/23

There was a question about PRECOMPUTE_SUBQUERY hint in an Oracle Forums thread.

Here I will post the answer I gave there and also elaborate it more as it explains a little known interesting fact about Oracle cursor management. Also it allows me to introduce few advanced Oracle troubleshooting scripts by example. This is a fairly long post, but if you are interested in some Oracle cursor management and SQL execution internals, keep on reading ;)

Consider the following test case with two tables, T1 and T2:

SQL> create table t1 as select rownum a from dual connect by level < 10;

Table created.

SQL> create table t2 as select rownum+10 b from dual connect by level < 10;

Table created.

SQL>
SQL> select * from t1;

         A
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.

SQL>
SQL> select * from t2;

         B
----------
        11
        12
        13
        14
        15
        16
        17
        18
        19

9 rows selected.

Now lets run a query with a simple subquery in it:

SQL> select a
  2  from   t1
  3  where  a in (select b from t2);

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  aucw6byq3d5q8, child number 0
-------------------------------------
select a from   t1 where  a in (select b from t2)

Plan hash value: 561629455

----------------------------------------------------------------------------
| Id  | Operation           | Name     | E-Rows |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |        |       |       |          |
|*  1 |  HASH JOIN SEMI     |          |      1 |  1066K|  1066K| 1056K (0)|
|   2 |   TABLE ACCESS FULL | T1       |      9 |       |       |          |
|   3 |   VIEW              | VW_NSO_1 |      9 |       |       |          |
|   4 |    TABLE ACCESS FULL| T2       |      9 |       |       |          |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"="B")

Note that a hash semijoin was performed which semijoined two of its child rowsources with join condition “A=B”.

Now lets run exactly the same query with PRECOMPUTE_SUBQUERY hint in subquery block:

SQL> select a
  2  from   t1
  3  where  a in (select /*+ PRECOMPUTE_SUBQUERY */b from t2);

no rows selected

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  fvnqhjkcjnybx, child number 0
-------------------------------------
select a from   t1 where  a in (select /*+ PRECOMPUTE_SUBQUERY */b from
t2)

Plan hash value: 3617692013

-------------------------------------------
| Id  | Operation         | Name | E-Rows |
-------------------------------------------
|   0 | SELECT STATEMENT  |      |        |
|*  1 |  TABLE ACCESS FULL| T1   |      5 |
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("A"=11 OR "A"=12 OR "A"=13 OR "A"=14 OR "A"=15 OR "A"=16
              OR "A"=17 OR "A"=18 OR "A"=19))

See what happened! The join is gone and it looks like table T2 is not accessed at all (as there is only one TABLE ACCESS rowsource which reads from table T1 ).

However, there has appeared a filter condition which has all the values from T2 in it! How are these values retrieved?

A simple sql_trace reveals this:

PARSING IN CURSOR #1 len=97 dep=1 uid=0 oct=3 lid=0 tim=1232490329981197 hv=1703909501 ad='3175d2cc' sqlid='7b0jqyxksz63x'
SELECT /*+ BYPASS_RECURSIVE_CHECK */ DISTINCT * FROM (select /*+ PRECOMPUTE_SUBQUERY */b from t2)
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=393655594,tim=1232490329981197
EXEC #1:c=1000,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=393655594,tim=1232490329981197
FETCH #1:c=4000,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,plh=393655594,tim=1232490329981197
FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=393655594,tim=1232490329981197
FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=393655594,tim=1232490329981197
FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=393655594,tim=1232490329981197
FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=393655594,tim=1232490329981197
FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=393655594,tim=1232490329981197
FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=393655594,tim=1232490329981197
FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=393655594,tim=1232490329981197
FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=393655594,tim=1232490329981197
FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=393655594,tim=1232490329981197
STAT #1 id=1 cnt=9 pid=0 pos=1 obj=0 op='HASH UNIQUE (cr=3 pr=0 pw=0 time=0 us cost=3 size=27 card=9)'
STAT #1 id=2 cnt=9 pid=1 pos=1 obj=79810 op='TABLE ACCESS FULL T2 (cr=3 pr=0 pw=0 time=0 us cost=2 size=27 card=9)'
CLOSE #1:c=0,e=0,dep=1,type=0,tim=1232490329981197

PARSING IN CURSOR #2 len=75 dep=0 uid=0 oct=3 lid=0 tim=1232490330002221 hv=2568649085 ad='3175d5cc' sqlid='fvnqhjkcjnybx'
select a
from   t1
where  a in (select /*+ PRECOMPUTE_SUBQUERY */b from t2)
END OF STMT
PARSE #2:c=28996,e=41781,p=0,cr=3,cu=0,mis=1,r=0,dep=0,og=1,plh=3617692013,tim=1232490330002221
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3617692013,tim=1232490330002221
FETCH #2:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=1,plh=3617692013,tim=1232490330002221
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=79809 op='TABLE ACCESS FULL T1 (cr=3 pr=0 pw=0 time=0 us cost=2 size=15 card=5)'
CLOSE #2:c=0,e=0,dep=0,type=0,tim=1232490330002221

There are two queries in sql_trace output, the second one is the one executed by me (and it’s dep=0 thus it’s a “top-level” query). However, just before parsing of my query started, another query was executed (the first query in trace), with dep=1, which means it’s a recursive query.

This is how Oracle first fetches the (distinct) values from T2 recursively, stores the result in memory and passes these values as filter conditions to my query. This allows Oracle to just fetch values from T1 table and compare the rows against “hardcoded” filter conditions, instead of having to do some sort of semijoin between two separate rowsources.

An 10053 trace also shows the following lines:

CSE: Considering common sub-expression elimination in query block SEL$2 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE:     CSE not performed on query block SEL$2 (#0).
CSE:     CSE not performed on query block SEL$1 (#0).
Copy query block qb# -1 (<unnamed>) : SELECT /*+ BYPASS_RECURSIVE_CHECK */ DISTINCT * FROM (select /*+ PRECOMPUTE_SUBQUERY */b from t2)

When evaluating rewrite options for subqueries, the optimizer unfolds that subquery text into a separate cursor (creating a query block qb# -1).

Unlike the unparsing operation which is used for generating remote SQL statement from distributed queries, this mechanism just copies the subquery SQL text to a recursive statement and executes it (This can be verified by setting some custom comment text into subquery and tracing the execution, the comments persist).

So, considering that all these “hardcoded” filter conditions need to be stored inside child cursor memory structures, what if the T2 table contains millions of rows? Are we going to have cursors tens of megabytes in size?

First, lets add many more rows into T2 table (I deliberately used an easily distinquishable number such as 5555).

SQL> delete t2;

9 rows deleted.

SQL> insert into t2 select rownum from dual connect by level <= 5555;

5555 rows created.

SQL> select a from   t1 where  a in (select /*+ PRECOMPUTE_SUBQUERY */b from t2);

         A
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.

Now I use a simple script called hash.sql which shows the hash value and child_number of the last SQL executed in my session (using v$session.prev_hash_value).

SQL> @hash

HASH_VALUE SQL_ID        CHILD_NUMBER
---------- ------------- ------------
4073976606 5phajvmtd7wsy            1

Let’s check, using my curheaps.sql script which reports for what memory is used inside some cursor’s heaps (data blocks). The script takes SQL hash value and child cursor number as parameters:

SQL> @curheaps 4073976606 1

  KGLNAHSH KGLHDPAR     CHILD# KGLHDADR KGLOBHD0    SIZE0    SIZE1    SIZE2    SIZE3    SIZE4    SIZE5 KGLOBHD6    SIZE6    SIZE7     STATUS
---------- -------- ---------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- ----------
4073976606 3175A618          1 2FD2D6DC 2FD945BC     3392        0        0        0        0        0 312C53F4  1412196        0          1


HEAP  CLASS    ALLOC_COMMENT         BYTES     CHUNKS
----- -------- ---------------- ---------- ----------
HEAP0 perm     permanent memor        2500          4
HEAP0 free     free memory             412          2
HEAP0 freeabl  kgltbtab                228          3


HEAP  CLASS    ALLOC_COMMENT         BYTES     CHUNKS
----- -------- ---------------- ---------- ----------
HEAP6 freeabl  qeeOpt: qeesCre      489048       5557
HEAP6 freeabl  optdef: qcopCre      400056       5555
HEAP6 freeabl  opn: qkexrInitO      334192       5558
HEAP6 freeabl  ub1[]: qkexrXfo       90604       5555
HEAP6 freeabl  strdef_buf : kk       90060       5556
HEAP6 free     free memory            3600          1
HEAP6 freeabl  kksol : kksnsg          920         23
HEAP6 freeabl  kctdef : qcdlgo         876          3
HEAP6 freeabl  ctxdef:kksLoadC         488          1
HEAP6 freeabl  kctdef : qcsfps         292          1
HEAP6 freeabl  qertbs:qertbIAl         228          1
[...snip...]
HEAP6 freeabl  kksoff : opitca          20          1
HEAP6 freeabl  opixfalo:froaty          16          1
HEAP6 freeabl  xplGenXpl:planL          16          1
HEAP6 freeabl  opixfalo:ctxkct          16          1

46 rows selected.

In the top of the output we see SIZE6 = 1412196, this shows that the HEAP6 of that child cursor takes 1.4MB of memory!

From the bold numbers in bottom section (5555, 5556 and so on) we see that there’s a number of allocations made inside the cursor heap which (pretty much) matches with the number of rows in table T2. This is where the “harcoded” filter conditions and data are kept.

The predicate information section coming from V$SQL_PLAN now has all the filter conditions listed in it (up to varchar2 limitation of 4000 bytes):

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      9 |00:00:00.01 |       4 |
|*  1 |  TABLE ACCESS FULL| T1   |      1 |      9 |      9 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("A"=1 OR "A"=2 OR "A"=3 OR "A"=4 OR "A"=5 OR "A"=6 OR "A"=7
              OR "A"=8 OR "A"=9 OR "A"=10 OR "A"=11 OR "A"=12 OR "A"=13 OR "A"=14 OR
              "A"=15 OR "A"=16 OR "A"=17 OR "A"=18 OR "A"=19 OR "A"=20 OR "A"=21 OR
              "A"=22 OR "A"=23 OR "A"=24 OR "A"=25 OR "A"=26 OR "A"=27 OR "A"=28 OR
              "A"=29 OR "A"=30 OR "A"=31 OR "A"=32 OR "A"=33 OR "A"=34 OR "A"=35 OR
              "A"=36 OR "A"=37 OR "A"=38 OR "A"=39 OR "A"=40 OR "A"=41 OR "A"=42 OR
              "A"=43 OR "A"=44 OR "A"=45 OR "A"=46 OR "A"=47 OR "A"=48 OR "A"=49 OR
              "A"=50 OR "A"=51 OR "A"=52 OR "A"=53 OR "A"=54 OR "A"=55 OR "A"=56 OR
              "A"=57 OR "A"=58 OR "A"=59 OR "A"=60 OR "A"=61 OR "A"=62 OR "A"=63 OR
              "A"=64 OR "A"=65 OR "A"=66 OR "A"=67 OR "A"=68 OR "A"=69 OR "A"=70 OR
              "A"=71 OR "A"=72 OR "A"=73 OR "A"=74 OR "A"=75 OR "A"=76 OR "A"=77 OR
              "A"=78 OR "A"=79 OR "A"=80 OR "A"=81 OR "A"=82 OR "A"=83 OR "A"=84 OR
              "A"=85 OR "A"=86 OR "A"=87 OR "A"=88 OR "A"=89 OR "A"=90 OR "A"=91 OR
              "A"=92 OR "A"=93 OR "A"=94 OR "A"=95 OR "A"=96 OR "A"=97 OR "A"=98 OR
              "A"=99 OR "A"=100 OR "A"=101 OR "A"=102 OR "A"=103 OR "A"=104 OR "A"=105 OR
              "A"=106 OR "A"=107 OR "A"=108 OR "A"=109 OR "A"=110 OR "A"=111 OR "A"=112
              OR "A"=113 OR "A"=114 OR "A"=115 OR "A"=116 OR "A"=117 OR "A"=118 OR
              "A"=119 OR "A"=120 OR "A"=121 OR "A"=122 OR "A"=123 OR "A"=124 OR "A"=125
              OR "A"=126 OR "A"=127 OR "A"=128 OR "A"=129 OR "A"=130 OR "A"=131 OR
              "A"=132 OR "A"=133 OR "A"=134 OR "A"=135 OR "A"=136 OR "A"=137 OR "A"=138
              [...snip..]

So, lets add even more rows to T2 and see how big the child cursors can grow:

SQL> delete t2;

5555 rows deleted.

SQL> insert into t2 select rownum from dual connect by level <= 99999;

99999 rows created.

SQL> select a from   t1 where  a in (select /*+ PRECOMPUTE_SUBQUERY */b from t2);

         A
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.

SQL> @hash

HASH_VALUE SQL_ID        CHILD_NUMBER
---------- ------------- ------------
4073976606 5phajvmtd7wsy            2

SQL> @curheaps 4073976606 2

  KGLNAHSH KGLHDPAR     CHILD# KGLHDADR KGLOBHD0    SIZE0    SIZE1    SIZE2    SIZE3    SIZE4    SIZE5 KGLOBHD6    SIZE6    SIZE7
---------- -------- ---------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- --
4073976606 3175A618          2 3343C208 2FD9768C     3392        0        0        0        0        0 312B45D8   925236        0


HEAP  CLASS    ALLOC_COMMENT         BYTES     CHUNKS
----- -------- ---------------- ---------- ----------
HEAP0 perm     permanent memor        2500          4
HEAP0 free     free memory             412          2
HEAP0 freeabl  kgltbtab                228          3


HEAP  CLASS    ALLOC_COMMENT         BYTES     CHUNKS
----- -------- ---------------- ---------- ----------
HEAP6 freeabl  optdef: qcopCre      720288      10000
HEAP6 freeabl  strdef_buf : kk      196476      10001
HEAP6 freeabl  kctdef : kkqucu        1460          5
HEAP6 freeabl  kctdef : qcdlgo         876          3
HEAP6 freeabl  ctxdef:kksLoadC         488          1
HEAP6 free     free memory             456          1
[..snip..]
HEAP6 freeabl  opixfalo:froaty          16          1

53 rows selected.

See, it looks like the maximum number of filter conditions allowed in a child cursor is 10000 (this test is done on 11.1.0.7 on Linux). And not all the memory structures do have 10000+ chunks populated! It looks like during the parsing phase Oracle realizes that there are too many filter conditions to be stored in memory, thus it bails out, returning failure from the precompute_subquery function. This causes the precompute_subquery hint to be “ignored” and the resulting execution plan is just like without the hint:

----------------------------------------------------------------------------
| Id  | Operation           | Name     | E-Rows |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |        |       |       |          |
|*  1 |  HASH JOIN SEMI     |          |      1 |  1066K|  1066K| 1084K (0)|
|   2 |   TABLE ACCESS FULL | T1       |      9 |       |       |          |
|   3 |   VIEW              | VW_NSO_1 |      9 |       |       |          |
|   4 |    TABLE ACCESS FULL| T2       |      9 |       |       |          |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"="B")

It should be evident, that the precompute_subquery hint was really not ignored. The optimizer did act on it, but after it realized that all conditions and rules were not met, it bailed out (this should well illustrate the fact that valid hints are never ignored – they are processed, but sometimes the other input conditions make optimizer end up not using it). Note that this hint is a special case, it hits an internal hardcoded limitation based on the real number of rows processed, it’s not a cost-based decision as such.

Another immediate question should be that does Oracle always fire the recursive subquery against T2 when the top-level query is re-executed (when it’s already cached in library cache). If it doesn’t, then we may end up using wrong filter conditions cached in heap6 if T2 contents change.

The answer to that is that whenever Oracle runs parts of a query in separate recursive context first, it will flag the top-level query and subsequent executions fail cursor sharing criteria and always cause a hard parse to happen. From my hash.sql script output above you can see that the child_number increases every execution.

Lets check what’s the reason for non-sharing using the nonshared.sql script (which just queries v$sql_shared_cursor but formats the output better). This script takes the SQL_ID as parameter:

SQL> @nonshared 5phajvmtd7wsy

SQL_ID                        : 5phajvmtd7wsy
ADDRESS                       : 3175A618
CHILD_ADDRESS                 : 3175B56C
CHILD_NUMBER                  : 0
-----------------
SQL_ID                        : 5phajvmtd7wsy
ADDRESS                       : 3175A618
CHILD_ADDRESS                 : 2FD2D6DC
CHILD_NUMBER                  : 1
CURSOR_PARTS_MISMATCH         : Y
-----------------
SQL_ID                        : 5phajvmtd7wsy
ADDRESS                       : 3175A618
CHILD_ADDRESS                 : 3343C208
CHILD_NUMBER                  : 2
CURSOR_PARTS_MISMATCH         : Y
-----------------

PL/SQL procedure successfully completed.

SQL>

Oracle documentation says this for CURSOR_PARTS_MISMATCH:

“Cursor was compiled with subexecution (cursor parts were executed)”

So, Oracle can nowadays pick subquery texts out of the main query and execute them separately and put the results back into main query as “hard-coded” filter values.

It looks like this feature is used for Oracle OLAP option only and not automatically for normal SQL. The reason could be that there are no efficient enough “semijoin” operations against OLAP data in Oracle, thus such optimization (and in my very brief tests the subquery precomputing actually performed worse than a hash semijoin).

Finally, this hint is undocumented (and likely meant for OLAP), so please do not try to start tuning your queries using it! ;)

This article is meant to be educational only, explaining some Oracle internals and also research approach for such cases.

I hope you enjoyed reading it as much as I enjoyed writing (and researching it)!

Also sorry for any typos and bad English which I don’t have time to correct, it’s Friday evening here where I am already and time to go drinking ;-)

Comments powered by Talkyard.