오라클 데이터베이스 성능 튜닝.

Presentation on theme: "오라클 데이터베이스 성능 튜닝."— Presentation transcript:

1 오라클 데이터베이스 성능 튜닝

2 튜닝 도구 튜닝 도구 EXPLAIN PLAN DBMS_XPLAN AUTOTRACE V$SQL_PLAN SQL*TRACE
TKPROF AWR

3 튜닝 도구 사용을 위한 실습 준비 실습용 계정 생성 및 권한 부여 scott 유저 생성
C:\Documents and Settings\Administrator>sqlplus / as sysdba SQL*Plus: Release Production on Copyright (c) 1982, 2005, Oracle. All rights reserved. 다음에 접속됨: Oracle Database 10g Enterprise Edition Release Production With the Partitioning, OLAP and Data Mining options SQL> CREATE USER SCOTT IDENTIFIED BY TIGER 2 DEFAULT TABLESPACE USERS 3 TEMPORARY TABLESPACE TEMP 4 QUOTA UNLIMITED ON USERS 5 ACCOUNT UNLOCK; 사용자가 생성되었습니다. SQL> GRANT CONNECT, RESOURCE TO SCOTT; 권한이 부여되었습니다. SQL>

4 튜닝 도구 사용을 위한 실습 준비 테스트용 데이터 생성 샘플 데이터 로드
-- NEW_SCOTT_10.DMP 파일을 C:\Documents and Settings\Administrator 디렉토리로 Copy 하세요 SQL> host Microsoft Windows XP [Version ] (C) Copyright Microsoft Corp. C:\Documents and Settings\Administrator>imp system/oracle fromuser=scott touser=scott file=NEW_SCOTT_10.DMP log=scott_imp.log Import: Release Production on Copyright (c) 1982, 2005, Oracle. All rights reserved. 다음에 접속됨: Oracle Database 10g Enterprise Edition Release Production With the Partitioning, OLAP and Data Mining options 엑스포트 파일은 규정 경로를 거쳐 EXPORT:V 에 의해 생성되었습니다 KO16KSC5601 문자집합과 AL16UTF16 NCHAR 문자 집합에 임포트가 완성되었습니다 KO16MSWIN949 문자 집합을 사용해서 서버를 임포트합니다 (문자집합 변환이 가능합니다) KO16MSWIN949 문자 집합을 사용해서 클라이언트를 엑스포트합니다 (문자집합 변환이 가능합니다) . SCOTT 객체를 SCOTT(으)로 임포트하는 중입니다 IMP-00008: 엑스포트 파일에 인식할 수 없는 명령이 있습니다:

5 튜닝 도구 사용을 위한 실습 준비 테스트용 데이터 생성 샘플 데이터 로드
. . 테이블 "ACCOUNT"(를)을 임포트 중 행이 임포트되었습니다 . . 테이블 "BIG_DEPT"(를)을 임포트 중 행이 임포트되었습니다 . . 테이블 "BIG_EMP"(를)을 임포트 중 행이 임포트되었습니다 . . 테이블 "DEPT"(를)을 임포트 중 행이 임포트되었습니다 . . 테이블 "EMP"(를)을 임포트 중 행이 임포트되었습니다 . . 테이블 "LARGE_ACCOUNT"(를)을 임포트 중 행이 임포트되었습니다 . . 테이블 "LARGE_DEPT"(를)을 임포트 중 행이 임포트되었습니다 . . 테이블 "LARGE_EMP"(를)을 임포트 중 행이 임포트되었습니다 . . 테이블 "PLAN_TABLE"(를)을 임포트 중 행이 임포트되었습니다 . . 테이블 "S_CHANGGO"(를)을 임포트 중 행이 임포트되었습니다 . . 테이블 "S_CHULHAJISI"(를)을 임포트 중 행이 임포트되었습니다 . . 테이블 "S_GOGAEK"(를)을 임포트 중 행이 임포트되었습니다 . . 테이블 "S_JAEGO"(를)을 임포트 중 행이 임포트되었습니다 . . 테이블 "S_MAECHE"(를)을 임포트 중 행이 임포트되었습니다 . . 테이블 "S_PROMOTION"(를)을 임포트 중 행이 임포트되었습니다 . . 테이블 "S_SANGPUM"(를)을 임포트 중 행이 임포트되었습니다 . . 테이블 "S_SPINOUT"(를)을 임포트 중 행이 임포트되었습니다 . . 테이블 "S_SUJU"(를)을 임포트 중 행이 임포트되었습니다 . . 테이블 "S_SUJU_DTL"(를)을 임포트 중 행이 임포트되었습니다 . . 테이블 "S_ZIPCODE"(를)을 임포트 중 행이 임포트되었습니다 C:\Documents and Settings\Administrator>

