| Cost on imported stats from production is changed [message #636363] |
Wed, 22 April 2015 06:00  |
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
|
Senior Member |
|
|
On Monday, I have refreshed the stats at local testing database from production database. explain plan generated from both the databases are same, only difference is cost. Optimizer parameter and indexes on tables are also same at both end.
Even after that, cost is showing differences. Explain and query is attached. Please let me know why there is differences.
Regards,
Ashish
|
|
|
|
|
|
| Re: Cost on imported stats from production is changed [message #636443 is a reply to message #636365] |
Fri, 24 April 2015 01:42  |
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
|
Senior Member |
|
|
Dear Michel,
Then, bringing statistics from production is meaningless, respect to cost.
It will show same execution plan, irrespective to cost and time.
Our requirement, is to get the same plan with the same cost at testing database. So that developer can tune the query.
Is this possible or hardware difference is matter?
Seeking your suggestion.
Regards,
Ashish Kumar Mahanta
|
|
|
|
| Re: Cost on imported stats from production is changed [message #636446 is a reply to message #636443] |
Fri, 24 April 2015 01:56 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Quote:only difference is cost
Cost is a relative unit and has no significance while compared on two different platforms.
Quote:
get the same plan with the same cost at testing database
hardware difference is matter?
As Michel already said, they are different servers, different databases, different infrastructure, different...
|
|
|
|
| Re: Cost on imported stats from production is changed [message #636449 is a reply to message #636443] |
Fri, 24 April 2015 02:28 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
It can be difficult to make your development system react in the same way as production. How did you actually create it? To ensure that it is as close as possible, you need to clone it using a technique such as an RMAN duplicate. Then you do not need to worry about the stats being different. You said that you "refreshed the stats at local testing database from production database", does that mean that the dev DB is not a clone? Or perhaps, not a recent clone? The worst case is that you created it with export/import, in which any testing of performance chaacteristics will likely be useless.
Lastly, which stats did you "refresh"? You'll need the object stats of course, and also dictionary, fixed object, and (never forget these!) system stats.
|
|
|
|
| Re: Cost on imported stats from production is changed [message #636450 is a reply to message #636449] |
Fri, 24 April 2015 02:51 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Also don't forget identical hardware: That includes the SAN layout, HBAs, switches etc. Identical system stats so the DB can value the "cost" of an IO correctly.
Unless this has been built from day 1 with this in mind and preferably using storage snaps, you've about the same odds of getting everything identical as a tornado hitting a junkyard and the parts forming a 747.
The thing is, even if you do manage this, it'll last about one test. This is because the database is smart, at basically attempts to adapt to its exact environment - cardinality feedback (dynamic statistics in 12c), dynamic samping and so forth will all conspire to make the DB the best it can be in the location it is with the data it has. Likely you'll also lack the intensity of production, you'll have different row groupings etc etc making range scans very hit and miss compared to production.
Anyway the long winded point I'm getting at is, you need to ignore cost and have the query tuned to a plan that makes sense. You'll need to know your data, the filter factors etc and how the optimizer works - basically you should already know the plan you want - the point of test is how well does that perform and is the DB liable to pick the plan you want most/all of the time.
If the query needs to be tuned, presumably you already know the problem steps where the time is going so it is a case of running that plan in test - improving it and checking that in test vs test the new plan executes optimally compared to the old. This will give you confidence that what you promote to production should behave in a similar pattern.
[Updated on: Fri, 24 April 2015 02:52] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
