专栏首页乐沙弥的世界Oracle AWR 阙值影响历史执行计划

Oracle AWR 阙值影响历史执行计划

      最近有网友提到为什么在dba_hist_sql_plan中无法查看到sql语句的历史执行计划,对于这个问题是由于缺省情况下,Oracle 设定的阙值并非捕获所有的sql语句,所以无法看到某些sql历史执行计划乃正常现象。在Oracle 9i的时候,我们可以通过设定不同的快照level获得不同程度的详细信息。也可以单独配置收集sql的阙值,如指定sql的执行次数,磁盘读的次数,解析调用的数量等。所有超出这个设置的sql语句都收集到snapshot之中。Oracle 10g,11g也有相应的设置。下面来描述这个问题。

1、缺省阙值的情形

--环境,下面的演示基于Oracle 10g
scott@CNMMBO> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

--下面的查询awr配置
scott@CNMMBO> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION                 TOPNSQL
---------- -------------------- ------------------------- ----------
 938506715 +00000 01:00:00.0    +00007 00:00:00.0         DEFAULT

--发布sql查询
scott@CNMMBO> select * from dept where loc='CHICAGO';

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

--获得sql_id
scott@CNMMBO> @my_last_sql

ADDRESS          HASH_VALUE SQL_ID        COMMAND_TYPE      PIECE SQL_TEXT
---------------- ---------- ------------- ------------ ---------- ------------------------------------------
000000009F942760 2626775672 2jbkb5qf92ums            3          0 select * from dept where loc='CHICAGO'

--从v$sql_plan获得缓冲区的执行计划
scott@CNMMBO> get sql_plan_curr.sql
  1  set linesize 135
  2  col id format 99
  3  col operation format a25
  4  col options format a25
  5  col object_name format a25 wrap
  6  SELECT id,
  7           operation,
  8           options,
  9           object_name,
 10           bytes,
 11           cpu_cost,
 12           io_cost
 13  FROM v$sql_plan
 14     WHERE sql_id = '&input_sql_id'
 15* ORDER BY id;
scott@CNMMBO> @sql_plan_curr.sql      -->此时可以查询到对应sql的执行计划
Enter value for input_sql_id: 2jbkb5qf92ums

 ID OPERATION                 OPTIONS                   OBJECT_NAME      BYTES   CPU_COST    IO_COST
--- ------------------------- ------------------------- --------------- ------- ---------- ----------
  0 SELECT STATEMENT
  1 TABLE ACCESS              FULL                      DEPT                20      36567          3

--下面尝试从dba_hist_sql_plan获得执行计划
scott@CNMMBO> get sql_plan_his.sql
  1  set linesize 135
  2  col id format 99
  3  col operation format a25
  4  col object_name format a25 wrap
  5  SELECT id,
  6           operation,
  7           options,
  8           object_name,
  9           bytes,
 10           cpu_cost,
 11           io_cost
 12      FROM dba_hist_sql_plan
 13     WHERE sql_id = '&input_sql_id'
 14* ORDER BY id;
scott@CNMMBO> @sql_plan_his     --查询无法获得执行计划
Enter value for input_sql_id: 2jbkb5qf92ums

no rows selected

scott@CNMMBO> exec dbms_workload_repository.create_snapshot();  -->执行一次快照,写入缓冲区的内容倒snapsho

PL/SQL procedure successfully completed.

scott@CNMMBO> @sql_plan_his     -->依旧无法获得执行计划
Enter value for input_sql_id: 2jbkb5qf92ums

no rows selected

2、修改阙值后的情形

--下面我们将topnsql参数设置为最大值,以确保任意sql只要执行一次即可写入到快照
scott@CNMMBO> exec dbms_workload_repository.modify_snapshot_settings(topnsql=>'MAXIMUM');

PL/SQL procedure successfully completed.

--校验awr配置
scott@CNMMBO> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION                 TOPNSQL
---------- -------------------- ------------------------- ----------
 938506715 +00000 01:00:00.0    +00007 00:00:00.0         MAXIMUM

--先看看dba_hist_sql_plan,此时肯定是不存在,因为没有执行快照
scott@CNMMBO> @sql_plan_his
Enter value for input_sql_id: 2jbkb5qf92ums

no rows selected

--再次执行一下原来的sql语句
scott@CNMMBO> select * from dept where loc='CHICAGO';

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

--此时执行手动创建快照实现写入
scott@CNMMBO> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

--再次查看,sql执行计划已经写入到awr快照
scott@CNMMBO> @sql_plan_his
Enter value for input_sql_id: 2jbkb5qf92ums

 ID OPERATION                 OPTIONS                   OBJECT_NAME      BYTES   CPU_COST    IO_COST
--- ------------------------- ------------------------- --------------- ------- ---------- ----------
  0 SELECT STATEMENT
  1 TABLE ACCESS              FULL                      DEPT                20      36567          3

--同时我们也可以通过DBMS_XPLAN.display_awr查看到相应的执行计划
--Author : Robinson
-- Blog  : http://blog.csdn.net/robinson_0612
scott@CNMMBO> @sql_plan_disp_awr
Enter value for input_sqlid: 2jbkb5qf92ums

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID 2jbkb5qf92ums
--------------------
select * from dept where loc='CHICAGO'

Plan hash value: 3383998547

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| DEPT |     1 |    20 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

13 rows selected.

--恢复缺省值
scott@CNMMBO> exec dbms_workload_repository.modify_snapshot_settings(topnsql=>'DEFAULT');

PL/SQL procedure successfully completed.

3、修改awr阙值的过程 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(    retention   IN  NUMBER    DEFAULT NULL,    interval    IN  NUMBER    DEFAULT NULL,    topnsql     IN  NUMBER    DEFAULT NULL,    dbid        IN  NUMBER    DEFAULT NULL);

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(    retention   IN  NUMBER    DEFAULT NULL,    interval    IN  NUMBER    DEFAULT NULL,    topnsql     IN  VARCHAR2,    dbid        IN  NUMBER    DEFAULT NULL);

--主要给出topnsql,具体可参照Oracle reference topnsql If NUMBER: Top N SQL size. The number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count). The value for this setting will not be affected by the statistics/flush level and will override the system default behavior for the AWR SQL collection. The setting will have a minimum value of 30 and a maximum value of 50,000. Specifying NULL will keep the current setting.

If VARCHAR2: Users are allowed to specify the following values: (DEFAULT, MAXIMUM, N), where N is the number of Top SQL to flush for each SQL criteria. Specifying DEFAULT will revert the system back to the default behavior of Top 30 for statistics level TYPICAL and Top 100 for statistics level ALL. Specifying MAXIMUM will cause the system to capture the complete set of SQL in the cursor cache. Specifying the number N is equivalent to setting the Top N SQL with the NUMBER type. Specifying NULL for this argument will keep the current setting.

4、小结     a、所有当前执行的sql的执行计划位于v$sql_plan视图,按照LRU算法淘汰     b、符合sql捕获条件的sql执行计划在awr快照生成之后会被填充到dba_hist_sql_plan数据字典     c、导致sql执行计划无法从dba_hist_sql_plan获得应考虑修改awr快照配置topnsql参数     d、awr快照同时受到statistics_level参数的影响。如果其值为all时,收集100条top sql,为typical时收集30条

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 视图 v$sql,v$sqlarea,$sqltext,v$sqltext_with_newlines 的差异

          视图v$sql,v$sqlarea,v$sqltext,v$sqltext_with_newlines 是几个经常容易混淆的视图,主要是提供libr...

    Leshami
  • PL/SQL --> 动态SQL调用包中函数或过程

          动态SQL主要是用于针对不同的条件或查询任务来生成不同的SQL语句。最常用的方法是直接使用EXECUTE IMMEDIATE来执行动态SQL语句字符...

    Leshami
  • Oracle 历史SQL语句执行计划的对比与分析

        基于CBO优化器的环境中,SQL执行计划的生成依赖于统计信息的真实与完整。如列的离散度,列上的直方图,索引的可用性,索引上的聚簇因子。当这些信息是真实完...

    Leshami
  • sql格式化工具

    该工具支持oracle、mysql、sql server等关系型数据库,能让你看到sql美化后的清晰的结构,可运用于对复杂SQL语句的分析或者是程序代码优化上,...

    ixiaoyang8
  • JDBC的基本使用流程

    JDBC的基本使用流程: 1 导入jar包: 导入ojdbc6.jar,在项目上右键 builder path–>add to builder path. ...

    葆宁
  • MyBatis系列第1篇:MyBatis未出世之前我们那些痛苦的经历

    MyBatis系列目标:从入门开始开始掌握一个高级开发所需要的MyBatis技能。

    路人甲Java
  • mysql防止网站被sql注入攻击 的3种方法

    mysql数据库一直以来都遭受到sql注入攻击的影响,很多网站,包括目前的PC端以及手机端都在使用php+mysql数据库这种架构,大多数网站受到的攻击都是与s...

    技术分享达人
  • dba麻烦终结者之路

    或许你厌倦了朝五晚六的开发工作,开始考ocp;或许你刚走出象牙塔,立志在数据库管理方面大干一场?经过一翻努力,终于有了份dba的工作,忐忑不安地坐在电脑旁,激动...

    Tony老师
  • JDBC系列:(3)使用Prepared

    py3study
  • JDBC完成修改

    使用流程不变: 导入jar包 加载驱动 创建连接对象 创建sql命名对象 创建sql命令 执行sql命令 关闭资源

    葆宁

扫码关注云+社区

领取腾讯云代金券