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
>
> Load Profile
> ~~~~~~~~~~~~ Per Second Per
> Transaction
> ---------------
> ---------------
> Redo size: 8,152.77
> 1,006,867.60
> Logical reads: 1,323.56
> 163,459.10
> Block changes: 43.14
> 5,327.50
> Physical reads: 12.50
> 1,543.40
> Physical writes: 200.46
> 24,757.40
> User calls: 0.09
> 11.10
> Parses: 0.68
> 83.90
> Hard parses: 0.07
> 8.30
> Sorts: 0.41
> 51.10
> Logons: 0.01
> 0.90
> Executes: 14.71
> 1,816.80
> Transactions: 0.01
>
> % Blocks changed per Read: 3.26 Recursive Call %: 99.77
> Rollback per transaction %: 40.00 Rows per Sort: 86.15
>
> Instance Efficiency Percentages (Target 100%)
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Buffer Nowait %: 100.00 Redo NoWait %: 100.00
> Buffer Hit %: 99.06 In-memory Sort %: 100.00
> Library Hit %: 97.38 Soft Parse %: 90.11
> Execute to Parse %: 95.38 Latch Hit %: 100.00
> Parse CPU to Parse Elapsd %: 92.42 % Non-Parse CPU: 97.64
>
> Shared Pool Statistics Begin End
> ------ ------
> Memory Usage %: 91.75 94.29
> % SQL with executions>1: 52.05 53.31
> % Memory for SQL w/exec>1: 44.93 44.41
>
> Top 5 Timed Events
> ~~~~~~~~~~~~~~~~~~
> % Total
> Event Waits Time (s)
> Ela Time
> -------------------------------------------- ------------ -----------
> --------
> 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
>

Do you have a specific part of your end of day process that needs tuning? Have you looked at the individual SQL statements involved in the end of day process? You will probably derive more benefit from looking at the individual performance of your SQL statements than looking at this Statspack report.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown