OraFAQ Forum: Performance Tuning » Execution plan misleads me (merged cross-posts)

Home » RDBMS Server » Performance Tuning » Execution plan misleads me (merged cross-posts)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Execution plan misleads me (merged cross-posts) [message #137450]

Thu, 15 September 2005 05:13

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

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

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Different tables? Different table size? Different databases? Indexes? Statistics? ...

A little bit more info would be useful here.

MHE

Report message to a moderator

Re: Execution plan misleads me [message #137460 is a reply to message #137453]

Thu, 15 September 2005 05:32

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

Report message to a moderator

Re: Execution plan misleads me [message #137489 is a reply to message #137450]

Thu, 15 September 2005 06:41

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?

Report message to a moderator

Re: Execution plan misleads me [message #137516 is a reply to message #137453]

Thu, 15 September 2005 07:31

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.

Report message to a moderator

Re: Execution plan misleads me [message #137602 is a reply to message #137489]

Thu, 15 September 2005 11:37

Barbara Boehmer
Messages: 9105
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.

Report message to a moderator

Re: Execution plan misleads me [message #137609 is a reply to message #137450]

Thu, 15 September 2005 12:15

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.

Report message to a moderator

Re: Execution plan misleads me [message #137617 is a reply to message #137609]

Thu, 15 September 2005 13:35

Barbara Boehmer
Messages: 9105
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.

Report message to a moderator

Re: Execution plan misleads me (merged cross-posts) [message #137690 is a reply to message #137450]

Fri, 16 September 2005 02:28

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!

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 08:13:40 CDT 2025