Difference between TP and TPURGENT TP
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. Difference 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>
So its important to be aware of the parallelism facilities defined for MEDIUM and HIGH. We’ll do operations in parallel whenever possible. You get faster queries and faster DML but there are implications on the way you write your scripts, the locking that will result and how commit processing must be handled. HIGH and MEDIUM are not just “bigger” versions of the LOW service.
Predefined Database Service Names for Autonomous Transaction
The predefined service names provide different levels of performance and concurrency for Autonomous Transaction Processing.
- tpurgent: The highest priority application connection service for time critical transaction processing operations. This connection service supports manual parallelism.
- tp: A typical application connection service for transaction processing operations. This connection service does not run with parallelism.
- high: A high priority application connection service for reporting and batch operations. All operations run in parallel and are subject to queuing.
- medium: A typical application connection service for reporting and batch operations. All operations run in parallel and are subject to queuing. Using this service the degree of parallelism is limited to four (4).
- low: A lowest priority application connection service for reporting or batch processing operations. This connection service does not run with parallelism.
For standard transactional activities on ATP, make sure it uses the TP or TPURGENT services. If you need faster performance for volume operations, then HIGH and MEDIUM are your friend,
but understand the locking and commit implications.
Predefined Database Service Names for Autonomous
Databases
Regarding parallelism for different services.
Ref - Connor Mcdonald ( TP AND TP URGENT )
