Wednesday, March 20, 2024

A key difference between TP and TPURGENT

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>






Autonomous Transaction Processing (ATP)



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.



Oracle doc -
























U







R

GENTA key difference between

 TP and TPURGENT

Monday, March 11, 2024

The Fundamental Characteristics of Storage concepts for DBAs.

 ♨️The Fundamental Characteristics of Storage concepts for DBAs.🧩


☑️Conceptual challenges between DBAs and Storage technicians and Developers.


Download and follow the link Below for better understanding 

Storage Concepts