oracle怎么创建基线,oracle 基线与优化（3）

oracle 基线与优化(三)

oracle 基线与优化:

生产库sts获取：

BEGIN

dbms_sqltune.create_sqlset(sqlset_name => 'SPS5', sqlset_owner => 'SYS');

END;

begin

DBMS_SCHEDULER.CREATE_JOB(job_name => 'SPS5',

job_type => 'PLSQL_BLOCK',

job_action => 'DECLARE bf VARCHAR2(98);

BEGIN

bf := q''#UPPER(PARSING_SCHEMA_NAME) = ''SYS'' AND UPPER(SQL_TEXT) = ''SELECT COUNT(*) FROM SYS.SPS_TEST'' #'';

dbms_sqltune.capture_cursor_cache_sqlset( sqlset_name=>''SPS5'',

time_limit=>''120'',

repeat_interval=>''5'',

basic_filter=>bf,

sqlset_owner=>''SYS'');

END;',

enabled => TRUE);

end;

生产库sts信息查询：

select name,statement_count from dba_sqlset;

导出前准备：(帮助理解)

BEGIN

DBMS_SQLTUNE.CREATE_STGTAB_SQLSET('PACK_SQLSET', 'SYSTEM');

END;

BEGIN

DBMS_SQLTUNE.PACK_STGTAB_SQLSET('SP52', 'SYS', 'PACK_SQLSET', 'SYSTEM');

END;

SELECT * FROM SYSTEM.PACK_SQLSET导出并传输到测试库：

执行：

begin

DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET('SPS5','SYS',TRUE,'PACK_SQLSET','SYSTEM');

end;

测试库上执行语句：

alter system flush buffer_cache;

alter system flush shared_pool;

select count(*) from SYS.SPS_TEST;

SELECT COUNT(*) FROM SYS.SPS_TEST创建执行SQL分析

declare

sts_task varchar2(64);

begin

sts_task := dbms_sqlpa.create_analysis_task(task_name => 'SPS5',

description => 'experiment11gR2 execute',

sqlset_name => 'SPS5');

end;执行分析：

declare

exe_task varchar2(64);

begin

exe_task := dbms_sqlpa.execute_analysis_task(task_name => 'SPS5',

execution_name => 'SPS51',

execution_type => 'CONVERT SQLSET', --sts获取

execution_desc => '11g sql trail');

end;

declare

exe_task varchar2(64);

begin

exe_task := dbms_sqlpa.execute_analysis_task(task_name => 'SPS5',

execution_name => 'SPS52',

execution_type => 'TEST EXECUTE',

execution_desc => '11g sql trail2');

end;

比较

begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'SPS5',

execution_type => 'COMPARE PERFORMANCE',

execution_name => 'Compare_elapsed_time2',

execution_params => dbms_advisor.arglist('execution_name1',

'SPS51',

'execution_name2',

'SPS52',

'comparison_metric',

'elapsed_time'));

end;

查看结果：

select xmltype(dbms_sqlpa.report_analysis_task('SPS5', 'html', 'typical', 'all', null, 100, 'Compare_elapsed_time')).getclobval(0, 0)

from dual;

General Information

Task Information:

Workload Information:

Task Name

: SPS5

Task Owner

: SYS

Description

:

SQL Tuning Set Name

: SPS5

SQL Tuning Set Owner

: SYS

Total SQL Statement Count

: 2

Execution Information:

Execution Name

: Compare_elapsed_time

Execution Type

: COMPARE PERFORMANCE

Description

:

Scope

: COMPREHENSIVE

Status

: COMPLETED

Started

: 12/28/2014 17:30:05

Last Updated

: 12/28/2014 17:30:05

Global Time Limit

: UNLIMITED

Per-SQL Time Limit

: UNUSED

Number of Errors

: 0

Analysis Information:

Before Change Execution:

After Change Execution:

Execution Name

: SPS51

Execution Type

: CONVERT SQLSET

Scope

: COMPREHENSIVE

Status

: COMPLETED

Started

: 12/28/2014 17:24:52

Last Updated

: 12/28/2014 17:24:52

Global Time Limit

: UNLIMITED

Per-SQL Time Limit

: UNLIMITED

Execution Name

: SPS52

Execution Type

: TEST EXECUTE

Scope

: COMPREHENSIVE

Status

: COMPLETED

Started

: 12/28/2014 17:27:32

Last Updated

: 12/28/2014 17:27:33

Global Time Limit

: UNLIMITED

Per-SQL Time Limit

: 60

Number of Errors

: 0

Comparison Metric:ELAPSED_TIME

Workload Impact Threshold:1%

SQL Impact Threshold:1%

Report Summary

Projected Workload Change Impact:

Overall Impact

:

99.5%

Improvement Impact

:

99.5%

Regression Impact

:

0%

SQL Statement Count

SQL Category

SQL Count

Plan Change Count

Overall

2

0

Improved

2

0

Top 2 SQL Sorted by Absolute Value of Change Impact on the Workload

object_id

sql_id

Impact on

Workload

Execution

Frequency

Metric

Before

Metric

After

Impact

on SQL

Plan

Change

6

ff9p4xt2sqhd4

77.3%

63

10253.7301587302

56

99.45%

n

5

ah0402tq401hh

