****************************************************************************
Database Performance Tuning Scripts
****************************************************************************
---------------------------------Measure
the Buffer Cache Hit Ratio------------------------------------
Increase
DB_BLOCK_BUFFER if cache hit ratio < 90%
-------------------------------------------------------------------------------select
1-(phy.value / (cur.value + con.value)) "Cache Hit Ratio",
round((1-(phy.value
/ (cur.value + con.value)))*100,2) "% Ratio"
from
v$sysstat cur, v$sysstat con, v$sysstat phy
where
cur.name = 'db block gets' and
con.name
= 'consistent gets' and
phy.name
= 'physical reads'
/
select
(d.value+c.value-p.value)/(d.value+c.value)*100 "hit ratio"
from
v$sysstat d,v$sysstat c,v$sysstat p
where
d.name='db block gets' and
c.name='consistent
gets' and
p.name
='physical reads'
/
col
name format a20 heading "Buffer Pool Name
select
name,free_buffer_wait,write_complete_wait,
buffer_busy_wait,db_block_gets,
consistent_gets,physical_reads,
physical_writes
from
v$buffer_pool_statistics;
----------------Sessions
with bad Buffer Cache Hit Ratio in %---------------
select substr(a.sid,1,5)
"Sid",
substr(a.username,1,10)
"User",
substr(a.osuser,1,23)
USER_NM,
--substr(a.machine,1,23)
MACHINE_NM,
substr(a.program,1,17)
program,
b.consistent_gets
"ConsGets",
b.block_gets
"BlockGets",
b.physical_reads
"PhysReads",
100
* round((b.consistent_gets + b.block_gets - b.physical_reads) /
(b.consistent_gets
+ b.block_gets),3) HitRatio
from
v$session a, v$sess_io b
where
a.sid = b.sid
and
(b.consistent_gets + b.block_gets) > 0
and
a.username is not null
and
a.username not in ('SYS','SYSTEM')
order
by HitRatio asc
/
-------------response
time per transaction---------------------------
select ((WAIT + val) / TRXN_CNT)
as TRXN_SPEED
from
(select sum(TOTAL_WAITS) as WAIT from v$system_event) ,
(select
value as VAL from v$sysstat where name='CPU used by this session'),
(select
sum(value) as TRXN_CNT from v$sysstat where name in ('user rollbacks','user
commits'))
/
------------sql
text executing morethan 25 times(SQL Hard Parsing )--------------
select
substr(sql_text,1,50), count(*) from v$sql
group
by substr(sql_text,1,50) having count(*) > 25
/
**---------------------Latch
Contention----------------**
col
name format a40 heading "Latch Name"
SELECT
name, gets, misses,
round((gets-misses)/decode(gets,0,1,gets),3)
hit_ratio,
sleeps,
round(sleeps/decode(misses,0,1,misses),3)
"sleeps/misses"
from
v$latch
where
gets != 0
order
by name;
select
name,immediate_gets,immediate_misses,(immediate_gets)/(immediate_gets+immediate_misses)
Hit_Ratio
from
v$latch where immediate_gets != 0;
**---------------Shared
Pool Statistics-------------------------------**
col
namespace format a22
select
namespace,gets,gethits,round(gethitratio,2)
gethitratio,pins,pinhits,round(pinhitratio,2)
pinhitratio,reloads,invalidations
from v$librarycache;
----------------------------------------------------------------------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
< 90 then dbms_output.put_line('*** HINT: Buffer Cache too low! Increase the
DB Block Buffer value.');
END
IF;
if
redlog
> 100 then dbms_output.put_line('*** HINT: Log Buffer value is rather
low!');
END
IF;
END;
/
---------------List
all supported INIT.ORA parameters----------------------------
select a.ksppinm
name, b.ksppstvl value, b.ksppstdf isdefault,
decode(a.ksppity,
1, 'boolean', 2, 'string', 3, 'number', 4, 'file',
a.ksppity)
type,
a.ksppdesc
description
from
sys.x$ksppi a, sys.x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm not like '\_%' escape '\'
order
by name
/
--------------List
all un-supported INIT.ORA parameters-------------------------
select a.ksppinm
name, b.ksppstvl value, b.ksppstdf isdefault,
decode(a.ksppity,
1, 'boolean', 2, 'string', 3, 'number', 4, 'file',
a.ksppity)
type,
a.ksppdesc
description
from
sys.x$ksppi a, sys.x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm like '\_%' escape '\'
order
by name
/
---------------active
(in progress) transactionsROLLBACK SEG-----------------
col name format a8
col
username format a8
col
osuser format a8
col
start_time format a17
col
status format a12
tti
'Active transactions'
select
username, terminal, osuser,
t.start_time,
r.name, t.used_ublk "ROLLB BLKS",
decode(t.space,
'YES', 'SPACE TX',
decode(t.recursive,
'YES', 'RECURSIVE TX',
decode(t.noundo,
'YES', 'NO UNDO TX', t.status)
))
status
from
sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
where
t.xidusn = r.usn
and
t.ses_addr = s.saddr
/
-------------------- Display rollback segment statistics---------------------------
column
"Rollback Segment"
format
a16
column
"Size (Kb)"
format
9,999,999
column
"Gets"
format
999,999,990
column
"Waits"
format
9,999,990
column
"% Waits"
format
90.00
column
"# Shrinks"
format
999,990
column
"# Extends"
format
999,990
Prompt
Prompt
Rollback Segment Statistics...
Select
rn.Name "Rollback Segment", rs.RSSize/1024 "Size (KB)",
rs.Gets "Gets",
rs.waits
"Waits", (rs.Waits/rs.Gets)*100 "% Waits",
rs.Shrinks
"# Shrinks", rs.Extends "# Extends"
from
sys.v_$RollName rn, sys.v_$RollStat rs
where
rn.usn = rs.usn
/
show
sga
show
parameters area_size
set
pages 999;
column
pga_size format 999,999,999
select
2048576+a.value+b.value
pga_size
from
v$parameter a,v$parameter b
where
a.name = 'sort_area_size'
and
b.name = 'hash_area_size';
column
pga_size format 999,999,999
accept
hwm number prompt 'Enter the high-water mark of connected users: '
select
150*(2048576+a.value+b.value)
pga_size
from
v$parameter a, v$parameter b
where
a.name = 'sort_area_size'
and
b.name = 'hash_area_size';
****************************************************************************************************
================================
Database Health monitoring script.
================================
REM
Segments Max extents & Current extent comparison
set
line 180
set
pagesize 10000
set
feedback off
prompt
col
Time format a50 heading "System Time"
select
to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS') Time from dual;
prompt
prompt
prompt
Rem**-------------Objects
Reaching Max extents-----------**
col
segment_name format a40 heading "Object Name"
col
max_extents format 9999999999 heading "Max Extents"
col
curr_extents format 99999999999 heading "Curent Extents"
select
a.segment_name,a.max_extents,b.curr_extents from dba_segments a,(select
segment_name,max(extent_id)
curr_extents from dba_extents group by segment_name) b where a.segment_name =
b.segment_name
and
(a.max_extents - b.curr_extents) <= 10;
Rem**--------------User
Session Information-----------------**
col
sid format 9999 heading "SID"
col
serial# format 9999999 heading "SERIAL NO"
col
logon_time format 9999999 heading "Login Time"
col
username format a12
col
osuser format a24
col
program format a38
select
s.sid,s.serial#,p.spid,s.username,s.osuser,s.program,to_char(s.LOGON_TIME,'DD-MON-YY:HH24:MI:SS')
"Log on
Time",round((s.LAST_CALL_ET/(60*60*24)),2)"Wait
in Days" from v$session s,v$process p where s.paddr = p.addr and
s.username
is not null order by 8 desc;
Rem**----------------------File
Information-------------------------**
col
file_name format a55 heading "File Name"
col
SizeInMB format 99999999 heading "Total Size (MB)"
col
MAXSIZE format 99999999 heading "Maximum Size (MB)"
compute
sum of SizeInMB on report
select
substr(file_name,1,60)
FIL_NAME,
BYTES/(1024*1024)
SizeInMB,
AUTOEXTENSIBLE,
MAXBYTES/(1024*1024)
MAXSIZE
from
dba_data_files
union
all
select
substr(file_name,1,60)
FIL_NAME,
BYTES/(1024*1024)
SizeInMB,
AUTOEXTENSIBLE,
MAXBYTES/(1024*1024)
MAXSIZE
from
dba_temp_files;
Rem**-----------------Tablespace
Information--------------------------*
col
tablespace_name format a25 heading "Tablespace Name"
col
logging format a10
col
status format a12
col
extent_management format a30 heading "Local/Dict"
select
tablespace_name,status,contents,decode(logging,LOGGING,'YES','NO')
Logging,extent_management from dba_tablespaces;
SELECT
Total.name "Tablespace Name",
Free_space,
(total_space-Free_space) Used_space, total_space
FROM
(select
tablespace_name, sum(bytes/1024/1024) Free_Space
from
sys.dba_free_space
group
by tablespace_name
)
Free,
(select
b.name, sum(bytes/1024/1024) TOTAL_SPACE
from
sys.v_$datafile a, sys.v_$tablespace B
where
a.ts# = b.ts#
group
by b.name
)
Total
WHERE
Free.Tablespace_name = Total.name;
Rem**-----------Tablespace
Fragmentation Status---------------------**
col
TABLESPACE_NAME format a25 heading "Tablespace Name"
select
substr(TABLESPACE_NAME,1,12)
Name,
TOTAL_EXTENTS
"Total Extents",
EXTENTS_COALESCED,
decode(PERCENT_EXTENTS_COALESCED,100,'NO','YES')
"Frag"
from
dba_free_space_coalesced;
Rem**---------------Top
20 Events and System Statistics-------------------------------**
col
event format a40 heading "Event Name"
col
Stat format a40 heading "Stat Name"
select
* from ( select name "Stat",value from v$sysstat order by value desc
) where rownum <= 20 ;
select
* from ( select event,total_waits from v$system_event order by total_waits desc
) where rownum <=20;
Rem**---------------Buffer
Cache statistics-------------------------------**
select
(1-(a.value/(b.value+c.value)))*100 "Buffer Cache Hit ratio"
from
v$sysstat a,v$sysstat b,v$sysstat c
where
a.name= 'physical reads'
and
b.name = 'consistent gets'
and
c.name = 'db block gets';
Rem**---------------File
I/O statistics-------------------------------**
col
file# format 99 heading "File No"
select
file#,PHYRDS "Physical Reads",
PHYWRTS
"Physical Writes",
READTIM
"Read Time",
WRITETIM
"Write Time",
AVGIOTIM
"Avg Time" from v$filestat;
select
f.file#,d.file_name,f.PHYRDS "Physical Reads",
f.PHYWRTS
"Physical Writes",
f.READTIM
"Read Time",
f.WRITETIM
"Write Time",
f.AVGIOTIM
"Avg Time"
from
v$filestat f,dba_data_files d
where
d.file_id=f.file#
/
--------------------------------------getting
sql statement -------------------------------------
select sql_text
from
v$sql
where
address in ( select address
from
v$open_cursor
where
sid = (select sid
from
v$mystat
where
rownum=1) )
and
upper(sql_text) like '%
------------------------------------idle
connections-------------------------------------------------
select
s.sid||','||s.serial# session,
s.USERNAME,
s.last_call_et
seconds_since_active,
s.status,
s.sql_address,
s.program
from
v$session s
where
s.sid = nvl(to_number('&sid'),s.sid);
