• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

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

by admin

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

Filed Under: oracle, Oracle 11g, oracle 12c, Oracle 19c

Some more articles you might also be interested in …

  1. PL/SQL: Factorial Program
  2. ORA-01031 When Compiling A Synonym
  3. How to Configure Device File owner/group with udev rules
  4. Starting, Stopping, and Checking the Status of the EM Cloud Control OMS
  5. How to rename database and then move datafile online in Oracle Database 12.2
  6. Extend rule sets by using factors in Oracle Database Vault
  7. Oracle 19c New Feature – Real-Time Statistics
  8. How to Verify if Oracle Active Data Guard is Enabled
  9. Patching and Upgrading Databases in Oracle Data Guard Configuration
  10. How to Create or Remove Restore Point on Oracle Standby database

You May Also Like

Primary Sidebar

Recent Posts

  • JavaFX ComboBox: Set a value to the combo box
  • Nginx load balancing
  • nginx 504 gateway time-out
  • Images preview with ngx_http_image_filter_module

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright