oraperf

Document Sample
oraperf Powered By Docstoc
					     Oracle 8i
Performance Tuning Guide




    Author:    James Dey
    Date:      29 July 2003
    Version:   1.1
    Email:     jdey@macehill.co.uk
    Website:    http://www.macehill.co.uk
Table of Contents
1. Testing methodology......................................................................................................3
2. Database performance tuning ........................................................................................3
   2.1 Analyse .....................................................................................................................3
   2.2 Database monitoring ................................................................................................4
2.3 Sql monitoring ..............................................................................................................7
   2.4 Access plans .............................................................................................................7
      2.4.1 Selects ................................................................................................................8
      2.4.2 Inserts ................................................................................................................8
      2.4.2 Deletes ...............................................................................................................8
      2.4.3 Updates ..............................................................................................................8
      2.4.4 Commits ............................................................................................................9
      2.4.5 Unions................................................................................................................9
      2.4.6 Joins ...................................................................................................................9
      2.4.7 Indexing ...........................................................................................................10
   2.5 Memory...................................................................................................................11
      2.5.1 Buffer cache ....................................................................................................11
      2.5.2 Library cache...................................................................................................11
      2.5.3 Dictionary cache .............................................................................................12
      2.5.2 Sort area ...........................................................................................................12
   2.6 CPU .........................................................................................................................12
   2.7 Disk .........................................................................................................................13
      2.7.1 Datafile usage..................................................................................................13
   2.8 Logging ...................................................................................................................13
   2.9 Locking ...................................................................................................................15
   2.10 Application ...........................................................................................................15
   2.11 Sorting...................................................................................................................15
   2.12 Reorganization .....................................................................................................15
3 Server performance monitoring ....................................................................................16
   3.1 Unix performance monitoring ...............................................................................16
   3.2 Windows performance tuning ...............................................................................16
4. Important init.ora parameters ......................................................................................17
   4.1 Monitoring parameters ...........................................................................................17
   4.2 Shared memory parameters ...................................................................................17
   4.3 Bufferpool parameters ...........................................................................................18
   4.4 Log parameters .......................................................................................................19




                                                                                                                                     2
1. Testing methodology

1) Create a benchmark environment
2) Run test and capture results
3) Investigate
4) Make small changes
5) Retest



2. Database performance tuning
2.1 Analyse

First thing to check is that “analyze statistics” has been done on the database.

This can be done with:-

“select table_name, last_analysed from user_tables;”

If not then you should perform an analyze when the users aren't using the system.The
most powerful command is:-

"analyze table <table_name> compute statistics"

By analysing the tables, you are drawing a map for the Oracle cost-based optimiser.
This will allow Oracle to compute the most efficient route to retrieve data for a query.

You can analyse an entire schema by using the dbms_utility package. You can also
use dbms_stats which has more options




                                                                                       3
2.2 Database monitoring

You first need to check that “timed_statistics=true” has been set in init.ora. If it hasn’t
you’ll need to add it and stop and restart the instance. Then:-

   1. Go to the admin directory ($ORACLE_HOME/rdbms/admin on windows)

   2. Run “svrmgrl”

   3. Run utlbstat.sql before you run the application which you wish to monitor.

   4. Run the application

   5. Run utlestat.sql to complete the statistics gathering exercise

Remember, you must query statistics after a typical workload has run. Querying
statistics after a limited period will give an erroneous impression of the database
performance.

utlestat.sql creates a report in the directory in which you ran the command.
Additionally, it creates the following tables which contain the monitored sql
statistics:-

stats$stats            General System Statistics
stats$file             File I/O Statistics
stats$latches          Latching Statistics
stats$roll             Rollback Segment Statistics
stats$kqrst            Dictionary Cache Statistics
stats$dates            Table containing ending date and time

Looking at report.txt, library cache should show a gethitratio and pinhitratio > 95%, if
not then check that you are parameterizing your sql statements and/or increase the
shared_pool_size

