RE: IO Details || ORCL

Everything Mladen wrote, +1.  

One more thing: If your “compute node” is across a VLAN or a LAN shared by multiple hosts from your storage, then depending on quality of the quality of service software and the network load of all traffic sharing the physical infrastructure of the LAN, your performance measurements may vary drastically by the varying load of all hosts. A typical bad measurement of VLAN storage i/o is done in the middle of the night when none of your employees are shopping or streaming video.  

If your storage is across a WAN, well, all consistency expectations are beyond the scope of a casual answer.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala Sent: Saturday, July 15, 2023 1:21 AM
To: oracle-l_at_freelists.org
Subject: Re: IO Details || ORCL  

On 7/14/23 23:34, Krishnaprasad Yadav wrote:

How to calculate read response time for a single block and for multiple blocks for an oracle database ? i tried certain research from different website , we see only metric is about : 'Average Synchronous Single-Block Read Latency' , i can't get any further details , Are Average Synchronous Single-Block Read Latency can be used to calculate the IO-throughput from database .

SLOB can help you with that. SLOB is an abbreviation for "Silly Little Oracle Benchmark" and was written by Kevin Closson, The software can be downloaded from GitHub. My favorite starting point for all things SLOB is: https://kevinclosson.net/slob/

If you don't want to include database in measuring the IO characteristics of your system, you can also use things like fio or bonnie++. Those programs are benchmark which measure IO performance. Especially fio has many options to emulate Oracle I/O (direct I/O) and is a great tool.

Oracle has DBMS_RESOURCE_MANAGER.CALIBRATE_IO. However, the results aren't repeatable which makes it somewhat questionable as a benchmark.

You can even update SYS.RESOURCE_IO_CALIBRATE$ manually. That's what SLOB is for. There was even a bug with DBMS_STATS failing because of row lock contention in RESOURCE_IO_CALIBRATE$ and Oracle Support directed people to truncate this table manually.

--

Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

--

http://www.freelists.org/webpage/oracle-l Received on Sun Jul 16 2023 - 00:16:54 CEST