Resizing Buffer Area
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Resizing Buffer Area

  1. #1
    Join Date
    May 2002
    Posts
    163

    Resizing Buffer Area

    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
    Nwcomer
    Student

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    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

  3. #3
    Join Date
    May 2002
    Posts
    163
    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
    Nwcomer
    Student

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    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

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.

  6. #6
    Join Date
    Dec 2001
    Posts
    203
    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
    sumit

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Looks like you have inefficient SQL's running repeatedly..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ===
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width