'분류 전체보기' 카테고리의 글 목록 (2 Page)

JJON's StorY

오라클 메모리 사용 확인(AIX) 2023.03.22
sqlarea 쿼리 확인 2022.07.21
SPM - 튜닝된 SQL의 실행계획을 등록하고 적용 하는 방법 2022.06.13
SPM(SQL PLAN MANAGEMENT) 2022.06.13

오라클 메모리 사용 확인(AIX)

타마마임팩트_쫀 2023. 3. 22. 15:35

2023. 3. 22. 15:35

ps -ef | egrep "$ORACLE_SID" | awk '{print $2}' | xargs -n 1 ps vw \
| awk '{print $7-$10,$0;priv+=$7-$10;trs[$9]=$10} \
END{for(tsiz in trs){TRS+=trs[tsiz]}; print priv+TRS," :TOTAL (",priv,"private memory,",TRS,"shared code segments - TRS)"}' \
| sed '1p;/^0/d' | sort -n +0

접속한 유저의 ORACLE_SID 환경변수를 가져오므로 필요시 변경.

사용 예 )

[lfmalldb01d:/DBMS/LFMQ] ps -ef | egrep "$ORACLE_SID" | awk '{print $2}' | xargs -n 1 ps vw \
> | awk '{print $7-$10,$0;priv+=$7-$10;trs[$9]=$10} \
> END{for(tsiz in trs){TRS+=trs[tsiz]}; print priv+TRS," :TOTAL (",priv,"private memory,",TRS,"shared code segments - TRS)"}' \
> | sed '1p;/^0/d' | sort -n +0
0       PID    TTY STAT  TIME PGIN  SIZE   RSS   LIM  TSIZ   TRS %CPU %MEM COMMAND
164  62980774  pts/0 A     0:00    0   164   184    xx    16    20  0.0  0.0 tail -f /DBMS/LFMQ/LFDBA/batch/logs/clon_LFMQ.20230322 
168   4916192  pts/3 A     0:00    0   168   188    xx    16    20  0.0  0.0 tail -f alert_LFMQ.log 
536  27263876      - A     0:00    1   536   848    xx   266   312  0.0  0.0 /bin/ksh /DBMS/LFMQ/LFDBA/batch/scripts/02_RMAN_restore.sh
552  65405570      - A     0:00    0   552   864    xx   266   312  0.0  0.0 /bin/ksh /DBMS/LFMQ/LFDBA/batch/scripts/clon_LFMQ.sh.20230
752  11666330      - A     0:00    0   752   784    xx    13    32  0.0  0.0 ora_dism_LFMQ 
1080  27132932      - A    61:46    1  1084  1160    xx    78    80  0.0  0.0 mxg_sndf c LFMQ D 
1100  31916982      - A     0:30    2  1100  1200    xx    97   100  0.0  0.0 mxg_obsd -c LFMQ -OTHERD -i 10 -D 
16824  33292958      - A     0:00  453 16824 17404    xx   737   580  0.0  0.0 /DBMS/LFMQ/product/12c/bin/tnslsnr LFMQ -inherit 
21680  64684780      - A     0:00    0 21680 227184    xx 398736 205504  0.0  0.0 ora_tt01_LFMQ 
21936   2687940      - A     0:00    0 21936 227440    xx 398736 205504  0.0  0.0 ora_mman_LFMQ 
21936   3933090      - A     0:00    0 21936 227440    xx 398736 205504  0.0  0.0 ora_psp0_LFMQ 
21936   5440628      - A     0:00    0 21936 227440    xx 398736 205504  0.0  0.0 ora_vkrm_LFMQ 
21936  19071944      - A     0:00    0 21936 227440    xx 398736 205504  0.0  0.0 ora_clmn_LFMQ 
21936  25428778      - A     0:00    0 21936 227440    xx 398736 205504  0.0  0.0 ora_vktm_LFMQ 
21936  27657160      - A     0:00    0 21936 227440    xx 398736 205504  0.0  0.0 ora_pmon_LFMQ 
21936  32113800      - A     0:00    0 21936 227440    xx 398736 205504  0.0  0.0 ora_reco_LFMQ 
21936  32179106      - A     0:00    0 21936 227440    xx 398736 205504  0.0  0.0 ora_pman_LFMQ 
21936  34538574      - A     0:00    0 21936 227440    xx 398736 205504  0.0  0.0 ora_pxmn_LFMQ 
21936  59507318      - A     0:00    0 21936 227440    xx 398736 205504  0.0  0.0 ora_diag_LFMQ 
21936  59965698      - A     0:00    0 21936 227440    xx 398736 205504  0.0  0.0 ora_tmon_LFMQ 
21936  63635820      - A     0:00    0 21936 227440    xx 398736 205504  0.0  0.0 ora_smon_LFMQ 
22000  22414500      - A     0:00    0 22000 227504    xx 398736 205504  0.0  0.0 ora_mmon_LFMQ 
22256  61604748      - A     0:00    0 22256 227760    xx 398736 205504  0.0  0.0 ora_o000_LFMQ 
22448   3408896      - A     0:00    0 22448 227952    xx 398736 205504  0.0  0.0 ora_svcb_LFMQ 
22448   5571450      - A     0:06    0 22448 227952    xx 398736 205504  0.0  0.0 ora_mmnl_LFMQ 
22832   5702820      - A     0:01    0 22832 228336    xx 398736 205504  0.0  0.0 ora_dbrm_LFMQ 
24176   3539926      - A     0:01    0 24176 229680    xx 398736 205504  0.0  0.0 ora_fenc_LFMQ 
24816  52560520      - A     0:00    0 24816 230320    xx 398736 205504  0.0  0.0 ora_gen0_LFMQ 
25008  22938874      - A     0:00    0 25008 230512    xx 398736 205504  0.0  0.0 ora_rbal_LFMQ 
25200   9962302      - A     0:00    0 25200 230704    xx 398736 205504  0.0  0.0 ora_asmb_LFMQ 
25712  24183766      - A     0:00    0 25712 231216    xx 398736 205504  0.0  0.0 ora_ofsd_LFMQ 
25712  26805160      - A     0:00    0 25712 231216    xx 398736 205504  0.0  0.0 ora_gen1_LFMQ 
26032  11076438      - A     0:00    0 26032 231536    xx 398736 205504  0.0  0.0 ora_lgwr_LFMQ 
27248  64225762      - A     0:02    0 27248 232752    xx 398736 205504  0.0  0.0 oracleLFMQ (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)
27376   9438136      - A     0:00    0 27376 232880    xx 398736 205504  0.0  0.0 ora_ckpt_LFMQ 
28016   8520670      - A     0:00    0 28016 233520    xx 398736 205504  0.0  0.0 ora_lreg_LFMQ 
28848   1311664      - A     0:00    0 28848 234352    xx 398736 205504  0.0  0.0 ora_dbw3_LFMQ 
28848   3736714      - A     0:00    0 28848 234352    xx 398736 205504  0.0  0.0 ora_dbw1_LFMQ 
28848  14943182      - A     0:00    0 28848 234352    xx 398736 205504  0.0  0.0 ora_dbw5_LFMQ 
28848  61997408      - A     0:00    0 28848 234352    xx 398736 205504  0.0  0.0 ora_dbw6_LFMQ 
28848  65274420      - A     0:00    0 28848 234352    xx 398736 205504  0.0  0.0 ora_dbw7_LFMQ 
28848  65405338      - A     0:00    0 28848 234352    xx 398736 205504  0.0  0.0 ora_dbw4_LFMQ 
28848  65929692      - A     0:00    0 28848 234352    xx 398736 205504  0.0  0.0 ora_dbw2_LFMQ 
30896  24839072      - A     0:00    0 30896 236400    xx 398736 205504  0.0  0.0 ora_dbw0_LFMQ 
45104   1049490      - A     0:00    0 45104 250608    xx 398736 205504  0.0  0.0 ora_tt00_LFMQ 
45680   7537580      - A     0:00    0 45680 251184    xx 398736 205504  0.0  0.0 ora_arc3_LFMQ 
45680  53019128      - A     0:00    0 45680 251184    xx 398736 205504  0.0  0.0 ora_arc1_LFMQ 
46000  54722908      - A     0:00    0 46000 251504    xx 398736 205504  0.0  0.0 ora_arc0_LFMQ 
46000  55050626      - A     0:00    0 46000 251504    xx 398736 205504  0.0  0.0 ora_arc2_LFMQ 
63032  55575448      - A    1173:59 1328 63544 64148    xx  1118  1116  0.0  0.0 mxg_rts c LFMQ r D 
73456  18285624      - A     0:01    3 73456 278960    xx 398736 205504  0.0  0.0 oracleLFMQ (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)
75120   8717264      - A     0:25    0 75120 280624    xx 398736 205504  0.0  0.0 ora_dia0_LFMQ 
82096  20054886      - A     0:00    0 82096 287600    xx 398736 205504  0.0  0.0 ora_mark_LFMQ 
1616696  51118350      - A     5:16    1 1616696 1822200    xx 398736 205504  0.1  2.0 oracleLFMQ (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)
1616696  60621190      - A     5:05    0 1616696 1822200    xx 398736 205504  0.1  2.0 oracleLFMQ (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)
1682296  10027908      - A     4:11    1 1682296 1887800    xx 398736 205504  0.1  2.0 oracleLFMQ (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)
1682296  10421162      - A     4:33    2 1682296 1887800    xx 398736 205504  0.1  2.0 oracleLFMQ (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)
1682296  11601008      - A     4:58    2 1682296 1887800    xx 398736 205504  0.1  2.0 oracleLFMQ (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)
1682296  15074266      - A     5:00    2 1682296 1887800    xx 398736 205504  0.1  2.0 oracleLFMQ (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)
1682296  16974894      - A     5:21    0 1682296 1887800    xx 398736 205504  0.1  2.0 oracleLFMQ (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)
1682296  30736686      - A     5:14    0 1682296 1887800    xx 398736 205504  0.1  2.0 oracleLFMQ (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)
1682296  56426814      - A     5:24    0 1682296 1887800    xx 398736 205504  0.1  2.0 oracleLFMQ (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)
1682296  65012452      - A     4:47    1 1682296 1887800    xx 398736 205504  0.1  2.0 oracleLFMQ (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)
1682936  60948954      - A     5:36    1 1682936 1888440    xx 398736 205504  0.1  2.0 oracleLFMQ (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)
20014808  :TOTAL ( 19807064 private memory, 207744 shared code segments - TRS)

