RMAN – V$BACKUP_ASYNC_IO

SELECT TYPE,TO_CHAR(OPEN_TIME,’dd/mm/yyyy hh:mi’),TO_CHAR(CLOSE_TIME,’dd/mm/yyyy hh:mi’), SHORT_WAITS,LONG_WAITS,IO_COUNT,LONG_WAITS/IO_COUNT,effective_bytes_per_second, FILENAME
FROM V$BACKUP_ASYNC_IO

You can check the RMAN CHANNEL PERFORMANCE using this query

RMAN backup – Performance

1. If the V$BACKUP_ASYNC_IO view is empty and the V$BACKUP_SYNC_IO view is not empty, then the backup is not being performed in asynchronous mode. So enable backup tape I/O slaves, and if the system does not support native asynchronous I/O, then also enable backup disk I/O slaves.
2. Query V$BACKUP_ASYNC_IO to determine the effective reading speed during the backup. This value is reported in the column
EFFECTIVE_BYTES_PER_SECOND in the row that has the TYPE column equal to AGREGGATE. This row represents the backup speed. Sometimes backups are slow not because reading or writing is slow, but because of other issues: connecting to the media manager, retrying while starting media manager processes, committing the backup of the tape, and so on. In such cases, troubleshoot the problem in the media management software.
3. Investigate the IO_COUNT, READY, SHORT_WAITS, and LONG_WAITS
columns:
· The column IO_COUNTS represents the total number of the I/O calls.
· The READY column is the number of asynchronous I/O reads or writes for which a buffer was immediately ready for use.
· The column SHORT_WAITS represents the number of times that a buffer was not immediately available, but a buffer became available after doing a nonblocking poll for I/O completion.
· LONG_WAITS is the number of times that a buffer was not immediately available, and only became available after a blocking wait was issued. In other words, the number of I/O calls where Oracle waited for the disk or tape.

select SID, SERIAL, USE_COUNT,
RMAN_STATUS_RECID, RMAN_STATUS_STAMP,DEVICE_TYPE, TYPE,
STATUS,FILENAME, SET_COUNT, SET_STAMP, BUFFER_SIZE, BUFFER_COUNT,
TOTAL_BYTES, OPEN_TIME, CLOSE_TIME, ELAPSED_TIME, MAXOPENFILES, BYTES,
EFFECTIVE_BYTES_PER_SECOND, IO_COUNT, READY, SHORT_WAITS, SHORT_WAIT_TIME_TOTAL,
SHORT_WAIT_TIME_MAX, LONG_WAITS, LONG_WAIT_TIME_TOTAL, LONG_WAIT_TIME_MAX
from gv$backup_async_io where inst_id = userenv(‘Instance’)

Archive log generated per day

select trunc(completion_time), sum(blocks*block_size)/1048576 mb
from gv$archived_log
group by trunc(completion_time)

Top 5 segments in the database that have the most number of physical reads

This script will list the top 10 segments in the database that have the most number of physical reads.

set pagesize 200
setlinesize 120
col segment_name format a20
col owner format a10

select segment_name,object_type,total_physical_reads
from ( select owner||’.’||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in (‘physical reads’)
order by total_physical_reads desc)
where rownum <=5;

Display database SGA statistics

