PROCEDURE: SYS.DBMS_FEATURE_ADAPTIVE_PLANS

Source

1 PROCEDURE dbms_feature_adaptive_plans
2   (feature_boolean    OUT  NUMBER,
3    aux_count          OUT  NUMBER,
4    feature_info       OUT  CLOB)
5 AS
6   NEW_LINE      CONSTANT  VARCHAR2(8) := '
7 ';
8 
9   adaptive_plan_param     VARCHAR2(10);   -- adaptive plans parameter
10   reporting_param         VARCHAR2(10);   -- reporting mode parameter
11   num_all_queries         NUMBER;         -- number of queries
12   num_adaptive_queries    NUMBER;         -- number of adaptive queries
13   reporting_param_value   VARCHAR2(10);   -- reporting mode param value
14   tmp_buf                 VARCHAR2(32767);
15 
16 BEGIN
17   dbms_lob.createtemporary(feature_info, TRUE);
18 
19   select ksppstvl
20   into adaptive_plan_param
21   from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and
22   ksppinm = 'optimizer_adaptive_plans';
23 
24   select ksppstvl
25   into reporting_param
26   from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and
27   ksppinm = 'optimizer_adaptive_reporting_only';
28 
29   if (reporting_param = 'FALSE' AND adaptive_plan_param = 'TRUE') then
30     feature_boolean := 1;
31   else
32     feature_boolean := 0;
33   end if;
34 
35   if (reporting_param = 'FALSE') then
36     reporting_param_value := 'No';
37   else
38     reporting_param_value := 'Yes';
39   end if;
40 
41 
42   -- Find # of sqls in v$sql
43   select count(*)
44   into   num_all_queries
45   from   v$sql vs, v$sqlcommand vsc
46   where vs.command_type = vsc.command_type and
47   vsc.command_name in ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'UPSERT');
48 
49   -- Find # of sqls which are adaptive
50   select count(*)
51   into   num_adaptive_queries
52   from   v$sql vs, v$sqlcommand vsc
53   where vs.command_type = vsc.command_type and
54   vs.is_resolved_adaptive_plan is NOT NULL and
55   vsc.command_name in ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'UPSERT');
56 
57   tmp_buf := 'Total number of queries: ' || num_all_queries ||
58              NEW_LINE ;
59 
60   dbms_lob.writeappend(feature_info, length(tmp_buf), tmp_buf);
61 
62   tmp_buf := 'Number of queries with an adaptive plan: ' ||
63               num_adaptive_queries ||
64              NEW_LINE ;
65 
66   dbms_lob.writeappend(feature_info, length(tmp_buf), tmp_buf);
67 
68   tmp_buf := 'Percentage of queries with an adaptive plan: ' ||
69              100*num_adaptive_queries/num_all_queries ||
70              NEW_LINE ;
71 
72   dbms_lob.writeappend(feature_info, length(tmp_buf), tmp_buf);
73 
74   tmp_buf := 'Are the queries running in reporting mode ? : ' ||
75              reporting_param_value ||
76              NEW_LINE ;
77 
78   dbms_lob.writeappend(feature_info, length(tmp_buf), tmp_buf);
79 
80 END dbms_feature_adaptive_plans;