Analyzing Undo from AWR and DBA Views

Application Team encountered an unable to extend Undo in one of their Prod Batch Job but there we no error in alert log on mentioned time

Mar 31 04:26:00 ORA-30036: unable to extend segment by 4 in undo tablespace ‘UNDOTBS1’

Alert Log entry

Sat Mar 31 03:08:08 2018
Archived Log entry 112180 added for thread 1 sequence 112180 ID 0xa8b77b1e dest 1:
Sat Mar 31 04:26:19 2018
Thread 1 advanced to log sequence 112182 (LGWR switch)
Current log# 4 seq# 112182 mem# 0:
+DATA/orcl/onlinelog/group_4.257.913465361
Current log# 4 seq# 112182 mem# 1:
+FLASH2/orcl/onlinelog/group_4.260.913465371
Sat Mar 31 04:26:27 2018
Archived Log entry 112181 added for thread 1 sequence 112181 ID 0xa8b77b1e dest 1:
Sat Mar 31 05:00:04 2018
ALTER SYSTEM ARCHIVE LOG
Sat Mar 31 05:00:04 2018
Thread 1 advanced to log sequence 112183 (LGWR switch)
Current log# 5 seq# 112183 mem# 0:
+DATA/orcl/onlinelog/group_5.267.913465111
Current log# 5 seq# 112183 mem# 1:
+FLASH2/orcl/onlinelog/group_5.261.913465121

How to Check this from database end ?

Method 1 :

Check using AWR :

In AWR Check in Undo Statistics

1.PNG

You can see the OOS -1 (Out of Space Count) . You can confirm here that the Undo run out of space2

You Can see the error occurred between 4:25-4:35 ,

The Undo Segment stats happens every ten minutes .

The Last column tells us about the stolen blocks from the expired and unexpired blocks.

How does the Undotablespace utilize expired and unexpired block in case the undo tablespace is not having guaranteed retention

select retention from dba_tablespaces where tablespace_name=’UNDO1BS1′;
RETENTION
———–
NOGUARANTEE

Below is how the algorithm works

When we execute an operations that needs to allocate undo space:

  1. Allocate an extent in an undo segment which has no active transaction. Why in other segment? Because Oracle tries to distribute transactions over all undo segments.
  2. If no undo segment was found then oracle tries to online an off-line undo segment and use it to assign the new extent..
  3. If no undo segments was possible to online, then Oracle creates a new undo segment and use it.
  4. If the free space doesn’t permit creation of undo segment, then Oracle tries to reuse an expired extent from the current undo segments.
  5. If failed, Oracle tries to reuse an expired extent from another undo segment.
  6. If failed, Oracle tries to autoextend a datafile (if autoextensible=yes)
  7. If failed, Oracle tries to reuse an unexpired extent from the current undo segment.
  8. If failed, Oracle tries to reuse an unexpired extent from another undo segment.
  9. If failed, then the operation will fail.

For a running transaction associated with undo segment/ extent and it needs more undo space:

  1. If the current extent has more free blocks then use the next free block that is all ready allocated to the extent.
  2. If the current extent does not have free blocks and if the next extent of the segment has expired then wrap in the the next extent and return the first block.
  3. If the next extent has not expired then get space from the UNDO tablespace. If a free extent is available then allocate it to the undo segment.
  4. If there is no free extent available then reuse from an offline undo segment. Deallocate the extent from the offline undo segment and add it to the current undo segment.
  5. Steal from online undo segment. Deallocate the extent from the online undo segment and add it to the current undo segment.
    From AWR it is very evident that the Undo tablespace was very busy and is trying to utilize the expired and unexpired blocks ,

Method 2:

Lets get more info from the DBA Views

SQL> sho parameter undo

NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
undo_retention integer 86400

3

At 4:25 We hit 1 in NOSPACEERRCNT (No Space Error Count)

Also you can see the the expired blocks coming down from 17625 to 6224 in matter of 3 hours ,The first and column lets you know how many block were taken from the Unexpired and expired blocks

The Query used to fetch this data :

select to_char(begin_time,’dd:mon:yyyy hh24:mi:ss’) “Begin_Time”,
UNXPSTEALCNT “#UnexpiredBlksTaken”,
EXPSTEALCNT “#ExpiredBlksTaken”,
NOSPACEERRCNT “SpaceRequests”,
ACTIVEBLKS “Active_blocks”,
NOSPACEERRCNT,
UNEXPIREDBLKS,
EXPIREDBLKS
from DBA_HIST_UNDOSTAT where SNAP_ID between 78728 and 78790 order by 1;

Undo Retention and nature of the batch jobs is what the determines the read consistency and the storage planning for UNDO Tablespaces ,Here we have 24 hours for Undo_retention

Advertisements

Understanding Usable_MB in Oracle ASM

USABLE_FILE_MB indicates the amount of free space, adjusted for mirroring, that is available for new files to restore redundancy after a disk failure

External Redundancy : Total_MB 1000 mb USABLE MB 1000 MB

NORMAL REDUNDANCY : TOTAL_MB :1000 MB USABLE_MB : (free_mb – required_mirror_free_mb) / 2

HIGH REDUNDANCY : TOTAL MB :1000 MB USABLE_MB: (free_mb – required_mirror_free_mb) / 3

Looks simple but there are factors which influence the required_mirror_free_mb

Let see the below setup now

1

The Test Diskgroup has two failure groups (FG1 and FG2) each contains 3 disk with capacity of 5GB each

2

USABLE_MB=(free_mb – required_mirror_free_mb) / 2

Now the Required_mirror_free_mb=5119MB

Thats the size of a single asm disk( Disk with max size is considered), In Normal redundancy we can tolerate single disk failure

USABLE_MB=(30600-5119)/2 =12740

Now lets see a different setup :

Lets create a Diskgroup with 3 failgroups (FG1,FG2,F3) with 2 disks each 5 GB

3.PNG

Now the surprise here is the Required_Mirror_MB has been changed from 5119 to 10238,

The reason being that even we loose a the third failure group FG3 we still can maintain a normal redundancy with 2 existing failure groups, Hence the required_mirror_free_mb will change from a single disk to a single failure group

Note that 3 FG in normal redundancy is different from 3FG with high redundancy

4

You can see the REQUIRED_MIRROR_FREE_MB is same for the both config(NORMAL 3FG and HIGH 3FG) but the USABLE_FILE_MB=6770

USABLE_MB: (free_mb – required_mirror_free_mb) / 3

USABLE_MB:(30549-10238)/3=6770

Now lets try High redundancy with 4 FG

5.PNG

Now the Required_mirror_mb has been increased from 10238 to 15357
The current setup can tolerate FG4 failure and can still operate in High Redundancy with default 2 disk failure , so that makes 3 disks total in this setup

5119*3=15357

USABLE_MB: (30549 – 15357) / 3=5064 MB

Oracle database installation in Azure

This Post is about exploring Oracle grid and database service from Azure.

Azure is a Cloud Platform from Microsoft which provides SQL Database as software as a service (saas) and iaas .

In saas you can directly create a SQL database from AZURE Portal by selecting the type of Server, You will have access only to SQL Database but not the server hosting it

While they provide Oracle database as infrastructure as a service (iaas)

You can sign up in azure portal with 200$ credit free for a month

First step would be creating a Virtual box with Oracle software ,

Click Add (+ Buton) and search for Oracle enterprise edition 12.1.0.2

1

Step 2 :

Provide User Login Details with Password followed by resource group and Location

2.png

Step 3:

Choose your Machine Configuration , Dont worry this will covered with your 200$ Credit

3.PNG

Step 4:

You can configure High Availability using availability set in Azure (Ignore this )

4.png

Step 5 :

Hit Create after checking your summary

5

This will take some time to deploy, Azure is little slow compared to AWS

Once the server is created ,You can get the IP details and login

You can see only the Oracle database software is installed while the GRID is not available

6

So Lets start by installing the required rpm for Oracle ASM followed by the GRID Installation

7

8.png

10.png

11.png

Check whether all rpm related to ASM are installed

12.png

Our Next step is to add Disk and label them using oracleasm lib

I have added only 2 disk since storage is charged in azure ūüėČ

Go to the Setting -> Disk and Add disks

13

