Trend Oracle Archive Graph : How much archive created y’day or last week

Often you want to show nice graphs/pictures to please your Colleagues. Here is an Excel graph I created to record Archive growth.

The following query shows a count and size of the redo log files by day:
– Daily Count and Size of Redo Log Space (Single Instance)
–

SELECT A.*,

Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb

FROM

(

SELECT

To_Char(First_Time,'YYYY-MM-DD') DAY,

Count(1) Count#,

Min(RECID) Min#,

Max(RECID) Max#

FROM

v$log_history

GROUP BY

To_Char(First_Time,'YYYY-MM-DD')

ORDER

BY 1 DESC

) A,

(

SELECT

Avg(BYTES) AVG#,

Count(1) Count#,

Max(BYTES) Max_Bytes,

Min(BYTES) Min_Bytes

FROM

v$log

) B

;

This v$log_history script comes from Ilya Petrenko

Output of the script : Delete columns Min(RECID) Min#,   Max(RECID) Max#

DAY	LOG COUNT	DAILY_AVG_MB
02/07/2010	98	4480
01/07/2010	126	5760
30/06/2010	54	2469
29/06/2010	28	1280
28/06/2010	37	1691
27/06/2010	14	640
26/06/2010	14	640
25/06/2010	14	640
24/06/2010	19	869
23/06/2010	14	640

Copy this table into Excel & click on Chart Wizard. Select X,Y co-ordinates and you will get a nice picture like this

Read More

Locate Log Switch Interval on Day/Hourly Basis

–List Timing for Log Switch on different clustered instances with thread details

SET LINESIZE 150
COLUMN TIMESTAMP FORMAT A30
COLUMN THREAD_ID FORMAT 999
COLUMN INSTANCE_ID FORMAT 999
SELECT  to_char(first_time,’DD-MON-YYYY HH24:MI’) TIMESTAMP,thread# THREAD_ID,inst_id INSTANCE_ID
FROM    GV$log_history
where INST_ID=&&INST_ID and trunc(first_time) =trunc(sysdate)
ORDER BY 1 desc;

– Will return a graph indicating log switches done on daily/hourly basis

SET LINESIZE 150
COLUMN DAY FORMAT A3
COLUMN Total FORMAT 99G990
COLUMN h00 FORMAT 999
COLUMN h01 FORMAT 999
COLUMN h02 FORMAT 999
COLUMN h03 FORMAT 999
COLUMN h04 FORMAT 999
COLUMN h05 FORMAT 999
COLUMN h06 FORMAT 999
COLUMN h07 FORMAT 999
COLUMN h08 FORMAT 999
COLUMN h09 FORMAT 999
COLUMN h10 FORMAT 999
COLUMN h11 FORMAT 999
COLUMN h12 FORMAT 999
COLUMN h13 FORMAT 999
COLUMN h14 FORMAT 999
COLUMN h15 FORMAT 999
COLUMN h16 FORMAT 999
COLUMN h17 FORMAT 999
COLUMN h18 FORMAT 999
COLUMN h19 FORMAT 999
COLUMN h20 FORMAT 999
COLUMN h21 FORMAT 999
COLUMN h22 FORMAT 999
COLUMN h23 FORMAT 999
COLUMN h24 FORMAT 999
BREAK ON REPORT
COMPUTE MAX OF “Total” ON REPORT
COMPUTE MAX OF “h00″ ON REPORT
COMPUTE MAX OF “h01″ ON REPORT
COMPUTE MAX OF “h02″ ON REPORT
COMPUTE MAX OF “h03″ ON REPORT
COMPUTE MAX OF “h04″ ON REPORT
COMPUTE MAX OF “h05″ ON REPORT
COMPUTE MAX OF “h06″ ON REPORT
COMPUTE MAX OF “h07″ ON REPORT
COMPUTE MAX OF “h08″ ON REPORT
COMPUTE MAX OF “h09″ ON REPORT
COMPUTE MAX OF “h10″ ON REPORT
COMPUTE MAX OF “h11″ ON REPORT
COMPUTE MAX OF “h12″ ON REPORT
COMPUTE MAX OF “h13″ ON REPORT
COMPUTE MAX OF “h14″ ON REPORT
COMPUTE MAX OF “h15″ ON REPORT
COMPUTE MAX OF “h16″ ON REPORT
COMPUTE MAX OF “h17″ ON REPORT
COMPUTE MAX OF “h18″ ON REPORT
COMPUTE MAX OF “h19″ ON REPORT
COMPUTE MAX OF “h20″ ON REPORT
COMPUTE MAX OF “h21″ ON REPORT
COMPUTE MAX OF “h22″ ON REPORT
COMPUTE MAX OF “h23″ ON REPORT

SELECT TRUNC(first_time) AS “Date”,
TO_CHAR(first_time, ‘Dy’) AS “Day”,
COUNT(1) AS “Total”,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’00′,1,0)) AS “h00″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’01′,1,0)) AS “h01″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’02′,1,0)) AS “h02″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’03′,1,0)) AS “h03″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’04′,1,0)) AS “h04″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’05′,1,0)) AS “h05″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’06′,1,0)) AS “h06″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’07′,1,0)) AS “h07″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’08′,1,0)) AS “h08″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’09′,1,0)) AS “h09″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’10′,1,0)) AS “h10″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’11′,1,0)) AS “h11″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’12′,1,0)) AS “h12″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’13′,1,0)) AS “h13″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’14′,1,0)) AS “h14″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’15′,1,0)) AS “h15″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’16′,1,0)) AS “h16″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’17′,1,0)) AS “h17″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’18′,1,0)) AS “h18″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’19′,1,0)) AS “h19″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’20′,1,0)) AS “h20″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’21′,1,0)) AS “h21″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’22′,1,0)) AS “h22″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’23′,1,0)) AS “h23″
FROM V$log_history
GROUP BY TRUNC(first_time), TO_CHAR(first_time, ‘Dy’)
ORDER BY 1;
CLEAR BREAKS;

Read More