Help with troubleshooting a query

[sjwales]

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)

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:

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

 

[sjwales]

As an aside, each of the views in the above query is made up of, effectively:

select * from table a, security_view b
where a.dstrct_code = b.dstrct_code

which makes sure that each user can only select data from authorized districts.

Running the query against the base table runs in < 1 minute in production. Querying against the views makes it run excessively long.

stephen.wales@riotinto.com