当前位置： 首页 > news >正文

SQL Profile 总结（一）

news 来源：原创 2024/4/26 20:34:57
一、前提概述
在介绍SQL Profile之前，不得不说的一个工具就是SQL Tuning Advisor；这个工具是从Oracle 10g開始引入，它的任务就是分析一个指定的SQL语句，并建议怎样使用一些方法来提高指定语句的性能，比如：收集缺失的对象统计信息、或者收集过时的对象统计信息、创建新的索引、调整SQL语句结构、採用SQL Profile等等方式。

二、为什么SQL Tuning Advisor可以找出提高语句性能的方法？
这须要从SQL Tuning Advisor的工作原理開始说明，例如以下
1、首先SQL Tuning Advisor将给定的SQL语句委派给Automotic Tuning Optimizer来完毕
2、Automotic Tuning Optimizer为Oracle查询优化器的一部分
3、Automotic Tuning Optimizer能够花较长的时间来产生一个高效的运行计划，比如：
  • 能够使用耗时的技术如如果分析（what-if），并加强对动态採样技术的利用来核实它的预计值
  • 能够执行实际执行计划中的多个步骤，并将得出的实际值与优化器评估的预计值相比較，来验证优化器最初的预计
注意：
1、尽管Automotic Tuning Optimizer为Oracle查询优化器的一部分，优化器无法再第一时间找到高效的运行计划，而Automotic Tuning Optimizer能够找到的原因主要是两者的工作职责不同。在正常情况下，优化器必须以最快的速度产生运行计划（秒级以内），而另外一个工具能够花较长时间来寻找高效的运行计划！
2、 SQL Tuning Advisor并不一定每次到能找到高效的运行计划
3、SQL Tuning Advisor生成的建立，我们一定要细致阅读，并依据实际情况来决定是否採用这些建议

三、SQL Tuning Advisor简单概述
SQL Tuning Advisor的核心接口是通过 dbms_sqltune程序包来提供的，能够接受的SQL语句类型例如以下4种
  1. SQL语句文本
  2. 存储在共享池中的SQL语句，指定SQL_ID就可以
  3. 存储在AWR资料库中的SQL语句，指定SQL_ID就可以
  4. SQL调优集的名称（能够看做是存储一系列SQL语句以及相关信息的对象集合）
四、封装的存储过程
依据实际使用，一般都是指定SQL_ID来进行优化，因此，我创建了一个存储过程，指定sql_id參数后，会自己主动进行sql优化，并打印出查看报告的方法，例如以下（以SYS用户执行）：
create or replace procedure p_create_sqltuning_task(p_sql_id varchar2) is
  v_tuning_task varchar2(30);
  v_sql_id v$session.sql_id%type;
begin
  v_sql_id := p_sql_id;
  v_tuning_task := dbms_sqltune.create_tuning_task(sql_id => v_sql_id);
  dbms_sqltune.execute_tuning_task(v_tuning_task);
  dbms_output.put_line('This Tuning task name is :   '|| v_tuning_task);
  dbms_output.put_line('-------------Please using follow command query SQL tuning report!------------');
  dbms_output.put_line('set linesize 200 pagesize 9999');
  dbms_output.put_line('set long 100000');
  dbms_output.put_line('select dbms_sqltune.report_tuning_task('''||v_tuning_task||''') from dual;');
end;
/
理论知识都相对枯燥，我们来看一个详细的測试；

五、演示样例
一、执行SQL tuning advisor
SQL >  exec p_create_sqltuning_task( 'g8hkhf0ma30vk');
This Tuning task  name  is :   TASK_18580
- - - - - - - - - - - - -Please  using follow command query  SQL tuning report! - - - - - - - - - - - -
set linesize  200 pagesize  9999
set long  100000
select dbms_sqltune.report_tuning_task( 'TASK_18580')  from dual;

PL / SQL  procedure successfully completed.

二、查看产生的报告
SQL >  set linesize  200 pagesize  9999
SQL >  set long  100000
SQL >  select dbms_sqltune.report_tuning_task( 'TASK_18580')  from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK( 'TASK_18580')
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
GENERAL INFORMATION  SECTION
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Tuning Task  Name   : TASK_18580
Tuning Task  Owner  : SYS
Workload  Type      : Single  SQL  Statement
Scope              : COMPREHENSIVE
Time  Limit(seconds):  1800
Completion Status  : COMPLETED
Started  at         :  06 / 10 / 2014  13: 10: 17
Completed  at       :  06 / 10 / 2014  13: 10: 21

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Schema  Name: FLOW
SQL ID     : g8hkhf0ma30vk
SQL Text   :  SELECT  "IP", "PR_URL", "ACC_DATE", "COOKIE"  FROM  "TB_FLOW"  "F"
              WHERE SUBSTR( "PR_URL",INSTR( "PR_URL",: "SYS_B_0"),: 1) <>: "SYS_B_1"
              AND TRUNC( "ACC_DATE") =: 2  AND  "PR_URL"  IS  NOT  NULL

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
FINDINGS  SECTION ( 2 findings)
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

1 -  SQL Profile Finding (see  explain plans  section below)
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
  A potentially better execution plan was  found  for this  statement.

  Recommendation (estimated benefit:  98. 26%)
   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
   - Consider accepting the recommended  SQL profile  to use parallel execution
     for this  statement.
     execute dbms_sqltune.accept_sql_profile(task_name  = >  'TASK_18580',
            task_owner  = >  'SYS', replace  = >  TRUE, profile_type  = >
            DBMS_SQLTUNE.PX_PROFILE);

  Executing this query parallel  with DOP  64 will improve its response  time
   98. 26% over the original plan. However, there  is  some cost  in enabling
  parallel execution. It will increase the  statement's resource consumption by
  an estimated 11.11% which may result in a reduction of system throughput.
  Also, because these resources are consumed over a much smaller duration, the
  response time of concurrent statements might be negatively impacted if
  sufficient hardware capacity is not available.

  The following data shows some sampled statistics for this SQL from the past
  week and projected weekly values when parallel execution is enabled.

                                 Past week sampled statistics for this SQL
                                 -----------------------------------------
  Number of executions                                                   0
  Percent of total activity                                              0
  Percent of samples with #Active Sessions > 2*CPU                       0
  Weekly DB time (in sec)                                                0

                              Projected statistics with Parallel Execution
                              --------------------------------------------
  Weekly DB time (in sec)                                                0

2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  The predicate TRUNC("F"."ACC_DATE")=:B1 used at line ID 2 of the execution
  plan contains an expression on indexed column "ACC_DATE". This  expression
  prevents the optimizer from efficiently using indices on table
  "FLOW"."TB_FLOW".

  Recommendation
  --------------
  - Rewrite the predicate into an equivalent form to take advantage of
    indices. Alternatively, create a function-based index on the expression.

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

1- Original
-----------
Plan hash value: 3675585382

-------------------------------------------------------------------------------------------
----
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |  2381 |   253K|   312K  (0)|999:59:59 |
   |       |
|   1 |  PARTITION RANGE ALL|         |  2381 |   253K|   312K  (0)|999:59:59 |     1 |    82 |
|*  2 |   TABLE ACCESS FULL | TB_FLOW |  2381 |   253K|   312K  (0)|999:59:59 |     1 |
82 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("PR_URL" IS NOT NULL AND SUBSTR("PR_URL",INSTR("PR_URL",:SYS_B_0),:1)<>:S
              YS_B_1 AND TRUNC(INTERNAL_FUNCTION("ACC_DATE"))=:2)

2- Using Parallel Execution
---------------------------
Plan hash value: 1016406201

---------------------------------------------------------------------------------------------------------------
---------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ
 |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------
-----------------------------
|   0 | SELECT STATEMENT     |          |  2381 |   253K|  5419   (0)| 40:42:45 |       |
      |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |
     |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |  2381 |   253K|  5419   (0)| 40:42:45 |       |       |  Q1,00 | P->S | QC
 (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |  2381 |   253K|  5419   (0)| 40:42:45 |     1 |    82 |  Q1,00 | PC
WC |            |
|*  4 |     TABLE ACCESS FULL| TB_FLOW  |  2381 |   253K|  5419   (0)| 40:42:45 |     1 |    82 |  Q1,
00 | PCWP |            |
-----------------------------------------------------------------------------------------------
-------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("PR_URL" IS NOT NULL AND SUBSTR("PR_URL",INSTR("PR_URL",:SYS_B_0),:1)<>:SYS_B_1 AND
              TRUNC(INTERNAL_FUNCTION("ACC_DATE"))=:2)

-------------------------------------------------------------------------------

三、查看报告建议后，删除该报告
exec dbms_sqltune.drop_tuning_task('TASK_18580');


下一篇開始正式介绍SQL profile

转载于:https://www.cnblogs.com/hrhguanli/p/3841125.html

相关文章：

  • “Material Design”设计规范在 ComponentOne For WinForm 的全新尝试！
  • Hihicoder 题目1 : Trie树（字典树，经典题）
  • Memcached理解笔记1---安装常规错误监控
  • linux extglob模式 和rm反选
  • LOJ2541:「PKUWC2018」猎人杀
  • Redis 二：入门基本篇
  • 2019预测：三大专家视角解读云计算、无服务器等关键趋势
  • mysql学习笔记--第1天
  • Flink-数据流编程模型
  • 最全的Android开发资源整理--进阶必备
  • Zookeeper请求处理
  • yii2 render和renderPartial区别
  • [20181219]script使用小技巧.txt
  • “寻梦之路 阿里之行”北城大数据学院学生代表团赴北京阿里中心参观
  • 二叉搜索树
  • 【402天】跃迁之路——程序员高效学习方法论探索系列（实验阶段159-2018.03.14）...
  • 03Go 类型总结
  • 0x05 Python数据分析，Anaconda八斩刀
  • CEF与代理
  • emacs初体验
  • Java知识点总结（JavaIO-打印流）
  • php中curl和soap方式请求服务超时问题
  • - 概述 - 《设计模式（极简c++版）》
  • 关于 Linux 进程的 UID、EUID、GID 和 EGID
  • 基于webpack 的 vue 多页架构
  • 强力优化Rancher k8s中国区的使用体验
  • 深入 Nginx 之配置篇
  • 数据结构java版之冒泡排序及优化
  • 通过几道题目学习二叉搜索树
  • 项目实战-Api的解决方案
  • 正则表达式小结
  • MiKTeX could not find the script engine ‘perl.exe‘ which is required to execute ‘latexmk‘.
  • 你学不懂C语言，是因为不懂编写C程序的7个步骤 ...
  • ###STL(标准模板库)
  • #ubuntu# #git# repository git config --global --add safe.directory
  • (16)UiBot：智能化软件机器人（以头歌抓取课程数据为例）
  • （solr系列:一）使用tomcat部署solr服务
  • （附源码）springboot电竞专题网站 毕业设计 641314
  • （附源码）springboot优课在线教学系统 毕业设计 081251
  • (简单) HDU 2612 Find a way，BFS。
  • （剑指Offer）面试题41：和为s的连续正数序列
  • （经验分享）作为一名普通本科计算机专业学生，我大学四年到底走了多少弯路
  • （论文阅读31/100）Stacked hourglass networks for human pose estimation
  • （四）c52学习之旅-流水LED灯
  • (转）【Ｈibernate总结系列】使用举例
  • （转）负载均衡，回话保持，cookie
  • ****** 二 ******、软设笔记【数据结构】-KMP算法、树、二叉树
  • . NET自动找可写目录
  • .bat批处理（五）：遍历指定目录下资源文件并更新
  • .net core webapi 部署iis_一键部署VS插件：让.NET开发者更幸福
  • .NET Core 版本不支持的问题
  • .Net+SQL Server企业应用性能优化笔记4——精确查找瓶颈
  • .NET下ASPX编程的几个小问题
  • []C/C++读取串口接收到的数据程序
  • []sim300 GPRS数据收发程序