잡학문구

힌트란 SQL 문장에 대한 지시 구문입니다.

Optimizer 힌트는 실행 계획을 변경할 수 있고 최적의 실행 계획을 사용하도록 하는 데 사용됩니다

옵티마이저가 알지 못하는 데이터에 대한 정보를 알고 있는 경우 특정 쿼리 실행 계획을 선택하도록 지시할 수 있습니다.

단, Optimizer에 대해 개념을 정확히 알고 사용해야 하며, 데이터의 변동성 등을 충분히 예측하여 사용해야 합니다.

힌트의 사용은 단기적으로 성능향상이 있을 수 있으나, 장기적으로 성능 저하를 초래할 수 있기 때문에 최대한 사용을 자제하며 정확히 알고 있는 경우에만 사용하도록 합니다..

뷰와 함께 힌트 사용하기

Oracle은 뷰(또는 하위 쿼리) 내부나에서 힌트 사용을 권장하지 않습니다. 또한 이러한 힌트로 인해 예기치 않은 실행 계획이 발생할 수 있습니다. 특히 뷰 내부 또는 뷰에 대한 힌트는 뷰가 최상위 쿼리에 병합 가능한지 여부에 따라 다르게 처리됩니다.

힌트 사용 방법

명령문 블록에는 힌트가 포함된 주석이 하나만 있을 수 있으며,

주석은 SELECT, UPDATE, INSERT, MERGE 또는 DELETE 키워드 뒤에 와야 합니다.

힌트 종류

1. Types of Hints

• Single-table : 단일 테이블 힌트는 하나의 테이블 또는 뷰에 지정됩니다. INDEX 및 USE_NL은 단일 테이블 힌트의 예입니다.
• Multi-table :다중 테이블 힌트는 힌트가 하나 이상의 테이블이나 뷰를 지정할 수 있다는 점을 제외하면 단일 테이블 힌트와 비슷합니다. LEADING은 다중 테이블 힌트의 예입니다. USE_NL(table1 table2)은 USE_NL(table1) 및 USE_NL(table2)의 형태와 동일하므로 다중 테이블 힌트로 간주되지 않습니다.
• Query block : 쿼리 블록 힌트는 단일 쿼리 블록에서 작동합니다. STAR_TRANSFORMATION 및 UNNEST는 쿼리 블록 힌트의 예입니다.
• Statement : 구문 힌트는 전체 SQL 문에 적용됩니다. ALL_ROWS는 구문 힌트의 예입니다.

SQL Plan Baselines. ???

2. Hints by Category

Optimizer 힌트는 다음 범주로 그룹화됩니다.

• Hints for Optimization Approaches and Goals
• Hints for Enabling Optimizer Features
• Hints for Access Paths
• Hints for Join Orders
• Hints for Join Operations
• Hints for Online Application Upgrade
• Hints for Parallel Execution
• Hints for Query Transformations
• Additional Hints

2.1 Hints for Optimization Approaches and Goals

ALL_ROWS 및 FIRST_ROWS(n) 힌트를 사용하면 최적화 접근 방식과 목표 중에서 선택할 수 있습니다. SQL 문에 최적화 접근 방식과 목표를 지정하는 힌트가 있는 경우 옵티마이저는 통계 유무, OPTIMIZER_MODE 초기화 매개변수 값, ALTER SESSION 문의 OPTIMIZER_MODE 매개변수에 관계없이 지정된 접근 방식을 사용합니다.

• ALL_ROWS
• FIRST_ROWS(n)

2.2 Hints for Enabling Optimizer Features

OPTIMIZER_FEATURES_ENABLE 힌트는 Oracle Database 릴리스 번호를 기반으로 최적화 기능을 활성화하기 위한 역할을 합니다. 이 힌트는 데이터베이스 업그레이드 후 계획 회귀를 확인할 때 유용한 방법입니다.
힌트에 대한 인수로 릴리스 번호를 지정합니다. 다음 예는 Oracle Database 11g Release 1(11.1.0.6)의 옵티마이저 기능으로 쿼리를 실행합니다.

SELECT /*+ optimizer_features_enable('11.1.0.6') */ employee_id, last_name
FROM    employees
ORDER BY employee_id;

2.3 Hints for Access Paths

다음 힌트는 테이블에 대한 특정 액세스 경로를 사용하도록 지시합니다.

• FULL
• CLUSTER
• HASH
• INDEX and NO_INDEX
• INDEX_ASC and INDEX_DESC
• INDEX_COMBINE and INDEX_JOIN
• INDEX_JOIN
• INDEX_FFS and NO_INDEX_FFS
• INDEX_SS and NO_INDEX_SS
• INDEX_SS_ASC and INDEX_SS_DESC

2.3.1 FULL Hint

FULL 힌트는 옵티마이저가 지정된 테이블에 대해 전체 테이블 스캔을 수행하도록 지시합니다.

SELECT /*+ FULL(e) */ employee_id, last_name
  FROM hr.employees e 
  WHERE last_name LIKE :b1;

Oracle Database는 WHERE 절의 조건에 의해 사용 가능한 last_name 열에 인덱스가 있는 경우에도 직원 테이블에 대해 전체 테이블 스캔을 수행하여 이 문을 실행합니다.
직원 테이블의 FROM 절에 별칭 e가 있으므로 힌트는 이름이 아닌 별칭으로 테이블을 참조해야 합니다.

 

2.3.2 CLUSTER Hint

CLUSTER 힌트는 지정된 테이블에 액세스하기 위해 클러스터 스캔을 명시적으로 선택합니다. 클러스터링된 개체에만 적용됩니다.

SELECT /*+ CLUSTER(e) */ employee_id, last_name
  FROM hr.employees e 
  WHERE last_name LIKE :b1;

 

2.3.3 HASH Hint

HASH 힌트는 명시적으로 해시 스캔을 선택하여 지정된 테이블에 액세스합니다. 클러스터에 저장된 테이블에만 적용됩니다.

SELECT /*+ HASH(e) */ employee_id, last_name
  FROM hr.employees e 
  WHERE last_name LIKE :b1;

 

 

2.3.4 INDEX and NO_INDEX Hint

INDEX hint

INDEX 힌트는 지정된 테이블에 대한 인덱스 스캔을 선택합니다. 도메인, B-트리, 비트맵 및 비트맵 조인 인덱스에 대해 INDEX 힌트를 사용할 수 있습니다. 그러나 Oracle은 여러 인덱스 조합에 INDEX보다 INDEX_COMBINE을 사용할 것을 권장합니다. 

SELECT /*+ INDEX (employees emp_department_ix)*/ 
       employee_id, department_id 
  FROM employees 
  WHERE department_id > 50;

NO_INDEX hint

NO_INDEX 힌트는 지정된 테이블에 대한 인덱스 집합을 허용하지 않습니다.  

SELECT /*+ NO_INDEX(employees emp_empid) */ employee_id 
  FROM employees 
  WHERE employee_id > 200; 

 

 

 

 

2.4 Hints for Join Orders

• LEADING
• ORDERED

2.6 Hints for Online Application Upgrade

온라인 애플리케이션 업그레이드 힌트는 에디션 기반 재정의를 사용하여 온라인 애플리케이션 업그레이드를 수행할 때 충돌하는 INSERT 및 UPDATE 작업을 처리하는 방법을 제안합니다.

• CHANGE_DUPKEY_ERROR_INDEX
• IGNORE_ROW_ON_DUPKEY_INDEX
• RETRY_ON_ROW_CHANGE

2.7 Hints for Parallel Execution

병렬 실행 힌트는 작업을 병렬화할지 여부와 방법에 대해 지시합니다. 다음 병렬 힌트를 사용할 수 있습니다.

• PARALLEL and NO_PARALLEL
• PARALLEL_INDEX and NO_PARALLEL_INDEX
• PQ_DISTRIBUTE

DBMS_XPLAN 을 사용하기 위해 필요한 권한입니다.

• V$SQL
• V$SQL_PLAN
• V$SQL_PLAN_STATISTICS_ALL
• V$SESSION

DBMS_XPLAN 실행권한이 없는 DB 사용자에 아래와 같이 실행권한을 부여합니다.

GRANT SELECT ON sys.v_$sql TO 유저명;
GRANT SELECT ON sys.v_$sql_plan TO 유저명;
GRANT SELECT ON sys.v_$sql_plan_statistics_all TO 유저명;
GRANT SELECT ON sys.v_$session TO 유저명;

실행계획

