Skip to Main Content
  • Questions

  • Oracle PQ processing in pluggable database

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Darren.

Asked: July 20, 2017 - 4:34 pm UTC

Last updated: July 27, 2017 - 1:21 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

The problem - I cannot get any parallel execution in a pluggable database in version 12.1.0.2.0 and hoping you can shed some light on what I am missing. Hopefully I provided enough information to get started.

It's almost like parallelism is not "enabled" for the pluggable database.

I have migrated a container only database to a database with one pluggable db and moved the application tables to the pluggable databases. Both databases are version 12.1.0.2.0 . The schemas definitions and data are identical, I moved them with datapump.

In the container only database, the query executes in parallel with no issues.

I have tried altering the session to force parallel, I have set a degree on the table, I have a parallel hint set.

I know that parallel processing is working in general, as I am able to run queries in parallel in the root container (cdb$root).

The table is partitioned by range on the TS field, TS is the top of the primary key (TS, SITE, CUST) and the data is fairly evenly distributed throughout the partitions.

The table:
Name           Null?    Type
 ----------------------------------------------------------------- -------- ------------------
 TS           NOT NULL NUMBER(15)
 SITE           NOT NULL VARCHAR2(4)
 CUST           NOT NULL NUMBER(15)
 RANK             NUMBER(7)
 COUNT             NUMBER(15)

TABLE_NAME    NUM_ROWS LAST_ANALYZED
------------------------------ ---------- ---------------
CUST    678115973 19-JUL-17

PARTITION_NAME        NUM_ROWS LAST_ANALYZED HIGH_VALUE
-------------------- ---------- --------------- --------------------
CUST_170224        0 19-JUL-17 '1487980800'
CUST_170225        20993324 19-JUL-17 '1488067200'
CUST_170226        20933754 19-JUL-17 '1488153600'
CUST_170227        21779830 19-JUL-17 '1488240000'
CUST_170228        14059138 19-JUL-17 '1488326400'
CUST_170301        22089144 19-JUL-17 '1488412800'
CUST_170302        17144928 19-JUL-17 '1488499200'
CUST_170303        22371898 19-JUL-17 '1488585600'
CUST_170304        21484837 19-JUL-17 '1488672000'
CUST_170305        21165207 19-JUL-17 '1488758400'
CUST_170306        22140324 19-JUL-17 '1488844800'
CUST_170307        22394525 19-JUL-17 '1488931200'
CUST_170308        22349191 19-JUL-17 '1489017600'
CUST_170309        22288298 19-JUL-17 '1489104000'
CUST_170310        22571513 19-JUL-17 '1489190400'
CUST_170311        23444140 19-JUL-17 '1489276800'
CUST_170312        23272062 19-JUL-17 '1489363200'
CUST_170313        23987001 19-JUL-17 '1489449600'
CUST_170314        24089762 19-JUL-17 '1489536000'
CUST_170315        25541106 19-JUL-17 '1489622400'
CUST_170316        32651962 19-JUL-17 '1489708800'
CUST_170317        32625644 19-JUL-17 '1489795200'
CUST_170318        32349277 19-JUL-17 '1489881600'
CUST_170319        32315020 19-JUL-17 '1489968000'
CUST_170320        28686996 19-JUL-17 '1490054400'
CUST_170321        22617558 19-JUL-17 '1490140800'
CUST_170322        23008672 19-JUL-17 '1490227200'
CUST_170323        23059973 19-JUL-17 '1490313600'
CUST_170324        15197689 19-JUL-17 '1490400000'
CUST_170325        21503200 19-JUL-17 '1490486400'
CUST_170711        0 19-JUL-17 '1499817600'
CUST_170712        0 19-JUL-17 '1499904000'
CUST_170713        0 19-JUL-17 '1499990400'
CUST_170714        0 19-JUL-17 '1500076800'
CUST_170715        0 19-JUL-17 '1500163200'
CUST_170716        0 19-JUL-17 '1500249600'
CUST_501231        0 19-JUL-17 '2556144000'



