ITPub博客

首页 > 数据库 > Oracle > sql_profile的使用(一)

sql_profile的使用(一)

Oracle 作者:azzotest 时间:2015-09-11 17:05:26 0 删除 编辑
今天看了老熊关于sql_profile的讲解,受益匪浅,自己在本机也做了一通,感觉好记性不如烂笔头还是得多总结总测试才能真正理解。
准备的数据如下,创建两个表,一个大,一个小,然后做表分析
SQL> create table t1 as select object_id,object_name from dba_objects where rownum<=50000;  
Table created.
SQL> create table t2 as select * from dba_objects;  
Table created.
SQL> create index t2_idx on t2(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
SQL>  exec dbms_stats.gather_table_stats(user,'t2',cascade=>true,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.

不加任何hint,查看执行计划,可以看到两个表都走了全表扫描。
SQL> set autot trace exp stat
SQL> set linesize 200
SQL> set pages 100
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id; 
26 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974
-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2500 |   112K|   122 |
|*  1 |  HASH JOIN         |      |  2500 |   112K|   122 |
|*  2 |   TABLE ACCESS FULL| T1   |  2500 | 77500 |     8 |
|   3 |   TABLE ACCESS FULL| T2   |   269K|  3952K|   107 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
              NOT NULL)
Note
-----
   - cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4477  consistent gets
          0  physical reads
          0  redo size
       1669  bytes sent via SQL*Net to client
        531  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         26  rows processed

如果手动调优,加入Hint,可以参考如下的形式,可以看到性能有了成倍的提升。

SQL> select /*+ use_nl(t1 t2) index(t2) */ t1.*,t2.owner   
  2           from t1,t2   
  3           where t1.object_name like '%T1%'   
     and t1.object_id=t2.object_id;  4  
26 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391


-----------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |  2500 |   112K|   258 |
|   1 |  NESTED LOOPS                |        |       |       |       |
|   2 |   NESTED LOOPS               |        |  2500 |   112K|   258 |
|*  3 |    TABLE ACCESS FULL         | T1     |  2500 | 77500 |     8 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    15 |     1 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
              NOT NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        323  consistent gets
          0  physical reads
          0  redo size
       1669  bytes sent via SQL*Net to client
        531  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         26  rows processed

下面来根据sql_id来进行调优,试试sql_profile给出的见解。先从缓存中查出刚才执行的sql语句。

SQL> select sql_id,sql_text from v$sql where sql_text like '%t1.object_name%'
  2  /
SQL_ID
。。。。
4zbqykx89yc8v
select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
2pxr40u2zm0ja
select sql_id,sql_text from v$sql where sql_text like '%t1.object_name%'

然后运行下面的存储过程,执行sqltune task.
SQL> var tuning_task varchar2(100);  
SQL>  DECLARE  
       l_sql_id v$session.prev_sql_id%TYPE;  
   l_tuning_task VARCHAR2(30);  
 BEGIN  
   l_sql_id:='4zbqykx89yc8v';  
   l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);  
   :tuning_task:=l_tuning_task;  
   dbms_sqltune.execute_tuning_task(l_tuning_task);  
   dbms_output.put_line(l_tuning_task);  
 END;
/  

PL/SQL procedure successfully completed.

查看task的name
SQL> print tuning_task;
TUNING_TASK
--------------------------------------------------------------------------------------------------------------------------------
TASK_12352

如果sql语句本身不复杂,涉及的表不大的话,执行是很快的。如下查看报告。
SQL> set long 99999
SQL>  col comments format a200
SQL>  SELECT dbms_sqltune.report_tuning_task(:tuning_task)COMMENTS FROM dual;                                                     
COMMENTS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : TASK_12352
Tuning Task Owner  : N1
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 07/10/2014 15:04:18
Completed at       : 07/10/2014 15:04:20

Schema Name: N1
SQL ID     : 4zbqykx89yc8v
SQL Text   : select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%'
             and t1.object_id=t2.object_id

-------------------------------------------------------------------------------
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: 92.9%)
  -----------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_12352',task_owner => 'N1', replace => TRUE);


  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.


                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE
  Elapsed Time (s):             .042304           .006329      85.03 %
  CPU Time (s):                 .042293           .006399      84.86 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                     4475               317      92.91 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                    26                26
  Fetches:                           26                26
  Executions:                         1                 1

  Notes
  -----
  1. Statistics for the original plan were averaged over 10 executions.


  2. Statistics for the SQL profile plan were averaged over 10 executions.

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

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

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    26 |  1196 |   796   (1)| 00:00:10 |
|*  1 |  HASH JOIN         |      |    26 |  1196 |   796   (1)| 00:00:10 |
|*  2 |   TABLE ACCESS FULL| T1   |    26 |   806 |    47   (3)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |   269K|  3952K|   748   (1)| 00:00:09 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
              NOT NULL)

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

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    26 |  1196 |    49   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |        |       |       |            |          |
|   2 |   NESTED LOOPS               |        |    26 |  1196 |    49   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | T1     |    26 |   806 |    47   (3)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    15 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

-------------------------------------------------------------------------------
可以从报告看出,改进确实是很客观的,提升了90%以上。

来简单验证一下,先得accept 一下。
SQL>  execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_12352',task_owner => 'N1', replace => TRUE);
PL/SQL procedure successfully completed.  

再来查询一下,看看是否启用了profile
SQL> set autot trace exp stat
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
 /
26 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    26 |  1196 |    49   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |        |       |       |            |          |
|   2 |   NESTED LOOPS               |        |    26 |  1196 |    49   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | T1     |    26 |   806 |    47   (3)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    15 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
   - SQL profile "SYS_SQLPROF_01471f52938e0000" used for this statement
Statistics
----------------------------------------------------------
         34  recursive calls
          1  db block gets
        338  consistent gets
          3  physical reads
        196  redo size
       1669  bytes sent via SQL*Net to client
        531  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         26  rows processed
         
再来看看如果改动了sql语句,多加了些空格,看看profile还能不能正常启用。
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=    t2.object_id
  2  /
26 rows selected.
Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    26 |  1196 |    49   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |        |       |       |            |          |
|   2 |   NESTED LOOPS               |        |    26 |  1196 |    49   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | T1     |    26 |   806 |    47   (3)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    15 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

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

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        323  consistent gets
          0  physical reads
          0  redo size
       1669  bytes sent via SQL*Net to client
        530  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         26  rows processed
        

可以看到,还是正常启用了。另外,库里的cursor_sharing参数如下。
SQL> show parameter cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_bind_capture_destination      string      memory+disk
cursor_sharing                       string      EXACT

所以在使用中,对于sql调优来说还是可以尝试使用sql_profile的,确实提供了不少的知识集。

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

请登录后发表评论 登录
全部评论

注册时间:2015-08-21

  • 博文量
    22
  • 访问量
    46213