Re: Interpretation of Statspack reports

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. Received on Wed Aug 22 2007 - 10:09:00 CDT