Where to start when tuning?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Where to start when tuning?

  1. #1
    Join Date
    Feb 2001
    Location
    Belgium, Sint-Truiden
    Posts
    82
    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

  2. #2
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    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


  3. #3
    Join Date
    Sep 2001
    Posts
    62
    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.


  4. #4
    Join Date
    Mar 2002
    Posts
    301
    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.
    Say No To Plastics

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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:

    Code:
    	/*==================================================*/
    	/*                                                  */
    	/*        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
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  6. #6
    Join Date
    May 2002
    Posts
    42
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width