Tales From A Lazy Fat DBA

Hi All,

In the past, I looked into a Database AWR performance report from a slow 19c where we suspected some IO issues and were looking into related metrics. “Tablespace IO Stats” was an important area and provided some important statistics like the average number of read requests per second, average read time in milliseconds, write waits, number of buffer waits, and average wait time in milliseconds for buffer waits for the tablespaces.

While looking into the report, I saw that the said section was not there and was missing for Tablespace IO stats. It appeared that the file IO statistics for Tablespaces had been disabled or were not collected. Below were the steps that you could do in such cases to get Tablespace IO stats data back in AWR reports in 19.X versions. Please run the following commands as SYS:

SQL> exec dbms_workload_repository.modify_table_settings(table_name => 'WRH$_FILESTATXS', flush_level => 'TYPICAL');
 
PL/SQL procedure successfully completed.
 
SQL> exec dbms_workload_repository.modify_table_settings(table_name => 'WRH$_DATAFILE', flush_level => 'TYPICAL');
 
PL/SQL procedure successfully completed.
 
SQL> exec dbms_workload_repository.modify_table_settings(table_name => 'Tempfile Group', flush_level => 'TYPICAL');
 
PL/SQL procedure successfully completed.
 
SQL> exec dbms_workload_repository.modify_table_settings(table_name => 'WRH$_TEMPFILE', flush_level => 'TYPICAL');
 
PL/SQL procedure successfully completed.
 
SQL> exec dbms_workload_repository.modify_table_settings(table_name => 'WRH$_TEMPSTATXS', flush_level => 'TYPICAL');
 
PL/SQL procedure successfully completed.
 
SQL>

When new AWR snapshots are generated, you will start getting Tablespace IO stats data for checking IO performance. You may have to run these commands in the PDBs also if you are generating AWR snapshots at PDB level and they are missing Tablespace IO stats data in AWR reports.

There is another way how you can force that data to the AWR, this is by using the new PL/SQL program called modify_table_settings() and that is recommended to be used to enable flushing of the tables WRH$_FILESTATXS, WRH$_DATAFILE and WRH$_TEMPSTATXS at TYPICAL or ALL depending on the flush_level of “Tempfile Group”.

Hope It Helped!
Prashant Dixit