Oracle sets the size of the shared pool

In Oracle 10G or 11G versions, the reasonable setting of the shared pool size has a significant impact on the performance of the Oracle database.

The size setting rules for the Shared pool are as follows:

1. Find a reasonable value for the shared pool setting, as follows:

Select ' Shared Pool ' component,

Shared_pool_size_for_estimate Estd_sp_size,

Estd_lc_time_saved_factor Parse_time_factor,

Case

When current_parse_time_elapsed_s + adjustment_s < 0 Then

0

ELSE

current_parse_time_elapsed_s + adjustment_s

END Response_time

From (select Shared_pool_size_for_estimate,

Shared_pool_size_factor,

Estd_lc_time_saved_factor,

A.estd_lc_time_saved,

e.value/100current_parse_time_elapsed_s,

C.estd_lc_time_saved-a.estd_lc_time_saved adjustment_s

From V$shared_pool_advice A,

(SELECT * from v$sysstat where NAME = ' Parse Time elapsed ') E,

(Select Estd_lc_time_saved

From V$shared_pool_advice

where shared_pool_size_factor = 1) c);

2. Set the Estd_sp_size value of the row for the record in the last SQL statement that parse_time_factor first equals 1 to the shared pool.

3. Set the statement as follows:

Alter system set SHARED_POOL_SIZE=XXX scope=spfile; or alter system set SHARED_POOL_SIZE=XXX Scope=both;

This article is from the "Lin XI" blog, please be sure to keep this source http://lutaoxu.blog.51cto.com/4224602/1574270

Oracle sets the size of the shared pool