HelloDBA [中文]
Search Internet Search HelloDBA
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

Introduce a SQL tuning tool

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2017-10-05 22:02:56

Share to  Twitter Facebook GMail Blogger Orkut Google Bookmarks Sina Weibo QQ Renren Tieba Kaixin Douban Taobao

When we tune a SQL, we need to spend a lot of time to analyze the SQL, find out the performance bottleneck first, then utilize the tuning rules or our own experience to take action to tune the SQL.

SQL Tuning is complex task. The junior developers or DBAs may even don't know from where to start when they are facing a poor performance SQL. It's also a time-consuming task for those experienced DBA.

I ever tuned a ton of SQLs, and I always hope I can get a SQL Tuning tool to help me find out the top SQL quickly, then analyzes the SQL automatically, and eventually gives me tuning suggestions. This will definitely take off those time-consuming steps from me, helps me to tune the SQL efficiently, and gives me more time to do other works.

Yes, SQLBooster is what I am looking for! You can download the latest version from this website:www.SQLFast.com

Download it, run setup, then you can start to use it.

You will be required to input a license key when you first run it. You can get a 30 day trail key for free! Please refer this website to know how to get it: Get A License Key (Click to View) 

This software does not have a beatiful UI, but its funcions are pretty good!

Please be aware, you will need two database user accounts to login: one for running the target SQL, the other for checking the performance statistics data, which will be able to access the fixed views and other system metadata (it's better be a DBA account). They could be the same account if it has the full privileges.This article tells you how to login database: Login Database (Click to View).

After login, you can use the View Current Sessions (Click to View) function to find out the long-running SQLs, or alternatively, you can also use Top SQL (Click to View) function to get the current/history top SQLs.

Spotted the poor performance SQL, you can parse the SQL structure, then analyze the SQL from the leaves to the root. The process is very simple, you just need to click the mouse a few times. You can refer to this article to learn how to do the SQL analyzing and tuning: How To Analyze and Tune a SQL (Click to View).

After the analyzing completed, you will be able to check the analyze result and identify the performance bottleneck. The analyzing was done from the leaves to the root, which means it was analyzing the lowest level subqueires first, then add the upper level query to be analyzed, and finally analyze the original whole SQL if necessary.  So, the first poor subquery in the analyze result is most likely be the performance bottleneck of the original SQL.

This screenshoot shows the analyze result. The SQL structure is located in the top-left; when you click one of the node of the SQL tree, the constructured SQL will be shown in the top-left side. The bottom side shows the analyze result. Click+click the "Extra Info" column, you will get some tuning suggestions. You may adapt these suggestions or use your own experience to tune the subquery. 

语句分析结果

After you implemented tuning actions to the target subquery, for example, created index, added SQL hint or restructured the SQL, you can test the SQL. If the result is satisfying, you can apply these actions to the origianl SQL.

That is how this tool help you to tune a SQL with much less manul works. It did a lot of jobs automatically for you, helped you to tune SQL more efficiently! Even a newbie can use it to tune a complex SQL independently!

Besides, this software also provides some other attractive functions, such as Top Event (Click to View)Session Lock Blocking Tree (Click to View)Tuning SQL with SQL Profile (Click to View) ect.

Top

Copyright ©2005, HelloDBA.Com All reseverd.

Declaration
by fuyuncat