About Oracle Statistics

于 2023-02-02 11:05:30 发布
阅读量327 收藏
点赞数
分类专栏： Tuning SQL优化 DBA 文章标签： oracle 数据库 dba
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u011868279/article/details/128844151
版权
DBA 同时被 3 个专栏收录
183 篇文章 13 订阅
订阅专栏
SQL优化
17 篇文章 2 订阅
订阅专栏
Tuning
5 篇文章 0 订阅
订阅专栏

1. What are statistics?

Answer: Input to the Cost-Based Optimizer(CBO),Provide information on 

User objects, Table, Partition,Subpartition ,Column ,Index, Index Partition, Index Subpartition, System, Dictionary , Memory structure(X$)

Statistics on a table are considered stale when more than STALE_PERCENT (default 10%) of the rows are changed (total number of inserts, deletes, updates) in the table.

Oracle monitors the DML activity for all tables and records it in the SGA.

The monitoring information is periodically flushed to disk, and is exposed in the *_TAB_MODIFICATIONS view.

2.Why do we care about statistics?

Poor statisitics usually lead to poor plans.

Collecting good quality stats is not straightforward

Collecting good quality stats may be time consuming.

Improving statistics quality improves the chance to find an optimal plan(usually)

The higher the sample the higher the accuracy

The higher the sample the longer is takes to collect

The longer it takes the less frequent we can collect fresh stats!

if your data changes frequently, we need to care about statistics.

if you have plenty of resources:

Gather statistics often and with a very large sample size.

If your resources are limited :

Use AUTO_SAMPLE_SIZE(11g)

Use a smaller sample size(try to avoid it)

If your data doesn't change frequently:

Gahter statistics less often and with a very large sample size.

---In 10g avoid AUTO_SAMPLE_SIZE

exec dbms_stats.gather_table_stats('owner', 'table_name', estimate_percent => NNN,granularity => “it depends”);

----In 11g use AUTO_SAMPLE_SIZE but keep an eye open. 
exec dbms_stats.gather_table_stats('owner', 'table_name');

About *_TAB_MODIFICATIONS

when querying *_TAB_MODIFICATIONS view you should ensure that you run DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO before doing so in order to obtain accurate results.

--  exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; -- 

[oracle@oracle-db-19c ~]$ 
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 2 09:37:33 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> 
SQL> 
SQL> select table_name,inserts,updates,deletes from dba_tab_modifications where table_name='emp';

no rows selected

SQL> 
SQL> 
SQL> 
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> 
SQL> select table_name,inserts,updates,deletes from dba_tab_modifications where table_name='emp';

no rows selected

SQL> show user;
USER is "SYS"
SQL>

Identify STALE STATS:

SQL> col owner format a30
SQL> col TABLE_NAME for a30
SQL> col PARTITION_NAME for a20
SQL> col SUBPARTITION_NAME for a20
SQL> select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from DBA_TAB_STATISTICS where STALE_STATS='YES';

