Database Performance Tuning Scripts -Keep Handy

****************************************************************************

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);