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

Thread: A real tricky question!!

  1. #1
    Join Date
    Dec 2001
    Location
    Brisbane, Australia
    Posts
    28

    Question

    One of our clients has the following problems:
    The application trace file shows that every forth read/fetch from Oracle takes longer time than other read/fetch. The application itself does the following:
    - declare cursor
    - open cursor
    - fetch until no more records
    Initially, I thought it is related to the data block but the data block factor is ruled out because the record size is 728 bytes and it adds up to about 2.9kb. Moreover, it happens to all tables which has different record length.

    Does anyone experience anything similar? Is there any parameter or Oracle setting that controls how many records to read per fetch / IO? I can only think of db_file_multiblock_read_count that may be related. Any help or advise will be greatly appreciated.

    Regards,
    Mike

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    db_file_multiblock_read_count only affects FULL TABLE SCAN
    if the segment HWM is high then I/O scan will scan all the blocks until HWM even 99% blocks contains no data

  3. #3
    Join Date
    Dec 2001
    Location
    Brisbane, Australia
    Posts
    28
    It is definitely using index. I modified db_file_multiblock_read_count but has no effects. Anyone has any suggestions?? Can anyone help??

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Can you be a little more specific about your problem? What makes you think that each forth fetch takes longer than previous three? And how much longer? 10%, 50%, 200% longer? How did you measure that. Which trace file can tell you that?

    What is your block size? How much logical reads and how much physical reads does your query execute? Do you have any migrated/chained rows in that table?

    We need more info....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Dec 2001
    Location
    Brisbane, Australia
    Posts
    28
    It is our application trace file. The application opens the cursor and then fetch all rows until no more record. It writes to the trace file between each fetch. The trace file indicates every forth read takes about 4ms longer which is 4 times longer (normally 1ms).

    The block size is 8k block. I don't have the detail of logical reads and physical reads as I don't have access to the customer database. I will try to get the info from the customer.

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Is this behavior consistent? I mean, do you consistantly get *every forth read* takes longer (ie always three short reeds followd by one long read), or is it *on everage one of four reads* takes longer?

    I can imagine couple of possible reasons:

    - On every forth logical read your database has to do a new physical read
    - About every forth record in your table is chained/migrated
    - The delay in every fourth fetch is actualy caused by your trace writing mechanism, not caused by the database itself! How is your application writing to the trace file? Is it possible that it simply buffers the measurments in some small buffer, and writes it into your file only when the buffer is full? It might just happen that it has to flush that buffer on every fourth fetch.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Aug 2001
    Posts
    184
    maybe ur 4th fetch is over 8k limit and you have to do another phys read?
    OCP DBA 8i
    ocpwannabe@yahoo.com
    -----------------------------
    When in doubt, pick 'C'.

  8. #8
    Join Date
    Dec 2001
    Location
    Brisbane, Australia
    Posts
    28
    Yes, it is consistently every forth read.

    I am suspecting that the problem is related to the OCI layer. I am looking into the OCI coding and it can configures to prefetch certain number of rows. I am getting the customer to try out a few things and hopefully it will resolves the issue.

    Thanks for all the reply!!

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