Solution to optimize database performance.

Document scope

This document is sample of implemented optimization solution. We recommend to develop specific optimization solutions for each database product to reach maximal optimization effect in production environments.

Optimization goal

To reach enough database performance by selected criterions.

Optimization method

1. Localization of restricted hardware resources (further «restricted resources»)

2. Localization of potentially top consumers of restricted resources (here it means database applications (further “bottlenecks”) by time periods).

3. Optimization of bottlenecks up to selected criterions.

Proposes on adequacy of optimization by selected criteria

Adequacy of optimization by selected criteria should be based on achievement of average coefficient of CPU load per week at 30% and achievement of average coefficient of CPU load per day at 50%. CPU load hear means percentage of total working time used to serve system or user queries. Measurement of these two coefficients should be performed under the methodology provided in the topic “Methods of measurements of CPU usage time coefficients”

Methods of measurements of CPU usage time coefficients

Calculation of average coefficient of CPU load per day

Measurement of service time should be performed by predefined SQL scripts, daily during two weeks period twice during working day :

Ú First measurement at 9:30

Ú Second measurement at 19:30

Average coefficient of CPU load per day is :

Lday.cpu= (T day.evening.cpu –T day.morning.cpu.)/(36000*Ncpu),

where T day.morning.cpu - CPU service time on 9:30

T day evening.cpu - CPU service time on 19:30

Ncpu - CPU quantity

Calculation of average coefficient of CPU load per week

Average coefficient of CPU load per week is:

Lweek.cpu=∑ Lday.cpu/5

Day=Mn

wherе Lday.cpu, - average coefficient of CPU load per working days.

Condition of measurement

1. Between the measurements the database server should not be restarted

2. Between the measurements the usual mode of operations is supposed.

Substantiation

Restricted resource

Database server is selected like restricted resource. It’s because of luck of CPU time, found during feasibility study. Thus CPU time is bottlenecks of database server.

This conclusion is made according to statistic gathered during one working week.

The statistic was collected by four basic categories :

1. CPU time

2. I/O

3. waits

4. latency time

Duration of resource busy periods was estimated by growing results. The results during one working week were :

MAJOR	MINOR	WAIT_EVENT	SECONDS	%	SECONDS MAJOR	% MAJOR

CPU time	Parsing	N/a	703	0.5514	114049	89.45932
	Reloads	N/a	646	0.5067
	Execution	N/a	112700	88.4012

Disk I/O	normal I/O	Db file sequential read	12899	10.1179	12950	10.13986
	full scans	Db file scattered read	26	0.0204
	direct I/O	direct path read	2	0.0016
		direct path write	2	0.0016
	other I/O	control file sequential read	17	0.0133
		control file parallel write	2	0.0016
		control file single write	2	0.0016
		db file single write	0	0.0000

waits	DBWn writes	rdbms ipc reply	2	0.0016	442	0.042357
	LGWR writes	log buffer space	52	0.0408
		log file switch completion	25
	enqueue locks	enqueue	1	0.0008
	other locks	latch free	354	0.2777
		library cache pin	7	0.0055
		library cache load lock	1	0.0008
				0.0000
latency	Commits	log file sync	43
	Network	SQL*Net message to client	24	0.0188	46	0.027454
		SQL*Net more data to client	8	0.0063
		SQL*Net more data from client	3	0.0024
		SQL*Net break/reset to client	0	0.0000
	file ops	file identify	7	0.0055
		file open	2	0.0016
	process ctl	inactive session	1	0.0008
	Misc	refresh controlfile command	1	0.0008
		switch logfile command	0	0.0000
				0.0000

TOTAL				99.98039	127487

Thus CPU load time during 4 working days, is 114049/(3600*24*4*2)=16.5% of calendar time or 114049/(3600*10*4*2)=39.6% of working time (night activity is ignored).

The Monday was considered separately, because of generation of huge reports. The results were :

MAJOR	MINOR	WAIT_EVENT	SECONS per day	% per day

CPU time	parsing	n/a	240	0.6331
	reloads	n/a	224	0.5909
	execution	n/a	27994	73.8472

disk I/O	normal I/O	Db file sequential read	9272	24.4592
	full scans	Db file scattered read	17	0.0448
	direct I/O	direct path read	7	0.0185
		direct path write	4	0.0106
	other I/O	control file sequential read	1	0.0026
		control file parallel write	0	0.0000
		control file single write	0	0.0000
		db file single write	0	0.0000

waits	DBWn writes	rdbms ipc reply	1	0.0026
	LGWR writes	log buffer space	3	0.0079
		log file switch completion	4	0.0106
	enqueue locks	enqueue	0	0.0000
	other locks	latch free	115	0.3034
		library cache pin	1	0.0026
		library cache load lock	0	0.0000


latency	commits	log file sync	12	0.0317
	Network	SQL*Net message to client	8	0.0211
		SQL*Net more data to client	4	0.0106
		SQL*Net more data from client	1	0.0026
		SQL*Net break/reset to client	0	0.0000
	file ops	file identify	0	0.0000
		file open	0	0.0000
	process ctl	inactive session	0	0.0000
	misc	refresh controlfile command	0	0.0000
		switch logfile command	0	0.0000


TOTAL			37908	100

The I/O here is more significant and has become perspective optimization subject.

Bottlenecks

The selected bottlenecks are :

1. Top database queries during working time.

2. Resource sharing in multi user mode

3. Irregular CPU load during working time (regulation modes).

Used literature

3. Craig A. Shallahamer. Tuning Oracle on the basics of response time. Version 5g, 11 February 2002

4. Tim Gorman. Tuning the Data Warehouse According to its Usage

5. Usenet group : comp.databases.oracle.server

Back to Parent Page