Where to start when tuning?

[Arbe]

All,

as of next week I need to start tuning the db that comes with our application at site of several customers.
I've theoretical knowledge on tuning (hit rates,...), but where do I really start? Does anyone have tips (and maybe some scripts) where my quest for cpu should start?

Thx,

Rik

[Orca777]

I think :
a) a short look on the init.ora-params,
for examples db_blockbuffers = 4096
log_buffer = 64000
are set for a playing-db

b) a short look on physical things
size online-redo
sharing of disk
rolbacksegments,
...

c) THEN LOOK after poor SQL-Stmts in V$sqlarea, something like TOPSQL, 90-95 % this is the reason for poor performance.

Orca

[sazzadur]

A good place to start would be to look at hit ratios of the follwing areas in the databse:

Library cache(should be 99%+) :

SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE"
FROM V$LIBRARYCACHE


Rowcache (data dictionnary cahce, should be 85%+):

SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE"
FROM V$ROWCACHE


Buffer cache(should be at least 90%, 95%+ preferred):

select (1-(phy.value/(db.value+cons.value)))*100 "Buffer Cache Hit"
from v$sysstat phy,v$sysstat db,v$sysstat cons
where phy.name='physical reads' and
db.name='db block gets' and
cons.name='consistent gets'


Also look at redo log file(bothe these values should be near 0):

select name,value
from v$sysstat
where name in ('redo log space requests','redo buffer allocation retries')

You could should also look at rollback segment hit rates(should be 100%) :

select name,waits,gets,extends,rssize, (100-(waits/gets)) "HIT RATIO"
from v$rollstat a,v$rollname b
where a.usn=b.usn

This should be ok for starters. Once you have done this if there are any problems you can start to look at rectifying them.

[oravijay]

Hi,


First find out what needs to be tuned.
For this you will have to collect statistics. We call it as Benchmark.
Once you have the benchmark then set a goal for it.

Ex:
If one query takes 10hrs to complete it's job, then set a goal like I want to bring it down to 5hrs.

As far as performance is concerned, you should set a goal to yourslef and then findout the
bottleneck. You should not go by 'Increase the performance'.

The problem might be in the statement level.
It might be due to heavy I/O or contention.
It might be due to insufficient memory allocated for Oracle.
It might be due to network.
You might not have enough space in the server itself.

The bottomline is, performance issues can come at any level. Create a benchmark and then
start digging into it.

Vijay.

[julian]

Originally posted by Arbe
All,

as of next week I need to start tuning the db that comes with our application at site of several customers.
I've theoretical knowledge on tuning (hit rates,...), but where do I really start? Does anyone have tips (and maybe some scripts) where my quest for cpu should start?

Thx,

Rik

You may try to get some basic info with this script, save it as dba.sql first:

	/*==================================================*/
	/*                                                  */
	/*        ORACLE v 8.x database SQL-script          */
	/*        Basic information about database          */
	/*          Connect with DBA-priviledges            */
	/*                                                  */
	/*        To run script in SQL*Plus type:           */
	/*                                                  */
	/*                 @dba.sql                  */
	/*                                                  */
	/*==================================================*/
clear columns
column name noprint new_value xdbname
select name from v$database
/
set feedback off
set linesize 70
set pagesize 999
spool dba_&&xdbname..txt
prompt*******************************************************************
clear columns
col report head 'REPORT OF DATABASE:' for a46
col day head 'DATE        TIME' for a20
select 	'Basic Information' report,
	TO_CHAR(sysdate,'DD.MM.YYYY  HH24:MI:SS') day
from sys.dual
/
prompt*******************************************************************
prompt
prompt
clear columns
col host_name head 'Hostname of Computer:' for a20
select host_name from v$instance
/
clear columns
col instance_name head 'Instance Name' for a14
col started head 'StartUp Time' for a19
col status for a8
col opentime head 'Open Time' for a19
select  vi.instance_name,
	to_char(vi.startup_time, 'DD.MM.YYYY HH24:MI:SS') started,
	vt.status,
	to_char(vt.open_time, 'DD.MM.YYYY HH24:MI:SS') opentime
from v$instance vi, v$thread vt
/
clear columns
col name head 'Database Name' for a22
col log_mode head 'Archivelog Mode' for a20
col createdate head 'Date of Creation' for a19
select	name, log_mode,
	to_char(created, 'DD.MM.YYYY HH24:MI:SS') createdate
from v$database
/
prompt
clear columns
col banner head 'Oracle Version Information' for a61
select banner from v$version
/
prompt
clear columns
col parameter head 'NLS_Database_Parameters'
col value head 'Values' for a15
select parameter, value from nls_database_parameters
order by parameter
/
col name head 'Database Compatible Parameter' for a30
col value head 'Value' for a15
select name, value from v$parameter
where name='compatible'
/
prompt
prompt
prompt *******************************************
prompt SYSTEM GLOBAL AREA, DB BLOCK SIZE, DATABASE
prompt BUFFERS, SHARED POOL SIZE, SORT AREA SIZE
prompt *******************************************
clear columns
clear computes
col name head 'System Global Area (SGA)|Group Name' for a24
col value head 'Size|(kB)' for 9999999.99
compute sum of value on report
break on report
select name, value/1024 value from v$sga
/
prompt
clear columns
col headbsize head 'Database block size:' for a30
col bsize head '(Bytes)' for a10
select name headbsize, value bsize from v$parameter where name = 'db_block_size'
/
clear columns
col headbfs head 'Database Buffers:' for a30
col bfs_pcs head '(pcs)' for a10
select name headbfs, value bfs_pcs from v$parameter where name = 'db_block_buffers'
/
prompt
clear columns
col headsps head 'Shared Pool Size:' for a30
col sps_size head '(Bytes)' for a10
col sps_ksize head '(kB)' for 9999999
select name headsps, value sps_size, value/1024 sps_ksize from v$parameter where name = 'shared_pool_size'
/
clear columns
col headsas head 'Sort Area Size:' for a30
col sas_size head '(Bytes)' for a10
col sas_ksize head '(kB)' for 9999999
select name headsas, value sas_size, value/1024 sas_ksize from v$parameter where name = 'sort_area_size'
/
prompt
clear columns
col headlogbfs head 'Redo Log Buffer:' for a30
col logbfs_size head '(Bytes)' for a10
col logbfs_ksize head '(kB)' for 9999999
select name headlogbfs, value logbfs_size, value/1024 logbfs_ksize from v$parameter where name = 'log_buffer'
/
prompt
prompt
prompt *****************************
prompt REDO LOG FILES, CONTROL FILES
prompt *****************************
clear columns
col member head 'Redo Log Files' for a42
col group# head 'Gr' for 9
col status head 'Status' for a8
col kbytes head 'Size|(kB)' for 99999
select 	lf.member,
	lf.group#,
	lg.status,
	lg.bytes/1024 kbytes
