Tuesday, October 23, 2007

Oracle Monitoring and Performance Tuning FAQ

Topics
Why and when should one tune performance?
What database aspects should be monitored?
Where should the tuning effort be directed?
What tuning indicators can one use?
What tools/utilities does Oracle provide to assist with performance tuning?
What is STATSPACK and how does one use it?
When is cost based optimization triggered?
How can one optimize %XYZ% queries?
Where can one find I/O statistics per table?
My query was fine last week and now it is slow. Why?
Why is Oracle not using the damn index?
When should one rebuild an index?
How does one tune Oracle Wait events?
What is the difference between DBFile Sequential and Scattered Reads?
Where can one get more info about Oracle Tuning?

--------------------------------------------------------------------------------
Back to top of file
--------------------------------------------------------------------------------

What database aspects should be monitored?
One should implement a monitoring system to constantly monitor the following aspects of a database. This can be achieved by writing custom scripts, implementing Oracle's Enterprise Manager, or buying a third-party monitoring product. If an alarm is triggered, the system should automatically notify the DBA (e-mail, page, etc.) to take appropriate action.
Infrastructure availability:

Is the database up and responding to requests
Are the listeners up and responding to requests
Are the Oracle Names and LDAP Servers up and responding to requests
Are the Web Listeners up and responding to requests
Etc.
Things that can cause service outages:

Is the archive log destination filling up?
Objects getting close to their max extents
Tablespaces running low on free space/ Objects what would not be able to extend
User and process limits reached
Etc.
Things that can cause bad performance:

See question "What tuning indicators can one use?".

Back to top of file
--------------------------------------------------------------------------------

Where should the tuning effort be directed?
Consider the following areas for tuning. The order in which steps are listed needs to be maintained to prevent tuning side effects. For example, it is no good increasing the buffer cache if you can reduce I/O by rewriting a SQL statement.
Database Design (if it's not too late):
Poor system performance usually results from a poor database design. One should generally normalize to the 3NF. Selective denormalization can provide valuable performance improvements. When designing, always keep the "data access path" in mind. Also look at proper data partitioning, data replication, aggregation tables for decision support systems, etc.

Application Tuning:
Experience showed that approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL. Also consider proper scheduling of batch tasks after peak working hours.

Memory Tuning:
Properly size your database buffers (shared_pool, buffer cache, log buffer, etc) by looking at your buffer hit ratios. Pin large objects into memory to prevent frequent reloads.

Disk I/O Tuning:
Database files needs to be properly sized and placed to provide maximum disk subsystem throughput. Also look for frequent disk sorts, full table scans, missing indexes, row chaining, data fragmentation, etc.

Eliminate Database Contention:
Study database locks, latches and wait events carefully and eliminate where possible.

Tune the Operating System:
Monitor and tune operating system CPU, I/O and memory utilization. For more information, read the related Oracle FAQ dealing with your specific operating system.


Back to top of file
--------------------------------------------------------------------------------

What tuning indicators can one use?
The following high-level tuning indicators can be used to establish if a database is performing optimally or not:
Buffer Cache Hit Ratio
Formula: Hit Ratio = (Logical Reads - Physical Reads) / Logical Reads
Action: Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) to increase hit ratio

Library Cache Hit Ratio
Action: Increase the SHARED_POOL_SIZE to increase hit ratio

Etc.

Back to top of file
--------------------------------------------------------------------------------

What tools/utilities does Oracle provide to assist with performance tuning?
Oracle provide the following tools/ utilities to assist with performance monitoring and tuning:
TKProf

UTLBSTAT.SQL and UTLESTAT.SQL - Begin and end stats monitoring

Statspack

Oracle Enterprise Manager - Tuning Pack


Back to top of file
--------------------------------------------------------------------------------

What is STATSPACK and how does one use it?
Statspack is a set of performance monitoring and reporting utilities provided by Oracle from Oracle8i and above. Statspack provides improved BSTAT/ESTAT functionality, though the old BSTAT/ESTAT scripts are still available. For more information about STATSPACK, read the documentation in file $ORACLE_HOME/rdbms/admin/spdoc.txt.
Install Statspack:

cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" @spdrop.sql -- Install Statspack -
sqlplus "/ as sysdba" @spcreate.sql -- Enter tablespace names when prompted

Use Statspack:
sqlplus perfstat/perfstat
exec statspack.snap; -- Take a performance snapshots
exec statspack.snap;

-- Get a list of snapshots
select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;

@spreport.sql -- Enter two snapshot id's for difference report

Other Statspack Scripts:
sppurge.sql - Purge a range of Snapshot Id's between the specified begin and end Snap Id's
spauto.sql - Schedule a dbms_job to automate the collection of STATPACK statistics
spcreate.sql - Installs the STATSPACK user, tables and package on a database (Run as SYS).
spdrop.sql - Deinstall STATSPACK from database (Run as SYS)
sppurge.sql - Delete a range of Snapshot Id's from the database
spreport.sql - Report on differences between values recorded in two snapshots
sptrunc.sql - Truncates all data in Statspack tables

Back to top of file
--------------------------------------------------------------------------------

When is cost based optimization triggered?
It's important to have statistics on all tables for the CBO (Cost Based Optimizer) to work correctly. If one table involved in a statement does not have statistics, Oracle has to revert to rule-based optimization for that statement. So you really want for all tables to have statistics right away; it won't help much to just have the larger tables analyzed.
Generally, the CBO can change the execution plan when you:

Change statistics of objects by doing an ANALYZE;
Change some initialization parameters (for example: hash_join_enabled, sort_area_size, db_file_multiblock_read_count).

Back to top of file
--------------------------------------------------------------------------------

How can one optimize %XYZ% queries?
It is possible to improve %XYZ% queries by forcing the optimizer to scan all the entries from the index instead of the table. This can be done by specifying hints.
If the index is physically smaller than the table (which is usually the case) it will take less time to scan the entire index than to scan the entire table.


Back to top of file
--------------------------------------------------------------------------------

Where can one find I/O statistics per table?
The UTLESTAT report shows I/O per tablespace but one cannot see what tables in the tablespace has the most I/O.
The $ORACLE_HOME/rdbms/admin/catio.sql script creates a sample_io procedure and table to gather the required information. After executing the procedure, one can do a simple SELECT * FROM io_per_object; to extract the required information.

For more details, look at the header comments in the $ORACLE_HOME/rdbms/admin/catio.sql script.


Back to top of file
--------------------------------------------------------------------------------

My query was fine last week and now it is slow. Why?
The likely cause of this is because the execution plan has changed. Generate a current explain plan of the offending query and compare it to a previous one that was taken when the query was performing well. Usually the previous plan is not available.
Some factors that can cause a plan to change are:

Which tables are currently analyzed? Were they previously analyzed? (ie. Was the query using RBO and now CBO?)
Has OPTIMIZER_MODE been changed in INIT.ORA?
Has the DEGREE of parallelism been defined/changed on any table?
Have the tables been re-analyzed? Were the tables analyzed using estimate or compute? If estimate, what percentage was used?
Have the statistics changed?
Has the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT been changed?
Has the INIT.ORA parameter SORT_AREA_SIZE been changed?
Have any other INIT.ORA parameters been changed?
What do you think the plan should be? Run the query with hints to see if this produces the required performance.

Back to top of file
--------------------------------------------------------------------------------

Why is Oracle not using the damn index?
This problem normally only arises when the query plan is being generated by the Cost Based Optimizer. The usual cause is because the CBO calculates that executing a Full Table Scan would be faster than accessing the table via the index. Fundamental things that can be checked are:
USER_TAB_COLUMNS.NUM_DISTINCT - This column defines the number of distinct values the column holds.
USER_TABLES.NUM_ROWS - If NUM_DISTINCT = NUM_ROWS then using an index would be preferable to doing a FULL TABLE SCAN. As the NUM_DISTINCT decreases, the cost of using an index increase thereby making the index less desirable.
USER_INDEXES.CLUSTERING_FACTOR - This defines how ordered the rows are in the index. If CLUSTERING_FACTOR approaches the number of blocks in the table, the rows are ordered. If it approaches the number of rows in the table, the rows are randomly ordered. In such a case, it is unlikely that index entries in the same leaf block will point to rows in the same data blocks.
Decrease the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT - A higher value will make the cost of a FULL TABLE SCAN cheaper.
Remember that you MUST supply the leading column of an index, for the index to be used (unless you use a FAST FULL SCAN or SKIP SCANNING).
There are many other factors that affect the cost, but sometimes the above can help to show why an index is not being used by the CBO. If from checking the above you still feel that the query should be using an index, try specifying an index hint. Obtain an explain plan of the query either using TKPROF with TIMED_STATISTICS, so that one can see the CPU utilization, or with AUTOTRACE to see the statistics. Compare this to the explain plan when not using an index.


Back to top of file
--------------------------------------------------------------------------------

