Query Tuning

xml requests going to the golden weblogic servlet aren't reaching the crossworlds servers- packets are coming out of the servlet however was stick on waiting for a response from crossworlds.

Application team was worried as query which was suppose to take 2-3 minutes was taking more than 30 mins to get executed.

Last analyzed is more than 3 months old

SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from DBA_TABLES WHERE
TABLE_NAME='GD_INVOICE_HEADER';
OWNER TABLE_NAME LAST_ANAL------------------------------ ------------------------------ ---------GDCWODS GD_INVOICE_HEADER 12-JUL-09

SQL> select count(*) from GDCWODS.GD_INVOICE_DETAIL;
COUNT(*)
----------
1605055

SQL> select count(*) from GDCWODS.GD_INVOICE_HEADER;
COUNT(*)
----------
250578

Create Explain Plan Table:
SQL> @?/rdbms/admin/utlxplan.sql

Explain Plan for the query:
SQL> EXPLAIN PLAN SET STATEMENT_ID='GD_ISSUE' FOR
select * from GDCWODS.GD_INVOICE_HEADER IH where SYSTEM_ID='NA' AND BILL_TO_PARTY='500818'
AND EXISTS (SELECT MAT_NUMBER FROM GDCWODS.GD_INVOICE_DETAIL WHERE DOCNUM=IH.DOCNUM AND
MAT_NUMBER ='FA10003') ;

Explained.

SQL> @?/rdbms/admin/utlxpls GD_ISSUE
Plan Table
--------------------------------------------------------------------------------
Operation Name Rows Bytes Cost Pstart Pstop
--------------------------------------------------------------------------------
SELECT STATEMENT 47 20K 1632
FILTER
TABLE ACCESS FULL GD_INVOIC 47 20K 1632
TABLE ACCESS FULL GD_INVOIC 1 20 1769
--------------------------------------------------------------------------------

Columns which are Indexed earlier:

SQL> select INDEX_NAME, TABLE_NAME, COLUMN_NAME from dba_ind_columns where

TABLE_NAME='GD_INVOICE_HEADER' and TABLE_OWNER='GDCWODS';

INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
-------------------------------------------------------------------------------------------

---------
GD_INV_HDR_IDX1 GD_INVOICE_HEADER
SYSTEM_ID

GD_INV_HDR_IDX1 GD_INVOICE_HEADER
DOCNUM


SQL> select INDEX_NAME, TABLE_NAME, COLUMN_NAME from dba_ind_columns where

TABLE_NAME='GD_INVOICE_DETAIL' and TABLE_OWNER='GDCWODS';

INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
-------------------------------------------------------------------------------------------

---------
GD_INV_DET_IDX1 GD_INVOICE_DETAIL
SYSTEM_ID

GD_INV_DET_IDX1 GD_INVOICE_DETAIL
DOCNUM

GD_INV_DET_IDX1 GD_INVOICE_DETAIL
SEQNUM

EXPLAIN 2:( no change is cost)
SQL> EXPLAIN PLAN SET STATEMENT_ID='GD_ISSUE1' FOR
select * from GDCWODS.GD_INVOICE_HEADER IH
where SYSTEM_ID='NA' AND BILL_TO_PARTY='500818'
AND EXISTS (SELECT 1 FROM GDCWODS.GD_INVOICE_DETAIL WHERE DOCNUM=IH.DOCNUM AND MAT_NUMBER

='FA10003') ; 2 3 4

Explained.

SQL> @?/rdbms/admin/utlxpls GD_ISSUE1

Plan Table
--------------------------------------------------------------------------------
Operation Name Rows Bytes Cost Pstart Pstop
--------------------------------------------------------------------------------
SELECT STATEMENT 47 20K 1632
FILTER
TABLE ACCESS FULL GD_INVOIC 47 20K 1632
TABLE ACCESS FULL GD_INVOIC 1 20 1769
--------------------------------------------------------------------------------

7 rows selected.

EXPLAIN 3 using Hint: (cost got increased)
/// USING HINT ///
SQL> EXPLAIN PLAN SET STATEMENT_ID='GD_ISSUE2' FOR
2 select * from GDCWODS.GD_INVOICE_HEADER IH where SYSTEM_ID='NA' AND

BILL_TO_PARTY='500818' AND EXISTS (SELECT /*+ INDEX(GD_INVOICE_DETAIL GD_INV_DET_IDX1) */

MAT_NUMBER FROM GDCWODS.GD_INVOICE_DETAIL WHERE DOCNUM=IH.DOCNUM AND MAT_NUMBER ='FA10003')

;

Explained.

SQL> @?/rdbms/admin/utlxpls GD_ISSUE2

Plan Table
--------------------------------------------------------------------------------
Operation Name Rows Bytes Cost Pstart Pstop
--------------------------------------------------------------------------------
SELECT STATEMENT 47 20K 1632
FILTER
TABLE ACCESS FULL GD_INVOIC 47 20K 1632
TABLE ACCESS BY INDEX ROGD_INVOIC 1 20 5262
INDEX FULL SCAN GD_INV_DE 1 5261
--------------------------------------------------------------------------------

8 rows selected.

Created Index on MAT_NUMBER:
SQL> create index GD_INV_DET_MAT_NUM_IDX1 on GDCWODS.GD_INVOICE_DETAIL(MAT_NUMBER);

Index created.

EXPLAIN PLAN 4 : (after indexing cost reduced drastically)
SQL> EXPLAIN PLAN SET STATEMENT_ID='GD_ISSUE3' FOR
select * from GDCWODS.GD_INVOICE_HEADER IH where SYSTEM_ID='NA' AND BILL_TO_PARTY='500818'

AND EXISTS (SELECT MAT_NUMBER FROM GDCWODS.GD_INVOICE_DETAIL WHERE DOCNUM=IH.DOCNUM AND

MAT_NUMBER ='FA10003') ;
2
Explained.

SQL> @?/rdbms/admin/utlxpls GD_ISSUE3

Plan Table
--------------------------------------------------------------------------------
Operation Name Rows Bytes Cost Pstart Pstop
--------------------------------------------------------------------------------
SELECT STATEMENT 47 20K 1632
FILTER
TABLE ACCESS FULL GD_INVOIC 47 20K 1632
TABLE ACCESS BY INDEX ROGD_INVOIC 1 20 2
INDEX RANGE SCAN GD_INV_DE 1 1
--------------------------------------------------------------------------------

8 rows selected.

Note: For 16lakhs records will take at least 5-6hrs and 25thousand record will take 1-2hrs depeding on cpu, memory, colums, etc so analyze will be taken in off peak hours