1. ORACLE SQL DEVELOPER 에서 자동 Trace 사용하기 위해 필요한 권한 부여
GRANT SELECT_CATALOG_ROLE TO SJ_USER;
GRANT SELECT ANY DICTIONARY TO SJ_USER;
-- REVOKE SELECT_CATALOG_ROLE FROM SJ_USER;
-- REVOKE SELECT ANY DICTIONARY FROM SJ_USER;
2. DBMS_XPLAN PACKAGE를 사용하기 위해 필요한 권한 부여
GRANT SELECT ON V_$SQL TO SJ_USER;
GRANT SELECT ON V_$SQL_PLAN TO SJ_USER;
GRANT SELECT ON V_$SESSION TO SJ_USER;
GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO SJ_USER;
-- REVOKE SELECT ON V_$SQL FROM SJ_USER;
-- REVOKE SELECT ON V_$SQL_PLAN FROM SJ_USER;
-- REVOKE SELECT ON V_$SESSION FROM SJ_USER;
-- REVOKE SELECT ON V_$SQL_PLAN_STATISTICS_ALL FROM SJ_USER;
* ORACLE SQL DEVELOPER의 자동 Trace 결과와 DBMS_XPLAN.DISPLAY_CURSOR 로 조회한 결과는 같다.
3. TRACE 파일을 생성했을 때와 동일하게 예측 ROW 수가 아닌 실제 ROW 수를 확인하고자 할 경우
ALTER SESSION SET STATISTICS_LEVEL = ALL;
혹은 10g 이상이면 SQL Level에 Hint 사용하면 된다.
/*+ GATHER_PLAN_STATISTICS */
4. DBMS_XPLAN.DISPLAY_CURSOR의 파라메터
DBMS_XPLAN.DISPLAY_CURSOR (SQL_ID VARCHAR2 DEFAULT NULL,
CURSOR_CHILD_NO INTEGER DEFAULT 0,
FORMAT VARCHAR2 DEFAULT 'TYPICAL')
5. 실행방법 (이방법은 잘안된다.)
-- 분석할 쿼리 실행
SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM SJ_USER.COM_MENU
START WITH MNU_PARENTUID IS NULL
CONNECT BY PRIOR SITE_ID = SITE_ID
AND PRIOR MNU_UID = MNU_PARENTUID;
-- 실행한 쿼리의 SQL_ID, CHILD_NUMBER 확인
SELECT SQL_ID
, CHILD_NUMBER
, SQL_FULLTEXT
FROM V$SQL
WHERE PARSING_SCHEMA_NAME = 'SJ_USER'
AND SQL_FULLTEXT LIKE '%GATHER_PLAN_STATISTICS%'
ORDER BY LAST_LOAD_TIME DESC;
-- 실행결과 조회
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('b4gp7hu0w97tp', 0, 'ADVANCED ALLSTATS LAST'));
6. 실행방법
SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM SJ_USER.COM_MENU
START WITH MNU_PARENTUID IS NULL
CONNECT BY PRIOR SITE_ID = SITE_ID
AND PRIOR MNU_UID = MNU_PARENTUID;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'advanced allstats last'));위 두개의 쿼리를 하나의 블륵으로 지정하여 한번에 실행하면 실행결과 나온다.
* 분석대상 쿼리를 정확하게 잡지 못하는 경우 조치
1. SET SERVEROUTPUT OFF; -- sql_id 잘 못가져올때 실행, 난 그래도 검색조건에 OR 연산자가 들어간 쿼리는 잘 잡지 못하더라....
SHOW SERVEROUTPUT;
2. 분석대상 쿼리의 결과가 모두 Fetch 되지 않아서 그렇다. 즉 Oracle Developer에서 모든 결과 집합이 조회되도록 한다음. 하면 된다.
1) Basics 항목
- Id : 각 Operationd의 ID임. *가 달려있는 경우는 Predicate Informatio에 Access 및 Filter에 관한 정보를 표시함
- Operation : 각각 실행되는 JOB
- Name : Operationdl 엑세스 하는 Table 및 Index
2) Query Optimizer Estimations 항목(예상치)
- E-Rows : 각 Operation이 끝났을 때 return 되는 건수.
- E-Bytes : 각 Operation이 Temporany Space를 사용한 양
- Cost(%CPU) : 각 Operation의 Cost. 괄호 안의 내용은 CPU Cost의 백분율임. 이 값은 Child Operation의 Cost를 합친 누적치.
- E-Time : 수행시간
3) Runtime Statistics 항목
- Starts : 각 Operation을 반복 수행한 건수
- A-Rows : 각 Operation이 Return 한 건수
- A-Time : 실제 실행시간. 0.01초까지 나타남(HH:MM:SS.FF). Child Operation의 A-Time을 합친 누적치
4) I/O Statistics
- Buffers : 각 Operation이 memory에서 읽은 Block 수.
- Reads : 각 Operation이 Disk에서 Read한 Block 수.
- Writes : 각 Operation이 Disk에서 White한 Block 수.
5) Memory Utilization Statistics(hash 작업이나 sort 작업 시 사용한 메모리 통계)
- OMen : Optimal Execution에 필요한 Memory
- SQL 실행 메모리가 최적의 크기를 가졌을때의 메모리. 여기서 메모리가 최적의 크기를 갖는다는 것은 예를 들어, disk에 write하지 않고 sort 작업을 수행하는 경우를 의미한다.
- 1Mem : One-pass Execution에 필요한 Momory
- SQL 실행 메모리가 1 pass의 크기를 가졌을 때의 메모리. 여기서 메모리가 1 pass의 크기를 갖는다는 의미는, 예를 들어 sort의 경우 disk에 임시 결과를 한번은 저장하고 결과를 merge해서 sort 작업을 마치는 경우를 의미한다.
- O/1/M : 각 Operation이 실행한 Optmal/One-pass/Multipass 횟수가 순서대로 표시됨
O 일 경우 메모리공간(hash_area_size)이 부족하지 않아 temp 영역(disk)을
사용하지 않고 처리 되었다는 의미임.
- multipass 횟수 : SQL 실행 메모리가 2 pass 이상의 크기를 가졌던 횟수.
- Used-mem : 마지막 실행 시 사용한 PGA -Memory
- Used-Tmp : 마지막 실행 시 메모리가 부족하여 Temporary Space를 대신 사용할 때 나타남. 보이는 값에 1024를 곱해야 함.
- Max-Tmp : 메모리가 부족하여 Temporary Space를 사용할 때 최대 Temp 사용량. Used-Tmp와 다른 점은 마지막 수행시가 아니라 SQL을 여러 번 수행했을 경우에 항상 최대값만 보인다는 것. 보이는 값에 1024를 곱해야 함.
6) 쿼리블록 정보 : Plan 상의 Id별로 쿼리블럭 및 Alias 정보를 출력.,
7) Outline Date : 오라클이 내부적으로 사용한 힌트.
8) Predicate Information : Plan 상의 Id 별로 인덱스 액세스, Filter, 조인정보 등을 표출
9) Column Projection Information : Plan 상의 Id 별로 Select 되는 컬럼의 정보.
* DBGuide.net의 GATHER_PLAN_STATISTICS 컬럼 내용
----------------------------------------------------------------------------------------------------------
Id, Operation, Name
이 부분은 우리가 흔히 봐 왔던 플랜 정보다. 자원에 대한 접근 순서와 접근 방법을 나타낸다.
참고적으로 접근 순서를 변경 할 수 있는 힌트절은 ORDERED, LEADING이 있다.
또한 접근 방법을 변경할 수 있는 힌트절은 USE_NL, USE_HASH, USE_MERGE가 있다.
Starts
오퍼레이션을 수행한 횟수를 의미한다. Starts * E-Rows 의 값이 A-Rows 값과 비슷하다면, 통계정보의 예측 Row 수와 실제 실행 결과에 따른 실제 Row 수가 유사함을 알 수 있다.
만약 값에 큰 차이가 있다면 통계정보가 실제의 정보를 제대로 반영하지 못했다고 생각할 수 있다.
이로 인해 오라클의 Optimizer가 잘못된 실행 계획을 수립할 수도 있음을 염두에 둬야 한다.
E-Rows (Estimated Rows)
통계정보에 근거한 예측 Row 수를 의미한다. 통계정보를 갱신할수록 값이 매번 다를 수 있으며,
대부분의 DB 운영에서는 통계정보를 수시로 갱신하지 않으므로 해당 값에 큰 의미를 둘 필요는 없다.
하지만 E-Rows 값과 A-Rows 값이 현격하게 차이가 있다면 오라클이 잘못된 실행 계획을 세울 수도 있음을 인지해야 하며 통계정보 생성을 검토해 보아야 한다.
A-Rows (Actual Rows)
쿼리 실행 결과에 따른 실제 Row 수를 의미한다. 우리는 A-Rows 에서 중요한 여러 정보를 추정 할 수 있다.
A-Time (Actual Elapsed Time)
쿼리 실행 결과에 따른 실제 수행 시간을 의미한다. 하지만 실행 시점의 여러 상황이 늘 가변적이고 또한 메모리에 올라온 Block의 수에 따라서 수행 시간이 달라지므로 해당 값에 큰 의미를 둘 필요는 없다.
Buffers (Logical Reads)
논리적인 Get Block 수를 의미한다. 해당 값은 오라클 옵티마이저가 일한 총량을 의미하므로, 튜닝을 진행할 때 필자가 가장 중요하게 생각하는 요소 중 하나다.
Reads (Physical Reads)
물리적인 Get Block 수를 의미한다. 동일한 쿼??음이 아닌 경우에는 값이 0인 것을 보면 알 수 있듯이 메모리에서 읽어온 Block은 제외된다. 해당 값에 큰 의미를 둘 필요는 없다.
위의 헤더에서 튜닝 시 가장 중요하게 활용되는 부분은 Buffers와 A-Rows다.
Buffers 값을 통해서 Get Block의 총량을 알 수 있고, A-Rows를 통해 플랜 단계별로 실제 Row 수를 알 수 있기 때문이다