by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

바인드 변수의 중요성




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

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.