Home » RDBMS Server » Performance Tuning » Query not have much cost on Explain Plan but very very slow (oracle 10g)
Query not have much cost on Explain Plan but very very slow [message #639621] Sun, 12 July 2015 23:06 Go to next message
toom2525
Messages: 2
Registered: July 2015
Location: Thailand
Junior Member
please advise What do I wrong.

Query not have much cost on Explain Plan but very very slow.

I use index for "where" but I can not execute this query.

/forum/fa/12759/0/

Report message to a moderator

Re: Query not have much cost on Explain Plan but very very slow [message #639622 is a reply to message #639621] Sun, 12 July 2015 23:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

Report message to a moderator

Re: Query not have much cost on Explain Plan but very very slow [message #639623 is a reply to message #639622] Mon, 13 July 2015 00:03 Go to previous messageGo to next message
toom2525
Messages: 2
Registered: July 2015
Location: Thailand
Junior Member
thank you for your reply.

Wait a minute I will add more detail.

Report message to a moderator

Re: Query not have much cost on Explain Plan but very very slow [message #639627 is a reply to message #639623] Mon, 13 July 2015 01:33 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Your cardinalities look 'off'. Are your statistics up to date?

Report message to a moderator

Re: Query not have much cost on Explain Plan but very very slow [message #639628 is a reply to message #639621] Mon, 13 July 2015 02:07 Go to previous message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member
It is difficult to tune SQL that one has not seen. Also, please can you use EXPLAIN PLAN and DBMS_XPLAN.DISPLAY to generate the plan, rather than whatever tool you used before?

However, I do see a couple of suspicious constructs.

You are joining and aggregating wod_det, wo_mstr, and wr_route twice and then using union all to concatenate the results. Can't that be accomplished with one factored subquery?

You are aggregating twice: at the level mentioned above, and again at the top. Isn't once enough?

You have an outer join that does not appear to be preserving any rows. Is it actually necessary?

[Updated on: Mon, 13 July 2015 02:08]

Report message to a moderator

Previous Topic: Please provide help for tuning the sql Query
Next Topic: Change in he execution plan
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Feb 06 13:33:47 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.