Differences between Oracle Autonomous Databases ATP & ADW

Summary

The video script explores Oracle's two autonomous database services: ADW for analytics in columnar format and ATP for transaction processing in row format. It highlights the differences in data storage, query optimization with parallelism for ADW and indexing for ATP, and memory allocation strategies. The script also explains how statistics are automatically maintained in both services, with specific focus on the real-time updates and bulk load activities in ADW, and significant data volume changes in ATP. The next video promises to guide on provisioning an ATP database.

Takeaways

• 🗂️ ADW vs ATP: Oracle offers two autonomous database services, ADW for data warehousing and ATP for transaction processing (OLTP).
• 📊 Data Storage Formats: ADW stores data in a columnar format, ideal for analytics, while ATP uses a row format, suitable for quick access and updates in transaction processing.
• 🔍 Query Optimization: ADW queries are automatically parallelized to handle large data volumes, whereas ATP uses indexes to access specific rows, enhancing performance.
• 🚀 Automatic Indexing: ATP with Oracle 19c automatically creates indexes, optimizing access to rows of interest, a feature that enhances the efficiency of transaction processing.
• 💾 Memory Allocation: ADW allocates most memory to PGA for in-memory operations like parallel joins and aggregations, while ATP prioritizes SGA to cache the critical working set and reduce I/O operations.
• 📈 Real-time Statistics: Both ADW and ATP maintain optimizer statistics in real time, ensuring the performance of the database systems.
• 📊 Bulk Load Statistics: In ADW, statistics including histograms are automatically maintained during bulk load activities, optimizing the analytical performance post data ingestion.
• 🔄 Dynamic Stats Gathering: ATP gathers statistics dynamically when there is a significant change in data volume, adapting to the evolving data landscape for optimal query performance.
• 🛠️ Provisioning Guidance: The next video will cover how to provision an ATP database, indicating a step-by-step guide for setting up an autonomous transaction processing environment.
• 🔑 Key Differences: Despite similarities, ADW and ATP have distinct backend operations, data storage methods, and optimization strategies tailored to their specific use cases in warehousing and transaction processing.

Q & A

What are the two variants of Oracle Autonomous Databases?

The two variants of Oracle Autonomous Databases are ADW (Autonomous Data Warehouse) and ATP (Autonomous Transaction Processing).

What type of application is ADW designed for?

ADW is designed for data warehouse applications that require analytics processing.

What is the data storage format used in ADW?

In ADW, data is stored in a columnar format, which is optimal for analytics processing.

What is the data storage format used in ATP?

In ATP, data is stored in a row format, which is ideal for transaction processing or OLTP.

How does query optimization differ between ADW and ATP?

Queries in ADW are automatically parallelized to handle large volumes of data, while ATP uses indexes to access specific rows of interest.

What is the significance of the PGA in ADW?

In ADW, the majority of the memory is allocated to the PGA to allow for parallel joins and complex aggregations to occur in memory, reducing the need for disk access.

Why is the SGA important in ATP?

In ATP, the majority of the memory is allocated to the SGA to ensure that the critical working set can be cached, minimizing the necessary I/O operations.

How are optimizer statistics maintained in ADW?

Optimizer statistics in ADW are automatically maintained, including histograms, as part of bulk load activities.

How are optimizer statistics updated in ATP?

In ATP, optimizer statistics are automatically gathered when there is a significant change in the volume of data.

What is the difference in the approach to statistics gathering between ADW and ATP during bulk load operations?

In ADW, statistics gathering occurs as soon as a bulk load is performed, whereas in ATP, statistics are gathered only when there is a significant change in data volume.

What will be the topic of the next video in the series?

The next video will cover how to provision an Autonomous Database, specifically focusing on ATP.

Outlines

00:00

💾 Database Variants and Storage Formats

The paragraph introduces two variants of Oracle's autonomous databases: Autonomous Data Warehouse (ADW) and Autonomous Transaction Processing (ATP). It discusses the rationale behind Oracle creating these two distinct services, highlighting their specific use cases. ADW is optimized for data warehouse applications and uses a columnar data format, which is ideal for analytics processing. ATP, on the other hand, is designed for Online Transaction Processing (OLTP) and employs a row format for data storage, facilitating quick access and updates to individual records. The paragraph emphasizes the importance of understanding how data is stored in each service, as it significantly impacts performance and functionality.

