Resizing Buffer Area

[newcomer]

Hi,

I am getting a significant BUFFER BUSY WAITS in my Database server, where as Buffer hit % is showing 99% along with Dict Hit % and Lib Cache Hit %. Should I have to resize my Buffer parameter value?

Regards

[hrishy]

Hi

Buffer Busy Segment header: add freelists or freelist groups.
Buffer Busy Data block: separate "hot" data; use reverse key indexes and/or smaller blocks.
Buffer Busy Data block: increase initrans and/or maxtrans.
Buffer Busy Undo header: add rollback segments or areas.
Buffer Busy Undo block: commit more often; use larger rollback segments or areas.


regards
Hrishy

[newcomer]

Hi Hrishy

Thank you very much. I am pasting my Wait events details with memory status, can you please help me - the areas I have to look into and how. I will appreciate your time. ---- I am poor in tuning and learning....

Thanks again in advance.

=====================================================================
Memory:

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.67 Redo NoWait %: 100.00
Buffer Hit %: 98.65 In-memory Sort %: 100.00
Library Hit %: 100.10 Soft Parse %: 99.85
Execute to Parse %: 98.12 Latch Hit %: 99.59
Parse CPU to Parse Elapsd %: % Non-Parse CPU:

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 89.09 89.08
% SQL with executions>1: 36.88 39.68
% Memory for SQL w/exec>1: 19.72 21.81



SGA breakdown difference for DB: GENPROD Instance: genprod Snaps: 1035 -1036

Pool Name Begin value End value Difference
----------- ------------------------ -------------- -------------- -----------
java pool free memory 32,768 32,768 0
large pool free memory 614,400 614,400 0
shared pool Checkpoint queue 22,293,600 22,293,600 0
shared pool DML locks 816,480 816,480 0
shared pool KGFF heap 474,272 474,272 0
shared pool KGK heap 95,424 95,424 0
shared pool KQLS heap 2,613,704 3,445,512 831,808
shared pool PL/SQL DIANA 1,329,384 1,882,792 553,408
shared pool PL/SQL MPCODE 9,994,360 9,298,088 -696,272
shared pool PL/SQL SOURCE 8,392 8,392 0
shared pool PLS non-lib hp 2,136 2,136 0
shared pool PX msg pool 97,160 97,160 0
shared pool PX subheap 19,600 19,600 0
shared pool State objects 2,238,880 2,238,880 0
shared pool db_block_buffers 348,860,928 348,860,928 0
shared pool db_block_hash_buckets 56,685,584 56,685,584 0
shared pool db_handles 880,000 880,000 0
shared pool dictionary cache 6,926,656 7,125,904 199,248
shared pool errors 252,536 252,536 0
shared pool event statistics per ses 3,836,560 3,836,560 0
shared pool fixed allocation callbac 18,312 18,312 0
shared pool free memory 105,934,248 106,028,456 94,208
shared pool library cache 144,981,336 143,515,128 -1,466,208
shared pool miscellaneous 8,017,896 7,901,568 -116,328
shared pool processes 1,072,000 1,072,000 0
shared pool sessions 2,687,360 2,687,360 0
shared pool simulator trace entries 2,000,000 2,000,000 0
shared pool sql area 247,340,232 247,921,384 581,152
shared pool table columns 25,056 29,248 4,192
shared pool table definiti 6,304 9,672 3,368
shared pool transactions 1,846,800 1,846,800 0
shared pool trigger inform 1,296 1,880 584
db_block_buffers ############## ############## 0
fixed_sga 102,076 102,076 0
log_buffer 4,194,304 4,194,304 0

====================================================================
Wait Events:

Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
SQL*Net more data to client 7,529,071 0 .00
db file sequential read 6,392,464 0 .00
buffer busy waits 2,150,518 0 .00
latch free 296,948 0 .00
db file parallel write 240,094 0 .00
-------------------------------------------------------------
Wait Events for DB: GENPROD Instance: genprod Snaps: 1035 -1036
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)

wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
SQL*Net more data to client 7,529,071 0 0 0 170.1
db file sequential read 6,392,464 0 0 0 144.4
buffer busy waits 2,150,518 0 0 0 48.6
latch free 296,948 293,325 0 0 6.7
db file parallel write 240,094 0 0 0 5.4
db file scattered read 124,124 0 0 0 2.8
log file sequential read 64,036 0 0 0 1.4
log file parallel write 40,491 3 0 0 0.9
log file sync 32,073 0 0 0 0.7
control file sequential read 11,310 0 0 0 0.3
file open 6,773 0 0 0 0.2
direct path read 3,142 0 0 0 0.1
control file parallel write 1,170 0 0 0 0.0
LGWR wait for redo copy 858 26 0 0 0.0
direct path write 669 0 0 0 0.0
refresh controlfile command 306 0 0 0 0.0
enqueue 243 0 0 0 0.0
SQL*Net break/reset to clien 41 0 0 0 0.0
log file switch completion 17 0 0 0 0.0
file identify 8 0 0 0 0.0
log file single write 4 0 0 0 0.0
library cache load lock 3 0 0 0 0.0
single-task message 2 0 0 0 0.0
SQL*Net message from client 10,720,427 0 0 0 242.2
SQL*Net message to client 10,720,403 0 0 0 242.2
SQL*Net more data from clien 278,228 0 0 0 6.3
-------------------------------------------------------------
Background Wait Events for DB: GENPROD Instance: genprod Snaps: 1035 -1036
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
db file parallel write 240,097 0 0 0 5.4
log file sequential read 64,036 0 0 0 1.4
log file parallel write 40,488 3 0 0 0.9
latch free 34,759 34,758 0 0 0.8
file open 5,173 0 0 0 0.1
direct path read 3,142 0 0 0 0.1
control file parallel write 1,170 0 0 0 0.0
control file sequential read 1,082 0 0 0 0.0
LGWR wait for redo copy 858 26 0 0 0.0
direct path write 656 0 0 0 0.0
db file sequential read 9 0 0 0 0.0
file identify 8 0 0 0 0.0
log file single write 4 0 0 0 0.0
rdbms ipc message 345,957 2,608 0 0 7.8
pmon timer 1,176 1,176 0 0 0.0
smon timer 12 12 0 0 0.0

[hrishy]

Hi

I think there is no problem with the size of your buffer cache.Thew problem is your database IO.

1)db file sequential read
2)buffer busy waits

Have a look at your sql's being used

The type of buffer that causes the wait can be queried using the v$waitstat view. This view lists the waits per buffer type for buffer busy waits, where COUNT is the sum of all waits for the class of block, and TIME is the sum of all wait times for that class:

select * from v$waitstat;

The columns of the v$session_wait view that are of particular interest for a buffer busy wait event are:

P1—The absolute file number for the data file involved in the wait.
P2—The block number within the data file referenced in P1 that is being waited upon.
P3—The reason code describing why the wait is occurring.

Here's an Oracle data dictionary query for these values:
select
p1 "File #".
p2 "Block #",
p3 "Reason Code"
from
v$session_wait
where
event = 'buffer busy waits';

If the output from repeatedly running the above query shows that a block or range of blocks is experiencing waits, the following query should show the name and type of the segment:
select
owner,
segment_name,
segment_type
from
dba_extents
where
file_id = &P1
and
&P2 between block_id and block_id + blocks -1;

Its okay many of us are novices in tuning :-)..You can upload ur statspack report to this website for recommendations

go to http://www.oraperf.com/index.html and clcik on analyze link for free analysis of statspack

regards
Hrishy

[DaPi]

You don't have any wait timings! I've put
timed_statistics = true
in the init.ora (I think you can also do an ALTER SYSTEM). The overhead is negligeable (they say). Not much point analysing until you know HOW LONG you have waited.

[sumit]

Thanks to Hrishy and Dapi for such a good help.

I followed your steps. I enabled the TIMED_STATISTICS=TRUE and performed the followings:

1. Found out all Wait Blocks and Class from V$WAITSTAT.
2. I found out the FILES,BLOCKS causing BUFFER BUSY WAITS.
3. Found out the SEGMENTS related to the above FILES and BLOCKS.

I have the following questions/doubts for you. Will appreciate your time again.

1) I can see a Significant COUNT for FREELIST and DATA BLOCK CLASS/BLOCK in v$WAITSTAT view. I understand now, that it will be a solution to add more FREELIST to the objects to get rid of BUFFER BUSY WAITS. But when I see all the SQL statements runnig on my server, NONE of them are trying to INSERT data into table. Only LARGE SELECTS on the above SEGMENTS are there from multiple processes.
a. IN SUCH CASE, do you think ADDING FREELIST will help me?
b. OR Suggest me if I have to take care of other things.
c. I was reading in one of the Oracle site that, BUFFER
BUSY WAITS might occur due to access of the same rows in a
block/buffer by multiple processes and they are suggesting
to reduce NO of ROWS per block. HOW TO REDUCE # OF ROWS
PER BLOCK?

2) The parameter PROCESSES are set to 1000. Which is in a higher side. Does this high value has any effect to the performance



Regards

[abhaysk]

Looks like you have inefficient SQL's running repeatedly..

[tamilselvan]

===
c. I was reading in one of the Oracle site that, BUFFER
BUSY WAITS might occur due to access of the same rows in a
block/buffer by multiple processes and they are suggesting
to reduce NO of ROWS per block. HOW TO REDUCE # OF ROWS
PER BLOCK?
===

There are many ways to reduce buffer busy waits:
1 Increase PCTFREE
2 HASH Partition the table
3 If the table has many columns (say 100), then split into 2 tables - each one will have 50 columns.
4 If possible, redesign the application

Tamil