[ORACLE] SQL 통계정보 변경 시 복구

1. 해당 쿼리 정보확인 

SQL>select * from DBA_HIST_SQLTEXT where SQL_TEXT like '%해당 쿼리%';

 

2.해당 쿼리의 SQL_ID 확인 후 해당 SQL_ID의 PLAN_HASH_VALUE 확인하여 변경 여부 확인

SQL>select * from GV$SQL_PLAN where sql_id='6wp4zfvpb97jh';

SQL>select * from DBA_HIST_SQL_PLAN where sql_id='6wp4zfvpb97jh';

 

3. SQLTUNE 을 위한 SQLSET 생성

SQL>

BEGIN

  DBMS_SQLTUNE.CREATE_SQLSET(

    sqlset_name => '6wp4zfvpb97jh',

    description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');

END;

/

 

4. sql 복구를 위하여 스냅샷 기간 확인을 위해 조회 

SQL> SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME FROM dba_hist_snapshot ORDER BY END_INTERVAL_TIME DESC;

 

5. snap_id 229434 ~ 235292 까지 6wp4zfvpb97jh의 대상 조회를 위해 수행

SQL>

DECLARE

  cur sys_refcursor;

BEGIN

  OPEN cur FOR

    SELECT VALUE(P)

    FROM TABLE(

       dbms_sqltune.select_workload_repository(begin_snap=>229434, end_snap=>235292,basic_filter=>'sql_id = ''6wp4zfvpb97jh''',attribute_list=>'ALL')

              ) p;

     DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> '6wp4zfvpb97jh', populate_cursor=>cur);

  CLOSE cur;

END;

/

 

 

6. 5번 수행 후 snap_id 229434 ~ 235292 동안의 6wp4zfvpb97jh 쿼리의 plan_hash_value 마다의 성능 확인 

SQL>SELECT

  first_load_time          ,

  executions as execs              ,

  parsing_schema_name      ,

  elapsed_time  / 1000000 as elapsed_time_secs  ,

  cpu_time / 1000000 as cpu_time_secs           ,

  buffer_gets              ,

  disk_reads               ,

  direct_writes            ,

  rows_processed           ,

  fetches                  ,

  optimizer_cost           ,

  sql_plan                ,

  plan_hash_value          ,

  sql_id                   ,

  sql_text

   FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => '6wp4zfvpb97jh')

             );

 

 

7. 6번조회 후 성능이 더 좋은  plan_hash_value 선택 후 수행 

SQL>

DECLARE

my_plans pls_integer;

BEGIN

  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(

    sqlset_name => '6wp4zfvpb97jh', 

    basic_filter=>'plan_hash_value = ''1961174709'''

    );

END;

/

 

 

7. sql_id - 6wp4zfvpb97jh 의 플랜 변경 확인 

SQL> SELECT * FROM dba_sql_plan_baselines ;

 

SQL> SELECT * FROM GV$SQLAREA

WHERE SQL_ID='6wp4zfvpb97jh';

 

 

 

8. PLAN_HASH_VALUE 가 변경이 안될 시 기존 sql_id의 shared_pool purge 수행

  -address 및 hash_value 확인을 위한 조회

SQL>select sql_id, address, hash_value from v$sql

where sql_id='6wp4zfvpb97jh';

 

SQL>exec dbms_shared_pool.purge('address명.hash_value명','C');

ex>

SQL>exec dbms_shared_pool.purge('070001102AF8BE18.3937705520','C');