개발자를위한 실시간 SQL 모니터링 DBMS_SQL_MONITOR 이용

OS환경 : Oracle Linux 7.2 (64bit)

DB 환경 : Oracle Database 18.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 실행계획 및 성능을 확인할 수 있음

해당 페이지는 아래의 뷰를 이용함

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

위에 나열된 뷰 외에 문서로 알려지지 않은 뷰들도 이용됨

테스트 시나리오1: 간단한 데이터베이스 작업에 대한 보고서

샘플스키마인 sh 유저로 작업을 진행하기 때문에 sh 유저가 없다면 유저를 생성해야함

sh 유저로 접속 후 아래 쿼리 실행

SQL> SELECT /*+ MONITOR */ s.prod_id, c.cust_last_name FROM sales s, customers c ORDER BY prod_id;

쿼리가 완료되지 않음

Oracle Database 19c 부터 권한이 없는 사용자 sh는 세션에서 간단한 데이터베이스 작업 (개별 SQL 및 PL / SQL 문)에 대한  SQL Monitor 보고서를 생성 할 수 있게됨

문제의 원인을 확인하기위해 아래와 같이 SQL Monitor를 사용

(18.3버전도 되는것을 확인함)

1. 쿼리를 취소함(Control + C 로 취소)

SQL> SELECT /*+ MONITOR */ s.prod_id, c.cust_last_name FROM sales s, customers c ORDER BY prod_id;
 
^CSELECT /*+ MONITOR */ s.prod_id, c.cust_last_name FROM sales s, customers c ORDER BY prod_id
                                                       *
ERROR at line 1:
ORA-01013: user requested cancel of current operation

2. DBMS_SQL_MONITOR.REPORT_SQL_MONITOR를 호출함

SQL> 
SET LONG 1000000
VARIABLE my_rept CLOB;
BEGIN
  :my_rept := DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(
                report_level => 'ALL', 
                TYPE         => 'text');
END;
/
PRINT :my_rept

3. 결과를 확인함

SQL> SQL Monitoring Report
 
SQL Text
------------------------------
SELECT /*+ MONITOR */ s.prod_id, c.cust_last_name FROM sales s, customers c ORDER BY prod_id
 
Error: ORA-1013
------------------------------
ORA-01013: user requested cancel of current operation
 
 
Global Information
------------------------------
 Status          :    DONE (ERROR)
 Instance ID         :    1
 Session         :    SH (81:63143)
 SQL ID          :    98rqbxxaxbuu1
 SQL Execution ID    :    16777216
 Execution Started   :    02/18/2019 15:09:16
 First Refresh Time  :    02/18/2019 15:09:16
 Last Refresh Time   :    02/18/2019 15:09:31
 Duration         :    15s
 Module/Action         :    SQL*Plus/-
 Service         :    SYS$USERS
 Program         :    sqlplus@oracle18 (TNS V1-V3)
 Fetch Calls         :    1
 
Global Stats
================================================================================
| Elapsed |   Cpu   |     IO    | Fetch | Buffer | Read | Read  | Write | Write |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets    | Reqs | Bytes | Reqs  | Bytes |
================================================================================
|      16 |     11 |      5.15 |     1 |   2795 |   82 |   4MB |  5284 |   1GB |
================================================================================
 
