Re: Statspack Help Please
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Thu, 27 Oct 2005 14:00:15 +0000 (UTC)
<art@xxxxxxxxxxxxxxx> wrote in message
news:1129829153.630056.316930@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>
> 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
.
- References:
- Statspack Help Please
- From: art
- Statspack Help Please
- Prev by Date: Re: dbms_system and string parameters
- Next by Date: Re: Oracle on v40z vs Oracle on v490
- Previous by thread: Re: Statspack Help Please
- Next by thread: Re: question about the listener.log file
- Index(es):
Relevant Pages
|
