For a Hybrid Banking Application , following waits are Observed :-
Oracle ver 9.2 (Dedicated Server connections)
Solaris 8
M/c SF15K
Application mostly uses Static Queries (i.e. with Bind variables) ,
though there may be some amount of Literal SQLs too
DB Size = 250 GB
Qs How may the following waits be approached ?
Qs Since we have just moved to 9.2 , any advisable standard init.ora parameters with
9.2 ?
Qs Should CURSOR_SHARING = FORCE or SIMILAR be considered ? Which is better
& what may be the Overhead of these on production ?
Shall answer any Clarifications .
Thanks
------------------------------------------------------------------------------
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
TBASUN 1820705732 tbasun 1 9.2.0.3.0 NO IB15KDB
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 71 03-Jun-03 12:00:05 2,953 53.2
End Snap: 91 03-Jun-03 13:00:05 3,030 55.2
Elapsed: 60.00 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 703M Std Block Size: 8K
Shared Pool Size: 400M Log Buffer: 6,144K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 251,510.31 1,682.23
Logical reads: 35,458.62 237.17
Block changes: 1,226.80 8.21
Physical reads: 2,391.08 15.99
Physical writes: 155.51 1.04
User calls: 8,556.91 57.23
Parses: 495.46 3.31
Hard parses: 71.17 0.48
Sorts: 201.79 1.35
Logons: 8.80 0.06
Executes: 5,949.80 39.80
Transactions: 149.51
% Blocks changed per Read: 3.46 Recursive Call %: 43.86
Rollback per transaction %: 55.51 Rows per Sort: 45.29
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.98 Redo NoWait %: 99.99
Buffer Hit %: 93.35 In-memory Sort %: 99.94
Library Hit %: 98.14 Soft Parse %: 85.64
Execute to Parse %: 91.67 Latch Hit %: 98.81
Parse CPU to Parse Elapsd %: 3.56 % Non-Parse CPU: 90.92
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 93.23 86.67
% SQL with executions>1: 54.37 75.05
% Memory for SQL w/exec>1: 47.47 70.06
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
library cache pin 97,608 95,845 40.95
latch free 2,788,119 70,018 29.91
CPU time 25,145 10.74
library cache load lock 19,686 19,419 8.30
db file sequential read 6,334,694 6,715 2.87
-------------------------------------------------------------
-------------------------------------------------------------
Latch Activity for DB: TBASUN Instance: tbasun Snaps: 71 -91
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
library cache 69,587,442 3.3 0.7 53315 663,932 50.5
row cache objects 29,587,406 2.8 0.1 2549 128,096 18.3
Library Cache Activity for DB: TBASUN Instance: tbasun Snaps: 71 -91
->"Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY 3,466 11.3 3,532 17.9 187 0
CLUSTER 12,921 0.1 14,953 0.1 0 0
INDEX 38,620 19.0 38,618 24.5 8 0
SQL AREA 1,627,354 6.3 26,006,472 0.8 152,116 8
TABLE/PROCEDURE 1,206,367 0.3 1,875,867 15.4 158,738 0
TRIGGER 1,405 0.2 1,713 44.4 542 0
-------------------------------------------------------------
shared_pool_reserved_size 104857600
shared_pool_size 419430400
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: VIVEK_SHARMA
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