If the dirty buffer write queue > (db_files*db_writer_processes)/2 then you need to
check first for disks which are being heavily written too and redistribute the load.
Then, if it’s still high, increase the value of db_writer_processes

The tablespace reads and writes will show you which tablespaces are being hit the
hardest. You should identify which disks they are on and spread their load. The
subsequent datafile reads and writes script helps with this.




                                                                                          4
The bufferpool hit ratio can be determined by the following piece of sql:-
select 1-(phy.value / (cur.value + con.value)) "Cache Hit Ratio",
round((1-(phy.value / (cur.value + con.value)))*100,2) "% Ratio"
from v$sysstat cur, v$sysstat con, v$sysstat phy
where cur.name = 'db block gets' and
      con.name = 'consistent gets' and
      phy.name = 'physical reads'
/
If the hit ratio < 95% then you should either reduce the load by tuning your sql and/or
increase db_block_buffers

The dictionary cache holds information about tables and columns. How well it is
performing can be determined by the following query:-
select sum(gets) “Gets”, sum(getmisses) “Misses”,
       (1 - (sum(getmisses) / (sum(gets) +
       sum(getmisses))))*100 “HitRate”
from   v$rowcache;
/
If the hit ratio < 95%, then you should increase the shared_pool_size.

The library cache holds sql information. How well it is working can be seen by using
this query:-
select sum(pins) Executions, sum(pinhits) “Execution Hits”,
       ((sum(pinhits) / sum(pins)) * 100) phitrat,
       sum(reloads) Misses,
       ((sum(pins) / (sum(pins) + sum(reloads))) * 100) hitrat
from   v$librarycache;
/
The phitrat (pin hit ratio) should be > 95% in a typical OLTP environment. It can be
lower in other environments, however, as in some applications, there are many SQL
statements which will only be executed once.

If phitrat is low, then there may be a problem that you haven’t parameterised your
SQL statements. You can check this by viewing the sql which is held in the library
cache with the following statement:-
select sql_text, loads
from   v$sql;
/

Check the log buffer size with:-
select value from v$sysstat
where name = 'redo log space requests';

If value > 100 then increase log_buffer




                                                                                       5
The following sql will produce statistics on your rollback segments:-
Select rn.Name "Rollback Segment", rs.RSSize/1024 "Size (KB)",
rs.Gets "Gets",
       rs.waits "Waits", (rs.Waits/rs.Gets)*100 "% Waits",
       rs.Shrinks "# Shrinks", rs.Extends "# Extends"
from   sys.v_$RollName rn, sys.v_$RollStat rs
where rn.usn = rs.usn
/
If there is a high percentage of waits then you need more rollback segments. If there
are a large number of shrinks and extends then your rollback extent sizes need tuning,
or you need to ensure that your application is using the correct rollback segments.




                                                                                     6
2.3 Sql monitoring

In Oracle, you can obtain access plans for single statements very easily:-

   1. Go to the admin directory and run utlxplan.sql. This creates the explain plan
      table
   2. Type “explain plan for <select statement>”

You can then query the plan table to see the access plan:-

select id “id”, parent_id “parent”, lpad(„ „,(level-1))|| operation
“operation”,
options “options”, object_name “object”
from plan_table start with id=0 connect by prior id = parent_id;

You read it, from the inside out.

Alternatively, you can type “set autotrace on”, and the plan_table is populated and
queried automatically.

If you want to capture sql statements which are run from within an application, you
need to:-

   1. set “sql_trace=on” in init.ora

This will trace each sql connection creating the trace files in the user_dump_dest
directory specified in init.ora

You can convert the trace files into a human-readable form using tkprof:-
tkprof <trace file> <text output file> explain=<user/password> sys=no
sort=fchcpu

Note the explain option ensures that you get an access plan for each executed
statement. I normally choose to sort on elapsed CPU time to fetch records, as queries
tend to be the heaviest users of the system, and CPU tends to be the bottleneck.


2.4 Access plans
You can get more visual access plans by using 3 rd party vendor products from TOAD
and Quest.




                                                                                        7
2.4.1 Selects
   1. Check that indexes are being used. B-tree indexes should be used if you are
      expecting to fetch a small number of rows from a large data set (OLTP)
      environments. Bitmap indexes are useful for selecting a large number of rows
      from a data set which has a relatively low range of values (DSS fact table)

   2. If large amounts of data are to be selected, ensure that you have set
      db_file_multiblock_read_count (prefetch) to a large value and have used a
      large block size when creating the database.

   3. If large amounts of data are to be sorted, then set sort_multiblock_read_count
      to a large value.

   4. Implement any available buffering mechanism between client application and
      server so that multiple rows are transferred to the client as a single block. This
      reduces network communication time.

   5. Allocate seperate tablespaces tables which have large volumes read.

   6. Use hints


2.4.2 Inserts
   1. If more than 25% of a table is to inserted, then using sqlldr is more efficient.
   2. Turn off logging i.e. “insert into mytable(c1,c2) values(v1,v2) nologging;”
   3. Drop indexes if not required. You can find out all the indexes that exist for
      the table by typing:- “select index_name from user_indexes where table_name
      = <table_name>”
   4. If inserting a large volume of rows, increase the size of extent size. Allocate
      seperate tablespaces for tables which have large volumes of inserts.


2.4.2 Deletes
If you want to empty the entire table - "truncate table <table>" is more efficient.


2.4.3 Updates
Ensure that you’ve specified a large enough pctfree parameter in the storage
charactistics of the table definition so that an update does not result in a chained row.




                                                                                            8
2.4.4 Commits
Transactions should be sufficiently long to ensure that data is consistent but of a short
enough length that excessive use of rollback segments and “snapshot too old” errors
do not occur. Log_buffer should be large enough so as to prevent a queue on the log
writer. Log files should be mirrored disks.

2.4.5 Unions
Ensure that there is sufficient sort_area_size to hold the contents of the tables to be
unioned


2.4.6 Joins
In terms of joins, a nested loop join should choose the table with the least rows as the
inner table (scanned multiple times) and the one with the largest rows as the outer
table.

Merge joins are used for tables where there's a foreign key index on the inner table
column and a primary key index on the outer table. Otherwise tends to be used when
there are 2 small tables. Advantage of these is inner table only needs scanning once.
Downside is that both tables need to be sorted before the join.

Hash joins apply a hashing algorithm to join columns and place the resulting values
into hash buckets. Hash joins will be used if there are 2 large tables to be joined and
you’ve set hash_join_enabled in init.ora. You should also set values for
hash_area_size so that there’s enough space to hold the hash buckets, and
hash_multiblock_read_count, so that hash buckets can be read quicker.




                                                                                           9
2.4.7 Indexing
Ensure that index tablespaces are spread across multiple disks separate from the data
tablespaces. If indexing large tables, ensure that the db_file_multiblock_read_count
(prefetch) size is large. Also you can create a cluster index for the index on a table
which is most often queried.
Check the output from tkprof. Normally, a query’s "Rows read" should be no more
than 10 times "Rows selected". If it's many multiples greater than this, then this is
indicative that full table scans are occurring.
Unfortunately, Oracle doesn’t have an easy means of determining the tables/indexes
which are most heavily used. However, you can see what the most heavily used
datafile is, look for the largest objects in the datafile, and then search the sql select
statements (captured by tkprof) to see which queries are reading these tables.

In order to achieve this, you can do 2 simple steps:-

1) To find out which datafiles are read the most use:-
select d.file#, name, phyblkrd, phyblkwrt from v$datafile d,
v$filestat f
where d.file# = f.file#;

2) To then find out which are the largest tables/indexes in the datafile, use:-
Select segment_name, file_id, sum(bytes)
from dba_extents
group by segment_name, file_id
order by 2 asc, 3 desc;

Ensure that indexes are placed on columns used in sql statements that are joined,
appear frequently in filter criteria resulting in less than 10% dataset returned, or have
"order by",”group by” or “distinct” criteria.

