| |
1. system/manager로 login 한 후 Database 에 대한 물리적 구조를 확인한다.
$ sqlplus system/manager
SQL> select name from v$datafile
union
select member from v$logfile
union
select name from v$controlfile
union
select value from v$parameter
where (name like 'log_archive_dest%'
and name not like 'log_archive_dest_state%')
or name in ('log_archive_dest','log_archive_duplext_dest') ;
NAME
----------------------------------------------------------------------
----------
/home/human/LABS/users01.dbf
/home/human/oradata/HUMAN/control01.ctl
/home/human/oradata/HUMAN/control02.ctl
/home/human/oradata/HUMAN/control03.ctl
/home/human/oradata/HUMAN/cwmlite01.dbf
/home/human/oradata/HUMAN/drsys01.dbf
/home/human/oradata/HUMAN/example01.dbf
/home/human/oradata/HUMAN/indx01.dbf
/home/human/oradata/HUMAN/isaya01.dbf
/home/human/oradata/HUMAN/log02b.rdo
/home/human/oradata/HUMAN/odm01.dbf
NAME
----------------------------------------------------------------------
----------
/home/human/oradata/HUMAN/query01.dbf
/home/human/oradata/HUMAN/redo01.log
/home/human/oradata/HUMAN/redo01b.rdo
/home/human/oradata/HUMAN/redo02.log
/home/human/oradata/HUMAN/sh_tbs01.dbf
/home/human/oradata/HUMAN/system01.dbf
/home/human/oradata/HUMAN/tools01.dbf
/home/human/oradata/HUMAN/undotbs01.dbf
/home/human/oradata/HUMAN/xdb01.dbf
/home/human/oradata/nanune01.dbf
location=/home/human/arch/log
2. database 의 물리적 file에 대한 I/O 를 확인한다.
SQL> col name format a38
SQL> select phyrds,phywrts,d.name
2 from v$datafile d,v$filestat f
3 where d.file# = f.file#;
PHYRDS PHYWRTS NAME
---------- ---------- --------------------------------------
3570 265 /home/human/oradata/HUMAN/system01.dbf
49 1169 /home/human/oradata/HUMAN/undotbs01.db
f
3 1 /home/human/oradata/HUMAN/cwmlite01.db
f
3 1 /home/human/oradata/HUMAN/drsys01.dbf
3 1 /home/human/oradata/HUMAN/example01.db
f
PHYRDS PHYWRTS NAME
---------- ---------- --------------------------------------
3 1 /home/human/oradata/HUMAN/indx01.dbf
3 1 /home/human/oradata/HUMAN/odm01.dbf
601 353 /home/human/oradata/HUMAN/tools01.dbf
17 10 /home/human/LABS/users01.dbf
3 1 /home/human/oradata/HUMAN/xdb01.dbf
3 1 /home/human/oradata/nanune01.dbf
3 1 /home/human/oradata/HUMAN/isaya01.dbf
3 1 /home/human/oradata/HUMAN/query01.dbf
2214 452 /home/human/oradata/HUMAN/sh_tbs01.dbf
3. V$SYSTEM_EVENT performance view 를 query하여 Redo log file 에 대한 Wait가 있는지 확인한다.
SQL> col event format a30
SQL>
SQL> select event,total_waits,time_waited, average_wait
2 from v$system_event
3 where event='log file sync'
4 or event = 'log file parallel write';
EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
------------------------------ ----------- ----------- ------------
log file parallel write 7619 7 0
log file sync 42 71 2
log file sync에 대한 wait event는 online redo log 나 unbatched commit 을 저장하고 있는 disk의 I/O속도가 slow임을 의미한다.
log file parallel write는 별로 유용하지 않다.
SQL> exit
4. perfstat/perfstat로 login하여 STATSPACK를 사용해 보자
$ sqlplus /nolog
SQL> @LABS/ptw9i/snap.sql
SQL> exit
$ sqlplus hr/hr
SQL> !cat $HOME/LABS/ptw9i/lab04_10.sql
variable n number;
begin
for i in 1..40
loop
select count(*) into :n from hr.temp_emps;
end loop;
end;
/
SQL> @LABS/ptw9i/lab04_10.sql
SQL> exit
$ sqlplus /nolog
SQL> @LABS/ptw9i/snap.sql
SQL> @LABS/ptw9i/spreport.sql
Enter value for begin_snap: 16
Enter value for end_snap: 17
SQL> exit
$ vi sp_16_17.lst
^LTablespace IO Stats for DB: HUMAN Instance: HUMAN Snaps: 16 -17
->ordered by IOs (Reads + Writes) desc
Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
TOOLS
167 1 4.3 1.0 15 0 0 0.0
SYSTEM
11 0 0.0 2.5 3 0 0 0.0
UNDOTBS1
4 0 0.0 1.0 6 0 0 0.0
-------------------------------------------------------------
Production DB의 경우 Disk를 주의 깊게 monitor해서 모든 device간에 workload가 분산 될 수 있도록 해야 한다.
만일 특정 datafile에 대해서는 다른 것들과 구별되어 I/O가 두드러지게 나타난다면 I/O의 양을 줄일 수 있는 방법을 찾아야 한다.
예를 들어 Full table scans의 횟수가 많다면 Index 사용을 고려해 볼 수 있을 것이다.
만일 이후에도 여전히 문제가 발생되고 있다면, 이용도가 낮은 device에 그 datafile을 이동 시키는 것도 고려해 볼 수 있다.
5. system/manager로 login하여 LOG_CHECKPOINT_TO_ALERT = TRUE 로 설정하여 Checkpoint 에 대한 정보를 alert log에 남기고 확인
$ sqlplus system/manager
SQL> alter system set log_checkpoints_to_alert = true;
SQL> exit
- sh/sh 로 login 하여 database에 대한 workload를 유발한다.
$ sqlplus /nolog
SQL> !cat $HOME/LABS/ptw9i/lab06_06.sql
connect system/manager
alter system set log_checkpoint_timeout = 30;
connect sh/sh
update sales
set amount_sold = amount_sold * 1.5
where cust_id > 150000;
rollback;
connect system/manager
alter system set log_checkpoint_timeout = 300000;
connect sh/sh
SQL> @LABS/ptw9i/lab06_06.sql
SQL> exit
Wed Aug 13 10:06:14 2003
Beginning log switch checkpoint up to RBA [0x4.2.10], SCN: 0x0000.001e5429
Thread 1 advanced to log sequence 4
Current log# 1 seq# 4 mem# 0: /home/human/oradata/HUMAN/redo01.log
Current log# 1 seq# 4 mem# 1: /home/human/oradata/HUMAN/redo01b.rdo
Wed Aug 13 10:06:14 2003
ARC0: Evaluating archive log 2 thread 1 sequence 3
ARC0: Beginning to archive log 2 thread 1 sequence 3
Creating archive destination LOG_ARCHIVE_DEST_1: '/home/human/arch/log/3.ARC'
Wed Aug 13 10:06:16 2003
Completed checkpoint up to RBA [0x4.2.10], SCN: 0x0000.001e5429
Wed Aug 13 10:06:24 2003
ARC0: Completed archiving log 2 thread 1 sequence 3
Wed Aug 13 10:06:32 2003
ALTER SYSTEM SET log_checkpoint_timeout=300000 SCOPE=MEMORY;
위 두 시간 차가 checkpoint interval이다.
|