Skip to main content

Cloud Control EM13c - Loader Throughput (rows per second) for Loader_D crossed the critical threshold and Total Loader Runtime in the Last Hour (seconds) for Loader_D crossed the critical threshold

Hello Guys,

Here is an another post related to Cloud Control EM13c - Real-time Scenario.

Suddenly We were receiving the following warning and critical alerts from EM13c Cloud Control during the time slot between 12 AM and 04:00 AM daily.

1) Message=Loader Throughput (rows per second) for Loader_D crossed the critical threshold (xx). Current value: xx.xx

 2) Message=Total Loader Runtime in the Last Hour (seconds) for Loader_D crossed the critical threshold (x,xxx). Current value: xxxx.xx

3) ORA-error stack (3,136) and ORA-error stack (609) were also logged in alert log

 4) Message=Incident (BEA-310003 [LowMemory]) detected in $OMS_HOME/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/adr/diag/ofm/GCDomain/EMGC_OMS1/alert/log.xml at time/line number:


When we analyzed the AWR reports of EM PDB repository database, we found there were few PL/SQL statements given below causing this issue with wait event SQL*Net Break/reset To Client Waits.
SQL statement with SQL_ID 4p3rny0g5b2ua - wait event "SQL*Net break/reset to client"
BEGIN EM_NOTIF_PKG.QUEUE_READY(:1 , :2 , :3 ); END;

SQL statement with SQL_ID a9t927jdagvtt - wait event "SQL*Net break/reset to client"
begin em_event_bus.listen(:1 , :2 , :3 ); end;

SQL statement with SQL_ID aphss0yj5ygxx - wait event "SQL*Net break/reset to client"
begin em_cntr_queue.listen(:1 , :2 , :3 ); end;

SQL statement with SQL_ID bafrnujjt7mgz - wait event "SQL*Net break/reset to client"
BEGIN EM_EVENT_BUS.DEQUEUE_ISSUE(:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ); END;

SQL statement with SQL_ID f6g21156r5v3y - - wait event "enq: TX - row lock contention"
SELECT BLACKOUT_STATUS FROM EM_BLACKOUTS_NG WHERE BLACKOUT_GUID = :B1 FOR UPDATE;

So when we googled further more days, we found a solution on community forum.
https://community.oracle.com/thread/3957820?parent=MOSC_EXTERNAL&sourceId=MOSC&id=3957820

Here the situation differs but symptoms are same as our environment. A guy (working in ORACLE as Framework developer) has correctly answered the question saying that “These symptoms are only due to the lack of resources situation. So adjusting the Heap Size should solve the problem”.

On the same day we got this solution, we received following critical alert on OMS HEAP USAGE also.

Target type=Oracle WebLogic Server
Target name=/EMGC_GCDomain/GCDomain/EMGC_OMS1
Categories=Capacity
Message=The heap usage is 100%.
Severity=Critical 


Note:

The heap memory settings for the OMS are defined with the following properties:
OMS_HEAP_MAX

OMS_HEAP_MIN
OMS_PERMGEN_ MIN
OMS_PERMGEN_MAX


Then we checked the Heap usage graph in Cloud Control EM13c Console and it was also showing sudden increase in the problematic window for few days and it's because of more backlog request have been written at that time which requires more memory and also we have a medium environment according to EM deployment guide but allocated OMS_HEAP_MAX was 2GB only. 

OMS HEAP USAGE GRAPH:








OMS_HEAP_MAX=2048M

Other parameter settings are same as mentioned in the EM deployment guide.

So we have decided to allocate 4GB of OMS_HEAP_MAX  as per deployment guide  by following below steps.

1) $OMS_HOME/bin/emctl set property -name <property_name> -value <number_followed_by_G_or_M>

For example:

$OMS_HOME/bin/emctl set property –name OMS_HEAP_MAX –value 4096M

Note: Values set using emctl command will override the values in the startEMServer.sh file.

And these parameters will carry forward whenever the OEM is upgraded.

