SQL Profile 如何使用

xiaui520

SQL> select * from v$version;　　

　　
BANNER
　　
----------------------------------------------------------------
　　
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
　　
PL/SQL Release 10.2.0.5.0 - Production
　　
CORE    10.2.0.5.0      Production
　　
TNS for Linux: Version 10.2.0.5.0 - Production
　　
NLSRTL Version 10.2.0.5.0 - Production
　　
SQL> grant dba to scott;
　　

　　
Grant succeeded.
　　
SQL> conn scott/tiger
　　
Connected.
　　
SQL> create table t1 (n number);
　　

　　
Table created.
　　

　　
插入数据
　　

　　
SQL> declare
　　
  2  begin
　　
  3  for i in 1..10000
　　
  4  loop
　　
  5  insert into t1 values(i);
　　
  6  commit;
　　
  7  end loop;
　　
  8  end;
　　
  9  /
　　

　　
PL/SQL procedure successfully completed.
　　

　　
SQL> select count(*) from t1;
　　

　　
  COUNT(*)
　　
----------
　　
     10000
　　

　　
SQL> create index idx_t1 on t1(n);
　　

　　
Index created.
　　

　　

　　
收集统计信息
　　

　　
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T1',method_opt=>'for all columns size 1',cascade=>true);
　　

　　
PL/SQL procedure successfully completed.
　　

　　
SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=1;
　　

　　
         N
　　
----------
　　
         1
　　

　　
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
　　

　　
PLAN_TABLE_OUTPUT
　　
--------------------------------------------------------------------------------
　　
SQL_ID  1kg76709mx29d, child number 0
　　
-------------------------------------
　　
select /*+ no_index(t1 idx_t1) */ * from t1 where n=1
　　

　　
Plan hash value: 3617692013
　　

　　
--------------------------------------------------------------------------
　　
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
　　
--------------------------------------------------------------------------
　　
|   0 | SELECT STATEMENT  |      |       |       |     6 (100)|          |
　　
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     3 |     6   (0)| 00:00:01 |
　　
--------------------------------------------------------------------------
　　

　　
Query Block Name / Object Alias (identified by operation id):
　　
-------------------------------------------------------------
　　

　　
   1 - SEL$1 / T1@SEL$1
　　

　　
Outline Data
　　
-------------
　　

　　
  /*+
　　

　　
PLAN_TABLE_OUTPUT
　　
--------------------------------------------------------------------------------
　　
      BEGIN_OUTLINE_DATA
　　
      IGNORE_OPTIM_EMBEDDED_HINTS
　　
      OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
　　
      ALL_ROWS
　　
      OUTLINE_LEAF(@"SEL$1")
　　
      FULL(@"SEL$1" "T1"@"SEL$1")
　　
      END_OUTLINE_DATA
　　
  */
　　

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

　　
PLAN_TABLE_OUTPUT
　　
--------------------------------------------------------------------------------
　　

　　
   1 - filter("N"=1)
　　

　　
Column Projection Information (identified by operation id):
　　
-----------------------------------------------------------
　　

　　
   1 - "N"[NUMBER,22]
　　

　　

　　
41 rows selected.
　　
针对上述SQL创建一个Automatic类型的SQL Profile的自动调整任务：
　　
SQL> declare
　　
  2  my_task_name varchar2(30);
　　
  3  my_sqltext clob;
　　
  4  begin
　　
  5  my_sqltext :='select /*+ no_index(t1 idx_t1) */ * from t1 where n=1';
　　
  6  my_task_name := dbms_sqltune.create_tuning_task(
　　
  7  sql_text => my_sqltext,
　　
  8  user_name => 'SCOTT',
　　
  9  scope => 'COMPREHENSIVE',
　　
10  time_limit => 60,
　　
11  task_name => 'my_sql_tuning_task_2',
　　
12  description => 'Task to tune a query on table t1');
　　
13  end;
　　
14  /
　　

　　
PL/SQL procedure successfully completed.
　　

　　

　　
执行上述自动调整任务
　　

　　
SQL> begin
　　
  2  dbms_sqltune.execute_tuning_task( task_name => 'my_sql_tuning_task_2');
　　
  3  end;
　　
  4  /
　　

　　