The query:

select /*+ parallel(cust,32)*/ site, cust, sum(count)
from voltron.cust 
where rank < '1000' group by site, cust order by site,sum(count);



The execution plan on the container-only database:

73260 rows selected.
Elapsed: 00:00:14.97

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation      | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |       |       |       | 32072 (100)|        |       |       | |      |     |
|   1 |  PX COORDINATOR      |        |       |       |       |     |        |       |       | |      |     |
|   2 |   PX SEND QC (ORDER)     | :TQ10002 |   118K|  1973K|       | 32072  (2)| 00:00:02 |       |       |  Q1,02 | P->S | QC (ORDER) |
|   3 |    SORT ORDER BY     |        |   118K|  1973K|  3837M| 32072  (2)| 00:00:02 |       |       |  Q1,02 | PCWP |     |
|   4 |     PX RECEIVE      |        |   118K|  1973K|       | 32072  (2)| 00:00:02 |       |       |  Q1,02 | PCWP |     |
|   5 |      PX SEND RANGE     | :TQ10001 |   118K|  1973K|       | 32072  (2)| 00:00:02 |       |       |  Q1,01 | P->P | RANGE     |
|   6 |       HASH GROUP BY     |        |   118K|  1973K|  3837M| 32072  (2)| 00:00:02 |       |       |  Q1,01 | PCWP |     |
|   7 |        PX RECEIVE     |        |   118K|  1973K|       | 32072  (2)| 00:00:02 |       |       |  Q1,01 | PCWP |     |
|   8 |  PX SEND HASH     | :TQ10000 |   118K|  1973K|       | 32072  (2)| 00:00:02 |       |       |  Q1,00 | P->P | HASH     |
|   9 |   HASH GROUP BY     |        |   118K|  1973K|  3837M| 32072  (2)| 00:00:02 |       |       |  Q1,00 | PCWP |     |
|  10 |    PX BLOCK ITERATOR |        |   125M|  2030M|       | 23882  (1)| 00:00:01 |     1 |    37 |  Q1,00 | PCWC |     |
|* 11 |     TABLE ACCESS FULL| CUST     |   125M|  2030M|       | 23882  (1)| 00:00:01 |     1 |    37 |  Q1,00 | PCWP |     |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  11 - access(:Z>=:Z AND :Z<=:Z)
       filter("RANK"<1000)




The execution plan in the new pluggable database:

73260 rows selected.
Elapsed: 00:01:58.97

------------------------------------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      |      |      | 1053K(100)|      |      |      |
|   1 |  SORT ORDER BY       |      |  159K| 2644K| 2855M| 1053K  (2)| 00:00:42 |      |      |
|   2 |   HASH GROUP BY       |      |  159K| 2644K| 2855M| 1053K  (2)| 00:00:42 |      |      |
|   3 |    PARTITION RANGE ALL|      |   93M| 1510M|      |  688K  (1)| 00:00:27 |    1 |   37 |
|*  4 |     TABLE ACCESS FULL | CUST |   93M| 1510M|      |  688K  (1)| 00:00:27 |    1 |   37 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("RANK"<1000)




Parallel parameters in the new database:

NAME         TYPE          VALUE
------------------------------------ --------------------------------- ------------------------------
_parallel_syspls_obey_force      boolean          TRUE
fast_start_parallel_rollback      string          LOW
parallel_adaptive_multi_user      boolean          TRUE
parallel_automatic_tuning      boolean          FALSE
parallel_degree_level       integer          100
parallel_degree_limit       string          CPU
parallel_degree_policy       string          MANUAL
parallel_execution_message_size      integer          32768
parallel_force_local       boolean          FALSE
parallel_instance_group       string
parallel_io_cap_enabled       boolean          FALSE
parallel_max_servers       integer          316
parallel_min_percent       integer          0
parallel_min_servers       integer          36
parallel_min_time_threshold      string          AUTO
parallel_server        boolean          TRUE
parallel_server_instances      integer          1
parallel_servers_target       integer          316
parallel_threads_per_cpu      integer          2
recovery_parallelism       integer          0

