Reveal the power of CBO

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,
        X.TABLE_NAME,
        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,
        H .COL_CNT
  FROM ALL_TABLES X,
        ALL_OBJECTS Y,
        ALL_TAB_COMMENTS Z,
        (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

Execution Plan:
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.

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


Benchmark Result:
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.

First observation:
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?
Reason:
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.

Second observation:
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.
Reason:
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.

Conclusion:
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™) for Oracle®

Tosska SQL Tuning Expert Pro (TSE Pro™) for Oracle®

Tosska Technologies announces the availability of Tosska SQL Tuning Expert Pro (TSE Pro™) for Oracle® version 2.0.8

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.

   
About Tosska
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 enquire@tosska.com.

Press and Media Inquiries
KaMing Ng
Chief Executive Officer
Tosska Technologies Limited
Phone: +852-21501987
Email: enquire@tosska.com

All Trademarks mentioned on this Site are the property of their respective owners.

How to tune Oracle application packages’ SQL without touching source code

Introduction

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.

SQL profile

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

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
https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/

Tosska Technologies announces the availability of Tosska SQL Tuning Expert (TSE™) for Oracle® version 1.5.0

An innovative machine tuning tool for SQL statements further enhanced

June. 23, 2018

Hong Kong, June 23, 2018 – Tosska Technologies Limited (Tosska), an IT company that provides database and SQL performance related tools, today announces the general availability of Tosska SQL Tuning Expert (TSE™) version 1.5.0, the enhanced version of their first “one-click machine tuning” product for SQL performance improvement for Oracle®.

SQL Tuning is a classical problem for every DBAs. Tuning SQL statements is finding the fastest route to execute the SQL statements. In order to tune a SQL statement, you are required to know your database architecture and have in-depth knowledge of SQL tuning skill.

“After the first release of Tosska SQL Tuning Expert (TSE™) back in Dec. 2017, we have seen a high download rate of the product everyday. People are excited about the convenience and power that TSE™ has brought to them in their day-to-day SQL tuning requirements. Many of them have shared with us their successes in using the product and there are cases where SQLs are improved by over 21 times !” said KaMing Ng, CEO of Tosska excitedly. “There are also a lot of cases where TSE™ provided solutions that DBAs have never thought of, if not using the product. Thanks to the innovative proprietary Artificial Intelligent (AI) engine behind the product that can explore rare SQL alternatives that most DBAs won’t thought of just by their experience.”

“Earlier this year, we have released a new line of SQL Tuning solutions called Tosska SQL Tuning Expert Pro (TSE Pro™), the Professional Versions of the TSE™ series that provides SQL Tuning without the need of source code and with index advise capability. But with the overwhelming success of TSE™, we will NOT stop enhancing our TSE™ product line. That’s why we have released the latest enhanced version of TSE™, version 1.5.0 today,” Mr. Ng continued.

“We have been receiving a lot of positive feedbacks from the public since the availability of TSE™. As a continuous improvement to the capability and user experience of the product, we now make version 1.5.0 general available. TSE™ version 1.5.0 supports the MERGE statement as well as better problem solving ability with more SQL alternatives generation. It now also provides new Bind Variables window and incorporated some minor bug fixes,” said Richard To, CTO of Tosska.

TSE™ version is now available for free download from Tosska’s website. Please visit Tosska’s website www.tosska.com for details.

   
About Tosska
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 enquire@tosska.com.

Press and Media Inquiries
KaMing Ng
Chief Executive Officer
Tosska Technologies Limited
Phone: +852-28248420
Email: enquire@tosska.com

All Trademarks mentioned on this Site are the property of their respective owners.

Tosska Technologies announces the availability of their major SQL performance tuning product – Tosska SQL Tuning Expert Pro (TSE Pro™) for Oracle®

Intelligent SQL tuning without touching your source code

May. 15, 2018

Hong Kong, May 15, 2018 – Tosska Technologies Limited (Tosska), an IT company that provides database and SQL performance related tools, today announces the general availability of Tosska SQL Tuning Expert Pro (TSE Pro™), the major product of the Tosska SQL Tuning Expert Family of Products that provides state-of-the-art machine tuning capability to improve SQL performance for Oracle® without touching the source code.

SQL Tuning is a classical problem for every DBAs. Tuning SQL statements is finding the fastest route to execute the SQL statements. There have already been a lot of SQL tuning products in the market providing better query plan visualization, better statistics analysis, high cost query plan steps indication or even rule-of-thumb syntax recommendations. All in all, those tools are not helpful if users don’t have in-depth SQL tuning knowledge and are not willing to spend extra effort to tune a SQL apart from their daily duties.