OWNER                          TABLE_NAME                     PARTITION_NAME       SUBPARTITION_NAME      NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ -------------------- -------------------- ---------- ---------------
SYS                            SEG$                                                                           5094 01-FEB-23
SYS                            IND$                                                                           2885 29-JAN-23
SYS                            TAB$                                                                           2219 31-JAN-23
SYS                            HISTGRM$                                                                      62858 01-FEB-23
SYS                            PARTOBJ$                                                                        275 29-JAN-23
SYS                            PROPS$                                                                           43 01-FEB-23
SYS                            STATS_TARGET$                                                                   792 01-FEB-23
SYS                            COL_USAGE$                                                                     3398 01-FEB-23
SYS                            COL_GROUP_USAGE$                                                                 13 01-FEB-23
SYS                            MON_MODS_ALL$                                                                   228 01-FEB-23
SYS                            WRI$_OPTSTAT_OPR                                                                 16 01-FEB-23
SYS                            WRI$_OPTSTAT_OPR_TASKS                                                         2620 01-FEB-23
SYS                            OPTSTAT_HIST_CONTROL$                                                            45 01-FEB-23
SYS                            OPT_FINDING$                                                                     28 01-FEB-23
SYS                            OPT_DIRECTIVE_OWN$                                                               24 01-FEB-23
SYS                            OPT_DIRECTIVE$                                                                   28 01-FEB-23
SYS                            EXP_OBJ$                                                                          9 01-FEB-23
SYS                            EXP_STAT$                                                                        47 01-FEB-23
SYS                            TABPART$                                                                        352 01-FEB-23
SYS                            INDPART$                                                                        233 01-FEB-23
SYS                            LOBFRAG$                                                                         27 31-JAN-23
SYS                            WRI$_ADV_TASKS                                                                   92 01-FEB-23
SYS                            WRI$_ADV_PARAMETERS                                                            6631 01-FEB-23
SYS                            WRI$_ADV_EXECUTIONS                                                              97 01-FEB-23
SYS                            WRI$_ADV_EXEC_PARAMETERS                                                          5 01-FEB-23
SYS                            WRI$_ADV_OBJECTS                                                                171 01-FEB-23
SYS                            WRI$_ADV_FINDINGS                                                               159 01-FEB-23
SYS                            WRI$_ADV_MESSAGE_GROUPS                                                         609 31-JAN-23
SYS                            WRI$_ADV_ADDM_TASKS                                                              86 01-FEB-23
SYS                            WRI$_ADV_ADDM_INST                                                               86 01-FEB-23
SYS                            WRI$_ADV_ADDM_FDG                                                               140 01-FEB-23
SYS                            WRI$_ADV_ADDM_TODO                                                                0 01-FEB-23
SYS                            SCHEDULER$_CLASS                                                                 15 22-JAN-23
SYS                            SCHEDULER$_JOB                                                                   25 01-FEB-23
SYS                            SCHEDULER$_WINDOW                                                                 9 01-FEB-23
SYS                            SCHEDULER$_WINDOW_DETAILS                                                         5 01-FEB-23
SYS                            WRI$_DBU_CPU_USAGE_SAMPLE                                                         2 01-FEB-23
SYS                            KET$_AUTOTASK_STATUS                                                              1 01-FEB-23
SYS                            KET$_CLIENT_TASKS                                                                 3 01-FEB-23
SYS                            WRI$_SQLMON_USAGE                                                                 1 01-FEB-23
SYS                            WRH$_DATAFILE                                                                    48 31-JAN-23
SYS                            WRH$_IOSTAT_FUNCTION                                                            540 01-FEB-23
SYS                            WRH$_IOSTAT_FILETYPE                                                            828 01-FEB-23
SYS                            WRH$_IOSTAT_DETAIL                                                              861 01-FEB-23
SYS                            WRH$_SQLSTAT                                                                   3678 01-FEB-23
SYS                            WRH$_SQLTEXT                                                                    863 01-FEB-23
SYS                            WRH$_SQL_SUMMARY                                                                140 01-FEB-23
SYS                            WRH$_SQL_BIND_METADATA                                                         3980 01-FEB-23
SYS                            WRH$_OPTIMIZER_ENV                                                              459 01-FEB-23
SYS                            WRH$_SYSTEM_EVENT                                                              5189 01-FEB-23
SYS                            WRH$_CON_SYSTEM_EVENT                                                          9083 01-FEB-23
SYS                            WRH$_BG_EVENT_SUMMARY                                                          4088 01-FEB-23
SYS                            WRH$_CHANNEL_WAITS                                                              144 01-FEB-23
SYS                            WRH$_WAITSTAT                                                                  2790 01-FEB-23
SYS                            WRH$_ENQUEUE_STAT                                                              3867 01-FEB-23
SYS                            WRH$_LATCH                                                                    40090 01-FEB-23
SYS                            WRH$_LATCH_MISSES_SUMMARY                                                      2373 01-FEB-23
SYS                            WRH$_EVENT_HISTOGRAM                                                          49062 01-FEB-23
SYS                            WRH$_MUTEX_SLEEP                                                               1519 01-FEB-23
SYS                            WRH$_LIBRARYCACHE                                                              1013 01-FEB-23
SYS                            WRH$_DB_CACHE_ADVICE                                                            785 01-FEB-23
SYS                            WRH$_BUFFER_POOL_STATISTICS                                                      36 01-FEB-23
SYS                            WRH$_ROWCACHE_SUMMARY                                                          2432 01-FEB-23
SYS                            WRH$_SGA                                                                        144 01-FEB-23
SYS                            WRH$_SGASTAT                                                                   1200 01-FEB-23
SYS                            WRH$_PGASTAT                                                                    572 01-FEB-23
SYS                            WRH$_PROCESS_MEMORY_SUMMARY                                                     372 01-FEB-23
SYS                            WRH$_RESOURCE_LIMIT                                                             792 01-FEB-23
SYS                            WRH$_SHARED_POOL_ADVICE                                                         930 01-FEB-23
SYS                            WRH$_SQL_WORKAREA_HISTOGRAM                                                     268 01-FEB-23
SYS                            WRH$_PGA_TARGET_ADVICE                                                          504 01-FEB-23
SYS                            WRH$_SGA_TARGET_ADVICE                                                          213 01-FEB-23
SYS                            WRH$_INSTANCE_RECOVERY                                                           36 01-FEB-23
SYS                            WRH$_RECOVERY_PROGRESS                                                            1 29-JAN-23
SYS                            WRH$_THREAD                                                                      36 01-FEB-23
SYS                            WRH$_SYSSTAT                                                                  77368 01-FEB-23
SYS                            WRH$_CON_SYSSTAT                                                              23139 01-FEB-23
SYS                            WRH$_SYS_TIME_MODEL                                                            1178 01-FEB-23
SYS                            WRH$_CON_SYS_TIME_MODEL                                                        6030 01-FEB-23
SYS                            WRH$_OSSTAT                                                                     950 01-FEB-23
SYS                            WRH$_PARAMETER                                                                18782 01-FEB-23
SYS                            WRH$_MVPARAMETER                                                                273 01-FEB-23
SYS                            WRH$_UNDOSTAT                                                                   178 01-FEB-23
SYS                            WRH$_SEG_STAT                                                                  3005 01-FEB-23
SYS                            WRH$_SEG_STAT_OBJ                                                               570 01-FEB-23
SYS                            WRH$_SYSMETRIC_HISTORY                                                        37467 01-FEB-23
SYS                            WRH$_SYSMETRIC_SUMMARY                                                         5796 01-FEB-23
SYS                            WRH$_CON_SYSMETRIC_SUMMARY                                                     5508 01-FEB-23
SYS                            WRH$_ACTIVE_SESSION_HISTORY                                                    6002 01-FEB-23
SYS                            WRH$_TABLESPACE_STAT                                                            591 01-FEB-23
SYS                            WRH$_LOG                                                                        108 01-FEB-23
SYS                            WRH$_TABLESPACE                                                                  42 31-JAN-23
SYS                            WRH$_TABLESPACE_SPACE_USAGE                                                     636 01-FEB-23
SYS                            WRH$_SERVICE_NAME                                                                14 01-FEB-23
SYS                            WRH$_SERVICE_STAT                                                              6612 01-FEB-23
SYS                            WRH$_SERVICE_WAIT_CLASS                                                        1018 01-FEB-23
SYS                            WRI$_SCH_CONTROL                                                                  1 01-FEB-23
SYS                            WRI$_SCH_VOTES                                                                    7 01-FEB-23
SYS                            WRH$_RSRC_CONSUMER_GROUP                                                        228 01-FEB-23
SYS                            WRH$_RSRC_PLAN                                                                  157 01-FEB-23
SYS                            WRH$_RSRC_METRIC                                                                422 01-FEB-23
SYS                            WRH$_RSRC_PDB_METRIC                                                            144 01-FEB-23
SYS                            WRH$_MEM_DYNAMIC_COMP                                                           792 01-FEB-23
SYS                            WRH$_DISPATCHER                                                                  36 01-FEB-23
SYS                            WRH$_SHARED_SERVER_SUMMARY                                                       36 01-FEB-23
SYS                            WRM$_WR_USAGE                                                                     1 01-FEB-23
SYS                            WRM$_DATABASE_INSTANCE                                                            5 01-FEB-23
SYS                            WRM$_SNAPSHOT                                                                    35 01-FEB-23
SYS                            WRM$_SNAPSHOT_DETAILS                                                          3787 01-FEB-23
SYS                            WRM$_SNAP_ERROR                                                                  17 01-FEB-23
SYS                            WRM$_PDB_IN_SNAP                                                                152 01-FEB-23
SYS                            WRM$_WR_CONTROL                                                                   1 01-FEB-23
SYS                            WRH$_PROCESS_WAITTIME                                                          7155 01-FEB-23
SYS                            WRH$_SESS_NETWORK                                                                 2 29-JAN-23
SYS                            WRI$_OPTSTAT_HISTHEAD_HISTORY                                                 18955 01-FEB-23
SYS                            WRI$_OPTSTAT_HISTGRM_HISTORY                                                 109249 01-FEB-23
SYS                            WRH$_CON_SYSSTAT               WRH$_CON_SYSSTAT_109                           23139 01-FEB-23
                                                              3429351_138

SYS                            WRH$_CON_SYSTEM_EVENT          WRH$_CON_SYSTEM_EVEN                            9083 01-FEB-23
                                                              T_1093429351_138

SYS                            WRH$_PROCESS_WAITTIME          WRH$_PROCESS_WAITTIM                            7155 01-FEB-23
                                                              E_1093429351_138

SYS                            WRH$_SESS_NETWORK              WRH$_SESS_NETWORK_10                               2 29-JAN-23
                                                              93429351_138

SYS                            WRH$_SYSMETRIC_HISTORY         WRH$_SYSMETRIC_HISTO                           37467 01-FEB-23
                                                              RY_1093429351_138

SYS                            WRH$_TABLESPACE_STAT           WRH$_TABLESPACE_STAT                             591 01-FEB-23
                                                              _1093429351_138

SYS                            WRH$_OSSTAT                    WRH$_OSSTAT_10934293                             950 01-FEB-23
                                                              51_138

SYS                            WRH$_SYS_TIME_MODEL            WRH$_SYS_TIME_MODEL_                            1178 01-FEB-23
                                                              1093429351_138

SYS                            WRH$_SERVICE_WAIT_CLASS        WRH$_SERVICE_WAIT_CL                            1018 01-FEB-23
                                                              ASS_1093429351_138

SYS                            WRH$_EVENT_HISTOGRAM           WRH$_EVENT_HISTOGRAM                           49062 01-FEB-23
                                                              _1093429351_138

SYS                            WRH$_MVPARAMETER               WRH$_MVPARAMETER_109                             273 01-FEB-23
                                                              3429351_138

SYS                            WRM$_PDB_IN_SNAP               WRM$_PDB_IN_SNAP_109                             152 01-FEB-23
                                                              3429351_138