DECLARE
libcac NUMBER(10,2);
rowcac NUMBER(10,2);
bufcac NUMBER(10,2);
redlog NUMBER(10,2);
spsize NUMBER;
blkbuf NUMBER;
logbuf NUMBER;
BEGIN
SELECT VALUE INTO redlog FROM v$sysstat
WHERE name = ‘redo log space requests’;
SELECT 100*(SUM(pins)-SUM(reloads))/SUM(pins) INTO libcac FROM v$librarycache;
SELECT 100*(SUM(gets)-SUM(getmisses))/SUM(gets) INTO rowcac FROM v$rowcache;
SELECT 100*(cur.VALUE + con.VALUE – phys.VALUE)/(cur.VALUE + con.VALUE) INTO bufcac
FROM v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
WHERE cur.statistic# = ncu.statistic#
AND ncu.name = ‘db block gets’
AND con.statistic# = nco.statistic#
AND nco.name = ‘consistent gets’
AND phys.statistic# = nph.statistic#
AND nph.name = ‘physical reads’;
SELECT VALUE INTO spsize FROM v$parameter WHERE name = ‘shared_pool_size’;
SELECT VALUE INTO blkbuf FROM v$parameter WHERE name = ‘db_block_buffers’;
SELECT VALUE INTO logbuf FROM v$parameter WHERE name = ‘log_buffer’;
DBMS_OUTPUT.put_line(‘> SGA CACHE STATISTICS’);
DBMS_OUTPUT.put_line(‘> ********************’);
DBMS_OUTPUT.put_line(‘> SQL Cache Hit rate = ‘||libcac);
DBMS_OUTPUT.put_line(‘> Dict Cache Hit rate = ‘||rowcac);
DBMS_OUTPUT.put_line(‘> Buffer Cache Hit rate = ‘||bufcac);
DBMS_OUTPUT.put_line(‘> Redo Log space requests = ‘||redlog);
DBMS_OUTPUT.put_line(‘> ‘);
DBMS_OUTPUT.put_line(‘> INIT.ORA SETTING’);
DBMS_OUTPUT.put_line(‘> ****************’);
DBMS_OUTPUT.put_line(‘> Shared Pool Size = ‘||spsize||’ Bytes’);
DBMS_OUTPUT.put_line(‘> DB Block Buffer = ‘||blkbuf||’ Blocks’);
DBMS_OUTPUT.put_line(‘> Log Buffer = ‘||logbuf||’ Bytes’);
DBMS_OUTPUT.put_line(‘> ‘);
IF
libcac < 99 THEN DBMS_OUTPUT.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
END IF;
IF
rowcac < 85 THEN DBMS_OUTPUT.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
END IF;
IF
bufcac 100 THEN DBMS_OUTPUT.put_line(‘*** HINT: Log Buffer value is rather low!’);
END IF;
END;

UNDO INFORMATION:dbms_undo_adv

Table 1
Function calls within DBMS_UNDO_ADV

Function

Description

Outputs

undo_info

Provides basic information about your undo

Tablespace name

Maximum size possible

Current retention value

If undo is auto extensible

If undo is guaranteed undo retention

longest_query

Allows you to see what the longest running query was so
you might be able to tune in relation to time

Length of the longest query

required_retention

Query this function to assist in determining what you
should set undo_retention to in order to help prevent snap-shot-too-old
errors. This value is based on the longest running query.

init.ora parameter undo_retention

best_possible_retention

Provides you with a value for undo_retention that best
fits your current undo tablespace size and usage.

init.ora parameter undo_retention

required_undo_size

Provides a value for the size of the undo tablespace to
create in order to support the current value of the init.ora parameter
undo_retention.

Undo tablespace size

undo_health

Gives descriptive output if any problems are encountered
with your current undo tablespace size or setting of the init.ora parameter
undo_retention and provides recommendations to fix.

Problem / Resolution descriptions

undo_advisor

Uses the advisor framework to give descriptive output if
any problems are encountered with your current undo configuration and
possible resolutions.

Problem / Resolution descriptions

undo_autotune

Tells you if undo auto tuning for undo retention is
enabled

TRUE / FALSE

rbu_migration

Provides the size required for undo tablespace size if you
wish to switch to automatic undo management

Size of Undo Tablespace

set serveroutput on
DECLARE
tsn VARCHAR2(40);
tss NUMBER(10);
aex BOOLEAN;
unr NUMBER(5);
rgt BOOLEAN;
retval BOOLEAN;
BEGIN
retval := dbms_undo_adv.undo_info(tsn, tss, aex, unr, rgt);
dbms_output.put_line(‘Undo Tablespace Is: ‘ || tsn);
dbms_output.put_line(‘Undo Tablespace Size Is: ‘ || TO_CHAR(tss));
IF aex THEN
dbms_output.put_line(‘Undo Autoextend Is Set To: TRUE’);
ELSE
dbms_output.put_line(‘Undo Autoextend Is Set To: FALSE’);
END IF;
dbms_output.put_line(‘Undo Retention Is: ‘ || TO_CHAR(unr));
IF rgt THEN
dbms_output.put_line(‘Undo Guarantee Is Set To: TRUE’);
ELSE
dbms_output.put_line(‘Undo Guarantee Is Set To: FALSE’);
END IF;
END;
/

Undo Tablespace Is: UNDOTBS
Undo Tablespace Size Is: 28076
Undo Autoextend Is Set To: FALSE
Undo Retention Is: 43200
Undo Guarantee Is Set To: FALSE

PL/SQL procedure successfully completed.

