Most database vendors provide optimization hints which enable a user to influence the decision the database SQL optimizer will make when determining which execution plan it will choose. Oracle provides a full set of optimization hints to help users to rectify an individual SQL performance problem, thereby making it the most open of all the database platforms. This approach admits that the database's internal SQL optimizer cannot guarantee every SQL will perform well and therefore it sometimes necessary for the user to intervene for some SQL statements when the database SQL optimizer has failed to generate a good execution plan for them.
You may not be aware that upgrading the database SQL optimizer is a risky undertaking for database vendors. No matter how good a new version of the database SQL optimizer is, it is going to have some negative impact on the performance of at least a few SQL statements. For example, if a new version of the database SQL optimizer can fix 50% of the existing SQL statements' performance problems, but in the meantime, it introduces 5% new performance problems for existing good SQL statements, mathematically, it is 10 times better than the old version. It should be a good idea to the upgrade. But the point is, that most systems are already running on an “adopted” status, which means that users have accepted what they have, they know which functions are running slow and that the person who tunes the database may have already changed the system configuration to address these problems. Sometimes, the users’ daily routines are changed to accommodate those slow SQL processes. For example, they may schedule a meeting for when they are going to launch a batch process or get a cup of coffee while a long running SQL is executing.
If there are any changes after upgrading to a new database version, you are likely to find that even with 50% improvement in SQL performance, this will not stop the users from complaining about the 5% new performance problems. So, this is why database vendors need to provide optimization hints to let the users fix problems at the individual SQL statement level and not in database SQL optimizer global level. This trend is becoming more popular among database vendors. Sybase’s Plan Forces and Microsoft SQL Server’s optimization hints work like Oracle optimization hints. IBM DB2 UDB does not provide any optimization hints, but they do provide optimization classes to adjust the intelligence of execution plan generation without the user's specific control. Unless the database SQL optimizer can guarantee that each execution plan it generates is the best execution plan for each unique SQL and database environment, we prefer the approach that Oracle provides which gives us the opportunity to help the database SQL optimizer choose the best execution plan using the optimization hints.
Something more than optimization hints is needed
Optimization hints are used to guide the database SQL optimizer to pick up a user specified execution plan during SQL optimization. But the problem with this approach to SQL tuning is that you must change your source code to include the optimization hints in your SQL statements. You can see that the effort needed to tune a SQL statement may be substantial. The tasks, from source modification, program compilation, unit testing, QA review to deployment, are time consuming and any mistakes may cause business interruption or an data integrity problem. For the users of packaged software, since the source code cannot be modified, so tuning SQL statements by adding optimization hints is not possible. To resolve this, database vendors have introduced a new feature to allow the user to control how the database SQL optimizer generates the execution plan without the need to change your SQL text. As each database vendor has its own naming practice, I call this type of feature “Plan Instruction Language”. For example, Sybase was the first to introduce the full implementation of this concept with its “Abstract Plan” feature. (Oracle Stored Outlines came out before Sybase's "Abstract Plan", but initially it focused on plan stabilization and was not easy to manage) To instruct the database SQL optimizer to generate a user preferred execution plan for a specific SQL statement, users can modify and save the “Abstract Plan” with the SQL text into the database. Sybase will match the incoming SQL text with the stored SQL text the next time the SQL statement is executed. If it finds a match, the corresponding “Abstract Plan” will be used to generate the execution plan for the SQL statement.
For incoming SQL statements, the SQL text is compared to the stored SQL text, and if a match is found, the saved abstract plan is used to execute the SQL statement..
This new method for SQL tuning reduces the time that a developer needs to tune a SQL statement, since the developer no longer needs to modify, QA, and deploy the source program. What the user has to do is to modify and test the Abstract Plan’s performance and then save it into the production database. Then, the targeted SQL performance will be improved with the new execution plan. Any unexpected performance degradation can be recovered easily by dropping the newly created Abstract Plan. Furthermore, this new SQL tuning feature enables the package users to tune their SQL statements without violating the maintenance contract by privately creating Abstract Plans for slow SQL statements. Package providers could even deploy their program source with different Plan Instructions to fit different database sizes.
This approach is now being commonly adopted by major database vendors. SQL Server 2005 has introduced their "Plan Guides", DB2 Version 9 has its new "Optimizer Profiles and Guidelines" and Oracle 10g has "SQL profiles". “Plan Guides” in SQL Server 2005 provides more powerful SQL optimization instructions that users can manually create for a SQL statement, apart from traditional optimization controls, you can parameterize (share cursor) or un-parameterize a SQL with hardcode literals, furthermore you can force a SQL with bind variable to “optimize for” a specific value (literal) without the need to change your source code as the following example:
Example:
Select * from employee where employee_id <@emp_id
OPTION ( OPTIMIZE FOR (@employee_id = 10) );
The example to force database optimizer to use employee_id index to search EMPLOYEE table, no matter what value it is passed to @employee_id
In coming Oracle11g, there is a new feature called “Plan Management”, according to what is described on the Oracle website, it appears that Oracle Plan Management will work like an internal plan versioning tool. It will verify and review the new execution plan before it is used, which means that the Oracle SQL optimizer will be more careful to use a new plan. The good news is that your SQL will not suddenly slowdown due to environment or statistics change, but it may delay or even forbid the Oracle cost based optimizer to adapt to a changing environment. Anyway, with the limited information available on Oracle 11g, we cannot draw any conclusion at this moment. Let’s wait and see what this new feature will bring!