|
|
| |
 |
|
Diving
Into the Shared Pool - An In Depth Look at Tuning the Shared Pool (Part 1)
Oracle Tips by Mike Ault |
Monitoring and Tuning the Shared Pool
The script in Figure 9 shows a view
that provides details on individual users SQL area reuse. The view
can be tailored to your environment if the limit on reuse (currently
set at 1) is too restrictive. For example, in a recent tuning
assignment resetting the value to 12 resulting in nearly 70 percent
of the SQL being rejected as garbage SQL, in DSS or data warehouse
systems where rollups are performed by the month, bi-monthly or
weekly values of 12, 24 or 52 might be advisable. Figure 10 shows a
report script that uses the view created in Figure 9.
REM
REM View to sort SQL into GOOD and GARBAGE
REM
CREATE OR REPLACE VIEW sql_garbage AS
SELECT
b.username users,
SUM(a.sharable_mem+a.persistent_mem) Garbage,
TO_NUMBER(null) good
FROM
sys.v_$sqlarea a, Remote DBA_users b
 |
If you like Oracle
tuning, you may enjoy the book
Oracle Tuning: The Definitive Reference
, with over 900 pages of BC's favorite tuning tips & scripts.
You can buy it directly from the publisher and save 30%, and
get instant access to the code depot of Oracle tuning scripts. |
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
|
|
|