SQL> SELECT dbms_undo_adv.longest_query(SYSDATE-1, SYSDATE)FROM dual;
DBMS_UNDO_ADV.LONGEST_QUERY(SYSDATE-1,SYSDATE)
———————————————-
45689
SQL> SELECT dbms_undo_adv.required_retention(SYSDATE-1, SYSDATE)FROM dual;
DBMS_UNDO_ADV.REQUIRED_RETENTION(SYSDATE-1,SYSDATE)
—————————————————
45689
SQL> show parameter undo
NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
undo_retention integer 43200
undo_tablespace string UNDOTBS

SQL> SELECT dbms_undo_adv.required_undo_size(43200, SYSDATE-1, SYSDATE)FROM dual;
DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(43200,SYSDATE-1,SYSDATE)
———————————————————
6426

SQL> set serveroutput on
DECLARE
v VARCHAR2(300);
BEGIN
v := dbms_undo_adv.undo_advisor(SYSDATE-1, SYSDATE, 1);
dbms_output.put_line(v);
END;
/
Finding 1:The undo tablespace is OK.

PL/SQL procedure successfully completed.

SQL> set serveroutput on

DECLARE
prob VARCHAR2(100);
reco VARCHAR2(100);
rtnl VARCHAR2(100);
retn PLS_INTEGER;
utbs PLS_INTEGER;
retv PLS_INTEGER;
BEGIN
retv := dbms_undo_adv.undo_health(prob, reco, rtnl, retn, utbs);
dbms_output.put_line(‘Problem: ‘ || prob);
dbms_output.put_line(‘Recmmnd: ‘ || reco);
dbms_output.put_line(‘Rationl: ‘ || rtnl);
dbms_output.put_line(‘Retentn: ‘ || TO_CHAR(retn));
dbms_output.put_line(‘UTBSize: ‘ || TO_CHAR(utbs));
END;
/

Problem: No problem found
Recmmnd:
Rationl:
Retentn: 0
UTBSize: 0

undo size based on retention:
SQL> SELECT dbms_undo_adv.required_undo_size(43200)FROM dual;

DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(43200)
—————————————
8403

SGA INFO

SQL> desc v$sgainfo
Name Null? Type
—————————————– ——– —————————-
NAME VARCHAR2(32)
BYTES NUMBER
RESIZEABLE VARCHAR2(3)

SQL>select * from v$sgainfo;

NAME BYTES RESIZEABLE
——————————– ———- —
Fixed SGA Size 2073128 No
Redo Buffers 31477760 No
Buffer Cache Size 1962934272 Yes
Shared Pool Size 788529152 Yes
Large Pool Size 33554432 Yes
Java Pool Size 16777216 Yes
Streams Pool Size 50331648 Yes
Granule Size 16777216 No
Maximum SGA Size 3456106496 No
Startup overhead in Shared Pool 117440512 No
Free SGA Memory Available 570425344

11 rows selected.

Log Switch details

set lines 120;
set pages 999;
SELECT
to_char(first_time,’YYYY-MM(MON)-DD’) day,
to_char(sum(decode(to_char(first_time,’HH24′),’00’,1,0)),’99’) “12a”,
to_char(sum(decode(to_char(first_time,’HH24′),’01’,1,0)),’99’) “1a”,
to_char(sum(decode(to_char(first_time,’HH24′),’02’,1,0)),’99’) “2a”,
to_char(sum(decode(to_char(first_time,’HH24′),’03’,1,0)),’99’) “3a”,
to_char(sum(decode(to_char(first_time,’HH24′),’04’,1,0)),’99’) “4a”,
to_char(sum(decode(to_char(first_time,’HH24′),’05’,1,0)),’99’) “5a”,
to_char(sum(decode(to_char(first_time,’HH24′),’06’,1,0)),’99’) “6a”,
to_char(sum(decode(to_char(first_time,’HH24′),’07’,1,0)),’99’) “7a”,
to_char(sum(decode(to_char(first_time,’HH24′),’08’,1,0)),’99’) “8a”,
to_char(sum(decode(to_char(first_time,’HH24′),’09’,1,0)),’99’) “9a”,
to_char(sum(decode(to_char(first_time,’HH24′),’10’,1,0)),’99’) “10a”,
to_char(sum(decode(to_char(first_time,’HH24′),’11’,1,0)),’99’) “11a”,
to_char(sum(decode(to_char(first_time,’HH24′),’12’,1,0)),’99’) “12p”,
to_char(sum(decode(to_char(first_time,’HH24′),’13’,1,0)),’99’) “1p”,
to_char(sum(decode(to_char(first_time,’HH24′),’14’,1,0)),’99’) “2p”,
to_char(sum(decode(to_char(first_time,’HH24′),’15’,1,0)),’99’) “3p”,
to_char(sum(decode(to_char(first_time,’HH24′),’16’,1,0)),’99’) “4p”,
to_char(sum(decode(to_char(first_time,’HH24′),’17’,1,0)),’99’) “5p”,
to_char(sum(decode(to_char(first_time,’HH24′),’18’,1,0)),’99’) “6p”,
to_char(sum(decode(to_char(first_time,’HH24′),’19’,1,0)),’99’) “7p”,
to_char(sum(decode(to_char(first_time,’HH24′),’20’,1,0)),’99’) “8p”,
to_char(sum(decode(to_char(first_time,’HH24′),’21’,1,0)),’99’) “9p”,
to_char(sum(decode(to_char(first_time,’HH24′),’22’,1,0)),’99’) “10p”,
to_char(sum(decode(to_char(first_time,’HH24′),’23’,1,0)),’99’) “11p”
from
v$log_history
GROUP by
to_char(first_time,’YYYY-MM(MON)-DD’)
ORDER BY
1;

