Memory Allocation의 중요성
momory access가 disk access가 더 빠르기 때문에, data가 가능한 Memory에 많이 저장되어 data request가 memory access로 해결될 수 있도록 한다.
Reducing Paging and Swaping
많은 OS는 real memory에 들어갈 수 없는 상당한 양의 정보를 다른 저장 장소로 옮기는데 이를 Paging or Swaping이라고 한다. 이것은 system performance를 저하시킨다.
system의 전체 memory가 할당할 memory를 수용하지 못할 경우로, 이때는 system의 memory를 증가시키든가, 그렇지 않으면 할당할 SGA를 줄여야 한다.
Tunning System Global Area
SGA의 목적은 data를 fast access를 위해 memory에 두는 것으로, page나 swap이 되어 속도가 저하되어서는 안된다. 초기 파라미터 setting값으로 PRE_PAGE_SGA를 YES로 해서 instance startup시에 모든 SGA를 real memory에 읽어 올 수는 있으나, swaping이나 paging을 막지는 못한다.
Tuning the Shared Pool
Shared Pool은 SGA의 부분으로, dictionary cache와 library cache (shared SQL area)로 구성되고, LRU algorithm에의해 관리된다.
· library cache containing shared SQL and PL/SQL areas
· data dictionary cache
· information for sessions connected through shared server processes
dictionary cache와 library cache의 miss가 data buffer miss보다 더 expensive하기 때문에 shared pool 에 충분한 memory를 할당해야 한다.
Tuning the Library Cache
library cache는 SQL과 PL/SQL area를 포함한다.
Cursors
각 SQL 문은 Library Cache( CONTEXT AREA, CURSOR)의 자신의 영역에서 parse된다.
같은 sql문을 쓰는 두 user는 같은 cursor를 공유한다.
· SQL text가 case, blank, comment까지 같아야 한다.
· reference되는 object가 같아야 한다.
· bind variable이 같아야 한다.
Library cache에서 parse할 공간이 모자랄때에는 'oldest cursor'를 close하고, space재 사용된다. cursor에 있는 문장이 다시 요구될때는, 다시 parse한다.
Cursor는 다음의 정보를 저장한다.
· parsed statement
· execution plan
· A list of referenced objects
Parse
syntax를 check한다.
statement가 SQL Area에 있는지 , Referenced object가 같은지 검색한후 같으면, 기존의
shared sql area를 사용하고 , parsed sql문이 없으면 새로운 shrared SQL area를 할당한다.
Execute Phase
Parse tree를 data buffer에 적용한다.
Physical reads or logical read/writes 한다.
Fetch
SELECT 문을 위해 data row를 retrieve한다.
The V$LIBRARYCACHE Table
librarycache는 다음의 것을 포함한다.
· 'SQL AREA'
· 'TABLE?PROCEDURE'
· 'BODY'
· 'TRIGGER'
다음 column은 execution call에서의 library cache miss를 나타낸다.
PINS library cache안의 item이 실행된 횟수
RELOADS execution step에서 library cache miss의 횟수. Oracle이 statement나 block
을 reparse하고, object definition을 reload.
SELECT SUM(pins) "Executions",
SUM(reloads) "Cache Misses while Executing"
FROM v$librarycache;
· 전체 reloads가 0 에 가까와야 한다.
Reducing Library Cache Misses
· library cache에 더 큰 memory를 할당하기 위해 SHARED_POOL_SIZE를 늘려준다.
· 할당된 memory를 사용하기 위해, session마다 허용되는 cursor수를 증가시킬 필요가 있다. OPEN_CURSORS 를 늘려 준다.
· 가능한한 같은 SQL문장을 사용한다.
· CURSOR_SPACE_FOR_TIME option을 사용하여 performance를 높일 수 있는데,이것은 Shared SQL area가 새로운 SQL문을 위한 space를 만들기 위해 언제 deallocate될 수 있는지 결정한다.
FALSE=> 관련된 cursor가 여전히 open이 되 어 있는지 상관없이 deallocate할 수
있다.
TRUE => 관련된 모든 cursor가 close될때까지 , shrared sql 영역과 private sql영역이
deallocate될 수 없다. performance를 조금 향상시킬 수 있다.
Shared pool size와 각 user에 할당된 private sql area 가 충분하지 않으면 TRUE로 setting하지 마라.
Tuning the Dictionary Cache
data dictionary cache의 performace를 monitor, improve할 수 있다.
Examing Data Dictionary Activity
instance startup시에는 dictionary cache에 어떤 data도없으므로, 모든 SQL문장이 Miss를 야기한다. 하지만 점차 data가 cache되어 결국 'steady state'가 된다.
V$ROWCACHE View
다음의 column 들이 data dictionarary cache의 효과적 사용을 나타낸다.
PARAMETER data dictionary의 특정 item을 나타내는 것으로, 'dc_'로 시작
된다.ex) file description (dc_files)
GETS 해당 item을 요구하는 total number
GETMISSES cache miss가 total 횟수
SELECT SUM(GETS) " DATA DICTIONARY GETS"
SUM(GETMISSES) " DATA DICTIONARY GET MISSES"
FROM V$ROWCACHE;
· GET에 대한 GETMISSES의 비육일 10%,15%이하여야 한다.
Tuning the Shared Pool with the Multi-Threaded Server
multi-threaded server architecture에서는 Oracle은 session정보를 user process의 memory가 아닌 shared pool 에 저장한다. Session 정보를 private SQL area와 sort area를 포함한다.
그러므로 multi-threaded server architecture에서는 session정보를 수용하기 위해서 shared pool memory를 증가시켜 준다.
V$SESSSTAT table
다음의 statistic이 session memory 사용을 측정하는데 유효하다.
SESSION MEMORY 각 session별로 할당된 momory bytes
MAX SESSION MEMORY session에 할당되었던 memory의 max값
Querying the V$SESSSTAT
multi-threaded를 하면 shared pool 을 얼마나 더 크게 늘려 주어야 하는지 결정한다.
SELECT SUM(value) || ' bytes' "Total memory for all sessions",
FROM v$sessstat, v$statname
WHERE NAME = 'session pga memory'
AND v$sessstat.statistic# = v$statname. statistic#;
· session이 dedicate server에 연결되어 있다면 memory가 user process의 부분이지만, 그렇지 않으면 shared pool의 부분이다.
Tuning the Buffer Cache
buffer cache peroformance를 monitor하고 향상시킨다.
Examing Buffer Cache Activity
V$SYSSTAT에 정보를 저장하는데 select any table권한이 있는 사람만 query가 가능하다.
DB BLOCK GETS, data request의 총 횟수. 이는 memory buffer를 access한 횟수 CONSISTENT GETS 포함
PHYSICAL READS data를 얻기 위해 disk의 datafile을 access한 횟수
SELECT name, value
FROM v$sysstat
WHERE name in ( 'db block gets','consistent gets','physical reads');
HIT Ratio=1-(physical reads / ( db block gets+consistent gets))
· hit ratio 가 낮다면 DB_BLCOK_BUFFERS를 늘려 주어 data bufffer size를 크게 할 수 있다.
X$KCBRBH Table
cache를 더 크게 함에따라 cache hit의 증가율을 본다.
DB_BLOCK_LRU_EXTENDED_STATISTICS에 정의한 값만큼의 row수에 대한 통계를 모은다.
SELECT 250*"TRUNC(indx/250)+1||' to '||250*(TRUNC(indx/250)+1)
"Interval", SUM(count) "Buffer Cache Hits"
FROM sys.x$kcbrbh
GROUP BY TRUNC(indx/250);
interval Buffer Cache Hits
1 to 250 16080
251 to 500 10950
501 to 750 710
751 to 1000 23140
The X$KCBCBH table
buffer를 감소시킴에 따라 cache hit가 감소하는 비율.
특정 구간 감소때 cache hit비율이 급격히 낮아지면 감소되지 않은 buffer size를 유지할 수 있다.
Tuning I/O
Monitoring Disk Activity
V$FILESTAT view를 query 할 수 있다.
PHYRDS 각 datafile을 읽은 횟수
PHYWRTS 각 datafile을 write한 횟수
SELECT name, phyrds, phywrts
FROM v$datafile df , v$filestat fs
WHER df.file# = fs.file#
· 하나의 disk의 total I/O는 그 disk상의 instance의 모든 datafile의 I/O를 합이다.
disk의 I/O를 분산시키는 것이 좋다.
Distributing I/O
· datafile과 redo log file을 각기 다른 disk에 분산
· table의 data를 각기 다른 disk에 "stripe"
· table과 index를 각기 다른 disk에 분산
· Oracle과 관련되지 않은 I/O감소
Datafile과 Redolog file분산
가능한 file을 분산하고, LGWR가 wating없이 Redo Log File에 access할 수있도록 redo log file을 datafile과 따로 둠
"Striping " table data
CREATE TABLESPACE stripetblspace
DATAFILE 'file_on_disk1' SIZE 500k,
DATAFILE 'file_on_disk2' SIZE 500k,
DATAFILE 'file_on_disk3' SIZE 500k,
DATAFILE 'file_on_disk4' SIZE 500k;
CREATE TABLE stripetable
col1 number(2)
col2 varchar2(10)
TABLESPACE stripetblspace
STORAGE ( INITIAL 495K NEXT 495K MAXEXTENTS 5 PCTINCREASE 0 )
Allocating Space in Data Blocks
best performance를 위해 data block을 어떻게 관리하나?
Migrated and Chained Rows
· Update는 block migration과 chaining을 야기 시킬 수 있다.
· migrated or chained된 row를 query하는데는 더 많은 I/O가 일어나다.
table과 cluster의 chaining을 check하기 위해서는
ANALYZE TABLE tablename LIST CHAINED ROWS option을 사용하고 ,
그 결과는 CHAINED_ROWS(UTLCHAIN.SQL script로 생성)에 저장된다.
Select * from chained_rows where table_name = 'ORDER_HIST';
OWNER_NAME TABLE_NAME CUST HEAD_ROWID TIMESTAMP
-------------------- -------------------- ------ ------------------- -----------------
SCOTT ORDER_HIST .......... 0000186A.0003.0001 04-AUG-92
CREATE TABLE int_order_hist
AS SELECT
FROM order_hist
WHERE ROWID IN
( SELECT head_row_id
FROM chained_rows
WHERE table_name = 'ORDER HIST')
chained row를 모아 임시 Table을 만든 후에 원래 Table에서 chained row record를 삭제 한다.
DELETE FROM order_hist
WHERE ROWID IN
( SELECT head_row_id
FROM chained_rows
WHERE table_name = 'ORDER HIST')
임시 table의 row를 원래 table에 다시 insert한다.
INSERT INTO order_hist
SELECT *
FROM int_order_hist;
T
Tuning Contention
Reduding Contetion for Rollback segments
v$WAITSTAT의 view로 block contention에 대한 정보를 얻을 수 있다.
SYSTEM UNDO HEADER SYSTEM ROLLBACK Segment의 head block을 포
함하는 buffer를 기다린 횟수
SYSTEM UNDO BLOCK SYSTEM ROLLBACK Segment의 blcok을 포함하
는 buffer를 기다린 횟수
UNDO HEADER 기타ROLLBACK Segment의 head block을 포
함하는 buffer를 기다린 횟수
UNDO BLOCK 기타 ROLLBACK Segment의 blcok을 포함하
는 buffer를 기다린 횟수
SELECT class, couunt
FROM v$waitstat
WHERE CLASS IN ( 'system undo header','system undo block','undo header','undo block');
· 각 class의 wait 값의 전체 buffer request(db block gets, consistent get)에 대한 비율이 1%이상이면 더 많은 rollback segment를 고려 해야 한다.
Reducing Contetion for Redo Log Buffer Latches
Space in the Redo Log Buffer
LGWR이 redo log entry를 buffer로 부터 redo log file에 write할때, USER PROCESS는 disk에 씌여진 entry위에 새로운 entry를 copy한다. LGWR은 일반적으로 buffer에 새로운 entry를 위한 space가 항상 있을 만큼 충분히 빨리 write한다.
V$SYSSTAT의 정보를 check할 수 있다.
SELECT name, value
FROM v$sysstat
WHERE name = 'redo log space requests';
· 이 값이 거의 0에 가깝다. 이 값이 지속적으로 증가하는 것은 process가 buffer의 space를 기다린다는 뜻이다. Redo log buffer의 size는 LOG_BUFFERS에 의해 결정된다. DB_BLOCK_BUFFERS의 배가 되는 것이 좋다.
Redo Log Buffer Latches
redo log buffer의 access는 latch에 의해 통제된다. 두 type의 latch는 redo log buffer에대한 access를 control한다.
· redo allocation latch
· redo copy latches
Redo Allocation Latch
Oracle user process는 buffer의 space를 할당하기 위해서 redo allocation latch 를 얻어야 한다. 하나의 redo allocation latch가 있기 때문에 ,한번에 하나의 user process가 space를 할당할 수 있다.
User process는 allocation latch를 보유하는 동안 , entry를 buffer에 copy 할 수 있고 , copy후에 latch를 풀어 준다.
Redo allocation latch에 copy될 수 있는 redo entry의 최대값은
LOG_SMALL_ENTRY_MAX_SIZE 에 의해 정해진다.
Redo Copy Latches
redo entry가 redo allocation latch에 copy하기가 너무 크면 , user process는 redo copy latch를 얻어야 한다. 여러개의 CPU가 있다면 redo log buffer는 다수의 redo copy latch를 가질 수 있고, 이것은 여러 process가 redo log buffer에 동시에 entry를 copy할 수 있도록 한다. Redo copy latch의 수는 LOG_SIMUTANEOUS_COPIES에 의해 결정되고, 그 기본값은 CPU수와 같다.
Examining Redo Log Activity
V$LATCH를 check해서 redo log buffer의 contention을 알 수 있다
다음과 같은 type의 latch request가 있다.
Willing-to-wait latch가 가용하지 않으면 process는 latch를 얻을 때까지 기다
린다.
Immediate latch를 얻지 못하면 기다리지 않고 진행한다.
Will-to-wait 와 관련해서
GETS latch를 성공적으로 얻은 Will-to-wait request수
MISSES willing-to-wait가 처음 latch얻는데 실패한 횟수
SLEEPS Process 가 기다려서 latch를 얻을 때까지 기다린 횟수
· 세번째에 latch를 얻는데 성공했다면, GETS 1, MISSES 1, SLEEPS 2이다.
Immediate requests와 관련해서
IMMEDIATE GETS 각 latch를 성공적으로 얻은 immediate request수
IMMEDIATE MISSES 각 latch를 얻는데 실패한 immediate request수
SELECT ln.name, gets, misses, immediate_gets, immediate_misses
FROM v$latch l, v$latchname ln
WHERE ln.name IN ('redo allocation','redo copy')
AND ln.latch#=l.latch#;
· GETS대 MISSESS가 1%가 넘으면 redo log buffer를 조정할 필요가 있다.
· redo allocation latch의 contention을 줄이기 위해, 하나의 process가 latch를 가질 시간을 단축시킬 수 있다. LOG_SMALL_ENTRY_MAX_SIZE의 값을 줄이면 된다.
· redo cop latch의 contention을 줄이기 위해서는 LG_SIMUTANEOUS_COPIES의 수를 늘이면 된다.
Reducing LRU Latch Contention
SMP(symmetric multiprocessor) machine에서는 LRU latch의 contention이 performance를저하시킬 수도 있다. LRU latch은 buffer cache에서의 buffer의 교체를 control한다.
DB_BLOCK_LRU_LATCHES의 기본값은 CPU 수의 반이다.
Single process mode에서는 Oracle이 자동으로 하나의 LRU latch만 사용한다.
Tuning Sorts
Oracle은 sort에 관한 정보를 V$SYSSTAT에 모은다.
Sorts(memory) disk의 temporary segment에 I/O를 일으키지 않고 sort area에
수용될 만큼 충분히 작은 sort횟수
Sorts(disk) 너무 커서 disk의 temporay segment의 I/O을 일으킨 sort
SELECT name, value
FROM v$sysstat
WHERE name IN ('sorts(memory)','sorts(disk)');
· disk의 횟수가 너무 크면 SORT_AREA_SIZE를 증가시켜 줄 수 있다. Sort size를 크
게 하면, 한번에 sort되는 size는 커서 그 횟수는 줄게 된다. 그 만큼 performance가 좋아 질 수 있지만, private SQL and PL/SQL area의 memory 가 감소될 지도 모르고, paging이나 swaping일어날 수도 있으므로, 그전에 사용가능한 memory 를 확인해야 한다.
· index를 생성할때 table이 오름차순으로 정렬되어 있으면 sort작업 없이 빨리 index를 만들 수 있다.
Create index emp_index ON emp(ename) NOSORT;
.
Recreating Index
index내의 fragment를 없애기 위해, 또는 storage parameter을 조정하기 위해 Index를 새로 만들려고 하면, 기존의 Index를 사용할 수 있다.
기존에 NAME, CUSTID, BALANCE칼럼에 index가 있으면, 그 index를 사용하여 다음의 같은 이름의 INDEX를 만들 수 있다.
Create index I_CUST_CUSTNO on CUST(NAME, CUSTID)
또한 Storage를 변경하고 싶으면 ALTER INDEX REBUILD명령어를 사용할 수 있다.
ALTER INDEX indx_name REBUILD STORAGE ( )...
Reducing Free List Contention
free list에 대한 contention은 buffer cache안에서의 free data block의 contention을 나타낸다.
V$WAITSTAT를 query해 볼 수 있다.
SELECT class, count
FROM V$waitstat
WHERE class = 'free list'
· 전체 request(db_block_gets + consistent_gets)에 대해 free list wait가 1%이상이면 free list를 더 크게 잡아 table을 만들 수 있다.
Tuning Checkpoint
checkpoint는 Oracle이 자동적으로 수행하는 operation이다.
checkpoint는 DBWR로 하여금 File write를 하도록 한다.
checkpoint가 빈번히 일어나면, 그 중간에 변화가 크지 않아 recovery시 시간을 단축시킬 수 있다. LOG_CHECKPOINT_INTEVAL, LOG_CHECK_POINT_TIMEOUT으로 그 빈도를 조절할 수 있다.
Planning for your Database Growth
Growth Profiles
Table Tuning 을 하기 위해서는, data에 의해 사용되는space, data의 지속성 정도(volatility), data증감 비율(growth rate of data)등의 요소를 고려 해 그 성격에 따라 catagory화 할 수 있다.
1.Business reference tables
· business를 위해 reference할 수 있는 data (ex: MTL_SYSTEM_ITEMS)
· growth rate가 작다.
· table이 작으면, full-indexed table이나, cached table 고려한다.
2.Application Reference Tables
· data integrity reference를 위한 valid code list같은 reference data( ex :TEMPERATURE_CODES)
· create table TEMPERATURE_CODES
( temp_code VARCHAR2(1) primary_key,
description VARCHAR2(25));
3) temp_code와 병행하여 description을 자주조회하고자 할때는 concatentaed index를
만들어 full table scan을 막는 것이 좋다.
Create index I_TEMP_CODE$CODE_DESC
on TEMPERATURE_CODES(Temp_code, Description);
2) table을 memory상에 두기 위해, cached table로 mark
alter table TEMPERATURE_CODES cache;
4.Business Transaction Tables
· Application에서 생기는 transaction을 저장
· delete, insert가 많기 때문에 business transaction은 그대로이나, index size가 늘어남 => Index stagnation
5.Temporary/Interface Tables
· Highwatermark (고수위선)이 높아질 가능성이 많아 check할 필요가 있다.
· Highwatermark가 높으면
6) SQL*Loader를 하면 Highwatermark에서부터 data가 insert되는데 space를 아끼기 줄이기 위해서는, truncate를 사용해 highwatermark를 reset한다.
7) Full table scan을 할때 highwatermark까지 읽어, 실제 data를 읽는 시간보다 많이 시간이 소요된다.
Memory and CPU Tuning Issues
Data Access Needs
data를 access하는 형태로 두가지 catagory, OLTP & BATCH로 나눌 수 있다.
1.OLTP Data Access Needs
OLTP Application Performanace를 높이기 위해 세가지 특징을 기억한다.
· data block buffer cache의 data를 읽음으로서 physical IO 감소. 즉 data block buffer의 hit ratio가 높아야 한다.
* hit ratio - data가 disk대신에 memory에서 읽어지는 비율
1) 90% hit ratio => disk에 다시 쓰이기 전에 data block cache에 있는 data가 9번은 읽힌다.
2) fully indexed table 이 full scaned table보다 buffer에 더 오래 지속
· table이고, 변화가 없을때 효율적
3) How To Calculate Hit ratio
v$sysstat view의 정보
Hit ratio = ( consistent gets + db block gets ) - physical reads
-------------------------------------------------------------- * 100
consistent gets + db block gets
4) Hit ratio에 영향을 주는 factor
b) Query문이 SGA에서 object를 찾지 못하면, object에 대한 info를 얻기 위해 recurcive query를 일으킴. table이 작고, 자주 사용되는 것은cache하는 것이 좋다
.(alter table tbl_name cache)
c) table scan을 통해 읽은 block는 data block cache에 오래 지속되지 않는다.
full table scan은 block당 한번의 consistent get을 갖고, index scan은 row당 몇번의 consistent get를 갖는다.
d) temporary segment를 사용하지 않도록 SORT_AREA_SIZE를 충분히 한다.
· shared SQL area가 parsed version 의 SQL commands를 저장하도록 memory를 충분히 하고, 표준화된 SQL문을 사용한다.
· index 를 사용하는condition으로 query를 하면 효과적이다.
2. BATCH Data Access Needs
· multiblock reads
1) db_block_size(바꿀 수 없슴. 바꾸려면 data export후 DB recreate ) 와
db_file_multiblock_read_count(바꿀 수 있음)에 의해 결정
ex) db_block_size = 4k , multiblock_read factor가 8이면,
한번에 buffer에 읽혀 지는 양은 64k이다.
· database block size(default 2k)를 크게 하면 OLTP나 BATCH Job Performance를 높임
Hardware Tuning Issues
1.External Space used by Oracle
· v$log_history를 query를 해서 너무 빈번하게 log switch가 되면 redo lod size늘려준다.
2.Identifying I/O Bottleneck
· v$filestat 로 각각의 file에 대한 IO를 검색해서, load를 분배한다.
· DBWR가 충분히 available하지 않으면. Init.ora 의 DB_WRITERS = 을 조정해 준다.
3.Data Striping
· alter table TEST allocate extent
( datafile '/d02/oracle/DEV/data_1b.dbf');
table의 extent를 특정 datafile에 assign함으로서 data 를stripe할 수 있다.
4.RAID Technologies
raid level로 disk의 physical한 stripe를 구현할 수 있다.
· RAID -1 - 1-to-1 disk mirroring
· RAID - 0+1 - a combination of striping and mirring
· RAID - 5 - Block Striping with distributed parity
Raw Devices and File Systems
Disk drive를partition할때 file system을 만들거나, raw device로 남겨놓을 수 있다.
Managing Space Inside Databases
1.Managing Extents
· extent는 '근접한 datablock' 을 말한다.
· segment당 가질수 있는 extent의 maximum number의 한계는 block size에 따라 다르지만 , 7.3 이후로는 unlimited로 정의 할 수도 있다.
· 한번에 읽을 수 있는 block수는 db_file_multiblock_read_count와 operation system's read buffer limitaion에 의해 결정
· 각 extent의 size는 multiblock size의 multiple이어야 불필요한 Physical I/O가 없다.
그러므로 table의 extent갯수는 extent size가 multi read count의 배수 이기만 하면 full tabel scan performance에 큰 영향 없다.
2.The benefit of Multiple Extents
· 하나의 single extents를 가지면 관리는 쉬우나, unix상에서의 stripe를 쓰지 않으면
I/O operation을 분산시키지 못한다. Parallel Query option도 I/O Bottle을 야기 시킬 수 있다.
3.Managing Tables
· table이 volitile(휘발적)하다면 highwatermark를 관리할 필요가 있다.
* Highwatermark : record가 저장된 highest block수
highwatermark를 관리하는 방법
4) table을 analyze한 후에 table block의 고수위선을 구할 수 있다.
analyze table SALES compute statistics;
· allocated 된 block수 query 한다.
select blocks
from dba_segments
where owner = 'APPDEMO' and segment_name = 'SALES'
· highwatermark위의 block수(emptyp block)을 구한다.
select empty_blocks
from dba_tables
where owner = 'APPDEMO' and segment_name = 'SALES'
HWM = DBA_SEGMENTS.block - DBA_TABLES.Empty_blocks - 1
2) 사용하지 않은 block은 deallocate할 수 있다.
· DBMS_SPACE를 사용
dbms_space.unused_space('APPDEMO','FND_USER','TABLE', OP1, OP2..)
· Alter Table command사용
Alter table XXX deallocate unused ;
4. Inside the Blocks
· block당 row수가 많을 수록 range scan이나 full scan을 하는데 더 적은 block을 읽을 것이다. So row density를 높여야 한다. head부분size는 쉽게 변하지 않으므로, free space를 넓혀야 하는데 그러기 위해서는 큰 block size가 유리하다.
· block의 pctfree space가 충분하지 않으면, row가 다른 block으로 migrate된다.
(default pctused size = 40%)
rew가 chain되면 block I/O가 많다.
5. Clusters
· Cluster의 두가지 목적
1) 하나의 table에 사용되었다면, cluster key로 지정된 것에 의해 오름차수 sort되어 저장
2) 여러개의 table을 같은 block에 저장함으로서, join시에 읽는 block 수를 줄임
· 하지만 clustered table의 transaction은 6~8배정도 늦다. 그래서 transaction이 많은 table에 사용하는 것은 좋지 않다.
6. Managing Indexes
· index values가 update되거나 delete되면 index안의 unusable space가 증가되어 index stagnation을 만든다. 이것을 해결하기 위해서는 index를 drop하고 recreate해야 한다. Fast index rebuild method가 있다.
· Create Index시에 unrecoverable을 사용함으로서 log 를 만들지 않아, 후에 archive를 사용해 recover를 못하는 대신, index와 table creation속도를 높인다.
7. Managing Temporary Segments
· alter tablespace TEMP
default storage (initial 1M next 1M pctincrease 0
minextents 1 maxextents249)
pctincrease 0, initial = next로 정해주는 것이 좋다.
· pctincrease가 0이면 SMON이 자동적으로 인접한 free extents를 coalesce하지 않는다.
Alter tablespace TEMP coalesce
8. Managing Rollback Segments
· transaction's rollback segment entry가 single extent에 저장될 수 없으면 second extent로 wrap된다. Rollback segment의 extent는 cyclically하게 할당되고, 그래서 마지막 extent에서, 첫번째 extent가 active하지 않으면, 첫번째 extent로 가고, 그렇지 않으면 segment가 새로운 extent를 extend한다.
1) rollback segment만을 위한 tablespace를 두고, 각 file에 I/O를 분산하라.
2) 모든 rollback segment size를 같게.
3) initial = next, pctincrease = 0
4) wraping을 막을 수 있도록 extent를 크게
5) optimal에서 extend하지 않도록, optimal을 크게
· 처음 rollback이 optimal을 넘었을때 shrink하지않고, 두번째 optimal setting을 넘었을때, optimal로 shrink.
· optimal setting, extend, wrap, shrink 횟수등을 보기 위해 V$ROLLSTAT 와 V$ROLLNAME을 query한다.
Select name, optsize, shrinks, aveshrink, wraps, extends
from V$ROLLSTAT, V$ROLLNAME
where V$ROLLSTAT.usn = V$ROLLNAME.usn;
· optimal size까지 축소 : alter rollback segment R2 shrink;
· large transaction을 처리하기 위해서는 large rollback이 필요.
그 large rollback을 사용하지 않을 땐,
alter rollback segment R_BIG offline
6.Managing Free Space
· tablespace 가 create될때, 생성된 datafile중 첫번째 block은 head block이고, 나머지 부분은
하나의 free extent가 된다.
· DBA_FREE_SPACE를 query하면 freespace 시작점, byte, block등을 알 수 있다.
10.Defragmentation of segment
· fragmented data segment는 performance problem뿐만 아니라, tablespace안의 space management
상의 문제
· segment가 fragment되면 , initital storage는 수정 불가하므로, 알맞은 storage parameter 를 정해 table을 rebuild. 이를 위해서는 Emport/Import Utility 사용.
1) exp system/manager file=exp.dmp compress=Y grants=Y indexes=Y tables=(HR.T1,HR.T2)
% compress option : 현재 할당된 table space를 check한 후, 그것을 새로운 inital extent로
잡고, export dump file 생성
11) 성공적이면 table drop
12) imp system/manager file=exp.dmp commit=Y buffer=64000 full=Y
=> 새로운 initial extent를 가지고, 새로운 table 생성. inital이 datafile보다 크면 error
13. Defragmentation of Free Extents
· segment가 drop되어, extent가 deallocate되면, Oracle7에서는 SMON이 정기적으로 응집.
(only if pctincrease is not 0)
· if pctincrease = 0, 강제적으로 coalesce
ALTER TABLESPACE tablespace_name COALESCE
Monitoring
· V$SYSSTAT
Query Processing
Transaction Management
Memory and CPU Usage
Database Management
User Statistics
< Query Processing>
'Consistent Gets' : 1 . consistent mode로 data를 retrieve하기 위해 block buffer에 access하는 수.
2. Table access through index시에 수치가 더 높다.
'db block Gets' : 1. 한번의 block get으로 얻을 수 있는 block의 수
3.user-by-user basis에서 statistics를 검색 : V$SESS_IO
'Physical Reads' : 1. Multi block read든지, single block read든 상관 없이 read block I/O마다 count.
4. SORT_DIRECT_WRITE가 TRUE나 AUTO로 set되어 있으면, temporary
segment로 부터의 physical reads는 session의 sort area로 직접 간다.
5. session-by-session별로 hit ratio를 보기 위해서는 V$session과 V$sess_io를 query
'Physical Writes' : 1 disk에 write하기 위한 I/O 수. 쓰여진 block당 count
2. DB_WRITER를 정함으로서 DBWR process수결정
' Write Requests' :1. Write request대 physical write의 비율은 한번의 write에 몇개의 block을 썼는 지
나타냄
' db block changes' :1. SGA의 변화된 block수.( Dirty list에 있는 것을 제외). buffer가 얼마만큼의 비
비율로 dirty되는 지 보여줌
' Consistent Changes' : cursor open되고 난 뒤에 update되어 old version block을 가져 오는 횟수.
'Table Scans(short tables)' and 'Table Scans(long tables)'
: 7.3부터는 cache되었거나, 5개 미만의 block을 가진 table => short table
short table에 buffer cache에 남아있기 쉽다.
'Sorts(Memory)' and 'Sorts(Disk)'
: SORT_AREA_SIZE가 작으면 temporary segment를 사용
Transaction Management
'Opened Cursors Current' : 현재 system에 Open된 cursor수
'Opened Cursors Cumulative'
'User Commits' : commit되면 redo log가 disk에 기록된다. User transaction rate를 반영한다.
'User Rollback'
'Redo Log Space Requests' : 0 이면 LGWR이 db write를 기다린는 뜻으로, LOG_BUFFER를 늘려 주어야 한다.
Memory and CPU Usage
'Recursive Calls' and 'Recursive CPU Usage'
'Data Dictionary Cache Load'
'Data Dictionary Maintenence'
'Object Allocation'
Database Management
'Background Timeouts' : Oracle background process를 위해 issue되는 tomeout수
'Message sent' and 'Message Received ' : back process간의 communication
Message sent => commit시 마다, database connect open시
'Enqueue Tomeouts' 'Enqueue Waits' 'Enqueue Request' 'Enqueue Release'
: locking과 관련. Enqueue request => lock request
enqueue release => time out된 request
enqueue waits > 0 이면, ENQUEUE_RESOURCES 가 부족
'Buffer Waits' : select Class, Count from v$waitstat;
buffer contention type : 'data blocks' => buffer cache안에 modified block이 너무 많아서 이므로,
dbwr 수를 증가
PARALLEL Options
1. Parallel options
task를 수행하기위해 여분의 system resource를 사용해 다수의 process를 일으킴. 다수의 CPU에 process를 배분. Parallel model에서는 database에 의해 multiple query server process가 create되고, query coordinates process가 load를 분배하고, output를 user에게 return한다.
2.Parallel Query Processing for Table scan
user process가 database에 connect할때 background shadow server process가 시작되고, full table scan operation이 요구될때 background shadow process는 coodinator에 -+의해 지정
coordinator는 작업을 분배하는데 , 그 분배는 table의 근접한 block범위에 따라 결정. 그래서 hardware configuration에 따라 같은 CPU or 다수의 CPU 에서 동시에 작업. 그 결과를 coordinator가 모아서 user에서 return.
When All Else Fails: New and Improved Tuning Options
Performance-related Changes
1.Unrecoverable Actions:
· create table as select, create index를 할때 unrecoverable option사용
· redo log entry 기록 안함
2.Dedicated Temporary Tablespaces
· alter tablespace TEMP temporary;
3.Sort Direct Writes
· SORT_DIRECT_WRITES : data를 SGA에 쓰는 대신 temporary segments에 기록
· SORT_WRITE_BUFFER_SIZE : SORT_WRITE_BUFFER_SIZE가 TRUE로 SET되어 있을 경우, MEMORY에 할당된 SORT BUFFER SIZE
· SORT_WRITE_BUFFERS : 할당된 BUFFER 수(2~6)
Oracle 7.3이후로 Object Administraion Changes
1.MAXEXTENTS UNLIMITED
: 7.3이전에는 MAX EXTENTS가 DB BLOCK SIZE에 따라 달랐는데, 7.3부터는 UNLIMITED가 가능하다.
2.DEALLOCATION OF UNUSED SPACE FROM TABLES AND INDEXES
ALTER TABLE COMPETITOR DEALLOCATE UNUSED KEEP 100K;
· UNUSED SPACE를 100K로 유지하라.
· 그전에 우선 DBMS_SPACE.UNUSED_SPACE를 사용해서 UNUSED SPACE를 조사해야 한다.
3.FAST INDEX RECREATION
· 7.3이전에는 INDEX를 새로 만드는 방법이 DROP시키고 새로 만드는 것.
그런 경우 TABLE에 LOCK을 걸어야 한다.
· 7.3에서는 REBUILD를 사용 가능. 현재의 INDEX를 SOURCE로 해서 INDEX RECREATE. PARALLEL이나 UNRECOVERABLE OPTION사용 가능.
· ALTER INDEX COMPANY_PK REBUILD
STORAGE ( INTIAL 100M NEXT 50M PCTINCREASE 0)
TABLESPACE INDX_2;
4.DATABASE ADMINISTRATION CHANGES
· RESIZABLE DATAFILES(AS OF 7.2)
CREATE TABLESPACE DATA
DATAFILE '/DB05/ORACLE/DATA01.DBF' SIZE 200M
AUTOEXTEND ON
NEXT 10M
MAXSIZE 250M;
· SYS.FILEXT$를 QUERY할 수 있다.
5.DYNAMICALLY CHANGEABLE INIT.ORA PARAMETERS
7.3이후로 몇개의 PARAMETER는 DB가 RUNNING중일때 바뀌어 질 수 있다.
SESSION LEVEL은 DB RUNNING일때 변경 가능하고, SYSTEM LEVEL은 DB가
다시 STARTUP되어야 한다.
ALTER SESSION SET PRATITION_VIEW_ENABLED=TRUE;
ALTER SYSTEM SET TIMED_STATISTICS=TRUE;
6.TABLESPACE COALESCE
DBA_FREE_SPACE_COLALESCED 의 PERCENT_BLOCKS_COALESCED COLUMN이 이상적으로는 100%가 되어야 한다.
7.READ-ONLY TABLESPACE
BACKUP & RECOVERY가 필요없는 REFERENCE DATA, HISTORICAL DATA, READ-ONLY LOOKUP DATA가 저장되어야 하고, ROLBACK SEGMENT, SYSTEM-OWNED OBJECTS, TEMPORARY SEGMENTS는 READ-ONLY가 되어서는 안된다.
8.SHRINKING ROLLBACK SEGMENTS
ALTER ROLLBACK SEGMENTS R1 SHRINK TO 15M;
9.STANDBY DATABASE(항상 recovery mode)
PRIMARY DATABASE의 COPY. PRIMARY DATABASE에 의해 GEN된 ARCHIVE REDO LOG가 STANDBY DATABASE로 적용이 되어, DISASTER시에 복구.
ALTER DATABASE CREATE STANDBY CONTROLFILE AS FILENAME;
ALTER DATABASE
ALTER DATABASE RECOVER STANDBY DATABASE;
ALTER DATABASE ACTIVATE STANDBY DATABASE;
RECOVER STNDBY DATABASE;
MEDIA RECOVERY STATUS
V$RECOVERY_FILE_STATUS : RECOVERY DATAFILE, RECOVERY DATABASE를 사용했을 경우 저장
V$RECOVERY_STATUS :
Additional Changes in Oracle 7.3
1.Comparing Replicated Tables
replicated environment 에서 있을때 각각의 replicated table의 copy가 동일한지 check하는 Package DBMS_RECTIFIER_DIFF.
DIFFERNCES procedure는 차이를 분석하고, RECTIFY procedure는 synchronous 하게 만든다.
2.Multi-Threaded Server
몇개의 server process에 많은 clinet process가 연결되어 memory, processing resource를 줄인다. MTS에서 client process는 shared dispatcher process와 연결되고, dispatcher router client는 다음에 avaiable한shared server process를 요구한다. MTS는 client 을 하나나 그이상의 dispatcher, shared server process 와 연결시켜 주는 SQL*NET listener process로 구성되어 있다.
MTS_DISPATCHERS(initials), MTS_MAX_DISPATCHERS(maximum)
MTS_SERVERS(initial server process), MTS_MAX_SERVERS
MTS_LISTENER_ADDRESS : connection info
MTS가 사용되는지 보기 위해서는 BG Process ora_s0, ora_d0가 있는 지 check
MTS를 사용하면 SGA안에서 shared SQL area 증가
SQL TUNING
Explaining SQL Statement Tuning
1.Using the optimizers
· init.ora에서 optimizer_mode를 고치거나,
'alter session set optimizer_goal = COST[ RULE, CHOOSE]' 로 optimizer변경.
· Rule-Based Optimizer
사용 가능하고, access level이 높은 path 로 실행한다.
· Cost-Based Optimizer
analyze를 사용해, object에 대한 statistics를 얻어, 그것을 바탕으로 execution path를 정한다..
1) commands로 table, index의 statistics를 모음.
Analyze table COMPANY compute statistics;
=> table COMPANY 에 대한 통계 를 모은다.
=>DBA_INDEXES 의 distinct_key column은 distinct key의 수 정보를 가지고 있고,
DBA_TABLES의 Num_Rows column은 row의 수에 대한 정보를 가진다.
2) System 에서 제공하는 Package를 이용.
Execute DBMS_UTILITY.ANALYZE_SCHEMA('APPOWNER','COMPUTE'):
3) estimate statistics는 단지 table의 1064 row만 scan.
· hybrid approach
OPTIMIZER_MODE =CHOOSE
2.Execution Plan
· 7.3 이후로는 ' set autotrace on' 을 사용하면, query후에 uxlplan으로 생성된 execution plan이 PLAN_TABLE에 자동으로 execute plan이 입력되도록 되어 있다.
<Cf) plan_table을 create하기 위해 utlxplan.sql script를 돌린다.>
ex)
set autotrace on
select COMPANY_ID, Name
from COMPANY
where State = 'VA'
connect by Parent_company_id = Prior Company_ID
start with Company_ID = 1;
FILTER
CONNECT BY
INDEX UNIQUE SCAN COMPANY_PK
TABLE ACCESS BY ROWID COMPANY
TABLE ACCESS BY ROWID COMPANY
INDEX RANGE SCAN COMPANY$PARENT
3.TKPROF
· server process의 dump file을 tkprof해 볼 수있다.
· init parameter에 SQL_TRACE = TRUE 로 정의하거나,
SQL> ALTER SESSION SET SQL_TRACE = TRUE; 명령어를 사용해 server process의 dump file이
trace되도록 한다. user dump destination에 지정된 곳에 dump file이 생긴다.
Ex)
select * from fnd_user. where user_name = 'AAA ';
$ cd udump
tkprof scm_demo_ora_15884.trc yhl.tkp explain=system/crocus;
Index Scan vs Full Table Scan
1.Indexed Scans
· Indexed scan은 multi-block-read를 못하므로, index 생성 여부를 경우에 따라 판단하라.
<다음의 column만 index create하라.>
· Table Data의 10%미만으로 query를 할때 유용.
· Where절에 다른 function없이 자주 사용될때
· high selectivity(many distinct column)를 가지는 것.
· 자주 reference되고, where조건과 join에 사용되는 것
· NULL value를 많이 가지는 것
<다음의 column 은 index를 create하지 말라.>
· 자주 modified될때
· WHERE 절에서 function이나 operation과 같이 사용될때
where CITY||'X' like '
where City is null
where City!= '
· low selectivity를 가질때
· 작은 table일때
* selectivity
selectivity = distinct row
전체 row
· selectivity가 높을 수록 index를 활용하면 좋다.
· 1000 record가 있는 table에 distinct row가 5개 있으면, 하나의 distinct row를 얻기
위해 index는 200개의 row를 return한다. 이때는 full table scan이 효과적이다.
그래서 index를 create하기 전에 index의 selectivity를 조사하는 것이 좋다.
· anaylze command는 index까지도 analyze하게 해주어, selectivity조사
select Distinct_keys
from USER_INDEXES
where Table_name = 'COMPANY'
and Index_Name = 'COMPANY$CITY';
select Num_Rows
from USER_TABLES
where Table_Name = 'COMPANY';
select Column_name, Num_Distinct
from USER_TAB_COLUMNS
where Table_Name = 'COMPANY'
· Foreign Key Constraints and Updating
foreign key에서 child key를 update하려고 하면, parent key에 lock을 건다. 그러므로 parent table을 동시에 update하고 싶으면, Constraint를 disable하거나, Foreign에 index를 생성시켜 index된 row에 lock을 걸게 한다.
· Index가 사용안되는 상황
· !=
· IS NULL or IS NOT NULL
· NOT IN with a list ( NOT EXIST를 사용하는 것이 더 낫다.
선택된 column이 null value를 포함하면 NOT EXIST를 사용)
· LIKE
2.Full Table Scans
disk를 읽어 buffer에 가지고 오는데, multi block을 한번의 I/O에 가져 올 수 있으므로 작은 table일때는 full table scan이 낫다.
Clusters
Cluster안에 있는 table과는 별도로 생성한다.
1.Index Clusters:
· Common column을 cluster key로 가짐. key에 index를 별도 생성해서 관리.
· Update, insert 보다Query 를 많이 하는 column에 사용.
· 생성시에 "INDEX"라는 글자 적어줌.
· Cluster생성후 cluster index, table생성, index생성후에 insert가 가능
· master, detail 관계의 table중에 master table의 access perfomance가 중요하다거면 cluster에서 제외한다.
· 하나의 table을 cluster함으로서 같은 cluster key를 가진 block을 한꺼번에 access할 수 있다.
· 하나의 row의 size의 합이 여러 block을 차지할만하면 cluster를 피하라.
2. Hash Clusters:
· 같은 hash value를 가진 모든 row를 같은 data block안에 저장.
· data를 찾기 위해 어떤 block을 뒤져야 하는지 결정하기 위해, 정의하는cluster key value위에 hash function을 사용.
· 두개의 key value가 같은 hash value를 생성할때 Collision이 발생한다.
· Cluster key가 unique identifier(empno)이고, 획일적으로 분포되어 있으면, internal hash function을 사용할 수 있다.
· HASH IS를 사용해서 hash할 column이나 expression을 정의할 수 있다.
· If cluster key < HASHKEYS then hash value = cluster key value.
· If cluster key >= HASHKEYS then hash value = MOD(cluster key,HASHKEYS)
· create cluster schema.cluster
pctfree
pctused
size .... ( 하나의 hash key가 block에서 차지하는 bytes)
( size와 kaskkeys는 hash cluster blocks의 data storage에 영향)
index
hashkeys ( cluster key 의 수)
hash is ( internal hash function을 무시하기 위해 정의 (column or expression))
Optimization Approach
1.Join Methods
· Nested Loops
=> join 조건중 하나만 index를 가짐
· Sort-Merge
=> 어떤 쪽도 index를 가지지 않음. Equal condition에서만 사용
· Cluster]
2.Data Retrieval Methods
· Row address => ROWID로 access
· HASH SCAN
· INDEX CLUSER SCAN
· INDEX SCAN
· FULL TABLE SCAN
3.Cost & Rule Based
· data dictionary가 referenced objects에 statistics를 가지고, 그것이 path를 결정하는데
사용되면 cost-based optimization.
· choose로 해 놓으면 statistics가 있으면 cost based고 아니면 rule based.
· Cost-base Example
select ename
from employee
where deptno =3
and empno between 100 and 250
order by ename;
800rows, 10 rows per block.
DB_FILE_MULTIBLOCK_READ_COUNT =8
6 department
empno starts at 1 and is unique
1. Full Table Scan Cost = 80/8 = 10 block visits ( Cost가 선택 )
2. Scan of I_DEPTNO Cost=0.17( 6/80)*80=14block visits
3. Scan of I_EMPNO Cost=0.19 *80 = 16block visits(250~100/800~1)
· Rule-based에선
Single-column index access가 full table scan보다 level이 더 높기 때문에 , index scan한다.
· Guidelines for Query Writing
1. Equal 조건 수행
2. OR (UNION ALL로 transform) 보다는 AND.
3. START WITH, CONNECT BY를 사용하는 것을 피하고,쓰려면 그 column에index를 사용
4. DISTINCT 사용을 조심.
5. SYS.DUAL 사용을 조심. Select sysdate, ename
6. query수를 줄이기 위해 DECODE사용
· Using HINTS
SELECT SOLDIER_NAME
FROM BATTALION
WHERE GENDOR = 'F';
INDEX SCAN : CLUSTER/DISTINCT = 1000/2 =500
FULL TABLE SCAN : TOTAL BLOCKS/MULTI BLOCK READ = 1000/8=125
SYSTEM은 FULL TABLE SCAN을 하려 하나, 1000 ROW중 10개의 'F' ROW를 FETCH하는 것은 인덱스를 사용하는 것이 유리.
SELECT /*+INDEX (battalion I_gendor) */
soldier_name
FROM battalion
WHERE gendor = 'F';
· cost-based approach를 해야 하는데, statistics가 없을때 ALL_ROWS 나 FIRST_ROWS hint사용
SELECT /*+ALL_ROWS */
ename
FROM emp WHERE empno = 7566
· Response time을 optmize하려고 할때
SELECT /*+USE_NL(emp) */ => emp table을 nested loop중 inner table로 사용
dept.loc, emp.ename
from emp, dept
4.Types of PL/SQL Block
· Anonymous Blocks
Database Triggers같은 A.B은 SGA에 보관되지 않고, load될때마다 recompile.
Trigger가 필요시에만 실행되기 위해 WHEN clause사용
· Procedure and Functions
network traffic을 줄이고, execution time을 줄인다.
'OraclE' 카테고리의 다른 글
| control file 재생성 (0) | 2008.12.12 |
|---|---|
| init<SID>.ora와 pfile,spfile의 차이.. (0) | 2008.12.11 |
| How to Relink Oracle Database Software on UNIX (0) | 2008.11.20 |
| relink (0) | 2008.11.20 |
| 옵티마이저와 신나게 노는 방법(엔코아) (0) | 2008.11.06 |