Oracle “Always Free” Autonomous Database: A Free Slice of Exadata?

In September of 2019 Oracle started offering an Autonomous Database (Data Warehouse or OLTP) that is part of their “Always Free” Oracle Cloud tier. “Free” usually means you’re not getting that much or there’s a catch, but I was surprised at what you do get.

Using the SSB (Sample Star Schema) benchmark, I ran a typical query against the SSB tables that joined all the tables and performed some aggregations. After collecting the SQL Monitoring reports from the free Autonomous database, I created a paid version of Autonomous database with 4 OCPUs and compared the results.

Some of the results were expected, some not — a few surprising features of Oracle Database came into the picture as I reviewed the results.

Autonomous Database Characteristics

The “Always Free” version provides you an Oracle single instance with 1 OCPU, 20 GB of storage, and some fraction of the compute server memory for the SGA (in this case SGA_TARGET is 3.6 GB).

The SSB schema conveniently comes pre-loaded on every Autonomous database, free or paid. The total size for all 5 tables is a about 185 GB, compressed for QUERY HIGH. Of course, since the Always Free version of Autonomous has only 20 GB of storage, the contents of the SSB schema does not count towards the quota.

The sample query is as follows. It joins all 5 of those tables and rolls up the profit by country and year.

select /*+ monitor */
  d.d_year,
  c.c_nation,
  sum(lo_revenue - lo_supplycost) profit
from
  SSB.LINEORDER l,
  SSB.DWDATE d,
  SSB.PART p,
  SSB.SUPPLIER s,
  SSB.CUSTOMER c
where
  l.lo_orderdate = d.d_datekey
  and l.lo_partkey = p.p_partkey
  and l.lo_suppkey = s.s_suppkey
  and l.lo_custkey = c.c_custkey
  and s.s_region = 'AMERICA'
  and c.c_region = 'AMERICA'
group by
  d.d_year,
  c.c_nation
order by
  d.d_year,
  c.c_nation;

With this dataset, I’ll return 35 rows.

Always Free Benchmark Results

Since there is only one OCPU, by default my query won’t run in parallel, even if I specify an explicit PARALLEL(4) hint. The query returned results in 2.9 minutes.

No in-memory area is allocated for the Autonomous instance; however, the execution plan shows numerous key vector operations that are offloaded to the storage servers.

The surprise came when I ran the query again — remember, this is with only 1 OCPU — it ran in 174 microseconds. Or some very short duration!

Wow, was it because of everything being in cache at the storage layer? Was it because it was all in PMEM in the storage servers? Not quite — it was a feature that I hadn’t seen used in production Oracle databases for years: the Oracle query result cache:

For comparisons to other databases without a result cache configured, I had to use the /*+ no_result_cache */ hint!

Subscribed (Paid) Benchmark Results

My paid version of Autonomous had 4 OCPUs allocated on shared infrastructure, so without using hints my query ran with PARALLEL(4).

Price/Performance Comparison

Free is free! At current rates in my pricing tier, it’s about US$100 a day to use an Autonomous Database with 4 OCPUs, an SGA_TARGET of 13.6 GB, PGA_AGGREGATE_TARGET of 20.4 GB, and 1 TB of storage. (SGA and PGA allocated are proportional to the number of OCPUs).

Conclusions

If you want to run an Oracle application in the cloud on Exadata, your total database size is under 20 GB, and one shared OCPU is sufficient for your throughput and response time needs, start with an “Always Free” Autonomous Database. It can be converted/migrated to a paid version of Autonomous when the need arises.

Also keep in mind that as fast as Exadata can be under the Autonomous Database framework, sometimes pre-Exadata Oracle Database features can be the biggest benefit to your analytic queries.