오라클 성능 고도화 원리와 해법 I (2016년-2)
1. 바인드 변수 테스트
-- 1. 테이블 생성
DROP TABLE USER.T;
CREATE TABLE T AS
SELECT *
FROM ALL_OBJECTS
;
-- 2. 테이블 UPDATE
UPDATE T
SET OBJECT_ID = ROWNUM
;
COMMIT;
-- 3. 인덱스 생성
CREATE UNIQUE INDEX T_U1 ON T(OBJECT_ID);
-- 4. 통계정보 생성
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER,
'T',
CASCADE => TRUE);
END;
/
-- 5. 통계정보 확인
@STAT
COLUMN_NAME DATA_TYPE DATA_LEN Null DISTINCT_KEY DENSITY NUM_NULLS NUM_BUCKETS SAMPLE_SIZE LAST Analyzed
------------------------------ ---------- -------- ---- -------------- ---------- -------------- ----------- -------------- -------------------
CREATED DATE 7 N 28,849 .000034663 0 1 52,908 2009-12-01 21:39:13
DATA_OBJECT_ID NUMBER 22 Y 231,041 4.3282E-06 640,896 1 231,079 2009-12-01 21:39:13
GENERATED VARCHAR2 1 Y 2 .5 0 1 5,340 2009-12-01 21:39:13
LAST_DDL_TIME DATE 7 N 29,401 .000034012 0 1 52,908 2009-12-01 21:39:13
OBJECT_ID NUMBER 22 N 871,975 1.1468E-06 0 1 5,340 2009-12-01 21:39:13
OBJECT_NAME VARCHAR2 30 N 275,980 3.6235E-06 0 1 871,975 2009-12-01 21:39:13
OBJECT_TYPE VARCHAR2 19 Y 22 .045454545 0 1 5,340 2009-12-01 21:39:13
OWNER VARCHAR2 30 N 197 .005076142 0 1 5,340 2009-12-01 21:39:13
SECONDARY VARCHAR2 1 Y 2 .5 0 1 5,340 2009-12-01 21:39:13
STATUS VARCHAR2 7 Y 1 1 0 1 5,340 2009-12-01 21:39:13
SUBOBJECT_NAME VARCHAR2 30 Y 3,958 .000252653 776,936 1 5,874 2009-12-01 21:39:13
TEMPORARY VARCHAR2 1 Y 2 .5 0 1 5,340 2009-12-01 21:39:13
TIMESTAMP VARCHAR2 19 Y 30,744 .000032527 0 1 52,908 2009-12-01 21:39:13
NUMBER_ROWS CLUSTERING BLEVEL AvgLfBlperKEY INITRAN Init/Next/ FLst/
OWNER.INDEX_NAME TABLESPACE DISTINCT_KEY FACTOR LEAF_BLOKCS AvgDtBlperKEY DEG LOGGIN MAXTRAN MIN/MAX FGrp LAS
---------------------------------------- ---------------------- --------------- -------------- ------------ ------------- ---- ------ ------- ------------- ----- ---
USER.T_U1 USER_DATA 871,975 12,325 [2] 1 1 YES 2/255 128k/128k / 200
871,975 1,820 1 1/unlimit
OWNER.INDEX_NAME Uniq Type Status Drop COLUMN LIST
---------------------------------------- ---- ------ ------ ---- ----------------------------------------------------------------------------------------------------
USER.T_U1 UNIQ NORMAL VALID NO OBJECT_ID
-- 6. EXPLAIN PLAN 확인
EXPLAIN PLAN FOR
SELECT OBJECT_NAME
FROM T
WHERE OBJECT_ID = 1000
;
@XPLAN
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 28 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | T_U1 | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1000)
-- 7. 바인드 변수 테스트
@mysess
MY_SESSION_INFO
--------------------------------------------------------------------------------
Sid, Serial# : 9924, 127
OS Process : 4064:4452 (CPID), 353028 (SPID)
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
DECLARE
TYPE rc IS REF CURSOR;
l_rc rc;
l_object_name T.OBJECT_NAME%TYPE;
BEGIN
FOR i IN 1..20000 LOOP
OPEN l_rc FOR
'SELECT /* TEST1 */
OBJECT_NAME
FROM T
WHERE OBJECT_ID = :X' USING i;
FETCH l_rc
INTO l_object_name;
CLOSE l_rc;
END LOOP;
END;
/
ALTER SESSION SET SQL_TRACE=FALSE;
-- 8. V$SQL 조회
SELECT SQL_TEXT,
LOADS,
PARSE_CALLS,
EXECUTIONS,
FETCHES
FROM V$SQL
WHERE SQL_TEXT LIKE '%TEST1'
AND SQL_TEXT NOT LIKE '%V$SQL%'
AND SQL_TEXT NOT LIKE 'DECLARE%'
;
SQL_TEXT LOADS PARSE_CALLS EXECUTIONS FETCHES
------------------------------------------------------------ ---------- ----------- ---------- ----------
SELECT /* TEST1 */ OBJECT_NAME FROM T WHERE OBJECT_ID = :X 1 20000 20000 20000
-- 9. Trace 조회
-- 이상한건 20000번을 Parse해야 하는데 15860 밖에 안했음
SELECT /* TEST1 */
OBJECT_NAME
FROM T
WHERE OBJECT_ID = :X;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 15860 0.25 0.20 0 0 0 0
Execute 15860 0.42 0.36 0 0 0 0
Fetch 15860 0.28 0.33 0 63440 0 15860
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 47580 0.95 0.90 0 63440 0 15860
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 44 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T (cr=4 pr=0 pw=0 time=138 us)
1 INDEX UNIQUE SCAN T_U1 (cr=3 pr=0 pw=0 time=85 us)(object id 14049579)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
2. Literal 값 테스트
@mysess
MY_SESSION_INFO
------------------------------------------------
Sid, Serial# : 9964, 48
OS Process : 5592:5580 (CPID), 385950 (SPID)
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
DECLARE
TYPE rc IS REF CURSOR;
l_rc rc;
l_object_name T.OBJECT_NAME%TYPE;
BEGIN
FOR i IN 1..20000 LOOP
OPEN l_rc FOR
'SELECT /* TEST1 */
OBJECT_NAME
FROM T
WHERE OBJECT_ID = ' || i;
FETCH l_rc
INTO l_object_name;
CLOSE l_rc;
END LOOP;
END;
/
ALTER SESSION SET SQL_TRACE=FALSE;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 15722 23.76 23.56 0 0 0 0
Execute 15722 1.04 0.82 0 0 0 0
Fetch 15735 0.71 0.77 0 62887 0 15734
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 47179 25.51 25.16 0 62887 0 15734
- Bind 사용시 00:00:01.23
- Literal 사용 시 00:00:09.03 (7.3배)
"구루비 DB 스터디 모임" 에서 2016년에 "오라클 성능 고도화 원리와 해법 I " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3171
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.