6 튜닝 도구 사용을 위한 실습 준비 PLUSTRACE 설정 PLUSTRACE 설정
C:\Documents and Settings\Administrator>sqlplus / as sysdba SQL*Plus: Release Production on Copyright (c) 1982, 2005, Oracle. All rights reserved. 다음에 접속됨: Oracle Database 10g Enterprise Edition Release Production With the Partitioning, OLAP and Data Mining options SQL> show user USER은 "SYS"입니다 SQL> SQL> drop role plustrace; drop role plustrace * 1행에 오류: ORA-01919: 롤 'PLUSTRACE'(이)가 존재하지 않습니다 SQL> create role plustrace; 롤이 생성되었습니다.

7 튜닝 도구 사용을 위한 실습 준비 PLUSTRACE 설정 PLUSTRACE 설정
SQL> grant select on v_$sesstat to plustrace; 권한이 부여되었습니다. SQL> grant select on v_$statname to plustrace; SQL> grant select on v_$mystat to plustrace; SQL> grant plustrace to dba with admin option; SQL> SQL> set echo off SQL> GRANT PLUSTRACE TO SCOTT;

8 튜닝 도구 사용을 위한 실습 준비 Plan_table 생성 Plan_table 생성
SQL> conn scott/tiger 연결되었습니다. SQL> DROP TABLE PLAN_TABLE PURGE; 테이블이 삭제되었습니다. 테이블이 생성되었습니다.

9 튜닝 도구 사용을 위한 실습 준비 Plan_table 생성 Plan_table 구조 SQL> DESC PLAN_TABLE
이름 널? 유형 STATEMENT_ID VARCHAR2(30) PLAN_ID NUMBER TIMESTAMP DATE REMARKS VARCHAR2(4000) OPERATION VARCHAR2(30) OPTIONS VARCHAR2(255) OBJECT_NODE VARCHAR2(128) OBJECT_OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) OBJECT_ALIAS VARCHAR2(65) OBJECT_INSTANCE NUMBER(38) OBJECT_TYPE VARCHAR2(30) OPTIMIZER VARCHAR2(255) SEARCH_COLUMNS NUMBER ID NUMBER(38) PARENT_ID NUMBER(38) DEPTH NUMBER(38) POSITION NUMBER(38) COST NUMBER(38) CARDINALITY NUMBER(38) BYTES NUMBER(38) OTHER_TAG VARCHAR2(255) PARTITION_START VARCHAR2(255) PARTITION_STOP VARCHAR2(255) PARTITION_ID NUMBER(38) OTHER LONG DISTRIBUTION VARCHAR2(30) CPU_COST NUMBER(38) IO_COST NUMBER(38) TEMP_SPACE NUMBER(38) ACCESS_PREDICATES VARCHAR2(4000) FILTER_PREDICATES VARCHAR2(4000) PROJECTION VARCHAR2(4000) TIME NUMBER(38) QBLOCK_NAME VARCHAR2(30) OTHER_XML CLOB

