l http://docs.oracle.com/cd/E14072_01/server.112/e10821/sql_tune.htm 를 번역한 문서이다.
17 Automatic SQL Tuning
이 챕터는 Oracle Database 의 자동 SQL tuning 에 대해 이야기한다. Automatic SQL tuning 은 복잡하고, 반복적이고, 시간소모적인 수동 process 를 자동화한다.
이 챕터는 다음의 섹션을 포함한다 :
· Overview of the Automatic Tuning Optimizer
· Managing the Automatic SQL Tuning Advisor
· Tuning Reactively with SQL Tuning Advisor
See Also:
Oracle Database 2 Day + Performance Tuning Guide Oracle Enterprise Manager 를 이용한 자동 SQL tuning features 정보를 포함한다.
17.1 Overview of the Automatic Tuning Optimizer
SQL statements 가 수행될 때, optimizer 는 실행플랜을 생성하는데 사용된다. optimizer 는 다음의 모드로 수행된다 :
· Normal mode
Optimizer 는 SQL 에 대해 동작하고 실행플랜을 생성한다. Normal 모드는 여러종류의 대다수 SQL 구문에 대해 납득할 만한 실행계획을 생성한다. Normal 모드에서는, Optimizer 는 정해진 타임 (몇 분의 1초와 같은) 과 같은 제약속에서 수행되며 이 제약속에서 가장 좋은 실행계획을 찾아야 한다.
· Tuning mode
Optimizer 는 normal mode 에서 만들어진 플랜이 더 개선될 수 있는가를 체크하기 위해서 추가적인 분석을 실시한다. Optimizer 결과는 실행플랜뿐 아니라 더 나은 플랜을 위한 기대되는 이익과 근본적인 이유가 있다. tuning mode 에서 수행을 하는 것은 Automatic Tuning Optimizer 를 의미한다.
tuning mode 에서는, Optimizer 는 single statement 마다 수분걸릴 수 있다. 쿼리마다 시간 뿐만 아니라 자원측면에서도 집중적으로 Automatic Tuning Optimizer 가 호출되는데 이는 반드시 hard-parsed 되어야 한다. Automatic Tuning Optimizer 는 복잡하고 로드가 높은 statement 들에 효과적이다.
Automatic Database Diagnostic Monitor (ADMM) SQL tuning 을 위해 사전에 후보군을 인지한다. (see Chapter 6,
"Automatic Performance Diagnostics")
Automatic SQL tuning 특징은 문제가 될 소지가 있는 SQL 에 대해 자동으로 감지하고 튜닝권고안을 시스템 유지보수 윈도우 시간에 자동화 된 유지보수잡으로 수행된다.
Automatic Tuning Optimizer 는 다음과 같은 tuning 분석을 실시한다.
17.1.1 Statistics Analysis
Optimizer 는 execution plans 을 생성하기 위해 object 통계치에 의존한다. 만약 이 정보가 오래되었거나 없다면, Optimizer 가 필요로 한 정보가 없기 때문에 양호하지 않은 실행플랜을 생성하게 된다. Automatic Optimizer 는 각 쿼리에 대해 통계정보가 오래되거나 없는 상황에 대해 체크하며, 두 종류의 결과를 생성한다 :
· 낡았거나 없는 통계정보가 없는 objects 에 대해 신뢰할 만한 통계수집을 위한 권고안
Optimizer 통계는 자동으로 수집되고 갱신되기에, 이 문제는 오로지 자동 optimizer 통계 수집 기능이 off 되어 있는 경우만 해당된다.
See "Managing
Automatic Optimizer Statistics Collection".
· 통계정보가 없거나 오래된 통계정보를 가진 오브젝트에 대한 통계조정계수에 대한 추가적인 정보
이러한 보조정보들은 SQL profile 에 저장된다.
17.1.2 SQL Profiling
SQL profile 은 특정 SQL 에 대한 보조적인 정보집합이다. 개념적으로, SQL profile 은 테이블과 인덱스에 대한 통계정보 SQL statement 이다. 데이터베이스는 이 보조적인 정보를 실행계획을 개선하기 위해 사용할 수 있다.
See Also:
17.1.3 Access Path Analysis
Access path 는 데이터베이스에서 어떤 데이터가 탐색되는가를 의미한다. 예를 들어, 인덱스를 사용하는 쿼리와 full table scan 을 사용하는 쿼리는 서로 다른 access paths 를 사용한다.
인덱스는 대형 테이블에 대한 full table scan 요구를 줄임으로써 성능상 거대한 이점을 가진다. 효과적인 인덱스 구성은 tuning 의 흔한 기법이다. Automatic Tuning Optimizer 는 새로운 인덱스가 성능상 큰 이득이 있는가를 판단한다. 만약 그런 인덱스가 인지되면 생성을 권고한다.
Automatic Tuning Optimizer 가 인덱스 권고안에 대해 시스템 전체의 SQL workload 에 대한 분석을 하지 않기에, SQL Access Advisor 유틸리티를 수행할 것을 권고한다. SQL Access Advisor 는 인덱스 생성이 시스템 전반적으로 미치는 영향 권고안을 수행하기 전에 볼 수 있다.
See "Automatic SQL
Tuning Features".
17.1.4 SQL Structure Analysis
Automatic Tuning Optimizer 는 성능이 나오지 않는 SQL statements 의 구조적인 문제를 인지한다. 이는 문장구성상의, 의미적의, 혹은 statement 의 디자인 문제일 수 있다. 각각의 케이스의, Automatic Tuning Optimizer 는 SQL Statements 의 재구성을 위한 적절한 제안을 만든다. 대체되는 구문은 비슷하나, 원본과 동일하지 않다.
예를들어, optimizer 는 union 연산자를 union all 로 바꿀것을 권하거나, not in 을 not exists 로 변경할 것을 권한다. 이 경우 당신은 상황에 맞는 적절한 권고안을 선택할 수 있다. 예를들어, 중복가능성이 없는 스키마디자인에서, union all 오퍼레이터는 union 오퍼레이터보다 훨씬 효율적이다. 이 변경은 데이터 속성에 대한 이해와 여러 정황에 대한 세심한 이해후에 수행될 수 있다.
17.1.5 Alternative Plan Analysis
SQL statement 를 튜닝하는 중에, SQL Tuning Advisor 는 real-time 과 historical performance data 를 대안 실행플랜을 위해 탐색한다. 만약 Original plan 이 아닌 플랜을 발견하면, SQL Tuning Advisor 는 대안 플랜을 발견했다고 보고한다.
SQL Tuning Advisor 는 대안 플랜의 유효성과 다른 플랜이 재현불가능함을 확인한다. 만약 재현가능한 대안플랜이 발견되면, 미래에 이 플랜을 선택가능하도록 SQL plan baseline 을 생성한다.
Example 17-1 select statement 에 대해 대한 플랜을 보여준다.
Example 17-1 Alternative Plan Finding
2- Alternative Plan Finding--------------------------- Some alternative execution plans for this statement were found by searching the system's real-time and historical performance data. The following table lists these plans ranked by their average elapsed time. See section "ALTERNATIVE PLANS SECTION" for detailed information on each plan. id plan hash last seen elapsed (s) origin note -- ---------- -------------------- ------------ --------------- ---------------- 1 1378942017 2009-02-05/23:12:08 0.000 Cursor Cache original plan 2 2842999589 2009-02-05/23:12:08 0.002 STS Information ----------- - The Original Plan appears to have the best performance, based on the elapsed time per execution. However, if you know that one alternative plan is better than the Original Plan, you can create a SQL plan baseline for it. This will instruct the Oracle optimizer to pick it over any other choices in the future. execute dbms_sqltune.create_sql_plan_baseline(task_name => 'TASK_XXXXX', object_id => 2, task_owner => 'SYS', plan_hash => xxxxxxxx);
Example 17-1 이는 SQL Tuning Advisor 가 두 개의 플랜을 발견했음을 보여준다, 하나는 cursor cache 다른하나는 SQL tuning set 에서 발견되었다. cursor cache 에서 발견된 플랜은 original plan 과 동일하다.
SQL Tuning Advisor 는 original plan 의 수행타임이 대안플랜의 수행타임보다 클 때만 대안플랜을 권고한다. 이 경우에, SQL Tuning Advisor 는 가장 좋은 퍼포먼스에 대해 SQL plan baseline 을 생성할 것을 권장한다. Example 17-1 에서, 대안플랜은 Original 만큼 좋지 않았다, 때문에 SQL Tuning Advisor 는 대안플랜 사용을 권고하지 않았다.
Example 17-2 에서, SQL Tuning Advisor 결과의 대안플랜섹션은 original 과 대안플랜 그리고 성능정보 요약을 포함한다. 가장 중요한 통계는 수행타임이다. original 플랜은 index 를 사용하나, 대안플랜은 full table scan 을 사용한다, 이로인해 증가된 수행타임은 .002 초이다.
Example 17-2 Alternative Plans Section
Plan 1------ Plan Origin :Cursor Cache Plan Hash Value :1378942017 Executions :50 Elapsed Time :0.000 sec CPU Time :0.000 sec Buffer Gets :0 Disk Reads :0 Disk Writes :0 Notes: 1. Statistics shown are averaged over multiple executions. 2. The plan matches the original plan. --------------------------------------------| Id | Operation | Name |--------------------------------------------| 0 | SELECT STATEMENT | || 1 | SORT AGGREGATE | || 2 | MERGE JOIN | || 3 | INDEX FULL SCAN | TEST1_INDEX || 4 | SORT JOIN | || 5 | TABLE ACCESS FULL| TEST |-------------------------------------------- Plan 2------ Plan Origin :STS Plan Hash Value :2842999589 Executions :10 Elapsed Time :0.002 sec CPU Time :0.002 sec Buffer Gets :3 Disk Reads :0 Disk Writes :0 Notes: 1. Statistics shown are averaged over multiple executions. -------------------------------------| Id | Operation | Name | -------------------------------------| 0 | SELECT STATEMENT | || 1 | SORT AGGREGATE | || 2 | HASH JOIN | || 3 | TABLE ACCESS FULL| TEST || 4 | TABLE ACCESS FULL| TEST1 |-------------------------------------
대안플랜을 SQL Tuning
Advisor 의 권고와 관계없이 선택하려면, DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE 를 호출한다. 당신은 이 프로시저를 존재하는 재현가능한 실현플랜에 대해 SQL plan baseline 생성에 사용할 수 있다.
17.2 Managing the Automatic SQL Tuning Advisor
SQL Tuning Advisor 는 statements 에 대해 SQL tuning 을 실시하기 위해 Automatic Tuning Optimizer 를 호출하는데 하나나 그 이상의 SQL statements 를 입력값으로 사용할 수 있다. 결과는 조언이나 권고의 형태를 띄며, 각각의 권고안에는 기대되는 이득에 대해 이론적 해석이 달려있다. 권고안은 objects 에 대한 통계수집, 새로운 인덱스 생성, SQL 재작성, 혹은 SQL profile 의 생성과 관련되어 있다. 이 권고안 중 선택하여 SQL statement 에 대한 tuning 을 완료한다.
데이터베이스는 시스템 유지 윈도우 기간을 통해 SQL Tuning Advisor 를 사용해 문제가 될만한 SQL 을 인식하고 권고안을 수행하면서 SQL statements 를 자동으로 튜닝할 수 있다.
자동으로 수행하는 경우, SQL Tuning Advisor 는 Automatic SQL
Tuning Advisor 로 표현된다.
이 섹션은 Automatic SQL Tuning Advisor 을 어떻게 관리하냐에 대한 내용이다 :
· How Automatic SQL Tuning Works
· Enabling and Disabling Automatic SQL Tuning
· Configuring Automatic SQL Tuning
· Viewing Automatic SQL Tuning Reports
See Also:
Oracle Database Administrator's Guide 자동화된 유지보수 태스크에 대한 정보이다.
17.2.1 How Automatic SQL Tuning Works
Oracle Database 는 AWR 에서 튜닝 대상으로 high-load SQL statements 선택하여 자동으로 SQL Tuning 을 수행한다. 이 과정은 Automatic SQL Tuning 이라 불리우며, 야간 유지보수 윈도우에서 기본 수행된다. 기본적으로, SQL Tuning 은 최대 한시간 수행된다. 시작시각, 종료시각, 수행빈도, 수행요일등의 속성을 변경할 수 있다.
Automatic SQL tuning 이 시작되면, database 는 다음의 과정을 밟는다:
1. Identify SQL candidates in the AWR for tuning.
Oracle Database 는 AWR 의 통계를 분석하고 튜닝가능한 SQL statement 리스트를 뽑아낸다. 이 구문들은 반복되는 high-load 스테이트먼트를 포함한다.
큰 개선효과가 기대되는 execution plan 의 SQL statements 에 대해서만 튜닝된다. 최근에 튜닝된 (지난 달) Recursive SQL 과 statements 는 무시되며, parallel queries, DML, DDL, 그리고 동시성 이슈로 문제를 일으킨 SQL statement 도 마찬가지로 무시된다.
후보군으로 선택된 SQL statement 는 성능이슈 순서대로 정렬된다. database 는 지난주 캡쳐된 AWR 을 가지고 CPU time 과 I/O times 을 합쳐 성능영향도를 계산한다.
2. Tune each SQL statement individually by calling SQL Tuning Advisor.
튜닝프로세스중에, 모든 권장안이 고려되고 보고되지만 SQL profiles 은 자동으로 수행될 수 있다.
3. Test SQL profiles by executing the SQL statement.
SQL Profile 이 권장되는경우, 데이터베이스는 SQL statement 를 실행플랜 있는경우 없는경우 모두 수행하여 테스트합니다. 만약 성능 향상이 3배 이상이라면, database 는 SQL profile 을 수용합니다. 다만 ACCEPT_SQL_PROFILES 파라미터가 TRUE 로 되어 있는 경우입니다. 반대의 경우는 automatic SQL tuning 은 SQL profile 의 생성을 추천하기만 합니다.
4. 선택적으로, 제공된 SQL profiles 을 실행하는건 세배 이상의 성능향상이 있을 때이다.
database 는 SQL profile 을 수행할지 말지 결정하기 위해 다른 factors 를 고려한다. 예를 들어, SQL profile 은 SQL statement 가 참조하는 object 정보가 낡은 통계정보를 가지고 있는 경우 SQL profile 을 수행하지 않는다. 당신은 어떤 SQL profiles 이 자동으로 수행될 지 인식할 수 있는데 그 이유는 기본적으로 type 이 AUTO 로 셋팅되어 있기 때문이다. (DBA_SQL_PROFILES view 에서 확인가능)
SQL plan management 가 사용되고, SQL plan baseline 이 SQL statement 에 대해 존재하면, database 는 새로운 plan baseline 을 SQL profile 생성시 추가한다. 결과적으로, optimizer 는 새롭게 개선된 SQL execution plan 을 SQL profile 이 생성되는 즉시 사용하게 된다. See Chapter 15, "Using SQL Plan Management".
Automatic SQL tuning process 중이나 완료후나, automatic SQL tuning report 를 사용하여 결과를 볼 수 있다. 이는 분석된 모든 SQL statement 에 대해 생성된 권고안이나, 자동으로 수행된 SQL profiles에 대한 상세정보를 볼 수 있다.
Figure 17-1 는도식화 된 automatic SQL tuning process 단계이다.
Figure 17-1 Automatic SQL Tuning
Description of
"Figure 17-1 Automatic SQL Tuning"
17.2.2 Enabling and Disabling Automatic SQL Tuning
Automatic SQL tuning 은 자동화된 유지보수의 파트로서 수행된다.
automatic SQL tuning 을 활성화하기 위해선, DBMS_AUTO_TASK_ADMIN 패키지의 ENABLE 프로시저를 사용한다.
BEGIN DBMS_AUTO_TASK_ADMIN.ENABLE( client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);END;/
비활성화 하기 위해서는, DBMS_AUTO_TASK_ADMIN 패키지의 DISABLE Procedure 를 사용한다.
BEGIN DBMS_AUTO_TASK_ADMIN.DISABLE( client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);END;/
You can pass a specific window name using the window_name parameter to
enable or disable the task in certain maintenance windows only.
window_name 파라미터를 사용하여 특정 유지보수 윈도우의 tasks 를 활성화 또는 비활성화 할 수 있다.
STATISTICS_LEVEL 파라미터를 BASIC 으로 셋팅하면 AWR 에 의해 자동으로 수집되는 통계정보가 비활성화 된다. 결과적으로 automatic SQL Tuning 도 비활성화 된다.
See Also:
· Oracle Database Administrator's Guide for information about the AutoTask infrastructure
·
Oracle
Database PL/SQL Packages and Types Reference for information about the DBMS_AUTO_TASK_ADMIN package
17.2.3 Configuring Automatic SQL Tuning
DBMS_SQLTUNE 패키지를 이용해 automatic SQL tuning task 의 속성을 구성할 수 있다. APIs 를 사용하기 위해서는, 최소한 ADVISOR 권한을 가지고 있어야 한다.
SQL Tuning Advisor 의 표준 동작을 구성하는 것 외에, SET_TUNING_TASK_PARAMETER 프로시저를 이용하여 특정 task parameters 를 지정할 수 있다. 이와 같이 하는 이유는 automatic tuning task 는 sys가 소유하며 sys만이 파라미터 할당이 가능하기 때문이다.
Table 17-2 lists the parameters that are specific to automatic SQL tuning which can be configured.
Table 17-1 SET_TUNING_TASK_PARAMETER Automatic SQL Tuning Parameters
|
Parameter |
Description |
|
|
SQL profiles 을 자동으로 채택할지 말지를 지정한다. |
|
|
Advisor framework 스키마에 task history 의 보관일을 설정한다. 기본적으로 만기되기전 30일 동안 보관된다. |
|
|
채택 된 자동화 SQL tuning task 에 대해 제한을 설정한다. 매일 시스템에 만들어질 수 있는 허용가능한 변경 수준을 SQL profiles 에 대해 셋팅한다. |
|
|
전체적으로 채택될 SQL profiles의 limit 를 셋팅한다. |
|
|
SQL Performance Analyzer 의 성능비교분석 작업에 실행이름을 지정합니다. NULL 인 경우 SQL Perfomance Analyzer 는 가장 최근 수행에 할당했던 이름을 사용합니다. |
|
|
SQL Performance Analyzer 의 이름을 지정한다. |
|
|
SQL Performance Analyzer task 의 owner 를 지정한다. |
Automatic SQL tuning 을 구성하기 위해서는, DBMS_SQLTUNE 패키지의 SET_TUNING_TASK_PARAMETER procedure 를 사용한다 :
BEGIN DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER( task_name => 'SYS_AUTO_SQL_TUNING_TASK', parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');END;/
이 예제는, automatic SQL tuning task 는 SQL Tuning Advisor 가 권고하는 SQL profiles 을 자동으로 수용한다.
See Also:
· "Configuring a SQL Tuning Task" for information about other parameters that can be configured for a SQL tuning task
·
Oracle
Database PL/SQL Packages and Types Reference for information about the DBMS_SQLTUNE package
17.2.4 Viewing Automatic SQL Tuning Reports
DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK function 을 이용해 SQL tuning report 는 생성된다. 이는 Automatic SQL tuning task 의 모든 실행정보를 포함한다.
이 report 를 생성하기 위해, ADVISOR 권한과 DBA_ADVISOR views 의 실행권한이 필요하다. 표준 SQL tuning report 와 달리 DBMS_SQLTUNE.REPORT_TUNING_TASK function 을 이용해 생성된 보고서는 SQL Tuning Advisor 의 single task execution 의 정보만 가지고 있으며, Automatic SQL tuning report 는 복수번의 automatic SQL Tuning task 에 대한 정보를 가지고 있다.
automatic SQL tuning report 를 보기위해서는, DBMS_SQLTUNE 의 REPORT_AUTO_TUNING_TASK function 을 사용한다 :
variable my_rept CLOB;BEGIN :my_rept :=DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK( begin_exec => NULL, end_exec => NULL, type => 'TEXT', level => 'TYPICAL', section => 'ALL', object_id => NULL, result_limit => NULL);END;/ print :my_rept
이 예제는, 가장 최근의 실행되었던 모든 SQL statements 에 대해 실제 수행되지 않았던 권고안을 포함해 report 가 생성된다.
See Also:
· Oracle Database 2 Day + Performance Tuning Guide to learn how to view automatic SQL tuning reports using Enterprise Manager
·
Oracle
Database PL/SQL Packages and Types Reference for information about the DBMS_SQLTUNE package
보고서에 포함된 Section 에 따라, report 의 다음 섹션에 대해 Automatic SQL tuning task 를 볼 수 있다 :
· General information
기본 정보 섹션은 유지보수기간중에 튜닝 된 SQL statement 와 생성된 profiles 의 숫자 등 높은 디테일의 정보를 제공한다.
· Summary
요약 섹션은 유지보수기간에 튜닝 된 SQL statements 와 각 SQL profile의 산정된 이득, 혹은 SQL profile 로 실제 수행된 실행통계정보를 나열한다. (SQL identifiers 를 이용)
· Tuning findings
이 섹션은 SQL Tuning Advisor 에 의해 분석된 각 SQL 구문에 다음과 같은 정보를 포함한다 :
o 각 SQL statements 와 관련된 모든 조사결과
o Profiles 이 수용되었는가 여부와 이유
o SQL profile 이 현재 시스템상에서 활성화 되어 있는가
o SQL profile 테스팅 기간 중 캡쳐된 상세 수행정보
· Explain plans
SQL Tuning Advisor 에 의해 분석된 각 SQL statement 의 구,신 실행플랜 정보를 보여준다.
· Errors
Automatic SQL tuning task 가 발견한 모든 에러를 리스팅한다.
17.3 Tuning Reactively with SQL Tuning Advisor
SQL Tuning Advisor 는 on-demand 튜닝에 의해 수동으로 호출될 수 있다. 복수의 statements 에 대해 튜닝하기 위해, SQL tuning set (STS) 생성이 필요하다. SQL tuning set 은 실행전후를 따라 SQL statements 를 저장하는 database object 이다.
17.3.1 Input Sources
SQL Tuning Advisor 는 몇 개의 소스로부터 입력을 받는다. 이 input sources 를 포함한다 :
· Automatic Database Diagnostic Monitor
기본적인 입력은 Automatic Database Diagnostic Monitor (ADDM) 이다. 기본적으로 ADDM 은 매시간 사전 수행되며 high-load SQL STATEMENTS 를 포함한 문제 분석을 위해 지난 한시간의 AWR 주요 통계정보를 분석한다. high-load SQL 이 인지되면, ADDM 은 해당 SQL 에 대해 SQL Tuning Advisor 를 수행할 것을 권고한다. See "Overview of the Automatic Database Diagnostic Monitor".
· Automatic Workload Repository
두번째로 중요한 입력은 AWR 이다. AWR 은 시스템 활동에 대해 high-load SQL statements 를 포함한 snapshots 을 찍는다.
AWR 을 보고 수동으로 high-load SQL 을 찾을 수 있다. 또 그 쿼리에 대해 SQL Tuning Advisor 수행이 가능하다, 그렇지만 이 동작들이 SQL Tuning process 로 자동으로 수행된다. 기본적으로, AWR 은 지난 8일에 대해 데이터를 보관한다. 보관주기안에 수행 된 high-load SQL 은 튜닝된다. See "Overview of the Automatic Workload Repository".
· Cursor cache
세번째는 cursor cache 입력이다. 이는 AWR 에서 아직 캡쳐되지 않은 최근의 SQL statements 에 쓰인다. Cursor cache 와 AWR 로 현재시각으로부터 기본 8일전까지 높은 부하의 SQL 문을 식별할 수 있다.
· SQL tuning set
다른 입력 가능한 소스로 STS 가 있다. STS 는 아직 배포되지 않은 SQL statement 를 포함할 수 있다. STS 가 입력으로 사용되면, STS 가 먼저 만들어져야 하며 저장되어야 합니다. See "Managing SQL Tuning Sets".
17.3.2 Tuning Options
SQL Tuning Advisor 는 tuning task 의 범위와 기간을 관리하기 위해 옵션을 제공합니다. 범위로는 limited 나 comprehensive 를 사용할 수 있습니다.
· Limited option 이 선택 된 경우, SQL Tuning Advisor 는 통계체크, 접근경로 분석, SQL 구조 분석을 기준으로 합니다. SQL profile 권고는 생성되지 않습니다.
· Comprehensive option 이 선택 된 경우, SQL Tuning Advisor 는 limited option 의 모든 분석 결과와 SQL Profiling 결과를 산출합니다. comprehensive option 에서는 tuning task 에 대해 time limit 를 설정할 수 있습니다. 이 값은 기본적으로 30 분 입니다.
17.3.3 Advisor Output
SQL statements 분석후에는, SQL Tuning Advisor 는 최적화 된 실행플랜, 제안된 최적화, 성능상 이득 산정과 권고를 수행하는 명령 등을 제공한다. 이용자는 단순히 어떤 권고안 중에 선택하기만 하면 된다.
17.3.4 Running the SQL Tuning Advisor
SQL Tuning Advisor 를 수행하는 추천 인터페이스는 Enterprise Manger 이다. 언제든 가능하다면, Enterprise manger 를 사용하여 SQL Tuning Advisor 를 수행해야 한다. (Oracle Database 2 Day + Performance Tuning Guide.) Enterprise Manger 사용이 불가능한 경우, DBMS_SQLTUNE 패키지를 이용하여 수행이 가능하다. APIs 를 사용하기 위해서는 사용 user 는 알맞은 권한을 소유하고 있어야 한다.
See Also:
Oracle
Database PL/SQL Packages and Types Reference for information on the security model for the DBMS_SQLTUNE package
DBMS_SQLTUNE 패키지를 사용한 SQL Tuning Advisor 수행
1. Create a SQL tuning set (if tuning multiple SQL statements)
2. Create a SQL tuning task
3. Execute a SQL tuning task
4. Display the results of a SQL tuning task
5. Implement recommendations as appropriate
SQL tuning task 는 단일 SQL statement 에 대해 생성될 수 있다. 복수의 statements 에 대해 튜닝하기 위해선, 먼저 STS 가 생성되어야 한다. STS 는 APIs 를 이용해 수동으로 생성하거나 Enterprise Manager 를 이용해 자동으로 생성할 수 있다. See "Managing SQL Tuning Sets".
Figure 17-2 package.DBMS_SQLTUNE 패키지를 이용한 SQL Tuning Advisor 수행의 스텝을 보여준다.
Figure 17-2 SQL Tuning Advisor APIs
Description of
"Figure 17-2 SQL Tuning Advisor APIs"
This section covers the following topics:
· Configuring a SQL Tuning Task
· Checking the Status of a SQL Tuning Task
· Checking the Progress of the SQL Tuning Advisor
· Displaying the Results of a SQL Tuning Task
· Additional Operations on a SQL Tuning Task
See Also:
· Oracle Database 2 Day + Performance Tuning Guide to learn how to run SQL Tuning Advisor manually using Enterprise Manager
·
Oracle
Database PL/SQL Packages and Types Reference for information about the DBMS_SQLTUNE package
17.3.4.1 Creating a SQL Tuning Task
Single SQL statement, STS, cursor cache 에서 SQL ID 로 선택된 statement, 혹은 AWR 에서 선택된 SQL ID 로 선택된 SQL ID 를 통해서도 tuning tasks 를 생성할수 있다.
예를 들어 지정된 SQL statement text 를 통해서도 최적화가 가능하며, SQL statement 는 CLOB 인자로 받아들인다. 다음의 PL/SQL code, User HR 은 ADVISOR 권한을 받았으며 function 은 User HR 로 HR schema 의 employees 에 수행된다.
DECLARE my_task_name VARCHAR2(30); my_sqltext CLOB;BEGIN my_sqltext := 'SELECT /*+ ORDERED */ * ' || 'FROM employees e, locations l, departments d ' || 'WHERE e.department_id = d.department_id AND ' || 'l.location_id = d.location_id AND ' || 'e.employee_id < :bnd'; my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => my_sqltext, bind_list => sql_binds(anydata.ConvertNumber(100)), user_name => 'HR', scope => 'COMPREHENSIVE', time_limit => 60, task_name => 'my_sql_tuning_task', description => 'Task to tune a query on a specified employee');END;/
이 예제에서는 :bnd 의 바인드 변수 100 은 SQL_BINDS 의 function 인자의 형태로 전달되었다. SCOPE 는 COMPREHENSIVE 로 셋팅되었는데 advisor 는 SQL Profiling 분석과, 60은 초당 수행될 수 있는 최대값이다. 추가적으로 task name 과 상세정보가 제공되었다.
CREATE_TUNING_TASK function 은 제공받은 값이나 새로운 Unique task name 을 생성하여 반환한다. 다른 APIs 를 사용하는 경우 task name 을 사용할 수 있다. 특정 owner 와 관련된 task names 을 보려면, 다음과 같이 수행한다 :
SELECT task_name FROM DBA_ADVISOR_LOG WHERE owner = 'HR';
17.3.4.2 Configuring a SQL Tuning Task
DBMS_SQLTUNE 패키지의 SET_TUNING_TASK_PARAMETER procedure 를 이용해 파라미터 구성이 끝나면 좋은 수준의 SQL tuning task 튜닝이 가능하다.
BEGIN DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER( task_name => 'my_sql_tuning_task', parameter => 'TIME_LIMIT', value => 300);END;/
이 예제에서, SQL tuning task 에서 수행 될 수 있는 최대 시간은 300초로 수정되었다.
Table 17-2 lists the parameters that can be configured using the SET_TUNING_TASK_PARAMETER procedure.
Table 17-2 SET_TUNING_TASK_PARAMETER Procedure Parameters
|
Parameter |
Description |
|
|
Tuning task 의 범주를 결정한다.: · LIMITED 각 SQL statement 에 1초 단위로 지정한다. SQL profile 은 생성하지 않는다. · COMPREHENSIVE 분석을 완료하며 적합한 경우 SQL profile 을 권고한다. 하지만 시간이 좀 더 걸린다. |
|
|
SQL 문을 파싱할 사용자를 지정한다. |
|
|
task 가 삭제될 days 를 지정 |
|
|
Default execution type |
|
|
Time limit (in number of seconds) task 가 타임아웃나기까지의 시간 |
|
|
Time limit (in number of seconds) 각 SQL statements 에 대한 타임아웃 시간 |
|
|
권고안의 득실을 산정하는 방법을 결정한다. · FULL - local time limit 를 전부 사용하여 시험수행한다. · AUTO - automatic time limit 를 사용하여 시험수행한다. · OFF - SQL statements 수행하지 않는다. |
|
|
SQL tuning set 이 사용하는 기본필터 |
|
|
SQL tuning set 이 사용하는 Object filter |
|
|
SQL tuning set 이 사용하는 plan filter |
|
|
SQL tuning set 이 사용하는 First ranking |
|
|
SQL tuning set 이 사용하는 Second ranking |
|
|
SQL tuning set 이 사용하는 Third ranking |
|
|
SQL tuning set 이
별도로 사용하는 ranking (besides |
|
|
튜닝 될 SQL statements 의 최대 수 |
|
|
STS 의 Percentage filter |
17.3.4.3 Executing a SQL Tuning Task
Tuning task 를 생성 후, task 를 실행하고 tuning process 를 시작해야 한다.
예제 :
BEGIN DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );END;/
다른 SQL Tuning Advisor task 와 같이, API EXECUTE_TUNING_TASK 를 사용해 automatic tuning task SYS_AUTO_SQL_TUNING_TASK 를 수행할 수 있다.
17.3.4.4 Checking the Status of a SQL Tuning Task
USER_ADVISOR_TASKS view 혹은 v$session_longops view의 확인을 통해 tasks 의 상태를 확인할 수 있다. 예를 들어 :
SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'my_sql_tuning_task';
17.3.4.5 Checking the Progress of the SQL Tuning Advisor
v$advisor_progress view 를 통해 SQL Tuning Advisor 의 진행추이를 확인할 수 있다.
예제 :
SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS WHERE user_name = 'HR' AND task_name = 'my_sql_tuning_task';
See Also:
Oracle Database Reference for information on the V$ADVISOR_PROGRESS view
17.3.4.6 Displaying the Results of a SQL Tuning Task
task 가 수행된 이후, REPORT_TUNING_TASK function 을 통해 결과를 출력할 수 있다.
예제 :
SET LONG 1000SET LONGCHUNKSIZE 1000SET LINESIZE 100SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task') FROM DUAL;
Report 는 SQL Tuning Advisor 의 권고안과 분석결과를 포함한다.
tuning tasks와 결과에 대한 추가적인 정보는 DBA views 에서 볼 수 있다. See "SQL Tuning Views".
17.3.4.7 Additional Operations on a SQL Tuning Task
다음의 APIs 로 SQL tuning tasks 를 관리할 수 있다 :
·
INTERRUPT_TUNING_TASK 수행중인 task 를 중단하고, 중간결과와 함께 일반종료한다.
·
RESUME_TUNING_TASK 이전에 중단된 task 를 재개한다.
·
CANCEL_TUNING_TASK 수행중인 task 를 취소하고, task 의 모든 결과를 삭제한다.
·
RESET_TUNING_TASK 수행중인 task 를 취소하고, task 의 모든 결과를 삭제하며 초기상태로 되돌린다.
·
DROP_TUNING_TASK task 를 삭제하고, 관련된 모든 결과를 삭제한다.
17.4 Managing SQL Tuning Sets
A SQL tuning set (STS) 은 하나나 그 이상의 수행통계 정보와SQL statements 를 포함하고 있는 database object 이다. 이 정보는 서로다른 소스로부터 로드될 수 있다, AWR 이나, cursor cache, 혹은 유저에 의해 제공되는 SQL 등이 있다. STS 는 다음을 포함한다 :
· SQL statement 집합
· 스키마, application module name 그리고 bind values 의 action list, cursor 편집환경과 같은 실행컨텍스트 환경
· Elapsed time, CPU time, buffer gets, disk reads, 수행된 row, cursor fetches, 실행횟수, 수행이 완료된 수, optimizer cost, command type 과 같은 기본 실행 통계정보
· 각 SQL statement 에 대해 관련된 수행플랜과 행단위의 통계정보 (선택적)
또한 application module name 과 action 혹은 실행 통계정보를 이용해 SQL statement 를 추출할 수 있다. 추가적으로 실행통계 정보의 조합을 기준으로 순위 산정이 가능하다.
STS 를 SQL Tuning Advisor 의 입력으로 사용할 수 있다. SQL tuning sets 은 database 를 넘어 타 시스템으로 전송가능하다. Production system 에서 성능이 안 나오는 SQL 에 대해, 이의 개선을 위한 활동이 Production system 에서 불가능 할 수 있다. 이 특징은 DBA 에게 문제가 되는 SQL 을 안전하게 분석하고 튜닝할 수 있는 테스트 장비로 옮길 수 있게 한다. tuning sets 을 전송하기 위해서는, DBMS_SQLTUNE 패키지 프로시저를 사용한다.
SQL tuning sets 을 관리하는 권장 인터페이스는 Enterprise Manager 이다. Enterprise Manger 가 사용 불가능한 경우에는 DBMS_SQLTUNE package procedures 를 통해 관리할 수 있다.
Typically, you use STS operations in the following sequence:
1. Create a new STS
This task is described in "Creating a SQL Tuning Set".
2. Load the STS
This task is described in "Loading a SQL Tuning Set".
3. Select the STS to review the contents
This task is described in "Displaying the Contents of a SQL Tuning Set".
4. Update the STS if necessary
This task is described in "Modifying a SQL Tuning Set".
5. Create a tuning task with the STS as input
6. Transport the STS to another system, if necessary
This task is described in "Transporting a SQL Tuning Set".
7. Drop the STS when finished
This task is described in "Dropping a SQL Tuning Set".
APIs 를 사용해 소유하고 있는 SQL Tuning set 을 관리하기 위해서는 ADMINISTER SQL TUNING SET 시스템 권한이나 ADMINISTER ANY SQL TUNING SET 시스템 권한이 필요하다.
Figure 17-3 SQL tuning sets APIs 를 사용하는 절차를 보여준다.
Figure 17-3 SQL Tuning Sets APIs
Description of
"Figure 17-3 SQL Tuning Sets APIs"
This section covers the following topics:
· Displaying the Contents of a SQL Tuning Set
· Transporting a SQL Tuning Set
· Additional Operations on SQL Tuning Sets
See Also:
· Oracle Database 2 Day + Performance Tuning Guide to learn how to manage SQL tuning sets using Enterprise Manager
·
Oracle
Database PL/SQL Packages and Types Reference for information about the DBMS_SQLTUNE package
17.4.1 Creating a SQL Tuning Set
CREATE_SQLSET 프로시저는 비어있는 STS 를 생성하는데 사용된다. 예를들어, 다음의 프로시저는 지정된 시간내에 I/O 부하가 큰 SQL statement 를 튜닝하기 위해 사용될 수 있다.
BEGIN DBMS_SQLTUNE.CREATE_SQLSET( sqlset_name => 'my_sql_tuning_set', description => 'I/O intensive workload');END;/
앞서의 샘플에서, my_sql_tuning_set 은 데이터베이스의 STS 이름이며 ‘I/O intensive workload’ 는 STS 에 할당된 설명이다.
17.4.2 Loading a SQL Tuning Set
LOAD_SQLSET 프로시저는 선택된 SQL statement 로 STS 에 추가한다. STS 의 기본소스를 workload repository 이며, 다른 STS, cursor cache 등이 있다. workload repository 와 STS 는, source 로부터 새로운 STS 에 내용을 추가하기 위해 사전 정의한 함수를 사용할 수 있다.
다음의 예제는, procedure calls 은 AWR peak baseline 이라 불리는 베이스라인으로부터 my_sql_tuning_set 을 로드하기 위해 사용된다. 데이터는 30 SQL statements 만 elapsed time (desc) 로 뽑아낸다. 먼저 ref cursor 를 특정 baseline 으로부터 select 하기 위해 open 한다. 다음으로 statement 와 그 통계값은 baseline 으로부터 STS 로 로드된다.
DECLARE baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;BEGIN OPEN baseline_cursor FOR SELECT VALUE(p) FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY( 'peak baseline', NULL, NULL, 'elapsed_time', NULL, NULL, NULL, 30)) p; DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name => 'my_sql_tuning_set', populate_cursor => baseline_cursor);END;/
17.4.3 Displaying the Contents of a SQL Tuning Set
SELECT_SQLSET table function 은 STS 의 contents 를 읽는다. STS 가 생성되고 로드된 이후엔, STS 내부의 sql 을 추출해 볼 수 있다. SELECT_SQLSET 프로시저가 이 기능을 위해 제공된다.
다음의 예제에서, STS 의 SQL statements 는 disk reads / buffer-gets * 100 이 75 % 보다 크거나 동일한 값에 대해 출력한다.
SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET( 'my_sql_tuning_set', '(disk_reads/buffer_gets) >= 0.75'));
만들어지고 로드된 SQL tuning sets 의 추가적인 정보는 DBA_SQLSET, DBA_SQLSET_STATEMENTS, DBA_SQLSET_BINDS 에서 조회가능하다.
17.4.4 Modifying a SQL Tuning Set
검색 조건을 기반으로 SQL statements 는 STS 로부터 업데이트나 삭제될 수 있다. 다음 예제에서, DELETE_SQLSET 프로시저는 50회보다 적게 수행 된 SQL statements 를 my_sql_tuning_set 부터 삭제한다.
BEGIN DBMS_SQLTUNE.DELETE_SQLSET( sqlset_name => 'my_sql_tuning_set', basic_filter => 'executions < 50');END;/
17.4.5 Transporting a SQL Tuning Set
SQL tuning sets 으로 전송가능하다. 이 연산은 한 database 에서 staging table 로 STS 를 추출하는 것과 관계있으며, staging table 에서 다른 database 로 importing 한다.
또 SQL tuning set 을 Oracle Database 10g(R2) 나 그 이후 버전의 database 로도 옮길 수 있다. 이 기술은 SQL Performance Analyzer 를 이용할 때 유용하다. 예를들어 다음의 시나리오가 있다 :
· 성능 감소가 일어난 production database 의 SQL (11G R2)
· 11.1 혹은 10g 버전의 Oracle Database 에서 만들어진 원격의 테스트 데이터베이스에 SQL Performance Analzyer 실험을 수행하고 있다.
· Production database 에서 test database 로 STS 를 복사하고 싶어하며 SQL Performance Analyzer 로 성능개선을 하고 싶다.
To transport a SQL tuning set:
1.
CREATE_STGTAB_SQLSET procedure 로 SQL tuning
set 이 추출될 곳에 staging table 을 생성한다.
다음 예제는 dba1 스키마에 my_10g_staging_table 을 생성하며 format 을 10.2 로 지정하였다 :
BEGIN DBMS_SQLTUNE.create_stgtab_sqlset( table_name => 'my_10g_staging_table', schema_name => 'dba1', db_version => DBMS_SQLTUNE.STS_STGTAB_10_2_VERSION );END;/
2. Staging table 로 STS 를 export 하기 위해 PACK_STGTAB_SQLSET 을 사용한다.
다음 예제는 hr 소유의 STS my_sts 를 사용해 dba1.my_10g_staging_table 에 채우는 예제이다.
BEGIN DBMS_SQLTUNE.pack_stgtab_sqlset( sqlset_name => 'my_sts', sqlset_owner => 'hr', staging_table_name => 'my_10g_staging_table', staging_table_owner => 'dba1', db_version => DBMS_SQLTUNE.STS_STGTAB_10_2_VERSION );END;/
3. SQL tuning sets 이 import 될 곳에 다음 mechanism 을 사용하여 (Oracle Data Pump 나 database link 와 같은) 옮긴다.
4. SQL tuning sets 이 import 된 database 에서, UNPACK_STAGTAB_SQLSET procedure 로 staging table 로부터 SQL tuning sets 을 import 한다.
다음 예제는 staging table 의 STS 를 어떻게 import 하는가에 대한 예제이다 :
BEGIN DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET( sqlset_name => '%', replace => TRUE, staging_table_name => 'my_10g_staging_table');END;/
17.4.6 Dropping a SQL Tuning Set
DROP_SQLSET 프로시저는 더 이상 사용되지 않는 STS 를 삭제한다. 예를들어 :
BEGIN DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => 'my_sql_tuning_set' );END;/
17.4.7 Additional Operations on SQL Tuning Sets
STS 를 관리하기 위해 다음의 APIs 를 사용할 수 있다 :
· STS 의 SQL statements 속성 변경
UPDATE_SQLSET procedure 는 STS name 과 SQL ID 로 식별되는 존재하는 STS 의SQL statements 속성을 변경한다. (PRIORITY 혹은 OTHER)
· Capturing the full system workload
CAPTURE_CURSOR_CACHE_SQLSET function 은 특정 인터벌로 cursor cache 를 반복적으로 조사하는 full system workload capture 를 활성화 한다. 이 function 은 SELECT_CURSOR_CACHE 와 LOAD_SQLSET 프로시저를 반복적으로 사용하는 것 보다 훨씬 유용하다. 이 function 은 전체로드를 효과적으로 캡쳐하며, high-load SQL statements 를 캡쳐하는 AWR 과는 대조적이다.
· Adding and removing a reference to an STS
ADD_SQLSET_REFERENCE function 은 존재하는 STS 에 새로운 참조를 추가한다. Function 의 실행결과로는 reference identifier 를 반환한다. REMOVE_SQLSET_REFERENCE 프로시저는 STS 가 더 이상 client 로부터 사용되지 않음을 알려 비활성화 한다.
17.5 Managing SQL Profiles
SQL profile 은 특정 SQL statement 에 대한 보조정보 집합이다.
이 섹션은 다음의 토픽을 포함한다.
See Also:
Oracle Database 2 Day + Performance Tuning Guide to learn how to manage SQL profiles using Enterprise Manager
17.5.1 Overview of SQL Profiles
SQL profile 은 Automatic SQL Tuning 과정중에 발견된 성능 불이익에 대한 정정을 포함한다. 이 정보는 Optimizer 가 더 좋은 plan 을 선택하게 함으로써 개선될 수 있다.
SQL profile 은 개개의 실행플랜에 대한 정보는 포함하지 않는다. 반면에, Optimizer 는 선택한 플랜에 대해 다음의 소스를 가진다.
· 데이터베이스 구성, bind 변수, Optimizer 통계, data set 등의 정보를 포함한 환경
· SQL profile 의 보조적인 통계정보
환경이나 SQL profile 이 변경되면, optimizer 는 새로운 플랜을 생성할 수 있다.
SQL profiles 을 SQL plan management 없이도 사용할 수 있다. SQL plan managements 를 사용하면, optimizer 가 선택한 plan 은 plan baseline 으로 선택되어야 한다. Baseline 에 대해 복수개의 plan 을 가지는 경우, 이들은 유용한 자원으로 남게 되는데 그 이유는 optimizer 가 가장 작은 비용으로 baseline 에서 선택할 수 있기 때문이다.
Figure 17-4 illustrates the relationship between a SQL statement and the SQL profile for this statement. The optimizer uses the profile and the environment to generate a query plan. In this example, the plan is in the SQL plan baseline for the statement.
SQL statement 와 SQL profile 같의 관계를 도식화했다. Optimizer 는 query plan 을 생성하기 위해 profile 과 environment 를 사용했다. 이 예제에선, 플랜은 SQL plan baseline 에 있다.
Description of
"Figure 17-4 SQL Profile"
SQL profiles 은 다음과 같은 이득을 준다 :
· 힌트나 stored outlines 과는 달리 profiles 은 optimizer 와 특정 플랜 혹은 서브플랜을 고정하지 않는다. 서로다른 상황에 대해 가장 좋은 실행계획을 유연하게 뽑기위해 Profiles 은 잘못된 비용을 수정한다.
· Profile 을 사용하는 경우 힌트와 달리 어플리케이션 코드에 아무런 변화가 필요없다. SQL profile 의 사용은 user 에 투명하다.
17.5.1.1 SQL Profile Recommendations
SQL 튜닝 중, SQL Tuning Advisor 가 튜닝하고 수행할 statement 를 선택한다. Database 는 다음 타입의 statement 에 대해 profile 할 수 있다 :
·
DML statements (SELECT, INSERT with a SELECT clause, UPDATE, and DELETE)
·
CREATE TABLE statements (only with the AS SELECT clause)
·
MERGE statements (the update or insert operations)
SQL Tuning Advisor invokes Automatic Tuning Optimizer to generate recommendations. Recommendations to accept SQL profiles occur in a finding.
SQL Tuning Advisor 는 권고안 생성을 위해 Automatic Tuning Optimizer 를 호출한다.
권고 수용할 SQL profiles 은 과정중에 발견한다.
Example 17-3 Database 가 비싼 조인을 사용하는 SELECT statement 에 대해 더 좋은 paln을 발견한 것을 보여준다. 권고안은 DBMS_SQLTUNE.ACCEPT_SQL_PROFILE 로 적용가능하다.
Example 17-3 Sample SQL Profile Finding
-------------------------------------------------------------------------------FINDINGS SECTION (2 findings)------------------------------------------------------------------------------- 1- SQL Profile Finding (see explain plans section below)-------------------------------------------------------- A potentially better execution plan was found for this statement. Choose one of the following SQL profiles to implement. Recommendation (estimated benefit: 99.45%) ------------------------------------------ - Consider accepting the recommended SQL profile. execute dbms_sqltune.accept_sql_profile(task_name => 'my_task', object_id => 3, task_owner => 'SH', replace => TRUE); Validation results ------------------ The SQL profile was tested by executing both its plan and the original plan and measuring their respective execution statistics. A plan may have been only partially executed if the other could be run to completion in less time. Original Plan With SQL Profile % Improved ------------- ---------------- ---------- Completion Status: PARTIAL COMPLETE Elapsed Time(us): 15467783 226902 98.53 % CPU Time(us): 15336668 226965 98.52 % User I/O Time(us): 0 0 Buffer Gets: 3375243 18227 99.45 % Disk Reads: 0 0 Direct Writes: 0 0 Rows Processed: 0 109 Fetches: 0 109 Executions: 0 1 Notes ----- 1. The SQL profile plan was first executed to warm the buffer cache. 2. Statistics for the SQL profile plan were averaged over next 3 executions.
드물게 SQL Tuning Advisor 는 Automatic Degree of Parallelism (Auto DOP) 사용을 권고한다. 이는 원본 플랜이 serial 하고 Auto DOP 의 사용이 long-running query 에 대해 수행타임 측면에서 큰 이득이 있을 때에만 권고된다. Report 에서는 Auto DOP 의 사용이 성능측면에서 어떤 부하를 주는지 상세정보를 준다. 또 SQL Tuning Advisor 는 serial 수행과 병렬 수행에 대해 profile 권고할 수도 있다.
Example 17-4 병렬 수행 권고안이다. 이 예제에서, parallelism 7 은 response time 을 크게 개선한다. 여기서 데이터베이스 성능을 중요시할지 응답시간을 중요시할지 반드시 결정해야 한다.
Example 17-4 Parallel Query Recommendation
Recommendation (estimated benefit: 99.99%) ------------------------------------------ - Consider accepting the recommended SQL profile to use parallel execution for this statement. execute dbms_sqltune.accept_sql_profile(task_name => 'gfk_task', object_id => 3, task_owner => 'SH', replace => TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE); Executing this query parallel with DOP 7 will improve its response time 82.22% over the SQL profile plan. However, there is some cost in enabling parallel execution. It will increase the statement's resource consumption by an estimated 24.43% which may result in a reduction of system throughput. Also, because these resources are consumed over a much smaller duration, the response time of concurrent statements might be negatively impacted if sufficient hardware capacity is not available. The following data shows some sampled statistics for this SQL from the past week and projected weekly values when parallel execution is enabled. Past week sampled statistics for this SQL ----------------------------------------- Number of executions 0 Percent of total activity .29 Percent of samples with #Active Sessions > 2*CPU 0 Weekly DB time (in sec) 76.51 Projected statistics with Parallel Execution -------------------------------------------- Weekly DB time (in sec) 95.21
17.5.1.2 SQL Profile Creation
Profile 을 수용하게 되면, 데이터베이스는 profile 을 생성해 data dictionary 에 지속적으로 저장한다.
SQL plan management 를 사용하고 SQL plan baseline 이 존재하면 SQL profile 이 만들어질 때 데이터베이스는 새로운 플랜을 baseline 에 추가한다. 반대로 SQL plan management 를 사용하지 않는 경우, 데이터베이스는 새로운 플랜 베이스라인을 추가하지 않는다.
SQL profile 과 plan baseline 간에는 엄격한 관계는 존재하지 않는다. 하드파싱을 할 때, optimizer 는 가용 가능한 플랜중 가장 좋은 best plan baseline 을 선택하기 위해 SQL profile 을 사용한다. 어떤 경우에는, SQL profile 은 optimizer 가 다른 plan baseline 선택을 유발하기도 한다.
See Also:
Chapter 15, "Using SQL Plan Management"
17.5.1.3 SQL Profile APIs
SQL profiles 은 통상적으로 Automatic SQL tuning 의 일부로 Enterprise Manager 로 관리된다. 또 DBMS_SQLTUNE 으로 관리가 가능한데 이 APIs 를 사용하기 위해, ADMINISTER SQL MANAGEMENT OBJECT 권한이 필요하다.
Table 17-3 SQL profiles 을 관리하기 위한 main procedure 와 functions 을 보여준다.
Table 17-3 DBMS_SQLTUNE APIs for SQL Profiles
|
Procedure or Function |
Description |
Section |
|
|
특정 tuning task 에 대해 SQL Profile 을 생성한다. |
|
|
|
존재하는 SQL Profile object 의 속성을 변경한다. |
|
|
|
지정된 이름의 SQL profile 을 삭제한다. |
|
|
|
한 시스템에서 다른 시스템으로 SQL profiles 을 복제하기 위한 staging table 을 생성한다. |
|
|
|
SYS 스키마에서 staging table 로 profile data 를 옮긴다. |
|
|
|
staging table 에 저장된 profile data 를 이용하여 시스템에 profiles 을 생성한다. |
Figure 17-5 shows the possible actions when using SQL profile APIs.
Description of
"Figure 17-5 SQL Profile APIs"
테이블이 커지거나 인덱스 생성/삭제 될 때, profile 의 plan 은 변경될 수 있다. Profile 은 데이터가 분산되어 있거나 대응 statements 가 변경되는 경우에도 관련을 지속한다. 보통은, SQL profile 을 refresh 할 필요없다.
긴 시간동안, profile content 는 구식이 된다. 이 경우, 상응하는 SQL statements 의 성능도 저하된다. 이러한 쿼리는 high-load 쿼리나 top SQL 로 나타날 수 있다. 이 경우, Automatic SQL Tuning task 는 high-load SQL 에 대해 다시 캡쳐한다. 또 새로운 profile 을 생성할 수 있다.
See Also:
Oracle
Database PL/SQL Packages and Types Reference for information about the DBMS_SQLTUNE package
17.5.2 Accepting a SQL Profile
SQL Tuning Advisor 가 권고하는 SQL profile 을 사용하기 위해 DBMS_SQLTUNE.ACCEPT_SQL_PROFILE 프로시저나 FUNCTION 을 사용할 수 있다. 이 프로시저는 Database 의 SQL profile 을 생성하고 저장한다.
가장 큰 룰로, SQL Tuning Advisor 가 권고하는 SQL profile 은 수용한다. index 와 SQL profile 이 함께 권고되면, 둘다 이용하거나 SQL profile 만 이용한다. 만약 인덱스를 생성하면, optimizer 는 새로운 인덱스를 위해 profile 이 필요하다.
어떤 경우에는, 병렬수행쿼리보다 serial 수행으로 더 큰 성능개선안을 발견하기도 한다. 이러한 경우 advisor 는 standard 와 parallel SQL profile 두 개의 경우에 대해 권고하며, 선택할 수 있게 한다.
Response time 이 증가가 처리량 감소 가치가 있는 경우에만 병렬계획을 수락한다.
(see Example 17-4).
To accept a SQL profile:
·
Call the DBMS_SQLTUNE.ALTER_SQL_PROFILE procedure.
In following example, my_sql_tuning_task is the name of the SQL tuning task and my_sql_profile is the name of the SQL profile. The PL/SQL block accepts a profile
that uses parallel execution (profile_type):
다음의 예제에서, my_sql_tuning_task 는 SQL tuning task 이름이고, my_sql_profile 은 SQL profile 이름이다. PL/SQL 블록은 병렬수행하는 profile 을 수용한다. (profile_type)
DECLARE my_sqlprofile_name VARCHAR2(30);BEGIN my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( task_name => 'my_sql_tuning_task', name => 'my_sql_profile', profile_type => DBMS_SQLTUNE.PX_PROFILE, force_match => TRUE );END;/
force_match 셋팅은 statement matching 을 컨트롤한다. 일반적으로, SQL profile 은 SQL statement 와 hash function 으로 생성되는 SQL signature 로 연관된다. 이 hash function 은 SQL statement 를 대문자로 변환하고 부가적인 여백공간을 signature 생성전에 제거한다. 때문에 대소문자와 공백 차이가 나는 모든 sql statement 에 대해 SQL profile 이 동작한다.
force_match 를 TRUE 로 셋팅하면, SQL profile 은 추가적으로 literal 값을 bind 값으로 정규화한다. 이 셋팅은 literal values 만 사용하는 시스템에 유용한데 이 셋팅은 literal values 만 다른 경우에 SQL profile 을 공유할 수 있기 때문이다. literal values 와 bind variables SQL text 에 함께 존재하거나 force_match 가 false(default) 로 되어 있으면 literal values 는 정규화 되지 않는다.
SQL profile 에 대한 정보를 DBA_SQL_PROFILES 에서 볼 수 있다.
17.5.3 Altering a SQL Profile
ALTER_SQL_PROFILE 로 존재하는 SQL profile 의 속성을 변경할 수 있다. 변경 가능한 속성값은 STATUS, NAME, DESCRIPTION 그리고 CATEGORY 이다.
CATEGORY 속성은 어떤 세션이 profile 을 적용할 수 있을지 결정한다. 또한 CATEGORY attribute 는 DBA_SQL_PROFILES.CATEGORY 를 조회하여 확인할 수 있다. 기본적으로, 모든 profiles 은 DEFAULT category 에 위치하며, 이것이 의미하는바는 SQLTUNE_CATEGORY 의 초기화 파라미터가 default 로 되어 있다는 것을 의미한다.
SQL profile 을 변경함으로써, 어떤 세션이 profile 생성의 영향을 받을지 정할 수 있다. 예를들어, category 를 DEV 로 지정하면 SQLTUNE_CATEGORY 파라미터가 DEV 로 되어 있는 세션만이 이 profile 을 사용한다. 다른 세션은 엑세스하지 못한다. 이 기술은 배포전에 profile 을 제한된 환경에서 테스트할 수 있게 한다.
To alter a SQL profile:
·
Call the DBMS_SQLTUNE.ALTER_SQL_PROFILE procedure.
다음의 예제에서, my_sql_profile 의 STATUS 속성은 DISABLED 로 수정되었다,
그 의미는 SQL
profile 은 편집중에는 사용되지 않는다는 뜻이다:
BEGIN DBMS_SQLTUNE.ALTER_SQL_PROFILE( name => 'my_sql_profile', attribute_name => 'STATUS', value => 'DISABLED');END;/
See Also:
Oracle
Database Reference for information on the SQLTUNE_CATEGORY initialization parameter
17.5.4 Dropping a SQL Profile
DROP_SQL_PROFILE 프로시저를 사용하여 SQL profile 을 삭제할 수 있다. 또 name 이 존재하지 않아 발생하는 에러를 무시할 것인지 셋팅가능하다. 이 예제에서는, default value false 가 선택되었다.
To drop a SQL profile:
·
Call the DBMS_SQLTUNE.DROP_SQL_PROFILE procedure.
The following example
drops the profile named my_sql_profile:
BEGIN DBMS_SQLTUNE.DROP_SQL_PROFILE( name => 'my_sql_profile' );END;/
17.5.5 Transporting a SQL Profile
SQL profiles 을 이동가능하다. 이는 한 데이터베이스의 sys schema 에서 staging table 로, staging table 에서 다른 데이터베이스로 import 하는 과정이 엮여 있다. 또 sql profile 을 동일 버전이나 상위버전의 Oracle 로 옮길 수 있다.
To transport a SQL profile:
1. CREATE_STGTAB_SQLPROF 프로시저를 SQL profiles 이 exported 될 곳에 staging table 을 생성하기 위해 사용한다.
다음 예제는 DBA1 스키마의 my_staging_table 을 생성하기 위한 예제이다 :
BEGIN DBMS_SQLTUNE.create_stgtab_sqlprof( table_name => 'my_staging_table', schema_name => 'DBA1' );END;/
2. PACK_STGTAB_SQLPROF 프로시저로 SQL profile 을 staging table 로 export 하기 위해 사용한다.
다음 예제는 dba1.my_staging_table 를 SQL profile my_profile 로 추가하기 위한 예제이다.
BEGIN DBMS_SQLTUNE.pack_stgtab_sqlprof( profile_name => 'my_profile', staging_table_name => 'my_staging_table', staging_schema_owner => 'dba1' );END;/
3. Oracle Data Pump 나 database link 와 같은 기술을 사용하여 staginb table 에서 SQL profiles 이 import 되야 할 database 로 옮긴다.
4. SQL profiles 이 import 될 database 에서, UNPACK_STGTAB_SQLPROF 프로시저를 staging table 로부터 import 하기 위해 사용한다.
다음의 예제는 staging table 의 SQL profile 을 어떻게 import 하나 보여주는 예제이다 :
BEGIN DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF( replace => TRUE, staging_table_name => 'my_staging_table');END;/
17.6 SQL Tuning Views
이 섹션은 tuning 한 SQL statements 에 대해 수집된 정보를 보여주는 view 에 대해 요약하였다. 이 정보에 엑세스 하기 위해서는 DBA 권한이 필요하다.
·
Advisor information views, such as DBA_ADVISOR_TASKS, DBA_ADVISOR_EXECUTIONS, DBA_ADVISOR_FINDINGS, DBA_ADVISOR_RECOMMENDATIONS, andDBA_ADVISOR_RATIONALE views.
·
SQL tuning information views, such as DBA_SQLTUNE_STATISTICS, DBA_SQLTUNE_BINDS, and DBA_SQLTUNE_PLANS views.
·
SQL tuning set views, such as DBA_SQLSET, DBA_SQLSET_BINDS, DBA_SQLSET_STATEMENTS, and DBA_SQLSET_REFERENCES views.
·
Information on captured execution plans for statements in SQL
tuning sets are displayed in the DBA_SQLSET_PLANS and USER_SQLSET_PLANS views.
·
SQL profile information is displayed in the DBA_SQL_PROFILES view.
If TYPE = MANUAL, then the SQL
profile was created manually by the SQL Tuning Advisor. If TYPE = MANUAL, then the
profile was created by automatic SQL tuning.
·
Advisor execution progress information is displayed in the V$ADVISOR_PROGRESS view.
·
Dynamic views containing information relevant to the SQL tuning,
such as V$SQL, V$SQLAREA, V$SQLSTATS, and V$SQL_BINDS views.