Analyzing the Inner Works of MySQL SQL Performance Tuning and Oracle Database

When it comes to MySQL, slow performance in large tables is one of the main sources of complaints. It is true that some users face problems as their database fails to sufficiently handle a more than a certain number of rows.

However, there also are many corporations that use MySQL for millions, even billions of rows of data and yet they successfully deliver excellent promise. So, why is there a contradiction between these two cases? The answer lies in understanding the intricacies of table designing in MySQL, and with the help of MySQL SQL performance tuning, how to make them work in your favour.    

What to Consider During MySQL SQL Performance Tuning

There are three major aspects of the database that can have an impact on databases with huge amounts of data. Let’s take a look at two of them now:

Buffers

The first thing to consider with any database management system is that you must have an estimate of the memory, even as you progress in terms of data accumulation. It is important for the memory to be sufficient because performance suffers greatly if it isn’t, so don’t be surprised if a drop in performance is greater than you anticipated because you may have lost track of the growth in data size and subsequently, the need for more memory space. This applies to the other aspects covered in this blog as well. Once data outgrows the memory, everything can be expected to slow down, and MySQL database and SQL becomes a necessity.

One way to ensure the memory remains sufficient for your data is to practice data partitioning. In this process, old data that is no longer required as often as recent data, is separated and stored in other servers. There are various other ways of ensuring sufficient space which we will talk about in another blog.

Indices

Indices, or indexes, are known by most of us to be a useful tool in improving the accessing speed of the database. An important thing to remember is that their usefulness depends a lot on exclusivity, i.e., the ability to select a number of rows that match with specific index ranges or values. Also, the nature of the workload – specifically whether it is cached or not – determines how much it will benefit from the use of an index.

This is actually overlooked by even MySQL optimizer at present and may need to be checked by other MySQL SQL performance tuning tools. Workload indices have a chance of much quicker access even if the size of the data being accessed is as large as fifty percent of the entire number of rows, as long as they are in-memory. On the other hand, for disk IO bound access, you may have greater success in fetching data through a full table scan irrespective of the number of rows you are requesting access to.

Since indices can differ from each other in many ways, they need to be used differently in order to effectively use them. For instance, you can place them either in a well-organized manner or at random spots, resulting in significant changes in their speed. Innodb also includes clustered keys which work by merging data and index access – such keys end up conserving IO that will prove invaluable for workloads that are entirely disk-bound.

In Conclusion

Designing table structures smartly involves taking into consideration all the abilities and disabilities of MySQL. This is especially important if you have to handle different kinds of databases in your organization.

The main reason why your organization has different databases in the first place is because of their different capabilities and shortcomings. So, the same design concepts won’t bring the same results in say, MS SQL or Oracle that they did in MySQL and vice versa. The same is true for their storage engines – each can have a different effect on the performance.

Once you have applied the right application architecture to plan your tables, you will be able to create applications that can easily handle huge data sets on the basis of MySQL.

Proper MySQL SQL performance tuning involves optimizations that can greatly boost the rate at which indices are accessed or scanned. There already are tools by Tosska Technologies Limited for this purpose like Tosska SQL Tuning Expert (TSEM™) for MySQL which you can download and start using today. Contact our team for further information or enquiries.

Improve Performance Tuning in SQL MySQL Through Multiple Parameters

MySQL tuning is no trivial task – it takes some work. However, Database Administrators know that there are a few parameters through which they can greatly enhance the speed and output of the database.

If you are in search of ways to improve performance tuning in sql MySQL , you will find some of the best in this blog. Each of the parameters mentioned here contain important settings that you can make changes to without much effort. Keep in mind that default values may vary according to the version of MySQL on your system. 

Performance Tuning in SQL MySQL: Main Categories

Here are the three major types of performance tuning in SQL MySQL, one of which DBAs usually focus on:

  • Hardware-based performance tuning
  • Tuning through Optimum techniques and practices 
  • Workload-based tuning

Hardware-based Performance Tuning in MySQL

Certain variables can be set according to the hardware specifications of your device. These include:

innodb_flush_log_at_trx_commit

For maximum durability, set it to “1”. If performance is your main concern, adjust this value to either “2” or “0”. However, doing so will result in lesser durability than if the value is set to “1”. 

innodb_flush_method

If you want to improve MySQL database performance by preventing double buffering, make sure this setting is at O_DIRECT.

innodb_buffer_pool_size

This size parameter is typically set within 50 to 70 percent of the overall RAM. You can proceed with tuning by checking on the buffer pool usage from time to time using a monitoring tool. 

innodb_log_file_size

