3. 커서공유

-- ############################################################################
-- 커서 공유
--
-- 1. 바인드 변수의 중요성
-- 2. 바인드 변수 부작용과 해법
-- 3. 애플리케이션 커서 캐싱
-- ############################################################################
 
-------------------------------------------------------------------------------
-- 1. 바인드 변수의 중요성
--    (1) 바인드 변수 사용 유무에 따른 성능 차이 테스트
-------------------------------------------------------------------------------
 
-- 사전 작업
drop table t1;
 
create table t1
as
select * from all_objects;
 
update t1 set object_id = rownum;
 
create unique index t1_idx on t(object_id);
 
begin 
    dbms_stats.gather_table_stats (ownname => 'ORAKIM', tabname => 'T1'); 
end;
/
 
alter system flush shared_pool;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- 바인드 변수 미사용 시
-- ▲▲▲▲▲▲▲▲▲▲▲
alter session set sql_trace = true;
 
declare
  type rc is ref cursor;
  l_rc rc;
  l_object_name t1.object_name%type;
begin
  for i in 1..20000
  loop
    open l_rc for
      'select /* test1 */ object_name from t1 where object_id = ' || i;
    fetch l_rc into l_object_name;
    close l_rc;
  end loop;
end;
/
 
select value from v$diag_info where name = 'Default Trace File';
 
alter session set sql_trace = false;
 
$ tkprof orakim_ora_15292.trc report_1.txt sys=no
 
--call     count       cpu    elapsed       disk      query    current        rows
--------- ------  -------- ---------- ---------- ---------- ----------  ----------
--Parse    20012     12.10      12.58          0          0          0           0
--Execute  20050      0.31       0.31          0          0          0           0
--Fetch    20085      7.98       8.65          0    3750484      75750       20053
--------- ------  -------- ---------- ---------- ---------- ----------  ----------
--total    60147     20.40      21.56          0    3750484      75750       20053
--
--Misses in library cache during parse: 20003   <-- Hard Parsing 20000회 발생. 커서 공유 X
 
select sql_id
     , parse_calls    -- 라이브러리 캐시에 SQL 커서를 찾으려는 요청 횟수
     , loads          -- 하드파싱을 거친 SQL 실행계획을 라이브러리 캐시에 적재한 횟수
     , executions     -- SQL을 수행한 횟수
     , invalidations  -- 커서가 무효화된 횟수. 커서가 참조하고 있는 오브젝트에 중요한 변화가(컬럼변경,통계갱신등) 일어났음을 의미
     , decode(sign(invalidations), 1, (loads-invalidations), 0) reloads
  from v$sql
 where sql_text like '%test1%'
   and sql_text not like '%v$sql%' 
;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- 바인드 변수 사용 시
-- ▲▲▲▲▲▲▲▲▲▲▲
alter system flush shared_pool;
 
alter session set sql_trace = true;
 
set timing on
 
declare
  type rc is ref cursor;
  l_rc rc;
  l_object_name t1.object_name%type;
begin
  for i in 1..20000
  loop
    open l_rc for
      'select /* test2 */ object_name from t1 where object_id = :x' using i;
    fetch l_rc into l_object_name;
    close l_rc;
  end loop;
end;
/
 
select value from v$diag_info where name = 'Default Trace File';
 
alter session set sql_trace = false;
 
$ tkprof orakim_ora_3000.trc report_2.txt sys=no
 
--call     count       cpu    elapsed       disk      query    current        rows
--------- ------  -------- ---------- ---------- ---------- ----------  ----------
--Parse    20000      0.12       0.14          0          0          0           0
--Execute  20000      0.20       0.20          0          0          0           0
--Fetch    20000      6.51       6.44          0    3750313      75750       20000
--------- ------  -------- ---------- ---------- ---------- ----------  ----------
--total    60000      6.84       6.79          0    3750313      75750       20000
--
--Misses in library cache during parse: 1 <-- Hard Parsing 1회 발생 나머지는 커서 공유 됨
 
select sql_id
     , parse_calls    -- 라이브러리 캐시에 SQL 커서를 찾으려는 요청 횟수
     , loads          -- 하드파싱을 거친 SQL 실행계획을 라이브러리 캐시에 적재한 횟수
     , executions     -- SQL을 수행한 횟수
     , invalidations  -- 커서가 무효화된 횟수. 커서가 참조하고 있는 오브젝트에 중요한 변화가(컬럼변경,통계갱신등) 일어났음을 의미
     , decode(sign(invalidations), 1, (loads-invalidations), 0) reloads
  from v$sql
 where sql_text like '%test2%'
   and sql_text not like '%v$sql%' 
;
 
 
-------------------------------------------------------------------------------
-- 2. 바인드 변수 부작용과 해법
-------------------------------------------------------------------------------
-- ▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼
-- 부작용
-- ▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼
-- 바인드 변수를 사용시 최초 수행할 때 최적화를 거친 실행계획을 캐시에 적재, 실행시점에는 그것을 가져와 값을 다르게 바인딩 하면서 반복 재사용 함.
-- 변수를 바인딩하는 시점이 (최적화 시점보다 나중인) 실행시점 이라는 사실.
-- 즉, SQL을 최적화하는 시점에 조건절 컬럼의 데이터 분포도를 활용하지 못하는 문제점. (컬럼 히스토그램 정보를 사용하지 못한다)
 
