Warning: log write broadcast wait time NNNms (SCN 0x0.XXXXXXXXXX)

Issue:

Warning: log write broadcast wait time NNNms (SCN 0x0.XXXXXXXXXX)

Explanation:

Broadcast on commit could fail to process pending broadcasts in a
timely manner in RAC, potentially affecting performance.

Unusually long broadcast ack warning messages are dumped to trace files:

“Warning: log write broadcast wait time …”

and/or foreground Log File Sync timeouts occur rather frequently
using Broadcast on Commit (default).

If this message occurring few times it can be ignored.

HTI (AGGREGATION[2]): resizing from XX slots to YY slots

HTI (AGGREGATION[2]): resizing from XX slots to YY slots

Issue:

The following message appears on Oracle trace file(s):

HTI (AGGREGATION[2]): resizing from XX slots to YY slots

Explanation:

This is just a stray message.

There is no indication that it has any impact on a system.

Oracle use this message for diagnostic purposes.

It simply documents a resize of an internal memory structure, over which application has no control (no ability to make use of
the information.) It should be ignored.

Understanding of locks, reading Oracle trace.

Understanding of locks, reading Oracle trace.

In this case, A session in 1 instance is initiating a transaction will hold exclusive mode lock on a TX resource, and a
session(instance 2) waiting to modify the row locked by the first transaction will request exclusive mode lock on the lock holder’s TX resource.

Testcase
————–

SQL> select instance_name from v$instance;

INSTANCE_NAME
—————-
apex1

SQL> select
sys_context(‘USERENV’,’SID’)
from dual; 2 3

SYS_CONTEXT(‘USERENV’,’SID’)
——————————————————————————–
329
SQL> show user
USER is “TEST”

SQL> CREATE TABLE test (n1 NUMBER , v1 VARCHAR2(100));
INSERT INTO test
SELECT n1, lpad (n1, 100,’SIDDHU’)
FROM
( SELECT level n1 FROM dual CONNECT BY level <=500
);
COMMIT;
Table created.

SQL> 2 3 4 5
500 rows created.

SQL>

Commit complete.

SQL>
SQL>

SQL> EXEC dbms_stats.gather_table_stats ( USER, ‘test’, CASCADE =>true);

PL/SQL procedure successfully completed.
Update a Single Row
—————–
SQL> update test set n1=n1 where n1=100;

SQL> select dbms_Transaction.LOCAL_TRANSACTION_ID from dual;

LOCAL_TRANSACTION_ID
——————————————————————————————————————————————————————————————————-
8.31.3741 >>>>>>>>>>>>>>>> A
From another Instance:
SQL> select instance_name from v$instance;

INSTANCE_NAME
—————-
apex2
SQL> select
sys_context(‘USERENV’,’SID’)
from dual; 2 3

SYS_CONTEXT(‘USERENV’,’SID’)
——————————————————————————————————————————————————————————————————-
16

SQL> update test set n1=n1 where n1=100; <<Hangs>>

—————————————————————-
SQL> SELECT sid, type, id1, id2, lmode, request
FROM gv$lock WHERE (type, id1, id2) IN
(SELECT type, id1, id2 FROM gv$lock WHERE request>0) ; 2 3

SID TY ID1 ID2 LMODE REQUEST
———- — ———- ———- ———- ———-
16 TX 524319 3741 0 6 >>> Requester
329 TX 524319 3741 6 0 >>> Holder
The combination of enqueue types id1 and id2 uniquely identifies the transaction.

SQL> col state format A10
SQL> col pid format 99999999
SQL> set serveroutput on size 100000
SQL> begin
2 print_Table (‘
3 with dl as (
4 SELECT inst_id, resource_name1, grant_level, request_level,
5 transaction_id0, which_queue, state, pid, blocked ,
6 blocker
7 FROM gv$ges_blocking_enqueue
8 )
9 SELECT dl.inst_id, dl.resource_name1, dl.grant_level,
10 dl.request_level, dl.state, s.sid, sw.event,
11 sw.seconds_in_wait sec
12 FROM dl,
13 gv$process p, gv$session s, gv$session_wait sw
14 WHERE (dl.inst_id = p.inst_id AND dl.pid = p.spid)
15 AND (p.inst_id = s.inst_id AND p.addr = s.paddr)
16 AND (s.inst_id = sw.inst_id AND s.sid = sw.sid)
17 ORDER BY sw.seconds_in_wait DESC
18 ‘);
19 end;
20 /
INST_ID : 2
RESOURCE_NAME1 : [0x8001f][0xe9d],[TX][ext 0x0, >>>>>>> B
GRANT_LEVEL : KJUSERNL
REQUEST_LEVEL : KJUSEREX
STATE : OPENING
SID : 16
EVENT : enq: TX – row lock contention
SEC : 81

—————–
INST_ID : 1
RESOURCE_NAME1 : [0x8001f][0xe9d],[TX][ext 0x0,
GRANT_LEVEL : KJUSEREX
REQUEST_LEVEL : KJUSEREX
STATE : GRANTED
SID : 329
EVENT : ges remote message
SEC : 0

—————–

PL/SQL procedure successfully completed.
Transaction_id is used to coin a unique resource_name [0x8001f][0xe9d],[TX] in GRD. The first part of
string [0x8001f] is the concatenation of strings 0x8 and 01f. 0x8 is a hexadecimal representation of decimal
8, and 01f is the hexadecimal representation of 31. The second part of string 0xe9d is the hexadecimal
representation of 3741 . These three parts, 8.31.3741 , combined together constitute the transaction_id we queried
using dbms_transaction package call(A=B).

Apart from that, with the queries that were provided earlier, you can get the GES LOCK BLOCKERS/WAITERS etc.

SQL> col state format a10
col inst_id format 99 head Inst
col owner_node format 99 head ‘Owner|Node’
SELECT inst_id, resource_name1, pid, state,
owner_node , grant_level, request_level
FROM gv$ges_enqueue
WHERE resource_name1 LIKE ‘[0x8001f][0xe9d],[TX]%’ ;SQL> SQL> SQL> SQL> 2 3 4

Owner
Inst RESOURCE_NAME1 PID STATE Node GRANT_LEV REQUEST_L
—- —————————— ——— ———- —– ——— ———
1 [0x8001f][0xe9d],[TX][ext 0x0, 49385 GRANTED 0 KJUSEREX KJUSEREX
1 [0x8001f][0xe9d],[TX][ext 0x0, 0 GRANTED 1 KJUSERNL KJUSEREX
2 [0x8001f][0xe9d],[TX][ext 0x0, 53226 OPENING

Oracle OEM performance charts don’t render.

There are a few reasons, why OEM can stop rendering performance charts.

This post addresses one of the possible solutions.

Follow carefully the below steps:

1- Take a backup of the following file (from the problematic database):

ORACLE_HOME/oc4j/j2ee/oc4j_applications/applications/em/em/WEB-INF/uix-config.xml

2- Open the file for editing and locate the following entry:

<!– An alternate configuration that disables accessibility features –>
<default-configuration>
<accessibility-mode>inaccessible</accessibility-mode>
</default-configuration>

3- Change the value of the “<accessibility-mode>” property from “inaccessible” to “accessible”.

4- Save the file and restart the DBConsole.

5- Check the status of the issue.

What’s the minimum target Oracle version that OEM 12c will be able to monitor?

Certified target database (for OMS/Agent 12.1.0.4.) are the following:
10.1.0.5.0 / 10.2.0.4.0 / 10.2.0.5.0 / 11.1.0.7.0 / 11.2.0.x / 12.x