Oracle Performance -- Possible Disk Bottleneck

[Database Administrator]

My apologies if this subject has been discussed. I searched the
groups, and I couldn't find a good thread.

We've been having performance problems with our Client/Server
application for months. Users contantly complain of slow response
times to their queries.

Here's the environment:

Oracle 9i on Windows. Poweredge 6850. 4, 3 GHz Quad Core
Processors. 8 GB RAM. 9i databses are stored on an EMC Clarrion
CX500 LUN -- RAID 10. 6, 15K 146GB dedicated disks. Oracle logs are
stored on a separate RAID 10 LUN on dedicated disks. 1 GBE switched
backend. Users connect using FastEthernet. XP clients. All disks on
the SAN are fibre channel.

CPU utilization is fine. RAM utilization is fine. Throughput on the
NIC is fine -- maxes out at 50 Mbps for a short while when users first
log in in the morning. Averages are 20 Mbps.

Using perfmon in Windows, If I look at Disk Reads/Sec and Disk Writes/
sec., here's what I see for averages:

Disk Reads/sec = 2200 Avg
Disk Writes/sec = 10 Avg

Our reads/sec seem EXTREMELY high for only 80 users.

Can someone help me understand if this is truly a disk bottleneck?

Thanks in advance!

[Database Administrator]

In message <1180043760.128981.180670@g4g2000hsf.googlegroups.c om>,
beth.stover@gmail.com writes
>My apologies if this subject has been discussed. I searched the
>groups, and I couldn't find a good thread.
>
>We've been having performance problems with our Client/Server
>application for months. Users contantly complain of slow response
>times to their queries.
>
>Here's the environment:
>
>Oracle 9i on Windows. Poweredge 6850. 4, 3 GHz Quad Core
>Processors. 8 GB RAM. 9i databses are stored on an EMC Clarrion
>CX500 LUN -- RAID 10. 6, 15K 146GB dedicated disks. Oracle logs are
>stored on a separate RAID 10 LUN on dedicated disks. 1 GBE switched
>backend. Users connect using FastEthernet. XP clients. All disks on
>the SAN are fibre channel.
>
>CPU utilization is fine. RAM utilization is fine. Throughput on the
>NIC is fine -- maxes out at 50 Mbps for a short while when users first
>log in in the morning. Averages are 20 Mbps.
>
>Using perfmon in Windows, If I look at Disk Reads/Sec and Disk Writes/
>sec., here's what I see for averages:
>
>Disk Reads/sec = 2200 Avg
>Disk Writes/sec = 10 Avg
>
>Our reads/sec seem EXTREMELY high for only 80 users.
>
>Can someone help me understand if this is truly a disk bottleneck?
>
>Thanks in advance!
>
Try using statspack to get an oracle perspective on what the delay is.

You can look it up in the docs, but essentially you install statspack,
set it up to collect statistics at intervals (maybe every 30 minutes)
and then run reports on selected intervals.

This will show what is using up time within the database.
--
Jim Smith
Ponder Stibbons Limited
RSS

[Database Administrator]

On May 24, 5:56 pm, beth.sto...@gmail.com wrote:
> My apologies if this subject has been discussed. I searched the
> groups, and I couldn't find a good thread.
>
> We've been having performance problems with our Client/Server
> application for months. Users contantly complain of slow response
> times to their queries.
>
> Here's the environment:
>
> Oracle 9i on Windows. Poweredge 6850. 4, 3 GHz Quad Core
> Processors. 8 GB RAM. 9i databses are stored on an EMC Clarrion
> CX500 LUN -- RAID 10. 6, 15K 146GB dedicated disks. Oracle logs are
> stored on a separate RAID 10 LUN on dedicated disks. 1 GBE switched
> backend. Users connect using FastEthernet. XP clients. All disks on
> the SAN are fibre channel.
>
> CPU utilization is fine. RAM utilization is fine. Throughput on the
> NIC is fine -- maxes out at 50 Mbps for a short while when users first
> log in in the morning. Averages are 20 Mbps.
>
> Using perfmon in Windows, If I look at Disk Reads/Sec and Disk Writes/
> sec., here's what I see for averages:
>
> Disk Reads/sec = 2200 Avg
> Disk Writes/sec = 10 Avg
>
> Our reads/sec seem EXTREMELY high for only 80 users.
>
> Can someone help me understand if this is truly a disk bottleneck?
>
> Thanks in advance!

Don't use the windows tools to check on SAN performance. Talk with
the storage admins. They will have a much better perspective on both
throughput and utilization. The perfmon tool might indicate an issue,
but I doubt it will tell much. I like the statspack suggestion if
done accurately and snap are scoped correctly.

Do you have virus software running? This will typically bump up I/O
reads in perfmon.

[Database Administrator]

On May 25, 7:42 am, EscVector wrote:
> On May 24, 5:56 pm, beth.sto...@gmail.com wrote:
>
>
>
>
>
> > My apologies if this subject has been discussed. I searched the
> > groups, and I couldn't find a good thread.
>
> > We've been having performance problems with our Client/Server
> > application for months. Users contantly complain of slow response
> > times to their queries.
>
> > Here's the environment:
>
> > Oracle 9i on Windows. Poweredge 6850. 4, 3 GHz Quad Core
> > Processors. 8 GB RAM. 9i databses are stored on an EMC Clarrion
> > CX500 LUN -- RAID 10. 6, 15K 146GB dedicated disks. Oracle logs are
> > stored on a separate RAID 10 LUN on dedicated disks. 1 GBE switched
> > backend. Users connect using FastEthernet. XP clients. All disks on
> > the SAN are fibre channel.
>
> > CPU utilization is fine. RAM utilization is fine. Throughput on the
> > NIC is fine -- maxes out at 50 Mbps for a short while when users first
> > log in in the morning. Averages are 20 Mbps.
>
> > Using perfmon in Windows, If I look at Disk Reads/Sec and Disk Writes/
> > sec., here's what I see for averages:
>
> > Disk Reads/sec = 2200 Avg
> > Disk Writes/sec = 10 Avg
>
> > Our reads/sec seem EXTREMELY high for only 80 users.
>
> > Can someone help me understand if this is truly a disk bottleneck?
>
> > Thanks in advance!
>
> Don't use the windows tools to check on SAN performance. Talk with
> the storage admins. They will have a much better perspective on both
> throughput and utilization. The perfmon tool might indicate an issue,
> but I doubt it will tell much. I like the statspack suggestion if
> done accurately and snap are scoped correctly.
>
> Do you have virus software running? This will typically bump up I/O
> reads in perfmon.- Hide quoted text -
>
> - Show quoted text -


Thanks for all of the information and suggestions.

I did some comparison of Windows tools (Perfmon) and EMC tools
(Navisphere analyzer). Perfmon was actually reporting the IO
correctly. Average IO reads are 2000, Average Write IO is @ 10.
There are some interesting metrics that I don't understand from Nav.
Analyzer: The storage processors are showing peaks close to 100% at
certain times of day, but the disks are not at 100%. I'm not sure
what that means. LUN % utilization is the same, so it looks like the
Storage processors are responsible for the high % utilization.

STATSPACK analysis shows something similar to Perfmon and Nav.
Analyzer: Physical reads are 2552, physical writes are 52. There's a
ton of information in the statspack report, so I'm not sure what else
to look at.

There is anti-virus software on this machine, but I don't think it
accounts for a significant part of the 2000+ IOPs per second. We use
the same product on all of our servers. IO is not a problem on any
other server. On this machine, there are peaks of 10,000 IOPs. This
seems abnormally high.

We're a very small shop, so I'm the storage person. This is a new
technology for us, so I'm in a learning curve here.

There don't appear to be many good SAN related groups that are active,
so I'm hoping someone here has experience with SANs and ORACLE to help
out.

EMC support is NOT what we were expecting. For the amount we pay for
support, the response is very very disappointing. I've had a ticket
open on this for MONTHS. Literally.

I guess the bottom line here is that I need to prove to my boss that
this is a disk bottleneck and that giving the database more spindles
will help. Also, I'd like to understand why IO reads are so high for
Oracle.

Any information would be appreciated.

[Database Administrator]

On Jun 7, 3:49 pm, beth.sto...@gmail.com wrote:
> On May 25, 7:42 am, EscVector wrote:
>
>
>
> > On May 24, 5:56 pm, beth.sto...@gmail.com wrote:
>
> > > My apologies if this subject has been discussed. I searched the
> > > groups, and I couldn't find a good thread.
>
> > > We've been having performance problems with our Client/Server
> > > application for months. Users contantly complain of slow response
> > > times to their queries.
>
> > > Here's the environment:
>
> > > Oracle 9i on Windows. Poweredge 6850. 4, 3 GHz Quad Core
> > > Processors. 8 GB RAM. 9i databses are stored on an EMC Clarrion
> > > CX500 LUN -- RAID 10. 6, 15K 146GB dedicated disks. Oracle logs are
> > > stored on a separate RAID 10 LUN on dedicated disks. 1 GBE switched
> > > backend. Users connect using FastEthernet. XP clients. All disks on
> > > the SAN are fibre channel.
>
> > > CPU utilization is fine. RAM utilization is fine. Throughput on the
> > > NIC is fine -- maxes out at 50 Mbps for a short while when users first
> > > log in in the morning. Averages are 20 Mbps.
>
> > > Using perfmon in Windows, If I look at Disk Reads/Sec and Disk Writes/
> > > sec., here's what I see for averages:
>
> > > Disk Reads/sec = 2200 Avg
> > > Disk Writes/sec = 10 Avg
>
> > > Our reads/sec seem EXTREMELY high for only 80 users.
>
> > > Can someone help me understand if this is truly a disk bottleneck?
>
> > > Thanks in advance!
>
> > Don't use the windows tools to check on SAN performance. Talk with
> > the storage admins. They will have a much better perspective on both
> > throughput and utilization. The perfmon tool might indicate an issue,
> > but I doubt it will tell much. I like the statspack suggestion if
> > done accurately and snap are scoped correctly.
>
> > Do you have virus software running? This will typically bump up I/O
> > reads in perfmon.- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks for all of the information and suggestions.
>
> I did some comparison of Windows tools (Perfmon) and EMC tools
> (Navisphere analyzer). Perfmon was actually reporting the IO
> correctly. Average IO reads are 2000, Average Write IO is @ 10.
> There are some interesting metrics that I don't understand from Nav.
> Analyzer: The storage processors are showing peaks close to 100% at
> certain times of day, but the disks are not at 100%. I'm not sure
> what that means. LUN % utilization is the same, so it looks like the
> Storage processors are responsible for the high % utilization.
>
> STATSPACK analysis shows something similar to Perfmon and Nav.
> Analyzer: Physical reads are 2552, physical writes are 52. There's a
> ton of information in the statspack report, so I'm not sure what else
> to look at.
>
> There is anti-virus software on this machine, but I don't think it
> accounts for a significant part of the 2000+ IOPs per second. We use
> the same product on all of our servers. IO is not a problem on any
> other server. On this machine, there are peaks of 10,000 IOPs. This
> seems abnormally high.
>
> We're a very small shop, so I'm the storage person. This is a new
> technology for us, so I'm in a learning curve here.
>
> There don't appear to be many good SAN related groups that are active,
> so I'm hoping someone here has experience with SANs and ORACLE to help
> out.
>
> EMC support is NOT what we were expecting. For the amount we pay for
> support, the response is very very disappointing. I've had a ticket
> open on this for MONTHS. Literally.
>
> I guess the bottom line here is that I need to prove to my boss that
> this is a disk bottleneck and that giving the database more spindles
> will help. Also, I'd like to understand why IO reads are so high for
> Oracle.
>
> Any information would be appreciated.

Congratulations. So you're the new DBA. Don't panic.
http://www.ooug.org/presentations/newDBAexpanded.pdf

Ok, that one is a little bit dated.
Someone posted that you want to install and use the Oracle-provided
tool "Statspack" to determine what is performing poorly or impacting
performance of business-critical usage. Some level 7 statspack
snapshots would be a good start (if statistics_level='TYPICAL').

Here is a place to start:
http://www.oracle.com/technology/dep...spack_opm4.pdf

There is a great deal of reference material regarding Oracle's
Statspack.
Install it, take a few snapshots, generate a report and the top
statements should jump right off of the page at you. What you do with
it after that is largely a matter of what can be changed in the
environment and in the application code.

If the database is configured with default parameters, there may be a
great deal of low-hanging fruit - in terms of:

- statistics have not yet been gathered or are stale.
- buffer cache is set to a very small value and can be increased
- pga_workarea_target is very small and can be increased
- optimizer settings can be altered resulting in more favorable
execution plans and response times.

If the budget and timeframe allows take classes.
If this is urgent and there isn't time to learn it on your own, hire
in a consultant.

Check out the documentation at:

http://www.oracle.com/pls/db102/homepage
http://www.oracle.com/technology/doc...base10gr2.html

The "2 Day DBA" might be good for you:

About this Book

Oracle Database 2 Day DBA is a database administration quick start
guide that teaches you how to perform day-to-day database
administrative tasks. The goal of this book is to help you understand
the concepts behind the Oracle Database. It teaches you how to perform
all common administration tasks needed to keep the database
operational, including how to perform basic troubleshooting and
performance monitoring activities.

The primary administrative interface used in this book is Oracle
Enterprise Manager in Database Console mode, featuring all the self-
management capabilities introduced in the Oracle Database.

If not, the Concepts Guide is located on that same page.

good luck.

-bdbafh






..

[Database Administrator]

Thanks for the support and the links. I'll take a look at the links
and the book.

Maybe I'm asking the wrong questions.

Maybe a better question would be:

Can 6 15k fibre channel drives in a RAID 10 configuration support 2000
IO's per second?

I've read that 15k drives can perform 180 IO's per second. And that's
with no RAID penalty. If this is the case, then my 6 drives should
only be able to handle 6 x 180 IOps = 1080 total capacity. If this is
true, then my average IO's per second of 2000+ is WAY over the
capacity of the drives.

Questions:

1. Is the scenario above a reasonable approach to solving this
problem?
2. Can 15k drives really only handle 180 IO's per second?
3. What happens to an Oracle environment (transaction oriented) when
drive IO limits are reached. Are there other metrics I can look at to
confirm this is a disk bottle neck. Is there something in the
statsapack that can show me this is a disk bottleneck?
4. Does 2000 IOps seem really high?





On Jun 7, 1:44 pm, bdbafh wrote:
> On Jun 7, 3:49 pm, beth.sto...@gmail.com wrote:
>
>
>
>
>
> > On May 25, 7:42 am, EscVector wrote:
>
> > > On May 24, 5:56 pm, beth.sto...@gmail.com wrote:
>
> > > > My apologies if this subject has been discussed. I searched the
> > > > groups, and I couldn't find a good thread.
>
> > > > We've been having performance problems with our Client/Server
> > > > application for months. Users contantly complain of slow response
> > > > times to their queries.
>
> > > > Here's the environment:
>
> > > > Oracle 9i on Windows. Poweredge 6850. 4, 3 GHz Quad Core
> > > > Processors. 8 GB RAM. 9i databses are stored on an EMC Clarrion
> > > > CX500 LUN -- RAID 10. 6, 15K 146GB dedicated disks. Oracle logs are
> > > > stored on a separate RAID 10 LUN on dedicated disks. 1 GBE switched
> > > > backend. Users connect using FastEthernet. XP clients. All disks on
> > > > the SAN are fibre channel.
>
> > > > CPU utilization is fine. RAM utilization is fine. Throughput on the
> > > > NIC is fine -- maxes out at 50 Mbps for a short while when users first
> > > > log in in the morning. Averages are 20 Mbps.
>
> > > > Using perfmon in Windows, If I look at Disk Reads/Sec and Disk Writes/
> > > > sec., here's what I see for averages:
>
> > > > Disk Reads/sec = 2200 Avg
> > > > Disk Writes/sec = 10 Avg
>
> > > > Our reads/sec seem EXTREMELY high for only 80 users.
>
> > > > Can someone help me understand if this is truly a disk bottleneck?
>
> > > > Thanks in advance!
>
> > > Don't use the windows tools to check on SAN performance. Talk with
> > > the storage admins. They will have a much better perspective on both
> > > throughput and utilization. The perfmon tool might indicate an issue,
> > > but I doubt it will tell much. I like the statspack suggestion if
> > > done accurately and snap are scoped correctly.
>
> > > Do you have virus software running? This will typically bump up I/O
> > > reads in perfmon.- Hide quoted text -
>
> > > - Show quoted text -
>
> > Thanks for all of the information and suggestions.
>
> > I did some comparison of Windows tools (Perfmon) and EMC tools
> > (Navisphere analyzer). Perfmon was actually reporting the IO
> > correctly. Average IO reads are 2000, Average Write IO is @ 10.
> > There are some interesting metrics that I don't understand from Nav.
> > Analyzer: The storage processors are showing peaks close to 100% at
> > certain times of day, but the disks are not at 100%. I'm not sure
> > what that means. LUN % utilization is the same, so it looks like the
> > Storage processors are responsible for the high % utilization.
>
> > STATSPACK analysis shows something similar to Perfmon and Nav.
> > Analyzer: Physical reads are 2552, physical writes are 52. There's a
> > ton of information in the statspack report, so I'm not sure what else
> > to look at.
>
> > There is anti-virus software on this machine, but I don't think it
> > accounts for a significant part of the 2000+ IOPs per second. We use
> > the same product on all of our servers. IO is not a problem on any
> > other server. On this machine, there are peaks of 10,000 IOPs. This
> > seems abnormally high.
>
> > We're a very small shop, so I'm the storage person. This is a new
> > technology for us, so I'm in a learning curve here.
>
> > There don't appear to be many good SAN related groups that are active,
> > so I'm hoping someone here has experience with SANs and ORACLE to help
> > out.
>
> > EMC support is NOT what we were expecting. For the amount we pay for
> > support, the response is very very disappointing. I've had a ticket
> > open on this for MONTHS. Literally.
>
> > I guess the bottom line here is that I need to prove to my boss that
> > this is a disk bottleneck and that giving the database more spindles
> > will help. Also, I'd like to understand why IO reads are so high for
> > Oracle.
>
> > Any information would be appreciated.
>
> Congratulations. So you're the new DBA. Don't panic.http://www.ooug.org/presentations/newDBAexpanded.pdf
>
> Ok, that one is a little bit dated.
> Someone posted that you want to install and use the Oracle-provided
> tool "Statspack" to determine what is performing poorly or impacting
> performance of business-critical usage. Some level 7 statspack
> snapshots would be a good start (if statistics_level='TYPICAL').
>
> Here is a place to start:http://www.oracle.com/technology/dep.../statspack_opm...
>
> There is a great deal of reference material regarding Oracle's
> Statspack.
> Install it, take a few snapshots, generate a report and the top
> statements should jump right off of the page at you. What you do with
> it after that is largely a matter of what can be changed in the
> environment and in the application code.
>
> If the database is configured with default parameters, there may be a
> great deal of low-hanging fruit - in terms of:
>
> - statistics have not yet been gathered or are stale.
> - buffer cache is set to a very small value and can be increased
> - pga_workarea_target is very small and can be increased
> - optimizer settings can be altered resulting in more favorable
> execution plans and response times.
>
> If the budget and timeframe allows take classes.
> If this is urgent and there isn't time to learn it on your own, hire
> in a consultant.
>
> Check out the documentation at:
>
> http://www.oracle.com/pls/db102/home...base10gr2.html
>
> The "2 Day DBA" might be good for you:
>
> About this Book
>
> Oracle Database 2 Day DBA is a database administration quick start
> guide that teaches you how to perform day-to-day database
> administrative tasks. The goal of this book is to help you understand
> the concepts behind the Oracle Database. It teaches you how to perform
> all common administration tasks needed to keep the database
> operational, including how to perform basic troubleshooting and
> performance monitoring activities.
>
> The primary administrative interface used in this book is Oracle
> Enterprise Manager in Database Console mode, featuring all the self-
> management capabilities introduced in the Oracle Database.
>
> If not, the Concepts Guide is located on that same page.
>
> good luck.
>
> -bdbafh
>
> .- Hide quoted text -
>
> - Show quoted text -

