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 ;-)
That’s a pretty cool feature.
I wonder now what’s a good practical case where this will come handy.
Hi Christo,
As it’s undocumented and (I think) it’s also meant for OLAP option queries, I don’t recommend anyone actually using this for trying to tune their normal queries.
This article aims to explain yet another layer of complexity in process of interpreting SQL and converting it into execution plan(s).
Nice internals piece.
My initial thought is that, if the subquery is broken out and executed as a separate SQL in its own right, then it may no longer operate as of the same SCN. A change could, theoretically, be committed between the two such that the query results aren’t accurate at a single point in time.
I would expect this hint to be pretty handy (internally) for queries against fact tables on the Exadata storage platform also. It’s a nice complement to a star transformation.
This is a very neat hint but I don’t see it as being a particularly useful one. The cost of accessing the “list of values” table is simply transferred to the parse phase, instead of the execution phase. Do you see it as being a useful optimization tool? If so, can you expand?
Kindest regards,
Hi Mladen,
Also the join itself will be replaced with a filter operation, so there will be no joining of multiple row sources, just filtering one. My (very) brief lab tests didn’t show any performance improvement as CPU usage is considered though.
As I wrote in the end of the post, I intended this post only for demonstrating some new capabilities of Oracle (and intro some of my scripts), but I haven’t seen any tuning/performance value for non-OLAP queries yet.
..
David,
Hmm, interesting idea.
Btw, Oracle 11.1.0.7 should be able to push both filter conditions and also join’s bloom filters down to cell level, so join conditions can be “exadata optimized” even without this subquery precomputation thing (and it’s possible to convert IN subqueries to semijoins..)
@Gary,
When you execute a query, Oracle takes the current system SCN and runs the query (consistent gets) as of that SCN. It’s possible to pass a requested SCN to another query as well (like the SELECT … FROM t AS OF SCN 123 for example), so Oracle is probably doing something similar. It gets the SCN for the subquery precomputation statement and then passes the same SCN on to the main query. That way there would be no consistency issue. I haven’t tested this, if the kernel developer has forgotten to do this, then it’s a bug..
@Karl
In 11g if you have the Diag and Tuning pack licenses, you can use V$SQL_MONITOR (or OEM for that).
Greg Rahn has written about it:
http://structureddata.org/2008/01/06/oracle-11g-real-time-sql-monitoring-using-dbms_sqltunereport_sql_monitor/
In case you don’t have Diag/Tuning pack licenses or you’re not on 10g, then you can use a technique of getting stack trace of the process and translating the function names to execution plan rowsource names. It’s not as good as thorough solution as the 11g SQL monitoring thing though..
http://blog.tanelpoder.com/2008/06/15/advanced-oracle-troubleshooting-guide-part-6-understanding-oracle-execution-plans-with-os_explain/
wonderful article!
Hi Tanel,
I have a view named ‘CFMP_VPIPELINE’ and table named ‘T_LOANAPPLICATION’.
Explain plan for the query on T_LOANAPPLICATION is
select loanapplicationid from t_loanapplication where filoanid = ‘xxxx’
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
—————————————————————————————————-
Plan hash value: 1345730894
————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 11 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_LOANAPPLICATION | 1 | 11 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LOANAPP_FILOANID | 1 | | 3 (0)| 00:00:01 |
————————————————————————————————-
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
—————————————————————————————————-
—————————————————
2 – access(“FILOANID”=’xxxx’)
14 rows selected.
Looks good to me, its using index and cost is also very less.
Explain plan for query on view CFMP_VPIPELINE is
select * from cfmp_vpipeline where loanapplicationid = :loan_number;
Plan hash value: 1521485150
———————————————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 7218 | 50 (0)| 00:00:01 |
| 1 | VIEW | CFMP_VPIPELINE | 1 | 7218 | 50 (0)| 00:00:01 |
| 2 | MERGE JOIN OUTER | | 1 | 1275 | 50 (0)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 1 | 1256 | 43 (0)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 1 | 1230 | 35 (0)| 00:00:01 |
| 5 | NESTED LOOPS OUTER | | 1 | 1203 | 34 (0)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 1175 | 33 (0)| 00:00:01 |
| 7 | NESTED LOOPS OUTER | | 1 | 1163 | 32 (0)| 00:00:01 |
| 8 | NESTED LOOPS OUTER | | 1 | 1052 | 30 (0)| 00:00:01 |
| 9 | NESTED LOOPS OUTER | | 1 | 944 | 28 (0)| 00:00:01 |
| 10 | NESTED LOOPS OUTER | | 1 | 826 | 26 (0)| 00:00:01 |
| 11 | NESTED LOOPS OUTER | | 1 | 769 | 24 (0)| 00:00:01 |
| 12 | NESTED LOOPS OUTER | | 1 | 715 | 22 (0)| 00:00:01 |
| 13 | NESTED LOOPS OUTER | | 1 | 696 | 20 (0)| 00:00:01 |
| 14 | NESTED LOOPS OUTER | | 1 | 218 | 18 (0)| 00:00:01 |
| 15 | NESTED LOOPS OUTER | | 1 | 197 | 16 (0)| 00:00:01 |
| 16 | NESTED LOOPS OUTER | | 1 | 187 | 14 (0)| 00:00:01 |
| 17 | NESTED LOOPS OUTER | | 1 | 163 | 12 (0)| 00:00:01 |
| 18 | NESTED LOOPS OUTER | | 1 | 146 | 10 (0)| 00:00:01 |
| 19 | NESTED LOOPS OUTER | | 1 | 138 | 8 (0)| 00:00:01 |
| 20 | NESTED LOOPS OUTER | | 1 | 128 | 6 (0)| 00:00:01 |
| 21 | NESTED LOOPS OUTER | | 1 | 114 | 4 (0)| 00:00:01 |
|* 22 | TABLE ACCESS BY INDEX ROWID| T_LOANAPPLICATION | 1 | 87 | 3 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | SYS_C004114 | 1 | | 2 (0)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID| T_LOANPURPOSETYPE | 7 | 189 | 1 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | SYS_C004117 | 1 | | 0 (0)| 00:00:01 |
| 26 | TABLE ACCESS BY INDEX ROWID | T_ADDITIONALLOANINFO2 | 1387K| 18M| 2 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | SYS_C004152 | 1 | | 1 (0)| 00:00:01 |
| 28 | TABLE ACCESS BY INDEX ROWID | T_TRANSACTION | 4858K| 46M| 2 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | SYS_C004132 | 1 | | 1 (0)| 00:00:01 |
| 30 | TABLE ACCESS BY INDEX ROWID | T_GOVLOAN | 4845K| 36M| 2 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | SYS_C004108 | 1 | | 1 (0)| 00:00:01 |
| 32 | TABLE ACCESS BY INDEX ROWID | T_TITLEDETAILS | 4795K| 77M| 2 (0)| 00:00:01 |
|* 33 | INDEX UNIQUE SCAN | SYS_C004131 | 1 | | 1 (0)| 00:00:01 |
| 34 | TABLE ACCESS BY INDEX ROWID | T_CLOSINGDETAIL | 4865K| 111M| 2 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | SYS_C004091 | 1 | | 1 (0)| 00:00:01 |
| 36 | TABLE ACCESS BY INDEX ROWID | T_FUNDINGDETAIL | 4858K| 46M| 2 (0)| 00:00:01 |
|* 37 | INDEX UNIQUE SCAN | SYS_C004105 | 1 | | 1 (0)| 00:00:01 |
| 38 | TABLE ACCESS BY INDEX ROWID | CFMP_HPK_ALL_DOC_DATES | 1 | 21 | 2 (0)| 00:00:01 |
|* 39 | INDEX UNIQUE SCAN | XPKCFMP_HPK_ALL_DOC_DATES | 1 | | 1 (0)| 00:00:01 |
| 40 | TABLE ACCESS BY INDEX ROWID | CFMP_HPK_LOANAPPLICATIONID | 1 | 478 | 2 (0)| 00:00:01 |
|* 41 | INDEX UNIQUE SCAN | XPKCFMP_HPK_LOANAPPLICATIONID | 1 | | 1 (0)| 00:00:01 |
| 42 | TABLE ACCESS BY INDEX ROWID | CFMP_HPK_ACTIVITY_DATA | 1 | 19 | 2 (0)| 00:00:01 |
|* 43 | INDEX UNIQUE SCAN | XPKCFMP_HPK_ACTIVITY_DATA | 1 | | 1 (0)| 00:00:01 |
| 44 | TABLE ACCESS BY INDEX ROWID | HR_PHONEBOOK_DENORM | 375K| 19M| 2 (0)| 00:00:01 |
|* 45 | INDEX UNIQUE SCAN | IDX_HR_PHONEBOOK_DENORM_PK | 1 | | 1 (0)| 00:00:01 |
| 46 | TABLE ACCESS BY INDEX ROWID | HR_PHONEBOOK_DENORM | 375K| 20M| 2 (0)| 00:00:01 |
|* 47 | INDEX UNIQUE SCAN | IDX_HR_PHONEBOOK_DENORM_PK | 1 | | 1 (0)| 00:00:01 |
| 48 | TABLE ACCESS BY INDEX ROWID | HR_PHONEBOOK_DENORM | 375K| 42M| 2 (0)| 00:00:01 |
|* 49 | INDEX UNIQUE SCAN | IDX_HR_PHONEBOOK_DENORM_PK | 1 | | 1 (0)| 00:00:01 |
| 50 | TABLE ACCESS BY INDEX ROWID | HR_PHONEBOOK_DENORM | 375K| 38M| 2 (0)| 00:00:01 |
|* 51 | INDEX UNIQUE SCAN | IDX_HR_PHONEBOOK_DENORM_PK | 1 | | 1 (0)| 00:00:01 |
| 52 | TABLE ACCESS BY INDEX ROWID | HR_PHONEBOOK_DENORM | 375K| 39M| 2 (0)| 00:00:01 |
|* 53 | INDEX UNIQUE SCAN | IDX_HR_PHONEBOOK_DENORM_PK | 1 | | 1 (0)| 00:00:01 |
|* 54 | TABLE ACCESS BY INDEX ROWID | T_ENTITYTABLE | 5 | 60 | 1 (0)| 00:00:01 |
|* 55 | INDEX UNIQUE SCAN | SYS_C004102 | 1 | | 0 (0)| 00:00:01 |
| 56 | TABLE ACCESS BY INDEX ROWID | T_DOCTYPE | 23 | 644 | 1 (0)| 00:00:01 |
|* 57 | INDEX UNIQUE SCAN | SYS_C004101 | 1 | | 0 (0)| 00:00:01 |
| 58 | TABLE ACCESS BY INDEX ROWID | T_LOANTYPE | 5 | 135 | 1 (0)| 00:00:01 |
|* 59 | INDEX UNIQUE SCAN | SYS_C004118 | 1 | | 0 (0)| 00:00:01 |
| 60 | VIEW | | 1 | 26 | 8 (0)| 00:00:01 |
| 61 | SORT GROUP BY | | 1 | 9 | 8 (0)| 00:00:01 |
| 62 | TABLE ACCESS BY INDEX ROWID | T_NMLSID_LOG | 4 | 36 | 8 (0)| 00:00:01 |
|* 63 | INDEX RANGE SCAN | NMLSIDLOG_LOANAPPID_INDX | 4 | | 3 (0)| 00:00:01 |
| 64 | BUFFER SORT | | 1 | 19 | 42 (0)| 00:00:01 |
|* 65 | TABLE ACCESS BY INDEX ROWID | T_DISBURSEMENT | 1 | 19 | 7 (0)| 00:00:01 |
|* 66 | INDEX RANGE SCAN | DISBURSE_LOANAPPLIC_INDX | 56 | | 3 (0)| 00:00:01 |
———————————————————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
22 – filter(“B”.”FILOANID” IS NOT NULL)
23 – access(“B”.”LOANAPPLICATIONID”=TO_NUMBER(:LOAN_NUMBER))
25 – access(“LPT”.”LOANPURPOSECODE”(+)=”B”.”LOANPURPOSECODE”)
27 – access(“AL”.”LOANINFO2ID”(+)=”B”.”LOANINFO2ID”)
29 – access(“BB”.”TRANSACTIONID”(+)=”B”.”TRANSACTIONID”)
31 – access(“O”.”GOVLOANID”(+)=”B”.”GOVLOANID”)
33 – access(“M”.”TITLEDETAILSID”(+)=”B”.”TITLEDETAILSID”)
35 – access(“L”.”CLOSINGDETAILID”(+)=”B”.”CLOSINGDETAILID”)
37 – access(“F2″.”FUNDINGDETAILID”(+)=”B”.”FUNDINGDETAILID”)
39 – access(“AD1″.”LOANAPPLICATIONID”(+)=TO_NUMBER(:LOAN_NUMBER))
41 – access(“LI”.”LOANAPPLICATIONID”(+)=TO_NUMBER(:LOAN_NUMBER))
43 – access(“AD”.”LOANAPPLICATIONID”(+)=TO_NUMBER(:LOAN_NUMBER))
45 – access(“PF_PB”.”EMP_ID”(+)=”LI”.”PFPC_SID”)
47 – access(“PO_PB”.”EMP_ID”(+)=”LI”.”POPC_SID”)
49 – access(“PR_PB”.”EMP_ID”(+)=”LI”.”PRPC_SID”)
51 – access(“LO_PB”.”EMP_ID”(+)=”LI”.”LOPC_SID”)
53 – access(“CL_PB”.”EMP_ID”(+)=”LI”.”CLPC_SID”)
54 – filter(“ET”.”ENTITYCODE”(+)=’1185′)
55 – access(“ET”.”ENTITYID”(+)=”LI”.”AFFINITYCOMPANYID”)
57 – access(“DT”.”DOCTYPE”(+)=”LI”.”DOCTYPE”)
59 – access(“LT”.”LOANTYPECODE”(+)=”LI”.”LOANTYPECODE”)
63 – access(“NML”.”LOANAPPLICATIONID”=TO_NUMBER(:LOAN_NUMBER))
65 – filter(“F1″.”DISBURSEMENTTYPECODE”(+)=’NET’)
66 – access(“F1″.”LOANAPPLICATIONID”(+)=TO_NUMBER(:LOAN_NUMBER))
This query also runs fine and cost is also very low.
But, if I try to combine above two queries as shown below, its taking so long to run
select * from cfmp_vpipeline where loanapplicationid in (select loanapplicationid from t_loanapplication where filoanid = ‘xxxx’)
Plan hash value: 216031662
———————————————————————————————————————————
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
———————————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 7229 | | 1098K (1)| 03:39:39 |
|* 1 | HASH JOIN | | 1 | 7229 | | 1098K (1)| 03:39:39 |
| 2 | TABLE ACCESS BY INDEX ROWID | T_LOANAPPLICATION | 1 | 11 | | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | LOANAPP_FILOANID | 1 | | | 3 (0)| 00:00:01 |
| 4 | VIEW | CFMP_VPIPELINE | 2568K| 17G| | 1098K (1)| 03:39:39 |
|* 5 | HASH JOIN RIGHT OUTER | | 2568K| 3123M| | 1098K (1)| 03:39:39 |
| 6 | TABLE ACCESS FULL | T_LOANTYPE | 5 | 135 | | 3 (0)| 00:00:01 |
|* 7 | HASH JOIN RIGHT OUTER | | 2568K| 3057M| 47M| 1098K (1)| 03:39:38 |
| 8 | TABLE ACCESS BY INDEX ROWID | T_DISBURSEMENT | 1596K| 28M| | 126K (1)| 00:25:14 |
|* 9 | INDEX RANGE SCAN | DISBURSE_DISBURSEME_INDX | 1635K| | | 4732 (1)| 00:00:57 |
|* 10 | HASH JOIN RIGHT OUTER | | 1706K| 2000M| 43M| 869K (1)| 02:53:54 |
| 11 | TABLE ACCESS FULL | HR_PHONEBOOK_DENORM | 375K| 38M| | 3571 (1)| 00:00:43 |
|* 12 | HASH JOIN RIGHT OUTER | | 1706K| 1824M| 16M| 772K (1)| 02:34:28 |
| 13 | VIEW | | 455K| 11M| | 9535 (2)| 00:01:55 |
| 14 | HASH GROUP BY | | 455K| 4005K| 47M| 9535 (2)| 00:01:55 |
| 15 | TABLE ACCESS FULL | T_NMLSID_LOG | 2023K| 17M| | 5608 (1)| 00:01:08 |
|* 16 | HASH JOIN RIGHT OUTER | | 1706K| 1782M| 46M| 672K (1)| 02:14:31 |
| 17 | TABLE ACCESS FULL | HR_PHONEBOOK_DENORM | 375K| 42M| | 3571 (1)| 00:00:43 |
|* 18 | HASH JOIN RIGHT OUTER | | 1706K| 1590M| | 586K (1)| 01:57:23 |
| 19 | TABLE ACCESS FULL | T_DOCTYPE | 23 | 644 | | 3 (0)| 00:00:01 |
|* 20 | HASH JOIN RIGHT OUTER | | 1706K| 1544M| 44M| 586K (1)| 01:57:22 |
| 21 | TABLE ACCESS FULL | HR_PHONEBOOK_DENORM | 375K| 39M| | 3571 (1)| 00:00:43 |
|* 22 | HASH JOIN RIGHT OUTER | | 1706K| 1364M| 23M| 512K (1)| 01:42:30 |
| 23 | TABLE ACCESS FULL | HR_PHONEBOOK_DENORM | 375K| 19M| | 3571 (1)| 00:00:43 |
|* 24 | HASH JOIN RIGHT OUTER | | 1706K| 1276M| 24M| 443K (1)| 01:28:42 |
| 25 | TABLE ACCESS FULL | HR_PHONEBOOK_DENORM | 375K| 20M| | 3571 (1)| 00:00:43 |
|* 26 | HASH JOIN RIGHT OUTER | | 1706K| 1183M| | 378K (1)| 01:15:48 |
|* 27 | TABLE ACCESS FULL | T_ENTITYTABLE | 5 | 60 | | 367 (1)| 00:00:05 |
|* 28 | HASH JOIN OUTER | | 1706K| 1163M| 398M| 378K (1)| 01:15:43 |
|* 29 | HASH JOIN RIGHT OUTER | | 1676K| 379M| 44M| 300K (1)| 01:00:10 |
| 30 | TABLE ACCESS FULL | CFMP_HPK_ACTIVITY_DATA | 1500K| 27M| | 1089 (3)| 00:00:14 |
|* 31 | HASH JOIN RIGHT OUTER | | 1656K| 344M| 167M| 279K (2)| 00:55:54 |
| 32 | TABLE ACCESS FULL | T_CLOSINGDETAIL | 4865K| 111M| | 36500 (2)| 00:07:19 |
|* 33 | HASH JOIN RIGHT OUTER | | 1641K| 303M| 101M| 214K (1)| 00:43:00 |
| 34 | TABLE ACCESS FULL | T_FUNDINGDETAIL | 4858K| 46M| | 5223 (2)| 00:01:03 |
|* 35 | HASH JOIN RIGHT OUTER | | 1634K| 286M| 101M| 189K (2)| 00:37:55 |
| 36 | TABLE ACCESS FULL | T_TRANSACTION | 4858K| 46M| | 15243 (2)| 00:03:03 |
|* 37 | HASH JOIN RIGHT OUTER | | 1633K| 271M| 47M| 154K (2)| 00:30:58 |
| 38 | TABLE ACCESS FULL | CFMP_HPK_ALL_DOC_DATES | 1500K| 30M| | 960 (4)| 00:00:12 |
|* 39 | HASH JOIN RIGHT OUTER | | 1633K| 238M| 132M| 138K (2)| 00:27:45 |
| 40 | TABLE ACCESS FULL | T_TITLEDETAILS | 4795K| 77M| | 9119 (2)| 00:01:50 |
|* 41 | HASH JOIN RIGHT OUTER | | 1633K| 211M| 92M| 111K (2)| 00:22:19 |
| 42 | TABLE ACCESS FULL | T_GOVLOAN | 4845K| 36M| | 25063 (2)| 00:05:01 |
|* 43 | HASH JOIN RIGHT OUTER | | 1633K| 199M| | 70979 (2)| 00:14:12 |
| 44 | TABLE ACCESS FULL | T_LOANPURPOSETYPE | 7 | 189 | | 3 (0)| 00:00:01 |
|* 45 | HASH JOIN RIGHT OUTER| | 1633K| 157M| 34M| 70965 (2)| 00:14:12 |
| 46 | TABLE ACCESS FULL | T_ADDITIONALLOANINFO2 | 1387K| 18M| | 9062 (2)| 00:01:49 |
|* 47 | TABLE ACCESS FULL | T_LOANAPPLICATION | 1633K| 135M| | 52516 (2)| 00:10:31 |
| 48 | TABLE ACCESS FULL | CFMP_HPK_LOANAPPLICATIONID | 1520K| 693M| | 22791 (2)| 00:04:34 |
———————————————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – access(“LOANAPPLICATIONID”=”LOANAPPLICATIONID”)
3 – access(“FILOANID”=’xxxx’)
5 – access(“LT”.”LOANTYPECODE”(+)=”LI”.”LOANTYPECODE”)
7 – access(“F1″.”LOANAPPLICATIONID”(+)=”B”.”LOANAPPLICATIONID”)
9 – access(“F1″.”DISBURSEMENTTYPECODE”(+)=’NET’)
10 – access(“LO_PB”.”EMP_ID”(+)=”LI”.”LOPC_SID”)
12 – access(“NM”.”LOANAPPLICATIONID”(+)=”B”.”LOANAPPLICATIONID”)
16 – access(“PR_PB”.”EMP_ID”(+)=”LI”.”PRPC_SID”)
18 – access(“DT”.”DOCTYPE”(+)=”LI”.”DOCTYPE”)
20 – access(“CL_PB”.”EMP_ID”(+)=”LI”.”CLPC_SID”)
22 – access(“PF_PB”.”EMP_ID”(+)=”LI”.”PFPC_SID”)
24 – access(“PO_PB”.”EMP_ID”(+)=”LI”.”POPC_SID”)
26 – access(“ET”.”ENTITYID”(+)=”LI”.”AFFINITYCOMPANYID”)
27 – filter(“ET”.”ENTITYCODE”(+)=’1185′)
28 – access(“LI”.”LOANAPPLICATIONID”(+)=”B”.”LOANAPPLICATIONID”)
29 – access(“AD”.”LOANAPPLICATIONID”(+)=”B”.”LOANAPPLICATIONID”)
31 – access(“L”.”CLOSINGDETAILID”(+)=”B”.”CLOSINGDETAILID”)
33 – access(“F2″.”FUNDINGDETAILID”(+)=”B”.”FUNDINGDETAILID”)
35 – access(“BB”.”TRANSACTIONID”(+)=”B”.”TRANSACTIONID”)
37 – access(“AD1″.”LOANAPPLICATIONID”(+)=”B”.”LOANAPPLICATIONID”)
39 – access(“M”.”TITLEDETAILSID”(+)=”B”.”TITLEDETAILSID”)
41 – access(“O”.”GOVLOANID”(+)=”B”.”GOVLOANID”)
43 – access(“LPT”.”LOANPURPOSECODE”(+)=”B”.”LOANPURPOSECODE”)
45 – access(“AL”.”LOANINFO2ID”(+)=”B”.”LOANINFO2ID”)
47 – filter(“B”.”FILOANID” IS NOT NULL)
To overcome this strange behaviour of optmizer, I tried using PRECOMPUTE_SUBQUERY hint
select * from cfmp_vpipeline where loanapplicationid in (select /*+ PRECOMPUTE_SUBQUERY */ loanapplicationid from t_loanapplication where filoanid = ‘xxxx’)
Plan hash value: 216031662
———————————————————————————————————————————
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
———————————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 7229 | | 1098K (1)| 03:39:39 |
|* 1 | HASH JOIN | | 1 | 7229 | | 1098K (1)| 03:39:39 |
| 2 | TABLE ACCESS BY INDEX ROWID | T_LOANAPPLICATION | 1 | 11 | | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | LOANAPP_FILOANID | 1 | | | 3 (0)| 00:00:01 |
| 4 | VIEW | CFMP_VPIPELINE | 2568K| 17G| | 1098K (1)| 03:39:39 |
|* 5 | HASH JOIN RIGHT OUTER | | 2568K| 3123M| | 1098K (1)| 03:39:39 |
| 6 | TABLE ACCESS FULL | T_LOANTYPE | 5 | 135 | | 3 (0)| 00:00:01 |
|* 7 | HASH JOIN RIGHT OUTER | | 2568K| 3057M| 47M| 1098K (1)| 03:39:38 |
| 8 | TABLE ACCESS BY INDEX ROWID | T_DISBURSEMENT | 1596K| 28M| | 126K (1)| 00:25:14 |
|* 9 | INDEX RANGE SCAN | DISBURSE_DISBURSEME_INDX | 1635K| | | 4732 (1)| 00:00:57 |
|* 10 | HASH JOIN RIGHT OUTER | | 1706K| 2000M| 43M| 869K (1)| 02:53:54 |
| 11 | TABLE ACCESS FULL | HR_PHONEBOOK_DENORM | 375K| 38M| | 3571 (1)| 00:00:43 |
|* 12 | HASH JOIN RIGHT OUTER | | 1706K| 1824M| 16M| 772K (1)| 02:34:28 |
| 13 | VIEW | | 455K| 11M| | 9535 (2)| 00:01:55 |
| 14 | HASH GROUP BY | | 455K| 4005K| 47M| 9535 (2)| 00:01:55 |
| 15 | TABLE ACCESS FULL | T_NMLSID_LOG | 2023K| 17M| | 5608 (1)| 00:01:08 |
|* 16 | HASH JOIN RIGHT OUTER | | 1706K| 1782M| 46M| 672K (1)| 02:14:31 |
| 17 | TABLE ACCESS FULL | HR_PHONEBOOK_DENORM | 375K| 42M| | 3571 (1)| 00:00:43 |
|* 18 | HASH JOIN RIGHT OUTER | | 1706K| 1590M| | 586K (1)| 01:57:23 |
| 19 | TABLE ACCESS FULL | T_DOCTYPE | 23 | 644 | | 3 (0)| 00:00:01 |
|* 20 | HASH JOIN RIGHT OUTER | | 1706K| 1544M| 44M| 586K (1)| 01:57:22 |
| 21 | TABLE ACCESS FULL | HR_PHONEBOOK_DENORM | 375K| 39M| | 3571 (1)| 00:00:43 |
|* 22 | HASH JOIN RIGHT OUTER | | 1706K| 1364M| 23M| 512K (1)| 01:42:30 |
| 23 | TABLE ACCESS FULL | HR_PHONEBOOK_DENORM | 375K| 19M| | 3571 (1)| 00:00:43 |
|* 24 | HASH JOIN RIGHT OUTER | | 1706K| 1276M| 24M| 443K (1)| 01:28:42 |
| 25 | TABLE ACCESS FULL | HR_PHONEBOOK_DENORM | 375K| 20M| | 3571 (1)| 00:00:43 |
|* 26 | HASH JOIN RIGHT OUTER | | 1706K| 1183M| | 378K (1)| 01:15:48 |
|* 27 | TABLE ACCESS FULL | T_ENTITYTABLE | 5 | 60 | | 367 (1)| 00:00:05 |
|* 28 | HASH JOIN OUTER | | 1706K| 1163M| 398M| 378K (1)| 01:15:43 |
|* 29 | HASH JOIN RIGHT OUTER | | 1676K| 379M| 44M| 300K (1)| 01:00:10 |
| 30 | TABLE ACCESS FULL | CFMP_HPK_ACTIVITY_DATA | 1500K| 27M| | 1089 (3)| 00:00:14 |
|* 31 | HASH JOIN RIGHT OUTER | | 1656K| 344M| 167M| 279K (2)| 00:55:54 |
| 32 | TABLE ACCESS FULL | T_CLOSINGDETAIL | 4865K| 111M| | 36500 (2)| 00:07:19 |
|* 33 | HASH JOIN RIGHT OUTER | | 1641K| 303M| 101M| 214K (1)| 00:43:00 |
| 34 | TABLE ACCESS FULL | T_FUNDINGDETAIL | 4858K| 46M| | 5223 (2)| 00:01:03 |
|* 35 | HASH JOIN RIGHT OUTER | | 1634K| 286M| 101M| 189K (2)| 00:37:55 |
| 36 | TABLE ACCESS FULL | T_TRANSACTION | 4858K| 46M| | 15243 (2)| 00:03:03 |
|* 37 | HASH JOIN RIGHT OUTER | | 1633K| 271M| 47M| 154K (2)| 00:30:58 |
| 38 | TABLE ACCESS FULL | CFMP_HPK_ALL_DOC_DATES | 1500K| 30M| | 960 (4)| 00:00:12 |
|* 39 | HASH JOIN RIGHT OUTER | | 1633K| 238M| 132M| 138K (2)| 00:27:45 |
| 40 | TABLE ACCESS FULL | T_TITLEDETAILS | 4795K| 77M| | 9119 (2)| 00:01:50 |
|* 41 | HASH JOIN RIGHT OUTER | | 1633K| 211M| 92M| 111K (2)| 00:22:19 |
| 42 | TABLE ACCESS FULL | T_GOVLOAN | 4845K| 36M| | 25063 (2)| 00:05:01 |
|* 43 | HASH JOIN RIGHT OUTER | | 1633K| 199M| | 70979 (2)| 00:14:12 |
| 44 | TABLE ACCESS FULL | T_LOANPURPOSETYPE | 7 | 189 | | 3 (0)| 00:00:01 |
|* 45 | HASH JOIN RIGHT OUTER| | 1633K| 157M| 34M| 70965 (2)| 00:14:12 |
| 46 | TABLE ACCESS FULL | T_ADDITIONALLOANINFO2 | 1387K| 18M| | 9062 (2)| 00:01:49 |
|* 47 | TABLE ACCESS FULL | T_LOANAPPLICATION | 1633K| 135M| | 52516 (2)| 00:10:31 |
| 48 | TABLE ACCESS FULL | CFMP_HPK_LOANAPPLICATIONID | 1520K| 693M| | 22791 (2)| 00:04:34 |
———————————————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – access(“LOANAPPLICATIONID”=”LOANAPPLICATIONID”)
3 – access(“FILOANID”=’xxxx’)
5 – access(“LT”.”LOANTYPECODE”(+)=”LI”.”LOANTYPECODE”)
7 – access(“F1″.”LOANAPPLICATIONID”(+)=”B”.”LOANAPPLICATIONID”)
9 – access(“F1″.”DISBURSEMENTTYPECODE”(+)=’NET’)
10 – access(“LO_PB”.”EMP_ID”(+)=”LI”.”LOPC_SID”)
12 – access(“NM”.”LOANAPPLICATIONID”(+)=”B”.”LOANAPPLICATIONID”)
16 – access(“PR_PB”.”EMP_ID”(+)=”LI”.”PRPC_SID”)
18 – access(“DT”.”DOCTYPE”(+)=”LI”.”DOCTYPE”)
20 – access(“CL_PB”.”EMP_ID”(+)=”LI”.”CLPC_SID”)
22 – access(“PF_PB”.”EMP_ID”(+)=”LI”.”PFPC_SID”)
24 – access(“PO_PB”.”EMP_ID”(+)=”LI”.”POPC_SID”)
26 – access(“ET”.”ENTITYID”(+)=”LI”.”AFFINITYCOMPANYID”)
27 – filter(“ET”.”ENTITYCODE”(+)=’1185′)
28 – access(“LI”.”LOANAPPLICATIONID”(+)=”B”.”LOANAPPLICATIONID”)
29 – access(“AD”.”LOANAPPLICATIONID”(+)=”B”.”LOANAPPLICATIONID”)
31 – access(“L”.”CLOSINGDETAILID”(+)=”B”.”CLOSINGDETAILID”)
33 – access(“F2″.”FUNDINGDETAILID”(+)=”B”.”FUNDINGDETAILID”)
35 – access(“BB”.”TRANSACTIONID”(+)=”B”.”TRANSACTIONID”)
37 – access(“AD1″.”LOANAPPLICATIONID”(+)=”B”.”LOANAPPLICATIONID”)
39 – access(“M”.”TITLEDETAILSID”(+)=”B”.”TITLEDETAILSID”)
41 – access(“O”.”GOVLOANID”(+)=”B”.”GOVLOANID”)
43 – access(“LPT”.”LOANPURPOSECODE”(+)=”B”.”LOANPURPOSECODE”)
45 – access(“AL”.”LOANINFO2ID”(+)=”B”.”LOANINFO2ID”)
47 – filter(“B”.”FILOANID” IS NOT NULL)
I dont see any difference in the plans..nor in the execution. I had also tried using push_subq hint within the subquery but it also didnt helped me.
I could see the difference of using hint PRECOMPUTE_SUBQUERY when I replace view with the table, but it has to be view only, i did also tried materializing the view
(using /*+ materialize */ hint) but no luck :(
Please help me to overcome this problem.
PS : Version Details
SQL> show rel
release 1002000400