1 PROCEDURE dbms_feature_services
2 (is_used OUT number, hwm OUT number, feature_info OUT clob)
3 AS
4 -- Based off dba_services
5 num_clb_long NUMBER := 0;
6 num_clb_short NUMBER := 0;
7 num_goal_service_time NUMBER := 0;
8 num_goal_throughput NUMBER := 0;
9 num_goal_none NUMBER := 0;
10 num_goal_null NUMBER := 0;
11 num_aq_notifications NUMBER := 0;
12
13 -- Based off gv$active_services
14 num_active_svcs NUMBER := 0;
15 num_active_svcs_wo_distinct NUMBER := 0;
16 avg_active_cardinality NUMBER := 0;
17
18 default_service_name varchar2(1000);
19 default_xdb_service_name varchar2(1000);
20 db_domain varchar2(1000);
21
22 BEGIN
23 -- initialize
24 is_used := 0;
25 hwm := 0;
26 feature_info := 'Services usage not detected';
27
28 -- get default service name - db_unique_name[.db_domain]
29
30 SELECT value INTO default_service_name FROM v$parameter WHERE
31 lower(name) = 'db_unique_name';
32
33 SELECT value INTO db_domain FROM v$parameter WHERE
34 lower(name) = 'db_domain';
35
36 -- create default XDB service name
37 default_xdb_service_name := default_service_name || 'XDB';
38
39 -- append db_domain if it is set
40 IF db_domain IS NOT NULL then
41 default_service_name := default_service_name || '.' || db_domain;
42 END IF;
43
44 SELECT count(*) INTO hwm
45 FROM dba_services
46 WHERE
47 NAME NOT LIKE 'SYS$%'
48 AND NETWORK_NAME NOT LIKE 'SYS$%'
49 AND NAME <> default_xdb_service_name
50 AND NAME <> default_service_name;
51
52 IF hwm > 0 THEN
53 is_used := 1;
54 END IF;
55
56 -- if services is used
57 IF (is_used = 1) THEN
58
59 -- Get the counts for CLB_GOAL variations
60 FOR item IN (
61 SELECT clb_goal, count(*) cg_count
62 FROM dba_services
63 where
64 NAME NOT LIKE 'SYS$%'
65 AND NETWORK_NAME NOT LIKE 'SYS$%'
66 AND NAME <> default_xdb_service_name
67 AND NAME <> default_service_name
68 GROUP BY clb_goal)
69
70 LOOP
71
72 IF item.clb_goal = 'SHORT' THEN
73 num_clb_short := item.cg_count;
74 ELSIF item.clb_goal = 'LONG' THEN
75 num_clb_long := item.cg_count;
76 END IF;
77
78 END LOOP;
79
80
81 -- Get the counts for GOAL variations
82 FOR item IN (
83 SELECT goal, count(*) g_count
84 FROM dba_services
85 where
86 NAME NOT LIKE 'SYS$%'
87 AND NETWORK_NAME NOT LIKE 'SYS$%'
88 AND NAME <> default_xdb_service_name
89 AND NAME <> default_service_name
90 GROUP BY goal)
91
92 LOOP
93
94 IF item.goal = 'SERVICE_TIME' THEN
95 num_goal_service_time := item.g_count;
96 ELSIF item.goal = 'THROUGHPUT' THEN
97 num_goal_throughput := item.g_count;
98 ELSIF item.goal = 'NONE' THEN
99 num_goal_none := item.g_count;
100 ELSIF item.goal is NULL THEN
101 num_goal_null := item.g_count;
102 END IF;
103
104 END LOOP;
105
106 -- count goal is NULL as goal = NONE
107 num_goal_none := num_goal_none + num_goal_null;
108
109 -- Get the count for aq_ha_notifications
110 SELECT count(*) into num_aq_notifications
111 FROM dba_services
112 where
113 NAME NOT LIKE 'SYS$%'
114 AND NETWORK_NAME NOT LIKE 'SYS$%'
115 AND NAME <> default_xdb_service_name
116 AND NAME <> default_service_name
117 AND AQ_HA_NOTIFICATIONS = 'YES';
118
119
120 SELECT count(distinct name), count(*)
121 INTO num_active_svcs, num_active_svcs_wo_distinct
122 FROM gv$active_services
123 WHERE
124 NAME NOT LIKE 'SYS$%'
125 AND NETWORK_NAME NOT LIKE 'SYS$%'
126 AND NAME <> default_xdb_service_name
127 AND NAME <> default_service_name;
128
129 IF num_active_svcs > 0 THEN
130
131 avg_active_cardinality :=
132 round(num_active_svcs_wo_distinct / num_active_svcs);
133
134 END IF;
135
136 feature_info :=
137 ' num_clb_long: ' || num_clb_long
138 ||' num_clb_short: ' || num_clb_short
139 ||' num_goal_service_time: ' || num_goal_service_time
140 ||' num_goal_throughput: ' || num_goal_throughput
141 ||' num_goal_none: ' || num_goal_none
142 ||' num_aq_notifications: ' || num_aq_notifications
143 ||' num_active_services: ' || num_active_svcs
144 ||' avg_active_cardinality: '|| avg_active_cardinality;
145
146 END IF;
147
148 END;
[Home] [Help]