If there only a couple of columns extra to those in the where criteria are to be
returned, consider adding these as include columns to an index.




                                                                                        10
2.5 Memory
If there's a memory problem, check buffer pool and sort area first. Total database
memory should never exceed more than 80% of total physical memory.

svmon available for AIX gives a true picture of total physical and used memory.

"ps aeux" shows memory used per process

Always leave where possible at least 20% of physical memory for the operating
system.


2.5.1 Buffer cache
The bufferpool hit ratio can be determined by the following piece of sql:-
select 1-(phy.value / (cur.value + con.value)) "Cache Hit Ratio",
round((1-(phy.value / (cur.value + con.value)))*100,2) "% Ratio"
from v$sysstat cur, v$sysstat con, v$sysstat phy
where cur.name = 'db block gets' and
      con.name = 'consistent gets' and
      phy.name = 'physical reads'
/

If the hit ratio < 95% then you should either reduce the load by tuning your sql and/or
increase db_block_buffers

db_writer_processes should be set to the number of CPUs. This parameter determines
the number of processes which will be kicked off by the engine asynchronously to
flush dirty (updated) pages from memory to disk.


2.5.2 Library cache
The library cache holds sql information. How well it is working can be seen by using
this query:-
select sum(pins) Executions, sum(pinhits) “Execution Hits”,
       ((sum(pinhits) / sum(pins)) * 100) phitrat,
       sum(reloads) Misses,
       ((sum(pins) / (sum(pins) + sum(reloads))) * 100) hitrat
from   v$librarycache;
/
The phitrat (pin hit ratio) should be > 95% in a typical OLTP environment. It can be
lower in other environments, however, as in some applications, there are many SQL
statements which will only be executed once.




                                                                                     11
If phitrat is low, then there may be a problem that you haven’t parameterised your
SQL statements. You can check this by viewing the sql which is held in the library
cache with the following statement:-
select sql_text, loads
from   v$sql;
/


2.5.3 Dictionary cache
The dictionary cache holds information about tables and columns. How well it is
performing can be determined by the following query:-
select sum(gets) “Gets”, sum(getmisses) “Misses”,
       (1 - (sum(getmisses) / (sum(gets) +
       sum(getmisses))))*100 “HitRate”
from   v$rowcache;
/
If the hit ratio < 95%, then you should increase the shared_pool_size.


2.5.2 Sort area

The following sql will show the number of sorts in memory and the number of sorts
on disk. It’s beneficial that sorting is in memory, so sort(disk)/sort(memory) should be
almost zero.
select name, value from v$sysstat where name like „sort%‟

Note: Before increasing sort_area_size, you need to ensure that indexes are
placed on columns used in sql statements that are joined or have "order
by",”group by” or “distinct” criteria.


2.6 CPU
If it's CPU. Check indexing of queries. Unindexed queries will have to join hundreds
if not 1000s of rows. If it's I/O move tables or indexes to other disks.

top if it's installed shows you the programs which use the most CPU

vmstat monitors memory and process usage
%usr shows percentage of CPU that user programs are using, and %sys is operating
system CPU usage.
Also check that the scan rate column is low (vmstat -s) - should be zero most times
with occasional bursts. Constantly high, means that it's paging information in and out
of memory.



                                                                                     12
2.7 Disk
iostat -xnc reports disk i/o activities (AIX and Solaris). In a well-tuned system, disk
activity should be minimal, %b (busy), %wio and/or wt should be less than 20%. To
improve disk usage, spread data over multiple RAIDed disks

O/S Asynchronous i/o should be used wherever possible. e.g. on AIX set
“use_asynch_io=true” in init.ora. This ensures that Oracle can rely on the o/s to write
the data to disk, and does not have to wait for it to complete the write operation.

Normally operating systems have a buffer, and databases have a buffer. This double-
buffering has a large performance hit. Use of raw devices eliminates the o/s buffer so
should be given some thought.