SYS                            WRH$_CON_SYS_TIME_MODEL        WRH$_CON_SYS_TIME_MO                            6030 01-FEB-23
                                                              DEL_1093429351_138

SYS                            WRP$_REPORTS_TIME_BANDS        SYS_P901                                          27 01-FEB-23
SYS                            WRP$_REPORTS                   SYS_P921                                          26 01-FEB-23
SYS                            WRP$_REPORTS_DETAILS           SYS_P922                                          26 01-FEB-23
SYS                            WRH$_SQLSTAT                   WRH$_SQLSTAT_1093429                            3678 01-FEB-23
                                                              351_138

SYS                            WRH$_SYSTEM_EVENT              WRH$_SYSTEM_EVENT_10                            5189 01-FEB-23
                                                              93429351_138

SYS                            WRH$_WAITSTAT                  WRH$_WAITSTAT_109342                            2790 01-FEB-23
                                                              9351_138

SYS                            WRH$_LATCH                     WRH$_LATCH_109342935                           40090 01-FEB-23
                                                              1_138

SYS                            WRH$_LATCH_MISSES_SUMMARY      WRH$_LATCH_MISSES_SU                            2373 01-FEB-23
                                                              MMARY_1093429351_138

SYS                            WRH$_DB_CACHE_ADVICE           WRH$_DB_CACHE_ADVICE                             785 01-FEB-23
                                                              _1093429351_138

SYS                            WRH$_ROWCACHE_SUMMARY          WRH$_ROWCACHE_SUMMAR                            2432 01-FEB-23
                                                              Y_1093429351_138

SYS                            WRH$_SGASTAT                   WRH$_SGASTAT_1093429                            1200 01-FEB-23
                                                              351_138

SYS                            WRH$_SYSSTAT                   WRH$_SYSSTAT_1093429                           77368 01-FEB-23
                                                              351_138

SYS                            WRH$_PARAMETER                 WRH$_PARAMETER_10934                           18782 01-FEB-23
                                                              29351_138

SYS                            WRH$_SEG_STAT                  WRH$_SEG_STAT_109342                            3005 01-FEB-23
                                                              9351_138

SYS                            WRH$_SERVICE_STAT              WRH$_SERVICE_STAT_10                            6612 01-FEB-23
                                                              93429351_138


144 rows selected.

SQL> 
SQL> 


----
OR
----

SQL> select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where OWNER='&OWNER' AND STALE_STATS='YES';
Enter value for owner: SYS
old   1: select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where OWNER='&OWNER' AND STALE_STATS='YES'
new   1: select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where OWNER='SYS' AND STALE_STATS='YES'

