9.(12c의 새로운 기능) SQL PLAN MANAGER 와 SQL Profile

SPM 은 SQL의 실행계획의 안정성을 유지하는데 중점을 둔 기능입니다.

평상시에 우리 회사의 중요한 sql에 대한 실행계획이 계속 좋은 실행계획으로
유지 관리되기를 dba 들은 소망합니다.  SPM 이 다음의 원리로 이를 구현해주고
있습니다. 

1. 실행계획 캡쳐 : 현재 수행되고 있는 SQL들의 실행계획을 자동으로 캡쳐합니다.
                         SQL Plan base line 에 저장합니다.
2. 실행계획 검증 : 저장된 실행계획을 주기적으로 검증하여, 최적의 실행계획을 유지
3. 실행계획 진화: 새로운 실행계획이 기존 실행계획보다 더 성능이 좋으면,
                        해당 계획을 SQL plan base  에 추가시킵니다.

select  ename, sal
  from  emp
  where  ename='SCOTT'; 

■ 실습:  

1. SPM 기능 활성화 하기

show  parameter  optimizer_capture_sql_plan_baselines

alter   system  set  optimizer_capture_sql_plan_baselines=true  scope=both;

shutdown immediate
startup

show  parameter  optimizer_capture_sql_plan_baselines

2. SQL 을 실행합니다.

connect  scott/tiger

select * from  emp  where deptno = 10;

create  index  emp_deptno
 on emp(deptno);

select * from  emp  where deptno = 10;
select * from emp where empno = 7788;

3. 캡쳐된 SQL PLAN baseline 을 확인합니다.

select  sql_handle, plan_name, enabled, accepted, fixed, optimizer_cost
  from  dba_sql_plan_baselines;

4. 해당 SQL의 SQL_ID를 확인합니다.

SELECT s.sql_id, b.sql_handle, b.plan_name, b.enabled, b.accepted, b.fixed, b.optimizer_cost, s.sql_text
FROM dba_sql_plan_baselines b
JOIN v$sql s ON s.exact_matching_signature = b.signature OR s.force_matching_signature = b.signature
WHERE s.sql_text LIKE 'select * from%'; -- 필요한 경우 특정 텍스트로 필터링  

문제1. SPM 기능을 다시 끄시오 

alter  system  set  optimizer_capture_sql_plan_baselines=false  scope=both;

shutdown  immediate

startup

show parameter optimizer_capture_sql_plan_baselines

■ SQL  profile 

  SQL profile 은 SQL튜닝 어드바이져의 일부로 제공이 되며 SQL문의 성능 향상을
 시키기 위한 목적으로 사용됩니다.  
 SQL문을 수정하지 않고 보다 더 좋은 실행계획으로 실행계획을 변경하고자 할때
 사용이 됩니다.  

 SQL profile 을 생성하는 방법 2가지?

 1. 수동 생성  : 스크립트를 직접 수행해서 생성하는 방법
 2. 자동 생성  : 밤10시에 오라클이 스스로 자동으로 생성하는 방법

■  수동 생성 방법 실습

-- scott으로 test 테이블 생성

SQL> connect scott/tiger

SQL> create table test (n number );

SQL> declare
begin
for  i  in  1 .. 10000  loop
insert into test values(i);
commit;
end loop;
end;
/

-- 인덱스 생성
SQL> create index test_idx on test(n);


-- test 테이블 통계정보 분석

SQL> analyze table test estimate statistics;


-- NO_INDEX 힌트를 주어 풀테이블 스캔으로 수행되는 SQL확인

-- 실행계획만 보겠다고 지정합니다.
SQL> set autot traceonly explain

SQL> select /*+ NO_INDEX(test test_idx) */ *  from test  where n = 1 ;

full table scan 이 됩니다.

-- 튜닝 TASK생성 후, SQL Tuning Advisor를 실행

SQL> connect / as sysdba

# 안되면 task를 drop  하고 수행합니다.
SQL> exec dbms_sqltune.drop_tuning_task('my_sql_tuning_task_1');


SQL> 
declare
         my_task_name    VARCHAR2(30);
         my_sqltext         CLOB; 
begin
         my_sqltext := 'select /*+ no_index(test test_idx) */ *
                              from test where n = 1';

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
                          sql_text => my_sqltext,
                          user_name => 'SCOTT',
                          scope => 'COMPREHENSIVE',
                          time_limit => 60,
                          task_name => 'my_sql_tuning_task_1',
                         description => 'Task to tune a query on a specified table' );
end;
/


SQL> 
begin
   DBMS_SQLTUNE.EXECUTE_TUNING_TASK (task_name => 'my_sql_tuning_task_1' );
end;
/


-- SQL Tuning Advisor을 통해 얻은 결과(튜닝 레포트)를 확인하고 SQL Profile을 적용

SQL> SET LONG 70000
SQL> SET LONGCHUNKSIZE 1000
SQL> SET LINESIZE 100
SQL> SET PAGES 4000

SQL> select DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_1') 
        from  DUAL;


-- SQL profile 을 받아들여서 보다 더 좋은 실행계획으로 변경을 하는 작업 

DECLARE
   my_sqlprofile_name VARCHAR2(30);
BEGIN
   my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
   task_name => 'my_sql_tuning_task_1',
    name => 'my_sql_profile' );
END;
/


-- 위에서 실행한 악성 SQL의 실행계획을 확인

SQL> conn scott/tiger
SQL> set autot traceonly explain
SQL> select /*+ NO_INDEX(test test_idx) */ * from test where n = 1;

SQL은 안고쳤는데 실행계획만 변경되었습니다. 

-- sql profile drop 방법
 
exec dbms_sqltune.drop_sql_profile('my_sql_profile', true)

-- sql tuning task  drop 방법

connect / as sysdba

select task_name
 from  dba_ADVISOR_TASKS;

exec dbms_sqltune.drop_tuning_task('my_sql_tuning_task_1');

SQL profile 을 적용해서 실행계획이 변경되게 해봤습니다.
위의 작업이 매일 밤 10시에 자동으로 수행됩니다.
하루동안 돌았던 SQL중에 악성 SQL들을 오라클이 선별해서 자동으로 SQL튜닝
어드바이져를 돌려서 SQL 프로파일을 생성합니다. 
SQL 프로파일을 자동으로 적용합니다. 

select  client_name, status
  from  dba_autotask_client;

위의 기능중에 sql튜닝 어드바이져가 자동으로 수행되는 기능을 끄시오 !

begin
   dbms_auto_task_admin.disable(  client_name=>'sql tuning advisor',
                                            operation => null,
                                            window_name=> null);
end;
/

select  client_name, status
  from  dba_autotask_client;

문제1. 위의 밤10시에 자동으로 도는 3가지 기능중 통계정보 자동 수집기능을 
         끄시오 !

begin
   dbms_auto_task_admin.disable(  client_name=>'auto optimizer stats collection',
                                            operation => null,
                                            window_name=> null);
end;
/

select  client_name, status
  from  dba_autotask_client;