备案控制台
探索云世界
开发者社区 数据库 文章 正文

sql_profile的使用(一)

2014-07-10 800
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 今天看了老熊关于sql_profile的讲解，受益匪浅，自己在本机也做了一通,感觉好记性不如烂笔头还是得多总结总测试才能真正理解。准备的数据如下，创建两个表，一个大，一个小，然后做表分析 SQL> create table t1 as select objec...
今天看了老熊关于sql_profile的讲解，受益匪浅，自己在本机也做了一通,感觉好记性不如烂笔头还是得多总结总测试才能真正理解。
准备的数据如下，创建两个表，一个大，一个小，然后做表分析
SQL> create table t1 as select object_id,object_name from dba_objects where rownum 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的，确实提供了不少的知识集。

文章标签：
SQL
Perl
缓存
存储
关键词：
SQL profile
jeanron100
目录
相关文章
码农飞哥
|
SQL 存储 监控
【MySQL从入门到精通】【高级篇】（二十二）慢查询日志分析，SHOW PROFILE查看SQL执行成本
上一篇文章我们介绍数据库的优化步骤【MySQL从入门到精通】【高级篇】（二十一）数据库优化步骤_查看系统性能参数，其中，说到了通过开启慢查询日志来分析慢查询的SQL。这篇文章就是具体来介绍如何开启慢查询日志以及如何分析慢查询日志。
码农飞哥
215 0
【MySQL从入门到精通】【高级篇】（二十二）慢查询日志分析，SHOW PROFILE查看SQL执行成本
遇见的昨天
|
SQL 存储 监控
Mysql中 慢查询日志和show profile进行sql分析
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
遇见的昨天
340 0
江夏、
|
SQL 关系型数据库 MySQL
几个必须掌握的SQL优化技巧(五)：Show Profile分析SQL性能
在应用的开发过程中，由于开发初期的数据量一般都比较小，所以开发过程中一般都比较注重功能上的实现，但是当完成了一个应用或者系统之后，随着生产数据量的急剧增长，那么之前的很多sql语句的写法就会显现出一定的性能问题，对生产的影响也会越来越大，这些不恰当的sql语句就会成为整个系统性能的瓶颈，为了追求系统的极致性能，必须要对它们进行优化。
江夏、
195 0
几个必须掌握的SQL优化技巧(五)：Show Profile分析SQL性能
袁袁袁袁满
|
SQL 存储 算法
MySQL数据库性能优化由浅入深（表设计、慢查询、SQL索引优化、Explain分析、Show Profile分析、配置优化）
通俗地理解三个范式，对于数据库设计大有好处。在数据库设计中，为了更好地应用三个范式，就必须通俗地理解三个范式(通俗地理解是够用的理解，并不是最科学最准确的理解
袁袁袁袁满
287 0
MySQL数据库性能优化由浅入深（表设计、慢查询、SQL索引优化、Explain分析、Show Profile分析、配置优化）
lfreeali
|
SQL 索引 Perl
[20180302]sql profile能减少分析时间吗?
[20180302]sql profile能减少分析时间吗? --//链接http://www.itpub.net/thread-2097379-1-1.html的讨论,测试看看sql profile能减少分析时间吗? --//要找到这样的语句分析时间"很长",...
lfreeali
876 0
lfreeali
|
SQL
[20180301]sql profile 非绑定变量.txt
[20180301]sql profile 非绑定变量.txt http://www.itpub.net/thread-2097379-1-1.html 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING            ...
lfreeali
832 0
玄学酱
|
SQL
9.4. SHOW PROFILE Syntax SQL性能分析器
玄学酱
923 0
玄学酱
|
SQL
27.4. SHOW PROFILE Syntax SQL性能分析器
玄学酱
911 0
幸福的红帆
|
SQL
高性能日记--show profile剖析sql语句
幸福的红帆
1403 0
余二五
|
SQL 测试技术 数据库
SQLT导入测试数据，及SQL Profile的使用
余二五
883 0

热门文章

最新文章

  • 1
    一文搞懂SQL优化——如何高效添加数据
  • 2
    Seata常见问题之服务端 error日志没有输出,客户端执行sql报错如何解决
  • 3
    【软件设计师备考 专题 】数据库语言（SQL）
  • 4
    【SQL server】玩转SQL server数据库：第三章 关系数据库标准语言SQL（二）数据查询
  • 5
    【SQL server】玩转SQL server数据库：第二章 关系数据库
  • 6
    SQL常见面试题总结2
  • 7
    NL2SQL进阶系列(5)：论文解读业界前沿方案（DIN-SQL、C3-SQL、DAIL-SQL）、新一代数据集BIRD-SQL解读
  • 8
    NL2SQL实践系列(2)：2024最新模型实战效果(Chat2DB-GLM、书生·浦语2、InternLM2-SQL等)以及工业级案例教学
  • 9
    数据库管理与电脑监控软件：SQL代码优化与实践
  • 10
    SQL，Group By 真扎心，原来是这样
  • 1
    sql语句创建数据库
    19
  • 2
    sql 数据库优化
    34
  • 3
    sql导入数据库命令
    12
  • 4
    一文解析 ODPS SQL 任务优化方法原理
    26
  • 5
    Oracle的PL/SQL游标属性：数据的“导航仪”与“仪表盘”
    27
  • 6
    sql 性能优化基于explain调优（二）
    12
  • 7
    Oracle的PL/SQL循环语句：数据的“旋转木马”与“无限之旅”
    10
  • 8
    Oracle的PL/SQL条件控制：数据的“红绿灯”与“分岔路”
    11
  • 9
    Oracle的PL/SQL表达式：数据的魔法公式
    13
  • 10
    Oracle的PL/SQL定义变量和常量：数据的稳定与灵动
    14

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server在电子商务中的应用与实践
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • 使用SQL语句实现数据表管理
  • 使用SQL语句实现数据插入、修改和删除操作
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）