Overview
\
---\-
SQL Performance Analyzer는 변경에 따른 위험관리 방안의 향상을 위해서 소개되는 신기능이다.
Database Replay는 실제로 캡처된 정보에 대해서만 그 초점을 맞추고 있음.
다음과 같은 변경 작업이 SQL에 미치는 영향을 파악하기 어려움.
초기화 파라미터 변경
옵티마이저 통계 리프레시
데이베이스 업그레이드
변경 작업이 특정 SQL 구문 또는 전체 SQL 워크로드에 미치는 영향을 평가/비교 할 수 있다.
Database Replay 와 SQL Replay 비교
\
-\-
1. Oracle 10g에서 Oracle 11g로 업그레이드한 후의 영향을 자동으로 시뮬레이션 할 수 있다.
2. Oracle 11g의 주요 업그레이드와 중요 패치 업그레이드 등 여러가지 상황에 대해 잠재적인 위험요소를 점검할 수 있다.
3. SQL 성능 통계에 대한 비교도 가능하다.
일반적인 데이터베이스 환경 변경사항
\- Database upgrades
\- Implementation of tuning recommendations
\- Schema changes
\- Statistics gathering
\- Database parameter changes
\- OS/ hardware changes
|Database Replay|SQL Replay|
|동일한 Application을 서로 다른 Database 시스템에 그대로 재연
Database변경이 Application 수행에 어떤 변화가 발생하는지 테스트|SQL
Performance Analyzer을 통해 SQL 실행계획의 구조에 어떤 영향을 미치는지 테스트|
문제가 파악 후
SQL Tuning Advisor (STA) 를 사용하여 본래 실행계획으로 환원시키거나 추가 튜닝을 통해서 문제를 해결함.
SQL Performance Analyzer 테스트 단계
0. 환경설정
1. SQL 튜닝 집합에 준하는 SQL Performance Analyzer 작업 생성
2. 초기 환경에서 SQL Tuning Set 재생
3 .변경된 환경에서 SQL Tuning Set 재생
4 .단계 2와 단계 3 비교
5 .비교 보고서 보기
\
---\-
0. 환경설정
sqlplus / as sysdba << \! > /tmp/create_apps_user.log
grant dba to scott;
create user apps identified by apps;
grant dba to apps;
grant execute on dbms_sqltune_internal to apps; \-\- 필요
grant create any sql profile to apps; \-\- 필요
grant select on scott.lu_pg_featurevalue_15 to apps;
grant select on scott.lu_elementrange_rel to apps;
demo_app.sql
\
---\-
Connect sh/sh
select /\* CONTROL_QUERY11 \*/ sum(QUANTITY_SOLD) from sales where PROMO_ID = 33;
select /\* CONTROL_QUERY12 \*/ sum(QUANTITY_SOLD) from sales where PROMO_ID = 350;
select /\* CONTROL_QUERY13 \*/ sum(QUANTITY_SOLD) from sales where PROMO_ID = 351;
select /\* CONTROL_QUERY14 \*/ sum(QUANTITY_SOLD) from sales where PROMO_ID = 534;
select /\* CONTROL_QUERY15 \*/ sum(QUANTITY_SOLD) from sales where PROMO_ID = 999;
select /\* CONTROL_QUERY21 \*/ sum(QUANTITY_SOLD) from sales where channel_id = 2;
select /\* CONTROL_QUERY22 \*/ sum(QUANTITY_SOLD) from sales where channel_id = 3;
select /\* CONTROL_QUERY23 \*/ sum(QUANTITY_SOLD) from sales where channel_id = 4;
select /\* CONTROL_QUERY24 \*/ sum(QUANTITY_SOLD) from sales where channel_id = 5;
select /\* CONTROL_QUERY25 \*/ sum(QUANTITY_SOLD) from sales where channel_id = 9;
\
---\-
1. SQL 튜닝 집합에 준하는 SQL Performance Analyzer 작업 생성
2. 초기 환경에서 SQL Tuning Set 재생
3 .변경된 환경에서 SQL Tuning Set 재생
4 .단계 2와 단계 3 비교
5 .비교 보고서 보기
결론
SPA는 데이터베이스에서 변경 작업을 수행하는 경우라면 어떠한 상황에서든 그 유용하다.
개별 SQL 구문의 실행 결과를 확인할 수 없는 Database Replay와 달리
특정 SQL 구문 또는 전체 애플리케이션 SQL 워크로드의 실행 결과를 비교하고 분석할 수 있습니다.
결과적으로 관리자는 변경 이전/이후의 장점과 단점을 비교하여 최적의 대안을 얻어낼 수 있다.
SPA는 관리자로 하여금 정확한 측정 결과를 바탕으로 결정을 내릴 수 있게 도와준다.
참고 DBMS_SQLPA 팩키지
\
-\-
\
\- create_analysis_task - sql text format \
---\-
\-\- NAME:
\-- create_analysis_task - CRATE an ANALYSIS TASK in order to process
\-- and analyzer perfromance of a single SQL statement (sql text format)
\-\-
\-\- DESCRIPTION
\-- This function is called to prepare the analysis of a single statement
\-- given its text. The function mainly creates an advisor task and sets
\-- its parameters.
\-\-
\-\- PARAMETERS:
\-- sql_text (IN) - text of a SQL statement
\-- bind_list (IN) - a set of bind values
\-- parsing_schema (IN) - the username for who the statement will be tuned
\-- task_name (IN) - optional analysis task name
\-- description (IN) - maximum of 256 SQL analysis description
\-\-
\-\- RETURNS:
\-- SQL analysis task unique name
\-\-
\-\- EXCEPTIONS:
\-- To be done
\
\-
FUNCTION create_analysis_task(
sql_text IN CLOB,
bind_list IN sql_binds := NULL,
parsing_schema IN VARCHAR2 := NULL,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2;
\
---\- create_analysis_task - sql_id format \
--\-
\-\- NAME:
\-- create_analysis_task - sql_id format
\-\-
\-\- DESCRIPTION
\-- This function is called to prepare the analysis of a single statement
\-- from the cursor cache given its identifier. The function mainly
\-- creates an advisor task and sets its parameters.
\-\-
\-\- PARAMETERS:
\-- sql_id (IN) - identifier of the statement
\-- plan_hash_value (IN) - hash value of the sql execution plan
\-- task_name (IN) - optional analysis task name
\-- description (IN) - maximum of 256 SQL analysis description
\-\-
\-\- RETURNS:
\-- SQL analysis task unique name
\-\-
\-\- EXCEPTIONS:
\-- To be done
\
\-
FUNCTION create_analysis_task(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2;
\
-\- create_analysis_task - workload repository format \
---\-
\-\- NAME:
\-- create_analysis_task - workload repository format
\-\-
\-\- DESCRIPTION
\-- This function is called to prepare the analysis of a single statement
\-- from the workload repository given a range of snapshot identifiers.
\-- The function mainly creates an advisor task and sets its parameters.
\-\-
\-\- PARAMETERS:
\-- begin_snap (IN) - begin snapshot identifier
\-- end_snap (IN) - end snapshot identifier
\-- sql_id (IN) - identifier of the statement
\-- plan_hash_value (IN) - plan hash value
\-- task_name (IN) - optional analysis task name
\-- description (IN) - maximum of 256 SQL analysis description
\-\-
\-\- RETURNS:
\-- SQL analysis task unique name
\-\-
\-\- EXCEPTIONS:
\-- To be done
\
\-
FUNCTION create_analysis_task(
begin_snap IN NUMBER,
end_snap IN NUMBER,
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2;
\
-\- create_analysis_task - sqlset format \
\-
\-\- NAME:
\-- create_analysis_task - sqlset format
\-\-
\-\- DESCRIPTION:
\-- This function is called to prepare the analysis of a sql tuning set.
\-- The function mainly creates an advisor task and sets its parameters.
\-\-
\-\- PARAMETERS:
\-- sqlset_name (IN) - sqlset name
\-- basic_filter (IN) - SQL predicate to filter the SQL from the STS
\-- order_by (IN) - an order-by clause on the selected SQL
\-- top_sql (IN) - top N SQL after filtering and ranking
\-- task_name (IN) - optional analysis task name
\-- description (IN) - maximum of 256 SQL analysis description
\-\-
\-- sqlset_owner (IN) - the owner of the sqlset, or null for current
\-- schema owner
\-\-
\-\- RETURNS:
\-- SQL analysis task unique name
\-\-
\-\- EXCEPTIONS:
\-- To be done
\
\-
FUNCTION create_analysis_task(
sqlset_name IN VARCHAR2 := NULL,
basic_filter IN VARCHAR2 := NULL,
order_by IN VARCHAR2 := NULL,
top_sql IN NUMBER := NULL,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL)
RETURN VARCHAR2;
\
-\- set_analysis_task_parameter \
-\-
\-\- NAME:
\-- set_analysis_task_parameter - set sql analysis task parameter value
\-\-
\-\- DESCRIPTION:
\-- This procedure updates the value of a task analysis parameter
\-- of type VARCHAR2. The possible analysis parameters that can be set
\-- by this procedure are:
\-- MODE : analysis scope (comprehensive, limited)
\-- BASIC_FILTER : basic filter for sql analysis set
\-- PLAN_FILTER : plan filter for sql tuning set (see select_sqlset
\-- for possible values)
\-- RANK_MEASURE1 : first ranking measure for sql analysis set
\-- RANK_MEASURE2 : second possible ranking measure for sql analysis set
\-- RANK_MEASURE3 : third possible ranking measure for sql analysis set
\-- RESUME_FILTER : a extra filter for sts besides basic_filter
\-\-
\-\-
\-\- PARAMETERS:
\-- task_name (IN) - identifier of the task to execute
\-- parameter (IN) - name of the parameter to set
\-- value (IN) - new value of the specified parameter
\-\-
\-\- RETURNS:
\-- NONE
\-\-
\-\- EXCEPTIONS:
\-- To be done
\
\-
PROCEDURE set_analysis_task_parameter(
task_name IN VARCHAR2,
parameter IN VARCHAR2,
value IN VARCHAR2);
\
-\- set_analysis_task_parameter \
-\-
\-\- NAME:
\-- set_analysis_task_parameter - set sql analysis task parameter value
\-\-
\-\- DESCRIPTION:
\-- This procedure updates the value of a sql analysis parameter
\-- of type NUMBER. The possible analysis parameters that can be set
\-- by this procedure are:
\-- DAYS_TO_EXPIRE : number of days until the task is deleted
\-- TIME_LIMIT : global time out
\-- LOCAL_TIME_LIMIT : local time out
\-- SQL_LIMIT : maximum number of sts statements to tune
\-- SQL_PERCENTAGE : percentage filter of sts statements
\-- EXECUTE_COUNT : multiple execution count to be used
\-- in the test execute. We intend to execute
\-- them multiple times in test execute.
\-\-
\-\- PARAMETERS:
\-- task_name (IN) - identifier of the task to execute
\-- parameter (IN) - name of the parameter to set
\-- value (IN) - new value of the specified parameter
\-\-
\-\- RETURNS:
\-- NONE
\-\-
\-\- EXCEPTIONS:
\-- To be done
\
\-
PROCEDURE set_analysis_task_parameter(
task_name IN VARCHAR2,
parameter IN VARCHAR2,
value IN NUMBER);
\
--\- set_analysis_default_parameter \
-\-
\-\- NAME:
\-- set_analysis_default_parameter - set sql analysis task parameter
\-- default value
\-\-
\-\- DESCRIPTION:
\-- This procedure is called to update the DEFAULT value of an analyzer
\-- parameter of type VARCHAR2.
\-\-
\-\- PARAMETERS:
\-- parameter (IN) - name of the parameter to set
\-- value (IN) - new value of the specified parameter
\-\-
\-\- RETURNS:
\-- NONE
\-\-
\-\- EXCEPTIONS:
\-- To be done
\
\-
PROCEDURE set_analysis_default_parameter(
parameter IN VARCHAR2,
value IN VARCHAR2);
\
---\- set_analysis_default_parameter \
\-
\-\- NAME:
\-- set_analysis_default_parameter - set sql analysis task parameter
\-- default value
\-\-
\-\- DESCRIPTION:
\-- This procedure is called to update the default value of an analyzer
\-- parameter of type NUMBER.
\-\-
\-\- PARAMETERS:
\-- parameter (IN) - name of the parameter to set
\-- value (IN) - new value of the specified parameter
\-\-
\-\- RETURNS:
\-- NONE
\-\-
\-\- EXCEPTIONS:
\-- To be done
\
\-
PROCEDURE set_analysis_default_parameter(
parameter IN VARCHAR2,
value IN NUMBER);
\
\- execute_analysis_task \
\-
\-\- NAME:
\-- execute_analysis_task - execute a sql analysis task
\-\-
\-\- DESCRIPTION:
\-- This procedure is called to execute a previously created analysis task
\-\-
\-\- PARAMETERS:
\-- task_name (IN) - identifier of the task to execute
\-- execution_type (IN) - type of the action to perform. Possible
\-- values are: [TEST] EXECUTE (default),
\-- EXPLAIN [PLAN],
\-- COMPARE [PERFORMANCE]
\-- If NULL it will default to the value of
\-- the DEFAULT_EXECUTION_TYPE parameter.
\-- execution_name (IN) - A name to qualify and identify an execution.
\-- If not specified, it be generated by
\-- the advisor and returned by function.
\-- execution_params (IN) - List of parameters (name, value) for
\-- the specified execution.
\-- Note that execution parameters are real
\-- task parameters that have effect only on
\-- the execution they specified for.
\-- Example:
\-- dbms_advisor.arglist('time_limit',
\-- 1000,
\-- 'COMPARE_METRIC',
\-- 'buffer_gets * 10')
\-- execution_desc (IN) - A 256-length string
\-\-
\-\- RETURNS:
\-- The function version returns the name of the new execution
\
\-
\-\- function flavor
FUNCTION execute_analysis_task(
task_name IN VARCHAR2,
execution_type IN VARCHAR2 := 'test execute',
execution_name IN VARCHAR2 := NULL,
execution_params IN dbms_advisor.argList := NULL,
execution_desc IN VARCHAR2 := NULL)
RETURN VARCHAR2;
\-\- procedure flavor
PROCEDURE execute_analysis_task(
task_name IN VARCHAR2,
execution_type IN VARCHAR2 := 'test execute',
execution_name IN VARCHAR2 := NULL,
execution_params IN dbms_advisor.argList := NULL,
execution_desc IN VARCHAR2 := NULL);
\
\- interrupt_analysis_task \
--\-
\-\- NAME:
\-- interrupt_analysis_task - interrupt a sql analysis task
\-\-
\-\- DESCRIPTION:
\-- This procedure interrupts the currently executing analysis task.
\-- The task will end its operations as it would at a normal exit
\-- so that the user will be able to access the intermediate results at
\-- this point.
\-\-
\-\- PARAMETERS:
\-- task_name (IN) - identifier of the task to execute
\
\-
procedure interrupt_analysis_task(task_name IN VARCHAR2);
\
\- cancel_analysis_task \
-\-
\-\- NAME:
\-- cancel_analysis_task - cancel a sql analysis task
\-\-
\-\- DESCRIPTION:
\-- This procedure is called to cancel the currently executing analysis
\-- task. All intermediate result data will be removed from the task.
\-\-
\-\- PARAMETERS:
\-- task_name (IN) - identifier of the task to execute
\
---\-
PROCEDURE cancel_analysis_task(task_name IN VARCHAR2);
\
\- reset_analysis_task \
-\-
\-\- NAME:
\-- reset_analysis_task - reset a sql analysis task
\-\-
\-\- DESCRIPTION:
\-- This procedure resets an analysis task to its initial state.
\-- All intermediate result data will be deleted. Call this procedure on
\-- a task that is not currently executing.
\-\-
\-\- PARAMETERS:
\-- task_name (IN) - identifier of the task to reset
\
\-
PROCEDURE reset_analysis_task(task_name IN VARCHAR2);
\
--\- drop_analysis_task \
-\-
\-\- NAME:
\-- drop_analysis_task - drop a sql analysis task
\-\-
\-\- DESCRIPTION:
\-- This procedure is called to drop a SQL analysis task.
\-- The task and All its result data will be deleted.
\-\-
\-\- PARAMETERS:
\-- task_name (IN) - identifier of the task to execute
\
\-
PROCEDURE drop_analysis_task(task_name IN VARCHAR2);
\
\- resume_analysis_task \
-\-
\-\- NAME:
\-- resume_analysis_task - resume a sql analysis task
\-\-
\-\- DESCRIPTION:
\-- This procedure resumes a previously interrupted task execution.
\-\-
\-\- PARAMETERS:
\-- task_name (IN) - identifier of the task to execute
\-- basic_filter (IN) - a SQL predicate to filter the SQL from a STS.
\-- Note that this filter will be applied in
\-- conjunction with the basic filter
\-- (i.e., parameter basic_filter) specified
\-- when calling create_analysis_task.
\-\- RETURNS:
\-- NONE
\-\-
\-\- EXCEPTIONS:
\-- To be done
\
\-
PROCEDURE resume_analysis_task(
task_name IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL);
\
--\- report_analysis_task \
---\-
\-\- NAME:
\-- report_analysis_task - report a SQL analysis task
\-\-
\-\- DESCRIPTION:
\-- This procedure is called to display the results of a analysis task.
\-\-
\-\- PARAMETERS:
\-- task_name (IN) - name of the task to report.
\-- type (IN) - type of the report.
\-- Possible values are: TEXT (default), HTML, XML.
\-- level (IN) - format of the recommendations. Possible values:
\-- BASIC - currently, same as typical
\-- TYPICAL(default)\- SQL with perf. changes+errors
\-- ALL - details of all SQL
\-- IMPROVED - only improved SQL
\-- REGRESSED - only regressed SQL
\-- CHANGED - only SQL with changed perf.
\-- UNCHANGED - only SQL with unchanged perf.
\-- CHANGED_PLANS - only SQL with plan changes
\-- UNCHANGED_PLANS - only SQL with unchanged plans
\-- ERRORS - SQL with errors only
\-- section (IN) - particular section in the report.
\-- Possible values are:
\-- SUMMARY (default) - workload summary only
\-- ALL - summary + details on SQL
\-- object_id (IN) - identifier of the advisor framework object that
\-- represents a given SQL in a tuning set (STS).
\-- top_sql (IN) - number of statements in a STS for which the
\-- report is generated.
\-- execution_name (IN) - name of the task execution to use. If NULL, the
\-- report will be generated for the last task
\-- execution.
\-- task_owner (IN) - owner of the relevant analysis task.
\-- Defaults to the current schema owner.
\-- order_by (IN) - how to sort SQL statements in the report
\-- (summary and body). Possible values are:
\-- + NULL (default) : order by impact on workload
\-- + workload_impact: same as null
\-- + sql_impact : order by change impact on SQL
\-- + metric_delta/change_diff: order by change
\-- difference in SQL perfomance in terms
\-- of the Comparison Metric.
\-\- RETURNS
\-- A clob containing the desired report.
\-\-
\-\- NOTE:
\-- So far order_by can be used only one report is generated for
\-- a comparison and not for a single test execute or explain plan.
\
\-
FUNCTION report_analysis_task(
task_name IN VARCHAR2,
type IN VARCHAR2 := 'text',
level IN VARCHAR2 := 'typical',
section IN VARCHAR2 := 'summary',
object_id IN NUMBER := NULL,
top_sql IN NUMBER := 100,
execution_name IN VARCHAR2 := NULL,
task_owner IN VARCHAR2 := NULL,
order_by IN VARCHAR2 := NULL)
RETURN clob;
\
\-
\-\- NAME:
\-- get_sess_optimizer_env - get session optimizer env
\-\-
\-\- DESCRIPTION:
\-- This function is a callout function to get the compilation
\-- environment from the session for a remote SPA trial. The CE
\-- itself is returned in its compact linear representation as a
\-- RAW data type.
\-\-
\-\- PARAMETERS:
\-- NONE
\-\-
\-\- RETURNS
\-- A raw containing the compilation environment
\-\-
\
\-
FUNCTION get_sess_optimizer_env
RETURN RAW;
찾아보기
Searching for Intelligent Life in Oracle's CBO