通过案例学调优之--SQL Profile

fgdfg

1、scott用户执行sql　　
17:19:56 SCOTT@ prod >create table test (n number);
　　
Table created.
　　

　　
17:20:16 SCOTT@ prod >begin
　　
17:20:24   2  for i in 1..100000 loop
　　
17:20:36   3  insert into test values (i);
　　
17:20:47   4  commit;
　　
17:20:49   5  end loop;
　　
17:20:52   6  end;
　　
17:20:53   7  /
　　
PL/SQL procedure successfully completed.
　　

　　
17:22:02 SCOTT@ prod >create index test_ind on test(n);
　　
Index created.
　　

　　
17:22:55 SCOTT@ prod >exec dbms_stats.gather_table_stats(user,'TEST' ,cascade=>true);
　　
PL/SQL procedure successfully completed.
　　

　　
17:23:15 SCOTT@ prod >set autotrace on
　　
17:23:43 SCOTT@ prod >select * from test where n=100;
　　
         N
　　
----------
　　
       100
　　
Elapsed: 00:00:00.01
　　
Execution Plan
　　
----------------------------------------------------------
　　
Plan hash value: 3357096749
　　
-----------------------------------------------------------------------------
　　
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
　　
-----------------------------------------------------------------------------
　　
|   0 | SELECT STATEMENT |          |     1 |     5 |     1   (0)| 00:00:01 |
　　
|*  1 |  INDEX RANGE SCAN| TEST_IND |     1 |     5 |     1   (0)| 00:00:01 |
　　
-----------------------------------------------------------------------------
　　
Predicate Information (identified by operation id):
　　
---------------------------------------------------
　　
   1 - access("N"=100)
　　
Statistics
　　
----------------------------------------------------------
　　
          1  recursive calls
　　
          0  db block gets
　　
          3  consistent gets
　　
          0  physical reads
　　
          0  redo size
　　
        415  bytes sent via SQL*Net to client
　　
        419  bytes received via SQL*Net from client
　　
          2  SQL*Net roundtrips to/from client
　　
          0  sorts (memory)
　　
          0  sorts (disk)
　　
          1  rows processed
　　

　　
对此sql建立sql profile：
　　
17:24:02 SCOTT@ prod >select /*+ no_index(test,test_ind) */ * from test where n=100;
　　
         N
　　
----------
　　
       100
　　
Elapsed: 00:00:00.01
　　
Execution Plan
　　
----------------------------------------------------------
　　
Plan hash value: 1357081020
　　
--------------------------------------------------------------------------
　　
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
　　
--------------------------------------------------------------------------
　　
|   0 | SELECT STATEMENT  |      |     1 |     5 |    69   (2)| 00:00:01 |
　　
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     5 |    69   (2)| 00:00:01 |
　　
--------------------------------------------------------------------------
　　
Predicate Information (identified by operation id):
　　
---------------------------------------------------
　　
   1 - filter("N"=100)
　　
Statistics
　　
----------------------------------------------------------
　　
          1  recursive calls
　　
          0  db block gets
　　
        191  consistent gets
　　
          0  physical reads
　　
          0  redo size
　　
        415  bytes sent via SQL*Net to client
　　
        419  bytes received via SQL*Net from client
　　
          2  SQL*Net roundtrips to/from client
　　
          0  sorts (memory)
　　
          0  sorts (disk)
　　
          1  rows processed
　　

　　
