오라클, SQL, SET AUTOTRACE

오라클, SQL, SET AUTOTRACE

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=598

 

오라클, SQL, SET AUTOTRACE

오라클, SQL, SET AUTOTRACE[참고]SET AUTOTRACE SQL*Plus에서 사용자는 자동으로 Optimizer에서 실행계획과 통계정보를 얻을 수 있다. AUTOTRACE를 사용하며 DML문을 성공적으로 수행 시 만들어지며 DML문의 성능 튜닝을 위한 방법으로 자주 이용 된다. SET AUTOTRACE를 사용하기 위해선 실행 계획용 테이블(…

ojc.asia

​

[참고]SET AUTOTRACE

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이라고 하면 실행계획만 출력 한다.

SQL> conn / as sysdba 연결되었습니다. -- 본인의 오라클설치 경로에 맞추도록 하세요. SQL> @d:\app\A\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql SQL> SQL> drop role plustrace; drop role plustrace * 1행에 오류: ORA-01919: 롤 'PLUSTRACE'(이)가 존재하지 않습니다 SQL> create role plustrace; 롤이 생성되었습니다. SQL> 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 SQL> grant plustrace to scott; 권한이 부여되었습니다. SQL> conn scott/tiger 연결되었습니다. -- 본인의 오라클설치 경로에 맞게 고치세요. SQL> @d:\app\A\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxplan.sql 테이블이 생성되었습니다. 여기 까지 하면 SQL*Plus에서 TRACE 하기위한 plustrace 롤을 SCOTT이 부여 받았고, 실행계획을 저장 할 테이블인 PLAN_TABLE도 생성된다. --쿼리 실행 결과는 안 나온다. SET AUTOTRACE TRACEONLY라고 해도 된다. SQL> SET AUTOTRACE TRACEONLY SQL> SET AUTOTRACE TRACE SQL> SELECT * FROM MYEMP1; 10000002 개의 행이 선택되었습니다. 경 과: 00:02:12.39 Execution Plan ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10M| 629M| 25300 (2)| 00:05:04 | | 1 | TABLE ACCESS FULL| MYEMP1 | 10M| 629M| 25300 (2)| 00:05:04 | ---------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 749456 consistent gets 91820 physical reads 0 redo size 815171348 bytes sent via SQL*Net to client 7333846 bytes received via SQL*Net from client 666668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10000002 rows processed SQL> set autotrace off --trace 기능 해제 SQL> SELECT count(*) FROM myemp1; COUNT(*) ---------- 10000002 SQL> set autotrace on; -- 부서별 급여의 최소값을 구하는데 그 최소값이 ‘1’번 부서의 급여최소값 보다 큰 부서 출력 SQL> SELECT DEPTNO, MIN(SAL) FROM MYEMP1 GROUP BY DEPTNO HAVING MIN(SAL) > (SELECT MIN(SAL) FROM MYEMP1 WHERE DEPTNO = '1'); D MIN(SAL) - ---------- 3 3 2 2 Execution Plan ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 8 | 25954 (4)| 00:05:12 | |* 1 | FILTER | | | | | | | 2 | HASH GROUP BY | | 1 | 8 | 25954 (4)| 00:05:12 | | 3 | TABLE ACCESS FULL| MYEMP1 | 10M| 76M| 25215 (1)| 00:05:03 | | 4 | SORT AGGREGATE | | 1 | 8 | | | |* 5 | TABLE ACCESS FULL| MYEMP1 | 2510K| 19M| 25229 (1)| 00:05:03 | ------------------------------------------------------------------------------ Statistics ---------------------------------------------------------- 594 recursive calls 0 db block gets 183794 consistent gets 183640 physical reads 0 redo size 665 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 11 sorts (memory) 0 sorts (disk) 2 rows processed Statistics ---------------------------------------------------------- 594 recursive calls //재귀호출의 횟수 0 db block gets //현재의 블록이 요구된 횟수 (DML or SELECT FOR UPDATE등에 의한 current mode로 읽힌 블록 수) 183794 consistent gets //한 블록에 대해 요구된 consistent read 횟수 (SELECT했을 때 읽기 일관성 모드로 읽힌 블록 수) 183640 physical reads //디스크로부터 읽어들인 데이터 블록의 총 개수 0 redo size 리두로그가 만들어진 크기(SIZE) 665 bytes sent via SQL*Net to client //Client에 보내진 바이트수 520 bytes received via SQL*Net from client //client로부터 받은 바이트 수 2 SQL*Net roundtrips to/from client //클라이언트에 송/수신된 Net메시지 합계 수 11 sorts (memory) //메모리에서 일어난 소트의 수 0 sorts (disk) //디스트에서 일어나 소트의 수 2 rows processed //연산을 하는 동안 처리한 ROW의 수 재귀적 호출이란, 오라클은 내부 처리를 위한 테이블을 관리 하며 이 테이블을 변화 시킬 때 내부적인 SQL문장을 생성하는데 이를 재귀적인 호출이라고 한다. 예를 들면 테이블의 존재나 권한의 체크 등을 위한 SQL등…