2. Restart the oms to implement the changes
$ emctl stop oms -all
$ emctl start oms


After this change, we have not received any of the above mentioned alerts once again and OMS HEAP USAGE also became normal all the time.

For your information:

Current settings can be checked with the following commands:

$ emctl get property -name OMS_HEAP_MAX
$ emctl get property -name OMS_HEAP_MIN
$ emctl get property -name OMS_PERMGEN_ MIN
$ emctl get property -name OMS_PERMGEN_MAX


Note:
If user set the java memory arguments using JAVA_EM_MEM_ARGS parameter, that will override the memory settings for OMS as well as other EM integrated components such as BI Publisher.


For example, if you set Max Heap memory as 4GB using this parameter. OMS application sets with 4GB and BI Publisher with another 4GB.

Recommended to use the parameter JAVA_EM_MEM_ARGS only when OMS is not integrated with any other application.

Comments

Post a Comment

Popular posts from this blog

Export and import multiple schema using expdp/impdp (Data Pump utility)

Use the below sql query to export and import multiple schema: expdp schemas=schema1,schema2,schema3 directory=DATA_PUMP_DIR dumpfile=schemas120514bkp.dmp exclude=statistics logfile=expdpschemas120514.log impdp schemas=schema1,schema2,schema3 directory=DATA_PUMP_DIR dumpfile=schemas120514bkp.dmp logfile=impdpschemas120514.log sql query to export and import a schema: expdp schemas=schema directory=DATA_PUMP_DIR dumpfile=schema120514bkp.dmp exclude=statistics logfile=expdpschema120514.log impdp schemas=schema directory=DATA_PUMP_DIR dumpfile=schema120514bkp.dmp logfile=expdpschema120514.log Parameter STATISTICS=NONE can either be used in export or import. No need to use the parameter in both. To export meta data only to get ddl of the schemas: expdp schemas=schema1,schema2,schema3 directory=TEST_DIR dumpfile=content.dat content=METADATA_ONLY exclude=statistics To get the DDL in a text file: impdp directory=TEST_DIR sqlfile=sql.dat logfile=sql.log dumpfil...
3 comments
Read more

Pinning execution plan for a sql_id using 'SQL Profile' to consistently reproduce a good plan

Deal all, Below post content is not my own. It is taken from MOSC document but the example shown below is taken from our environment. I got to know the below topic when we had experienced the bad SQL query performance in one of our production environment during daily batch running time. Hence we had implemented the same mentioned in the title "Pinning execution plan for a sql_id using 'SQL Profile' to consistently reproduce a good plan". Introduction The "cost-based optimizer (CBO or just optimizer) is the part of database software that determines the most efficient means of accessing the specified data (or at least what it "calculates" as the most efficient). The optimizer determines which execution plan is most efficient by considering several sources of information, including query conditions, available access paths, statistics gathered for the system, and hints. Some changes in those conditions may affect the calculations of the plan...
9 comments
Read more

Metrics Process Limit Usage percentage (%) is critical or warning in OEM or Cloud Control 12c or Cloud Control 13c

Please run the below queries to trace details during Metric alert "Process Limit % is >= 75" in OEM or Cloud Control 12c or Cloud Control 13c To check the Metrics Process Limit Usage (%): ***************************************************** set lines 300 col INITIAL_ALLOCATION for a17 col LIMIT_VALUE for a12 select resource_name,current_utilization,max_utilization, INITIAL_ALLOCATION,LIMIT_VALUE,((current_utilization*100)/(INITIAL_ALLOCATION)) as "Process limit %" from v$resource_limit where resource_name in ('processes'); The below query will give you all connected sessions (ACTIVE and INACTIVE) to instance: ******************************************************************************************************* set pages 100 set lines 1000 col SPID heading 'PID' for a06 col SID for 99999 col SERIAL# heading 'serial' for 999999 col SQL_ID for a14 col USERNAME for a10 col PROGRAM for a20 col MODULE for a18 col OSU...
Post a Comment
Read more