2、通过sys用户建立sql profile（使用dbms_sqltune包）
　　
17:28:26 SYS@ prod >declare
　　
17:29:34   2
　　
17:29:34   3    my_task_name VARCHAR2(30);
　　
17:29:34   4
　　
17:29:34   5    my_sqltext CLOB;
　　
17:29:34   6
　　
17:29:34   7    begin
　　
17:29:34   8
　　
17:29:34   9       my_sqltext := 'select /*+ no_index(test test_ind) */ * from test where n=100';
　　
17:29:34  10
　　
17:29:34  11       my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
　　
17:29:34  12
　　
17:29:34  13       sql_text => my_sqltext,
　　
17:29:34  14
　　
17:29:34  15       user_name => 'SCOTT',
　　
17:29:34  16
　　
17:29:34  17       scope => 'COMPREHENSIVE',
　　
17:29:34  18
　　
17:29:34  19       time_limit => 60,
　　
17:29:34  20
　　
17:29:34  21       task_name => 'my_tun1',
　　
17:29:34  22
　　
17:29:34  23       description => 'Task to tune a query on a specified table');
　　
17:29:34  24
　　
17:29:34  25  end;
　　
17:29:35  26  /
　　
PL/SQL procedure successfully completed.
　　

　　
建立调优任务：
　　
17:29:37 SYS@ prod >begin
　　
17:30:39   2
　　
17:30:39   3  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_tun1');
　　
17:30:39   4
　　
17:30:39   5  end;
　　
17:30:39   6
　　
17:30:39   7  /
　　
PL/SQL procedure successfully completed.
　　

　　
查看调优task：
　　
17:32:47 SYS@ prod >set long 1000
　　
17:33:17 SYS@ prod >set longchunksize 1000
　　
17:33:24 SYS@ prod >set linesize 100
　　
17:33:32 SYS@ prod >SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_tun1') from dual
　　
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUN1')
　　
----------------------------------------------------------------------------------------------------
　　
GENERAL INFORMATION SECTION
　　
-------------------------------------------------------------------------------
　　
Tuning Task Name   : my_tun1
　　
Tuning Task Owner  : SYS
　　
Workload Type      : Single SQL Statement
　　
Scope              : COMPREHENSIVE
　　
Time Limit(seconds): 60
　　
Completion Status  : COMPLETED
　　
Started at         : 11/07/2014 17:30:41
　　
Completed at       : 11/07/2014 17:30:49
　　
-------------------------------------------------------------------------------
　　
Schema Name: SCOTT
　　
SQL ID     : b1wdr0b0qzsbg
　　
SQL Text   : select /*+ no_index(test test_ind) */ * from test where n=100
　　
-------------------------------------------------------------------------------
　　
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUN1')
　　
----------------------------------------------------------------------------------------------------
　　
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: 99.41%)
　　

　　
17:34:58 SYS@ prod >execute dbms_sqltune.accept_sql_profile(task_name =>'my_tun1',task_owner => 'SYS', replace => TRUE);
　　
PL/SQL procedure successfully completed.
　　

　　
建立sql profile：
　　
17:39:22 SYS@ prod >DECLARE
　　
17:41:13   2
　　
17:41:13   3  my_sqlprofile_name VARCHAR2(30);
　　
17:41:13   4
　　
17:41:13   5  begin
　　
17:41:13   6
　　
17:41:13   7  my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
　　
17:41:13   8
　　
17:41:13   9  task_name => 'my_tun1',
　　
17:41:13  10
　　
17:41:13  11  name => 'my_sqlprofile',force_match=>false);
　　
17:41:13  12
　　
17:41:13  13  end;
　　
17:41:15  14  /
　　
DECLARE
　　
*
　　
ERROR at line 1:
　　
ORA-13830: SQL profile with category DEFAULT already exists for this SQL statement
　　
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 16259
　　
ORA-06512: at "SYS.PRVT_SQLPROF_INFRA", line 31
　　
ORA-06512: at "SYS.DBMS_SQLTUNE", line 7133
　　
ORA-06512: at line 7
　　

　　
17:44:28 SYS@ prod >DECLARE
　　
17:46:00   2
　　
17:46:00   3  my_sqlprofile_name VARCHAR2(30);
　　
17:46:00   4
　　
17:46:00   5  begin
　　
17:46:00   6
　　
17:46:00   7  my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
　　
17:46:00   8
　　
17:46:00   9  task_name => 'my_tun1',
　　
17:46:00  10
　　
17:46:00  11  name => 'my_sqlprofile',force_match=>false,CATEGORY=>'scott');
　　
17:46:00  12
　　
17:46:00  13  end;
　　
17:46:01  14  /
　　
PL/SQL procedure successfully completed.
　　

　　
17:53:49 SYS@ prod >select name, CATEGORY,sql_text,status from dba_sql_profiles;
　　
NAME                           CATEGORY
　　
------------------------------ ------------------------------
　　
SQL_TEXT
　　
----------------------------------------------------------------------------------------------------
　　
STATUS
　　
--------
　　
SYS_SQLPROF_0149899c759a0000   DEFAULT
　　
select /*+ no_index(test test_ind) */ * from test where n=100
　　
ENABLED
　　
my_sqlprofile                  SCOTT
　　
select /*+ no_index(test test_ind) */ * from test where n=100
　　
ENABLED
　　

　　
删除存在的sql profile：
　　
17:53:51 SYS@ prod >exec DBMS_SQLTUNE.DROP_SQL_PROFILE(name =>'SYS_SQLPROF_0149899c759a0000');
　　
PL/SQL procedure successfully completed.
　　

　　
17:55:20 SYS@ prod >exec DBMS_SQLTUNE.DROP_SQL_PROFILE(name =>'my_sqlprofile');
　　
PL/SQL procedure successfully completed.
　　

　　
重新建立sql profile：
　　
17:55:35 SYS@ prod >DECLARE
　　
17:56:13   2
　　
17:56:13   3  my_sqlprofile_name VARCHAR2(30);
　　
17:56:13   4
　　
17:56:13   5  begin
　　
17:56:13   6
　　
17:56:13   7  my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
　　
17:56:13   8
　　
17:56:13   9  task_name => 'my_tun1',
　　
17:56:13  10
　　
17:56:13  11  name => 'my_sqlprofile');
　　
17:56:13  12
　　
17:56:13  13  end;
　　
17:56:16  14  /
　　
PL/SQL procedure successfully completed.
　　
Elapsed: 00:00:00.04
　　

　　
查看sql profile：
　　
18:01:48 SYS@ prod >col name for a20
　　
18:01:55 SYS@ prod >r
　　
  1* select name,CATEGORY,SQL_TEXT,TASK_EXEC_NAME ,STATUS from dba_sql_profiles
　　
