We have a production database used mostly for OLTP and a few batch jobs and then a replicated copy of the prod database that ad-hoc queries run again.
One of the query developers has come to me with a problem.
Her query runs in about a minute on the replicated database and runs for 30 minutes plus on production.
The query looks like this: (obscuring real table names etc)
I would expect this code to go and parse PO_Table first (keyed on dstrct_code and po_no) to get the subset of PO's to work upon and then join to invoices, PO_items and the xref table that joins PO to Purchase Requisition to get the other details.
According to the Explain on the replicated database, it appears to do just that.
In production, the first two lines of the explain output look like this:
Now, these are views in both instances and PO_NO on the invoice table is defined as substring(1,6) of contract no. But, it looks like it's scanning the invoice table for the match on po_no - which would in effect be a full table scan of the table, which would explain why I'm having run time issues with it.
Now, query tuning is not my forte, but this one has be baffled.
I can't use an index hint, since this query is generated by a client side query tool where you point and click and ir generates a query that is run.
Tables in both databases are analyzed nightly via dbms_stats and both show 26-OCT-04 as the last_analyzed date in both user_tables and user_indexes.
Would anyone have a clue as to why these two are running differently and more to the point, why the prod based query seems to be trying to get data from the index_table instead of the PO_table.
Any suggestions appreciated.
Specifics:
Production: Oracle 9.2.0.5 HPUX 11.11
Replicated: Oracle 9.2.0.5 Windows 2000 Server
Thanks
Steve
stephen.wales@riotinto.com
One of the query developers has come to me with a problem.
Her query runs in about a minute on the replicated database and runs for 30 minutes plus on production.
The query looks like this: (obscuring real table names etc)
Code:
SELECT
a.INV_NO,
a.PMT_STATUS,
a.INV_DATE,
b.DSTRCT_CODE,
b.COMPLETED_DATE,
b.PO_NO,
c.PO_ITEM_NO,
d.PREQ_NO,
d.PREQ_ITEM
FROM
Invoice_table a,
Invoice_PO_Xref d,
PO_Item_Table c,
Po_table b
WHERE
b.PO_NO = d.PO_NO AND
b.PO_NO = a.PO_NO AND
c.PO_ITEM_NO = d.PO_ITEM_NO AND
c.PO_NO = d.PO_NO AND
b.DSTRCT_CODE = '<district>' AND
b.PO_NO LIKE 'K550%'
I would expect this code to go and parse PO_Table first (keyed on dstrct_code and po_no) to get the subset of PO's to work upon and then join to invoices, PO_items and the xref table that joins PO to Purchase Requisition to get the other details.
According to the Explain on the replicated database, it appears to do just that.
In production, the first two lines of the explain output look like this:
Code:
OPERATION OPTIONS OBJECT_NAME OBJECT_TYP OPTIMIZER SEARCH_COLUMNS ID PARENT_ID
------------------------------ -------------------- --------------- ---------- ---------- -------------- ---- ---------
COST CARDINALITY BYTES CPU_COST IO_COST
---------- ----------- ---------- ---------- ----------
ACCESS_PREDICATES
-------------------------------------------------------------------------------------------------------------------------
FILTER_PREDICATES
-------------------------------------------------------------------------------------------------------------------------
SELECT STATEMENT CHOOSE 0
11398 1 173 11398
TABLE ACCESS BY INDEX ROWID MSF260 ANALYZED 1 0
11388 1 37 11388
"A"."PO_NO"=RTRIM(SUBSTR(RPAD("A"."CONTRACT_NO",8),1,6)) AND RTRIM(SUBSTR(RPAD("A"."CONTRACT_NO",8),1,6)) LIKE 'K550%'
Now, these are views in both instances and PO_NO on the invoice table is defined as substring(1,6) of contract no. But, it looks like it's scanning the invoice table for the match on po_no - which would in effect be a full table scan of the table, which would explain why I'm having run time issues with it.
Now, query tuning is not my forte, but this one has be baffled.
I can't use an index hint, since this query is generated by a client side query tool where you point and click and ir generates a query that is run.
Tables in both databases are analyzed nightly via dbms_stats and both show 26-OCT-04 as the last_analyzed date in both user_tables and user_indexes.
Would anyone have a clue as to why these two are running differently and more to the point, why the prod based query seems to be trying to get data from the index_table instead of the PO_table.
Any suggestions appreciated.
Specifics:
Production: Oracle 9.2.0.5 HPUX 11.11
Replicated: Oracle 9.2.0.5 Windows 2000 Server
Thanks
Steve
stephen.wales@riotinto.com