sql_profile的使用(一) (r2笔记29天)

今天看了老熊关于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的,确实提供了不少的知识集。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-07-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第四篇(r2笔记41天)

生产中有一条sql语句消耗了大量的cpu资源,执行时间在18秒左右, Session:PRODBUSER (1560:61133)SQL ID:1hg2wcua...

2035
来自专栏杨建荣的学习笔记

联系生活来简化sql(r3笔记第43天)

目前生产环境中有一条sql语句的CPU消耗很高。执行时间比较长。从awr中抓到的sql语句如下: SELECT run_request.run_mode, ...

2713
来自专栏乐沙弥的世界

使用 EXPLAIN PLAN 获取SQL语句执行计划

     SQL查询语句的性能从一定程度上影响整个数据库的性能。很多情况下,数据库性能的低下差不多都是不良SQL语句所引起。而SQL语句的执行 计划则决定了S...

1035
来自专栏杨建荣的学习笔记

生产环境大型sql语句调优实战第一篇(一) (r2笔记第31天)

在生产环境中有一条sql语句的性能极差,在早晨非高峰时段运行抽取数据,平均要花费40分钟,有时候竟然要跑10个多小时。 sql语句比较长,需要点耐心往下看。我对...

3174
来自专栏杨建荣的学习笔记

关于oracle中的反连接(r3笔记第95天)

在之前的章节中见到讨论过oracle中的半连接 http://blog.itpub.net/23718752/viewspace-1334483/ 与半连接相对...

2057
来自专栏数据和云

补偏救弊 | 关于一致性读与语句性能关系的一大误区

作者简介 黄玮(Fuyuncat) 资深 Oracle DBA,致力于数据库底层技术的研究,其作品获得广大同行的高度评价。 个人网站 www.HelloDB...

2544
来自专栏数据和云

SQL之美 - 分页查询的排序问题

编辑手记:前面我们分享过分页查询的基础知识,其目的就是控制输出结果集大小,将结果尽快的返回。主要有两种方式,一种是嵌套的查询方式,一种是通过范围控制分页的最大值...

3236
来自专栏杨建荣的学习笔记

通过执行计划中的CONCATENATION分析sql问题(r4笔记第16天)

昨天开发的一个同事找到我,说写了一条sql语句,但是执行了半个小时还没有执行完,想让我帮忙看看是怎么回事。 他大体上给我讲了下逻辑,表bl1_rc_rates是...

2844
来自专栏杨建荣的学习笔记

一条执行时间两天半的sql语句简化(r4笔记第62天)

周末刚过去,今天来到办公室做例行检查,就发现一条sql语句已经执行234841秒(65小时),已经两天多了。 查看了一下对应的Undo资源消耗,发现这个语句是最...

2728
来自专栏杨建荣的学习笔记

关于查询转换的一些简单分析(一) (r3笔记第37天)

在sql解析器中,在生成执行计划的时候,会在多个执行计划中选择最优的计划,在这个过程中,查询转换就是一个很重要的过程。 虽然最终的执行结果没有变化,但是从优化器...

2955

扫码关注云+社区