Scope
Back to part XXII, it could be interesting to have a consolidated activity view of non-CDB databases or of PDB pluggable databases belonging to different CDBs.
We will define the concept of “database group”, in which one database will be the primary database (like the CDB$ROOT container database) and the other databases will be defined as secondary databases.
We will take two databases as an example. In our case we will use a CDB database where the CDB$ROOT container will be the primary database and the PDB1 container will be the secondary database. We will also modify the structure of the table lss$_sampling_session in order to handle RAC instances.
Let’s begin with the PDB1 container.
PDB1 – Tablespace modification
We will use the ‘LSS_REPOSITORY’ tablespace that was already created in the post “Average Active Sessions and CDB – Light Sampling Sessions”, but we will increase its size to 150 Mo:
@> connect / as sysdba
Connected.
SYS@orcl> ALTER SESSION SET CONTAINER=PDB1;
Session altered.
SYS@orcl> show con_name;
CON_NAME
------------------------------
PDB1
SYS@orcl> ALTER DATABASE
2 DATAFILE 'C:\APP\ORACLE\ORADATA\ORCL\PDB1\LSS_REPOSITORY01.DBF' RESIZE 150 M;
Database altered.
PDB1 – Local user creation
Connected to the PDB1 container, we will create the LSS local user:
SYS@orcl> CREATE USER LSS IDENTIFIED BY lss
2 CONTAINER=CURRENT
3 DEFAULT TABLESPACE LSS_REPOSITORY
4 QUOTA UNLIMITED ON LSS_REPOSITORY;
User created.
PDB1 – Base privileges
We will grant the following privileges to the LSS user:
SYS@orcl> GRANT CREATE SESSION TO LSS;
Grant succeeded.
SYS@orcl> GRANT SELECT_CATALOG_ROLE TO LSS;
Grant succeeded.
SYS@orcl> GRANT CREATE PROCEDURE TO LSS;
Grant succeeded.
SYS@orcl> GRANT CREATE VIEW TO LSS;
Grant succeeded.
SYS@orcl> GRANT CREATE TABLE TO LSS;
Grant succeeded.
PDB1 – Table creation
Let’s now create the table lss$_sampling_session in the LSS schema:
@> connect lss@pdb1
Enter password:
Connected.
LSS@pdb1> show con_name;
CON_NAME
------------------------------
PDB1
LSS@pdb1> CREATE TABLE lss$_sampling_session
2 AS
3 SELECT CAST (1 AS NUMBER) sample_id,
4 CAST (NULL AS DATE) sample_time,
5 sid,
6 serial#,
7 user#,
8 type,
9 command,
10 program,
11 module,
12 action,
13 service_name,
14 client_identifier,
15 CAST ('1' AS CHAR(1)) session_state,
16 state,
17 event#,
18 event,
19 seq#,
20 p1,
21 p2,
22 p3,
23 wait_class#,
24 wait_time_micro,
25 sql_id,
26 con_id,
27 inst_id,
28 sql_hash_value,
29 sql_child_number,
30 CAST (1 AS NUMBER) element_id
31 FROM gv$session
32 WHERE 1=2;
Table created.
We have added four new columns:
- inst_id: to handle RAC instances,
- sql_hash_value and sql_child_number: to handle more precisely sql statements,
- element_id: to handle the database group. This number must be unique in all the databases that belong to a database group.
PDB1 – Other privileges
Let’s grant the SELECT privilege on the underlying object of gv$session in order to use it in a procedure:
@> connect / as sysdba
Connected.
SYS@orcl> ALTER SESSION SET CONTAINER=PDB1;
Session altered.
SYS@orcl> show con_name;
CON_NAME
------------------------------
PDB1
SYS@orcl> GRANT SELECT ON SYS.GV_$SESSION TO LSS;
Grant succeeded.
Let’s grant the EXECUTE privilege on the DBMS_LOCK package:
SYS@orcl> GRANT EXECUTE ON DBMS_LOCK TO LSS;
Grant succeeded.
PDB1 – Package specifications
Let’s create the package specifications as following:
CREATE OR REPLACE PACKAGE LSS.DBMS_SAMPLING_SESSION AS
-- =======================================================================
-- Package : DBMS_SAMPLING_SESSION
-- Description : Sample active sessions
-- Author : Arnaud Fargues
-- Creation : 14.06.2015
-- Modifications :
-- =======================================================================
PROCEDURE start_sampling (
p_sampling_window_min IN NUMBER DEFAULT 60,
p_sampling_interval_sec IN NUMBER DEFAULT 5);
END DBMS_SAMPLING_SESSION;
/
PDB1 – Package body
You will find below the package body:
CREATE OR REPLACE PACKAGE BODY LSS.DBMS_SAMPLING_SESSION AS
PROCEDURE start_sampling (
p_sampling_window_min IN NUMBER DEFAULT 60,
p_sampling_interval_sec IN NUMBER DEFAULT 5)
-- =======================================================================
-- Procedure : start_sampling
-- Description : Starts sampling active sessions
-- Parameters : p_sampling_window_min : sampling period time in minutes
-- p_sampling_interval_sec : sampling period interval in seconds
-- Author : Arnaud Fargues
-- Creation : 14.06.2015
-- Modifications : 10.12.2015 Handle RAC instances
-- 12.01.2016 Add columns sql_hash_value and sql_child_number
-- 22.01.2016 Add column element_id
-- =======================================================================
AS
v_nb_sample INTEGER;
v_index INTEGER;
v_cur_date DATE;
v_sample_id NUMBER;
v_sample_per_min NUMBER;
v_session_sid NUMBER;
BEGIN
v_sample_per_min := 60 / p_sampling_interval_sec;
v_nb_sample := p_sampling_window_min * v_sample_per_min;
SELECT NVL(MAX(sample_id),0)
INTO v_sample_id
FROM lss$_sampling_session;
SELECT SYS_CONTEXT ('USERENV', 'SID')
INTO v_session_sid
FROM DUAL;
FOR v_index IN 1..v_nb_sample
LOOP
v_sample_id := v_sample_id + 1;
DBMS_APPLICATION_INFO.SET_MODULE(
module_name => 'DBMS_SAMPLING_SESSION',
action_name => 'Sample:'|| TO_CHAR(v_sample_id));
v_cur_date := SYSDATE;
INSERT INTO lss$_sampling_session(
sample_id,
sample_time,
inst_id,
sid,
serial#,
session_state,
state,
user#,
type,
command,
program,
module,
action,
service_name,
client_identifier,
event#,
event,
seq#,
p1,
p2,
p3,
wait_class#,
wait_time_micro,
sql_id,
sql_hash_value,
sql_child_number,
con_id,
element_id)
SELECT
v_sample_id,
v_cur_date,
inst_id,
sid,
serial#,
decode(state,'WAITING','W','C'),
state,
user#,
type,
command,
program,
module,
action,
service_name,
client_identifier,
event#,
event,
seq#,
p1,
p2,
p3,
wait_class#,
wait_time_micro,
sql_id,
sql_hash_value,
sql_child_number,
con_id,
2
FROM gv$session
WHERE status='ACTIVE'
AND ((state = 'WAITING' AND wait_class# <> 6)
OR (state <> 'WAITING' AND ((SID = v_session_sid AND event# <> 389) OR sid <> v_session_sid)));
IF v_index MOD v_sample_per_min = 0 THEN
COMMIT;
END IF;
DBMS_LOCK.SLEEP(p_sampling_interval_sec);
END LOOP;
END start_sampling;
END DBMS_SAMPLING_SESSION;
/
Remarks:
- For the PDB1 container which is a secondary database the element_id value is 2,
- For non-CDB databases, you can set the con_id column value to 0.
Let’s continue with the CDB$ROOT container.
CDB$ROOT – Table update
We will add the four new columns to the table lss$_sampling_session in the C##LSS schema:
@> connect c##lss@orcl
Enter password:
Connected.
C##LSS@orcl> show con_name;
CON_NAME
------------------------------
CDB$ROOT
C##LSS@orcl> ALTER TABLE lss$_sampling_session
2 ADD (inst_id NUMBER,
3 sql_hash_value NUMBER,
4 sql_child_number NUMBER,
5 element_id NUMBER);
Table altered.
CDB$ROOT – Other privileges
Let’s grant the SELECT privilege on the underlying object of gv$session in order to use it in a procedure instead of v$session:
@> connect / as sysdba
Connected.
SYS@orcl> show con_name
CON_NAME
------------------------------
CDB$ROOT
SYS@orcl> GRANT SELECT ON SYS.GV_$SESSION TO C##LSS;
Grant succeeded.
Gv$session is a view that contains data linked to several containers (CDB$ROOT, PDB’s). Because the common user C##LSS should see data of all those containers we will set the CONTAINER_DATA attribute as follow:
SYS@orcl> ALTER USER C##LSS
2 SET CONTAINER_DATA = (CDB$ROOT,PDB1)
3 FOR gv_$session CONTAINER=CURRENT;
User altered.
CDB$ROOT – Package body
The package body is the same than for the PDB1 container except that the value of element_id is 1 for CDB$ROOT:
You must replace this code fragment
... con_id,
2
FROM gv$session
WHERE status='ACTIVE'...
by
... con_id,
1
FROM gv$session
WHERE status='ACTIVE'...
Shortly
We will consolidated the two database activities in a stacked area graph and compare it to the graph described in the part XXII.
Oracle database experiences 