DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Sql Tunning ..

  1. #1
    Join Date
    Feb 2001
    Posts
    119

    Sql Tunning ..

    EXPLAIN PLAN FOR SELECT /*+ INDEX(RH XPK_CUSTOMER_HOMES) INDEX(CD IDX_CUSTOMER_DETAILS) */
    COUNT(CD.invoice_no) FROM CUSTOMER_DETAILS CD,CUSTOMER_INVOICES FI ,CUSTOMER_HOMES CH
    WHERE RH.CUSTOMER_ID = CD.CUSTOMER_ID
    AND CD.invoice_no = FI.invoice_no
    AND CH.invoice_no = CD.invoice_no
    AND FI.invoice_date BETWEEN to_date('01-feb-03') AND to_date('04-feb-03') ;

    Production
    SELECT STATEMENT Cost = 690412
    SORT AGGREGATE
    MERGE JOIN
    SORT JOIN
    NESTED LOOPS
    TABLE ACCESS BY INDEX ROWID CUSTOMER_INVOICES 2
    INDEX RANGE SCAN IDX_CUSTOMER_INVOICES NON-UNIQUE
    TABLE ACCESS BY INDEX ROWID CUSTOMER_DETAILS 1
    INDEX RANGE SCAN IDX_CUSTOMER_DETAILS NON-UNIQUE
    SORT JOIN
    TABLE ACCESS BY INDEX ROWID CUSTOMER_HOMES 3
    INDEX FULL SCAN XPK_CUSTOMER_HOMES UNIQUE



    Development

    SELECT STATEMENT Cost =
    SORT AGGREGATE
    NESTED LOOPS
    NESTED LOOPS
    TABLE ACCESS BY INDEX ROWID CUSTOMER_INVOICES 2
    INDEX RANGE SCAN IDX_CUSTOMER_INVOICES NON-UNIQUE
    TABLE ACCESS BY INDEX ROWID CUSTOMER_DETAILS 1
    INDEX RANGE SCAN IDX_CUSTOMER_DETAILS NON-UNIQUE
    TABLE ACCESS BY INDEX ROWID CUSTOMER_HOMES 3
    INDEX UNIQUE SCAN XPK_CUSTOMER_HOMES UNIQUE


    Production is very slow .
    All tables and Indexes are analyzed.
    all parameters are same in dev and production .
    Last edited by Subha; 02-05-2003 at 12:29 AM.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    is the data the same in production and development? Have you tried it without hints? Is the hardware the same? Run tkprof to see what is really going on.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  3. #3
    Join Date
    Feb 2001
    Posts
    119
    The data is the same in production and development.
    Have you tried it without hints?
    Yes .tried also (RULE/ORDERED/USE_NL)

    Is the hardware the same?
    Yes ...

    Run tkprof to see what is really going on.

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

    Is the number of records same in production and devlopment..

    when were the tables last analyzed in production and development..
    what about the table stats are they same in production and development..

    regards
    Hrishy

  5. #5
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304
    Production database is performing sort operations which is causing this delay.

    Please check XPK_CUSTOMER_HOMES index in both the database and reanalyze it, becuase it is doing full scan for this index in development. Make sure this index is identical on both databases.
    Agasimani
    OCP(10g/9i/8i/8)

  6. #6
    Join Date
    Feb 2001
    Posts
    119
    Why This Merge Join ???
    I get Good Performance in Nested Loops .
    How to avoid Merge Join ...

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