| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Cached blocks and performance
Jonathan,
>> I think performance is
>> being adversely affected because sometimes I am accessing more blocks
>> than I should have to, to get the rows I need back.
This could be true of almost any read operation! If you have 200 rows per Oracle block, then every time you read a 'single row' from a table, you are reading all 200 into the buffer cache (if it isn't already there) just to get that one you want. If you are full scanning the table, then you'll be reading shed loads of rows to get the ones you want. If you use an index, say primary key, then to get the one 'hit' you still need to read in the other 199 - its just how Oracle works.
>> Although my
>> overall cache hit ratio is good (I've read the myths thread and know
>> it isn't an indicator to be used on its own)
Man cannot live by ratios alone. A couple of FTSs will soon increase your ratio towards the nice to have settings.
>> some queries seem to
>> generate excessive I/O activity, i.e. Physical reads much higher for
>> one of two queries that have similar execution plans and similar rows
>> returned. What forces a query to go to disc?
>> 2 - Sparse rows in objects (too large pct free, stale index leaf
>> blocks, rows selected just happen to be spread across many blocks)
>> 3 - Rows chained across blocks (too small pct free, regular updates)
>> Which of these is true in my case, I think I have ruled out 1,4 and 5
>> could it be 2 or 3, how can I diagnose this, what is the cure?
Well, you'd go to disc any time the row you want is not in a block already in the cache. This means that either the block has never been read yet, or has aged out. In the case of a FTS, blocks age out quicker.
If your query is forcing the use of an index, youll need cache space for these too. Again, use of an index needs to read the index root, intermediate and leaf blocks and then the data block. So if the query is hinted to use an index when a FTS would be better, then your going to hit the disc - if the cache is to small.
If you have chaining then you could be using a block size that is too small. I'm on unix and we use 8K. I believe Howard has used 16K (and possibly 32 K) on Windows with no problems. More space to put data in bigger blocks as the percentage of the block not used by Oracle overhead is smaller. You should watch out for INITRANS and FREELISTS when using a bigger block. If you think you have chaining, analyse the tables (better to use DBMS_STATS) and check chain_cnt in user_tables for non-zero entries.
Bigger block = more rows, and more index entries = less disc reads to
get the same data. How big is your block size ?
But a bigger block needs mose space in the cache for the same number of
blocks - this might cause your SGA to page which is a real performance
hog.
Additionaly, the block size should be the same as the OS (or File
system) block size. If you have an 8K Oracle block and a 4K OS block
then you need two disc reads for each Oracle block read - this *might*
trigger read ahead on the OS end as it could 'think' that you are doing
a sequential scan of the disc. If the Oracle block is 4K and the OS is
8K, then your OS is reading 8K each time when Oracle requests 4K.
HTH Norman.
Tel: 0113 289 6265 Fax: 0113 289 3146 URL: http://www.Lynx-FS.com -------------------------------------Received on Fri Aug 30 2002 - 07:23:16 CDT
 |
 |