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