[ORACLEJAVA커뮤니티자바오라클교육강좌오라클자바교육강좌,자바교육오라클교육]Automatic SQL Tuning(SQL Tuning Set)

[ORACLEJAVA커뮤니티자바오라클교육강좌오라클자바교육강좌,자바교육오라클교육]

오라클자바커뮤니티에서 설립한  개발자실무교육6년차 오엔제이프로그래밍 실무교육센터

(신입사원채용무료교육, 오라클, SQL, 튜닝, 자바, 스프링, Ajax, jQuery, 안드로이드, 아이폰, 닷넷, C#, ASP.Net)   www.onjprogramming.co.kr 

-------------------------
SQL Tuning Sets
-------------------------

SQL Tuning Set이란 실행 문맥을 따라 만들어진 명령문들의 그룹 입니다. 자동으로 만들어지며 이것에 접근 하기 위해서는 몇 가지 권한이 필요 합니다.

SQL>CONN sys/password AS SYSDBA
SQL>GRANT ADMINISTER ANY SQL TUNING SET TO scott;
SQL>CONN scott/tiger

CREATE_SQLSET 이라는 프로시저를 통해 만들어 집니다.

BEGIN
  DBMS_SQLTUNE.create_sqlset (
    sqlset_name  => 'test_sql_tuning_set',
    description  => 'A test SQL tuning set.');
END;
/

Statement들은 다음에 나오는 몇 가지 함수에 위해(이 함수들은 statement를 어디에서 찾는지에 대해 기술 합니다.) statement의 REF CURSOR를 인자로 받아들이는 LOAD_SQLSET 이라는 프러시저에 의해 SQL Tuning  Set에 추가 됩니다.

•        SELECT_WORKLOAD_REPOSITORY - Retrieves statements from the Automatic Workload Repository (AWR).
•        SELECT_CURSOR_CACHE - Retrieves statements from the cursor cache.
•        SELECT_SQLSET - Retrieves statements from another SQL tuning set.

[예]
-- Load the SQL set from the Automatic Workload Repository (AWR).
DECLARE
  l_cursor  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPEN l_cursor FOR
    SELECT VALUE(p)
    FROM  TABLE (DBMS_SQLTUNE.select_workload_repository (
                    765,  -- begin_snap
                    766,  -- end_snap
                    NULL, -- basic_filter
                    NULL, -- object_filter
                    NULL, -- ranking_measure1
                    NULL, -- ranking_measure2
                    NULL, -- ranking_measure3
                    NULL, -- result_percentage
                    10)  -- result_limit
                  ) p;

  DBMS_SQLTUNE.load_sqlset (
    sqlset_name    => 'test_sql_tuning_set',
    populate_cursor => l_cursor);
END;
/

-- Load the SQL set from the cursor cache.
DECLARE
  l_cursor  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPEN l_cursor FOR
    SELECT VALUE(p)
    FROM  TABLE (DBMS_SQLTUNE.select_cursor_cache (
                    NULL, -- basic_filter
                    NULL, -- object_filter
                    NULL, -- ranking_measure1
                    NULL, -- ranking_measure2
                    NULL, -- ranking_measure3
                    NULL, -- result_percentage
                    1)    -- result_limit
                  ) p;

  DBMS_SQLTUNE.load_sqlset (
    sqlset_name    => 'test_sql_tuning_set',
    populate_cursor => l_cursor);
END;
/

-- Create a new set and load it from the existing one.
DECLARE
  l_cursor  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  DBMS_SQLTUNE.create_sqlset(
    sqlset_name  => 'test_sql_tuning_set_2',
    description  => 'Another test SQL tuning set.');

  OPEN l_cursor FOR
    SELECT VALUE(p)
    FROM  TABLE (DBMS_SQLTUNE.select_sqlset (
                    'test_sql_tuning_set', -- sqlset_name
                    NULL,                  -- basic_filter
                    NULL,                  -- object_filter
                    NULL,                  -- ranking_measure1
                    NULL,                  -- ranking_measure2
                    NULL,                  -- ranking_measure3
                    NULL,                  -- result_percentage
                    NULL)                  -- result_limit
                  ) p;

  DBMS_SQLTUNE.load_sqlset (
    sqlset_name    => 'test_sql_tuning_set_2',
    populate_cursor => l_cursor);
END;
/

SQL Tuning Set의 내용은 SELECT_SQLSET 함수에 위해 확인 할 수 있습니다.

SELECT *
FROM  TABLE(DBMS_SQLTUNE.select_sqlset ('test_sql_tuning_set'));

아래처럼 Statement에 대해 ADD_SQLSET_REFERENCE 함수에 의해 reference를 얻은 후 REMOVE_SQLSET_REFERENCE procedure를 통해 삭제하는 것이 가능 합니다.

DECLARE
  l_ref_id  NUMBER;
BEGIN
  -- Add a reference to a set.
  l_ref_id := DBMS_SQLTUNE.add_sqlset_reference (
    sqlset_name => 'test_sql_tuning_set',
    reference  => 'Used for manual tuning by SQL*Plus.');

  -- Delete the reference.
  DBMS_SQLTUNE.remove_sqlset_reference (
    sqlset_name  => 'test_sql_tuning_set',
    reference_id => l_ref_id);
END;
/


UPDATE_SQLSET 프러시저는 Statement에 대한 속성 중 문자열(MODULE and ACTION) 과 숫자 (PRIORITY and PARSING_SCHEMA_ID)를 매개변수로 하여 변경 또는 삭제가 가능 합니다.

아래의 예문을 참고 하세요~

BEGIN
  DBMS_SQLTUNE.update_sqlset (
    sqlset_name    => 'test_sql_tuning_set',
    sql_id          => '19v5guvsgcd1v',
    attribute_name  => 'ACTION',
    attribute_value => 'INSERT');
END;
/



BEGIN
  -- Delete statements with less than 50 executions.
  DBMS_SQLTUNE.delete_sqlset (
    sqlset_name  => 'test_sql_tuning_set',
    basic_filter => 'executions < 50');

  -- Delete all statements.
  DBMS_SQLTUNE.delete_sqlset (
    sqlset_name  => 'test_sql_tuning_set');
END;
/


다음 예처럼 삭제가 가능 합니다.

BEGIN
  DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'test_sql_tuning_set');
  DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'test_sql_tuning_set_2');
END;
/

다음은 Automatic SQL Tuning과 관련된 정보를 볼 수 있는 View 입니다.

•DBA_ADVISOR_TASKS
•DBA_ADVISOR_FINDINGS
•DBA_ADVISOR_RECOMMENDATIONS
•DBA_ADVISOR_RATIONALE
•DBA_SQLTUNE_STATISTICS
•DBA_SQLTUNE_BINDS
•DBA_SQLTUNE_PLANS
•DBA_SQLSET
•DBA_SQLSET_BINDS
•DBA_SQLSET_STATEMENTS
•DBA_SQLSET_REFERENCES
•DBA_SQL_PROFILES
•V$SQL
•V$SQLAREA
•V$ACTIVE_SESSION_HISTORY

[출처] 오라클자바커뮤니티 - http://www.oraclejavanew.kr/bbs/board.php?bo_table=LecOracle&wr_id=203