Parallel Statement Queuing

Parallel Statement Queuing was introduced in 11.2. Instead of downgrading the requested degree of parallelism (DOP) Oracle will now put your session in a queue until the requested number of slaves become available. Your session would wait for event 'resmgr: pq queued' while waiting in the queue. Like other queues, this is a first in first out queue.

A statement is considered for parallel statement queuing when parallel_degree_policy is set to AUTO. This parameter controls other stuffs as well such as In Memory Parallel Execution and it also turns on auto DOP calculations. So, Oracle will decide for you the degree of parallelism based on the segment size and parameter configurations. 

Let's begin with a test case. I'm running Oracle 19c on an Exadata X7 Machine. My parallel related parameters are 

parallel_max_servers         : 1713

parallel_servers_target       : 96 

parallel_degree_limit         : 128

parallel_degree_policy       : AUTO

Parallel_max_servers specifies the maximum number of parallel execution process for an instance. But this parameter is not the upper limit for statement queuing.

 A SQL would wait in the queue when

sum of currently active parallel slaves + requested number of slaves exceeds parallel_servers_target.

In my system, parallel_servers_target is set to 96. Imagine I have a query that is currently running with 64 PX slaves. And then another query requests for 64 slaves. Since 64+64 would exceed the parallel_servers_target value of 96, my second query would wait in the queue. As soon as the first query finishes, the second one would come out of the queue and begin execution. 

This is why it is important to set the parameter parallel_servers_target to an optimal value. Why does Oracle doesn't consider the parallel max servers instead ? It is because Oracle wants you have two virtual pools of parallel slaves. One pool would serve the queries coming out of auto DOP while others coming from Manual DOP. We will see it shortly. 

Let's begin with a test case. I've a table T1 which is query high compressed and the table size is 2.5 TB.

From 1st Session I run this query on T1, Oracle auto calculates the DOP to 64 and SQL begins execution. And while this query is running I open three more sessions and run the same query again

SQL> select /* query 1 */ AVG(COL1) + sum(COL2) from T1

-- SQL Monitor Global Stats section 

Global Stats

============================================================================================================================================

| Elapsed | Queuing |   Cpu   |    IO    |  Other   | Fetch | Buffer | Read | Read  | Uncompressed |  Offload   |    Offload     |  Cell   |

