Exadata - Oracle DB Blocking Sessions in BRM application


Issue #:

 Issue occurrences: FEB 10th, FEB 15th, FEB 19th

Time : After 5:15PM for all occurrences

 SQL_ID : Query -- blocking itself and other queries


Analysis #1:

Query is not performing bad all the times and every time situation is not same. We can have a call today, to explain you more details.

 1.       10th feb – multiple select+update was running

2.       11th ,12th and 13th feb – query execution is less than 1 second

3.       14th Feb – execution of query around 2,00 times and it took 1.2 seconds per execution.

4.       15th Feb – not only this query but insert query on table also took more than 2,000 seconds. But nothing load or suspicious observed on DB level. No cpu or IO wait also observed.

5.       16th and 17th Feb – query took less than 1 second

6.       18th Feb – execution more than 3,000 times and took 1.32 seconds per execution

7.       19th Feb -- not only this query but insert query on same table also took more than 25 seconds. Stats gathering only observed

 10th Feb 5pm to 6pm:


Wait

Event

Wait Time

Summary Avg Wait Time (ms)

I#

Class

Event

Waits

%Timeouts

Total(s)

Avg(ms)

%DB time

Avg

Min

Max

Std Dev

Cnt

*

Application

enq: TX - row lock contention

41,882

0.00

397,243.98

9484.84

99.70

9546.90

8651.16

10442.64

1266.77

2

*

 

DB CPU

 

 

962.47

 

0.24

 

 

 

 

2

*

Network

SQL*Net more data from client

17,408

0.00

200.38

11.51

0.05

11.51

11.40

11.62

0.15

2

*

User I/O

cell smart table scan

1,107,581

41.36

191.88

0.17

0.05

0.17

0.17

0.17

0.00

2

*

User I/O

cell single block physical read

169,057

0.00

70.15

0.41

0.02

0.45

0.35

0.54

0.13

2

*

User I/O

Disk file Mirror Read

136,496

0.00

42.24

0.31

0.01

0.31

0.30

0.32

0.01

2

*

Cluster

gc current block 2-way

160,196

0.00

22.01

0.14

0.01

0.14

0.14

0.14

0.00

2

*

Other

Streams AQ: qmn coordinator waiting for slave to start

3

100.00

16.89

5629.46

0.00

5629.46

5629.46

5629.46

 

2

*

Cluster

gc cr block 2-way

118,706

0.00

16.22

0.14

0.00

0.14

0.14

0.14

0.00

2

*

System I/O

control file sequential read

171,775

0.00

14.20

0.08

0.00

0.08

0.07

0.10

0.02

2

Statistic

Owner

Tablespace Name

Object Name

Subobject Name

Obj. Type

Value

%Total

%Capture

buffer busy waits


 

TABLE

133

 

50.00

buffer busy waits


 

TABLE

104

 

39.10

gc buffer busy


 

TABLE

5

 

20.83

physical writes


 

TABLE

2,726

 

16.89

physical write requests


 

 TABLE

2,365

1.67

17.00

physical write requests


        

   INDEX PARTITION

2,026

1.43

14.56

row lock waits


 

  TABLE

4,372

 

96.88

DML operations for 20th Feb 2020. Updates 3 times greater than inserts. No deletes.

Tables size is 3GB.

Table growth from last 30 days.

Table information:

Table record count:

<table information pulled>

Analysis #2:

 Capturing Live blocking sessions information.





Analysis #3:

 Created Health Checkup report for monitoring purpose.

Application Question:

 We have created Oracle SR under Oracle in Application category and currently we are parallelly working on capturing Debug level logs to create the issue, working on suggestion from delivery and checking customer code for memory leaks and error handling.

 But for the insert into account SQL which is getting blocked by select for update SQL.

Could you please check what can cause this and insert SQL taking so long and suggestions.

 Answer:

 Similar Application level debug enabling, we can enable debug on DB user session level. But we need core DBA support.

 https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof

@Core DBA: please can you provide any trace logs generated on 21st Feb between 5:00pm to 7:00pm.

 Just tracked some more info

 Session 5758 blocked other sessions. This session is inactive and no sql_id (means just simple idle and blocking other session)

 <session information >

 Before getting idle or inactive, previous sql_id

 <sql text information>

@Core DBA:

 After observing below session 5758 is waiting for data in UNDO segment (no sql and inactive) and this query is blocked other sessions today @5:35PM.

Moreover, I seen alert error on TEMP segment @10:16 am.   Please have a look on my observations.

 Please can you help us to provide trace files from 5pm to 6pm today.  Temp segment error trace file also. So that we can look in detail about this session 5758.

Analysis #4:

 *** 2020-02-21 17:26:25.751

Verified Hangs in the System

                     Root       Chain Total               Hang              

  Hang Hang          Inst Root  #hung #hung  Hang   Hang  Resolution        

    ID Type Status   Num  Sess   Sess  Sess  Conf   Span  Action            

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

    260 HANG UNRSLVBL    1    16     2     3    LOW  LOCAL Unresolvable:User 

  Hang Ignored Reason: This hang is probably the result of an application

    problem.  External invention is required.  This hang will be ignored.

 

  inst# SessId  Ser#     OSPID PrcNm Event

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

      1   3939 38255    171413    FG enq: TX - row lock contention

      1     16 35377    148784    FG SQL*Net message from client

 

Victim Information

                                                                      Ignored

  HangID  Inst#  Sessid  Ser Num      OSPID  Fatal BG  Previous Hang    Count

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

     259      2      23    22497       8936     N      Existing Hang        1

     260      1      16    35377     148784     N           New Hang        1

 

*** 2020-02-21 17:26:25.751

@App Team:

 Point 1 and 2 are referred to deadlock sessions --> very less chances were DB will automatically kill any one session. But here that is also not happening.

Point 3 and 4 --> we already checked AWR report and we did not see much load.

 Trace files are generated by DB code, beyond this needs to look then we need to contact Oracle support.

The following information will assist Oracle Support Services

in further analysis of the root cause of the hang.

 

*** 2020-02-21 17:26:25.751

Verified Hangs in the System

                     Root       Chain Total               Hang              

  Hang Hang          Inst Root  #hung #hung  Hang   Hang  Resolution        

    ID Type Status   Num  Sess   Sess  Sess  Conf   Span  Action            

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

    260 HANG UNRSLVBL    1    16     2     3    LOW  LOCAL Unresolvable:User 

  Hang Ignored Reason: This hang is probably the result of an application

    problem.  External invention is required.  This hang will be ignored.

 Seems oracle given script from doc id 2530284.1. below Doc id has given solution also.

Sessions Concurrency Observed in BRM Database (Doc ID 2530284.1)

 If oracle suggest same thing then please provide below our observation.

IN above doc id, sql is blocking other sql but in given report, we can see blocker Sql is empty but same locked row# access from different instances.

 Locked object    : <table_name>, <schema_name>

Locked row#      : AAAO7pAAAAAAH/8AAa[o#=61161, f#=1024, b#=32764, r#=26] --> same row# requested from different instance ( 1 and 2 )

Blocker SQL        : []  --> empty


Solution:

 Oracle BRM application SR team given configuration changes to fix blocking sessions issue.


Comments

Post a Comment

Popular Posts

AWR Report Analysis

Image
AWR report analysis is important for  Oracle DBA on Daily issues or P1/P2 issues time. Below some of the main things we general look to figure out issue. 1. 2.  3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13.
Read more

Goldengate 19c Installation with issue resolution