OWNER                          TABLE_NAME                     LAST_ANALYZED   STALE_STATS
------------------------------ ------------------------------ --------------- ---------------------
SYS                            WRH$_WAITSTAT                  01-FEB-23       YES
SYS                            WRH$_SQL_SUMMARY               01-FEB-23       YES
SYS                            WRH$_LOG                       01-FEB-23       YES
SYS                            STATS_TARGET$                  01-FEB-23       YES
SYS                            WRH$_ACTIVE_SESSION_HISTORY    01-FEB-23       YES
SYS                            WRH$_IOSTAT_DETAIL             01-FEB-23       YES
SYS                            WRH$_RSRC_PLAN                 01-FEB-23       YES
SYS                            WRH$_CON_SYSMETRIC_SUMMARY     01-FEB-23       YES
SYS                            WRH$_SYS_TIME_MODEL            01-FEB-23       YES
SYS                            WRH$_MVPARAMETER               01-FEB-23       YES
SYS                            SCHEDULER$_WINDOW              01-FEB-23       YES
SYS                            OPT_DIRECTIVE$                 01-FEB-23       YES
SYS                            WRH$_INSTANCE_RECOVERY         01-FEB-23       YES
SYS                            SEG$                           01-FEB-23       YES
SYS                            WRH$_TABLESPACE                31-JAN-23       YES
SYS                            WRH$_PROCESS_MEMORY_SUMMARY    01-FEB-23       YES
SYS                            WRM$_SNAPSHOT                  01-FEB-23       YES
SYS                            WRH$_SGA_TARGET_ADVICE         01-FEB-23       YES
SYS                            WRH$_SERVICE_STAT              01-FEB-23       YES
SYS                            WRH$_RSRC_METRIC               01-FEB-23       YES
SYS                            WRH$_SQL_WORKAREA_HISTOGRAM    01-FEB-23       YES
SYS                            WRH$_SGASTAT                   01-FEB-23       YES
SYS                            WRH$_SQLSTAT                   01-FEB-23       YES
SYS                            WRH$_PGASTAT                   01-FEB-23       YES
SYS                            WRI$_ADV_TASKS                 01-FEB-23       YES
SYS                            PARTOBJ$                       29-JAN-23       YES
SYS                            EXP_OBJ$                       01-FEB-23       YES
SYS                            PROPS$                         01-FEB-23       YES
SYS                            WRI$_OPTSTAT_OPR_TASKS         01-FEB-23       YES
SYS                            TABPART$                       01-FEB-23       YES
SYS                            WRH$_ROWCACHE_SUMMARY          01-FEB-23       YES
SYS                            WRH$_RSRC_CONSUMER_GROUP       01-FEB-23       YES
SYS                            WRH$_MEM_DYNAMIC_COMP          01-FEB-23       YES
SYS                            WRH$_SEG_STAT_OBJ              01-FEB-23       YES
SYS                            WRH$_SERVICE_WAIT_CLASS        01-FEB-23       YES
SYS                            WRH$_SYSSTAT                   01-FEB-23       YES
SYS                            WRH$_SYSTEM_EVENT              01-FEB-23       YES
SYS                            WRM$_DATABASE_INSTANCE         01-FEB-23       YES
SYS                            WRH$_CON_SYS_TIME_MODEL        01-FEB-23       YES
SYS                            WRH$_TABLESPACE_SPACE_USAGE    01-FEB-23       YES
SYS                            WRH$_DATAFILE                  31-JAN-23       YES
SYS                            WRH$_CHANNEL_WAITS             01-FEB-23       YES
SYS                            WRH$_DB_CACHE_ADVICE           01-FEB-23       YES
SYS                            WRH$_SHARED_SERVER_SUMMARY     01-FEB-23       YES
SYS                            WRH$_RSRC_PDB_METRIC           01-FEB-23       YES
SYS                            WRH$_SYSMETRIC_HISTORY         01-FEB-23       YES
SYS                            WRI$_ADV_ADDM_TASKS            01-FEB-23       YES
SYS                            KET$_CLIENT_TASKS              01-FEB-23       YES
SYS                            WRI$_ADV_OBJECTS               01-FEB-23       YES
SYS                            WRH$_ENQUEUE_STAT              01-FEB-23       YES
SYS                            WRH$_EVENT_HISTOGRAM           01-FEB-23       YES
SYS                            WRH$_CON_SYSTEM_EVENT          01-FEB-23       YES
SYS                            SCHEDULER$_WINDOW_DETAILS      01-FEB-23       YES
SYS                            WRH$_THREAD                    01-FEB-23       YES
SYS                            OPT_DIRECTIVE_OWN$             01-FEB-23       YES
SYS                            OPT_FINDING$                   01-FEB-23       YES
SYS                            OPTSTAT_HIST_CONTROL$          01-FEB-23       YES
SYS                            WRH$_BUFFER_POOL_STATISTICS    01-FEB-23       YES
SYS                            SCHEDULER$_JOB                 01-FEB-23       YES
SYS                            COL_GROUP_USAGE$               01-FEB-23       YES
SYS                            COL_USAGE$                     01-FEB-23       YES
SYS                            WRH$_IOSTAT_FUNCTION           01-FEB-23       YES
SYS                            WRH$_OSSTAT                    01-FEB-23       YES
SYS                            WRM$_PDB_IN_SNAP               01-FEB-23       YES
SYS                            WRM$_SNAP_ERROR                01-FEB-23       YES
SYS                            WRH$_LIBRARYCACHE              01-FEB-23       YES
SYS                            WRI$_ADV_ADDM_TODO             01-FEB-23       YES
SYS                            TAB$                           31-JAN-23       YES
SYS                            HISTGRM$                       01-FEB-23       YES
SYS                            WRH$_UNDOSTAT                  01-FEB-23       YES
SYS                            MON_MODS_ALL$                  01-FEB-23       YES
SYS                            INDPART$                       01-FEB-23       YES
SYS                            WRH$_TABLESPACE_STAT           01-FEB-23       YES
SYS                            WRH$_PGA_TARGET_ADVICE         01-FEB-23       YES
SYS                            WRI$_ADV_EXEC_PARAMETERS       01-FEB-23       YES
SYS                            WRH$_LATCH_MISSES_SUMMARY      01-FEB-23       YES
SYS                            WRI$_OPTSTAT_HISTHEAD_HISTORY  01-FEB-23       YES
SYS                            WRH$_SEG_STAT                  01-FEB-23       YES
SYS                            WRH$_SYSMETRIC_SUMMARY         01-FEB-23       YES
SYS                            WRI$_OPTSTAT_HISTGRM_HISTORY   01-FEB-23       YES
SYS                            WRI$_ADV_PARAMETERS            01-FEB-23       YES
SYS                            WRH$_OPTIMIZER_ENV             01-FEB-23       YES
SYS                            WRI$_ADV_FINDINGS              01-FEB-23       YES
SYS                            WRH$_RECOVERY_PROGRESS         29-JAN-23       YES
SYS                            EXP_STAT$                      01-FEB-23       YES
SYS                            WRH$_SQL_BIND_METADATA         01-FEB-23       YES
SYS                            WRH$_CON_SYSSTAT               01-FEB-23       YES
SYS                            SCHEDULER$_CLASS               22-JAN-23       YES
SYS                            WRH$_SGA                       01-FEB-23       YES
SYS                            WRI$_OPTSTAT_OPR               01-FEB-23       YES
SYS                            WRH$_SESS_NETWORK              29-JAN-23       YES
SYS                            WRH$_SQLTEXT                   01-FEB-23       YES
SYS                            WRH$_BG_EVENT_SUMMARY          01-FEB-23       YES
SYS                            WRI$_ADV_ADDM_FDG              01-FEB-23       YES
SYS                            LOBFRAG$                       31-JAN-23       YES
SYS                            IND$                           29-JAN-23       YES
SYS                            WRI$_ADV_ADDM_INST             01-FEB-23       YES
SYS                            WRH$_RESOURCE_LIMIT            01-FEB-23       YES
SYS                            WRM$_WR_CONTROL                01-FEB-23       YES
SYS                            WRI$_DBU_CPU_USAGE_SAMPLE      01-FEB-23       YES
SYS                            WRH$_PROCESS_WAITTIME          01-FEB-23       YES
SYS                            WRH$_MUTEX_SLEEP               01-FEB-23       YES
SYS                            WRH$_LATCH                     01-FEB-23       YES
SYS                            WRH$_DISPATCHER                01-FEB-23       YES
SYS                            WRH$_IOSTAT_FILETYPE           01-FEB-23       YES
SYS                            WRH$_SHARED_POOL_ADVICE        01-FEB-23       YES
SYS                            WRH$_PARAMETER                 01-FEB-23       YES
SYS                            KET$_AUTOTASK_STATUS           01-FEB-23       YES
SYS                            WRI$_ADV_EXECUTIONS            01-FEB-23       YES
SYS                            WRM$_WR_USAGE                  01-FEB-23       YES
SYS                            WRI$_SCH_VOTES                 01-FEB-23       YES
SYS                            WRI$_SCH_CONTROL               01-FEB-23       YES
SYS                            WRI$_ADV_MESSAGE_GROUPS        31-JAN-23       YES
SYS                            WRM$_SNAPSHOT_DETAILS          01-FEB-23       YES
SYS                            WRH$_SERVICE_NAME              01-FEB-23       YES
SYS                            WRI$_SQLMON_USAGE              01-FEB-23       YES
SYS                            WRH$_SYS_TIME_MODEL            01-FEB-23       YES
SYS                            WRH$_MVPARAMETER               01-FEB-23       YES
SYS                            WRH$_WAITSTAT                  01-FEB-23       YES
SYS                            WRP$_REPORTS                   01-FEB-23       YES
SYS                            WRH$_SGASTAT                   01-FEB-23       YES
SYS                            WRH$_SQLSTAT                   01-FEB-23       YES
SYS                            WRH$_SERVICE_STAT              01-FEB-23       YES
SYS                            WRP$_REPORTS_TIME_BANDS        01-FEB-23       YES
SYS                            WRH$_ROWCACHE_SUMMARY          01-FEB-23       YES
SYS                            WRH$_SERVICE_WAIT_CLASS        01-FEB-23       YES
SYS                            WRH$_CON_SYS_TIME_MODEL        01-FEB-23       YES
SYS                            WRH$_SYSSTAT                   01-FEB-23       YES
SYS                            WRH$_SYSTEM_EVENT              01-FEB-23       YES
SYS                            WRH$_EVENT_HISTOGRAM           01-FEB-23       YES
SYS                            WRH$_SYSMETRIC_HISTORY         01-FEB-23       YES
SYS                            WRH$_DB_CACHE_ADVICE           01-FEB-23       YES
SYS                            WRH$_CON_SYSTEM_EVENT          01-FEB-23       YES
SYS                            WRH$_OSSTAT                    01-FEB-23       YES
SYS                            WRP$_REPORTS_DETAILS           01-FEB-23       YES
SYS                            WRH$_TABLESPACE_STAT           01-FEB-23       YES
SYS                            WRM$_PDB_IN_SNAP               01-FEB-23       YES
SYS                            WRH$_LATCH_MISSES_SUMMARY      01-FEB-23       YES
SYS                            WRH$_SEG_STAT                  01-FEB-23       YES
SYS                            WRH$_SESS_NETWORK              29-JAN-23       YES
SYS                            WRH$_CON_SYSSTAT               01-FEB-23       YES
SYS                            WRH$_PROCESS_WAITTIME          01-FEB-23       YES
SYS                            WRH$_LATCH                     01-FEB-23       YES
SYS                            WRH$_PARAMETER                 01-FEB-23       YES

144 rows selected.

SQL>

Gather STATS

CASCADE => TRUE : Gather statistics on the indexes as well. If not used Oracle will determine whether to collect it or not.
DEGREE => 4: Degree of parallelism.
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE : (DEFAULT) Auto set the sample size % for skew(distinct) values (accurate and faster than setting a manual sample size).
METHOD_OPT=> : For gathering Histograms:
FOR COLUMNS SIZE AUTO : You can specify one column between “” instead of all columns.
FOR ALL COLUMNS SIZE REPEAT : Prevent deletion of histograms and collect it only for columns already have histograms.
FOR ALL COLUMNS : Collect histograms on all columns.
FOR ALL COLUMNS SIZE SKEWONLY : Collect histograms for columns have skewed value should test skewness first
FOR ALL INDEXED COLUMNS : Collect histograms for columns have indexes only.

