Exadata - Oracle DB Performing Slow due to High IO

Issue #:

People are complaining that DB is performing slow and observing high I/O. Earlier to today, could see there are few set of functions related to application Module load causing the slowness.Can you please have a look further and provide your guidance.

 Analysis #1:

 From below AWR report, we can see cell getting exhaust with full table scan. Can you please provide cell statistics (load) between 1pm to 2pm (today and yesterday).

 @App lead:

Every time we cannot say jobs load issue if data comes more. We need to go in detail where exactly caused problem.

 From last week, I have seen load max 12 but today it went to 15. And both instances more than 10.


This time USER IO is high and timeout is 47% (not good sign).

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

*

User I/O

cell smart table scan

85,678,559

47.82

251,734.83

2.94

65.15

2.94

2.93

2.95

0.01

2

*

User I/O

cell single block physical read

75,291,249

0.00

45,166.16

0.60

11.69

0.60

0.60

0.60

0.00

2

*

DB CPU

33,067.17

8.56



2

*

User I/O

cell multiblock physical read

2,605,214

0.00

8,380.55

3.22

2.17

3.12

2.47

3.77

0.92

2

*

User I/O

cell list of blocks physical read

5,474,088

0.00

5,052.89

0.92

1.31

0.94

0.89

0.99

0.07

2

*

User I/O

direct path read temp

262,112

0.00

4,348.86

16.59

1.13

16.65

15.94

17.36

1.00

2

*

User I/O

direct path read

1,017,655

0.00

3,059.73

3.01

0.79

2.49

1.92

3.05

0.80

2

*

Network

SQL*Net more data from client

578,089

0.00

2,593.47

4.49

1.26

4.49

4.49

4.49

1

*

Scheduler

resmgr:cpu quantum

203,446

0.00

1,426.67

7.01

0.37

7.20

6.11

8.29

1.54

2

*

Cluster

gc cr grant 2-way

10,534,866

0.00

1,402.35

0.13

0.36

0.17

0.12

0.22

0.08

2

Below highlighted sql_id’s, cpu and IO wait are very high. we need to talk with developer team to tune this pl/sql.

Total

Per Execution

Percentage of Total

SQL Id

CPU (s)

Elapsed (s)

IOWait (s)

Gets

Reads

Rows

Cluster (s)

Execs

CPU (s)

Elapsed (s)

IOWait (s)

Gets

Reads

Rows

Cluster (s)

DB CPU

DB time

IO Wait

Gets

Reads

Cluster

Execs

SQL Text

3x4h2q7rhhq30

1,147.15

3,381.13

2,315.04

28,337,764

12,787,826

1

156.77

1

1,147.15

3,381.13

2,315.04

28,337,764.00

12,787,826.00

1.00

156.77

3.47

0.88

0.72

0.20

0.09

4.82

0.00

DECLARE job BINARY_INTEGER := ...

fdp0brc3xgcpy

1,146.47

2,641.43

1,483.05

53,540,177

42,737,097

1

87.61

1

1,146.47

2,641.43

1,483.05

53,540,177.00

42,737,097.00

1.00

87.61

3.47

0.68

0.46

0.37

0.31

2.70

0.00

DECLARE job BINARY_INTEGER := ...

 Below we can see highlighted table doing full table scan.


@core DBA:

Seems there is no metric created in OEM for Exadata machine. If we can enable metric IOPS then it will be easy for us to analyse Exadata cells IO whenever we see “cell smart table scan”.

Please find attached oracle document for metric details along with creation steps.

After creating metric, expected output will be:

Analysis #2:

 

Requested by app team:

 

Please find below current SGA and CPU. If we can increase SGA to 60GB and CPU to 24.

NAME                                               TYPE    VALUE   

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

sga_max_size                                       big integer 34G

sga_target                                         big integer 28G

cpu_count                                          integer     12

                                                                                                  

You can change large pages also based on SGA. This will help us for LOB’s segment.

use_large_pages

*

TRUE

@core DBA

 App team accidently confirmed DB facing issue because of memory/cpu crunch. So, Client requested how much memory and cpu needed extra so that he can approve it. So, I suggested values.

 DB is using ASMM only, there is no value set for memory_target value.

NAME                                               TYPE        VALUE
-------------------------------------------------- ----------- -----
memory_max_target                                  big integer 0
memory_target                                      big integer 0 

DB parameter use_large_pages set to TRUE, so I suggest to check huge_pages on server level and change it accordingly, so that we can take advantage when ever memory requested more than default 4KB. Can you please provide system parameter values from server level and you can check current huge_pages value set in server level.

 https://docs.oracle.com/database/121/UNXAR/appi_vlm.htm#UNXAR403

https://www.carajandb.com/en/blog/2016/7-easy-steps-to-configure-hugepages-for-your-oracle-database-server/

Analysis #3:

 Please find below my analysis for right side graph.

 1.       There is spike after 12pm on 7th Jan but not on 8th Jan for IORM, IO.

2.       If you see IO is constant for whole period but after 12PM on 8th is also constant.

3.       IORM wait time is 12ms on 7th Jan whereas on 8th Jan < 2ms and if you see small IO also increased during that time.

 Please have a look on belong oracle doc, it is not easy to identify IO bottle neck using AWR report only. We need Exadata health checkup also.

http://www.aioug.org/ODevCYatra/2018/Karan_Oracle_DISK_IO.pdf

 If they configure Exadata (read only for us), then we can easily analyse whenever we will have issue in DB.

https://docs.oracle.com/cd/E24628_01/doc.121/e27442/ch7_metrics.htm#EMXIG356

 Core DBA: Has anyone done a TFA analysis? I would like to see the OSWatcher logs between 12 and 3PM that day. If we have raised an SR for the elevated cell smart scan event, we should get a TFA diagcollect and send that to Oracle for analysis.

Venu: We already requested to SR team but hey refused to look TFA report and told us to first test given hidden parameters.

Core DBA:

It seems the cell server bad health started from Nov19th, still it is in RED. To do deep troubleshooting/analysis may we need to initiate discussion with L3 DBA team.

Final Solution:

We collected SQL's which are performing full table scan and used high IO. We ran sql tuning advisor, collcted sql history plan values and manually checked missing columns indexes. At last we created new indexes for impacted SQLs to reduce IO.



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