[Home] [Help]
PACKAGE BODY: APPS.HR_TKPROF_PLUS
Source
1 PACKAGE BODY hr_tkprof_plus AS
2 /* $Header: hrtkplus.pkb 120.0.12020000.2 2012/11/29 14:56:42 avarri ship $ */
3 -- define global vars
4 g_version CONSTANT VARCHAR2(30) := '115.1'; -- version
5 -- define global log variables
6 g_log_file_extention CONSTANT VARCHAR2(4) := '.cbo'; -- log file extension
7 g_script_file_extention CONSTANT VARCHAR2(4) := '.sql'; -- script file extension
8 g_log_level PLS_INTEGER; -- log level
9 g_log_file UTL_FILE.file_type; -- log file handle
10 g_log_filename VARCHAR2(120); -- log filename
11 g_script_filename VARCHAR2(120); -- script filename
12 g_filename VARCHAR2(120); -- tkprof filename
13 g_script_file UTL_FILE.file_type; -- script file handle
14 g_log_line_separator CONSTANT VARCHAR2(80) := LPAD('-',78,'-');
15 -- define global table structures
16 g_table_counter BINARY_INTEGER;
17 g_backup_stats VARCHAR2(1);
18 g_script_stats VARCHAR2(1);
19 TYPE g_table_owner_table_type IS TABLE OF VARCHAR2(61) INDEX BY BINARY_INTEGER;
20 TYPE g_table_owner_status_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
21 g_table_owner_table g_table_owner_table_type;
22 g_table_owner_status_table g_table_owner_status_type;
23 g_table_name_table g_table_owner_table_type;
24 --
25 g_stat_table_name CONSTANT VARCHAR2(30) := 'FND_STATTAB';
26 g_explain_sql_counter PLS_INTEGER;
27 g_explained_sql_counter PLS_INTEGER;
28 g_not_explained_sql_counter PLS_INTEGER;
29 g_explain_sql_statement_text CONSTANT VARCHAR2(10) := 'expstmtid:';
30 g_explain_sql_text_header CONSTANT VARCHAR(50) := 'EXPLAIN PLAN SET '||
31 'STATEMENT_ID=''' ||
32 g_explain_sql_statement_text;
33 g_explain_table VARCHAR2(61);
34 g_sql_text VARCHAR2(32767);
35 g_error_text VARCHAR2(2000);
36 g_error_std_text CONSTANT VARCHAR2(45) := 'Unexpected internal error has '||
37 'occurred in ';
38 -- ---------------------------------------------------------------------------------------
39 -- procedure:
40 -- write_log
41 -- description:
42 -- write out the log text (p_text) to the log file if tge log level has been set at the
43 -- correct level
44 -- ---------------------------------------------------------------------------------------
45 PROCEDURE write_log(p_text IN VARCHAR2 DEFAULT NULL
46 ,p_level IN PLS_INTEGER DEFAULT 1
47 ,p_file IN UTL_FILE.file_type DEFAULT g_log_file) IS
48 BEGIN
49 IF g_log_level > 0 AND p_level <= g_log_level THEN
50 IF p_text IS NULL THEN
51 -- output new line
52 UTL_FILE.new_line(file => p_file, lines => 1);
53 ELSE
54 -- output the log line
55 UTL_FILE.put_line(file => p_file, buffer => p_text);
56 END IF;
57 END IF;
58 EXCEPTION
59 WHEN OTHERS THEN
60 -- an internal error has occurred
61 g_error_text := g_error_std_text||'write_log: '||
62 TO_CHAR(SQLCODE)||': '||SQLERRM;
63 RAISE;
64 END write_log;
65 -- ---------------------------------------------------------------------------------------
66 -- procedure:
67 -- delete_all_expstms
68 -- description:
69 -- deletes previously explained SQL stmts for the tkrpof file and commits if set.
70 -- ---------------------------------------------------------------------------------------
71 PROCEDURE delete_all_expstms(p_commit IN BOOLEAN DEFAULT FALSE) IS
72 BEGIN
73 -- delete all the explan plans which have been previously generated
74 -- by this utility
75 EXECUTE IMMEDIATE 'DELETE FROM '||g_explain_table||' pt WHERE pt.statement_id LIKE '''||
76 g_explain_sql_statement_text || '%''';
77 -- check to see if the delete is to be committed
78 IF p_commit THEN
79 COMMIT;
80 END IF;
81 EXCEPTION
82 WHEN OTHERS THEN
83 -- an internal error has occurred
84 g_error_text := g_error_std_text||'delete_all_expstms: '||
85 TO_CHAR(SQLCODE)||': '||SQLERRM;
86 RAISE;
87 END delete_all_expstms;
88 -- ---------------------------------------------------------------------------------------
89 -- procedure:
90 -- export_table_stats
91 -- description:
92 -- get a list of unique table names from the execution plans for the trace file and
93 -- exports the stats
94 -- ---------------------------------------------------------------------------------------
95 PROCEDURE export_table_stats(p_statid IN VARCHAR2 DEFAULT NULL) IS
96 --
97 TYPE l_cursor_type IS REF CURSOR;
98 l_cursor l_cursor_type;
99 l_table_owner VARCHAR2(30);
100 l_table_name VARCHAR2(30);
101 --
102 BEGIN
103 IF g_backup_stats = 'Y' THEN
104 -- before we export the stats to the stats table
105 -- delete any previously created stats for the same
106 -- statid
107 EXECUTE IMMEDIATE 'DELETE FROM '||
108 g_stat_table_name||
109 ' st WHERE st.statid = :statid' USING p_statid;
110 END IF;
111 -- open the dynamic cursor
112 OPEN l_cursor FOR
113 'SELECT di.table_owner table_owner, '||
114 'di.table_name table_name '||
115 'FROM '||g_explain_table||' pt, dba_indexes di '||
116 'WHERE pt.statement_id LIKE ''exp%'' '||
117 'AND pt.object_owner NOT IN (''SYS'',''SYSTEM'') '||
118 'AND pt.object_type IN (''UNIQUE'',''NON-UNIQUE'') '||
119 'AND di.index_name = pt.object_name '||
120 'AND di.owner = pt.object_owner '||
121 'UNION '||
122 'SELECT dt.owner table_owner, '||
123 'dt.table_name table_name '||
124 'FROM '||g_explain_table||' pt, dba_tables dt '||
125 'WHERE pt.statement_id LIKE ''exp%'' '||
126 'AND pt.object_type IS NULL '||
127 'AND dt.table_name = pt.object_name '||
128 'AND dt.owner = pt.object_owner '||
129 'AND pt.object_owner NOT IN (''SYS'',''SYSTEM'') '||
130 'ORDER BY 1,2';
131 LOOP
132 -- fetch each row
133 FETCH l_cursor INTO l_table_owner, l_table_name;
134 EXIT WHEN l_cursor%NOTFOUND;
135 -- increment the table counter
136 g_table_counter := g_table_counter + 1;
137 -- add the owner,table name
138 g_table_owner_table(g_table_counter) := l_table_owner;
139 g_table_name_table(g_table_counter) := l_table_name;
140 -- are the stats to be backed up?
141 IF g_backup_stats = 'Y' THEN
142 --
143 BEGIN
144 -- backup the stats for the table
145 fnd_stats.backup_table_stats
146 (schemaname => l_table_owner,
147 tabname => l_table_name,
148 statid => p_statid,
149 cascade => TRUE);
150 -- set the table status to Y
151 g_table_owner_status_table(g_table_counter) := 'Y';
152 EXCEPTION
153 WHEN OTHERS THEN
154 -- an error has occurred during gathering the stats so set the
155 -- status to N but continue processing
156 g_table_owner_status_table(g_table_counter) := 'N';
157 END;
158 ELSE
159 -- set status to N
160 g_table_owner_status_table(g_table_counter) := 'N';
161 END IF;
162 END LOOP;
163 CLOSE l_cursor;
164 EXCEPTION
165 WHEN OTHERS THEN
166 IF l_cursor%ISOPEN THEN
167 CLOSE l_cursor;
168 END IF;
169 -- an internal error has occurred
170 g_error_text := g_error_std_text||'export_table_stats: '||
171 TO_CHAR(SQLCODE)||': '||SQLERRM;
172 RAISE;
173 END export_table_stats;
174 -- ---------------------------------------------------------------------------------------
175 -- procedure:
176 -- check_explain_table
177 -- description:
178 -- dynamically checks to see if the plan table exists
179 -- ---------------------------------------------------------------------------------------
180 PROCEDURE check_explain_table IS
181 l_dummy NUMBER(1);
182 BEGIN
183 EXECUTE IMMEDIATE 'SELECT 1 FROM SYS.DUAL WHERE EXISTS (SELECT 1 FROM '||
184 g_explain_table||')' INTO l_dummy;
185 EXCEPTION
186 WHEN NO_DATA_FOUND THEN
187 -- the plan table is empty but exists so ignore error
188 NULL;
189 WHEN OTHERS THEN
190 -- plan table does exist so error
191 g_error_text := 'The Plan Table '||g_explain_table||
192 ' does not exist - please create it';
193 RAISE;
194 END check_explain_table;
195 -- ---------------------------------------------------------------------------------------
196 -- procedure:
197 -- explain_sql
198 -- description:
199 -- dynamically explains the sql text
200 -- ---------------------------------------------------------------------------------------
201 PROCEDURE explain_sql(p_sql_text IN VARCHAR2) IS
202 --
203 TYPE l_cursor_type IS REF CURSOR;
204 l_cursor l_cursor_type;
205 l_exp_line VARCHAR2(32767);
206 --
207 BEGIN
208 write_log(p_level => 1);
209 -- build the explain sql statement header in the following format:
210 -- EXPLAIN PLAN SET STATEMENT_ID='expstmtid:<g_explain_sql_counter>' FOR <p_sql_text>
211 -- and execute immediately
212 -- execute the sql statment to be explained
213 EXECUTE IMMEDIATE g_explain_sql_text_header ||g_explain_sql_counter|| ''' INTO '||
214 g_explain_table||' FOR ' ||p_sql_text;
215 -- increment the explained counter
216 g_explained_sql_counter := g_explained_sql_counter + 1;
217 -- output the explain plan to the log file
218 write_log(p_text => 'Explain statement_id = '||
219 g_explain_sql_statement_text||
220 g_explain_sql_counter,
221 p_level => 1);
222 write_log(p_level => 1);
223 -- write out the explain plan
224 OPEN l_cursor FOR 'SELECT LPAD('' '',2*(LEVEL-1))||operation||'||
225 'DECODE(options, NULL,'''','' ''||'||
226 'options)||'' ''||object_name||'' (cost=''||'||
227 'cost||'', card=''||cardinality||'', bytes=''||bytes'||
228 '||'')'' exp_line '||
229 'FROM '||g_explain_table||' START WITH id=0 AND '||
230 'statement_id = :c_statement_id '||
231 'CONNECT BY PRIOR id = parent_id '||
232 'AND PRIOR NVL(statement_id, '' '') = NVL(statement_id, '' '') '||
233 'AND PRIOR timestamp <= timestamp'
234 USING g_explain_sql_statement_text||g_explain_sql_counter;
235 LOOP
236 -- fetch each explain line
237 FETCH l_cursor INTO l_exp_line;
238 EXIT WHEN l_cursor%NOTFOUND;
239 -- write out the execution line
240 write_log(p_text => l_exp_line, p_level => 1);
241 END LOOP;
242 CLOSE l_cursor;
243 write_log(p_level => 1);
244 EXCEPTION
245 -- if an error has occurred during explaining, ignore it, setting the
246 -- explained status to false
247 WHEN OTHERS THEN
248 IF l_cursor%ISOPEN THEN
249 CLOSE l_cursor;
250 END IF;
251 write_log(p_text => 'Error explaining:'||TO_CHAR(SQLCODE)||', '||SQLERRM,p_level => 1);
252 write_log(p_level => 1);
253 g_not_explained_sql_counter := g_not_explained_sql_counter + 1;
254 END explain_sql;
255 -- ---------------------------------------------------------------------------------------
256 -- function:
257 -- get_line
258 -- description:
259 -- gets a line from the file
260 -- ---------------------------------------------------------------------------------------
261 FUNCTION get_line(
262 p_file IN UTL_FILE.file_type)
263 RETURN VARCHAR2 IS
264 l_buffer VARCHAR2(32767);
265 BEGIN
266 -- get a line from the file
267 UTL_FILE.get_line(file => p_file, buffer => l_buffer);
268 RETURN (l_buffer);
269 EXCEPTION
270 WHEN OTHERS THEN
271 -- an internal error has occurred
272 g_error_text := g_error_std_text||'get_line: '||
273 TO_CHAR(SQLCODE)||': '||SQLERRM;
274 END get_line;
275 -- ---------------------------------------------------------------------------------------
276 -- function:
277 -- sql_reserved_word
278 -- description:
279 -- determines if a sql reserve word is at position 1 in the specified string
280 -- ---------------------------------------------------------------------------------------
281 FUNCTION sql_reserved_word(
282 p_text IN VARCHAR2)
283 RETURN BOOLEAN IS
284 l_text VARCHAR2(32767) := UPPER(p_text);
285 BEGIN
286 IF INSTR(l_text, 'SELECT') = 1
287 OR INSTR(l_text, 'INSERT') = 1
288 OR INSTR(l_text, 'UPDATE') = 1
289 OR INSTR(l_text, 'DELETE') = 1 THEN
290 RETURN (TRUE);
291 END IF;
292 RETURN (FALSE);
293 EXCEPTION
294 WHEN OTHERS THEN
295 -- an internal error has occurred
296 g_error_text := g_error_std_text||'sql_reserved_word: '||
297 TO_CHAR(SQLCODE)||': '||SQLERRM;
298 END sql_reserved_word;
299 -- ---------------------------------------------------------------------------------------
300 -- procedure:
301 -- process_tkprof_file
302 -- description:
303 -- processes the tkprof file extracting out the SQL stmts.
304 -- ---------------------------------------------------------------------------------------
305 PROCEDURE process_tkprof_file(
306 p_file IN UTL_FILE.file_type,
307 p_limit IN PLS_INTEGER) IS
308 --
309 l_tkprof_line VARCHAR2(32767);
310 l_sql_found BOOLEAN := FALSE;
311 l_sql_text VARCHAR2(32767);
312 --
313 BEGIN
314 -- process TKPROF file
315 <<get_lines>>
316 LOOP
317 BEGIN
318 -- get next line from tkprof
319 l_tkprof_line := get_line(p_file);
320 -- check to see if line is null and sql not found
321 IF l_tkprof_line IS NULL
322 AND (NOT l_sql_found) THEN
323 -- get next line to see if a SQL stmt reserved word exists
324 l_tkprof_line := get_line(p_file);
325 IF sql_reserved_word(p_text => l_tkprof_line) THEN
326 -- start of sql stmt has been found in the TKPROF file
327 l_sql_found := TRUE;
328 l_sql_text := l_tkprof_line;
329 -- incremenet the explain counter
330 g_explain_sql_counter := g_explain_sql_counter + 1;
331 -- write out the SQL stmt to the log file
332 write_log(p_text => g_log_line_separator, p_level => 1);
333 write_log(p_text => 'SQL stmt:'||g_explain_sql_counter, p_level => 1);
334 write_log(p_level => 1);
335 write_log(p_text => l_tkprof_line, p_level => 1);
336 END IF;
337 -- check to see if the sql stmt is terminated
338 ELSIF l_tkprof_line IS NULL
339 AND l_sql_found THEN
340 -- sql stmt terminated
341 l_sql_found := FALSE;
342 -- explain the SQL stmt
343 explain_sql(p_sql_text => l_sql_text);
344 -- check to see if the limit has been reached
345 IF g_explain_sql_counter = p_limit THEN
346 -- limit reached, terminate the processing
347 EXIT get_lines;
348 END IF;
349 -- check to see if we are processing a sql stmt line
350 ELSIF l_tkprof_line IS NOT NULL
351 AND l_sql_found THEN
352 l_sql_text := l_sql_text || ' ' || l_tkprof_line;
353 write_log(p_text => l_tkprof_line, p_level => 1);
354 END IF;
355 EXCEPTION
356 WHEN OTHERS THEN
357 EXIT get_lines;
358 END;
359 END LOOP;
360 END process_tkprof_file;
361 -- ---------------------------------------------------------------------------------------
362 -- procedure:
363 -- show_header
364 -- description:
365 -- writes out to the log file if the log level is greater or equal to 1 the log header
366 -- information which includes CBO INIT.ora parameter values
367 -- ---------------------------------------------------------------------------------------
368 PROCEDURE show_header
369 (p_location IN VARCHAR2,
370 p_filename IN VARCHAR2,
371 p_limit IN PLS_INTEGER) IS
372 --
373 TYPE l_cursor_type IS REF CURSOR;
374 l_cursor l_cursor_type;
375 l_name VARCHAR2(64);
376 l_value VARCHAR2(512);
377 l_dvalue VARCHAR2(512);
378 l_banner VARCHAR2(64);
382 IF g_log_level >= 1 THEN
379 l_run_date VARCHAR2(22) := TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS');
380 --
381 BEGIN
383 -- output the log file header info
384 write_log(g_log_line_separator);
385 write_log;
386 write_log(p_text => 'TKPROF PLUS');
387 write_log(p_text => '-----------');
388 write_log(p_text => 'tkprof plus version : '||g_version);
389 write_log(p_text => 'tkprof file location : '||p_location);
390 write_log(p_text => 'tkprof file to process : '||p_filename);
391 write_log(p_text => 'No of SQL Stmts to process: '||NVL(TO_CHAR(p_limit),'ALL'));
392 write_log(p_text => 'Backup stats? : '||NVL(g_backup_stats,'N'));
393 write_log(p_text => 'Script stats? : '||NVL(g_script_stats,'N'));
394 IF g_script_stats = 'Y' THEN
395 write_log(p_text => 'Script file : '||g_script_filename);
396 END IF;
397 write_log(p_text => 'Logging level : '||g_log_level);
398 write_log(p_text => 'Log file : '||g_log_filename);
399 write_log(p_text => 'Date and Time of run : '||l_run_date);
400 --
401 write_log;
402 write_log(p_text => 'Version numbers of core library components in the Oracle server');
403 write_log(p_text => '---------------------------------------------------------------');
404 -- use native dynamic SQL because the USER may not have access to V$VERSION table
405 BEGIN
406 OPEN l_cursor FOR 'SELECT banner FROM v$version';
407 LOOP
408 -- fetch the version info
409 FETCH l_cursor INTO l_banner;
410 EXIT WHEN l_cursor%NOTFOUND;
411 -- write out the version info
412 write_log(p_text => l_banner);
413 END LOOP;
414 CLOSE l_cursor;
415 EXCEPTION
416 WHEN OTHERS THEN
417 -- user doesn't have access to the v$version table
418 write_log(p_text => 'Version information not available');
419 IF l_cursor%ISOPEN THEN
420 CLOSE l_cursor;
421 END IF;
422 END;
423 write_log;
424 --
425 write_log(p_text => 'CBO INIT.ora parameters');
426 write_log(p_text => '-----------------------');
427 -- use native dynamic SQL because PL/SQL does not support the use of sub-query functionality
428 -- in a select list
429 OPEN l_cursor FOR
430 'SELECT name pname,'||
431 'value pvalue'||
432 ',decode(name,'||
433 '''_sort_elimination_cost_ratio'', decode(value,''5'',''OK'',''RECOMMEND => 5''),'||
434 '''_optimizer_mode_force'', decode(value,''TRUE'',''OK'', ''RECOMMEND => TRUE''),'||
435 '''_fast_full_scan_enabled'', decode(value,''FALSE'',''OK'', ''RECOMMEND => FALSE''),'||
436 '''_ordered_nested_loop'', decode(value,''TRUE'',''OK'', ''RECOMMEND => TRUE''),'||
437 '''_complex_view_merging'', decode(value,''TRUE'',''OK'', ''RECOMMEND => TRUE''),'||
438 '''_push_join_predicate'', decode(value,''TRUE'',''OK'', ''RECOMMEND => TRUE''),'||
439 '''_use_column_stats_for_function'', decode(value,''TRUE'',''OK'', ''RECOMMEND => TRUE''),'||
440 '''_push_join_union_view'', decode(value,''TRUE'',''OK'', ''RECOMMEND => TRUE''),'||
441 '''_like_with_bind_as_equality'', decode(value,''TRUE'',''OK'', ''RECOMMEND => TRUE''),'||
442 '''_or_expand_nvl_predicate'', decode(value,''TRUE'',''OK'', ''RECOMMEND => TRUE''),'||
443 '''_table_scan_cost_plus_one'', decode(value,''TRUE'',''OK'', ''RECOMMEND => TRUE''),'||
444 '''_optimizer_undo_changes'', decode(value,''FALSE'',''OK'', ''RECOMMEND => FALSE''),'||
445 '''db_file_multiblock_read_count'', decode(value,''8'',''OK'', ''RECOMMEND => 8''),'||
446 '''optimizer_max_permutations'', decode(value,''79000'',''OK'', ''RECOMMEND => 79000''),'||
447 '''optimizer_mode'', decode(value,''CHOOSE'',''OK'', ''RECOMMEND => CHOOSE''),'||
448 '''optimizer_percent_parallel'', decode(value,''0'',''OK'', ''RECOMMEND => 0''),'||
449 '''optimizer_features_enable'', decode(value,''8.1.6'',''OK'', ''RECOMMEND => 8.1.6''),'||
450 '''query_rewrite_enabled'', decode(value,''TRUE'',''OK'', ''RECOMMEND => TRUE''),'||
451 '''compatible'', decode(value,''8.1.6'',''OK'', ''RECOMMEND => 8.1.6''),'||
452 '''optimizer_index_caching'', decode(value,''0'',''OK'', ''RECOMMEND => 0''),'||
453 '''optimizer_index_cost_adj'', decode(value,''100'',''OK'', ''RECOMMEND => 100''),'||
454 '''hash_area_size'', DECODE((SELECT TO_CHAR(TO_NUMBER(v1.value) * 2) '||
455 'FROM v$parameter v1 '||
456 'WHERE v1.name = ''sort_area_size''),'||
457 'value,''OK'',''RECOMMEND => (2*sort area size)''),'||
458 '''sort_area_size'', DECODE((SELECT ''Y'' '||
459 'FROM SYS.DUAL '||
460 'WHERE TO_NUMBER(value) '||
461 'BETWEEN 256000 AND 2000000), '||
462 '''Y'',''OK'',''RECOMMEND => ( >= 256k <= 2M)''), '||
463 ''' '') pdvalue '||
464 'FROM v$parameter '||
465 'WHERE name IN (''_sort_elimination_cost_ratio'','||
466 '''_optimizer_mode_force'','||
467 '''_fast_full_scan_enabled'','||
468 '''_ordered_nested_loop'','||
469 '''_complex_view_merging'','||
470 '''_push_join_predicate'','||
471 '''_use_column_stats_for_function'','||
472 '''_push_join_union_view'','||
473 '''_like_with_bind_as_equality'','||
474 '''_or_expand_nvl_predicate'','||
475 '''_table_scan_cost_plus_one'','||
479 '''optimizer_mode'','||
476 '''_optimizer_undo_changes'','||
477 '''db_file_multiblock_read_count'','||
478 '''optimizer_max_permutations'','||
480 '''optimizer_percent_parallel'','||
481 '''optimizer_features_enable'','||
482 '''query_rewrite_enabled'','||
483 '''compatible'','||
484 '''db_block_size'','||
485 '''optimizer_index_caching'','||
486 '''optimizer_index_cost_adj'','||
487 '''timed_statistics'','||
488 '''sort_area_size'','||
489 '''sort_multi_block_read_count'','||
490 '''hash_join_enabled'','||
491 '''hash_area_size'')'||
492 ' ORDER BY 1';
493 LOOP
494 -- fetch each INIT.ora parameter
495 FETCH l_cursor INTO l_name,l_value,l_dvalue;
496 EXIT WHEN l_cursor%NOTFOUND;
497 -- write out the INIT.ora parameter
498 write_log(RPAD(l_name,30)||RPAD(l_value,15)||l_dvalue);
499 END LOOP;
500 CLOSE l_cursor;
501 write_log;
502 --
503 IF g_script_stats = 'Y' AND g_log_level = 2 THEN
504 write_log(p_text =>'DECLARE'
505 ,p_level => 2
506 ,p_file => g_script_file);
507 write_log(p_text =>' -- This anonymous PL/SQL block has been generated from '||
508 'HR_TKPROF_PLUS'
509 ,p_level => 2
510 ,p_file => g_script_file);
511 write_log(p_text =>' -- and will import table, column and index statistics '||
512 'into the '||g_stat_table_name
513 ,p_level => 2
514 ,p_file => g_script_file);
515 write_log(p_text =>' -- table with the statid of '||g_filename
516 ,p_level => 2
517 ,p_file => g_script_file);
518 write_log(p_text =>' -- The statistics where gathered on '||l_run_date
519 ,p_level => 2
520 ,p_file => g_script_file);
521 write_log(p_text => ' l_st VARCHAR2(30) := '''||g_stat_table_name||'''; -- stat table name'
522 ,p_level => 2
523 ,p_file => g_script_file);
524 write_log(p_text => ' l_stid VARCHAR2(30) := '''||g_filename||'''; -- statid'
525 ,p_level => 2
526 ,p_file => g_script_file);
527 write_log(p_text => 'BEGIN'
528 ,p_level => 2
529 ,p_file => g_script_file);
530 END IF;
531 END IF;
532 EXCEPTION
533 WHEN OTHERS THEN
534 -- an internal error has occurred
535 g_error_text := g_error_std_text||'show_header: '||
536 TO_CHAR(SQLCODE)||': '||SQLERRM;
537 -- check to ensure the cursor is closed
538 IF l_cursor%ISOPEN THEN
539 CLOSE l_cursor;
540 END IF;
541 END show_header;
542 -- ---------------------------------------------------------------------------------------
543 -- procedure:
544 -- show_stats
545 -- description:
546 -- writes out to the log file the table/column and index stats if the log level is 2.
547 -- ---------------------------------------------------------------------------------------
548 PROCEDURE show_stats IS
549 --
550 TYPE l_cursor_type IS REF CURSOR;
551 l_cursor l_cursor_type;
552 l_column_name VARCHAR2(30);
553 l_nd NUMBER;
554 l_es NUMBER;
555 l_ec NUMBER;
556 l_nn NUMBER;
557 l_d NUMBER;
558 l_h VARCHAR2(1);
559 l_acl NUMBER;
560 --
561 CURSOR csr_table(c_owner VARCHAR2, c_table_name VARCHAR2) IS
562 SELECT db.table_name table_name,
563 TO_CHAR(db.num_rows,999999999999) num_rows,
564 TO_CHAR(db.blocks,999999) blocks,
565 TO_CHAR(db.empty_blocks,9999999999) empty_blocks,
566 TO_CHAR(db.avg_row_len,99999999999) arl,
567 TO_CHAR(db.chain_cnt,9999999) chcnt,
568 TO_CHAR(db.last_analyzed, 'DD-MON-YYYY HH24:MI:SS') la
569 FROM dba_tables db
570 WHERE db.table_name = c_table_name
571 AND db.owner = c_owner;
572 --
573 CURSOR csr_index(c_owner VARCHAR2, c_table_name VARCHAR2) IS
574 SELECT di.index_name index_name,
575 TO_CHAR(di.num_rows,9999999999) num_rows,
576 TO_CHAR(di.distinct_keys,9999999999) dk,
577 TO_CHAR((1/di.distinct_keys),9.99999999) s,
578 TO_CHAR((di.num_rows/di.distinct_keys),9999999999) ec,
579 TO_CHAR(di.leaf_blocks,999999) lb,
580 TO_CHAR(di.clustering_factor,9999999) cf,
581 TO_CHAR(di.blevel,999999) bl,
582 TO_CHAR(di.last_analyzed, 'DD/MM/YYYY HH24:MI:SS') la,
583 di.avg_leaf_blocks_per_key albpk,
584 di.avg_data_blocks_per_key adbpk
585 FROM dba_indexes di
586 WHERE di.table_name = c_table_name
587 AND di.table_owner = c_owner
588 AND di.owner = c_owner
589 AND di.num_rows > 0
590 ORDER BY DECODE(di.uniqueness,'UNIQUE',1,2), 1;
591 --
592 CURSOR csr_index_cols(c_owner VARCHAR2, c_table_name VARCHAR2) IS
593 SELECT c.index_name index_name,
594 i.uniqueness uniqueness,
595 SUBSTR(c.column_name,1,30) column_name,
596 c.column_position column_position
597 FROM dba_ind_columns c,
598 dba_indexes i
599 WHERE i.table_name = c_table_name
600 AND i.table_owner = c_owner
604 ORDER BY c.table_name,
601 AND i.owner = c_owner
602 AND c.index_name = i.index_name
603 AND c.index_owner = i.owner
605 DECODE(i.uniqueness,'UNIQUE',1,2),
606 c.index_name,
607 c.column_position ASC;
608 --
609 BEGIN
610 IF g_log_level = 2 THEN
611 FOR i IN g_table_owner_table.FIRST..g_table_owner_table.LAST LOOP
612 -- write out the table stats
613 write_log(p_level => 2);
614 write_log(LPAD('=',130,'=')
615 ,p_level => 2);
616 write_log(p_level => 2);
617 write_log(p_text => RPAD('Table Name',30)||
618 LPAD('Num Rows',13)||
619 LPAD('Blocks',7)||
620 LPAD('Empty Blks',11)||
621 LPAD('Avg Row Len',13)||
622 LPAD('Chained',8)||
623 LPAD('Last Analyzed',21)
624 ,p_level => 2);
625 write_log(p_text => RPAD('-',30,'-')||' '||
626 LPAD('-',12,'-')||' '||
627 LPAD('-',6,'-')||' '||
628 LPAD('-',10,'-')||' '||
629 LPAD('-',12,'-')||' '||
630 LPAD('-',7,'-')||' '||
631 LPAD('-',20,'-')
632 ,p_level => 2);
633 -- write the table stats out
634 -- n.b: used a for loop even though only returning one row to
635 -- save on var usage declaration (being lazy really!)
636 FOR j IN csr_table(g_table_owner_table(i),g_table_name_table(i)) LOOP
637 write_log(p_text => RPAD(j.table_name,30)||
638 LPAD(j.num_rows,13)||
639 LPAD(j.blocks,7)||
640 LPAD(j.empty_blocks,11)||
641 LPAD(j.arl,13)||
642 LPAD(j.chcnt,8)||' '||
643 j.la
644 ,p_level => 2);
645 IF g_script_stats = 'Y' THEN
646 write_log(p_text => ' -- set the table, column and index stats for table '||j.table_name
647 ,p_level => 2
648 ,p_file => g_script_file);
649 write_log(p_text => ' DBMS_STATS.SET_TABLE_STATS'
650 ,p_level => 2
651 ,p_file => g_script_file);
652 write_log(p_text => ' (ownname=>'''||g_table_owner_table(i)||
653 ''',tabname=>'''||j.table_name||
654 ''',stattab=>l_st'||
655 ',statid=>l_stid'||
656 ',numrows=>'||TO_CHAR(TO_NUMBER(j.num_rows))||
657 ',numblks=>'||TO_CHAR(TO_NUMBER(j.blocks))||
658 ',avgrlen=>'||TO_CHAR(TO_NUMBER(j.arl))||');'
659 ,p_level => 2
660 ,p_file => g_script_file);
661 END IF;
662 END LOOP;
663 -- write out column stats header
664 write_log(p_level => 2);
665 write_log(p_text => RPAD('Column Name',30)||
666 LPAD('NDV',13)||
667 LPAD('1/NDV',11)||
668 LPAD('Cardinality',12)||
669 LPAD('Num Of Nulls',13)||
670 LPAD('Density',8)||
671 LPAD('HGram',6)
672 ,p_level => 2);
673 write_log(p_text => RPAD('-',30,'-')||' '||
674 LPAD('-',12,'-')||' '||
675 LPAD('-',10,'-')||' '||
676 LPAD('-',11,'-')||' '||
677 LPAD('-',12,'-')||' '||
678 LPAD('-', 7,'-')||' '||
679 LPAD('-', 5,'-')
680 ,p_level => 2);
681 -- write the column stats out
682 -- n.b. used native dynamic SQL to get around the sub-query in the select column
683 -- list which is not supported directly in PL/SQL
684 OPEN l_cursor FOR
685 'SELECT dc.column_name column_name,'||
686 ' TO_CHAR(dc.num_distinct, 999999999999) nd,'||
687 ' TO_CHAR(DECODE(NVL(dc.num_distinct,0), 0, 0, 1 / dc.num_distinct),'||
688 ' ''9.999999999'') es,'||
689 ' TO_CHAR(DECODE(NVL(dc.num_distinct,0), 0, 0, CEIL(dt.num_rows/dc.num_distinct)),'||
690 ' ''99999999999'') ec,'||
691 ' TO_CHAR(dc.num_nulls, 999999999999) nn,'||
692 ' TO_CHAR(dc.density,''9.99999'') d,'||
693 ' dc.avg_col_len acl,'||
694 ' DECODE((SELECT 1'||
695 ' FROM dba_histograms dh'||
696 ' WHERE dh.owner = dt.owner'||
697 ' AND dh.table_name = dt.table_name'||
698 ' AND dh.column_name = dc.column_name'||
699 ' AND dh.endpoint_number NOT IN (0,1)'||
700 ' AND ROWNUM < 2),1,''Y'',''N'') h '||
701 'FROM user_synonyms syn, dba_tab_columns dc, dba_tables dt '||
702 'WHERE syn.synonym_name = :c_table_name' ||
703 'AND syn.table_owner = :c_owner ' ||
704 'AND dc.table_name = syn.table_name ' ||
705 'AND dc.owner = syn.table_owner ' ||
706 'AND dc.table_name = dt.table_name '||
707 'AND dc.num_distinct > 0 '||
708 'AND dt.table_name = :c_table_name '||
709 'AND dt.owner = :c_owner '||
710 'AND dc.owner = dt.owner '||
711 'ORDER BY dc.column_id' USING g_table_name_table(i),g_table_owner_table(i);
712 --
713 LOOP
717 -- write out each col stats
714 -- fetch each COLUMN stat row
715 FETCH l_cursor INTO l_column_name,l_nd,l_es,l_ec,l_nn,l_d,l_acl,l_h;
716 EXIT WHEN l_cursor%NOTFOUND;
718 write_log(p_text => RPAD(l_column_name,30)||
719 LPAD(l_nd,13)||
720 LPAD(l_es,11)||
721 LPAD(l_ec,12)||
722 LPAD(l_nn,13)||
723 LPAD(l_d,8)||
724 LPAD(l_h, 6)
725 ,p_level => 2);
726 --
727 IF g_script_stats = 'Y' THEN
728 write_log(p_text => ' DBMS_STATS.SET_COLUMN_STATS'
729 ,p_level => 2
730 ,p_file => g_script_file);
731 write_log(p_text => ' (ownname=>'''||g_table_owner_table(i)||
732 ''',tabname=>'''||g_table_name_table(i)||
733 ''',colname=>'''||l_column_name||
734 ''',stattab=>l_st'||
735 ',statid=>l_stid'||
736 ',distcnt=>'||TO_CHAR(TO_NUMBER(l_nd))||
737 ',density=>'||TO_CHAR(TO_NUMBER(l_d))||
738 ',nullcnt=>'||TO_CHAR(TO_NUMBER(l_nn))||
739 ',avgclen=>'||TO_CHAR(l_acl)||');'
740 ,p_level => 2
741 ,p_file => g_script_file);
742 END IF;
743 END LOOP;
744 CLOSE l_cursor;
745 -- write out index stats header
746 write_log(p_level => 2);
747 write_log(p_text => RPAD('Index Name',30)||
748 LPAD('Num Rows',13)||
749 LPAD('Unique Keys',13)||
750 LPAD('1/NDK',11)||
751 LPAD('Cardinality',12)||
752 LPAD('LBlks',13)||
753 LPAD('ClustF',8)||
754 LPAD('Levels',7)||
755 LPAD('Last Analyzed',21)
756 ,p_level => 2);
757 write_log(p_text => RPAD('-',30,'-')||' '||
758 LPAD('-',12,'-')||' '||
759 LPAD('-',12,'-')||' '||
760 LPAD('-',10,'-')||' '||
761 LPAD('-',11,'-')||' '||
762 LPAD('-',12,'-')||' '||
763 LPAD('-',7,'-')||' '||
764 LPAD('-',6,'-')||' '||
765 LPAD('-',20,'-')
766 ,p_level => 2);
767 -- write out index stats
768 FOR j IN csr_index(g_table_owner_table(i),g_table_name_table(i)) LOOP
769 write_log(p_text => RPAD(j.index_name,30)||
770 LPAD(j.num_rows,13)||
771 LPAD(j.dk,13)||
772 LPAD(j.s,11)||
773 LPAD(j.ec,12)||
774 LPAD(j.lb,13)||
775 LPAD(j.cf,8)||
776 LPAD(j.bl,7)||' '||
777 j.la
778 ,p_level => 2);
779
780 IF g_script_stats = 'Y' THEN
781 write_log(p_text => ' DBMS_STATS.SET_INDEX_STATS'
782 ,p_level => 2
783 ,p_file => g_script_file);
784 write_log(p_text => ' (ownname=>'''||g_table_owner_table(i)||
785 ''',indname=>'''||j.index_name||
786 ''',stattab=>l_st'||
787 ',statid=>l_stid'||
788 ',numrows=>'||TO_CHAR(TO_NUMBER(j.num_rows))||
789 ',numlblks=>'||TO_CHAR(TO_NUMBER(j.lb))||
790 ',numdist=>'||TO_CHAR(TO_NUMBER(j.dk))||
791 ',avglblk=>'||TO_CHAR(j.albpk)||
792 ',avgdblk=>'||TO_CHAR(j.adbpk)||
793 ',clstfct=>'||TO_CHAR(TO_NUMBER(j.cf))||
794 ',indlevel=>'||TO_CHAR(TO_NUMBER(j.bl))||');'
795 ,p_level => 2
796 ,p_file => g_script_file);
797 END IF;
798 END LOOP;
799 -- write out index key information header
800 write_log(p_level => 2);
801 write_log(p_text => RPAD('Index Name',31)||
802 RPAD('Uniqueness',11)||
803 RPAD('Column Name',31)||
804 LPAD('Position',8)
805 ,p_level => 2);
806 write_log(p_text => RPAD('-',30,'-')||' '||
807 RPAD('-',10,'-')||' '||
808 RPAD('-',30,'-')||' '||
809 LPAD('-',8,'-')
810 ,p_level => 2);
811 -- write out index key information
812 FOR j IN csr_index_cols(g_table_owner_table(i),g_table_name_table(i)) LOOP
813 IF j.column_position = 1 THEN
814 write_log(p_text => RPAD(j.index_name,31)||
815 RPAD(j.uniqueness,11)||
816 RPAD(j.column_name,31)||
817 LPAD(j.column_position,8)
818 ,p_level => 2);
819 ELSE
820 write_log(p_text => RPAD(' ',31)||
821 RPAD(' ',11)||
822 RPAD(j.column_name,31)||
823 LPAD(j.column_position,8)
824 ,p_level => 2);
825 END IF;
826 END LOOP;
827 END LOOP;
828 END IF;
829 EXCEPTION
833 TO_CHAR(SQLCODE)||': '||SQLERRM;
830 WHEN OTHERS THEN
831 -- an internal error has occurred
832 g_error_text := g_error_std_text||'show_stats: '||
834 IF l_cursor%ISOPEN THEN
835 CLOSE l_cursor;
836 END IF;
837 RAISE;
838 END show_stats;
839 -- ---------------------------------------------------------------------------------------
840 -- procedure:
841 -- show_summary
842 -- description:
843 -- writes out to the log file a summary if the log level is greater or equal to 1.
844 -- ---------------------------------------------------------------------------------------
845 PROCEDURE show_summary IS
846 BEGIN
847 IF g_log_level >= 1 THEN
848 -- write summary info
849 write_log;
850 write_log(g_log_line_separator);
851 write_log(p_text => 'SUMMARY');
852 write_log(p_text => '-------');
853 write_log(p_text => 'SQL stmts processed : '||g_explain_sql_counter);
854 write_log(p_text => 'SQL stmts explained : '||g_explained_sql_counter);
855 write_log(p_text => 'SQL stmts not explained : '||g_not_explained_sql_counter);
856 IF g_explained_sql_counter > 0 THEN
857 write_log(p_text => 'Stats been backed up? : '||NVL(g_backup_stats,'N'));
858 IF g_backup_stats = 'Y' THEN
859 write_log(p_text => 'Stats backed up to table: '||g_stat_table_name);
860 write_log(p_text => 'The statid is : '||g_filename);
861 END IF;
862 write_log;
863 write_log(p_text => 'Table/Column/Indexs stats backed up for');
864 write_log(p_text => '---------------------------------------');
865 FOR i in 1..g_table_counter LOOP
866 write_log(RPAD(g_table_owner_table(i)||'.'||g_table_name_table(i),62)||
867 'backed up = '||g_table_owner_status_table(i));
868 END LOOP;
869 -- write out table index stats if level 2
870 show_stats;
871 ELSE
872 write_log(p_text => 'Stats been backed up? : N - no stats to back up');
873 END IF;
874 write_log;
875 IF g_script_stats = 'Y' AND g_log_level = 2 THEN
876 write_log(p_text => 'END;'
877 ,p_level => 2
878 ,p_file => g_script_file);
879 write_log(p_text => '/'
880 ,p_level => 2
881 ,p_file => g_script_file);
882 END IF;
883 END IF;
884 EXCEPTION
885 WHEN OTHERS THEN
886 -- an internal error has occurred
887 g_error_text := g_error_std_text||'show_summary: '||
888 TO_CHAR(SQLCODE)||': '||SQLERRM;
889 RAISE;
890 END show_summary;
891 -- ---------------------------------------------------------------------------------------
892 -- procedure:
893 -- open_log_file
894 -- description:
895 -- if the log level has been set (e.g. greater than zero) then the log file is opened
896 -- for writing. the log file location is the same as the TKPROF location. the log file
897 -- name is the TKRPOF filename with an extra extention indentified by the global
898 -- g_log_file_extention
899 -- ---------------------------------------------------------------------------------------
900 PROCEDURE open_log_file
901 (p_location IN VARCHAR2,
902 p_filename IN VARCHAR2,
903 p_log_level IN PLS_INTEGER,
904 p_limit IN PLS_INTEGER) IS
905 --
906 BEGIN
907 -- check and set the log level
908 IF p_log_level > 0 AND p_log_level <= 2 THEN
909 -- set the log filename
910 g_log_filename := p_filename||g_log_file_extention;
911 -- open the log file for writing
912 g_log_file := UTL_FILE.fopen
913 (location => p_location,
914 filename => g_log_filename,
915 open_mode => 'w',
916 max_linesize => 32767);
917 -- set the global log level
918 g_log_level := p_log_level;
919 --
920 IF g_script_stats = 'Y' AND p_log_level = 2 THEN
921 g_script_filename := p_filename||g_script_file_extention;
922 g_script_file := UTL_FILE.fopen
923 (location => p_location,
924 filename => g_script_filename,
925 open_mode => 'w',
926 max_linesize => 32767);
927 END IF;
928 -- write out the log file header
929 show_header
930 (p_location => p_location,
931 p_filename => p_filename,
932 p_limit => p_limit);
933 ELSE
934 g_log_level := 0;
935 END IF;
936 EXCEPTION
937 WHEN UTL_FILE.invalid_path THEN
938 g_error_text := 'The LOG location path is invalid';
939 RAISE;
940 WHEN UTL_FILE.invalid_mode THEN
941 g_error_text := 'The LOG file was opened with an invalid mode';
942 RAISE;
943 WHEN UTL_FILE.invalid_operation THEN
944 g_error_text := 'The LOG file was opened with an invalid operation';
945 RAISE;
946 WHEN OTHERS THEN
947 g_log_level := 0;
948 g_error_text := g_error_std_text||'open_log_file: '||
949 TO_CHAR(SQLCODE)||': '||SQLERRM;
950 RAISE;
951 END open_log_file;
952 -- ---------------------------------------------------------------------------------------
953 -- procedure:
954 -- close_log_file
955 -- description:
956 -- if the log level has been set (e.g. greater than zero) or the log file is open
957 -- flush out the buffer and close the log file.
958 -- ---------------------------------------------------------------------------------------
959 PROCEDURE close_log_file IS
963 -- write out the log summary
960 l_file UTL_FILE.file_type := g_log_file;
961 BEGIN
962 IF g_log_level > 0 THEN
964 show_summary;
965 -- flush the buffer
966 UTL_FILE.FFLUSH(file => l_file);
967 -- close the opened log file
968 UTL_FILE.fclose(file => l_file);
969 IF g_script_stats = 'Y' AND g_log_level = 2 THEN
970 l_file := g_script_file;
971 -- flush the buffer
972 UTL_FILE.FFLUSH(file => l_file);
973 -- close the opened script file
974 UTL_FILE.fclose(file => l_file);
975 END IF;
976 END IF;
977 EXCEPTION
978 WHEN OTHERS THEN
979 -- an internal error has occurred
980 g_error_text := g_error_std_text||'close_log_file: '||
981 TO_CHAR(SQLCODE)||': '||SQLERRM;
982 RAISE;
983 END close_log_file;
984 -- ---------------------------------------------------------------------------------------
985 -- function:
986 -- open_tkprof_file
987 -- description:
988 -- open the specified tkprof file returning the tkprof file handle.
989 -- ---------------------------------------------------------------------------------------
990 FUNCTION open_tkprof_file(
991 p_location IN VARCHAR2,
992 p_filename IN VARCHAR2)
993 RETURN UTL_FILE.file_type IS
994 --
995 l_location_null EXCEPTION;
996 l_filename_null EXCEPTION;
997 --
998 BEGIN
999 IF p_location IS NULL THEN
1000 RAISE l_location_null;
1001 ELSIF p_filename IS NULL THEN
1002 RAISE l_filename_null;
1003 END IF;
1004 -- open the tkprof file for reading and return the file type
1005 -- handle
1006 RETURN (
1007 UTL_FILE.fopen(
1008 location => p_location,
1009 filename => p_filename,
1010 open_mode => 'r',
1011 max_linesize => 32767));
1012 EXCEPTION
1013 WHEN l_location_null THEN
1014 g_error_text := 'The TKPROF location path is required and cannot be NULL';
1015 RAISE;
1016 WHEN l_filename_null THEN
1017 g_error_text := 'The TKPROF filename is required and cannot be NULL';
1018 RAISE;
1019 WHEN UTL_FILE.invalid_path THEN
1020 g_error_text := 'The TKPROF location path is invalid';
1021 RAISE;
1022 WHEN UTL_FILE.invalid_mode THEN
1023 g_error_text := 'The TKPROF file was opened with an invalid mode';
1024 RAISE;
1025 WHEN UTL_FILE.invalid_operation THEN
1026 g_error_text := 'The TKRPOF file was opened with an invalid operation';
1027 RAISE;
1028 WHEN OTHERS THEN
1029 g_error_text := g_error_std_text||'open_tkprof_file: '||
1030 TO_CHAR(SQLCODE)||': '||SQLERRM;
1031 RAISE;
1032 END open_tkprof_file;
1033 -- ---------------------------------------------------------------------------------------
1034 -- procedure:
1035 -- close_tkprof_file
1036 -- description:
1037 -- close the tkprof file
1038 -- ---------------------------------------------------------------------------------------
1039 PROCEDURE close_tkprof_file(
1040 p_file IN UTL_FILE.file_type) IS
1041 l_file UTL_FILE.file_type := p_file;
1042 BEGIN
1043 IF UTL_FILE.IS_OPEN(l_file) THEN
1044 -- close the opened tkprof file
1045 UTL_FILE.fclose(file => l_file);
1046 END IF;
1047 EXCEPTION
1048 WHEN OTHERS THEN
1049 -- an internal error has occurred
1050 g_error_text := g_error_std_text||'close_tkprof_file: '||
1051 TO_CHAR(SQLCODE)||': '||SQLERRM;
1052 RAISE;
1053 END close_tkprof_file;
1054 --
1055 PROCEDURE run(
1056 p_location IN VARCHAR2,
1057 p_filename IN VARCHAR2,
1058 p_backup_stats IN VARCHAR2 DEFAULT 'N',
1059 p_script_stats IN VARCHAR2 DEFAULT 'N',
1060 p_limit IN PLS_INTEGER DEFAULT 5,
1061 p_log_level IN PLS_INTEGER DEFAULT 2,
1062 p_explain_table IN VARCHAR2 DEFAULT 'PLAN_TABLE') IS
1063 --
1064 l_file UTL_FILE.file_type;
1065 --
1066 BEGIN
1067 -- initialise the explain sql counters
1068 g_explain_sql_counter := 0;
1069 g_explained_sql_counter := 0;
1070 g_not_explained_sql_counter := 0;
1071 g_table_counter := 0;
1072 g_table_owner_table.DELETE;
1073 g_table_owner_status_table.DELETE;
1074 g_table_name_table.DELETE;
1075 g_error_text := NULL;
1076 g_filename := p_filename;
1077 -- set the gather status
1078 IF UPPER(p_backup_stats) = 'Y' THEN
1079 g_backup_stats := 'Y';
1080 ELSE
1081 g_backup_stats := NULL;
1082 END IF;
1083 -- set the script status
1084 IF UPPER(p_script_stats) = 'Y' AND p_log_level = 2 THEN
1085 g_script_stats := 'Y';
1086 ELSE
1087 g_script_stats := NULL;
1088 END IF;
1089 -- set the g_explain_table global
1090 g_explain_table := NVL(UPPER(p_explain_table),'PLAN_TABLE');
1091 -- validate the plan table exists
1092 check_explain_table;
1093 -- open TKPROF file
1094 l_file := open_tkprof_file
1095 (p_location => p_location,
1096 p_filename => p_filename);
1097 -- open the log file
1098 open_log_file
1099 (p_location => p_location,
1100 p_filename => p_filename,
1101 p_log_level => p_log_level,
1102 p_limit => p_limit);
1103 -- delete any previously created explain plans
1104 delete_all_expstms(p_commit => TRUE);
1105 -- process TKPROF file
1106 process_tkprof_file
1107 (p_file => l_file,
1108 p_limit => p_limit);
1109 -- close TKPROF file
1110 close_tkprof_file(p_file => l_file);
1111 -- gather/export table/column/index stats
1112 export_table_stats(p_statid => p_filename);
1113 -- close_log_file
1114 close_log_file;
1115 EXCEPTION
1116 WHEN OTHERS THEN
1117 -- close all open files
1118 UTL_FILE.fclose_all;
1119 raise_application_error(-20001,g_error_text);
1120 END run;
1121 END hr_tkprof_plus;