Oracle 실행 계획은 요청된 데이터에 액세스하는 방법을 보여주는 세부 단계의 집합입니다. 실행 계획의 각 단계는 행을 생성하거나 소비하는 데이터베이스 연산자 집합입니다.

실행계획 관련 파라미터는 timed_statistics 와 statistics_level 이 있고 힌트는 /*+ gather_plan_statistics */ 이 있습니다.

< TIMED_STATISTICS >

TIMED_STATISTICS는 시간과 관련된 통계 수집 여부를 지정합니다. 값은 true 또는 false 입니다.

• true : 통계는 trace 파일에 수집되거나 V$SESSTATS및 V$SYSSTATS 동적 뷰에 표시됩니다.
• false : 모든 시간 관련 통계 값은 0으로 설정됩니다. 이 설정을 사용하면 Oracle이 운영 체제에서 시간을 요청하는 오버헤드를 피할 수 있습니다.

11.1.0.7.0부터 STATISTICS_LEVEL의 값이 TYPICAL 또는 ALL로 설정된 경우 TIMED_STATISTICS 값을 false 로 설정할 수 없습니다. TIMED_STATISTICS를 true 로 설정하면 더 정확한 통계를 생성할 수 있습니다.

< STATISTICS_LEVEL >

STATISTICS_LEVEL은 데이터베이스 및 운영체제 통계정보에 대한 수집 수준을 지정합니다. 값은 basic, typical, all 중 하나입니다. 기본값은 typical 입니다.

• TYPICAL : 데이터베이스 자체 관리 기능에 필요한 모든 주요 통계 수집
• ALL : TYPICAL 설정으로 수집된 통계정보에 부가적인 통계 추가. 부가적인 통계는 시간이 지정된 운영 체제 통계 및 계획 실행 통계입니다.
• BASIC : Oracle Database 기능에 필요한 많은 중요한 통계 수집이 비활성화됨

/*+ gather_plan_statistics */

GATHER_PLAN_STATISTICS 힌트는 "Row source execution" 정보를 활성화합니다.

Row source execution 이란 Explain Plan에서 보는 Row수의 예측 값이 아닌 실제 실행 과정에서 추출된 Row 수를 보여줍니다.

오라클에서 실제 SQL문을 실행하는 동안의 실행 계획을 각 Row Source별로 수행통계를 수집하기 위해 gather_plan_statistics 힌트를 사용하거나 statistics_level 파리미터를 all로 설정합니다. 즉 statistics_level = all 과 /*+ gather_plan_statistics */ 힌트는 동일하게  추가적인 SQL 실행통계를 수집합니다.

/*+ gather_plan_statistics */ 힌트는 실행통계 데이터를 PLAN_TABLE에 저장하지 않고, V$SQL_PLAN 뷰에 저장합니다..

실행 계획을 보는 방법에는 EXPLAIN PLAN과 SET AUTORACE, SQL TRACE, DBMS_XPLAN 등이 있습니다.

1) EXPLAIN PLAN

EXPLAIN PLAN
SET STATEMENT_ID = 'HR_PLAN' INTO PLAN_TABLE --INTO PLAN_TABLE은 생략 가능
FOR
SELECT * 
FROM REGIONS A, COUNTRIES B 
WHERE  A.REGION_ID = B.REGION_ID;

 -- DBMS_XPLAN으로 PLAN 보기
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','HR_PLAN','ALL'));

 -- SQL로 PLAN 보기
SELECT
    lpad(' ', level - 1)||operation|| ' ('|| options||')' "Operation",
    object_name       "Name",
    optimizer         "Optimizer",
    cost              "Cost",
    cardinality       "Cardinality",
    bytes             "Bytes",
    partition_start   "Partition Start",
    partition_id      "Partition ID"
   FROM plan_table
CONNECT BY prior id = parent_id AND prior statement_id = statement_id
  START WITH id = 0 AND statement_id = '&1'
  ORDER BY id;

EXPLAIN PLAN의 사용법은 SELECT문 앞에 실행 계획을 저장하라는 EXPLAIN PLAN 명령어를 사용하고 실행할 SQL에 statement ID를 부여한 다음 FOR를 쓰면 됩니다. 이렇게 쿼리문의 실행계획을 PLAN_TABLE에 저장한 후 직접 조회하거나 DBMS_XPLAN 패키지를 사용하여 조회하면 됩니다. EXPLAIN PLAN은 SQL의 실행 계획만을 수립하고 SQL문은 수행하지 않습니다. DBMS_XPLAN 패키지를 사용하는 것이 편리하고 효율적이지만, 추가적인 정보(Partition 정보 등)를 보고자 할 때 SQL 문을 수행할 수 있습니다. 

다만 EXPLAIN PLAN은 한번에 하나의 쿼리문의 실행계획만을 확인할 수 있고 그 실행계획을 또 확인하기 위해 별도의 SELECT문을 작성해야하는 번거로움과 데이터를 읽지 않기 떄문에 소요시간을 추정한다거나 데이터 관련 I/O정보를 확인할 수 없다는 점이 단점입니다.

2) AUTOTRACE 

SET AUTOTRACE ON;

SELECT * 
FROM REGIONS A, COUNTRIES B 
WHERE  A.REGION_ID = B.REGION_ID;

SET AUTOTRACE 명령어를 사용하면 쿼리문을 실행시킬 때마다 실행계획을 바로 확인할 수 있습니다. AUTOTRACE의 옵션을 활용하여 통계정보, 실행계획, 실제 데이터 조회 여부 등을 조정할 수 있습니다.

AUTOTRACE가 안될 경우

AUTOTRACE도 마찬가지로 PLAN_TABLE 테이블이 생성되어 있어야 하고 plustrace 라는 롤이 부여되어 있어야 합니다. plustrace 롤은 기본적으로 생성되어 있지 않으므로 sysdba로 아래 sql을 수행하여 role을 생성해 줍니다.

SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql

PLUSTRACE ROLE을 사용자에게 부여합니다.

$ sqlplus "/as sysdba"

SQL> grant plustrace to <user>;

이제 set autotrace on 명령어를 사용할 수 있습니다.

AUTOTRACE 주요 옵션

3) SQL TRACE 

SQL TRACE는 실행 계획뿐만이 아니라 여러 세션에서 수행한 SQL의 통계 정보, 수행시간, 결과 등을 TRACE 파일로 기록하여 이를 파일 형태로 저장하는 방법을 말합니다. SQL Trace는 실행되는 SQL문의 실행통계를 세션별로 모아서 Trace 파일을 만듭니다. 이때 만들어지는 파일은 .trc의 확장자를 갖습니다. 이 .trc 파일들은 바이너리 형태로 저장되기에 읽기 편한 형태로 파일을 변환하는 과정이 필요한데 TKPROF 유틸리티를 이용하여 변환합니다.

또한 SQL Trace는 세션 레벨과 인스턴스 레벨에서 SQL 문장들을 분석 할 수 있는데 인스턴스 레벨로 SQL Trace를 수행시키면 DBMS에 성능에 영향이 있으므로 인스턴스스 레벨의 SQL Trace는 설정하지 않도록 합니다.

sql trace 설정 및 해제

SQL> alter session set sql_trace = true;
OR
SQL> execute dbms_system.set_sql_trace_in_session(true);

-- 특정 세션 sql trace 설정
SQL> execute dbms_system.set_sql_trace_in_session(session_id, serial_id, true);

-- session level sql trace 종료
SQL> alter session set sql_trace = false;
-- 특정 세션 sql trace 설정 해제
SQL> execute dbms_system.set_sql_trace_in_session(session_id, serial_id, false);

DBMS_SYSTEM 패키지 는 SYS 계정만 사용할 수 있습니다. 일반 계정이 dbms_system 패키지를 사용하려면 아래와  같이 synonym 생성 및 실행권한을 부여합니다. 일반계정에 dbms_system 실행권한을 부여할 경우에는 영향도 검토를 충분히 한 다음에 부여합니다.

SQL> CREATE PUBLIC SYNONYM dbms_system FOR dbms_system;
SQL> grant execute on dbms_system to <user>

-- To grant privileges to all users:
SQL> grant execute on dbms_system to public

trace 파일

sql_trace를 true로 설정한 session에서 수행한 sql 문에 대한 상세 정보는 diag 디렉토리에 SID_ora_spid.trc 라는 파일 이름으로 저장됩니다. 이 디렉토리에는 많은 trc 파일이 존재하므로, 특정 세션의 spid를 알아야 그 세션에서 생성한 파일임을 쉽게 알 수 있습니다.

trace 파일은 diagnostic_dest 로 정의된 디렉토리 하위에 diag/rdbms/orcl/ORCL/trace 경로에 존재합니다.

예시에서는 diagnostic_dest가 /data 이므로 /data/diag/rdbms/orcl/ORCL/trace 경로에 trace 파일이 생성됩니다. 경로는 각자의 SID에 따라 상이합니다.

$ sqlplus hr/<password>
SQL> select sys_context('USERENV', 'SID') from dual;

-- 위에서 확인한 SID 를 아래 &SID에 입력
select b.spid
  from v$session a, v$process b
 where a.sid = &sid
   and b.addr = a.paddr;

22978

## diagnostic 경로 확인(trace 파일이 저장되는 경로)
$ sqlplus "/as sysdba"
SQL> show parameter diag;
diagnostic_dest                      string      /data

SQL> exit;

$ cd /data/diag/rdbms/orcl/ORCL/trace

## 위에서 찾은 SPID로 trace 파일을 찾는다. trace 파일명은 SID_ora_spid.trc 형태임
## 예로 SID가 ORCL이고 spid가 22987인 경우 trace 파일은 ORCL_ora_22987.trc 

$ ls ORCL_ora_22987.trc

tkprof - trace 파일 분석

tkprof 명령어는 binary trace 파일을 text 파일 형태로 읽기 쉽게 변환해 줍니다.

$ tkprof trace_file.trc output_file.txt sys=no

## 위의 trace 파일을 tkprof 로 분석
$ tkprof ORCL_ora_22987.trc output_file.txt sys=no
## tkprof 로 분석한 내용을 확인(vi or vim)
$ vi output_file.txt

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          0           0
Execute     14      0.00       0.00          0          0          0           0
Fetch       14      0.00       0.00          0         28          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       32      0.00       0.00          0         28          0           2

Misses in library cache during parse: 3
Misses in library cache during execute: 3

sys=no 옵션은 시스템 내부적으로 수행되는 SQL 문을 분석 대상에서 제외합니다.

4) DBMS_XPLAN 패키지

DBMS_XPLAN.DISPLAY(
   table_name    IN  VARCHAR2  DEFAULT 'PLAN_TABLE',
   statement_id  IN  VARCHAR2  DEFAULT  NULL, 
   format        IN  VARCHAR2  DEFAULT  'TYPICAL',
   filter_preds  IN  VARCHAR2 DEFAULT NULL);

SELECT * FROM table (DBMS_XPLAN.DISPLAY);

SELECT * FROM table (DBMS_XPLAN.DISPLAY('my_plan_table'));

SELECT * FROM table (DBMS_XPLAN.DISPLAY('plan_table', null, 'basic'));

SELECT * FROM table (DBMS_XPLAN.DISPLAY('plan_table', 'foo'));

Syntax

DBMS_XPLAN.DISPLAY_CURSOR(
   sql_id            IN  VARCHAR2  DEFAULT  NULL,
   cursor_child_no   IN  NUMBER    DEFAULT  0, 
   format            IN  VARCHAR2  DEFAULT  'TYPICAL');

예제

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR);

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp'));

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp', NULL, 'ALLSTATS LAST');

DBMS_XPLAN에서 제공하는 모든 통계 정보를 표시하려면 아래와 같이 수행합니다.

 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp', '0', 'advanced allstats last'))

위와 같이 다양한 방법으로 SQL문의 실행 계획과 그에 따른 통계정보를 확인할 수 있습니다.

사전 예측정보는 실제 수행 통계와 상이할 수 있으므로 간단한 SQL 실행계획 확인용으로 사용하고,

SQL튜닝을 위한 상세 정보를 위해서는 DBMS_XPLAN.DISPLAY_CURSOR를 활용합니다.

parsing, fetch, query block, current block 등의 추가 정보가 필요한 경우에는 tkprof 를 활용합니다. 

SQL 튜닝 절차

1. 튜닝대상 SQL 식별 :

     AWR report, APM 툴, DB 모니터링 툴, 개발자 요청, 전수조사, 샘플조사 등

2. 튜닝대상 SQL의 sql_id 추출 :

     SQL_ID가 제공된 경우에는 제공된 sql_id를 사용하고 그렇지 않은 경우에는V$SQLAREA, V$SQL,           

     DBA_HIST_SQLTEXT, DBA_HIST_SQLSTAT 등의 뷰에서 추출합니다.

3. DBMS_XPLAN.DISPLAY_CURSOR 수행 :

    추출한 sql_id 를 가지고  DBMS_XPLAN.DISPLAY_CURSOR 패키지로 실행계획, 통계 정보 등을 조회합니다.

4. SQL 튜닝

    인덱스 생성, SQL 재작성, SQL분리, 힌트 사용, 데이터모델 변경, 반정규화, 요약테이블 생성  등으로 SQL 문장을 튜닝합니다.

오라클은 입력된 바인드 값이 32, 128, 2000, 4000 바이트를 넘을 때마다 새로운 Child 커서를 생성한다.

즉, bind 변수를 사용한 SQL 이라도 bind 변수값의 길이가 위의 구간에 따라 새로운 Child 커서가 생성된다.

아래의 테스트 케이스로 확인해 본다.

varchar2(4000) 컬럼을 가진 테스트 테이블을 생성한다.

create table tab1 ( col1 varchar2(4000));

아래 프로시저를 수행한 후 결과값을 보면 정확히 32, 128, 2000, 4000 바이트 구간에서 child 커서가 생성되는 것을 볼 수 있다.

set serveroutput on

declare
  l_cnt number;
  l_child_cnt number;
  l_prev_child_cnt number;
  l_bind_value varchar2(4000);
  l_sql_id varchar2(13);
begin

  l_prev_child_cnt := 0;

  for c in 1..4000
  loop
    l_bind_value := lpad('A', c, '0');  
    select count(*) into l_cnt from tab1 where col1 = l_bind_value;

    if c = 1 then
      select prev_sql_id into l_sql_id -- sql_id 를 찾음
      from   v$session
      where  sid = userenv('sid')
      and    username is not null
      and    prev_hash_value <> 0;
      dbms_output.put_line('sql_id --> ' || l_sql_id);
    end if;

    select count(*) into l_child_cnt from v$sql where sql_id = l_sql_id;

    if l_prev_child_cnt < l_child_cnt then -- 새 Child 커서 생길 때마다
      dbms_output.put_line(c);
      l_prev_child_cnt := l_child_cnt;
    end if;

  end loop;
end;
/

sql_id --> 4bp0vskw058t3
1
33
129
2001

PL/SQL procedure successfully completed.

child 커서가 공유되지 못한 이유를 보기위해서는 V$SQL_SHARED_CURSOR 뷰를 조회해 본다.

위의 테스트 결과는 hash_match_failed 컬럼과 bind_lentgh_upgradeable 컬럼값이 'Y' 인 것을 볼 수 있다.

즉 child 커서를 공유하지 못한 이유가 hash_match_fail, bind_lentgh 때문이라는 것을 알 수 있다. hash_match_fail 은 histogram이 생성되어 있지 않아서 발생하는 것 같다.

HASH_MATCH_FAILED

Hash mismatch. Set to "Y" if sharing fails due to a hash mismatch, such as the case with mismatched histogram data or a range predicate marked as unsafe by literal replacement (See Bug 3461251)


BIND_LENGTH_UPGRADEABLE Bind length upgradeable and could not be shared because a bind variable size was smaller than the new value being inserted
(marked as BIND_MISMATCH in earlier versions).

reson 컬럼에는 Bind mismatch로 표시된다.

select
    sh.sql_id,
    sh.child_number, sh.hash_match_failed, sh.bind_length_upgradeable,
    xt.r1
from
    v$sql_shared_cursor sh
    inner join xmltable (
        '/ChildNode'
        passing xmlparse(content sh.reason)
        columns
            r1 varchar2(40) path 'reason'
    ) xt on ( 1 = 1 )
where sh.sql_id = '4bp0vskw058t3';


SQL_ID	CHILD_NUMBER	HASH_MATCH_FAILED	BIND_LENGTH_UPGRADEABLE	R1
4bp0vskw058t3	0	N	N	Bind mismatch(22)
4bp0vskw058t3	1	Y	Y	Bind mismatch(22)
4bp0vskw058t3	2	Y	Y	Bind mismatch(22)
4bp0vskw058t3	3	Y	Y	Bind mismatch(22)

* 참고 : child 커서가 공유되지 않는 이유

• USE_FEEDBACK_STATSCardinality feedback. Cardinality feedback is being used and therefore a new plan could be formed for the current execution.

BIND_EQUIV_FAILUREThe bind value's selectivity does not match that used to optimize the existing child cursor. When adaptive cursor sharing is used and the cursor is bind aware, then if the selectivity is outside of the current ranges and a new plan is desirable then a new child is raised with this as the reason code for non-sharing of the previous plan. For an example, see Document 836256.1. After each execution in the example, run:

select sql_id, address, child_address, child_number, BIND_EQUIV_FAILURE 
from v$sql_shared_cursor where sql_id='19sxt3v07nzm4';

once the cursor is marked as bind aware and a second plan is seen then the following will be the resultant output:  

SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
19sxt3v07nzm4 000000007A1C0DE0 000000007A1BF980            0 N
19sxt3v07nzm4 000000007A1C0DE0 000000007A10DDB0            1 Y

As can be seen, the new version is created due to BIND_EQUIV_FAILURE

Parent and Child Cursors

(상위 커서/하위 커서, 부모 커서/자식 커서)

"커서"는 사용자가 실행하는 SQL 문에 할당되는 라이브러리 캐시의 메모리 영역입니다. 이 메모리 영역은 SQL 텍스트, SQL 실행 계획, 통계 등과 같은 SQL 문에 대한 주요 정보를 저장합니다.

각 SQL 문에는 하나의 상위 커서와 하나 이상의 하위 커서가 있습니다. 상위 및 하위 커서가 무엇인지 설명합니다.
커서 = 메모리 영역을 염두에 두십시오.

왜 커서가 두 종류인가?

두 종류의 커서(부모 및 자식)가 있는 것은 Oracle 데이터베이스 설계에 따른 것입니다.

실행하는 각 SQL 문에 대해 Oracle 엔진은 상위 및 하위 커서라는 두 개의 커서를 생성합니다. 동일한 SQL 문에 대해 다른 바인드 값이나 두 개의 다른 스키마 또는 다른 리터럴 값 등이 있을 수 있는 것과 같은 다른 차이점이 있을 수 있기 때문에 두 개의 커서가 생성됩니다.

상위 커서는 SQL 문을 보유하고 하위 커서는 정보를 보유합니다. 차이점과 관련이 있습니다. 이것은 본질적으로 SQL 문이 하드 또는 소프트 구문 분석을 수행할 것인지 여부를 결정하는 요소로 자식 커서를 만듭니다.

PARENT CURSOR

• 커서의 SQL 텍스트를 저장합니다. 두 문장이 동일한 경우 동일한 상위 커서를 공유합니다.
• 모든 상위 커서는 그에 대해 생성된 하나 이상의 하위 커서와 함께 실행됩니다.
• 상위 커서는 V$SQLAREA 뷰에 표시됩니다. v$sqlarea의 VERSION_COUNT 열은 이 부모 커서에 몇 개의 자식 커서가 있는지 알려줍니다.

CHILD CURSOR

• 부모 커서는 적어도 하나의 자식 커서를 가진다.
• 상위 커서는 SQL 텍스트를 저장하지만, 하위 커서는 환경 정보, 통계 정보, 바인드 변수 정보, 실행 세부 정보와 같은 SQL 문과 관련된 중요한 정보를 저장합니다.
  
• SQL 텍스트가 자식 커서에 저장되지 않으므로 자식 커서는 메모리 공간을 덜 차지합니다.
  
• 모든 자식 커서는 부모에 속해야 합니다.
  
• 쿼리에 대해 자식 커서는 하드 파싱을 할지 또는 소프트 파싱을 할지를 결정합니다. SQL 쿼리가 동일해서 부모 커서는 동일하지만 자식  커서가 공유되지 않고 하드 파싱(재컴파일)을 하는 상황이 있을 수 있습니다.
  
• 상위 커서는 V$SQL 보기에 표시됩니다.
  
• V$SQL_SHARED_CURSOR는 옵티마이저가 커서를 비공유로 표시하기로 결정한 이유를 제공하므로 매우 유용한 보기입니다. 따라서 SQL 문이 동일하지만 하드파싱이 발생 때 이 뷰를 참조합니다.

V$SQL_SHARED_CURSOR

동일한 상위 커서에 대해 둘 이상의 하위 커서가 생성되되어었을 때 기존 하위 커서와 공유되지 않는 이유를 설명합니다. 뷰의 각 열은 커서를 공유할 수 없는 이유를 식별합니다. 공유되지 않은 사유의 열 값은 "Y" 로 표시됩니다.

특정 자식은 여러 가지 이유로 공유에 실패했을 수 있습니다. 이 이유는 다른 기존 자식 커서를 사용하려는 이유가 됩니다.

cursor_sharing 파라미터

CURSOR_SHARING은 어떤 SQL이 동일한 커서를 공유할 수 있는지를 결정합니다. 세 가지 값이 있습니다.

EXACT

동일한 텍스트가 있는 SQL 구문만 동일한 커서를 공유할 수 있습니다.

FORCE

일부 문자가 다를 경우 그 문자가 SQL문의 의미에 영향을 미치지 않는 한 강제로 커서를 공유합니다.

SIMILAR

문자가 SQL문의 의미나 실행계획 최적화에 영향을 미치지 않는 한 일부 문자가 달라도 동일한 커서로 공유하도록 합니다.
cursor_sharing의 default는 EXACT입니다.

예를 들어 아래 두 개의 서로 다른 SQL 문이 있습니다.

select * from EMP WHERE EMPNO=7369;
select * from EMP where EMPNO=7369;

둘 다 동일한 결과를 생성하지만 이들은 서로 다른 SQL입니다. "where"를 보면 첫 번째 문장에서는 대문자로, 두 번째 문장에서는 소문자로 작성되어 있는데 Oracle optimizer는 두 개를 상이한 SQL로 판단합니다.

V$SQLAREA 뷰를 조회합니다. 부모 커서에 대한 정보를 보여줍니다.

V$SQLAREA 에서는 SQL문에 대한 Parsing 정보 (sql text, sql_id, parse calls, executions 등)와 메모리 사용량, CPU 사용량, 수행시간, 모듈정보 등 외에도 많을 정보를 확인할 수 있습니다.

select *
from  v$sqlarea 
where upper(sql_text) like upper('%select * from EMP%7369%')
and   upper(sql_text) not like upper('%v$sqlarea%');

