SQL_IDs, Plans, and Baselines, Oh My!

Do you run a 3rd party application that uses Oracle RDBMS under-the-hood? Do you experience performance issues? Frustrated that you do not own the application SQL that is causing the issues? Maybe an upcoming upgrade to 11g might give you some relief.

Many of the customers I speak with are unaware of a new 11g feature called ‘SQL Plan Management’ (docs.) A big bonus here is that a majority of the functionality is available without any additional licensed packs – although you do get more assistance if you are using the Diagnostic/Tuning configuration (docs).

Let’s say one of your fancy database performance tools or scripts detects a plan running frequently that is less than optimal. You pull up the SQL_ID, and immediately recognize that as belonging to the ‘X’ module in your packaged application. You check the stats and indexes – all are present and accounted for. So, you open a support ticket with your vendor and hope they fix it in a patch.

What if you had ANOTHER option?

Instead, you or the database identify a better plan. You test it, and voila it’s 80-bazillion times faster than the default. What if you could just tell Oracle that you have a better plan than it does for this query? With the SQL Plan Baseline you can. No changes to the data or to your application, but the database will now run your inefficient query better auto-magically!

With a few package calls, or OEM clicks you can deploy your new plan or plans and the database engine will use them if it can whenever your query is executed.

These new plans are stored in the database – in a mystery object inside of the SYSAUX tablespace (docs). They’ll stay there based on a retention/space policy defined via

[sql]
DBMS_SPM.CONFIGURE()
[/sql]

If the plan goes more than year without being executed, then it gets aged out. This can be configured to an absurd period of time apparently – 10 years! You can also tell Oracle to never age your plan out, by disabling the AUTOPURGE flag for your baseline.

We bounce our database every week, so this isn’t viable for us.
No, you should be covered here. The baselines are stored in the database, not in a buffer somewhere that’s likely to be flushed at some point.

SQL_IDs do go away, but since they are just a hash value of the query object – then they should remain the same even after a hard-parse occurs. Or as I understand it after reading Tanel Poder’s (Twitter) excellent blog post on the subject (link):

Fixed Baselines
Oracle is an expensive solution. There are free database engines out there, and relatively cheaper enterprise ready RDBMS solutions to choose from. So why pay the premium to use Oracle? I think this technology is a great example of why you get what you pay for. With 11g, Oracle is continually evolving plans ‘under the hood’ for you automatically. It won’t let performance degrade because a poorer plan is generated. However, no technology is fool-proof, and so you can also have a direct say over what is happening in the database via a Fixed Baseline (docs.)

You can tell Oracle to run your baseline even if the cost of one of its plans is lower. “I know better than you!” Or let us put it this way, have you ever seen a query with a higher cost run more efficiently than a query of a lower cost (that returns the same data)? This happens very frequently in Oracle (but hardly ever in SQL Server or DB2?!?). So even though things are ‘automatic’, we still need smart people to run our applications and databases.

Tune in Test, Deploy to Prod
Baselines can be exported and imported (docs)! If you’re more of a GUI type, then there are tools out there that can help or automate this procedure.