OS 환경 : Oracle Linux 7.6 (64bit)
OS 환경 : Oracle Database 19.3.0.0
방법 : 개발자를위한 실시간 SQL 모니터링 DBMS_SQL_MONITOR 이용
SELECT_CATALOG_ROLE 롤이 없는 데이터베이스 사용자도 실행 계획 및 성능 메트릭을 포함하여 자체 SQL 문에 대한 SQL Monitor 보고서를 생성하고 볼 수 있음
데이터베이스 개발자의 주요 책임은 SQL 문을 작성하고 조정하는 것임
SQL Monitor 보고서에 액세스 할 수 있으므로 개발자는 데이터베이스 관리자 권한 없이도 이러한 업무를 수행 할 수 있음
기본적으로 AWR은 XML 형식의 SQL 모니터링 보고서를 자동으로 캡처함
Enterprise Manager Cloud Control 의 Monitored SQL Execution Details 에서 sql 실행계획 및 성능을 확인할 수 있음
해당 페이지는 아래의 뷰를 이용함
|
1
2
3
4
5
6
7
8
9
|
GV$SQL_MONITOR
GV$SQL_PLAN_MONITOR
GV$SQL_MONITOR_SESSTAT
GV$SQL
GV$SQL_PLAN
GV$ACTIVE_SESSION_HISTORY
GV$SESSION_LONGOPS
DBA_HIST_REPORTS
DBA_HIST_REPORTS_DETAILS
|
위에 나열된 뷰 외에 문서로 알려지지 않은 뷰들도 이용됨
sql monitor의 장점
- 5초 이상 수행된 쿼리에 경우 자동으로 수집됨 (바인드 변수의 값도 알수있음)
- 5초 미안 수행된 쿼리의 경우 monitor 힌트를 사용하면 동일하게 수집됨
- 병렬 쿼리도 모니터링이 가능함 개별 병렬 프로세스의 일량을 확인할 수 있음
본문에서는 이 힌트들을 사용해보고 어떻게 동작하는지 확인해봄
테스트
emp 테이블로 작업을 진행하기 때문에 emp 테이블이 없다면 생성해야함
오라클 EMP, DEPT table 생성 스크립트 ( https://positivemh.tistory.com/99 )
0. emp, dept 테이블을 이용해 big 테이블 생성
|
1
2
3
4
5
|
SQL>
drop table big_emp purge;
drop table big_dept purge;
create table big_emp as select * from emp connect by level <= 5;
create table big_dept as select * from dept connect by level <= 5;
|
1. 샘플 쿼리 실행
|
1
2
3
4
5
6
|
SQL>
select /*+ monitor montest1 */ e.empno, e.ename, d.dname, d.loc
from big_emp e, big_dept d
where e.empno >= 7800
order by empno;
(쿼리 실행중..)
|
2. 다른세션에서 해당 쿼리 sql_id 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> select sql_id, sql_text from v$sql where sql_text like '%montest1%';
SQL_ID
-------------
SQL_TEXT
---------------------------------------------------------------------------------------------------------------------------------
fz4qfcrmxfvp3
select sql_id, sql_text from v$sql where sql_text like '%montest1%'
48105wa8sm388
select /*+ monitor montest1 */ e.empno, e.ename, d.dname, d.loc from big_emp e, big_dept d where e.empno >= 7800 order by empno
|
sql id 가 48105wa8sm388 임
48105wa8sm388 로 sql_monitor 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
|
SQL>
set lines 200 pages 1000 long 999999
set longchunksize 200
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR('48105wa8sm388') FROM DUAL;
DBMS_SQLTUNE.REPORT_SQL_MONITOR('48105WA8SM388')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
SQL Text
------------------------------
select /*+ monitor montest1 */ e.empno, e.ename, d.dname, d.loc from big_emp e, big_dept d where e.empno >= 7800 order by empno
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Session : IMSI (22:38675)
SQL ID : 48105wa8sm388
SQL Execution ID : 16777216
Execution Started : 07/22/2024 22:31:59
First Refresh Time : 07/22/2024 22:31:59
Last Refresh Time : 07/22/2024 22:32:19
Duration : 22s
Module/Action : SQL*Plus/-
Service : SYS$USERS
Program : sqlplus@ora19 (TNS V1-V3)
Global Stats
========================================================================
| Elapsed | Cpu | IO | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes |
========================================================================
| 22 | 15 | 6.40 | 927 | 29 | 8MB | 15580 | 3GB |
========================================================================
SQL Plan Monitoring Details (Plan Hash Value=2297506101)
============================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | | | (%) | (# samples) |
============================================================================================================================================================================================
| -> 0 | SELECT STATEMENT | | | | 19 | +2 | 1 | 0 | | | | | . | . | | |
| -> 1 | SORT ORDER BY | | 244M | 2M | 22 | +1 | 1 | 0 | | | 15580 | 3GB | 249KB | 3GB | 86.36 | Cpu (13) |
| | | | | | | | | | | | | | | | | direct path write temp (6) |
| -> 2 | MERGE JOIN CARTESIAN | | 244M | 389K | 19 | +2 | 1 | 88M | | | | | . | . | | |
| -> 3 | TABLE ACCESS FULL | BIG_EMP | 179K | 945 | 19 | +2 | 1 | 64433 | 29 | 8MB | | | . | . | | |
| 4 | BUFFER SORT | | 1364 | 2M | 21 | +0 | 64433 | 88M | | | | | 65536 | . | 13.64 | Cpu (3) |
| 5 | TABLE ACCESS FULL | BIG_DEPT | 1364 | 2 | 1 | +2 | 1 | 1364 | | | | | . | . | | |
============================================================================================================================================================================================
|
해당 쿼리의 성능 문제의 원인은 ID 2에 있는 MERGE JOIN CARTESIAN 임
쿼리 작성자가 실수로 조인절을 적지 않았기 때문에 성능에 문제가 발생함
두 테이블에서 조인 된 데이터를 정렬할때 대부분의 DB 시간이 소요됨(ID 1 Sort Order by)
4. html 으로 구성된 SQL monitor 보고서를 보고싶은 경우 아래 쿼리 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
SET FEEDBACK OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET TRIM ON
SET PAGES 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SPOOL /tmp/long_sql.htm
SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(
SQL_ID => '48105wa8sm388',
EVENT_DETAIL => 'YES',
REPORT_LEVEL => 'ALL',
TYPE => 'HTML')
FROM DUAL;
SPOOL OFF
|
5. /tmp/long_sql.htm 을 웹 브라우저로 열기
실행계획과 IO량 하드웨어 사용량등이 표시됨
6. 그래픽으로 구성된 활성(active) SQL monitor 보고서를 보고싶은 경우 아래 쿼리 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
SET FEEDBACK OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET TRIM ON
SET PAGES 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SPOOL /tmp/long_sql.htm
SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(
SQL_ID => '48105wa8sm388',
EVENT_DETAIL => 'YES',
REPORT_LEVEL => 'ALL',
TYPE => 'ACTIVE')
FROM DUAL;
SPOOL OFF
|
7. /tmp/long_sql.htm 을 웹 브라우저로 열기
실행계획과 IO량 하드웨어 사용량등이 표시됨
참조 :
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| SPM(SQL Plan Management) SQL PLAN BASELINE 이용한 Export / Import 테스트 (실행계획 변경) (0) | 2019.09.19 |
|---|---|
| SPM(SQL Plan Management) SQLSET 이용한 Export / Import 테스트 (실행계획 변경) (0) | 2019.09.19 |
| automatic evolution task 파라미터 설정 (0) | 2019.02.18 |
| 순간적인(1~2분 사이) 과다 실행된 SQL 찾기 (0) | 2019.02.12 |
| SQL 튜닝시 필요한 스크립트 (0) | 2019.01.28 |