Skip to Main Content
  • Questions
  • DBMS_SYSTEM.ksdwrt affecting the database operations.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Saivishnu.

Asked: July 25, 2020 - 11:28 am UTC

Answered by: Connor McDonald - Last updated: July 27, 2020 - 2:51 am UTC

Category: Database Development - Version: 11.2.0.2.0

Viewed 100+ times

You Asked

Hi Team,
Recently we faced performance degradation situation which impacted overall operations of application.
From awr report, a proc is taking more than 3 or 4 seconds to complete. Lets call that proc 'A'.
In addmrpt, we are getting a statement similar to one given below:
Tune the entry point PL/SQL 'A' of type
"PROCEDURE". Refer to the PL/SQL documentation for
addition information.
Rationale
7821 seconds spent in executing PL/SQL "SYS.DBMS_SYSTEM.KSDWRT" of type
"PACKAGE".

We are focusing on proc 'A' because performance degradation happens when this proc is called on a regular basis (every one or two seconds).
But this proc is spending almost all of its time in executing SYS.DBMS_SYSTEM.KSDWRT. We have inferred this from awr and addm reports.

Since SYS.DBMS_SYSTEM.KSDWRT is undocumented, we are not getting any information on how this process is working internally.

My doubts are as follows:

1) How does SYS.DBMS_SYSTEM.KSDWRT manage to access the alert.log?
2) Is it using any oracle background process to access this log?
3) Can multipe sessions write to log concurrently or a wait occur there?
4) Does writing to log impact other DML operations?

and we said...

Is that an Oracle PLSQL routine that is calling DBMS_SYSTEM? or is that one of your own PLSQL routines ?

If is it the latter, then ...well.... you are sort of on your own here, because its an undocumented package, so it comes with all the good and the bad of taking that risk.

But writing to the alert log is inherently not a hugely scalable thing because we open the file, write it, and then close it every time. That's why you can open it at any time under Windows and not get the familiar "this file is in use" error.

Bottom line - writing to alert log should not be part of your day to day activities, and if something in the Oracle kernel is doing it, then its probably time to log a support call.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database