1/*
2** Zabbix
3** Copyright (C) 2001-2021 Zabbix SIA
4**
5** This program is free software; you can redistribute it and/or modify
6** it under the terms of the GNU General Public License as published by
7** the Free Software Foundation; either version 2 of the License, or
8** (at your option) any later version.
9**
10** This program is distributed in the hope that it will be useful,
11** but WITHOUT ANY WARRANTY; without even the implied warranty of
12** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13** GNU General Public License for more details.
14**
15** You should have received a copy of the GNU General Public License
16** along with this program; if not, write to the Free Software
17** Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
18**/
19
20package oracle
21
22import (
23	"context"
24
25	"zabbix.com/pkg/zbxerr"
26)
27
28func sessionsHandler(ctx context.Context, conn OraClient, params map[string]string, _ ...string) (interface{}, error) {
29	var (
30		sessions string
31		err      error
32	)
33
34	row, err := conn.QueryRow(ctx, `
35		SELECT
36			JSON_OBJECTAGG(v.METRIC VALUE v.VALUE)
37		FROM
38			(
39			SELECT
40				METRIC, SUM(VALUE) AS VALUE
41			FROM
42				(
43				SELECT
44					LOWER(REPLACE(STATUS || ' ' || TYPE, ' ', '_')) AS METRIC,
45					COUNT(*) AS VALUE
46				FROM
47					V$SESSION
48				GROUP BY
49					STATUS, TYPE
50
51				UNION
52
53				SELECT
54					DISTINCT *
55				FROM
56					TABLE(sys.ODCIVARCHAR2LIST('inactive_user', 'active_user', 'active_background')),
57					TABLE(sys.ODCINUMBERLIST(0, 0, 0))
58				)
59			GROUP BY
60				METRIC
61
62			UNION
63
64			SELECT
65				'total' AS METRIC,
66				COUNT(*) AS VALUE
67			FROM
68				V$SESSION
69
70			UNION
71
72			SELECT
73				'long_time_locked' AS METRIC,
74				COUNT(*) AS VALUE
75			FROM
76				V$SESSION
77			WHERE
78				BLOCKING_SESSION IS NOT NULL
79				AND BLOCKING_SESSION_STATUS = 'VALID'
80				AND SECONDS_IN_WAIT > :1
81
82			UNION
83
84			SELECT
85				'lock_rate' ,
86				(CNT_BLOCK / CNT_ALL) * 100 pct
87			FROM
88				(
89				SELECT
90					COUNT(*) CNT_BLOCK
91				FROM
92					V$SESSION
93				WHERE
94					BLOCKING_SESSION IS NOT NULL),
95				(
96				SELECT
97					COUNT(*) CNT_ALL
98				FROM
99					V$SESSION)
100			UNION
101			SELECT
102				'concurrency_rate',
103				NVL(ROUND(SUM(duty_act.CNT * 100 / num_cores.VAL)), 0)
104			FROM
105				(
106					SELECT
107						DECODE(SESSION_STATE, 'ON CPU', 'CPU', WAIT_CLASS) WAIT_CLASS, ROUND(COUNT(*) / (60 * 15), 1) CNT
108					FROM
109						V$ACTIVE_SESSION_HISTORY sh
110					WHERE
111						sh.SAMPLE_TIME >= SYSDATE - 15 / 1440
112						AND DECODE(SESSION_STATE, 'ON CPU', 'CPU', WAIT_CLASS) IN ('Concurrency')
113					GROUP BY
114						DECODE(SESSION_STATE, 'ON CPU', 'CPU', WAIT_CLASS)
115				) duty_act,
116				(
117					SELECT
118						SUM(VALUE) VAL
119					FROM
120						V$OSSTAT
121					WHERE
122						STAT_NAME = 'NUM_CPU_CORES'
123				) num_cores
124			) v
125	`, params["LockMaxTime"])
126	if err != nil {
127		return nil, zbxerr.ErrorCannotFetchData.Wrap(err)
128	}
129
130	err = row.Scan(&sessions)
131	if err != nil {
132		return nil, zbxerr.ErrorCannotFetchData.Wrap(err)
133	}
134
135	return sessions, nil
136}
137