Index information

–show index information. showDex.sql
set linesize 150
col owner for a10
col index_name for a25
col table_name for a20 hea TNAME
col table_owner for a10 hea TOWNER
col compression for a3 trunc hea CMP
col index_type for a10 trunc
col degree for a6
col ANLYZED for a7 trunc
col STATUS for a3 trunc hea STS
col ini_trans for 999 hea ITR
col max_trans for 999 hea MTR
col num_rows for 9999999 hea ROWS
col tablespace_name for a10 hea TBNAME
select owner,index_name,index_type,STATUS,INI_TRANS,MAX_TRANS,COMPRESSION,degree,TABLESPACE_NAME,num_rows,
to_char(LAST_ANALYZED, ‘DDMonYY’) ANLYZED,table_owner,table_name from DBA_INDEXES where owner in
(‘SYS’,’SYSTEM’,’OUTLN’,’DBSNMP’,’OUTLN’,’ORACLE’,’PERFSTAT’,’OPS$ORACLE’) order by 1,2;

Monitoring: database extents

1. Objects whose next extent is too big to fit in the tablespace (ORA-1653, ORA-1654, ORA-1655):

SELECT OWNER,SEGMENT_NAME, round(NEXT_EXTENT/(1024*1024),1) next_in_MB,
S.TABLESPACE_NAME,round(MAX_FREE_BYTES/(1024*1024),1) max_free_MB,
autoextensible_bytes/(1024*1024) autoextend_MB
FROM SYS.DBA_SEGMENTS S,
(SELECT TABLESPACE_NAME,MAX(BYTES) MAX_FREE_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(select tablespace_name, sum(maxbytes)-sum(bytes) autoextensible_bytes
from dba_data_files
group by tablespace_name) d
WHERE
S.NEXT_EXTENT > ( F.MAX_FREE_BYTES + d.autoextensible_bytes )
AND S.TABLESPACE_NAME=F.TABLESPACE_NAME and
f.tablespace_name=d.tablespace_name;

2. Objects that are near their MAXEXTENTS (ORA-1632):

select I.OWNER, INDEX_NAME, EXTENTS, I.MAX_EXTENTS
from dba_indexes I, DBA_SEGMENTS S
where I.OWNER=S.OWNER AND I.INDEX_NAME=S.SEGMENT_NAME AND
S.SEGMENT_TYPE=’INDEX’
AND I.max_extents < 100000
AND I.MAX_EXTENTS – S.EXTENTS < 10;

SCRIPT: what is the session doing

select
–‘alter system kill session ”’|| s.SID||’,’ || s.serial# ||”” ,
–‘kill -9 ‘ || p.spid,
p.SPID UnixProcess ,s.SID,s.serial#,
s.USERNAME,s.COMMAND,s.MACHINE,
s.SQL_ADDRESS,s.SQL_HASH_VALUE
,s.program, cpu_time,fetches,
disk_reads,buffer_gets,rows_processed,
executions,child_latch,event,sql_text,COMMAND_TYPE,
sbc.name,to_char(sbc.last_captured,’yyyy-mm-dd hh24:mi:ss’),
sbc.value_string
from v$session s
join v$process p on p.ADDR = s.PADDR — and p.SID in (27817)
left outer join v$sqlarea sa on sa.ADDRESS = s.SQL_ADDRESS
left outer join V$SQL_BIND_CAPTURE sbc on sbc.ADDRESS = s.SQL_ADDRESS
where 1=1
–and s.program like ‘imp%’
–and s.username =’SIEBEL’
–AND p.SPID in (15179,8304)
–and s.SID in (1019)

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;