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.
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:
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:
- Database users can generate and view SQL monitor report of the SQL statements issued by themself, without granting any additional privileges.
- 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.
- 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