There are at least two commonly used methods to improve an SQL statement’s performance. First, users can influence Oracle SQL optimizer to pick up another execution plan for their SQL statements. Second, users can add new indexes to speed up the data retrieval efficiency. Let us discuss these two methods in the following. Influence Oracle SQL optimizer It is a commonly used method by rewriting the SQL statement to a semantically equivalent but syntactically different SQL statement to influence Oracle SQL optimizer to pick up the other execution plan that may run faster than your original SQL statement. This tuning method has been popular for decades for Oracle. Due to the fact that Oracle internal SQL rewrite/transformation ability has been improving a lot in the last ten years, manually rewrite method is getting difficult to be mastered by inexperienced database developers. It is because Oracle is getting not that sensitive to users’ SQL statements syntax, the execution plan will be relatively fixed by the Oracle’s stronger internal SQL transformation mechanism. The Oracle SQL transformation engine will rewrite your SQL statement to a more canonical syntax for later stage of execution plan generation. Instead of using manual SQL syntax rewrite to influence Oracle SQL optimizer to generate a better plan for your SQL statements, Oracle officially provides better SQL Hints features for users to help Oracle to solve SQL performance problem. Using Hints to improve SQL statements SQL Hints is a powerful feature that Oracle provides for users to help Oracle to improve any bad performance SQL statements. This method is safer than manually rewritten SQL syntax without the risk of changing the semantics of your SQL statements. Example of using SQL hints. SELECT /*+ USE_NL_WITH_INDEX(@SEL$1 EMPLOYEE EMPLOYEE_PK) PARALLEL(@SEL$1 DEPARTMENT) */ * FROM employees, departments WHERE emps_dpt_id = dpt_id AND emps_dpt_id IN ('IAO','FMD') Adding new indexes to improve SQL statements Adding new indexes to the tables to improve SQL statements is a commonly used method by most developers, but adding new indexes will incur overhead to the tables embedded in the SQL statements. Sometimes, it may sacrifice many SQL statements’ performance for just saving only one SQL statement. Unless the new set of indexes is good for all your related SQL statements, it is highly recommended to review those related SQL’s execution plans before deploying to production database. How to make sure your SQL is using the best execution plan already? Manually checking whether SQL statements are already running with the best execution plans is challenging for inexperienced developers. Furthermore, how to influence Oracle SQL optimizer to use a better execution plan instead of the original poor performance plan for your SQL is even harder. Let us show you how to solve both problems by just points and clicks with Tosska SQL Tuning Expert Pro (TSE Pro). The following SQL with index on employees(emps_dept), employees(emps_grade), departments(dpts_id) and grades(grds_id). The execution time for this SQL is 11.77 seconds. We do not know whether it is the best execution time that Oracle SQL optimizer can provide. In order to know the answer, we place this SQL into the product and press <Tune> to look for better execution plan for this SQL statement. There is a better plan with execution time 0.16 second found after a few minutes of tuning process. It is more than 70 times faster than the original execution plan that is selected by Oracle. A hint /*+ PRECOMPUTE_SUBQUERY(@SEL$2) PRECOMPUTE_SUBQUERY(@SEL$3) */ is added to the SQL to tell Oracle to generate a specific execution plan with better performance without adding new indexes. Some users may worry about the effort of changing the source code to embed this new optimizer hint, but TSE Pro comes with a plan deployment function that allows users to deploy the plan with SQL Patches or Plan Baselines without the need of changing program source code. How to make sure your SQL is using the best indexes? The SQL has already come with individual index on employees(emps_dept), employees(emps_grade), departments(dpts_id) and grades(grds_id). Let us see if any new indexes can help to improve this SQL statement. The engine provides multiple sets of indexes recommendations after pressing the <Recommend> button and 2 sets of indexes with execution time 2.13 and 2.14 seconds are found. The best “index set 1“makes Oracle SQL optimizer generate a new plan that is almost 6 times faster than original execution plan. The “index set 1” and “index set 2” has almost the same performance improvement, but the size of “index set 2” is relatively smaller with less need for resources when compare to “index set 1”, so “index set 1” is more preferable in most database systems. Adding new indexes or adding hints? Introducing new indexes to a database not only adds extra overhead to all relative DML SQL statements, but also increases the instability of the tables’ related SQL’s performance which might be due to the new plans generated by the newly created indexes. Therefore, adding hints is a much safer way to improve a SQL unless you are sure that it cannot solve the problem. Obviously, adding hints to the SQL has a better performance improvement than that of adding new indexes for this SQL example. Furthermore, TSE Pro provides a Plan Deployment function that allows users to improve their SQL performance by just points and clicks without the need of changing their program source codes.
