Re: can anyone help me tune the database

trameshkumar_at_gmail.com wrote:
> STATSPACK report for
>
> Snap Id Snap Time Sessions Curs/Sess Comment
> ------- ------------------ -------- ---------
> -------------------
> Begin Snap: 52 23-Aug-06 22:28:51 16 5.4
> End Snap: 53 23-Aug-06 22:49:26 16 4.7
> Elapsed: 20.58 (mins)
>
> Cache Sizes (end)
> ~~~~~~~~~~~~~~~~~
> Buffer Cache: 504M Std Block Size: 8K
> Shared Pool Size: 216M Log Buffer: 512K
>
> CPU time 83
> 62.08
> direct path write 16,504 43
> 32.23
> db file sequential read 927 3
> 2.02
> db file scattered read 962 2
> 1.25
> control file parallel write 401 1
> 1.11
> -------------------------------------------------------------
> Wait Events for DB: OCTOPUS Instance: octopus Snaps: 52 -53
> -> s - second
> -> cs - centisecond - 100th of a second
> -> ms - millisecond - 1000th of a second
> -> us - microsecond - 1000000th of a second
> -> ordered by wait time desc, waits desc (idle events last)
>
> Avg
> Total Wait wait
> Waits
> Event Waits Timeouts Time (s) (ms)
> /txn
> ---------------------------- ------------ ---------- ---------- ------
> --------
> direct path write 16,504 0 43 3
> 1,650.4
> db file sequential read 927 0 3 3
> 92.7
> db file scattered read 962 0 2 2
> 96.2
> control file parallel write 401 0 1 4
> 40.1
> log file parallel write 96 91 1 7
> 9.6
> ARCH wait on SENDREQ 20 0 1 31
> 2.0
> control file sequential read 304 0 0 1
> 30.4
> log file sync 6 0 0 8
> 0.6
> db file parallel write 10 5 0 2
> 1.0
> SQL*Net break/reset to clien 2 0 0 8
> 0.2
> log buffer space 1 0 0 12
> 0.1
> latch free 1 0 0 9
> 0.1
> SQL*Net more data to client 14 0 0 0
> 1.4
> LGWR wait for redo copy 5 0 0 0
> 0.5
> SQL*Net message from client 96 0 3,070 31977
> 9.6
> SQL*Net message to client 96 0 0 0
> 9.6
> -------------------------------------------------------------
> Background Wait Events for DB: OCTOPUS Instance: octopus Snaps: 52
> -53
> -> ordered by wait time desc, waits desc (idle events last)
>
> Avg
> Total Wait wait
> Waits
> Event Waits Timeouts Time (s) (ms)
> /txn
> ---------------------------- ------------ ---------- ---------- ------
> --------
> control file parallel write 401 0 1 4
> 40.1
> log file parallel write 96 91 1 7
> 9.6
> ARCH wait on SENDREQ 20 0 1 31
> 2.0
> control file sequential read 260 0 0 1
> 26.0
> db file sequential read 2 0 0 81
> 0.2
> db file parallel write 10 5 0 2
> 1.0
> latch free 1 0 0 9
> 0.1
> LGWR wait for redo copy 5 0 0 0
> 0.5
> rdbms ipc message 1,426 1,345 11,689 8197
> 142.6
> smon timer 4 4 1,229 ######
> 0.4
> -------------------------------------------------------------
>
> At first I found that the statistics were not present and used the
> dbms_stats utility to create them .Until I created it was using rule
> based optimizer.There is visible improvement.But during the end of the
> day process and the month end process it takes more time.This statspack
> is for the end of day process

I agree with the other people who responded in this thread - not enough information in the report.

The report covers 20.58 minutes (1234.8 seconds). If I am reading the report correctly (I don't play with Statspack much), the database waited on sorts to disk 16,500 times for a total of 43 seconds, roughly 5 seconds were spent reading data/indexes from disk, and 3,070(?) seconds waiting for the client to send another request to the server. Did the process complete before the end of the 20.58 minute capture?

If I were in this situation, I would perform a 10046 trace at level 8 to see the SQL statements and their associated wait events. If you manually review the log file (Cary Millsap's book is helpful for this) you can see the SQL statement being parsed, and then what happens after the parse. For instance, if after the parse, you see a delay for "SQL*Net message from client" that lasts 45 seconds, that is likely an indication of a bottleneck on the client computer.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Sep 11 2006 - 12:25:27 CDT