sqlplus / as sysdba
SQL>@?/rdbms/admin/utlxplan.sql
SQL> @?/sqlplus/admin/plustrce.sql
SQL>grant plustrace to hr;
SQL>conn hr/hr
SQL>set autotrace on;
explain plan for
select * from employees;
select * from table(dbms_xplan.display);
alter system set timed_statistics = true;
alter system set max_dump_file_size=10;
alter system set user_dump_dest='/u01/app/oracle/admin/orcl/udump';
alter session set sql_trace=true;
select * from employees;
exit
#>tkporf XXX.trace test.prf
#>vi test.prf
Trace 파일은 시스템을 튜닝하는데 필요한 아주 유요한 정보를 제공한다.
● 시스템 전체에 대해서 SQL_TRACE를 수행시키면 전체적인 수행성능은 20%~30% 정도 감소한다.
● SQL문의 실행통계를 Session별로 모아서 Trace 파일을 만든다.
- SQL Parsing, Execute, Fetch를 수행한 횟수
- CPU Time, Elapsed Time(총 경과시간)
- Disk(물리적), Memory(논리적) 읽기를 수행한 횟수
- 추출된 Row의 수
- 라이브러리 캐쉬 miss 수
● SQL_TRACE에 의해서 생성되는 Trace 파일의 확장자는 .trc이다.
-TKPROF 유틸리티로 읽을 수 있는 파일을 생성해야 한다.
 INIT.ORA의 파라미터 변경 |