Database Level

Gathering statistics for all objects in database, cascade will include indexes

SQL> 
SQL> show user;
USER is "SYS"
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where STALE_STATS='YES';

OWNER                          TABLE_NAME                     LAST_ANALYZED   STALE_STATS
------------------------------ ------------------------------ --------------- ---------------------
SYS                            SEG$                           01-FEB-23       YES
SYS                            IND$                           29-JAN-23       YES
SYS                            TAB$                           31-JAN-23       YES
SYS                            HISTGRM$                       01-FEB-23       YES
SYS                            PARTOBJ$                       29-JAN-23       YES
SYS                            PROPS$                         01-FEB-23       YES
SYS                            STATS_TARGET$                  01-FEB-23       YES
SYS                            COL_USAGE$                     01-FEB-23       YES
SYS                            COL_GROUP_USAGE$               01-FEB-23       YES
SYS                            MON_MODS_ALL$                  01-FEB-23       YES
SYS                            WRI$_OPTSTAT_OPR               01-FEB-23       YES
SYS                            WRI$_OPTSTAT_OPR_TASKS         01-FEB-23       YES
SYS                            OPTSTAT_HIST_CONTROL$          01-FEB-23       YES
SYS                            OPT_FINDING$                   01-FEB-23       YES
SYS                            OPT_DIRECTIVE_OWN$             01-FEB-23       YES
SYS                            OPT_DIRECTIVE$                 01-FEB-23       YES
SYS                            EXP_OBJ$                       01-FEB-23       YES
SYS                            EXP_STAT$                      01-FEB-23       YES
SYS                            TABPART$                       01-FEB-23       YES
SYS                            INDPART$                       01-FEB-23       YES
SYS                            LOBFRAG$                       31-JAN-23       YES
SYS                            WRI$_ADV_TASKS                 01-FEB-23       YES
SYS                            WRI$_ADV_PARAMETERS            01-FEB-23       YES
SYS                            WRI$_ADV_EXECUTIONS            01-FEB-23       YES
SYS                            WRI$_ADV_EXEC_PARAMETERS       01-FEB-23       YES
SYS                            WRI$_ADV_OBJECTS               01-FEB-23       YES
SYS                            WRI$_ADV_FINDINGS              01-FEB-23       YES
SYS                            WRI$_ADV_MESSAGE_GROUPS        31-JAN-23       YES
SYS                            WRI$_ADV_ADDM_TASKS            01-FEB-23       YES
SYS                            WRI$_ADV_ADDM_INST             01-FEB-23       YES
SYS                            WRI$_ADV_ADDM_FDG              01-FEB-23       YES
SYS                            WRI$_ADV_ADDM_TODO             01-FEB-23       YES
SYS                            SCHEDULER$_CLASS               22-JAN-23       YES
SYS                            SCHEDULER$_JOB                 01-FEB-23       YES
SYS                            SCHEDULER$_WINDOW              01-FEB-23       YES
SYS                            SCHEDULER$_WINDOW_DETAILS      01-FEB-23       YES
SYS                            WRI$_DBU_CPU_USAGE_SAMPLE      01-FEB-23       YES
SYS                            KET$_AUTOTASK_STATUS           01-FEB-23       YES
SYS                            KET$_CLIENT_TASKS              01-FEB-23       YES
SYS                            WRI$_SQLMON_USAGE              01-FEB-23       YES
SYS                            WRH$_DATAFILE                  31-JAN-23       YES
SYS                            WRH$_IOSTAT_FUNCTION           01-FEB-23       YES
SYS                            WRH$_IOSTAT_FILETYPE           01-FEB-23       YES
SYS                            WRH$_IOSTAT_DETAIL             01-FEB-23       YES
SYS                            WRH$_SQLSTAT                   01-FEB-23       YES
SYS                            WRH$_SQLTEXT                   01-FEB-23       YES
SYS                            WRH$_SQL_SUMMARY               01-FEB-23       YES
SYS                            WRH$_SQL_BIND_METADATA         01-FEB-23       YES
SYS                            WRH$_OPTIMIZER_ENV             01-FEB-23       YES
SYS                            WRH$_SYSTEM_EVENT              01-FEB-23       YES
SYS                            WRH$_CON_SYSTEM_EVENT          01-FEB-23       YES
SYS                            WRH$_BG_EVENT_SUMMARY          01-FEB-23       YES
SYS                            WRH$_CHANNEL_WAITS             01-FEB-23       YES
SYS                            WRH$_WAITSTAT                  01-FEB-23       YES
SYS                            WRH$_ENQUEUE_STAT              01-FEB-23       YES
SYS                            WRH$_LATCH                     01-FEB-23       YES
SYS                            WRH$_LATCH_MISSES_SUMMARY      01-FEB-23       YES
SYS                            WRH$_EVENT_HISTOGRAM           01-FEB-23       YES
SYS                            WRH$_MUTEX_SLEEP               01-FEB-23       YES
SYS                            WRH$_LIBRARYCACHE              01-FEB-23       YES
SYS                            WRH$_DB_CACHE_ADVICE           01-FEB-23       YES
SYS                            WRH$_BUFFER_POOL_STATISTICS    01-FEB-23       YES
SYS                            WRH$_ROWCACHE_SUMMARY          01-FEB-23       YES
SYS                            WRH$_SGA                       01-FEB-23       YES
SYS                            WRH$_SGASTAT                   01-FEB-23       YES
SYS                            WRH$_PGASTAT                   01-FEB-23       YES
SYS                            WRH$_PROCESS_MEMORY_SUMMARY    01-FEB-23       YES
SYS                            WRH$_RESOURCE_LIMIT            01-FEB-23       YES
SYS                            WRH$_SHARED_POOL_ADVICE        01-FEB-23       YES
SYS                            WRH$_SQL_WORKAREA_HISTOGRAM    01-FEB-23       YES
SYS                            WRH$_PGA_TARGET_ADVICE         01-FEB-23       YES
SYS                            WRH$_SGA_TARGET_ADVICE         01-FEB-23       YES
SYS                            WRH$_INSTANCE_RECOVERY         01-FEB-23       YES
SYS                            WRH$_RECOVERY_PROGRESS         29-JAN-23       YES
SYS                            WRH$_THREAD                    01-FEB-23       YES
SYS                            WRH$_SYSSTAT                   01-FEB-23       YES
SYS                            WRH$_CON_SYSSTAT               01-FEB-23       YES
SYS                            WRH$_SYS_TIME_MODEL            01-FEB-23       YES
SYS                            WRH$_CON_SYS_TIME_MODEL        01-FEB-23       YES
SYS                            WRH$_OSSTAT                    01-FEB-23       YES
SYS                            WRH$_PARAMETER                 01-FEB-23       YES
SYS                            WRH$_MVPARAMETER               01-FEB-23       YES
SYS                            WRH$_UNDOSTAT                  01-FEB-23       YES
SYS                            WRH$_SEG_STAT                  01-FEB-23       YES
SYS                            WRH$_SEG_STAT_OBJ              01-FEB-23       YES
SYS                            WRH$_SYSMETRIC_HISTORY         01-FEB-23       YES
SYS                            WRH$_SYSMETRIC_SUMMARY         01-FEB-23       YES
SYS                            WRH$_CON_SYSMETRIC_SUMMARY     01-FEB-23       YES
SYS                            WRH$_ACTIVE_SESSION_HISTORY    01-FEB-23       YES
SYS                            WRH$_TABLESPACE_STAT           01-FEB-23       YES
SYS                            WRH$_LOG                       01-FEB-23       YES
SYS                            WRH$_TABLESPACE                31-JAN-23       YES
SYS                            WRH$_TABLESPACE_SPACE_USAGE    01-FEB-23       YES
SYS                            WRH$_SERVICE_NAME              01-FEB-23       YES
SYS                            WRH$_SERVICE_STAT              01-FEB-23       YES
SYS                            WRH$_SERVICE_WAIT_CLASS        01-FEB-23       YES
SYS                            WRI$_SCH_CONTROL               01-FEB-23       YES
SYS                            WRI$_SCH_VOTES                 01-FEB-23       YES
SYS                            WRH$_RSRC_CONSUMER_GROUP       01-FEB-23       YES
SYS                            WRH$_RSRC_PLAN                 01-FEB-23       YES
SYS                            WRH$_RSRC_METRIC               01-FEB-23       YES
SYS                            WRH$_RSRC_PDB_METRIC           01-FEB-23       YES
SYS                            WRH$_MEM_DYNAMIC_COMP          01-FEB-23       YES
SYS                            WRH$_DISPATCHER                01-FEB-23       YES
SYS                            WRH$_SHARED_SERVER_SUMMARY     01-FEB-23       YES
SYS                            WRM$_WR_USAGE                  01-FEB-23       YES
SYS                            WRM$_DATABASE_INSTANCE         01-FEB-23       YES
SYS                            WRM$_SNAPSHOT                  01-FEB-23       YES
SYS                            WRM$_SNAPSHOT_DETAILS          01-FEB-23       YES
SYS                            WRM$_SNAP_ERROR                01-FEB-23       YES
SYS                            WRM$_PDB_IN_SNAP               01-FEB-23       YES
SYS                            WRM$_WR_CONTROL                01-FEB-23       YES
SYS                            WRH$_PROCESS_WAITTIME          01-FEB-23       YES
SYS                            WRH$_SESS_NETWORK              29-JAN-23       YES
SYS                            WRI$_OPTSTAT_HISTHEAD_HISTORY  01-FEB-23       YES
SYS                            WRI$_OPTSTAT_HISTGRM_HISTORY   01-FEB-23       YES
SYS                            WRH$_CON_SYSSTAT               01-FEB-23       YES
SYS                            WRH$_CON_SYSTEM_EVENT          01-FEB-23       YES
SYS                            WRH$_PROCESS_WAITTIME          01-FEB-23       YES
SYS                            WRH$_SESS_NETWORK              29-JAN-23       YES
SYS                            WRH$_SYSMETRIC_HISTORY         01-FEB-23       YES
SYS                            WRH$_TABLESPACE_STAT           01-FEB-23       YES
SYS                            WRH$_OSSTAT                    01-FEB-23       YES
SYS                            WRH$_SYS_TIME_MODEL            01-FEB-23       YES
SYS                            WRH$_SERVICE_WAIT_CLASS        01-FEB-23       YES
SYS                            WRH$_EVENT_HISTOGRAM           01-FEB-23       YES
SYS                            WRH$_MVPARAMETER               01-FEB-23       YES
SYS                            WRM$_PDB_IN_SNAP               01-FEB-23       YES
SYS                            WRH$_CON_SYS_TIME_MODEL        01-FEB-23       YES
SYS                            WRP$_REPORTS_TIME_BANDS        01-FEB-23       YES
SYS                            WRP$_REPORTS                   01-FEB-23       YES
SYS                            WRP$_REPORTS_DETAILS           01-FEB-23       YES
SYS                            WRH$_SQLSTAT                   01-FEB-23       YES
SYS                            WRH$_SYSTEM_EVENT              01-FEB-23       YES
SYS                            WRH$_WAITSTAT                  01-FEB-23       YES
SYS                            WRH$_LATCH                     01-FEB-23       YES
SYS                            WRH$_LATCH_MISSES_SUMMARY      01-FEB-23       YES
SYS                            WRH$_DB_CACHE_ADVICE           01-FEB-23       YES
SYS                            WRH$_ROWCACHE_SUMMARY          01-FEB-23       YES
SYS                            WRH$_SGASTAT                   01-FEB-23       YES
SYS                            WRH$_SYSSTAT                   01-FEB-23       YES
SYS                            WRH$_PARAMETER                 01-FEB-23       YES
SYS                            WRH$_SEG_STAT                  01-FEB-23       YES
SYS                            WRH$_SERVICE_STAT              01-FEB-23       YES

