I have a query like
SELECT DISTINCT PM.PARTY_CODE, PM.PARTY_NAME FROM PURCHASE_ORDER PO, PURCHASE_ORDER_ITEM POI, PARTY_MASTER PM
WHERE PO.PO_NO = POI.PO_NO AND PO.PO_DATE = POI.PO_DATE AND OPEN_PO='Y' AND PO.CCODE=:1 AND PO.FYCODE=:GLOBAL.MYFINY AND PM.PARTY_CODE=PO.SUPP_CODE AND PM.CCODE=PO.CCODE AND (PO.PO_NO,PO.PO_DATE,POI.ITEM_CODE,POI.QTY_ORD) NOT IN (SELECT DISTINCT PS.PO_NO,PS.PO_DATE,PD.ITEM_CODE,SUM(NVL(PD.QTY,0)) SCH_QTY FROM PURCHASE_ORDER_SCHEDULE PS, PURCHASE_SCHEDULE_DETAIL PD
WHERE PS.SCHEDULE_NO = PD.SCHEDULE_NO AND PS.SCHEDULE_DATE = PD.SCHEDULE_DATE AND PS.CCODE = :2 AND PS.FYCODE=:GLOBAL.MYFINY GROUP BY PS.PO_NO,PS.PO_DATE,PD.ITEM_CODE) UNION SELECT PM.PARTY_CODE, PM.PARTY_NAME FROM WORK_ORDER_MASTER W, WORK_ORDER_RET_ITEM_DETAIL WD, PARTY_MASTER PM
WHERE W.WORK_ORDER_NO = WD.WORK_ORDER_NO AND W.WORK_ORDER_DATE = WD.WORK_ORDER_dATE AND W.CCODE=:3 AND PM.PARTY_CODE=W.SUPPLIER_CODE AND PM.CCODE=W.CCODE AND (W.WORK_ORDER_NO,W.WORK_ORDER_DATE,WD.RET_ITEM_CODE,WD.RET_ITEM_QTY) NOT IN (SELECT DISTINCT PS.PO_NO,PS.PO_DATE,PD.ITEM_CODE,SUM(NVL(PD.QTY,0)) SCH_QTY FROM PURCHASE_ORDER_SCHEDULE PS, PURCHASE_SCHEDULE_DETAIL PD
WHERE PS.SCHEDULE_NO = PD.SCHEDULE_NO AND PS.SCHEDULE_DATE = PD.SCHEDULE_DATE AND PS.CCODE = :4 AND PS.FYCODE = :5 GROUP BY PS.PO_NO,PS.PO_DATE,PD.ITEM_CODE);
First i have tune it in 11g using sqltrpt.sql this gives me sql_profile_accept suggestion when i use it, its solves the issue and query is executed fast.
this is the execution plan in 11g after tunning.
1- Original
-----------
Plan hash value: 1571402389
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3029 | 402K| | 494 (3)| 00:00:06 |
| 1 | SORT UNIQUE | | 3029 | 402K| 456K| 423 (9)| 00:00:06 |
| 2 | UNION-ALL | | | | | | |
|* 3 | HASH JOIN | | 3028 | 402K| | 300 (2)| 00:00:04 |
|* 4 | TABLE ACCESS FULL | PARTY_MASTER | 410 | 11890 | | 5 (0)| 00:00:01 |
| 5 | MERGE JOIN ANTI NA | | 3028 | 316K| | 295 (2)| 00:00:04 |
| 6 | SORT JOIN | | 3028 | 186K| 472K| 140 (2)| 00:00:02 |
|* 7 | HASH JOIN | | 3028 | 186K| | 91 (2)| 00:00:02 |
|* 8 | TABLE ACCESS FULL | PURCHASE_ORDER | 1066 | 30914 | | 22 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | PURCHASE_ORDER_ITEM | 6056 | 201K| | 68 (0)| 00:00:01 |
|* 10 | SORT UNIQUE | | 4667 | 200K| 536K| 155 (2)| 00:00:02 |
| 11 | VIEW | VW_NSO_1 | 4667 | 200K| | 100 (2)| 00:00:02 |
| 12 | HASH GROUP BY | | 4667 | 319K| 400K| 100 (2)| 00:00:02 |
|* 13 | HASH JOIN | | 4667 | 319K| | 21 (5)| 00:00:01 |
|* 14 | TABLE ACCESS FULL | PURCHASE_ORDER_SCHEDULE | 1839 | 69882 | | 9 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | PURCHASE_SCHEDULE_DETAIL | 4678 | 146K| | 11 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | | | | | |
| 17 | NESTED LOOPS | | 1 | 98 | | 5 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 69 | | 4 (0)| 00:00:01 |
| 19 | TABLE ACCESS FULL | WORK_ORDER_RET_ITEM_DETAIL | 1 | 37 | | 3 (0)| 00:00:01 |
|* 20 | TABLE ACCESS BY INDEX ROWID| WORK_ORDER_MASTER | 1 | 32 | | 1 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | PK_WORK_ORDER_MASTER | 1 | | | 0 (0)| 00:00:01 |
|* 22 | FILTER | | | | | | |
| 23 | HASH GROUP BY | | 1 | 75 | | 22 (10)| 00:00:01 |
|* 24 | HASH JOIN | | 278 | 20850 | | 21 (5)| 00:00:01 |
|* 25 | TABLE ACCESS FULL | PURCHASE_ORDER_SCHEDULE | 119 | 5117 | | 9 (0)| 00:00:01 |
| 26 | TABLE ACCESS FULL | PURCHASE_SCHEDULE_DETAIL | 4678 | 146K| | 11 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | PK_PARTY_CODE | 1 | | | 0 (0)| 00:00:01 |
|* 28 | TABLE ACCESS BY INDEX ROWID | PARTY_MASTER | 1 | 29 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
and when i use manual sql tuning advisor its is not showing mwe any advice in 10g
So, How can i solve it in 10g
this is the execution plan in 10g
1- Original
-----------
Plan hash value: 1979320185
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 44728 (100)| |
| 1 | SORT UNIQUE | | 3906 | 392K| 904K| 44636 (10)| 00:08:56 |
| 2 | UNION-ALL | | | | | | |
|* 3 | FILTER | | | | | | |
|* 4 | HASH JOIN | | 3905 | 392K| | 62 (4)| 00:00:01 |
|* 5 | HASH JOIN | | 1376 | 89440 | | 22 (5)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | PARTY_MASTER | 400 | 12400 | | 4 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | PURCHASE_ORDER | 1692 | 57528 | | 17 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | PURCHASE_ORDER_ITEM | 6106 | 226K| | 39 (0)| 00:00:01 |
|* 9 | FILTER | | | | | | |
| 10 | SORT GROUP BY | | 1 | 79 | | 19 (6)| 00:00:01 |
|* 11 | HASH JOIN | | 1856 | 143K| | 18 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | PURCHASE_ORDER_SCHEDULE | 1856 | 79808 | | 8 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | PURCHASE_SCHEDULE_DETAIL | 4706 | 165K| | 10 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 1 | 100 | | 4 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 1 | 69 | | 3 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | WORK_ORDER_RET_ITEM_DETAIL | 1 | 37 | | 2 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID| WORK_ORDER_MASTER | 1 | 32 | | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PK_WORK_ORDER_MASTER | 1 | | | 0 (0)| |
|* 19 | FILTER | | | | | | |
| 20 | SORT GROUP BY | | 1 | 85 | | 19 (6)| 00:00:01 |
|* 21 | HASH JOIN | | 135 | 11475 | | 18 (0)| 00:00:01 |
|* 22 | TABLE ACCESS FULL | PURCHASE_ORDER_SCHEDULE | 135 | 6615 | | 8 (0)| 00:00:01 |
| 23 | TABLE ACCESS FULL | PURCHASE_SCHEDULE_DETAIL | 4706 | 165K| | 10 (0)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID | PARTY_MASTER | 1 | 31 | | 1 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | PK_PARTY_CODE | 1 | | | 0 (0)| |
----------------------------------------------------------------------------------------------------------------------