Autonomous Robots need Autonomous Databases or Top 100 Automatic Features of the Oracle Database

One of the very few words in the English language originating from Slavic languages is the word robot. It comes from an Old Church Slavonic word rabota which means (servitude of forced) labor/work.

100 years ago, in 1921, Karel Čapek’s play RUR introduced the word “robot” to science fiction. And for the Oracle DBAs: he itroduced just the robots not the “Release Update Revisions” 🙂

In a recent paper called “A Realtime Autonomous Robot Navigation Framework for Human like High-level Interaction and Task Planning in Global Dynamic Environment“, the authors proposed the self-driving technology framework to perform high-level interactions and human-like tasks:

You can see the overview of the proposed framework: a robot-centric on-demand database and a working memory are used for autonomous navigation, behavior planner, and learning. The robot can download database from network or cloud if necessary.

Yet in another research paper called “Autonomous robot system architecture for automation of structural health monitoring“, the proposed system uses an autonomous robot, database and the proposed architecture is used to integrate all sub-systems for the automation of the Structural Health Monitoring.

For such Autonomous Robots, we need also Autonomous Databases.

Here is a new Guide to the Oracle’s self-driving database and you can see below Gartner’s rankings where Oracle Autonomous Database is on top of every list but the aim of this post is not to compare Oracle Autonomous Database with other databases but to list all 100 automation/autonomous features of the Oracle database. First have a look at what is new with the latest release Oracle 21c:

– Native Blockchain Tables
– Automatic Index Optimization
– Automatic Zone Maps
– Automatic SQL Tuning Sets
– Automatic Indexing Enhancements
– Autonomous Health Framework and Object Activity Tracking System
– Automatic Materialized Views
– Auto-Result Cache

And here are the Top 100 features (in no order whatsoever) that make Oracle database “The Autonomous Database”:

1. Automatic Storage Management (ASM)
2. Automatic Workload Repository (AWR)
3. Automatic Data Optimization (ADO)
4. Automatic Database Diagnostic Monitor (ADDM)
5. Automatic Space Segment Management (ASSM)
6. Automatic Diagnostic Repository (ADR)
7. Automatic In-Memory
8. Automatic Big Table Caching
9. Automatic Degree of Parallelism
10. Automatic Index Optimization
11. Automatic Indexing
12. Automatic Materialized Views
13. Automatic SQL Plan Management
14. Automatic SQL Tuning Set
15. Automatic Zone Maps
16. Autonomous Health Framework
17. Automatic Memory Management
18. Automatic Undo Management
19. Automatic Undo Retention Tuning
20. Automatic Shared Memory Management
21. Automatic PGA Memory Management
22. Automatic Plan Capture with SPM
23. Automatic Refresh of Materialized Views
24. Automatic VIP failback
25. Automatic AWR PDB Autoflush
26. Automatic Segment Advisor
27. Automatic Tablespace Point In Time Recovery
28. Automatic Outage Resolution with Oracle Data Guard
29. Automatic Statistics Gathering
30. Automatically Created Storage Indexes in Exadata
31. Automates Database Upgrades with AutoUpgrade
32. Auto-List Partitioning
33. Automating Diagnostic Collection for Trace File Analyzer
34. Oracle GoldenGate Automatic CDR
35. Automatically Enabled Resource Manager for Database In-Memory
36. Automatic Data Corruption Repair
37. Automatic Oracle restart
38. Automated tracking: Object Activity Tracking System (OATS)
39. Automatic Storage Management Cluster File System
40. SQL*Net Auto-Detection of Support for Out-of-Band Breaks
41. Automatic Service Registration
42. Automatic Purging of Workspaces
43. Automatic DBCA creation of database management policy for RAC
44. Automatic Storage Management Cluster File System (Oracle ACFS) Snapshots
45. Oracle Data Guard Broker Support for Multiple Automatic Failover Targets
46. ACFS Auto-Resize
47. Automatic Background Index Maintenance for Oracle Text
48. Automatically Maintained Standby Databases
49. Automatically Enabling Oracle Database Quality of Service (QoS) Management
50. Automatic Collection of Dynamic Statistics
51. Automatically Synchronize Password Files in Oracle Data Guard Configurations
52. Automatic Data Optimization (ADO) for Hybrid Columnar Compression (HCC)
53. Automatic Locking of Inactive User Accounts
54. Automatic Management of Near Real-Time Indexes
55. Heat Map and Automatic Data Optimization Support for CDBs
56. High-Frequency Automatic Optimizer Statistics Collection
57. Automatic Storage Management (ASM) File Access Control
58. Automatic Support for Both SASL and Non-SASL Active Directory Connections
59. Automatic KDC Discovery When Configuring OCI Clients
60. Automatic Column Group Detection
61. Automatic CDR
62. AutoShrink for ACFS
63. Auto-Tuning Oracle C Client Interface (OCI) Applications
64. Automated Transaction Draining for Oracle Grid Infrastructure Upgrades
65. Automatic CDR Support of Tables with Unique Indexes/Constraints
66. Automatic Real-Time statistics
67. Automatic Correction of Non-logged Blocks at a Data Guard Standby Database
68. Automatic Disaster Recovery Fails with RMAN
69. Automatic Channel Failover
70. Automatic Synchronization of Oracle Text Index
71. Automatic Registration of the Database with the Default Listener
72. Automatic Offlining of the Datafile in Noarchivelog
73. Automatic Datatype Conversion
74. Automatic Tape Drive Cleaning and a Cleaning Tape in a Tape Library
75. Automatic Controlfile Backup
76. Automatic Eject Of Tape After Backup Using Oracle Secure Backup
77. Automatic BackupSet Failover On Missing or Corrupt BackupPieces
78. Automatic BMR (Block Media Recovery)
79. Automatic System Tasks
80. Automatic Database Performance Monitoring
81. Automatic Archiving
82. Automatic Propagation in Replication
83. Automatic Job Scheduling
84. Automatic Resume of DataPump
85. Automatic (Real-Time) ADDM Trigger Control
86. Automatic Identify and Allocate Compute & Storage Resources for ADB
87. Automatic Install and Configure Database Software for ADB
88. Automatic Configure Oracle Grid Infrastructure for ADB
89. Automatic Configure Oracle Real Application Clusters for ADB
90. Automatic System & Storage Optimization for ADB
91. Automatically Derived Settings for ADB
92. Automatic Scripted Scaling for ADB
93. Automatic Health Check for ADB
94. Automatic Updates for ADB
95. Automatic Schema Level Optimization for ADB
96. Fault Detection & Resolution for ADB
97. Automatic Security Updates for ADB
98. Automatic Secondary Indexing for ADB
99. Automatic Database Encryption ADB
100. Automatic Database Backups for ADB

Note please: these are the Top 100 on my list – there are much more!

Automatic Materialized Views in Oracle Database 21c

“It’s supposed to be automatic, but actually you have to push this button.” ― John Brunner

With Oracle Database 21c, there is no button for Automatic Materialized Views – it is fully automatic.

DBA_MVIEWS has a new column call AUTO – that is how a DBA can distinguish the auto MVs from the manual ones. The auto naming convention is something like AUTO_MV$$_H3KBHG7DAH6T5. That is all start with AUTO_MV$$.

The Oracle database automatically collects workload statistics, SQL statements and query execution statistics. Oracle also maintains and purges the history of the workload. Automatic materialized views use workload information provided by the Object Activity Tracking System (OATS) as part of the automated decision-making processes.

All preconfigured / additionally configured parameters can be viewed from DBA_AUTO_MV_CONFIG:

Here is how automatic materialized views work:

– The database automatically detects and collects workload query execution statistics including buffer-gets, database time, estimated cost, and other statistics: DBA_AUTO_MV_ANALYSIS_EXECUTIONS displays information about analysis and tuning executions, including concurrency, degree of parallelism (DOP) requested by the user and actual DOP upon execution finish, status, associated advisor, and informational or error message.

– Oracle creates candidate materialized views hidden from the database workload and verifies that they will deliver the projected performance benefit by test executing a sample of workload queries in the background: DBA_AUTO_MV_ANALYSIS_RECOMMENDATIONS
Displays recommendations associated with automatic materialized views

– There are provided reports with detailed performance test results and which materialized views have been implemented: DBA_AUTO_MV_ANALYSIS_REPORT reports on analyses and recommendations, including task and execution names, sequence number of the journal entry, and message entry in the journal

– Automatic materialized view refresh is also automatic: DBA_AUTO_MV_REFRESH_HISTORY displays the owner name, view name, date, start and end time, elapsed time, status, and error number (if an error occurred) for each automatic materialized view refresh

Automatic MVs are off by default. As DBAs, we can use the CONFIGURE procedure of the DBMS_AUTO_MV package to configure automatic materialized views creation in the database. The AUTO_MV_MODE parameter enables (IMPLEMENT) or disables (OFF) automatic materialized views, or engages report-only mode (REPORT ONLY). The AUTO_MV_MAINT_TASK parameter activates or deactivates the task performing the maintenance (refreshes, validations, and clean up).

Let us enable it all:

Oracle Database 21c includes data dictionary views that display information about automatic materialized views as well as OATS (Object Activity Tracking System). DBAs can use the DBMS_ACTIVITY.CONFIGURE procedure to control the three OATS parameters within a specific database.

ACTIVITY_INTERVAL defines the interval between snapshots:

exec dbms_activity.configure('ACTIVITY_INTERVAL_MINUTES','30');

ACTIVITY_RETENTION_DAYS defines how long snapshots are saved:

exec dbms_activity.configure('ACTIVITY_RETENTION_DAYS','60');

ACTIVITY_SPACE_PERCENT sets how much of available space is reserved for snapshots:

exec dbms_activity.configure('ACTIVITY_SPACE_PERCENT','10');

You mostly likely will get though the following error, even alter system set “_exadata_feature_on”=true scope=spfile; and restart:

ERROR at line 1:
ORA-40216: feature not supported
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_ACTIVITY", line 278
ORA-06512: at "SYS.DBMS_ACTIVITY", line 314
ORA-06512: at line 1

The feature is only available on Exadata and ExaCs.

Here are some additional details:

– Automatic materialized views support partitioned and non-partitioned base tables
– Incremental materialized view refresh is supported
– If partitioned base tables use either range, list, or composite partitioning, then they are eligible for Partition Change Tracking (PCT) view refresh
– If there is performance advantage, the automatic materialized view recommendations will include a partitioned automatic materialized view based on the partitioning of the base table of the materialized view and the partitioning type supported is auto-list partitioning, which will mirror the partitioning of the fact table
– The automatic materialized view maintenance module decides the type of refresh that is the most beneficial at the time of refresh, and will decide during run time whether to switch from incremental refresh to full refresh
– DBAs can drop automatic materialized views using the dbms_auto_mv.drop_auto_mv procedure