Feb-15

20-Feb

RMAN backup details

set linesize 200
col OUTPUT_BYTES_PER_SEC_DISPLAY for a10
col startTime for a20
col EndTime for a20
col Elapsed for a10
set pagesize 50
SELECT
            to_char(start_time,'dd-mon-yyyy hh24:mi:ss') startTime,
            to_char(end_time,'dd-mon-yyyy hh24:mi:ss') EndTime,
            to_char((end_time - start_time)*24*60*60) Elapsed,
            output_bytes_per_sec_display,
            status,
        OUTPUT_BYTES/1024/1024/1024,INPUT_TYPE
FROM v$rman_backup_job_details
order by  start_time;

RMAN Daily archive log backup size

set linesize 200
SELECT         to_char(end_time,'dd-mon-yyyy') EndTime,
sum(OUTPUT_BYTES/1024/1024/1024)
FROM v$rman_backup_job_details
where INPUT_TYPE='ARCHIVELOG'
group by to_char(end_time,'dd-mon-yyyy');

17-Feb

Full Database caching (12.1.0.2):  Database can be cached into buffer cache including NOCACHE LOBS.    By default tables will be cached based on the normal rules as previous versions.  You can enable full database caching by executing the command

SQL> ALTER DATABASE FORCE FULL DATABASE CACHING;

You can view the current caching status from the V$DATABASE, under the column FORCE_FULL_DB_CACHING

SELECT force_full_db_caching FROM v$database;

LOG BUFFER ALLCOATION RETRIES:  In heavy active systems, there could be contention on placing redo into log buffer and can slow down the updates.  You can review the statistics “Log Buffer Allocation Retries” in V$SYSSTAT or V$SESSTAT to check how many times a user session re-try for space in log buffer.

SQL> SELECT name,value from V$SYSSTAT WHERE name ='redo buffer allocation retries';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo buffer allocation retries                                      5585576

Ideally this statistics should ideally tends to zero for a better performing system.

You can use the below SQL to query the current users who are contributing to the this wait is,

set linesize 200
set pagesize 50
select username,name,value
from v$sesstat st,v$statname sn,v$session se
where st.STATISTIC# = sn.STATISTIC#
and se.sid=st.sid
and sn.name ='redo buffer allocation retries'
and se.username is not null
and value > 0
order by value;
Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: