SQLT不但能收集SQL最佳化資訊，還能呼叫 SQL Tuning Advisor （STA）給出最佳化建議。

下面是一個 SQLT 診斷SQL後，推薦用一個複合索引來代替單列索引的案例。

首先，建立測試表tab_test ，並且在 object_type 列上建立一個單列索引。接著收集該表的統計資訊。

點選(此處)摺疊或開啟

scott@ORCL>create table tab_test as select * from dba_objects;
Table created.
scott@ORCL>create index ind_test_type on tab_test(object_type);
Index created.
scott@ORCL>analyze table tab_test compute statistics;
Table analyzed.
scott@ORCL>analyze table tab_test compute statistics for all indexed columns;
Table analyzed.

需要最佳化的SQL出場：

點選(此處)摺疊或開啟

select /* index_replace */ count(*) from tab_test where owner = \'SYSTEM\' and object_type = \'SYNONYM\';

scott@ORCL>select /* index_replace */ count(*) from tab_test where owner = 'SYSTEM' and object_type = 'SYNONYM';
我們簡單分析一下。這裡有兩個過濾條件。 owner = 'SYSTEM' 和 object_type = 'SYNONYM'。兩個條件分別過濾的行數和合在一起過濾的行數對比。

點選(此處)摺疊或開啟

scott@ORCL>select count(*) from tab_test where owner = \'SYSTEM\'; -- 用 owner = 'SYSTEM' 過濾，有 570行
COUNT(*)
----------
570
scott@ORCL>select count(*) from tab_test where object_type = \'SYNONYM\'; -- 用 object_type = 'SYNONYM' 過濾，有28394行
COUNT(*)
----------
28394
scott@ORCL>select /* index_replace */ count(*) from tab_test where owner = \'SYSTEM\' and object_type = \'SYNONYM\'; -- 兩個條件一起過濾，只剩下 8行
COUNT(*)
----------
8

從上面的資料過濾對比來看，建立一個owner和object_type 複合索引，可以更大限度的塞選資料。而且就這SQL來說，owner = 'SYSTEM' 比 object_type = 'SYNONYM' 選擇性更好，所以所首選項應該選owner列。（這裡的選擇性更好，是指的用owner = 'SYSTEM' 就剩下 570行比 object_type = 'SYNONYM' 濾出來的28394行記錄數更少。

分析到此完畢。我用SQLT來做最佳化建議

先找到待最佳化 SQL的 sqlid

點選(此處)摺疊或開啟

sys@ORCL>var sql_id varchar2(50)
sys@ORCL>begin
2 select sql_id into :sql_id from v$sql where sql_text like \'select /* index_replace */ count(*) %\' and rownum = 1;
3 end;
4 /
PL/SQL procedure successfully completed.
sys@ORCL>print :sql_id;
SQL_ID
--------------------------------------------------------------------------------------------------------------------------------
6dnqjw2dgvnms
sys@ORCL>select sql_text from v$sql where sql_id = \'6dnqjw2dgvnms\';
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
select /* index_replace */ count(*) from tab_test where owner = \'SYSTEM\' and object_type = \'SYNONYM\'

呼叫SQLT的sqltxtract 方法在 sqlt/run/sqltxtract.sql

6dnqjw2dgvnms 是要最佳化sql的 sqlid
oracle是SQLTXPLAN使用者的口令

點選(此處)摺疊或開啟

sys@ORCL>@/home/oracle/sqlt/run/sqltxtract.sql 6dnqjw2dgvnms oracle

操作成功後，得到 SQLT 報告的 zip包 sqlt_s89762_xtract_6dnqjw2dgvnms.zip 在登入sqlplus時，當前的系統目錄下。
開啟sqlt_s89762_main.html檔案

點STA Report

點選(此處)摺疊或開啟

FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 13.8%)
-----------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => \'sqlt_s89762_mem\',
task_owner => \'SYS\', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .006684 .005851 12.46 %
CPU Time (s): .006698 .005899 11.92 %
User I/O Time (s): 0 0
Buffer Gets: 1094 943 13.8 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 98.75%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index. If you choose to create the
recommended index, consider dropping the index \"SCOTT\".\"IND_TEST_TYPE\"
because it is a prefix of the recommended index.
create index SCOTT.IDX$$_02820001 on SCOTT.TAB_TEST(\"OBJECT_TYPE\",\"OWNER\");
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run \"Access Advisor\"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.

STA給了兩個建議： 1，建立 sql profile
execute dbms_sqltune.accept_sql_profile(task_name => \'sqlt_s89762_mem\', task_owner => \'SYS\', replace => TRUE);

2，建立複合索引，並且刪除單列索引
create index SCOTT.IDX$$_02820001 on SCOTT.TAB_TEST(\"OBJECT_TYPE\",\"OWNER\");
consider dropping the index \"SCOTT\".\"IND_TEST_TYPE\"

調整過後的，執行計劃對比

點選(此處)摺疊或開啟

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 80 |
| 1 | SORT AGGREGATE | | 1 | 28 | |
|* 2 | TABLE ACCESS BY INDEX ROWID| TAB_TEST | 1 | 28 | 80 |
|* 3 | INDEX RANGE SCAN | IND_TEST_TYPE | 1700 | | 5 |
------------------------------------------------------------------------------
使用複合索引
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 1 |
| 1 | SORT AGGREGATE | | 1 | 28 | |
|* 2 | INDEX RANGE SCAN| IDX$$_02820001 | 1 | 28 | 1 |

SQLT 最佳化SQL 用複合索引代替單列索引的案例

相關文章