19.12 - 4 node RAC
Getting huge spike in morning between around 4am - 9am
SQL causing it is multiple runs of the following. The table in question is about 4gb in size, not fragmented or anything.
select dbms_rowid.rowid_type(rowid) from <owner>.<table_name> where rownum=1;
Now, whats running it is the Oracle server itself and the process is multiple W000 worker slaves which are spawned from the KTSJ process. (can see from gv$active_Session_history).
Now, for those unaware, KTSJ is the process run by Space Management Coordinator process (SMCO}.
We can see from gv$sql, the SQL is executing hundreds of times in the morning, and chewing CPU, taking .5gb IO each time. Anyone any idea what could be causing this to run? Its far and away the heaviest hitting SQL for us in a very busy system otherwise.
edit: Some more info if needed.
Its only 1 table that is affected by this, its the same table each time. Other tables are being run by the process with same SQL type do have the same SQL but have massively less load on IO.
The table is basic compressed and partitioned on a date column.
Theres no clobs in this table
Theres about 200k row updates each morning on this which is tiny for our system. We have 100k+ tables with many in multi 100gb size with multi million row updates each morning.
Theres no scheduler jobs for this table.
Its not called by any procs/packages/views or mviews.
What is the function of SMCO background process
SMCO coordinates the following space management tasks. It performs proactive space allocation and space reclamation. It dynamically spawns secondary processes (Wnnn) to implement the task.
Tablespace-level space (Extent) pre-allocation.
Pre-allocation here refers to datafile extention, Datafile extension happens when a space request (extent allocation) operation generally triggered via Insert / loading to a segment does not find contiguous space in the tablespace, the session will extend the file by the next increment set for the datafile and will continue with the space request or Extent allocation.
For SMCO to autoextend a datafile, the AUTOEXTEND should be set to ON for the datafile. SMCO decides to expand the tablespace based on history, extension is split evenly across all datafiles in the tablespace which have not reached their maxsize and are still limited to 10% of the full tablespace size in one hourly SMCO wakeup.
(Full tablespace size = Sum of datafile sizes at any given instance of time.
Now I checked the tablespace the table lives in, 5 datafiles, autoextend on next 100M up to max up to max of 16G. This is a standard setting on all our tablespaces. Ive increased the setting on all files to be next 500m in an effort to reduce but still cant see why this would be happening.