Re: reads vs writes

The *Logical read (blocks)* and *Block changes* of the AWR *Load Profile* information can help you decipher whether your application is OLTP or DWH type namely

Load Profile

*Per Second*

*Per Transaction*

*Per Exec*

*Per Call*

DB Time(s):

106.9

1.1

0.10

0.11

DB CPU(s):

1.1

0.0

0.00

0.00

Background CPU(s):

0.2

0.0

0.00

0.00

Redo size (bytes):

18,884.0

189.3

Logical read (blocks):

5,747.1

57.6

Block changes:

74.1

0.7

*Block changes/Logical read (blocks)* gives the proportion of blocks reads that are changed.

Instead of using the AWR report you can write a script against a couple of AWR historical tables like shown below:

FROM              dba_hist_sysstat a,

             dba_hist_snapshot sna

            WHERE

                a.snap_id  = sna.snap_id

            AND a.instance_number = sna.instance_number

            AND a.stat_name in

                        (

                        ,'session logical reads'

                        ,'db block changes'

                        ,'physical reads'

                        )

And derive from there the evolution of % of blocks read that are changed

Best regards

Mohamed Houri

Le mar. 13 févr. 2024 à 20:37, Tim Gorman <tim.evdbt_at_gmail.com> a écrit :

> Raman,
>
> In my experience, a ratio of 15 reads to every write is very much to be
> expected (i.e. workload of 93-94% reads, 6-7% writes) for almost all
> database workloads. I would be shocked if it were outside this range,
> especially using V$SYSSTAT data averaged over 2 years.
>
> It strikes me that there is not a great deal that can be understood just
> from I/O counts. I/O is always measured at least three ways...
>
> 1. counts (I/O requests per second or IOPS) <== this is what you're
> seeking...
> 2. throughput (MB/s)
> 3. latency (s, ms, us, or ns)
>
> I find counts or IOPS useful mainly if each I/O request is exactly the
> same size, such as the 4096 bytes used by IBM mainframes forever and ever,
> amen. But Oracle RDBMS uses a variety of I/O request sizes, ranging from
> 512 bytes to 1 MByte and beyond, so counting I/Os or measuring IOPS is of
> limited use with Oracle database workloads because 1000 sequential reads of
> 512M each arguably cannot be compared to 1000 random-access single-block
> reads of 8K each. Thus, IOPS and counts of I/Os are not very useful for
> any purpose, in general.
>
> Throughput is a very useful metric for measuring and understanding I/O
> performance because it is a rate based on a constant unit of measure,
> usually MB/s. When comparing a value of 50 MB/s to a value of 100 MB/s, it
> is safe to say that the latter value is greater.
>
> Latency, the amount of time it takes for an I/O request to be fulfilled,
> is also dependent on the size of the I/O request, and thus difficult to
> obtain or compare without that qualification. If someone says that they
> are achieving I/O latency of 1 ms, then that is a suspect statement,
> because we don't know the I/O request size used, nor whether it was a read,
> write, or combination of reads and writes. On the other hand, if they say
> they achieved I/O latency of 1 ms with 8 KB reads, then we have a useful
> value for comparison with other 8 KB reads.
>
> Oracle attempts to provide more information in the views related to
> V$SYSMETRIC (i.e. including V$SYSMETRIC_HISTORY and
> DBA_HIST_SYSMETRIC_SUMMARY). I would hope that, for the counts and latency
> metric, Oracle makes some attempt to "normalize" I/O request size and not
> just throw all data from all I/O requests into the same bucket, but I
> haven't seen any information one way or the other, so I tend to take
> Oracle's SYSMETRIC information for IOPS and latency with a bit of suspicion.
>
> However, Oracle also presents some information on I/O throughput in these
> views, such as METRIC_NAME of...
>
> - Physical Read Bytes Per Sec
> - Physical Read Total Bytes Per Sec
> - Physical Write Bytes Per Sec
> - Physical Write Total Bytes Per Sec
>
> For I/O latency, Oracle also presents some information using METRIC_NAME
> of...
>
> - Average Synchronous Single-Block Read Latency
>
> In summary, please be aware that simply counting the number of I/O
> requests is only a portion of the information needed to evaluate I/O
> performance. Be sure to try to obtain measurements of I/O throughput as
> well as latency to provide a more accurate (and more complex) depiction of
> what is going on.
>
> Hope this helps?
>
> -Tim
>
>
>
>
>
> On 2/12/2024 11:59 AM, Ram Raman wrote:
>
> Hi all,
>
> We are trying to find out if our database is more read or write intensive.
> so I queried v$sysstat in our DB that has been running for the past 2
> years. When i was looking for physical writes, I queried for statistics
> using 'physical write%' and 'physical read%' and it looks like we are read
> heavy with about 15x more reads than writes. Is this a right way
> approximately to gauge if a system is read or write heavy?
>
> --
> Thanks,
> Ram
>
>
>

-- 

Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Visit My         - Blog <http://www.hourim.wordpress.com/>

Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri
<https://twitter.com/MohamedHouri>

--
http://www.freelists.org/webpage/oracle-l