1. 首页
  2. IT资讯

[20200214]Printing all table preferences affecting dbms_stats.gather_table_stats

[20200214]Printing all table preferences affecting dbms_stats.gather_table_stats.txt

–//看了连接:https://martincarstenbach.wordpress.com/2020/02/13/printing-all-table-preferences-affecting-dbms_stats-gather_table_stats/
–//测试一下发现仅仅运行在19c,而实际上sys.OPTSTAT_HIST_CONTROL$的sname字段记录参数,修改一下,我从来不写pl/sql,完全不熟悉:

/* Formatted on 2020/2/14 9:46:55 (QP5 v5.269.14213.34769) */
SET SERVEROUTPUT ON VERIFY OFF

PROMPT
PROMPT getting table prefs for &1
PROMPT —————————————-

DECLARE
   v_version   VARCHAR2 (100);
   v_compat    VARCHAR2 (100);

   TYPE prefs_t IS TABLE OF VARCHAR2 (100);

   v_prefs     prefs_t;

   CURSOR prefs_cursor
   IS
        SELECT sname
          FROM sys.OPTSTAT_HIST_CONTROL$
      ORDER BY sname;

   PROCEDURE print_prefs (pi_prefs prefs_t)
   AS
      v_value   VARCHAR2 (100);
   BEGIN
      FOR i IN pi_prefs.FIRST .. pi_prefs.LAST
      LOOP
         v_value :=
            sys.DBMS_STATS.get_prefs
            (
               pname     => pi_prefs (i)
              ,ownname   => USER
              ,tabname   => sys.DBMS_ASSERT.sql_object_name ('&1')
            );
         sys.DBMS_OUTPUT.put_line
         (
            RPAD (pi_prefs (i), 36) || ': ' || v_value
         );
      END LOOP;
   END;
BEGIN
   sys.DBMS_UTILITY.db_version (v_version, v_compat);
   sys.DBMS_OUTPUT.put_line ('VERSION : ' || v_version);

   OPEN prefs_cursor;

   FETCH prefs_cursor BULK COLLECT INTO v_prefs;

   print_prefs (v_prefs);

   CLOSE prefs_cursor;
END;
/

SET SERVEROUTPUT OFF

–//测试看看:
SCOTT@78> @ ver1
PORT_STRING                    VERSION        BANNER
—————————— ————– ——————————————————————————–
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

SCOTT@78> @prefs_t.txt dept
getting table prefs for dept
—————————————
APPROXIMATE_NDV                     : TRUE
AUTOSTATS_TARGET                    : AUTO
CASCADE                             : DBMS_STATS.AUTO_CASCADE
CONCURRENT                          : FALSE
DEBUG                               : 0
DEGREE                              : NULL
ESTIMATE_PERCENT                    : DBMS_STATS.AUTO_SAMPLE_SIZE
GRANULARITY                         : AUTO
INCREMENTAL                         : FALSE
INCREMENTAL_INTERNAL_CONTROL        : TRUE
METHOD_OPT                          : FOR ALL COLUMNS SIZE AUTO
NO_INVALIDATE                       : DBMS_STATS.AUTO_INVALIDATE
PUBLISH                             : TRUE
SKIP_TIME                           :
STALE_PERCENT                       : 10
STATS_RETENTION                     :
SYS_FLAGS                           : 1
TABLE_CACHED_BLOCKS                 : 1
TRACE                               : 0
PL/SQL procedure successfully completed.

SCOTT@book> exec dbms_stats.SET_TABLE_PREFS(ownname=>user,tabname=>'dept',pname=>'STALE_PERCENT',pvalue=>20);
PL/SQL procedure successfully completed.

SCOTT@78> @ prefs_t.txt dept
getting table prefs for dept
—————————————
VERSION : 11.2.0.4.0
APPROXIMATE_NDV                     : TRUE
AUTOSTATS_TARGET                    : AUTO
CASCADE                             : DBMS_STATS.AUTO_CASCADE
CONCURRENT                          : FALSE
DEBUG                               : 0
DEGREE                              : NULL
ESTIMATE_PERCENT                    : DBMS_STATS.AUTO_SAMPLE_SIZE
GRANULARITY                         : AUTO
INCREMENTAL                         : FALSE
INCREMENTAL_INTERNAL_CONTROL        : TRUE
METHOD_OPT                          : FOR ALL COLUMNS SIZE AUTO
NO_INVALIDATE                       : DBMS_STATS.AUTO_INVALIDATE
PUBLISH                             : TRUE
SKIP_TIME                           :
STALE_PERCENT                       : 20
STATS_RETENTION                     :
SYS_FLAGS                           : 1
TABLE_CACHED_BLOCKS                 : 1
TRACE                               : 0
PL/SQL procedure successfully completed.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2675545/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/182926.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code