맨 마지막에 20014808 가 총 사이즈로 단위는 KB로 본 예제에서는 총 20G 정도 사용 중.

'OraclE' 카테고리의 다른 글

sqlarea 쿼리 확인 (0)	2022.07.21
SPM - 튜닝된 SQL의 실행계획을 등록하고 적용 하는 방법 (0)	2022.06.13
SPM(SQL PLAN MANAGEMENT) (0)	2022.06.13
OGG 동기화 테이블 추가 (0)	2022.04.04
active session history (0)	2022.01.30

sqlarea 쿼리 확인

타마마임팩트_쫀 2022. 7. 21. 14:43

2022. 7. 21. 14:43

SELECT *
FROM ( SELECT INST_ID, SQL_ID, HASH_VALUE, PARSING_SCHEMA_NAME SCHEMA, LENGTH(SQL_FULLTEXT) SQL_LENGTH, MODULE, BUFFER_GETS, EXECUTIONS,
ROUND(BUFFER_GETS/DECODE(EXECUTIONS, 0, NULL, EXECUTIONS)) "BUFFER_GET/EXEC",
ROUND(DISK_READS/DECODE(EXECUTIONS, 0, NULL, EXECUTIONS)) "DISK_READ/EXEC",
ROUND(ROWS_PROCESSED/DECODE(EXECUTIONS, 0, NULL, EXECUTIONS)) "ROWS_PROCESSED/EXEC",
ROUND(PARSE_CALLS/DECODE(EXECUTIONS, 0, NULL, EXECUTIONS)) "PARSE_CALL/EXEC",
ROUND(CPU_TIME/DECODE(EXECUTIONS, 0, NULL, EXECUTIONS)/1000000,2) "CPU_TIME(SEC)/EXEC",
ROUND(ELAPSED_TIME/DECODE(EXECUTIONS, 0, NULL, EXECUTIONS)/1000000,5) "ELAPSED(SEC)/EXEC",
ROUND(USER_IO_WAIT_TIME/DECODE(EXECUTIONS, 0, NULL, EXECUTIONS)/1000000,2) "IOWAIT/EXEC",
ROUND(CLUSTER_WAIT_TIME/DECODE(EXECUTIONS, 0, NULL, EXECUTIONS)/1000000,2) "CLWAIT/EXEC",
ROUND(APPLICATION_WAIT_TIME/DECODE(EXECUTIONS, 0, NULL, EXECUTIONS)/1000000,2) "APWAIT/EXEC",
ROUND(CONCURRENCY_WAIT_TIME/DECODE(EXECUTIONS, 0, NULL, EXECUTIONS)/1000000,2) "CCWAIT/EXEC",
FIRST_LOAD_TIME, LAST_LOAD_TIME, SQL_FULLTEXT
FROM GV$SQLAREA
WHERE 1=1
--AND INST_ID=2
--AND PARSING_SCHEMA_NAME NOT IN ('SYS','SYSTEM','DBSNMP','SYSMAN','WMSYS','EXFSYS','OGG','SKPDBA')
--AND PARSING_SCHEMA_NAME IN ('IDMS')
--AND MODULE NOT IN ('Orange for ORACLE Standard','Orange for ORACLE DBA - Loader','SQL*Plus','TOAD 10.5.1.3','Orange for ORACLE DBA','Orange for ORACLE (Unicode) DBA ','SQL Developer','SQLGateMain.exe','Orange for ORACLE Standard ','SQLGate for Oracle Developer 6.2.0.1','SQLGate Query Session','SQLGate 6.4.1.3','OrangeMain.EXE','SQL Developer')
--AND UPPER(SQL_FULLTEXT) NOT LIKE 'EXPLAIN%'
--AND UPPER(SQL_FULLTEXT) NOT LIKE 'SELECT 1 FROM DUAL%'
--AND UPPER(SQL_FULLTEXT) NOT LIKE '%ANALYZE%'
--AND UPPER(SQL_FULLTEXT) NOT LIKE 'EXPLAIN%'
--AND UPPER(SQL_FULLTEXT) NOT LIKE '%GETRECENTORDERLIST%'
--AND UPPER(SQL_FULLTEXT) LIKE '%CART%'
AND SQL_ID IN ('7b85a52pcgbuw')
--AND (ROUND(BUFFER_GETS/DECODE(EXECUTIONS, 0, NULL, EXECUTIONS)) > 10000 OR ROUND(DISK_READS/DECODE(EXECUTIONS, 0, NULL, EXECUTIONS)) > 10000)
AND EXECUTIONS >= 1
--ORDER BY SUBSTR(SQL_FULLTEXT,1,50)
--ORDER BY EXECUTIONS DESC
--ORDER BY ROUND(ELAPSED_TIME/DECODE(EXECUTIONS, 0, NULL, EXECUTIONS)/1000000,5) DESC
--ORDER BY ROUND(BUFFER_GETS/DECODE(EXECUTIONS, 0, NULL, EXECUTIONS)) DESC
--ORDER BY ROUND(CLUSTER_WAIT_TIME/DECODE(EXECUTIONS, 0, NULL, EXECUTIONS)/1000000,2) DESC
)
WHERE ROWNUM <= 1000;

