This post is not about LOGGING/NOLOGGING table option in Oracle which generates REDO Log entries or bypasses it eventually in case of NOLOGGING. This article is about custom logging feature in order to be able to backtrack potential issues in the script or premature warning of slowing down.
The purpose of logging details about a run of your processes will help you to determine where is the bottleneck of your script and mostly even how you can tackle it.
How to log?
There are many ways of how you can log your processes. The way I do it is shared in the article about Logging package. Whether you build your own or use any script downloaded from the internet, it is essential that you log some basic information in order to be able to identify potential issues.
What to log?
These are the main information you should be after in your script:
- a unique identifier for each process
- this is very important to have a sequence to generate a unique ID for each run
- information that identifies the process
- information that identifies every step in your process
- general start time / datetime
- start and stop time of each step
- number of rows processed in each step (if applicable)
- error message for each step (in case of any)
- who run the procedure
Optional but also very recommended:
- session id (sid)
- serial number (serial#)
- scheduler job name
Both of these optional you can actually find somewhere else based on the information above but it makes it easier to have right there 🙂
I highly recommend using logging for all of your processes. You can then create a dashboard where you will monitor all jobs and their improvement or worsening over the time. Just do not overuse it because it might become a performance issue. Remember, if your process is not performing well or you are just suspicious there is something wrong, go into that procedure and use debugging techniques to identify the root cause.