SQL中涉及到的对象未收集统计信息。 STA给出收集统计的建议

创建测试表

点击(此处)折叠或打开

scott@ORCL>create table tab_test as select * from dba_objects;
Table created.

执行待优化的SQL 添加一个注释 NO_STAT，将来好识别该SQL（看清楚，这不是HINT)

点击(此处)折叠或打开

scott@ORCL>select /* NO_STAT */ count(1) from tab_test;
COUNT(1)
----------
76508

STA 指定待优化sql 的方式有 1）直接输入SQL 2）该sql还在shared pool里话，通过 sql_id 3）如果在AWR历史数据中的话，也可以通过sql_id 4）从sqlset中获得 5）从sql性能分析器(SQL Performance Analyzer SPA)中获得
刚执行的sql，还在共享池中缓存，用sys用户，找到该sql的id

点击(此处)折叠或打开

scott@ORCL>conn /as sysdba
Connected.
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 /* NO_STAT */ count(1)%\' AND rownum<=1;
3 end;
4 /
PL/SQL procedure successfully completed.
sys@ORCL>print :sql_id;
SQL_ID
--------------------------------------------------------------------------------------------------------------------------------
5dfrhvqta7pqn
sys@ORCL>select sql_text from v$sql where sql_id = \'5dfrhvqta7pqn\';
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
select /* NO_STAT */ count(1) from tab_test

调用STA

点击(此处)折叠或打开

sys@ORCL>var task_name varchar2(50) -- 接受创建优化任务的任务名
sys@ORCL>begin
2 :task_name := dbms_sqltune.create_tuning_task(sql_id => :sql_id); -- 创建优化任务，返回任务名，用主机变量task_name接受
3 dbms_sqltune.execute_tuning_task (:task_name);
4 end;
5 /
PL/SQL procedure successfully completed.
sys@ORCL>print :task_name; -- 打印任务名
TASK_NAME
--------------------------------------------------------------------------------------------------------------------------------
TASK_596

查看优化建议

点击(此处)折叠或打开

sys@ORCL>select dbms_sqltune.report_tuning_task (:task_name) from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK(:TASK_NAME)
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_596
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 06/05/2014 12:00:11
Completed at : 06/05/2014 12:00:12
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 5dfrhvqta7pqn
SQL Text : select /* NO_STAT */ count(1) from tab_test
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table \"SCOTT\".\"TAB_TEST\" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table. -- 这里建议收集TAB_TEST表的统计信息
execute dbms_stats.gather_table_stats(ownname => \'SCOTT\', tabname => -- 收集语句都给力现成的
\'TAB_TEST\', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => \'FOR ALL COLUMNS SIZE AUTO\');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 2448592499
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 305 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TAB_TEST | 76650 | 305 (1)| 00:00:04 |
-----------------------------------------------------------------------

找STA的建议，执行搜集语句

点击(此处)折叠或打开

sys@ORCL>execute dbms_stats.gather_table_stats(ownname => \'SCOTT\', tabname => \'TAB_TEST\', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => \'FOR ALL COLUMNS SIZE AUTO\');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.30

STA 建议收集统计信息