Image
  Basic Installation of GoldenGate 19c in OEL6. Tested with below requirements: Step1: Vmware requirements:   For installing OEL6.8 and Oracle DB 12c  installation, please refer URL https://venuoracledba.blogspot.com/2020/08/oracle-12c-2-node-rac-setup-in-vmware.html     Step2: After OEL6.8 installation finished. We will use /opt for goldengate installation.   Step3: unzip GG software in folder. Here unzipped in gg_software folder.     Folder fbo_ggs_Linux_x64_shiphome will create. Step4:  Enable GUI by issuing x host + command in root user. and keep open this root terminal.   Step5:  Enable GUI by issuing x host + command in oracle user.   Move to software path   execute ./runInstaller and select database version. Selected 12c version for my environment. and click Next     provide GG home, oracle home and port number and click Next.     After verifying paths and information, Click Install   ...
Read more

Oracle 11g Physical Standy Setup

  Step1: take Binaries tar backup from Primary DB server tar -cvf /path/11.2.0.4.tar 11.2.0.4 Step2: untar Binaries tar back on Standby DB server tar -xvf /path/11.2.0.4.tar -C /path ###################### Binaries Cloning ################### Step3: run clone command on Standby DB server export ORACLE_HOME=<PATH> cd $ORACLE_HOME/clone/bin perl $ORACLE_HOME/clone/bin/clone.pl ORACLE_BASE='<PATH>' ORACLE_HOME='<PATH>' ORACLE_HOME_NAME='<ANY_NAME>' ###################### POST Binaries Cloning ################### Step4: run root scripts $ORACLE_BASE/oraInventory/orainstRoot.sh $ORACLE_HOME/root.sh ###################### Pre-requisites ################### Standby: Step5: update hostnames in listener and tnsnames files Step6: start the listener in standby Server Primary: Step7: check force logging enabled -- to make sys activities will logged in redo logs SQL> select force_logging from v$database; - NO ( default ) to enable force logging - S...
Read more

Oracle 12c 2-node RAC setup in VMware Player 15 -- Part01

Image
Tried to install Oracle 12c 2-node RAC for testing purpose but due to low Hardware configuration, ended up with Single Node RAC. I got lot of issues during Installation and tried to resolve them with my best knowledge. Please find below installation steps and troubleshooting. Main issues started from Step90. you can go through quickly. Requirements Most of the times, bridge option in VMWARE will not connect with host machine. I tried below option to use internet connection from host machine. We need to cross verify vmnet8 is NAT or not. Installation Steps Step1 launch VMware and click on New Virtual Machine Step2   select OEL iso Image Step3 Provide New Virtual Machine Name Step4 Provide HDD size and select single virtual disk. Step5  Select host setting for monitor and Network as NAT Step6 New Virtual Machine is ready for launch. Step7 OEL Installation starts automatically. Press Enter Step8 Skip Disk Checking Step9 Click Next Step10   Select langu...
Read more

Oracle to Postgres

Image
  Round 1: Created metadata for tables, sequences, packages Round 2: Created indexes Round 3: freeze column name to freezes to_date function updated GEOMETRY datatype , installed POSTGIS package to support this datatype Round 4: created triggers created Functions Migration: bash-4.2$ pwd /usr/edb/migrationtoolkit/etc bash-4.2$ cat toolkit.properties SRC_DB_URL=jdbc:oracle:thin:@localhost:1521:orcl SRC_DB_USER=hr SRC_DB_PASSWORD=hr TARGET_DB_URL=jdbc:edb://postoffline:5444/dev TARGET_DB_USER=enterprisedb TARGET_DB_PASSWORD=password bash-4.2$  -bash-4.2$ pwd /usr/edb/migrationtoolkit/bin -bash-4.2$ ls -lrt total 724 -rwxr-xr-x. 1 root root   2439 Dec 11 13:29 runJavaApplication.sh -rwxr-xr-x. 1 root root    544 Dec 11 13:33 runMTK.sh -rwxr-xr-x. 1 root root 729258 Dec 11 13:33 edb-migrationtoolkit.jar -bash-4.2$  ./runMTK.sh HR OUTPUT: Running EnterpriseDB Migration Toolkit (Build 55.7.0) ... Source database connectivity info... conn =jdbc:oracle:thin:@o...
Read more

Execution_Explain Plan