-- 따라서 바인드 변수를 사용하 때 옵티마이저는 평균 분포를 가정한 실행계획을 생성.
-- 컬럼 분포가 균일할 때는 문제될 것이 없지만 그렇지 않을 때는 실행 시점에 바인딩되는 값에 따라 최적이 아닌 실행계획일 수 있어 문제.
 
-- 이처럼 바인드 변수를 사용할 때는 정확한 컬럼 히스토그램에 근거하지 않고 카디널리티를 구하는 정해진 계산식에 기초해 비용을 계산하므로 최적이 아닌 실행계획을 수립할 가능성이 높다.
 
drop table t1;
 
create table t1
as
select rownum no from dual connect by level <= 1000;
 
analyze table t1 compute statistics for table for all columns;
 
-- 등치(=) 조건이 아닌 부등호나 Between 같은 범위 기반 검색조건일 때는 고정된 규칙을 사용하므로 더 부정확한 예측에 기반한 실행계획을 만든다.
--
-- (1) 부등호   : 선택도를 5%   로 계산
-- (2) 범위조건 : 선택도를 0.25%로 계산
--
-- 카디널리티(Cardinality) = 선택도(Selectivily) * 전체 레코드 건수
--
-- 테이블에 1,000개 로우가 있을 때 옵티마이저는 부등호 조건에 대해 50개 로우가 출력될 것으로 예상, 범위 조건절에 대해 3개의 로우가 출력될 것으로 예상 함. 
 
explain plan for select * from t1 where no <= :no;
select * from table(dbms_xplan.display(null, null, 'basic rows'));
-- 바인드변수 값에 100 이 들어올 경우 실행계획은 50건, 실제는 99건
 
explain plan for select * from t1 where no between :no1 and :no2;
select * from table(dbms_xplan.display(null, null, 'basic rows'));
-- 바인드변수 값에 100,200 이 들어올 경우 실행계획은 3건, 실제는 99건
 
-- ▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼
-- 해법
-- ▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼
-- 입력 값에 따라 SQL 분리
-- 예외적으로, Literal 상수 값 사용
 
 
-------------------------------------------------------------------------------
-- 3. 어플리케이션 커서 캐싱 방법
-------------------------------------------------------------------------------
-- ▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼
-- (1) 바인드 변수 사용 + 커서를 캐싱 미사용
-- ▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲
public static void noCaching(Connection conn, int count)throws Exception{
    PreparedStatement stmt;
    ResultSet rs;
 
    for(int i = 1; i <= count; i++){
        stmt = conn.prepareStatement("select /* test1 */ ?, ?, ?, a.* from t1 a where a.object_id = 1000");
        stmt.setInt(1, i);
        stmt.setInt(2, i);
        stmt.setString(3, "test1");
        rs = stmt.executeQuery();
 
        rs.close();
        stmt.close();
    }
}
 
// Parse Call이 Execute Call 횟수만큼 발생, 하드파싱 한번 발생
--call     count       cpu    elapsed       disk      query    current        rows
--------- ------  -------- ---------- ---------- ---------- ----------  ----------
--Parse    20000      0.12       0.14          0          0          0           0
--Execute  20000      0.20       0.20          0          0          0           0
--Fetch    20000      6.51       6.44          0    3750313      75750       20000
--------- ------  -------- ---------- ---------- ---------- ----------  ----------
--total    60000      6.84       6.79          0    3750313      75750       20000
--
--Misses in library cache during parse: 1
 
-- ▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼
-- (2) 바인드 변수 사용 + 애플리케이션 커서 캐싱 사용
-- ▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲
-- [PreparedStatement]를 루프문 바깥에 선언하고 루프 내에서 반복 사용하다가 루프를 빠져 나왔을 때 닫는다.
public static void cursorHolding(Connection conn, int count)throws Exception{
    
    PreparedStatement stmt = conn.prepareStatement("select /* test2 */ ?, ?, ?, a.* from t1 a where a.object_id = 1000");
    ResultSet rs;
 
    for(int i = 1; i <= count; i++){
        stmt.setInt(1, i);
        stmt.setInt(2, i);
        stmt.setString(3, "test2");
        rs = stmt.executeQuery();
 
        rs.close();
    }
    
    stmt.close();
}
 
// Parse Call이 한번만 실행
--call     count       cpu    elapsed       disk      query    current        rows
--------- ------  -------- ---------- ---------- ---------- ----------  ----------
--Parse        1      0.12       0.14          0          0          0           0
--Execute  20000      0.20       0.20          0          0          0           0
--Fetch    20000      6.51       6.44          0    3750313      75750       20000
--------- ------  -------- ---------- ---------- ---------- ----------  ----------
--total    60000      6.84       6.79          0    3750313      75750       20000
--
--Misses in library cache during parse: 1