database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Tutorials 11939 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 11939
SHARED_POOL_SIZE
작성자
정재익(advance)
작성일
2002-09-07 21:15
조회수
10,754

SHARED_POOL_SIZE

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

 

The Shared_Pool_Size is specific to all versions of Oracle and is measured in bytes. This is the memory that is allocated to Oracle for areas like the data dictionary, stored procedures, and statements. Comprising a large part of the SGA, the Shared_Pool_Size is comprised of the dictionary cache and library cache, and just like DB_Block_Buffers above, should not be set too low or too high.

 

The Shared_Pool_Size can be monitored through the data dictionary cache and the library cache. Both should be continuously monitored for an appropriate hit ratio.

 

When a user processes a SQL statement, Oracle references the data dictionary several times. Reducing physical disk IO is very important, so the more information that is stored in memory, the less that needs to be read from disk.

The data dictionary cache is very important in this respect because this is where the data dictionary components are buffered (think of it as a print

buffer). The data dictionary component can be monitored via v$rowcache using the following select statement:

 


        select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio"  
        from v$rowcache; 

You should see output similar to the following: 

        Hit Ratio 
 95.40% 

 

You should aim to have this value above 90%. The exception is when the database is first started, the value will be somewhere around 85%.

 

The library cache consists of shared SQL and PL/SQL areas. When SQL is executed, the statement has to be parsed (or taken apart for further clarification). The library cache reduces the overhead of this by maintaining parsed SQL and PL/SQL in the library cache. When there is a subsequent execution of this statement, there will not be the need to re-parse the statement. Essentially, you are reducing the work of the database. If, for example, the customer is on an OLTP system which consistently issues the same SQL, performance is enhanced. Of course, using bind variables to have sharable versions of the statements.

 

We have discussed having the Shared_Pool_Size set too low, but adverse effects may comes from setting the Shared_Pool_Size too high as well. Having the shared pool set too large increase the probability of latch contention when the database needs to find a free peace of memory to load a new statement. You can query v$sgastat to show the available free memory. This will tell you memory is being wasted. As an example, let's look at the following problem:

 

         select name, bytes/1024/1024 "Size in MB" 
         from v$sgastat 
         where name='free memory'; 

You should see output similar to the following: 
  
         NAME Size in MB 
 Free memory 39.6002884 

 

What this return would tell you is that there is 39M of free memory in the shared pool, which would mean that the shared pool is being under utilized. If the shared pool was 70M, over half of it would be under utilized. This memory could be allocated elsewhere.

 

 

See <Note.62143.1> for further reference on tuning the shared pool.

[Top]
No.
제목
작성자
작성일
조회
11942PL/SQL 자료 정리 (1)
정재익
2002-09-07
33824
11941WEB상에서 PLSQL로 EXCEL FILE만드는 예제
정재익
2002-09-07
8388
11940Reference Note for Init.Ora Parameter "AUDIT_TRAIL
정재익
2002-09-07
7146
11939SHARED_POOL_SIZE
정재익
2002-09-07
10754
11938DB_BLOCK_BUFFERS
정재익
2002-09-07
8327
11937INDEX 생성 시 고려 사항.
정재익
2002-09-07
9925
11936DATE TYPE 데이타를 효과적으로 조회하는 방법
정재익
2002-09-07
11998
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.048초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다