10 튜닝 도구 EXPLAIN PLAN EXPLAIN PLAN 사용법 EXPLAIN PLAN SET STATEMENT_ID = ‘이름’ FOR [분석 대상 SQL] 출력 예 PLAN_TABLE_OUTPUT Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | 1131K| (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| BIG_EMP | | 1131K| (0)| 00:00:01 | 개의 행이 선택되었습니다.

11 튜닝 도구 EXPLAIN PLAN EXPLAIN PLAN 사용시 확인되는 정보 옵티마이저의 유형 생성된 실행계획 COST 정보 실행시에 필요한 여러 리소스에 대한 정보

12 튜닝 도구 EXPLAIN PLAN explain plan 사용 실습 SQL> explain plan
2 set statement_id='test' 3 into plan_table 4 for 5 select * from big_emp; 해석되었습니다. -- 마지막 실행된 explain plan을 자동으로 분석해 줌 PLAN_TABLE_OUTPUT Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | 1131K| (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| BIG_EMP | | 1131K| (0)| 00:00:01 | 8 개의 행이 선택되었습니다. SQL>

13 튜닝 도구 EXPLAIN PLAN PLAN_TABLE을 직접 조회하는 방법 SQL> SET LINESIZE 120
SQL> COL OPERATION FOR A50 SQL> COL OPTIONS FOR A20 SQL> COL OBJECT_NAME FOR A10 SQL> SELECT LPAD(' ', 2 * LEVEL) || OPERATION || DECODE(ID, 0, ' Cost=' || POSITION) OPERATION ,OPTIONS ,OBJECT_NAME 4 FROM PLAN_TABLE 5 CONNECT BY PRIOR ID = PARENT_ID 6 START WITH ID = 0 7 ORDER BY ID; OPERATION OPTIONS OBJECT_NAM SELECT STATEMENT Cost=41 TABLE ACCESS FULL BIG_EMP SQL>

14 튜닝 도구 EXPLAIN PLAN DBMS_XPLAN을 사용하는 방법 select plan_table_output from table(dbms_xplan.display('plan_table','DB1')); 출력 예 PLAN_TABLE_OUTPUT Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | | (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| BIG_EMP | | | (0)| 00:00:01 | Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT 1 - filter("DEPTNO"=10) 13 개의 행이 선택되었습니다. SQL>

15 튜닝 도구 DBMS_XPLAN DBMS_XPLAN을 사용하는 실습
SQL> select plan_table_output from table(dbms_xplan.display('plan_table','test')); PLAN_TABLE_OUTPUT Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | | (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| BIG_EMP | | | (0)| 00:00:01 | Predicate Information (identified by operation id): 1 - filter("DEPTNO"=10) 13 개의 행이 선택되었습니다. SQL>

16 튜닝 도구 테스트 SQL> EXPLAIN PLAN 2 SET STATEMENT_ID = 'DB1' 3 FOR
SELECT * FROM BIG_EMP WHERE DEPTNO = 10; 해석되었습니다. PLAN_TABLE_OUTPUT Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | | (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| BIG_EMP | | | (0)| 00:00:01 | Predicate Information (identified by operation id): 1 - filter("DEPTNO"=10) 13 개의 행이 선택되었습니다. SQL>

17 튜닝 도구 테스트 SQL> SET LINESIZE 120 SQL> COL OPERATION FOR A50
SQL> COL OPTIONS FOR A20 SQL> COL OBJECT_NAME FOR A10 SQL> SELECT LPAD(' ', 2 * LEVEL) || OPERATION || DECODE(ID, 0, ' Cost=' || POSITION) OPERATION ,OPTIONS ,OBJECT_NAME 4 FROM PLAN_TABLE 5 WHERE STATEMENT_ID = 'DB1' 6 CONNECT BY PRIOR ID = PARENT_ID 7 START WITH ID = 0 8 ORDER BY ID; OPERATION OPTIONS OBJECT_NAM SELECT STATEMENT Cost=41 TABLE ACCESS FULL BIG_EMP SQL>

18 튜닝 도구 테스트 SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'DB1')); PLAN_TABLE_OUTPUT Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | | (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| BIG_EMP | | | (0)| 00:00:01 | Predicate Information (identified by operation id): 1 - filter("DEPTNO"=10) 13 개의 행이 선택되었습니다.

19 튜닝 도구 테스트 -- 인덱스 생성 후의 변경 실행계획 SQL> CREATE INDEX I_BIG_EMP_EMPNO
2 ON BIG_EMP(DEPTNO); 인덱스가 생성되었습니다. SQL> EXPLAIN PLAN 2 SET STATEMENT_ID = 'DB2' 3 FOR SELECT * FROM BIG_EMP WHERE DEPTNO = 10; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'DB2')); PLAN_TABLE_OUTPUT Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | | (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| BIG_EMP | | | (0)| 00:00:01 | Predicate Information (identified by operation id): 1 - filter("DEPTNO"=10) 13 개의 행이 선택되었습니다. SQL>

20 튜닝 도구 테스트 SQL> EXPLAIN PLAN 2 SET STATEMENT_ID = 'DB3' 3 FOR
SELECT /*+ INDEX(BIG_EMP I_BIG_EMP_EMPNO) */ * FROM BIG_EMP WHERE DEPTNO = 10; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'DB3')); PLAN_TABLE_OUTPUT Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | 208K| (0)| 00:00:12 | | 1 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | | 208K| (0)| 00:00:12 | |* 2 | INDEX RANGE SCAN | I_BIG_EMP_EMPNO | | | (0)| 00:00:01 | Predicate Information (identified by operation id): 2 - access("DEPTNO"=10) 14 개의 행이 선택되었습니다. SQL>

21 튜닝 도구 AUTOTRACE AUTOTRACE 사용 방법 autotrace 명령어는 SQL*PLUS의 환경 설정 명령어임 SQL*PLUS 상에서 SET 명령어를 통해 설정 가능함 SET AUTOTRACE [ ON | OFF | TRACE{ONLY}] 명령어를 통해 설정 가능함 출력 예 Execution Plan Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | 208K| (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| BIG_EMP | | 208K| (0)| 00:00:01 | Predicate Information (identified by operation id): 1 - filter("DEPTNO"=10) Statistics 1 recursive calls 0 db block gets 539 consistent gets 0 physical reads 0 redo size bytes sent via SQL*Net to client 4290 bytes received via SQL*Net from client 357 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 5336 rows processed

22 튜닝 도구 AUTOTRACE AUTOTRACE을 사용하는 실습 SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT * 2 FROM BIG_EMP 3 WHERE DEPTNO = 10; 5336 개의 행이 선택되었습니다. Execution Plan Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | 208K| (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| BIG_EMP | | 208K| (0)| 00:00:01 | Predicate Information (identified by operation id): 1 - filter("DEPTNO"=10) Statistics 1 recursive calls 0 db block gets 539 consistent gets 0 physical reads 0 redo size bytes sent via SQL*Net to client 4290 bytes received via SQL*Net from client 357 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 5336 rows processed SQL>

23 튜닝 도구 AUTOTRACE AUTOTRACE을 사용하는 실습
SQL> SELECT /*+ INDEX(BIG_EMP I_BIG_EMP_EMPNO) */ * 3 FROM BIG_EMP 4 WHERE DEPTNO = 10; 5336 개의 행이 선택되었습니다. Execution Plan Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | 208K| (0)| 00:00:12 | | 1 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | | 208K| (0)| 00:00:12 | |* 2 | INDEX RANGE SCAN | I_BIG_EMP_EMPNO | | | (0)| 00:00:01 | Predicate Information (identified by operation id): 2 - access("DEPTNO"=10) Statistics 1 recursive calls 0 db block gets 893 consistent gets 0 physical reads 0 redo size bytes sent via SQL*Net to client 4290 bytes received via SQL*Net from client 357 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 5336 rows processed SQL>

24 튜닝 도구 V$SQL_PLAN V$SQL_PLAN 오라클의 Data Dictionary 중에 하나임 오라클의 SGA 영역 중 Shared Pool 메모리에 저장된 SQL에 관한 정보임 V$SQL_PLAN을 활용하는 SQL SELECT ID ,LPAD(' ', DEPTH) || OPERATION OPERATION ,OPTIONS ,OBJECT_NAME ,OPTIMIZER ,COST FROM V$SQL_PLAN WHERE HASH_VALUE = &1 AND ADDRESS = '&2' START WITH ID = 0 CONNECT BY (PRIOR ID = PARENT_ID AND PRIOR HASH_VALUE = HASH_VALUE AND PRIOR CHILD_NUMBER = CHILD_NUMBER) ORDER SIBLINGS BY ID, POSITION;

25 튜닝 도구 V$SQL_PLAN V$SQL_PLAN을 사용한 실습 SQL> conn system/oracle
연결되었습니다. SQL> SELECT ADDRESS ,HASH_VALUE ,SUBSTR(SQL_TEXT,1,30) STATEMENT 4 FROM V$SQL 5 WHERE UPPER(SQL_TEXT) LIKE '%BIG_EMP%DEPT%'; ADDRESS HASH_VALUE STATEMENT 6ACDB SELECT /*+ INDEX(BIG_EMP I_BIG

26 튜닝 도구 V$SQL_PLAN V$SQL_PLAN을 사용한 실습 SQL> COL OPERATION FOR A30
SQL> COL OPTIONS FOR A30 SQL> COL OBJECT_NAME FOR A10 SQL> COL OPTIMIZER FOR A10 SQL> SELECT ID ,LPAD(' ', DEPTH) || OPERATION OPERATION ,OPTIONS ,OBJECT_NAME ,OPTIMIZER ,COST 7 FROM V$SQL_PLAN 8 WHERE HASH_VALUE = &1 9 AND ADDRESS = '&2' 10 START WITH ID = 0 11 CONNECT BY (PRIOR ID = PARENT_ID AND PRIOR HASH_VALUE = HASH_VALUE AND PRIOR CHILD_NUMBER = CHILD_NUMBER) 14 ORDER SIBLINGS BY ID, POSITION; 1의 값을 입력하십시오: 구 8: WHERE HASH_VALUE = &1 신 8: WHERE HASH_VALUE = 2의 값을 입력하십시오: 6ACDB624 구 9: AND ADDRESS = '&2' 신 9: AND ADDRESS = '6ACDB624‘ ID OPERATION OPTIONS OBJECT_NAM OPTIMIZER COST 0 SELECT STATEMENT ALL_ROWS 1 TABLE ACCESS BY INDEX ROWID BIG_EMP 2 INDEX RANGE SCAN I_BIG_EMP_EMPNO SQL> show user USER은 "SYSTEM"입니다 SQL>

27 튜닝 도구 결과 분석 각 도구를 사용한 분석 SQL> CONN SCOTT/TIGER 연결되었습니다.
SQL> EXPLAIN PLAN 2 SET STATEMENT_ID = 'ANAL2' 3 FOR SELECT EMPNO ,ENAME ,JOB ,SAL ,DNAME FROM EMP E ,DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.EMPNO = 7369; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'ANAL2')); PLAN_TABLE_OUTPUT Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | | (20)| 00:00:01 | |* 1 | HASH JOIN | | | | (20)| 00:00:01 | |* 2 | TABLE ACCESS FULL| EMP | | | (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| DEPT | | | (0)| 00:00:01 |

28 튜닝 도구 결과 분석 각 도구를 사용한 분석 PLAN_TABLE_OUTPUT
Predicate Information (identified by operation id): 1 - access("E"."DEPTNO"="D"."DEPTNO") 2 - filter("E"."EMPNO"=7369) 16 개의 행이 선택되었습니다. SQL> CREATE UNIQUE INDEX PK_EMP ON EMP(EMPNO); 인덱스가 생성되었습니다. SQL> ALTER TABLE EMP 2 ADD CONSTRAINT PK_EMP PRIMARY KEY(EMPNO) 3 USING INDEX; 테이블이 변경되었습니다. SQL> CREATE UNIQUE INDEX PK_DEPT ON DEPT(DEPTNO); SQL> ALTER TABLE DEPT 2 ADD CONSTRAINT PK_DEPT PRIMARY KEY(DEPTNO) SQL>

29 튜닝 도구 결과 분석 각 도구를 사용한 분석 SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'ANAL1')); PLAN_TABLE_OUTPUT Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | | (0)| 00:00:01 | | 1 | NESTED LOOPS | | | | (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | | | (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | PK_EMP | | | (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| DEPT | | | (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | PK_DEPT | | | (0)| 00:00:01 | Predicate Information (identified by operation id): 3 - access("E"."EMPNO"=7369) 5 - access("E"."DEPTNO"="D"."DEPTNO") 18 개의 행이 선택되었습니다. SQL>

30 튜닝 도구 SQL*TRACE SQL*TRACE 사용시 확인되는 정보 옵티마이저의 유형 생성된 실행계획 COST 정보 실행시에 소모된 여러 리소스에 대한 정보 SQL 수행 과정(PARSE, EXECUTE, FETCH)의 각 과정에서 소모된 시간, Block 개수, 진행된 Row의 수 등의 실제 수행시의 정보를 제공함

31 튜닝 도구 SQL*TRACE SQL*TRACE 사용하기 위해 주로 설정하는 초기화 값 몇가지 Parameter 를 설정하여 더욱 안정되게 진행함 TIMED_STATISTICS, SQL_TRACE, USER_DUMP_DEST, MAX_DUMP_FILE_ SIZE 설정값 확인 및 TRACE 준비 SQL> CONN / AS SYSDBA 연결되었습니다. SQL> SHOW USER USER은 "SYS"입니다 SQL> SET LINESIZE 120 SQL> SHOW PARAMETER TIMED_STATISTICS NAME TYPE VALUE timed_statistics boolean TRUE SQL> SHOW PARAMETER USER_DUMP_DEST user_dump_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP SQL> GRANT ALTER SESSION TO SCOTT; 권한이 부여되었습니다. SQL>

32 튜닝 도구 SQL*TRACE SQL> CONN SCOTT/TIGER 연결되었습니다.
SQL> ALTER SESSION SET SQL_TRACE = TRUE; 세션이 변경되었습니다. SQL> SELECT EMPNO ,ENAME ,JOB ,SAL ,DNAME 6 FROM EMP E ,DEPT D 8 WHERE E.DEPTNO = D.DEPTNO 9 AND E.EMPNO = 7369; SQL> exit Oracle Database 10g Enterprise Edition Release Production With the Partitioning, OLAP and Data Mining options에서 분리되었습니다. C:\Documents and Settings\Administrator>cd C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP C:\oracle\product\10.2.0\admin\ORCL\udump>tkprof orcl_ora_26544.trc T26544.TKF SYS=NO TKPROF: Release Production on Copyright (c) 1982, 2005, Oracle. All rights reserved. C:\oracle\product\10.2.0\admin\ORCL\udump>

33 튜닝 도구 TKPROF SQL*TRACE를 통해서 생성된 RAW 트레이스 파일을 사람이 분석하기 용이한 Format으로 변경해 주는 Tool TKPROF를 통해서 분석된 내용 실행된 SQL문 구문분석 수행을 위한 CPU 시간 디스크로부터 읽은 Block 수 메모리로부터 읽은 Block 수 조건을 만족하는 Row 수 옵티마이저의 유형 분석된 실행 계획 실행계획의 비용 계산 결과

34 튜닝 도구 TKPROF 사용 문법 TKPROF [트레이스 파일명] [분석결과 파일명] [옵션] 옵션 OPTIONS 설 명
EXPLAIN=[table명] 실행계획 정보를 함께 보여 줌(해당 사용자에는 PLAN_TABLE이 생성되어 있어야 함 PRINT=n 트레이스 파일 내에 분석된 SQL 문의 수를 n 만큼 제한하여 출력할 때 사용 RECORD=[파일명] 트레이스 파일 내에 분석된 SQL문을 지정한 파일에 저장해 줌 SORT=[옵션] 트레이스 파일 내에 분석된 SQL 문장을 지정한 옵션에 의해 분류(Sorting)하여 출력함 SYS=[NO] 트레이스 파일 내에 생성된 SQL 문장 중에 오라클 서버가 내부적인 작업을 위해 실행한 SQL 문을 출력시 포함할지를 결정함

35 튜닝 도구 TKPROF TKPROF를 통해 분석된 파일의 내용
TKPROF: Release Production on Copyright (c) 1982, 2005, Oracle. All rights reserved. Trace file: orcl_ora_26544.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 ALTER SESSION SET SQL_TRACE = TRUE call count cpu elapsed disk query current rows Parse Execute Fetch total Misses in library cache during parse: 0 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 62

36 튜닝 도구 TKPROF TKPROF를 통해 분석된 파일의 내용 SELECT EMPNO ,ENAME ,JOB ,SAL
,DNAME FROM EMP E ,DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.EMPNO = 7369 call count cpu elapsed disk query current rows Parse Execute Fetch total Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 62 Rows Row Source Operation 1 NESTED LOOPS (cr=4 pr=0 pw=0 time=134 us) 1 TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=65 us) 1 INDEX UNIQUE SCAN PK_EMP (cr=1 pr=0 pw=0 time=31 us)(object id 56172) 1 TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=0 pw=0 time=39 us) 1 INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=15 us)(object id 56173) ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS Execute total Misses in library cache during execute: 1

37 튜닝 도구 TKPROF TKPROF를 통해 분석된 파일의 내용
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows Parse Execute Fetch total 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: orcl_ora_26544.trc Trace file compatibility: 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. 51 lines in trace file. 5 elapsed seconds in trace file.

38 튜닝 도구 AWR AWR(Automatic Workload Repository)는 현재 시점의 오라클의 상태에 대한 정보를 Disk에 기록한 자료임 AWR의 특징 Default로 1시간에 한번씩 DB의 상태정보를 기록함 기록된 정보를 통해서 시간의 구간 별로 Report 출력이 가능함 성능 분석의 자료로 사용 가능함 Active Session 대한 샘플링 데이터에 대한 데이터도 기록함 Report 생성용 SQL Script를 오라클에서 제공함

39 튜닝 도구 AWR AWR Report 생성 C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN>sqlplus system/oracle SQL*Plus: Release Production on Copyright (c) 1982, 2005, Oracle. All rights reserved. 다음에 접속됨: Oracle Database 10g Enterprise Edition Release Production With the Partitioning, OLAP and Data Mining options

40 튜닝 도구 AWR AWR Report 생성 생성된 HTML용 Report 확인