Oracle Database Performance Hub report using DBMS_PERF

  When we  have performance issues normally we have to login to  oem  to get  performance details . 

  We can get quick snap of  performance using DBMS_PERF.REPORT_PERFHUB    as mentioned below .   Its old tool  introduced in 12c but  not frequently used . 

Command : 

SET  LONG   10000000 ;

 SET LONGC 10000000 ;

 SET LINESIZE 32767 ;

 SET PAGESIZE 0 ;

 SET TRIMSPOOL ON ;

SPOOL perfhub.html

SELECT DBMS_PERF.REPORT_PERFHUB FROM DUAL;

SPOOL OFF;

.Other option used :

SET HEADING OFF LINESIZE 32767 PAGESIZE 0 TRIMSPOOL ON TRIMOUT ON LONG 9999999 VERIFY OFF LONGCHUNKSIZE 100000 FEEDBACK OFF

SET TERMOUT OFF

spool perfhub.html

SELECT DBMS_PERF.REPORT_PERFHUB (

    is_realtime         => 0           -- (  0 = dba_hist, 1 = v$ash  )

  , outer_start_time    => sysdate-1

  , outer_end_time      => sysdate

  , selected_start_time => TIMESTAMP'2023-05-15 20:15:00'

  , selected_end_time   => TIMESTAMP'2023-05-15 20:45:00'

  , type=>'ACTIVE'

)

FROM dual ;

spool off

SET TERMOUT ON HEADING ON PAGESIZE 5000 LINESIZE 999 FEEDBACK ON 

 

Historical SQL monitoring report can be extracted for particular SQL statement using SQL Details report.

Example: To generate SQL details report to get the SQL monitoring information for particular SQL statement in historical mode.

SQL> set pages 0 linesize 32767 trimspool on trim on long 1000000 longchunksize 10000000

SQL> spool sql_details_history.html

SQL> select dbms_perf.report_sql(sql_id=>'9vkyyg1xj6fgc',is_realtime=>0,type=>'active',selected_start_time=>to_date('10-SEP-18 04:00:00','dd-MON-YY hh24:mi:ss'),selected_end_time=>to_date('10-SEP-18 05:00:00','dd-MON-YY hh24:mi:ss')) from dual;

SQL> spool off

Note in 19c or higher it is better to add "outer_start_time" and  "outer_end_time" to get the desired results:

SQL> set pages 0 linesize 32767 trimspool on trim on long 1000000 longchunksize 10000000

SQL> spool sql_details_history.html

SQL> select dbms_perf.report_perfhub( is_realtime=>0, outer_start_time=>to_date('06-SEP-2022 12:00:00','dd-MON-YYYY hh24:mi:ss'), outer_end_time=>to_date('06-SEP-2022 13:00:00','dd-MON-YYYY hh24:mi:ss'), selected_start_time=>to_date('06-SEP-2022 12:00:00','dd-MON-YYYY hh24:mi:ss'), selected_end_time=>to_date('06-SEP-2022 13:00:00','dd-MON-YYYY hh24:mi:ss')) from dual;

SQL> spool off

Generating Database Performance Hub Report :

@?/rdbms/admin/perfhubrpt.sql

Syntax

DBMS_PERF.REPORT_PERFHUB (
   is_realtime          IN NUMBER   DEFAULT NULL,
   outer_start_time     IN DATE     DEFAULT NULL,
   outer_end_time       IN DATE     DEFAULT NULL,
   selected_start_time  IN DATE     DEFAULT NULL,
   selected_end_time    IN DATE     DEFAULT NULL,
   inst_id              IN NUMBER   DEFAULT NULL,
   dbid                 IN NUMBER   DEFAULT NULL,
   monitor_list_detail  IN NUMBER   DEFAULT NULL,
   workload_sql_detail  IN NUMBER   DEFAULT NULL,
   addm_task_detail     IN NUMBER   DEFAULT NULL,
   report_reference     IN VARCHAR2 DEFAULT NULL,
   report_level         IN VARCHAR2 DEFAULT NULL,
   type                 IN VARCHAR2 DEFAULT 'ACTIVE',
   base_path            IN VARCHAR2 DEFAULT NULL);
 RETURN CLOB;

 REPORT_PERFHUB Function Parameters

Parameter	Description
is_realtime	If 1, then real-time. If NULL (default) or 0, then historical mode.
outer_start_time	Start time of outer period shown in the time selector. If NULL (default): If is_realtime=0 (historical), then 24 hours before outer_end_time. If is_realtime=1 (realtime mode), then 1 hour before outer_end_time.
outer_end_time	End time of outer period shown in the time selector. If NULL (default), then latest AWR snapshot. If is_realtime=0 (historical), then the latest AWR snapshot If is_realtime=1 (realtime mode), this is the current time (and any input is ignored)
selected_start_time	Start time period of selection. If NULL (default) If is_realtime=0, then 1 hour before selected_end_time If is_realtime=1, then 5 minutes before selected_end_time
selected_end_time	End time period of selection. If NULL (default) If is_realtime=0, then latest AWR snapshot If is_realtime=1, then current time
inst_id	Instance ID to for which to retrieve data If -1, then current instance If number is specified, then for that instance If NULL (default), then all instances
dbid	DBID to query. If NULL, then current DBID. If is_realtime=1, then DBID must be the local DBID.
monitor_list_detail	Top N in SQL monitor list for which to retrieve SQL monitor details. If NULL (default), then retrieves top 10 If 0, then retrieves no monitor list details
workload_sql_detail	Top N in Workload Top SQL list to retrieve monitor details, If NULL (default), then retrieves top 10 If 0, then retrieves no monitor list details
addm_task_detail	Maximum N latest ADDM tasks to retrieve If NULL (default), retrieves available data but no more than N If 0, then retrieves no ADDM task details
report_reference	Must be NULL when used from SQL*Plus.
report_level	'typical' will get all tabs in performance hub
type	Report type: 'ACTIVE' (default) 'xml' returns XML
base_path	URL path for HTML resources since flex HTML requires access to external files. This is only valid for type='ACTIVE' and is typically not used. Default value will retrieve the required files from OTN.

 

Reference : 

Monitoring Database Performance Using Performance Hub Report (Doc ID 2436566.1)

https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_PERF.html#GUID-85CD8AB9-B6E0-444F-91A5-762EB92A74E9 

How To Get Historical SQL Monitor Report For SQL Statements (Doc ID 2555350.1)

Force cleanup of temporary segments through DROP_SEGMENT event in Oracle database

We recently had scenario where  temp segments were not getting released  even though  no  active sessions were there  and  "alter tablespace temp  coalesce;"   was not helping  .   

This needs to be done only after oracle support  recommendation .and this  feature  surprisingly exists from Oracle version  8 . 

This routine does what SMON does in the background, i.e. drops temporary segments.

Before force dropping temp segment try to gracefully clean . Sometimes temp can be resized after restart of database 

 select sum(blocks*8/1024) size_mb from v$tempseg_usage where tablespace='TEMP';

