Home > Oracle, Performance > buffer busy waits

buffer busy waits


Ordering data in a table is important to avoid bottlenecks.  In the previous post I had explained how to avoid ITL waits by ordering the data.  This demo will shows how we can remove “buffer busy waits” by ordering the data.  I ran the below 5 similar sql scripts on a table ABC concurrently.


$ more 5.sql

update abc set owner='ABC' where mod(object_id,10)=5;

commit;

exit

$ more 4.sql

update abc set owner='ABC' where mod(object_id,10)=4;

commit;

exit

$ more 3.sql

update abc set owner='ABC' where mod(object_id,10)=3;

commit;

exit

$ more 2.sql

update abc set owner='ABC' where mod(object_id,10)=2;

commit;

exit

$ more 1.sql

update abc set owner='ABC' where mod(object_id,10)=1;

commit;

exit

$ more 0.sql

update abc set owner='ABC' where mod(object_id,10)=0;

commit;

exit

nohup sqlplus apple/apple @0.sql &

nohup sqlplus apple/apple @1.sql &

nohup sqlplus apple/apple @2.sql &

nohup sqlplus apple/apple @3.sql &

nohup sqlplus apple/apple @4.sql &

nohup sqlplus apple/apple @5.sql &

All the 6 UPDATE sessions were almost hanging on a mix of “buffer busy waits” and “free buffer waits”.   Being a small buffer cache and considering the the size of the server, I was not worried about the “free buffer waits”, but “buffer busy waits” which I do not like to see – CBC latch contention due to 6 parallel updates on the ABC table.


SQL> set linesize 160

SQL col event for a40

SQL> col username for a10

SQL> col OSUSER for a12

SQL> select v.seq#,v.sid,username,v.event,program

2  from v$session_wait v, v$session s  where  s.sid=v.sid and username='APPLE'

3  order by last_call_et;

SEQ#        SID USERNAME   EVENT                                    PROGRAM

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

552        115 APPLE      free buffer waits                        sqlplus@bandevsol01 (TNS V1-V3)

563         81 APPLE      buffer busy waits                        sqlplus@bandevsol01 (TNS V1-V3)

600         92 APPLE      buffer busy waits                        sqlplus@bandevsol01 (TNS V1-V3)

432         90 APPLE      buffer busy waits                        sqlplus@bandevsol01 (TNS V1-V3)

414         80 APPLE      free buffer waits                        sqlplus@bandevsol01 (TNS V1-V3)

551         88 APPLE      buffer busy waits                        sqlplus@bandevsol01 (TNS V1-V3)

6 rows selected.

Looking at the amount of data I was updating, creating an index was a good idea.


SQL> select count(*) from abc where mod(object_id,10)=0;

COUNT(*)

----------

37174

SQL> select count(*) from abc where mod(object_id,10)=1;

COUNT(*)

----------

37175

SQL>  select count(*) from abc where mod(object_id,10)=2;

COUNT(*)

----------

37175

SQL>  select count(*) from abc where mod(object_id,10)=3;

COUNT(*)

----------

37175

SQL> select count(*) from abc where mod(object_id,10)=4;

COUNT(*)

----------

37175

SQL> select count(*) from abc where mod(object_id,10)=5;

COUNT(*)

----------

37174

SQL> select count(*) from  abc;

COUNT(*)

----------

371744

In all conditions, I was updating only 10% of the table data, so a function based index on mod(object_id,10)  should restrict my UPDTAE access only limited rows, so that I will not hammer the CBC, will minimize  CBC latch contention.


SQL> create index i_objid on abc(mod(object_id,10));

Index created.

SQL> exec dbms_stats.gather_table_stats('APPLE','ABC',cascade=>true);

PL/SQL procedure successfully completed.

To my surprise, Oracle did not pick up the index, rather opted FTS for the updates.  Usually developer community rush to use hints to force the index.  But I decide to see why the index is not getting used.  Using a select statement, compared the blocks gets for both with and without index.


Execution Plan

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

Plan hash value: 3116099409

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      | 37174 |  3920K|  1461   (3)| 00:00:18 |

|*  1 |  TABLE ACCESS FULL| ABC  | 37174 |  3920K|  1461   (3)| 00:00:18 |

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

 

Predicate Information (identified by operation id):

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

1 - filter(MOD("OBJECT_ID",10)=1)

 

Statistics

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

0  recursive calls

0  db block gets

7727  consistent gets

5276  physical reads

0  redo size

2829186  bytes sent via SQL*Net to client

27781  bytes received via SQL*Net from client

2480  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

37175  rows processed

 

SQL> select /*+ index (a ,I_OBJID) */ * from abc a where mod(object_id,10)=1;

37175 rows selected.

Execution Plan

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

Plan hash value: 2443312682

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

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |         | 37174 |  3920K|  4811   (1)| 00:00:58 |

|   1 |  TABLE ACCESS BY INDEX ROWID| ABC     | 37174 |  3920K|  4811   (1)| 00:00:58 |

