Re: Statspack Help Please

<art_at_chicagorsvp.com> wrote in message
news:1129829153.630056.316930_at_z14g2000cwz.googlegroups.com...
>
> 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 looks like this is a snapshot over about 18.5 minutes (1,112 seconds) judging from the numbers in the last extract you showed. 4,000 logical I/Os and 7,400 'buffer is pinned' is usually not going to result in much CPU usage per second.

However, doing the following four times per second looks like a bit of an impending threat. Anything to do with the context option (or text server, or whatever it's called these days) can end up doing more work than you can really control.
> 4,366 4,366 66.98 2545474735
> SELECT /*+ ORDERED */'X' FROM CTXSYS.DR$INDEX,SYS.USER$ U W
> HERE IDX_OWNER# = U.USER# AND U.NAME = :b1 AND IDX_NAME = :b2

The next one is a bit of an oddity - because it looks like smon trying to clean up tablespaces every 10 seconds instead of every five minutes. As Sybrand point out, this suggests that you are using dictionary managed tablespaces - moreover, it may be that you are regularly (creating and) dropping objects, which is not a good strategy.

> 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
>

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals
Now available to pre-order.

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005