-
Notifications
You must be signed in to change notification settings - Fork 0
/
spa_elapsed.sql
124 lines (98 loc) · 3.04 KB
/
spa_elapsed.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
-- -----------------------------------------------------------------------------------
-- File Name : https://MikeDietrichDE.com/wp-content/scripts/12c/run_spa.sql
-- Author : Mike Dietrich
-- Description : Run SQL Performance Analyzer on a SQL Tuning Set
-- Requirements : Access to the DBA role.
-- Call Syntax : @run_spa.sql
-- Last Modified: 20/06/2018
-- -----------------------------------------------------------------------------------
SET SERVEROUT ON
SET PAGESIZE 1000
SET LONG 2000000
SET LINESIZE 400
--
-- Check if SQL Tuning SET if it exists
--
DECLARE
sts_exists number;
sts_task VARCHAR2(64);
tname VARCHAR2(100);
spa_exists number;
BEGIN
SELECT count(*)
INTO sts_exists
FROM DBA_SQLSET
WHERE rownum = 1 AND
name = 'STS_CaptureAWR';
IF sts_exists <> 1 THEN
DBMS_OUTPUT.PUT_LINE('SQL Tuning Set does not exist - creating it ...');
SYS.DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name=>'STS_CaptureAWR',
description=>'Statements from AWR Before-Change'
);
ELSE
DBMS_OUTPUT.PUT_LINE('SQL Tuning Set does exist - will run SPA now ...');
END IF;
SELECT count(*)
INTO spa_exists
FROM DBA_ADVISOR_TASKS
WHERE rownum = 1 AND
task_name = 'UPGRADE_TO_19C_2';
IF spa_exists = 1 THEN
SYS.DBMS_SQLPA.DROP_ANALYSIS_TASK(
task_name=>'UPGRADE_TO_19C_2'
);
ELSE
DBMS_OUTPUT.PUT_LINE('SQL Performance Analyzer Task does not exist - will be created ...');
END IF;
--
-- Create a SPA Task and parameterize it
--
tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
sqlset_name=>'STS_CaptureAWR',
task_name=>'UPGRADE_TO_19C_2',
description=>'Move on from 19c');
--
-- Set Parameters for SPA Task
--
DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(
task_name => 'UPGRADE_TO_19C_2',
parameter => 'workload_impact_threshold',
value => 2);
DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(
task_name => 'UPGRADE_TO_19C_2',
parameter => 'sql_impact_threshold',
value => 2);
--
-- Convert STS information from 11.2.0.4
--
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'UPGRADE_TO_19C_2',
execution_name => 'EXEC_SPA_TASK_11204_2',
execution_type => 'CONVERT SQLSET',
execution_desc => 'Convert 11204 Workload');
--
-- Simulate execution of STS in 19c
--
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'UPGRADE_TO_19C_2',
execution_name => 'EXEC_SPA_TASK_18C_2',
execution_type => 'TEST EXECUTE',
execution_desc => 'Test 11204 Workload in 19c');
--
-- Compare performance before/after on ELAPSED_TIME
--
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'UPGRADE_TO_19C_2',
execution_name => 'Compare 11204 to 19c ELAPSED',
execution_type => 'COMPARE PERFORMANCE',
execution_params =>
DBMS_ADVISOR.ARGLIST(
'comparison_metric',
'elapsed_time',
'execution_name1','EXEC_SPA_TASK_11204_2',
'execution_name2','EXEC_SPA_TASK_18C_2'),
execution_desc => 'Compare 11204 to 19c ELAPSED'
);
END;
/