Oracle hidden parameters

The following SQL can be used to find the values of all Oracle hidden parameters in your database:

select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl,ksppstdf,
decode(bitand(ksppiflg/256,1),1,’TRUE’,’FALSE’),
decode(bitand(ksppiflg/65536,3),1,’IMMEDIATE’,2,’DEFERRED’,3,’IMMEDIATE’,’FALSE’),
decode(bitand(ksppstvf,7),1,’MODIFIED’,4,’SYSTEM_MOD’,’FALSE’),
decode(bitand(ksppstvf,2),2,’TRUE’,’FALSE’),
ksppdesc
from x$ksppi x,
x$ksppcv y
where (x.indx = y.indx)
/*and (translate(ksppinm,’_’,’#’) not like ‘#%’
or (translate(ksppinm,’_’,’#’) like ‘#%’and ksppstdf = ‘TRUE’))*/
order by 3;

Calculate the approximate size of the SGA (System Global Area)

How to calculate the approximate size of the SGA (System Global Area)

It is very difficult and time-consuming process to find the exactly SGA size based on values of init.ora parameters. It is difficult because of different port specific sizes of data structures that are allocated in the SGA.

It is time consuming because there are so many parameters that influence the SGA size.

For example, any parameter that configures a number of resources, such as PROCESSES and SESSIONS, will have an impact on the SGA size. SGA size information displayed upon startup of the database
. You can display it by using svrmgrl or sqlplus.

Here are few examples to find the SGA in various oracle versions.

In Oracle 8.0.X – svrmgrl connect internal show sga In Oracle 8.1.X – svrmgrl or sqlplus /nolog connect internal show sga In Oracle 9.X – sqlplus connect sys as sysdba show sga What are different sub-divisions of the SGA?

Let’s take an sample output from svrmgrl (SHOW SGA)

Total System Global Area 23460696 bytes

Fixed Size 72536 bytes

Variable Size 22900736 bytes

Database Buffers 409600 bytes

Redo Buffers 77824 bytes

Total System Global Area : – Total in bytes of all the sub-divisions that makes up the SGA.

Fixed Size: Fixed size contains general information about the state of the database and the instance, which the background processes need to access. This does not store user data. Usually this area is less than 100k in size.

Variable Size: This part is influenced by the following init.ora parameters. shared_pool_size large_pool_size java_pool_size Database Buffers:

This holds data blocks copies that are read from datafiles and can be calculated by using following formula. size = db_block_buffers * block size Redo Buffers: This is another buffer in the SGA that holds information about changes made to the database.

Approximating size calculation of the SGA

In 8.0.X :Use the following formula. ((db_block_buffers * block size) + (shared_pool_size + large_pool_size + log_buffers) + 1MB

In 8.1.X : Use the following formula. ((db_block_buffers * block size) + (shared_pool_size + large_pool_size + java_pool_size + log_buffers) + 1MB

In 9.X : To approximate size of the SGA (Shared Global Area), use following formula: db_cache_size + db_keep_cache_size + db_recycle_cache_size + db_nk_cache_size + shared_pool_size + large_pool_size + java_pool_size + log_buffers + 1mb