Set the size of the Shared Pool in Oracle

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.