Tuning Global Cache - Expert Oracle RAC Performance Diagnostics and Tuning - page 387

Step 3
As discussed earlier, most of the shared pool related resources, specifically those showing high resource sizes, are
part of the shared pool. It's in the best interest to check the shared pool utilization for instance 5. Shared pool for all
instances has been set to a size of 20G.

SQL> SELECT inst_id,
NAME,
VALUE
FROM gv$parameter
WHERE NAME LIKE 'shared_pool_size%'
ORDER BY inst_id;

INST_ID NAME VALUE
---------- ------------------------------ --------------
1 shared_pool_size 21474836480
2 shared_pool_size 21474836480
3 shared_pool_size 21474836480
4 shared_pool_size 21474836480
5 shared_pool_size 21474836480
6 shared_pool_size 21474836480
7 shared_pool_size 21474836480
8 shared_pool_size 21474836480

8 rows selected.

Step 4
Further investigation of the instances indicated that automatic memory management has been enabled. This would
mean that the 20G allocated to the shared pool is just a startup value and should grow or shrink based on demand.

SQL> show parameter sga_

NAME TYPE VALUE
------------------------------------ ----------- ----------
sga_max_size big integer 70G
sga_target big integer 0

Step 5
Checking the GV$SHARED_POOL_ADVICE indicated that the server is low on the shared pool. Based on the frequent need
for a higher shared pool, increasing the start value to 30G provided improved performances.
The chain effect of having a low-shared pool size can also be causing issues with performance of SQL statements.
If the SGA or shared pool needs to grow or shrink, it could result in library cache invalidation, reparses, and therefore
a slowdown in SQL execution.

■ the rule should be, when the MAX_UTILIZATION (Mu) gets close to the LIMIT_VALUE (lV) or the
INITIAL_ALLOCATION (ia) and remains the same at this value for a considerable amount of time, consider increasing
the SHARED_POOL .

Note