1 PACKAGE dbms_sqltune_util1 AS
2
3 -----------------------------------------------------------------------------
4 -- section for constants and global variables --
5 -----------------------------------------------------------------------------
6
7 -- target object ids which are defined in OBJ_XXX_NUM keat constants
8 OBJ_SQL# CONSTANT NUMBER := 7; -- obj
9 OBJ_SQLSET# CONSTANT NUMBER := 8; -- obj
10 OBJ_AUTO_SQLWKLD# CONSTANT NUMBER := 22; -- obj
11 OBJ_SPA_EXEC_PROP# CONSTANT NUMBER := 23; -- SPA exec property
12 OBJ_SPA_TASK# CONSTANT NUMBER := 24; -- SPA task obj
13 OBJ_SPM_EVOLVE_TASK# CONSTANT NUMBER := 25; -- SPM evolve task obj
14
15 -- Execution types
16 -- Names:
17 SQLTUNE CONSTANT VARCHAR2(10) := 'TUNE SQL';
18 TEST_EXECUTE CONSTANT VARCHAR2(12) := 'TEST EXECUTE';
19 EXPLAIN_PLAN CONSTANT VARCHAR2(12) := 'EXPLAIN PLAN';
20 COMPARE CONSTANT VARCHAR2(19) := 'COMPARE PERFORMANCE';
21 STS2TRIAL CONSTANT VARCHAR2(19) := 'CONVERT SQLSET';
22 SQLDIAG CONSTANT VARCHAR2(19) := 'SQL DIAGNOSIS';
23 SPMEVOLVE CONSTANT VARCHAR2(14) := 'SPM EVOLVE';
24
25 -- IDs:
26 SQLTUNE# CONSTANT PLS_INTEGER := 1; /* Sql tuning */
27 EXECUTE# CONSTANT PLS_INTEGER := 2; /* SQL test execute */
28 EXPLAIN# CONSTANT PLS_INTEGER := 3; /* SQL explain plan */
29 SQLDIAG# CONSTANT PLS_INTEGER := 4; /* SQL diagnosis */
30 COMPARE# CONSTANT PLS_INTEGER := 5; /* compare for SQLPA */
31 EVOLVE# CONSTANT PLS_INTEGER := 6; /* SPM Evolve */
32
33 --
34 -- task_wkldobj, task_sqlobj, property_map
35 --
36 -- The task_wkldobj structure stores information about the input to
37 -- a tuning task. We examine it during the parts of the report where
38 -- we need to have different logic depending on the target object. The
39 -- 'props' field is a hashtable mapping property names to values, and the
40 -- 'sql' field defines the current SQL we are operating on. For
41 -- single-statement tasks it is populated with the sql target object.
42 --
43 -- For STSes the workload is the same for all executions so we just load
44 -- it up once. For the automatic sql workload, it is different in each
45 -- execution so we have to refresh the data.
46 --
47 -- We also define constants for valid property names here.
48 TYPE property_map IS TABLE OF VARCHAR2(32767) INDEX BY VARCHAR2(32767);
49 TYPE task_sqlobj IS RECORD(
50 obj_id NUMBER,
51 sql_id VARCHAR2(13),
52 plan_hash_value NUMBER,
53 parsing_schema_name VARCHAR2(30),
54 sql_text CLOB,
55 other_xml CLOB,
56 exec_frequency NUMBER,
57 flags BINARY_INTEGER,
58 con_name VARCHAR2(30),
59 con_dbid NUMBER
60 );
61
62 TYPE task_wkldobj IS RECORD(
63 adv_id NUMBER, -- advisor id#
64 task_name VARCHAR2(30), -- name of the current task
65 type NUMBER, -- one of OBJ_XXX_NUM keat constants
66 obj_id NUMBER, -- object id of target object
67 props property_map, -- (name, value) pairs describing the target
68 cursql task_sqlobj, -- SQL object for the current statement
69 is_cdb BOOLEAN -- checks if this ia cdb env
70 );
71
72 TYPE task_spaobj IS RECORD(
73 exec1_name VARCHAR2(32767), -- the execution name of trial one
74 exec1_type_num NUMBER, -- the execution type of trial one
75 comp_exec_name VARCHAR2(32767), -- compare exec name, max length ?
76 ce_obj_id NUMBER, -- obj id of comp env
77 target_obj_type NUMBER, -- could be SQLSET or SQL
78 target_obj_id NUMBER, -- id of the target object of SPA task
79 wkld task_wkldobj -- has the target obj id
80 );
81
82 -- Constants used as property names in the 'props' hashtable
83
84 -- STS properties
85 PROP_SQLSET_NAME CONSTANT VARCHAR2(30) := 'SQLSET_NAME'; -- sts name
86 PROP_SQLSET_OWNER CONSTANT VARCHAR2(30) := 'SQLSET_OWNER'; -- sts owner
87 PROP_SQLSET_ID CONSTANT VARCHAR2(30) := 'SQLSET_ID'; -- sts id
88 PROP_SQLSET_DESC CONSTANT VARCHAR2(30) := 'SQLSET_DESC'; -- sts desc
89
90 -- Shared properties for multi-statement targets
91 PROP_NB_SQL CONSTANT VARCHAR2(30) := 'NB_STMTS'; -- total #stmts
92 -- (NOT # in rept)
93 PROP_CON_DBID CONSTANT VARCHAR2(30) := 'CON_DBID';
94
95 -- properties for STS2 (compare STS)
96 PROP_SQLSET_NAME2 CONSTANT VARCHAR2(30) := 'SQLSET_NAME2';
97 PROP_SQLSET_OWNER2 CONSTANT VARCHAR2(30) := 'SQLSET_OWNER2';
98 PROP_SQLSET_ID2 CONSTANT VARCHAR2(30) := 'SQLSET_ID2';
99 PROP_SQLSET_DESC2 CONSTANT VARCHAR2(30) := 'SQLSET_DESC2';
100 PROP_NB_SQL2 CONSTANT VARCHAR2(30) := 'NB_STMTS2';
101 PROP_CON_DBID2 CONSTANT VARCHAR2(30) := 'CON_DBID2';
102
103 -- Automatic Workload properties
104 PROP_SUM_ELAPSED CONSTANT VARCHAR2(30) := 'SUM_ELAPSED'; -- sum of elapsed
105
106 -- Single statement properties
107 PROP_SQL_ID CONSTANT VARCHAR2(30) := 'SQL_ID';
108 PROP_PARSING_SCHEMA CONSTANT VARCHAR2(30) := 'PARSING_SCHEMA';
109 PROP_SQL_TEXT CONSTANT VARCHAR2(30) := 'SQL_TEXT';
110 PROP_TUNE_STATS CONSTANT VARCHAR2(30) := 'TUNE_STATS';
111
112 -- Parse modes for query
113 PARSE_MOD_SQLSET CONSTANT VARCHAR2(6) := 'SQLSET' ;
114 PARSE_MOD_AWR CONSTANT VARCHAR2(4) := 'AWR' ;
115 PARSE_MOD_CURSOR CONSTANT VARCHAR2(5) := 'V$SQL' ;
116 PARSE_MOD_CAPCC CONSTANT VARCHAR2(8) := 'V$SQLCAP' ;
117 PARSE_MOD_PROFILE CONSTANT VARCHAR2(10):= 'SQLPROFILE' ;
118 -----------------------------------------------------------------------------
119 -- public utility procedures and functions --
120 -----------------------------------------------------------------------------
121
122 ---------------------------- get_sqlset_identifier --------------------------
123 -- NAME:
124 -- get_sqlset_identifier
125 --
126 -- DESCRIPTION:
127 -- This function gets the SqlSet identifier ginven its name
128 --
129 -- PARAMETERS:
130 -- sts_name (IN) - sqlset name
131 -- sts_owner (IN) - owner of sqlset
132 --
133 -- RETURN:
134 -- The SqlSet id.
135 -----------------------------------------------------------------------------
136 FUNCTION get_sqlset_identifier(sts_name IN VARCHAR2, sts_owner IN VARCHAR2)
137 RETURN NUMBER;
138
139 ---------------------------- get_sqlset_con_dbid --------------------------
140 -- NAME:
141 -- get_sqlset_con_dbid
142 --
143 -- DESCRIPTION:
144 -- This function gets the container DB id given STS id
145 --
146 -- PARAMETERS:
147 -- sts_id (IN) - SQL tuning set id
148 --
149 -- RETURN:
150 -- Container DB id.
151 -----------------------------------------------------------------------------
152 FUNCTION get_sqlset_con_dbid(sts_id IN NUMBER)
153 RETURN NUMBER;
154
155 ----------------------------- get_sqlset_nb_stmts ---------------------------
156 -- NAME:
157 -- get_sqlset_nb_stmts
158 --
159 -- DESCRIPTION:
160 -- This function gets number of SQL statements in a SQL tuning set
161 --
162 -- PARAMETERS:
163 -- sts_id (IN) - SQL tuning set id
164 --
165 -- RETURN:
166 -- Number of SQL in SQL tuning sets.
167 -----------------------------------------------------------------------------
168 FUNCTION get_sqlset_nb_stmts(sts_id IN NUMBER)
169 RETURN NUMBER;
170
171 ------------------------------------ get_view_text --------------------------
172 -- NAME:
173 -- get_view_text
174 --
175 -- DESCRIPTION:
176 -- This function is used to return the text of the sql to capture plans
177 -- given a parse mode
178 --
179 -- PARAMETERS:
180 -- parse_mode (IN) - parsing mode (PARSE_MOD_XXX constants)
181 --
182 -- RETURN:
183 -- plan query text corresponding to the parsing mode
184 -----------------------------------------------------------------------------
185 FUNCTION get_view_text(parse_mode IN VARCHAR2)
186 RETURN VARCHAR2;
187
188 ------------------------------ validate_task_status -------------------------
189 -- NAME:
190 -- validate_task_status: check whether the task status is valid to
191 -- be reported
192 --
193 -- DESCRIPTION:
194 -- A task report cannot be generated if the task status is INITIAL
195 -- or CANCELED
196 --
197 -- PARAMETERS:
198 -- tid (IN) - task identifier
199 --
200 -- RETURN:
201 -- VOID
202 -----------------------------------------------------------------------------
203 PROCEDURE validate_task_status(tid IN NUMBER);
204
205 ----------------------------- get_execution_type ----------------------------
206 -- NAME:
207 -- get_executin_type: get type of a task execution
208 --
209 --
210 -- DESCRIPTION:
211 -- This functin retrieve the type of a given task execution
212 --
213 -- PARAMETERS:
214 -- tid (IN) - task identifier
215 -- ename (IN) - name of the execution
216 --
217 -- RETURN:
218 -- VOID
219 -----------------------------------------------------------------------------
220 FUNCTION get_execution_type(tid VARCHAR2, ename VARCHAR2)
221 RETURN VARCHAR2;
222
223 ------------------------------ init_task_wkldobj ----------------------------
224 -- NAME:
225 -- init_task_wkldobj: initialize the task_wkldobj structure
226 -- specifying the target of this tuning task.
227 --
228 -- DESCRIPTION:
229 -- This procedure initializes our structure of that defines the object
230 -- type of the workload as well as all of its properties. We pass
231 -- it to different functions in the report that need to have logic about
232 -- the input.
233 --
234 -- PARAMETERS:
235 -- tid (IN) - task ID
236 -- begin_exec (IN) - first execution name for the report
237 -- (auto wkld only)
238 -- end_exec (IN) - last execution name for the report
239 -- (auto wkld only)
240 -- target (OUT NOCOPY) - initialized task_wkldobj structure
241 --
242 -- RETURN:
243 -- VOID
244 --
245 -- RAISES:
246 -- NO_DATA_FOUND if the workload object cannot be located
247 -----------------------------------------------------------------------------
248 PROCEDURE init_task_wkldobj(
249 tid IN NUMBER,
250 begin_exec IN VARCHAR2 := NULL,
251 end_exec IN VARCHAR2 := NULL,
252 wkld OUT NOCOPY task_wkldobj);
253
254
255 ---------------------------- init_task_spaobj -------------------------------
256 -- NAME:
257 -- init_task_spaobj: initialize the task_spaobj structure specifying
258 -- the target of this tuning task.
259 --
260 -- DESCRIPTION:
261 -- This procedure initializes our structure of that defines the object
262 -- type of SPA task whose regressions will be tuned by the tuning task.
263 --
264 -- PARAMETERS:
265 -- tid (IN) - task ID
266 -- comp_exec_name (IN) - execution name of compare performance
267 -- trial for the SPA task
268 -- spa_task (OUT NOCOPY) - initialized task_wkldobj structure
269 --
270 -- RETURN:
271 -- VOID
272 --
273 -----------------------------------------------------------------------------
274 PROCEDURE init_task_spaobj(
275 tid IN NUMBER,
276 task_name IN VARCHAR2,
277 comp_exec_name IN VARCHAR2,
278 spa_task OUT NOCOPY task_spaobj);
279
280
281 ------------------------------ get_wkldtype_name ----------------------------
282 -- NAME:
283 -- get_wkldtype_name
284 --
285 -- DESCRIPTION:
286 -- This function returns the string version of the workload type
287 -- number.
288 --
289 -- PARAMETERS:
290 -- type_num (IN) - OBJ_XXX# constant
291 --
292 -- RETURN:
293 -- Workload type name
294 -----------------------------------------------------------------------------
295 FUNCTION get_wkldtype_name(type_num IN NUMBER)
296 RETURN VARCHAR2;
297
298 --------------------------------- validate_name -----------------------------
299 -- NAME:
300 -- validate_name
301 --
302 -- DESCRIPTION:
303 -- This function checks whether a given name (e.g., sqlset name) is valid
304 -- A name must not. It is just a syntactic checker, i.e. it does not
305 -- check to see if the object actually exists.
306 --
307 -- PARAMETERS:
308 -- name (IN) - a given name
309 --
310 -- RETURN:
311 -- VOID.
312 --
313 -- EXCEPTIONS
314 -- TO BE DONE
315 ----------------------------------------------------------------------------
316 PROCEDURE validate_name(name IN VARCHAR2);
317
318 -------------------------- alter_session_parameter -------------------------
319 -- NAME:
320 -- alter_session_parameter
321 --
322 -- DESCRIPTION:
323 -- This function sets the indicated parameter to a hardcoded value
324 -- if it is currently different, and returns a boolean value indicating
325 -- whether or not the value had to be changed.
326 --
327 -- It is designed to be pretty generic so we can use it for different
328 -- parameters but not so generic to cause SQL injections. Right now
329 -- it won't work for anything more than a simple on/off value.
330 -- Values are hardcoded because for the simple boolean scenario it is
331 -- unlikely that we would need to change a session parameter to have
332 -- different values. Typical usage model is as follows:
333 --
334 -- prm_set := alter_session_parameter(PNUM_XXX);
335 --
336 -- ...
337 --
338 -- if (prm_set) then
339 -- restore_session_parameter(PNUM_XXX);
340 -- end if;
341 --
342 -- PARAMETERS:
343 -- pnum (IN) - parameter number as PNUM_XXX constant
344 -- PNUM_SYSPLS_OBEY_FORCE: set _parallel_syspls_obey_force to FALSE
345 --
346 --
347 -- RETURN:
348 -- TRUE if the parameter value needed to be changed
349 ----------------------------------------------------------------------------
350 PNUM_SYSPLS_OBEY_FORCE CONSTANT NUMBER := 1; -- change from TRUE to FALSE
351
352 FUNCTION alter_session_parameter(pnum IN NUMBER)
353 RETURN BOOLEAN;
354
355 -------------------------- restore_session_parameter -----------------------
356 -- NAME:
357 -- restore_session_parameter
358 --
359 -- DESCRIPTION:
360 -- This function follows up on a call to set_session_parameter by
361 -- clearing it back to its initial value. It should only be called
362 -- when the set function returns TRUE indicating the value was changed.
363 --
364 -- PARAMETERS:
365 -- pnum (IN) - parameter number as PNUM_XXX constant
366 --
367 -- RETURN:
368 -- NONE
369 ----------------------------------------------------------------------------
370 PROCEDURE restore_session_parameter(pnum IN NUMBER);
371
372 ------------------------------- get_current_time ---------------------------
373 -- NAME:
374 -- get_current_time
375 --
376 -- DESCRIPTION:
377 -- Just a wrapper around ksugctm().
378 --
379 -- PARAMETERS:
380 -- None
381 --
382 -- RETURN:
383 -- current time (from ksugctm) as DATE
384 ----------------------------------------------------------------------------
385 FUNCTION get_current_time
386 RETURN DATE;
387
388 ----------------------------- get_dbid_from_conid ---------------------------
389 --
390 -- NAME:
391 -- get_dbid_from_conid - Get con Dbid From Conid
392 --
393 -- DESCRIPTION:
394 -- This function returns the container dbid for a container id. If not in
395 -- in a cdb environment, it simply returns the dbid from v$database.
396 --
397 -- PARAMETERS:
398 -- con_id (IN) - (REQUIRED) CDB container id
399 --
400 -- RETURNS:
401 -- con_dbid for the given con_id
402 -----------------------------------------------------------------------------
403 FUNCTION get_dbid_from_conid(con_id IN PLS_INTEGER)
404 RETURN NUMBER;
405
406 --------------------------- is_running_fake_cc_test -------------------------
407 -- NAME:
408 -- is_running_fake_cc_test: check if we are running the fake cc tests
409 --
410 --
411 -- DESCRIPTION:
412 -- Determine from _sta_control, if we are running fake cursor cache
413 -- tests. The capture sts queries are parsed differently for those tests.
414 --
415 -- PARAMETERS:
416 -- MONE
417 --
418 -- RETURN:
419 -- TRUE if we are running fake cc tests, FALSE otherwise
420 ----------------------------------------------------------------------------
421 FUNCTION is_running_fake_cc_test
422 RETURN BOOLEAN;
423
424 END dbms_sqltune_util1;
[Home] [Help]