PL/SQL procedure successfully completed.
　　

　　

　　
查看刚刚运行的自动调整任务的调整结果：
　　

　　
SQL> set long 9000
　　
SQL> set longchunksize 1000
　　
SQL> set lines 800
　　
SQL> select dbms_sqltune.report_tuning_task('my_sql_tuning_task_2') from dual;
　　

　　
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
　　
-------------------------------------------------------------------------------
　　
GENERAL INFORMATION SECTION
　　
-------------------------------------------------------------------------------
　　
Tuning Task Name                  : my_sql_tuning_task_2
　　
Tuning Task Owner                 : SCOTT
　　
Scope                             : COMPREHENSIVE
　　
Time Limit(seconds)               : 60
　　
Completion Status                 : COMPLETED
　　
Started at                        : 07/12/2015 04:34:15
　　
Completed at                      : 07/12/2015 04:34:16
　　
Number of SQL Profile Findings    : 1
　　

　　
-------------------------------------------------------------------------------
　　
Schema Name: SCOTT
　　
SQL ID     : 4bh6sn1zvpgq7
　　
SQL Text   : select /*+ no_index(t1 idx_t1) */ * from t1 where n=1
　　

　　
-------------------------------------------------------------------------------
　　
FINDINGS SECTION (1 finding)
　　
-------------------------------------------------------------------------------
　　

　　
1- SQL Profile Finding (see explain plans section below)
　　
--------------------------------------------------------
　　

　　
  A potentially better execution plan was found for this statement.
　　

　　
  Recommendation (estimated benefit: 83.98%)
　　
  ------------------------------------------
　　
  - Consider accepting the recommended SQL profile.
　　
    execute dbms_sqltune.accept_sql_profile(task_name =>
　　
            'my_sql_tuning_task_2', replace => TRUE);
　　

　　
-------------------------------------------------------------------------------
　　
EXPLAIN PLANS SECTION
　　
-------------------------------------------------------------------------------
　　

　　
1- Original With Adjusted Cost
　　
------------------------------
　　
Plan hash value: 3617692013
　　

　　
--------------------------------------------------------------------------
　　
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
　　
--------------------------------------------------------------------------
　　
|   0 | SELECT STATEMENT  |      |     1 |     3 |     6   (0)| 00:00:01 |
　　
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     3 |     6   (0)| 00:00:01 |
　　
--------------------------------------------------------------------------
　　

　　

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

　　
   1 - filter("N"=1)
　　

　　
2- Using SQL Profile
　　
--------------------
　　
Plan hash value: 1369807930
　　

　　
---------------------------------------------------------------------------
　　
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
　　
---------------------------------------------------------------------------
　　
|   0 | SELECT STATEMENT |        |     1 |     3 |     1   (0)| 00:00:01 |
　　
|*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     3 |     1   (0)| 00:00:01 |
　　
---------------------------------------------------------------------------
　　

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

　　
   1 - access("N"=1)
　　

　　
-------------------------------------------------------------------------------
　　

　　
按照上面提示，输入下面的命令，接收这个SQL Profile：
　　

　　
SQL> execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2', replace => TRUE);
　　

　　
PL/SQL procedure successfully completed.
　　
SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=1;
　　

　　
         N
　　
----------
　　
         1
　　

　　
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
　　

　　
PLAN_TABLE_OUTPUT
　　
-------------------------------------------------------------------------------
　　
SQL_ID  1kg76709mx29d, child number 1
　　
-------------------------------------
　　
select /*+ no_index(t1 idx_t1) */ * from t1 where n=1
　　

　　
Plan hash value: 1369807930
　　

　　
---------------------------------------------------------------------------
　　
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
　　
---------------------------------------------------------------------------
　　
|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          |
　　
|*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     3 |     1   (0)| 00:00:01 |
　　
---------------------------------------------------------------------------
　　

　　
Query Block Name / Object Alias (identified by operation id):
　　
-------------------------------------------------------------
　　

　　
   1 - SEL$1 / T1@SEL$1
　　

　　
Outline Data
　　
-------------
　　

　　
  /*+
　　
      BEGIN_OUTLINE_DATA
　　
      IGNORE_OPTIM_EMBEDDED_HINTS
　　
      OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
　　
      ALL_ROWS
　　
      OUTLINE_LEAF(@"SEL$1")
　　
      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))
　　
      END_OUTLINE_DATA
　　
  */
　　

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

　　
PLAN_TABLE_OUTPUT
　　

　　
   1 - access("N"=1)
　　

　　
Column Projection Information (identified by operation id):
　　
-----------------------------------------------------------
　　

　　
   1 - "N"[NUMBER,22]
　　

　　
Note
　　
-----
　　
   - SQL profile "SYS_SQLPROF_01539fb60e15c000" used for this statement
　　

　　

　　
45 rows selected.