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 用复合索引代替单列索引的案例