| Time(s) | Time(s) | Time(s) | Waits(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |    Bytes     | Elig Bytes | Returned Bytes | Offload |

============================================================================================================================================

|    1535 |    0.00 |     368 |     1166 |     0.18 |     1 |   122M |   2M |   2TB |          4TB |        2TB |          130GB | 219.01% |

============================================================================================================================================

Parallel Execution Details (DOP=64 , Servers Allocated=64)

As you can see, Oracle allocates 64 PX slaves. There is no queuing time here because this is the first SQL I'm executing and we haven't yet exhausted the parallel servers target limit of 96.

Now comes the second query, Oracle computes the DOP to be 64 and since this would exceed the target of 96, Oracle places this SQL in the queue. You can see that the queue time for this SQL is 21 seconds.

SQL> select /* query 2 */ AVG(COL1) + sum(COL2) from T1

Global Stats

============================================================================================================================================

| Elapsed | Queuing |   Cpu   |    IO    |  Other   | Fetch | Buffer | Read | Read  | Uncompressed |  Offload   |    Offload     |  Cell   |

| Time(s) | Time(s) | Time(s) | Waits(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |    Bytes     | Elig Bytes | Returned Bytes | Offload |

============================================================================================================================================

|    1769 |      21 |     372 |     1375 |     0.75 |     1 |   122M |   2M |   2TB |          4TB |        2TB |          130GB | 219.01% |

============================================================================================================================================

Parallel Execution Details (DOP=64 , Servers Allocated=64)

Now comes my third query. This is interesting, I execute this query with parallel 4 hint. Even though we have hardcoded the DOP to 4, Oracle still considers this as an Auto DOP SQL and this is eligible for parallel statement queuing. 

Currently I have 64 slaves active ( first query), second query is in the queue waiting for 64. Currently we have 96-64=  32 free PX slaves. With 32 free slaves, I' requesting just 4. Let's execute the query and see what happens

SQL> select /*+ PARALLEL(4) query 3 */ AVG(COL1) + sum(COL2) from T1

Global Stats

=========================================================================================================================

| Elapsed | Queuing |   Cpu   |    IO    | Buffer | Read | Read  | Uncompressed |  Offload   |    Offload     |  Cell   |

| Time(s) | Time(s) | Time(s) | Waits(s) |  Gets  | Reqs | Bytes |    Bytes     | Elig Bytes | Returned Bytes | Offload |

=========================================================================================================================

|     401 |    8.37 |     319 |       74 |   120M |   2M |   2TB |          4TB |        2TB |          128GB | 219.11% |

=========================================================================================================================

Parallel Execution Details (DOP=4 , Servers Allocated=4)

This SQL too was placed in the queue. Queuing time was 8.37 seconds. So, why was my SQL placed in the queue inspite of the fact that I had 32 PX slaves free and I requested just 4. This is because, our query 3 is behind query 2 in the queue. As I said, its a first in first out queue, so query 2 requesting for 64 PX slaves would get the first priority. Sessions requesting for fewer DOP will not move ahead in the queue. 

Now let's run another query. This time with NO_STATEMENT_QUEUING hint. And see what happens.

SQL> select /*+ parallel(4) NO_STATEMENT_QUEUING query 4 */ AVG(COL1) + sum(COL2) from T1

Global Stats

===============================================================================================================

| Elapsed |   Cpu   |    IO    | Buffer | Read | Read  | Uncompressed |  Offload   |    Offload     |  Cell   |

| Time(s) | Time(s) | Waits(s) |  Gets  | Reqs | Bytes |    Bytes     | Elig Bytes | Returned Bytes | Offload |

===============================================================================================================

|      75 |      37 |       38 |    23M | 365K | 356GB |        377GB |      356GB |            5GB | 104.51% |

===============================================================================================================

Parallel Execution Details (DOP=4 , Servers Allocated=4)

So, no queuing time here. This SQL was never placed in the queue. Because we have used the no_statement_queuing hint Oracle does not consider parallel server target as the ceiling value. In fact, now it considers the parallel_max_servers which we have set to 1713. 

Basically, Oracle has two separate pools. One pool of 96 PX slaves - this pool is reserved for statements coming from AUTO DOP. 

And other pool of 1713-96 = 16717 PX slaves - this pool is reserved for statements coming from manual DOP or for statements coming with no_statement_queuing hint.

CONCLUSION 

We need to understand about these virtual pools. If all of my statements are coming thru auto DOP then we must set parallel_servers_target exactly equal to that of parallel_max_servers. And if I have a mixed workload where SQLs come via both auto and manual DOP, then it is best advised to set parallel_servers_target to 50% of max_parallel_servers.

AWR architecture in a PDB CDB environment

In the previous post we discussed how data sharing works across the PDBs. In this post we will understand the AWR framework works in the multitenant environment. Note that Oracle has made a lot changes between 12.1 and 12.2 and this blog focusses on the 12.2 architecture. Since Oracle 12.2, we can have two different AWR snapshots.

1. AWR snapshot taken at the CDB level. The underlying tables such as WRH$_SQLSTAT will contain data related to root and all of the PDBs.

2. AWR snapshot taken at the PDB level. The underlying tables such as WRH$_SQLSTAT will contain only the current PDBs data.

AWR snapshot at PDB level is disabled by default. To enable it, we need to modify the parameter AWR_PDB_AUTO_FLUSH_ENABLED to TRUE. This parameter is modifiable at PDB level. Since a PDB level and a CDB level snapshot are two different snapshots - it is possible to configure different retention and frequency settings for them. Since we have two different snapshots, this implies that some of the data is redundant, it is stored both in the PDB and the CDB. We will see it shortly.

Underlying tables such as WRH$_SQLSTAT or WRH$_SNAPSHOT are Metadata Linked tables. This means that the dictionary is in the root container but each PDB has its own private data segment. You can consider these as separate tables in each of the PDBs.

Now Oracle exposes this table in the form of views. In earlier versions we had  the DBA_HIST_* views such as DBA_HIST_SQLSTAT or DBA_HIST_SNAPSHOT. But from 12.2 these tables are exposed in different ways.

As you can see, underlying table WRH$_SQLSTAT is exposed via three different views.

AWR_ROOT_SQLSTAT - This is a data link view. If you select from this view you get the data from the CDB's AWR repository.

AWR_PDB_SQLSTAT - This is a metadata link view. If you select from this view you get the data from the current PDB

AWR_CDB_SQLSTAT - This is an extended data link view. If you select from this view you get the data from both the current PDB and the CDB.

DBA_HIST_SQLSTAT is just a view that points to AWR_CDB_SQLSTAT

This is just one example, all other AWR related tables are exposed via similar views.
If we query AWR_ROOT_SQLSTAT, being a data link view, it picks up data from the root container. Remember that CDB's tables contain data from all the PDBs. And if you read this view from a PDB, Oracle is intelligent enough to automatically apply the filter on CON_ID .

SQL> alter session set container=PDB1 ;

SQL> explain plan for
           select * from AWR_ROOT_SQLSTAT ;

Plan hash value: 2631641967

-------------------------------------------------------------------------------------
| Id  | Operation        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |                  |   100 |   209K|     1 (100)| 00:00:01 |
|*  1 |  DATA LINK FULL  | AWR_ROOT_SQLSTAT |   100 |   209K|     1 (100)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CON_ID"=0 OR "CON_ID"=3)

As you can see, since I queried this view from a PDB, Oracle has applied the CON_ID filter.
Therefore, AWR_ROOT_SQLSTAT gets the data from the CDB's WRH$_SQLSTAT after applying the con_id filter. It brings the PDB's data along with the CDB's data itself.

And when we query AWR_CDB_SQLSTAT -since this is an extended data link view it queries the data from the CDB and the current PDB. And since CDB snapshots has the PDB data, you will see duplicate rows here. A SQL will show up twice in this view. One coming from CDB snapshots and other coming from the PDB snapshot. ( This is why it isn't a good idea to collect a PDB level snapshot). If you have a customized script on DBA_HIST_SQLSTAT you may have to modify the script and query AWR_PDB_SQLSTAT instead.

Now that we have covered how the AWR data is stored internally, let us look at the options we have to generate the AWR report.

From a PDB, if you run awrrpt, Oracle asks you to choose the location of AWR data
We get to choose the location of AWR data.  This is because, as explained above, we have different AWR repository, one in the CDB and others in each of the PDBs.
If you choose AWR_ROOT - Oracle then queries AWR_ROOT_* views. Pulls the data from the CDB after applying the con_id filters.

If you choose AWR_PDB - Oracle then queries AWR_PDB_* views. Pulls the data from the current PDBs AWR data. This option is available only when PDB level snapshots are available.

And if you run an AWR report from CDB, you don't get to choose the location. Oracle picks up the data from the root itself using the AWR_PDB_* views.  AWR from CDB is a consolidated report. It displays the information about the each of the PDBs. Since this is a mutitenant architecture we have just one instance, some information such as background wait events are displayed only in the CDB level AWR report.

Conclusion :

PDB level snapshot is clearly an overhead. And this causes same data to be stored in two different places. Probably this is the reason why Oracle chose to disable this by default.
My recommendation would be to keep it disabled. CDB level AWR snaps are enough to get us the diagnostic data. And we can generate the report from either the CDB or the PDB based on what you need to investigate.