High LIO (Logical I/O) And CPU Consumption On TS$ When Using A "tablespace group" As TEMP TS
(Doc ID 1469347.1)
Last updated on JANUARY 30, 2022
Applies to:
Oracle Database - Enterprise Edition - Version 11.1.0.6 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
The following SQL running 2,190,042 times/ day and becoming one of the largest LIO (Logical I/O) consumers on this Database
"select ts# from ts$ where dflmaxext =:1 and bitand(flags, 1024) =1024 and online$=1"
To check this behavior, you can get a 10046 trace file on the process ID
OR
From AWR reports, you could have this query in the top of "SQL ordered by Gets", "SQL ordered by Executions" or SQL ordered by Parse Calls entries:
SQL ordered by Parse Calls
| Parse Calls | Executions | % Total Parses | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|
| 263,831 | 263,831 | 19.74 | au7f6hpnhxpkx |
select ts# from ts$ where dflm... |
SQL ordered by Executions
| Executions | Rows Processed | Rows per Exec | CPU per Exec (s) | Elap per Exec (s) | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|---|---|
| 263,831 | 263,911 | 1.00 | 0.00 | 0.00 | au7f6hpnhxpkx | select ts# from ts$ where dflm... |
SQL ordered by Gets
| Buffer Gets | Executions | Gets per Exec | %Total | CPU Time (s) | Elapsed Time (s) | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|---|---|---|
| 76,255,959 | 263,831 | 289.03 | 22.46 | 115.57 | 119.99 | au7f6hpnhxpkx | select ts# from ts$ where dflm... |
Changes
Temporary Tablespace group being used
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| SQL ordered by Parse Calls |
| SQL ordered by Executions |
| SQL ordered by Gets |
| Changes |
| Cause |
| Solution |
| References |