패키지 캐시 정보 및 db2advis를 사용하여 성능 향상 기회 확인

디자인 어드바이저에서는 SQL문을 분석하여 데이터베이스 성능을 향상시키는 방법에 대한 권장사항을 작성할 수 있습니다. 패키지 캐시의 명령문(패키지 캐시 이벤트 모니터에서 캡처된 명령문 포함)을 디자인 어드바이저에 대한 입력으로 사용하여 지정된 워크로드 또는 두 특정 시점 간에 실행된 모든 명령문에 대한 성능을 향상시키기 위해 변경할 수 있는 내용을 식별합니다.

시작하기 전에

CREATE EVENT MONITOR문은 이벤트 모니터에 의해 생성된 형식화되지 않은 이벤트(UE) 테이블을 저장하기 위해 페이지 크기가 8K 이상인 테이블스페이스가 필요합니다. 테이블스페이스의 이름이 CREATE EVENT MONITOR문에 명시적으로 지정되지 않은 경우, 데이터베이스의 기본 테이블스페이스가 사용됩니다.
디자인 어드바이저에 필요한 Explain 테이블이 작성되어 있어야 합니다.

이 타스크에 대한 정보

이 태스크에서는 패키지 캐시 이벤트 모니터를 사용하여 두 특정 시점 사이에 시스템에서 수행된 모든 작업을 트랙하고 db2advis 명령을 사용하여 해당 기간 동안 실행된 고비용 명령문을 분석하는 방법을 보여줍니다. db2advis 명령의 출력에서는 패키지 캐시 이벤트 모니터가 활성화된 상태에서 실행된 명령문을 기반으로 성능을 향상시키기 위해 데이터베이스에 적용할 수 있는 변경사항 또는 조정사항을 제안합니다. 문제가 있는 명령문이 더 이상 패키지 캐시에 있지 않도록 하는 경우 패키지 캐시 이벤트 모니터를 사용하여 이러한 명령문을 캡처하는 것이 유용합니다.

제한

이 특정 예에서 분석된 명령문의 길이는 3000자로 제한됩니다. 이 제한사항은 stmt_text 모니터 요소와 같은 LOB 값과 함께 사용할 수 없는 명령문에 사용된 GROUP BY절의 사용으로 인한 것입니다.

프로시저

패키지 캐시에서 제거(퇴거)될 때 명령문을 캡처하기 위해 패키지 캐시 이벤트 모니터를 작성하십시오.
예를 들어, TRACKSTMTS라는 이벤트 모니터를 작성하려면 다음 SQL을 사용할 수 있습니다.
```
CREATE EVENT MONITOR TRACKSTMTS FOR PACKAGE CACHE WRITE TO UNFORMATTED EVENT TABLE
```
이 명령문은 이벤트 모니터 TRACKSTMT와 동일한 이름의 UE 테이블에 기록되는 패키지 캐시 이벤트 모니터를 작성합니다.

기본적으로 패키지 캐시에서 제거된 모든 명령문이 패키지 캐시 이벤트 모니터에 의해 캡처됩니다. 수집되는 정보의 양을 제한하려면 수집되는 정보를 제한하는 CREATE EVENT MONITOR문의 파트로 옵션을 지정할 수 있습니다. 자세한 정보는 CREATE EVENT MONITOR문(패키지 캐시)에 대한 문서를 참조하십시오.
다음으로, 이벤트 모니터를 활성화하십시오.
```
SET EVENT MONITOR TRACKSTMTS STATE 1
```
참고: 기본적으로 이 이벤트 모니터는 데이터베이스 활성화 시 자동으로 시작됩니다. 왜냐하면 AUTOSTART 옵션이 기본적으로 적용되기 때문입니다. 그러나 이 이벤트 모니터가 이미 활성화된 데이터베이스에서 작성되므로 SET EVENT MONITOR 명령을 사용하여 수동으로 시작해야 합니다.
데이터베이스에 연결하고 성능 분석을 수행할 명령문, 워크로드 또는 애플리케이션을 실행하십시오. 원하는 만큼 정보를 수집할 수 있습니다. 그러나 이러한 유형의 성능 튜닝은 주기적으로 실행되는 애플리케이션 또는 워크로드가 있는 경우 가장 잘 작동됩니다. 그렇지 않으면 이전에 실행한 명령문에 대한 튜닝사항이 나중에 실행되는 명령문에 영향을 미치지 않습니다.
데이터 수집이 완료되면 다음과 같이 이벤트 모니터를 비활성화하십시오.
```
SET EVENT MONITOR TRACKSTMTS STATE 0
```
EVMON_FORMAT_UE_TO_TABLES 프로시저를 사용하여 이벤트 모니터에서 채워진 UE 테이블에서 데이터를 추출하십시오.
```
CALL EVMON_FORMAT_UE_TO_TABLES 
    ('PKGCACHE', NULL, NULL, NULL, NULL, NULL, NULL, -1, 
     'SELECT * FROM TRACKSTMTS')
```
이 프로시저가 패키지 캐시 이벤트 모니터에 의해 수집된 데이터에서 두 개의 관계형 테이블을 작성합니다.
- PKGCACHE_EVENT
- PCKCACHE_METRICS
첫 번째 테이블에 가장 자주 사용되는 모니터 요소 및 캡처된 각 이벤트와 연관된 메트릭이 포함됩니다. 두 번째 테이블에 각 이벤트에 대한 자세한 메트릭이 포함됩니다.
참고 : PKGCACHE_METRICS 열의 값은 PKGCACHE_EVENT 테이블의 METRICS 열에 포함된 XML 문서에서도 확인할 수 있습니다. 이는 컬럼 중심으로 더욱 편리하게 액세스할 수 있도록 PKGCACHE_METRICS 테이블에 제공되어 있습니다.

