SQL & PL/SQL

[When query takes too long|http://forums.oracle.com/forums/thread.jspa?messageID=1812597]

Hi,
the first query can be modified as follows:

select count(OBJID) as counter
from oaminspectorshipments
where ridkey = 'psi08'
AND OBJID IN ( select PRESENTATION_ID
from INSPECTOR_SHIPMENTS_PROPOSALS a
,oamInspectorPOs b
where a.proposal_id=b.agreement_id
and lower(b.purchase_order) = lower('tony-filter03') )

you can do the same in the second query also.

Interesting approach: "Please optimize the queries".

1. You could think about re-writing your queries using joins instead of using IN and nested subqueries (untested code):

select count(OBJID) as counter
from oaminspectorshipments a
inner join INSPECTOR_SHIPMENTS_PROPOSALS b
on a.OBJID = b.PRESENTATION_ID
inner join oamInspectorPOs c
on b.PROPOSAL_ID = c.agreement_id
where a.ridkey = 'psi08' 
and lower(c.PURCHASEORDER) = lower('tony-filter03');

Note that this only works if you don't get multiple rows from INSPECTOR_SHIPMENTS_PROPOSALS with the same PRESENTATION_ID, otherwise you need to revert to the IN operator or e.g. use a SELECT DISTINCT in an inline view.

2. Do you have reasonable statistics gathered on the involved tables/indexes using DBMS_STATS?

3. Which version of Oracle are you using (4-digit number, e.g. 9.2.0.8)

4. Follow the link provided and generate an execution plan that you might want to post here. You can also run your statement with SQL trace enabled as described in the thread and post the "tkprof" output using the trace file generated.

Here's a detailed description how to generate a useful explain plan output:

Could you please post an properly formatted explain plan output using DBMS_XPLAN.DISPLAY including the "Predicate Information" section below the plan to provide more details regarding your statement. Please use the "code" tag (in curly brackets) before and after to enhance readability of the output provided:

In SQL*Plus:

SET LINESIZE 130

EXPLAIN PLAN FOR <your statement>;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Note that the package DBMS_XPLAN.DISPLAY is only available from 9i on.

In previous versions you could run the following in SQL*Plus (on the server) instead:

@?/rdbms/admin/utlxpls

A different approach in SQL*Plus:

SET AUTOTRACE ON EXPLAIN

<run your statement>;

will also show the execution plan.

In order to get a better understanding where your statement spends the time you might want to turn on SQL trace as described here:

[When your query takes too long|http://forums.oracle.com/forums/thread.jspa?threadID=501834]

and post the "tkprof" output here, too.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/