fgdfg 发表于 2018-10-23 12:52:51

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

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

  
17:20:16 SCOTT@ prod >begin
  
17:20:24   2for i in 1..100000 loop
  
17:20:36   3insert into test values (i);
  
17:20:47   4commit;
  
17:20:49   5end loop;
  
17:20:52   6end;
  
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
  
----------------------------------------------------------
  
          1recursive calls
  
          0db block gets
  
          3consistent gets
  
          0physical reads
  
          0redo size
  
      415bytes sent via SQL*Net to client
  
      419bytes received via SQL*Net from client
  
          2SQL*Net roundtrips to/from client
  
          0sorts (memory)
  
          0sorts (disk)
  
          1rows 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
  
----------------------------------------------------------
  
          1recursive calls
  
          0db block gets
  
      191consistent gets
  
          0physical reads
  
          0redo size
  
      415bytes sent via SQL*Net to client
  
      419bytes received via SQL*Net from client
  
          2SQL*Net roundtrips to/from client
  
          0sorts (memory)
  
          0sorts (disk)
  
          1rows 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:3410
  
17:29:3411       my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
  
17:29:3412
  
17:29:3413       sql_text => my_sqltext,
  
17:29:3414
  
17:29:3415       user_name => 'SCOTT',
  
17:29:3416
  
17:29:3417       scope => 'COMPREHENSIVE',
  
17:29:3418
  
17:29:3419       time_limit => 60,
  
17:29:3420
  
17:29:3421       task_name => 'my_tun1',
  
17:29:3422
  
17:29:3423       description => 'Task to tune a query on a specified table');
  
17:29:3424
  
17:29:3425end;
  
17:29:3526/
  
PL/SQL procedure successfully completed.
  

  
建立调优任务:
  
17:29:37 SYS@ prod >begin
  
17:30:39   2
  
17:30:39   3DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_tun1');
  
17:30:39   4
  
17:30:39   5end;
  
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   3my_sqlprofile_name VARCHAR2(30);
  
17:41:13   4
  
17:41:13   5begin
  
17:41:13   6
  
17:41:13   7my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
  
17:41:13   8
  
17:41:13   9task_name => 'my_tun1',
  
17:41:1310
  
17:41:1311name => 'my_sqlprofile',force_match=>false);
  
17:41:1312
  
17:41:1313end;
  
17:41:1514/
  
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   3my_sqlprofile_name VARCHAR2(30);
  
17:46:00   4
  
17:46:00   5begin
  
17:46:00   6
  
17:46:00   7my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
  
17:46:00   8
  
17:46:00   9task_name => 'my_tun1',
  
17:46:0010
  
17:46:0011name => 'my_sqlprofile',force_match=>false,CATEGORY=>'scott');
  
17:46:0012
  
17:46:0013end;
  
17:46:0114/
  
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   3my_sqlprofile_name VARCHAR2(30);
  
17:56:13   4
  
17:56:13   5begin
  
17:56:13   6
  
17:56:13   7my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
  
17:56:13   8
  
17:56:13   9task_name => 'my_tun1',
  
17:56:1310
  
17:56:1311name => 'my_sqlprofile');
  
17:56:1312
  
17:56:1313end;
  
17:56:1614/
  
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
  
----------------------------------------------------------
  
      790recursive calls
  
          0db block gets
  
      168consistent gets
  
          6physical reads
  
      116redo size
  
      415bytes sent via SQL*Net to client
  
      419bytes received via SQL*Net from client
  
          2SQL*Net roundtrips to/from client
  
         13sorts (memory)
  
          0sorts (disk)
  
          1rows processed


页: [1]
查看完整版本: 通过案例学调优之--SQL Profile