이벤트 모니터에서 출력을 쿼리하여 실행 시간이 가장 오래 걸리는 명령문을 판별하십시오. 이 예에서, 문장 실행 시간 (stmt_exec_time - 문장 실행 시간 모니터 요소 )은 전체 비용을 결정하는 데 사용되는 모니터 요소에 소요된 시간입니다. 모든 데이터베이스 파티션 전반에 이 모니터 요소가 합계됩니다.

팁 : 쿼리의 결과를 텍스트 파일로 저장하십시오. 다음 단계에서 이 파일이 사용됩니다.

WITH STMTS AS
(
  SELECT SUM(TOTAL_STMT_EXEC_TIME)/SUM(TOTAL_NUM_COORD_EXEC_WITH_METRICS) AS AVG_TIME_PER_EXEC,
         STMT_TEXT, SUM(NUM_EXECUTIONS) AS NUM_EXECUTIONS, STMT_TYPE_ID
  FROM (
        (
         SELECT    SUM(STMT_EXEC_TIME) AS TOTAL_STMT_EXEC_TIME,
                   SUM(NUM_COORD_EXEC_WITH_METRICS) AS TOTAL_NUM_COORD_EXEC_WITH_METRICS,
                   SUM(NUM_COORD_EXEC) AS NUM_EXECUTIONS,
                   VARCHAR(STMT_TEXT, 3000) AS STMT_TEXT,
                   STMT_TYPE_ID
         FROM      PKGCACHE_EVENT AS E, PKGCACHE_METRICS AS M 
         WHERE     E.XMLID = M.XMLID 
          AND      NUM_COORD_EXEC_WITH_METRICS > 0
         GROUP BY  VARCHAR(STMT_TEXT, 3000),STMT_TYPE_ID
         ORDER BY TOTAL_NUM_COORD_EXEC_WITH_METRICS DESC
         FETCH FIRST 50 ROWS ONLY
        )
        UNION ALL
        (
         SELECT    SUM(STMT_EXEC_TIME) AS TOTAL_STMT_EXEC_TIME,
                   SUM(NUM_COORD_EXEC_WITH_METRICS) AS TOTAL_NUM_COORD_EXEC_WITH_METRICS,
                   SUM(NUM_COORD_EXEC) AS NUM_EXECUTIONS,
                   VARCHAR(STMT_TEXT, 3000) AS STMT_TEXT,
                   STMT_TYPE_ID
         FROM      TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-2)) AS T
         WHERE     NUM_COORD_EXEC_WITH_METRICS > 0
         GROUP BY  VARCHAR(STMT_TEXT, 3000),STMT_TYPE_ID
         ORDER BY TOTAL_NUM_COORD_EXEC_WITH_METRICS DESC
         FETCH FIRST 50 ROWS ONLY
        )
       ) AS Q_UA 
  GROUP BY STMT_TEXT, STMT_TYPE_ID
)
SELECT      '--# SET FREQUENCY ' || NUM_EXECUTIONS || X'0A' || STMT_TEXT || ';'
FROM        STMTS  WHERE STMT_TYPE_ID LIKE 'DML, Select%' OR STMT_TYPE_ID LIKE 'DML, Insert%' 1 
ORDER BY    AVG_TIME_PER_EXEC DESC
FETCH FIRST 50 ROWS ONLY;