-- SQL 수행경과
SQL_TEXT	SQL_ID	SHARABLE_MEM	PERSISTENT_MEM	RUNTIME_MEM	SORTS	VERSION_COUNT	LOADED_VERSIONS	OPEN_VERSIONS	USERS_OPENING	FETCHES	EXECUTIONS	PX_SERVERS_EXECUTIONS	END_OF_FETCH_COUNT	USERS_EXECUTING	LOADS	FIRST_LOAD_TIME	INVALIDATIONS	PARSE_CALLS	DISK_READS	DIRECT_WRITES	DIRECT_READS	BUFFER_GETS	APPLICATION_WAIT_TIME	CONCURRENCY_WAIT_TIME	CLUSTER_WAIT_TIME	USER_IO_WAIT_TIME	PLSQL_EXEC_TIME	JAVA_EXEC_TIME	ROWS_PROCESSED	COMMAND_TYPE	OPTIMIZER_MODE	OPTIMIZER_COST	OPTIMIZER_ENV	OPTIMIZER_ENV_HASH_VALUE	PARSING_USER_ID	PARSING_SCHEMA_ID	PARSING_SCHEMA_NAME	KEPT_VERSIONS	ADDRESS	HASH_VALUE	OLD_HASH_VALUE	PLAN_HASH_VALUE	FULL_PLAN_HASH_VALUE	MODULE	MODULE_HASH	ACTION	ACTION_HASH	SERIALIZABLE_ABORTS	OUTLINE_CATEGORY	CPU_TIME	ELAPSED_TIME	OUTLINE_SID	LAST_ACTIVE_CHILD_ADDRESS	REMOTE	OBJECT_STATUS	LITERAL_HASH_VALUE	LAST_LOAD_TIME	IS_OBSOLETE	IS_BIND_SENSITIVE	IS_BIND_AWARE	CHILD_LATCH	SQL_PROFILE	SQL_PATCH	SQL_PLAN_BASELINE	PROGRAM_ID	PROGRAM_LINE#	EXACT_MATCHING_SIGNATURE	FORCE_MATCHING_SIGNATURE	LAST_ACTIVE_TIME	BIND_DATA	TYPECHECK_MEM	IO_CELL_OFFLOAD_ELIGIBLE_BYTES	IO_INTERCONNECT_BYTES	PHYSICAL_READ_REQUESTS	PHYSICAL_READ_BYTES	PHYSICAL_WRITE_REQUESTS	PHYSICAL_WRITE_BYTES	OPTIMIZED_PHY_READ_REQUESTS	LOCKED_TOTAL	PINNED_TOTAL	IO_CELL_UNCOMPRESSED_BYTES	IO_CELL_OFFLOAD_RETURNED_BYTES	CON_ID	IS_REOPTIMIZABLE	IS_RESOLVED_ADAPTIVE_PLAN
select * from EMP WHERE EMPNO=7369	2zp7d0gmunwfj	23539	8888	6752	0	1	1	0	0	1	1	0	1	0	1	2022-01-02/00:18:14	0	1	0	0	0	7	0	0	0	0	0	0	1	3	FIRST_ROWS	3	E289FB89D011220170020080AEF5C3E2CFFA332056414555519521105545551545545558591555449665851D5511058555555155515122555415A0EA0C5551454265415454449081566E001696C6A355451501025415404416FD157151551555550001550A16214545D1C31444A1011015595510250153355555155551E91F1411855B0501655D56456144551525245005F9A4160090505165551695504415957945000544000A5AD01122010502000002000000100000000100002000000210D007000000D00700002003000101000038F8000200000090010000E80300D0112201026564640202643202320000020003020A0A05050A1400020000F4010000640A0A0A0A64E803000064C010030200020000FFFF00000202324000400000E803000010270000002000000A1E03C80000080000010000102700000304810A80969800E80300004B64FFFF0000FFFF00000010000014A08601006440204E00000702C8C823F8FF0000FFFF00000204400A1450320A0A00001000	3703031112	95	95	SCOTT	0	000000008CA91018	3886707153	3891276153	3956160932	3634526668	TOAD 12.8.0.49	2077942562		0	0		835	1118		000000008CA908C0	N	VALID	0	2022-01-02 오전 12:18:14	N	N	N	0				0	0	3.83665796575199E18	1.6946033956547E19	2022-01-02 오전 12:18:14		0	0	0	0	0	0	0	0	1	2	0	0	0	N	
select * from EMP where EMPNO=7369	fah6htfu5fdjg	23539	8888	6752	0	1	1	1	0	1	1	0	1	0	1	2022-01-02/00:18:15	0	1	0	0	0	7	0	0	0	0	0	0	1	3	FIRST_ROWS	3	E289FB89D011220170020080AEF5C3E2CFFA332056414555519521105545551545545558591555449665851D5511058555555155515122555415A0EA0C5551454265415454449081566E001696C6A355451501025415404416FD157151551555550001550A16214545D1C31444A1011015595510250153355555155551E91F1411855B0501655D56456144551525245005F9A4160090505165551695504415957945000544000A5AD01122010502000002000000100000000100002000000210D007000000D00700002003000101000038F8000200000090010000E80300D0112201026564640202643202320000020003020A0A05050A1400020000F4010000640A0A0A0A64E803000064C010030200020000FFFF00000202324000400000E803000010270000002000000A1E03C80000080000010000102700000304810A80969800E80300004B64FFFF0000FFFF00000010000014A08601006440204E00000702C8C823F8FF0000FFFF00000204400A1450320A0A00001000	3703031112	95	95	SCOTT	0	000000008C9AD6E0	3025614383	4198020274	3956160932	3634526668	TOAD 12.8.0.49	2077942562		0	0		1245	1280		000000008A6EEB18	N	VALID	0	2022-01-02 오전 12:18:15	N	N	N	0				0	0	3.83665796575199E18	1.6946033956547E19	2022-01-02 오전 12:18:15		0	0	0	0	0	0	0	0	1	2	0	0	0	N

Oracle DB 에서 실행한 SQL 내역을 조회하고자 할때 v$sql 딕셔너리 뷰를 이용할 수 있습니다.

V$SQL을 조회해 봅니다. 아래와 같이 2개의 SQL 문이 조회됩니다.

v$sql 에는 실행한 SQL 문과 실행 시작시간, 종료시간, 경과시간 등의 다양한 정보가 들어있습니다.

select sql_text, sql_id, sharable_mem, loaded_versions, open_versions, executions, 
       loads, parse_calls, buffer_gets, rows_processed, elapsed_time,
       is_obsolete, is_bind_sensitive, is_bind_aware
from   v$sql 
where  upper(sql_text) like upper('%select * from EMP%7369%')
and    upper(sql_text) not like upper('%v$sql%');

-- SQL 수행결과
SQL_TEXT	SQL_ID	SHARABLE_MEM	LOADED_VERSIONS	OPEN_VERSIONS	EXECUTIONS	LOADS	PARSE_CALLS	BUFFER_GETS	ROWS_PROCESSED	ELAPSED_TIME	IS_OBSOLETE	IS_BIND_SENSITIVE	IS_BIND_AWARE
select * from EMP WHERE EMPNO=7369	2zp7d0gmunwfj	23539	1	0	1	1	1	7	1	1118	N	N	N
select * from EMP where EMPNO=7369	fah6htfu5fdjg	23539	1	1	1	1	1	7	1	1280	N	N	N

V$SQL_SHARED_CURSOR

로드된 각 자식 커서에 대한 정보를 보여줍니다.
커서를 공유할 수 없는 이유를 설명하는 컬럼을 가지고 있습니다.

select * from v$sql_shared_cursor
where  sql_id in('2zp7d0gmunwfj','fah6htfu5fdjg');

-- SQL 수행 결과
SQL_ID	ADDRESS	CHILD_ADDRESS	CHILD_NUMBER	UNBOUND_CURSOR	SQL_TYPE_MISMATCH	OPTIMIZER_MISMATCH	OUTLINE_MISMATCH	STATS_ROW_MISMATCH	LITERAL_MISMATCH	FORCE_HARD_PARSE	EXPLAIN_PLAN_CURSOR	BUFFERED_DML_MISMATCH	PDML_ENV_MISMATCH	INST_DRTLD_MISMATCH	SLAVE_QC_MISMATCH	TYPECHECK_MISMATCH	AUTH_CHECK_MISMATCH	BIND_MISMATCH	DESCRIBE_MISMATCH	LANGUAGE_MISMATCH	TRANSLATION_MISMATCH	BIND_EQUIV_FAILURE	INSUFF_PRIVS	INSUFF_PRIVS_REM	REMOTE_TRANS_MISMATCH	LOGMINER_SESSION_MISMATCH	INCOMP_LTRL_MISMATCH	OVERLAP_TIME_MISMATCH	EDITION_MISMATCH	MV_QUERY_GEN_MISMATCH	USER_BIND_PEEK_MISMATCH	TYPCHK_DEP_MISMATCH	NO_TRIGGER_MISMATCH	FLASHBACK_CURSOR	ANYDATA_TRANSFORMATION	PDDL_ENV_MISMATCH	TOP_LEVEL_RPI_CURSOR	DIFFERENT_LONG_LENGTH	LOGICAL_STANDBY_APPLY	DIFF_CALL_DURN	BIND_UACS_DIFF	PLSQL_CMP_SWITCHS_DIFF	CURSOR_PARTS_MISMATCH	STB_OBJECT_MISMATCH	CROSSEDITION_TRIGGER_MISMATCH	PQ_SLAVE_MISMATCH	TOP_LEVEL_DDL_MISMATCH	MULTI_PX_MISMATCH	BIND_PEEKED_PQ_MISMATCH	MV_REWRITE_MISMATCH	ROLL_INVALID_MISMATCH	OPTIMIZER_MODE_MISMATCH	PX_MISMATCH	MV_STALEOBJ_MISMATCH	FLASHBACK_TABLE_MISMATCH	LITREP_COMP_MISMATCH	PLSQL_DEBUG	LOAD_OPTIMIZER_STATS	ACL_MISMATCH	FLASHBACK_ARCHIVE_MISMATCH	LOCK_USER_SCHEMA_FAILED	REMOTE_MAPPING_MISMATCH	LOAD_RUNTIME_HEAP_FAILED	HASH_MATCH_FAILED	PURGED_CURSOR	BIND_LENGTH_UPGRADEABLE	USE_FEEDBACK_STATS	CON_ID
2zp7d0gmunwfj	000000008CA91018	000000008CA908C0	0	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	0
fah6htfu5fdjg	000000008C9AD6E0	000000008A6EEB18	0	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	0