The size of the file log is usually set in the 128M – 2G range. It is supposed to be sufficiently spacious to store approximately sixty minutes of logs and enable MySQL to flush processes, place checkpoints, and reorganize writes for sequential I/O. Again, refer to a tuning tool like Tosska SQL Tuning Expert (TSEM™) for MySQL® for further insight on whether or not the log file size needs to be adjusted. 

Tuning through Optimum Techniques and Practices

This category involves using the best MySQL practices for performance tuning in SQL MySQL: 

innodb_file_per_table

Keep this at “ON” in order to ensure a separate InnoDB table space for each table present in the database. 

innodb_stats_on_metadata

Don’t want database statistics to update constantly, and consequently, slow down read speeds? Ensure this setting is turned off, in that case. 

innodb_buffer_pool_instances

The recommended value for this is “8”. On the other hand, if the buffer pool size is less than 1G, then set it to “1”.

query_cache_type & query_cache_size

Disabling the query cache is considered useful in improving MySQL database performance. You can disable it by setting both query_cache_type and query_cache_size to zero.

Workload-based Performance Tuning for MySQL

This kind of performance tuning in SQL MySQL is relative; it depends on the workload, which is why additional details regarding the specific workload are needed. Thankfully, gathering such information is much more convenient, thanks to reliable MySQL graphing and tuning tools like Tosska SQL Tuning Expert (TSEM™). Tosska’s tools are designed to display an extensive range of metrics and give users insights and allocate resources accordingly. 

Experts suggest making changes to the innodb_buffer_pool_size parameter first. Consider the following metrics to decide whether this setting has to be raised or lowered – 

  • Your device’s RAM
  • Buffer pool size
  • The number of free pages available

Once this is done, you can improve MySQL database performance be observing the InnoDB Log File usage metrics; as mentioned already, the log file settings are generally adjusted in order to store around an hour of log data. If the data written exceeds the originally set capacity, then this setting has to be increased and MySQL rebooted. The query “Show engine innodb status” is useful in assessing what size will be ideal for the InnoDB log file.

If it starts to get burdensome, you can rely on Tosska’s tuning tools for MySQL. Visit our website for our top-of-the-line tools and to get in touch with our experts to know more about them!

Oracle Database and SQL: Tips for MySQL Database Performance Tuning

Until now we have learned a lot about Oracle database and SQL performance tuning. Now, let’s discuss MySQL database performance tuning. Just like any other relational database, MySQL could also be a nightmare to many. It can crawl to a halt at a moment’s notice and in the next moment, you will find it leaving your apps in the lurch and your business on the line. The fact is, regular errors underlie most MySQL performance issues. 

To ensure your MySQL server bustles along at great speed, providing consistent and stable performance, it’s imperative to eradicate such mistakes that are often confused by some subtlety in your configuration trap or workload. Just like any other performance tuning tips for Oracle database and SQL, MySQL too has some of its own performance tuning techniques that we have described in this blog. Let’s get started without wasting more words-

Oracle Database and SQL- Know How to Tune MySQL Database Performance

Fortunately, many MySQL performance problems have similar solutions which make both tuning MySQL and troubleshooting a manageable task. 

Here are a few tips for getting great performance out of MySQL.

Tip 1# Profile Your Workload

The best way you can understand how your server invests its time is to profile its workload. By doing so you could expose the most expensive queries for further tuning. When you issue a query against the server, you don’t have to care much about anything else except how quickly it completes. Therefore, time is the most crucial metric to consider here. 

The most significant way of profiling your workload is using a tool such as MySQL Enterprise Monitor’s query analyzer. Such a tool is meant to capture queries that are executed by the server. Furthermore, it returns a table of tasks sorted by decreasing order of response time, quickly bubbling up the most time-consuming and expensive jobs to the top so that you can find where you need to put your efforts. Workload-profiling tools group identical queries together that allow you to find slow queries as well as the queries that are fast but executed multiple times. 

Tip 2# Recognize the Four Basic Resources

For functioning, a database server requires four basic resources- Network, CPU, Disk, and memory. If any of these resources are weak, overloaded, or erratic, then the database server is most likely to perform badly. Recognizing these fundamental resources is crucial in two specific areas: selecting troubleshooting and hardware issues. 

While you choose hardware for MySQL, you must ensure the components perform well all around. Just as essential, ensure to balance them reasonably well against each other. Often, businesses choose servers having fast CPUs and disks but that are starved for memory. In some cases, adding memory is a cheap way of enhancing performance by order of magnitude, specifically on workloads that are disk-bound. This might seem counterintuitive, but in various cases, disks are overused as there isn’t sufficient memory to hold the server’s working set of data. 

