STA,SAA的简单实验

STA
1.创建测试用户及表
SQL> grant dba to zel identified by zel;
Grant succeeded.
SQL> conn zel/zel
Connected.
SQL> create table t as select * from all_objects;


2.创建调整任务
SQL> declare
  2   v_task_name varchar2(100);
  3  v_sqltext clob;
  4   begin
  5  v_sqltext:='select count(*) from t where object_name=''EMP''';
  6   v_task_name:=dbms_sqltune.create_tuning_task(
  7  sql_text=>v_sqltext,
  8  task_name=>'tuning_task'
  9     );
 10  end;
 11   /




PL/SQL procedure successfully completed.


3.执行任务
SQL> exec dbms_sqltune.execute_tuning_task('tuning_task');


PL/SQL procedure successfully completed.
4.查看任务
SQL> select task_name,status from user_advisor_tasks;




TASK_NAME       STATUS
------------------------------ -----------
tuning_task       COMPLETED




5.查看结果
SQL> set long 999999999
SQL> select dbms_sqltune.report_tuning_task('tuning_task') from dual;




DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_TASK')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : tuning_task
Tuning Task Owner  : zel
Workload Type   : Single SQL Statement
Scope   : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at   : 04/12/2016 23:31:40
Completed at   : 04/12/2016 23:31:41








DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_TASK')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: zel
SQL ID   : bafbkb1karrkm
SQL Text   : select count(*) from t where object_name='EMP'

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_TASK')
--------------------------------------------------------------------------------
  Table "zel"."T" was not analyzed.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => 'zel', tabname => 'T',
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =>
   'FOR ALL COLUMNS SIZE AUTO');

  Rationale
  ---------

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_TASK')
--------------------------------------------------------------------------------
    The optimizer requires up-to-date statistics for the table in order to
    select a good execution plan.

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: 99.7%)
  -----------------------------------------
  - Consider running the Access Advisor to improve the physical schema design

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_TASK')
--------------------------------------------------------------------------------
    or creating the recommended index.
    create index zel.IDX$$_00550001 on zel.T("OBJECT_NAME");

  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.

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_TASK')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_TASK')
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |  | 1 |    17 |   338   (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |  | 1 |    17 |       |  |
|*  2 |   TABLE ACCESS FULL| T  |    14 |   238 |   338   (1)| 00:00:05 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_NAME"='EMP')

2- Using New Indices

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_TASK')
--------------------------------------------------------------------------------
--------------------
Plan hash value: 1406225525

--------------------------------------------------------------------------------
----
| Id  | Operation  | Name   | Rows  | Bytes | Cost (%CPU)| Time
   |
--------------------------------------------------------------------------------
----
|   0 | SELECT STATEMENT  |   | 1 | 17 | 1   (0)| 00:00:
01 |

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_TASK')
--------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE   |   | 1 | 17 | |
   |
|*  2 |   INDEX RANGE SCAN| IDX$$_00550001 | 1 | 17 | 1   (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_NAME"='EMP')

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_TASK')
--------------------------------------------------------------------------------

6.删除任务
SQL> exec dbms_sqltune.drop_tuning_task('tuning_task');

PL/SQL procedure successfully completed.

SQL> select task_name,status from user_advisor_tasks;

no rows selected

SAA

1.创建测试表、创建索引、对表进行分析
SQL> create table scott.t5 tablespace users as select rownum id from dual connect by rownum<=100000;

Table created.

SQL> create index scott.idx_t5_id on scott.t5(id) tablespace users;

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'T5');

PL/SQL procedure successfully completed.

2.创建任务，使用QUICK_TUNE过程，分析单条SQL,本SQL理论上是应该走索引的，我们使用hint，强制让其走全表扫描，测试SQL Access Advisor给出的建议
SQL> DECLARE
  2  v_task_name VARCHAR2(30);
  3  v_sql varchar2(200);
  4   BEGIN
  5   v_task_name := 'MY_TASK_ACCESS_ADVISOR';
  6  v_sql:='select /*+ full(t) */ * from scott.t5 where id=:1';
  7  DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR, v_task_name,v_sql);
  8   END;
  9  /


PL/SQL procedure successfully completed.

3.统计刚刚创建的任务存在一条，说明已经给出建议了，否则，count为0
SQL> select count(*) from user_advisor_actions WHERE task_name='MY_TASK_ACCESS_ADVISOR';


  COUNT(*)
----------
1

4. 查看建议
SQL> SET LONG 100000
SQL> SET PAGESIZE 5000
SQL> SELECT DBMS_ADVISOR.get_task_script('MY_TASK_ACCESS_ADVISOR') AS script FROM dual;


SCRIPT
--------------------------------------------------------------------------------
Rem  SQL Access Advisor: Version 11.2.0.4.0 - Production
Rem
Rem  Username:       zel
Rem  Task:      MY_TASK_ACCESS_ADVISOR
Rem  Execution date:
Rem


/* RETAIN INDEX "SCOTT"."IDX_T5_ID" */

 SQL Tuning Advisor (STA) 优化SQL语句：http://blog.csdn.net/tianlesoftware/article/details/5630888
 Oracle SQL Access Advisor 说明:http://blog.csdn.net/tianlesoftware/article/details/7223180

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30604784/viewspace-2080153/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30604784/viewspace-2080153/