I/O Usage by Schema by Day

Caution

This script modifies data in custom tracking tables and requires a database link (@ngp11). Note: The script contains a syntax error that needs correction.

Script: io_by_day.SQL

DELETE FROM IO_by_schema_by_date2
WHERE AWR_DATE = ( SELECT MAX(AWR_DATE) FROM CPU_BY_SCHEMA_BY_DATE );
COMMIT;




INSERT INTO oradba.IO_by_schema_by_date2 AS
SELECT  /*+ PARALLEL(XX, 2) PARALLEL( SQL, 2) PARALLEL(SNAP,2) */ PARSING_SCHEMA_NAME,
 trunc(snap.begin_interval_time) t_date,
 sum(disk_reads_delta) tio,
 xx.iio DIO,
 (sum(DISK_READS_DELTA) / xx.iio) xpct
from dba_hist_sqlstat@ngp11 sql, dba_hist_snapshot@ngp11 snap,
 (
 select /*+ parallel(sql2, 2) parallel(snap2,2) */ suM(sql2.DISK_READS_DELTA) iio, trunc(snap2.begin_interval_time) bit
 from dba_hist_sqlstat@ngp11 sql2, dba_hist_snapshot@ngp11 snap2
 where sql2.snap_id > 0
 and sql2.snap_id = snap2.snap_id
 and sql2.instance_number = snap2.instance_number
 AND trunc(snap2.begin_interval_time) > (SELECT MAX(AWR_DATE) FROM io_BY_SCHEMA_BY_DATE2)
 group by
 trunc(snap2.begin_interval_time)
 ) xx
where sql.snap_id >  0
and sql.snap_id = snap.snap_id
--and (parsing_schema_name = 'NGPROD')
and sql.instance_number = snap.instance_number
AND trunc(snap.begin_interval_time) > (SELECT MAX(AWR_DATE) FROM io_BY_SCHEMA_BY_DATE2)
and xx.bit = trunc(snap.begin_interval_time)
group by
PARSING_SCHEMA_NAME,
trunc(snap.begin_interval_time), xx.iio
order by trunc(snap.begin_interval_time) , PARSING_SCHEMA_NAME
/
commit;
commit;
commit;
@cpu_by_day
commit;
commit;

Important Note

Syntax Error: Line 9 has incorrect syntax. The “AS” keyword should be removed:

-- Incorrect:
INSERT INTO oradba.IO_by_schema_by_date2 AS

-- Correct:
INSERT INTO oradba.IO_by_schema_by_date2

Purpose

This script calculates and stores daily disk I/O consumption by schema, tracking what percentage of total database I/O each schema consumes. It’s designed for I/O trend analysis and capacity planning.

Key Features

• Daily I/O Aggregation: Summarizes disk reads by day and schema
• Percentage Calculation: Shows each schema’s share of total I/O
• Incremental Loading: Only processes new data since last run
• Integrated Processing: Calls cpu_by_day.sql for combined metrics

Prerequisites

1. Custom Table: IO_by_schema_by_date2 must exist
2. Database Link: @ngp11 link must be configured
3. AWR Access: DBA_HIST views accessible via link
4. Related Script: cpu_by_day.sql must be available
5. Table Structure:

CREATE TABLE IO_by_schema_by_date2 (
    PARSING_SCHEMA_NAME VARCHAR2(30),
    AWR_DATE DATE,
    TIO NUMBER,
    DIO NUMBER,
    XPCT NUMBER
);

Process Flow

1. Cleanup: Deletes the most recent date (using CPU table’s max date)
2. Calculate Total: Computes total daily disk reads across all schemas
3. Schema Breakdown: Calculates disk reads per schema
4. Percentage: Computes each schema’s percentage of total I/O
5. Storage: Inserts results into tracking table
6. CPU Processing: Executes cpu_by_day.sql for combined analysis

Output Columns

• PARSING_SCHEMA_NAME: Schema that parsed the SQL
• T_DATE: Date of measurement
• TIO: Total disk reads for schema
• DIO: Daily total disk reads for database
• XPCT: Percentage of total I/O used by schema

Configuration Notes

Database Link

• Currently uses @ngp11
• Modify to match your environment

Parallel Processing

• Uses extensive parallel hints for performance
• Adjust degree based on system capacity

Table Naming

• Uses IO_by_schema_by_date2 (note the “2”)
• Ensure consistency with your naming convention

Usage

After fixing the syntax error:

SQL> @io_by_day.SQL

Query Results

To view the collected data:

-- Top I/O consumers by day
SELECT awr_date, parsing_schema_name,
       ROUND(xpct * 100, 2) as io_percent,
       tio as disk_reads
FROM io_by_schema_by_date2
WHERE awr_date >= SYSDATE - 30
ORDER BY awr_date DESC, xpct DESC;

-- Schema I/O trend
SELECT parsing_schema_name,
       MIN(xpct * 100) min_pct,
       AVG(xpct * 100) avg_pct,
       MAX(xpct * 100) max_pct,
       SUM(tio) total_reads
FROM io_by_schema_by_date2
WHERE awr_date >= SYSDATE - 30
GROUP BY parsing_schema_name
ORDER BY avg_pct DESC;

Combined Analysis

Since this script calls cpu_by_day.sql, you can correlate CPU and I/O:

-- CPU vs I/O correlation
SELECT c.parsing_schema_name,
       c.awr_date,
       ROUND(c.xpct * 100, 2) cpu_pct,
       ROUND(i.xpct * 100, 2) io_pct
FROM cpu_by_schema_by_date c
JOIN io_by_schema_by_date2 i
  ON c.parsing_schema_name = i.parsing_schema_name
  AND c.awr_date = i.awr_date
WHERE c.awr_date >= SYSDATE - 7
ORDER BY c.awr_date DESC, c.xpct DESC;

Maintenance Considerations

1. Data Retention: Implement purge policy for old data
2. Index Strategy: Index on AWR_DATE and PARSING_SCHEMA_NAME
3. Statistics: Maintain current optimizer statistics
4. Error Handling: Add exception handling for link failures

Related Scripts

• cpu_by_day.sql: CPU consumption tracking
• random_io.sql: Random I/O pattern analysis
• gvio.sql: Real-time I/O statistics