Another good example of this perspective relates to CPUs. In most conditions, MySQL performs well with fast CPUs as every query runs in a single thread and can’t be parallelized across CPUs. 

During troubleshooting, examine the performance and utilization of all the four resources with much care and determine whether they are performing badly or are simply being asked to execute excessive work. This knowledge can help solve issues instantly. 

Tip 3# Don’t use MySQL as a Queue

Queues and queue-like access patterns can sneak into your application without even letting you know. For instance, if you set the status of an item so that a specific work process can claim it before working on it, then you are ignorantly formulating a queue. Marking emails as unsent, sending them and then remarking them as sent in a common example. 

Queues create problems for two big reasons: they serialize your workload which prevents tasks from being done in parallel, and they mostly result in a table that includes work in process as well as earlier data from jobs that were executed long ago. Both add concealment to the application and load to MySQL. 

Tip 4# Firstly Filter Results by the Cheapest 

The best way of optimizing MySQL is to do imprecise, cheap work first, then the precise and hard work on the smaller that will result in a set of data. 

For instance, consider you are searching for something within an assigned radius of a geographical point. The initial tool in many programmers’ toolbox is the great-circle (Haversine) formula. This will compute the distance along the sphere’s surface. The trouble with this technique is that the formula needs a variety of operations related to trigonometry, which are very intensive toward the CPU. Great-circle calculations tend to perform slowly and make the machine’s CPU utilization skyrocket. 

Before you implement the great-circle formula, cut down your records to a small subset of the total, and scrape the resulting set to a precise circle. A square that comprises the circle either precisely or precisely is a simple way of doing this. That way, the world outside the square never gets hit with all those expensive trig functions. 

Tip 5# Understand the Two Scalability Death Traps

Scalability isn’t as fuzzy as you may think. In fact, there are explicit mathematical definitions of scalability that are defined as equations. Such equations emphasize why systems don’t scale and why they should. Consider the Universal Scalability Law, a definition that is convenient in expressing and quantifying a system’s scalability qualities. It explains scaling issues in terms of two elementary costs: crosstalk and serialization. 

Parallel processes that must hold for something serialized to take place are inherently limited in their scalability. Similarly, if such processes are required to chat with each other all the time for coordinating their work, they restrict each other. If you avoid crosstalk and serialization, your application could scale much better.

SQL Plan Management Oracle – All You Need to Know About

While upgrading the Oracle database or making any changes in the system parameters, you might have noticed that some SQLs’ performance or queries get highly regressed. If this happens, then don’t worry it’s quite obvious and will always happen whenever there are any changes in the plan. But you can prevent this regression if you use SQL Plan Management Oracle.

In case you aren’t aware of what it is and how it can help, then this blog is surely meant for you. In this blog, we are going to cover every basic aspect related to the SQL plan management and that how it will help in preventing query or performance regression.

An Overview of SQL Plan Management Oracle

SQL Plan Management is a deterrent tool that allows the optimizer to manage SQL execution plans automatically while ensuring that only verified and known plans are used by the database.

SQL plan management uses a mechanism that lets the optimizer to use it for a SQL statement. This mechanism is known as a SQL Plan baseline. It’s a set of accepted plans. A plan is typically known to hold all plan-related information such as a set of hints, SQL plan identifier, optimizer environment, and bind values.

The optimizer uses this information for reproducing an execution plan. Commonly, the database accepts a plan into the plan baseline only after it verifies and confirms that the plan performs absolutely well.

In short, a SQL Plan management Oracle is a tool used for mitigating the risk of query regression when you upgrade to Oracle Database11g or 12c.

Key Components of SQL Plan Management

Mainly, there are three essential elements of SQL Plan management. They are as follows:

  • SQL Plan Baseline Capture

The component creates a SQL plan baselines that describes the accepted or trusted execution plan for all relevant SQL statements. If you aren’t sure where you can find the SQL plan baselines, then let us tell you that you can find the plan baselines stored in a plan history in the SQL Management Base. The management base will be found in the SYSAUX tablespace.

  • SQL Plan Baseline Selection

SQL Plan baseline selection makes sure that the tool uses only the accepted execution plans for statements that have a SQL plan baseline. Furthermore, it ensures that it tracks every new execution plan in the plan history for a statement. The plan history comprises both unaccepted and accepted plans. An unaccepted plan can either be rejected (verified but not performant) or unverified (newly found but not verified).

  • SQL Plan Baseline Evolution

