Oracle Autonomous Transaction Processing – A Good Start

Posted in: Cloud, Oracle

It has been almost a year since the last Open World where Oracle CTO and founder Larry Ellison proclaimed the new upcoming fully autonomous cloud database service. It created a lot of attention and almost everybody in the Oracle community has been wondering when the service would become available. At first, and some time ago, we got the Oracle Autonomous Data Warehouse Cloud (ADWC) and finally, just recently, we were able to put our hands on the On-Line Transaction Processing (OLTP) version. It’s called the Autonomous Transaction Processing (ATP) database. And I want to share the very first impression and feelings of using this new service. Please keep in mind that everything mentioned here is accurate at the time of writing. The Oracle Public Cloud is in a permanent state of rapid change and innovation, and some features or problems we are seeing now may disappear.

Let’s start from scratch and try to create an ATP instance. I opened my Oracle Cloud Dashboard, found “Autonomous Database” and clicked on it.

It redirected me to Oracle Cloud Infrastructure (OCI) where I was able to find the Autonomous Transaction Processing.

The first positive moment came when I was able to launch the ATP service page from my Safari browser. You may remember that in the past Safari was not supported on Oracle OCI and you had to use either Firefox or Chrome on your Mac to work on OCI. Now everything works perfectly well with Safari.

So, I went ahead and created the new ATP instance. In comparison with other database services, it doesn’t ask for too many details. You just enter the database name, number of CPU cores, storage size and a password for the “ADMIN” user. You provide those values, push the “Create Autonomous Transaction Processing” button and wait while the instance is provisioned.

And this was the second time that I was pleasantly surprised when my ATP instance was provisioned and made available in just five minutes after pressing the button. This is really good progress in comparison with other Oracle Cloud database services.

The instance was ready and I started to check parameters, storage and what we had there in general.

When I checked the instance parameters and patches, I found that the system was patched to the latest July bundle patch. Interesting to note that the patch was marked as “ADW BUNDLE PATCH.” It looks like Oracle used the same bundles for ATP and for ADW.

SQL> SELECT patch_id,version,action_time,description,bundle_series FROM dba_registry_sqlpatch;
 
        PATCH_ID VERSION              ACTION_TIME                 DESCRIPTION                                                                                          BUNDLE_SERIES                 
---------------- -------------------- --------------------------- ---------------------------------------------------------------------------------------------------- ------------------------------
        27833146 12.2.0.1             18-04-15 22:53:33,631780000 ADW BUNDLE PATCH 180408.12.2.0.1. (27833146)                                                         12.2.0.1.0ADW                 
        27912909 12.2.0.1             18-05-13 09:26:55,583388000 ADW BUNDLE PATCH 180423.12.2.0.1 (27912909)                                                          12.2.0.1.0ADW                 
        28021834 12.2.0.1             18-05-20 06:30:40,791622000 ADW BUNDLE PATCH 180511.12.2.0.1 (28021834)                                                          12.2.0.1.0ADW                 
        28083996 12.2.0.1             18-06-10 13:24:07,039773000 ADW BUNDLE PATCH 180531.12.2.0.1 (28083996)                                                          12.2.0.1.0ADW                 
        28228725 12.2.0.1             18-07-01 16:20:00,784425000 ADW BUNDLE PATCH 180621.12.2.0.1 (28228725)                                                          12.2.0.1.0ADW                 
        28392695 12.2.0.1             18-07-29 09:01:06,437228000 ADW BUNDLE PATCH 12.2.0.1.0(ID:180722)

When I was browsing through parameter settings, I also noted that the workaround for the latest issue with incremental backups on Exadata was also in place. The issue was published only a few days before. This is a good sign that Oracle is serious about security and availability on the ATP.

orcl_high> SHOW parameter _disable_cell_optimized_backups
NAME                            TYPE    VALUE 
------------------------------- ------- ----- 
_disable_cell_optimized_backups BOOLEAN TRUE  
orcl_high>

We know that the ATP is built on Exadata and allows us to use storage indexes and other Exadata features. I briefly tested optimizer reads and flash cache and the features worked pretty well.

orcl_high> CREATE TABLE t1 AS SELECT * FROM (SELECT rownum t1_id, object_id p1, object_name p2, owner p3 FROM all_objects),(SELECT rownum FROM dual CONNECT BY LEVEL <=20000); TABLE T1 created. orcl_high> UPDATE t1 SET t1_id = NULL WHERE t1_id BETWEEN 500 AND 510;
 
220,000 ROWS updated.
 
orcl_high> commit;
 
Commit complete.
 
orcl_parallel> EXEC dbms_stats.gather_table_stats('ADMIN','t1');
 
PL/SQL PROCEDURE successfully completed.
 
orcl_high> SELECT TABLE_NAME, num_rows, sample_size, last_analyzed FROM user_tables WHERE TABLE_NAME LIKE '%T1%'; 
 
TABLE_NAME                                                                                                                               NUM_ROWS      SAMPLE_SIZE LAST_ANA
-------------------------------------------------------------------------------------------------------------------------------- ---------------- ---------------- --------
T1                                                                                                                                      366240000        366240000 18-08-29
 
orcl_high> SELECT COUNT(*) FROM t1 WHERE t1_id IS NULL;
 
        COUNT(*)
----------------
          220000
 
orcl_high> SELECT n.name,s.value FROM v$mystat s, v$statname n WHERE n.statistic#=s.statistic# AND n.name LIKE '%cell flash cache read%';
 
NAME                                                                    VALUE
------------------------------------------------------------ ----------------
cell flash cache READ hits                                              17229
cell flash cache READ hits FOR controlfile reads                            0
cell flash cache READ hits FOR smart IO                                 17159
 
orcl_high> SELECT n.name,s.value FROM v$mystat s, v$statname n WHERE n.statistic#=s.statistic# AND n.name LIKE '%physical%optimized%';
 
NAME                                                                    VALUE
------------------------------------------------------------ ----------------
physical READ requests optimized                                        17229
physical READ total bytes optimized                               17963958272
...
orcl_high> SELECT n.name,s.value FROM v$mystat s, v$statname n WHERE n.statistic#=s.statistic# AND n.name LIKE '%smart scan%';
 
NAME                                                                    VALUE
------------------------------------------------------------ ----------------
cell physical IO interconnect bytes returned BY smart scan           18662816
...

And of course, you are probably getting familiar with the service console dashboard and SQL monitor.

Overall, my first impression is good. I cannot say anything yet about any “self-driving” features or how it will work with a real business workload. But I think it is definitely a step forward in the Oracle cloud database services and so far it looks promising. I hope Oracle will keep moving in that direction and continue to make the cloud services better and more mature.

email

Interested in working with Gleb? Schedule a tech call.

About the Author

Regarded by his peers as an Oracle guru, Gleb is known for being able to resolve any problem related to Oracle. He loves the satisfaction of troubleshooting, and his colleagues even say that seeking Gleb’s advice regarding an issue is more efficient than looking it up. Gleb enjoys the variety of challenges he faces while working at Pythian, rather than working on the same thing every day. His areas of speciality include Oracle RAC, Exadata, RMAN, SQL tuning, high availability, storage, performance tuning, and many more. When he’s not working, running, or cycling, Gleb can be found reading.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *