High LIO (Logical I/O) And CPU Consumption On TS$ When Using A "tablespace group" As TEMP TS
Last updated on DECEMBER 15, 2016
Applies to:Oracle Database - Enterprise Edition - Version 22.214.171.124 and later
Information in this document applies to any platform.
***Checked for relevance on 15-Dec-2016***
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
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|
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...|
Temporary Tablespace group being used
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms