RE: Time to read 6000 (block size 2k) blocks

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Fri, 6 Aug 2004 09:41:30 -0500
Message-ID: <003301c47bc3$79b264b0$6501a8c0@CVMLAP02>

Yes, this is actually 0.003568s/read, which is pretty good.

A better question, though, is, "Does the application really need to make 18,805 visits to the database buffer cache to return just one row?"

Unless your query uses some kind of aggregation function to return the single row (count, sum, etc.), then you should be able to make this SQL = do
its job with 10-20 LIOs instead of 18,805. If you can do that, you = should be
able to reduce response time from 41.99s to about 0.04s.

I can't see your SQL here, but because there were 2 fetch calls, I'll = bet
that you're not aggregating the result, and that you should be able to = get
to the 0.04s response time target. It might be as simple as a missing = index,
or SQL that debilitates the use of an index.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Upcoming events:
- Performance Diagnosis 101: 8/10 Boston, 9/14 San Francisco, 10/5 = Charlotte
- SQL Optimization 101: 7/26 Washington DC, 8/16 Minneapolis, 9/20 = Hartford
- Hotsos Symposium 2005: March 6-10 Dallas - Visit www.hotsos.com for schedule details...

-----Original Message-----

From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Khedr, Waleed
Sent: Friday, August 06, 2004 9:19 AM
To: oracle-l_at_freelists.org
Subject: RE: Time to read 6000 (block size 2k) blocks

Five millisecond is not bad for single block sequential read.

Waleed

-----Original Message-----

From: jaysingh1_at_optonline.net [mailto:jaysingh1_at_optonline.net]=3D20 Sent: Friday, August 06, 2004 9:49 AM
To: oracle-l_at_freelists.org
Subject: Time to read 6000 (block size 2k) blocks

Hi All,

The question may be wispy.
We have 14 CPU sun box,8i 2 node OPS. Not under heavy load.

In our case it is taking 21.86 sec for 6126 blocks (from disk)

db file sequential read                      6126        0.29

21.86

Approximately how long it should take to read 6000 blocks?

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 11.39 41.98 6126 18805 0 1
------- ------ -------- ---------- ---------- ---------- ----------

total 4 11.40 41.99 6126 18805 0 1

db file sequential read                      6126        0.29

21.86

Thanks
Sami

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com


----------------------------------------------------------------

To unsubscribe send email to:  oracle-l-request_at_freelists.org

put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Fri Aug 06 2004 - 09:38:34 CDT