However, you can also limit the file caching on some operating systems e.g. on AIX,
you can log in as root and type “vmtune –p 5 –P 10”. This tells AIX to have a hold a
minimum 5% of memory for caching files and a maximum 10%. The normal
parameter values are 20% and 80%.
Note: You’ll need to add this command to /etc/rc.local to ensure that it’s set on
every system reboot.


2.7.1 Datafile usage

In order to work out which disks are most heavily used, you can check datafile usage
in Oracle by using the following sql:-

select name, phyblkrd, phyblkwrt from v$datafile d, v$filestat f
where d.file# = f.file#;


2.8 Logging
Logging can also have an effect. Logging should be done on a seperate disk from the
main database tables and indexes. Modifying the log_buffer can have an effect if
there's a lot of logging going on. Logs should be put on separate disk. You can avoid
logging by doing the following:-

insert into table <table_name> (c1,c2) values (v1,v2) no logging

If you have long running transactions you should have a small number of large log
files to hold the redo information e.g. data warehouses. If you are worried about the
recovery time in this situation set reduce the size of log_checkpoint_interval and
log_checkpoint_timeout, and a consistent check point is taken more often. This


                                                                                        13
reduces the recovery time. However, creating a consistent check point has an
influence on application performance.
If you have many short running transactions e.g OLTP, you should have a large
number of small log files.




                                                                                14
2.9 Locking
As Oracle implements rollback segments, there are less locking issues than there are
with other database systems as there are no read locks. However, developers should
ensure that transactions are as short as possible, so that rollback segments can be kept
small.

2.10 Application
For an OLTP environment, check the report.txt from bstat/estat. The library cache
should show a gethitratio and pinhitratio > 95%, if not then check that you are
parameterizing your sql statements and/or increase the shared_pool_size

If you have many CPUs and only a single process e.g. data warehouse loading, then
turning on parallism will allow the optimiser to decide how many CPUs to use to
process the sql statement.

2.11 Sorting
The following sql will show the number of sorts in memory and the number of sorts
on disk. It’s beneficial that sorting is in memory, so sort(disk)/sort(memory) should be
almost zero.
select name, value from v$sysstat where name like „sort%‟

Note: Before increasing sort_area_size, you need to ensure that indexes are
placed on columns used in sql statements that are joined or have "order
by",”group by” or “distinct” criteria.


2.12 Reorganization
Over time, tables and indexes can become fragmented due to deletes and updates. A
reorganization and redefinition of the table and/or index storage parameters should
then be undertaken. Since Oracle 8i, this is now possible. The command to achieve
this is:-

ALTER TABLE JOB_STATUS MOVE TABLESPACE NEW_JOB_DATA
STORAGE (INITIAL 500M NEXT 500M
MINEXTENTS 2 MAXEXTENTS 200
PCTINCREASE 0)
PCTFREE 0
PCTUSED 70
NOLOGGING
PARALLEL 6;

Prior to Oracle 8i, you had to export the table data, create a new table definition and
re-import the table data

                                                                                          15
3 Server performance monitoring
3.1 Unix performance monitoring
nmon available for AIX gives a good system overview.

svmon available for AIX gives a true picture of total physical and used memory.

top if it's installed shows you the programs which use the most CPU

vmstat monitors memory and process usage
%usr shows percentage of CPU that user programs are using, and %sys is operating
system CPU usage. Also check that the scan rate column is low - should be zero most
times with occasional bursts. Constantly high, means that it's paging information in
and out of memory.

sar -o temp 60 10 will sample CPU usage for 10 minutes and write to file temp.

iostat -xnc reports disk i/o activities (AIX and Solaris). In a well-tuned system, disk
activity should be minimal, %b (busy), %wio and/or wt should be less than 20%. To
improve disk usage, spread data over multiple RAIDed disks


3.2 Windows performance tuning
Task manager shows you CPU, memory and disk usage on a per process basis.
Windows XP also shows you total network performance.

You should avoid double-buffering of data on Windows.

If you have multiple disks, make sure that you move the paging file to a separate disk.
By default, it’s created on the c: drive.

If you have more than 4Gb of physical memory, you need to turn on Windows AWE.
You do this with the /PAE boot.ini switch. (only useful for W2K Advanced and
Datacenter servers)




                                                                                      16
4. Important init.ora parameters
4.1 Monitoring parameters
timed_statistics     Collects timing statistics
sql_trace            Collects trace files containing statistics for executed sql
                     statements

4.2 Shared memory parameters
db_block_size        Size of smallest piece of data. Should be small for OLTP
                     applications so as to not to grab too much memory e.g. 2K.
                     Should be large for OLAP applications so that large amounts of
                     data can be fetched back in 1 go e.g. 32K

db_block_buffers     This parameter * db_block_size gives you the size of the
                     database buffer cache. It should be large enough to hold most
                     of the frequently queried data. 80% of physical memory should
                     be assigned to this and the database buffer cache.

db_file_multiblock_count    This should be set high e.g. 32 for OLAP applications,
                            as this encourages the choice of full table scans and
                            retrieves multiple blocks at one time, if full table scans
                            are performed. 8 is suitable for OLTP applications

shared_pool_size     Size of the dictionary cache and library cache. Should be large
                     for OLTP databases which have a large number of complex
                     applications running against them. 80% of physical memory
                     should be assigned to this and the database buffer cache.

shared_pool_reserved_size   This should be set to 10% of shared_pool_size.


sort_area_size       Care should be taken to not make this too high. A
                     sort_area_size chunk of memory is allocated to each connected
                     user, so the more users, the smaller this parameter should be to
                     prevent paging. 10K is a good value to start off with.

sort_area_retained_size     Should be set to 10% of sort_area_size. This chunk of
                            memory is retained for smaller sorts, so that large sorts
                            don’t empty the sort heap




                                                                                     17
sort_multiblock_read_count Recommend setting this to 4 for OLTP apps and 32 for
                           OLAP apps. Ensures that large sorts can be read in
                           more efficiently

Processes            Should be set to 55 (number of core Oracle processes) +
                     (2*number of users). Don’t make it too high, as it takes
                     memory from the shared_pool_size.

Large_pool_size      This is used for backups and restores. Recommend setting to
                     209715200 (200Mb), where memory is available.


4.3 Bufferpool parameters
db_block_size        Size of smallest piece of data. Should be small for OLTP
                     applications so as to not to grab too much memory e.g. 2K.
                     Should be large for OLAP applications so that large amounts of
                     data can be fetched back in 1 go e.g. 32K

db_block_buffers     This parameter * db_block_size gives you the size of the
                     database buffer cache. It should be large enough to hold most
                     of the frequently queried data. 80% of physical memory should
                     be assigned to this and the database buffer cache.

db_file_multiblock_count    This should be set high e.g. 32 for OLAP applications,
                            as this encourages the choice of full table scans and
                            retrieves multiple blocks at one time, if full table scans
                            are performed. 8 is suitable for OLTP applications

buffer_pool_keep     This should normally be set to 5% of db_block_buffers. This is
                     used to hold pinned static table data which is queried often

buffer_pool_recycle This should normally be set to 25% of db_block_buffers




                                                                                     18
4.4 Log parameters
log_buffer           This should be high for high workload applications e.g. 131072
                     This ensures that the database does not have to wait for the log
                     writer to write a log record to the transaction log

log_checkpoint_interval     Recommend setting to zero and using the
                            log_checkpoint_timeout parameter

log_checkpoint_timeout      Time in seconds before the dirty (changed) data in the
                            database buffer cache is written to disk to ensure a point
                            of consistency. This should be set low if you want to
                            recover from a crash quickly e.g. 60, or very high, if
                            you have long running transactions which you want to
                            perform quickly and are not concerned about recovery
                            e.g. 1800 (every half hour).




                                                                                   19

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:27
posted:4/25/2010
language:English
pages:19