When should one rebuild an index?
You can run the 'ANALYZE INDEX VALIDATE STRUCTURE' command on the affected indexes - each invocation of this command creates a single row in the INDEX_STATS view. This row is overwritten by the next ANALYZE INDEX command, so copy the contents of the view into a local table after each ANALYZE. The 'badness' of the index can then be judged by the ratio of 'DEL_LF_ROWS' to 'LF_ROWS'.

Back to top of file
--------------------------------------------------------------------------------

How does one tune Oracle Wait events?
Some wait events from V$SESSION_WAIT and V$SYSTEM_EVENT views:
Event Name: Tuning Recommendation:

db file sequential read Tune SQL to do less I/O. Make sure all objects are analyzed. Redistribute I/O across disks.
buffer busy waits Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i)/ Analyze contention from SYS.V$BH
log buffer space Increase LOG_BUFFER parameter or move log files to faster disks



Back to top of file
--------------------------------------------------------------------------------

What is the difference between DBFile Sequential and Scattered Reads?
Both "db file sequential read" and "db file scattered read" events signify time waited for I/O read requests to complete. Time is reported in 100's of a second for Oracle 8i releases and below, and 1000's of a second for Oracle 9i and above. Most people confuse these events with each other as they think of how data is read from disk. Instead they should think of how data is read into the SGA buffer cache.
db file sequential read:

A sequential read operation reads data into contiguous memory (usually a single-block read with p3=1, but can be multiple blocks). Single block I/Os are usually the result of using indexes. This event is also used for rebuilding the controlfile and reading datafile headers (P2=1). In general, this event is indicative of disk contention on index reads.

db file scattered read:

Similar to db file sequential reads, except that the session is reading multiple data blocks and scatters them into different discontinuous buffers in the SGA. This statistic is NORMALLY indicating disk contention on full table scans. Rarely, data from full table scans could be fitted into a contiguous buffer area, these waits would then show up as sequential reads instead of scattered reads.

The following query shows average wait time for sequential versus scattered reads:

prompt "AVERAGE WAIT TIME FOR READ REQUESTS"
select a.average_wait "SEQ READ", b.average_wait "SCAT READ"
from sys.v_$system_event a, sys.v_$system_event b
where a.event = 'db file sequential read'
and b.event = 'db file scattered read';

Back to top of file
--------------------------------------------------------------------------------

Where can one get more info about Oracle Tuning?
Tuning Information:

This FAQ: Tuning Scripts

This FAQ: Tuning Papers

Ixora = Unix + Oracle - scripts, tips and advanced performance tuning information

Kevin Loney's New DBA FAQs: Part 2 - Database Monitoring

On-Line Services:

Yet Another Performance Profiling (YAPP) Method - Upload your BSTAT/ ESTAT and StatsPack output for analysis

itrprof SQL Analyzer - Upload your SQL_TRACE/Event10046 trace files to find bottlenecks and tuning advice

27 comments:

Anonymous said...

My partner and I stumbled over here different web address and thought
I may as well check things out. I like what I see so
now i'm following you. Look forward to looking into your web page for a second time.

Feel free to visit my website; Diets to lose weight

Anonymous said...

Your way of telling everything in this piece of writing is actually pleasant,
every one can effortlessly know it, Thanks a lot.



Check out my homepage topofutahmarathon.com

Anonymous said...

Excellent post. I was checking continuously this blog and I'm impressed! Extremely useful information specifically the last part :) I care for such information much. I was seeking this certain info for a long time. Thank you and good luck.

Here is my blog post http://ok2sharemates.com/blogs/entry/Several-Suggested-Exercises-To-Use-Your

Anonymous said...

Great post. I was checking continuously this blog and I'm inspired! Extremely useful info specially the last part :) I deal with such information much. I used to be seeking this certain information for a long time. Thank you and good luck.

my web page Max Thermo Burn Review

Anonymous said...

This design is spectacular! You certainly know how
to keep a reader amused. Between your wit and your videos, I was almost moved to start my
own blog (well, almost...HaHa!) Great job. I really enjoyed what you
had to say, and more than that, how you presented it. Too cool!



Here is my blog ... exercises to increase Vertical leap

Anonymous said...

I like the valuable info you provide in your articles. I will
bookmark your weblog and check again here frequently.
I'm quite sure I will learn many new stuff right here! Best of luck for the next!

My homepage :: workouts to increase vertical

Anonymous said...

Hey There. I found your blog using msn. This is a very well written article.
I will make sure to bookmark it and come back to read more of your useful
information. Thanks for the post. I will definitely comeback.


my homepage; the vacation fulfillment