V$SQLAREA 는 부모 커서에 대한 정보를 담고 있고, V$SQL 은 자식 커서에 대한 정보를 담고 있습니다.

V$SQL_SHARED_CURSOR 은 커서를 공유하지 못한 사유에 대한 정보를 담고 있습니다.

< 참조>

• Performance Tuning Basics 1 : Selectivity and Cardinality
• Performance Tuning Basics 2 : Parsing
• Performance Tuning Basics 3 : Parent and Child Cursors
• Performance Tuning Basics 4 : Bind Variables
• Performance Tuning Basics 5 : Trace and TKPROF – Part 1: Trace
• Performance Tuning Basics 6 : Trace and TKPROF – Part 2: Generating TKPROF
• Performance Tuning Basics 7 : Trace and TKPROF – Part 3: Analyzing TKPROF Files
• Performance Tuning Basics 8 : Trace File Analyzer (TRCA)
• Performance Tuning Basics 9 : Optimizer Mode
• Performance Tuning Basics 10 : Histograms
• Performance Tuning Basics 11 : Steps to analyze a performance problem
• Performance Tuning Basics 12 : Dynamic Performance Views
• Performance Tuning Basics 13 : Automatic Workload Repository (AWR) Basics
• Performance Tuning Basics 14 : Active Sessions History (ASH) Basics
• Performance Tuning Basics 15 : AWR Report Analysis
• Performance Tuning Basics 16 : Using SQL Tuning Health-Check Script (SQLHC)

컬럼에 히스토그램을 삭제하고 자동 통계가 생성되지 않도록 하려면?

데이터 분포가 왜곡된 ID라는 VARCHAR2 열이 있는 테이블을 가지고 있다고 할 때 이 테이블에 대해 자동 통계 작업이 시작되면 거의 모든 SQL 문에서 사용되며 데이터 왜곡이 있기 때문에 이 열에 히스토그램이 자동으로 생성됩니다. 그러나 ID 열의 값은 매우 길고 각 ID의 처음 32자는 동일할 데이터라고 가정합니다.(데이터베이스는 Oracle Database 11g임).

Oracle이 VARCHAR2 열에 히스토그램을 생성할 때 Oracle Database 12c에서 열 값의 처음 64자 또는 Oracle Database 11g에서 열 값의 처음 32자만 고려합니다. 즉, 열 값의 처음 32/64바이트가 동일하면 실제로 값이 다르고 다른 히스토그램 버킷에 있는 것으로 가정하더라도 모든 값이 히스토그램의 단일 버킷에 포함됩니다. Optimizer는 이 히스토그램을 사용할 때 잠재적으로 카디널리티를 잘못 추정할 수 있으며, 이는 차선의 계획을 초래할 수 있습니다. 고유 값 및 기타 통계의 수는 정확하지만 히스토그램에만 이 문제가 있습니다. 이러한 이유로 현재 또는 미래에는 이 특정 열에 히스토그램을 사용하지 않는 것이 좋습니다. 그렇다면 기존 히스토그램을 삭제하고 향후 수집되지 않도록 하려면 어떻게 해야 할까요?

1. 먼저 ID 열에 히스토그램을 삭제합니다.

DBMS_STATS.DELETE_COLUMN_STATS 프로시저를 사용하여 col_stat_type 매개변수를 'HISTOGRAM'으로 설정하면 됩니다.

BEGIN
   dbms_stats.delete_column_stats(
         ownname=>'SH', tabname=>'SALES', colname=>'PROD_ID', col_stat_type=>'HISTOGRAM');
END;

2. DBMS_STATS.SET_TABLE_PREF 프로시저를 사용하여 PROD_ID 컬럼을 제외한 SALES 테이블의 모든 컬럼에 대해 히스토그램을 수집하도록 합니다. 아래 프로시저를 수행하면 PROD_ID 컬렁에 대한 히스토그램을 생성하지 않습니다. 하나의 버킷만을 생성하므로 통계 정보로써의 의미가 없도록 하는 것입니다.

BEGIN
   dbms_stats.set_table_prefs('SH', 'SALES',
   'METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 1 PROD_ID');
END;
/

자동 통계 수집 작업이 PORD_ID 컬럼에 히스토그램을 다시 생성도록 하려면 아래와 같이 DBMS_STATS.SET_TABLE_PREF 프로시저를 사용합니다. Oracle 이 자동으로 통계 정보에 대한 버킷을 생성하도록 합니다.

BEGIN
   dbms_stats.set_table_prefs('SH', 'SALES',
   'METHOD_OPT', 
   'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE AUTO PROD_ID');
END;
/

2021/12/28 오전 10:35분경 업무가 정상 처리가 안된다는 연락을 받고

최초 테이블 Lock을 의심해서 Lock 정보를 조회하려 DB에 접속 시도를 했으나, 접속이 되지 않았다.

몇 번의 접속 시도를 하는데 갑자기 접속이 되어 DB를 보니 다른 Muiti-Zone으로 failover가 된 상태였다.

DB 시작시간을 확인해 보니 2021/12/28 오전 10:39:09 였다. 장애 발생 이후 시간이다.

이후 AWS RDS의 모니터링과 Performnace Insights 를 살펴보았다.

특정 시점에서 평소 5미만인 Active session이 478까지 증가하였고,

그 시점의 SQL이 library cache lock, library cache : mutex X, regmgr CPU quantum, kksfbc child completion, CPU 지표들이 상당히 높계 계측되었다.

CloudWatch가 수집한 서버 모니터링 정보를 보니,

CPU, DB Connections, Write IOPS, read IOPS 등의 장애 시점에 평소 대비 높았다.

특히 Freeable Memory는 장애 시점에 0으로 여유 메모리가 없는 상황이 발생했다. 이런 상황에서 AWS에서 RDS를 장애로 판단하고 Failover 시킨 것으로 보인다.

일단, SQL 을 추출하여 Plan 정보를 확인해 보았다. SQL문은 Bind 변수를 사용하였다.

특별한 점은 없어 보였다.

library cache lock event 로 보아, 장애 시점에 DDL이 있었을 것이라고 판단하고 object 들의 생성일자와 수정일자를 살펴보았다. 그 시점에 생성이나 변경된 object는 보이지 않았다. ( 생성하고 삭제했을 경우에는 알수가 없을까?)

장애 원인으로 가정한 아래 세가지 상황은 아니다. 

1) 테이블 LOCK

2) library cache lock 이 발생한 SQL 관련 object의 생성이나 수정

3) 과도한 SQL 문장(bind 변수없이 literal 사용 포함)

그래서 library cache lock, library cache : mutex X  2가지 event를 추적해 보았다.

첫번째 왜 이런 이벤트(library cache lock, library cache : mutex X)가 발생하는가?

library cache lock 은 

• SQL 구문을 파싱할 때나 LCO를 참조할 때 획득해야 하는 Lock으로 library cache lock 획득하지 못하여 대기하며 발생하는 이벤트이다.

library cache : mutex X

• shared pool에서 cursor를 검색할 때 mutex를 획득하지 못하여 대기하며 발생하는 이벤트이다

결론은 child cursor가 너무 많아 검색 시간이 오래 걸리면서 다른 세션들이 library cache lock, library cache : mutex X 를 획득하는데 대기하는 이벤트가 발생한 것으로 판단해 볼 수 있다.

장애 시점의 child cursor 에 대한 상세 정보를 추적해 본다.

1. snap_id 확인

dba_hist_snapshot 에서 장애 발생 시점의 snap_id를 확인해 보았다.

- 장애 직전 snapshot : 649

- 장애 이후 snapshot : 650

DB 기동 시간 : 2021/12/28 AM 10:39:09.000

2. sql_id와 SQL 관련 정보 확인

SQL fulltext와 sql_id 등의 정보를 얻고자 v$sql 을 조회하려고 했으나 v$sql은 dynamic performance view로 DB가 기동된 이후의 정보만 조회할 수 있으므로 failover된 상태라 dba_hist_sqltext과 dba_hist_sqlstat 뷰를 조회했다.

dba_hist_sqltext 에서 sql_id를 추출했다.

