SQL*Plus에서 Autotrace를 이용하여 Oracle Optimizer가 만드는 실행계획과 통계정보를 얻을 수 있는데 DML문의 성능 튜닝을 위한 방법으로 자주 이용된다.
SET AUTOTRACE를 사용하기 위해선 실행 계획용 테이블(PLAN_TABLE)이 존재해야 하며 구문을 활성화하기 위해 SET AUTOTRACE ON, 비활성화 하기 위해 SET AUTOTRACE OFF하면 된다. 참고로 SET AUTOTRACE에서 사용할 수 있는 옵션은 다음과 같다. SET AUTOTRACE OFF : 기본값으로 AUTOTRACE를 수행하지 않는다. SET AUTOTRACE ON EXPLAIN : 실행계획만을 출력 SET AUTOTRACE ON STATISTICS : 통계정보만을 출력 SET AUTOTRACE ON : 옵티마이저의 실행계획과 통계정보, 질의의 실행 결과를 함께 출력 SET AUTOTRACE TRACEONLY [EXPLAIN] : SET AUTOTRACE ON과 유사하지만 옵티마이저의 실행계획과 통계정보만 출력, 쿼리 결과는 출력하지 않는다. 만약 EXPLAIN이라고 하면 실행계획만 출력, STATISTICS라고 하면 통계정보만 출력 한다.
Creating the PLUSTRACE Role
C:\> sqlplus / as sysdba
SQL> @C:\db_home\sqlplus\admin\plustrce.sql SQL> drop role plustrace; drop role plustrace * 1행에 오류: ORA-01919: 롤 'PLUSTRACE'(이)가 존재하지 않습니다 SQL> create role plustrace; 롤이 생성되었습니다. SQL> grant select on v_$sesstat to plustrace; 권한이 부여되었습니다. SQL> grant select on v_$statname to plustrace; 권한이 부여되었습니다. SQL> grant select on v_$session to plustrace; 권한이 부여되었습니다. SQL> grant plustrace to dba with admin option; 권한이 부여되었습니다. SQL> set echo off
Granting the PLUSTRACE Role SQL> grant plustrace to scott; -- AutoTrace를 사용할 계정 권한이 부여되었습니다. SQL> conn scott/tiger -- AutoTrace를 사용할 계정 연결되었습니다.
여기까지 하면 SQL*Plus에서 TRACE 하기위한 plustrace 롤을 SCOTT이 부여 받았고 , 실행계획을 저장할 테이블인 PLAN_TABLE도 생성된다. -- 아래 set autotrace on은 SQL문이 실행될 때 마다 실행 계획과 통계정보, 그리고 SQL의 실행 결과까지 보기 위해서 이며 set autotrace trace라고만 하면 결과는 보여주지 않고 실행 계획과 통계정보만 보여주고 기능을 해제하기 위해서는 OFF라고 하면된다. --쿼리 실행 결과는 안 나온다. SQL>set autotrace trace
Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size 1467 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed
SQL> set autotrace off --Autotrace 기능 해제
SQL> select count(*) from emp;
COUNT(*) ---------- 14
SQL> set timing on; SQL> set autotrace traceonly statistics ; – 결과는 보여주지 않고 통계정보, 쿼리실행시간만 보여준다. SQL> select * from emp where empno = 7369;
경 과: 00:00:00.02
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 985 bytes sent via SQL*Net to client 387 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
SQL> set autotrace on; SQL> select deptno, min(sal) from emp group by deptno having min(sal) > (select min(sal) from emp where deptno = 30) order by deptno; DEPTNO MIN(SAL) ---------- ---------- 10 1300 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 FILTER 2 1 SORT (GROUP BY) 3 2 TABLE ACCESS (FULL) OF 'EMP' 4 1 SORT (AGGREGATE) 5 4 TABLE ACCESS (FULL) OF 'EMP'
Statistics ---------------------------------------------------------- //재귀호출의 횟수, SQL 파싱과 최적화 과정등에서 발생하게 되는 Data Dictionary 조회, // 테이블의 존재나 권한체크(사용자가 테이블에 대해 SELECT 권한 등이 있는지) //하드파싱 시증가. Recursive Call을 최소화 하기 위해서는, 바인드 변수를 사용하여 //하드파싱 발생 자체를 줄이는 것이 효과적이다 191 recursive calls //현재의 블록이 요구된 횟수 //DML or SELECT FOR UPDATE등에 의한 Current Mode로 읽힌 블록 수 5 db block gets //한 블록에 대해 요구된 consistent read 횟수 //SELECT했을 때 읽기 일관성 모드로 읽힌 블록 수(Consistent Mode) 28 consistent gets //디스크로부터 읽어들인 데이터 블록의 총 개수 7 physical reads // 리두로그가 만들어진 크기(SIZE) 0 redo size // Client에 보내진 바이트수 448 bytes sent via SQL*Net to client // Client로부터 받은 바이트 수 503 bytes received via SQL*Net from client //클라이언트에 송/수신된 Net메시지 합계 수 //SQL*Pluss라면 Set Arraysize 설정에 따라 달라진다. 2 SQL*Net roundtrips to/from client // 메모리에서 일어난 소트의 수 3 sorts (memory) // 디스트에서 일어난 소트의 수 0 sorts (disk) //연산을 하는 동안 처리한 ROW의 수 1 rows processed
DB BLOCK GETS vs CONSISTENT GETS
Consistent Mode : 읽기 일관성이 보장된 상태에서 데이터 블록을 읽는 것인데 대부분 SELECT 쿼리가 해당된다. 쿼리가 시작된 시점을 기준으로 일관성있게 읽어 들이며 읽는 도중에 값이 바뀌더라도 쿼리 시작시점의 값으로 읽는다. 그러므로 SCN(System Change Number)의 값을 확인하면서 읽는데 데이터값이 변경된 후 Commit되면 SCN번호가 증가되므로 이 경우에는 Rollback Segment에서 과거의 블록을 읽어들인다. AUTOTRACE에서는 consistent gets로 표시된다.
Current Mode : SQL 시작시점이 아닌 실제 블록을 액세스하는 그 시점의 최종값을 읽어들이는데 AUTOTRACE에서는 db block gets로 표시된다. SELECT FOR UPDATE 또는 DML 또는 디스크 소트가 필요할 정도의 대용량 정렬시 나타난다.
아래의 Update문을 보자.
Update emp Set sal = sal + 100 Where deptno = 10;
이 경우 수정될 데이터를 찾기 위해 먼저 deptno가 10인 데이터를 읽기 일관성 모드(Consistent Mode)로 읽고 실제 값을 변경하는 시점에 Current Mode로 다시 읽어서 그때의 SAL 값에 100을 더해 변경한다. 이때 deptno=10 데이터를 찾을 때 읽어들이는 blocks은 consistent gets에 나타나고, 실제 변경을 하려고 Current Mode로 읽은 블록은 db block gets에 나타난다.
추가로 한가지 더 이해할 부분은 수정할 데이터를 조회하는 Consistent Mode로 읽을 때는 block단위로 I/O가 발생하므로 읽은 블록수가 표시된다. 또 실제 수정하기 위해 Current Mode로 읽을 때는 수정될 데이터를 찾기 위해 블록을 읽으므로 수정될 데이터 건수와 블록을 읽은 수가 비슷하게 나타나며 Consistent Mode로 읽은 블록수는 수정하기 위해 데이터를 읽은 블록보다 대체로 더 큰값을 가진다.
[실습]
SAL 값은 1000 이 됩니다.
세션A에서 update 실행 후 commit 하기 전에 다른 세션B에서 또 update를 실행 했습니다.
세션B는 A가 update를 실행하면서 commit을 하지 않았고 Lock을 걸어서 B는 대기하는데, A가 Commit을 한 이후 B의 update 구문은 실행이 됩니다. B가 update를 실행할 때 7369의 SAL은 900이므로 consistent mode로 900을 읽고 current mode로도 900읽어 1000으로 변경 합니다.
SQL*Plus에서 사용자는 자동으로 Optimizer에서 실행계획과 통계정보를 얻을 수 있다. AUTOTRACE를 사용하며 DML문을 성공적으로 수행 시 만들어지며 DML문의 성능 튜닝을 위한 방법으로 자주 이용 된다.
SET AUTOTRACE를 사용하기 위해선 실행 계획용 테이블(PLAN_TABLE)이 존재해야 하며 구문을 활성화 하기 위해 SET AUTOTRACE ON, 비활성화 하기위해 SET AUTOTRACE OFF하고 하면 됩니다. 참고로 SET AUTOTRACE에서 사용 할 수 있는 옵션은 다음과 같다.
SET AUTOTRACE OFF : 기본값으로 AUTOTRACE를 수행하지 않는다.
SET AUTOTRACE ON EXPLAIN : 실행계획만을 출력
SET AUTOTRACE ON STATISTICS : 통계정보만을 출력
SET AUTOTRACE ON : 옵티마이저의 실행계획과 통계정보, 질의의 실행 결과를 함께 출력
SET AUTOTRACE TRACEONLY [EXPLAIN] : SET AUTOTRACE ON과 유사하지만 옵티마이저의 실행계획과 통계정보를 출력, 만약 EXPAIN이라고 하면 실행계획만 출력 한다.