Shrink TEMP Tablespace using alter tablespace command

SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 50M;.

Shrink TEMPFILE using alter tablespace command

SQL> ALTER TABLESPACE temp SHRINK TEMPFILE '/u01/app/oracle/oradata/TEST11G/temp01.dbf' KEEP 40M;

Shrink TEMP Tablespace to the smallest possible size:

SQL> ALTER TABLESPACE temp SHRINK SPACE;

DESCRIPTION : 

Finds all the temporary segments in a tablespace which are not currently locked and drops them.

PARAMETER : Level parameter used in command 

level - tablespace number+1. If the value is 2147483647 then

temp segments in ALL tablespaces are dropped, otherwise, only

segments in a tablespace whose number is equal to the LEVEL

specification are dropped.

Manually release the temporary segment, the utilization rate will not decrease, but the content of the temporary tablespace will be released

alter session set events 'immediate trace name DROP_SEGMENTS level 4'

Description: TS# of temp tablespace is 3, So TS#+1=4

View temp content details

select se.inst_id,se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value)) as Space, tablespace,segtype,sql_text from gv$sort_usage su,gv$parameter p,gv $session se,gv$sql s

where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr order by

se.username,se.sid;

If the query finds that most of the temp temporary table space is lob temporary fields, you can set the 60025 event

alter system set event="60025 trace name context forever" scope=spfile;

Restart to take effect

References : 

EVENT: DROP_SEGMENTS - Forcing cleanup of TEMPORARY segments (Doc ID 47400.1)

Note 177334.1 Overview of Temporary Segments 

Note 35513.1 Removing 'Stray' TEMPORARY Segments

Note 61997.1 SMON - Temporary Segment Cleanup and Free Space Coalescing

NOTE:160426.1 - TEMPORARY Tablespaces : Tempfiles or Datafiles ?

Note 102339.1 Temporary Segments: What Happens When a Sort Occurs 

Note 1039341.6 Temporary Segments Are Not Being De-Allocated After a Sort 

Note 68836.1 How To Efficiently Drop (or Truncate) A Table With Many Extents

Note 132913.1 How To Free Temporary Segment in Temporary Tablespace Dynamically

Resize Of Temporary Tablespace with KEEP keyword not working (Doc ID 1663229.1)

Debugging any Oracle errors and events using ERRORSTACK

 

DBA has seen ORA-00600 trace file generated every time ORA-00600 or ORA-07445 occurs which has each and every detail about this error. 

Generating trace files for ORA-00600 or ORA-07445 is the default behaviour of Oracle, but the Same doesn’t happen in case of other errors.

We are using ERRORSTACK to  capture  details  for all other  oracle errors and events .  By enabling error stack we are force capturing  error  details in alert log and with its trace file details .

ERRORSTACK Parameters:

0 – Error stack only

1 – Error stack and function call stack (if implemented)

2 – As 1 plus the ProcessState

3 – As 2 plus the context area (all cursors and current cursor highlighted)

Enable

ALTER SYSTEM SET EVENT ='<ORAError> trace name errorstack level <LevelNumber>' SCOPE=spfile;

e.g To set errorstack for ORA – 1652 ,Syntax will be

 

alter system set events ‘1652 trace name errorstack level 3’;  -- system level 

alter session set events ‘1652 trace name errorstack level 3’; -- sesion level 

Disable

ALTER SYSTEM SET EVENT='<ORAError> trace name errorstack off';

ALTER SYSTEM SET EVENT='1652 trace name errorstack off';

Display Events

-- SQLPlus> show parameter event

-- oradebug eventdump session;   OR   oradebug eventdump system  

-- DBA_ENABLED_TRACES or WRI$_TRACING_ENABLED can be used to see status also:

SELECT TRACE_TYPE, PRIMARY_ID, QUALIFIER_ID1, WAITS, BINDS FROM DBA_ENABLED_TRACES;

You can also use oradebug to take errorstack for particular process (say ospid 1234) as below

sqlplus “/ as sysdba”

oradebug setospid 1234

oradebug unlimit

oradebug dump errorstack 3

References :

How to Collect Errorstacks for use in Diagnosing Performance Issues. (Doc ID 1364257.1)

Oracle picks more parallel slaves then what is defined in PARALLEL HINT

  

We  had strange scenario where  application team  defined  parallel  10  in   parallel  hint   eg    " SELECT /*+ parallel(10)*/ "   ,   however   we  were seeing  huge parallel slaves sessions  around 100+  . 

This  kind  of  behavior is normal  when parallel_degree_policy is set to true  ,   but we had  parallel_degree_policy=manual  and   still we  were seeing huge parallel slaves  far above what is defined in hint  

parallel_degree_policy=manual

parallel_degree_limit = CPU

On checking it as found that  When we dont add table name in parallel hint , it will enable parallel auto  dop   . We can see  this in  sql plan . 

  Note

-----

- cpu costing is off (consider enabling it)

- automatic DOP: Computed Degree of Parallelism is 124 because of degree limit

If we  dont   want  to enable auto dop when parallel_degree_policy -is set to  manual  , we need to  add parallel hint  on specific tables   eg .  /*+ PARALLEL(employee_table, 35) */

Parallel Statement Queuing  .

Parallel Statement Queuing has been introduced from 12c and is  key feature .  Parallel Statement Queuing is enabled by default  when we enable Parallel  auto dop  by setting parallel_degree_policy to auto .

In case when parallel_degree_policy is set to manual  , we can still enable Parallel Statement Queuing  by 2 ways .  i,e  

1) using parameter  "_parallel_statement_queuing"=true  

     

select a.ksppinm "Parameter",b.ksppstvl "Session Value",c.ksppstvl "Instance Value" from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c where a.indx = b.indx and a.indx = c.indx and a.ksppinm in ('_fix_control','_parallel_statement_queuing') order by 1;

2) using  hint  - select /*+PARALLEL(4) STATEMENT_QUEUING*/ .

STATEMENT_QUEUING Hint can be deployed through patch 

begin

sys.dbms_sqldiag_internal.i_create_patch(sql_id=>'<SQL_ID>', hint_text=>'OPT_PARAM(''_parallel_statement_queuing'' ''true'')', name=> 'SQL_Patch_parallel_statement_queuing_sql_id'); >>>>>>>>>>>>>>>REPLACE <SQL_ID> with your sql_id

end;

/

References :

Automatic Degree of Parallelism (AutoDOP) is enabled when "PARALLEL_DEGREE_POLICY=MANUAL and PARALLEL HINT" is Used at SQL Statement (Doc ID 1542556.1)

How to Achieve Parallel Statement Queuing for an SQL When PARALLEL_DEGREE_POLICY=MANUAL (Doc ID 1902069.1)

How To Enable Parallel Query For A Function? ( Doc ID 1093773.1 )

