http://VietEnglish.com

http://Edgar411.com

http://ForumGrouper.com

http://hpricot.com

perfTuneCheckList.html
04-06-2002 Bikle
-----------------------

Dan's Performance Tuning Checklist

During the 14 years I've worked with Oracle databases, I've seen some
common mistakes or design decisions which lead to poor performance.
When I find something which catches my attention, I add it to my
checklist.  I do keep in mind that a design decision which leads to
poor performance, cannot always be called a mistake.

For example, SAP has been known to generate sequence numbers by
slapping an exclusive lock on a specialized table and then SELECTing
FROM and then UPDATEing that table each time it needs a sequence
number for a transaction.  Why can't SAP just get a sequence number
from a SEQUENCE which is a thousand times faster?  SAP cannot do this
because not all RDBMS vendors support SEQUENCEs.  And SAP made a
design decision to be RDBMS independent.

So, when I saw this I added an item to my checklist:

-Look for evidence that the application gets all of its sequence numbers
from SEQUENCEs rather than by SELECTing FROM a table.

In addition to compiling a checklist, I've done a great deal of
reading about Oracle Performance Tuning.  A good place to start is the
Oracle Performance Guide.

A url to the Oracle Performance Guide is listed below:

8i:
http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76992/toc.htm

9i:
http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/server.901/a87503/toc.htm

A look at the above document reveals that it is certainly no recipe.  It contains
a great deal of information.  Some DBAs might see it as a parallel of the Web;
it contains too much information.

Where does a DBA start when he is faced with a large Production
database which performs poorly?

I start with my checklist.  

My current performance tuning checklist is displayed below:

-Make note of the version of the Oracle kernel
-Setup Statspack to collect statistics once an hour from the DB
-Take a hardware inventory of the DB host
-Take an inventory of the Applications which access the DB
-Use UNIX utilities to periodically collect information about the host's state
-Try to find other hosts which are shouldering the same kind of load
-Try to find other DBAs who administer other hosts which are shouldering the same kind of load


-Any SQL statements in V$SQL which are "pigs?"
-Are the SQL "pigs" well written?
-Any memory structures (Library Cache, Buffer Cache, ...) which are too small?
-Do large segments make use of the RECYCLE pool?
-Any latch contention? (keep in mind this is usually a symptom)
-RB segment contention?
-RB segment header contention?
-Do RB segments make use of OPTIMAL keyword? If yes, how often do they shrink?
-What percentage of the time is the kernel getting objects it is looking for?
 (data buffers, sql statements, latches, rb segments, rb segments headers, ...)
-Any attempt to balance I/O via Volume management software?
-Any attempt to balance I/O via relative physical placement of DB objects?
-Are "accordion segments" placed in locally managed tablespaces?
-Are large segments partitioned?
-Do we need to rebuild indexes?
-Do we need to coalesce indexes?
-How are we rebuilding indexes? From data in the table? the old index?
-Are the DB users assigned a locally managed temp tablespace?
-What's in the SYSTEM tablespace?
-Are short lived connections forced to spawn dedicated servers?
-What are the details surrounding the DB backup?
-Are we making use of incremental backups to reduce system load?
-Are any OLTP sessions bumping into locks?
-Are any OLTP sessions attempting to obtain locks in "wait" mode?
-Any evidence of deadlocks?
-Are any sessions bumping into locks?
-Any small tables with non-unique indexes? (consider dropping the index)
-Any fk columns on tables which are not indexed? (consider adding index)
-Any small frequently accessed tables which are not cached?
-Is init.ora parameter sql_trace=true?
-Is init.ora parameter cursor_sharing=force?
-Are redolog files mirrored by oracle or volume mgr software?
-Are archivelog files mirrored by oracle or volume mgr software?
-Do we have any purge routines which are issuing large DELETE commands?
 (Possible fix is savvy use of partitions)
-Any large bulk loads which force the kernel to look at freelists?
-Do we have a job or cron job scheduled to ANALYZE segments?
-Are we using InterMedia Text? If yes, do we periodically optimize it?
-Is anything getting pinned into the SGA (tables, packages)?
-Do we have low ratio of logical reads to Net8 roundtrips?
-Do we have any chained or migrated rows?
-Are we using the long datatype? (CLOB might be more appropriate)
-Do we frequently ANALYZE large segments which rarely change?
-Do we have any large b-tree indexes with low cardinality?
-Do we have any indexes filled by a sequence which are not reverse key indexes?
-How are sequence numbers generated? via a SEQUENCE? How much is cached?
-Does Net8 clean up after dead connections? (fix via sqlnet.expire_time)
-Are we storing unneeded RMAN information about old backups in the controlfiles?
-Are redologs on raw devices or 'direct I/O' file systems?
-Do we have any large, 'stagnant' indexes?
-Are we using RMAN to backup the DB? If yes, is LARGE_POOL_SIZE set to at least 200m?
-Are we making use of the FIRST_ROWS hint for online ad hoc queries?
-Are we making use of the ALL_ROWS hint for long running reports?

OS problems/information:

-Any hardware problems listed in /var/adm/messages?
-Slow I/O rates on any of the disks?
-All the CPUs working?
-All the memory working?
-Is the host swapping?
-How does the SGA size relate to total RAM?

Application problems/information:

-When is the Application busy/slow?
-What types of Application behavior stress the DB 
(long running reports, data loads, world events, ...)?
-How does the Application connect?  Does it route connections into a pool
 of dedicated servers?  Does it rely upon MTS? Does each user get a dedicated server?

After I obtain answers to the above set of questions, I use DBA common
sense to focus on objects within the environment which act as key bottlenecks.

For example, assume for the sake of simplicity, I found only two
problems with the database:

1. The database is using UFS data files and UFS redolog files
2. Many sessions are waiting on locks

I would focus on the locks first.  The maximum boost I could get by
switching from UFS to raw would be maybe 20 percent.  The maximum
boost I could get by removing a lock put in place by poor application
design (Designer forgot to index an FK column for example) could be quite
literally, infinite.

Finally, one change which should be looked at is an upgrade of the Oracle
software from 8, or 8i to 9i.  The 9i Oracle kernel has numerous performance
enhancements over 8 and 8i.  Some thoughts about 9i performance enhancements
may be found at the url listed below:

9i Performance Considerations