NAME                 CATEGORY   SQL_TEXT
　　
-------------------- ---------- --------------------------------------------------
　　
TASK_EXEC_NAME                 STATUS
　　
------------------------------ --------
　　
my_sqlprofile        DEFAULT    select /*+ no_index(test test_ind) */ * from test
　　
                                where n=100
　　
EXEC_427                       ENABLED
　　

　　
3、以scott用户的身份进行验证
　　
18:01:55 SYS@ prod >conn scott/tiger
　　
Connected.
　　
18:02:43 SCOTT@ prod >set autotrace on
　　
18:02:46 SCOTT@ prod > select /*+ no_index(test test_ind) */ * from test where n=100;
　　
         N
　　
----------
　　
       100
　　
Elapsed: 00:00:00.05
　　
Execution Plan
　　
----------------------------------------------------------
　　
Plan hash value: 3357096749
　　
-----------------------------------------------------------------------------
　　
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
　　
-----------------------------------------------------------------------------
　　
|   0 | SELECT STATEMENT |          |     1 |     5 |     1   (0)| 00:00:01 |
　　
|*  1 |  INDEX RANGE SCAN| TEST_IND |     1 |     5 |     1   (0)| 00:00:01 |
　　
-----------------------------------------------------------------------------
　　
Predicate Information (identified by operation id):
　　
---------------------------------------------------
　　
   1 - access("N"=100)
　　
Note
　　
-----
　　
   - SQL profile "my_sqlprofile" used for this statement
　　
Statistics
　　
----------------------------------------------------------
　　
        790  recursive calls
　　
          0  db block gets
　　
        168  consistent gets
　　
          6  physical reads
　　
        116  redo size
　　
        415  bytes sent via SQL*Net to client
　　
        419  bytes received via SQL*Net from client
　　
          2  SQL*Net roundtrips to/from client
　　
         13  sorts (memory)
　　
          0  sorts (disk)
　　
          1  rows processed