Hit Add data Disk and it will prompt you the Disk details

14.png

15.png

Add one more Disk

16

Please note that i have reconfigured the disk from 3GB to 10 GB , you will see in the coming snap as 10 GB

17

You can see the disk are recognised from OS

18.png

19.png

[root@ORACLEDB ~]# fdisk /dev/sdc
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x955f74a7.
Changes will remain in memory only, until you decide to write them.

After that, of course, the previous content won’t be recoverable.
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
The device presents a logical sector size that is smaller than
the physical sector size. Aligning to a physical sector (or optimal
I/O) size boundary is recommended, or performance may be impacted.
WARNING: DOS-compatible mode is deprecated. It’s strongly recommended to
switch off the mode (command ‘c’) and change display units to
sectors (command ‘u’).

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-1305, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-1305, default 1305):
Using default value 1305

Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.

Similarly do for the other disk

20.png

Now configure ASM LIB :

[root@ORACLEDB init.d]# oracleasm configure

ORACLEASM_ENABLED=false

ORACLEASM_UID=

ORACLEASM_GID=

ORACLEASM_SCANBOOT=true

ORACLEASM_SCANORDER=””

ORACLEASM_SCANEXCLUDE=””

ORACLEASM_SCAN_DIRECTORIES=””

ORACLEASM_USE_LOGICAL_BLOCK_SIZE=”false”

21.png

22

23.png

I noticed even XCLOCK is missing,Run below command to install it

yum install xorg-x11-apps

Now Change the ownership of the disks from root to oracle

24.png

Cross check it

25.png

As per Azure Documentation Increase the swap size from 2GB to 8GB

26

27.png

Now restart the machine for swap size to reflect

28.png

Now download the Grid software and extract in the VM machine

Now we are all ready to install Grid Software

Install XMING/EXCEED and enable X11 forwarding in your Putty for GUI

29

30

31

32

33

34

35

36

37

38

39

40

41

42

Since the Database software is already installed , we need to create db via DBCA

43

44

45

46

47

48

49

50

51

Ignore the Filesystem option for the datafile storage ,I have changed again to DATA

52

53

54

55

56

You can see both ASM and Database are up now ūüôā

ONLINE TABLE RE-ORG USING DBMS_REDEFINITION

We are going to perform an online table re-org using dbms_redefinition package

Below table is an ideal candidate for an online re-org

Checking Top Fragmented Table:

OWNER           TABLE_NAME                     Fragmented size      Actual size          ROUND((BLOCKS*8),2)-ROUND ((NUM_ROWS*AVG_RO reclaimable space %

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

SCOTT       TEST          28249008kb           18250689.06kb        9998318.94kb                                         25.3935223

The Dependencies on the table can be found from dba_dependencies

Dependencies on the Table:

OWNER      NAME                  TYPE               REFERENCED_OWNE REFERENCED_NAME           REFERENCED_TYPE    REFERENCED_LINK_NAME DEPE
---------- --------------------- ------------------ --------------- ------------------------- ------------------ -------------------- ----
HR          TEST                  SYNONYM            SCOTT           TEST                      TABLE               HARD

Current Size:

The Current size of the table is around 27 GB

SQL> select sum(bytes/1024/1024/1024) GB from dba_segments where segment_name='TEST';
        GB
----------
26.9404297

Lets Check if the Table can be redefined online ?

SQL> SET SERVEROUTPUT ON
begin
DBMS_REDEFINITION.CAN_REDEF_TABLE(
uname=>'SCOTT',
tname=>'TEST');
end;
/SQL>   2    3    4    5    6
begin
*
ERROR at line 1:
ORA-12089: cannot online redefine table "SCOTT"."TEST" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 143
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1635
ORA-06512: at line 2

The Reason the table failed is because the Online redefinition happens either by using a primary key or using ROWID,
The Default is the Primary Key, Hence re-run the statement with Rowid Option

SQL> SET SERVEROUTPUT ON
begin
DBMS_REDEFINITION.CAN_REDEF_TABLE(
uname=>'SCOTT',
tname=>'TEST',
options_flag=>DBMS_REDEFINITION.cons_use_rowid);
end;
/
SQL>   2    3    4    5    6    7
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00

Create a backup table :

SQL> alter session enable parallel dml;

Session altered.

SQL> create table /*+ PARALLEL(4) */ SCOTT.TEST1 AS SELECT * FROM /*+ PARALLEL(4) */ SCOTT.TEST ;

Table created.

Starting redefinition :

SET SERVEROUTPUT ON
 begin
 DBMS_REDEFINITION.start_redef_table( 
 uname=>'SCOTT',
 orig_table=>'TEST',
 int_table=>'TEST1',
 options_flag=>DBMS_REDEFINITION.cons_use_rowid);
 end;
 /

You Can see exclusive lock on the new table but not the old table

U_NAME     OBJ_OWNER  OBJECT_NAME               OBJECT_TYPE         OSUSER                         STATUS          MODE_HELD
---------- ---------- ------------------------- ------------------- ------------------------------ --------------- ---------------
SYS        SYS        SNAP$                     TABLE              oracle                         Global          Row-X (SX)
SYS        SCOTT      TEST1                     TABLE              oracle                         Global          Exclusive
SYS        SCOTT      TEST1                     TABLE              oracle                         Global          Exclusive

U can check the progress from the long ops and check for the progress

Longops output :

 1500 Table Scan           03:23:45  2018-FEB-26          SCOTT.TEST    3531126    3531126 Blocks             160 Table Scan:  SCOTT.TEST

                                                                                                                           : 3531

                                                                                                                                 126 out of 3531126 Blocks

                                                                                                                                  done




 1500 Table Scan           03:57:19  2018-FEB-26          SCOTT.TEST    2299060    3531126 Blocks             420 Table Scan:  SCOTT.TEST

                                                                                                                           : 2299

                                                                                                                                 060 out of 3531126 Blocks

                                                                                                                                  done

You can synchronize the table once again to make sure it’s in sync

SQL>
SET SERVEROUTPUT ON
SET SERVEROUTPUT ON
begin
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
uname=>'SCOTT',
orig_table=>'TEST',
int_table=>'TEST1');
end;
/

SQL> SQL> SQL>   2    3    4    5    6    7

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05

Now copy the dependable objects of the table, we have only one synonym here

SQL>  SET SERVEROUTPUT ON
SQL>  DECLARE
  2   l_num_errors PLS_INTEGER;
  3   begin
  4   DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
  5   uname=>'SCOTT',
  6   orig_table=>'TEST',
  7   int_table=>'TEST1',
  8   copy_indexes => DBMS_REDEFINITION.cons_orig_params,
  9   copy_triggers => TRUE,
 10   copy_constraints => TRUE,
 11   copy_privileges => TRUE,
 12   ignore_errors => FALSE,
 13   num_errors => l_num_errors);
 14   end;
 15   /
PL/SQL procedure successfully completed.

Before the final step you can see the size of both tables

SQL> SELECT SEGMENT_NAME,SUM(BYTES/1024/1024/1024)gb FROM DBA_SEGMENTS WHERE SEGMENT_NAME LIKE '%TEST%' GROUP BY SEGMENT_NAME;
SEGMENT_NAME                        GB
--------------------------------- ----------
TEST                              26.9404297
TEST1                             20.953125

Now you can complete the activity by finishing the redef, You will have an exclusive lock on your main table only for few seconds

SQL>  SET SERVEROUTPUT ON
begin
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
uname=>'SCOTT',
orig_table=>'TEST',
int_table=>'TEST1');
end;
/ SQL>   2

You can see the size of the table is swapped now

SEGMENT_NAME      GB
---------------- ----------
TEST              20.953125
TEST1            26.9404297

Check for any unused columns on the table

select * from dba_unused_col_tabs where TABLE_NAME=’TEST’
OWNER                          TABLE_NAME                          COUNT
------------------------------ ------------------------------ ----------
SCOTT                           TEST                                1

SQL> ALTER TABLE SCOTT.TEST drop unused columns;

Table altered.

Check if the object is valid

OWNER            OBJECT_NAME               OBJECT_TYPE         STATUS
---------------- ------------------------- ------------------- -------
HR               TEST                     SYNONYM             VALID
SCOTT            TEST                     TABLE               VALID

