프린트 하기

내맘대로긍정이 알려주는

Oracle 23ai 신기능
무료 세미나 발표자료

다운로드 trending_flat

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량 하드웨어 사용량등이 표시됨

 

 

참조 : 

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/monitoring-database-operations.html#GUID-4048D00E-2635-42C8-A37D-71EFAC619062

https://docs.oracle.com/en/database/oracle/oracle-database/19/newft/new-features.html#GUID-25B329C9-070C-4AE5-BC65-2CCF40F3C399

https://hoing.io/archives/289