Multipart cursor subexecution and PRECOMPUTE_SUBQUERY hint

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 ;-)

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

This entry was posted in Cool stuff, Oracle and tagged , , , . Bookmark the permalink.

15 Responses to Multipart cursor subexecution and PRECOMPUTE_SUBQUERY hint

  1. Christo Kutrovsky says:

    That’s a pretty cool feature.

    I wonder now what’s a good practical case where this will come handy.

  2. Tanel Poder says:

    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).

  3. Gary says:

    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.

  4. Pingback: Log Buffer #133: A Carnival of the Vanities for DBAs

  5. 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.

  6. 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,

  7. Tanel Poder says:

    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..)

  8. Tanel Poder says:

    @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..

  9. Tanel Poder says:

    @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/

  10. dalin qin says:

    wonderful article!

  11. Pingback: Upgrade Whoa « Oracle Scratchpad

  12. raghu says:

    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.

  13. raghu says:

    PS : Version Details

    SQL> show rel
    release 1002000400

  14. Pingback: ORA-00600: [kglUnKeepHandle] при превышении лимита количества дочерних курсоров « Oracle mechanics

  15. Pingback: 12c fixed subquery | Oracle Scratchpad

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>