|*  2 |   INDEX RANGE SCAN          | I_OBJID | 37174 |       |    76   (2)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access(MOD("OBJECT_ID",10)=1)

Statistics

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

1  recursive calls

0  db block gets

9354  consistent gets

3695  physical reads

0  redo size

2829186  bytes sent via SQL*Net to client

27781  bytes received via SQL*Net from client

2480  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

37175  rows processed

So, that is the reason why oracle is not using the index – the data distribution (clustering factor) is not favoring the index usage.  This is where we need to  worry about the developers who use INDEX hints to force index access.  Data is always dynamic, so keep your SQLs also dynamic – let CBO build an execution plan based on the current environment.  By keeping INDEX hint, you are telling Oracle to use a single standard plan which may not be suitable for the same data in different distributions.    Looking at the CLUSTERING_FACTOR columns explained clearly why oracle is choosing FTS, skipping the available index.


SQL> select index_name,CLUSTERING_FACTOR,table_name

from user_indexes where table_name='ABC';

INDEX_NAME                     CLUSTERING_FACTOR TABLE_NAME

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

I_OBJID                                    47277 ABC

SQL> SELECT BLOCKS,NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME='ABC';

BLOCKS   NUM_ROWS

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

5276     371744

So the clustering factor is 47,277 while the total number of blocks in the table is 5,276.  Oracle will favor index access only if the clustering factor is close to the number of BLOCKS in the table which is not the case here.  Forcing Oracle for an index access will drive up cost, execution time, consistent gets due to the inefficient plan.   So I decided to order the data by rebuilding the table using mod(object_id,10) order clause.  Created a backup table and INSERT back to ABC with ORDER BY  mod(object_id,10).

 SQL> insert into abc select * from abc_bak order  by  mod(object_id,10);

371744 rows created.

SQL> exec dbms_stats.gather_table_stats('APPLE','ABC',cascade=>true);

PL/SQL procedure successfully completed.

SQL> select index_name,CLUSTERING_FACTOR,table_name from user_indexes where table_name='ABC';

INDEX_NAME                     CLUSTERING_FACTOR TABLE_NAME

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

I_OBJID                                     5269 ABC

SQL> SELECT BLOCKS,NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME='ABC';

BLOCKS   NUM_ROWS

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

5272     371744

Now the CLUSTERING_FACTOR come down 5,269 and the number of blocks in the table is 5,272.  My UPDATE statements should go well now using the index.   CLUSTERING_FACTOR also plays a vital role in EXADATA for the effective use of Storage Indexes (SI).  Storage Indexes are the in memory objects built for a table and there were no indexes present.


Execution Plan

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

Plan hash value: 3453688465

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

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | UPDATE STATEMENT             |         | 34165 |   734K|   556   (1)| 00:00:07 |

|   1 |  UPDATE                      | ABC     |       |       |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| ABC     | 34165 |   734K|   556   (1)| 00:00:07 |
<div>

|*  3 |    INDEX RANGE SCAN          | I_OBJID | 34165 |       |    70   (2)| 00:00:01 |

This index will help to reduce the CBC latch contention and “buffer busy waits” will be a history.  A well sized buffer cache in a good system will reduce the  “free buffer waits” as well.


SQL> /

SEQ#        SID USERNAME   EVENT                                    PROGRAM

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

214         77 APPLE      db file sequential read                  sqlplus@bandevsol01 (TNS V1-V3)

242         72 APPLE      db file sequential read                  sqlplus@bandevsol01 (TNS V1-V3)

208         85 APPLE      db file sequential read                  sqlplus@bandevsol01 (TNS V1-V3)

895         83 APPLE      db file sequential read                  sqlplus@bandevsol01 (TNS V1-V3)

162         71 APPLE      db file sequential read                  sqlplus@bandevsol01 (TNS V1-V3)

245         75 APPLE      db file sequential read                  sqlplus@bandevsol01 (TNS V1-V3)

&nbsp;

6 rows selected.

&nbsp;

SQL> /

&nbsp;

SEQ#        SID USERNAME   EVENT                                    PROGRAM

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

499         77 APPLE      free buffer waits                        sqlplus@bandevsol01 (TNS V1-V3)

523         72 APPLE      free buffer waits                        sqlplus@bandevsol01 (TNS V1-V3)

501         85 APPLE      free buffer waits                        sqlplus@bandevsol01 (TNS V1-V3)

1175         83 APPLE      free buffer waits                        sqlplus@bandevsol01 (TNS V1-V3)

445         71 APPLE      free buffer waits                        sqlplus@bandevsol01 (TNS V1-V3)

529         75 APPLE      free buffer waits                        sqlplus@bandevsol01 (TNS V1-V3)

6 rows selected.

So, data distribution is an important factor, and well neglected almost in all environments.  A well physically organised table can save you from many performance issues – remember to order based on a more frequently used column(s).    Also, developers think twice before you put INDEX hint, you are not really increasing the performance, rather it is a liability.

About these ads
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: