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. 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.

Pl sql developer explain plan