Re: Interpretation of Statspack reports

On Aug 22, 5:09 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Aug 22, 7:27 am, Helma <bad_elef..._at_hotmail.com> wrote:
>
>
>
> > Hello all,
>
> > I'm new into reading statspack reports, and i would like to hear your
> > opinion on some findings i find odd.
> > It's a 1hour snapreport from an oracle 9 on a 2CPU windowsmachine with
> > one thirth-party application and a handful of users. Dataloading is
> > about 1Gb per day.
> > There are a couple of weird choices made with the application design,
> > e.g. every table has it's own tablespace, so there are more than 900
> > tablespaces with over 1800 datafiles. There are only a few users, the
> > database is configured as shared server. etc!
>
> > Anyway:
>
> > Top 5 Timed Events
> > ~~~~~~~~~~~~~~~~~~ % Total
> > Event Waits Time (s) Ela Time
> > -------------------------------- ------------ -----------
> > --------
> > CPU time 1,448 66.73
> > db file sequential read 39,817 268 12.36
> > log file sync 388,735 196 9.03
> > log file parallel write 433,236 183 8.45
> > control file sequential read 9,500 27 1.25
>
> > Q1:
> > This application primarily loads data , although i suspect in a
> > suboptimal way. I would have
> > expected that I/O would be the main timed event, not the CPU. Does
> > this confirm that the procedure that processes and loads the data is
> > suboptimal?
>
> > Q2:
> > Event Waits Timeouts
> > log file parallel write 433,236 431,408
>
> > The number of timeout almost equals the waits - what does this mean?
> > If it means anything...
>
> > Q3: What book / article do you advice to learn the fine art of
> > reportreading? Most of the things i found are rather basic - how to
> > create a report, add statspack jobs, etc.
>
> > TIA,
> > H.
>
> It looks like you have a lot of opportunity to improve performance.
> Unfortunately, a Statspack report may not provide enough detailed
> information to determine the source of the problem. Some help in
> interpretting the wait events:
> LOG FILE PARALLEL WRITE: Writing redo records to the redo log files
> from the log buffer. Usually indicates slow devices or contention
> where the online redo logs are located.
> P1: files P2: blocks P3: requests
>
> LOG FILE SYNC: I/O, overcommitting: Slow disks that store the online
> logs, Un-batched commits. This wait event is one of the first events
> to show increased latencies due to the time a process spends waiting
> in a CPU wait queue while processing excessive LIOs.
> EXAMINE: Check the disks that house the online redo logs for resource
> contention. Check the number of transactions (commits + rollbacks)
> each second, from V$SYSSTAT.
>
> DB FILE SEQUENTIAL READ: I/O, SQL statement tuning: Poorly tuned SQL,
> Slow I/O system. 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. Single block I/Os are usually the result of index
> scans. Rarely, full table scans could get truncated to a single block
> call due to extent boundaries, or buffers already present in the
> buffer cache.
>
> 1800 open data files on Windows will be a problem. My guess is that
> each one is growing in small increments, maybe 8KB or 256KB. The redo
> logs are probably also quite small, and may be located on slow a RAID
> 5 array. A shared server configuration should not be used when there
> are only a couple users in the system.
>
> I would suggest that you start looking at the wait event interface to
> determine the cause of the performance problem, if one exists. To
> help you get started, take a look at the output of the following:
> SELECT
> *
> FROM
> V$SESSION_EVENT;
>
> SELECT
> *
> FROM
> V$SYSTEM_EVENT;
>
> SELECT
> *
> FROM
> V$SYSSTAT;
>
> SELECT
> *
> FROM
> V$SESSTAT SS,
> V$STATNAME SN
> WHERE
> SS.STATISTIC#=SN.STATISTIC#;
>
> Google searches will be your friend when trying to determine the
> meaning of the results from the above, as will the Oracle Performance
> Tuning Guide. Once you have a feel for what is returned by the above,
> you can start looking at 10046 and 10053 trace files to determine
> roughly what is happening in the system that causes the performance
> issue.
>
> Consider creating a small number of locally mananged tablespaces (1 or
> 2, or how ever many may be needed) that autoextend in 200MB
> increments, and move all of the tables and indexes to those
> tablespaces. Also, consider resizing the redo logs so that they
> switch roughly every 20 to 30 minutes.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

Thank you , Charles! I will work on this :)

H. Received on Wed Aug 22 2007 - 10:19:23 CDT