This component is meant to assess every unverified execution plan for a given statement in the plan history for either to be accepted or rejected.

What’s the Main Purpose of SQL Plan Management?

SQL plan management oracle restricts performance or query regression due to any plan changes in the database. Secondly, this tool aims at gracefully adapting to changes.

It must be noted that if an event has caused any irreversible execution plan changes such as dropping an index, SQL plan baseline cannot help in this case.

Advantages of SQL Plan Management Oracle

SQL plan management can preserve or improve SQL performance in database systems and upgrades and data changes.

Some more specific benefits comprise the following-

  • When a database upgrade causes the installation of a new optimizer version, it usually results in plan changes for a small part of SQL statements. Due to plan changes, either the performance of the system improves or there isn’t any change literally. However, in some cases, plan changes result in performance regression and here’s where SQL plan baselines come into play. It significantly lessens potential regressions that result from an upgrade.
  • Ongoing data and system changes can have an impact on plans for some SQL statements potentially creating performance regressions. Plan baselines assist in reducing this performance regression. Further, it stabilizes the SQL performance of the system as well as the database.
  • Employing new application modules brings in new SQL statements into the database. The application software is likely to use the proper SQL execution plan that’s developed in a standard test configuration for the new statements. In case the system configuration differs from the test configuration, then the database can develop SQL plan baselines over time to produce better performance.

In the Bottom Line

During an automated or manual updating of statistics for some objects or while changing some parameters related to the optimizer, or any changes made in the system cause a drastic change in the execution plan. Even more dramatic change is noticed while a database is upgraded. While most plans lead to improvement as they are made to adapt to the new system environment, there are some that lead to performance regression as well. In such cases, we need a SQL plan management mechanism whose main work is to reduce the regression risk.

If you are stuck with long queries or if your system’s performance has reduced, you can get a SQL query optimization tool online. Tosska Technologies Limited is one such company that provides solutions and tools related to database and SQL related performance optimization and improvements. We use advanced technologies like AI (artificial intelligence) so that our tool can help you solve numerous tasks at a time.

Improve Oracle Database Performance Tuning with Tosska’s AI-Enabled Tools

improve oracle database performance tuning

Oracle database performance tuning is a crucial method for accelerating the application function and data retrieval process. In order to speed up the performance of the application, the developer or the database administrator has to expedite query response time. This implies that they must have a deep understanding of how well the database is organized and how it satisfies its purpose.

Generally, SQL tuning means minimizing the query plan steps which in turn decreases the wait time and time cost. There are a number of ways you can implement to performance tune your Oracle database. In most of the techniques, you would require changing the source code. However, at Tosska Technologies, you will get products that are AI-based and don’t require touching the source code. Let us introduce you to our amazing products that are extremely helpful in Oracle database performance tuning.

3 Brilliant Tools Offered by Tosska for Improve Oracle Database Performance Tuning

Tosska provides solutions for SQL and database related performance optimizations and improvements. We are one of the very few companies in the world that uses artificial intelligence technology to solve different database performance problems. We help our customers to minimize their hardware investment and increase their level of database application service.

We have enlisted below some tools provided by Tosska Technologies that help to improve Oracle database performance successfully.

  1. Tosska SQL Tuning Expert (TSE™) for Oracle®

It’s an innovative machine or SQL tuning tool that upgrades your SQL statements without involving the user. By just point and click, the tool will help you with the ultimate SQL performance solution. The only thing you are required to do is to enter your problematic SQL statement into the product and press the button.

You are free from doing any testing, guessing or analyzing during the complete SQL tuning process. The enhanced SQL statement will get benchmarked with your original SQL statement side by side without any doubt. It isn’t just another tool but provides users with better statistics analysis, query plan visualization, and more.

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

One of the best parts of having this tool is that it will tune your Oracle database without touching your source code. Surprised? But, it’s true. Tosska SQL Tuning Expert Pro is a tool that helps in upgrading SQL performance without even touching the source code of the program. Besides this, users can even use various other performance query plans for different sizes of production databases without the need of keeping a number of versions of the program source. It is specially designed for package application users who don’t hold the source code of their applications.

With our SQL Tuning Expert Pro, SQL tuning gets even easier for application developers than ever before. You can improve the SQL statement without conducting any time-consuming test, program implementation, and integration test that are essential in the software development cycle. Apart from these brilliant SQL tuning attributes, strong indexes recommendation function is also given that assists users in reviewing and discovering more potential indexes that are extremely crucial but may be missing in the current database schema.