[Database Administrator]

On Jun 7, 12:49 pm, beth.sto...@gmail.com wrote:
> On May 25, 7:42 am, EscVector wrote:
>
>
>
>
>
> > On May 24, 5:56 pm, beth.sto...@gmail.com wrote:
>
> > > My apologies if this subject has been discussed. I searched the
> > > groups, and I couldn't find a good thread.
>
> > > We've been having performance problems with our Client/Server
> > > application for months. Users contantly complain of slow response
> > > times to their queries.
>
> > > Here's the environment:
>
> > > Oracle 9i on Windows. Poweredge 6850. 4, 3 GHz Quad Core
> > > Processors. 8 GB RAM. 9i databses are stored on an EMC Clarrion
> > > CX500 LUN -- RAID 10. 6, 15K 146GB dedicated disks. Oracle logs are
> > > stored on a separate RAID 10 LUN on dedicated disks. 1 GBE switched
> > > backend. Users connect using FastEthernet. XP clients. All disks on
> > > the SAN are fibre channel.
>
> > > CPU utilization is fine. RAM utilization is fine. Throughput on the
> > > NIC is fine -- maxes out at 50 Mbps for a short while when users first
> > > log in in the morning. Averages are 20 Mbps.
>
> > > Using perfmon in Windows, If I look at Disk Reads/Sec and Disk Writes/
> > > sec., here's what I see for averages:
>
> > > Disk Reads/sec = 2200 Avg
> > > Disk Writes/sec = 10 Avg
>
> > > Our reads/sec seem EXTREMELY high for only 80 users.
>
> > > Can someone help me understand if this is truly a disk bottleneck?
>
> > > Thanks in advance!
>
> > Don't use the windows tools to check on SAN performance. Talk with
> > the storage admins. They will have a much better perspective on both
> > throughput and utilization. The perfmon tool might indicate an issue,
> > but I doubt it will tell much. I like the statspack suggestion if
> > done accurately and snap are scoped correctly.
>
> > Do you have virus software running? This will typically bump up I/O
> > reads in perfmon.- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks for all of the information and suggestions.
>
> I did some comparison of Windows tools (Perfmon) and EMC tools
> (Navisphere analyzer). Perfmon was actually reporting the IO
> correctly. Average IO reads are 2000, Average Write IO is @ 10.
> There are some interesting metrics that I don't understand from Nav.
> Analyzer: The storage processors are showing peaks close to 100% at
> certain times of day, but the disks are not at 100%. I'm not sure
> what that means. LUN % utilization is the same, so it looks like the
> Storage processors are responsible for the high % utilization.
>
> STATSPACK analysis shows something similar to Perfmon and Nav.
> Analyzer: Physical reads are 2552, physical writes are 52. There's a
> ton of information in the statspack report, so I'm not sure what else
> to look at.
>
> There is anti-virus software on this machine, but I don't think it
> accounts for a significant part of the 2000+ IOPs per second. We use
> the same product on all of our servers. IO is not a problem on any
> other server. On this machine, there are peaks of 10,000 IOPs. This
> seems abnormally high.
>
> We're a very small shop, so I'm the storage person. This is a new
> technology for us, so I'm in a learning curve here.
>
> There don't appear to be many good SAN related groups that are active,
> so I'm hoping someone here has experience with SANs and ORACLE to help
> out.
>
> EMC support is NOT what we were expecting. For the amount we pay for
> support, the response is very very disappointing. I've had a ticket
> open on this for MONTHS. Literally.
>
> I guess the bottom line here is that I need to prove to my boss that
> this is a disk bottleneck and that giving the database more spindles
> will help. Also, I'd like to understand why IO reads are so high for
> Oracle.
>
> Any information would be appreciated.- Hide quoted text -
>
> - Show quoted text -

On top of bdbafh's suggestions, looks like you should get Cary
Milsap's book and follow it through to determine the exact set of
problems.