from v$logfile lf, v$log lg
where lf.group#=lg.group#
order by group#
/
clear columns
col name head 'Control Files' for a45
col status head 'Status' for a15
select name, status from v$controlfile
/
prompt
prompt
prompt ***************************************
prompt TABLESPACES, DATABASE FILES, FREE SPACE
prompt ***************************************
clear columns
col tablespace_name head 'Tablespace' for a15
col status head 'Status' for a7
col contents head 'Contents' for a9
col initext head 'Initial|(kB)' for 99999
col nextext head 'Next|(kB)' for 99999
col min_extents head 'Min|Ext' for 99
col max_extents head 'Max|Ext' for 999
col pct_increase head 'Inc|(%)' for 999
select 	ts.tablespace_name,
	ts.status,
	ts.contents,
	ts.initial_extent/1024 initext,
	ts.next_extent/1024 nextext,
	ts.min_extents,
	ts.max_extents,
	ts.pct_increase
from sys.dba_tablespaces ts
order by tablespace_name
/	
clear columns
col file_id head 'Id#' for 999
col file_name head 'Database filename' for a39
col Mbytes head 'Size|(MB)' for 9999.99
col tablespace_name head 'Tablespace' for a15
select	df.file_id file_id,
	df.file_name file_name,
	df.bytes/(1024*1024) MBytes,
	ts.tablespace_name tablespace_name
from sys.dba_tablespaces ts, sys.dba_data_files df
where ts.tablespace_name = df.tablespace_name
order by df.file_id, df.tablespace_name
/
clear columns
clear computes
col fid head 'Id#' for 999
col tsname head 'Tablespace' for a15
col total head 'Size|(MB)' for 99999.99
col free head 'Free|(MB)' for 99999.99
col freepros head 'Free|(%)' for 9999.99
break on report
compute sum of total on report
compute sum of free on report
select	df.file_id fid,
	df.tablespace_name tsname,
	df.bytes/(1024*1024) total,
	NVL(sum(fs.bytes)/(1024*1024),0) free,
	NVL((100*((sum(fs.bytes))/df.bytes)),0) freepros
from sys.dba_data_files df, sys.dba_free_space fs
where df.file_id = fs.file_id(+)
group by df.tablespace_name, df.FILE_ID, df.bytes
order by df.tablespace_name
/
prompt
clear columns
col name head 'Max. number of database files (init.ora)' for a48
col value head 'pcs' for a10
select name, value from v$parameter 
where name='db_files'
/
prompt
prompt
prompt *****************
prompt ROLLBACK-SEGMENTS
prompt *****************
clear columns
col owner head 'Owner' for a7
col segment_name head 'Segment name' for a12
col status head 'Status' for a7
col tablespace_name head 'Tablespace' for a12
col initial_extent/1024 head 'Init|(kB)' for 99999
col next_extent/1024 head 'Next|(kB)' for 99999
col min_extents head 'Min|Ext' for 99
col max_extents head 'Max|Ext' for 999
col pct_increase head 'Inc|(%)' for 99
select 	owner, segment_name, tablespace_name,
	initial_extent/1024, next_extent/1024,
	min_extents, max_extents,pct_increase, status
from sys.dba_rollback_segs
order by segment_name
/
prompt
prompt
prompt ****************
prompt USER INFORMATION
prompt ****************
clear columns
col user_id head 'Id#' for 999
col username head 'Username' for a12
col created head 'Created' for a12
col defTBS head 'Default|Tablespace' for a15
col tempTBS head 'Temporary|Tablespace' for a15
select 	user_id, username, TO_CHAR(created,'DD.MM.YYYY') created,
	default_tablespace defTBS, temporary_tablespace tempTBS
from sys.dba_users
order by username
/
spool off

[alenz]

here are the steps for tuning. they are listed in the order. If possiable do #1 then #2 etc. It will do you no good to increase db_block_buffers if some user is doing a full table scan on a table with millions of rows and Gigs of data. (unless of course you use 64bit oracle and have 64GB of RAM ... )

1. Tune App design
2. Tune the App. Tune all the SQL in the App.
3. Tune O/S configureation (kernel changes, etc)
4. Tune Memory Structures (Buffer cache, row cache, lib cache, log buffer ...)
5. Tune Disk I/O usage
6. Detect and eliminate Resource Contention (Latch in buffers, dispatchers...)


because you have an existing application you cannot do much about the design (#1). You can look at the SQL that is ececuting and find the bad ones.
this is straight out of the book.

Andrew
OCP