Do Active Oracle Database Background Processes Have A SQL_ID?
Do Oracle Database BACKGROUND processes play a part in a blank, empty or null SQL_ID saga? If so, it would help us separate foreground (application) activity from the Oracle background process activity.
This would help us more quickly zero in on solid performance improving solutions! In a nutshell, reduce distractions.
In this post I reference an entire day of ASH/AWR data from a large production Oracle Database system. I also purposely used my free BloodHound Toolkit and ordered script execution so you can do the same thing I do!
What I learned was fascinating and helped me clarify why so many active sessions do NOT have a SQL_ID.
Get Up To Speed Quickly
Over the past few weeks, I have done a lot of research related to active Oracle Database sessions with a blank, empty or null SQL_ID. To ensure you didn't miss anything, here is a summary:
- Whenever I see a log file sync wait event, while I don't expect to see a SQL_ID and definitely not the DML SQL_ID(s), I do know that a commit is taking place.
- Usually Log file Sync waiting (committing) sessions have a blank SQL_ID
- Usually CPU consuming committing sessions have a blank SQL_ID,
- ... but not always.
- PLSQL anonymous blocks do NOT have a blank sql_id, that is, they always have a SQL_ID.
- Procedures do NOT have a blank sql_id when wrapped by plsql. But the procedure itself, as shown in a library cache dump, does not have a SQL_ID.
OK, let's get started!
CPU Sessions With A Blank SQL_ID
First, let's look at the SQL_IDs for all the CPU consuming active Oracle sessions. Using my BloodHound toolkit, here is how to do this.
SQL> @bhtopcolcpu sql_id sample_id %
Database: prod40 15-Jul 10:25am
Report: bhtopcolcpu.sql BloodHound by OraPub, Inc. Page 1
When On CPU Show Top sql_id - ASH Sample Based Analysis Report
% Samples
SQL_ID ON CPU
-------------------- ---------
11.42
4tdpuxb82b7ju 6.08
048znjmq3uvs9 5.50
chpa6821mm5h6 3.40
...
There were 54284 ON CPU samples available and used for this analysis.
Only sessions with % ON CPU >= 0% of sample ON CPU are displayed.
This report shows the top op_20140801n3ALL ON CPU BH sql_id(s) samples
when the sample_id was like %
This tells me that of the 54284 active "ON CPU" sessions, 11.42%, that is 6199 sessions, have a blank SQL_ID.
CPU: Categorizing By Foreground And Background Process
Now I want to split the "ON CPU" sessions into two categories: background and foreground processes.
SQL> @bhtopcolcpu sql_id session_type BACKGROUND
Database: prod40 15-Jul 10:25am
Report: bhtopcolcpu.sql BloodHound by OraPub, Inc. Page 1
When On CPU Show Top sql_id - ASH Sample Based Analysis Report
% Samples
SQL_ID ON CPU
-------------------- ---------
99.29
6ajkhukk78nsr 0.20
7qjhf5dzmazsr 0.16
3c1kubcdjnppq 0.04
...
There were 4505 ON CPU samples available and used for this analysis.
Only sessions with % ON CPU >= 0% of sample ON CPU are displayed.
This report shows the top op_20140801n3ALL ON CPU BH sql_id(s) samples
when the session_type was like BACKGROUND%
The reports show that of 4505 active "ON CPU" BACKGROUND process sessions, 99.29%, that is 4473 sessions, have a blank SQL_ID. Essentially, every background process that is consuming CPU has a blank SQL_ID. But why? Patience...
Let's shift our focus to the CPU consuming foreground processes.
SQL> @bhtopcolcpu sql_id session_type FOREGROUND
Database: prod40 15-Jul 10:25am
Report: bhtopcolcpu.sql BloodHound by OraPub, Inc. Page 1
When On CPU Show Top sql_id - ASH Sample Based Analysis Report
% Samples
SQL_ID ON CPU
-------------------- ---------
4tdpuxb82b7ju 6.63
048znjmq3uvs9 6.00
chpa6821mm5h6 3.70
3.47
1r0xtxsud9g2m 3.40
cbwt5v4r6xyj0 3.27
gcb5z9gj5m8wv 3.10
...
There were 49779 ON CPU samples available and used for this analysis.
Only sessions with % ON CPU >= 0% of sample ON CPU are displayed.
This report shows the top op_20140801n3ALL ON CPU BH sql_id(s) samples
when the session_type was like FOREGROUND%
So, of the 49779 active "ON CPU" FOREGROUND process sessions, only 3.47%, that is 1727 sessions, have a blank SQL_ID. Therefore, most of the active CPU consuming foreground processes have an associated SQL_ID.
CPU: Checking If The Numbers Add Up
The first report above is about ALL Oracle CPU consuming process. The second and third report show the breakdown of these processes into two categories; background and foreground process. I had better check to see if the numbers match. Otherwise, there could be a problem with my query based on dba_hist_active_sess_history.
Foreground CPU blank sql_id sessions 1727 report
Background CPU blank sql_id sessions 4473 report
---------------------------------------------------
All CPU blank sql_id sessions 6200 derived
All CPU blank sql_id session 6199 report
Difference 1 difference
Nice! First, the numbers add up... just a rounding error. More importantly, this means that when a background process session was caught in the act (that is, it was sampled) of consuming CPU, 72% of the samples had a blank SQL_ID! This means that:
most of active CPU consuming sessions with a blank SQL_ID were background processes!
Waiting Sessions With A Blank SQL_ID
Now let's look at the SQL_IDs for all the waiting active Oracle sessions. Using my BloodHound toolkit, here is how to do this.
SQL> @bhtopcolwait sql_id event %
Database: prod40 15-Jul 10:25am
Report: bhtopcolwait.sql BloodHound by OraPub, Inc. Page 1
When Waiting Show Top sql_id - ASH Sample Based Analysis Report
% Samples
SQL_ID Waited
-------------------- ---------
11.52
8sksw60nkukuf 3.30
9k0avzfr3f1jc 1.63
00supunq9ynb3 1.54
...
There were 33094 WAITING samples available and used for this analysis.
Only sessions with % wait >= 0% of sample waits are displayed.
This report shows the top op_20140801n3ALL sql_id(s) by wait samples
when the event was like %
This tells me that of the 33094 WAITING active Oracle sessions, 11.52%, that is 3812 sessions, had a blank SQL_ID. In other words, around 90% of Oracle sessions waiting on a non-idle event had an associated SQL_ID.
WAIT: Categorizing By Foreground And Background Process
Like I did with the active CPU consuming sessions, I want to break-down the waiting Oracle sessions into two categories; foreground and background processes. Here we go...
SQL> @bhtopcolwait sql_id session_type BACKGROUND
Database: prod40 15-Jul 10:25am
Report: bhtopcolwait.sql BloodHound by OraPub, Inc. Page 1
When Waiting Show Top sql_id - ASH Sample Based Analysis Report
% Samples
SQL_ID Waited
-------------------- ---------
98.14
6ajkhukk78nsr 0.74
1cq3qr774cu45 0.18
...
There were 2850 WAITING samples available and used for this analysis.
Only sessions with % wait >= 0% of sample waits are displayed.
This report shows the top op_20140801n3ALL sql_id(s) by wait samples
when the session_type was like BACKGROUND
The AWR ASH data shows that of the 2850 WAITING active background sessions, 98.14%, that is 2797 sessions have a blank SQL_ID. Wow! That's incredible! Nearly all of the actively waiting Oracle background processes have no SQL_ID! Why is that? Patience...
Let's shift our focus to the actively waiting FOREGROUND processes.
SQL> @bhtopcolwait sql_id session_type FOREGROUND
Database: prod40 15-Jul 10:26am
Report: bhtopcolwait.sql BloodHound by OraPub, Inc. Page 1
When Waiting Show Top sql_id - ASH Sample Based Analysis Report
% Samples
SQL_ID Waited
-------------------- ---------
8sksw60nkukuf 3.61
3.35
9k0avzfr3f1jc 1.79
00supunq9ynb3 1.68
brnbr9b6128ky 1.57
...
There were 30244 WAITING samples available and used for this analysis.
Only sessions with % wait >= 0% of sample waits are displayed.
This report shows the top op_20140801n3ALL sql_id(s) by wait samples
when the session_type was like FOREGROUND
Not nearly as exciting as the waiting active background processes! The above report show that of the 30244 WAITING active foreground sessions, 3.35%, that is 1013 sessions have a blank SQL_ID. Basically, most of the waiting active Oracle foreground processes have a SQL_ID.
That's good news for us, because the foreground processes primarily run our application SQL and we want to be able to identify the problem application SQL!
WAIT: Checking If The Numbers Add Up
The first waiting report above is about ALL the active Oracle processes waiting on a non-idle event. The second and third reports show the foreground process breakdown by session type: foreground or background.
It's always a good idea to check that the numbers add up. This is one way I check to ensure my reporting SQL is working as expected.
Foreground Wait blank sql_id sessions 1013 report
Background Wait blank sql_id sessions 2797 report
---------------------------------------------------
All Wait blank sql_id sessions 3810 derived
All Wait blank sql_id session 3812 report
Difference 2 difference
Nice! This means that when a session is caught in the act of waiting on a non-idle wait event (gotcha! You've been sampled!) along with a blank SQL_ID, 73% of the time it was a background process. So, when it comes to waiting sessions with a blank SQL_ID, they were usually a background process
Log File Sync Waiting Sessions With A Blank SQL_ID
When I started this "blank SQL_ID" quest, I noticed 100% of my log file sync waiting Oracle sessions had no SQL_ID. Now let's look if those sessions are either foreground or background processes. Here how to find out.
SQL> @bhtopcolwait sql_id,session_type event log%file%sync
Database: prod40 15-Jul 10:26am
Report: bhtopcolwait.sql BloodHound by OraPub, Inc. Page 1
When Waiting Show Top sql_id,session_type - ASH Sample Based Analysis Report
% Samples
SQL_ID SESSION_TY Waited
-------------------- ---------- ---------
FOREGROUND 100.00
There were 938 WAITING samples available and used for this analysis.
Only sessions with % wait >= 0% of sample waits are displayed.
This report shows the top op_20140801n3ALL sql_id,session_type(s) by wait samples
when the event was like log%file%sync
Very clear indeed! The above report shows that 100% of the waiting log file sync sessions (938 sessions) with a blank SQL_ID were foreground process. I would definitely not expect that, especially in more DML intensive workload. But on this large production Oracle system over this 24 hour period, that's the way it was.
Why No Background Process SQL_ID?
First of all, a background process can definately have an associated SQL_ID. Using the same data I've been using in this article, you can see the SMON background process does have non-blank SQL_IDs.
SQL> @bhtopcolcpu SQL_ID program %SMON%
Database: prod40 15-Jul 02:30pm
Report: bhtopcolcpu.sql BloodHound by OraPub, Inc. Page 1
When On CPU Show Top SQL_ID - ASH Sample Based Analysis Report
% Samples
SQL_ID ON CPU
-------------------- ---------
56.67
bdntyxtax2smq 10.00
b2gnxm5z6r51n 6.67
3c1kubcdjnppq 6.67
350f5yrnnmshs 6.67
0bfsw4s3fbj2p 3.33
...
There were 30 ON CPU samples available and used for this analysis.
Only sessions with % ON CPU >= 0% of sample ON CPU are displayed.
This report shows the top op_20140801n3ALL ON CPU BH SQL_ID(s) samples
when the program was like %SMON%
Based solely on the above report, it's pretty obvious (56.67% obvious) that SMON may or not have an associated SQL_ID when consuming CPU. But why would a background process NOT have a SQL_ID?
A background process can make calls directly to Oracle kernel functions. A direct kernel function call does not require a SQL statement.
But, a kernel function call will require some CPU and perhaps even some wait time.
I think it's interesting to know that Oracle Corporation determines when an Oracle process is active and non-active. Plus ASH by default does not record Oracle process activity unless it is deemed worthy to report.
For example, when a session is waiting on an idle wait, normally the activity is not recorded by ASH. It makes sense, when you think about it. Just imagine all the worthless ASH rows that would be recorded along with all the associated CPU cycles consumed and disk space required.
Setting the Oracle instance parameter, _ash_sample_all to TRUE should cause Oracle to record both foreground and background session activity, including when a session is waiting on an idle wait event. I'm not recommending this, simply making you aware it can be done. Finally, I have not personally tested this parameter.
Making Sense Of It All
This post has lots of good information in it, but it's scattered throughout the post. Let me summarize it for you.
Both foreground and background processes had blank SQL_IDs, however...
- With a blank SQL_ID and waiting, 73% of the sessions were background processes
- With a blank SQL_ID and consuming CPU, 72% of the session were background processes
- All log file sync waiting sessions were foreground processes
- All log file sync waiting sessions had a blank SQL_ID
Distilling this further:
- Around 72% of blank SQL_IDs were related to background process activity
- Log file sync waiting sessions were always foreground processes and always had a blank SQL_ID
Use Your Words!
In English. When I saw a blank SQL_ID, while it can be associated with either a foreground or background process, it was very likely (72%) to be a background process. When a foreground process was committing and waiting on a log file sync event, its SQL_ID was always blank.
Caution: My Data. Not Yours.
Remember, my above summarizations are a reflection of my data only. That is, a single day on an large production OLTP focused system. If you do what I did above, which I hope you do, your results may vary.
Be very careful about taking my results and saying something like, "Log file sync waiting sessions will ALWAYS have a blank SQL_ID!" But don't unnecessarily put yourself in a corner.
I hope you really enjoyed this article and got a lot out of it. For me, it really makes all this empty SQL_ID work worth it.
All the best to you in your work.
Craig.
Start my FREE 18 lesson Machine Learning For Oracle Professionals E-Course here.
Craig Shallahamer is a long time Oracle DBA who specializes in predictive analytics, machine learning and Oracle performance tuning. Craig is a performance researcher and blogger, consultant, author of two books, an enthusiastic conference speaker a passionate teacher and an Oracle ACE Director. More about Craig Shallahamer...
If you have any questions or comments, feel free to email me directly at craig at orapub.com.
 |
 |
 |
|||
| How To Solve A Similar SQL Statement Parsing Issue | How To Change The Priority Of Oracle Database Background Processes | Speed Sometimes Means Changing The Rules | |||
