暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Oracle 19C 实施Optimizer Statistics Advisor建议

Oracle 19C 实施Optimizer Statistics Advisor建议

原创 Asher.HU 2021-02-04
938


您可以使用来自动实施所有建议DBMS_STATS.IMPLEMENT_ADVISOR_TASK，也可以使用来生成可编辑的脚本DBMS_STATS.SCRIPT_ADVISOR_TASK。


 6.1实现Optimizer Statistics Advisor建议的操作

该DBMS_STATS.IMPLEMENT_ADVISOR_TASK功能为指定的Optimizer Statistics Advisor任务实现建议。如果未指定执行名称，则Optimizer Statistics Advisor将使用最新执行。

实施建议的最简单方法是使用DBMS_STATS.IMPLEMENT_ADVISOR_TASK。在这种情况下，无需生成脚本。您可以指定顾问程序忽略现有过滤器，(level=>'ALL')或使用默认值，以接受现有过滤器(level=>'TYPICAL')。

先决条件

要使用DBMS_STATS.IMPLEMENT_ADVISOR_TASK，您必须满足以下先决条件：

  • 要执行此子程序，您必须具有ADVISOR特权。
  • 您必须是任务的所有者。

注意：

该子程序使用调用者的权限执行。

执行此任务的结果取决于执行用户的特权：

  • SYSTEM 水平

    只有同时具有ANALYZE ANY和ANALYZE ANY DICTIONARY特权的用户才能在系统级规则上执行此任务。

  • 操作水平

    结果取决于以下特权：

    • 同时具有ANALYZE ANY和ANALYZE ANY DICTIONARY特权的用户可以执行所有统计信息操作的任务。
    • 与用户的ANALYZE ANY特权，但没有对ANALYZE ANY DICTIONARY权限的用户可以执行此任务相关的任何除统计模式操作SYS。
    • 与用户的ANALYZE ANY DICTIONARY特权，但没有对ANALYZE ANY权限的用户可以执行此任务相关自己的架构和统计业务SYS架构。
    • 既ANALYZE ANY没有ANALYZE ANY DICTIONARY特权又没有特权的用户只能对与自己的架构有关的统计信息操作执行此操作。
  • 对象级别

    用户可以对具有统计信息收集特权的任何对象执行此任务。

要实施顾问操作：

  1. 在SQL * Plus中，以具有必要特权的用户身份登录数据库。
  2. DBMS_STATS.IMPLEMENT_ADVISOR_TASK以以下形式执行函数，其中占位符具有以下定义：
    • tname 是任务的名称。
    • result 是CLOB变量，其中包含已成功实施的建议的列表。
    • filter_lvl是实现的级别：（TYPICAL尊重现有过滤器）或ALL（忽略过滤器）。
    EXECUTE result := DBMS_STATS.IMPLEMENT_ADVISOR_TASK('tname', level => filter_lvl);

    例如，要实现针对该任务的所有建议opt_adv_task1，请使用以下代码：

    VARIABLE b_ret CLOB
DECLARE
  v_tname VARCHAR2(32767);
BEGIN
  v_tname := 'opt_adv_task1';
  :b_ret := DBMS_STATS.IMPLEMENT_ADVISOR_TASK(v_tname);
END;
/
  3. （可选）打印XML输出以确认已实现的操作。

    例如，要打印上一步中返回的XML，请使用以下代码（包括示例输出）：

    SET LONG 10000
SELECT XMLType(:b_ret) AS imp_results FROM DUAL;
    IMP_RESULTS
------------------------------------
<implementation_results>
  <rule NAME="AVOIDSETPROCEDURES">
    <implemented>yes</implemented>
  </rule>
  <rule NAME="USEGATHERSCHEMASTATS">
    <implemented>yes</implemented>
  </rule>
  <rule NAME="AVOIDSETPROCEDURES">
    <implemented>yes</implemented>
  </rule>
  <rule NAME="USEGATHERSCHEMASTATS">
    <implemented>yes</implemented>
  </rule>
  <rule NAME="USEDEFAULTPARAMS">
    <implemented>no</implemented>
  </rule>
  <rule NAME="USEDEFAULTPARAMS">
    <implemented>yes</implemented>
  </rule>
  <rule NAME="NOTUSEINCREMENTAL">
    <implemented>yes</implemented>
  </rule>
</implementation_results>




18.2.6.2使用Optimizer Statistics Advisor生成脚本

该DBMS_STATS.SCRIPT_ADVISOR_TASK函数将生成一个可编辑脚本，其中包含针对指定的Optimizer Statistics Advisor任务的建议。

与不同IMPLEMENT_ADVISOR_TASK，会SCRIPT_ADVISOR_TASK生成一个脚本，您可以在执行之前对其进行编辑。输出脚本包含注释和可执行代码。与一样IMPLEMENT_ADVISOR_TASK，您可以指定顾问忽略现有过滤器(level=>'ALL')或使用默认值，该默认值优先于现有过滤器(level=>'TYPICAL')。您可以指定该函数将脚本作为CLOB和文件（或仅作为CLOB）返回。

先决条件

要使用该DBMS_STATS.SCRIPT_ADVISOR_TASK功能，您必须满足以下先决条件：

  • 要执行此子程序，您必须具有ADVISOR特权。
  • 您必须是任务的所有者。

注意：

该子程序使用调用者的权限执行。

执行此任务的结果取决于执行用户的特权：

  • SYSTEM 水平

    只有同时具有ANALYZE ANY和ANALYZE ANY DICTIONARY特权的用户才能在系统级规则上执行此任务。

  • 操作水平

    结果取决于以下特权：

    • 同时具有ANALYZE ANY和ANALYZE ANY DICTIONARY特权的用户可以执行所有统计信息操作的任务。
    • 与用户的ANALYZE ANY特权，但没有对ANALYZE ANY DICTIONARY权限的用户可以执行此任务相关的任何除统计模式操作SYS。
    • 与用户的ANALYZE ANY DICTIONARY特权，但没有对ANALYZE ANY权限的用户可以执行此任务相关自己的架构和统计业务SYS架构。
    • 既ANALYZE ANY没有ANALYZE ANY DICTIONARY特权又没有特权的用户只能对与自己的架构有关的统计信息操作执行此操作。
  • 对象级别

    用户可以对具有统计信息收集特权的任何对象执行此任务。



生成顾问脚本：

  1. 在SQL * Plus中，以具有ADVISOR特权的用户身份登录数据库。
  2. DBMS_STATS.SCRIPT_ADVISOR_TASK以以下形式执行函数，其中占位符具有以下定义：
    • tname 是任务的名称。
    • exec 是执行的名称（默认为null）。
    • dir 是目录的名称（默认为null）。
    • result 是CLOB变量，其中包含已成功实施的建议的列表。
    • filter_lvl是实现的级别：（TYPICAL尊重现有过滤器）或ALL（忽略过滤器）。
    EXEC result := DBMS_STATS.SCRIPT_ADVISOR_TASK('tname', execution_name => 'exec', 
  dir_name => 'dir', level => 'filter_lvl');

    例如，要生成包含任务建议的脚本opt_adv_task1，请使用以下代码：

    VARIABLE b_script CLOB
DECLARE
  v_tname VARCHAR2(32767);
BEGIN
  v_tname := 'opt_adv_task1';
  :b_script := DBMS_STATS.SCRIPT_ADVISOR_TASK(v_tname);
END;
/

    注意：

    如果未指定执行名称，则Optimizer Statistics Advisor将使用最新执行。

  3. 打印脚本。

    例如，要打印上一步返回的脚本，请使用以下代码（包括示例输出）：

    DECLARE
  v_len    NUMBER(10);
  v_offset NUMBER(10) :=1;
  v_amount NUMBER(10) :=10000;
BEGIN
  v_len := DBMS_LOB.getlength(:b_script);
  WHILE (v_offset < v_len)
  LOOP
    DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(:b_script,v_amount,v_offset));
    v_offset := v_offset + v_amount;
  END LOOP;
END;
/


    以下示例显示了一个示例脚本：

    -- Script generated for the recommendations from execution EXEC_23
-- in the statistics advisor task OPT_ADV_TASK1
-- Script version 12.2

