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 9031 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 9031
오라클 Performance Tuning Scripts
작성자
정재익(advance)
작성일
2001-12-25 12:54
조회수
4,224

Oracle Performance Tuning

 

Name: DB Block Efficiency

 

select round((1-(pr.value/(bg.value+cg.value)))*100,2)

from v$sysstat pr, v$sysstat bg, v$sysstat cg

where pr.name = 'physical reads'

and bg.name = 'db block gets'

and cg.name = 'consistent gets'

 

The init.ora parameter: DB_BLOCK_BUFFERS controls

the amount of memory allocated for the data cache.

When an application requests data, Oracle first

attempts to find it in the data cache.

The more often Oracle finds requested data in memory

a physical IO is avoided, and thus overall performance

is better. Under normal circumstances this ratio

should be greater than or equal to 95%. Initially

set the DB_BLOCK_BUFFERS size to be 20 - 50% the

size of the SGA.

 

Name: Dictionary Cache Efficiency

 

select round(sum(gets)/(sum(gets)+sum(getmisses)) * 100,2)

from v$rowcache;

 

The init.ora parameter: SHARED_POOL_SIZE controls the amount of

memory allocated for the shared buffer pool.

The shared buffer pool contains SQL and PL/SQL statements

(library cache), the data dictionary cache,

and information on data base sessions.

This percentage will never equal 100 because the cache

must perform an initial load when Oracle first starts up.

The percentage, therefore, should continually get closer

to 100 as the system stays "up."

 

Ideally, the entire data dictionary would be cached in memory.

Initially set the SHARED_POOL_SIZE to be 50-100% the size of

the init.ora parameter: DB_BLOCK_BUFFERS - then fine tune the

parameter.

 

Name: Disk Reads Max SQL

 

select sql_text

from v$sqlarea, v$session

where address = sql_address

and username is not null

and disk_reads/executions =

(select max(disk_reads/executions)

from v$sqlarea, v$session

where address = sql_address

and username is not null)

 

This query returns the first 1000 bytes of the SQL statement

having the highest number of disk reads per execution.

This query is designed to help determine the user generated

SQL causing a large number of disk reads per statement execution.

 

Name: Disk Reads Max User

 

select username

from v$sqlarea, v$session

where address = sql_address

and username is not null

and disk_reads/executions =

(select max(disk_reads/executions)

from v$sqlarea, v$session

where address = sql_address

and username is not null)

 

This query returns the username associated with the SQL

statement having the highest number of disk reads per execution.

This query is designed to help determine the user causing

a large number of disk reads per statement execution.

[Top]
No.
제목
작성자
작성일
조회
9039Intermedia Text 설치하기 [1]
정재익
2001-12-25
4501
9037ora-04031과 alter system flush shared_pool에 관해...
정재익
2001-12-25
4399
9036일반 User 로 접속한후 DB를 start or shutdown 하는 방법
정재익
2001-12-25
4273
9031오라클 Performance Tuning Scripts
정재익
2001-12-25
4224
9030CASE 의 사용예
정재익
2001-12-25
4250
9029NULLIF및 COALESCE 함수
정재익
2001-12-25
4120
9022Pro*C 란? (2)
정재익
2001-12-25
9083
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.024초, 이곳 서비스는
	PostgreSQL v16.2로 자료를 관리합니다