Changing Database Unique Name in Standby

I have a dataguard setup where the primary unique name is orcl and standby is orcl_std

Its not advisable to name db_unique name with standby or std suffix as it would be confusing after switchover

So i will be changing the db_unique_name from ORCL_STD to ORCL_IND in Standby
Log_archive_dest_2 info in primary 

NAME                                 TYPE        VALUE

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

log_archive_dest_2                   string      service="orcl_std", LGWR ASYNC

                                                  NOAFFIRM delay=0 optional com

                                                 pression=disable max_failure=0

                                                  max_connections=1 reopen=300

                                                 db_unique_name="orcl_std" net_

                                                 timeout=30, valid_for=(all_log

                                                 files,primary_role)
   DEST_ID DEST_NAME            STATUS    ARCHIVED_SEQ# DB_UNIQUE_NAME  DATABASE_MODE   RECOVERY_MODE           GAP_STATUS      ERROR

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

         1 LOG_ARCHIVE_DEST_1   VALID               98 NONE            OPEN            IDLE

         2 LOG_ARCHIVE_DEST_2   VALID               98 orcl_std        OPEN_READ-ONLY  MANAGED REAL TIME APPLY NO GAP

         3 LOG_ARCHIVE_DEST_3   INACTIVE            98 NONE            UNKNOWN         IDLE

Lets check the broker configuration :
[oracle@localhost ~]$ dgmgrl /DGMGRL for Linux: Version 11.2.0.1.0 – Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type “help” for information.Connected.

DGMGRL> show configuration
Configuration - my_dg_config
  Protection Mode: MaxPerformance  
Databases: 
orcl     - Primary database    
orcl_std - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:SUCCESS

 

DGMGRL> show database orcl_std
Database - orcl_std
Role:            PHYSICAL STANDBY  
Intended State:  APPLY-ON  
Transport Lag:   0 seconds 
Apply Lag:       0 seconds  
Real Time Query: ON  Instance(s):    orcl_std
Database Status:SUCCESS

 

DGMGRL> disable database orcl_std;
 Disabled.
DGMGRL> show configuration
 Configuration - my_dg_config
 Protection Mode: MaxPerformance
 Databases:
 orcl     - Primary database
 orcl_std - Physical standby database (disabled)
 Fast-Start Failover: DISABLED
DGMGRL> show database orcl_std
Database - orcl_std
  Role:            PHYSICAL STANDBY
   Intended State:  APPLY-ON
   Transport Lag:   (unknown)
   Apply Lag:       (unknown)
   Real Time Query: OFF
   Instance(s):orcl_std
Database Status:DISABLED

Connect to standby :

SQL> alter system set db_unique_name=’orcl_ind’ scope=spfile;
System altered.

The Parameter change needs a reboot,

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  523108352 bytes
Fixed Size                  1337632 bytes
Variable Size             411043552 bytes
Database Buffers          104857600 bytes
Redo Buffers                5869568 bytes
Database mounted.
Database opened.

SQL> select name,db_unique_name,open_mode,database_role from v$database;
NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
ORCL      orcl_ind                       READ ONLY            PHYSICAL STANDBY

 

Note that starting an MRP will not work until the changes are made in Broker as well.

You can also see a new directory is formed in the OS level as the db_unique_name is changed

/new_orahome/oracle/app/oracle/diag/rdbms/orcl_ind/orcl_std/trace

DGMGRL> show configuration
 Configuration - my_dg_config
 Protection Mode: MaxPerformance
 Databases:
 orcl     - Primary database
 orcl_std - Physical standby database (disabled)
 Fast-Start Failover: DISABLED
 Configuration Status:SUCCESS

The Parameter needs to be changed in Broker to update the .dat files
The DAT files are the parameter files to broker
DGMGRL> edit database 'orcl_std' rename to 'orcl_ind';
 Succeeded.

DGMGRL> enable database orcl_ind;
 Enabled.

DGMGRL> show configuration
 Configuration - my_dg_config
 Protection Mode: MaxPerformance
 Databases:
 orcl     - Primary database
 orcl_ind - Physical standby database
 Fast-Start Failover: DISABLED
 Configuration Status:
 SUCCESS
SQL> select name,db_unique_name,open_mode,database_role from v$database;
 NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
 --------- ------------------------------ -------------------- ----------------
 ORCL      orcl_ind                       READ ONLY WITH APPLY PHYSICAL STANDBY

Also the Broker makes changes in log_archive_dest_2 in primary

NAME                                 TYPE        VALUE

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

log_archive_dest_2                   string      service="orcl_std", LGWR ASYNC

                                                  NOAFFIRM delay=0 optional com

                                                 pression=disable max_failure=0

                                                  max_connections=1 reopen=300

                                                 db_unique_name="orcl_ind" net_

                                                 timeout=30, valid_for=(all_log

                                                 files,primary_role)

 

Perform log switches in primary and check the config from sqlplus prompt

 

   DEST_ID DEST_NAME            STATUS    ARCHIVED_SEQ# DB_UNIQUE_NAME  DATABASE_MODE   RECOVERY_MODE           GAP_STATUS      ERROR

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

         1 LOG_ARCHIVE_DEST_1   VALID               104 NONE            OPEN            IDLE

         2 LOG_ARCHIVE_DEST_2   VALID               104 orcl_ind        OPEN_READ-ONLY  MANAGED REAL TIME APPLY NO GAP

         3 LOG_ARCHIVE_DEST_3   INACTIVE            104 NONE            UNKNOWN         IDLE

Child Cursor/Bind Peeking/Roll_Invalid_Mismatch

A Sql id has 4 Sql Plan Hash value ,The plan hash value is created/chosen for various factors ,

SQL_ID PLAN_HASH_VALUE EXECUTIONS CROWS CPU_MINS ELA_MINS

————- ————— ———- ———- ———- ———-

70mthkfd29bz8 750596882 2750 297 0 0

70mthkfd29bz8 2607216684 33 22 0 0

70mthkfd29bz8 776891890 7441 897 3 17

70mthkfd29bz8 3571699922 8651 1495 4 18

Oracle Finally choose the best plan hash value which has low cost and settles for it.In the above case it has to be 750596882 since the run time is much lesser

Below is the AWR report for the last 10 days , You can see the plan hash value changes very frequently.

     SNAP_ID    NODE   BEGIN_INTERVAL_TIME      SQL_ID               PLAN_HASH_VALUE   EXECS   AVG_ETIME     AVG_LIO
     -------    ------ ---------------------    --------------     -----------------   ------  ------------ --------- 
     34887      1 12-FEB-18 05.00.46.856 AM      70mthkfd29bz8       750596882            2         .000            5.0
     34887      1 12-FEB-18 05.00.46.856 AM      70mthkfd29bz8      3571699922           11        9.521       10,901.7
     34889      1 12-FEB-18 07.00.55.783 AM      70mthkfd29bz8                           18        5.426        8,611.1
     34981      1 16-FEB-18 03.00.24.234 AM      70mthkfd29bz8       776891890           13       12.489       11,410.5
     34982      1 16-FEB-18 04.00.33.333 AM      70mthkfd29bz8                           21        2.046        8,381.9
     35049      1 18-FEB-18 11.00.51.517 PM      70mthkfd29bz8       750596882           43         .001           15.2
     35049      1 18-FEB-18 11.00.51.517 PM      70mthkfd29bz8      3571699922           10        6.780        8,257.3
     35094      1 20-FEB-18 08.00.38.868 PM      70mthkfd29bz8       750596882           83         .001           17.0
     35094      1 20-FEB-18 08.00.38.868 PM      70mthkfd29bz8       776891890          256         .106       22,327.3
     35095      1 20-FEB-18 09.00.44.717 PM      70mthkfd29bz8                          300         .066       22,311.7
     35096      1 20-FEB-18 10.00.50.146 PM      70mthkfd29bz8                          215         .072       21,793.5
     35097      1 20-FEB-18 11.00.55.430 PM      70mthkfd29bz8                          168         .060       21,190.6
     35132      1 22-FEB-18 10.00.50.797 AM      70mthkfd29bz8       750596882          227         .001           11.6
     35132      1 22-FEB-18 10.00.50.797 AM      70mthkfd29bz8      3571699922           78        1.760       24,842.5
     35133      1 22-FEB-18 11.00.58.429 AM      70mthkfd29bz8                          322         .441       23,947.