-- No scripts will be provided for the rule AVOIDSETPROCEDURES.  Please check the report 
-- for more details.
-- No scripts will be provided for the rule USEGATHERSCHEMASTATS. Please check the report 
-- for more details.
-- No scripts will be provided for the rule AVOIDINEFFICIENTSTATSOPRSEQ. Please check the 
-- report for more details.
-- No scripts will be provided for the rule AVOIDUNNECESSARYSTATSCOLLECTION. Please check 
-- the report for more details.
-- No scripts will be provided for the rule GATHERSTATSAFTERBULKDML. Please check the 
-- report for more details.
-- No scripts will be provided for the rule AVOIDDROPRECREATE. Please check the report 
-- for more details.
-- No scripts will be provided for the rule AVOIDOUTOFRANGE. Please check the report 
-- for more details.
-- No scripts will be provided for the rule AVOIDANALYZETABLE. Please check the report 
-- for more details.
-- No scripts will be provided for the rule AVOIDSETPROCEDURES. Please check the report 
-- for more details.
-- No scripts will be provided for the rule USEGATHERSCHEMASTATS. Please check the report 
-- for more details.
-- No scripts will be provided for the rule AVOIDINEFFICIENTSTATSOPRSEQ. Please check the 
-- report for more details.
-- No scripts will be provided for the rule AVOIDUNNECESSARYSTATSCOLLECTION. Please check 
-- the report for more details.
-- No scripts will be provided for the rule GATHERSTATSAFTERBULKDML. Please check the 
-- report for more details.
-- No scripts will be provided for the rule AVOIDDROPRECREATE. Please check the report 
-- for more details.
-- No scripts will be provided for the rule AVOIDOUTOFRANGE. Please check the report 
-- for more details.
-- No scripts will be provided for the rule AVOIDANALYZETABLE. Please check the report 
-- for more details.

-- Scripts for rule USEDEFAULTPARAMS
-- Rule Description: Use Default Parameters in Statistics Collection Procedures
-- Use the default preference value for parameters

begin dbms_stats.set_global_prefs('PREFERENCE_OVERRIDES_PARAMETER', 'TRUE'); end;
/

-- Scripts for rule USEDEFAULTOBJECTPREFERENCE
-- Rule Description: Use Default Object Preference for statistics collection
-- Setting object-level preferences to default values
-- setting CASCADE to default value for object level preference
-- setting ESTIMATE_PERCENT to default value for object level preference
-- setting METHOD_OPT to default value for object level preference
-- setting GRANULARITY to default value for object level preference
-- setting NO_INVALIDATE to default value for object levelpreference

-- Scripts for rule USEINCREMENTAL
-- Rule Description: Statistics should be maintained incrementally when it is beneficial.
-- Turn on the incremental option for those objects for which using incremental is helpful.

-- Scripts for rule UNLOCKNONVOLATILETABLE
-- Rule Description: Statistics for objects with non-volatile should not be locked
-- Unlock statistics for objects that are not volatile.

-- Scripts for rule LOCKVOLATILETABLE
-- Rule Description: Statistics for objects with volatile data should be locked
-- Lock statistics for volatile objects.

-- Scripts for rule NOTUSEINCREMENTAL
-- Rule Description: Statistics should not be maintained incrementally when it is not
   beneficial
-- Turn off incremental option for those objects for which using incremental is not helpful.

begin dbms_stats.set_table_prefs('SH', 'CAL_MONTH_SALES_MV', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'CHANNELS', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'COUNTRIES', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'CUSTOMERS', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'DIMENSION_EXCEPTIONS', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'FWEEK_PSCAT_SALES_MV', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'PRODUCTS', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'PROMOTIONS', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'SUPPLEMENTARY_DEMOGRAPHICS', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'TIMES', 'INCREMENTAL', 'FALSE'); end;
/

-- Scripts for rule USEAUTODEGREE
-- Rule Description: Use Auto Degree for statistics collection
-- Turn on auto degree for those objects for which using auto degree is helpful.

-- Scripts for rule AVOIDSTALESTATS
-- Rule Description: Avoid objects with stale or no statistics
-- Gather statistics for those objcts that are missing or have no statistics.

-- Scripts for rule MAINTAINSTATSCONSISTENCY
-- Rule Description: Statistics of dependent objects should be consistent
-- Gather statistics for those objcts that are missing or have no statistics.

oracle19csql优化
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论