144 rows selected.

SQL> exec dbms_stats.gather_database_stats(cascade=>TRUE,method_opt =>'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL> 

----- For faster execution

SQL> 
SQL> 
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>6);

PL/SQL procedure successfully completed.

SQL> 

---or 

SQL> 
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>6);

PL/SQL procedure successfully completed.

SQL>

SCHEMA LEVEL

Gathering statistics for all objects in a schema, cascade will include indexes. If not used Oracle will determine whether to collect it or not.



exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where STALE_STATS='YES' and OWNER='&owner;

set timing on

exec dbms_stats.gather_schema_stats(ownname=>'&schema_name', CASCADE=>TRUE,ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,degree =>4);
OR
exec dbms_stats.gather_schema_stats(ownname=>'&schema_name',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,degree =>4);
-- CASCADE is not included here. Let Oracle will determine whether to collect statatics on indexes or not.
OR
EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('&schema_name'); Will gather stats on 100% of schema tables.
[oracle@oracle-db-19c ~]$ 
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 2 10:17:57 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> set pagesize 200
SQL> set linesize 200
SQL> 
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where STALE_STATS='YES' and OWNER='&owner';
Enter value for owner: sys
old   1: select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where STALE_STATS='YES' and OWNER='&owner'
new   1: select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where STALE_STATS='YES' and OWNER='sys'

no rows selected

SQL> select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where STALE_STATS='YES' and OWNER='&owner';
Enter value for owner: SYS
old   1: select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where STALE_STATS='YES' and OWNER='&owner'
new   1: select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where STALE_STATS='YES' and OWNER='SYS'

OWNER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LAST_ANALYZED   STALE_STATS
--------------- ---------------------
SYS
WRP$_REPORTS
02-FEB-23       YES

SYS
WRP$_REPORTS_TIME_BANDS
02-FEB-23       YES

SYS
HIST_HEAD$
02-FEB-23       YES

SYS
TABPART$
02-FEB-23       YES

SYS
KU_UTLUSE
02-FEB-23       YES

SYS
WRM$_DATABASE_INSTANCE
02-FEB-23       YES

SYS
USTATS$
02-FEB-23       YES

SYS
OPTSTAT_HIST_CONTROL$
02-FEB-23       YES

SYS
SCHEDULER$_JOB
02-FEB-23       YES

SYS
COL_USAGE$
02-FEB-23       YES

SYS
CLU$
02-FEB-23       YES

SYS
WRP$_REPORTS_DETAILS
02-FEB-23       YES

SYS
TAB$
02-FEB-23       YES

SYS
HISTGRM$
02-FEB-23       YES

SYS
MON_MODS_ALL$
02-FEB-23       YES

SYS
INDPART$
02-FEB-23       YES

SYS
TABCOMPART$
02-FEB-23       YES

SYS
IND$
02-FEB-23       YES