앞의 샘플 명령문에서는 MON_GET_PKG_CACHE_STMT 테이블 함수의 인메모리 정보와 패키지 캐시 이벤트 모니터의 데이터가 모두 검색되었습니다. 두 데이터 세트를 모두 확인하면 패키지 캐시에 있는 명령문 및 패키지에서 제거된 명령문의 데이터를 볼 수 있습니다. 캐시에서 아직 제거되지 않은 명령문도 포함되도록 실행하는 데 비용이 많이 드는 명령문을 평가하는 경우 이를 수행할 수 있습니다. 각각의 경우 쿼리가 명령문이 실행된 횟수를 기반으로 패키지 캐시 이벤트 모니터 및 활성 패키지 캐시에서 상위 50개의 명령문을 검색합니다. 그런 다음 이러한 명령문에서 명령문이 실행된 평균 시간을 기반으로 상위 50개의 SELECT 또는 INSERT문 중 하나가 선택됩니다.

참고 : 실행 비용이 많이 드는 문장을 결정할 때 사용할 여러 가지 모니터 요소 중에서 선택할 수 있습니다. 이 예에서는 명령문 실행 시간이 사용됩니다. 이 수치는 이 섹션을 실행하는 모든 구성원과 에이전트에서 실행하는 데 소요된 시간(대기 시간 포함)을 나타냅니다. 대신에 CPU 시간 (total_cpu_time - Total CPU time monitor element )을 사용하도록 선택할 수도 있습니다. 이 옵션은 명령문을 처리하는 데 CPU가 소비한 시간만 보고합니다. 또한 패키지 캐시 이벤트 모니터에서 리턴된 기타 여러 소요 시간 요소를 선택할 수 있습니다. 패키지 캐시 이벤트 모니터의 EVMON_FORMAT_UE_TO_TABLES에 의해 관계형 테이블에 기록된 정보 또는 패키지 캐시 이벤트 모니터의 XML에 기록된 정보를 참조하여 선택할 수 있는 모니터 요소에 대한 자세한 내용을 확인하세요.

또한 쿼리는 디자인 어드바이저가 분석에 사용하는 --# SET FREQUENCY 형식으로 출력을 표시합니다.

이전 쿼리는 다음과 같은 결과를 리턴합니다.

