19c feature – Real-time SQL Monitoring for Developers
The real-time SQL monitoring is a new feature that enables DBAs to monitor the performance of SQL statements while they are executing
Because a primary job duty of database developers is to write and tune SQL statements,
Starting in Oracle Database 19c,Oracle allow database user to view their own Real Time SQL Monitoring reports without requiring DBA privileges or SELECT_CATALOG_ROLE.
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.If 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
–user1 for SQL Statement using Tables
drop user user1 cascade;
create user user1 identified by <PASSWORD>;
alter user user1 quota unlimited on users;
grant connect,resource to user1;
–2.prepare test data
conn user1/<PASSWORD>
drop table table1;
drop table table2;
create table table1(c1 number, c2 char(100));
create table table2(c1 number, c2 char(100));
begin
for i in 1 .. 200 loop
for j in 1 .. 100 loop
insert into table1 values(i,’A’);
commit;
end loop;
end loop;
end;
/
begin
for i in 1 .. 200 loop
for j in 1 .. 100 loop
insert into table2 values(i,’A’);
commit;
end loop;
end loop;
end;
/
–2.Executing SQL and check SQL Monitor Active Report
conn user1/user1
select /*+ use_nl(a b) */ count(*)
from table1 a, table2 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
WikiDBA 