Anonymous said...

Wow, marvelous blog format! How long have you been running a blog for?
you make blogging glance easy. The entire glance of your site is fantastic, let alone the content!


Take a look at my page ... exercises to improve vertical

Anonymous said...

This is a topic that is near to my heart..
. Many thanks! Exactly where are your contact details though?


Also visit my blog :: exercises for vertical jump

Anonymous said...

That is a very good tip particularly to those new to the blogosphere.
Simple but very accurate info… Appreciate your sharing this one.
A must read post!

Also visit my webpage: charleston bed and breakfast

Anonymous said...

Today, I went to the beachfront with my kids.
I found a sea shell and gave it to my 4 year old daughter and said "You can hear the ocean if you put this to your ear." She placed the shell to her ear and screamed.

There was a hermit crab inside and it pinched her ear.
She never wants to go back! LoL I know this is totally off topic but I had to tell someone!


Here is my web site: vertical jump program reviews

Anonymous said...

Hi it's me, I am also visiting this web site daily, this website is really good and the visitors are truly sharing nice thoughts.

my blog :: teeth-Bleaching-products.com

Anonymous said...

I am sure this post has touched all the internet people,
its really really pleasant post on building up new
blog.

Here is my blog; vertical Leap workouts

Anonymous said...

Hi to every single one, it's truly a pleasant for me to visit this site, it consists of helpful Information.

Stop by my web-site: acorn superglide

Anonymous said...

My programmer is trying to convince me to move to .
net from PHP. I have always disliked the idea because of the expenses.

But he's tryiong none the less. I've been
using Movable-type on a variety of websites for about a year and am worried about switching to
another platform. I have heard great things about blogengine.

net. Is there a way I can transfer all my wordpress posts into it?
Any help would be really appreciated!

Also visit my blog ... vertical leap exercises

Anonymous said...

Hello there, just became alert to your blog through Google, and found that
it is truly informative. I am gonna watch out for brussels.
I will appreciate if you continue this in future. Many people will be benefited from your writing.
Cheers!

Feel free to visit my webpage: workouts to improve vertical jump

Anonymous said...

Spot on with this write-up, I seriously feel this web site needs much more attention.
I'll probably be returning to read more, thanks for the advice!

My blog post :: acoustic guitar chords for beginners

Anonymous said...

My brother recommended I might like this web site. He was entirely right.

This post truly made my day. You can not imagine just how much time
I had spent for this info! Thanks!

My website ... www.health-careconsulting.com

Anonymous said...

Hello everybody, here every one is sharing these familiarity, so it's fastidious to read this webpage, and I used to pay a visit this blog all the time.

Also visit my web blog; worldmeetmarket.com

Anonymous said...

Amazing! Its truly amazing post, I have got much clear idea
regarding from this piece of writing.

Take a look at my website :: jinn-tech.com

Anonymous said...

For hottest news you have to visit world-wide-web and on
web I found this website as a best website for newest updates.


my web blog workouts to improve vertical leap

Anonymous said...

Hi there! This is kind of off topic but I need some advice from an established blog.
Is it very hard to set up your own blog? I'm not very techincal but I can figure things out pretty quick. I'm
thinking about making my own but I'm not sure where to start. Do you have any ideas or suggestions? With thanks

Stop by my blog rays-big-Mouth.com

Anonymous said...

Valuable info. Lucky me I discovered your web site unintentionally, and I'm shocked why this accident did not happened earlier! I bookmarked it.

Take a look at my web site; exercises for Vertical leap

Anonymous said...

I got this web site from my pal who shared with me concerning this website and now this time I am browsing this
web site and reading very informative articles or reviews at
this place.

Check out my web page :: exercises for vertical

Anonymous said...

Right here is the right web site for anyone who hopes to understand this topic.
You realize a whole lot its almost hard to argue with you (not that I actually would want to…HaHa).

You definitely put a new spin on a subject which has been discussed for ages.
Great stuff, just great!

Take a look at my website :: exercises to improve vertical

Anonymous said...

Simply want to say your article is as astounding.

The clearness on your publish is simply spectacular and i could
assume you're an expert in this subject. Well together with your permission let me to clutch your RSS feed to keep updated with impending post. Thank you 1,000,000 and please continue the enjoyable work.

Also visit my homepage; www.inthelobby.net

raybanoutlet001 said...

michael kors outlet online
nike air max 90
cheap jordan shoes
ed hardy uk
mont blanc outlet
cheap ugg boots
ray ban sunglasses outlet
bears jerseys
ugg outlet
ugg boots