[ORACLE] SQL PLAN 고정

SQL PLAN 고정 

 

 - 커서의 실행계획 확인


SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR(' SQL_ID입력'));
 
출력 EX>
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID    123123ssaawd2, child number 0
-------------------------------------
select * from emp a, dept b where e.deptno = d.deptno; 
 
Plan hash value: 4216042411
 
---------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      |      |    6 (100)|      |
|*  1 |  HASH JOIN       |      |    14 |   770 |    6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |    4 |    88 |    3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   462 |    3   (0)| 00:00:01 |
---------------------------------------------------------------------------
 


 - 현재 실행계획을 SQLPLAN 베이스라인으로 설정


set serveroutput on
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET (
  SQL_NAME => '123123ssaawd2',
  DESCRIPTION => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
end;
/
PL/SQL procedure successfully completed.
 
- snap 시간 및 snap id 확인 쿼리
select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot order by end_interval_time;

 - 해당 시간대에 sql_id에 플랜변경된 내용 수집
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE (
dbms_sqltune.select_workload_repository(begin_snap=>1111, end_snap=>1234, basic_filter='sql_id=''123123ssaawd2''',attribute_list='ALL')
) p;
DBMS_SQLTUNE_LOAD_SQLSET(sqlset_name=>'123123ssaawd2', populate_cursor=>cur);
CLOSE cur;
END;
/

 - 위에 수집한 플랜정보의 성능 및  정보 확인
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=>'123123ssaawd2')
);


 -위에서 나온 좋은 성능의 플랜 고정
Declare
my_plans pls_integer;
begin
 my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET (
sqlset_name =>'123123ssaawd2',
basic_filter=>'plan_hash_value = ''1234567655'''
);
END;
/


 - 해당 쿼리 정보 확인
select * from dba_sql_plan_baselines;
select * from gv$sqlarea where sql_id='123123ssaawd2';

 - 메모리에 플랜정보 purge 명령어 
select sql_id,address,hash_value fro v$sql where sql_id='123123ssaawd2';
exec dbms_shared_pool.purge('address.hash_value','C');