Users are eager for one-button-solution tool that can tune a SQL statement automatically without the need of users’ intervention. The Tosska SQL Tuning Expert Product family aims to help DBAs to do SQL tuning just by multiple easy points and clicks.

“Tosska SQL Tuning Expert Pro is a tool for users to improve SQL performance without touching their program source code. Users can even deploy different performance query plans for various sizes of production databases without the effort of keeping multiple versions of the program source. It is especially suitable for package application users who don’t own the source code of their applications,” said KaMing Ng, CEO of Tosska. “If you are a packaged application user, how can you tune your SQL if you can’t edit a query directly? That’s why we decided to expand the TSE Product family and developed TSE Pro™ to solve this problem.”

“There are multiple features provided by Oracle such SQL Profiles, SQL Plan Baselines and SQL patch that you can use to tell Oracle to fix a SQL’s query plan. But the use of these features is limited by Hints injection only, you cannot rewrite a SQL with different syntax and ask the original SQL to accept a rewritten SQL’s query plan. So, hints-based SQL tuning is becoming more important than ever before. Tosska SQL Tuning Expert Pro is the only tool that can provide the most advanced Auto-Hints-Injection solution in the market to fully automate the process from SQL tuning to plan deployment,” said Richard To, CTO of Tosska. “TSE Pro™ also provides an intelligent workload-based index advisor that helps users to review their existing database schema if there are any new indexes which can help to improve a given SQL workload. The TSE Pro™ uses our proprietary Artificial Intelligent (AI) engine which can handle up to thousands of SQL statements and give you a reasonable recommendation that even human experts cannot achieve.”

Free Trial of TSE Pro™ is now available for download from Tosska’s website. Please visit Tosska’s website www.tosska.com for details.

   
About Tosska
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 enquire@tosska.com.

Press and Media Inquiries
KaMing Ng
Chief Executive Officer
Tosska Technologies Limited
Phone: +852-28248420
Email: enquire@tosska.com

All Trademarks mentioned on this Site are the property of their respective owners.

Unlock the power of using Oracle Optimizer Hints to tune SQL statements

Common SQL tuning methods
SQL tuning is the process to improve a SQL statement’s performance up to the user’s expectation. There are at least three methods which are commonly used by developers or DBAs, These methods are creating new indexes, rewriting the SQL syntax and applying Oracle Optimizer Hints to the SQL statements. Each method has its pros and cons and suitable for different stages of application cycle. Let’s discuss these three methods in the following.

Create new indexes for SQL statement
Creating new indexes for SQL statements are a very common method to improve SQL performance, it is especially important during database development. As new indexes to a SQL statement are not only affecting current SQL, it is also affecting other SQL statements running on the same database. So, it should be used very carefully in in production database. Normally, users are required to make impact analysis to other relevant SQL statements for the newly-created indexes.

Rewrite SQL statement
There are a lot of people teaching SQL rewrite skills over the internet. Most of those rewrite rules are inherited from Oracle rule-based SQL optimizer or older version of cost-based SQL optimizer. For example, some people may think that the following SQL may have different performances:
Select * from A where A.unique_key in (select B.unique_key from B);
Select * from A where exists (select ‘x’ from B where A.unique_key=B.unique_key);

Actually, if you put these two SQLs into Oracle database, you may probably get the same query plan from Oracle; it is because Oracle SQL optimizer will rewrite these two SQLs into one standard form internally to enable better query plans generation. A stronger internal rewrite ability was developed by Oracle SQL optimizer in last two decades. So, some obvious problems such as “KEY IS NOT NULL” or “NVL(KEY,’ABC’) =’ABC’ ” were not able to use indexes are solved by Oracle already. Of course, there are still some limitations in Oracle SQL optimizer for complex SQL transformation, so experience users may still be able to tune a SQL statement through SQL rewrite to influence Oracle SQL optimizer to generate a better query plan. But this approach is getting more difficult to apply by DBAs since the relationship between SQL syntax and final query plan generation is getting weaker, this is because Oracle cost-based optimizer (CBO) is getting smarter and the syntax of the SQL text is no longer a dominating factor to the cost calculation and plans generation.
SQL rewrite is still useful both in development and production database, since it is an isolated change to a database and no other SQLs’ performance will be affected, and it is safer than building new indexes. But it requires SQL code changes in program sources, so unit test or integration test may still be necessary. In contrast, using hints to tune a SQL is relatively safer, since it is not easy to change the semantics of the SQL accidentally.

Apply hints to SQL statement
Most databases in the market provide some sorts of query plan control language to help its optimizer to better optimize a specific SQL statement. For example; Optimization Guidelines of IBM LUW and Plan Guides of SQL Server are provided for many years already. Oracle provides Optimizer Hints that embedded with SQL text as a remark to tell Oracle SQL optimizer how to optimize the SQL statement. As hints will not affect the semantic of the SQL statement, so it is relatively safer than SQL rewrite and building new indexes. There are more than a hundred of documented Optimize Hints provided by Oracle. Unless you are an expert in using Optimizer Hints; using the right hints to tune a SQL is not easy to be mastered even by a human expert, since it is close to a million of permutations if we just pick 3 hints out from a hundred of hints.
Let’s use a simple SQL example with the follow Optimizer Hints to show how Optimizer Hints works, the Hints is used to tell Oracle SQL optimizer to use index of DEPARTMENT table during query plan selection if possible. Oracle will try to find the lowest cost plan among all plans with index retrieval of DEPARTMENT table.
Select /*+ INDEX(@QB2 DEPARTMENT) */ *
From employee
Where emp_dept in (Select /*+ QB_NAME(QB2) */ dpt_id
    From department
    Where dpt_name LIKE ‘D%’);

Oracle SQL Hints is not a programing language
Oracle Optimizer Hints is not a programing language that come with proper syntax check or error return. It means an invalid Hints instruction to a SQL statement that Oracle SQL optimizer will not return with error message. Furthermore, even if it is a valid Hints instruction that Oracle SQL optimizer actually cannot comply with, there will be no error message returned too. So, users have to do a lot of trial and error before it can influence SQL optimizer to generate a specific better query plan.

Knowing the solution
It is a very common tuning practice that people are trying to find the best query plan for a bad performance SQL statement, it works like a human mimic Oracle SQL optimizer’s job to optimize a SQL statement with human’s knowledge. If the SQL statement is simple and Oracle SQL optimizer had made an obvious mistake, probably human intervention may work fine for this kind of simple SQL. Here is an example:
Select * from A where A.KEY<’ABC’;
If Oracle SQL optimizer fails to use index to retrieve records from table A and using index of KEY1 is actually a better solution. You can use Optimizer Hints to instruct Oracle to use index instead of full table scan for this SQL statement.
Select /*+ INDEX(KEY1 KEY1_INX) */ from A where A.KEY1<’ABC’ and A.KEY2<123 and A.KEY3<’C’;
Knowing the best solution is easy for simple SQL statements, but it is difficult for complex SQL statements. Since there are a lot of execution steps in the query plan for complex SQL, human beings are not able to estimate each step and work out a series of query steps to compose the best performance query plan. So, using Oracle Optimizer Hints to instruct SQL optimizer to generate specific query plan for complex SQL may not be easy even for an experienced SQL tuning expert.

Knowing the problem
Instead of knowing the solution of a SQL statement, it is relatively easier for a human expert to find where the problem is in a complex query plan. The way to tell Oracle to bypass the problem is applying hints with prefix “NO_” such as NO_INDEX or NO_USE_HASH. It tells Oracle not to use the specified operation in the query plan and select another operation instead with the lowest cost. This approach is not commonly adopted in the market due to people are normally bound by solution oriented thinking.
For example:
Select /*+ NO_INDEX(KEY2 KEY2_INX) */ * from A where A.KEY1<’ABC’ and A.KEY2<123 and A.KEY3<’C’;
If you think the KEY2_INX is not a good index to retrieve records from table A, you can disable the KEY2 index by applying /*+ NO_INDEX(KEY2 KEY2_INX) */ and let Oracle to select other index to retrieve the records.

Unlock the potential power of Oracle optimizer hints
Whether you are using solution oriented approach or problem bypassing approach, you need to know the details of the SQL’s query plan, data distribution, cost of each step, and try to predict what will be the final actual aggregated cost of the plan. Sometimes you have to use both techniques for complex SQL statements; you have to provide the best query steps for parts of the query plan and use “NO_*” to bypass those known issues in the query plan. It is a very complicated process and it is not easy to carry out by common SQL developers without in-depth knowledge of SQL tuning.
The following is an example shows a SQL that took 8.56 seconds to finish and the query plan looks normal. The SQL syntax is intact, so it is not much room for SQL rewrite to take place. May be parallel execution can help to improve the SQL statement, but which table or index should be used for parallel execution and how the new parallel execution steps are affecting the entire query plan. It is not an easy job even for an experienced SQL tuning expert. That is why the potential of Oracle optimizer hints is not be fully explored from the beginning.

