Sql Statement Tuning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Sql Statement Tuning

  1. #1
    Join Date
    Jun 2005
    Posts
    20

    Sql Statement Tuning

    explain plan for
    SELECT /*+ first_rows */
    i.ACTUAL_PURCHASE_PRICE,
    i.COMM_ID,
    cd.CONTRACT_OID LAST_CONTRACT_OID,
    c.CONTRACT_ID LAST_CONTRACT_ID,
    cd.EFFECTIVE_DATE,
    cd.START_DATE,
    i.LIST_PRICE,
    i.LOCATION_OID,
    i.ORDER_NUMBER,
    i.QTY,
    i.SERIAL_NUMBER,
    i.SERIAL_NUMBER_LK,
    i.SHIP_DATE,
    i.SO_LINE_NUM,
    i.OID,
    i.VERSION,
    i.PO_NUMBER,
    p.PRODUCT_ID,
    p.PRODUCT_ID_LK,
    p.PRODUCT_NAME,
    P.PRODUCT_NUMBER,
    P.STATUS PRODUCT_STATUS,
    cd.COVERAGE_TYPE_OID,
    cd.SERV_PART_NUMBER,
    c.BUYER_COMPANY_OID,
    ct.COVERAGE_NAME ,
    L.SELLER_COM_OID,
    L.COMPANY_ID,
    L.COMPANY_STATUS,
    L.STATE_NAME,
    L.STATE_CODE,
    (
    select quote_id
    from SAM_quote q
    where q.creation_date =
    (
    select max(q.creation_date)
    from SAM_quote q , SAM_quote_detail qd
    where q.OID = qd.quote_oid
    and i.oid = qd.INS_PRODUCT_OID
    )
    and rownum < 2
    ) latest_quote_id ,
    (
    select oid from SAM_quote q
    where q.creation_date =
    (
    select max(q.creation_date)
    from SAM_quote q , SAM_quote_detail qd
    where q.OID = qd.quote_oid
    and i.oid = qd.INS_PRODUCT_OID
    )
    and rownum < 2
    ) latest_quote_oid ,
    (
    select opportunity_id from SAM_opportunity o
    where o.created_time =
    (
    select max(o.created_time)
    from SAM_opportunity o , SAM_opportunity_detail Od
    where O.OID = Od.opportunity_oid
    and i.oid = Od.INSTALLED_PRODUCT_OID
    )
    and rownum < 2
    ) latest_opportunity_id ,
    (
    select oid from SAM_opportunity o
    where o.created_time =
    (
    select max(o.created_time)
    from SAM_opportunity o , SAM_opportunity_detail Od
    where O.OID = Od.opportunity_oid
    and i.oid = Od.INSTALLED_PRODUCT_OID
    )
    and rownum < 2
    ) latest_opportunity_oid
    FROM INV_PRODUCT i,
    CON_DETAIL cd,
    CON c,
    PRODUCT p,
    LOC l,
    COV_TYPE ct
    WHERE i.last_contract_detail_oid = cd.oid(+) AND
    NVL(cd.contract_oid,-99999) = c.oid(+) AND
    L.oid = i.location_oid AND
    p.oid = i.product_oid AND
    cd.COVERAGE_TYPE_OID = ct.oid(+)
    and i.serial_number_lk like '%'


    1 select operation, options, object_name
    2 from plan_table
    3* connect by prior id=parent_id and prior statement_id = statement_id
    SQL>
    SQL> /

    OPERATION OPTIONS OBJECT_NAME
    ------------------------------ ------------------------------ ------------------------------
    SELECT STATEMENT
    COUNT STOPKEY
    NESTED LOOPS OUTER
    NESTED LOOPS
    NESTED LOOPS OUTER
    NESTED LOOPS OUTER
    NESTED LOOPS
    NESTED LOOPS
    NESTED LOOPS
    NESTED LOOPS OUTER
    NESTED LOOPS
    TABLE ACCESS FULL SAM_COUNTRY
    TABLE ACCESS BY INDEX ROWID SAM_LOCATION
    INDEX RANGE SCAN IDX_LOCATION_10
    TABLE ACCESS BY INDEX ROWID SAM_STATE
    INDEX UNIQUE SCAN PK_STATE
    TABLE ACCESS BY INDEX ROWID COMPANY
    INDEX UNIQUE SCAN PK_COMPANY
    TABLE ACCESS BY INDEX ROWID INV_PRODUCT
    INDEX RANGE SCAN IDX_INSTALLED_PRODUCT_06
    TABLE ACCESS BY INDEX ROWID SAM_PRODUCT
    INDEX UNIQUE SCAN PK_PRODUCT
    TABLE ACCESS BY INDEX ROWID CON_DETAIL
    INDEX UNIQUE SCAN PK_CONTRACT_DETAIL
    TABLE ACCESS BY INDEX ROWID COV_TYPE
    INDEX UNIQUE SCAN PK_COVERAGE_TYPE
    TABLE ACCESS BY INDEX ROWID SAM_COMPANY
    INDEX UNIQUE SCAN PK_COMPANY
    TABLE ACCESS BY INDEX ROWID CON
    INDEX UNIQUE SCAN PK_CONTRACT


    HOW TO TUNE THIS STATEMENT.
    HOW THESE QUERY CAN BE REWRITTEN
    I ALSO ATTACHED SAME FOR CLEAR VISIBILTY.
    Attached Files Attached Files

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Code:
    (
                select quote_id
                from SAM_quote q
                where q.creation_date =
                    (
                    select max(q.creation_date)
                    from SAM_quote q , SAM_quote_detail qd
                    where q.OID = qd.quote_oid
                    and i.oid = qd.INS_PRODUCT_OID
                    )
                and rownum < 2
                ) latest_quote_id ,
    I think you do not need subquery.
    Try this:
    Code:
    select max(quote_id)
    from   SAM_quote q , SAM_quote_detail qd
    where q.OID = qd.quote_oid
    and    i.oid = qd.INS_PRODUCT_OID

    Tamil
    Last edited by tamilselvan; 09-22-2005 at 12:22 PM.

  3. #3
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    The /*+first_rows */ hint will tend to favour indexed paths rather than hash or merge join. If it really is you wish to get the first rows as quickly as possible, then it should help. However, if you want total throughput on the query, then you might be better removing it.

    The second problem is all your inline selects. These will be executed row-by-row, hence all the nested loops in the plan. Although the joins are indexed, having four futher index lookups for each row of the main query will slow things down, making the overall throughput of the query quite bad.

    I would try to re-write it with fewer inline selects. I think the most efficient way to do this would be to make use of an OLAP function such as ROW_NUMBER().

    Code:
    SELECT
    i.ACTUAL_PURCHASE_PRICE,
    i.COMM_ID,
    cd.CONTRACT_OID LAST_CONTRACT_OID,
    c.CONTRACT_ID LAST_CONTRACT_ID,
    cd.EFFECTIVE_DATE,
    cd.START_DATE,
    i.LIST_PRICE,
    i.LOCATION_OID,
    i.ORDER_NUMBER,
    i.QTY,
    i.SERIAL_NUMBER,
    i.SERIAL_NUMBER_LK,
    i.SHIP_DATE,
    i.SO_LINE_NUM,
    i.OID,
    i.VERSION,
    i.PO_NUMBER,
    p.PRODUCT_ID,
    p.PRODUCT_ID_LK,
    p.PRODUCT_NAME,
    P.PRODUCT_NUMBER,
    P.STATUS PRODUCT_STATUS,
    cd.COVERAGE_TYPE_OID,
    cd.SERV_PART_NUMBER,
    c.BUYER_COMPANY_OID,
    ct.COVERAGE_NAME ,
    L.SELLER_COM_OID,
    L.COMPANY_ID,
    L.COMPANY_STATUS,
    L.STATE_NAME,
    L.STATE_CODE,
    LATEST_QUOTE_PROD.QUOTE_iD,
    LATEST_QUOTE_PROD.OID,
    LATEST_SAM_PROD.OPPORTUNITY_ID,
    LATEST_SAM_PROD.OID
    FROM INV_PRODUCT i,
    CON_DETAIL cd,
    CON c,
    PRODUCT p,
    LOC l,
    COV_TYPE ct,
    (select quote_id, oid, ins_product_id
    from
    (select
    q.quote_id, 
    q.oid, 
    q.ins_product_id,
    rownumber() over (partition by qd.ins_product_oid order by q.creation_date desc) as quote_prod_rank
    from
    sam_quote q,
    sam_quote_detail qd
    where q.OID = qd.quote_oid) x
    where x.quote_prod_rank = 1) latest_quote_prod,
    (select opportunity_id, oid, INSTALLED_PRODUCT_OID
    from
    (select 
    o.opportunity_id, o.oid, od.INSTALLED_PRODUCT_OID,
    rownumber() over (partition by od.INSTALLED_PRODUCT_OID order by od.created_time desc) as sam_prod_rank
    from SAM_opportunity o , SAM_opportunity_detail Od
    where O.OID = Od.opportunity_oid) x
    where sam_prod_rank = 1) latest_sam_prod
    WHERE i.last_contract_detail_oid = cd.oid(+) AND
    NVL(cd.contract_oid,-99999) = c.oid(+) AND
    L.oid = i.location_oid AND
    p.oid = i.product_oid AND
    cd.COVERAGE_TYPE_OID = ct.oid(+)
    and i.serial_number_lk like '%'
    and latest_quote_prod.ins_product_oid (+) = i.oid
    and latest_sam_prod.INSTALLED_PRODUCT_OID (+) = i.oid

  4. #4
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349

    R U Using View

    Are you using any view as i see
    "TABLE ACCESS FULL SAM_COUNTRY"

    if so and SAM_Country has large amount of data you need to concentrate tuning this view.

    -Malay
    http://www.perf-engg.com
    A performance engineering forum

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