| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> FW: [Fwd: Response times with different multiblock read count]
Prasad,
the difference here is :
select count(*) does not require the data in all of the columns. if you are going to be including columns that return a large amount of data, then the larger reads should help.
As its most likely that the OS read size is 64 KB, it makes sense that a local minimum of the response time function is found at that point.
Why don't you retry the query while bringing back all (or most) of the columns?
hth,
Paul
Paul Drake
DBA/SysAdmin
Professional Software Systems
Hi All,
I am running a test query with different db_file_multiblock_read_count to test the overall throughput.
environment : oracle 8.1.6 on hp-ux v11
db_block_size : 16k
system is using LVM and file system is using buffered I/O and disk is
mirrored but not stripped.
I was expecting that having larger multiblock count would result in
better performance. But, when I was testing the query with smaller
multiblock
count,the response time is better.
Here is the testing result.
Multiblock time in Secs ---------- ------------ 1 12.5 better 2 12.5 better 4 12.6 better 8 27 bad 16 27 bad 32 18.6 okay 64 19.0 okay
I don't understand why 8,16 are taking longer time. I did make sure that oracle is issuing proper multiblock read count with the help of multiblock_read_test.sql which is available in Steve Adams's site. Thank you steve for providing valuable information & scripts on your web site.
select statement that I am using is,
select /*+ full(t) noparallel(t) nocache(t) */ count(*) from &Table t;
I am clueless why it is behaving like this. Please pass your
suggestions.
If you need more info like oracle/OS settings etc, please let me know.
When I was doing some reading on steve adam's site, he suggests the following.
Date: 29-Dec-2000 20:55
Nevertheless, it remains best to allow Oracle to use large multiblock
reads, as long as the optimizer is not doing
inappropriate full scans. The explanation relates to the system call and
context switch overheads associated with I/O.
It is cheaper in CPU usage terms to request a 256K I/O and allow the
operating system to service it in 64K chunks,
than to request 4 I/O's of 64K each. As is mentioned in the tip on Why
Large Multiblock Reads, the increased
latency is not an issue because multiblock reads should not be used in
cases where "first rows" response time is
critical.
Thanks in advance.
Best Regards,
Prasad
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Paul Drake
INET: drake_at_psscorp.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Mar 28 2001 - 13:14:46 CST
 |
 |