With the help of a latest AI algorithm, a computer-searching engine can dramatically release user effort to discover the combination of Hints without going through a huge Hints permutation space. It makes Hints SQL tuning become easier and can solve more problems than you expected.
The following solution shows a series of hints combination that tell Oracle not to use EMPLOYEES.EMPSS_GRADE_INX index and exclude Hash Join to join table DEPARTMENTS and then use parallel index scan of table EMPLOYEES. It makes a new query plan that runs 70% faster than original plan. The whole tuning process is finished without human intervention and the result is safe since it does not involve any syntax rewrite.


https://tosska.com/tosska-sql-tuning-expert-tse-oracle/

Tuning SQL without touching the its SQL text
If you are a packaged application user, you might be wondering how you can tune your SQL if you can’t edit a query directly. Or, if you are an application developer, you want to have a quick fix on SQL performance without the time to go thought source code change and unit test. There are multiple features provided by Oracle such SQL Profiles, SQL Plan Baselines and SQL patch that you can use to tell Oracle to fix a SQL’s query plan without the need to change the SQL text. But the using of these features are limited by Hints injection only, you cannot rewrite a SQL with different syntax and ask the original SQL to accept a rewritten SQL’s query plan. So, hints-based SQL tuning is becoming more important in today’s rapidly changing database applications.
Actually, SQL performance should be detached from application source code, the SQL performance should be manageable to deploy and rollback anytime, anywhere. It should also be tailorable for one source SQL code to fit different sizes of databases. Hints-based SQL tuning will be the key to unlock the potential power of SQL performance management for Oracle databases and it is becoming more important in modern database SQL tuning process.

Tosska Technologies announces the availability of their flagship SQL performance tuning product – Tosska SQL Tuning Expert (TSE™) for Oracle®

An innovative machine tuning tool for SQL statements

Dec. 18, 2017

Hong Kong, December 18, 2017 – Tosska Technologies Limited (Tosska), an IT company that provides database and SQL performance related tools, today announces the general availability of Tosska SQL Tuning Expert (TSE™), their first product that provide machine tuning capability to improve SQL performance for Oracle®.

SQL Tuning is a classical problem for every DBAs. Tuning SQL statements is finding the fastest route to execute the SQL statements. In order to tune a SQL statement, you are required to know your database architecture and have in-depth knowledge of SQL tuning skill.

“There are a lot of SQL tuning tips in the market, but most of them are good only in specific database environment. You need to try those tips one by one manually on your databases. It is not only time-consuming, but there is also no guarantee that you will find the best performance solution for your SQL statements,” said KaMing Ng, CEO of Tosska. “It may be up to days or weeks for a DBA or experienced SQL developer to tune a problematic SQL statement. SQL tuning is a very skillful job that not many developers are able to carry out in an enterprise. Should such expensive and valuable time be saved and used for other even more productive tasks inside a company? That’s why we designed TSE to solve this problem.”

“The design concept behind TSE™ is to provide a machine tuning tool that optimizes SQL statements without the need of user’s involvement. Users just need to input their problematic SQL statements into the tool and press a button and then the tool will take care the rest. Users don’t have to do analysis, guessing or testing during the entire SQL tuning process,” said Richard To, CTO of Tosska. “TSE™ achieves this by using a proprietary technology with an embedded Artificial Intelligent (AI) engine invented by Tosska to mimic a human expert SQL tuning process, in which the engine tries every possible effective Oracle Hints combinations for a SQL statement to improve the execution speed within the given quota. As the permutation of Oracle Hints combinations to a SQL statement is so huge, it is impossible for a human expert to accomplish it for complex SQL statements.”

TSE™ is now available for free download from Tosska’s website. Please visit Tosska’s website www.tosska.com for details.

   
About Tosska
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 enquire@tosska.com.

Press and Media Inquiries
KaMing Ng
Chief Executive Officer
Tosska Technologies Limited
Phone: +852-28248420
Email: enquire@tosska.com

All Trademarks mentioned on this Site are the property of their respective owners.

Why use Oracle In-Memory database from another perspective

A lot of people are talking about why or why not use Oracle In-memory database in their applications and most of them are too focused on the size of the database or whether it is an OLAP application. It seems that small and medium size databases are not suitable for using Oracle In-memory database option. But if your OLTP databases are suffering from performance bottleneck and you are looking for solutions, I think Oracle In-Memory database option should be on your solutions list, especially when you are planning to upgrade your hardware.

