Thanks for the question, Saivishnu.
Asked: July 25, 2020 - 11:28 am UTC
Last updated: July 27, 2020 - 2:51 am UTC
Version: 11.2.0.2.0
Viewed 1000+ 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 Connor 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.
Is this answer out of date? If it is, please let us know via a Comment