Skip to content

Insight and analysis of technology and business strategy

Oracle autonomous transaction processing - a good start

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.

Top Categories

  • There are no suggestions because the search field is empty.

Tell us how we can help!

dba-cloud-services
Upcoming-Events-banner