| 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 #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
|
|
|
|
| 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?
|
|
|
|
| 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.
|
|
|
|
|
|
|
|
|
|
| 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!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