https://blogs.oracle.com/datawarehousing/post/px-server-sets-parallelizers-dfo-trees-parallel-groups-lets-talk-about-terminology

SRDC - Data Collection for Query executing in Parallel returning error (Doc ID 2076943.1)

Other Known Issues related to parallel hint in 19c  :

9c: Duplicate PQ workers created at instance level (Doc ID 2719466.1)

BUG:32342582 - WRONG RESULTS WITH PARALLEL AFTER UPGRADE TO 19.9

Bug 19849585 : PARALLEL HINT IGNORED WHEN USING GROUP BY

Issue with parallel hint on 19c CDB database where the hint is not honored and going for serial scans. Apparently seems to be Oracle bug Bug 30618478 

Number of Preallocated Parallel Workers Are More Than Parallel_min_servers (Doc ID 2654231.1)

 Bug 31195441 : 18C: LONG PARSING TIME WHEN QUERY EXECUTING IN PARALLEL

Views :

col username for a12

col "QC SID" for A6

col "SID" for A6

col "QC/Slave" for A8

col "Req. DOP" for 9999

col "Actual DOP" for 9999

col "Slaveset" for A8

col "Slave INST" for A9

col "QC INST" for A6

set pages 300 lines 300

col wait_event format a30

select

decode(px.qcinst_id,NULL,username,

' - '||lower(substr(pp.SERVER_NAME,

length(pp.SERVER_NAME)-4,4) ) )"Username",

decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,

to_char( px.server_set) "SlaveSet",

to_char(s.sid) "SID",

to_char(px.inst_id) "Slave INST",

decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE,

case sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event ,

decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",

to_char(px.qcinst_id) "QC INST",

px.req_degree "Req. DOP",

px.degree "Actual DOP"

from gv$px_session px,

gv$session s ,

gv$px_process pp,

gv$session_wait sw

where px.sid=s.sid (+)

and px.serial#=s.serial#(+)

and px.inst_id = s.inst_id(+)

and px.sid = pp.sid (+)

and px.serial#=pp.serial#(+)

and sw.sid = s.sid

and sw.inst_id = s.inst_id

order by

decode(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID),

px.QCSID,

decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),

px.SERVER_SET,

px.INST_ID ;

set pages 300 lines 300

col wait_event format a30

select

sw.SID as RCVSID,

decode(pp.server_name,

NULL, 'A QC',

pp.server_name) as RCVR,

sw.inst_id as RCVRINST,

case sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event ,

decode(bitand(p1, 65535),

65535, 'QC',

'P'||to_char(bitand(p1, 65535),'fm000')) as SNDR,

bitand(p1, 16711680) - 65535 as SNDRINST,

decode(bitand(p1, 65535),

65535, ps.qcsid,

(select

sid

from

gv$px_process

where

server_name = 'P'||to_char(bitand(sw.p1, 65535),'fm000') and

inst_id = bitand(sw.p1, 16711680) - 65535)

) as SNDRSID,

decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE

from

gv$session_wait sw,

gv$px_process pp,

gv$px_session ps

where

sw.sid = pp.sid (+) and

sw.inst_id = pp.inst_id (+) and

sw.sid = ps.sid (+) and

sw.inst_id = ps.inst_id (+) and

p1text = 'sleeptime/senderid' and

bitand(p1, 268435456) = 268435456

order by

decode(ps.QCINST_ID, NULL, ps.INST_ID, ps.QCINST_ID),

ps.QCSID,

decode(ps.SERVER_GROUP, NULL, 0, ps.SERVER_GROUP),

ps.SERVER_SET,

ps.INST_ID ;

set pages 300 lines 300

col "Username" for a12

col "QC/Slave" for A8

col "Slaveset" for A8

col "Slave INST" for A9

col "QC SID" for A6

col "QC INST" for A6

col "operation_name" for A30

col "target" for A30

select

decode(px.qcinst_id,NULL,username,

' - '||lower(substr(pp.SERVER_NAME,

length(pp.SERVER_NAME)-4,4) ) )"Username",

decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,

to_char( px.server_set) "SlaveSet",

to_char(px.inst_id) "Slave INST",

substr(opname,1,30) operation_name,

substr(target,1,30) target,

sofar,

totalwork,

units,

start_time,

timestamp,

decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",

to_char(px.qcinst_id) "QC INST"

from gv$px_session px,

gv$px_process pp,

gv$session_longops s

where px.sid=s.sid

and px.serial#=s.serial#

and px.inst_id = s.inst_id

and px.sid = pp.sid (+)

and px.serial#=pp.serial#(+)

order by

decode(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID),

px.QCSID,

decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),

px.SERVER_SET,

px.INST_ID ;

 

select inst_id

, status

, count(1) px_servers#

from gv$px_process

group by inst_id, status

order by inst_id, status ;

SELECT DECODE(px.qcinst_id, NULL, username, ' – ' || lower(SUBSTR(pp.SERVER_NAME, LENGTH(pp.SERVER_NAME)-4,4) ) ) "Username",

 DECODE(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave",

 TO_CHAR( px.server_set) "SlaveSet",

 TO_CHAR(s.sid) "SID",

 TO_CHAR(px.inst_id) "Slave INST",

 DECODE(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) AS STATE,

  CASE sw.state

    WHEN 'WAITING'

    THEN SUBSTR(sw.event,1,30)

    ELSE NULL

  END AS wait_event,

  DECODE(px.qcinst_id, NULL ,TO_CHAR(s.sid) ,px.qcsid) "QC SID",

  TO_CHAR(px.qcinst_id) "QC INST",

  px.req_degree "Req. DOP",

  px.degree "Actual DOP"

FROM gv$px_session px,

  gv$session s ,

  gv$px_process pp,

  gv$session_wait sw

WHERE px.sid   =s.sid (+)

AND px.serial# =s.serial#(+)

AND px.inst_id = s.inst_id(+)

AND px.sid     = pp.sid (+)

AND px.serial# =pp.serial#(+)

AND sw.sid     = s.sid

AND sw.inst_id = s.inst_id

ORDER BY DECODE(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID),

  px.QCSID,

  DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),

  px.SERVER_SET,

  px.INST_ID,

  pp.SERVER_NAME  ;

column name format a50

column value format 999,999

SELECT NAME, VALUE

FROM GV$SYSSTAT

WHERE UPPER (NAME) LIKE '%PARALLEL OPERATIONS%'

  OR UPPER (NAME) LIKE '%PARALLELIZED%'

  OR UPPER (NAME) LIKE '%PX%'

;

Select

decode(px.qcinst_id,NULL,username,

' - '||lower(substr(pp.SERVER_NAME,

length(pp.SERVER_NAME)-4,4) ) )"Username",

decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,

to_char( px.server_set) "SlaveSet",

to_char(px.inst_id) "Slave INST",

substr(opname,1,30) operation_name,

substr(target,1,30) target,

sofar,

totalwork,

units,

start_time,

timestamp,

decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",

to_char(px.qcinst_id) "QC INST"

from gv$px_session px,

gv$px_process pp,

gv$session_longops s

where px.sid=s.sid

and px.serial#=s.serial#

and px.inst_id = s.inst_id

and px.sid = pp.sid (+)

and px.serial#=pp.serial#(+)

order by decode(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID),px.QCSID,

decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), px.SERVER_SET,px.INST_ID;

select

   ps.qcsid,

   ps.sid,

   p.spid,

   ps.inst_id,

   ps.degree,

   ps.req_degree

from

   gv$px_session ps

   join

   gv$session s

      on ps.sid=s.sid

         and

         ps.inst_id=s.inst_id

   join

   gv$process p

      on p.addr=s.paddr

         and

         p.inst_id=s.inst_id 

order by

   qcsid,

   server_group desc,

   inst_id,

   sid;

Oracle High Undo UNEXPIRED utilization due to Autotune retention causing ORA-01555

This is  very old issue where undo extends puck  up high retention ( more then defined undo_retention) and  undo block remains in unexpired state for long time .  This is because of   _undo_autotune which is  by default set to true . 

 _undo_autotune   will try to override the undo_retention parameter. When _undo_autotune is set to True (default value), based on the size of the undo tablespace Oracle will try to keep the undo segments to higher time than defined in undo_retention parameter.

SQL> select TABLESPACE_NAME,STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY tablespace_name,STATUS order by tablespace_name;

TABLESPACE_NAME STATUS SUM(BYTES) COUNT(*)

------------------------------ --------- ----------------- ----------

UNDOTBS1 EXPIRED 65536 1
UNDOTBS1 UNEXPIRED 10285809664 3271
UNDOTBS2 EXPIRED 142802944 6
UNDOTBS2 UNEXPIRED 4242735104 642

Suggested Solutions :

One of below solution   can  be applied . 

1)    Setting  “_undo_autotune” = false;

2) Setting   _smu_debug_mode=33554432

With this setting, V$UNDOSTAT.TUNED_UNDORETENTION is not calculated based on a percentage of the fixed size undo tablespace. Instead it is set to the maximum of (MAXQUERYLEN secs + 300) and UNDO_RETENTION.

Set   at CDB level and monitor the database

3)  Setting “_HIGHTHRESHOLD_UNDORETENTION”=900

Setting    this parameter will limit high value of undo retention.

alter system set “_HIGHTHRESHOLD_UNDORETENTION”=900 SCOPE=spfile;

Views : 

SELECT property_name, property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED';

select TABLESPACE_NAME,retention from dba_tablespaces where TABLESPACE_NAME like '%UNDO%';

select TABLESPACE_NAME,STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY tablespace_name,STATUS order by tablespace_name;

select max(maxquerylen),max(tuned_undoretention) from DBA_HIST_UNDOSTAT;

SELECT

a.ksppinm "Parameter",

decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",

c.ksppstvl "Instance",

decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",

decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",

decode(p.isdefault,'FALSE','F','TRUE','T') "D",

a.ksppdesc "Description"

FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p

WHERE a.indx = b.indx AND a.indx = c.indx

AND p.name(+) = a.ksppinm

AND lower(a.ksppinm) in ('_smu_debug_mode')

ORDER BY a.ksppinm;

  select segment_name, nvl(sum(act),0) "ACT BYTES",

  nvl(sum(unexp),0) "UNEXP BYTES",

  nvl(sum(exp),0) "EXP BYTES"

  from (select segment_name, nvl(sum(bytes),0) act,00 unexp, 00 exp

  from dba_undo_extents where status='ACTIVE' group by segment_name

  union

  select segment_name, 00 act, nvl(sum(bytes),0) unexp, 00 exp

  from dba_undo_extents where status='UNEXPIRED' group by segment_name

  union

  select segment_name, 00 act, 00 unexp, nvl(sum(bytes),0) exp

  from dba_undo_extents where status='EXPIRED' group by segment_name)

  group by segment_name

  order by 1

 /

select segment_name, nvl(sum(act),0) "ACT BYTES",

nvl(sum(unexp),0) "UNEXP BYTES",

nvl(sum(exp),0) "EXP BYTES"

from (select segment_name, nvl(sum(bytes),0) act,00 unexp, 00 exp

from dba_undo_extents where status='ACTIVE' group by segment_name

union

select segment_name, 00 act, nvl(sum(bytes),0) unexp, 00 exp

from dba_undo_extents where status='UNEXPIRED' group by segment_name

union

select segment_name, 00 act, 00 unexp, nvl(sum(bytes),0) exp

from dba_undo_extents where status='EXPIRED' group by segment_name)

group by segment_name

order by 1

/

select tablespace_name,round(sum(case when status = 'UNEXPIRED' then bytes else 0 end) / 1048675,2) unexp_MB ,

round(sum(case when status = 'EXPIRED' then bytes else 0 end) / 1048576,2) exp_MB ,

round(sum(case when status = 'ACTIVE' then bytes else 0 end) / 1048576,2) act_MB

from dba_undo_extents group by tablespace_name

/

SELECT s.sid, s.serial#, s.username, s.program, t.used_ublk, t.used_urec FROM v$session s, v$transaction t WHERE s.taddr = t.addr ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

References :

ORA-01555 for long running queries if "_undo_autotune"=true (Doc ID 2784427.1)

High Tuned_undoretention though Undo Datafiles are Autoextend on (Doc ID 2582183.1)

Customer Recommended High Undo Utilization On 19c PDB Database due to Automatic TUNED_UNDORETENTION (Doc ID 2710337.1)

Related Issues :

Bug 31113682 - ORA-1555 FOR LONG RUNNING QUERIES IF "_UNDO_AUTOTUNE"=TRUE

Active Undo Segment Keep Growing And Not Releasing/Reusing Space (Doc ID 2343397.1)

Dump Undo Block :

for our  analysis we  can  try to  dump  one of undo block to get more information . 

select SEGMENT_NAME, sum(bytes)/1024/1024/1024 from DBA_UNDO_EXTENTS where status='UNEXPIRED' and segment_name in (

SELECT a.name from

v$rollname a,

v$rollstat b,

dba_rollback_segs c,

v$transaction d,

v$session e

WHERE

a.usn=b.usn AND

a.name=c.segment_name AND

a.usn=d.xidusn AND

d.addr=e.taddr ) group by SEGMENT_NAME order by 2;

alter session set tracefile_identifier='XID';

alter session set max_dump_file_size = unlimited;

alter system dump undo header '_SYSSMU31_4199861047$';

 

Analyzing ORA-4031 In Oracle Database 19c

 

Possible Reason :

1) Too many Child Cursors 

2)  In memory  enabled 

3)  Undersized Sga 

4)  Pinned objects  in shared pool 

5)  Memory Fragmentation 

6) Too many hard parsing ,  

 

Possible Solution : 

