Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle Scripts for Improving Database Performance

Oracle Tips by Burleson Consulting
July/August 1996

 

Even if you've got some fancy third-party tools for measuring database performance, you can benefit from extending your basic Oracle performance tools and creating a proactive, customized framework for tuning your Oracle databases.

by Don Burleson

This article originally appeared in the July/August 1996 issue of Oracle Magazine.

LISTING 1: A sample perf UNIX script that replaces the regular UTLESTAT utility with a customized version called SPECIALESTAT.

if [ $# != 1 ]
then 
	echo "usage: $0 { bstat | estat }" 
	exit 1 
fi 
 
if [ "${1}" != bstat -a "${1}" != estat ] 
then 
	echo "usage: $0 { bstat | estat }" 
	exit 1 
fi 
 
SQLPATH=/usr/perfmon 
 
if [ "${1}" = bstat ] 
then 
	# Begin collection 
	sqlplua << ! 
		connect internal 
		@${ORACLE_HOME}/rdbms/admin/utlbstat 
		exit 
! 
 
else 
	# End data collection 
	sqlplus << ! 
		connect internal 
		@${SQLPATH}/specialestat 
		exit 
! 
	sqlplus / @${SQLPATH}/tracker ${ORACLE_SID} 
fi 
  
exit 0 
L
ISTING 2: This script invokes the tracker utility to capture the UTLESTAT information into permanent tables and then drop the temporary tables.
rem This SQL script will load the tracking tables 
 
insert into track_stats 
( oracle_sid, collection_started) 
select '&1',min(stats_gather_times) 
from sys.stats$dates; 
 
update track_stats 
set collection_ended = 
			(select max(stats_gather_times) 
			from sys.stats$dates), 
	run_date = to_date(substr(collection_started,1,12),'DD-MON-YY HH24'), 
		consistent_gets = 
			(select change 
			from sys.stats$stats 
			where name = 'consistent gets'), 
		block_gets = 
			(select change 
			from sys.stats$stats 
			where name = 'db block gets'), 
		physical_reads = 
			(select change 
			from sys.stats$stats 
			where name = 'physical reads'), 
		buffer_busy_waits = 
			(select change 
			from sys.stats$stats 
			where name = 'buffer busy waits'), 
		buffer_free_needed = 
			(select change 
			from sys.stats$stats 
			where name = 'free buffer requested'), 
		free_buffer_waits = 
			(select change 
			from sys.stats$stats 
			where name = 'free buffer waits'), 
		free_buffer_scans = 
			(select change 
			from sys.stats$stats 
			where name = 'free buffer scans'), 
		enqueue_timeouts = 
			(select change 
			from sys.stats$stats 
			where name = 'enqueue timeouts'), 
		redo_space_wait = 
			(select change 
			from sys.stats$stats 
			where name = 'redo log space wait time'), 
		write_wait_time = 
			(select change 
			from sys.stats$stats 
			where name = 'write wait time'), 
		write_complete_waits = 
			(select change 
			from sys.stats$stats 
			where name = 'write complete waits'), 
		rollback_header_gets = 
			(select sum(trans_tbl_gets) 
			from sys.stats$roll), 
		rollback_header_waits = 
			(select sum(trans_tbl_waits) 
			from sys.stats$roll) 
where collection_ended is null; 
 
insert into latches 
(ls_latch_name, ls_latch_gets, ls_latch_misses, 
	ls_latch_sleeps, ls_latch_immed_gets, 		
ls_latch_immed_misses) 
select name, gets, misses, sleeps, immed_gets, immed_miss 
from sys.stats$latches; 
 
update latches set 
	ls_collection_started = 
			(select min(stats_gather_times) 
			from sys.stats$dates) 
where ls_oracle_sid is null; 
 
update latches set 
	run_date = to_date(substr(ls_collection_started,
		1,12),'DD-MON-YY HH24') 
where ls_oracle_sid is null; 
 
update latches 
set ls_oracle_sid = 
			(select '&1' 
			from sys.dual), 
	ls_collection_ended = 
			(select max(stats_gather_times) 
			from sys.stats$dates) 
where ls_oracle_sid is null; 
 

LISTING 3: This routine interrogates all tablespaces and dumps the information into a statistical table.

insert into tablespace_stat values (
select dfs.tablespace_name,
round(sum(dfs.bytes)/1048576,2),
round(max(dfs.bytes)/1048576,2)
from  sys.dba_free_space dfs
group by dfs.tablespace_name
order by dfs.tablespace_name); 

LISTING 4: Attach this script to a cron process to gather table-extent information at a specified time interval.

insert into tab_stat values(
select ds.tablespace_name,
dt.owner,
dt.table_name,
ds.bytes/1024,
ds.extents,
dt.max_extents,
dt.initial_extent/1024,
dt.next_extent/1024,
dt.pct_increase,
dt.pct_free,
dt.pct_used
from sys.dba_segments ds,
sys.dba_tables dt
where ds.tablespace_name = dt.tablespace_name
and ds.owner = dt.owner
and ds.segment_name = dt.table_name
order by 1,2,3);

LISTING 5: This table-extents-report script joins the extents table against itself to show growth in extents.

break on c0 skip 2 on c1 skip 1
ttitle " Table Report| > 50 Extents or new extents";
spool /tmp/rpt10
select
distinct
b.sid c0,
substr(b.owner,1,6) c1,
substr(b.tablespace_name,1,10) c2,
substr(b.table_name,1,20) c3,
(b.blocks_alloc*2048)/1024 c4,
c.next_extent/1024 c5,
a.extents c6,
b.extents c7
from tab_stat a,
tab_stat b,
dba_tables c
where
rtrim(c.table_name) = rtrim(b.table_name)
and
a.sid = b.sid
and 
  rtrim(a.tablespace_name) <> 'SYSTEM'
and 
a.tablespace_name = b.tablespace_name
and
a.table_name = b.table_name
and
to_char(a.run_date) = to_char(b.run_date-7)
-- compare to one week prior
and
(
a.extents < b.extents
-- where extents has increased
or
b.extents > 50
)
order by b.sid;

LISTING 6: Use this script to get a fast overview of the state of a troubled system.

spool /tmp/snap;
 
prompt****************************************************
prompt Hit Ratio Section
prompt****************************************************
prompt
prompt =========================
prompt BUFFER HIT RATIO
prompt =========================
prompt (should be > 70, else increase db_block_buffers in init.ora)
 
--select trunc((1-(sum(decode(name,'physical reads',value,0))/
--(sum(decode(name,'db block gets',value,0))+
--(sum(decode(name,'consistent gets',value,0)))))
-- )* 100) "Buffer Hit Ratio"
--from v$sysstat;
 
column "logical_reads" format 99,999,999,999
column "phys_reads" format 999,999,999
column "phy_writes" format 999,999,999
select a.value + b.value "logical_reads",
c.value   "phys_reads",
d.value   "phy_writes",
round(100 * ((a.value+b.value)-c.value) /
(a.value+b.value))
"BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c, v$sysstat d
where
a.statistic# = 37
and
b.statistic# = 38
and
c.statistic# = 39
and
d.statistic# = 40;
 
prompt
prompt
prompt =========================
prompt DATA DICT HIT RATIO
prompt =========================
prompt (should be higher than 90 else increase shared_pool_size in init.ora)
prompt
 
column "Data Dict. Gets"   format 999,999,999
column "Data Dict. cache misses" format 999,999,999
select sum(gets) "Data Dict. Gets",
sum(getmisses) "Data Dict. cache misses",
trunc((1-(sum(getmisses)/sum(gets)))*100) "DATA DICT CACHE HIT
RATIO"
from v$rowcache;
prompt
prompt =========================
prompt LIBRARY CACHE MISS RATIO
prompt =========================
prompt (If > .1, i.e., more than 1% of the pins 
prompt  resulted in reloads, then increase the shared_pool_size in init.ora)
column "LIBRARY CACHE MISS RATIO" format 99.9999
column "executions" format 999,999,999
column "Cache misses while executing" format 999,999,999
select sum(pins) "executions", sum(reloads) "Cache misses while executing",
(((sum(reloads)/sum(pins)))) "LIBRARY CACHE MISS RATIO"
from v$librarycache;
 
prompt
prompt =========================
prompt Library Cache Section
prompt =========================
prompt hit ratio should be > 70, and pin ratio > 70 ...
prompt
 
column "reloads" format 999,999,999
select namespace, trunc(gethitratio * 100) "Hit ratio",
trunc(pinhitratio * 100) "pin hit ratio", reloads "reloads"
from v$librarycache;
prompt
prompt
prompt =========================
prompt REDO LOG BUFFER
prompt =========================
prompt
set heading off
column value format 999,999,999
select substr(name,1,30),
value
from v$sysstat where name = 'redo log space requests';
 
set heading on
prompt
prompt
prompt
 
column bytes format 999,999,999
select name, bytes from v$sgastat where name = 'free memory';
 
prompt
prompt****************************************************
prompt SQL Summary Section
prompt****************************************************
prompt
column "Tot SQL run since startup" format 999,999,999
column "SQL executing now"  format 999,999,999
select sum(executions) "Tot SQL run since startup",
sum(users_executing) "SQL executing now"
from v$sqlarea;
 
prompt
prompt
prompt****************************************************
prompt Lock Section
prompt****************************************************
prompt
prompt =========================
prompt SYSTEM-WIDE LOCKS - all requests for locks or latches
prompt =========================
prompt
select substr(username,1,12) "User",
substr(lock_type,1,18) "Lock Type",
substr(mode_held,1,18) "Mode Held"
from sys.dba_lock a, v$session b
where lock_type not in ('Media Recovery','Redo Thread')
and a.session_id = b.sid;
prompt
prompt =========================
prompt DDL LOCKS - These are usually triggers or other DDL
prompt =========================
prompt
select substr(username,1,12) "User",
substr(owner,1,8) "Owner",
substr(name,1,15) "Name",
substr(a.type,1,20) "Type",
substr(mode_held,1,11) "Mode held"
from sys.dba_ddl_locks a, v$session b
where a.session_id = b.sid;
 
prompt
prompt =========================
prompt DML LOCKS - These are table and row locks...
prompt =========================
prompt
select substr(username,1,12) "User",
substr(owner,1,8) "Owner",
substr(name,1,20) "Name",
substr(mode_held,1,21) "Mode held"
from sys.dba_dml_locks a, v$session b
where a.session_id = b.sid;
 
prompt
prompt
prompt****************************************************
prompt Latch Section
prompt****************************************************
prompt if miss_ratio or immediate_miss_ratio > 1 then latch
prompt contention exists, decrease LOG_SMALL_ENTRY_MAX_SIZE in init.ora
prompt
column "miss_ratio" format .99
column "immediate_miss_ratio" format .99
select substr(l.name,1,30) name,
(misses/(gets+.001))*100 "miss_ratio",
(immediate_misses/(immediate_gets+.001))*100
"immediate_miss_ratio"
from v$latch l, v$latchname ln
where l.latch# = ln.latch#
and (
(misses/(gets+.001))*100 > .2
or
(immediate_misses/(immediate_gets+.001))*100 > .2
)
order by l.name;
 
prompt
prompt
prompt****************************************************
prompt Rollback Segment Section
prompt****************************************************
prompt if any count below is > 1% of the total number of requests for data
prompt then more rollback segments are needed
 --column count format 999,999,999
select class, count
from v$waitstat
where class in ('free list','system undo header','system undo block',
'undo header','undo block')
group by class,count;
 
column "Tot # of Requests for Data" format 999,999,999
select sum(value) "Tot # of Requests for Data" from v$sys stat where
name in ('db block gets', 'consistent gets');
prompt
prompt =========================
prompt ROLLBACK SEGMENT CONTENTION
prompt =========================
prompt
prompt   If any ratio is > .01 then more rollback segments are needed
 
column "Ratio" format 99.99999
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
 
column "total_waits" format 999,999,999
column "total_timeouts" format 999,999,999
prompt
prompt
set feedback on;
prompt****************************************************
prompt Session Event Section
prompt****************************************************
prompt if average-wait > 0 then contention exists
prompt
select substr(event,1,30) event,
total_waits, total_timeouts, average_wait
 from v$session_event
where average_wait > 0 ;
--or total_timeouts > 0;
 
prompt
prompt
prompt****************************************************
prompt Queue Section
prompt****************************************************
prompt average wait for queues should be near zero ...
prompt
column "totalq" format 999,999,999
column "# queued" format 999,999,999
select paddr, type "Queue type", queued "# queued", wait, totalq,
decode(totalq,0,0,wait/totalq) "AVG WAIT" from v$queue;
 
set feedback on;
prompt
prompt
--prompt****************************************************
--prompt Multi-threaded Server Section
--prompt****************************************************
--prompt
--prompt If the following number is > 1
--prompt then increase MTS_MAX_SERVERS parm in init.ora
--prompt
-- select decode( totalq, 0, 'No Requests',
--    wait/totalq || ' hundredths of seconds')
--    "Avg wait per request queue"
-- from v$queue
-- where type = 'COMMON';
 
--prompt
--prompt If the following number increases, consider adding dispatcher processes
 
--prompt
-- select decode( sum(totalq), 0, 'No Responses',
-- sum(wait)/sum(totalq) || ' hundredths of seconds')
-- "Avg wait per response queue"
-- from v$queue q, v$dispatcher d
-- where q.type = 'DISPATCHER'
-- and q.paddr = d.paddr;
 
--set feedback off;
--prompt
--prompt
--prompt =========================
--prompt DISPATCHER USAGE
--prompt =========================
--prompt (If Time Busy > 50, then change
MTS_MAX_DISPATCHERS in init.ora)
--column "Time Busy" format 999,999.999
--column busy  format 999,999,999
--column idle  format 999,999,999
--select name, status, idle, busy,
-- (busy/(busy+idle))*100 "Time Busy"
--from v$dispatcher;
 
--prompt
--prompt
--select count(*) "Shared Server Processes"
-- from v$shared_server
-- where status = 'QUIT';
 
--prompt
--prompt
--prompt high-water mark for the multi-threaded server
--prompt
 
--select * from v$mts;
 
--prompt
--prompt****************************************************
--prompt file i/o should be evenly distributed across drives.
--prompt
 
--select
--substr(a.file#,1,2) "#",
--substr(a.name,1,30) "Name",
--a.status,
--a.bytes,
--b.phyrds,
--b.phywrts
--from v$datafile a, v$filestat b
--where a.file# = b.file#;
 
--select substr(name,1,55) system_statistic, value
-- from v$sysstat
-- order by name;
 
spool off;
[px009u]: 99 99 52

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational