메뉴 건너뛰기

bysql.net

5._바인드_변수의_중요성

2012.04.22 15:11

시와처 조회 수:4505

1. sample Data 생성

12:51:49 SQL> create table t as select * from dba_objects;

테이블이 생성되었습니다.

12:52:09 SQL> update t set object_id = rownum;

84495 행이 갱신되었습니다.

12:52:13 SQL> create unique index t_idx on t(object_id);

인덱스가 생성되었습니다.

12:52:13 SQL> analyze table t compute statistics;

테이블이 분석되었습니다.

12:52:15 SQL> set autotrace traceonly explain

12:52:15 SQL> select object_name from t where object_id = 1000;


Execution Plan

----------------------------------------------------------

Plan hash value: 4034308152


-------------------------------------------------------------------------------------                    

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |       |     1 |    20 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    20 |     2   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | T_IDX |     1 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID"=1000) 


  •  T_IDX index를 access path로 사용하여 데이터를 읽어 오는것을 확인할 수 있다.


2. bind 변수 사용 처리의 예

12:55:21 SQL> SET TIMING ON

12:55:22 SQL> DECLARE

12:55:22   2    TYPE rc IS REF CURSOR;

12:55:22   3    l_rc rc;

12:55:22   4    l_object_name t.object_name%TYPE;

12:55:22   5  BEGIN

12:55:22   6    FOR i IN 1 .. 20000

12:55:22   7    LOOP

12:55:22   8     OPEN l_rc FOR

12:55:22   9        'SELECT /* test_bind */ object_name             

12:55:22  10           FROM t

12:55:22  11         WHERE  object_id = :x' USING i;

12:55:22  12     FETCH l_rc INTO l_object_name;

12:55:22  13     CLOSE l_rc;

12:55:22  14    END LOOP;

12:55:22  15  END;

12:55:22  16  /


PL/SQL 처리가 정상적으로 완료되었습니다.


경   과: 00:00:01.37


  •  2만번 수행에 소요된 시간이 1.37초 인것을 확인할 수 있다
  •  v$sql의 내용을 확인해 보면 parse_call 이 2만번 발생하였으나 하드 파싱은 1회만 발생한 것을 확인할 수 있다.

12:55:23 SQL> SELECT sql_text, loads, parse_calls, executions, fetches

12:55:43   2    FROM v$sql

12:55:43   3   WHERE sql_text LIKE '%test_b%'

12:55:43   4     AND sql_text NOT LIKE '%v$sql%'

12:55:43   5     AND sql_text NOT LIKE '%DECLARE%';


SQL_TEXT                                                          LOADS PARSE_CALLS EXECUTIONS    FETCHES

----------------------------------------------------------------  ------ ----------- ---------- ----------             

SELECT /* test_bind */ object_name  FROM t WHERE  object_id = :x       1       20000      20000      20000


 


  •  bind 변수 처리한  10046 event trace 는 다음과 같다.
단 한번의 library cache Misses 를 볼수 있다.

 SELECT /* test_bind */ object_name

         FROM t

       WHERE  object_id = :x


call     count       cpu    elapsed       disk      query    current        rows               

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse    20000      0.05       0.12          0          0          0           0

Execute  20000      0.92       1.25          0          0          0           0

Fetch    20000      0.19       0.24          9      60000          0       20000

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total    60000      1.16       1.62          9      60000          0       20000


Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: 79     (recursive depth: 1)




3. Liter 처리

14:15:51 SQL> SET TIMING ON

14:15:51 SQL> DECLARE

14:15:51   2    TYPE rc IS REF CURSOR;

14:15:51   3    l_rc rc;

14:15:51   4    l_object_name t.object_name%TYPE;

14:15:52   5  BEGIN

14:15:52   6    FOR i IN 1 .. 20000

14:15:52   7    LOOP

14:15:52   8     OPEN l_rc FOR

14:15:52   9        'SELECT /* test_liter */ object_name                         

14:15:52  10           FROM t

14:15:52  11         WHERE  object_id = ' || i;

14:15:52  12     FETCH l_rc INTO l_object_name;

14:15:52  13     CLOSE l_rc;

14:15:52  14    END LOOP;

14:15:52  15  END;

14:15:52  16  /


PL/SQL 처리가 정상적으로 완료되었습니다.


경   과: 00:00:15.90 

  •  15.90  초가 발생된것을 확인 할 수 있다.
  •  v$sql  을 확인한 결과

SELECT sql_text, loads, parse_calls, executions, fetches

  FROM v$sql                                       

 WHERE sql_text LIKE '%test_l%'                     

   AND sql_text NOT LIKE '%v$sql%'                 

   AND sql_text NOT LIKE '%DECLARE%';              



SQL_TEXT                                                                              LOADS PARSE_CALLS EXECUTIONS    FETCHES

-------------------------------------------------------------------------------- ---------- ----------- ---------- ----------            

SELECT /* test_liter */ object_name          FROM t        WHERE  object_id = 19          1           1          1          1

SELECT /* test_liter */ object_name          FROM t        WHERE  object_id = 19          1           1          1          1

SELECT /* test_liter */ object_name          FROM t        WHERE  object_id = 19          1           1          1          1

....

....

....

SELECT /* test_liter */ object_name          FROM t        WHERE  object_id = 19          1           1          1          1

SELECT /* test_liter */ object_name          FROM t        WHERE  object_id = 19          1           1          1          1

SELECT /* test_liter */ object_name          FROM t        WHERE  object_id = 19          1           1          1          1

SELECT /* test_liter */ object_name          FROM t        WHERE  object_id = 19          1           1          1          1

SELECT /* test_liter */ object_name          FROM t        WHERE  object_id = 19          1           1          1          1

SELECT /* test_liter */ object_name          FROM t        WHERE  object_id = 19          1           1          1          1

SELECT /* test_liter */ object_name          FROM t        WHERE  object_id = 20          1           1          1          1

SELECT /* test_liter */ object_name          FROM t        WHERE  object_id = 19          1           1          1          1

SELECT /* test_liter */ object_name          FROM t        WHERE  object_id = 19          1           1          1          1



53 rows selected


 

 for loop 를 통해 수행된 각각의 sql 대한 sql이 따로 생성 된것을 볼 수 있으며  적재된 2만개중 캐시에서 밀려난 정보도 많은 것을 확인 할 수 있다.



위의 예에서 본것과 같이

바인드 변수 사용에 따른 효과는 분명하다

바인드 변수를 사용은 커서를 많이 생성하지 않고 하나를 반복 재사용하므로 메모리 사용량과 파싱 소요 시간을 줄여준다

궁극적으로 시스템 전반의 메모리와 CPU 사용률을 낮춰 데이터베이스 성능향상에 기여 한다.


cursor_sharing 파라미터를 통해 일시적인 해결 가능 

영구적 목적으로 사용해서는 안된다.


이 게시물을
목록
목록
쓰기
태그