Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

KTSJ process running select dbms_rowid.rowid_type(rowid) causing huge load

oraLaroJan 27 2023 — edited Jan 27 2023

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.

Comments

807574
I would need to know:

1. Are these messages SENT to users outside your server? Is so, then the test -rewrite you give is totally useless.

2. Have you confirmed that the mails being sent by your users is properly addressed to live domains? If not, your users should get these.
807574
Yes,

the mail addressed to live domains outside my server. I't seems that the server is delaying the delivery of email, I had several complains from my users that they send an email outside and the receipient received it after five hour.


Thanks Jay
807574
Ok, so. . .

Is it to many domains, or only a few?

Have you looked at the error messages added to the message files themselves in the outbound queue? You may very well get information for the reason for the delay, there.

In almost all cases, messages that cannot be delivered to external domains is a problem for the external domain, not for iMS.
1 - 3

Post Details

Added on Jan 27 2023
3 comments
5,949 views