1 PACKAGE dbms_auto_sqltune AUTHID CURRENT_USER AS
2
3 --
4 -- report type (possible values) constants
5 --
6 TYPE_TEXT CONSTANT VARCHAR2(4) := dbms_sqltune.TYPE_TEXT;
7 TYPE_XML CONSTANT VARCHAR2(3) := dbms_sqltune.TYPE_XML;
8 TYPE_HTML CONSTANT VARCHAR2(4) := dbms_sqltune.TYPE_HTML;
9
10 --
11 -- report level (possible values) constants
12 --
13 LEVEL_TYPICAL CONSTANT VARCHAR2(7) := dbms_sqltune.LEVEL_TYPICAL;
14 LEVEL_BASIC CONSTANT VARCHAR2(5) := dbms_sqltune.LEVEL_BASIC;
15 LEVEL_ALL CONSTANT VARCHAR2(3) := dbms_sqltune.LEVEL_ALL;
16
17 --
18 -- report section (possible values) constants
19 --
20 SECTION_FINDINGS CONSTANT VARCHAR2(8) := dbms_sqltune.SECTION_FINDINGS;
21 SECTION_PLANS CONSTANT VARCHAR2(5) := dbms_sqltune.SECTION_PLANS;
22 SECTION_INFORMATION CONSTANT VARCHAR2(11):=
23 dbms_sqltune.SECTION_INFORMATION;
24 SECTION_ERRORS CONSTANT VARCHAR2(6) := dbms_sqltune.SECTION_ERRORS;
25 SECTION_ALL CONSTANT VARCHAR2(3) := dbms_sqltune.SECTION_ALL;
26 SECTION_SUMMARY CONSTANT VARCHAR2(7) := dbms_sqltune.SECTION_SUMMARY;
27
28
29 --------------------------- execute_auto_tuning_task ------------------------
30 -- NAME:
31 -- execute_auto_tuning_task - execute the SYS_AUTO_SQL_TUNING_TASK
32 --
33 -- DESCRIPTION:
34 -- This procedure is called to execute SYS_AUTO_SQL_TUNING_TASK
35 -- manually. The behavior will be the same as in automatic executions.
36 -- NOTE only SYS can call this API.
37 --
38 -- PARAMETERS:
39 -- execution_name (IN) - A name to qualify and identify an execution
40 -- If not specified, it be generated by
41 -- the advisor and returned by function.
42 -- execution_params (IN) - List of parameters (name, value) for
43 -- the specified execution. Notice that execution
44 -- parameters are real task parameters that
45 -- have effect only on the execution they
46 -- specified for.
47 -- Example:
48 -- dbms_advisor.arglist('time_limit', 12,
49 -- 'username', 'foo')
50 -- execution_desc (IN) - A 256-length string describing the execution.
51 --
52 -- RETURNS:
53 -- The function version returns the name of the new execution
54 --
55 -- EXCEPTIONS:
56 -- To be done
57 -----------------------------------------------------------------------------
58 FUNCTION execute_auto_tuning_task(
59 execution_name IN VARCHAR2 := NULL,
60 execution_params IN dbms_advisor.argList := NULL,
61 execution_desc IN VARCHAR2 := NULL)
62 RETURN VARCHAR2;
63 --
64 PROCEDURE execute_auto_tuning_task(
65 execution_name IN VARCHAR2 := NULL,
66 execution_params IN dbms_advisor.argList := NULL,
67 execution_desc IN VARCHAR2 := NULL);
68
69 --------------------------- report_auto_tuning_task -------------------------
70 -- NAME:
71 -- report_auto_tuning_task
72 --
73 -- DESCRIPTION:
74 -- Get a report from the automatic tuning task. This differs from the
75 -- report_tuning_task API in that it takes a range of subtasks to report
76 -- on. NOTE that this API also exists in the DBMS_SQLTUNE package so
77 -- all users with access to the views can see a report.
78 --
79 -- PARAMETERS:
80 -- begin_exec (IN) - name of execution to begin the report from. Null
81 -- to get a report on the most recent run. See
82 -- DBA_ADVISOR_EXECUTIONS.
83 -- end_exec (IN) - name of execution to end the report at. Null to
84 -- get a report on the most recent run. See
85 -- DBA_ADVISOR_EXECUTIONS.
86 -- type (IN) - output type for report, one of:
87 -- TYPE_TEXT: text report
88 -- TYPE_HTML: html report
89 -- level (IN) - level of detail in the report:
90 -- LEVEL_BASIC: simple version of the report.
91 -- Just show info about the actions taken by
92 -- the advisor.
93 -- LEVEL_TYPICAL: show info about every statement
94 -- analyzed, including recs not implemented.
95 -- LEVEL_ALL: verbose report level, also give
96 -- annotations about statements skipped over.
97 -- section (IN) - sections of report to show (comma-separated):
98 -- SECTION_SUMMARY - summary information
99 -- SECTION_FINDINGS - tuning findings
100 -- SECTION_PLAN - explain plans
101 -- SECTION_INFORMATION - general information
102 -- SECTION_ERROR - statements with errors
103 -- SECTION_ALL - all statements
104 -- object_id (IN) - advisor framework object id that represents a
105 -- single statement to restrict reporting to. NULL
106 -- for all statements. Only valid for reports
107 -- that target a single execution.
108 -- result_limit (IN) - maximum number of SQL to show in the report
109 --
110 -- RETURNS:
111 -- CLOB with report content
112 --
113 -- PRIVILEGES:
114 -- Need SELECT privilege on the DBA_ADVISOR views
115 -----------------------------------------------------------------------------
116 FUNCTION report_auto_tuning_task(
117 begin_exec IN VARCHAR2 := NULL,
118 end_exec IN VARCHAR2 := NULL,
119 type IN VARCHAR2 := TYPE_TEXT,
120 level IN VARCHAR2 := LEVEL_TYPICAL,
121 section IN VARCHAR2 := SECTION_ALL,
122 object_id IN NUMBER := NULL,
123 result_limit IN NUMBER := NULL)
124 RETURN CLOB;
125
126 ------------------------ set_auto_tuning_task_parameter ---------------------
127 -- NAME:
128 -- set_auto_tuning_task_parameter - set auto sql tuning task parameter
129 -- value (VARCHAR2 value).
130 --
131 -- DESCRIPTION:
132 -- Similar to set_tuning_task_parameter, but used for the reserved auto
133 -- tuning task. Using this API any user with ADVISOR privilege and
134 -- EXECUTE on this package can set the auto tuning task parameters;
135 -- using dbms_sqltune.set_tuning_task_parameter only SYS can set them.
136 --
137 -- For a description of the parameters that can be set using this API,
138 -- see the comments for dbms_sqltune.set_tuning_task_parameter.
139 --
140 -- PARAMETERS:
141 -- parameter (IN) - name of the parameter to set
142 -- value (IN) - new value of the specified parameter
143 --
144 -- RETURNS:
145 -- NONE
146 --
147 -- EXCEPTIONS:
148 -- To be done
149 -----------------------------------------------------------------------------
150 PROCEDURE set_auto_tuning_task_parameter(
151 parameter IN VARCHAR2,
152 value IN VARCHAR2);
153
154 ------------------------ set_auto_tuning_task_parameter ---------------------
155 -- NAME:
156 -- set_auto_tuning_task_parameter - set auto sql tuning task parameter
157 -- value (NUMBER value).
158 --
159 -- DESCRIPTION:
160 -- Similar to set_tuning_task_parameter, but used for the reserved auto
161 -- tuning task. Using this API any user with ADVISOR privilege and
162 -- EXECUTE on this package can set the auto tuning task parameters;
163 -- using dbms_sqltune.set_tuning_task_parameter only SYS can set them.
164 --
165 -- For a description of the parameters that can be set using this API,
166 -- see the comments for dbms_sqltune.set_tuning_task_parameter.
167 --
168 -- PARAMETERS:
169 -- parameter (IN) - name of the parameter to set
170 -- value (IN) - new value of the specified parameter
171 --
172 -- RETURNS:
173 -- NONE
174 --
175 -- EXCEPTIONS:
176 -- To be done
177 -----------------------------------------------------------------------------
178 PROCEDURE set_auto_tuning_task_parameter(
179 parameter IN VARCHAR2,
180 value IN NUMBER);
181
182 END dbms_auto_sqltune;
[Home] [Help]