Re: What affects performance?

From: Jgreene <jgreene_at_aol.com>
Date: 6 Jun 1994 19:51:02 -0400
Message-ID: <2t0cp6$au0_at_search01.news.aol.com>

In reference to several authors on this thread, I have to argue that those of us who get paid to be computer nerds should at least put up the facade that it is a science (although luck always helps). In regards to Oracle performance, the Oracle literature devotes several chapters to testing and tuning performance. For those with slow connections, please pardon this rather long post. However, I could not resist the urge to transmit the procedure I have used to determine the health of somel rather large (100 GB+) Oracle 7 systems. I believe that this procedure can test for most of the normally encountered problems faced in databases and answer the question of what parameters you need to change or what you need to buy. It is an adaptation of the Oracle book procedures, with a slight Unix spin (VMS and other systems have more sophisticated system performance monitoring tools that Unix). Hope it helps at least someone (and hit escape or control-C if you can't wait for the whole message to download).

p.s. the format looked a lot better using fonts, bold, etc in MS Word, but we have to use what the Internet provides.

Oracle 7 Tuning and Checkout Process
6/2/94

References:	Oracle 7 Server Administrator's Guide
		Oracle 7 Server for Unix Administrator's Reference

Guide

Oracle Instance Examined:

Date Examined: _______________ Time:
_______________ to _______________

Tuning the Operating System

Monitoring CPU Load

Test: sar -u [ No script ]

Monitor over a 10 minute representative period at intervals of 15 seconds

Data: % user: Percentage of CPU time spent in the user state

% sys: Percentage of CPU time spend in the system state

% idle: Percentage of CPU time in the idle state

Goal: At peak load times, idle > 0 is good.

Action: If users seeing appreciable degradation and user CPU time > system CPU time and memory and disk are not issues, then larger CPU capacity needed.

2. Monitoring Disk I/O Performance

Test: sar -b and sar -u [No script]

Monitor over a 10 minute representative period at intervals of 15 seconds

	Data:	Avg bread:	blocks read (file systems)
		Avg bwrit:	blocks written (file systems)
		Avg pread:	partition reads (raw partitions)
		Avg pwrit:	partition writes (raw partitions)
		Avg R/W Sum:	Sum of bread, bwrit, pread and pwrit


(Total reads and writes)


		Avg %wio:	Percentage of CPU time waiting on
blocked I/O
		Avg %rcache:	Percentage of reads satisfied with
cache hit
		Avg %wcache:	Percentage of writes satisfied with

cache hit

Goal: Sum of bread, bwrit, pread and pwrit is overall health. Anything greater than 40 for two drives or 60 for four to eight drives is problem.

If %wio is consistently greater than 20, may be I/O bound

For file systems, not raw devices, %rcache should be > 90 and %wcache should be >60 else may be I/O bound.

Action: More drives, more controllers, balance I/O between disks or disk striping.

3. Monitoring Memory Utilization

Test: sar -rwp [No script]

Monitor over a 10 minute representative period at intervals of 15 seconds

	Data:	freeswp		free swap space available
		swpots/s		average number of swap outs
per second
		bswpot/s		average number of 512 byte
pages swapped out per second
		vflts/s		virtual memory faults per second
		rclm/s		average number of pages reclaimed per

second

Goal: If page outs or vflts/s is high, probably paging too much

If swap outs, anticipated page memory shortfall or reclaim pages is true, then doing heavy swapping and memory may be too little.

Action: Add memory, distribute workload more evenly, tune paging and swapping kernel parameters (On System V, increase gpgslo and/or decrease vhandr).

Tuning Oracle Memory Allocation

4. Tuning the Library Cache

Test: select sum(pins) "Exec", [ Script: libcache.sql ]

	sum(reloads) "Misses"
	from v$librarycache

Data:	Misses:		Number of times an item in the library cache
was executed
	Exec:		Number of library cache misses on execution
steps
	Miss/Exec:	Ratio of misses to execs

Goal:	Misses to executions ratio less than 1%

Action:	Allocate additional memory for library cache


(SHARED_POOL_SIZE)


	Writing identical SQL statements

5.	Tuning the Data Dictionary Cache

Test:	select sum(gets) "Gets",		[ Script:  ddcache ]
	sum(getmisses) "Miss"
	from v$rowcache



Data:	Miss:		Number of data requests resulting in cache
misses
	Gets:		Total number of requests for information
	Miss/Get:	Ratio of misses to gets

Goal:	miss to get ratio less than 10%

Action:	Increase cache memory (SHARED_POOL_SIZE)

6.	Tuning the Shared Pool with the Multi-Threaded Server

	Test:	select sum(value) || 'bytes' "Total Memory"
	[ Script:  mtsmem.sql ]
		from v$sysstat
		where name='session memory'

	Data:	Session memory:		Amount of memory allocated to
the session
		SHARED_POOL_SIZE:	An init.ora parameter

	Goal:	SHARED_POOL_SIZE is greater than or equal to this

number

Action: Increase cache memory (SHARED_POOL_SIZE)

7. Tuning the Buffer Cache

Test: select name,value [ Script:
bufcache.sql ]

		from v$sysstat
		where name in ('db block gets','consistent

gets','physical reads')

Data: Physical reads: Total number of requests for data requiring access to disk files

db block gets: Request satisfied by buffers in memory

consistent gets: Request satisfied by buffers in memory

Total gets: Sum of db block gets and consistent gets

Hit ratio: 1 - (physical reads / Total gets)

Goal: Hit ratio above 60% or 70%

Action: Increase value of DB_BLOCK_BUFFERS

Tuning Oracle I/O

8. Monitoring Disk Activity

Test: select name,phyrds,phywrts [ Script: diskact.sql ]

		from v$datafile dr,v$filestat fs
		where df.file#=fs.file#

	Data:	name:		Disk name
		phyrds:		Reads to disks
		phywrts:		Writes to disks

	Goal:	Balance load between disks

Action:	Balance by transferring files.  Reduce other applications

load to heavy data base files. Striping table data. Separating data files and redo log files.

Tuning Oracle Contention

9. Identifying Rollback Segment Contention

Test: select sum(value) [ Script:
gettotal.sql ]

		from v$sysstat
		where name in ('db block gets','consistent gets')

		select class,count			[ Script:
rbscont.sql ]
		from v$waitstat
		where class in ('system undo header','system undo
block',
		'undo header','undo block')

	Data:	Total gets:		Sum of db block gets and

consistent gets

System undo header: Waits for buffers containing header blocks of the SYSTEM RBS

System undo block: Waits for buffers other than header blocks in SYSTEM RBS

Undo header: Waits for buffers of header blocks of other than SYSTEM RBS
Undo block: Waits for buffers other than header blocks of other than SYSTEM RBS

Goal: If any of the waits is > 1% of number of requests
(gets) then problem.

Action: Add more rollback segments.

MTS Dispatcher Process Contention

Test: select network "protocol", [
Script: mtsdis.sql ]

		sum(busy)/(sum(busy)+sum(idle)) "Total busy rate"
		from v$dispatcher
		group by network

	Data:	protocol:	List of protocols used by
multi-threaded server
		Percentage:	Busy rate of the dispatcher (%)

	Goal:	Keep less than 50% busy

	Action:	Adding dispatcher processes

11.	Shared Server Process Contention

	Test:	select decode(totalq,0,'No Requests',wait/totalq ||
'1/100 sec')		[Script:  avgwait.sql ]
		"Average wait per request"
		from v$queue
		where type='COMMON'

		select count(*) "Shared Server Processes"
	[ Script:  shproc.sql ]
		from v$shared_server
		where status != 'QUIT'

	Data:	Wait per request:		Total waiting time
for all requests that have ever been in the queue 
					(1/100 sec).
		Shared server processes	Number of  shared server
processes currently running (Oracle spawns 
					these processes, as needed,
up to the maximum number allowed).
		MTS_MAX_SERVERS	Number of server processes allowed,

init.ora parameter.

Goal: Shared server processes less than MTS_MAX_SERVERS

Action: Alter MTS_MAX_SERVERS

Space in the Redo Log Buffer

Test: select name,value [ Script:
redbuf.sql ]

		from v$sysstat
		where name='redo log space requests'

	Data:	requests:	Number of times a user process waits

for space in the redo log buffer.

Goal: Should be near zero

Action: Increase size of redo log buffer

Memory for Sorts

Test: select name,value [ Script:
memsort.sql ]

		from sys$sysstat
		where name in ('sorts (memory)','sorts (disk)')



	Data:	sorts (memory):	Number of sorts small enough to be
performed entirely in sort areas without I/O 
				to temporary segments on disk.
		sorts (disk):	Number of sorts too large to be
performed entirely in memory and requiring I/O 
				to temporary segments on disk.
		
	Goal:	Minimum sorts to disk

	Action:	Increase SORT_AREA_SIZE


14.	Free List Contention

	Test:	select class,count			[ Script: 
freelist.sql ]
		from v$waitstat
		where class='free list'

		select sum(value)			[ Script: 
gettotal.sql ]
		from v$sysstat
		where name in ('db block gets','consistent gets')

	Data:	Count:		Number of waits for free blocks.
		Total gets:	Total number of requests for data.

	Goal:	Keep counts per get less than 1%

	Action:	Increase free lists (created per table)

Oracle's Top Tuning Tips

Index tables appropriately.
Use cost-based optimization whenever possible.
To direct the optimizer to choose a specific way to execute a SQL statement, use hints, or suggestions within a comment within the statement.
Take advantage of new features of Oracle 7- enforced integrity constraints, stored procedures and shared SQL and PL/SQL areas.
Reduce time spent parsing by keeping frequently-used SQL statements and stored procedures parsed after execution and by keeping data dictionary in memory.
If excessive I/O of data blocks occurs, increase the size of the buffer cache in the SGA.
On operating systems with virtual storage, be sure the SGA fits into read memory to ensure fast access to cached data.
Distribute I/O so that none of the disks containing your data files and redo log files are operating near full capacity.
Reduce contention for latches if the percentage of latch requests that result in waiting significantly exceeds 1%.
Be sure all Oracle processes, both foreground and background, have the same operating system process priority.

For more details, see the Oracle 7 Server Administrator's Guide, page 20-4. Received on Tue Jun 07 1994 - 01:51:02 CEST

This message: [ Message body ]
Next message: Courtney Kurk: "Checking if a Oracle DB is up"
Previous message: Jgreene: "Re: Oracle7 for Windows"
In reply to: Steve Caswell: "Re: What affects performance?"
Next in thread: Andrew Smith: "Re: ?? ODBC or IDAPI on Unix Platforms ??"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message