SQL Plan Monitoring Details (Plan Hash Value=2036849021)
=================================================================================================================================================================================================
| 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 | (Max) | (Max) |   (%)    |   (# samples)    |
=================================================================================================================================================================================================
|  0 | SELECT STATEMENT           |           |         |        |         14 |     +2 |     1 |      0 |       |   |       |       |     . |     . |          |         |
|  1 |     SORT ORDER BY              |           |     58G | 757M |         15 |     +1 |     1 |      0 |       |   |  5284 |   1GB |  71MB |   1GB |          |         |
|  2 |      MERGE JOIN CARTESIAN          |           |     58G |    26M |         14 |     +2 |     1 |    68M |       |   |       |       |     . |     . |          |         |
|  3 |       TABLE ACCESS FULL          | CUSTOMERS       |   48475 |    273 |         14 |     +2 |     1 |     67 |       |   |       |       |     . |     . |          |         |
|  4 |       BUFFER SORT              |           |      1M | 757M |         14 |     +2 |    67 |    68M |       |   |       |       |  31MB |     . |          |         |
|  5 |        PARTITION RANGE ALL       |           |      1M |    539 |          1 |     +2 |     1 |     1M |       |   |       |       |     . |     . |          |         |
|  6 |         BITMAP CONVERSION TO ROWIDS  |           |      1M |    539 |          1 |     +2 |    16 |     1M |       |   |       |       |     . |     . |          |         |
|  7 |          BITMAP INDEX FAST FULL SCAN | SALES_PROD_BIX |         |        |          1 |     +2 |    16 |    44408 |    81 |   4MB |       |       |     . |     . |          |         |
=================================================================================================================================================================================================

서식 때문에 출력된 결과를 읽기가 어려움

4. 그래픽으로 구성된 활성 SQL 모니터 보고서를 확인하기 위해 아래 쿼리 실행

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(
         report_level => 'ALL', 
         TYPE         => 'active') 
FROM   DUAL;
SPOOL OFF

5. /tmp/long_sql.htm 을 웹 브라우저로 열기

실행계획과 IO량 하드웨어 사용량등이 표시됨

성능 분석

해당 쿼리의 성능 문제의 원인은 3행에 있는 MERGE JOIN CARTESIAN 임

쿼리 작성자가 실수로 WHERE절을 적지 않았기 때문에 성능에 문제가 발생함

두 테이블에서 조인 된 데이터를 정렬할때 대부분의 DB 시간이 소요됨(2행 Sort Order by)

테스트 시나리오2: 복합 데이터베이스 운영에 대한 보고서

이 시나리오는 DBMS_SQL_MONITOR 데이터베이스 작업을 정의하는데 사용되며 활성 보고서를 생성함

목표는 sh스키마에 있는 네개의 테이블 쿼리를 작업으로 그룹화 한 다음 보고서를 생성하는 것

1. SQL*Plus에서 관리자 계정(system이나 sysdba)으로 접속함

SQL 모니터가 SQL을 추적하도록 SHOP(force_tracking 설정)라는 작업을 시작하고 4개의 쿼리를 실행한 다음 다음과 같이 작업을 종료함

SQL> 
VARIABLE exec_id NUMBER;
BEGIN
  :exec_id := DBMS_SQL_MONITOR.BEGIN_OPERATION ( dbop_name => 'SHOP', forced_tracking => 'Y' );
END;
/
 
PL/SQL procedure successfully completed.
 
SQL> SELECT COUNT(*) FROM sh.sales;
 
  COUNT(*)
----------
   1016271
 
SQL> SELECT COUNT(*) FROM sh.customers;
 
  COUNT(*)
----------
     50000
 
SQL> 
SELECT prod_id, cust_id 
FROM   sh.sales 
WHERE  prod_id < 26 
ORDER BY prod_id;
 
   PROD_ID    CUST_ID
---------- ----------
    25    38420
    25    38420
.
.
3332 rows selected.
 
SQL> 
SELECT cust_id, cust_first_name, cust_last_name, cust_city 
FROM   sh.customers 
WHERE  cust_id < 30000 
ORDER BY cust_id;
 
   CUST_ID CUST_FIRST_NAME    CUST_LAST_NAME                 CUST_CITY
---------- -------------------- ---------------------------------------- ------------------------------
     29920 Ronald        Eubank                     Moerdijk
     29930 Ronni        Lloyd                     Moerdijk
.
.
2999 rows selected.
 
SQL> 
BEGIN
  DBMS_SQL_MONITOR.END_OPERATION ( dbop_name => 'SHOP', dbop_eid => :exec_id );
END;
/
 
PL/SQL procedure successfully completed.

2. status 및 meta data 를 포함하여 작업에 대한 meta data를 얻기위해 V$SQL_MONITOR(샘플 출력 포함)을 조회함

SQL> 
COL STATUS FOR a10
COL DBOP_NAME FOR a10
COL CON_NAME FOR a5
 
SELECT STATUS, SQL_ID, DBOP_NAME, DBOP_EXEC_ID,
       TO_CHAR(ELAPSED_TIME/1000000,'000.00') AS ELA_SEC 
FROM   V$SQL_MONITOR
WHERE  DBOP_NAME = 'SHOP';
 
STATUS       SQL_ID     DBOP_NAME  DBOP_EXEC_ID ELA_SEC
---------- ------------- ---------- ------------ -------
DONE             SHOP               7  000.16
 
1 rows selected.

3. SQL Monitor 리포트에 대한 meta data를 가져오려면 DBMS_SQL_MONITOR.REPORT_SQL_MONITOR을 조회함(샘플 출력 포함)

SQL> 
SET LONG 10000000
SET LONGCHUNKSIZE 10000000 
SET PAGES 0
SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR( 
  dbop_name => 'SHOP', type => 'TEXT', report_level => 'ALL') AS rpt
FROM DUAL;
 
Global Information
------------------------------
 Status          :    DONE
 Instance ID         :    1
 Session         :    SYS (81:21106)
 DBOP Name         :    SHOP
 DBOP Execution ID   :    7
 First Refresh Time  :    02/18/2019 16:22:35
 Last Refresh Time   :    02/18/2019 16:24:28
 Duration         :    113s
 Module/Action         :    sqlplus@oracle18 (TNS V1-V3)/-
 Service         :    SYS$USERS
 Program         :    sqlplus@oracle18 (TNS V1-V3)
 
Global Stats
==============================================
| Elapsed |   Cpu   |     IO    | Application |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   |
==============================================
|    0.17 |    0.17 |      0.00 |    0.00 |
==============================================
 
 
1 row selected.

4. 그래픽으로 구성된 활성 SQL 모니터 보고서를 확인하기 위해 아래 쿼리 실행

SQL> 
SET TRIMSPOOL ON
SET TRIM ON
SET PAGES 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
 
SPOOL /tmp/shop.htm
SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(dbop_name=>'SHOP',report_level=>'ALL',TYPE=>'active') 
FROM   DUAL;
SPOOL OFF

5. /tmp/shop.htm 을 웹 브라우저로 열기

참조 : 

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