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 DECEMBER 15, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.
***Checked for relevance on 15-Dec-2016***

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 CallsExecutions% Total ParsesSQL IdSQL ModuleSQL Text
263,831 263,831 19.74 au7f6hpnhxpkx  

select ts# from ts$ where dflm...

SQL ordered by Executions

ExecutionsRows ProcessedRows per ExecCPU per Exec (s)Elap per Exec (s)SQL IdSQL ModuleSQL Text
263,831 263,911 1.00 0.00 0.00 au7f6hpnhxpkx   select ts# from ts$ where dflm...

SQL ordered by Gets

Buffer GetsExecutionsGets per Exec%TotalCPU Time (s)Elapsed Time (s)SQL IdSQL ModuleSQL 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

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