how to verify gather schema statistics [message #680470] |
Fri, 15 May 2020 10:59 |
wtolentino
Messages: 421 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 |
|
Michel Cadot
Messages: 68729 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 #680477 is a reply to message #680476] |
Fri, 15 May 2020 13:30 |
|
Michel Cadot
Messages: 68729 Registered: March 2007 Location: Saint-Maur, France, https...
|
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.
|
|
|
|