[20200214]Printing all table preferences affecting dbms_stats.gather_table_stats

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

--//看了連線：
--//測試一下發現僅僅執行在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.