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 99 rows selected.
SQL> SQL> select * from t2;
B
11 12 13 14 15 16 17 18 199 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=1232490329981197PARSING 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 99 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> @hashHASH_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 1KGLNAHSH 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 99 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 5phajvmtd7wsySQL_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 ;-)
