Difference between pages "Check Generated Archivelog Sizes" and "Sys Aux Statistics"

Latest revision as of 22:57, 8 November 2019

Note to-self: Hardware Stats are stored in

SYS.AUX_STATS$

To gather the statistics:

exec dbms_stats.gather_system_stats('Start');
-- during peak times
exec dbms_stats.gather_system_stats('Stop');

To delete the statistics:

exec dbms_stats.delete_system_stats;

@@ Line 1: / Line 1: @@
-Script may or may not work for everyone.  The difficulty with this one is that it has to get the archivelog sizes from v$archived_log which could contain duplicate rows as indicated in Oracle documentation ....
+Note to-self:  Hardware Stats are stored in
-"If the log is archived twice, there will be two archived log records with the same THREAD#, SEQUENCE#, and FIRST_CHANGE#, but with a different name"
+<blockquote><pre>
+SYS.AUX_STATS$
+</pre></blockquote>
-To counter this, I was able to simply filter out by the name field, which uniquely contained the orignal archivelog names ending with .arc.
+To gather the statistics:
+<blockquote><pre>
+exec dbms_stats.gather_system_stats('Start');
+-- during peak times
+exec dbms_stats.gather_system_stats('Stop');
+</pre></blockquote>
-<pre>
+To delete the statistics:
-select to_char(b.first_time,'yyyy-mm-dd') DAY
+<blockquote><pre>
-     , count(*) NO_LOGS
+exec dbms_stats.delete_system_stats;
-     , sum(BLOCKS * BLOCK_SIZE)/1024/1024 GB
+</pre></blockquote>
-  from v$archived_log a
-     , v$log_history b
- where a.sequence# = b.sequence#
-   and a.thread# = b.thread#
-   and a.first_change# = b.first_change#
-   and a.name like '%.arc%'
-group by to_char(b.first_time,'yyyy-mm-dd');
-</pre>