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

You cannot simply generate statistics once and forget about them; you need a regular schedule to make them current. I would:

select 'alter table '||table_name||' monitoring;' from user_tables

spool monitor_tbls.sql
/
spool off

@monitor_tbls

Then schedule dbms_stats.gather_schema_stats and set options to 'gather stale' and let Oracle do the work of deciding which tables need refreshing and when.

You don't mention if the data is skewed (not evenly distributed within the range); such situations may benefit from histograms (method_opt => 'for all indexed columns size auto', for example). Of course you'd need to test this on a non-production database before you run and make this change to your prod database. Jonathan Lewis has excellent information in his current text('Cost Based Oracle Fundamentals', Apress); you should obtain a copy and read it, cover to cover, before making any changes to the way statistics are gathered.

David Fitzjarrell Received on Mon Sep 11 2006 - 11:36:00 CDT