Query tuning with large data set
Hi Gurus,
I have a query which I need to tune. Its running on a 32 CPU/64GB RAM HP-UX machine with 9i database for a Oracle E-Buss Suite application.
The query is as below:
SELECT 3 Type , soh . header_id , sol . line_id , soh . order_number , tt . name , sol . line_number
FROM apps.oe_order_lines_all sol , apps.oe_order_headers_all soh , apps.oe_transaction_types_tl tt , apps.mtl_system_items_vl mtlv
WHERE : P_ACTIONS = 'SCHED_NOT_PK'
AND ( soh.header_id = sol.header_id
AND nvl ( sol.schedule_status_code , 'N' ) = 'SCHEDULED'
AND nvl ( sol.shipping_interfaced_flag , 'N' ) = 'N'
AND mtlv.inventory_item_id = sol.inventory_item_id
AND mtlv.organization_id = sol.ship_from_org_id
AND tt.language = userenv ( 'LANG' )
AND tt.transaction_type_id = soh.order_type_id
AND nvl ( sol.open_flag , 'N' ) = 'Y'
AND sol.schedule_ship_date <= ( sysdate - : P_OVERDUE_DAYS ) )
and nvl ( soh.org_id , 0 ) = nvl ( : p_org_id , 0 )
and nvl ( sol.org_id , 0 ) = nvl ( : p_org_id , 0 )
The explain plan looks like this:
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 225K| 26M| | 2357K|
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | 225K| 26M| | 2357K|
| 3 | NESTED LOOPS | | 224K| 23M| | 2357K|
|* 4 | HASH JOIN | | 224K| 21M| | 2357K|
|* 5 | TABLE ACCESS FULL | OE_TRANSACTION_TYPES_TL | 137 | 3836 | | 2 |
|* 6 | HASH JOIN | | 224K| 15M| 13M| 2357K|
|* 7 | TABLE ACCESS FULL| OE_ORDER_LINES_ALL | 224K| 11M| | 2298K|
|* 8 | TABLE ACCESS FULL| OE_ORDER_HEADERS_ALL | 1666K| 33M| | 57671 |
|* 9 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 1 | 9 | | |
|* 10 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_TL_U1 | 1 | 12 | | |
-------------------------------------------------------------------------------------------
I created a function based index on soh.org_id on the OE_ORDER_HEADERS_ALL table. After index creation the plan looks like this
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 122 | 824K|
|* 1 | FILTER | | | | |
| 2 | NESTED LOOPS | | 1 | 122 | 824K|
| 3 | NESTED LOOPS | | 1 | 94 | 824K|
| 4 | NESTED LOOPS | | 1 | 82 | 824K|
| 5 | NESTED LOOPS | | 1 | 73 | 824K|
| 6 | TABLE ACCESS BY INDEX ROWID| OE_ORDER_HEADERS_ALL | 33322 | 683K| 57670 |
|* 7 | INDEX RANGE SCAN | OE_ORDER_HEADERS_N10 | 13329 | | 3267 |
|* 8 | TABLE ACCESS BY INDEX ROWID| OE_ORDER_LINES_ALL | 1 | 52 | 23 |
|* 9 | INDEX RANGE SCAN | OE_ORDER_LINES_N1 | 30 | | 2 |
|* 10 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 1 | 9 | |
|* 11 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_TL_U1 | 1 | 12 | |
| 12 | TABLE ACCESS BY INDEX ROWID | OE_TRANSACTION_TYPES_TL | 1 | 28 | 1 |
|* 13 | INDEX UNIQUE SCAN | OE_TRANSACTION_TYPES_TL_U1 | 1 | | |
------------------------------------------------------------------------------------------------
The problem here is that in this 3 million rows table there are two distinct org_id 's and 1 one them (which is mostly used in the queries) is used in 80% of the rows in the table.
I wanted to know what are options for tuning this query with such large data volumes. Purging and Archiving are not in currently being considered as a short term solution. I doubt if Partitioning will really help due to the low-cardinality of the data.
Please help.
