[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.