Besides that, you might want to post init.ora parameters that are not
default, 9 databases (assuming you are using the Oracle definition of
"database," it's different than the one many MS-oriented folk use - do
the terms ORACLE_HOME and ORACLE_SID mean anything to you?) could be a
major part of the problem. As Paul mentioned, the cache buffers may
be set too low, causing many reads to load them, where if there were
more, the buffers would already be loaded. If you just had one db
rather than nine, you could easily increase the buffer size. But they
are additive across databases. There is an Oracle product called OEM
(I've forgotten where it is, perhaps on a supplemental cd) that has
some decent visualizations of what is happening, and what will likely
happen with certain changes - but only per db. The data comes from
system tables named something like %ADVISOR%, which you can learn
about in the Performance manual, learning much about what's going on
while you are at it.

Besides that, as you will no doubt discover as you work through these
things, most performance problems come from the application code.

It's only after all that you can prove that more spindles will help.
Of course, proof to a boss may not need to be so rigorous, and many
people have no problem saying more spindles=better performance. It's
just a bit embarrassing after spending all that money to discover the
bottleneck was elsewhere, or some simple code change or adding an
index could have fixed 90% of the problem. The statspack may tell you
that, or it may mask the problem, depending... but you won't know by
not running it. There should be some "top" information in there you
may want to post. It's possible that you would be doing more writing
if you weren't doing so much reading, the wait info may be
informative. Some people in this group are willing to go through an
entire statspack if you post it.

I'm guessing storage processor peaking while disks are not may
indicate loads satisfied by storage cache, if they were satisfied by
Oracle's cache instead you may see some improvement. Google about for
papers about reducing logical I/O, too.

jg
--
@home.com is bogus.
"There is no try in production." - bdbafh

[Database Administrator]

I'm digging into the performance article tonight. Thanks so much for
the feedback.

Here is the statspack. I hate posting something so large, so I hope
it's ok. If someone is kind enough to take a peak, then it is
appreciated.

SQL>
SQL> column library format a12 trunc;
SQL> column pinhitratio heading 'PINHITRATI';
SQL> column gethitratio heading 'GETHITRATI';
SQL> column invalidations heading 'INVALIDATI';
SQL> set numwidth 10;
SQL> Rem Select Library cache statistics. The pin hit rate should be
high.
SQL> select namespace library,
2 gets,
3 round(decode(gethits,0,1,gethits)/decode(gets,0,1,gets),3)
4 gethitratio,
5 pins,
6 round(decode(pinhits,0,1,pinhits)/decode(pins,0,1,pins),3)
7 pinhitratio,
8 reloads, invalidations
9 from stats$lib;

LIBRARY GETS GETHITRATI PINS PINHITRATI RELOADS
INVALIDATI
------------ ---------- ---------- ---------- ---------- ----------
----------
BODY 696 .993 709 .961
22 0
CLUSTER 1645 .998 1462 .995
0 0
INDEX 0 1 0 1
0 0
JAVA DATA 0 1 0 1
0 0
JAVA RESOURC 0 1 0 1
0 0
JAVA SOURCE 0 1 0 1
0 0
OBJECT 0 1 0 1
0 0
PIPE 0 1 0 1
0 0
SQL AREA 122439 .944 1060773 .982
2886 10
TABLE/PROCED 461062 .991 434007 .978
3622 0
TRIGGER 2914 .972 2914 .93
122 0

11 rows selected.

SQL>
SQL> column "Statistic" format a27 trunc;
SQL> column "Per Transaction" heading "Per Transact";
SQL> column ((start_users+end_users)/2) heading "((START_USER"
SQL> set numwidth 12;
SQL> Rem The total is the total value of the statistic between the
time
SQL> Rem bstat was run and the time estat was run. Note that the
estat
SQL> Rem script logs on to the instance so the per_logon statistics
will
SQL> Rem always be based on at least one logon.
SQL> select 'Users connected at ',to_char(start_time, 'dd-mon-yy
hh24:mi:ss'),':',start_users from stats$dates;

'USERSCONNECTEDAT' TO_CHAR(START_TIME '
START_USERS
------------------- ------------------ -
------------
Users connected at 18-apr-07 15:00:02 :
99

SQL> select 'Users connected at ',to_char(end_time, 'dd-mon-yy
hh24:mi:ss'),':',end_users from stats$dates;

'USERSCONNECTEDAT' TO_CHAR(END_TIME,' '
END_USERS
------------------- ------------------ -
------------
Users connected at 18-apr-07 15:30:00 :
106

SQL> select 'avg # of connections: ',((start_users+end_users)/2) from
stats$dates;

'AVG#OFCONNECTIONS:' ((START_USER
----------------------
------------
avg # of connections:
102.5

SQL>
SQL> select n1.name "Statistic",
2 n1.change "Total",
3 round(n1.change/trans.change,2) "Per Transaction",
4 round(n1.change/((start_users + end_users)/2),2) "Per
Logon",
5 round(n1.change/(to_number(to_char(end_time,
'J'))*60*60*24 -
6 to_number(to_char(start_time, 'J'))*60*60*24 +
7 to_number(to_char(end_time, 'SSSSS')) -
8 to_number(to_char(start_time, 'SSSSS')))
9 , 2) "Per Second"
10 from
11 stats$stats n1,
12 stats$stats trans,
13 stats$dates
14 where
15 trans.name='user commits'
16 and n1.change != 0
17 order by n1.name;

Statistic Total Per Transact Per Logon
Per Second
--------------------------- ------------ ------------ ------------
------------
CR blocks created 489 1.01
4.77 .27
DBWR buffers scanned 837286 1726.36
8168.64 465.68
DBWR checkpoint buffers wri 10 .02 .
1 .01
DBWR checkpoints 1 0 .
01 0
DBWR free buffers found 833915 1719.41
8135.76 463.8
DBWR lru scans 1682 3.47
16.41 .94
DBWR make free requests 2061 4.25
20.11 1.15
DBWR summed scan depth 837286 1726.36
8168.64 465.68
DBWR transaction table writ 86 .18 .
84 .05
DBWR undo block writes 1107 2.28
10.8 .62
SQL*Net roundtrips to/from 240230 495.32
2343.71 133.61
SQL*Net roundtrips to/from 161 .33
1.57 .09
active txn count during cle 1193 2.46
11.64 .66
background checkpoints comp 1 0 .
01 0
background checkpoints star 1 0 .
01 0
background timeouts 3992 8.23
38.95 2.22
buffer is not pinned count 96882351 199757.42
945193.67 53883.4
buffer is pinned count 209263905 431471.97 2041599.07
116387.04
bytes received via SQL*Net 38590790 79568.64 376495.51
21463.18
bytes received via SQL*Net 28719 59.21
280.19 15.97
bytes sent via SQL*Net to c 122136830 251828.52 1191578.83
67929.27
bytes sent via SQL*Net to d 13584 28.01
132.53 7.56
calls to get snapshot scn: 4293150 8851.86
41884.39 2387.74
calls to kcmgas 5178 10.68
50.52 2.88
calls to kcmgcs 625 1.29
6.1 .35
cleanout - number of ktugct 1529 3.15
14.92 .85
cleanouts and rollbacks - c 368 .76
3.59 .2
cleanouts only - consistent 159 .33
1.55 .09
cluster key scan block gets 204645 421.95
1996.54 113.82
cluster key scans 186011 383.53
1814.74 103.45
commit cleanout failures: b 556 1.15
5.42 .31
commit cleanouts 8022 16.54
78.26 4.46
commit cleanouts successful 7466 15.39
72.84 4.15
commit txn count during cle 1385 2.86
13.51 .77
consistent changes 8930 18.41
87.12 4.97
consistent gets 106281336 219136.78 1036891.08
59110.87
consistent gets - examinati 37888575 78120.77 369644.63
21072.62
current blocks converted fo 3 .01 .
03 0
cursor authentications 3841 7.92
37.47 2.14
data blocks consistent read 978 2.02
9.54 .54
db block changes 92528 190.78
902.71 51.46
db block gets 97942 201.94
955.53 54.47
deferred (CURRENT) block cl 5224 10.77
50.97 2.91
dirty buffers inspected 2355 4.86
22.98 1.31
enqueue conversions 80 .16 .
78 .04
enqueue releases 29350 60.52
286.34 16.32
enqueue requests 29319 60.45
286.04 16.31
enqueue timeouts 6 .01 .
06 0
execute count 760148 1567.32
7416.08 422.77
free buffer inspected 6398 13.19
62.42 3.56
free buffer requested 4870521 10042.31
47517.28 2708.85
hot buffers moved to head o 602322 1241.9
5876.31 335
immediate (CR) block cleano 527 1.09
5.14 .29
immediate (CURRENT) block c 719 1.48
7.01 .4
index fast full scans (full 4 .01 .
04 0
index fetch by key 22111694 45591.12 215723.84
12297.94
index scans kdiixs1 14908898 30740
145452.66 8291.93
leaf node 90-10 splits 19 .04 .
19 .01
leaf node splits 124 .26
1.21 .07
logons cumulative 156 .32
1.52 .09
logons current 7 .01 .
07 0
messages received 4153 8.56
40.52 2.31
messages sent 4153 8.56
40.52 2.31
no work - consistent read g 50271488 103652.55 490453.54
27959.67
opened cursors cumulative 73980 152.54
721.76 41.15
opened cursors current 169 .35
1.65 .09
parse count (failures) 147 .3
1.43 .08
parse count (hard) 9984 20.59
97.4 5.55
parse count (total) 145995 301.02
1424.34 81.2
physical reads 4873242 10047.92
47543.82 2710.37
physical reads direct 5328 10.99
51.98 2.96
physical writes 18709 38.58
182.53 10.41
physical writes direct 13164 27.14
128.43 7.32
physical writes non checkpo 18703 38.56
182.47 10.4
pinned buffers inspected 3785 7.8
36.93 2.11
prefetched blocks 958405 1976.09
9350.29 533.04
prefetched blocks aged out 179 .37
1.75 .1
process last non-idle time 1797 3.71
17.53 1
recursive calls 1443176 2975.62
14079.77 802.66
recursive cpu usage 50082 103.26
488.6 27.85
redo blocks written 28594 58.96
278.97 15.9
redo buffer allocation retr 2 0 .
02 0
redo entries 45818 94.47
447 25.48
redo log space requests 2 0 .
02 0
redo size 13883700 28626.19
135450.73 7721.75
redo synch writes 611 1.26
5.96 .34
redo wastage 302472 623.65
2950.95 168.23
redo writes 1182 2.44
11.53 .66
rollback changes - undo rec 295 .61
2.88 .16
rollbacks only - consistent 124 .26
1.21 .07
rows fetched via callback 21750324 44846.03 212198.28
12096.95
session logical reads 106379278 219338.72 1037846.61
59165.34
session pga memory 13097796 27005.76
127783.38 7284.65
session pga memory max 19168416 39522.51 187008.94
10660.97
session uga memory 8597944504 17727720.63 83882385.4
4781949.11
session uga memory max 62722392 129324.52 611925.78
34884.53
shared hash latch upgrades 14783761 30481.98
144231.81 8222.34
shared hash latch upgrades 2071 4.27
20.2 1.15
sorts (disk) 11 .02 .
11 .01
sorts (memory) 1614111 3328.06
15747.42 897.73
sorts (rows) 3434505 7081.45
33507.37 1910.18
summed dirty queue length 26 .05 .
25 .01
switch current to new buffe 614 1.27
5.99 .34
table fetch by rowid 133067911 274366.83 1298223.52
74008.85
table fetch continued row 593492 1223.69
5790.17 330.08
table scan blocks gotten 2769428 5710.16
27018.81 1540.28
table scan rows gotten 159581878 329034.8 1556896.37
88755.22
table scans (long tables) 520 1.07
5.07 .29
table scans (short tables) 1214716 2504.57
11850.89 675.59
transaction rollbacks 4 .01 .
04 0
user calls 249521 514.48
2434.35 138.78
user commits 485 1
4.73 .27
user rollbacks 53 .11 .
52 .03
workarea executions - onepa 12 .02 .
12 .01
workarea executions - optim 46135 95.12
450.1 25.66

115 rows selected.

SQL>
SQL> column "Event Name" format a32 trunc;
SQL> set numwidth 13;
SQL> Rem System wide wait events for non-background processes (PMON,
SQL> Rem SMON, etc). Times are in hundreths of seconds. Each one of
SQL> Rem these is a context switch which costs CPU time. By looking
at
SQL> Rem the Total Time you can often determine what is the bottleneck
SQL> Rem that processes are waiting for. This shows the total time
spent
SQL> Rem waiting for a specific event and the average time per wait on
SQL> Rem that event.
SQL> select n1.event "Event Name",
2 n1.event_count "Count",
3 n1.time_waited "Total Time",
4 round(n1.time_waited/n1.event_count, 2) "Avg Time"
5 from stats$event n1
6 where n1.event_count > 0
7 order by n1.time_waited desc;

Event Name Count Total Time Avg
Time
-------------------------------- ------------- -------------
-------------
SQL*Net message from client 240834 8773750
36.43
db file sequential read 3742459
216409 .06
rdbms ipc message 362 179038
494.58
queue messages 179 178797
998.87
virtual circuit status 60 178452
2974.2
dispatcher timer 30 175414
5847.13
jobq slave wait 127 37982
299.07
db file scattered read 166893
13092 .08
SQL*Net break/reset to client 881 12105
13.74
buffer busy waits 41906
4122 .1
SQL*Net more data to client 21316
122 .01
latch free 1092
65 .06
log file sync 585
34 .06
SQL*Net message to client 240967
29 0
direct path read 468
18 .04
SQL*Net more data from client 1685
15 .01
library cache pin 21
15 .71
SQL*Net message from dblink 161
14 .09
control file sequential read 69
10 .14
log file switch completion 2
6 3
db file parallel read 4 5
1.25
single-task message 1
3 3
direct path write 44
1 .02
rdbms ipc reply 2
1 .5
SQL*Net message to dblink 161
0 0
local write wait 1
0 0
library cache load lock 2
0 0

27 rows selected.

SQL>
SQL>
SQL> Rem System wide wait events for background processes (PMON, SMON,
etc)
SQL> select n1.event "Event Name",
2 n1.event_count "Count",
3 n1.time_waited "Total Time",
4 round(n1.time_waited/n1.event_count, 2) "Avg Time"
5 from stats$bck_event n1
6 where n1.event_count > 0
7 order by n1.time_waited desc;

Event Name Count Total Time Avg
Time
-------------------------------- ------------- -------------
-------------
rdbms ipc message 6391 1486474
232.59
pmon timer 614 179445
292.26
smon timer 105 179082
1705.54
db file parallel write 1331
114 .09
log file sequential read 103
70 .68
control file sequential read 463
67 .14
control file parallel write 616
63 .1
db file sequential read 279
60 .22
log file parallel write 1182
7 .01
direct path read 38
5 .13
LGWR wait for redo copy 57
0 0
direct path write 38
0 0
log file single write 2
0 0
rdbms ipc reply 13
0 0
latch free 4
0 0
async disk IO 49
0 0

16 rows selected.

SQL>
SQL>
SQL> column latch_name format a18 trunc;
SQL> set numwidth 11;
SQL> Rem Latch statistics. Latch contention will show up as a large
value for
SQL> Rem the 'latch free' event in the wait events above.
SQL> Rem Sleeps should be low. The hit_ratio should be high.
SQL> select name latch_name, gets, misses,
2 round((gets-misses)/decode(gets,0,1,gets),3)
3 hit_ratio,
4 sleeps,
5 round(sleeps/decode(misses,0,1,misses),3) "SLEEPS/MISS"
6 from stats$latches
7 where gets != 0
8 order by name;

LATCH_NAME GETS MISSES HIT_RATIO SLEEPS
SLEEPS/MISS
------------------ ----------- ----------- ----------- -----------
-----------
Consistent RBA 1183 0 1
0 0
FAL request queue 36 0 1
0 0
FIB s.o chain latc 10 0 1
0 0
FOB s.o list latch 1496 0 1
0 0
SQL memory manager 42177 0 1
0 0
active checkpoint 5413 2 1
0 0
alert log latch 4 0 1
0 0
archive control 63 0 1
0 0
archive process la 52 0 1
0 0
cache buffer handl 4407340 225 1
0 0
cache buffers chai 180254493 31059 1
85 .003
cache buffers lru 15557 12 .999
0 0
channel handle poo 616 0 1
0 0
channel operations 2758 3 .999
0 0
checkpoint queue l 441402 0 1
0 0
child cursor hash 102978 5 1
0 0
commit callback al 120 0 1
0 0
constraint object 28 0 1
0 0
dictionary lookup 1 0 1
0 0
dml lock allocatio 11444 0 1
0 0
dummy allocation 304 0 1
0 0
enqueue hash chain 58725 1 1
0 0
enqueues 51823 5 1
0 0
event group latch 154 0 1
0 0
global tx hash map 338 0 1
0 0
hash table column 348 0 1
0 0
job_queue_processe 32 0 1
0 0
ktm global data 109 0 1
0 0
lgwr LWN SCN 1409 0 1
0 0
library cache 4876926 3631 .999
48 .013
library cache load 17092 0 1
0 0
library cache pin 2833769 581 1
0 0
library cache pin 1133176 17 1
0 0
list of block allo 260 0 1
0 0
loader state objec 112 0 1
0 0
longop free list p 5 0 1
0 0
message pool opera 324 0 1
0 0
messages 19318 2 1
0 0
mostly latch-free 1417 8 .994
0 0
multiblock read ob 386610 92 1
0 0
ncodef allocation 31 0 1
0 0
object stats modif 19 0 1
0 0
post/wait queue 882 0 1
0 0
process allocation 300 0 1
0 0
process group crea 300 0 1
0 0
redo allocation 48388 3 1
0 0
redo writing 10154 0 1
0 0
row cache enqueue 1637776 358 1
0 0
row cache objects 1663298 294 1
0 0
sequence cache 6560 0 1
0 0
session allocation 780275 105 1
0 0
session idle bit 511589 1 1
0 0
session switching 232 0 1
0 0
session timer 614 0 1
0 0
shared pool 2543027 5586 .998
963 .172
simulator hash lat 5805854 2 1
0 0
simulator lru latc 247343 8 1
0 0
sort extent pool 1217 0 1
0 0
temporary table st 3 0 1
0 0
transaction alloca 369 0 1
0 0
transaction branch 224 0 1
0 0
undo global data 19239 0 1
0 0
user lock 1014 0 1
0 0

63 rows selected.

SQL>
SQL> set numwidth 16
SQL> Rem Statistics on no_wait gets of latches. A no_wait get does
not
SQL> Rem wait for the latch to become free, it immediately times out.
SQL> select name latch_name,
2 immed_gets nowait_gets,
3 immed_miss nowait_misses,
4 round((immed_gets/(immed_gets+immed_miss)), 3)
5 nowait_hit_ratio
6 from stats$latches
7 where immed_gets + immed_miss != 0
8 order by name;

LATCH_NAME NOWAIT_GETS NOWAIT_MISSES
NOWAIT_HIT_RATIO
------------------ ---------------- ----------------
----------------
SQL memory manager 599 0
1
cache buffers chai 5900251 332
1
cache buffers lru 6124904 3900 .
999
checkpoint queue l 5125 0
1
hash table column 753064 5
1
job workq parent l 4 1 .
8
library cache 47360 249 .
995
longop free list p 5 0
1
post/wait queue 587 0
1
process allocation 154 0
1
redo copy 45818 57 .
999
row cache objects 4618 0
1
session idle bit 3 0
1
simulator lru latc 317307 23
1

14 rows selected.

SQL>
SQL> Rem Buffer busy wait statistics. If the value for 'buffer busy
wait' in
SQL> Rem the wait event statistics is high, then this table will
identify
SQL> Rem which class of blocks is having high contention. If there
are high
SQL> Rem 'undo header' waits then add more rollback segments. If
there are
SQL> Rem high 'segment header' waits then adding freelists might help.
Check
SQL> Rem v$session_wait to get the addresses of the actual blocks
having
SQL> Rem contention.
SQL> select * from stats$waitstat
2 where count != 0
3 order by count desc;

CLASS COUNT
TIME
------------------ ----------------
----------------
data block 41901
0
segment header 2
0
1st level bmb 1
0
undo header 1
0
2nd level bmb 1
0

SQL>
SQL>
SQL> set lines 159;
SQL> set numwidth 19;
SQL> Rem Waits_for_trans_tbl high implies you should add rollback
segments.
SQL> select * from stats$roll;

UNDO_SEGMENT TRANS_TBL_GETS TRANS_TBL_WAITS
UNDO_BYTES_WRITTEN SEGMENT_SIZE_BYTES XACTS
SHRINKS WRAPS
------------------- ------------------- -------------------
------------------- ------------------- -------------------
------------------- -------------------
0 106
0 0 385024
0 0 0
1 2786
0 1443900 10608640
0 1 3
2 873
0 348998 37937152
-1 0 0
3 1779
0 1529048 8511488
-1 1 2
4 978
0 432266 8511488
0 0 1
5 215
0 27596 10608640
-1 0 0
6 929
0 139108 6479872
1 0 0
7 477
0 269772 9560064
1 0 0
8 999
0 507498 5365760
0 0 0
9 804
0 90410 4317184
-1 0 0
10 212
0 17184 58843136
-1 0 0
11 890
0 535460 2220032
0 0 1

12 rows selected.

SQL> set lines 79;
SQL>
SQL> column name format a39 trunc;
SQL> column value format a39 trunc;
SQL> Rem The init.ora parameters currently in effect:
SQL> select name, value from v$parameter where isdefault = 'FALSE'
2 order by name;

NAME
VALUE
---------------------------------------
---------------------------------------
background_dump_dest D:\oracle\admin\PROD
\bdump
compatible
9.2.0.0.0
control_files G:\ORACLE\ORADATA\PROD
\CONTROL01.CTL, G
core_dump_dest D:\oracle\admin\PROD
\cdump
db_block_size
8192
db_cache_size
209715200
db_domain
NORCALMUTUAL.COM
db_file_multiblock_read_count
8
db_files
254
db_name
PROD
db_writer_processes
4
dispatchers (PROTOCOL=TCP)
(SERVICE=PRODXDB)
dml_locks
500
enqueue_resources
129000
hash_join_enabled
FALSE
ifile D:\oracle\admin\PROD\pfile
\init.ora
instance_name
PROD
job_queue_processes
10
large_pool_size
100663296
log_archive_dest F:\ORACLE\oradata\PROD
\arch
log_archive_start
TRUE
log_buffer
12288000
log_checkpoint_interval
10000
max_dump_file_size
UNLIMITED
nls_date_format DD-MON-
YYYY
open_cursors
310
optimizer_index_caching
90
optimizer_index_cost_adj
20
optimizer_mode
RULE
pga_aggregate_target
50000000
processes
200
query_rewrite_enabled
TRUE
query_rewrite_integrity
ENFORCED
remote_login_passwordfile
EXCLUSIVE
resource_limit
TRUE
sga_max_size
1665379432
shared_pool_size
209715200
sort_area_size
5120000
undo_management
AUTO
undo_retention
10800
undo_tablespace
RBS
user_dump_dest D:\oracle\admin\PROD
\udump
utl_file_dir D:\ORACLE\admin\PROD
\error_log

43 rows selected.

SQL>
SQL> column name format a15 trunc;
SQL> column scan_reqs heading 'SCAN_REQ';
SQL> column scan_miss heading 'SCAN_MIS';
SQL> column cur_usage heading 'CUR_USAG';
SQL> set numwidth 8;
SQL> Rem get_miss and scan_miss should be very low compared to the
requests.
SQL> Rem cur_usage is the number of entries in the cache that are
being used.
SQL> select * from stats$dc
2 where get_reqs != 0 or scan_reqs != 0 or mod_reqs != 0;

NAME GET_REQS GET_MISS SCAN_REQ SCAN_MIS MOD_REQS COUNT
CUR_USAG
--------------- -------- -------- -------- -------- -------- --------
--------
dc_tablespaces 800 1 0 0 0
6 6
dc_segments 118105 323 0 0 1
1556 1556
dc_rollback_seg 4330 0 0 0 0
22 22
dc_users 264160 18 0 0 3
77 77
dc_user_grants 2942 10 0 0 0
69 69
dc_objects 83952 3524 0 0 1
6030 6030
dc_usernames 65463 13 0 0 0
127 127
dc_object_ids 244021 203 0 0 0
1408 1408
dc_sequences 3007 4 0 0 3007
8 8
dc_profiles 277 0 0 0 0
3 3
dc_database_lin 176 2 0 0 0
2 2
dc_histogram_de 39886 511 0 0 0
2210 2210
dc_global_oids 83 3 0 0 0
20 20
dc_table_scns 814 10 0 0 10
70 70

14 rows selected.

SQL>
SQL>
SQL> set lines 157;
SQL> column table_space format a80 trunc;
SQL> set numwidth 10;
SQL> Rem Sum IO operations over tablespaces.
SQL> select
2 table_space||' '
3 table_space,
4 sum(phys_reads) reads, sum(phys_blks_rd) blks_read,
5 sum(phys_rd_time) read_time, sum(phys_writes) writes,
6 sum(phys_blks_wr) blks_wrt, sum(phys_wrt_tim) write_time,
7 sum(megabytes_size) megabytes
8 from stats$files
9 group by table_space
10 order by table_space;

TABLE_SPACE
READS BLKS_READ READ_TIME WRITES BLKS_WRT WRITE_TIME
MEGABYTES
--------------------------------------------------------------------------------
---------- ---------- ---------- ---------- ---------- ----------
----------
BO
788 4482 156 13 13 0 1447
BO_DATA
1 1 0 1 1 0 1753
DEVELOP_DATA
2 2 0 2 2 0 6292
IMPROM_DATA
1 1 0 1 1 0 52
IMPROM_INDEX
1 1 0 1 1 0 10
MAT_INDEX
1 1 0 1 1 0 105
MAT_VIEW
2 2 0 2 2 0 755
MVLOGS
2817 10781 560 506 506 0 1049
NORCAL_DATA
1 1 0 1 1 0 4194
NORCAL_INDEX
1 1 0 1 1 0 1819
OASIS_DATA
3164277 4076220 178398 1186 1186 0
46658
OASIS_INDEX
709663 709911 43584 2122 2122 0
18164
PLMS_DATA
15 15 2 1 1 0 4060
PLMS_INDEX
155 155 46 1 1 0 259
RBS
628 628 86 1203 1203 0 5177
STAGING
1 1 0 1 1 0 10
SYSTEM
31279 65793 7530 169 169 0
644
TOOLS
1 1 0 1 1 0 6
USERS
1 1 0 1 1 0 1

19 rows selected.

SQL>
SQL>
SQL> set lines 196;
SQL> column table_space format a48 trunc;
SQL> column file_name format a48 trunc;
SQL> set numwidth 10;
SQL> Rem I/O should be spread evenly accross drives. A big difference
between
SQL> Rem phys_reads and phys_blks_rd implies table scans are going on.
SQL> select table_space, file_name,
2 phys_reads reads, phys_blks_rd blks_read, phys_rd_time
read_time,
3 phys_writes writes, phys_blks_wr blks_wrt, phys_wrt_tim
write_time,
4 megabytes_size megabytes,
5 round(decode(phys_blks_rd,0,0,phys_rd_time/phys_blks_rd),2)
avg_rt,
6 round(decode(phys_reads,0,0,phys_blks_rd/phys_reads),2)
"blocks/rd"
7 from stats$files order by table_space, file_name;

TABLE_SPACE
FILE_NAME READS
BLKS_READ READ_TIME WRITES BLKS_WRT WRITE_TIME MEGABYTES
AVG_RT blocks/rd
------------------------------------------------
------------------------------------------------ ---------- ----------
---------- ---------- ---------- ---------- ---------- ----------
----------
BO G:\ORACLE\ORADATA\PROD
\BO.DBF 788 4482 156
13 13 0 1447 .03 5.69
BO_DATA G:\ORACLE\ORADATA\PROD
\BO_DATA.DBF 1 1 0
1 1 0 1753 0 1
DEVELOP_DATA G:\ORACLE\ORADATA\PROD
\DEVELOP_DATA1.DBF 1 1 0
1 1 0 3146 0 1
DEVELOP_DATA G:\ORACLE\ORADATA\PROD
\DEVELOP_DATA2.DBF 1 1 0
1 1 0 3146 0 1
IMPROM_DATA G:\ORACLE\ORADATA\PROD
\IMPROM_DATA.DBF 1 1 0
1 1 0 52 0 1
IMPROM_INDEX G:\ORACLE\ORADATA\PROD
\IMPROM_INDEX.DBF 1 1 0
1 1 0 10 0 1
MAT_INDEX G:\ORACLE\ORADATA\PROD
\MAT_INDEX01.DBF 1 1 0
1 1 0 105 0 1
MAT_VIEW G:\ORACLE\ORADATA\PROD
\MAT_VIEW01.DBF 1 1 0
1 1 0 372 0 1
MAT_VIEW G:\ORACLE\ORADATA\PROD
\MAT_VIEW02.DBF 1 1 0
1 1 0 383 0 1
MVLOGS G:\ORACLE\ORADATA\PROD
\MVLOGS01.DBF 2817 10781 560
506 506 0 1049 .05 3.83
NORCAL_DATA G:\ORACLE\ORADATA\PROD
\NORCAL_DATA.DBF 1 1 0
1 1 0 4194 0 1
NORCAL_INDEX G:\ORACLE\ORADATA\PROD
\NORCAL_INDEX.DBF 1 1 0
1 1 0 1819 0 1
OASIS_DATA G:\ORACLE\ORADATA\PROD
\OASIS_DATA.DBF 228054 300634 13278
59 59 0 4194 .04 1.32
OASIS_DATA G:\ORACLE\ORADATA\PROD
\OASIS_DATA02.DBF 386824 463948 18623
81 81 0 4194 .04 1.2
OASIS_DATA G:\ORACLE\ORADATA\PROD
\OASIS_DATA03.DBF 369424 443541 18170
227 227 0 4194 .04 1.2
OASIS_DATA G:\ORACLE\ORADATA\PROD
\OASIS_DATA04.DBF 357685 425626 17532
107 107 0 4194 .04 1.19
OASIS_DATA G:\ORACLE\ORADATA\PROD
\OASIS_DATA05.DBF 277522 346085 15384
56 56 0 4194 .04 1.25
OASIS_DATA G:\ORACLE\ORADATA\PROD
\OASIS_DATA06.DBF 293545 361484 16348
67 67 0 4194 .05 1.23
OASIS_DATA G:\ORACLE\ORADATA\PROD
\OASIS_DATA07.DBF 342057 461038 21361
148 148 0 4194 .05 1.35
OASIS_DATA G:\ORACLE\ORADATA\PROD
\OASIS_DATA08.DBF 283007 389248 15957
104 104 0 4194 .04 1.38
OASIS_DATA G:\ORACLE\ORADATA\PROD
\OASIS_DATA09.DBF 290337 375326 16232
221 221 0 4194 .04 1.29
OASIS_DATA G:\ORACLE\ORADATA\PROD
\OASIS_DATA10.DBF 171062 262770 13083
62 62 0 4194 .05 1.54
OASIS_DATA G:\ORACLE\ORADATA\PROD
\OASIS_DATA11.DBF 164710 246246 12406
34 34 0 4194 .05 1.5
OASIS_DATA G:\ORACLE\ORADATA\PROD
\OASIS_DATA12.DBF 50 274 24
20 20 0 524 .09 5.48
OASIS_INDEX G:\ORACLE\ORADATA\PROD
\OASIS_INDEX.DBF 112369 112369 7337
256 256 0 3146 .07 1
OASIS_INDEX G:\ORACLE\ORADATA\PROD
\OASIS_INDEX02.DBF 103923 103923 6208
255 255 0 3146 .06 1
OASIS_INDEX G:\ORACLE\ORADATA\PROD
\OASIS_INDEX03.DBF 92139 92139 6024
403 403 0 3146 .07 1
OASIS_INDEX G:\ORACLE\ORADATA\PROD
\OASIS_INDEX04.DBF 155117 155117 8162
357 357 0 3146 .05 1
OASIS_INDEX G:\ORACLE\ORADATA\PROD
\OASIS_INDEX05.DBF 99319 99319 5555
325 325 0 3146 .06 1
OASIS_INDEX G:\ORACLE\ORADATA\PROD
\OASIS_INDEX06.DBF 146796 147044 10298
526 526 0 2434 .07 1
PLMS_DATA G:\ORACLE\ORADATA\PROD
\PLMS_DATA01.DBF 15 15 2
1 1 0 4060 .13 1
PLMS_INDEX G:\ORACLE\ORADATA\PROD
\PLMS_INDEX01.DBF 155 155 46
1 1 0 259 .3 1
RBS G:\ORACLE\ORADATA\PROD
\RBS01.ORA 447 447 63
878 878 0 4128 .14 1
RBS G:\ORACLE\ORADATA\PROD
\RBS02.ORA 181 181 23
325 325 0 1049 .13 1
STAGING G:\ORACLE\ORADATA\PROD
\STAGING.DBF 1 1 0
1 1 0 10 0 1
SYSTEM G:\ORACLE\ORADATA\PROD
\SYSTEM01.DBF 31279 65793 7530
169 169 0 644 .11 2.1
TOOLS G:\ORACLE\ORADATA\PROD
\TOOLS01.DBF 1 1 0
1 1 0 6 0 1
USERS G:\ORACLE\ORADATA\PROD
\USERS01.DBF 1 1 0
1 1 0 1 0 1

38 rows selected.

SQL> set lines 79;
SQL>
SQL> column start_time format a25;
SQL> column end_time format a25;
SQL> Rem The times that bstat and estat were run.
SQL> select to_char(start_time, 'dd-mon-yy hh24:mi:ss') start_time,
2 to_char(end_time, 'dd-mon-yy hh24:mi:ss') end_time
3 from stats$dates;

START_TIME
END_TIME
-------------------------
-------------------------
18-apr-07 15:00:02 18-apr-07
15:30:00

SQL>
SQL> column banner format a75 trunc;
SQL> Rem Versions
SQL> select * from v$version;

BANNER
---------------------------------------------------------------------------
Oracle9i Release 9.2.0.7.0 -
Production
PL/SQL Release 9.2.0.7.0 -
Production
CORE 9.2.0.7.0
Production
TNS for 32-bit Windows: Version 9.2.0.7.0 -
Production
NLSRTL Version 9.2.0.7.0 -
Production

SQL>
SQL>
SQL> spool off;



On Jun 7, 2:51 pm, joel garry wrote:
> On Jun 7, 12:49 pm, beth.sto...@gmail.com wrote:
>
>
>
>
>
> > On May 25, 7:42 am, EscVector wrote:
>
> > > On May 24, 5:56 pm, beth.sto...@gmail.com wrote:
>
> > > > My apologies if this subject has been discussed. I searched the
> > > > groups, and I couldn't find a good thread.
>
> > > > We've been having performance problems with our Client/Server
> > > > application for months. Users contantly complain of slow response
> > > > times to their queries.
>
> > > > Here's the environment:
>
> > > > Oracle 9i on Windows. Poweredge 6850. 4, 3 GHz Quad Core
> > > > Processors. 8 GB RAM. 9i databses are stored on an EMC Clarrion
> > > > CX500 LUN -- RAID 10. 6, 15K 146GB dedicated disks. Oracle logs are
> > > > stored on a separate RAID 10 LUN on dedicated disks. 1 GBE switched
> > > > backend. Users connect using FastEthernet. XP clients. All disks on
> > > > the SAN are fibre channel.
>
> > > > CPU utilization is fine. RAM utilization is fine. Throughput on the
> > > > NIC is fine -- maxes out at 50 Mbps for a short while when users first
> > > > log in in the morning. Averages are 20 Mbps.
>
> > > > Using perfmon in Windows, If I look at Disk Reads/Sec and Disk Writes/
> > > > sec., here's what I see for averages:
>
> > > > Disk Reads/sec = 2200 Avg
> > > > Disk Writes/sec = 10 Avg
>
> > > > Our reads/sec seem EXTREMELY high for only 80 users.
>
> > > > Can someone help me understand if this is truly a disk bottleneck?
>
> > > > Thanks in advance!
>
> > > Don't use the windows tools to check on SAN performance. Talk with
> > > the storage admins. They will have a much better perspective on both
> > > throughput and utilization. The perfmon tool might indicate an issue,
> > > but I doubt it will tell much. I like the statspack suggestion if
> > > done accurately and snap are scoped correctly.
>
> > > Do you have virus software running? This will typically bump up I/O
> > > reads in perfmon.- Hide quoted text -
>
> > > - Show quoted text -
>
> > Thanks for all of the information and suggestions.
>
> > I did some comparison of Windows tools (Perfmon) and EMC tools
> > (Navisphere analyzer). Perfmon was actually reporting the IO
> > correctly. Average IO reads are 2000, Average Write IO is @ 10.
> > There are some interesting metrics that I don't understand from Nav.
> > Analyzer: The storage processors are showing peaks close to 100% at
> > certain times of day, but the disks are not at 100%. I'm not sure
> > what that means. LUN % utilization is the same, so it looks like the
> > Storage processors are responsible for the high % utilization.
>
> > STATSPACK analysis shows something similar to Perfmon and Nav.
> > Analyzer: Physical reads are 2552, physical writes are 52. There's a
> > ton of information in the statspack report, so I'm not sure what else
> > to look at.
>
> > There is anti-virus software on this machine, but I don't think it
> > accounts for a significant part of the 2000+ IOPs per second. We use
> > the same product on all of our servers. IO is not a problem on any
> > other server. On this machine, there are peaks of 10,000 IOPs. This
> > seems abnormally high.
>
> > We're a very small shop, so I'm the storage person. This is a new
> > technology for us, so I'm in a learning curve here.
>
> > There don't appear to be many good SAN related groups that are active,
> > so I'm hoping someone here has experience with SANs and ORACLE to help
> > out.
>
> > EMC support is NOT what we were expecting. For the amount we pay for
> > support, the response is very very disappointing. I've had a ticket
> > open on this for MONTHS. Literally.
>
> > I guess the bottom line here is that I need to prove to my boss that
> > this is a disk bottleneck and that giving the database more spindles
> > will help. Also, I'd like to understand why IO reads are so high for
> > Oracle.
>
> > Any information would be appreciated.- Hide quoted text -
>
> > - Show quoted text -
>
> On top of bdbafh's suggestions, looks like you should get Cary
> Milsap's book and follow it through to determine the exact set of
> problems.
>
> Besides that, you might want to post init.ora parameters that are not
> default, 9 databases (assuming you are using the Oracle definition of
> "database," it's different than the one many MS-oriented folk use - do
> the terms ORACLE_HOME and ORACLE_SID mean anything to you?) could be a
> major part of the problem. As Paul mentioned, the cache buffers may
> be set too low, causing many reads to load them, where if there were
> more, the buffers would already be loaded. If you just had one db
> rather than nine, you could easily increase the buffer size. But they
> are additive across databases. There is an Oracle product called OEM
> (I've forgotten where it is, perhaps on a supplemental cd) that has
> some decent visualizations of what is happening, and what will likely
> happen with certain changes - but only per db. The data comes from
> system tables named something like %ADVISOR%, which you can learn
> about in the Performance manual, learning much about what's going on
> while you are at it.
>
> Besides that, as you will no doubt discover as you work through these
> things, most performance problems come from the application code.
>
> It's only after all that you can prove that more spindles will help.
> Of course, proof to a boss may not need to be so rigorous, and many
> people have no problem saying more spindles=better performance. It's
> just a bit embarrassing after spending all that money to discover the
> bottleneck was elsewhere, or some simple code change or adding an
> index could have fixed 90% of the problem. The statspack may tell you
> that, or it may mask the problem, depending... but you won't know by
> not running it. There should be some "top" information in there you
> may want to post. It's possible that you would be doing more writing
> if you weren't doing so much reading, the wait info may be
> informative. Some people in this group are willing to go through an
> entire statspack if you post it.
>
> I'm guessing storage processor peaking while disks are not may
> indicate loads satisfied by storage cache, if they were satisfied by
> Oracle's cache instead you may see some improvement. Google about for
> papers about reducing logical I/O, too.
>
> jg
> --
> @home.com is bogus.
> "There is no try in production." - bdbafh- Hide quoted text -
>
> - Show quoted text -

[Database Administrator]

beth.stover@gmail.com wrote:
> Thanks for the support and the links. I'll take a look at the links
> and the book.
>
> Maybe I'm asking the wrong questions.
>
> Maybe a better question would be:
>
> Can 6 15k fibre channel drives in a RAID 10 configuration support 2000
> IO's per second?
>
> I've read that 15k drives can perform 180 IO's per second. And that's
> with no RAID penalty. If this is the case, then my 6 drives should
> only be able to handle 6 x 180 IOps = 1080 total capacity. If this is
> true, then my average IO's per second of 2000+ is WAY over the
> capacity of the drives.
>
> Questions:
>
> 1. Is the scenario above a reasonable approach to solving this
> problem?
> 2. Can 15k drives really only handle 180 IO's per second?
> 3. What happens to an Oracle environment (transaction oriented) when
> drive IO limits are reached. Are there other metrics I can look at to
> confirm this is a disk bottle neck. Is there something in the
> statsapack that can show me this is a disk bottleneck?
> 4. Does 2000 IOps seem really high?
>
>
>
>
>
> On Jun 7, 1:44 pm, bdbafh wrote:
>> On Jun 7, 3:49 pm, beth.sto...@gmail.com wrote:
>>
>>
>>
>>
>>
>>> On May 25, 7:42 am, EscVector wrote:
>>>> On May 24, 5:56 pm, beth.sto...@gmail.com wrote:
>>>>> My apologies if this subject has been discussed. I searched the
>>>>> groups, and I couldn't find a good thread.
>>>>> We've been having performance problems with our Client/Server
>>>>> application for months. Users contantly complain of slow response
>>>>> times to their queries.
>>>>> Here's the environment:
>>>>> Oracle 9i on Windows. Poweredge 6850. 4, 3 GHz Quad Core
>>>>> Processors. 8 GB RAM. 9i databses are stored on an EMC Clarrion
>>>>> CX500 LUN -- RAID 10. 6, 15K 146GB dedicated disks. Oracle logs are
>>>>> stored on a separate RAID 10 LUN on dedicated disks. 1 GBE switched
>>>>> backend. Users connect using FastEthernet. XP clients. All disks on
>>>>> the SAN are fibre channel.
>>>>> CPU utilization is fine. RAM utilization is fine. Throughput on the
>>>>> NIC is fine -- maxes out at 50 Mbps for a short while when users first
>>>>> log in in the morning. Averages are 20 Mbps.
>>>>> Using perfmon in Windows, If I look at Disk Reads/Sec and Disk Writes/
>>>>> sec., here's what I see for averages:
>>>>> Disk Reads/sec = 2200 Avg
>>>>> Disk Writes/sec = 10 Avg
>>>>> Our reads/sec seem EXTREMELY high for only 80 users.
>>>>> Can someone help me understand if this is truly a disk bottleneck?
>>>>> Thanks in advance!
>>>> Don't use the windows tools to check on SAN performance. Talk with
>>>> the storage admins. They will have a much better perspective on both
>>>> throughput and utilization. The perfmon tool might indicate an issue,
>>>> but I doubt it will tell much. I like the statspack suggestion if
>>>> done accurately and snap are scoped correctly.
>>>> Do you have virus software running? This will typically bump up I/O
>>>> reads in perfmon.- Hide quoted text -
>>>> - Show quoted text -
>>> Thanks for all of the information and suggestions.
>>> I did some comparison of Windows tools (Perfmon) and EMC tools
>>> (Navisphere analyzer). Perfmon was actually reporting the IO
>>> correctly. Average IO reads are 2000, Average Write IO is @ 10.
>>> There are some interesting metrics that I don't understand from Nav.
>>> Analyzer: The storage processors are showing peaks close to 100% at
>>> certain times of day, but the disks are not at 100%. I'm not sure
>>> what that means. LUN % utilization is the same, so it looks like the
>>> Storage processors are responsible for the high % utilization.
>>> STATSPACK analysis shows something similar to Perfmon and Nav.
>>> Analyzer: Physical reads are 2552, physical writes are 52. There's a
>>> ton of information in the statspack report, so I'm not sure what else
>>> to look at.
>>> There is anti-virus software on this machine, but I don't think it
>>> accounts for a significant part of the 2000+ IOPs per second. We use
>>> the same product on all of our servers. IO is not a problem on any
>>> other server. On this machine, there are peaks of 10,000 IOPs. This
>>> seems abnormally high.
>>> We're a very small shop, so I'm the storage person. This is a new
>>> technology for us, so I'm in a learning curve here.
>>> There don't appear to be many good SAN related groups that are active,
>>> so I'm hoping someone here has experience with SANs and ORACLE to help
>>> out.
>>> EMC support is NOT what we were expecting. For the amount we pay for
>>> support, the response is very very disappointing. I've had a ticket
>>> open on this for MONTHS. Literally.
>>> I guess the bottom line here is that I need to prove to my boss that
>>> this is a disk bottleneck and that giving the database more spindles
>>> will help. Also, I'd like to understand why IO reads are so high for
>>> Oracle.
>>> Any information would be appreciated.
>> Congratulations. So you're the new DBA. Don't panic.http://www.ooug.org/presentations/newDBAexpanded.pdf
>>
>> Ok, that one is a little bit dated.
>> Someone posted that you want to install and use the Oracle-provided
>> tool "Statspack" to determine what is performing poorly or impacting
>> performance of business-critical usage. Some level 7 statspack
>> snapshots would be a good start (if statistics_level='TYPICAL').
>>
>> Here is a place to start:http://www.oracle.com/technology/dep.../statspack_opm...
>>
>> There is a great deal of reference material regarding Oracle's
>> Statspack.
>> Install it, take a few snapshots, generate a report and the top
>> statements should jump right off of the page at you. What you do with
>> it after that is largely a matter of what can be changed in the
>> environment and in the application code.
>>
>> If the database is configured with default parameters, there may be a
>> great deal of low-hanging fruit - in terms of:
>>
>> - statistics have not yet been gathered or are stale.
>> - buffer cache is set to a very small value and can be increased
>> - pga_workarea_target is very small and can be increased
>> - optimizer settings can be altered resulting in more favorable
>> execution plans and response times.
>>
>> If the budget and timeframe allows take classes.
>> If this is urgent and there isn't time to learn it on your own, hire
>> in a consultant.
>>
>> Check out the documentation at:
>>
>> http://www.oracle.com/pls/db102/home...base10gr2.html
>>
>> The "2 Day DBA" might be good for you:
>>
>> About this Book
>>
>> Oracle Database 2 Day DBA is a database administration quick start
>> guide that teaches you how to perform day-to-day database
>> administrative tasks. The goal of this book is to help you understand
>> the concepts behind the Oracle Database. It teaches you how to perform
>> all common administration tasks needed to keep the database
>> operational, including how to perform basic troubleshooting and
>> performance monitoring activities.
>>
>> The primary administrative interface used in this book is Oracle
>> Enterprise Manager in Database Console mode, featuring all the self-
>> management capabilities introduced in the Oracle Database.
>>
>> If not, the Concepts Guide is located on that same page.
>>
>> good luck.
>>
>> -bdbafh
>>
>> .- Hide quoted text -
>>
>> - Show quoted text -

I think the answer to your question is yes. But to be honest I
haven't worked with a stand-alone 15K drive in my life: Always
striped and mirrored as part of a disk set.

By way of comparison though ... the 7500 RPM drive on my laptop,
using FORALL, can easily do 500,000 inserts/sec.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Database Administrator]

On May 24, 5:56 pm, beth.sto...@gmail.com wrote:
>We've been having performance problems with our Client/Server
>application for months. Users contantly complain of slow response
>times to their queries.

>Here's the environment:
>Oracle 9i on Windows. Poweredge 6850. 4, 3 GHz Quad Core
>Processors. 8 GB RAM. 9i databses are stored on an EMC Clarrion
>CX500 LUN -- RAID 10. 6, 15K 146GB dedicated disks. Oracle logs are
>stored on a separate RAID 10 LUN on dedicated disks. 1 GBE switched
>backend. Users connect using FastEthernet. XP clients. All disks on
>the SAN are fibre channel.

>CPU utilization is fine. RAM utilization is fine. Throughput on the
>NIC is fine -- maxes out at 50 Mbps for a short while when users first
>log in in the morning. Averages are 20 Mbps.

I still do not like sifting through Statspack reports (ever since
reading Cary Millsap's book a couple years ago, I prefer other
methods). I will pull out a couple items from the report that look
interesting:

* TNS for 32-bit Windows: Version 9.2.0.7.0 -

32 bit Windows with 8GB of memory - it is likely better to stick with
4GB, or jump to 16GB.

* db_cache_size 209,715,200
* db_writer_processes 4
* hash_join_enabled FALSE
* large_pool_size 100,663,296
* log_buffer 12,288,000
* log_checkpoint_interval 10000
* optimizer_index_caching 90
* optimizer_index_cost_adj 20
* optimizer_mode RULE
* pga_aggregate_target 50,000,000
* sga_max_size 1,665,379,432
* shared_pool_size 209,715,200
* sort_area_size 5,120,000

- Multiple DB writer processes are recommended for systems with
multiple CPUs (at least one DB writer for every 8 CPUs)
- HASH_JOIN_ENABLED controls whether the CBO is permitted to use a
Hash Join when multiple row sets are joined.
- LARGE_POOL_SIZE specifies (in bytes) the size of the large pool
allocation heap. The large pool allocation heap is used in shared
server systems for session memory, by parallel execution for message
buffers, and by backup processes (RMAN) for disk I/O buffers. Parallel
execution allocates buffers out of the large pool only when SGA_TARGET
is set.
- LOG_BUFFER - On most systems, sizing the log buffer larger than 1MB
does not provide any performance benefit. LGWR begins to write entries
from the redo log buffer to the online redo log if any of the
following are true: the log buffer becomes 1/3 full, LGWR is posted by
a server process performing a COMMIT or ROLLBACK, or if DBWR posts
LGWR to do so.
- LOG_CHECKPOINT_INTERVAL parameter specifies the maximum number of
operating system redo blocks the incremental checkpoint target should
lag the current log tail. Regardless of this value, a checkpoint
always occurs when switching from one online redo log file to another.
- OPTIMIZER_INDEX_CACHING - may be important if OPTIMIZER_MODE were
not RULE, as DB_CACHE_SIZE may not be large enough to cache 90% of the
index blocks.
- OPTIMIZER_MODE - RULE uses indexes, even if those are not the best
access method, why RULE?
- PGA_AGGREGATE_TARGET defines the total PGA memory allocation for the
instance - this is less than ten times the value of SORT_AREA_SIZE,
which is per sorting operation.
- SGA_MAX_SIZE looks very big for a 32 bit platform, especially
considering the size of PGA_AGGREGATE_TARGET and DB_CACHE_SIZE.
- SHARED_POOL_SIZE might be fine.

The largest wait event of interest:
Event Name Count Total Time
-------------------------------- ------------- -------------
db file sequential read 3742459 2164.09s

A sequential read is a single-block read, where a user process is
reading a buffer into the SGA buffer cache and waiting for a physical
I/O, usually caused by an index access.


Total Per Tranaction
physical reads 4873242 10047.92
physical reads direct 5328 10.99
recursive calls 1443176 2975.62

Look over the information that appears above. Are there any changes
that you can make to the database instance to improve performance?

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.