OSstats in the new database:

Stat Name       Value OS Stat ID 
------------------------ ---------------- ---------- --
BUSY_TIME         19,989,738    2 
FREE_MEMORY_BYTES   211,172,405,248 1011 
GLOBAL_RECEIVE_SIZE_MAX       134,217,728 2007 
GLOBAL_SEND_SIZE_MAX       134,217,728 2006 
IDLE_TIME      1,071,129,175    1 
INACTIVE_MEMORY_BYTES    24,064,081,920 1012 
IOWAIT_TIME      66,910    5 
LOAD     2   15 
NICE_TIME          21    6 
NUM_CPUS          72    0 
NUM_CPU_CORES          36   16 
NUM_CPU_SOCKETS    2   17 
PHYSICAL_MEMORY_BYTES   540,663,746,560 1008 
RSRC_MGR_CPU_WAIT_TIME     10,082   14 
SWAP_FREE_BYTES     21,474,832,384 1013 
SYS_TIME   3,278,369    4 
TCP_RECEIVE_SIZE_DEFAULT    87,380 2004 
TCP_RECEIVE_SIZE_MAX       134,217,728 2005 
TCP_RECEIVE_SIZE_MIN      4,096 2003 
TCP_SEND_SIZE_DEFAULT     65,536 2001 
TCP_SEND_SIZE_MAX       134,217,728 2002 
TCP_SEND_SIZE_MIN      4,096 2000 
USER_TIME         16,704,548    3 
VM_IN_BYTES    0 1009 
VM_OUT_BYTES    0 1010


with LiveSQL Test Case:
https://livesql.oracle.com/apex/f?p=590:43:112319230253045::NO:RP:P43_ID:112668385240344034960002483897812779134&success_msg=TmV3IFNjcmlwdCAmcXVvdDtTZXNzaW9uIDAxJnF1b3Q7IHNhdmVkIHdpdGggMyBz%7B%7DdGF0ZW1lbnRz%2FHjeuZMYAYhhN6HfL3vKmst50nmSxJvzpr-5KZZD2AMVTo

and Connor said...

Check to see if there are Resource Manager limits in play here

select plan, profile, pluggable_database, parallel_server_limit from DBA_CDB_RSRC_PLAN_DIRECTIVES;

(The plan could be attached to a profile or a specific pluggable)

And perhaps check if cpu_count has been set in the pluggable to limit cpu consumption.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

cpu_count was the issue but....

Darren Collins, July 26, 2017 - 4:16 pm UTC

This is on a bare metal service and for some reason cpu_count was specifically set to 4 when the db was created.

I had verified there were no resource plan directives, and actually had created a resource plan to see if that would resolve the issue but it did not.

Oracle sees this on the server:

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> @&s\osstats
NUM_CPUS 72 Number of active CPUs
NUM_CPU_CORES 36 Number of CPU cores
NUM_CPU_SOCKETS 2 Number of physical CPU sockets

SQL> show parameter cpu_count
NAME VALUE
------------------------------------ ---------------
cpu_count 4


You cannot set cpu_count at the pluggable level, so I altered it at the container level to 72 to match what the db sees as the number of cpus.

This allowed the pluggable to start executing in parallel. I suspect (and I may test it later) that if I had executed parallel of say "2" that it may have executed the query in parallel since it was lower than the cpu_count.

What is interesting is that the container db ignored cpu_count=4 and ran with whatever degree I specified (highest I went was 36). The pluggable however honored the cpu_count parameter, which is a good thing.

Thanks for the assistance.

Connor McDonald
July 27, 2017 - 1:21 am UTC

Glad you got to the bottom of it. ANd thanks for getting back to us, so that others will benefit.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.