Do Active Oracle Database Background Processes Have A SQL_ID?

Posted on 21-July-2015 by Craig Shallahamer, craig@orapub.com

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:

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...

Distilling this further:

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.

If you have any questions or comments, feel free to email me directly at craig at orapub.com.

Oracle Database Library Cache Visualization Tool: How To Use It Oracle Database Performance Analysis: Total Time vs Bits of Time OracleDatabase Parallelism Introduces Limits-Part 3