DB File Size Calculation

Calculate the Optimum Redo log file

consider resize your redo logs !!!!!

select OPTIMAL_LOGFILE_SIZE from v$instance_recovery;
use the v$instance_recovery view (column: OPTIMAL_LOGFILE_SIZE) to determine a recommended size for your online redo logs. if this column value shows a value greater than the size of your smallest online log, then you should configure all your online logs to be at least this size.

Here is a query that shows you the time between log switches.

set pagesize 1000
set linesize 10000
SELECT B.RECID,
       TO_CHAR(B.FIRST_TIME,'dd-mon-yy hh24:mi:ss') start_time,
       A.RECID,
       TO_CHAR(A.FIRST_TIME,'dd-mon-yy hh24:mi:ss') end_time,
       round(((a.first_time - b.first_time)*25)*60,2) minutes
 FROM
V$LOG_HISTORY A,V$LOG_HISTORY B
WHERE A.RECID = B.RECID + 1
ORDER BY A.FIRST_TIME ASC
/

Calculate the Optimum Undo TBS

http://www.akadia.com/services/ora_optimize_undo.html

Calculate the Optimum Temp TBS
You can look at the size used for the temporary tablespace and size it accordingly by using this query:

select
x.TABLESPACE_NAME TABLESPACE_NAME,
x.Allocated_MB Allocated_MB,
y.Used_MB Used_MB,
y.Free_MB Free_MB,
(y.Used_MB / x.Allocated_MB) *100 perc_used
from
(
select
a.TABLESPACE_NAME,
SUM(a.BYTES/1024/1024) Allocated_MB
from
dba_temp_files a
where
a.TABLESPACE_NAME=UPPER('&&1')
GROUP BY
a.TABLESPACE_NAME
) x,
(
SELECT
SUM(b.BYTES_USED/1024/1024) Used_MB,
SUM(b.BYTES_FREE/1024/1024) Free_MB
FROM
v$temp_space_header b
WHERE
b.TABLESPACE_NAME=UPPER('&&1')
GROUP BY
B.TABLESPACE_NAME
) y ;

TABLESPACE_NAME ALLOCATED_MB USED_MB FREE_MB PERC_USED
—————————— ———— ———- ———- ———-
TEMP02 1024 11 1013 1.07421875

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.