----------------------------------------------------------------------------------------------------------------------------
--# SET FREQUENCY 1                                                                                               
WITH STMTS AS ( SELECT SUM(TOTAL_STMT_EXEC_TIME)/SUM(TOTAL_NUM_COORD_EXEC_WITH_METRICS) AS AVG_TIME_PER_EXEC, STMT
--# SET FREQUENCY 2                                                                                               
WITH STMTS AS ( SELECT SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME, EXECUTABLE_ID, VARCHAR(STMT_TEXT, 3000) AS STMT_TEXT
--# SET FREQUENCY 1055                                                                                            
SELECT POLICY FROM SYSTOOLS.POLICY WHERE MED='DB2CommonMED' AND DECISION='NOP' AND NAME='CommonPolicy';           
--# SET FREQUENCY 99                                                                                              
SELECT CREATOR, NAME, CTIME FROM SYSIBM.SYSTABLES WHERE TYPE='T' OR TYPE='S' OR TYPE='N' WITH UR;                 
--# SET FREQUENCY 1                                                                                               
UPDATE SYSTOOLS.HMON_ATM_INFO SET STATS_LOCK = 'N', REORG_LOCK = 'N';                                             
--# SET FREQUENCY 1                                                                                               
UPDATE SYSTOOLS.HMON_ATM_INFO AS ATM SET STATS_FLAG = 'N', REORG_FLAG = 'N' WHERE (ATM.SCHEMA, ATM.NAME) IN   (SEL
--# SET FREQUENCY 1                                                                                               
SELECT POLICY FROM SYSTOOLS.POLICY WHERE MED='DB2TableMaintenanceMED' AND DECISION='TableRunstatsDecision' AND NAM
--# SET FREQUENCY 83                                                                                              
WITH JTAB(JSCHEMA,JNAME) AS (VALUES(TABLE_SCHEMA(CAST(? AS varchar(128)), CAST(? AS varchar(128))), TABLE_NAME (CA
--# SET FREQUENCY 122                                                                                             
WITH VTYPED (NAME, SCHEMA) AS (VALUES(TABLE_NAME (CAST(? AS varchar(128)), CAST(? AS varchar(128))), TABLE_SCHEMA(
--# SET FREQUENCY 1210                                                                                            
SELECT COLNAME, TYPENAME FROM  SYSCAT.COLUMNS WHERE TABNAME='POLICY' AND TABSCHEMA='SYSTOOLS';                    
--# SET FREQUENCY 105                                                                                             
SELECT TABNAME FROM SYSCAT.TABLES  WHERE TABNAME='HMON_ATM_INFO' AND  TABSCHEMA='SYSTOOLS';                       
--# SET FREQUENCY 104                                                                                             
DELETE FROM SYSTOOLS.HMON_ATM_INFO AS ATM WHERE NOT EXISTS ( SELECT * FROM SYSIBM.SYSTABLES AS IBM WHERE ATM.NAME 
--# SET FREQUENCY 1118                                                                                            
VALUES(SUBSTR(:H00003      ,:H00014,:H00015 ))         INTO :H00009:H00017    ;                                   
--# SET FREQUENCY 274                                                                                             
INSERT INTO "ASRISK"."PKGCACHE_EVENT"("EVENT_ID","XMLID","EVENT_TYPE","EVENT_TIMESTAMP","MEMBER","SECTION_TYPE","I
--# SET FREQUENCY 1                                                                                               
SELECT IBM.TID, IBM.FID FROM SYSIBM.SYSTABLES AS IBM, SYSTOOLS.HMON_ATM_INFO AS ATM WHERE ATM.STATS_FLAG <> 'Y' AN
--# SET FREQUENCY 115                                                                                             
VALUES(SUBSTR(CAST(? AS CLOB(162)),CAST(? AS INTEGER),CAST(? AS INTEGER)));                                       
--# SET FREQUENCY 8227   

⋮
                         
-# SET FREQUENCY 532                                                                                             
SELECT TBNAME, TBCREATOR FROM "ASRISK  ".SYSINDEXES WHERE NAME = 'INDCOLUMNS01' AND CREATOR = 'SYSIBM  ';         
--# SET FREQUENCY 105                                                                                             
SELECT TABNAME FROM SYSCAT.TABLES  WHERE TABNAME='HMON_COLLECTION' AND  TABSCHEMA='SYSTOOLS';                     
--# SET FREQUENCY 4091                                                                                            
SELECT STATS_LOCK, REORG_LOCK FROM SYSTOOLS.HMON_ATM_INFO WHERE SCHEMA = ? AND NAME = ? AND CREATE_TIME = ? FOR UP
--# SET FREQUENCY 17100                                                                                           
SELECT CREATE_TIME FROM SYSTOOLS.HMON_ATM_INFO WHERE SCHEMA = ? AND NAME = ? FOR UPDATE;                          
--# SET FREQUENCY 524                                                                                             
SELECT COUNT(*) FROM "SYSIBM".SYSTABLES WHERE NAME = 'SYSDATAPARTITIONEXPRESSION' AND CREATOR = 'SYSIBM  ' AND TYP
--# SET FREQUENCY 532                                                                                             
SELECT COUNT(*) FROM "SYSIBM".SYSTABLES WHERE NAME = 'SYSCOLUMNS' AND CREATOR = 'SYSIBM  ' AND TYPE = 'S';        
                                                                                                                  
  47 record(s) selected

참고 : 위의 샘플 출력에서 줄은 표시를 위해 잘라냈습니다.

6단계에서 쿼리가 반환한 문장을 사용하여 ' db2advis ' 명령을 위한 입력 파일을 만듭니다. ( db2advis 명령에 대한 입력 파일 생성에 대한 자세한 정보는 해당 명령에 대한 참조 문서를 참조하십시오.)
7단계에서 만든 입력 파일을 사용하여 ' db2advis ' 명령을 실행합니다.
예를 들어, 작성한 입력 파일을 pkgcache_stmts.txt라고 하고, 다음과 같이 명령을 실행합니다.
```
db2advis -d customer -i pkgcache_stmts.txt -m MICP
```
여기서,
- -d CUSTOMER 은(는) 권장사항을 가져오는 데이터베이스의 이름을 식별합니다.
- -i pkgcache_stmts.txt는 db2advis에 대한 입력 파일의 이름을 식별합니다.
- -m MICP은(는) 성능을 향상시키기 위해 다음 권장사항을 생성하는 db2advis 명령에 대한 지시문입니다.
  
  M
  
  새 구체화된 쿼리 테이블(MQT) 및 MQT에 대한 인덱스를 작성하도록 권장합니다. 파티션된 데이터베이스 환경에서는 MQT에 대한 파티셔닝도 권장됩니다.
  
  I
  
  새 인덱스를 권장합니다. 이는 기본값입니다.
  
  C
  
  표준 테이블을 다차원 클러스터링(MDC) 테이블로 변환하거나 테이블에 클러스터링 인덱스를 생성할 것을 권장합니다.
  
  P
  
  기존 테이블을 재파티션할 것을 권장합니다.

결과

디자인 어드바이저가 다음과 같이 권장사항을 리턴합니다.

execution started at timestamp 2010-03-16-14.25.57.562000
Using the default table space name USERSPACE1
found [47] SQL statements from the input file
excluding statement [0] from the workload.
excluding statement [1] from the workload.
excluding statement [19] from the workload.
excluding statement [39] from the workload.
Recommending indexes...
Recommending MQTs...
Recommending Multi-Dimensional Clusterings...
Found 19 user defined views in the catalog table
Found [17] candidate MQTs
Getting cost of workload with MQTs
total disk space needed for initial set [   0.159] MB
total disk space constrained to         [  69.215] MB
  2  indexes in current solution
  0  MQTs in current solution
total disk space needed for initial set [   0.024] MB
total disk space constrained to         [ 103.822] MB
No useful Multi-dimensional Clustering dimensions for this workload
 [5651.8281] timerons  (without recommendations)
 [5519.8281] timerons  (with current solution)
 [2.34%] improvement


--
--
-- LIST OF MODIFIED CREATE-TABLE STATEMENTS WITH RECOMMENDED PARTITIONING KEYS AND TABLESPACES AND/OR RECOMMENDED MULTI-DIMENSIONAL CLUSTERINGS
-- ===========================
--  No new partitioning keys or tablespaces are recommended for this workload.


--
--
-- LIST OF RECOMMENDED MQTs
-- ===========================


--
--
-- RECOMMENDED EXISTING MQTs
-- ===========================


--
--
-- UNUSED EXISTING MQTs
-- ============================
-- DROP TABLE "ASRISK  "."ADEFUSR";


--
--
-- RECOMMENDED CLUSTERING INDEXES
-- ============================


--
--
-- LIST OF RECOMMENDED INDEXES
-- ===========================
-- index[1],    0.024MB
   CREATE INDEX "ASRISK  "."IDX003161830530000" ON "ASRISK  "."SYSINDEXES"
   ("CREATOR" ASC, "NAME" ASC, "TBCREATOR" ASC, "TBNAME"
   ASC) ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;
   COMMIT WORK ;


--
--
-- RECOMMENDED EXISTING INDEXES
-- ============================
-- RUNSTATS ON TABLE "SYSTOOLS"."POLICY" FOR SAMPLED DETAILED INDEX "SYSTOOLS"."POLICY_UNQ" ;
-- COMMIT WORK ;
-- RUNSTATS ON TABLE "SYSTOOLS"."HMON_ATM_INFO" FOR SAMPLED DETAILED INDEX "SYSTOOLS"."ATM_UNIQ" ;
-- COMMIT WORK ;
-- RUNSTATS ON TABLE "SYSIBM  "."SYSDATAPARTITIONS" FOR SAMPLED DETAILED INDEX "SYSIBM  "."INDDATAPARTITIONS03" ;
-- COMMIT WORK ;
-- RUNSTATS ON TABLE "SYSIBM  "."SYSTABLES" FOR SAMPLED DETAILED INDEX "SYSIBM  "."INDTABLES01" ;
-- COMMIT WORK ;
-- RUNSTATS ON TABLE "SYSIBM  "."SYSTABLESPACES" FOR SAMPLED DETAILED INDEX "SYSIBM  "."INDTABLESPACES04" ;
-- COMMIT WORK ;
-- RUNSTATS ON TABLE "SYSIBM  "."SYSCOLUMNS" FOR SAMPLED DETAILED INDEX "SYSIBM  "."INDCOLUMNS01" ;
-- COMMIT WORK ;
-- RUNSTATS ON TABLE "SYSIBM  "."SYSINDEXES" FOR SAMPLED DETAILED INDEX "SYSIBM  "."INDINDEXES02" ;
-- COMMIT WORK ;
-- RUNSTATS ON TABLE "SYSIBM  "."SYSTRIGGERS" FOR SAMPLED DETAILED INDEX "SYSIBM  "."INDTRIGGERS02" ;
-- COMMIT WORK ;


--
--
-- UNUSED EXISTING INDEXES
-- ============================
-- DROP INDEX "ASRISK  "."PKGCACHE_EVENT_IND1";
-- ===========================
--

-- ====ADVISOR DETAILED XML OUTPUT=============
-- ==(Benefits do not include clustering recommendations)==
  ⋮

참고 : 디자인 어드바이저의 출력 결과는 표시를 위해 일부가 잘려 있습니다.

다음에 수행할 작업

성능 향상을 위해 데이터베이스에 대한 변경사항을 결정할 때 디자인 어드바이저의 출력을 사용하는 것이 유용합니다.