Oracle makes up statistics when you demand the use of the CBO. If the number of users in development is 100, then it is 1000 in production.ģ.When contention for objects increases(which is the case in production) the OPTIMIZER will for sure take different decesions as far as execution path is concerned.Ĭan you give your valuable expert opinion on the above.ġ.Is my contention right? If so can you give me some more factors which make the up the difference in performance between devolopment and production.Ģ.How can I benchmark my standpoint and show it on paper that tuning in development not necessarily meants the sql will do fine in Production. My point of view is that even though everything is the same datawise in d1, t1 and p1, the OPTIMIZER will not necessarily decide on the same execution path in all the 3 instances.ġ.The hardware is not the same for the 3 instances.Ģ.More number of users access the production database. I disagree with them, even though the database is refreshed every day. This is in a case where the dev and test database is refreshed everyday. I was in a meeting today, and was surprised to hear from a lot of developers that they are under the opinion that a sql tuned in development will work the same way in test and in production. Tom, I'm a regular visitor toyour website, and have quite a bit on tuning on your forum.
They will show you the SQL your application is submitting to the database, the plans used to run it, how many rows flowed through each step of the plan, how many rows were returned to the client, how many fetches took place to get those rows, how much CPU it took and how much wall clock time it took (plus lots more information).Īddenda: Updated links to recent versions of the documentation here: Those three things are the most powerful application tuning tools out there.
DATAGRIP EXECUTION PLAN MANUAL
I would suggest as a way to see what is really happening - to read the server tuning manual and find all about SQL_TRACE, TIMED_STATISTICS, and TKPROF. If you have not analyzed the tables recently - with their current set of data, then the plans generated by the optimizer can be quite bad indeed. My question back to you would be - have you analyzed the tables or have you just set the optimizer_mode. So, I'll assume you've set the optimizer_mode to FIRST_ROWS or ALL_ROWS. O ALL_ROWS (find a plan to get the last row the fastest using O FIRST_ROWS (find a plan to get the first row the fastest using
O CHOOSE (uses CBO if statistics are present, RBO otherwise)
You say you've set the optimizer_mode = cost, but values for optimizer_mode are PB isn't really hung in all probability but the query is taking a very long time to complete. It covers all of the access plans and such so you'll know what a SORT MERGE JOIN versus NESTED LOOPS means.Īs for the second problem - it sounds like a bad query plan is being generated for the given query. Which is the server tuning guide, will be invaluable to you in learning this. Do you mean "how do I interpret it"? If so please see: As for "a methodolody to predict the output of explain plan" - I'm not sure at all what you are looking for.
0 kommentar(er)