The SQL optimizer plan space is getting bigger in Oracle In-Memory database
Plan space is the size of potential process methods that database SQL optimizer will consider before processing your input SQL statements. A bigger plan space size means that database SQL optimizer will consider more potential methods to process your SQL statements. So, your SQL statements have more chances to run faster. With Oracle’s In-Memory database new In-Memory data access methods, Oracle SQL optimizer will consider a bigger plan space before the execution of your SQL statement. Due to the multiplier effect, a new In-Memory execution plan step might result in tremendous huge expansion of plan space size. For example, a SQL statement with 5 tables in In-Memory, a new “Table Access Inmemory Full” step to each table’s access method, the result is C(5,1)+C(5,2)+C(5,3)+C(5,4)+C(5,5)=5+10+10+5+1=31. But don’t forget this multiplier is applicable to every single execution plan in original plan space tree, it means the new plan space size might be potentially 31 times bigger than the original plan space.

How OLTP database get advantage from In-Memory database?
An OLTP database is a transaction-oriented application which requires quick response time for each transaction, but it doesn’t mean that there is no complex SQL for online report or data consolidation during business hours. If these kinds of slow SQL statements are running simultaneously with other online transactions, the overall performance will be affected. If hardware upgrade is one of the options, you should consider using Oracle In-Memory as one of the alternative solutions.
Here is an example that shows you how an OLTP SQL statement gets benefit from using Oracle In-Memory database option.
The following is a typical OLTP SQL with all tables analyzed, the original execution plan shows an Oracle adaptive plan that Hash Join or Nested Loop will be decided during the initial stage of execution. The elapsed time of this SQL is around 1 minute and 35 seconds.

Let’s put EMPLOYEE table into Oracle In-Memory with a force hint and Oracle use it with “Table Access Inmemory Full” scan, the following benchmark shows that the “Auto 1” SQL took only 0.45 second to finish with more than 200 times faster than the original execution plan. The solution indicates that the introduction of a new “Table Access Inmemory Full” scan to the EMPLOYEE table has actually created a much better execution plan compared with the original one.

There is no need to put all tables into In-Memory for OLTP’s SQL
When using an OLTP database, we don’t want to introduce too much overhead to the online transactions by populating all tables into In-Memory. So, the goal of In-Memory SQL tuning for OLTP SQL is not to select the best performance solution, but the most cost-effective solution with fewer tables In-Memory tables but still acceptable performance improvement instead.
The following “Auto 3” solution with all tables are put into In-Memory, but the improvement is just 0.01 second better than the “Auto 1” which only requires one table to be put into In-Memory. Therefore, it is obvious that “Auto 1” is a much more preferable choice.

Oracle should charge their In-Memory option by In-Memory size
Actually, Oracle In-Memory database is not only beneficial to OLAP users, but it is also a good SQL performance- enhancement tool for all database users like OLTP users. In view of high pricing of Oracle In-Memory database option which limits the rapid adaptation of this new technology, it is recommended that Oracle should decide the price according to the In-Memory size. In other words, a lower size with lower price will definitely help Oracle
In-Memory database to penetrate widely into all databases users, including OLTP users.

Author: Richard To (Richard.to@tosska.com), CTO of Tosska Technologies Limited

Tosska Launches the Tosska In-Memory Maestro (TIM™) for Oracle® v1.2.0 Beta Program

In-Memory SQL Optimization In-Memory Objects recommendation

Hong Kong, – June 30, 2017


Tosska Technologies Limited (Tosska) announced today the commencement of the v1.2.0 Beta program for their flagship product Tosska In-Memory Maestro (TIM™) for Oracle®. The latest version, TIM™ v1.2.0 will be replacing its previous version TIM™ v1.1.0 which was not designed to work with the latest Oracle® v12c R2 In-memory release.

“Oracle® v12c R2 is out in the market for a while now and many users have upgraded to this new release. However this new release has some significant differences in the way how some critical database and SQL performance information is released for tools like ours. In view if this, We have upgraded TIM™ from v1.1.0 to v1.2.0 to make it compatible will all previous and current Oracle® In-memory releases”, said the CTO of Tosska, Richard To. “The feedback so far is satisfactory and it is expected that the Beta program will be able to be completed within a month.” TIM™ v1.2.0 is expected to be GA by end of August.

Tosska In-Memory Maestro (TIM™) for Oracle® v1.1.0 officially released !

In-Memory SQL Optimization In-Memory Objects recommendation

Hong Kong, – June 1, 2017


I am excited to announce that Tosska has just released our first version of Tosska In-Memory Maestro (TIM™) for Oracle® v1.1.0, the ultimate tool to manage your powerful Oracle® In-Memory capabilities that every database administrator has been looking for !

Just go and download a free trial immediately to explore the fascinating features that TIM™ can offer you. Enjoy your test drive !