exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
exec DBMS_STATS.GATHER_DICTIONARY_STATS(degree=>16);
exec DBMS_STATS.GATHER_DATABASE_STATS(options=>'GATHER AUTO', degree=>16);
exec DBMS_STATS.GATHER_SYSTEM_STATS('EXADATA');
exec DBMS_STATS.GATHER_SYSTEM_STATS;
col pval2 format a32
select * from sys.aux_stats$;
uncollect
SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- --------------------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 03-02-2017 13:10
SYSSTATS_INFO DSTOP 03-02-2017 13:10
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 2676.39155
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- --------------------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 10-30-2018 11:34
SYSSTATS_INFO DSTOP 10-30-2018 11:34
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 2097
SYSSTATS_MAIN IOSEEKTIM 16
SYSSTATS_MAIN IOTFRSPEED 204800
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC 128
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
exec dbms_stats.set_system_stats( 'MBRC', 128);
exec dbms_stats.set_system_stats( 'IOSEEKTIM', 10);
exec dbms_stats.set_system_stats( 'IOTFRSPEED', 204800);
ALTER SESSION FORCE PARALLEL DDL PARALLEL 16;
ALTER SESSION FORCE PARALLEL DML PARALLEL 16;
exec dbms_stats.delete_table_stats('SYS','WRI$_OPTSTAT_SYNOPSIS$');
exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_SYNOPSIS$');
SELECT NVL(TO_CHAR(last_analyzed, 'YYYYMMDD'), 'NO STATS') last_analyzed, COUNT(*) dictionary_tables
FROM dba_tables
WHERE owner = 'SYS'
GROUP BY TO_CHAR(last_analyzed, 'YYYYMMDD')
ORDER BY 1 DESC;
select NVL(TO_CHAR(last_analyzed, 'YYYYMMDD'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'YYYYMMDD')
ORDER BY 1 DESC;
No Responses