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 11513 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 11513
Oracle Performance Tuning
작성자
정재익(advance)
작성일
2002-07-29 11:38
조회수
9,232

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.
제목
작성자
작성일
조회
11843Oracle 8i Database를 SQL Server 2000에 Linked Server로 연결하기 위한 연결 설정
정재익
2002-08-29
8768
11776Oracle XA and TP Monitor FAQ
정재익
2002-08-23
7737
11537RedHat 7.1 에서 Oracle 8.1.7 의 설치법
정재익
2002-07-31
10495
11513Oracle Performance Tuning
정재익
2002-07-29
9232
11512Oracle 9i 에서 CASE 사용예 [2]
정재익
2002-07-29
8704
11511NULLIF및 COALESCE 함수
정재익
2002-07-29
8908
11510윈도우용 오라클에서 memory 효율적인 사용방법
정재익
2002-07-29
7557
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.049초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다