라클에서 성능개선 대상 식별을 위해 모니터링할 때 사용하면 유요한 SQL입니다.
1. V$SQLAREA에서 Top SQL 식별
SELECT ROUND(BUFFER_GETS/EXECUTIONS) AS BUFFER_GETS_PER_EXEC, -- 1회 실행당 읽은 블록 수
ROUND(ELAPSED_TIME/(EXECUTIONS*1000000),2) AS "ELAPSED_TIME_PER_EXEC(Sec)",
-- 1회 실행당 처리시간(초단위)
ROUND((ROWS_PROCESSED/EXECUTIONS)) AS ROWS_PER_EXEC, -- 1회 실행당 처리된 건수
A.*
FROM (SELECT DECODE(EXECUTIONS,NULL,1,0,1,EXECUTIONS) AS EXECUTIONS,
-- 누적 실행 횟수
BUFFER_GETS, -- 누적 읽은 블록 수
DISK_READS, -- 누적 디스크 읽는 수
FETCHES, -- 누적 Fetch 수
ROWS_PROCESSED, -- 누적 처리한 레코드 수
CPU_TIME, -- 누적 CPU사용 시간(microsecond)
ELAPSED_TIME, -- 누적 SQL 처리시간(microsecond)
SQL_TEXT, -- SQL 텍스트(최대 1,000바이트)
SQL_ID,
ADDRESS,
HASH_VALUE
FROM V$SQLAREA
ORDER BY BUFFER_GETS DESC
) A
WHERE ROWNUM < 20 ;
2. V$SESSION을 사용한 현재 세션 상태 조회
SELECT S.SID AS SID,
S.SERIAL# AS "Serial#",
S.USERNAME,
DECODE(S.COMMAND, '0', 'NO', '1', 'Cr Tab', '2', 'Insert',
'3', 'Select', '6', 'Update', '7', 'Delete', '9', 'Create Idx',
'10', 'Drop Idx', '15', 'Alter Tbl', '24', 'Create Proc',
'32', 'Create Link', '33', 'Drop Link', '36', 'Create RBS',
'37', 'Alter RBS', '38', 'Drop RBS', '40', 'Alter TBS',
'41', 'Drop TBS', '42', 'Alter Sess', '45', 'Rollback',
'47', 'PL/SQL Exe', '62', 'Anal Table', '63', 'Anal Index',
'85', 'Truncate', 'Unkown Command') Command,
S.MACHINE ||'['|| S.PROGRAM||']' "Client program",
ROUND(Q.BUFFER_GETS/Q.EXECUTIONS, 2) AS "Buffer gets per exec",
W.EVENT || ': ' || S.P1TEXT || ' ' || S.P1 || ',' || S.P2TEXT || ' ' || S.P2 || ','
|| S.P3TEXT || ' ' || S.P3 || ',WAITING:' || S.WAIT_TIME AS "Waiting on",
S.SQL_ID AS "SQL ID",
Q.SQL_TEXT AS "SQL Text"
FROM V$SESSION S,
V$SESSION_WAIT W,
V$SQLAREA Q
WHERE W.WAIT_TIME = 0
AND W.SID = S.SID
AND S.STATUS = 'ACTIVE'
AND S.TYPE <> 'BACKGROUND'
AND S.SQL_ID = Q.SQL_ID
ORDER BY 4;
3. V$ACTIVE_SESSION_HISTORY를 이용한 과거 세션 상태 조회
SELECT SAMPLE_TIME AS "Time",
SESSION_ID AS ID,
SESSION_SERIAL# AS "Serial#",
(SELECT USERNAME FROM ALL_USERS B WHERE B.USER_ID = A.USER_ID) AS "User name",
SQL_OPNAME AS "Command",
MACHINE ||'['|| PROGRAM||']' "Client program",
CASE WHEN EVENT IS NULL THEN NULL ELSE
EVENT || ': ' || P1TEXT || ' ' || P1 || ',' || P2TEXT || ' ' || P2 || ','
|| P3TEXT || ' ' || P3 ||',WAITING:' || WAIT_TIME
END AS "Waiting on",
SQL_ID AS "SQL ID",
(SELECT SQL_TEXT FROM V$SQLAREA B WHERE B.SQL_ID = A.SQL_ID) AS "SQL Text"
FROM V$ACTIVE_SESSION_HISTORY A
WHERE SAMPLE_TIME BETWEEN TO_TIMESTAMP('20151028 230029', 'YYYYMMDD HH24MISS')
AND TO_TIMESTAMP('20151020 230031', 'YYYYMMDD HH24MISS')
AND SESSION_TYPE != 'BACKGROUND'
ORDER BY 6;
4. 현재 수행중인 SQL 수행 상태 보기
<오랜지용>
SELECT DBMS_LOB.SUBSTR(TEXT, 3000, 1) AS TEXT1,
DBMS_LOB.SUBSTR(TEXT, 3000, 3001) AS TEXT2,
DBMS_LOB.SUBSTR(TEXT, 3000, 6001) AS TEXT3,
DBMS_LOB.SUBSTR(TEXT, 3000, 9001) AS TEXT4,
DBMS_LOB.SUBSTR(TEXT, 3000, 12001) AS TEXT5,
DBMS_LOB.SUBSTR(TEXT, 3000, 15001) AS TEXT6,
DBMS_LOB.SUBSTR(TEXT, 3000, 18001) AS TEXT7,
DBMS_LOB.SUBSTR(TEXT, 3000, 21001) AS TEXT8
FROM (SELECT
DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id=>'djwgxd2t0adb7', event_detail=>'YES') AS TEXT
FROM DUAL
);
<SQLPlus 용>
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM on
SELECT
DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id=>'djwgxd2t0adb7', event_detail=>'YES')
FROM DUAL;
4. 락 대기 상태 확인
SELECT /*+ NO_MERGE(V) ORDERED */
DECODE(V.HOLD_SID, NULL, '', '(' || V.INST_ID || ')' || V.HOLD_SID) "Hold Sid",
DECODE(V.WAIT_SID, NULL, '', '^', '▽',
'(' || V.INST_ID || ')' || V.WAIT_SID) "Wait Sid",
V.GB "HW Type",
SW.SECONDS_IN_WAIT "Wait Time",
V.TYPE "Lock Type",
DECODE(V.LMODE, 0, 'None', -- 'None' ,
1, 'Null', -- 'Null' ,
2, 'Row Sh', -- 'Row Share' ,
3, 'Row Ex', -- 'Row Exclusive' ,
4, 'Share', -- 'Share' ,
5, 'Sh R X', -- 'Share Row Exclusive' ,
6, 'Ex', -- 'Exclusive' ,
TO_CHAR(V.LMODE)) "Hold Lock Mode",
DECODE(V.REQUEST, 0, 'None', -- 'None' ,
1, 'Null', -- 'Null' ,
2, 'Row Sh', -- 'Row Share' ,
3, 'Row Ex', -- 'Row Exclusive' ,
4, 'Share', -- 'Share' ,
5, 'Sh R X', -- 'Share Row Exclusive' ,
6, 'Ex', -- 'Exclusive' ,
TO_CHAR(V.REQUEST)) "Request Lock Mode",
(SELECT OBJECT_NAME || '(' || SUBSTR(OBJECT_TYPE, 1, 1) || ')'
FROM DBA_OBJECTS DO
WHERE DO.OBJECT_ID = S.ROW_WAIT_OBJ#) "Locked Object",
SUBSTR(S.USERNAME, 1, 8) AS "User",
TO_CHAR(S.SID) || ',' || TO_CHAR(S.SERIAL#) AS SID,
SUBSTR(STATUS, 1, 1) AS "Status",
S.PROGRAM as "Program",
SUBSTR(S.EVENT, 1, 25) AS "Wait Event",
LAST_CALL_ET AS LCE,
TRIM((SELECT SUBSTR(SQL_TEXT, 1, 20)
FROM GV$SQL SQ
WHERE SQ.INST_ID = S.INST_ID
AND SQ.SQL_ID = S.SQL_ID
AND ROWNUM = 1)) AS "SQL Text"
FROM (SELECT ROWNUM, INST_ID, SID,
DECODE(REQUEST, 0, TO_CHAR(SID)) HOLD_SID,
DECODE(REQUEST, 0, '^', TO_CHAR(SID)) WAIT_SID,
DECODE(REQUEST, 0, 'holding', 'waiting') GB,
ID1, ID2, LMODE, REQUEST, TYPE
FROM GV$LOCK
WHERE (ID1, ID2, TYPE) IN (SELECT ID1,
ID2,
TYPE
FROM GV$LOCK
WHERE (REQUEST != 0))
) V,
GV$SESSION S,
GV$SESSION_WAIT SW,
GV$PROCESS P
WHERE V.SID = S.SID
AND V.INST_ID = S.INST_ID
AND S.SID = SW.SID
AND S.INST_ID = SW.INST_ID
AND S.PADDR = P.ADDR
AND S.INST_ID = P.INST_ID
ORDER BY V.ID1,
V.REQUEST,
SW.SECONDS_IN_WAIT DESC
6. AWR 저장소 확인
SELECT SNAP_ID AS "Snap ID", INSTANCE_NUMBER AS "Instance",
TO_CHAR(STARTUP_TIME, 'YYYYMMDD HH24:MI') AS "DB Start time",
TO_CHAR(BEGIN_INTERVAL_TIME, 'YYYYMMDD HH24:MI') AS "Begin Time",
TO_CHAR(END_INTERVAL_TIME, 'YYYYMMDD HH24:MI') AS "End Time",
SNAP_LEVEL AS "Level"
FROM DBA_HIST_SNAPSHOT
ORDER BY INSTANCE_NUMBER DESC, SNAP_ID DESC;
7. Hard parsing 시간 측정
AVG_HARD_PARSE_TIME in V$SQLSTATS
'모니터링' 카테고리의 다른 글
| 오픈소스 APM]스카우터(Scouter) (0) | 2022.11.18 |
|---|---|
| 오라클 테이블 정보 조회 SQL (0) | 2022.11.18 |
| Solaris Dtrace를 사용한 malloc/free 모니터링(누수탐지) (0) | 2022.11.18 |