How about also looking at pinning procedures, or even tables (if small and
you have enough memory) in to the library cache too? Look for the for those
that have a high execute/parse ratio..
> Get Pct Pin Pct
Invali-
> Namespace Requests Miss Requests Miss Reloads
dations
> --------------- ------------ ------ -------------- ------ ---------- -----
---
> TABLE/PROCEDURE 1,206,367 0.3 1,875,867 15.4 158,738
0
I would consider reading this:
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96533/memory.h
tm#31503
And, seeing as you are on 9.2, why not check out the v$shared_pool_advice,
and v$library_cache_memory views, I'd be interested to see if they give you
any *useful* information - and I'm sure the rest of the list would be to..
;)
Regards
Mark
===================================================
Mark Leith | T: +44 (0)1905 330 281
Sales & Marketing | F: +44 (0)870 127 5283
Cool Tools UK Ltd | E: mark_at_cool-tools.co.uk
===================================================
http://www.cool-tools.co.uk
Maximising throughput & performance
-----Original Message-----
Sent: 04 June 2003 10:30
To: Multiple recipients of list ORACLE-L
Increase shared pool.
VIVEK_SHARMA wrote:
>
> 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: VIVEK_SHARMA_at_infosys.com
>
> 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: ListGuru_at_fatcity.com (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Anjo Kolk
INET: anjo_at_oraperf.com
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: ListGuru_at_fatcity.com (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).
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mark Leith
INET: mark_at_cool-tools.co.uk
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: ListGuru_at_fatcity.com (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).
Received on Wed Jun 04 2003 - 05:19:39 CDT
