Please observe my queries explain plan and let me know what is the root cause taking more time on second execution.
--GT Table creation
create global temporary table gt_table1
(column1 varchar2(4000),
column2 varchar2(4000),
...........
...........
...........
column32 varchar2(4000));
--Index creation on GT Table
create index gt_ind on gt_table1(column1);
--Inserted few records in GT Table
First time execution:
---------------------
SELECT DISTINCT L.column1
FROM table_1 l, table_2 C
WHERE L.column1 IS NOT NULL
AND C.column1 = 'N'
AND L.column2= C.column2
AND L.column3 = Nvl(C.column4, C.column3)
AND EXISTS (SELECT 1
FROM gt_table1 Gt
WHERE Gt.column1 = 'INV'
AND Gt.column3 = L.column3
AND Gt.column2 = L.column2);
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.24 0.24 0 56144 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.25 0.25 0 56144 0 7
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
7 7 7 HASH UNIQUE (cr=56144 pr=0 pw=0 time=243533 us cost=12 size=107 card=1)
53217 53217 53217 NESTED LOOPS (cr=56144 pr=0 pw=0 time=175098 us cost=11 size=107 card=1)
470 470 470 NESTED LOOPS (cr=45879 pr=0 pw=0 time=885698 us cost=2 size=98 card=1)
40526 40526 40526 SORT UNIQUE (cr=1678 pr=0 pw=0 time=112705 us cost=1 size=88 card=1)
43589 43589 43589 TABLE ACCESS BY INDEX ROWID gt_table1 (cr=1678 pr=0 pw=0 time=58867 us cost=1 size=88 card=1)
43589 43589 43589 INDEX RANGE SCAN gt_ind (cr=468 pr=0 pw=0 time=17695 us cost=1 size=0 card=1)(object id 106265)
470 470 470 TABLE ACCESS BY INDEX ROWID table_1 (cr=44201 pr=0 pw=0 time=66366 us cost=0 size=10 card=1)
40526 40526 40526 INDEX UNIQUE SCAN L_PK (cr=2227 pr=0 pw=0 time=26789 us cost=0 size=0 card=1)(object id 106833)
53217 53217 53217 TABLE ACCESS BY INDEX ROWID table_2 (cr=10265 pr=0 pw=0 time=66445 us cost=9 size=63 card=7)
53217 53217 53217 INDEX RANGE SCAN FN_L (cr=412 pr=0 pw=0 time=14934 us cost=1 size=0 card=40)(object id 107181)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
********************************************************************************
Second time execution:
----------------------
SELECT DISTINCT L.column1
FROM table_1 l, table_2 C
WHERE L.column1 IS NOT NULL
AND C.column1 = 'N'
AND L.column2= C.column2
AND L.column3 = Nvl(C.column4, C.column3)
AND EXISTS (SELECT 1
FROM gt_table1 Gt
WHERE Gt.column1 = 'INV'
AND Gt.column3 = L.column3
AND Gt.column2 = L.column2);
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 32.18 32.19 0 2615299 1 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 32.19 32.21 0 2615299 1 7
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
7 7 7 HASH UNIQUE (cr=2615299 pr=0 pw=0 time=32194380 us cost=496 size=107 card=1)
53217 53217 53217 NESTED LOOPS (cr=2615299 pr=0 pw=0 time=18648158 us cost=495 size=107 card=1)
470 470 470 NESTED LOOPS SEMI (cr=2604882 pr=0 pw=0 time=18493280 us cost=486 size=98 card=1)
2082 2082 2082 TABLE ACCESS FULL table_1 (cr=1774 pr=0 pw=0 time=12285 us cost=486 size=20770 card=2077)
470 470 470 TABLE ACCESS BY INDEX ROWID gt_table1 (cr=2603108 pr=0 pw=0 time=32121867 us cost=0 size=88 card=1)
77602103 77602103 77602103 INDEX RANGE SCAN gt_ind(cr=449669 pr=0 pw=0 time=14391938 us cost=0 size=0 card=1)(object id 106265)
53217 53217 53217 TABLE ACCESS BY INDEX ROWID table_2 (cr=10417 pr=0 pw=0 time=68857 us cost=9 size=63 card=7)
53217 53217 53217 INDEX RANGE SCAN FN_L (cr=592 pr=0 pw=0 time=16834 us cost=1 size=0 card=40)(object id 107181)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
********************************************************************************
In your second execution, pretty much all the time is spent on these steps:
470 470 470 TABLE ACCESS BY INDEX ROWID gt_table1 (cr=2603108 pr=0 pw=0 time=32121867 us cost=0 size=88 card=1)
77602103 77602103 77602103 INDEX RANGE SCAN gt_ind(cr=449669 pr=0 pw=0 time=14391938 us cost=0 size=0 card=1)(object id 106265)
You can see this in the time stat:
time=32121867 us = 32.1 seconds<code>
Why?
Well it processes 77 million rows! 77,602,103 to be precise. That's a lot of work...
But it chucks most of these away to end up with just 470!
You can see this in the first three columns "Rows" columns of the plan.
So how can Oracle Database get it so wrong?
Well it thinks that you'll only access one row from the GTT!
You can see this from the "card=1" stat at the end of the lines accessing g_table1 and g_ind. This is clearly way off.
To overcome this you could try adding the dynamic_sampling hint to your query. e.g.:
<code>SELECT /*+ dynamic_sampling (4) */DISTINCT L.column1
FROM ...
This may help the optimizer figure out more accurate row estimates for the GTT.
Or you could improve the stats for the GTT by capturing them when it's "full" or setting them manually.
I'd also review that index on the GTT. According to the first plan, you access 43,589 rows using it:
43589 43589 43589 TABLE ACCESS BY INDEX ROWID gt_table1 (cr=1678 pr=0 pw=0 time=58867 us cost=1 size=88 card=1)
43589 43589 43589 INDEX RANGE SCAN gt_ind (cr=468 pr=0 pw=0 time=17695 us cost=1 size=0 card=1)(object id 106265)That seems a little high for it to be effective. Though it depends on how many rows there are in the table...
In any case, you may get some benefit by including the other columns you query in it:
create index gt_ind on gt_table1(column1, column2, column3);
Though you'll need to experiment with this to see if it helps. You may even be better off removing the index completely.