Oracle SQL*Plus에서 SET AUTOTRACE를 사용하는 과정에서 여러 가지 문제가 발생했습니다.
AUTOTRACE는 쿼리 실행 결과뿐 아니라 실행 계획(Execution Plan)과 통계(Statistics) 정보를 함께 보여주는 기능입니다. 그러나 통계 정보를 출력하려면 내부적으로 V$SESSTAT, V$STATNAME, V$MYSTAT, V$SESSION 등의 성능 뷰를 참조해야 하며, 일반 사용자에게는 접근 권한이 없습니다. Oracle에서는 이를 위해 PLUSTRACE 롤을 사용하도록 설계되어 있습니다.
문제 상황
PLUSTRACE 롤이 없어 발생한 오류
SP2-0618: 세션 식별자를 찾을 수 없습니다. PLUSTRACE 롤이 사용으로 설정되었는지 점검하십시오
SP2-0611: STATISTICS 레포트를 사용 가능시 오류가 생겼습니다Oracle 12c 이상에서 CDB 루트에 접속해 발생한 오류
ORA-65096: 공통 사용자 또는 롤 이름은 C## 접두어로 시작해야 합니다해결 방법
1. PDB로 전환 후 롤 생성
우선 SYSDBA 로 접근 후 현재 컨테이너 확인합니다.
SHOW CON_NAME;사용 가능한 PDB 확인합니다.
SHOW PDBS;PDB로 전환합니다.
ALTER SESSION SET CONTAINER = ORCLPDB1;롤 생성 및 권한을 부여합니다.
CREATE ROLE PLUSTRACE;
GRANT SELECT ON V_$SESSTAT TO PLUSTRACE;
GRANT SELECT ON V_$STATNAME TO PLUSTRACE;
GRANT SELECT ON V_$MYSTAT TO PLUSTRACE;
GRANT SELECT ON V_$SESSION TO PLUSTRACE;
GRANT PLUSTRACE TO SCOTT;또는 해당 롤은 plustrce.sql이라는 파일로 존재하기에, 위 대신 다음과 같이 간단하게 수행도 가능합니다.
@?/sqlplus/admin/plustrce.sql2. CDB 루트에서 공통 롤로 생성
이 방식은 CDB 에서 직접 권한을 주는 방법으로 비추천하는 방법입니다.
CREATE ROLE C##PLUSTRACE CONTAINER=ALL;
GRANT SELECT ON V_$SESSTAT TO C##PLUSTRACE CONTAINER=ALL;
GRANT SELECT ON V_$STATNAME TO C##PLUSTRACE CONTAINER=ALL;
GRANT SELECT ON V_$MYSTAT TO C##PLUSTRACE CONTAINER=ALL;
GRANT SELECT ON V_$SESSION TO C##PLUSTRACE CONTAINER=ALL;
GRANT C##PLUSTRACE TO SCOTT CONTAINER=ALL;AUTOTRACE를 이쁘게 보는 방법
1. SQL*Plus 화면 포맷 조정
SET AUTOTRACE ON
SET LINESIZE 200
SET PAGESIZE 1000
SET TRIMOUT ON
SET TRIMSPOOL ON
SET TAB OFF
COLUMN "Operation" FORMAT A30
COLUMN "Name" FORMAT A15
COLUMN "Cost (%CPU)" FORMAT A15-
SET AUTOTRACE ON: 쿼리를 실행할 때 자동으로 실행 계획과 통계를 함께 출력합니다. 실행 결과만 보는 것이 아니라 성능 분석까지 바로 확인할 수 있는 핵심 옵션입니다.-
결과만 보고 싶으면:
SET AUTOTRACE OFF -
실행 계획만 보고 싶으면:
SET AUTOTRACE TRACEONLY EXPLAIN -
통계만 보고 싶으면:
SET AUTOTRACE TRACEONLY STATISTICS
-
-
SET LINESIZE 200: 한 줄에 출력할 최대 문자 수를 지정합니다. 기본값이 작으면 실행 계획 표가 줄 바꿈되어 읽기 불편하므로, 200 정도로 설정하면 대부분의 계획이 한 줄에 깔끔하게 표시됩니다. -
SET PAGESIZE 1000: 한 페이지에 표시할 최대 줄 수를 지정합니다. 작게 설정하면 실행 계획과 통계가 페이지 단위로 나뉘어 보기가 불편합니다. 큰 값으로 설정하면 전체 계획이 한 번에 표시됩니다. -
SET TRIMOUT ON/SET TRIMSPOOL ON: 출력과 SPOOL 파일에서 각 줄 끝의 공백을 제거합니다. 정렬이 깨지지 않고 깔끔하게 표시됩니다. -
SET TAB OFF: 컬럼 구분 시 탭 문자가 아닌 공백을 사용합니다. 탭 때문에 표가 들쭉날쭉해지는 문제를 방지합니다. -
COLUMN "Operation" FORMAT A30,"Name" FORMAT A15,"Cost (%CPU)" FORMAT A15: 실행 계획의 주요 컬럼 폭을 지정합니다. 기본 폭보다 좁으면 글자가 잘리거나 정렬이 깨지기 때문에, 폭을 지정해 가독성을 높입니다.
2. DBMS_XPLAN 사용
EXPLAIN PLAN FOR
SELECT * FROM EMP WHERE DEPTNO = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);실제 실행 통계까지 확인:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));실제 실행하기
오라클 성능 고도화 원리와 해법2 에 나오는 스크립트를 실행해봅니다.
CREATE TABLE T_EMP
AS
SELECT *
FROM EMP, (SELECT ROWNUM NO FROM DUAL CONNECT BY LEVEL <= 100000);
ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
ALTER SESSION SET SORT_AREA_SIZE = 1048576;우선, 필요한 테이블을 만들고, 디스크 소트가 유발되는지 확인하기 위해 WORKAREA_SIZE_POLICY를 manual로, SORT_AREA_SIZE 를 1 MB 로 낮게 설정하였습니다.
SET AUTOTRACE ON
SET LINESIZE 200
SET PAGESIZE 1000
SET TRIMOUT ON
SET TRIMSPOOL ON
SET TAB OFF
COLUMN "Operation" FORMAT A30
COLUMN "Name" FORMAT A15
COLUMN "Cost (%CPU)" FORMAT A15SELECT *
FROM (
SELECT NO, EMPNO, ENAME, JOB, MGR, SAL
, AVG(SAL) OVER (PARTITION BY TO_CHAR(NO), DEPTNO) AVG_SAL
FROM T_EMP
)
WHERE NO = 1
ORDER BY SAL DESC;실행 결과는 아래와 같습니다.
NO EMPNO ENAME JOB MGR SAL AVG_SAL
---------- ---------- -------------------- -------------------- ---------- ---------- ----------
1 1009 KING PRESIDENT 5000 2250
1 1019 KIM HR MANAGER 1009 3500 2816.66667
...
50 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 4263631893
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000K| 424M| | 156K (1)| 00:00:07 |
| 1 | SORT ORDER BY | | 5000K| 424M| 520M| 156K (1)| 00:00:07 |
|* 2 | VIEW | | 5000K| 424M| | 54296 (1)| 00:00:03 |
| 3 | WINDOW SORT | | 5000K| 162M| 248M| 54296 (1)| 00:00:03 |
| 4 | TABLE ACCESS FULL| T_EMP | 5000K| 162M| | 8875 (1)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NO"=1)
Statistics
----------------------------------------------------------
253 recursive calls
13 db block gets
32393 consistent gets
96097 physical reads
668 redo size
3524 bytes sent via SQL*Net to client
183 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
50 rows processed-
recursive calls: Oracle 내부에서 발생한 재귀 SQL문 실행 횟수입니다. -
db block gets: 쓰기 가능한 데이터 블록을 읽은 논리적 읽기 횟수로, 인덱스 검색 시 주로 증가합니다. -
consistent gets: 일관성 있는 데이터 블록을 읽은 횟수로, 대부분 메모리에서 처리됩니다. -
physical reads: 디스크에서 직접 읽은 블록 수입니다. 값이 높으면 I/O 부하가 크다는 신호입니다. -
redo size: 쿼리 수행 중 생성된 redo 로그 크기(bytes)입니다. -
bytes sent via SQL*Net to client: 서버에서 클라이언트로 전송된 데이터 크기입니다. 결과가 많으면 값이 커집니다. -
bytes received via SQL*Net from client: 클라이언트에서 서버로 전송된 SQL문이나 파라미터 크기입니다. -
SQL*Net roundtrips to/from client: 서버와 클라이언트 간의 왕복 통신 횟수입니다. 네트워크 비용을 확인할 때 참고합니다. -
sorts (memory): 메모리에서 수행된 정렬 연산 횟수입니다. -
sorts (disk): 메모리 부족 시 디스크에 임시 파일을 만들어 수행한 정렬 횟수입니다. -
rows processed: 쿼리에서 실제로 처리된 행(row) 수입니다. SELECT는 반환 행, DML은 영향을 받은 행 수를 나타냅니다.
