django oracle clob,SPA（SQL Performance Analyzer）实践小记 step-by-step

--1. Create a SQL Tuning Set:

BEGINdbms_sqltune.create_sqlset('DJANGO_SET1');END;

--2.Load SQL into the STS(From AWR Snapshots,load all the queries between two snapshots) --测试环境，SQL不足，加载两个快照间所有sql

select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1;

DECLARE

CUR SYS_REFCURSOR;

BEGIN

OPEN CUR FOR

SELECT VALUE(P)

FROM TABLE(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(BEGIN_SNAP => 84,

END_SNAP   => 115)) P;

DBMS_SQLTUNE.LOAD_SQLSET('DJANGO_SET1', CUR);

CLOSE CUR;

END;

--3. CREATE ADVISOR TASK  AND SET PARAMETERS( PL/SQL Packages and Types Reference DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER)

DECLARE

STS_TASK VARCHAR2(4000);

BEGIN

STS_TASK := DBMS_SQLPA.CREATE_ANALYSIS_TASK(SQLSET_NAME => 'DJANGO_SET1',

TASK_NAME   => 'DJANGO_SPA_TASK1');

DBMS_OUTPUT.PUT_LINE(STS_TASK);--DJANGO_SPA_TASK1

END;

/

--4.设置任务参数

BEGIN

DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(TASK_NAME => 'DJANGO_SPA_TASK1',

PARAMETER => 'APPLY_CAPTURED_COMPILENV',

VALUE     => 1);

DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(TASK_NAME => 'DJANGO_SPA_TASK1',

PARAMETER => 'COMPARISON_METRIC',

VALUE     => 'cpu_time + buffer_gets * 10)');

DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(TASK_NAME => 'DJANGO_SPA_TASK1',

PARAMETER => 'DEFAULT_EXECUTION_TYPE',

VALUE     => 'execute');

END;

/

--5.1执行任务

BEGIN

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME      => 'DJANGO_SPA_TASK1',

EXECUTION_TYPE => 'EXECUTE',

EXECUTION_NAME => 'beore_change');--before

END;

/

--.本处测试修改optimizer_features_enable和optimizer_index_cost_adj参数

SQL> alter system set optimizer_features_enable='10.2.0.4'  scope=memory; --11.2.0.4

System altered.

SQL> alter system set optimizer_index_cost_adj=25 scope=memory;--100

System altered.

--5.2系统参数，修改参数后再次执行任务

BEGIN

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME      => 'DJANGO_SPA_TASK1',

EXECUTION_TYPE => 'EXECUTE',

EXECUTION_NAME => 'after_change');

END;

--5.3 细分各维度

/*

elapsed_time (default),cpu_time, buffer_gets, disk_reads, direct_writes, and optimizer_cost.

*/

--BUFFER_GETS

BEGIN

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME        => 'DJANGO_SPA_TASK1',

EXECUTION_NAME   => 'compare_bg',

EXECUTION_TYPE   => 'COMPARE PERFORMANCE',

EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST('COMPARISON_METRIC',

'BUFFER_GETS',

'EXECUTION_NAME1',

'beore_change',

'EXECUTION_NAME2',

'after_change'),

EXECUTION_DESC   => 'change optimizer_features_enable and optimizer_index_cost_adj');

END;

/

--elapsed_time

BEGIN

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME        => 'DJANGO_SPA_TASK1',

EXECUTION_NAME   => 'compare_elapsed_time1',

EXECUTION_TYPE   => 'COMPARE PERFORMANCE',

EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST('COMPARISON_METRIC',

'elapsed_time',

'EXECUTION_NAME1',

'beore_change',

'EXECUTION_NAME2',

'after_change'),

EXECUTION_DESC   => 'change optimizer_features_enable and optimizer_index_cost_adj');

END;

/

--cpu_time

BEGIN

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME        => 'DJANGO_SPA_TASK1',

EXECUTION_NAME   => 'compare_cpu_time1',

EXECUTION_TYPE   => 'COMPARE PERFORMANCE',

EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST('COMPARISON_METRIC',

'cpu_time',

'EXECUTION_NAME1',

'beore_change',

'EXECUTION_NAME2',

'after_change'),

EXECUTION_DESC   => 'change optimizer_features_enable and optimizer_index_cost_adj');

END;

/

--disk_reads

BEGIN

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME        => 'DJANGO_SPA_TASK1',

EXECUTION_NAME   => 'compare_disk_reads1',

EXECUTION_TYPE   => 'COMPARE PERFORMANCE',

EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST('COMPARISON_METRIC',

'disk_reads',

'EXECUTION_NAME1',

'beore_change',

'EXECUTION_NAME2',

'after_change'),

EXECUTION_DESC   => 'change optimizer_features_enable and optimizer_index_cost_adj');

END;

/

--direct_writes

BEGIN

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME        => 'DJANGO_SPA_TASK1',

EXECUTION_NAME   => 'compare_direct_writes1',

EXECUTION_TYPE   => 'COMPARE PERFORMANCE',

EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST('COMPARISON_METRIC',

'direct_writes',

'EXECUTION_NAME1',

'beore_change',

'EXECUTION_NAME2',

'after_change'),

EXECUTION_DESC   => 'change optimizer_features_enable and optimizer_index_cost_adj');

END;

/

--optimizer_cost

BEGIN

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME        => 'DJANGO_SPA_TASK1',

EXECUTION_NAME   => 'compare_optimizer_cost1',

EXECUTION_TYPE   => 'COMPARE PERFORMANCE',

EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST('COMPARISON_METRIC',

'optimizer_cost',

'EXECUTION_NAME1',

'beore_change',

'EXECUTION_NAME2',

'after_change'),

EXECUTION_DESC   => 'change optimizer_features_enable and optimizer_index_cost_adj');

END;

/

--6.获取报告

SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('DJANGO_SPA_TASK1',

'HTML',

'ALL',

'ALL',

EXECUTION_NAME => 'after_change')

FROM DUAL;

SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('DJANGO_SPA_TASK1',

'HTML',

'ALL',

'ALL',

EXECUTION_NAME => 'beore_change')

FROM DUAL;

SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('DJANGO_SPA_TASK1', 'html', 'typical', 'all', NULL, 100, 'compare_elapsed_time1'))

.GETCLOBVAL(0, 0)

FROM DUAL;

SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('DJANGO_SPA_TASK1', 'html', 'typical', 'all', NULL, 100, 'compare_cpu_time1'))

.GETCLOBVAL(0, 0)

FROM DUAL;

SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('DJANGO_SPA_TASK1', 'html', 'typical', 'all', NULL, 100, 'compare_disk_reads1'))

.GETCLOBVAL(0, 0)

FROM DUAL;

SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('DJANGO_SPA_TASK1', 'html', 'typical', 'all', NULL, 100, 'compare_direct_writes1'))

.GETCLOBVAL(0, 0)

FROM DUAL;

SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('DJANGO_SPA_TASK1', 'html', 'typical', 'all', NULL, 100, 'compare_optimizer_cost1'))

.GETCLOBVAL(0, 0)

FROM DUAL;

--7.报告展示示例

参考：

1.How to Load Queries into a SQL Tuning Set (STS) (文档 ID 1271343.1)

2.SQL Performance Analyzer (An Oracle White Paper November 2007)

3.Oracle  Database Real Application Testing User’s Guide 11.2

4.Oracle Database PL/SQL Packages and Types Reference 11.2

5.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27349469/viewspace-2021799/，如需转载，请注明出处，否则将追究法律责任。