[Home] [Help]
PACKAGE BODY: APPS.HR_TKPROF_PLUS
Source
1 PACKAGE BODY hr_tkprof_plus AS
2 /* $Header: hrtkplus.pkb 115.3 2004/01/20 09:49:47 mroberts noship $ */
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
329 -- incremenet the explain counter
326 -- start of sql stmt has been found in the TKPROF file
327 l_sql_found := TRUE;
328 l_sql_text := l_tkprof_line;
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);
379 l_run_date VARCHAR2(22) := TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS');
380 --
381 BEGIN
382 IF g_log_level >= 1 THEN
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''),'||
441 '''_like_with_bind_as_equality'', 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''),'||
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'','||
476 '''_optimizer_undo_changes'','||
477 '''db_file_multiblock_read_count'','||
478 '''optimizer_max_permutations'','||
479 '''optimizer_mode'','||
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'
531 END IF;
528 ,p_level => 2
529 ,p_file => g_script_file);
530 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
601 AND i.owner = c_owner
602 AND c.index_name = i.index_name
603 AND c.index_owner = i.owner
604 ORDER BY c.table_name,
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
650 ,p_level => 2
647 ,p_level => 2
648 ,p_file => g_script_file);
649 write_log(p_text => ' DBMS_STATS.SET_TABLE_STATS'
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 dba_tab_columns dc, dba_tables dt '||
702 'WHERE dc.table_name = dt.table_name '||
703 'AND dc.num_distinct > 0 '||
704 'AND dt.table_name = :c_table_name '||
705 'AND dt.owner = :c_owner '||
706 'AND dc.owner = dt.owner '||
707 'ORDER BY dc.column_id' USING g_table_name_table(i),g_table_owner_table(i);
708 --
709 LOOP
710 -- fetch each COLUMN stat row
711 FETCH l_cursor INTO l_column_name,l_nd,l_es,l_ec,l_nn,l_d,l_acl,l_h;
712 EXIT WHEN l_cursor%NOTFOUND;
713 -- write out each col stats
714 write_log(p_text => RPAD(l_column_name,30)||
715 LPAD(l_nd,13)||
716 LPAD(l_es,11)||
717 LPAD(l_ec,12)||
718 LPAD(l_nn,13)||
719 LPAD(l_d,8)||
720 LPAD(l_h, 6)
721 ,p_level => 2);
722 --
723 IF g_script_stats = 'Y' THEN
724 write_log(p_text => ' DBMS_STATS.SET_COLUMN_STATS'
725 ,p_level => 2
726 ,p_file => g_script_file);
727 write_log(p_text => ' (ownname=>'''||g_table_owner_table(i)||
728 ''',tabname=>'''||g_table_name_table(i)||
729 ''',colname=>'''||l_column_name||
730 ''',stattab=>l_st'||
731 ',statid=>l_stid'||
732 ',distcnt=>'||TO_CHAR(TO_NUMBER(l_nd))||
733 ',density=>'||TO_CHAR(TO_NUMBER(l_d))||
734 ',nullcnt=>'||TO_CHAR(TO_NUMBER(l_nn))||
735 ',avgclen=>'||TO_CHAR(l_acl)||');'
736 ,p_level => 2
737 ,p_file => g_script_file);
738 END IF;
739 END LOOP;
740 CLOSE l_cursor;
741 -- write out index stats header
742 write_log(p_level => 2);
743 write_log(p_text => RPAD('Index Name',30)||
744 LPAD('Num Rows',13)||
745 LPAD('Unique Keys',13)||
746 LPAD('1/NDK',11)||
747 LPAD('Cardinality',12)||
748 LPAD('LBlks',13)||
749 LPAD('ClustF',8)||
750 LPAD('Levels',7)||
754 LPAD('-',12,'-')||' '||
751 LPAD('Last Analyzed',21)
752 ,p_level => 2);
753 write_log(p_text => RPAD('-',30,'-')||' '||
755 LPAD('-',12,'-')||' '||
756 LPAD('-',10,'-')||' '||
757 LPAD('-',11,'-')||' '||
758 LPAD('-',12,'-')||' '||
759 LPAD('-',7,'-')||' '||
760 LPAD('-',6,'-')||' '||
761 LPAD('-',20,'-')
762 ,p_level => 2);
763 -- write out index stats
764 FOR j IN csr_index(g_table_owner_table(i),g_table_name_table(i)) LOOP
765 write_log(p_text => RPAD(j.index_name,30)||
766 LPAD(j.num_rows,13)||
767 LPAD(j.dk,13)||
768 LPAD(j.s,11)||
769 LPAD(j.ec,12)||
770 LPAD(j.lb,13)||
771 LPAD(j.cf,8)||
772 LPAD(j.bl,7)||' '||
773 j.la
774 ,p_level => 2);
775
776 IF g_script_stats = 'Y' THEN
777 write_log(p_text => ' DBMS_STATS.SET_INDEX_STATS'
778 ,p_level => 2
779 ,p_file => g_script_file);
780 write_log(p_text => ' (ownname=>'''||g_table_owner_table(i)||
781 ''',indname=>'''||j.index_name||
782 ''',stattab=>l_st'||
783 ',statid=>l_stid'||
784 ',numrows=>'||TO_CHAR(TO_NUMBER(j.num_rows))||
785 ',numlblks=>'||TO_CHAR(TO_NUMBER(j.lb))||
786 ',numdist=>'||TO_CHAR(TO_NUMBER(j.dk))||
787 ',avglblk=>'||TO_CHAR(j.albpk)||
788 ',avgdblk=>'||TO_CHAR(j.adbpk)||
789 ',clstfct=>'||TO_CHAR(TO_NUMBER(j.cf))||
790 ',indlevel=>'||TO_CHAR(TO_NUMBER(j.bl))||');'
791 ,p_level => 2
792 ,p_file => g_script_file);
793 END IF;
794 END LOOP;
795 -- write out index key information header
796 write_log(p_level => 2);
797 write_log(p_text => RPAD('Index Name',31)||
798 RPAD('Uniqueness',11)||
799 RPAD('Column Name',31)||
800 LPAD('Position',8)
801 ,p_level => 2);
802 write_log(p_text => RPAD('-',30,'-')||' '||
803 RPAD('-',10,'-')||' '||
804 RPAD('-',30,'-')||' '||
805 LPAD('-',8,'-')
806 ,p_level => 2);
807 -- write out index key information
808 FOR j IN csr_index_cols(g_table_owner_table(i),g_table_name_table(i)) LOOP
809 IF j.column_position = 1 THEN
810 write_log(p_text => RPAD(j.index_name,31)||
811 RPAD(j.uniqueness,11)||
812 RPAD(j.column_name,31)||
813 LPAD(j.column_position,8)
814 ,p_level => 2);
815 ELSE
816 write_log(p_text => RPAD(' ',31)||
817 RPAD(' ',11)||
818 RPAD(j.column_name,31)||
819 LPAD(j.column_position,8)
820 ,p_level => 2);
821 END IF;
822 END LOOP;
823 END LOOP;
824 END IF;
825 EXCEPTION
826 WHEN OTHERS THEN
827 -- an internal error has occurred
828 g_error_text := g_error_std_text||'show_stats: '||
829 TO_CHAR(SQLCODE)||': '||SQLERRM;
830 IF l_cursor%ISOPEN THEN
831 CLOSE l_cursor;
832 END IF;
833 RAISE;
834 END show_stats;
835 -- ---------------------------------------------------------------------------------------
836 -- procedure:
837 -- show_summary
838 -- description:
839 -- writes out to the log file a summary if the log level is greater or equal to 1.
840 -- ---------------------------------------------------------------------------------------
841 PROCEDURE show_summary IS
842 BEGIN
843 IF g_log_level >= 1 THEN
844 -- write summary info
845 write_log;
846 write_log(g_log_line_separator);
847 write_log(p_text => 'SUMMARY');
848 write_log(p_text => '-------');
849 write_log(p_text => 'SQL stmts processed : '||g_explain_sql_counter);
850 write_log(p_text => 'SQL stmts explained : '||g_explained_sql_counter);
851 write_log(p_text => 'SQL stmts not explained : '||g_not_explained_sql_counter);
852 IF g_explained_sql_counter > 0 THEN
853 write_log(p_text => 'Stats been backed up? : '||NVL(g_backup_stats,'N'));
854 IF g_backup_stats = 'Y' THEN
855 write_log(p_text => 'Stats backed up to table: '||g_stat_table_name);
856 write_log(p_text => 'The statid is : '||g_filename);
857 END IF;
858 write_log;
859 write_log(p_text => 'Table/Column/Indexs stats backed up for');
863 'backed up = '||g_table_owner_status_table(i));
860 write_log(p_text => '---------------------------------------');
861 FOR i in 1..g_table_counter LOOP
862 write_log(RPAD(g_table_owner_table(i)||'.'||g_table_name_table(i),62)||
864 END LOOP;
865 -- write out table index stats if level 2
866 show_stats;
867 ELSE
868 write_log(p_text => 'Stats been backed up? : N - no stats to back up');
869 END IF;
870 write_log;
871 IF g_script_stats = 'Y' AND g_log_level = 2 THEN
872 write_log(p_text => 'END;'
873 ,p_level => 2
874 ,p_file => g_script_file);
875 write_log(p_text => '/'
876 ,p_level => 2
877 ,p_file => g_script_file);
878 END IF;
879 END IF;
880 EXCEPTION
881 WHEN OTHERS THEN
882 -- an internal error has occurred
883 g_error_text := g_error_std_text||'show_summary: '||
884 TO_CHAR(SQLCODE)||': '||SQLERRM;
885 RAISE;
886 END show_summary;
887 -- ---------------------------------------------------------------------------------------
888 -- procedure:
889 -- open_log_file
890 -- description:
891 -- if the log level has been set (e.g. greater than zero) then the log file is opened
892 -- for writing. the log file location is the same as the TKPROF location. the log file
893 -- name is the TKRPOF filename with an extra extention indentified by the global
894 -- g_log_file_extention
895 -- ---------------------------------------------------------------------------------------
896 PROCEDURE open_log_file
897 (p_location IN VARCHAR2,
898 p_filename IN VARCHAR2,
899 p_log_level IN PLS_INTEGER,
900 p_limit IN PLS_INTEGER) IS
901 --
902 BEGIN
903 -- check and set the log level
904 IF p_log_level > 0 AND p_log_level <= 2 THEN
905 -- set the log filename
906 g_log_filename := p_filename||g_log_file_extention;
907 -- open the log file for writing
908 g_log_file := UTL_FILE.fopen
909 (location => p_location,
910 filename => g_log_filename,
911 open_mode => 'w',
912 max_linesize => 32767);
913 -- set the global log level
914 g_log_level := p_log_level;
915 --
916 IF g_script_stats = 'Y' AND p_log_level = 2 THEN
917 g_script_filename := p_filename||g_script_file_extention;
918 g_script_file := UTL_FILE.fopen
919 (location => p_location,
920 filename => g_script_filename,
921 open_mode => 'w',
922 max_linesize => 32767);
923 END IF;
924 -- write out the log file header
925 show_header
926 (p_location => p_location,
927 p_filename => p_filename,
928 p_limit => p_limit);
929 ELSE
930 g_log_level := 0;
931 END IF;
932 EXCEPTION
933 WHEN UTL_FILE.invalid_path THEN
934 g_error_text := 'The LOG location path is invalid';
935 RAISE;
936 WHEN UTL_FILE.invalid_mode THEN
937 g_error_text := 'The LOG file was opened with an invalid mode';
938 RAISE;
939 WHEN UTL_FILE.invalid_operation THEN
940 g_error_text := 'The LOG file was opened with an invalid operation';
941 RAISE;
942 WHEN OTHERS THEN
943 g_log_level := 0;
944 g_error_text := g_error_std_text||'open_log_file: '||
945 TO_CHAR(SQLCODE)||': '||SQLERRM;
946 RAISE;
947 END open_log_file;
948 -- ---------------------------------------------------------------------------------------
949 -- procedure:
950 -- close_log_file
951 -- description:
952 -- if the log level has been set (e.g. greater than zero) or the log file is open
953 -- flush out the buffer and close the log file.
954 -- ---------------------------------------------------------------------------------------
955 PROCEDURE close_log_file IS
956 l_file UTL_FILE.file_type := g_log_file;
957 BEGIN
958 IF g_log_level > 0 THEN
959 -- write out the log summary
960 show_summary;
961 -- flush the buffer
962 UTL_FILE.FFLUSH(file => l_file);
963 -- close the opened log file
964 UTL_FILE.fclose(file => l_file);
965 IF g_script_stats = 'Y' AND g_log_level = 2 THEN
966 l_file := g_script_file;
967 -- flush the buffer
968 UTL_FILE.FFLUSH(file => l_file);
969 -- close the opened script file
970 UTL_FILE.fclose(file => l_file);
971 END IF;
972 END IF;
973 EXCEPTION
974 WHEN OTHERS THEN
975 -- an internal error has occurred
976 g_error_text := g_error_std_text||'close_log_file: '||
977 TO_CHAR(SQLCODE)||': '||SQLERRM;
978 RAISE;
979 END close_log_file;
980 -- ---------------------------------------------------------------------------------------
981 -- function:
982 -- open_tkprof_file
983 -- description:
984 -- open the specified tkprof file returning the tkprof file handle.
985 -- ---------------------------------------------------------------------------------------
986 FUNCTION open_tkprof_file(
987 p_location IN VARCHAR2,
988 p_filename IN VARCHAR2)
989 RETURN UTL_FILE.file_type IS
990 --
994 BEGIN
991 l_location_null EXCEPTION;
992 l_filename_null EXCEPTION;
993 --
995 IF p_location IS NULL THEN
996 RAISE l_location_null;
997 ELSIF p_filename IS NULL THEN
998 RAISE l_filename_null;
999 END IF;
1000 -- open the tkprof file for reading and return the file type
1001 -- handle
1002 RETURN (
1003 UTL_FILE.fopen(
1004 location => p_location,
1005 filename => p_filename,
1006 open_mode => 'r',
1007 max_linesize => 32767));
1008 EXCEPTION
1009 WHEN l_location_null THEN
1010 g_error_text := 'The TKPROF location path is required and cannot be NULL';
1011 RAISE;
1012 WHEN l_filename_null THEN
1013 g_error_text := 'The TKPROF filename is required and cannot be NULL';
1014 RAISE;
1015 WHEN UTL_FILE.invalid_path THEN
1016 g_error_text := 'The TKPROF location path is invalid';
1017 RAISE;
1018 WHEN UTL_FILE.invalid_mode THEN
1019 g_error_text := 'The TKPROF file was opened with an invalid mode';
1020 RAISE;
1021 WHEN UTL_FILE.invalid_operation THEN
1022 g_error_text := 'The TKRPOF file was opened with an invalid operation';
1023 RAISE;
1024 WHEN OTHERS THEN
1025 g_error_text := g_error_std_text||'open_tkprof_file: '||
1026 TO_CHAR(SQLCODE)||': '||SQLERRM;
1027 RAISE;
1028 END open_tkprof_file;
1029 -- ---------------------------------------------------------------------------------------
1030 -- procedure:
1031 -- close_tkprof_file
1032 -- description:
1033 -- close the tkprof file
1034 -- ---------------------------------------------------------------------------------------
1035 PROCEDURE close_tkprof_file(
1036 p_file IN UTL_FILE.file_type) IS
1037 l_file UTL_FILE.file_type := p_file;
1038 BEGIN
1039 IF UTL_FILE.IS_OPEN(l_file) THEN
1040 -- close the opened tkprof file
1041 UTL_FILE.fclose(file => l_file);
1042 END IF;
1043 EXCEPTION
1044 WHEN OTHERS THEN
1045 -- an internal error has occurred
1046 g_error_text := g_error_std_text||'close_tkprof_file: '||
1047 TO_CHAR(SQLCODE)||': '||SQLERRM;
1048 RAISE;
1049 END close_tkprof_file;
1050 --
1051 PROCEDURE run(
1052 p_location IN VARCHAR2,
1053 p_filename IN VARCHAR2,
1054 p_backup_stats IN VARCHAR2 DEFAULT 'N',
1055 p_script_stats IN VARCHAR2 DEFAULT 'N',
1056 p_limit IN PLS_INTEGER DEFAULT 5,
1057 p_log_level IN PLS_INTEGER DEFAULT 2,
1058 p_explain_table IN VARCHAR2 DEFAULT 'PLAN_TABLE') IS
1059 --
1060 l_file UTL_FILE.file_type;
1061 --
1062 BEGIN
1063 -- initialise the explain sql counters
1064 g_explain_sql_counter := 0;
1065 g_explained_sql_counter := 0;
1066 g_not_explained_sql_counter := 0;
1067 g_table_counter := 0;
1068 g_table_owner_table.DELETE;
1069 g_table_owner_status_table.DELETE;
1070 g_table_name_table.DELETE;
1071 g_error_text := NULL;
1072 g_filename := p_filename;
1073 -- set the gather status
1074 IF UPPER(p_backup_stats) = 'Y' THEN
1075 g_backup_stats := 'Y';
1076 ELSE
1077 g_backup_stats := NULL;
1078 END IF;
1079 -- set the script status
1080 IF UPPER(p_script_stats) = 'Y' AND p_log_level = 2 THEN
1081 g_script_stats := 'Y';
1082 ELSE
1083 g_script_stats := NULL;
1084 END IF;
1085 -- set the g_explain_table global
1086 g_explain_table := NVL(UPPER(p_explain_table),'PLAN_TABLE');
1087 -- validate the plan table exists
1088 check_explain_table;
1089 -- open TKPROF file
1090 l_file := open_tkprof_file
1091 (p_location => p_location,
1092 p_filename => p_filename);
1093 -- open the log file
1094 open_log_file
1095 (p_location => p_location,
1096 p_filename => p_filename,
1097 p_log_level => p_log_level,
1098 p_limit => p_limit);
1099 -- delete any previously created explain plans
1100 delete_all_expstms(p_commit => TRUE);
1101 -- process TKPROF file
1102 process_tkprof_file
1103 (p_file => l_file,
1104 p_limit => p_limit);
1105 -- close TKPROF file
1106 close_tkprof_file(p_file => l_file);
1107 -- gather/export table/column/index stats
1108 export_table_stats(p_statid => p_filename);
1109 -- close_log_file
1110 close_log_file;
1111 EXCEPTION
1112 WHEN OTHERS THEN
1113 -- close all open files
1114 UTL_FILE.fclose_all;
1115 raise_application_error(-20001,g_error_text);
1116 END run;
1117 END hr_tkprof_plus;