To be more specific, it’s an extremely brilliant and cost-aware index advice engine that lets users analyze their existing database schema- in case any new indexes are available that are helpful in enhancing an assigned SQL workload. The engine is capable of handling up to thousands of SQL statements and offers you a reasonable recommendation that even human experts are not able to accomplish.

3. Tosska In-Memory Maestro (TIM™) for Oracle®

Tosska In-Memory Maestro for Oracle is a tool that automates the process of optimizing In-memory SQL and offers recommendations for In-memory objects for a given SQL workload. Our proprietary artificial intelligence engine is highly beneficial in achieving the aforesaid. The tool also offers a user-friendly In-memory simulation function for users so that they can virtually estimate their in-memory table objects for one or more SQL workload captured from AWR or SGA without actually populating those table objects.

Final Words

Nowadays, most of the highly succeeding companies make use of artificial intelligence to upgrade themselves, then why not you too implement it! With our AI-enabled tool, you can improve oracle database performance easily and in less time. Even, in one of our tools, the source code of the program will remain untouched, yet the problem will be solved. The tools really don’t require the user’s involvement in fixing the queries. Therefore, if you are searching for any such product, then browse our products and get the one that suits you the best.

Tosska Technologies aims at helping users to smooth out the problem with the new and advanced technologies in the market. You should surely give it a try; we assure you will love the product offered by us.

Don’t Overlook Oracle Database and SQL Performance: Here’s why

oracle database and sql

Being a DBA is not always a fun job, thanks to certain time-consuming tasks that it entails. One of these is to ensure optimal Oracle database and SQL performance. Typically, it is done by spending a lot of time tuning the long list of SQL statements and software code in order to improve efficiency and enhance access. However, SQL is just one aspect that is related to the performance of database systems.  

Database Administrators also need to invest their time in enhancing the design, physical structure, and specifications of the database objects. These objects are the tables, indices, and the information stored over several files. In the case of data inefficiency, it becomes necessary to observe and modify the actual construction and composition of database objects on a consistent basis. This is because any amount of SQL performance tuning is bound to fall short in a database that is improperly organized or poorly constructed.  

Optimizing Oracle Database and SQL: 5 Important Techniques 

The DBA has to be aware of all the specifications that the database management systems consist of as this knowledge will enable them to use the right techniques to optimize database constructs.

A majority of the most popular DBMSs are compatible with all the methods we have mentioned below, though they may be used differently depending on the database. Let’s take a look:

  1. Indexing: An essential aspect of the Oracle database and SQL performance tuning process is by selecting the right indices and alternatives in order to enable efficient queries. 
  2. Clustering: This involves implementing the physical pattern of data on the disk so that it is clustered on the same page whenever accessed in a particular order. 
  3. Compressing: Data is compressed by decreasing storage requirements, thereby allowing more of it to be stored in a smaller amount of space. This also reduces storage expenses and enhances access if you can add a larger number of rows per page.
  4. Freeing Up Space: Assigning extra room for data growth allows new data to be added to its table easily without leaving the table disorganized.
  5. Partitioning: This entails the segregation of one database table into various sections that are saved in several files. This can be done in multiple ways; by partitioning one file in the same computer, partitioning using shared-disk clustering or by shared-nothing partitioning, depending on the DBMS in question.
  6. File Organizing and Placement: Allocating data from both – database systems and data files – to the correct places is a big step in organizing data and improving Oracle and SQL database performance.
  7. Checking the Page Size: The size of the block or the page determines how efficiently data can be stored and accessed, which is why it is vital to use the suitable page size. The smaller the size of the page, the fewer rows per page, which increases sequential data access requirements.  
  8. Interleaving: Merging all the data from several tables in a sequence into a file helps enhance join performance. However, this method seems to have become less popular than it used to be.
  9. Reorganizing Database Objects: Eliminating the defects from the database by reorganizing and arranging database objects is a well-used technique in SQL performance tuning. In fact, it enormously increases performance, especially if the data was previously fragmented, disorganized or scattered in some way.
  10. Denormalization: This method is considered as a last resort attempt in case the database is unable to perform optimally with a completely normalized implementation. This is because it differs from the logical design. 

All of these techniques are useful and should be considered when the DBA creates a plan for tuning and monitoring the database. Each aspect may not necessarily be applicable to every database object but it must be analyzed for its applicability all the same. Moreover, techniques that are not applicable during initial implementation may turn out to be useful as the application undergoes changes over time in various aspects like data volume, usage, and database characteristics.

Reveal the power of CBO

download free sql server

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®

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

download free sql server
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/

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

download free sql server

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.

Why use Oracle In-Memory database from another perspective

download free sql server

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