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”

Advertisements

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