Posted by When you are using Autonomous Transaction Processing database, as well as the LOW, MEDIUM and HIGH services that are also present on the Autonomous Data Warehouse offering, there are TP and TPURGENT services that you can use. I got asked by a customer how they differ, and while TPURGENT gets a little more slice of the pie, as you can see below:
SQL> select plan, group_or_subplan, cpu_p1
2 from DBA_RSRC_PLAN_DIRECTIVES
3 where group_or_subplan like '%TP%';
PLAN GROUP_OR_SUB CPU_P1
------------ ------------ ----------
OLTP_PLAN TP 8
OLTP_PLAN TPURGENT 12
there is another important difference to be aware of. As the name suggests, TP is designed for “transactional processing” which means the expectation is for short, snappy transactions. A consequence of this, is that there is no capacity to perform operations in parallel when connecting to the TP service, even if you try to force it.
SQL> conn ADMIN/xxxx@myatp_tp
Connected.
SQL> create table t as select * from dba_objects;
Table created.
SQL> select owner, count(*) from t group by owner;
OWNER COUNT(*)
------------------------------ ----------
SYS 19401
SYSTEM 472
DBSNMP 59
APPQOSSYS 6
...
43 rows selected.
SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
SQL_ID gupp4rhyp22fz, child number 0
-------------------------------------
select owner, count(*) from t group by owner
Plan hash value: 47235625
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 22 (100)| |
| 1 | HASH GROUP BY | | 43 | 430 | 22 (14)| 00:00:01 |
| 2 | TABLE ACCESS STORAGE FULL| T | 68290 | 666K| 20 (5)| 00:00:01 |
-----------------------------------------------------------------------------------
14 rows selected.
SQL>
SQL> select /*+ parallel */ owner, count(*) from t group by owner;
OWNER COUNT(*)
------------------------------ ----------
SYS 19401
SYSTEM 472
DBSNMP 59
APPQOSSYS 6
...
43 rows selected.
SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID caw2nz87gxkja, child number 0
-------------------------------------
select /*+ parallel */ owner, count(*) from t group by owner
Plan hash value: 47235625
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 22 (100)| |
| 1 | HASH GROUP BY | | 43 | 430 | 22 (14)| 00:00:01 |
| 2 | TABLE ACCESS STORAGE FULL| T | 68290 | 666K| 20 (5)| 00:00:01 |
-----------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
0 - STATEMENT
U - parallel
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
25 rows selected.
SQL>
Compare that to when you connect to the TPURGENT service. By default, just like the TP service operations are expected to the business transactions and thus no parallelism is activated.
SQL> conn ADMIN/xxxx@myatp_tpurgent
Connected.
SQL> create table t as select * from dba_objects;
Table created.
SQL> select owner, count(*) from t group by owner;
OWNER COUNT(*)
------------------------------ ----------
SYS 19317
SYSTEM 472
DBSNMP 59
APPQOSSYS 6
...
44 rows selected.
SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID gupp4rhyp22fz, child number 0
-------------------------------------
select owner, count(*) from t group by owner
Plan hash value: 47235625
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 23 (100)| |
| 1 | HASH GROUP BY | | 44 | 440 | 23 (14)| 00:00:01 |
| 2 | TABLE ACCESS STORAGE FULL| T | 71476 | 698K| 21 (5)| 00:00:01 |
-----------------------------------------------------------------------------------
14 rows selected.
However, under TPURGENT you can obtain parallel processing should you need it by explicitly nominating it with hint:
SQL> select /*+ parallel */ owner, count(*) from t group by owner;
OWNER COUNT(*)
------------------------------ ----------
SYS 19317
SYSTEM 472
DBSNMP 59
APPQOSSYS 6
...
44 rows selected.
SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID caw2nz87gxkja, child number 1
-------------------------------------
select /*+ parallel */ owner, count(*) from t group by owner
Plan hash value: 129087698
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 13 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 44 | 440 | 13 (16)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 44 | 440 | 13 (16)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 44 | 440 | 13 (16)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 44 | 440 | 13 (16)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | HASH GROUP BY | | 44 | 440 | 13 (16)| 00:00:01 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 71476 | 698K| 11 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS STORAGE FULL| T | 71476 | 698K| 11 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - storage(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
29 rows selected.
SQL>
The documentation covers this important difference
but unfortunately the FAQ is incorrect (but we’ll get that fixed!)
Learning is not a spectator sport 