-
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.
-
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.
-
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.
-
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
-
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)
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|