추출한 sql_id로 dba_hist_sqlstat 을 조회했다. 특이사항을 확인해 본다.

plan_hash_value 가 0 인 경우가 많다.

메모리 부족현상이 있었으니 sharable_mem 컬럼과 주의깊게 살펴볼 필요가 있는 dba_hist_sqlstat 테이블의 컬럼(loaded_versions, version_count, fetches_total, fetches_delta, executions_total, executions_delta, invalidations_total, invalidations_delta, parse_calls_total, parse_calls_delta, buffer_gets_total, buffer_gets_delta, rows_processed_total, rows_processed_delta, elapsed_time_total, elapsed_time_delta) 등을 선정해 분석해 본다.

장애 시점에 sharable_mem 값이 상당이 높게 나왔다. 그러나 다른 날의 자료와 비교해 볼 때 상대적으로 특이사항은 보이지 않는다.

version_count 값을 조회해 보니 상당이 높게 나왔다. 다른 날 대비 약간의 이상 징후가 보인다.

parse_calls_delta 값의 추이도 특이사항이 보이지 않는다.

elapsed_time_delta 값의 추이도 장애 시점 상 특이사항이 보이지 않는다.

1차 dba_hist_sqlstat 으로 분석한 결과, version_count 에 대한 분석이 필요한 것으로 판단된다.

* 특이사항으로는 위 컬럼의 정보들이 수집되지 않았다. NULL 값들이 상당히 많다 --> 왜 정보를 수집하지 못했는지에 대한 확인이 필요해 보인다. 정보수집을 못했을 때에 version_count가 상대적으로 상당히 값이 크다.

<참고사항>

장애 시점에 서버 메모리 사용률이 100% 이었는데 이는 어떻게 설명되는가? 아래 이미지를 보면 이해할 수 있을 것이다. 

(1) 커서란?

공유 커서 (shared cursor) : 라이브러리 캐시에 공유돼 있는 Shared SQL Area
세션 커서 (session cursor) : Private SQL AREA에 저장된 커서
애플리케이션 커서 (application cursor) : 세션 커서를 가리키는 핸들

추가적으로, 대기 이벤트들이 발생하면서 Active session이 급격히 증가하였고

이는 서버 프로세스를 생성하며 메모리를 소진시키는 원인이 되었다. 

1차 결론은

version_count 때문에 장애가 발생한 것을 추정하고 이 값을 중심으로 분석해 본다.

version_count는 무엇인가?  왜 version_count가 높게 나오는 것일까? 

version_count란

SQL은 한번 파싱되면 실행계획을 생성하고 재사용하기 위해서 실행정보를 생성한다. ( 이를 LCO: library cache object 형태로 저장되며 shared cursor 라고 합니다.)

그리고 메모리(Shared pool 안의 library cache 안의 Shared SQL Area)에 저장합니다.

동일한 SQL 이더라도 여러가지 이유(주1)로 실행정보를 공유하지 못하면 추가적인 Chlid LCO ( child cursor )를 생성합니다.

즉, 새로운 버전의 child cursor 가 생성되는 것입니다. 이를 version count 가 증가한다고 표현합니다.

하나의 SQL(parent cursor)에 대해서 여러 child cursor 가 생기면 가장 문제가 되는 것은

SQL 수행시에 어떻게 실행할지 실행정보를 탐색하는데 오래걸리고,

경합이 발생할 수 있다는 점과 (이 때 발생하는 Wait Event를 library cache latch 라고 합니다.)

메모리의 공간을 차지한다는 점입니다.

아래 MOS 문서도 참고할 만합니다.

Troubleshooting: High Version Count Issues (Doc ID 296377.1)

https://support.oracle.com/epmos/faces/DocumentDisplay?id=296377.1

​

High SQL Version Counts - Script to determine reason(s) (Doc ID 438755.1)

https//support.oracle.com/epmos/faces/DocumentDisplay?id=438755.1

​

하나의 SQL 문장이 여러 개 Child 커서를 갖게 되는 이유

1. SQL에서 참조하는 오브젝트명이 같지만 SQL을 실행한 사용자에 따라 다른 오브젝트를 가리킬 때 - N/A

2. 참조 오브젝트가 변경돼 커서가 무효화되면 이후 그 커서를 처음 사용하려는 세션에 의해 다시 하드파싱돼야 하는데

   , 특정 세션이 아직 기존 커서를 사용 중(Pin)일 때 - N/A

3.  옵티마이저 모드를 비롯해 옵티마이저 관련 파라미터가 다를 때

Optimizer mismatch : The optimizer environment does not match the existing child cursor

 [ Optimizer Mode 외에 옵티마이져 관련 파라미터 불일치시 ]

4.  입력된 바인드 값의 길이가 크게 다를 때

5.  NLS 파라미터를 다르게 설정했을 때 - NA

6.  SQL trace를 활성화했을 때 - N/A

위의 사유 중 해당 사항을 찾기 위해

version_count가 높게 나온 이유를 파악하기 위해서 V$SQL_SHARED_CURSOR 조회한다.

mismatch 컬럼이 약 61개 정도라 일일이 'Y'인 값을 파악하는 것이 쉽지 않다.

아래 SQL을 수행하면 sql 문장이 출력되는데

출력된 SQL의  '&v_sql_id' 에 자신의 'sql_id' 를 입력한 후 query를 수행하면 mismatch 컬럼값이 'Y'인 것을 count 해서 보여준다.

select q'<select reason_not_sharing, count(*)
 from gv$sql_shared_cursor
 unpivot ( flag for reason_not_sharing in (>' || listagg(column_name, ', ') within group (order by null) || q'<) )
 where sql_id = nvl('>' || '&' || q'<v_sql_id', sql_id)
   and flag = 'Y'
  group by reason_not_sharing>' x
 from dba_tab_columns
 where owner = 'SYS'
   and table_name = 'V_$SQL_SHARED_CURSOR'
   and data_type = 'VARCHAR2'
   and data_length = 1;
   
   -------- Results ------
REASON_NOT_SHARING:COUNT(*)
USE_FEEDBACK_STATS:47775
BIND_EQUIV_FAILURE:47787
LOAD_OPTIMIZER_STATS:6

커서가 공유되지 않은 이유로 USE_FEEDBACK_STATS, BIND_EQUIV_FAILURE 로 조회되었다.

위의 query에서 찾은 컬럼으로 다시 아래 SQL로 reason까지 같이 조회해 본다.

SQL> 
select BIND_EQUIV_FAILURE, use_feedback_stats,  substr(s, instr(s, '<reason>')+8, instr(s, '</reason>') - (instr(s, '<reason>') + 8) ) res, count(*)
from 
(
select BIND_EQUIV_FAILURE, use_feedback_stats, DBMS_LOB.substr(reason, 100, 1) s
from v$sql_shared_cursor where sql_id = '69v32zxbv1mzk'
) 
group by BIND_EQUIV_FAILURE, use_feedback_stats, substr(s, instr(s, '<reason>')+8, instr(s, '</reason>') - (instr(s, '<reason>') + 8) )
order by 1;

------- Results --------------------------------
BIND_EQUIV_FAILURE:USE_FEEDBACK_STATS:RES:COUNT(*)
Y:N:Bind mismatch(24):6
Y:N:Bind mismatch(25):6
Y:Y:Bind mismatch(22):564
Y:Y:Bind mismatch(24):1145
Y:Y:Optimizer mismatch(13):46183

커서가 공유되지 않은 이유로 USE_FEEDBACK_STATS, BIND_EQUIV_FAILURE이고 reason은 Bind mismatch와 Optimizer mismatch 로 조회되었다.

우선 bind mismatch가 발생한 사유를 알아보자.

BIND_MISMATCH : The bind metadata does not match the existing child cursor.

Oracle은 varchar2 TYPE을 32, 128, 2000, 4000 구간으로 나누어 bind 값을 사용하기 때문에 bind값의 길이에 따라 bind mismatch 가 발생한다. 그러나, 우리의 경우에는 bind를 4개 사용했고 각각 varchar2(27), varchar2(27), varchar2(1000), varchar2(1000) 이었다. 경우의 수를 따져도 9개(1*1*3*3)이다. 즉, 순수 bind 값의 길이 때문에 child cursor 가 새로 생겼다고 보기는 어려울 것 같다.

커서가 공유되지 않은 원인으로 도출된 두 개의 값에 대해 분석해 본다.

BIND_EQUIV_FAILURE = 'Y'

USE_FEEDBACK_STATS = 'Y' 

이 두 값이 의미하는 것이 무엇인지 확인해 본다.

• USE_FEEDBACK_STATS : 카디널리티 피드백. 카디널리티 피드백이 사용 중이고 현재 실행에 대한 새 계획이 생성될 수 있다.

• BIND_EQUIV_FAILURE : 바인드 값의 선택도가 기존 자식 커서를 최적화하는 데 사용된 선택도와 일치하지 않습니다. adaptive cursor sharing이 설정되어 있고 커서가 바인드를 인식할 때 선택도가 현재 범위를 벗어나 새로운 Plan이 더 요구되는 경우 이전 Plan을 공유하지 않고 새로운 child cursor 생성합니다. 오라클 문서 836256.1을 참조하십시오.  
•  
•  
• ... once the cursor is marked as bind aware and a second plan is seen then the following will be the resultant output:
  SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
  ------------- ---------------- ---------------- ------------ -
  19sxt3v07nzm4 000000007A1C0DE0 000000007A1BF980            0 N
  19sxt3v07nzm4 000000007A1C0DE0 000000007A10DDB0            1 Y
  
  
  As can be seen, the new version is created due to BIND_EQUIV_FAILURE
• select sql_id, address, child_address, child_number, BIND_EQUIV_FAILURE from v$sql_shared_cursor where sql_id='19sxt3v07nzm4';

v$sql_cs_selectivity 조회

select count(*)
from v$sql_cs_selectivity 
where sql_id='69v32zxbv1mzk' ;  

--> 102052


select predicate, range_id, low, high, count(*)
from V$SQL_CS_SELECTIVITY where sql_id = '69v32zxbv1mzk'
group by predicate, range_id, low, high;

PREDICATE RANGE_ID     LOW      HIGH     COUNT(*)
--------------------------------------------------
=1	        0	    0.301835	0.368910	2800
=1	        0	    0.296330	0.368910	1
=2	        0	    0.301827	0.368899	10633
=1	        0	    0.296330	0.368899	1
=1	        0	    0.296330	0.362181	32339
=2	        0	    0.296330	0.362181	32339
=2	        0	    0.296330	0.368910	1
=1	        0	    0.301827	0.368899	10633
=2	        0	    0.301835	0.368910	2800
=2	        0	    0.296330	0.368899	1

선택도 범위(V$SQL_CS_SELECTIVITY)가 많으며 겹친다. 

BIND_EQUIV_FAILURE 상세 설명

커서가 바인드를 인식하게 되면 이 커서가 실행될 때마다 확장 커서 공유 레이어 코드(Extended Cursor Sharing layer code)가 바인드 변수 값(본 문서의 경우에는 4개의 바인드 변수가 있음)을 보고(Peeking)  v$sql_cs_selectivity에 대한 select를 실행한다. 기존 child 커서가 Peeking한 바인드 변수의 선택도를 수용하고 있는지 확인하기 위해서이다. 자식 커서가 발견되면 공유하고 그렇지  않은 경우 새 child 커서가 바인드 변수 값 선택성의 새 범위와 함께 v$sql_cs_selectivity에 입력된다.
이 경우 확장 커서 공유 레이어 코드(Extended Cursor Sharing layer  code)가 v$sql_cs_selectivity에서 적절한 선택 범위(BIND_EQUIV_FAILURE)를 가진 자식 커서를 찾지 못하면 새로운 실행계획을 컴파일하고 다수의 "optimal" plan으로 v$sql 뷰를 채운다.

체크사항 :

• V$SQLAREA에서 커서의 VERSION_COUNT가 높다.
• 커서를 공유하지 않는 이유가 V$SQL_SHARED_CURSOR에 BIND_EQUIV_FAILURE로 표시된다.
• 쿼리에는 커서 공유와 관련된 바인드 변수를 사용했다.
• 선택도 범위(V$SQL_CS_SELECTIVITY)가 많으며 겹친다. 
• 동시성이 높은 경우 문제가 "커서: 핀 S는 X에서 대기"로 표시되며 커서에 대해 대기한다.

위의 경우에 해당될 경우 버그로 의심할 수 있다. 그럴 경우 workaroud로 다음 설정을 권고한다. 본 문서가 오라클 공식 문서가 아니기에 적용 시 반드시 Oracle의 지원을 받아 진행해야 한다. 

Workaround : _optimizer_extended_cursor_sharing_rel = none    으로 설정

결국은 optimizer_adaptive_cursor_sharing 때문인 것 같다.

DB 전체에 대해 사용하지 않도록 설정하던지, 아니면 문제를 일으키는 SQL 관련 테이블의 컬럼에 대해서만 설정하던지.

일단, 후자를 선택해 진행해 본다.

대상 테이블 컬럼의 histogram 을 삭제하고, 더 이상 histogram을 생성하지 않도록 설정한다.

상기 장애와 관련하여 최선의 선택이 아닐까 한다. 물론 다른 부작용도 검토를 해야 한다.

Disable the Adaptive cursor sharing in Oracle

alter system set "_optimizer_adaptive_cursor_sharing"=false scope= both;

--Disable bind variable or other parameter
alter system set "cursor_sharing"=exact scope=both;
alter system set "_optimizer_extended_cursor_sharing_rel"=none scope=both;
alter system set "_optimizer_extended_cursor_sharing"=none scope= both;

Three 11g features of cost-based optimization and SQL execution to consider when upgrading from 10g:

Cardinality feedback. This feature, enabled by default in 11.2, is intended to improve execution plans for repeated executions. See Support note 1344937.1 and documentation for additional info. You can be disable cardinality feedback with an underscore parameter which can be set at session or instance level. Usual caveats on setting underscore parameters apply:
alter session set "_OPTIMIZER_USE_FEEDBACK"=FALSE;
This can also be done with a hint, therefore at statement level, using the opt_param syntax: /*+ opt_param('_OPTIMIZER_USE_FEEDBACK','FALSE') */


Adaptive cursor sharing. This was introduced in 11gR1 to address SQL performance consistency issues related to bind variable peeking.  See support note 740052.1 and documentation for details. For the purposes of this doc, if you want to get the 10g behavior with  an Oracle version higher than 10g and overall if you want to disable this feature, you may want to add the hint:
/*+ NO_BIND_AWARE */. 
According to support note 11657468.8 adaptive cursor sharing can be disabled by setting the following 2 parameters (say at session level): _optimizer_adaptive_cursor_sharing = false, _optimizer_extended_cursor_sharing_rel = "none"

For more details on this bug, the Document ID of MOS is 14176247.8

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=561615465291525&parent=EXTERNAL_SEARCH&sourceId=PROBLEM&id=1621829.1&_afrWindowMode=0&_adf.ctrl-state=j1i7joth3_4

참조 : https://www.linkedin.com/pulse/oracle-cursor-sharing-bug-diego-zucca

위에서 발생한

kksfbc child completion 이벤트에 대해 _kks_use_mutex_pin =  false 로 설정하라는 글들이 있는데

이  hidden parameter는 Oracle 동의가 있어야만 사용해야 하며 이 매개변수는 Oracle 12c에서 설정하면 안 됩니다." 라는 글도 있으니 참고하기 바랍니다. 본 문서에서는 이 이벤트는 다루지 않는다.

- Version Count가 큰 SQL 확인

SQL>SELECT SQL_ID , VERSION_COUNT ,  EXECUTIONS , INVALIDATIONS  

        FROM v$sqlarea 

        where sql_id = '위에서 찾은 sql_id'

        ORDER BY  version_count desc;

69v32zxbv1mzk 4097 54132 0

USE_FEEDBACK_STATS이 'Y' 인 경우, 

(Y|N) A hard parse is forced so that the optimizer can reoptimize the query with improved cardinality estimates

옵티마이저가 향상된 카디널리티 추정으로 쿼리를 다시 최적화할 수 있도록 하드 구문 분석이 강제 실행됩니다.

AWR Report 확인

AWS RDS Oracle Enterprise version에서만 제공한다.

ASH Report 확인

alert log 파일 확인

tarce 파일 확인

<참고>

semaphore vs mutex 란?

mutex : object, 자물쇠, locking mechanism, multiple program threads 이 single recource에만 접근 가능(동시 접근 불가), 오직 mutex를 acquire/release 할 수만 있다(Locked or unlocked)

화장실 열쇠

semaphore : sgnaling mechanism, 신호동, integer(couting semaphore), semaphore value는 update할수 있다.

signal  and wait.