Query tuning with large data set
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Query tuning with large data set

  1. #1
    Join Date
    Aug 2002
    Posts
    56

    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.
    When in doubt ...go to the basics!!

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    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

  3. #3
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

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

    Code:
    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

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Some of your Nvl() functions are redundant.

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

    ... is the same as ...

    sol.schedule_status_code = 'Y'
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width