Help - Search - Members - Calendar
Full Version: Improvement of Select query
Oracle DBA Forums > Oracle > Oracle Forum
mallik
Hi Gurus,

Please help me in improving the query performance.

I have a query which takes child ids in where clause with execution time of 4 seconds.
But if i replace child Ids with parent Id , the same query is taking 15 minutes :-(.

I Am here with attaching the execution plans of both queries
with child IDs in where clause (children_plan.txt) , with parent ID in where clause ( parent_plan.txt).

many thanks in advance.

Mallik
Steve
I would need a real explain plan to even begin to analyze this...producing it from v$sql_plan generally isn't enough info. Can you do this:

set linesize 150
spool output.txt
set autotrace traceonly
...run your first query
...run your second query
spool off

It's a start.
mallik
Hi Steve,

Thank you for your reply.
Please find the output.txt file as attachment.

Mallik
Steve
Based on the statistics at the bottom, neither query looks too awful.

I'd probably recommend weeding out your full table scans. Cartesians frequently show up when doing merge scans, but you may want to try other join methods or verify your where clause criteria (there are a lot of cartesians!). Based on the explain plans, it looks like you're using several inefficient methods for the small amount of data: sort merges, and-equals, etc.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.