SYS
WRM$_WR_CONTROL
02-FEB-23       YES

SYS
WRI$_SQLMON_USAGE
02-FEB-23       YES

SYS
WRP$_REPORTS
02-FEB-23       YES

SYS
WRP$_REPORTS_TIME_BANDS
02-FEB-23       YES

SYS
WRP$_REPORTS_DETAILS
02-FEB-23       YES


23 rows selected.

SQL> set timing on
SQL> 
SQL> exec dbms_stats.gather_schema_stats(ownname=>'&schema_name',CASCADE=>TRUE,ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,degree =>4);
Enter value for schema_name: hr
BEGIN dbms_stats.gather_schema_stats(ownname=>'hr',CASCADE=>TRUE,ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,degree =>4); END;

*
ERROR at line 1:
ORA-20000: Schema "HR" does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 42210
ORA-06512: at "SYS.DBMS_STATS", line 42165
ORA-06512: at "SYS.DBMS_STATS", line 9404
ORA-06512: at "SYS.DBMS_STATS", line 42048
ORA-06512: at "SYS.DBMS_STATS", line 42196
ORA-06512: at line 1


Elapsed: 00:00:00.02
SQL> exec dbms_stats.gather_schema_stats(ownname=>'&schema_name',CASCADE=>TRUE,ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,degree =>4);
Enter value for schema_name: SYS

PL/SQL procedure successfully completed.

Elapsed: 00:00:55.78
SQL> 
SQL> 
SQL> 
SQL> exec dbms_stats.gather_schema_stats(ownname=>'&schema_name',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,degree =>4);
Enter value for schema_name: SYS

PL/SQL procedure successfully completed.

Elapsed: 00:01:03.20
SQL> 
SQL> 
SQL> 
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('&schema_name');
Enter value for schema_name: SYS

PL/SQL procedure successfully completed.

Elapsed: 00:00:58.80
SQL>

TABLE Level

-- The CASCADE parameter determines whether or not statistics are gathered for the indexes on a table.

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
SELECT OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS WHERE TABLE_NAME='&TNAME';

exec dbms_stats.gather_table_stats(ownname=>'&Schema_name',tabname=>'&Table_name',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=>TRUE,degree =>4);
OR
-- Gather statistics on the table with histograms being automatically created
exec dbms_stats.gather_table_stats('&SCHEMA_NAME','&Table_name');

Index Statistics

exec DBMS_STATS.GATHER_INDEX_STATS(ownname => '&OWNER',indname =>'&INDEX_NAME',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE);

SYSTEM STATISTICS

What is system statistics?

System statistics are statistics about CPU speed and IO performance, it enables the CBO to effectively cost each operation in an execution plan. Introduced in Oracle 9i.

Why gathering system statistics?

Oracle highly recommends gathering system statistics during a representative workload,
ideally at peak workload time, in order to provide more accurate CPU/IO cost estimates to the optimizer.
You only have to gather system statistics once.
There are two types of system statistics (NOWORKLOAD statistics & WORKLOAD statistics):

NOWORKLOAD statistics:

 This will simulates a workload -not the real one but a simulation- and will not collect full statistics, it's less accurate than "WORKLOAD statistics" but if you can't capture the statistics during a typical workload you can use noworkload statistics. To gather noworkload statistics:

SQL> execute dbms_stats.gather_system_stats(); 

SQL> 
SQL> 
SQL> execute dbms_stats.gather_system_stats(); 

PL/SQL procedure successfully completed.

Elapsed: 00:00:25.33
SQL>

WORKLOAD statistics:

This will gather statistics during the current workload [which supposed to be representative of actual system I/O and CPU workload on the DB]. To gather WORKLOAD statistics:

SQL> 
SQL> execute dbms_stats.gather_system_stats('start');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
SQL>

 Once the workload window ends after 1,2,3.. hours or whatever, stop the system statistics gathering:

SQL> execute dbms_stats.gather_system_stats('stop');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.14
SQL> 
You can use time interval (minutes) instead of issuing start/stop command manually:
SQL> execute dbms_stats.gather_system_stats('interval',60);

 

Check the system values collected:
SQL> 
SQL> show user;
USER is "SYS"
SQL> col pname format a20
SQL> col pval2 format a40
SQL> select * from sys.aux_stats$;

SNAME                                                                                      PNAME                     PVAL1 PVAL2
------------------------------------------------------------------------------------------ -------------------- ---------- ----------------------------------------
SYSSTATS_INFO                                                                              STATUS                          COMPLETED
SYSSTATS_INFO                                                                              DSTART                          02-02-2023 10:49
SYSSTATS_INFO                                                                              DSTOP                           02-02-2023 10:50
SYSSTATS_INFO                                                                              FLAGS                         1
SYSSTATS_MAIN                                                                              CPUSPEEDNW                 2943
SYSSTATS_MAIN                                                                              IOSEEKTIM                    16
SYSSTATS_MAIN                                                                              IOTFRSPEED                 4096
SYSSTATS_MAIN                                                                              SREADTIM                   .008
SYSSTATS_MAIN                                                                              MREADTIM
SYSSTATS_MAIN                                                                              CPUSPEED                   2944
SYSSTATS_MAIN                                                                              MBRC
SYSSTATS_MAIN                                                                              MAXTHR
SYSSTATS_MAIN                                                                              SLAVETHR

13 rows selected.

Elapsed: 00:00:00.16
SQL> 
cpuspeedNW:  Shows the noworkload CPU speed, (average number of CPU cycles per second).
ioseektim:    The sum of seek time, latency time, and OS overhead time.
iotfrspeed:  I/O transfer speed,tells optimizer how fast the DB can read data in a single read request.
cpuspeed:      Stands for CPU speed during a workload statistics collection.
maxthr:          The maximum I/O throughput.
slavethr:      Average parallel slave I/O throughput.
sreadtim:     The Single Block Read Time statistic shows the average time for a random single block read.
mreadtim:     The average time (seconds) for a sequential multiblock read.
mbrc:             The average multiblock read count in blocks.

Notes:
-When gathering NOWORKLOAD statistics it will gather (cpuspeedNW, ioseektim, iotfrspeed) system statistics only.
-Above values can be modified manually using DBMS_STATS.SET_SYSTEM_STATS procedure.
-According to Oracle, collecting workload statistics doesn't impose an additional overhead on your system.

Delete system statistics

SQL> 
SQL> 
SQL> execute dbms_stats.delete_system_stats();

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>

How lock/unlock statistics on table

1.Create table and verify

SQL> create table raj ( x number );

Table created.

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name='RAJ' and owner='SH';

STATT
-----
	 <---- Output NULL. Hence table unlocked. It will allow to gather stats on this table

SQL>

2.Lock stats

SQL> exec dbms_stats.lock_table_stats('SH', 'RAJ');

PL/SQL procedure successfully completed.

SQL>

3. Verify

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name='RAJ' and owner='SH';

STATT
-----
ALL <---- Hence table locked. It will not allow to gather stats on this table

SQL>

Tryied to gather stats, but fails

SQL> exec dbms_stats.gather_table_stats('sh', 'raj');
BEGIN dbms_stats.gather_table_stats('sh', 'raj'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)  <-- LOCKED

4. Unlock

SQL> exec dbms_stats.unlock_table_stats('SH', 'RAJ');

PL/SQL procedure successfully completed.

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name='RAJ' and owner='SH';

STATT
-----
	<----it's unlocked, It will allow to gather stats on this table

