Home » RDBMS Server » Server Administration » Cost on imported stats from production is changed (11g, 11.2.0.3, Window server)
 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 #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 |
|
|
|
Goto Forum:
Current Time: Sat May 03 00:50:17 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
