Set the size of the Shared Pool in Oracle
In Oracle 10g or 11G version, how to properly set the shared pool size has a great impact on the performance of Oracle databases.
The size setting rules of the Shared Pool are as follows:
1. Check the reasonable value set by the shared pool. 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,
(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 value of ESTD_SP_SIZE in the row of the record whose PARSE_TIME_FACTOR is equal to 1 for the first time in the last SQL statement 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;
Install Oracle 11gR2 (x64) in CentOS 6.4)
Steps for installing Oracle 11gR2 in vmwarevm
Install Oracle 11g XE R2 In Debian
Sharing pool for Oracle Performance Optimization