Example to Unveil the Power of Oracle Cost-Based SQL Optimizer
A user who has a SQL statement takes a long time to execute, actually the SQL is not very complicated, but it has a very complex execution plan.
Mimic SQL text ：
SELECT TO_CHAR(SYSDATE, ‘yyyy-mm-dd’) AS STAT_DATE,
SYSDATE AS STAT_TIME,
NVL(X.NUM_ROWS, 0) AS TABLE_ROWS,
NVL(ROUND(X.NUM_ROWS * X.AVG_ROW_LEN / 1024, 2), 0) AS TABLE_SIZES,
Y.CREATED AS CREATE_TIME,
Z.COMMENTS AS TABLE_COMMENT,
FROM ALL_TABLES X,
(SELECT H .TABLE_NAME, COUNT(1) AS COL_CNT
FROM ALL_TAB_COLS H
WHERE H.OWNER = ‘TOSSKA’
GROUP BY H .TABLE_NAME) H
WHERE X.TABLE_NAME = Y.OBJECT_NAME
AND X.TABLE_NAME(+) = Z.TABLE_NAME
AND X.TABLE_NAME = H.TABLE_NAME
AND Y.OBJECT_TYPE IN (‘TABLE PARTITION’, ‘TABLE’)
AND X. OWNER = ‘TOSSKA’
AND Y. OWNER = ‘TOSSKA’
AND Z. OWNER = ‘TOSSKA’
ORDER BY X.TABLE_NAME
The following partial plan steps list about 10% of a total of 214 steps of execution plan.
User input the SQL into Tosska SQL Tuning Expert for Oracle, and press Tune to start exploring if there are only potential better query plans from Oracle. User goes back to his daily work and let the computer do the rest of tuning job on his behalf. Finally, there are 5 better alternatives of SQL which are found within an hour.
Tosska SQL Tuning Expert equipped with an AI engine which can try most effective combinations of Hints injection to the problematic SQL. The engine can deeply explore any hidden good execution plans that Oracle CBO cannot discover at the stage of online SQL optimization, but those good plans actually exist in the plans space for the given SQL statement.
Original SQL takes 14 minutes and 7 seconds.
The best alternative SQL with hints injected is SQL 45 and it takes 4 seconds only.
SQL45 is running more than 99 times faster than the Original SQL.
Observation of the query plans generated by Oracle CBO
Tosska SQL Tuning Expert is a Hints-Injection-Based SQL tuning tool without the need of rewriting user’s SQL text. So, various hints injected to a SQL statement and the corresponding query plans generated by Oracle are all potential query plans that Oracle SQL optimizer can provide for the SQL statement.
Let’s review those generated query plans and why Oracle cannot find the best query plan at the beginning.
Original SQL’s cost is 1330, but the cost of SQL 130, SQL 135 and SQL 45 are all lower than Original SQL’s cost, why can’t Oracle pick up these lower cost plans?
Oracle cannot explore all potential query plans that it can generate within a short time, otherwise it will take even longer time to optimize a given SQL that might not be fully compensated by an unforeseeable better query plan.
SQL 127 and SQL 129 have 3 times higher cost than Original SQL, but the speed is much faster than Original SQL, it means the cost estimation of these two SQL are exceptionally wrong.
It is the limitation of cost estimation algorithm used in database SQL optimizer. Theoretically, there is no 100% accurate SQL cost estimation algorithm in the market that can handle various environments, and the problem is especially true for complex SQL statements like the above SQL statement.
Oracle has the most sophisticated SQL optimizer in the market. There are a lot of better query plans that Oracle are potential can run faster for your SQL statements. So, whenever you are thinking to upgrade your hardware or cloud service spending, you should first explore the potential power that Oracle SQL optimizer can provide for your SQL statements, and what you need is only a right tool that can unveil the potential power of your Oracle SQL optimizer.
Tosska SQL Tuning Expert (TSE™) provides the first 20 alternatives for free if you don’t have a license. If you are satisfied with our product’s functionality, you should purchase a license from our website to enjoy the full power of our product.
Bringing the automatic SQL tuning technology to new heights
Dec. 20, 2018
Hong Kong, Dec. 20, 2018 – Tosska Technologies Limited (Tosska), an IT company that provides database and SQL performance related tools using Artificial Intelligent technology, today announces the general availability of Tosska SQL Tuning Expert Pro (TSE Pro™) version 2.0.8, an advanced version of their “one-click machine tuning” product for SQL performance improvement for Oracle®.
SQL Tuning is a classical problem for every DBAs. It requires in-depth knowledge of SQL tuning skill. With Tosska SQL Tuning Expert Pro (TSE Pro™), DBAs finally have the ultimate SQL tuning tool that can ease their day-to-day tuning job by simple one-click tuning process. TSE Pro™’s Index Adviser also helps DBAs to explore the possibility to further improve SQL performance using indexes yet with good work load balancing for all SQLs involved.
“Continuous improvement to our product is a key R&D directive for Tosska. Though we have received a lot of positive feedback on our TSE Pro™ product, we are keeping on looking for ways to improve it further. The newly announced TSE Pro™ version 2 comes with an enhanced tuning knowledge-base that supports almost double the number of hints comparing to our previous version, bringing the automatic SQL tuning technology to new heights. It creates a new standard of Hints-injection-based SQL tuning ability that no other tools can achieve in the market.” said KaMing Ng, CEO of Tosska. “This increase in supported hints means that more problematic SQL can be improved and the actual test result shows that even very simple SQL statements still have chance to be improved in various environments.”
“We have been keeping our ears open and listen to feedbacks from our users around the world. As such, in TSE Pro™ version 2, we have added a new ‘Tune Top SQLs’ module that can help user to extract high workload SQL statements from SGA and AWR into a ‘Top SQL Repository’ and store in local PC. It is a very useful function for DBA to identify problematic SQL statements without the need of going through thousands of lines in program source codes. Each identified problematic SQL statement can be tuned by just a point-and-click in the Top SQL Repository window, and it is tightly integrated with the Tune SQL function to streamline the flow from problematic SQL identification to SQL optimization. Users can also make any remarks on specific SQL statement as a reminder for follow-up actions.” said Richard To, CTO of Tosska.
TSE Pro™ version 2 is now available for free trial download from Tosska’s website. Please visit Tosska’s website www.tosska.com for details.
Tosska Technologies is a company that focuses in providing solutions for database and SQL related performance optimization and improvements. Our mission is to help users to smooth out the hurdle by our new technologies. Furthermore, Tosska is one of the very few companies in the world that focus in using artificial intelligence technology to solve various database performance problems. It is our goal to help our customers to reduce their hardware investment; increase their database applications service level and free up their human resources for more strategic activities with our innovative technologies. For more information visit www.tosska.com or email us at email@example.com.
Press and Media Inquiries
Chief Executive Officer
Tosska Technologies Limited
All Trademarks mentioned on this Site are the property of their respective owners.
Bring the automatic SQL tuning technology to new heights
With almost doubled the number of hints supported in version 2, Tosska SQL Tuning Expert Pro (TSE Pro) V2 SQL tuning ability is brought to new heights. It creates a new standard of Hints-injection-based SQL tuning ability that no other tools can achieve in the market. More problematic SQL can be improved with the new Hints and bigger Quota allocation of each Intelligent Level in TSE Pro version 2. The actual test result shows that even very simple SQL statements still have chance to be improved in various environments.
Options Window shows new Quota and Hints setting
Streamline from problematic SQL identification to optimization
A new Tune Top SQLs module is added to TSE Pro version 2, it is a powerful tool that can help user to extract high workload SQL statements from SGA and AWR into a Top SQL Repository and store in local PC. It is a very useful function for DBA to identify problematic SQL statements without the need of going through thousands of lines in program source codes. The layout of the captured SQL statements can be ordered by various resource consumptions, users can easily review and locate problematic SQL statements and prioritize them for later SQL tuning exercise. Each identified problematic SQL statement can be tuned by just a point and click in the Top SQL Repository window, and it is tightly integrated with the Tune SQL function to streamline the flow from problematic SQL identification to SQL optimization. Each tuned SQL will be recorded with improvement statistics and tuned SQL hints solutions. Users can also make any remarks on specific SQL statement as a reminder for follow-up actions.
SQL statements are captured from SGA and to be tuned one by one according to user’s priority
Specific SQL’s tuning details can be shown right next to SQL list tag page
Application package software is a collection of software programs that is developed for the purpose of being licensed to third-party organizations. Although
a package software may be tailored for a user’s specific needs through parameters or tables, the software itself is not developed specifically for an organization. So, users do not own the source code and have no way to modify the embedded SQL statements for performance tuning purpose. There are a lot of application packages built on Oracle RDBMS such as Siebel, PeopleSoft JD Edwards, SAP and so on. In order to help application packages’ users, Oracle provides some features for helping users to tune their SQL statements without the need to change their source code.
It is a profile generated by Oracle SQL Tuning Advisor. A SQL profile contains corrections for wrongly estimated statistics, auxiliary information. Therefore, SQL profile just guides the optimizer to a better plan, but they do not guarantee the same plan each time the statement is parsed. For certain SQL statements, no matter how good the statistics are corrected, Oracle SQL optimizer is still not able to generate a better plan in specific environments. For these kinds of SQL statements, human intervention is necessary, but SQL profile is not a convenient tool for developers to force Oracle to pick up a new plan without changing the program source code.
SQL plan baselines and stored outlines
Due to the Oracle environment changes or Oracle database version upgrade, it might target Oracle SQL optimizer to generate new plans for certain SQL statements. If it is not good, and we need something to preserve the old plans for the new environment. To achieve SQL plan stability, stored outlines was the major tool in earlier releases of Oracle Database. This feature is still supported in Oracle Database 11g; however, it might be depreciated in the future releases and replaced by SQL plan management. The mechanism of SQL Plan Baselines is to preserve the performance of specified SQL statements, regardless of changes in the database environment or release upgrade. Furthermore, create Plan Baselines manually for a SQL statement is possible, and this technique can help developers to guide Oracle SQL optimizer to generate a specific plan for a bad performance SQL statement. So, when Oracle SQL optimizer receive the same SQL statement next time, a better performance plan will be composed according to the new plan baselines stored in database. There is no need to change the SQL syntax in source programs.
For example, if you want to tune a SQL with execution plan-A that is currently used by Oracle SQL optimizer in your database, and you want to tune the SQL with Hints to make Oracle SQL optimizer to generate a new execution plan-B. What you have to do is to execute the tuned SQL with new Hints and use the following method provided by Oracle:
Execute the tuned SQL with Hints and plan B cached in SGA.
SET SERVEROUTPUT ON DECLARE My_Plan PLS_INTEGER; BEGIN My_Plan := DBMS_SPM.load_plans_from_cursor_cache( sql_id => 'Plan-B SQL_ID', plan_hash_value => 'Plan-B plan_hash_value’, sql_handle => 'Original SQL’s sql_handle'); DBMS_OUTPUT.put_line('Plan Loaded=> ' || My_plan); END;
To enable the use of the tuned plan, manually alter the tuned plan to a fixed plan by setting its FIXED attribute to YES.
To enable the use of SQL plan baselines, make sure the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter is set to TRUE.
Weaknesses of using SQL Plan baselines for SQL tuning
As the SQL plan baselines was designed to preserve the performance of SQL statements such as after the following environment changes:
- New optimizer version
- Changes to optimizer statistics and optimizer parameters
- Changes to schema and metadata definitions
- Changes to system settings
- SQL profile creating
You can see that it is not designed for the purpose of manual SQL tuning. There are also some additional limitations such as Parallel Hints is not supported by SQL Plan Baselines, you cannot load a Plan-B with Parallel Hints applied your SQL with bad performance of original Plan-A. Parallel Hints sometimes are very important for a better plan generation by Oracle SQL optimizer.
SQL Patches is part of the features provided by SQL Repair Advisor which is used to fix a SQL statement’s critical failures such as returning wrong result. The SQL Repair Advisor analyzes the problematic statement and in many cases recommends a SQL patch to repair the statement. The SQL patch is to influence the Oracle SQL optimizer to choose an alternate execution plan for future executions, instead of using the original problematic execution plan. There is a public API call to create SQL patches manually provided by Oracle Database 12c Release 2 onwards. The DBMS_SQLDIAG.CREATE_SQL_PATCH package can help users to create a SQL Patch for specific SQL statement for SQL tuning purpose. You can change a bad performance SQL statement’s execution plan without the need to modify the program source code as the following example:
DECLARE Patch_name VARCHAR2(32767); BEGIN Patch_name := SYS.DBMS_SQLDIAG.create_sql_patch( sql_text => 'SELECT * FROM employees WHERE emps_dept IN (SELECT dpts_id FROM departments WHERE dpts_avg_salary <200000)', hint_text => 'INDEX(@SEL$1 EMPLOYEES) INDEX(@SEL$2 DEPARTMENTS)', name => 'my_sql_patch_name'); END;
If your database version is before Oracle database 12c Release 2, you must use this package DBMS_SQLDIAG_INTERNAL.i_create_patch instead. Both SQL text and SQL ID is able to be used for SQL hints injection. The injected hints for your SQL should be placed in hint_text input parameter. There is only one line of Hints text you can use for a SQL and there is no way to define your own query block name for any subqueries’ block. So, if your SQL has multiple subqueries and you want to instruct Oracle to do something in subqueries’ blocks, you must use Oracle default query block names in your injected hints text.
hint_text => ‘ INDEX(@SEL$1 EMPLOYEES) INDEX(@SEL$2 DEPARTMENTS) ‘
This hints text in the above example shows that @SEL$1 and @SEL$2 are default query block names provided by Oracle in the execution plan of the SQL. The Hints tells Oracle use index search for EMPLOYEES table in query block @SEL$1 and also use index search for DEPARTMENTS in query block @SEL$2.
Pros and cons of using SQL Patches to tune SQL
SQL Patches is more flexible to accept hints instructions without SQL Plan Baselines’ limitations, complex hints with parallel operations are normally accepted by SQL patches. There is no additional maintenance effort to tell Oracle to use the SQL Patches after it is created. Oracle will use the stored hints to optimize any SQL with the same SQL ID or SQL Text and generate a better performance execution plan. Furthermore, you can also use SQL Patches to disable a SQL with a destructive hints already written in a package application or even use it to control a bind-aware SQL execution behavior.
As the injected hints text must be placed in one text line and using default query block name only, manually compose a desire Hints to improve a SQL statement will be a difficult task for most SQL developers especially for complex SQL statements with many subqueries.
A tool to automatically create Hints and SQL Patches
There is only one tool in the market so far that is able to generate a better hints and create SQL Patch in a fully automatic way.
Tosska SQL Tuning Expert Pro is a tool for users to improve SQL performance without touching their program source code. Users can deploy different performance query plans for various sizes of production databases without the effort of keeping multiple versions of the program source, and it is especially suitable for package application users who don’t own the source code of their applications. The tool will try most useful hints combinations to tune your bad performance SQL statement, the best Hints combination SQL performance will be benchmarking side by side with the original SQL. Users will get the exact performance improvement without any guesswork or uncertain cost assessment only.
You can visit our website for product details
Best elapsed time as the tuning objective for SELECT statement is normally useful for most situations, but for some online screens for many records display, good response time is very important for user experience. So, you should consider using response time as the tuning objective for this kind of applications.
You should capture the most representative SQL workload that created performance problem in your system. For example, if there was a performance problem at 11:00 AM yesterday, you should extract this SQL workload from AWR for index recommendation.
If you cannot find better alternatives at the current Intelligent Level, you can increase the Intelligent Level or disable the “Disregard SQL alternatives with cost > 50 times of Original SQL” to try more Hints permutation for your SQL statement.