Physical/Logical I/O, Direct Path, Smart Scan Awareness, Storage Latency, and I/O Calibration

Learning outcomes

ServiceHub storage dashboards show high MB/s, while Oracle reports a slow query. One engineer blames the disks; another says “it is a full scan, so Smart Scan should handle it.” On an ordinary Free container there is no Exadata Smart Scan, and bytes alone cannot distinguish useful I/O from an SQL plan reading far too much data. Oracle performance engineering separates logical reads, buffered physical reads/writes, direct path operations and measured storage latency.

1. Logical I/O is buffer access, not necessarily storage access

A logical read (consistent/current get) accesses a database block through Oracle's buffer/cache machinery. The block may already be in memory. A physical read transfers data from storage into memory or a direct-path consumer. Reducing logical work often reduces CPU and can reduce physical I/O too; buying faster storage does not fix a query that visits 100 times more blocks than necessary.

SELECT name,valueFROM v$sysstatWHERE name IN (  'session logical reads',  'physical reads',  'physical reads direct',  'physical writes',  'physical writes direct',  'table scan blocks gotten',  'index fast full scans (full)')ORDER BY name;

2. File-level I/O exposes requests, bytes and service time

SELECT  filetype_name,  small_read_reqs,  small_read_megabytes,  large_read_reqs,  large_read_megabytes,  small_write_reqs,  small_write_megabytes,  large_write_reqs,  large_write_megabytes,  small_read_servicetime,  large_read_servicetime,  small_write_servicetime,  large_write_servicetime,  asynch_io,  access_method,  con_idFROM v$iostat_fileORDER BY  (small_read_megabytes+large_read_megabytes+   small_write_megabytes+large_write_megabytes) DESC;

In V$IOSTAT_FILE, the service-time columns are cumulative total milliseconds for the corresponding request type; derive a coarse average only by dividing service time by request count. They are not storage-array percentile latency. V$FILEMETRIC_HISTORY separately exposes recent average read/write time. Correlate both with OS/storage metrics and SQL workload.

SELECT  file_id,  begin_time,  end_time,  average_read_time,  average_write_time,  physical_reads,  physical_writesFROM v$filemetric_historyORDER BY end_time DESC,file_id;

3. Build one table with selective and broad access paths

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE sh23_io_work PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE sh23_io_work ASSELECT  LEVEL AS event_id,  MOD(LEVEL,10000) AS technician_id,  DATE '2026-01-01' + MOD(LEVEL,240) AS event_date,  MOD(LEVEL*17,100000)/10 AS amount,  RPAD('servicehub-io-',180,'x') AS payloadFROM dualCONNECT BY LEVEL <= 150000;CREATE INDEX sh23_io_tech_ixON sh23_io_work(technician_id);BEGIN  DBMS_STATS.GATHER_TABLE_STATS(USER,'SH23_IO_WORK',cascade=>TRUE);END;/

4. Compare runtime row-source evidence

SELECT /*+ gather_plan_statistics */       SUM(amount)FROM sh23_io_workWHERE technician_id=4242;SELECT *FROM TABLE(  DBMS_XPLAN.DISPLAY_CURSOR(    NULL,NULL,'ALLSTATS LAST +IOSTATS +PREDICATE'  ));

SELECT /*+ gather_plan_statistics */       SUM(amount)FROM sh23_io_workWHERE event_date >= DATE '2026-01-01';SELECT *FROM TABLE(  DBMS_XPLAN.DISPLAY_CURSOR(    NULL,NULL,'ALLSTATS LAST +IOSTATS +PREDICATE'  ));

Compare actual rows, starts, buffers and physical-read columns. A broad table scan can be the correct access path when most rows are needed. “Full scan” is not synonymous with “bad plan.”

5. Snapshot session/system deltas around the query

SELECT  n.name,  s.valueFROM v$mystat sJOIN v$statname n  ON n.statistic#=s.statistic#WHERE n.name IN (  'session logical reads',  'physical reads',  'physical reads direct',  'physical writes',  'physical writes direct')ORDER BY n.name;

Capture before and after in the same session to isolate the query. A second execution may benefit from cache, so record cache/warmup state as part of any benchmark rather than presenting first-run and warm-run results as comparable.

6. Direct path bypasses part of the ordinary buffer-cache path

Large scans, TEMP work, parallel operations and direct-path loads can use direct I/O paths depending on the operation/release/configuration. Counters such as physical reads direct provide evidence. Direct path does not mean storage offload.

7. OS/storage evidence must use the same window

iostat -x 1 10vmstat 1 10

Get-Counter '\PhysicalDisk(*)\Avg. Disk sec/Read',            '\PhysicalDisk(*)\Avg. Disk sec/Write',            '\PhysicalDisk(*)\Disk Reads/sec',            '\PhysicalDisk(*)\Disk Writes/sec' `  -SampleInterval 1 -MaxSamples 10

OS device metrics include other processes/filesystems. Oracle file statistics include database activity. Match timestamps and device/file mapping before attributing one to the other.

8. I/O calibration is not an innocent benchmark

DBMS_RESOURCE_MANAGER.CALIBRATE_IO drives intense random block-size and 1 MB reads to estimate maximum IOPS/MBPS/latency under Oracle's calibration model. Current documentation requires SYSDBA, TIMED_STATISTICS=TRUE, asynchronous I/O, and in a CDB it is run from CDB$ROOT. It is extremely disruptive; run it only during an approved idle/off-peak window on a representative system.

ALTER SESSION SET CONTAINER=CDB$ROOT;SELECT name,valueFROM v$parameterWHERE name IN (  'timed_statistics',  'filesystemio_options')ORDER BY name;SELECT * FROM v$io_calibration_status;SELECT *FROM dba_rsrc_io_calibrateORDER BY start_time DESC;

9. Deliberately wrong: run calibration at noon to “see production storage performance”

The calibration workload competes aggressively with production I/O and can create the very incident being investigated. It also measures storage under Oracle's synthetic pattern, not the application's SQL mix. The repair is to use passive interval evidence during production, then schedule calibration on representative storage during a controlled maintenance/test window if justified.

10. Cleanup

DROP TABLE sh23_io_work PURGE;

11. Production judgment

Optimize the amount and shape of I/O before optimizing the device. Start with SQL row-source/buffer evidence and logical/physical deltas; correlate Oracle file service time with OS/storage telemetry. Treat direct path as an access mechanism, Smart Scan as an Exadata-specific offload, and calibration as a disruptive capacity test.

No optional pack, restart or COMPATIBLE change is needed for the mandatory Free lab. FILESYSTEMIO_OPTIONS is platform-specific, static and not PDB-modifiable; do not change it from a tutorial without validating filesystem/database support. Lesson 3 moves from I/O delay to serialization: sessions can be fast individually yet stall each other on latches, mutexes, enqueues or hot blocks.

Authoritative references

• V$IOSTAT_FILE — file I/O requests/bytes/service time
• V$FILEMETRIC_HISTORY — recent file latency/throughput
• DBMS_RESOURCE_MANAGER.CALIBRATE_IO — I/O calibration prerequisites/API
• I/O Calibration — calibration operation/cautions
• Exadata Smart Scan — Exadata storage offload boundary