How to set up shared pool in Oracle

There is no universal, universally applicable value for the size of the shared pool, and different system loads require a shared pool of different sizes to manage. Usually when we set up shared pool, we should follow the "Don't be too big, not too small" principle, set an initial value, and then let the system run for a period of time, during which time, the use of shared pool observation and monitoring, Finally, according to the load of the system, we can get a reasonable value under the current load. Note that this only means that under the current load, if the load changes significantly as the system escalates, then the shared pool needs to be monitored again and adjusted accordingly.

In general, setting a shared pool of more than 1GB does not significantly improve performance, which in contrast will cause more trouble for Oracle to manage shared pool and monitor shared pool. We can set the shared pool to 10% of the SGA when the system is online, but do not exceed 1GB to allow the system to function for a period of time, we can use the Oracle 9i after the introduction of the Consultant (advisory) to help us determine whether the set of shared pool is reasonable.

If you set the initialization parameter Statistics_level to typical (default) or all, you can start the recommended feature for shared pool, and if you set to basic, turn off the recommended feature. Use the following SQL statement to display the size of the shared pool that Oracle recommends.

Sql> Select Shared_pool_size_for_estimate "SP", Estd_lc_size "EL",

Estd_lc_memory_objects "ELM",

2 estd_lc_time_saved "ELT", Estd_lc_time_saved_factor as "ELTs",

3 estd_lc_memory_object_hits as "ELMO"

4 from V$shared_pool_advice;

SP EL ELM ELT elts ELMO

---------  -------        ----------     ---------      --------     ------------

128 135 12223 8566 0.9993 2980874

160 166 15809 8567 0.9994 2981291

192 197 19167 8570 0.9998 2982322

224 228 22719 8572 1 2982859

256 259 27594 8572 1 2982906

288 292 31436 8572 1 2982917

320 323 36157 8572 1 2982920

352 354 40371 8572 1 2982929

384 385 45019 8572 1 2982937

416 389 46099 8572 1 2982937

448 389 46099 8572 1 2982937

480 389 46099 8572 1 2982937

512 389 46099 8572 1 2982937

The first column represents the dimension values of the shared pool that Oracle estimates, and the other columns represent the metric values shown under the estimated shared pool size, which can be seen in Oracle's online help. Our primary focus is on the value of the Estd_lc_time_saved_factor column, when the column value is 1 o'clock, which means that adding the shared pool size does not make sense for performance improvement. For the above example, when the shared pool is 224MB, the optimal size is achieved. For a shared pool that is larger than 224MB, it is a waste of space and no more benefits.