Home » RDBMS Server » Server Administration » how to verify gather schema statistics (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
how to verify gather schema statistics [message #680470] Fri, 15 May 2020 10:59 Go to next message
wtolentino
Messages: 309
Registered: March 2005
Senior Member
i run a gather statistics for a schema. how do i verify if the stats was applied?

example:
DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME           => 'HR',
                                ESTIMATE_PERCENT  => DBMS_STATS.AUTO_SAMPLE_SIZE,
                                METHOD_OPT        => 'FOR ALL COLUMNS SIZE AUTO', 
                                CASCADE           => TRUE);
thank you,
warren
Re: how to verify gather schema statistics [message #680472 is a reply to message #680470] Fri, 15 May 2020 11:04 Go to previous messageGo to next message
Michel Cadot
Messages: 67289
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DBA_COL_PENDING_STATS
DBA_IND_PENDING_STATS
DBA_IND_STATISTICS
DBA_PART_COL_STATISTICS
DBA_STAT_EXTENSIONS
DBA_SUBPART_COL_STATISTICS
DBA_TAB_COL_STATISTICS
DBA_TAB_HISTGRM_PENDING_STATS
DBA_TAB_PENDING_STATS
DBA_TAB_STATISTICS
DBA_TAB_STATS_HISTORY
DBA_TAB_STAT_PREFS
Edit: one more view, maybe others are missing. Check the doc.

[Updated on: Fri, 15 May 2020 11:05]

Report message to a moderator

Re: how to verify gather schema statistics [message #680476 is a reply to message #680472] Fri, 15 May 2020 13:21 Go to previous messageGo to next message
wtolentino
Messages: 309
Registered: March 2005
Senior Member
thanks so much and i appreciate your help.
Re: how to verify gather schema statistics [message #680477 is a reply to message #680476] Fri, 15 May 2020 13:30 Go to previous messageGo to next message
Michel Cadot
Messages: 67289
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

A good query when you search something in the dictionary (here info about 'statistics'):
SQL> Col table_name format a30 heading "View"
SQL> Col comments   format a85 heading "Comment" word_wrap
SQL> Select table_name, comments
  2  from dict
  3  where lower(comments) like '%statistics%'
  4    and table_name like 'DBA\_%' escape '\'
  5    and table_name not like 'DBA\_HIST%' escape '\'
  6  order by 1
  7  /
View                           Comment
------------------------------ -------------------------------------------------------------------------------------
DBA_COL_PENDING_STATS          Pending statistics of tables, partitions, and subpartitions
DBA_CPU_USAGE_STATISTICS       Database CPU Usage Statistics
DBA_FEATURE_USAGE_STATISTICS   Database Feature Usage Statistics
DBA_FREE_SPACE_COALESCED       Statistics on Coalesced Space in Tablespaces
DBA_HIGH_WATER_MARK_STATISTICS Database High Water Mark Statistics
DBA_IND_PENDING_STATS          Pending statistics of tables, partitions, and subpartitions
DBA_IND_STATISTICS             Optimizer statistics for all indexes in the database
DBA_OPTSTAT_OPERATIONS         History of statistics operations performed
DBA_REPRESOLUTION_STATISTICS   Statistics for conflict resolutions for all replicated tables in the database
DBA_REPRESOL_STATS_CONTROL     Information about statistics collection for conflict resolutions for all replicated
                               tables in the database
DBA_SSCR_CAPTURE               Session state capture statistics
DBA_SSCR_RESTORE               Session state restore statistics
DBA_STAT_EXTENSIONS            Optimizer statistics extensions
DBA_STREAMS_TP_COMPONENT_STAT  DBA Streams Component Statistics
DBA_STREAMS_TP_PATH_STAT       DBA Streams Path Statistics
DBA_TAB_HISTGRM_PENDING_STATS  Pending statistics of tables, partitions, and subpartitions
DBA_TAB_PENDING_STATS          Pending statistics of tables, partitions, and subpartitions
DBA_TAB_STATISTICS             Optimizer statistics for all tables in the database
DBA_TAB_STATS_HISTORY          History of table statistics modifications
DBA_TAB_STAT_PREFS             Statistics preferences for tables
DBA_TSM_DESTINATION            Transparent session migration source session statistics
DBA_TSM_HISTORY                Transparent session migration statistics
DBA_TSM_SOURCE                 Transparent session migration source session statistics
DBA_USTATS                     All statistics collected on either tables or indexes
This gives a first list, of course there are views to remove as out of your scope but this is a good starter.

Re: how to verify gather schema statistics [message #680478 is a reply to message #680477] Fri, 15 May 2020 14:18 Go to previous message
wtolentino
Messages: 309
Registered: March 2005
Senior Member
thanks so much again and that will be very helpful.
Previous Topic: Oracle DB Upgrade Clarification
Next Topic: Ora-48181. no space left on the device
Goto Forum:
  


Current Time: Fri Aug 07 03:45:23 CDT 2020