Keywords

💡Autonomous Data Warehouse (ADW)

Autonomous Data Warehouse (ADW) is a cloud-based service designed for data warehousing applications. It is optimized for analytics processing and is characterized by its ability to automatically manage and tune the database to deliver high performance. In the video, ADW is highlighted for its use of columnar data storage, which is ideal for analytics as it allows for efficient data compression and fast query processing across large datasets. The script mentions that ADW is best for creating data summaries and is automatically parallelized for handling large volumes of data.

💡Autonomous Transaction Processing (ATP)

Autonomous Transaction Processing (ATP) is another variant of Oracle's autonomous database service, tailored for online transaction processing (OLTP) applications. It is designed to handle high volumes of transactions with fast response times. The video script explains that ATP stores data in row format, which is ideal for transaction processing as it allows quick access and updates to individual records. ATP is also mentioned to automatically create indexes, which is crucial for efficient data retrieval in transactional systems.

💡Columnar Format

Columnar format is a method of storing data where each column of data is stored separately from other columns. This format is beneficial for analytical queries as it allows for faster aggregation and analysis of data. The video emphasizes that ADW uses columnar format, which is particularly suited for data warehousing because it enables parallel processing and efficient data compression, thus enhancing the performance of analytical queries.

💡Row Format

Row format, as opposed to columnar format, stores data in rows where all the data for a given record is stored together. This format is ideal for transaction processing as it allows for quick access to all the data in a single record. The video script points out that ATP uses row format because it facilitates fast updates and retrieval of individual records, which is essential for transactional systems.

💡Query Optimization

Query optimization refers to the process of improving the performance of a query, typically by reducing the amount of processing required to execute it. The video script explains that ADW automatically parallelizes queries due to the large volumes of data typically accessed in data warehousing environments. This optimization technique allows for faster processing of complex analytical queries. In contrast, ATP uses indexes to optimize queries, focusing on accessing specific rows of interest.

💡Parallelism

Parallelism in the context of databases refers to the ability to process multiple operations simultaneously, which can significantly improve performance, especially for data-intensive tasks. The video script mentions that ADW automatically parallelizes queries, which is crucial for handling large datasets efficiently in a data warehouse environment.

💡PGA (Program Global Area)

The Program Global Area (PGA) is a memory area in Oracle databases used for storing data and control information during SQL statement execution. The video script explains that in ADW, a majority of the memory is allocated to the PGA to support parallel joins and complex aggregations in memory, which can prevent performance bottlenecks caused by data spilling onto disk.

💡SGA (System Global Area)

The System Global Area (SGA) is a memory area that holds shared data and control structures for Oracle databases. The video script points out that in ATP, most of the memory is allocated to the SGA to ensure that the critical working set can be cached, thereby reducing the need for disk I/O operations and improving transaction processing performance.

💡Statistics Gathering

Statistics Gathering in databases involves collecting and updating information about the data distribution and usage patterns, which is used by the query optimizer to choose the most efficient execution plan for queries. The video script explains that in ADW, statistics, including histograms, are automatically maintained as part of bulk load activities, while in ATP, statistics are gathered when there is a significant change in the volume of data, ensuring that the optimizer has up-to-date information to make efficient query plans.

💡Bulk Load

Bulk Load refers to the process of loading large amounts of data into a database in a single operation. The video script mentions that in ADW, statistics gathering is performed as part of bulk load activities, which is important for maintaining accurate optimizer statistics after large-scale data insertions.

💡Provisioning

Provisioning in the context of cloud services like databases refers to the process of setting up and configuring a new instance of a service. The video script indicates that the next video will cover how to provision an ATP database, suggesting that the process is an important step in utilizing autonomous databases and will be discussed in detail.

Highlights

Oracle has created two different autonomous database services: ADW (Autonomous Data Warehouse) and ATP (Autonomous Transaction Processing).

ADW is designed for data warehouse applications, while ATP is for OLTP (Online Transaction Processing) applications.

Data storage format differs between ADW and ATP; ADW uses a columnar format ideal for analytics, ATP uses a row format suitable for transaction processing.

In ADW, queries are automatically parallelized to handle large volumes of data, whereas ATP uses indexes to access specific rows of interest.

Oracle 19c introduces automatic index creation in ATP, enhancing performance for autonomous databases.

