Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    3

    Unanswered: Buffer Wait Ratio: 75.1%

    I'm having issues with slow SELECT-Statements in a customer 4.6C SAP system with an Informix database underneath.

    SAP (st02) says:
    Data Buffers Size kb 0 (In words: Zero!)
    Buffer Reads 16,356,855,961
    Buffer Wait Ratio: 75.1%

    Active Database parameters are as following:
    Parameter: BTSCANNER
    Value: num=12,threshold=500000,rangesize=500,alice=6,comp ression=med
    Parameter: BUFFERPOOL 0
    Value: size=2K,buffers=800000,lrus=124,lru_min_dirty=1.00 00000000,lru_m
    Parameter: BUFFERS 0
    Value: 800000
    Parameter: BUFFERS_DEFAULT
    Value: size=2K
    Parameter: BUFFSIZE 0
    Value: 2048

    What is BUFFERS 0 anyway? Shouldn't it be just BUFFERS?
    Can that be a typo which cause DB to have no data buffer at all?!?

  2. #2
    Join Date
    Aug 2009
    Posts
    3
    I noticed the system has also run out of physical memory. Do you think, that might be it? A swapped data buffer?

  3. #3
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    The BUFFERS 0 , probably is a misinterpretation of SAP.
    The BUFFERS keyword is a old config parameter on Informix

    This configuration have only 1.6GB of buffers to keep your data on memory (data saved on 2K pages). Depending of the database size and how much access your have, this is to low .

    First , check if all your data are working in dbspaces with 2K pagesizes (onstat -d).
    Then, if you confirm this, check the possibility to grow up the BUFFERPOOLS buffers.
    If you have data working with pagesizes different of 2K , review your BUFFERPOOLS for this pagesizes.

    To make sure the reason of the slowness of your SQL , execute it with SET EXPLAIN ON , and analyze the access (if use index, if create temporary tables, etc).
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  4. #4
    Join Date
    Aug 2009
    Posts
    3
    Well, database is 5 TB in size.

    We're actually migrating it completely through SAP 4.7 to an SAP ECC system.

    BUFFERS have been boosted to 1500000.

    But it's no good. I'm telling them, that we need their productive system as source for the test cycles.

    Thanks for the hint.

Posting Permissions

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