-
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
-
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
-
It is definitely using index. I modified db_file_multiblock_read_count but has no effects. Anyone has any suggestions?? Can anyone help??
-
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?
-
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.
-
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?
-
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'.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|