Whenever we get request for application slowness or sql query then we need to identify sql_id and to analyze sql_id, we need to check SQL execution/explain plan. 1. EXECUTION_PLAN: select id,plan_hash_value,operation, options,object_name,cardinality,cost,bytes,cpu_cost,io_cost,time,partition_start,partition_stop, object_node,other_tag,qblock_name,other_xml from dba_hist_sql_plan where sql_id='<SQL_ID>'; or select * from table(dbms_xplan.display_awr('<SQL_ID>')); EXPLAIN_PLAN: explain plan for <SQL_QUERY>; select * from table(dbms_xplan.display);
Read more

Active Session History Analysis

Image
ASH  analysis is important for  Oracle DBA on Daily issues or P1/P2 issues time. Below some of the main events we general look to figure out issue. RAC / Cloud RDS: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. Redo Generation: 14. 15. 16. 17. 18. 19.
Read more

General Queries

                                                                              Tablespace growth Size We can use view to check tablespace size greater than 80% RAC / NON-RAC / Cloud RDS: SELECT tbm.TABLESPACE_NAME,   round(tbm.USED_SPACE * tb.BLOCK_SIZE /(1024*1024*1024),2) as USED_SPACE_GB,   round(tbm.TABLESPACE_SIZE * tb.BLOCK_SIZE /(1024*1024*1024),2) as TBS_SIZE_GB,   round((tbm.TABLESPACE_SIZE - tbm.USED_SPACE) * tb.BLOCK_SIZE /(1024*1024*1024),2) as TBS_FREE_SIZE_GB,   round(tbm.USED_PERCENT) as PERCENT_USEDFROM dba_tablespace_usage_metrics tbm      join dba_tablespaces tb on tb.TABLESPACE_NAME = tbm.TABLESPACE_NAME w...
Read more

Fragmentation & Re-organisation

Total  Savings: set verify off column file_name format a50 word_wrapped column smallest format 999,990 heading "Smallest|Size|Poss." column currsize format 999,990 heading "Current|Size" column savings format 999,990 heading "Poss.|Savings" break on report compute sum of savings on report column value new_val blksize select value from v$parameter where name = 'db_block_size' / select file_name, ceil( (nvl(hwm,1)*&amp;&amp;blksize)/1024/1024 ) smallest, ceil( blocks*&amp;&amp;blksize/1024/1024) currsize, ceil( blocks*&amp;&amp;blksize/1024/1024) - ceil( (nvl(hwm,1)*&amp;&amp;blksize)/1024/1024 ) savings from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) order by savings desc / HWM query for all datafiles: col data_file_name for a50 col tablespace_name for a20 set lines 300 select tablespace_name, file_id, file_name DATA_FILE_NAME, Al...
Read more

OEM patches & vulnerabilities

Image
  Tools: Agent: agentpatcher OMS: omspatcher coherence/weblogic: optach 1. Agent JDK update. please refer the below oracle reference page Supported Java SE Downloads on MOS (oracle.com) 2. Removing log4j-core-2.8.2.jar validate jar file exist cd $OEM_BASE/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/_WL_user/emdb/1danf1/database/jet/emsaasui/emcdbms-ui/ear/APP-INF/lib $OEM_BASE/agent_13.5.0.0.0/oracle_common/jdk/bin/jar tvf log4j-core-2.8.2.jar | grep JndiLookup.class output 2974 Sun Apr 02 15:24:22 AEST 2017 org/apache/logging/log4j/core/lookup/JndiLookup.class stop OMS  $OEM_BASE/middleware/bin/emctl status oms -details  $OEM_BASE/middleware/bin/emctl stop oms -all remove log4j zip -q -d log4j-core-2.8.2.jar org/apache/logging/log4j/core/lookup/JndiLookup.class validate jar file exist $OEM_BASE/agent_13.5.0.0.0/oracle_common/jdk/bin/jar tvf log4j-core-2.8.2.jar | grep JndiLookup.class start OMS  $OEM_BASE/middleware/bin/emctl status oms -details ...
Read more