What is "Buffer Busy Wait " - - Tuning

“When several concurrent sessions will read the same blocks/same table or same index block.”

Definition : When two or more session issue the same query/related query (that access the same database blocks), the few sessions will read the data from database buffer cache (based in inittrans and maxtrans in block level) while other sessions are in wait. In that case , Buffer Busy Wait – Event occurs.

How to find Buffer Busy Waits ?

Using the AWR  report top-5 wait-event shows the read by other session or Buffer busy wait event.

Or Using v$segment_statistics or v$system_event also, we can see the buffer busy wait event.

What are Hot Objects/Blocks ?

When Number of concurrent session’s access single block in an object then they are called as hot object.

How to find these Hot Objects ?

Using AWR report “Segment statistics” section shows the HOT objects list.

Or also using below query, we find the hot objects. :

SELECT relative_fno, owner, segment_name, segment_type

FROM dba_extents

WHERE file_id = &file

AND &block BETWEEN block_id AND block_id + blocks - 1;

How we reduce the buffer busy waits?

Method 1:

Increasing INITRANS value method:

First we should know how concurrent sessions accessing a single block in an object?

Each database-block having 3 layers.

1. Cache layer

2. Transaction layer

3. Data layer

Transaction layer is playing vital role for block contention.

Each block will have ITL (INTERESTED TRANSACTION LIST) slots. This ITL slots is required for any sessions that’s need to modify a block in an object.

INITRANS value for table having segment 1 & INITRANS for index segment having 2.

MAXTRANS value default is 255.

If there is no free ITL slot in a blocks, then transaction will waiting for serially for a free ITL slot.

By increasing INITRANS value to avoid the serial transaction waiting. Concurrently number of session will perform the DML operation in single block.

Each ITL requires approximately 23 bytes in the block header.

Method 2 : Increasing PCTFREE method:

Suppose a single 8 KB block contains 1000 rows. We reducing the rows in a block can easily reduce the buffer busy wait.

PCTFREE space is used for future updates only. We have an 8 KB data block. Default PCTFREE value is 10%. If we increased the PCTFREE value is 20% automatically number of rows inserted in a block is automatically reduced.

Method 3:   Reducing database block size method:

It’s similar to PCTFREE method. Suppose a single 8 KB block contains 1000 rows. Using db multiblock size future we used 4 KB data block. Now 1000 rows should be stored two 4 KB blocks.

Our goal is to reduce the number of records stored in a block.

Tune the inefficient queries:

Reduce the number of blocks accessing for an objects in buffer cache. By tuning the query to minimize the number of blocks reads from disk to database buffer cache.

Example: I have a one huge table & it contains 10000 blocks. There is no index for this table.If we doing any operation against this table, it’s going full table scan & accessing all the blocks in a table (server process reads the 10000 blocks from disk to database buffer cache). We can put proper index for this table & avoid the full table scan.

See Also : 

How to Prepare for DBA Interview

General Tips to Prepare for an Oracle DBA Job Interview

For more updates,  Like Us Our Facebook Page Here 

Deadlock (Practical) – Simulating and Identifying

Today I will show you , how to overcome with Deadlock.

First , we will simulate the deadlock scene between 2 users

And  then solve that situation.

Login as SYSDBA

$ Sqlplus / as sysdba

SQL> create user a1 identified by a1;

User created.

SQL> grant connect , resource to a1;

Grant succeeded.

SQL> create user b1 identified by b1;

User created.

SQL> grant connect , resource to b1;

Grant succeeded.

Terminal 1

SQL> conn a1/a1;

Connected.

SQL> create table student_info (roll_no number (4));

Table created.

SQL> insert into student_info values(1001);

1 row created.

SQL> insert into student_info values(1002);

1 row created.

SQL> insert into student_info values(1003);

1 row created.

SQL> insert into student_info values(1004);

1 row created.

SQL> commit;

Commit complete.

SQL> grant update on a1.student_info to b1;

Grant succeeded.

SQL> update student_info set roll_no=0099;

4 rows updated.

Terminal 2

SQL> update a1.student_info set roll_no=8855;

(it is blocked or hanged as User A1 is updating and not Yet committed..)

Sometimes, you will get error as below

          *

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

Terminal 3  (Another Terminal to check the blocker to kill its session)

Login as SYSDBA

SQL> select b.inst_id,b.sid “blocker”,w.inst_id,w.sid “waiter”

           from gv$lock b,gv$lock w

           where b.block>0 and w.request>0 and b.id1=w.id1 and b.id2=w.id2;

   INST_ID            BLOCKER             INST_ID                WAITER

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

                 1                            1                             1                             33

SQL> select username,sid, serial# from gv$session where sid in (1,33) ;

USERNAME                                                 SID      SERIAL#

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

A1                                                                            1               9

B1                                                                           33          151

SQL> alter system kill session '1,9';

System altered.

Note: To know what table (id) is accessed by the users

SQL> desc v$locked_object

SQL> select inst_id,object_id from gv$locked_object

            where session_id in (1,33);

 

 INST_ID  OBJECT_ID

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

1                   13008

SQL> select owner,object_name from dba_objects

            where object_id=13008;

OWNER                               OBJECT_NAME

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

A1                                          EMP_INFO

 See also : General Tips for DBA Interview

Join Us on FACEBOOK HERE

Some Useful Scripts on Session

Find Active Sessions in Oracle Database

SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time

FROM V$Session

WHERE

Status=‘ACTIVE’ AND

UserName IS NOT NULL;

How to check the maximum number of allowed connections to an Oracle database?

The number of sessions the database was configured to allow

SELECT name, value

 FROM v$parameter

 WHERE name = 'sessions'

The number of sessions currently active

SELECT COUNT(*)

 FROM v$session

Identify the session which is locking the object

select a.sid, a.serial#

from v$session a, v$locked_object b, dba_objects c

where b.object_id = c.object_id

and a.sid = b.session_id

and OBJECT_NAME='EMP';

Oracle: Total Size of The Database

An oracle database consists of data files, redo log files, control files, temporary files.

The size of the database actually means the total size of all these files.

select

( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +

( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +

( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +

( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"

from

dual ;