Hi Experts,
I have a cursor query which calls proc_x inside it, like this -
. . .
for i in (master_cursor_query)
loop
proc_x(. . .);
end loop;
. . .
---// inside proc_x
INSERT INTO table_temp
select . . .
from . . .
where . . . ;
The plan of the query when executed for 1 record is like:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 21 (100)| 1 |00:00:00.01 | 27 | | | |
| 1 | VIEW | | 1 | 1 | 21 (5)| 1 |00:00:00.01 | 27 | | | |
| 2 | WINDOW SORT | | 1 | 1 | 21 (5)| 1 |00:00:00.01 | 27 | 2048 | 2048 | 1/0/0|
| 3 | NESTED LOOPS | | 1 | 1 | 20 (0)| 1 |00:00:00.01 | 27 | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 20 (0)| 1 |00:00:00.01 | 26 | | | |
| 5 | NESTED LOOPS OUTER | | 1 | 1 | 19 (0)| 1 |00:00:00.01 | 24 | | | |
| 6 | NESTED LOOPS OUTER | | 1 | 1 | 18 (0)| 1 |00:00:00.01 | 21 | | | |
| 7 | NESTED LOOPS OUTER | | 1 | 1 | 16 (0)| 1 |00:00:00.01 | 17 | | | |
| 8 | NESTED LOOPS OUTER | | 1 | 1 | 13 (0)| 1 |00:00:00.01 | 13 | | | |
| 9 | NESTED LOOPS | | 1 | 1 | 9 (0)| 1 |00:00:00.01 | 8 | | | |
|* 10 | TABLE ACCESS BY INDEX ROWID| LOC | 1 | 1 | 4 (0)| 1 |00:00:00.01 | 4 | | | |
|* 11 | INDEX RANGE SCAN | LOC_IX_02 | 1 | 1 | 3 (0)| 1 |00:00:00.01 | 3 | | | |
|* 12 | TABLE ACCESS BY INDEX ROWID| LOC_TES | 1 | 1 | 5 (0)| 1 |00:00:00.01 | 4 | | | |
|* 13 | INDEX RANGE SCAN | LOC_TES_IX_01 | 1 | 6 | 3 (0)| 1 |00:00:00.01 | 3 | | | |
|* 14 | TABLE ACCESS BY INDEX ROWID | LOC_XF | 1 | 1 | 4 (0)| 1 |00:00:00.01 | 5 | | | |
|* 15 | INDEX RANGE SCAN | ORD_OCC_REC_X_REF_IX_01 | 1 | 1 | 3 (0)| 1 |00:00:00.01 | 4 | | | |
|* 16 | TABLE ACCESS BY INDEX ROWID | LOC_TES_SCD | 1 | 1 | 3 (0)| 1 |00:00:00.01 | 4 | | | |
|* 17 | INDEX RANGE SCAN | LOC_TES_SCD_PK | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 3 | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | ORH | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 4 | | | |
|* 19 | INDEX UNIQUE SCAN | ORD_REC_HEAD_UQ_REC_NUMBER | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 3 | | | |
| 20 | TABLE ACCESS BY INDEX ROWID | FCS | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 3 | | | |
|* 21 | INDEX UNIQUE SCAN | FCS_PK | 1 | 1 | 0 (0)| 1 |00:00:00.01 | 2 | | | |
|* 22 | INDEX UNIQUE SCAN | TES_PK | 1 | 1 | 0 (0)| 1 |00:00:00.01 | 2 | | | |
| 23 | TABLE ACCESS BY INDEX ROWID | TES | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 1 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - filter(("OCC"."FAC_D"=19 AND "OCC"."SORS_SYS"='KS' AND "OCC"."SENT"='N' AND "OCC"."OR_TYP"='P'))
11 - access("OCC"."PA_ID"=302179 AND "OCC"."OR_DR_DT"=TO_DATE(' 2015-05-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "OCC"."STATUS"='A')
12 - filter("OCCT"."STATUS"='A')
13 - access("OCC"."LOC_ID"="OCCT"."LOC_ID")
14 - filter(UPPER("ACTIVE_YN")='Y')
15 - access("OCCT"."LOC_ID"="ORR"."LOC_ID" AND "OCCT"."LOC_TES_ID"="ORR"."LOC_TES_ID")
16 - filter("OCCTS"."STATUS"='A')
17 - access("OCCT"."LOC_TES_ID"="OCCTS"."LOC_TES_ID")
19 - access("ORR"."REC_NO"="ORH"."REC_NO")
21 - access("OCCTS"."SCD_ID"="FCS"."SCD_ID")
22 - access("T"."TES_ID"="OCCT"."TES_ID")
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
When the query is executed for all the records i.e. as a single SQL query,
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | O/1/M | Max-Tmp |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 296K(100)| 15550 |00:03:16.32 | 1458K| 754K| 254K| | | | |
| 1 | VIEW | | 1 | 131K| 296K (2)| 15550 |00:03:16.32 | 1458K| 754K| 254K| | | | |
| 2 | WINDOW SORT | | 1 | 131K| 296K (2)| 15550 |00:03:16.31 | 1458K| 754K| 254K| 774M| 9108K| | |
|* 3 | FILTER | | 1 | | | 3649K|00:03:07.64 | 1458K| 753K| 166K| | | | |
|* 4 | HASH JOIN RIGHT OUTER | | 1 | 131K| 292K (2)| 3649K|00:03:06.43 | 1458K| 753K| 166K| 2045K| 2045K| 1/0/0| |
| 5 | TABLE ACCESS FULL | FCS | 1 | 13272 | 69 (0)| 13288 |00:00:00.01 | 133 | 0 | 0 | | | | |
|* 6 | HASH JOIN RIGHT OUTER | | 1 | 131K| 291K (2)| 3649K|00:03:02.95 | 1458K| 753K| 166K| 1613M| 30M| | 1242K|
|* 7 | TABLE ACCESS FULL | LOC_TES_SCD | 1 | 5038 | 39126 (4)| 32M|00:00:12.37 | 136K| 2253 | 0 | | | | |
|* 8 | HASH JOIN | | 1 | 131K| 252K (2)| 3634K|00:01:41.72 | 1321K| 602K| 17355 | 1115K| 1115K| 1/0/0| |
| 9 | TABLE ACCESS FULL | TES | 1 | 3042 | 31 (0)| 3042 |00:00:00.01 | 110 | 0 | 0 | | | | |
|* 10 | HASH JOIN OUTER | | 1 | 131K| 252K (2)| 3634K|00:01:39.05 | 1321K| 602K| 17355 | 589M| 17M| 1/0/0| 146K|
|* 11 | HASH JOIN OUTER | | 1 | 131K| 238K (2)| 3634K|00:01:26.30 | 1284K| 584K| 0 | 481M| 17M| 1/0/0| |
|* 12 | HASH JOIN | | 1 | 131K| 79549 (2)| 3546K|00:00:30.13 | 699K| 0 | 0 | 59M| 4993K| 1/0/0| |
|* 13 | TABLE ACCESS FULL | LOC | 1 | 119K| 40720 (3)| 573K|00:00:01.64 | 148K| 0 | 0 | | | | |
| 14 | TABLE ACCESS BY INDEX ROWID| LOC_TES | 1 | 1898K| 35628 (1)| 42M|00:00:16.96 | 550K| 0 | 0 | | | | |
|* 15 | INDEX RANGE SCAN | LOC_TES_IX_02 | 1 | 1898K| 6399 (1)| 42M|00:00:06.06 | 76707 | 0 | 0 | | | | |
|* 16 | TABLE ACCESS FULL | LOC_XF | 1 | 457K| 158K (2)| 32M|00:00:23.99 | 585K| 584K| 0 | | | | |
| 17 | TABLE ACCESS FULL | ORH | 1 | 2976K| 10107 (1)| 3031K|00:00:00.97 | 37402 | 0 | 0 | | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(TRUNC(SYSDATE@!)+65-1>=TRUNC(SYSDATE@!)-1)
4 - access("OCCTS"."SCD_ID"="FCS"."SCD_ID")
6 - access("OCCT"."LOC_TES_ID"="OCCTS"."LOC_TES_ID")
7 - filter("OCCTS"."STATUS"='A')
8 - access("T"."TES_ID"="OCCT"."TES_ID")
10 - access("ORR"."REC_NO"="ORH"."REC_NO")
11 - access("OCCT"."LOC_ID"="ORR"."LOC_ID" AND "OCCT"."LOC_TES_ID"="ORR"."LOC_TES_ID")
12 - access("OCC"."LOC_ID"="OCCT"."LOC_ID")
13 - filter(("OCC"."STATUS"='A' AND "OCC"."SENT"='N' AND "OCC"."OR_TYP"='P' AND "OCC"."OR_DR_DT">=TRUNC(SYSDATE@!)-1 AND
"OCC"."OR_DR_DT"<=TRUNC(SYSDATE@!)+65-1))
15 - access("OCCT"."STATUS"='A')
16 - filter(UPPER("ACTIVE_YN")='Y')
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
This doesn't look like a good plan. Looks like it requires lot of temp space for sorting etc. Should I go ahead with the loop-by-loop processing?
Please advise.
Thanks and Regards,
-Ranit
(on Oracle 11.2.0.4.0)