Re: Statspack Help Please

art_at_chicagorsvp.com wrote:
> Hi,
>
> We are all pretty new to Oracle here. We are running 9.2.0.5 on AIX
> and have a statspack report. I'd like to post some of it and see if
> anyone can tell me if there is an issue:
>
> Cache Sizes (end)
> ~~~~~~~~~~~~~~~~~
> Buffer Cache: 112M Std Block Size: 8K
> Shared Pool Size: 112M Log Buffer: 512K
>
> Load Profile
> ~~~~~~~~~~~~ Per Second Per Transaction
> --------------- ---------------
> Redo size: 909.01 3,559.24
> Logical reads: 4,000.48 15,663.84
> Block changes: 3.42 13.38
> Physical reads: 0.15 0.60
> Physical writes: 0.05 0.20
> User calls: 6.31 24.70
> Parses: 5.86 22.95
> Hard parses: 0.12 0.45
> Sorts: 0.73 2.84
> Logons: 0.02 0.07
> Executes: 34.42 134.75
> Transactions: 0.26
>
> % Blocks changed per Read: 0.09 Recursive Call %: 90.91
> Rollback per transaction %: 73.94 Rows per Sort: 151.04
>
>
>
> Should the Logical Reads be so high? Also, what would cause the
> rollback to be at 73%?
>
>
>
> Instance Efficiency Percentages (Target 100%)
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Buffer Nowait %: 100.00 Redo NoWait %: 100.00
> Buffer Hit %: 100.00 In-memory Sort %: 100.00
> Library Hit %: 98.75 Soft Parse %: 98.02
> Execute to Parse %: 82.97 Latch Hit %: 99.50
> Parse CPU to Parse Elapsd %: 56.14 % Non-Parse CPU: 98.75
>
> Shared Pool Statistics Begin End
> ------ ------
> Memory Usage %: 95.84 95.64
> % SQL with executions>1: 45.31 46.91
> % Memory for SQL w/exec>1: 42.98 45.48
>
>
> Why is the memory usage so high? Should it be at 95%???
>
>
> SQL ordered by Parse Calls for DB: SUG Instance: SUG Snaps: 11 -12
> -> End Parse Calls Threshold: 1000
>
> % Total
> Parse Calls Executions Parses Hash Value
> ------------ ------------ -------- ----------
> 4,366 4,366 66.98 2545474735
> Module: JDBC Thin Client
> SELECT /*+ ORDERED */'X' FROM CTXSYS.DR$INDEX,SYS.USER$ U W
> HERE IDX_OWNER# = U.USER# AND U.NAME = :b1 AND IDX_NAME = :b2
>
> 104 104 1.60 2095543314
> select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t whe
> re t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0
>
> 101 102 1.55 3404108640
> Module: JDBC Thin Client
> ALTER SESSION SET ISOLATION_LEVEL = READ COMMITTED
>
> 81 81 1.24 1307778841
> select condition from cdef$ where rowid=:1
>
> 48 128 0.74 3787621475
> Module: EPTS.exe
> Select EPTS_DISCHARGES.rowid, EPTS_DISCHARGES.* from EPTS_DISCHA
> RGES where EPTS_DISCHARGES.ROWID=:V1
>
> 36 36 0.55 633914867
>
>
> Notice that the Parse & Executions are mostly the same. What does this
> mean?
>
>
> Instance Activity Stats for DB: SUG Instance: SUG Snaps: 11 -12
>
> Statistic Total per Second per Trans
> ----------------------- ------------ -------------- ------------
> buffer is not pinned count 219,445 197.3 772.7
> buffer is pinned count 8,276,689 7,443.1 29,143.3
> consistent gets 4,445,008 3,997.3 15,651.4
> process last non-idle time 22,596,439,061 20,320,538.7 ############
>
>
> Are these high numbers ok?
>
>
> If I can provide anymore info let me know. We just would like to know
> what those high numbers mean, especially why the CPU is at 95% and the
> rollback is at 73%.
>
> Thanks as always.

It strikes me that you are looking for issues in the numbers rather than having issues and looking for solutions in the numbers.

What is the issue?
What is the application? Home grown or commercial? Feel free to contact me off-line.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)