The Geek Diary

Real-time SQL Monitoring for Developers – Oracle Database 19c New Feature

by

The real-time SQL monitoring is an 11g new feature that enables DBAs to monitor the performance of SQL statements while they are executing, Please refer the following post for details.

Understanding Real-Time SQL Monitoring in Oracle 11g
Beginners Guide to Monitoring SQL Statements with Real-Time SQL Monitoring in Oracle Database

Because a primary job duty of database developers is to write and tune SQL statements, starting in Oracle Database 19c, Oracle allows database user to view their own Real-Time SQL Monitoring reports without requiring DBA privileges or SELECT_CATALOG_ROLE.

From the official documentation:

Starting in Oracle Database 19c, Oracle Database includes undocumented V$ views that enable a database user without the SELECT_CATALOG_ROLE to see the plans and statistics for SQL and PL/SQL statements executed within the session. A user without SELECT_CATALOG_ROLE cannot see SQL execution statistics and details for other users.

New set of V$ views:

V$ALL_SQL_MONITOR
V$ALL_SQL_PLAN_MONITOR
V$ALL_ACTIVE_SESSION_HISTORY
V$ALL_SQL_PLAN

Mainly scenarios is following:

  1. Database users can generate and view SQL monitor report of the SQL statements issued by themself, without granting any additional privileges.
  2. If users have not been granted the SELECT_CATALOG_ROLE ,they can not generate and view SQL monitor report of SQL statements executed by other users.
  3. f users have been granted the SELECT_CATALOG_ROLE ,they can see SQL monitor report of SQL executed by other users.

And you can generate and view SQL monitoring report from the SQL*PLUS command line by DBMS_SQLTUNE.REPORT_SQL_MONITOR_LIST and DBMS_SQLTUNE.REPORT_SQL_MONITOR_LIST package or from Enterprise Manager (EM) just as 11g.

TEST CASE

--1.create users
conn / as sysdba

--1-1.create Low-privileged users without DBA privilege
--dep1 for SQL Statement using Tables

drop user dep1 cascade;
create user dep1 identified by dep1;
alter user dep1 quota unlimited on users;
grant connect,resource to dep1;

--2.prepare test data
conn dep1/dep1

drop table testa_dep1;
drop table testb_dep1;
create table testa_dep1(c1 number, c2 char(100));
create table testb_dep1(c1 number, c2 char(100));

begin
  for i in 1 .. 200 loop
    for j in 1 .. 100 loop
      insert into testa_dep1 values(i,'A');
      commit;
    end loop;
  end loop;
end;
/

begin
  for i in 1 .. 200 loop
    for j in 1 .. 100 loop
      insert into testb_dep1 values(i,'A');
      commit;
    end loop;
  end loop;
end;
/

--2.Executing SQL and check SQL Monitor Active Report
conn dep1/dep1

select /*+ use_nl(a b) */ count(*)
 from testa_dep1 a, testb_dep1 b
 where a.c1=b.c1;

--3.Generate and view SQL Monitor List and Active Report
--should be able to view SQL monitor report of the SQL statements issued by user-self, without granting any additional privileges.
--should not be able to view SQL monitor report of SQL statements issued by other users.

--REPORT_SQL_MONITOR_LIST
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
spool monitor_list_sql_dep1_active.html
SELECT DBMS_SQLTUNE.report_sql_monitor_list(type =>'ACTIVE',report_level => 'ALL') AS report FROM dual;
spool off

--REPORT_SQL_MONITOR
set trimspool on
set trim on
set pages 0
set linesize 1000
set long 1000000
set longchunksize 1000000
spool monitor_sql_dep1.html
select dbms_sqltune.report_sql_monitor(type=>'active') from dual;
spool off

You May Also Like