Continue reading “Child Cursor/Bind Peeking/Roll_Invalid_Mismatch”

I/O load on ASM DISKGROUP

Below is one of the method how you start troubleshooting I/O load balancing in ASM.

The Below query gives how good the data is balanced in any given diskgroup  (o/p should be near to 100%)

Please note that below example is from external redundancy , So there are no failure group available

SELECT dg.group_number “GROUP#”,

dg.name,

DECODE (total_dg.total_io, 0, 100, 100 * (DECODE (SIGN (1 – df.sum_io / total_dg.total_io), -1, 0, (1 – df.sum_io / total_dg.total_io)))) “IO_BALANCED”

FROM (SELECT d.group_number group_number,

SUM (ABS ((d.reads + d.writes) – tot.avg_io)) sum_io

FROM v$asm_disk_stat d,

(SELECT group_number,

SUM (reads) + SUM (writes),

DECODE (COUNT (*), 0, 0, (SUM (reads) + SUM (writes)) / COUNT (*)) avg_io

FROM v$asm_disk_stat

WHERE header_status = ‘MEMBER’

GROUP BY group_number) tot

WHERE header_status = ‘MEMBER’ AND tot.group_number = d.group_number

GROUP BY d.group_number) df,

(SELECT group_number,

SUM (reads) + SUM (writes) total_io

FROM v$asm_disk_stat

WHERE header_status = ‘MEMBER’

GROUP BY group_number) total_dg,

V$ASM_DISKGROUP dg

WHERE df.group_number = total_dg.group_number

AND df.group_number = dg.group_number

The Output should be close to 100  , If its less than 80 check if all the disk on the diskgroup are of same size

CASE STUDY :

GROUP# NAME                           IO_BALANCED

———- —————————— ———–

1 DATA                            60.7173153

2 FLASH                           46.7913472

Below table provides you the size of each disk and the read and write on each disk

You can see that the ASM_009,010,011,012 are 200 GB instead of 99 or 100GB ,

This makes the data balancing tough in the Diskgroup

ASM_006,007,008 has more read compared to the other disk.

Group Disk Header Mode State Added to Diskgroup Total_GB Free_GB Disk_Name Path PerRead PerWrite
1 0 MEMBER ONLINE NORMAL 5-Apr-15 99.996094 0.234375 ASM_001 ORCL:ASM_001 0.008 0.001
1 1 MEMBER ONLINE NORMAL 6-May-15 99.996094 0.226563 ASM_002 ORCL:ASM_002 0.008 0.002
1 2 MEMBER ONLINE NORMAL 25-May-15 99.996094 0.222656 ASM_003 ORCL:ASM_003 0.007 0.002
1 3 MEMBER ONLINE NORMAL 28-May-15 100 0.222656 ASM_006 ORCL:ASM_006 0.013 0.002
1 4 MEMBER ONLINE NORMAL 28-May-15 100 0.222656 ASM_007 ORCL:ASM_007 0.012 0.004
1 5 MEMBER ONLINE NORMAL 28-May-15 99 0 ASM_008 ORCL:ASM_008 0.012 0.003
1 6 MEMBER ONLINE NORMAL 30-May-15 199.99609 0.476563 ASM_009 ORCL:ASM_009 0.006 0.002
1 7 MEMBER ONLINE NORMAL 30-May-15 199.99609 0.484375 ASM_010 ORCL:ASM_010 0.006 0.002
1 8 MEMBER ONLINE NORMAL 30-May-15 199.99609 0.488281 ASM_011 ORCL:ASM_011 0.007 0.002
1 9 MEMBER ONLINE NORMAL 6-Jul-16 200.00391 0.507813 ASM_012 ORCL:ASM_012 0.007 0.002
2 0 MEMBER ONLINE NORMAL 5-Apr-15 99.996094 48.89453 ASM_005 ORCL:ASM_005 0.002 0.002
2 1 MEMBER ONLINE NORMAL 25-May-15 99.996094 48.91016 ASM_004 ORCL:ASM_004 0.002 0.003