SQL> exec dbms_stats.gather_table_stats('sh', 'raj');

PL/SQL procedure successfully completed.

SQL>

Locked: ALL
Unlocked: NULL

DB架构
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
oracle bin 打头的表,关于WRI$和WRH$打头的表
weixin_34669890的博客
04-09 1181
我也发现同样问题,我装了一个测试库,偶尔用时发现无法switch log file.经检查发现是db_recovery_file_dest_size达到了设定的值.于是,我就用logmnr分析了一个archivelog,发现SEG_OWNERSEG_NAME HITS---------- --------...
Oracle CBO选错执行计划的一种场景
bisal的专栏
12-10 930
测试人员做应用性能测试，反馈有一条SQL语句，之前执行非常快，现在执行时间，明显慢了。   SQL语句非常简单，根据日期字段，检索当日表中记录数， select count(*) from user.tbl where cki_date = to_date('2017-10-10','yyyy-mm-dd');   我们执行一次10053， SQL&gt; alter sessi...
oracle10g的sysaux空间暴增与空间回收
冰刀(skate)
02-21 6298
author：skate time：2012/02/22   oracle10g的sysaux空间暴增与空间回收   在Oracle10中表空间SYSAUX引入，oracle把统计信息存储在这里，这也是为了更好的优化system表空间，我们可以用视图V$SYSAUX_OCCUPANTS 查看，oracle有哪些数据存贮在SYSAUX中。 SELECT occupant_name, sp
辅助表空间的对象清理
期待着的博客
05-14 1001
oracle11.2.0.4辅助表空间的对象清理： 查询对象占用空间的多少 SELECT occupant_name "Item", space_usage_kbytes / 1048576 "Space Used (GB)", schema_name "Schema", move_procedure "Move Procedure" FROM v$sysaux_occupants ORDER BY 1; 最后发现A...
Script:诊断SYSAUX表空间使用情况
weixin_33860147的博客
03-19 213
Script:以下脚本可以用于诊断SYSAUX表空间使用情况   ./opatch lsinventory -detail @?/rdbms/admin/awrinfo select dbms_stats.get_stats_history_retention from dual; select dbms_stats.get_stats_history_availability from...
Oracle Database Sample Schemas 12.2c
03-02
示例数据库模式为Oracle数据库的每个版本提供了一个通用的平台。在Oracle 12cR2中，提供了如下测试用户： ...Schema SH – Division Sales tracks business statistics to facilitate business decisions.
最完整的Toad For Oracle使用手册
09-22
- **Toad Server Statistics**：提供了服务器统计信息的查看方法。 - **SQL Monitor**：介绍了SQL监视器的使用方法。 - **Toad UNIX Monitor**：讲解了UNIX监视器的使用方法。 - **ADDM/AWR**：提供了ADDM/AWR的使用...
oracle学习基础素材表-emp表-dept表等等
05-10
about to export ORACLE's tables via Conventional Path ... . . exporting table BOOK 1 rows exported . . exporting table CLASS 0 rows exported . . exporting table CUSTOMER 0 rows exported EXP-00091: ...
Oracle Database 10g: The Top 20 Features for DBAs
04-20
**Description:** AWR collects and maintains performance statistics for diagnosing performance issues. It provides a comprehensive view of system activity over time, helping DBAs identify and ...
oracle 10g SYSAUX表空间快速增长之WRI$_OPTSTAT_HISTGRM_HISTORY篇
clafygz35624829的博客
12-30 835
在下午的检查中，还发现另外几个对象在sysaux表空间中占据很大的空间：I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST，大小为4124M，WRI$_OPTSTAT_HISTGRM_HISTORY，大小为2893M，...
SM/OPTSTAT导致sysaux异常增长
wu_wei_jie的专栏
04-18 3488
这问题是由最近一个数据库SYSAUX使用空间过多引出来的.<br />SM/OPTSTAT是用于存储老的统计信息，10G之前，当对表/字段/索引做了相应的统计信息之后，新的统计信息就会覆盖老的统计信息，也就是说的无法直接找回统计信息，要找回，只能事先通过dbms_stats导出来。10G就不必了，它会自动的存到相应的表里，而这些表是存在sysaux的，但这也引出了一个问题：如果这些表的数据不断的增长，而不把老的数据删除的话，sysaux迟早会暴了的。默认的情况下,系统会为SM/OPTSTAT保留31天的记录
SYSAUX 表空间历史统计数据过大的处理办法—--范例篇
IT小灰的笔记
06-22 5049
【INTRO】 SM/OPTSTAT是用于存储老的统计信息，10G之前，当对表/字段/索引做了相应的统计信息之后，新的统计信息就会覆盖老的统计信息，也就是说的无法直接找回统计信息，要找回，只能事先通过dbms_stats导出来。10G就不必了，它会自动的存到相应的表里，而这些表是存在sysaux的，但这也引出了一个问题：如果这些表的数据不断的增长，而不把老的数据删除的话，sysaux迟早会被撑爆
oracle sysaux 大小,oracle 10g SYSAUX表空间快速增长之WRI$_OPTSTAT_HISTGRM_HISTORY篇
weixin_28789499的博客
04-10 667
在下午的检查中，还发现另外几个对象在sysaux表空间中占据很大的空间：I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST，大小为4124M，WRI$_OPTSTAT_HISTGRM_HISTORY，大小为2893M，前者是后者的索引，此表是用来保存历史的的收集统计信息的。查看metalink相关文章：Statistics space used by SM/OPTSTAT in the ...
WRI$_OPTSTAT_HISTGRM_HISTORY太大清理
cri5768的博客
07-13 1109
Errors in file /home/oradba/oracle/diag/rdbms/yjfcdbp/yjfcdb/trace/yjfcdb_m001_22647.trc: ORA-12751: cpu time or ...
ORACLE 11.2.0.3 不停机处理SYSAUX表空间一直增长问题
贤时间
04-25 1068
表象 SYSAUX表空间一直增长，一个月增长快10G，需要定时维护表空间 原因 oracle bug引起 (Doc ID 1055547.1) MMON performs the purge of the optimizer stats history automatically. However it has an internal limit of 5 minutes to perform this job. If the operation takes more than 5 minutes, th
alter table move与shrink space
zuixinnet的专栏
03-21 1387
alter table move与shrink space 发表于 2011 年 05 月 16 日 由惜分飞 链接：http://www.xifenfei.com/1051.html 标题：alter table move与shrink space 作者：惜分飞©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.] 都知道alter table mo
Oracle 12c数据库优化器统计信息收集的最佳实践（一）
Joy0921的博客
09-01 2044
导语Oracle优化器会为SQL语句产生所有可能的访问路径（执行计划），然后从中选择一条COST值最低的执行路径，这个cost值是指oracle估算执行SQL所消耗的资源。为了让优化器能够精确计算的每一条执行计划的COST值，这就需要被执行SQL语句所需访问的所有对象（表和索引等）和系统有必要的描述信息。这些必要的信息通常被称为optimizer statistics（优化器统计信息）。理解和管理...
PostgreSQL 11 preview - 表达式索引柱状图buckets\STATISTICS\default_statistics_target可设置...
weixin_33743661的博客
07-28 223
标签 PostgreSQL , 表达式索引 , 柱状图 , buckets , 增强 , 11 背景 PostgreSQL 支持表达式索引，优化器支持CBO，对于普通字段，我们有默认统计信息bucket控制，也可以通过alter table alter column来设置bucket，提高或降低字段级的统计精度。 ALTER TABLE [ IF E...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值