1 package dbms_xplan AUTHID CURRENT_USER as
2
3 --- ------------------------------------------------------------------------
4 --- DBMS_XPLAN CONSTANTS SECTION
5 --- ------------------------------------------------------------------------
6
7 --- The following constants designate the flags returned in the bit vector
8 --- from the COMPARE_QUERY_PLANS function.
9
10 UNKNOWN_DIFF_CLASS CONSTANT NUMBER := POWER(2,31);
11
12 --- ------------------------------------------------------------------------
13 --- DBMS_XPLAN PUBLIC FUNCTIONS SECTION
14 --- ------------------------------------------------------------------------
15 ---
16 --- OVERVIEW
17 ---
18 --- This package defines several table functions which can be used to
19 --- display execution plans.
20 ---
21 --- - DISPLAY is generally used to display the execution plan produced
22 --- by an EXPLAIN PLAN command; you can either display the most
23 --- recent explained statement, or the statement for a specific
24 --- statement id.
25 ---
26 --- In addition, this table function can also be used to display
27 --- any plan (with or without statistics) stored in a table as
28 --- long as the columns of this table are named the same as
29 --- columns of the plan_table (or v$sql_plan_statistics_all if
30 --- statistics are included).
31 --- A predicate on the specified table can be used to select rows
32 --- of the plan to display.
33 ---
34 --- - DISPLAY_CURSOR displays the execution plans for one or several
35 --- cursors in the shared SQL area, depending on a filter
36 --- criteria. It can display the plan for the last executed
37 --- statement, the current (if session is active) or last
38 --- executed statement (if session is inactive) of a specific
39 --- session, or all cursors matching an arbitrary criteria
40 --- defined via SQL. In addition to the explain plan, various
41 --- plan statistics (e.g. io, memory and timing) can be
42 --- reported (based on the v$sql_plan_statistics_all views).
43 ---
44 --- Specific cursors are identified by SQL_ID and optionally a
45 --- SQL_CHILD_NUMBER.
46 ---
47 --- The DEFAULT without any parameters shows the last executed
48 --- statement of the session.
49 ---
50 --- NOTE: To use the DISPLAY_CURSOR functionality, the calling
51 --- user must have SELECT privilege on V$SQL_PLAN_STATISTICS_ALL,
52 --- V$SQL, and V$SQL_PLAN. By default, only the select_catalog
53 --- role has the SELECT privilege on these views.
54 ---
55 --- - DISPLAY_AWR displays the execution plans for SQL statements stored in
56 --- the Automatic Workload Repository (AWR).
57 --- NOTE: To use the DISPLAY_AWR functionality, the calling user
58 --- must have SELECT prvilege on DBA_HIST_SQL_PLAN and
59 --- DBA_HIST_SQLTEXT. By default, select privilige for these
60 --- views is granted to the select_catalog role.
61 ---
62 --- - DISPLAY_SQLSET displays the execution plans for SQL statements stored
63 --- in a SQL tuning set.
64 --- NOTE: To use the DISPLAY_SQLSET functionality, the calling
65 --- user must have SELECT prvilege on ALL_SQLSET_PLANS and
66 --- ALL_SQLSET_STATEMENTS. By default, select privilige for these
67 --- views is granted to the public role.
68 ---
69 --- - DISPLAY_SQL_PLAN_BASELINE displays one or more execution plans for
70 --- the specified sql_handle of a SQL statement. If plan_name is
71 --- specified denoting a single plan then that plan is displayed.
72 --- The plan information stored in the SQL management base is
73 --- used to generate and display the plan. It is possible that
74 --- the stored plan id may not match up with the plan id of the
75 -- generated plan. A plan id mismatch means that the stored plan
76 --- is not reproducible. Such a plan is deemed invalid by the
77 --- optimizer and ignored when the corresponding SQL statement is
78 -- compiled and a cursor is built. When plan id mismatch occurs
79 --- a note saying 'the plan is invalid' is shown in the notes
80 --- section of the plan.
81 --- NOTE: To use DISPLAY_SQL_PLAN_BASELINE function, the calling
82 --- user must have SELECT prvilege on DBA_SQL_PLAN_BASELINES
83 --- view. By default, SELECT privilege on this view is granted
84 --- to the SELECT_CATALOG_ROLE.
85 ---
86 ---
87 --- For example:
88 --- To show the last explained statement
89 --- explain plan for select ename, deptno
90 --- from emp e, dept d
91 --- where e.deptno = d.deptno;
92 --- select * from table(dbms_xplan.display);
93 ---
94 --- To show the last executed statement of your session
95 --- select * from table(dbms_xplan.display_cursor);
96 ---
97 --- See more detailed examples below
98 ---
99 --- SECURITY
100 ---
101 --- The execution privilege on this package is granted to PUBLIC.
102 --- The display procedures of this package are run under the caller
103 --- security.
104 ---
105 ---
106 --- PROCEDURES AND FUNCTIONS
107 ---
108 --- function display(table_name varchar2 default 'PLAN_TABLE',
109 --- statement_id varchar2 default null,
110 --- format varchar2 default 'TYPICAL',
111 --- filter_preds varchar2 default null)
112 ---
113 --- - table_name:
114 --- specifies the table name where the plan is stored. This
115 --- parameter defaults to "PLAN_TABLE" which is the default
116 --- plan table for the explain plan. If NULL is specified,
117 --- the default of 'PLAN_TABLE' will be taken into account.
118 --- The parameter is case insensitive.
119 ---
120 --- - statement_id:
121 --- specifies the statement id of the plan to display. This
122 --- parameter defaults to NULL. If no statement_id is defined,
123 --- the most recent explained statement in <table_name> will
124 --- be displayed, assuming that the "filter_preds" parameter is
125 --- NULL (its default).
126 ---
127 --- - format:
128 --- Determines what information stored in the plan will be
129 --- shown. The format string can use the following predefined
130 --- three formats, each representing a common use case:
131 ---
132 --- 'BASIC': Display only the minimum set of information, i.e. the
133 --- operation id, the operation name and its option
134 ---
135 --- 'TYPICAL': This is the default. Display most information
136 --- of the explain plan (operation id, name and option,
137 --- #rows, #bytes and optimizer cost). Pruning,
138 --- parallel and predicate information are only
139 --- displayed when applicable. Excludes only PROJECTION,
140 --- ALIAS and REMOTE SQL information (see below).
141 ---
142 --- 'ALL': Maximum user level, like typical with additional
143 --- informations (PROJECTION, ALIAS and information about
144 --- REMOTE SQL if the operation is distributed).
145 ---
146 --- For finer control on the display output, the following keywords
147 --- can be added to the above three standard format to customize their
148 --- default behavior. Each keyword either represents a logical group
149 --- of plan table columns (e.g. PARTITION) or logical additions to the
150 --- base plan table output (e.g. PREDICATE). Format keywords must
151 --- be separated by either a comma or a space:
152 ---
153 --- ROWS: if relevant, shows number of rows estimated by the optimizer
154 ---
155 --- BYTES: if relevant, shows number of bytes estimated by the
156 --- optimizer
157 ---
158 --- COST: if relevant, shows optimizer cost information
159 ---
160 --- PARTITION: If relevant, shows partition pruning information
161 ---
162 --- PARALLEL: If relevant, shows PX information (distribution method
163 --- and table queue information)
164 ---
165 --- PREDICATE: If relevant, shows the predicate section
166 ---
167 --- PROJECTION: If relevant, shows the projection section
168 ---
169 --- ALIAS: If relevant, shows the "Query Block Name / Object Alias"
170 --- section
171 ---
172 --- REMOTE: If relevant, shows the information for distributed query
173 --- (e.g. remote from serial distribution and remote SQL)
174 ---
175 --- NOTE: If relevant, shows the note section of the explain plan.
176 ---
177 --- Format keywords can be prefixed by the sign '-' to exclude the
178 --- specified information. For example, '-PROJECTION' exclude
179 --- projection information.
180 ---
181 --- Finally, if the target plan table (see "table_name" parameter) also
182 --- stores plan statistics columns (e.g. it is a table used to capture
183 --- the content of the fixed view v$sql_plan_statistics_all), then
184 --- additional format keywords can be used to specify which class of
185 --- statistics to display. These additionnal format keywords are IOSTATS,
186 --- MEMSTATS, ROWSTATS, ALLSTATS and LAST described along with the
187 --- display_cursor() table function (see below).
188 ---
189 --- Example:
190 --- - use 'ALL -PROJECTION -NOTE' to display everything except the
191 --- projection and note sections.
192 ---
193 --- - use 'TYPICAL PROJECTION' to display using the typical format
194 --- with the additional projection section (which is normally excluded
195 --- under the typical format). Since typical is default, using
196 --- simply 'PROJECTION' is equivalent.
197 ---
198 --- - use '-BYTES -COST -PREDICATE' to display using the typical
199 --- format but excluding optimizer cost and byte estimates
200 --- as well as the predicate section.
201 ---
202 --- - use 'BASIC ROWS' to display basic information with the
203 --- additional number of rows estimated by the optimizer.
204 ---
205 ---
206 --- - filter_preds: SQL filter predicate(s) to restrict the set of rows
207 --- selected from the table where the plan is stored. When
208 --- value is NULL (the default), the plan displayed
209 --- corresponds to the last executed explain plan.
210 ---
211 --- For example:
212 ---
213 --- filter_preds=>'plan_id = 10'
214 ---
215 --- "filter_preds" can reference any column of the table
216 --- where the plan is stored and can contain any SQL
217 --- construct (e.g. sub-query, function calls...).
218 ---
219 --- WARNING: Application developers should expose this
220 --- parameter to end-users only after careful
221 --- consideration since it could expose the application
222 --- to SQL injection. Indeed, "filter_preds" can
223 --- potentially reference any table or execute any server
224 --- function for which the database user invoking the
225 --- table function has privileges.
226 ---
227 --- ------------------------------------------------------------------------
228 --- function display_cursor(sql_id varchar2 default null,
229 --- cursor_child_no integer default 0,
230 --- format varchar2 default 'TYPICAL')
231 ---
232 --- - sql_id:
233 --- specifies the sql_id value for a specific SQL statement, as
234 --- shown in V$SQL.SQL_ID, V$SESSION.SQL_ID, or
235 --- V$SESSION.PREV_SQL_ID. If no sql_id is specified, the last
236 --- executed statement of the current session is shown.
237 ---
238 --- - cursor_child_no:
239 --- specifies the child number for a specific sql cursor, as shown in
240 --- V$SQL.CHILD_NUMBER or in V$SESSION.SQL_CHILD_NUMBER,
241 --- V$SESSION.PREV_CHILD_NUMBER. This input parameter is only
242 --- considered when sql_id is set.
243 ---
244 --- If not specified, all child cursors for the specified sql_id are
245 --- displayed.
246 ---
247 --- - format:
248 --- The format string has the same meaning as that for the regular
249 --- display() table function (see format description above). In
250 --- addition, the following four format keywords are introduced
251 --- to support the various plan statistics columns available
252 --- in v$sql_plan_statistics_all.
253 ---
254 --- These keywords can also be used by the display() table function
255 --- assuming that the specified table has the same statistics columns
256 --- available in v$sql_plan_statistics_all.
257 ---
258 --- IOSTATS: Assuming that basic plan statistics are
259 --- collected when SQL statements are executed (either by
260 --- using the gather_plan_statistics hint or by setting the
261 --- parameter statistics_level to ALL), this format will show
262 --- IO statistics for all (or only for the last as shown below)
263 --- executions of the cursor.
264 ---
265 --- MEMSTATS: Assuming that PGA memory management is enabled (i.e
266 --- pga_aggregate_target parameter is set to a non 0 value),
267 --- this format allows to display memory management
268 --- statistics (e.g. execution mode of the operator, how
269 --- much memory was used, number of bytes spilled to
270 --- disk, ...). These statistics only apply to memory
271 --- intensive operations like hash-joins, sort or some bitmap
272 --- operators.
273 ---
274 --- ROWSTATS: Assuming that basic plan statistics are
275 --- collected when SQL statements are executed (either by
276 --- using the gather_plan_statistics hint or by setting the
277 --- parameter statistics_level to ALL), this format will show
281 --- ALLSTATS: A shortcut for 'IOSTATS MEMSTATS ROWSTATS'
278 --- row count statistics for all (or only for the last as
279 --- shown below) executions of the cursor.
280 ---
282 ---
283 --- LAST: By default, plan statistics are shown for all executions of
284 --- the cursor. The keyword LAST can be specified to see only
285 --- the statistics for the last execution.
286 ---
287 ---
288 --- Also, the following two formats are still supported for backward
289 --- compatibility:
290 ---
291 --- 'RUNSTATS_TOT': Same as 'IOSTATS', i.e. displays IO statistics
292 --- for all executions of the specified cursor.
293 --- 'RUNSTATS_LAST': Same as 'IOSTATS LAST', i.e. displays the runtime
294 --- statistics for the last execution of the cursor.
295 ---
296 ---
297 --- PRIVILEGES:
298 --- - To use the DISPLAY_CURSOR functionality, the calling
299 --- user must have SELECT privilege on V$SQL_PLAN_STATISTICS_ALL,
300 --- V$SQL, and V$SQL_PLAN, otherwise it will show an appropriate
301 --- error message.
302 ---
303 --- - Unless used in DEFAULT mode to display the last executed
304 --- statement, all internal SQL statements of this package and
305 --- the calling SQL statement using this table function will be
306 --- suppressed.
307 ---
308 --- ------------------------------------------------------------------------
309 --- function display_awr(sql_id varchar2,
310 --- plan_hash_value integer default null,
311 --- db_id integer default null,
312 --- format varchar2 default 'TYPICAL',
313 --- con_id integer default null)
314 ---
315 --- - sql_id:
316 --- specifies the sql_id value for a SQL statement having its plan(s)
317 --- stored in the AWR. You can find all stored SQL statements by
318 --- querying DBA_HIST_SQL_PLAN.
319 ---
320 --- - plan_hash_value:
321 --- identifies a specific stored execution plan for a SQL statement.
322 --- Optional parameter. If suppressed, all stored execution plans are
323 --- shown.
324 ---
325 --- - db_id:
326 --- identifies the plans for a specific dabatase. If this parameter is
327 --- omitted, it will be defaulted to the local database identifier.
328 ---
329 --- - format:
330 --- The format string has the same meaning as that for the regular
331 --- display() table function (see format description above).
332 ---
333 --- - con_id:
334 --- identifies the plans for a specific container. If this parameter
335 --- is omitted, it will be default to sys_context('userenv', 'con_id')
336 ---
337 --- ------------------------------------------------------------------------
338 --- function display_sqlset(sqlset_name varchar2,
339 --- sql_id varchar2,
340 --- plan_hash_value integer default null,
341 --- format varchar2 default 'TYPICAL',
342 --- sqlset_owner varchar2 default null)
343 ---
344 --- - sqlset_name:
345 --- specified the name of the SQL tuning set.
346 ---
347 --- - sql_id:
348 --- specifies the sql_id value for a SQL statement having its plan(s)
349 --- stored in the SQL tuning set. You can find all stored SQL
350 --- statements by querying USER/DBA/ALL_SQLSET_PLANS or table function
351 --- SELECT_SQLSET from package dbms_sqltune.
352 ---
353 --- - plan_hash_value:
354 --- identifies a specific stored execution plan for a SQL statement.
355 --- Optional parameter. If suppressed, all stored execution plans are
356 --- shown.
357 ---
358 --- - format:
359 --- The format string has the same meaning as that for the regular
360 --- display() table function (see format description above).
361 ---
362 --- - sqlset_owner:
363 --- Specifies the owner of the SQL tuning set. The default is the
364 --- name of the current user.
365 ---
366 --- ------------------------------------------------------------------------
367 --- function display_sql_plan_baseline(
368 --- sql_handle varchar2 default NULL,
369 --- plan_name varchar2 default NULL,
370 --- format varchar2 default 'TYPICAL')
371 ---
372 --- - sql_handle:
373 --- SQL statement handle. It identifies the SQL statement whose plans
374 --- are to be explained and displayed. If NULL then PLAN_NAME must be
375 --- specified.
376 --- You can find SQL plan baselines created for various SQL statements
377 --- by querying DBA_SQL_PLAN_BASELINES catalog view.
378 ---
379 --- - plan_name:
380 --- Plan name. It identifies a specific plan to be explained and
381 --- displayed. Default NULL means all plans associated with identified
382 --- SQL statement to be explained and displayed. If NULL then
383 --- sql_handle must be specified.
384 ---
385 --- - format:
386 --- The format string has the same meaning as that for the regular
390 --- Examples DBMS_XPLAN.DISPLAY():
387 --- display() table function (see format description above).
388 ---
389 --- ------------------------------------------------------------------------
391 ---
392 --- 1/ display the last explain plan stored in the plan table:
393 ---
394 --- set linesize 150
395 --- set pagesize 2000
396 --- select * from table(dbms_xplan.display);
397 ---
398 ---
399 --- 2/ display from the plan table "my_plan_table":
400 ---
401 --- set linesize 150
402 --- set pagesize 2000
403 --- select * from table(dbms_xplan.display('my_plan_table'));
404 ---
405 ---
406 --- 3/ display minimum plan table:
407 ---
408 --- set linesize 150
409 --- set pagesize 2000
410 --- select * from table(dbms_xplan.display(null, null,'basic'));
411 ---
412 ---
413 --- 4/ display all information in plan table, excluding projection:
414 ---
415 --- set linesize 150
416 --- set pagesize 2000
417 --- select * from table(dbms_xplan.display(null, null,
418 --- 'all -projection'));
419 ---
420 ---
421 --- 5/ display the plan whose statement_id is 'foo':
422 ---
423 --- set linesize 150
424 --- set pagesize 2000
425 --- select * from table(dbms_xplan.display('plan_table', 'foo'));
426 ---
427 ---
428 --- 6/ display statpack plan for hash_value=76725 and snap_id=245
429 ---
430 --- set linesize 150
431 --- set pagesize 2000
432 --- select * from table(dbms_xplan.display('stats$sql_plan', null,
433 --- 'all', 'hash_value=76725 and snap_id=245'));
434 ---
435 --- ------------------------------------------------------------------------
436 --- Examples DBMS_XPLAN.DISPLAY_CURSOR():
437 ---
438 --- 1/ display the currently or last executed statement
439 --- (this will also show the usage of this package)
440 ---
441 --- set linesize 150
442 --- set pagesize 2000
443 --- select * from table(dbms_xplan.display_cursor);
444 ---
445 ---
446 --- 2/ display the currently or last executed statement of session id 9
447 --- (it will return 'no rows selected' for any SQL statement using
448 --- this package)
449 ---
450 --- - Identify the sql_id and the child_number in
451 --- a separate SQL statement and use them as parameters for
452 --- DISPLAY_CUSRSOR()
453 ---
454 --- SQL> select prev_sql_id, prev_child_number
455 --- from v$session where sid=9;
456 ---
457 --- PREV_SQL_ID PREV_CHILD_NUMBER
458 --- ------------- -----------------
459 --- f98t6zufy04g5 0
460 ---
461 --- set linesize 150
462 --- set pagesize 2000
463 --- select *
464 --- from table(dbms_xplan.display_cursor('f98t6zufy04g5', 0));
465 ---
466 --- - Alternatively, you can combine the two statements into one
467 ---
468 --- set linesize 150
469 --- set pagesize 2000
470 --- select t.*
471 --- from v$session s,
472 --- table(dbms_xplan.display_cursor(s.prev_sql_id,
473 --- s.prev_child_number)) t
474 --- where s.sid=9;
475 ---
476 --- NOTE: the table deriving the input parameters for
477 --- DBMS_XPLAN.DISPLAY_CURSOR() must be the FIRST (left-side)
478 --- table(s) in the select statement relative to the table function
479 ---
480 ---
481 --- 3/ display all cursors containing the case sensisitve string 'FoOoO',
482 --- excluding SQL parsed by SYS
483 ---
484 --- set linesize 150
485 --- set pagesize 2000
486 --- select t.*
487 --- from v$sql s,
488 --- table(dbms_xplan.display_cursor(s.sql_id,
489 --- s.child_number)) t
490 --- where s.sql_text like '%FoOoO%' and s.parsing_user_id <> 0;
491 ---
492 ---
493 --- 4/ display all information about all cursors containing the case
494 --- insensitive string 'FOO', including SQL parsed by SYS
495 ---
496 --- set linesize 150
497 --- set pagesize 2000
498 --- select t.*
499 --- from v$sql s,
500 --- table(dbms_xplan.display_cursor(s.sql_id,
501 --- s.child_number, 'ALL')) t
502 --- where upper(s.sql_text) like '%FOO%';
503 ---
504 ---
505 --- 5/ display the last executed runtime statistics for all cursors
506 --- containing the case insensitive string 'sales', including SQL
507 --- parsed by SYS
508 ---
509 --- set linesize 150
510 --- set pagesize 2000
511 --- select t.*
512 --- from v$sql s,
513 --- table(dbms_xplan.display_cursor(s.sql_id, s.child_number,
514 --- 'ALLSTATS LAST')) t
515 --- where lower(s.sql_text) like '%sales%';
516 ---
517 ---
518 --- 6/ display the aggregated runtime statistics for all cursors containing
519 --- the case sensitive string 'sAleS' and were parsed by user SH
520 ---
524 --- from v$sql s, dba_users u,
521 --- set linesize 150
522 --- set pagesize 2000
523 --- select t.*
525 --- table(dbms_xplan.display_cursor(s.sql_id, s.child_number,
526 --- 'RUNSTATS_TOT')) t
527 --- where s.sql_text like '%sAleS%'
528 --- and u.user_id=s.parsing_user_id
529 --- and u.username='SH';
530 ---
531 --- ------------------------------------------------------------------------
532 --- Examples DBMS_XPLAN.DISPLAY_AWR():
533 ---
534 --- 1/ display all stored plans in the AWR containing
535 --- the case sensitive string 'sAleS'. Don't display predicate
536 --- information but add the query block name / alias section.
537 ---
538 --- set linesize 150
539 --- set pagesize 2000
540 --- select t.*
541 --- from dba_hist_sqltext ht,
542 --- table(dbms_xplan.display_awr(ht.sql_id, null, null,
543 --- '-PREDICATE +ALIAS')) t
544 --- where ht.sql_text like '%sAleS%';
545 ---
546 --- NOTE: the table deriving the input parameters for
547 --- DBMS_XPLAN.DISPLAY_AWR() must be the FIRST (left-side)
548 --- table(s) in the select statement relative to the table
549 --- function.
550 ---
551 --- ------------------------------------------------------------------------
552 --- Examples DBMS_XPLAN.DISPLAY_SQLSET():
553 ---
554 --- 1/ display all stored plans for a given statement in the SQL tuning set
555 --- named 'my_sts' owner by the current user (the caller).
556 ---
557 --- set linesize 150
558 --- set pagesize 2000
559 --- select *
560 --- from table(dbms_xplan.display_sqlset('my_sts',
561 --- 'gcfysssf6hykh',
562 --- null,
563 --- 'ALL -NOTE -PROJECTION')) t
564 ---
565 --- ------------------------------------------------------------------------
566 --- Examples DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE():
567 ---
568 --- 1/ display all plans of a SQL statement identified by the sql handle
569 --- 'SYS_SQL_b1d49f6074ab95af' using TYPICAL format.
570 ---
571 --- set linesize 150
572 --- set pagesize 2000
573 --- select t.*
574 --- from table(dbms_xplan.display_sql_plan_baseline(
575 --- 'SYS_SQL_b1d49f6074ab95af')) t;
576 ---
577 --- 2/ display all plans of one or more SQL statements containing the
578 --- string 'HR2' using BASIC format.
579 ---
580 --- set linesize 150
581 --- set pagesize 2000
582 --- select t.*
583 --- from (select distinct sql_handle from dba_sql_plan_baselines
584 --- where sql_text like '%HR2%') pb,
585 --- table(dbms_xplan.display_sql_plan_baseline(pb.sql_handle, null,
586 --- 'basic')) t;
587 ---
588 --- NOTE: the table deriving the input parameters for
589 --- DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE() must be the first
590 --- (left-side) table in the select statement relative to the
591 --- table function.
592 ---
593 --- -------------------------------------------------------------------------
594
595 -- display from PLAN_TABLE
596 function display(table_name varchar2 default 'PLAN_TABLE',
597 statement_id varchar2 default null,
598 format varchar2 default 'TYPICAL',
599 filter_preds varchar2 default null)
600 return dbms_xplan_type_table
601 pipelined;
602
603 -- display from V$SQL_PLAN (or V$SQL_PLAN_STATISTICS_ALL)
604 function display_cursor(sql_id varchar2 default null,
605 cursor_child_no integer default 0,
606 format varchar2 default 'TYPICAL')
607 return dbms_xplan_type_table
608 pipelined;
609
610 -- display from AWR
611 function display_awr(sql_id varchar2,
612 plan_hash_value integer default null,
613 db_id integer default null,
614 format varchar2 default 'TYPICAL',
615 con_id integer default null)
616 return dbms_xplan_type_table
617 pipelined;
618
619 -- display from SQL tuning set
620 function display_sqlset(sqlset_name varchar2,
621 sql_id varchar2,
622 plan_hash_value integer default null,
623 format varchar2 default 'TYPICAL',
624 sqlset_owner varchar2 default null)
625 return dbms_xplan_type_table
626 pipelined;
627
628 -- display from SQL plan baseline
629 function display_sql_plan_baseline(sql_handle varchar2 default null,
630 plan_name varchar2 default null,
631 format varchar2 default 'TYPICAL')
632 return dbms_xplan_type_table
633 pipelined;
634
635 -- display from SQL profile
636 function display_sql_profile_plan(name varchar2,
637 format varchar2 default 'TYPICAL')
638 return dbms_xplan_type_table
642 function display_sql_patch_plan(name varchar2,
639 pipelined;
640
641 -- display from SQL patch
643 format varchar2 default 'TYPICAL')
644 return dbms_xplan_type_table
645 pipelined;
646
647 ----------------------------------------------------------------------------
648 -- ---------------------------------------------------------------------- --
649 -- --
650 -- The folloing section of this package contains functions and procedures --
651 -- which are for INTERNAL use ONLY. PLEASE DO NO DOCUMENT THEM. --
652 -- --
653 -- ---------------------------------------------------------------------- --
654 ----------------------------------------------------------------------------
655 -- private procedure, used internally
656 function prepare_records(plan_cur IN sys_refcursor,
657 i_format_flags IN number,
658 diag_repos_cur IN sys_refcursor default null)
659 return dbms_xplan_type_table
660 pipelined;
661
662 -- private function to validate the user format (used internally)
663 function validate_format(hasPlanStats IN boolean,
664 format IN VARCHAR2,
665 format_flags OUT NUMBER)
666 return boolean;
667
668 FUNCTION format_size(num number)
669 RETURN varchar2;
670
671 FUNCTION format_number(num number)
672 RETURN varchar2;
673
674 FUNCTION format_size2(num number)
675 RETURN varchar2;
676
677 FUNCTION format_number2(num number)
678 RETURN varchar2;
679
680 FUNCTION i_display_smb_plan(name IN VARCHAR2,
681 format IN VARCHAR2 DEFAULT 'TYPICAL',
682 obj_type IN NUMBER )
683 RETURN dbms_xplan_type_table
684 pipelined;
685
686 --
687 -- formats a number representing time in seconds using the format HH:MM:SS.
688 -- This function is internal to this package
689 --
690 function format_time_s(num number)
691 return varchar2;
692
693 --
694 -- This is a helper function to build the XML version of the text of the
695 -- select query that is run before the display display function to retrieve
696 -- and display the execution plan of a SQL.
697 -- All this function does it to wrap a given query, used to fetch a plan, by
698 -- XML construct. The goal is to maintain ONE and SINGLE version of the XML
699 -- format we use for the plan table.
700 --
701 -- This function is also called by prvtspai.sql in sqltune directory.
702 -- table_query : query to fetch plan from a plan table
703 --
704 FUNCTION prepare_plan_xml_query(
705 plan_query IN VARCHAR2) -- query to fetch plan table
706 RETURN VARCHAR2;
707
708 --
709 -- This function builds the xml version of an explain plan.
710 -- The function queries the caller specified plan table and format the
711 -- resulting plan lines into XML before returning them to the caller.
712 --
713 -- table_name : name of the table or view that stores the plan
714 -- statement_id: identifier of the sql statement in the plan table
715 -- plan_id : identifier of the sql plan in the plan table. Currently
716 -- used by sql replay only. SQL replay is used to produce plans
717 -- for SQL stored in sql tuning set using plan_ids and so
718 -- we need to be able to share the query we use to get the plans.
719 -- format : format of the plan output. See description in
720 -- function display
721 -- filter_preds: predicate to filter the content of the plan table
722 -- plan_tag : caller specified name of the root element in the plan xml
723 -- tree. by default it is set to 'xplan'
724 -- report_ref : optional report reference. Needed only to generate
725 -- xml of the servelet.
726 --
727 function build_plan_xml(
728 table_name in varchar2 default 'PLAN_TABLE',
729 statement_id in varchar2 default NULL,
730 plan_id in number default NULL,
731 format in varchar2 default 'TYPICAL',
732 filter_preds in varchar2 default NULL,
733 plan_tag in varchar2 default 'plan',
734 report_ref in varchar2 default NULL)
735 return xmltype;
736
737 --
738 -- This function returns an explain plan in a CLOB format.
739 -- The function queries the caller specified plan table, generate the
740 -- resulting plan lines into XML and then calls the XML reporting framework
741 -- the produce and return the plan as a CLOB.
742 --
743 -- table_name : name of the table or view that stores the plan
744 -- statement_id: identifier of the sql statement in the plan table
745 -- format : format of the plan output. See description in
746 -- function display
747 -- filter_preds: predicate to filter the content of the plan table
748 -- type : type of output. Possible values are:
749 -- TEXT (default), HTML, ACTIVE, or XML.
750 --
751 function display_plan(
752 table_name in varchar2 default 'PLAN_TABLE',
753 statement_id in varchar2 default NULL,
754 format in varchar2 default 'TYPICAL',
755 filter_preds in varchar2 default NULL,
759 -- *** do not document this type ***
756 type in varchar2 default 'TEXT')
757 return clob;
758
760 -- type and array for first records similar to PLAN_TABLE
761 -- added additional runtime stat columns - those will be filled with NULL
762 ---values for DBMS_XPLAN.DISPLAY
763 --
764 type plan_record is record (
765 id number, -- operation id
766 parent_id number, -- parent id
767 partition_id number, -- partition id
768 timestamp date, -- time of plan generation
769 optimizer varchar2(20), -- optimizer mode
770 position number, -- position used to detect RBO
771 search_columns number, -- search columns
772 depth number, -- depth
773 operation varchar2(300), -- operation
774 options varchar2(255), -- options
775 object_name varchar2(64), -- name of the object
776 object_owner varchar2(64), -- owner of the object
777 object_type varchar2(30), -- type of the object
778 object_instance number, -- object instance
779 cardinality number, -- optimizer cardinality
780 bytes number, -- optimizer bytes
781 temp_space number, -- temp space consumption
782 cost number, -- optimizer cost
783 io_cost number, -- optimizer cpu cost
784 cpu_cost number, -- optimizer cpu cost
785 time number, -- optimizer estimated time
786 pstart varchar2(255), -- pruning info (start)
787 pstop varchar2(255), -- pruning info (stop)
788 object_node varchar2(128), -- tq id for PQ
789 other_tag varchar2(255), -- other_tag for PQ
790 distrib varchar2(30), -- PX distribution
791 projection varchar2(4000), -- projection information
792 access_pred varchar2(4000), -- access predicates
793 filter_pred varchar2(4000), -- filter predicates
794 other varchar2(32767),-- other tag, slave SQL
795 qblock_name varchar2(64), -- query block name
796 object_alias varchar2(65), -- object alias
797 other_xml clob, -- other_xml
798 sql_profile varchar2(64), -- sql_profile in v$sql
799 sql_plan_baseline varchar2(30), -- sql_plan_baseline in v$sql
800 starts number, -- number of starts
801 outrows number, -- number of rows output by node
802 crgets number, -- number of cr buffer gets
803 cugets number, -- number of cr buffer gets
804 reads number, -- number of physical reads
805 writes number, -- number of physical writes
806 etime number, -- elapsed time
807 mem_opt number, -- optimal memory requirement
808 mem_one number, -- one-pass memory requirement
809 last_mem_used number, -- last memory used
810 last_mem_usage VARCHAR2(10), -- last memory usage (e.g. OPTIMAL)
811 mem_opt_cnt number, -- count of optimal memory usage
812 mem_one_cnt number, -- count of one-pass memory usage
813 mem_multi_cnt number, -- count of multi-pass memory usage
814 max_tmp_used number, -- max temp used
815 last_tmp_used number); -- last temp used
816 type plan_table is table of plan_record;
817
818 -- do not document this function
819 FUNCTION get_plan_rows(table_name in varchar2 default 'PLAN_TABLE',
820 statement_id IN varchar2 default null,
821 plan_id IN varchar2 default null,
822 format IN VARCHAR2 DEFAULT 'TYPICAL',
823 filter_preds in varchar2 default null,
824 mask_cost in number default 0)
825 RETURN plan_table
826 pipelined;
827
828 -- do not document this function
829 FUNCTION get_cursor_rows(sql_id varchar2 default null,
830 cursor_child_no integer default 0,
831 format varchar2 default 'TYPICAL')
832 RETURN plan_table
833 PIPELINED;
834
835
836 ----------------------------- diff_plan_outline ------------------------------
837 --
838 -- This function compares two sql plans generated by the given outlines
839 -- The job is done via a SQLDiag task and the function returns the task_id
840 --
841 -- PARAMETERS:
842 -- sql_text (IN) - text of the SQL statement
843 -- outline1 (IN) - outline - for base plan
844 -- outline2 (IN) - outline - for target plan
845 -- user_name (IN) - the parsing schema name
846 -- default to current user
847 --
848 -- RETURN:
849 -- task_id: can be used to retrieve the report of findings later
850 ------------------------------------------------------------------------------
851 function diff_plan_outline(
852 sql_text in clob,
853 outline1 in clob,
854 outline2 in clob,
855 user_name in varchar2 := NULL)
856 return varchar2;
857
858 ----------------------------- diff_plan -------------------------------------
859 --
860 -- This function compares two sql plans
861 -- reference plan: implicitly defined
862 -- target plan: a plan generated by the given outline
863 --
864 -- The job is done via a SQLDiag task and the function returns the task_id
865 --
866 -- PARAMETERS:
867 -- sql_text (IN) - text of the SQL statement
868 -- outline (IN) - used to generate the target plan
869 -- user_name (IN) - the parsing schema name
870 -- default to current user
871 --
872 -- RETURN:
873 -- task_id: can be used to retrieve the report of findings later
874 ------------------------------------------------------------------------------
875 function diff_plan(
876 sql_text in clob,
877 outline in clob,
878 user_name in varchar2 := NULL)
879 return varchar2;
880
881 ----------------------------- diff_plan_sql_baseline -------------------------
882 --
883 -- This function compares two given sql plans (specified via plan_names)
884 -- The job is done via a SQLDiag task and the function returns the task_id
885 --
886 -- PARAMETERS:
887 -- baseline_plan_name1 (IN) - plan name - base
888 -- baseline_plan_name2 (IN) - plan name - target
889 --
890 -- RETURN:
891 -- task_id: can be used to retrieve the report of findings later
892 ------------------------------------------------------------------------------
893 function diff_plan_sql_baseline(
894 baseline_plan_name1 in varchar2,
895 baseline_plan_name2 in varchar2)
896 return varchar2;
897
898 ----------------------------- diff_plan_cursor -------------------------------
899 --
900 -- This function compares two sql plans derived from the given cursor child #
901 -- The job is done via a SQLDiag task and the function returns the task_id
902 --
903 -- PARAMETERS:
904 -- sql_id (IN) - sql id to specify a SQL statement
905 -- cursor_child_num1 (IN) - child number - for base plan
906 -- cursor_child_num2 (IN) - child number - for target plan
907 --
908 -- RETURN:
909 -- task_id: can be used to retrieve the report of findings later
910 ------------------------------------------------------------------------------
911 function diff_plan_cursor(
912 sql_id IN VARCHAR2,
913 cursor_child_num1 IN NUMBER,
914 cursor_child_num2 IN NUMBER)
915 return varchar2;
916
917 ----------------------------- diff_plan_awr ----------------------------------
918 --
919 -- This function compares two sql plans specified by the given plan hash ids
920 -- The job is done via a SQLDiag task and the function returns the task_id
921 --
922 -- PARAMETERS:
923 -- sql_id (IN) - sql id to specify a SQL statement
924 -- plan_hash_value1 (IN) - base plan
925 -- plan_hash_value1 (IN) - target plan
926 --
927 -- RETURN:
928 -- task_id: can be used to retrieve the report of findings later
929 ------------------------------------------------------------------------------
930 function diff_plan_awr(
931 sql_id IN VARCHAR2,
932 plan_hash_value1 IN NUMBER,
933 plan_hash_value2 IN NUMBER)
934 return varchar2;
935
936 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
937 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
938 -- ------------------------------------------ --
939 -- PLAN DIFF SUPPORT FUNCTIONS --
940 -- ------------------------------------------ --
941 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
942 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
943 ------------------------------ get_report_xml --------------------------------
944 -- NAME:
945 -- get_report_xml
946 --
947 -- DESCRIPTION:
948 -- This function builds the entire report in XML.
949 --
950 -- PARAMETERS:
951 -- report_ref (IN) - the report reference string that
952 -- identifies this report
953 -- tid (IN) - task ID
954 -- method (IN) - method of comparing, eg, 'outline'
955 -- RETURN:
956 -- the report in XML
957 ------------------------------------------------------------------------------
958 FUNCTION get_plandiff_report_xml(
959 report_ref IN VARCHAR2 := NULL,
960 tid IN NUMBER,
961 method IN VARCHAR2)
962 RETURN XMLTYPE;
963
964 end dbms_xplan;
[Home] [Help]