| EXPLAIN PLAN CHANGE [message #274535] |
Tue, 16 October 2007 06:17  |
arunprasad_bh
Messages: 32
Registered: June 2007
|
Member |
|
|
Hi I have a query that joins about 14 tables. The largest table is a partitioned table with total rows of about 6million.
The query is returning results in four and half min (oracle version 9.2.0.5)
We migrated to a higher version (9.2.0. . Now the plan is changed and the same query is taking about 30min.
The new machine is much faster, and of higher spec.
We are now running both the machines in parallel.
All the stats related information in both the environments is same. Still when we generate plan, they are different.
Have you ever faced such a problem? do you know what to look for?
Stats are same.
Partitions are same.
Number of records are same.
Both environments are running in Parallel.
I compared the databases using toad. Everything except db_block_checking are same.
Thanks
|
|
|
|
|
|
| Re: EXPLAIN PLAN CHANGE [message #274589 is a reply to message #274535] |
Tue, 16 October 2007 09:04  |
arunprasad_bh
Messages: 32
Registered: June 2007
|
Member |
|
|
We know that the plan itself is not correct. It is calculating the cost wrong and picking a wrong plan.
As I have the plan from the old version, I used order hint and forced the same plan, which is costing more than the one generated by new version. Yet the high cost plan is giving results in less than three min.
Point is why optimizer is calculating the cost of the plan that takes more time to execute as less than the one which performs better.
|
|
|
|
|
|
| Re: EXPLAIN PLAN CHANGE [message #274635 is a reply to message #274535] |
Tue, 16 October 2007 11:54 |
arunprasad_bh
Messages: 32
Registered: June 2007
|
Member |
|
|
Now I have trace files for both the environments. But strugguling to understand why optimizer calculated the cost and choose a wrong plan.
Michel the point is the plan is not correct. In new version the cost for bad plan is less than good one.
Does the trace gives any clue regrding why this is happening?
|
|
|
|
|
|
|
|
| Re: EXPLAIN PLAN CHANGE [message #274703 is a reply to message #274648] |
Tue, 16 October 2007 22:04 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
There are 87178291200 different possible join orders of a 14-table join. The CBO does not evaluate all of them. To my knowlege the choice of which plans to evaluate is somewhat chaotic; the slightest change in inputs can result in completely different subset of plans being evaluated.
The chances of the plan for such a SQL remaining stable after a reboot is suspect. The chances of stability between databases is remote indeed.
This will get worse still when you upgrade to 10g. Dynamic sampling means that the plan can be affected from run-to-run by the physical distribution of data in your table. ie. Same data + different storage = different plan.
The ONLY way to ensure plan stability - espacially between databases - is to use the ORDERED hint, plus USE_* hints to specify join methods, plus INDEX*/FULL hints to specify access methods. Alternatively, use OUTLINES (which do exactly the same thing).
Ross Leishman
|
|
|
|
| Re: EXPLAIN PLAN CHANGE [message #274749 is a reply to message #274703] |
Wed, 17 October 2007 01:20 |
 |
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Is stability the way?
Each version comes with new access paths and new improvements (and new bugs).
You want stability, use RBO and nested loops.
Otherwise, read the new features, follow the new guidelines, let the optimizer chooses its path and fix (hint) if it is not correct. Yes, this is painful but this the way for performances.
If performances is not your issue, go back to RBO.
By the way, hints (ORDERED, INDEX and so on) is not a guarantee of stability. Internal query transformations are applied BEFORE hints, so you could have some surprises with ORDERED hint for instance if Oracle internally changes the order of table in FROM clause.
Regards
Michel
|
|
|
|
| Re: EXPLAIN PLAN CHANGE [message #274802 is a reply to message #274749] |
Wed, 17 October 2007 03:16 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I wasn't advocating stability, just describing how you would go about achieving it.
Generally speaking, I agree with you - get good stats and let CBO do the rest.
But 14-table joins are a different story. 87178291200 join orders is just the start, then you have up to 4782969 different join-type combinations and 16384 access method combinations for a total of 6,831,653,917,205,672,755,200 different plans. Obviously a great many of these are silly plans that don't deserve evaluation, but it's terribly easy for a good plan to be missed.
With SQLs this big, the chances of an optimal plan NOT being amongst those evaluated is quite reasonable.
In these situations I would usually suggest a simple LEADING or ORDERED hint. In most cases the driving table is not a choice that is affected by version upgrades, and its enough to lead CBO to the optimal plan. However if you have a mission-critical application or a crippling support contract, then plan stability is a pretty good thing.
Ross Leishman
|
|
|
|
| Re: EXPLAIN PLAN CHANGE [message #274812 is a reply to message #274535] |
Wed, 17 October 2007 04:39 |
arunprasad_bh
Messages: 32
Registered: June 2007
|
Member |
|
|
As I told before, I am trying to get the optimizer get the plan that would take less time because these queries are fired from BO. If this is a query is used in a sql, I would have gone for a hint(which I already demonstrated).
The point here is why the optimizer is calculating the cost of the non-optimum plan to be less than the optimum one, and also differently between 9.2.0.5 and 9.2.0.8?
Michel, I have now got the 10053 trace files. I used tkprof from 9.2.0.1. Will this make the formatting any different? I still did not find any useful information what the optimizer considered before choosing this plan.
Thanks,
Aruna
|
|
|
|
|
|
| Re: EXPLAIN PLAN CHANGE [message #274842 is a reply to message #274813] |
Wed, 17 October 2007 06:51 |
mson77
Messages: 208
Registered: August 2007
Location: Brazil
|
Senior Member |
|
|
Hello arunprasad_bh,
As you said before that you have access to both oracle environments. And here we are talking about paths chosen by the optimizer considering that both have the same stats information.
Could you run and paste here the result of the command below for each oracle environment?show parameter optimizer;
mson77
|
|
|
|
|
|
| Re: EXPLAIN PLAN CHANGE [message #274866 is a reply to message #274535] |
Wed, 17 October 2007 08:32 |
arunprasad_bh
Messages: 32
Registered: June 2007
|
Member |
|
|
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------
optimizer_dynamic_sampling integer 1
optimizer_features_enable string 9.2.0
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_max_permutations integer 2000
optimizer_mode string CHOOSE
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
SQL> SHOW PARAMETER OPTIMIZER
NAME TYPE VALUE
------------------------------------ ----------- -------------------------
optimizer_dynamic_sampling integer 1
optimizer_features_enable string 9.2.0
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_max_permutations integer 2000
optimizer_mode string CHOOSE
SQL>
Michel I got the 10053 files from both the environments. The 9.2.0.8 is extensive. But 9.2.0.5 has not produced equivalent file. It does not have all the stats provided by higher version.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: EXPLAIN PLAN CHANGE [message #274905 is a reply to message #274535] |
Wed, 17 October 2007 11:18 |
arunprasad_bh
Messages: 32
Registered: June 2007
|
Member |
|
|
There are more than 300 different combinations considered. There are also differences in the way the join orders are numbered.
Has someone did this before? I would attempt to do this the hardway, just to know the cause. But knowing that some one did this exercise before, would be of great help.
In the first glance, I can say that new version has not got the optimum join order. I have compared join order using #n numbering of the tables.
|
|
|
|
|
|
| Re: EXPLAIN PLAN CHANGE [message #274944 is a reply to message #274812] |
Wed, 17 October 2007 16:47 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| arunprasad_bh wrote on Wed, 17 October 2007 11:39 |
As I told before, I am trying to get the optimizer get the plan that would take less time because these queries are fired from BO. If this is a query is used in a sql, I would have gone for a hint(which I already demonstrated).
|
First, apologize my terminology, which may not be accurate, as I am not BO developer. But it is possible to force hint into BO generated query.
The trick is in creation of user defined (derived) column in the universe containing this hint followed by anything (preferrably NULL, eg. /*+ ORDERED */ NULL). Then put this column as the first one in the report query.
It is quite tricky, but the generated query contains hint in the right place (right after SELECT). Also I do not know whether it is possible to assure order of tables in the FROM clause, so you may prefer LEADING hint.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
