오라클 19c 하드파싱 쿼리 플랜 변경 의심시 조치방법

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.10.0.0

 

방법 : 오라클 19c 하드파싱 쿼리 플랜 변경 의심시 조치방법

운영중 특정 쿼리로 인해 db의 부하가 심해지고 cpu 사용률이 올라갈때

아래 쿼리로 쿼리의 실행계획이 변경되었는지 확인가능함

 

1. 차일드 커서 체크(실행계획 변경여부 확인 child_number 0이상이면 실행계획이 변경되었을 수 있음)

SQL>
--https://positivemh.tistory.com/372
select sql_id,
  PARSING_SCHEMA_NAME "USER",
  PLAN_HASH_VALUE,
  OPTIMIZER_MODE,
  child_number,
  parse_calls,
  USERS_OPENING,
  USERS_EXECUTING,
  loads,
  executions,
  invalidations,
  decode(sign(invalidations), 1, (loads-invalidations), 0) reloads,
  FIRST_LOAD_TIME,
  LAST_LOAD_TIME
from v$sql
where sql_id='sql_id'
and  sql_text not like '%v$sql%';

 

 

2. 동일 sql_id의 다른 plan_hash_value 확인

SQL>
select distinct sql_id, sql_plan_hash_value from dba_hist_active_sess_history
where sql_id = 'sql_id';

 

 

3. sql_id 실행계획 plan_hash_value 별 성능 확인

SQL> 
select a.instance_number inst_id, a.snap_id,a.plan_hash_value, to_char(begin_interval_time,'dd-mon-yy hh24:mi') btime, 
abs(extract(minute from (end_interval_time-begin_interval_time)) + extract(hour from (end_interval_time-begin_interval_time))*60 + extract(day from (end_interval_time-begin_interval_time))*24*60) minutes,
executions_delta executions, round(ELAPSED_TIME_delta/1000000/greatest(executions_delta,1),4) "avg duration (sec)" 
from dba_hist_sqlstat a, dba_hist_snapshot b
where sql_id='sql_id' and a.snap_id=b.snap_id
and a.instance_number=b.instance_number
order by snap_id desc, a.instance_number;

 

 

이전에 수행된적이 있거나 바인드 변수를 사용한 쿼리의 경우 위 쿼리에  sql_id를 넣어

조회할 경우 플랜이 변경되었는지 여부를 알수 있고 spm을 이용해서 변경전 good 플랜으로 다시 고정시켜줄 수 있음

 

 

하지만 이전에 수행된적이 없거나 바인드 변수를 사용하지 않은 쿼리의 경우 하드 파싱이 일어나고

위 쿼리를 이용해 조회해도 이전 good 플랜을 찾아볼 수 없음

 

 

이 경우 v$sql에서 sql_text 로 like 조건으로 비슷한 쿼리가 수행된 적이 있는지 확인후

해당 플랜으로 수행되도록 힌트를 넣어 튜닝해줄 수 있음

비슷한 쿼리가 수행되었는지 확인

SQL> 
select sql_id, plan_hash_value, last_load_time, sql_text
from v$sql
where sql_text like '%select * from ttest1 where col1>=3%';

 

 

비슷한 쿼리를 찾았다면 쿼리의 full_text를 복사 한뒤

해당 쿼리에 gather_plan_statistics 힌트를 넣어 실행, 또는 예상실행계획 확인(일반 xplan 또는 autotrace)

SQL> select /*+ gather_plan_statistics test1 */ 
*
from ttest1
where col1>=3
;

 

 

v$sql 에서 해당 sql의 sql_id 확인

SQL> 
select sql_id, plan_hash_value, last_load_time, sql_text
from v$sql
where sql_text like '%select /*+ gather_plan_statistics test1 */%';

 

 

xplan display_cursor로 커서에 올라간 실제 플랜 조회

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('62qrgv4fh00xt', NULL, 'ADVANCED ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID    62qrgv4fh00xt, child number 0
-------------------------------------
select /*+ gather_plan_statistics test1 */ * from ttest1 where col1>=3
 
Plan hash value: 4041766012
 
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation     | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time    | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |      1 |    |    |     2 (100)|        |      1 |00:00:00.01 |       2 |
|*  1 |  INDEX RANGE SCAN| TTEST1_IX1 |      1 |      1 |    13 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / TTEST1@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "TTEST1"@"SEL$1" ("TTEST1"."COL1"))
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("COL1">=3)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "COL1"[NUMBER,22]
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
 
   1 -    SEL$1
     E -  test1
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
 
Query Block Registry:
---------------------
 
  <q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[TTEST1]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
 
 
 
59 rows selected.

성능이 좋았던 쿼리가 ttest1_ix1 index를 사용하고 있음을 확인

 

 

해당 쿼리 실행계획 확인 후 느려진 쿼리에 힌트 적용

SQL> select /*+ index(ttest1 ttest1_ix1) */
 * 
from ttest1 
where col1>=3;

 

 

참조 : https://positivemh.tistory.com/364