고정 헤더 영역
상세 컨텐츠
본문
SYS@ora11g> alter system flush buffer_cache;
System altered.
SYS@ora11g> /
HR@ora11g> alter session set tracefile_identifier = 'hr';
Session altered.
HR@ora11g> alter session set events '10046 trace name context forever, level 8';
Session altered.
HR@ora11g> select /*+ index (i ind_random_ix) */ * from ind_random where owner = 'SYS' and object_name = 'ALL_OBJECTS';
OWNER OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S NAMESPACE
--------- --------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
SYS ALL_OBJECTS
3367 VIEW
25-AUG-13 25-AUG-13 2013-08-25:05:19:03 VALID N N N 1
HR@ora11g> alter session set events '10046 trace name context off';
Session altered.
HR@ora11g> !
[oracle@oracle ~]$ cd $ORACLE_BASE/diag/rdbms/ora11g/ora11g/trace
[oracle@oracle trace]$ ls *hr.trc
ora11g_ora_4371_hr.trc
[oracle@oracle trace]$ tkprof ora11g_ora_4371_hr.trc ora4371.txt
TKPROF: Release 11.2.0.4.0 - Development on Wed Feb 14 13:44:41 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
[oracle@oracle trace]$ cat ora4371.txt
더보기
[oracle@oracle trace]$ cat ora4371.txt
TKPROF: Release 11.2.0.4.0 - Development on Wed Feb 14 13:44:41 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: ora11g_ora_4371_hr.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SQL ID: au5ugynbpmmvu Plan Hash: 780424712
select /*+ index (i ind_random_ix) */ *
from
ind_random where owner = 'SYS' and object_name = 'ALL_OBJECTS'
--- ALL_OBJECTS 에는 인덱스가 안걸려 있으므로 filter
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.01 986 988 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 986 988 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS FULL IND_RANDOM (cr=988 pr=986 pw=0 time=4867 us cost=276 size=98 card=1)
--- 버퍼 피닝이 돌아가고 있기 때문에 I/O 가 그래도 나름 줄어듦.
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
Disk file operations I/O 1 0.00 0.00
db file sequential read 1 0.00 0.00
direct path read 22 0.00 0.00
SQL*Net message from client 2 20.80 20.80
********************************************************************************
SQL ID: 06nvwn223659v Plan Hash: 0
alter session set events '10046 trace name context off'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: 84
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.01 986 988 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.01 0.01 986 988 0 1
--- I/O 986 건 발생 > 1건 찾음
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 20.80 25.47
Disk file operations I/O 1 0.00 0.00
db file sequential read 1 0.00 0.00
direct path read 22 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
2 user SQL statements in session.
0 internal SQL statements in session.
2 SQL statements in session.
********************************************************************************
Trace file: ora11g_ora_4371_hr.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
2 user SQL statements in trace file.
0 internal SQL statements in trace file.
2 SQL statements in trace file.
2 unique SQL statements in trace file.
73 lines in trace file.
20 elapsed seconds in trace file.
HR@ora11g> drop index ind_random_ix;
Index dropped.
HR@ora11g> create index hr.ind_random_ix on hr.ind_random(owner, object_name);
Index created.
SYS@ora11g> alter system flush buffer_cache;
System altered.
SYS@ora11g> /
HR@ora11g> alter session set tracefile_identifier = 'hr';
Session altered.
HR@ora11g> alter session set events '10046 trace name context forever, level 8';
Session altered.
HR@ora11g> select /*+ index(i ind_random_ix) */ * from ind_random i where owner = 'SYS' and object_name = 'ALL_OBJECTS';
OWNER OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S NAMESPACE
--------- --------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
SYS ALL_OBJECTS
3367 VIEW
25-AUG-13 25-AUG-13 2013-08-25:05:19:03 VALID N N N 1
HR@ora11g> alter session set events '10046 trace name context off';
Session altered.
HR@ora11g> !
[oracle@oracle ~]$ cd $ORACLE_BASE/diag/rdbms/ora11g/ora11g/trace
[oracle@oracle trace]$ ls *hr.trc
[oracle@oracle trace]$ ls *hr.trc
ora11g_ora_4727_hr.trc
[oracle@oracle trace]$ tkprof ora11g_ora_4727_hr.trc report.txt sys=no
TKPROF: Release 11.2.0.4.0 - Development on Wed Feb 14 14:00:33 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
[oracle@oracle trace]$ cat report.txt
더보기
[oracle@oracle trace]$ cat report.txt
TKPROF: Release 11.2.0.4.0 - Development on Wed Feb 14 14:00:33 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: ora11g_ora_4727_hr.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
The following statement encountered a error during parse:
select * from ind_random where owner = 'SYS'; and object_name = 'ALL_OBJECTS'
Error encountered: ORA-00911
********************************************************************************
SQL ID: 01fn23ftthzcr Plan Hash: 334209613
select /*+ index(i ind_random_ix) */ *
from
ind_random i where owner = 'SYS' and object_name = 'ALL_OBJECTS'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 4 5 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 4 5 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID IND_RANDOM (cr=5 pr=4 pw=0 time=380 us cost=4 size=98 card=1)
1 1 1 INDEX RANGE SCAN IND_RANDOM_IX (cr=4 pr=3 pw=0 time=340 us cost=3 size=0 card=1)(object id 90029)
pr=3 : 인덱스를 타면서도 피지컬하게 3개 블록 읽음
버퍼피닝이 돌아가지 않았기 때문에 1+1 하면서 메모리에서 하나 읽음
pr=3 에서의 래치는 cache buffers lru chains
인덱스를 걸면 random I/O 수를 확 줄일 수 있다.
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net break/reset to client 2 0.00 0.00
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 49.95 86.41
db file sequential read 4 0.00 0.00
********************************************************************************
SQL ID: 06nvwn223659v Plan Hash: 0
alter session set events '10046 trace name context off'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: 84
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 4 5 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 4 5 0 1
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4 0.00 0.00
SQL*Net message from client 4 49.95 108.26
SQL*Net break/reset to client 2 0.00 0.00
db file sequential read 4 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
2 user SQL statements in session.
0 internal SQL statements in session.
2 SQL statements in session.
********************************************************************************
Trace file: ora11g_ora_4727_hr.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
2 user SQL statements in trace file.
0 internal SQL statements in trace file.
2 SQL statements in trace file.
2 unique SQL statements in trace file.
64 lines in trace file.
36 elapsed seconds in trace file.
# db file sequential read
- single block I/O 방식으로 I/O 요청시에 발생할 수 있는 이벤트
- single block I/O 란: 한 번의 I/O call 에 하나의 데이터블록만 읽어 메모리에 적재한다.
- single block I/O 는 index range scan, index full scan 시에 발생
# db file scattered read
- multi block I/O 방식으로 I/O 요청시에 발생할 수 있는 이벤트
- multi block I/O: 한번의 I/O call 에 인접한 블록들을 같이 메모리에 읽어들인다.
- db_file_multiblock_read_count 파라미터에 설정되어 있는 값이 기준
(db_file_multiblock_read_count 값은 작게는 하지 말자.)
- 보편적으로 OS에 종속된다.
- full table scan, index fast full scan 시에 발생
index fast full scan = multi block I/O
- multi block I/O 시 한번에 읽을 수 있는 블록의 수는 128개이고
블록 사이즈 8K 기준으로 1,048,576 (1m) 씩 읽어들인다.
HR@ora11g> create table c_table nologging as select * from all_objects order by object_id;
Table created.
nologging: 리두가 생기지 않도록 함
인덱스 만들기 1
HR@ora11g> create index c_obj_idx on c_table(object_id);
Index created.
인덱스 만들기 2
HR@ora11g> create index c_obj_name_idx on c_table(object_name);
Index created.
HR@ora11g> select num_rows, blocks, avg_row_len from user_tables where table_name = 'C_TABLE';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
지금은 결과가 하나도 안보이지만
통계 수집을 하고 나서
HR@ora11g> execute dbms_stats.gather_table_stats('hr','c_table')
PL/SQL procedure successfully completed.
위의 문장을 다시 실행하면: 결과가 보인다.
HR@ora11g> select num_rows, blocks, avg_row_len from user_tables where table_name = 'C_TABLE';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
68186 1009 98
'오라클 퍼포먼스 튜닝' 카테고리의 다른 글
| 2024년 2월 15일 (1) | 2024.02.15 |
|---|---|
| 2024년 2월 14일 5교시 + 6교시 (0) | 2024.02.14 |
| 2024년 2월 14일 3교시 (0) | 2024.02.14 |
| 2024년 2월 14일 2교시 (0) | 2024.02.14 |
| 2024년 2월 14일 (발렌타인데이 ㅠㅠ) 1교시 (0) | 2024.02.14 |