'OraclE' 카테고리의 다른 글

오라클 메모리 사용 확인(AIX) (0)	2023.03.22
SPM - 튜닝된 SQL의 실행계획을 등록하고 적용 하는 방법 (0)	2022.06.13
SPM(SQL PLAN MANAGEMENT) (0)	2022.06.13
OGG 동기화 테이블 추가 (0)	2022.04.04
active session history (0)	2022.01.30

SPM - 튜닝된 SQL의 실행계획을 등록하고 적용 하는 방법

타마마임팩트_쫀 2022. 6. 13. 15:18

2022. 6. 13. 15:18

-- --------------------------------------------------------------------
-- SPM - 튜닝된 SQL의 실행계획을 등록하고 적용 하는 방법
-- --------------------------------------------------------------------

-- --------------------------------------------------------------------
-- test sql
-- --------------------------------------------------------------------
WITH RETRY AS (
    SELECT LMR.REQ_URL 
      FROM LST_MEMSYNC_RETRY LMR
         , LST_MEMSYNC_ERR LME
     WHERE LMR.TRAN_ID = LME.TRAN_ID
       AND PROC_STS_CD = 9500
       AND LMR.REQ_URL IN ( '/api/member/ext/join'         /* 회원가입 */
                          , '/api/member/ext/modify'       /* 회원수정 */
                          , '/api/member/ext/secede'       /* 회원탈퇴 */
                          , '/api/member/ext/integrate'    /* 회원통합 */
                          , '/api/member/ext/lastBuyInfo'  /* 최종구매일자 조회 */
                          )
       AND LMR.PROC_DT >= TO_DATE(TO_CHAR(SYSDATE - 1/24, 'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS')
)
SELECT * 
  FROM (
        SELECT COUNT(CASE WHEN REQ_URL = '/api/member/ext/join'        THEN 1 END) AS "1"   /* 회원가입 */
             , COUNT(CASE WHEN REQ_URL = '/api/member/ext/modify'      THEN 1 END) AS "2"  /* 회원수정 */
             , COUNT(CASE WHEN REQ_URL = '/api/member/ext/secede'      THEN 1 END) AS "3"  /* 회원탈퇴 */
             , COUNT(CASE WHEN REQ_URL = '/api/member/ext/integrate'   THEN 1 END) AS "4"  /* 회원휴면복구 */
             , COUNT(CASE WHEN REQ_URL = '/api/member/ext/lastBuyInfo' THEN 1 END) AS "5"  /* 회원휴면처리 */
             , COUNT(*) AS TOTAL_CNT
        FROM RETRY
  ) T
 WHERE T.TOTAL_CNT > 0;






SQL_ID  1s1tfdnkncynj, child number 2
-------------------------------------
WITH RETRY AS (     SELECT LMR.REQ_URL       FROM LST_MEMSYNC_RETRY LMR
         , LST_MEMSYNC_ERR LME      WHERE LMR.TRAN_ID = LME.TRAN_ID
   AND PROC_STS_CD = 9500        AND LMR.REQ_URL IN (
'/api/member/ext/join'         /* 회원가입 */                           ,
'/api/member/ext/modify'       /* 회원수정 */                           ,
'/api/member/ext/secede'       /* 회원탈퇴 */                           ,
'/api/member/ext/integrate'    /* 회원통합 */                           ,
'/api/member/ext/lastBuyInfo'  /* 최종구매일자 조회 */
 )        AND LMR.PROC_DT >= TO_DATE(TO_CHAR(SYSDATE - 1/24,
'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS') ) SELECT *   FROM (
SELECT COUNT(CASE WHEN REQ_URL = '/api/member/ext/join'        THEN 1
END) AS "1"   /* 회원가입 */              , COUNT(CASE WHEN REQ_URL =
'/api/member/ext/modify'      THEN 1 END) AS "2"  /* 회원수정 */
  , COUNT(CASE WHEN REQ_URL = '/api/member/ext/s   '

Plan hash value: 1100111422

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                   | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                        |      1 |      0 |00:00:00.01 |       4 |
|   1 |  VIEW                                   |                        |      1 |      0 |00:00:00.01 |       4 |
|*  2 |   FILTER                                |                        |      1 |      0 |00:00:00.01 |       4 |
|   3 |    SORT AGGREGATE                       |                        |      1 |      1 |00:00:00.01 |       4 |
|   4 |     NESTED LOOPS                        |                        |      1 |      0 |00:00:00.01 |       4 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| LST_MEMSYNC_RETRY      |      1 |      0 |00:00:00.01 |       4 |
|*  6 |       INDEX RANGE SCAN                  | IX03_LST_MEMSYNC_RETRY |      1 |      0 |00:00:00.01 |       4 |
|*  7 |      INDEX RANGE SCAN                   | IDX_LST_MEMSYNC_ERR_01 |      0 |      0 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(COUNT(*)>0)
   6 - access("LMR"."PROC_DT">=TO_DATE(TO_CHAR(SYSDATE@!-.0416666666666666666666666666666666666667,'YYYYMMD
              DHH24MISS'),'YYYYMMDDHH24MISS') AND "LMR"."PROC_DT" IS NOT NULL)
       filter((TO_NUMBER("PROC_STS_CD")=9500 AND INTERNAL_FUNCTION("LMR"."REQ_URL")))
   7 - access("LMR"."TRAN_ID"="LME"."TRAN_ID")




-- --------------------------------------------------------------------
-- SPM 등록
-- --------------------------------------------------------------------

SET SERVEROUTPUT ON
DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '1s1tfdnkncynj');
    
  DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/

Plans Loaded: 2

PL/SQL procedure successfully completed.




-- --------------------------------------------------------------------
-- SPM 등록 후 조회
-- --------------------------------------------------------------------

set linesize 150
col plan_name for a40
select sql_handle, plan_name, to_char(created, 'YYYY/MM/DD HH24:MI:SS') created, optimizer_cost, enabled, accepted, fixed 
from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                                CREATED             OPTIMIZER_COST ENA ACC FIX
------------------------------ ---------------------------------------- ------------------- -------------- --- --- ---
SQL_1f1d24da6058d80a           SQL_PLAN_1y794v9h5jq0a26ff63a8           2022/06/10 10:37:25              6 YES YES NO
SQL_1f1d24da6058d80a           SQL_PLAN_1y794v9h5jq0aa1311447           2022/06/10 10:37:25             43 YES YES NO







SQL_ID  1s1tfdnkncynj, child number 2
-------------------------------------
WITH RETRY AS (     SELECT LMR.REQ_URL       FROM LST_MEMSYNC_RETRY LMR
         , LST_MEMSYNC_ERR LME      WHERE LMR.TRAN_ID = LME.TRAN_ID
   AND PROC_STS_CD = 9500        AND LMR.REQ_URL IN (
'/api/member/ext/join'         /* 회원가입 */                           ,
'/api/member/ext/modify'       /* 회원수정 */                           ,
'/api/member/ext/secede'       /* 회원탈퇴 */                           ,
'/api/member/ext/integrate'    /* 회원통합 */                           ,
'/api/member/ext/lastBuyInfo'  /* 최종구매일자 조회 */
 )        AND LMR.PROC_DT >= TO_DATE(TO_CHAR(SYSDATE - 1/24,
'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS') ) SELECT *   FROM (
SELECT COUNT(CASE WHEN REQ_URL = '/api/member/ext/join'        THEN 1
END) AS "1"   /* 회원가입 */              , COUNT(CASE WHEN REQ_URL =
'/api/member/ext/modify'      THEN 1 END) AS "2"  /* 회원수정 */
  , COUNT(CASE WHEN REQ_URL = '/api/member/ext/s   '

Plan hash value: 1100111422

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                   | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                        |      1 |      0 |00:00:00.01 |       4 |
|   1 |  VIEW                                   |                        |      1 |      0 |00:00:00.01 |       4 |
|*  2 |   FILTER                                |                        |      1 |      0 |00:00:00.01 |       4 |
|   3 |    SORT AGGREGATE                       |                        |      1 |      1 |00:00:00.01 |       4 |
|   4 |     NESTED LOOPS                        |                        |      1 |      0 |00:00:00.01 |       4 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| LST_MEMSYNC_RETRY      |      1 |      0 |00:00:00.01 |       4 |
|*  6 |       INDEX RANGE SCAN                  | IX03_LST_MEMSYNC_RETRY |      1 |      0 |00:00:00.01 |       4 |
|*  7 |      INDEX RANGE SCAN                   | IDX_LST_MEMSYNC_ERR_01 |      0 |      0 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(COUNT(*)>0)
   6 - access("LMR"."PROC_DT">=TO_DATE(TO_CHAR(SYSDATE@!-.0416666666666666666666666666666666666667,'YYYYMMD
              DHH24MISS'),'YYYYMMDDHH24MISS') AND "LMR"."PROC_DT" IS NOT NULL)
       filter((TO_NUMBER("PROC_STS_CD")=9500 AND INTERNAL_FUNCTION("LMR"."REQ_URL")))
   7 - access("LMR"."TRAN_ID"="LME"."TRAN_ID")

Note
-----
   - SQL plan baseline SQL_PLAN_1y794v9h5jq0a26ff63a8 used for this statement


-- 정상적으로 SQL_PLAN_1y794v9h5jq0a26ff63a8 사용







-- --------------------------------------------------------------------
-- SQL 튜닝
-- --------------------------------------------------------------------

-- IX03_LST_MEMSYNC_RETRY 에서 IX02_LST_MEMSYNC_RETRY 를 사용하도록 변경

WITH RETRY AS (
    SELECT /*+ INDEX (LMR IX02_LST_MEMSYNC_RETRY) */
    	   LMR.REQ_URL 
      FROM LST_MEMSYNC_RETRY LMR
         , LST_MEMSYNC_ERR LME
     WHERE LMR.TRAN_ID = LME.TRAN_ID
       AND PROC_STS_CD = 9500
       AND LMR.REQ_URL IN ( '/api/member/ext/join'         /* 회원가입 */
                          , '/api/member/ext/modify'       /* 회원수정 */
                          , '/api/member/ext/secede'       /* 회원탈퇴 */
                          , '/api/member/ext/integrate'    /* 회원통합 */
                          , '/api/member/ext/lastBuyInfo'  /* 최종구매일자 조회 */
                          )
       AND LMR.PROC_DT >= TO_DATE(TO_CHAR(SYSDATE - 1/24, 'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS')
)
SELECT * 
  FROM (
        SELECT COUNT(CASE WHEN REQ_URL = '/api/member/ext/join'        THEN 1 END) AS "1"   /* 회원가입 */
             , COUNT(CASE WHEN REQ_URL = '/api/member/ext/modify'      THEN 1 END) AS "2"  /* 회원수정 */
             , COUNT(CASE WHEN REQ_URL = '/api/member/ext/secede'      THEN 1 END) AS "3"  /* 회원탈퇴 */
             , COUNT(CASE WHEN REQ_URL = '/api/member/ext/integrate'   THEN 1 END) AS "4"  /* 회원휴면복구 */
             , COUNT(CASE WHEN REQ_URL = '/api/member/ext/lastBuyInfo' THEN 1 END) AS "5"  /* 회원휴면처리 */
             , COUNT(*) AS TOTAL_CNT
        FROM RETRY
  ) T
 WHERE T.TOTAL_CNT > 0;



SQL_ID  6h3wa41tqv9sc, child number 0
-------------------------------------
WITH RETRY AS (     SELECT /*+ INDEX (LMR IX02_LST_MEMSYNC_RETRY) */
     LMR.REQ_URL       FROM LST_MEMSYNC_RETRY LMR          ,
LST_MEMSYNC_ERR LME      WHERE LMR.TRAN_ID = LME.TRAN_ID        AND
PROC_STS_CD = 9500        AND LMR.REQ_URL IN ( '/api/member/ext/join'
      /* 회원가입 */                           , '/api/member/ext/modify'
    /* 회원수정 */                           , '/api/member/ext/secede'
  /* 회원탈퇴 */                           , '/api/member/ext/integrate'
/* 회원통합 */                           , '/api/member/ext/lastBuyInfo'
/* 최종구매일자 조회 */                           )        AND LMR.PROC_DT >=
TO_DATE(TO_CHAR(SYSDATE - 1/24, 'YYYYMMDDHH24MISS'),
'YYYYMMDDHH24MISS') ) SELECT *   FROM (         SELECT COUNT(CASE WHEN
REQ_URL = '/api/member/ext/join'        THEN 1 END) AS "1"   /* 회원가입 */
             , COUNT(CASE WHEN REQ_URL = '/api/member/ext/modify'
THEN 1 END) AS "2"  /* 회원수정 */

Plan hash value: 1914046030

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                   | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                        |      1 |      0 |00:00:00.01 |       4 |
|   1 |  VIEW                                   |                        |      1 |      0 |00:00:00.01 |       4 |
|*  2 |   FILTER                                |                        |      1 |      0 |00:00:00.01 |       4 |
|   3 |    SORT AGGREGATE                       |                        |      1 |      1 |00:00:00.01 |       4 |
|   4 |     NESTED LOOPS                        |                        |      1 |      0 |00:00:00.01 |       4 |
|*  5 |      TABLE ACCESS BY INDEX ROWID BATCHED| LST_MEMSYNC_RETRY      |      1 |      0 |00:00:00.01 |       4 |
|*  6 |       INDEX RANGE SCAN                  | IX02_LST_MEMSYNC_RETRY |      1 |      0 |00:00:00.01 |       4 |
|*  7 |      INDEX RANGE SCAN                   | IDX_LST_MEMSYNC_ERR_01 |      0 |      0 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(COUNT(*)>0)
   5 - filter((TO_NUMBER("PROC_STS_CD")=9500 AND INTERNAL_FUNCTION("LMR"."REQ_URL")))
   6 - access("LMR"."PROC_DT">=TO_DATE(TO_CHAR(SYSDATE@!-.0416666666666666666666666666666666666667,'YYYYMMD
              DHH24MISS'),'YYYYMMDDHH24MISS') AND "LMR"."PROC_DT" IS NOT NULL)
   7 - access("LMR"."TRAN_ID"="LME"."TRAN_ID")





-- --------------------------------------------------------------------
-- 튜닝 된 sql 을 SPM 등록
-- --------------------------------------------------------------------

-- sql_id 와 plan_hash_value 는 튜닝된 내용으로 기입하고
-- sql_handle 은 원본 쿼리가 등록된 SPM 핸들 값을 넣는다.
-- 튜닝된 plan을 사용하도록 fixed 를 yes 로 고정한다.

SET SERVEROUTPUT ON
DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '6h3wa41tqv9sc',
    plan_hash_value => 1914046030,
    sql_handle => 'SQL_1f1d24da6058d80a',
    fixed => 'YES',
	enabled => 'YES');
    
  DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/

Plans Loaded: 1

PL/SQL procedure successfully completed.






set linesize 150
col plan_name for a40
select sql_handle, plan_name, to_char(created, 'YYYY/MM/DD HH24:MI:SS') created, optimizer_cost, enabled, accepted, fixed 
from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                                CREATED             OPTIMIZER_COST ENA ACC FIX
------------------------------ ---------------------------------------- ------------------- -------------- --- --- ---
SQL_1f1d24da6058d80a           SQL_PLAN_1y794v9h5jq0a26ff63a8           2022/06/10 10:37:25              6 YES YES NO
SQL_1f1d24da6058d80a           SQL_PLAN_1y794v9h5jq0aa1311447           2022/06/10 10:41:44             43 YES YES YES





-- --------------------------------------------------------------------
-- 튜닝 전 sql 을 SPM 에서 비활성화
-- --------------------------------------------------------------------

SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    SQL_HANDLE => 'SQL_1f1d24da6058d80a',
    PLAN_NAME => 'SQL_PLAN_1y794v9h5jq0a26ff63a8',
    ATTRIBUTE_NAME => 'ENABLED',
    ATTRIBUTE_VALUE => 'NO');
    
  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

Plans Altered: 1

PL/SQL procedure successfully completed.




set linesize 150
col plan_name for a40
select sql_handle, plan_name, to_char(created, 'YYYY/MM/DD HH24:MI:SS') created, optimizer_cost, enabled, accepted, fixed 
from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                                CREATED             OPTIMIZER_COST ENA ACC FIX
------------------------------ ---------------------------------------- ------------------- -------------- --- --- ---
SQL_1f1d24da6058d80a           SQL_PLAN_1y794v9h5jq0a26ff63a8           2022/06/10 10:37:25              6 NO  YES NO
SQL_1f1d24da6058d80a           SQL_PLAN_1y794v9h5jq0aa1311447           2022/06/10 10:41:44             43 YES YES YES






-- --------------------------------------------------------------------
-- 원본 SQL 수행하여 튜닝된 plan 사용 하는지 확인
-- --------------------------------------------------------------------

WITH RETRY AS (
    SELECT LMR.REQ_URL 
      FROM LST_MEMSYNC_RETRY LMR
         , LST_MEMSYNC_ERR LME
     WHERE LMR.TRAN_ID = LME.TRAN_ID
       AND PROC_STS_CD = 9500
       AND LMR.REQ_URL IN ( '/api/member/ext/join'         /* 회원가입 */
                          , '/api/member/ext/modify'       /* 회원수정 */
                          , '/api/member/ext/secede'       /* 회원탈퇴 */
                          , '/api/member/ext/integrate'    /* 회원통합 */
                          , '/api/member/ext/lastBuyInfo'  /* 최종구매일자 조회 */
                          )
       AND LMR.PROC_DT >= TO_DATE(TO_CHAR(SYSDATE - 1/24, 'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS')
)
SELECT * 
  FROM (
        SELECT COUNT(CASE WHEN REQ_URL = '/api/member/ext/join'        THEN 1 END) AS "1"   /* 회원가입 */
             , COUNT(CASE WHEN REQ_URL = '/api/member/ext/modify'      THEN 1 END) AS "2"  /* 회원수정 */
             , COUNT(CASE WHEN REQ_URL = '/api/member/ext/secede'      THEN 1 END) AS "3"  /* 회원탈퇴 */
             , COUNT(CASE WHEN REQ_URL = '/api/member/ext/integrate'   THEN 1 END) AS "4"  /* 회원휴면복구 */
             , COUNT(CASE WHEN REQ_URL = '/api/member/ext/lastBuyInfo' THEN 1 END) AS "5"  /* 회원휴면처리 */
             , COUNT(*) AS TOTAL_CNT
        FROM RETRY
  ) T
 WHERE T.TOTAL_CNT > 0;



SQL_ID  1s1tfdnkncynj, child number 3
-------------------------------------
WITH RETRY AS (     SELECT LMR.REQ_URL       FROM LST_MEMSYNC_RETRY LMR
         , LST_MEMSYNC_ERR LME      WHERE LMR.TRAN_ID = LME.TRAN_ID
   AND PROC_STS_CD = 9500        AND LMR.REQ_URL IN (
'/api/member/ext/join'         /* 회원가입 */                           ,
'/api/member/ext/modify'       /* 회원수정 */                           ,
'/api/member/ext/secede'       /* 회원탈퇴 */                           ,
'/api/member/ext/integrate'    /* 회원통합 */                           ,
'/api/member/ext/lastBuyInfo'  /* 최종구매일자 조회 */
 )        AND LMR.PROC_DT >= TO_DATE(TO_CHAR(SYSDATE - 1/24,
'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS') ) SELECT *   FROM (
SELECT COUNT(CASE WHEN REQ_URL = '/api/member/ext/join'        THEN 1
END) AS "1"   /* 회원가입 */              , COUNT(CASE WHEN REQ_URL =
'/api/member/ext/modify'      THEN 1 END) AS "2"  /* 회원수정 */
  , COUNT(CASE WHEN REQ_URL = '/api/member/ext/s   '

Plan hash value: 1914046030

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                   | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                        |      1 |      0 |00:00:00.01 |       4 |
|   1 |  VIEW                                   |                        |      1 |      0 |00:00:00.01 |       4 |
|*  2 |   FILTER                                |                        |      1 |      0 |00:00:00.01 |       4 |
|   3 |    SORT AGGREGATE                       |                        |      1 |      1 |00:00:00.01 |       4 |
|   4 |     NESTED LOOPS                        |                        |      1 |      0 |00:00:00.01 |       4 |
|*  5 |      TABLE ACCESS BY INDEX ROWID BATCHED| LST_MEMSYNC_RETRY      |      1 |      0 |00:00:00.01 |       4 |
|*  6 |       INDEX RANGE SCAN                  | IX02_LST_MEMSYNC_RETRY |      1 |      0 |00:00:00.01 |       4 |
|*  7 |      INDEX RANGE SCAN                   | IDX_LST_MEMSYNC_ERR_01 |      0 |      0 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(COUNT(*)>0)
   5 - filter((TO_NUMBER("PROC_STS_CD")=9500 AND INTERNAL_FUNCTION("LMR"."REQ_URL")))
   6 - access("LMR"."PROC_DT">=TO_DATE(TO_CHAR(SYSDATE@!-.0416666666666666666666666666666666666667,'YYYYMMD
              DHH24MISS'),'YYYYMMDDHH24MISS') AND "LMR"."PROC_DT" IS NOT NULL)
   7 - access("LMR"."TRAN_ID"="LME"."TRAN_ID")

Note
-----
   - SQL plan baseline SQL_PLAN_1y794v9h5jq0aa1311447 used for this statement


-- 튜닝된 plan 의 인덱스와 SQL_PLAN_1y794v9h5jq0aa1311447 을 사용하는것을 확인

'OraclE' 카테고리의 다른 글

오라클 메모리 사용 확인(AIX) (0)	2023.03.22
sqlarea 쿼리 확인 (0)	2022.07.21
SPM(SQL PLAN MANAGEMENT) (0)	2022.06.13
OGG 동기화 테이블 추가 (0)	2022.04.04
active session history (0)	2022.01.30

SPM(SQL PLAN MANAGEMENT)

타마마임팩트_쫀 2022. 6. 13. 14:54

2022. 6. 13. 14:54

-- SPM(SQL PLAN MANAGEMENT) 
-- SPM은 baseline를 DB내에 여러개의 Plan을 저장 해 놓고 검증된 실행계획만 사용할 수 있도록 하는 기능이다. 


-- --------------------------------------------------------------------
-- 기본 설정
-- --------------------------------------------------------------------
set linesize 150
col NAME_COL_PLUS_SHOW_PARAM for a40
col VALUE_COL_PLUS_SHOW_PARAM for a25
show parameter sql_plan


NAME_COL_PLUS_SHOW_PARAM                 TYPE        VALUE_COL_PLUS_SHOW_PARAM
---------------------------------------- ----------- -------------------------
optimizer_capture_sql_plan_baselines     boolean     FALSE
optimizer_use_sql_plan_baselines         boolean     TRUE

-- OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES => TRUE : 자동수집, FALSE : 수동 수집 , default FALSE
-- OPTIMIZER_USE_SQL_PLAN_BASELINES => BASELINE 등록 이후 SPM PLAN 이용 할지 여부 (TRUE : 사용) 




-- --------------------------------------------------------------------
-- test sql
-- --------------------------------------------------------------------
WITH RETRY AS (
    SELECT LMR.REQ_URL 
      FROM LST_MEMSYNC_RETRY LMR
         , LST_MEMSYNC_ERR LME
     WHERE LMR.TRAN_ID = LME.TRAN_ID
       AND PROC_STS_CD = 9500
       AND LMR.REQ_URL IN ( '/api/member/ext/join'         /* 회원가입 */
                          , '/api/member/ext/modify'       /* 회원수정 */
                          , '/api/member/ext/secede'       /* 회원탈퇴 */
                          , '/api/member/ext/integrate'    /* 회원통합 */
                          , '/api/member/ext/lastBuyInfo'  /* 최종구매일자 조회 */
                          )
       AND LMR.PROC_DT >= TO_DATE(TO_CHAR(SYSDATE - 1/24, 'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS')
)
SELECT * 
  FROM (
        SELECT COUNT(CASE WHEN REQ_URL = '/api/member/ext/join'        THEN 1 END) AS "1"   /* 회원가입 */
             , COUNT(CASE WHEN REQ_URL = '/api/member/ext/modify'      THEN 1 END) AS "2"  /* 회원수정 */
             , COUNT(CASE WHEN REQ_URL = '/api/member/ext/secede'      THEN 1 END) AS "3"  /* 회원탈퇴 */
             , COUNT(CASE WHEN REQ_URL = '/api/member/ext/integrate'   THEN 1 END) AS "4"  /* 회원휴면복구 */
             , COUNT(CASE WHEN REQ_URL = '/api/member/ext/lastBuyInfo' THEN 1 END) AS "5"  /* 회원휴면처리 */
             , COUNT(*) AS TOTAL_CNT
        FROM RETRY
  ) T
 WHERE T.TOTAL_CNT > 0;





-- --------------------------------------------------------------------
-- OLD plan 
-- --------------------------------------------------------------------

SQL_ID  1s1tfdnkncynj, child number 0
-------------------------------------
WITH RETRY AS (     SELECT LMR.REQ_URL       FROM LST_MEMSYNC_RETRY LMR
         , LST_MEMSYNC_ERR LME      WHERE LMR.TRAN_ID = LME.TRAN_ID
   AND PROC_STS_CD = 9500        AND LMR.REQ_URL IN (
'/api/member/ext/join'         /* 회원가입 */                           ,
'/api/member/ext/modify'       /* 회원수정 */                           ,
'/api/member/ext/secede'       /* 회원탈퇴 */                           ,
'/api/member/ext/integrate'    /* 회원통합 */                           ,
'/api/member/ext/lastBuyInfo'  /* 최종구매일자 조회 */
 )        AND LMR.PROC_DT >= TO_DATE(TO_CHAR(SYSDATE - 1/24,
'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS') ) SELECT *   FROM (
SELECT COUNT(CASE WHEN REQ_URL = '/api/member/ext/join'        THEN 1
END) AS "1"   /* 회원가입 */              , COUNT(CASE WHEN REQ_URL =
'/api/member/ext/modify'      THEN 1 END) AS "2"  /* 회원수정 */
  , COUNT(CASE WHEN REQ_URL = '/api/member/ext/s   '

Plan hash value: 1914046030

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                   | Starts | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                        |      1 |      0 |00:00:00.01 |       4 |      3 |
|   1 |  VIEW                                   |                        |      1 |      0 |00:00:00.01 |       4 |      3 |
|*  2 |   FILTER                                |                        |      1 |      0 |00:00:00.01 |       4 |      3 |
|   3 |    SORT AGGREGATE                       |                        |      1 |      1 |00:00:00.01 |       4 |      3 |
|   4 |     NESTED LOOPS                        |                        |      1 |      0 |00:00:00.01 |       4 |      3 |
|*  5 |      TABLE ACCESS BY INDEX ROWID BATCHED| LST_MEMSYNC_RETRY      |      1 |      0 |00:00:00.01 |       4 |      3 |
|*  6 |       INDEX RANGE SCAN                  | IX02_LST_MEMSYNC_RETRY |      1 |      0 |00:00:00.01 |       4 |      3 |
|*  7 |      INDEX RANGE SCAN                   | IDX_LST_MEMSYNC_ERR_01 |      0 |      0 |00:00:00.01 |       0 |      0 |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(COUNT(*)>0)
   5 - filter((TO_NUMBER("PROC_STS_CD")=9500 AND INTERNAL_FUNCTION("LMR"."REQ_URL")))
   6 - access("LMR"."PROC_DT">=TO_DATE(TO_CHAR(SYSDATE@!-.0416666666666666666666666666666666666667,'YYYYMMDDHH24MISS
              '),'YYYYMMDDHH24MISS') AND "LMR"."PROC_DT" IS NOT NULL)
   7 - access("LMR"."TRAN_ID"="LME"."TRAN_ID")







-- --------------------------------------------------------------------
-- 인덱스 확인
-- --------------------------------------------------------------------

===============================
====== INDEX COLUMN CHECK =====
===============================

OWNER        INDEX_NAME                   COLUMN_NAME                  PARTITIONED        MB TABLESPACE_NAME                STATUS   UNIQUENES GLO
------------ ---------------------------- ---------------------------- ------------ -------- ------------------------------ -------- --------- ---
LFMMEM       IX02_LST_MEMSYNC_RETRY       PROC_DT                      NO              3,122 TS_LFM_IDX                     VALID    NONUNIQUE YES
                                          REQ_FAIL_CNT

             IX03_LST_MEMSYNC_RETRY       PROC_DT                      NO              7,085 TS_LFM_IDX                     VALID    NONUNIQUE YES
                                          PROC_STS_CD
                                          REQ_URL

             IX_LST_MEMSYNC_RETRY_01      REQ_URL                      NO              8,330 TS_LFM_IDX                     VALID    NONUNIQUE YES
                                          PROC_STS_CD
                                          REQ_FAIL_CNT

             PK_LST_MEMSYNC_RETRY         TRAN_ID                      NO              7,213 TS_LFM_IDX                     VALID    UNIQUE    YES







-- --------------------------------------------------------------------
-- NEW plan 
-- --------------------------------------------------------------------

SQL_ID  1s1tfdnkncynj, child number 1
-------------------------------------
WITH RETRY AS (     SELECT LMR.REQ_URL       FROM LST_MEMSYNC_RETRY LMR
         , LST_MEMSYNC_ERR LME      WHERE LMR.TRAN_ID = LME.TRAN_ID
   AND PROC_STS_CD = 9500        AND LMR.REQ_URL IN (
'/api/member/ext/join'         /* 회원가입 */                           ,
'/api/member/ext/modify'       /* 회원수정 */                           ,
'/api/member/ext/secede'       /* 회원탈퇴 */                           ,
'/api/member/ext/integrate'    /* 회원통합 */                           ,
'/api/member/ext/lastBuyInfo'  /* 최종구매일자 조회 */
 )        AND LMR.PROC_DT >= TO_DATE(TO_CHAR(SYSDATE - 1/24,
'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS') ) SELECT *   FROM (
SELECT COUNT(CASE WHEN REQ_URL = '/api/member/ext/join'        THEN 1
END) AS "1"   /* 회원가입 */              , COUNT(CASE WHEN REQ_URL =
'/api/member/ext/modify'      THEN 1 END) AS "2"  /* 회원수정 */
  , COUNT(CASE WHEN REQ_URL = '/api/member/ext/s   '

Plan hash value: 1100111422

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                   | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                        |      1 |      0 |00:00:00.01 |       4 |
|   1 |  VIEW                                   |                        |      1 |      0 |00:00:00.01 |       4 |
|*  2 |   FILTER                                |                        |      1 |      0 |00:00:00.01 |       4 |
|   3 |    SORT AGGREGATE                       |                        |      1 |      1 |00:00:00.01 |       4 |
|   4 |     NESTED LOOPS                        |                        |      1 |      0 |00:00:00.01 |       4 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| LST_MEMSYNC_RETRY      |      1 |      0 |00:00:00.01 |       4 |
|*  6 |       INDEX RANGE SCAN                  | IX03_LST_MEMSYNC_RETRY |      1 |      0 |00:00:00.01 |       4 |
|*  7 |      INDEX RANGE SCAN                   | IDX_LST_MEMSYNC_ERR_01 |      0 |      0 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(COUNT(*)>0)
   6 - access("LMR"."PROC_DT">=TO_DATE(TO_CHAR(SYSDATE@!-.0416666666666666666666666666666666666667,'YYYYMMD
              DHH24MISS'),'YYYYMMDDHH24MISS') AND "LMR"."PROC_DT" IS NOT NULL)
       filter((TO_NUMBER("PROC_STS_CD")=9500 AND INTERNAL_FUNCTION("LMR"."REQ_URL")))
   7 - access("LMR"."TRAN_ID"="LME"."TRAN_ID")






-- --------------------------------------------------------------------
-- 이전 플랜으로 SPM 등록
-- --------------------------------------------------------------------

-- sql id와 Plan hash value를 알고 있을때 
SET SERVEROUTPUT ON
DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '1s1tfdnkncynj',
    plan_hash_value => 1914046030);
    
  DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/

Plans Loaded: 1

PL/SQL procedure successfully completed.





-- sql id만 알고 있을때
SET SERVEROUTPUT ON
DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '1s1tfdnkncynj');
    
  DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/




-- --------------------------------------------------------------------
-- 등록된 SPB 조회
-- --------------------------------------------------------------------
-- SQL_HANDLE : SQL_ID에 대한 SPB 대표이름
-- PLAN_NAME : SQL_HANDLE에 종속된 Plan 이름
-- ENABLED : YES 일때 옵티마이저가 사용 가능
-- ACCEPT : PLAN_NAME 중 ACCEPT가 YES인 경우에만 SPB로 사용됨
-- FIXED : SQL_HANDLE 내에 여러 PLAN_NAME이 있을 경우 FIXED가 YES인 PLAN_NAME만 사용됨

set linesize 150
col plan_name for a40
select sql_handle, plan_name, to_char(created, 'YYYY/MM/DD HH24:MI:SS') created, enabled, accepted, fixed 
from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                                CREATED             ENA ACC FIX
------------------------------ ---------------------------------------- ------------------- --- --- ---
SQL_1f1d24da6058d80a           SQL_PLAN_1y794v9h5jq0a26ff63a8           2022/06/09 13:46:24 YES NO  NO
SQL_1f1d24da6058d80a           SQL_PLAN_1y794v9h5jq0aa1311447           2022/06/09 13:45:46 YES YES NO




-- --------------------------------------------------------------------
-- 쿼리 재수행 후 이전 plan 사용 하는지 확인
-- --------------------------------------------------------------------

SQL_ID  1s1tfdnkncynj, child number 2
-------------------------------------
WITH RETRY AS (     SELECT LMR.REQ_URL       FROM LST_MEMSYNC_RETRY LMR
         , LST_MEMSYNC_ERR LME      WHERE LMR.TRAN_ID = LME.TRAN_ID
   AND PROC_STS_CD = 9500        AND LMR.REQ_URL IN (
'/api/member/ext/join'         /* 회원가입 */                           ,
'/api/member/ext/modify'       /* 회원수정 */                           ,
'/api/member/ext/secede'       /* 회원탈퇴 */                           ,
'/api/member/ext/integrate'    /* 회원통합 */                           ,
'/api/member/ext/lastBuyInfo'  /* 최종구매일자 조회 */
 )        AND LMR.PROC_DT >= TO_DATE(TO_CHAR(SYSDATE - 1/24,
'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS') ) SELECT *   FROM (
SELECT COUNT(CASE WHEN REQ_URL = '/api/member/ext/join'        THEN 1
END) AS "1"   /* 회원가입 */              , COUNT(CASE WHEN REQ_URL =
'/api/member/ext/modify'      THEN 1 END) AS "2"  /* 회원수정 */
  , COUNT(CASE WHEN REQ_URL = '/api/member/ext/s   '

Plan hash value: 1914046030

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                   | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                        |      1 |      0 |00:00:00.01 |       4 |
|   1 |  VIEW                                   |                        |      1 |      0 |00:00:00.01 |       4 |
|*  2 |   FILTER                                |                        |      1 |      0 |00:00:00.01 |       4 |
|   3 |    SORT AGGREGATE                       |                        |      1 |      1 |00:00:00.01 |       4 |
|   4 |     NESTED LOOPS                        |                        |      1 |      0 |00:00:00.01 |       4 |
|*  5 |      TABLE ACCESS BY INDEX ROWID BATCHED| LST_MEMSYNC_RETRY      |      1 |      0 |00:00:00.01 |       4 |
|*  6 |       INDEX RANGE SCAN                  | IX02_LST_MEMSYNC_RETRY |      1 |      0 |00:00:00.01 |       4 |
|*  7 |      INDEX RANGE SCAN                   | IDX_LST_MEMSYNC_ERR_01 |      0 |      0 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(COUNT(*)>0)
   5 - filter((TO_NUMBER("PROC_STS_CD")=9500 AND INTERNAL_FUNCTION("LMR"."REQ_URL")))
   6 - access("LMR"."PROC_DT">=TO_DATE(TO_CHAR(SYSDATE@!-.0416666666666666666666666666666666666667,'YYYYMMD
              DHH24MISS'),'YYYYMMDDHH24MISS') AND "LMR"."PROC_DT" IS NOT NULL)
   7 - access("LMR"."TRAN_ID"="LME"."TRAN_ID")

Note
-----
   - SQL plan baseline SQL_PLAN_1y794v9h5jq0aa1311447 used for this statement


-- Note 부분에 SQL plan baseline 에서 SQL_PLAN_1y794v9h5jq0aa1311447 사용하고 있다고 표기







-- --------------------------------------------------------------------
-- SPM 삭제
-- --------------------------------------------------------------------

SET SERVEROUTPUT ON
DECLARE
  l_plans_dropped  PLS_INTEGER;
BEGIN
  l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (sql_handle => 'SQL_1f1d24da6058d80a');
  DBMS_OUTPUT.put_line('Plans Dropped: ' || l_plans_dropped);
END;
/

Plans Dropped: 2

PL/SQL procedure successfully completed.

'OraclE' 카테고리의 다른 글

sqlarea 쿼리 확인 (0)	2022.07.21
SPM - 튜닝된 SQL의 실행계획을 등록하고 적용 하는 방법 (0)	2022.06.13
OGG 동기화 테이블 추가 (0)	2022.04.04
active session history (0)	2022.01.30
orange plan 테이블 생성 (0)	2022.01.30

PREV 이전 1 2 3 4 5 ···53 NEXT 다음

JJON's StorY

JJON's StorY

오라클 메모리 사용 확인(AIX)

'OraclE' 카테고리의 다른 글

sqlarea 쿼리 확인

'OraclE' 카테고리의 다른 글

SPM - 튜닝된 SQL의 실행계획을 등록하고 적용 하는 방법

'OraclE' 카테고리의 다른 글

SPM(SQL PLAN MANAGEMENT)

'OraclE' 카테고리의 다른 글

+ Recent posts

티스토리툴바