22.2%

17

10892.1764705882

38

99.65%

n

Report Details

SQL Details:

Object ID

: 6

Schema Name

: SYS

SQL ID

: ff9p4xt2sqhd4

Execution Frequency

: 63

SQL Text

: select count(*) from SYS.SPS_TEST

Execution Statistics:

Stat Name

Impact on

Workload

Value

Before

Value

After

Impact

on SQL

elapsed_time

77.3%

.010254

.000056

99.45%

parse_time

.000121

cpu_time

76.6%

.008914

0

100%

user_io_time

0

buffer_gets

78.63%

2050

3

99.85%

cost

3138.71%

558

2

99.64%

reads

0%

0

0

0%

writes

0%

0

0

0%

io_interconnect_bytes

0

rows

1

1

Notes:

After Change:

该语句已首先执行以预热缓冲区高速缓存。

显示的统计信息是后面的 9 执行的平均值。

Findings (1):

此 SQL 的性能得到了改善。

Execution Plan Before Change:

Plan Hash Value

: 3930752761

Id

Operation

Name

Rows

Bytes

Cost

Time

0

SELECT STATEMENT

558

1

. SORT AGGREGATE

1

2

.. TABLE ACCESS FULL

SPS_TEST

143778

558

00:00:07

Execution Plan After Change:

Plan Id

: 202

Plan Hash Value

: 3930752761

Id

Operation

Name

Rows

Bytes

Cost

Time

0

SELECT STATEMENT

1

2

00:00:01

1

. SORT AGGREGATE

1

2

.. TABLE ACCESS FULL

SPS_TEST

1

2

00:00:01

SQL Details:

Object ID

: 5

Schema Name

: SYS

SQL ID

: ah0402tq401hh

Execution Frequency

: 17

SQL Text

: SELECT COUNT(*) FROM SYS.SPS_TEST

Execution Statistics:

Stat Name

Impact on

Workload

Value

Before

Value

After

Impact

on SQL

elapsed_time

22.2%

.010892

.000038

99.65%

parse_time

.000125

cpu_time

23.4%

.010094

0

100%

user_io_time

0

buffer_gets

21.22%

2050

3

99.85%

cost

846.95%

558

2

99.64%

reads

0%

0

0

0%

writes

0%

0

0

0%

io_interconnect_bytes

0

rows

1

1

Notes:

After Change:

该语句已首先执行以预热缓冲区高速缓存。

显示的统计信息是后面的 9 执行的平均值。

Findings (1):

此 SQL 的性能得到了改善。

Execution Plan Before Change:

Plan Hash Value

: 3930752761

Id

Operation

Name

Rows

Bytes

Cost

Time

0

SELECT STATEMENT

558

1

. SORT AGGREGATE

1

2

.. TABLE ACCESS FULL

SPS_TEST

143778

558

00:00:07

Execution Plan After Change:

Plan Id

: 201

Plan Hash Value

: 3930752761

Id

Operation

Name

Rows

Bytes

Cost

Time

0

SELECT STATEMENT

1

2

00:00:01

1

. SORT AGGREGATE

1

2

.. TABLE ACCESS FULL

SPS_TEST

1

2

00:00:01

其他：

--比较CPU_TIME

begin

dbms_sqlpa.execute_analysis_task(task_name => '11gsps2',

execution_name => 'comparecpu',

execution_type => 'COMPARE PERFORMANCE',

execution_params => dbms_advisor.arglist('COMPARISON_METRIC',

'CPU_TIME',

'EXECUTION_NAME1',

'11g_trail',

'EXECUTION_NAME2',

'11g_trail2'),

execution_desc => 'Compare 10g SQL Trace Performance to 11g Test-Execute for CPU_TIME');

end;

/

--比较BUFFER_GETS

begin

dbms_sqlpa.execute_analysis_task(task_name => '11gsps2',

execution_name => 'comparbuffergets',

execution_type => 'COMPARE PERFORMANCE',

execution_params => dbms_advisor.arglist('COMPARISON_METRIC',

'BUFFER_GETS',

'EXECUTION_NAME1',

'11g_trail',

'EXECUTION_NAME2',

'11g_trail2'),

execution_desc => 'Compare 10g SQL Trace Performance to 11g Test-Execute for BUFFER_GETS');

end;

--比较实际执行时长

begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => '11gsps2',

execution_type => 'COMPARE PERFORMANCE',

execution_name => 'Compare_elapsed_time',

execution_params => dbms_advisor.arglist('execution_name1',

'11g_trail',

'execution_name2',

'11g_trail2',

'comparison_metric',

'elapsed_time'));

end;

/

--比较物理读

begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => '11gsps2',

execution_type => 'COMPARE PERFORMANCE',

execution_name => 'Compare_physical_reads0',

execution_params => dbms_advisor.arglist('execution_name1',

'11g_trail',

'execution_name2',

'11g_trail2',

'comparison_metric',

'disk_reads'));

end;

/

set long 100000 longchunksize 100000 linesize 200 head off feedback off echo off

spool spa_report_elapsed_time.html

SELECT dbms_sqlpa.report_analysis_task('SPA_TEST', 'HTML', 'ALL','ALL', execution_name=>'Compare_elapsed_time') FROM dual;

spool off