Viscosity
logo-black
  • Solutions
    • Data
      • Performance Assessment
      • Proactive Healthcheck
      • Database Migration & Consolidation
      • Performance Tuning
      • Microsoft Services
        • Microsoft Azure Services
        • Microsoft SQL Server Services
        • Microsoft Gold Azure Partner
      • Oracle Services
        • Exadata Resale & Services
        • Oracle Database Appliance Resale & Services
        • Oracle Database 19c
        • RAC
        • GoldenGate
        • Data Guard
        • Oracle & SQL Database
    • Infrastructure
      • Engineered Systems
        • Engineered Systems Managed Services
        • Exadata Resale & Services
        • Oracle Database Appliance Resale & Services
        • ODA Health Checks
      • Cloud
        • Hybrid Cloud
        • Viscosity Edge Cloud
        • Virtualization & Cloud Expertise
        • Microsoft Azure Services
    • Application Development
      • Oracle APEX
      • Viscosity AMP
      • JD Edwards
  • Resources
    • Case Studies
    • Whitepapers
    • Data Sheets
    • DBA Resources
    • Publications
  • Events
    • Upcoming Events
    • 2023 Past Events
    • 2022 Past Events
  • Blog
    • Viscosity's Blog
    • Newsroom
    • The 12 Days of Oracle Database 18c/19c
  • About
    • Partnerships
    • Clients
    • Contact
    • Minority Development
    • Terms & Conditions
      • Privacy Policy
Hit enter to search or ESC to close
ENGAGE WITH US

Oracle database , Performance Tuning , Oracle Database Technology , io reads

How to Tell if the IO Subsystem Reads Are Struggling

By Craig Shallahamer
December 05, 2022

got-io-issues-w250How many times have you heard or felt, "It's an IO problem!" How can you tell? What does this even mean? Who's fault is it? It is a broad and complicated topic.

But before we can approach the IO team, we must know if the IO subsystem is struggling.

For this article, I am focusing on how to tell if the IO reads are fast enough. To add a little spice and reality into the mix, I am only going to use the Oracle performance v$ views.

So, here we go!

 

How To Tell If IO Reads Are Fast Enough

As DBAs, we have a powerful way to understand how long it takes for a single operating system IO read request to complete. The secret lives within the common Oracle wait event, db file sequential read.

The db file sequential read wait is unique in its simplicity. And it is this simplicity that enables us to know, with confidence, if the IO reads are fast enough.

How Oracle Gets IO Read Times

Here is how it works. Because Oracle instruments its non-CPU consuming source code before a wait event occurs, Oracle asks the OS, "What time is it?" and stores the result. When the wait is over, Oracle again asks the OS, "What time is it?" At this point, Oracle knows both the beginning and end times plus what the wait was all about, so Oracle gives it a name.

The db file sequential read is a simple single block synchronous read IO call. On Linux, the system call is usually pread. We can use this simplicity to determine if the IO subsystem is struggling with IO reads.

Here is a great reference about the difference between read and pread.

Using the Linux strace command, we can see how this is done. Below is a snippet from an Oracle Database 12c foreground process when it is screaming, "db file sequential read!"

$ strace –rp 2518
...
0.000324 clock_gettime(CLOCK_MONOTONIC, {504, 52586559}) = 0
0.000040 clock_gettime(CLOCK_MONOTONIC, {504, 52625324}) = 0
0.000040 pread(257, "\6\242\0\f\0"..., 8192, 427270144) = 8192
0.000047 clock_gettime(CLOCK_MONOTONIC, {504, 52712996}) = 0
0.000044 clock_gettime(CLOCK_MONOTONIC, {504, 52757393}) = 0
0.000329 clock_gettime(CLOCK_MONOTONIC, {504, 53086771}) = 0
0.000040 clock_gettime(CLOCK_MONOTONIC, {504, 53125505}) = 0
0.000040 pread(257, "\6\76 [y\f\0"..., 8192, 427278336) = 8192
0.000047 clock_gettime(CLOCK_MONOTONIC, {504, 53213583}) = 0
0.000040 clock_gettime(CLOCK_MONOTONIC, {504, 53253021}) = 0
0.000327 clock_gettime(CLOCK_MONOTONIC, {504, 53580561}) = 0
0.000040 clock_gettime(CLOCK_MONOTONIC, {504, 53619199}) = 0
0.000040 pread(257, "\6\273\f\0"..., 8192, 427286528) = 8192
0.000047 clock_gettime(CLOCK_MONOTONIC, {504, 53706779}) = 0
0.000040 clock_gettime(CLOCK_MONOTONIC, {504, 53752611}) = 0
...

Let's focus on the above output for a bit. Knowing the Oracle database block size is 8KB, we can tell the Oracle foreground process is making single Oracle database block reads using the Linux pread call.

Do you see the system call, which is also a C function, clock_gettime? This is the Oracle foreground process asking, "What time is it?". As DBAs through Oracle's wait event views, we see the ending time minus the beginning time as the wait time for these db file sequential read(s).

Pure and simple, for the Oracle foreground process, this is the response time for a single block synchronous read call. If you want to know how long single block synchronous read calls are taking, simply look at your db file sequential read times.

Actually Watching An Oracle Process Figuring Out the Wait Time

It's true. You can learn a lot from sitting on your butt. If you want to see with your own eyes, an Oracle server process determines wait time for a multiple block synchronous read (event name is, db file scattered read), watch the below video.


WARNING: I need to mention that, for multiple block reads, Oracle will want to do asynchronous multi-block read calls instead of synchronous multi-block read calls. So, please do NOT use db file scattered read wait times to determine IO read response times. Currently, the only way I know of to reliably and easily get IO read response times from within Oracle is to use the sequential read wait times.

How Long Is Too Long?

This really is the question, isn't it? My rule of thumb is 10ms. That is, if the average db file sequential read time is greater than 10ms, then the IO subsystem is struggling.

There is more to this story. My 10ms rule is truly a "rule of thumb." There are other factors, such as, "Is 10ms too long for a hugely expensive IO subsystem?" Probably.

Here's an idea. Right now, email your IO vendor or IO system administrator. Ask them what an acceptable average single block read time is. Don't hang up until you get an answer ;)

Are there other factors? For sure. The average wait time is, well... the average. Are there longer wait times? For sure. How many and how long? We can get this information, and we can use it to tell if the IO subsystem is truly struggling.

 

Want to know how to determine the answers to these questions? That's the topic for my next article.

All the best in your Oracle performance tuning work!

Craig.

Start my FREE 18-lesson Machine Learning For Oracle Professionals E-Course here.

All posts
About Author
Craig Shallahamer

Craig is a co-founder of OraPub, Inc., an Oracle ACE Director and now the Applied AI Scientist at Viscosity North America. He is a long time Oracle DBA specializing in predictive analytics, machine learning and Oracle performance tuning. He has over 30 years of experience in consulting, training and tool development in analytics (machine learning) and Oracle database performance tuning for literally hundreds of companies around the world, including Oracle Corporation. He has been actively involved in several Oracle user groups, including IOUG, NoCOUG and UTOUG, and has managed conference DBA tracks for several years. He is the author of two classic and best selling books - Oracle Performance Firefighting and Forecasting Oracle Performance. Craig is a researcher, blogger, an enthusiastic conference speaker and a passionate teacher.

You might also like
Oracle 23c New Features
Oracle 23c New Features
December 05, 2022
Loading Large CSV Files Using Oracle External Tables
Loading Large CSV Files Using Oracle External Tables
December 05, 2022
SUBMIT YOUR COMMENT
logo fondo oscuro 2-01
Viscosity's core expertise includes:
Data Transformation, Emerging Technology, High Availability & Scalability Solutions, Cloud Migrations, Performance Tuning, Data Integrations, Machine Learning, APEX Development, and Custom
Application Development.

Solutions

Resources

Events

Blog

About

 

Contact
Email: sales@viscosityna.com

Telephone:
(469) 444-1380
Copyright 2022. All Rights Reserved by Viscosity North America.