| ● SQL_TRACE를 생성하려면 먼저 다음과 같은 파라미터들을 INIT.ORA에 지정해야한다. ● CREATE INDEX문에서 NOSORT Option의 사용 ▶ TIMED_STATISTICS=TRUE ● 시간 통계를 모을 수 있게 한다.▶ SQL_TRACE=TRUE ● Session을 종료하는 모든 사용자들의 Trace를 수행한다.▶ USER_DUMP_DEST=directory path ● SQL_TRACE가 Trace 파일을 저장하는 디렉토리를 지정한다.● 보통 시스템 덤프(dump) 디레토리이다.(예; oracle_home/rdbms/log) ▶ MAX_DUMP_SIZE=number ● Trace 파일의 물리적인 크기를 바이트 단위로 지정할 수 있게 한다.● SQL_TRACE는 공간이 부족하면, 완전한 출력이 되지 않는다. |
| INIT.ORA의 파라미터 변경 예제 |
| timed_statistics = true # if you want timed statistics user_dump_dest = /oracle7/app/oracle/product/7.3.2/rdbms/log max_dump_file_size = 10240 # limit trace file size to 5 Meg each |
| SQL_TRACE를 수행하는 방법 |
| ● 오라클 툴과 사용자 세션들에 따라 다르며 오라클 툴이 아닌 경우에는 조금씩 다를 수 있다. ● SQL*Plus에서는 다음과 같이 세션을 변경시켜야 한다. SQL>alter session set sql_trace=true; ● optimizer goal의 변경은 다음과 같다.SQL>alter session set optimizer_goal=rule; ● 생성되는 위치는 init.ora에서 정의한 user_dump_dest 디렉토리에 *.trc형태로 생성된다. |
| 트레이스 파일 보기 |
| - 생성된 트레이스 파일은(*.trc) 바로 볼 수 없다. - 그래서 tkprof라는 유틸리티를 사용하여 생성된 트레이스 파일을 분석이 가능한 형식으로 전환하여 준다. - 이미 생성된 트레이스 파일이나 트레이스 파일을 생성하고 있는 중에도 tkprof를 수행시킬 수 있다. - 트레이스 파일은 SQL문에 대한 실행계획뿐만 아니라 실행시간, 다양한 옵션을 이용하여 분석하기 쉬운 형태 등의 정보를 보여준다. ● tkprof는 다음과 같이 실행시킬 수 있다. - tracefile : 생성된 트레이스 파일명 ● 만약 init.ora 파라미터를 TIMED_STATISTICS=FALSE로 지정했을 때는 수행 중에 액세스된 블록 수를 보여주는 EXEQRY가 가장 실용적이다. ● 예) tkprof ccdb_ora_1124.trc 1124.txt explain=scott/tiger |
| 트레이스 파일의 분석 |
| ● tkprof는 정형화된 리스트(출력파일)를 생성한다. ● 생성된 파일에는 다음과 같은 내용들을 포함하고 있다. call count cpu elapsed disk query current rows ------- -------- -------- ----------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.01 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.02 2 3 0 1 ------- ------- --------- ------------ ---------- ---------- ---------- ---------- total 3 0.01 0.03 2 3 0 1 ▶parse ● SQL문이 파싱되는 단계에 대한 통계이다. 새로 파싱을 했거나, 공유 풀에서 찾아 온 것도 포함된다.● 단, PL/SQL 내에서 반복 수행(Loop)된 SQL이나 PRO*SQL에서 보존커서(Hold cursor)를 지정한 경우에는 한번만 파싱된다. ▶execute ● SQL문의 실행 단계에 대한 통계이다. UPDATE, INSERT, DELETE 문들은 여기에 수행한 결과가 나타난다.● 전체범위 방식으로 처리된 결과가 여러 건인 경우는 주로 여기에 많은 값이 나타나며 fetch에는 아주 적은 값이 나타난다. ▶fetch ● SQL문이 실해되면서 페치된 통계이다.● 부분범위 방식으로 처리된 SELECT문들이나 전체범위 처리를 한 후 한 건을 추출하는 경우(AGGREGATE, 전체집계, Count 등)는 주로 여기에 많은 값들이 나타나고 execute에는 아주 적은 값이 나타난다. ▶count ● SQL문이 파싱된 횟수, 실행된 횟수, 페치가 수행된 횟수이다.▶cpu ● pares, execute, fetch가 실제로 사용한 CPU 시간이다.(1/100초 단위)▶elapsed ● 작업의 시작에서 종료시까지 실제 소요된 총 시간이다.▶disk ● 디스크에서 읽혀진 데이타 블록의 수▶query ● 메모리 내에서 변경되지 않은 블록을 읽거나 다른 세션에 의해 변경 되었으나 아직 Commit되지 않아 복사해 둔 스냅샷 블록을 읽은 블록의 수이다.● SELECT문에서는 거의가 여기에 해당하며 UPDATE, DELETE, INSERT 시에는 소량만 발생한다. ▶current ● 현 세션에서 작업한 내용을 Commit하지 않아 오로지 자신에게만 유효한 블록(Dirty Block)을 액섹스한 블록 수이다.● 주로 UPDATE, INSERT, DELETE 작업시 많이 발생한다. SELECT 문에서는 거의 없으나 아주 적은 양인 경우가 대부분이다. ▶rows ● SQL문을 수행한 결과에 의해 최종적으로 액세스된 로우의 수이다.● 서브쿼리에 의해서 추출된 로우는 제외된다. ● 만약 SUM, AVG, MAX, MIN, COUNT 등의 그룹함수를 사용한 경우라면 큰 의미가 없다. |
| 분석결과의 예 |
| ● execute, fetch의 횟수가 동일하다는 것은 SQL 수행시마다 기본키에 의해 한건씩만 처리되고 있다는 것을 의미한다. - 만약 pares가 1인데 execurte와 fetch가 100이라면 루프가 100번 수행되면서(어프리케인션은 한번만 수행되고 SQL은 루프 내에서 반복수행되었다. - 왜냐하면, 어플리케이션이 여러번 실행되었다면 비록 SQL이 실제 파싱하지 않고 Shared SQL Area에서 찾아 왔다고 해도 parse의 횟수는 증가되기 때문이다) 보관커서 상태의 SQL이 한 건씩을 추출한 상태이다. - 이 경우의 SQL문은 'SELECT ... INTO ...'형식으로 사용되었을 것이다. ● parse가 1이고 execute가 1이며, fetch가 100이라면 SQL은 단 한번 수행되었고(루프 내에서 수행되지 않았음) 페치만 연속해서 100번을 수행한 것이다. ● parse : execute : fetch의 비율은 공통 작업이 여러번 수행되면 그 배수로 나타난다. ● fetch가 10인데 rows가 100이라면 운반단위가 10인 다중처리(Array Processing)를 사용하여 한번 페치마다 10건의 로우가 추출되었음을 의미한다. ● 트레이스의 중간부분에 'Misses im library cache during parse : 1'이라는 문장이 있다. 이것은 공유 SQL 영역에서 파상된 결과를 찾지 못하여 실제 파싱작업을 하게 되었다는 것을 의미한다. ● 최종적으로 추출된 로우의 수는 적으나 많은 CPU 시간이 소요되었다면 이것은 분명히 적절한 액세스 경로로 수해되지 않았음을 의미한다. ● CPU 시간과 ELAPSED 시간의 차이는 적을수록 좋다. 만약 CPU시간에 비해 ELAPSED 시간이 훨씬 많다면, 그 원인은 다음 중 하나일 가능성이 높다. - 이 숫자들이 커다는 것은 메모리 공유영역의 적중률(Hit Ratio)이 낮다는 것을 의미한다. ● Overall totals For All Statements에서 적중률 계산은 다음과 같다. ▶ (Execute 'disk' + Fetch 'Disk')/(Execute 'query' + Execute 'current' + Fetch 'query' + Fetch 'Current') * 100 ● 이 값이 10%이상이라면 메모리 캐쉬에서 데이타를 찾는 비율(적중률)이 너무 낮은 것이다.● 다음은 아주 빠른 응답이 요구되는 온라인 프로세싱 시스템의 경우에서만 적용되는 규칙들이다. - 모든 Execute 'CPU'가 1초보다 적어야 한다. |