[Home] [Help]
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;