Skip to Main Content
  • Questions
  • no Temp tablespace in AWR section "Tablespace IO Stats"

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Martina.

Asked: August 18, 2023 - 11:01 am UTC

Last updated: August 31, 2023 - 2:49 am UTC

Version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.19.0.0.0

Viewed 1000+ times

You Asked

Hello,

I am trying to investigate a performance issue for our client and need to check Temp tablespace avg rd(ms) for each temp file however TEMP01 tablespace is not included in the AWR at all.

SQL> show parameter statistics

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_statistics_level              string      TYPICAL
optimizer_adaptive_statistics        boolean     FALSE
optimizer_real_time_statistics       boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
statistics_level                     string      TYPICAL
timed_os_statistics                  integer     0
timed_statistics                     boolean     TRUE

and Connor said...

You might be impacted by the issue described in MOS 34733173.8 (there are patches)

Check that out, and if it doesn't apply, then get back to us and we'll take a fresh look.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

No temp tablespace in awr section tablespace io stats

Martina, August 29, 2023 - 8:55 am UTC

Hello, thank you for your answer. As we couldn't apply patch now execute and change only one tables level(WRH$_TEMPFILE) from ALL to TYPICAL and it is working. Now we see data for TEMP01 in AWR report in section "Tablespace IO Stats"

SQL> select TABLE_NAME,FLUSH_LEVEL_VAL FROM AWR_CDB_TABLE_SETTINGS WHERE TABLE_NAME='WRH$_DATAFILE';

TABLE_NAME FLUSH_LEVEL_VAL
------------------------------ --------------------------------
WRH$_DATAFILE TYPICAL

SQL> select TABLE_NAME,FLUSH_LEVEL_VAL FROM AWR_CDB_TABLE_SETTINGS WHERE TABLE_NAME='WRH$_FILESTATXS';

TABLE_NAME FLUSH_LEVEL_VAL
------------------------------ --------------------------------
WRH$_FILESTATXS TYPICAL

SQL> select TABLE_NAME,FLUSH_LEVEL_VAL FROM AWR_CDB_TABLE_SETTINGS WHERE TABLE_NAME='WRH$_TEMPSTATXS';

TABLE_NAME FLUSH_LEVEL_VAL
------------------------------ --------------------------------
WRH$_TEMPSTATXS TYPICAL

SQL> select TABLE_NAME,FLUSH_LEVEL_VAL FROM AWR_CDB_TABLE_SETTINGS WHERE TABLE_NAME='Tempfile Group';

TABLE_NAME FLUSH_LEVEL_VAL
------------------------------ --------------------------------
Tempfile Group TYPICAL

SQL> select TABLE_NAME,FLUSH_LEVEL_VAL FROM AWR_CDB_TABLE_SETTINGS WHERE TABLE_NAME='WRH$_TEMPFILE';

TABLE_NAME FLUSH_LEVEL_VAL
------------------------------ --------------------------------
WRH$_TEMPFILE TYPICAL

Connor McDonald
August 31, 2023 - 2:49 am UTC

Thanks for getting back to us with that info

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.