Query tuning with large data set

[bhatia333]

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.

[hrishy]

Hi

Please use code tags # symbol in the editors when posting sql's and plan info.

Please also post the predicate information from the explain plan

thank you

regards
Hrishy

[Bore]

Hi,
correct me if I am wrong BUT
The first plan shows that the select statement returns 225K rows
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 225K| 26M| | 2357K|
The second one returns only 1 row
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 122 | 824K|

About your question, you cannot make a low cardinality column selective. You should use another column to apply a stronger predicate on it

Cheers

[hrishy]

Hi

How much time does the query take to execute
What happens if you rewrite using hints like

SELECT /*+ parallel(soh 4) parallel (sol 4) */
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 )

regards
Hrishy

[slimdave]

Some of your Nvl() functions are redundant.

nvl(sol.schedule_status_code , 'N' ) = 'Y'

... is the same as ...

sol.schedule_status_code = 'Y'