Lin Hong's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

Oracle 23c New features - Real-Time SQL Monitoring Enhancements Tips

2022-12-20

Oracle 23c New features - Real-Time SQL Monitoring Enhancements Tips

ocw-23c-announcement

Real-time SQL Monitoring works independently and concurrently across multiple PDB containers in an efficient manner. SQL statements, PL/SQL procedures and functions, and DBOPs (Database Operations) are monitored at PDB and CDB levels. You can efficiently query SQL Monitor reports across ad-hoc time ranges, DBIDs (internal database identifiers), and CON_DBIDs (CDB identifiers). This data is also accessible through SQL History Reporting.

Additionally, SQL Monitoring data can be exported along with the Automatic Workload Repository (AWR) and imported into another database or container for longer term storage and analysis.

Real-time SQL Monitoring is now supported per-PDB and CDB levels efficiently by default. As a PDBA persona, you can get a more accurate view of the monitored SQL for your application.

SQL Monitoring data can be transported through the AWR framework to a different container or database for longer term storage and offline analysis.

MODIFY_AWREXP_SETTINGS

Enable or disable it on each PDB by locally calling the DBMS_WORKLOAD_REPOSITORY subprogram MODIFY_AWREXP_SETTINGS.

21.6.1 Enabling or Disabling Real-Time SQL Monitoring on PDBs

Use the procedure MODIFY_AWREXP_SETTINGS on each PDB to enable or disable this feature on each PDB:

PROCEDURE modify_awrexp_settings(dbid IN NUMBER DEFAULT NULL, sqlmon_option IN VARCHAR2 DEFAULT 'NO');

Specify whether to export SQL Monitor data. If you do not want to export SQL Monitor data, enter NO or press Enter to continue. Enter YES to export SQL Monitor data

Test in 23c.

SYS@cdb1> alter session set container=pdb1;

Session altered.

SYS@cdb1> SELECT DBID,SNAP_INTERVAL,RETENTION,TOPNSQL,CON_ID,TABLESPACE_NAME FROM DBA_HIST_WR_CONTROL;

       DBID SNAP_INTERVAL     RETENTION 	TOPNSQL  CON_ID TABLESPACE_NAME
----------- ----------------- ----------------- ------- ------- ---------------
 1093239018 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT       0 SYSAUX
 2616168235 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT       3 PDB1_AWR

SYS@cdb1> exec dbms_workload_repository.MODIFY_AWREXP_SETTINGS(2616168235,'YES');
BEGIN dbms_workload_repository.MODIFY_AWREXP_SETTINGS(2616168235,'YES'); END;

*
ERROR at line 1:
ORA-13574: Input parameters to AWR operation are invalid: YES
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 2922
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 2956
ORA-06512: at line 1

SYS@cdb1> exec dbms_workload_repository.MODIFY_AWREXP_SETTINGS(INCLUDE_SQLMON_OPTION=>'YES');
BEGIN dbms_workload_repository.MODIFY_AWREXP_SETTINGS(INCLUDE_SQLMON_OPTION=>'YES'); END;
*
ERROR at line 1:
ORA-13574: Input parameters to AWR operation are invalid: YES
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 2922
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 2956
ORA-06512: at line 1

SYS@cdb1> 

SYS@cdb1> exec dbms_workload_repository.MODIFY_AWREXP_SETTINGS(1093239018,'YES');
BEGIN dbms_workload_repository.MODIFY_AWREXP_SETTINGS(1093239018,'YES'); END;

*
ERROR at line 1:
ORA-13516: AWR Operation failed: DBID must be local dbid
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 2922
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 2956
ORA-06512: at line 1

SYS@cdb1> 
SYS@cdb1> exec dbms_workload_repository.MODIFY_AWREXP_SETTINGS(2616168235,'NO');

PL/SQL procedure successfully completed.

SYS@cdb1> 
SYS@cdb1> exec dbms_workload_repository.MODIFY_AWREXP_SETTINGS(2616168235);

PL/SQL procedure successfully completed.

SYS@cdb1> 

Can NOT find the package to check the status of awrexp’s setting.????

Reference

21.6 Real-Time SQL Monitoring Across Multiple PDBs

Refer:

23c-help

Have a good work&life! 2022/12 via LinHong


Similar Posts

Comments