Quite recently I had to trace/profile an extremely intensive batch job to provide information to Oracle for a further analysis.
At very first moment it was obvious that this is a wrong approach and whatever we’ll get, it cannot server as evidence of performance issue that we have, as TKProf is designed to trace single SQL statement or short duration of some job, it seems this is a standard procedure when dealing with Oracle Support.
Point of this article is not to elaborate if unified approach is good for all kind of performance issues, but rather to show what you can expect as a result and provide technical evidence.
When you enable tracing, either by using AFTER LOGON trigger (old fashion) or by using services, first you need to know what are you tracing and how long you are planning to trace it.
In this case, we are trying to trace job that is running several hours, and that is running in parallel.
This is a extreme example what you should never trace.
What are the consequences of using wrong method to trace such job?
The most obvious one is 2MB/sec grow of trace files.
Second one is heavy load on database server, where the top processes are ksoftirqd.
You can easily find on Web that ksoftirqd is system internal process that enqueues interrupts that are coming from the attached devices.
When you see ksoftirqd among top processes, it is a sign that your system is under heavy interrupt load.
The following picture can support that (red are system calls that are due to the ksoftirqd activity).
Next picture is showing the same job after the tracing has been disabled.
Difference is more than a visible (green are user processes that are coming from the database).
The last issue with misusing trace mechanism for tracing heavy, long duration jobs that are running in parallel is the result itself.
In AWR report you can expect to see unusual order of Top SQL queries for example.
When you tracing, at the top will be queries that are coming through the several function calls (one procedure/package/function is calling another one that is calling the third one for example), not the queries that you will normally see when tracing is disabled.
Hopefully this article will help you to avoid similar mistakes and convince you to use proper tool to do a job.
Comments