Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle enterprise linux download

Poorna PrakashJan 11 2010 — edited Jan 11 2010
Hi pl tell me where can i download oracle enterprise linux also oracle for linux
This post has been answered by Hussein Sawwan-Oracle on Jan 11 2010
Jump to Answer

Comments

Karthick2003
[When query takes too long|http://forums.oracle.com/forums/thread.jspa?messageID=1812597]
Sreekanth Munagala
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.
Randolf Geist
user636482 wrote:
Hi ,

I have written 2 queries as follows.But these queries are taking more time.

Please optimize these queries.
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/
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 8 2010
Added on Jan 11 2010
5 comments
2,653 views