Home » RDBMS Server » Performance Tuning » Execution plan misleads me (merged cross-posts)
Execution plan misleads me (merged cross-posts) [message #137450] Thu, 15 September 2005 05:13 Go to next message
randres
Messages: 3
Registered: September 2005
Junior Member
I have two very similar queries. Performed an explain plan the costs are 37 and 10 respectively. The first one lasts 7 minutes and the second one 24 minutes. Does anybody know what can be the cause for this?
Oops, sorry, I choose the wrong forum. How do I delete this?

[Updated on: Thu, 15 September 2005 05:18]

Report message to a moderator

Execution plan misleads me [message #137453 is a reply to message #137450] Thu, 15 September 2005 05:23 Go to previous messageGo to next message
randres
Messages: 3
Registered: September 2005
Junior Member
I have two very similar queries. Performed an explain plan, the first one costs 37 and the second one 10, but its execution times are 7 minutes and 24 minutes respectively. Does anybody know the reason for this?

The second query is the first query minus a couple of tables that I remove in order to obtain a better excution plan.

I attach explain plans:

SELECT STATEMENT Cost = 37
SORT GROUP BY
NESTED LOOPS
HASH JOIN
NESTED LOOPS
HASH JOIN
TABLE ACCESS BY INDEX ROWID DWH_AP_FICHA_VTA_BRICK
NESTED LOOPS
TABLE ACCESS FULL DWH_DIM_AP_FECHAS
INDEX RANGE SCAN PK_DWH_AP_FICHA_VTA_BRICK
INDEX FAST FULL SCAN PK_DWH_REL_POSICION_PRODUCTO
INDEX UNIQUE SCAN PK_DWH_REL_POSICION_BRICK
TABLE ACCESS FULL DWH_DIM_AP_RED_COMERCIAL
INDEX UNIQUE SCAN PK_DWH_DIM_AP_RED_COMERCIAL

---

SELECT STATEMENT Cost = 10
SORT GROUP BY
NESTED LOOPS
NESTED LOOPS
MERGE JOIN CARTESIAN
TABLE ACCESS FULL DWH_DIM_AP_RED_COMERCIAL
BUFFER SORT
TABLE ACCESS FULL DWH_DIM_AP_FECHAS
TABLE ACCESS BY INDEX ROWID DWH_AP_FICHA_VTA_BRICK
INDEX RANGE SCAN PK_DWH_AP_FICHA_VTA_BRICK
INDEX UNIQUE SCAN PK_DWH_REL_POSICION_BRICK

The biggest table is DWH_AP_FICHA_VTA_BRICK (2 million rows), followed by DWH_REL_POSICION_BRICK (only 16.000).
Same tables, same database, indexes and statistics (performed yesterday).

[Updated on: Thu, 15 September 2005 05:56]

Report message to a moderator

Re: Execution plan misleads me [message #137458 is a reply to message #137453] Thu, 15 September 2005 05:31 Go to previous messageGo to next message
Maaher
Messages: 7064
Registered: December 2001
Senior Member
Different tables? Different table size? Different databases? Indexes? Statistics? ...

A little bit more info would be useful here.

MHE
Re: Execution plan misleads me [message #137460 is a reply to message #137453] Thu, 15 September 2005 05:32 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
1. Cost is a purely arbitrary figure assigned by Oracle and cannot be used by you to compare XPlans

2. Do you think that maybe it would be helpful for people trying to answer you if you were to include the Xplans in you post?

Jim
Re: Execution plan misleads me [message #137489 is a reply to message #137450] Thu, 15 September 2005 06:41 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Why would this be the wrong forum? It is the perfect place.

But cost is not an absolute value to measure performance. Only elapsed time can do that (along with counts of resources used along the way). CBO cost is just a relative value for a given parse in a given session with a given set of "environment" parameters factored in.

How about you post us the two queries along with your table structure so that we can take a look? And did you read the performance tuning sticky?
Re: Execution plan misleads me [message #137516 is a reply to message #137453] Thu, 15 September 2005 07:31 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
This is a duplicate post of the one in performance tuning. Please check my answer there and provide additional information, such as the queries being compared, the results of each (are we comparing apples to oranges), and the physical table and index structures. Also the version, how you gathered stats, your optimizer mode, and well...that would be a start.
Re: Execution plan misleads me [message #137602 is a reply to message #137489] Thu, 15 September 2005 11:37 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
smartin wrote on Thu, 15 September 2005 04:41

Why would this be the wrong forum? It is the perfect place.


It was moved from the suggestions and feedback forum where it did not belong, to the performance tuning forum, where it belongs. You didn't see it until after the move, so you had no way of knowing where it was originally posted, unless you happened to see the link from the other forum.
Re: Execution plan misleads me [message #137609 is a reply to message #137450] Thu, 15 September 2005 12:15 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Gotcha...makes sense now.

I did see another post of this question in one of the sql forums.
Re: Execution plan misleads me [message #137617 is a reply to message #137609] Thu, 15 September 2005 13:35 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
I just stumbled upon the one in the sql forum and merged that here as well. So, all the pieces should be together in the right place now.

Re: Execution plan misleads me (merged cross-posts) [message #137690 is a reply to message #137450] Fri, 16 September 2005 02:28 Go to previous message
randres
Messages: 3
Registered: September 2005
Junior Member
Well, according to what some experts told me, explain plan costs are not the rigth way to compare execution plans. Rather than that is better to compare the plan itself (obviously its more difficult but works). Finally I changed the optimizer hint and I obtained better response times for both queries. Thanks to everybody!
Previous Topic: how to find out if a table has dirty blocks in buffers
Next Topic: Difference in retrieving number of rows between two queries
Goto Forum:
  


Current Time: Tue Dec 06 21:58:53 CST 2022