sql/RAC_Xplan.sql at master · JAC2703/sql · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
declare

------------------------
-- PROCEDURE: Display --
------------------------
/******************************************************************************
Wrapper around DBMS_Xplan.Display to view plans in a RAC environment.

PARAMETERS: Inst_Id : Instance identifier.
            SQL_Id  : SQL idendifier.
            Format  : DBMS_Xplan display format (see Oracle documentation for options.)
                    : Defaults to ADVANCED.

NOTES: Run as an anonymous block rather than a piplined function due to grants.

******************************************************************************/

    procedure Display(
                  Inst_Id in number
              ,   SQL_Id  in varchar2
              ,   Format  in varchar2 := 'ADVANCED'
              )

    is

    ---------------
    -- constants --
    ---------------

        Plan_View constant varchar2(30) := 'gv$sql_plan_statistics_all';

    ---------------
    -- variables --
    ---------------

        Child_No        number;
        Filter_Template varchar2(100) := q'[inst_id = %s and sql_id = '%s' and child_number = %s]';

    ---------------------------------------------------------------------------

    begin

        select
            child_number
        into
            Child_No
        from
            gv$sql_plan_statistics_all
        where
            inst_id = Display.Inst_Id
        and sql_id  = Display.SQL_Id
        and rownum < 2;

        Filter_Template := Sys.Utl_Lms.Format_Message(Filter_Template, to_char(Inst_Id), SQL_Id, to_char(Child_No));

        <<Plan_Loop>>
        for Current_Plan_Row in(
            select
                plan_table_output
            from
                table(
                    Sys.DBMS_Xplan.Display(
                                       Table_Name   => Plan_View
                                   ,   Format       => Display.Format
                                   ,   Filter_Preds => Filter_Template
                                   )
                )
        ) loop

            Sys.DBMS_Output.Put_Line(Current_Plan_Row.Plan_Table_Output);

        end loop Plan_Loop;

    end Display;

---------------------
-- PROCEDURE: Main --
---------------------

    procedure Main is

    begin

        Display(
            Inst_Id => &1
        ,   SQL_Id  => '&2'
        );

    end Main;

-------------------------------------------------------------------------------

begin

    Main;

end;
/