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 Go to next message
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 #636365 is a reply to message #636363] Wed, 22 April 2015 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Because these are not the same servers.
Cost is meaningless across servers (or database or SQL), just use it for the same query on the same server and database.

Re: Cost on imported stats from production is changed [message #636443 is a reply to message #636365] Fri, 24 April 2015 01:42 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous message
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

Previous Topic: ITL wait events for indexes
Next Topic: Delete repository tables in Oracle data modeler
Goto Forum:
  


Current Time: Thu Mar 28 11:58:01 CDT 2024