 |
|
Oracle Tips by Burleson |
Chapter 4 Oracle Instance
Design
Yr. Mo Dy Hr
EVENT WAITS AVG_WAIT_SECS
------------- ------------------------------ -----------
-------------
2003-08-25 13 SQL*Net more data from client 4,008
1
2003-08-25 13 SQL*Net break/reset to client 59,288
7
2003-08-25 14 latch free 16,850
7
2003-08-25 14 enqueue 4,312
27,418
2003-08-25 14 buffer busy waits 1,550
33
2003-08-25 14 LGWR wait for redo copy 209
2
2003-08-25 14 SQL*Net message to client 675,660
0
2003-08-25 14 SQL*Net more data to client 21,057
1
2003-08-25 14 SQL*Net message from client 675,642
2,402
2003-08-25 14 SQL*Net more data from client 4,214
1
2003-08-25 14 SQL*Net break/reset to client 62,844
8
2003-08-25 15 latch free 14,644
7
2003-08-25 15 enqueue 5,011
28,436
2003-08-25 15 buffer busy waits 3,339
32
2003-08-25 15 LGWR wait for redo copy 283
2
2003-08-25 09 SQL*Net message to client 510,794
0
2003-08-25 09 SQL*Net more data to client 21,264
1
2003-08-25 09 SQL*Net message from client 510,776
2,127
2003-08-25 09 SQL*Net more data from client 3,183
1
2003-08-25 09 SQL*Net break/reset to client 47,880
8
2003-08-25 11 latch free 61,649
9
In addition to monitoring for shared pool wait
events you can also use the v$shared_pool_advice utility to help you
get the optimal size for the shared pool.
The Shared Pool advisory
utility
This shared pool advisory functionality has
been extended in Oracle release 2 to include a new advice called
v$shared_pool_advice, and there is talk to expending the advice
facility to all SGA RAM areas in future releases of Oracle.
In Oracle release 2, the v$shared_pool_advice
show the marginal difference in SQL parses as the shared pool
changes in size from 10% of the current value to 200% of the current
value.
The Oracle documentation contains a complete
description for the set-up and use of shared pool advice, and it is
very simple to configure. Once it is installed, you can run a
simple script to query the v$shared_pool_advice view and see the
marginal changes in SQL parses for different shared_pool sizes.
--
************************************************
-- Display shared pool advice
-- ************************************************
set lines
100
set pages 999
column
c1 heading 'Pool |Size(M)'
column c2 heading 'Size|Factor'
column c3 heading 'Est|LC(M) '
column c4 heading 'Est LC|Mem. Obj.'
column c5 heading 'Est|Time|Saved|(sec)'
column c6 heading 'Est|Parse|Saved|Factor'
column c7 heading 'Est|Object Hits' format 999,999,999
SELECT
shared_pool_size_for_estimate c1,
http://rampant-books.com/book_2003_2_audit.htm
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
