You may be running into a limitation of HP-UX that can cause this problem. In
HP-UX during any one time slice the SO will return a MAX of 32K of data from
disk. This does take a little thought to understand. The OS is multitasking
between all of the processes. To do so it breaks time up into equal parts
called a 'time slice'. It is during this time slice that your process has the
full attention of the CPU, but your disk reads are a system process run during a
different time slice of a much higher level process. This process leaves the
data in a buffer that you can then access during your time slice. That's all
well and good, but if HP allowed you to retrieve all of the data you wanted at
one time then the system disk read process would consume more and more of the
available time slices, since it runs at a higher priority or "nice" level, and
the disk buffer would become totally useless to everyone else. Consequently
they arbitrarily set the max amount of data that can be retrieved to 32K. Yes
it's odd, but that's the way it is. This also gets affected by the number of
processes that run on the machine as well and the number that are "waiting" on
something and therefore relinquish their time slice.
This is strange, odd, and very confusing. It took our HP CE over an hour to
explain it to me and I'm not totally sure I understand, but from observation I
can tell you that it is true. Try the same query with the same
multi_block_read_count when the system is busy, and when your the only one
there.
Dick Goulet
____________________Reply Separator____________________
Author: [EMAIL PROTECTED]
Date: 3/28/2001 8:25 AM
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:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
