Shulou(Shulou.com)06/01 Report--

In the 10G or 11G version of oracle, how to set the shared pool size reasonably has a great impact on the performance of oracle database.

The rules for setting Shared Pool size are as follows:

1. The reasonable value set by shared pool is found, and the statement is 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)

two。 Set the value of the ESTD_SP_SIZE in the row of the record whose PARSE_TIME_FACTOR found in the previous SQL statement is equal to 1 for the first time to shared pool.

3. The setting statement is as follows:

Alter system set shared_pool_size=XXX scope=spfile; or alter system set shared_pool_size=XXX scope=both

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.