Memory allocation strategies vary between ADW and ATP; ADW allocates more to PGA for parallel operations, ATP to SGA for caching critical data sets.

Optimizer statistics in both ADW and ATP are automatically maintained in real-time, preventing plan aggregations.

ADW maintains statistics, including histograms, during bulk load activities, ensuring optimal performance post data influx.

ATP gathers statistics automatically when there is a significant change in data volume, adapting to data changes for query optimization.

The video will cover how to provision an ATP database in the next installment, providing practical guidance for users.

Provisioning processes for both ADW and ATP are quite similar, with ATP being used as an example in the upcoming video.

Oracle's autonomous databases offer distinct advantages for different types of applications, optimizing performance based on use case.

The columnar format in ADW enhances analytical processing by storing data in a way that simplifies large-scale data analysis.

Row format in ATP facilitates quick access and updates to individual records, crucial for transactional systems.

ADW's automatic parallelization of queries is a key feature for handling the demands of data warehousing environments.

ATP's use of indexes is a strategic approach to optimize access to specific data rows, enhancing OLTP performance.

Oracle's autonomous databases provide real-time statistics updates, a critical feature for maintaining query efficiency.

The video concludes with a teaser for the next video on ATP database provisioning, engaging viewers to continue learning.

Transcripts

okay so as we saw in the previous video

that

there are two database variants for

autonomous databases which is

adw that is autonomous data warehouse

and the second one is atp that is

autonomous transaction processing

now the very first question that would

be coming to your mind would be

why oracle has created two different

services okay one thing is

uh you can understand that uh adw

is for something like a data warehouse

kind of application

right and atp is more of like an oltp

kind of application that is all good

but what exactly is happening behind the

scenes

so let's take a look the very first

thing is the data format

so the thing is that please remember

that the data is stored differently in

each service

in adw the data is stored in a columnar

format

and and that's the best format for

analytics processing

while in atp or autonomous transaction

processing

the data is stored in a row format the

row format is ideal for

transaction processing or oltp as it

allows

quick access and updates to all the

columns in an

individual record since all of the data

for a given record stored together

in memory and on storage so very

important please remember

with adw the data is stored in a

columnar format

and atp it is in row format

right next thing is about

query uh optimization the queries that

are

executed on adw are automatically

parallelized

as they tend to access large volumes of

data so oracle understands

that because since you are having a data

warehouse you you need parallelism on

that

right while indexes are used to

um on atp to access only the specific

rows of interest so as you can see

creates indexes

it actually comes up with the 19c so if

you are running or autonomous database

with 19c version then it will actually

create indexes

as well automatically or autonomously

for for you

right so always remember that you have

uh

adw for creating data summaries and atp

more of uh you'll have indexes and with

90c you'll have

automatic indexes as well then comes uh

the configuration part

so in adw the majority of the memory is

allocated to the pga

why because it allows parallel joins and

complex aggregations to occur

in memory rather than spilling onto disk

which can be slow

and whilst if we talk about atp the

majority of the memory is allocated to

the

sga to ensure that the critical working

set can be cached

to avoid the necessary i o

then you can see that stats uh gathering

is pretty much similar so the statistics

are updated in real time while

preventing the plan aggregations

so uh regardless of which type of

autonomous database service you use

the optimizer statistics will be

automatically maintained

but uh talking about atp and adw on

adw the stats or including histograms

are automatically maintained as part of

the

bulk load activities let's say when you

are doing your bulk load you're loading

doing a bulk load on your database or

warehouse at that time your stats would

be gathered

when we talk about atp the data is

because in atp

or oltp the data is added using more

traditional insert statements as you

know so the stats are automatically

gathered when the volume of data changes

significantly which is really really

important guys

in adw the stats gathering is done

when as soon as you do a bulk load of

data but in atp

the stats gather is done only when

oracle discovers

that there is some big change in uh the

volume of data

so the stats are automatically gathered

when the volume of data changes

significantly enough to make a

difference to the statistics

so guys please remember these two

services atp adw

they look all similar the main purpose

for adw

is warehousing and atp is otp

but what things work behind the scenes

and how data is stored behind the scenes

uh we actually

discussed in this video in the next

video we shall take a look into how you

can provision

uh your autonomous database so we'll

just pick one of the autonomous

databases because provisioning is quite

similar

in our case we picked atp i'll show you

how you can provision your atp database

thanks for watching guys