PROCEDURE: SYS.DBMS_FEATURE_AUTO_REOPT

Source

1 PROCEDURE dbms_feature_auto_reopt
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   reporting_param         VARCHAR2(10);
10   use_feedback_param      VARCHAR2(10);
11   num_all_queries         NUMBER;   -- number of queries
12   num_reopt_queries       NUMBER;   -- number of reoptimizable queries
13   tmp_buf                 VARCHAR2(32767);
14 
15 BEGIN
16   dbms_lob.createtemporary(feature_info, TRUE);
17 
18   select ksppstvl
19   into reporting_param
20   from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and
21   ksppinm = 'optimizer_adaptive_reporting_only';
22 
23   select ksppstvl
24   into use_feedback_param
25   from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and
26   ksppinm = '_optimizer_use_feedback';
27 
28   if (reporting_param = 'FALSE' AND use_feedback_param = 'TRUE') then
29     feature_boolean := 1;
30   else
31     feature_boolean := 0;
32   end if;
33 
34   -- Find # of sqls in v$sql
35   select count(*)
36   into   num_all_queries
37   from   v$sql vs, v$sqlcommand vsc
38   where vs.command_type = vsc.command_type and
39   vsc.command_name in ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'UPSERT');
40 
41   -- Find # of sqls which are reoptimizable
42   select count(*)
43   into   num_reopt_queries
44   from   v$sql vs, v$sqlcommand vsc
45   where vs.command_type = vsc.command_type and vs.is_reoptimizable = 'Y'
46   and vsc.command_name in ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'UPSERT');
47 
48   tmp_buf := 'Total number of queries: ' || num_all_queries ||
49              NEW_LINE ;
50 
51   dbms_lob.writeappend(feature_info, length(tmp_buf), tmp_buf);
52 
53   tmp_buf := 'Number of reoptimizable queries: ' || num_reopt_queries ||
54              NEW_LINE ;
55 
56   dbms_lob.writeappend(feature_info, length(tmp_buf), tmp_buf);
57 
58   tmp_buf := 'Percentage of reoptimizable queries: ' ||
59              100*num_reopt_queries/num_all_queries ||
60              NEW_LINE ;
61 
62   dbms_lob.writeappend(feature_info, length(tmp_buf), tmp_buf);
63 
64 END dbms_feature_auto_reopt;