Originally posted by jmodic Hm, I wouldn't say it is irrelevant. It would be (more or less) irrelevant if you had OS block size of 4K and DB_BLOCK_SIZE of 8K, but not the other way arround as the original poster asked.
If you have 8K OS block size and 4K database block size, then for every single block I/O operation the database requests, the OS will read an extra 4K of data the database hasn't asked for.
Jurij :
Ofcourse OS will fetch the extra 4K for single block I/O request, but I intended to say "Irrelevent" with respect to Performance Issue, because OS always fetechs in multilples of it Block Size limited my Max I/O per fetch.
So have DB Block_Size 8K or 4K, you will get 8K blocks per request (considering 8K block size as said by poster).
So, the Q of performance is Irrelevent, coz in any case you will get non requested data as well.
Abhay.
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"
UNIFORM Initial/Next sizes for both tablespace, and therefore segments, are 4MB.
PCTFREE 0
32K (128 Blocks per extent)
8K (512 Blocks per extent)
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
alter session set db_file_multiblock_read_count=64;
Session altered.
set timi on
Alter session set events '10046 trace name context forever, level 12';
Session altered.
8K Default Tablespace
SQL> select count(*) from test
where snapshot_id_c = 'test';
To find out OS block size, login as sys and run the following query:
select max(lebsz) from sys.x$kccle;
If Veritas is used and block size is 8k, then it is advisable to set your DB_BLOCK_SIZE to 8K. The reason is simple: no extra IO.
When they are NOT equal then OS has to do reconstruct the data block in the memory. This will impair performance.
Do the "elapsed" times not suggest that you are spending less time waiting on the 32K reads than on the 8k reads? How does that correlate to the longer elapsed time for the 32k read?
Originally posted by tamilselvan To find out OS block size, login as sys and run the following query:
select max(lebsz) from sys.x$kccle;
If Veritas is used and block size is 8k, then it is advisable to set your DB_BLOCK_SIZE to 8K. The reason is simple: no extra IO.
When they are NOT equal then OS has to do reconstruct the data block in the memory. This will impair performance.
Tamil
I'd like to see some supporting evidence -- i don't believe that any overhead that might be associated with this block reconstruction would be enough to offset the loss in i/o performance from larger block sizes.
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: FIRST_ROWS
1 SORT (AGGREGATE)
0 TABLE ACCESS (FULL) OF 'TEST'
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file scattered read 650 0.07 28.21
SQL*Net message from client 2 52.87 52.87
SQL> exit
SQL> Alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> select count(*) from test
where snapshot_id_c = 'test';
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: FIRST_ROWS
1 SORT (AGGREGATE)
0 TABLE ACCESS (FULL) OF 'TEST
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file scattered read 631 0.06 24.22
SQL*Net message from client 2 47.37 47.37
32K block size performed slightly quicker. But DMBC still as expected.
Incidently, for default Buffer_cache we can look at v$bh table for objects in the cache, how can we see what's in the non default cache?
it looks like you might get something from upping the DMBC there. What value were you using? Might like to try raising it by a factor of 4 and see if it eliminates some of those waits.
The DB is pretty quiet, but the server get's a bit of a hammering from time to time from other DB's. So, I'm still not convinced of a better read performance on larger non-default blocks sizes.
I was also testing indexes and tables in larger non-default DB's this morning... which was also eyebrow raising, with the blocks of an index scan going into the default db_cache... not the db_32k_cache as I'd have expected.... more testing to go on that one.
Reply With Quote
Bookmarks