Hi Raghu,
Can you trace a couple of your top sessions by setting
10046 and see which SQL statements are causing the
most resource consumption. This will give us an idea
of the "cause". The "effect" that you are seeing is a
very high level of CPU consumption and waits for the
cache buffers chains latches.
>From the look of it (and we need to confirm this with
the trace data), your SQL statements are probably
performing more logical I/O than what is required. The
report you shared with us, reveals 5,806 Logical I/Os
per second over a 24.42 minutes. With 1482 seconds in
this time period, the amount of logical I/O performed
is 8,604,492 blocks. How many concurrent sessions did
you have then?
The symptom of cache buffers chains contention may
corroborate that there could be more logical I/O
performed than what is required. You need to
investigate what is causing this and whether or not
this is reasonable?
One of the potential reasons for this could be
"over-indexing" of the tables and/or "forceful use of
indexes for many queries", which may do quite well
with full-table scans. Use the 10046 trace data as
your "starting point" to take you down the right path
for your problem solving process.
Cheers,
Gaja
- Raghu Banaji <raghu.banaji_at_ifsna.com> wrote:
> All users waiting for latch "cache buffer chains"
>
> -----Original Message-----
> Sent: Friday, April 26, 2002 11:59 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
> From the statspack report it shows that u have lots
> of latchfree waits which
> is event timing out
> can u see in the session_Wait which is the user and
> what latch he is waiting
> on
>
>
> -----Original Message-----
> Sent: Friday, April 26, 2002 2:24 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Hi,
>
> I have a customer who runs an ERP application
> written using
> ORACLE. Almost all code is written using PL/SQL
> packages. There are about
> 80-100 users
> at any given point of time(6 AM - 11 PM). Oracle
> version is 8.1.7.1.1
> Standard Edition.
> All the four CPU's on their server is pegged at 100%
> for most of the time.
> This is resulting
> in end users complaning about slow performance and
> slower log-ons to the
> application.
> This issue has been occuring consistently for over
> 2-3 weeks now. Archive
> logging is enabled
> and the database is normally shutdown once a week
> for cold backup.
>
> The Admin guys have been monitoring the server using
> Performance monitor and
>
> find that Oracle.exe is the process consuming 99% of
> the resource. Memory
> usage is
> constant and there is plenty of it free. Hard disk
> drives show no activity.
> A normal
> assumption would be that disk drives would be going
> crazy with all
> the CPU activity going on, but that does not seem to
> be the case.
>
> Oracle software and one PRODuction database exist on
> this server.
>
> Their Server configuration consists of:
> Windows 2000 OS (Advanced Server), with 4 Pentium
> III Xeon processors (each
> 700 Mhz),
> 4 Gb RAM and 2 disk drives on seperate disk
> controllers. Hard drives are of
> 15,000 RPM's.
>
>
> MY APPROACH SO FAR:
>
> Step 1:
> I started looking into this issue since last week
> and the first area I
> concentrated
> was on finding SQL statements that were taking too
> many hits or taking too
> long to complete.
> I was able to tune almost 10 of the top SQL
> statements last week. In most of
> the previous performance
> issues I have been involved with this has resulted
> in huge performance gains
> and life went on.
> In this situation, tuning these top 10 SQL's did
> result in small gains, but
> did not make
> any difference to the CPU contention. They still
> continued to be pegged at
> 100% most of the times.
>
> Step 2:
> The next step I took was to find out if there were
> hard parsing going on. As
> mentioned in a number
> of articles here, this would cause the CPU to work
> extra harder.
> My check resulted in only 2 SQL statement that were
> hard parsed.
> Based on the fact that there are a number of very
> big jobs, user queries
> and other activities that go on a daily basis,
> should I really worry about
> it ?
>
>
> Step 3:
> Next step was to increase the number of rollback
> segments from 5 to 35.
> Previously, there
> were 5 big rollback segments. Now, there are 35
> medium sized rollback
> segments spread over
> 2 rollback tablespaces. This step was taken
> yesterday and so far there has
> been no improvement
> as far as CPU pegging goes. It is still pegged at
> 100%
>
> Step 4:
> Certain articles in Metalink suggested that one of
> the reasons
> that would make the CPU spin continously would be
> SMON working overtime
> to clean up a large number of temporary extents, or
> to coalesce a large
> number of free extents.
> This can manifest itself by SMON appearing to spin,
> consuming a high
> percentage of CPU for
> long periods. I really dont know how to test this
> statement. PCT_INCREASE is
> set to 0 for
> all the tablespaces except SYSTEM and one ROLLBACK
> tablespace.
> I will re-set PCT_INCREASE to 0 for the second
> ROLLBACK tablespace also.
> Apart from this I
> really dont know what else I can look for. My
> temporary tablespace is around
> 3 GB.
> Is there a SQL statement that I can run and find out
> if there is a problem
> with SMON?
>
> Step 5:
> The final step was to run Statspack on this instance
> for about 25 minutes.
> I have copied extracts from some of the data that
> would be of interest.
>
> Hard parses and the Top 5 waits seem to be the issue
> atleast in this report.
>
> Could some one share some of your experience in the
> area of tuning these
> wait events
> and what you would recomend me to do next. My CPU is
> still pegged at 100%.
>
>
> STATSPACK report for
>
> DB Name DB Id Instance Inst Num
> Release OPS Host
> ------------ ----------- ------------ --------
> ----------- --- ------------
> PROD 3888465932 prod 1
> 8.1.7.1.1 NO ERP1
>
> Snap Id Snap Time Sessions
> ------- ------------------ --------
> Begin Snap: 31 26-Apr-02 09:21:38 169
> End Snap: 41 26-Apr-02 09:46:03 169
> Elapsed: 24.42 (mins)
>
> Cache Sizes
> ~~~~~~~~~~~
> db_block_buffers: 131072
> log_buffer: 163840
> db_block_size: 8192
> shared_pool_size: 314572800
>
> Load Profile
> ~~~~~~~~~~~~ Per Second
> Per Transaction
> ---------------
> ---------------
> Redo size: 9,606.78
> 2,352.32
> Logical reads: 5,806.11
> 1,421.69
> Block changes: 59.60
> 14.59
> Physical reads: 62.26
> 15.24
> Physical writes: 8.04
> 1.97
> User calls: 45.08
> 11.04
>
=== message truncated ===
Gaja Krishna Vaidyanatha
Director, Storage Management Products,
Quest Software, Inc.
Co-author - Oracle Performance Tuning 101
http://www.osborne.com/database_erp/0072131454/0072131454.shtml
Do You Yahoo!?
Yahoo! Games - play chess, backgammon, pool and more
http://games.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gaja Krishna Vaidyanatha
INET: oraperfman_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Fri Apr 26 2002 - 16:43:21 CDT
