Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Statspack Help Please

Re: Statspack Help Please

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Thu, 20 Oct 2005 19:47:11 +0200
Message-ID: <ejlfl1lpfdp0ll9h579kq6qu878t7is7ln@4ax.com>


Comments embedded

On 20 Oct 2005 10:25:53 -0700, 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?

Logical reads in what timeframe? 1 minute? 1 hour? High logical reads probably points to badly untuned SQL

 Also, what would cause the
>rollback to be at 73%?
>

Badly written application

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

Unsharable SQL. However, a shared pool of 112M is not much these days

Should it be at 95%???

Who can tell? At least it's not oversized.

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

Assuming you are using JDBC, you are not using PrepareStatement calls, so you parse every statement over and over and over again. This is the safest way to create an unscalable application.

It also appears you are using Dictionary Managed Tablespaces, which you shouldn't do in 9i (and in 8i)

Your 'alter session set isolation level = read committed' statement is redundant, as this is the default.

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

They are yet again pointing to bad sql.

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

--
Sybrand Bakker, Senior Oracle DBA
Received on Thu Oct 20 2005 - 12:47:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US