1) Use  bind variables 

2)   Set enough  Sga 

References : 

1) https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/improving-rwp-cursor-sharing.html#GUID-971F4652-3950-4662-82DE-713DDEED317C 

2) Troubleshooting: High Version Count Issues (Doc ID 296377.1)

#####################################

Logs to Collect 

#####################################

1) Tfa 

2) Using customized  sql 

2)  Using  srdc_db_ora4031.sql  retrieved from 2232371.1

Use Tfa 

$TFA_HOME/bin/tfactl diagcollect -srdc ora4031

Use your  customized  sql 

spool /tmp/memory_status.txt <<<<<< Call out the database name

set linesize 200

col VALUE for a50

set pagesize 100

set pagesize 100

col Parameter for a50

col "Session Value" for a50

col "Instance Value" for a50

column component format a25

column Initial format 99,999,999,999

column Final format 99,999,999,999

column Started format A25

select name,value from v$system_parameter where name in ( 'memory_max_target', 'memory_target', 'sga_max_size', 'sga_target', 'shared_pool_size', 'db_cache_size', 'large_pool_size', 'java_pool_size', 'pga_aggregate_target', 'workarea_size_policy', 'streams_pool_size' ) ;

select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"

from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c

where a.indx = b.indx and a.indx = c.indx and a.ksppinm in

('__shared_pool_size','__db_cache_size','__large_pool_size','__java_pool_size','__streams_pool_size','__pga_aggregate_target','__sga_target','memory_target');

select * from v$sgastat where pool like '%shared%' order by bytes;

select NAMESPACE,GETHITRATIO,PINHITRATIO,RELOADS,INVALIDATIONS from v$librarycache;

select HS.BEGIN_INTERVAL_TIME,ss.*

from DBA_HIST_SGASTAT ss ,dba_hist_snapshot hs

where pool=’shared pool’ and name=’free memory’

and SS.SNAP_ID=HS.SNAP_ID

and SS.INSTANCE_NUMBER=HS.INSTANCE_NUMBER

and ss.instance_number=1

–and HS.BEGIN_INTERVAL_TIME between to_date(’17-09-2019 13:00:00′,’dd-mm-yyyy hh24:mi:ss’) and to_date(’17-09-2019 15:30:00′,’dd-mm-yyyy hh24:mi:ss’)

order by ss.snap_id desc;

SELECT COMPONENT ,OPER_TYPE,INITIAL_SIZE "Initial",FINAL_SIZE "Final",to_char(start_time,'dd-mon hh24:mi:ss') Started FROM V$SGA_RESIZE_OPS;

SELECT COMPONENT ,OPER_TYPE,INITIAL_SIZE "Initial",FINAL_SIZE "Final",to_char(start_time,'dd-mon hh24:mi:ss') Started FROM V$MEMORY_RESIZE_OPS;

select * from v$pga_target_advice;

spool off;

   

Handling Oracle Database Result Cache Corruption

Result Cache,   can be used to cache query and function results in memory. The cached information is stored in a dedicated area inside the shared pool 

where it can be shared by other PL/SQL programs that are performing similar calculations. 

 The result cache takes its memory from the shared pool. Therefore, if you expect to increase the maximum size of the result cache, take this into consideration when sizing the shared pool.

-> If you are managing the size of the shared pool using the SGA_TARGET initialization parameter, Oracle Database allocates 0.50% of the value of the SGA_TARGET parameter to the result cache.

-> If you are managing the size of the shared pool using the SHARED_POOL_SIZE initialization parameter, then Oracle Database allocates 1% of the shared pool size to the result cache.

##############  Result Cache Parameters ##############

1) Result_cache_mode 

The Result Cache is set up using the result_cache_mode initialization parameter with one of these three values:

-> MANUAL is the default and recommended value.

-> Query results can only be stored in the result cache by using a query hint or table annotation. 

-> FORCE : All results are stored in the result cache.

2)  RESULT_CACHE_MAX_SIZE 

-> Specifies the memory allocated to the server result cache. To disable the server result cache, set this parameter to 0.

-> the result cache is specific to each database instance and 

can be sized differently on each instance.

3)  RESULT_CACHE_MAX_RESULT 

-> Specifies the maximum amount of server result cache memory  (in percent) that can be used for a single result. 

Valid values are between 1 and 100. The default value is 5%.

You can set this parameter at the system or session level.

4) RESULT_CACHE_REMOTE_EXPIRATION 

-> The default value is 0, which specifies that results using remote objects will not be cached

If a non-zero value is set for this parameter, DML on the remote database does not invalidate the server result cache. 

Specifies the expiration time (in minutes) for a result in  the server result cache that depends on remote database objects.

##############  how to check result cache corruption ##############  

At  times we  come across   result cache Contention  or Corruption  issues  which  can be easily fixed by disabling and re-enabling result cache .    "ORA-600 [qesrcro_dol2ro] / result cache corruption"   can be seen in alert log in case of   corruption issues .

SQL> SELECT dbms_result_cache.status() FROM dual; 

 DBMS_RESULT_CACHE.STATUS()

 --------------------------------------------------------------------------------

 CORRUPT

--> Generate Result Cache Report 

SQL> SET SERVEROUTPUT ON 

SQL> EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT 

-> It will flush the server result cache.

EXEC DBMS_RESULT_CACHE.FLUSH;

##############   Flushing Result Cache  ##############  

--Flush retaining statistics (default for both are FALSE)

begin

dbms_result_cache.flush (

   retainmem => FALSE,

   retainsta => TRUE);

end;

/

--Flush Retaining memory (default for both are FALSE)

begin

dbms_result_cache.flush (

   retainmem => TRUE,

   retainsta => FALSE);

end;

/

--Flush memory and statistics globally

begin

dbms_result_cache.flush(

   retainmem => TRUE,

   retainsta => FALSE,

   global => TRUE);

end;

/

##############   Manually Use Result Cache Using Hint   ##############  

--Use the result_cache hint because manual mode is being used for this instance

select /*+ result_cache */ 

------------------------------------------

| Id  | Operation                        |

------------------------------------------

|  11 |  COUNT STOPKEY                   |

|  12 |   VIEW                           |

|  13 |    RESULT CACHE                  |

------------------------------------------

The /*+ NO_RESULT_CACHE */ hint instructs the database not to cache the results in either the server or client result caches.

Enable result cache of Table

ALTER TABLE sales RESULT_CACHE (MODE FORCE);

##############   Disable and re-enable  the result cache  ##############  

 alter system set RESULT_CACHE_MAX_SIZE=0;

 alter system set RESULT_CACHE_MAX_SIZE=0; 

 alter system set RESULT_CACHE_MAX_SIZE=125856K

##############   Temporarily ByPass  the result cache  ##############  

If we   dont  want to  disable Result cache , temporarily we can   Bypass  Result cache using below 

--> To Bypass

begin

dbms_result_cache.bypass(true) ; 

dbms_result_cache.flush ; 

end ; 

/ 

select dbms_result_cache.status ()  from dual  ;

--> To Normalize 

begin

dbms_result_cache.bypass(flase) ; 

dbms_result_cache.flush ; 

end ; 

/ 

##############  other views ##############  

# to check which sql is using result cache 

select   sid , result_cache from v$sql ; 

select * from GV$RESULT_CACHE_DEPENDENCY;

select * from GV$RESULT_CACHE_MEMORY;

select * from GV$RESULT_CACHE_OBJECTS;

select * from GV$RESULT_CACHE_STATISTICS;

show parameters result

Set line 400 

--Check result cache parameters

col name for a30

col value for a30

select

   name,

   value

from

   v$parameter

where

   name like '%result%';

--Query the v$result_cache_objects to check if there is any cached object

select

   count(*)

from

   v$result_cache_objects;

col "Space Over" for a30

col "Space Unused" for a30

col "Obj_Name_Dep" for a30

select

   type "Type",

   name "Name",

   namespace "SQL|PL/SQL",

   creation_timestamp "Creation",

   space_overhead "Space Over",

   space_unused "Space Unused",

   cache_id "Obj_Name_Dep",

   invalidations "Invds"

from

   gv$result_cache_objects;  

--Check the Result Cache Setting and Statistics

select

   name "Name",

   value "Value"

from

v$result_cache_statistics;

 

--Check objects cached

select

   o.owner "Owner",

   o.object_id "ID",

   o.object_name "Name",

   r.object_no "Obj Number"

from

   dba_objects o,

   gv$result_cache_dependency r

where

   o.object_id = r.object_no;

--Checking memory blocks and their status in Result Cache Memory

select   *  from

   gv$result_cache_memory;

SQL> set linesize 150

SQL> select id,name,row_count,type,invalidations from v$result_cache_objects;

##############  Requirements for the Result Cache ##############  

 

Enabling the result cache does not guarantee that a specific result set will be included in the server or client result cache. 

In order for results to be cached, the following requirements must be met:

1. Read Consistency Requirements

-> If the current session has an active transaction referencing objects in a query, then the results from this query are not eligible for caching.

2. Query Parameter Requirements

Cache results can be reused if they are parameterized with variable values when queries are equivalent and  the parameter values are the same. Different values or bind variable names may cause cache misses. Results are parameterized if any of the following constructs are used in the query:

-> Bind variables

-> The SQL functions DBTIMEZONE, SESSIONTIMEZONE, USERENV/SYS_CONTEXT (with constant variables), UID, and USER NLS parameters

Tracing Oracle Datapump expdp/impdp

Expdp and impdp has a built in mechanism to trace executions. We can add the (hidden) expdp parameter “trace” so we can mark the command to 
output a useful trace file. Unfortunately, this parameter is hidden – meaning it does not appear in the expdp help=yes and we still need to figure out how to use it.
The trace parameter is being set using an hexadecimal number with 7 digits. There is no need to add 0x at the beginning of the string – it will not accept decimal numbers or binary inputs. 
The number must be written with a lower case. We will also need the privileges to run trace on the session, and obviously, the permissions to export or import.


-- Example of combination (last 4 digits are usually 0300): 
40300 to trace Process services
80300 to trace Master Control Process (MCP)
400300 to trace Worker process(es)


In order to trace all Data Pump components, level 1FF0300 can be specified:
However for most cases full level tracing is not required. As trace 400300 is to trace Worker process(es) and trace 80300 is to trace Master Control Process (MCP).
So combining them is trace 480300 and by using trace 480300 you will be able to trace both Master Control process (MCP) and the Worker process(es). This would serve the purpose.



Run a Data Pump job with full tracing:

This results in two trace files in BACKGROUND_DUMP_DEST:   
--    Master Process trace file: <SID>_dm<number>_<process_id>.trc    
--    Worker Process trace file: <SID>_dw<number>_<process_id>.trc    

And one trace file in USER_DUMP_DEST:   
--    Shadow Process trace file: <SID>_ora_<process_id>.trc    



To use full level tracing issue data pump export as,
expdp DUMPFILE=expdp.dmp LOGFILE=expdp.log TRACE=1FF0300
To use full level tracing for data pump import operation issue import as,
impdp DUMPFILE=expdp.dmp LOGFILE=expdp.log TRACE=1FF0300



So to solve any data pump export problem issue,
expdp DUMPFILE=expdp.dmp LOGFILE=expdp.log TRACE=480300 metrics=yes
To solve any data pump import problem issue,
impdp DUMPFILE=expdp.dmp LOGFILE=expdp.log TRACE=480300 metrics=yes


Data Pump tracing can also be started with a line with EVENT 39089 in the initialization parameter file. 
This method should only be used to trace the Data Pump calls in an early state, e.g. if details are needed about the DBMS_DATAPUMP.OPEN API call. 
Trace level 0x300 will trace all Data Pump client processes. Note that this event cannot be enabled through an ALTER SYSTEM command, only a restart of the database will enable the event.

-- Enable event
ALTER SYSTEM SET EVENTS = '39089 trace name context forever, level 0x300' ;
-- Disable event
ALTER SYSTEM SET EVENTS = '39089 trace name context off' ;




We can also use  tfactl  to collect diagnostic information
tfactl diagcollect -srdc  dbexpdp
tfactl diagcollect -srdc  dbimpdp




References:

Export/Import DataPump Parameter TRACE - How to Diagnose Oracle Data Pump (Doc ID 286496.1)

Troubleshooting advanced queue issues in Oracle 19c database

 

Seeing   issues  related to queuing  below  article  is  documented to  have some handy steps for troubleshooting queuing issues 

AQ Components 

The four main components of AQ are: 

1. Message - A message consists of message content, or payload, which can be specified using typed or raw data and message attributes or control information.

 2. Message Queue – Messages are stored in queues and these queues act as “postal boxes” where different applications can look for “mail” in the form of messages. Thus, when one application wants to contact certain applications for certain tasks, it can leave messages in these queues, and the receiving applications will be able to find these messages for processing. 

3. Message Interfaces – AQ supports enqueue, dequeue, and propagation operations that integrate seamlessly with existing applications by supporting popular standards. AQ messages can be created, queried, propagated and consumed using popular application programming interfaces (API) such as PL/SQL, C/C++, Java, Visual Basic (through Oracle Objects for OLE), Python, Node.js, and ODP.NET. AQ provides support for the Java Message Service 1.1 (JMS) API that allows Java applications to utilize the message queuing functionality. 

4. Message Handling – AQ supports rule-based routing of messages according to data in the message payload or attributes. Additionally, message transformations can be applied to messages to re-format data before the messages are automatically delivered to target applications or subscribers. Oracle Database 19c can also exchange AQ messages with IBM MQ and TIBCO/Rendezvous through the Oracle Messaging Gateway.

Terms To Understand :

1) Queue 

2) Queue  tables 

3)  Propagations 

4) Subscribers 

Parameter Influencing Advanced queuing :

Main database parameter is aq_tm_processes . However below are other parameters  that influence advanced queuing 

aq_tm_processes

processes

job_queue_processe

timed_statistics

SGA_TARGET

streams_pool_size

undo_retention

_client_enable_auto_unregister

shared_pool_size 

Troubleshooting AQ : 

1) Restart  queue process and   queue monitoring 

 aq_tm_processes  --> make it 0 

 kill  existing   q0 process . 

oracle aq_tm_processes  --> make it 1 

ps -ef | grep -i e00  | grep -i  dbname  -- kill it 

-- Perform the below to reset the qmon process:

alter system set "_aq_stop_backgrounds"=TRUE;

alter system set "_aq_stop_backgrounds"=FALSE;

2)  Restart job  queue processes 

3) Check which jobs are being run by querying dba_jobs_running. It is possible that other jobs are starving the propagation jobs.

4)  Check to see that the queue table sys.aq$_prop_table_instno exists in DBA_QUEUE_TABLES. The queue sys.aq$_prop_notify_queue_instnomust  also exist in DBA_QUEUES and must be enabled for enqueue and dequeue.

5)   Turn on propagation tracing at the highest level using event 24040, level 10

6) . Debugging information is logged to job queue trace files as propagation takes place. You can check the trace file for errors and for statements indicating that messages have been sent.

7) Generate TFA as per 1905796.1 

$TFA_HOME/bin/tfactl diagcollect -srdc dbaqmon

8)  Generate   AQ & MGW Health Check   using Configuration Script ( Doc ID 1193854.1 )

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/03/aq-mgw-health-check-and-configuration.html

9)  Check  *qm0*.trc  in  trace   directory .

10)  Restart  Time Managers  from root container 

EXEC dbms_aqadm.stop_time_manager;

EXEC dbms_lock.sleep(120); -- sleep is must

EXEC dbms_aqadm.start_time_manager;

11)    Restart  queue process 

Begin 

dbms_aqadm.stop_queue  ('<schema name>.<queue name>'); ; 

END ; 

/

Begin 

dbms_aqadm.start_queue ( queue_name =>'QUEUENAME' ) ; 

END ; 

/

Views :

SELECT msg_state, MIN(enq_time) min_enq_time, MAX(deq_time) max_deq_time, count(*) FROM abdul_queue  GROUP BY msg_state;

select TOTAL_NUMBER 

from DBA_QUEUE_SCHEDULES 

where QNAME=’<source_queue_name>’;

select* from QUEUE_PRIVILEGES  ;

SELECT name, queue_type, waiting, ready, expired

FROM dba_queues q

JOIN v$aq v ON q.qid = v.qid

WHERE q.name = 'queue_name'  ;

 select count(*), min(enq_time), max(deq_time), state, q_name from ABDUL.QUEUE_TABLE group by q_name, state;

set line 100

col Parameter for a50

col "Session Value" for a20

col "Instance Value" for a20

SELECT a.ksppinm "Parameter",b.ksppstvl "Session Value",c.ksppstvl "Instance Value" FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm in ( '_client_enable_auto_unregister', '_emon_send_timeout' )

/

-- to check if dequeue is  happening 

select queue_id,shard_id,priority,ENQUEUED_MSGS,dequeued_msgs, enqueued_msgs-dequeued_msgs backlog from gv$aq_sharded_subscriber_stat order by queue_id, shard_id, priority;

 Check Message State and Destination. Find the queue table for a given queue

select QUEUE_TABLE 

from DBA_QUEUES 

where NAME = &queue_name;

Check for messages in the source queue with

select count (*) 

from AQ$<source_queue_table>  

where q_name = 'source_queue_name';

Check for messages in the destination queue.

select count (*) 

from AQ$<destination_queue_table>  

where q_name = 'destination_queue_name';

select * from GV$AQ  ;

select * from aq.aq$objmsgs80_qtab ;

select * from dba_queues where qid=14140;

SELECT * FROM SYS.ALL_QUEUES ;

select * from DBA_QUEUE_SCHEDULES ;

COLUMN OWNER HEADING 'Owner' FORMAT A10

COLUMN NAME HEADING 'Queue Name' FORMAT A28

COLUMN QUEUE_TABLE HEADING 'Queue Table' FORMAT A22

COLUMN USER_COMMENT HEADING 'Comment' FORMAT A15

SELECT q.OWNER, q.NAME, t.QUEUE_TABLE, q.USER_COMMENT

  FROM DBA_QUEUES q, DBA_QUEUE_TABLES t

  WHERE t.OBJECT_TYPE = 'SYS.ANYDATA' AND

        q.QUEUE_TABLE = t.QUEUE_TABLE AND

        q.OWNER       = t.OWNER;

DBA_QUEUE_TABLES: All Queue Tables in Database

The DBA_QUEUE_TABLES view contains information about the owner instance for a queue table. A queue table can contain multiple queues. In this case, each queue in a queue table has the same owner instance as the queue table. Its columns are the same as those in ALL_QUEUE_TABLES.

Werbung

DBA_QUEUES: All Queues in Database The DBA_QUEUES view specifies operational characteristics for every queue in a database. Its columns are the same as those ALL_QUEUES.

DBA_QUEUE_SCHEDULES: All Propagation Schedules The DBA_QUEUE_SCHEDULES view describes all the current schedules in the database for propagating messages.

QUEUE_PRIVILEGES: Queues for Which User Has Queue Privilege The QUEUE_PRIVILEGES view describes queues for which the user is the grantor, grantee, or owner. It also shows queues for which an enabled role on the queue is granted to PUBLIC.

AQ$Queue_Table_Name: Messages in Queue Table

The AQ$Queue_Table_Name view describes the queue table in which message data is stored. This view is automatically created with each queue table and should be used for querying the queue data. The dequeue history data (time, user identification and transaction identification) is only valid for single-consumer queues.

***********************

.

SET MARKUP HTML ON SPOOL ON HEAD "<TITLE> DIAG - INFO </title> -

<STYLE TYPE='TEXT/CSS'><!--BODY {background: ffffc6} --></STYLE>"

SET ECHO ON

spool AQ_Diag.html

set pagesize 120

set linesize 180

column global_name format a20

column current_scn format 99999999999999999999

alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';

alter session set nls_timestamp_format='dd-mon-yyyy hh24:mi:ss';

select global_name, sysdate from global_name;

select * from gv$version;

select inst_id, instance_number, instance_name, host_name, startup_time, status from gv$instance order by inst_id;

SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') "Current Container" FROM DUAL;

select con_id, name, cdb, current_scn from gv$database order by inst_id;

select * from gv$containers order by inst_id,con_id;

select * from gv$pdbs order by inst_id,con_id;

select owner, queue_table, type, RECIPIENTS from dba_queue_tables where

queue_table='ABDUL_QUEUE';

SELECT queue_table, owner_instance, primary_instance, secondary_instance

FROM dba_queue_tables

WHERE owner = 'ABDUL' AND queue_table = 'ABDUL_QUEUE';

select count(*), state, q_name from ABDUL.ABDUL_QUEUE

group by q_name, state;

select count(*), min(enq_time), max(deq_time), state, q_name from ABDUL.ABDUL_QUEUE

group by q_name, state;

select count(*), min(enq_time), max(deq_time), state, q_name from ABDUL.ABDUL_QUEUE

where deq_time < (sysdate - 1)

group by q_name, state;

select trunc(enq_time,'HH'), q_name, state, count(*), max(deq_time) from ABDUL.ABDUL_QUEUE

group by trunc(enq_time,'HH'),q_name, state;

select * from dba_queue_subscribers where queue_table='ABDUL_QUEUE';

spool off

SET MARKUP HTML OFF

SET ECHO ON

***********************

Possible message states in Oracle Advanced Queuing

Value	Name	Meaning
0	READY	The message is ready to be processed, i.e., either the delay time of the message has passed or the message did not have a delay time specified
1	WAITING or WAIT	The delay specified by message_properties_t.delay while executing dbms_aq.enqueue has not been reached.
2	RETAINED OR PROCESSED	The message has been successfully processed (dequeued) but will remain in the queue until the retention_time specified for the queue while executing dbms_aqadm.create_queue has been reached.
3	EXPIRED	The message was not successfully processed (dequeued) in either 1) the time specified by message_properties_t.expiration while executing dbms_aq.enqueue or 2) the maximum number of dequeue attempts (max_retries) specified for the queue while executing dbms_aqadm.create_queue.
4	IN MEMORY	User-enqueued buffered message
7	SPILLED	User-enqueued buffered message spilled to disk
8	DEFERRED	Buffered messages enqueued by a Streams Capture process
9	DEFERRED SPILLED	Capture-enqueued buffered messages that have been spilled to disk
10	BUFFERED EXPIRED	User-enqueued expired buffered messages

Known Issues :

1) Issue 1

AQ Queue Monitor Does Not Change Message MSG_STATE from WAIT to READY on RAC  after restart / patching :

Queue table "ABDUL_QUEUE" is AQ queue table owned by user "USER01" and view "AQ$ABDUL_QUEUE" is created when creating queue table "ABDUL_QUEUE" 

and returns read only data from "ABDUL_QUEUE" with more meaningful/readable values for columns .

During restart queue process goes in hung state and primary_instance  gets de-assigned .Check if any hung message detected  in  *qm0*.trc files 

This can be fixed by altering queue  

execute dbms_aqadm.alter_queue_table (queue_table => 'ABDU_QUEUE',primary_instance => 2,secondary_instance => 1);

select MSG_ID,MSG_STATE,DELAY,ENQ_TIME

  from USER01.AQ$ABDUL_QUEUE

 where MSG_STATE='WAIT'

order by ENQ_TIME;

select owner, queue_table, object_type, owner_instance, primary_instance, secondary_instance

  from dba_queue_tables where queue_table='ABDUL_QUEUE';

AQ Messages Not Changing Status from DELAY to READY Or READY to PROCESSED In RAC Databases (Doc ID 2879519.1)

2)  Issue 2 : 

 Sharded  Queues :  Sharded AQ:RAC: MESSAGES in READY when SHARD OWNERSHIP HAS CHANGED.

 select distinct MSG_PRIORITY from owner.aq_table ; 

 select * from GV$AQ_CACHED_SUBSHARDS ;

 select * from GV$AQ_REMOTE_DEQUEUE_AFFINITY;

 select * from SYS.aq$_queue_shards;

 select INST_ID,JOB_ID,QUEUE_NAME,JOB_STATE,MSGS_SENT,OWNER_INSTANCE_ID from GV$AQ_CROSS_INSTANCE_JOBS;

Purge Advanced Queue Table  if auto purge not happening 

1-)Identifiy how many data you will purge. If you want purge all off them you can easily purge without condition. 

select owner,name,queue_table,retention

from dba_queues

where owner='OWNER_AQ'

and queue_table='QUEUE_TABLE_T'

and queue_type='NORMAL_QUEUE';

select count(1)

from OWNER_AQ.AQ$QUEUE_TABLE_T

where msg_state=’PROCESSED’ and enq_time<to_date(’16/10/2017 17:05:30′,’dd/mm/yyyy hh24:mi:ss’);

2.1-) Purge queue table with conditions.

DECLARE

po dbms_aqadm.aq$_purge_options_t;

BEGIN

po.block := TRUE;

DBMS_AQADM.PURGE_QUEUE_TABLE(

queue_table => 'OWNER_AQ.QUEUE_TABLE_T',

purge_condition => 'qtview.queue = ''QUEUE_TABLE_Q''

and qtview.enq_timestamp < to_date(''16/10/2017 17:05:30'',''dd/mm/yyyy hh24:mi:ss'') and qtview.msg_state = ''PROCESSED''', purge_options => po);

END;

/

2.2-) Purge queue table without conditions. With this option you will gain space like truncate command on table .

DECLARE

po dbms_aqadm.aq$_purge_options_t;

BEGIN

dbms_aqadm.purge_queue_table('Queue_Table_Name', NULL, po);

END;

2.3-) If your dataset is big it can be executed like that. Condition may change.

begin

FOR i IN 1..15

LOOP

DECLARE

po dbms_aqadm.aq$_purge_options_t;

BEGIN

po.block := TRUE;

DBMS_AQADM.PURGE_QUEUE_TABLE(

queue_table => ‘OWNER_AQ.QUEUE_TABLE_T’,

purge_condition => ‘qtview.queue = ”QUEUE_TABLE_Q”

and qtview.enq_timestamp < to_date(”’||i*2||’/09/2017 05:05:30”,”dd/mm/yyyy hh24:mi:ss”) and qtview.msg_state = ”PROCESSED”’, purge_options => po);

END;

commit;

dbms_lock.sleep(20);

END LOOP;

end;

/

References : 

1) 

https://www.oracle.com/a/otn/docs/database/aq-db19c-technicalbrief.pdf

2) 

https://www.oracle.com/a/otn/docs/database/jmssq-db19c-technicalbrief.pdf

3) 

https://documentation.braintribe.com/Content/customer/oeamtc/oeamtcdocu/Troubleshooting/Oracle%20Advanced%20Queues%20(AQ).htm

4) 

https://docs.oracle.com/database/121/ADQUE/aq_intro.htm#ADQUE0100

5) 

https://docs.oracle.com/database/121/STRMS/strms_qpmon.htm#STRMS908

6) 

After import of queue: ORA-25226 dequeue failed queue not enabled for dequeue (Doc ID 1290221.1)

7) 

https://www.oracle.com/docs/tech/database/jmssq-db19c-technicalbrief.pdf

https://www.morganslibrary.org/reference/demos/aq_demo_rac.html