Dontcheff

Archive for the ‘DBA’ Category

Database Innovation and Automation: the DBA employment after 2020

In Autonomous, DBA on December 13, 2019 at 09:57

DBA employment is expected to have increased by 11% in 2026:

According to the Bureau of Labor Statistics employment of database administrators (DBAs) is projected to grow 9 percent from 2018 to 2028, faster than the average for all occupations. Growth in this occupation will be driven by the increased data needs of companies in all sectors of the economy. Database administrators will be needed to organize and present data in a way that makes it easy for analysts and other stakeholders to understand.

As you can see from the DBA job outlook article, the employment of DBAs in the computer systems design and related services industry is projected to grow 21 percent from 2018 to 2028.

In a new study from Intellipaat, the top IT skills required for a high-flying career are Big Data, Data Science, Business Intelligence and ETL, Salesforce, SAP, Oracle Developer and Administrator, Cloud Computing, Artificial Intelligence, Python Developers, and Blockchain Developers. Oracle DBA/Developer is #6 on the list – followed by Cloud Computing and Artificial Intelligence.

Most of us have been witnessing this strange phenomenon in the IT world where anything successful must have been decided from the top and pushed down. In reality, most innovative concepts are implemented by teams at lower levels in the organizations, like the DBA teams, and afterwards successfully proven to be among the best decisions taken within the company. Example: automation. This is nothing new. But now it has been become more fashionable than ever.

Database innovation in IT is often perceived as jumping from one database brand to another or creating/adding new features into a new database brand. Although that might be true to a certain extent, database innovation is a long, incremental process taking years of research, testing and series of software improvements.

Autonomous databases are an evolution of the database software, part of the progress. Like the cost based optimizer did not radically change the way we write SQL statements, automation will not (and is not) radically changing the way DBAs work on daily basis.

In less than 7 minutes, Connor McDonald answers the most typical DBA question nowadays about the future of autonomous: Does this mean an autonomous database will be free from me and my DBA career?

It is impossible to predict what is going to happen, all is up to speculations. Just have a look at something that was written on dba-oracle.com about 15 years go. Oracle 17-3d did not introduce the time dimension to database management, allowing three-dimensional data representation. And as of 2019, there are no computers on the market that are inherently 128-bit machines.

And how many times can you find the word Cloud in the article above: “Oracle 2020: A Glimpse Into the Future of Database Management”?

These ten article cover the subject in depth:

Autonomous Database: A DBA’s New Best Friend?
2019 Data Management Predictions: Oracle Autonomous Database is in your future
The DBA’s Role and Oracle Autonomous Database
Every DBA Needs to Understand the Impacts of Oracle Autonomous Database
Oracle’s next chapter: The Autonomous Database and the DBA
Will automated databases kill the DBA position?
A Veteran DBA’s Insights On Oracle’s New Autonomous Data Warehouse
The Autonomous Database Wants to Eat Your Lunch (and maybe your job)
Oracle: the autonomous database drives the ‘evolution’ of the DBA
Oracle Autonomous Database features free DBAs from routine tasks

Bottom line, DBA will still be needed in the autonomous future for (at least) these tasks (in no order whatsoever):

– database recovery
– data security
– user management
– data migration between environments
– capacity planning and sizing
– database provisioning
– scaling, starting, stopping the db services
– monitoring
– performance tuning
– automate more and more .. and more
– migrate databases from non-Oracle brands to ADB 🙂

“The long-term future of database administrators could be at risk if every enterprise adopts the Oracle 18c autonomous database”: very, very long-term indeed!

Once more and finally – all comes from people, not from AI and SW – let me quote LJE: “What is Oracle? A bunch of people. And all of our products were just ideas in the heads of those people – ideas that people typed into a computer, tested, and that turned out to be the best idea for a database or for a programming language.” Larry Ellison

Take your time! Linear thinking based on a limited short term perspective does not derive database innovation. In reality, innovation is a set of small steps that eventually lead up to a big deal that changes the game. Like the Autonomous Database.

Memoptimized Rowstore Fast Ingest in Oracle Database 19c

In Data, Database tuning, Databases, DBA, Oracle database, Oracle internals on November 25, 2019 at 15:53

“A filmmaker should never assume he’s superior to his subject. I often find that even the simplest topic remains an enigma. The best film portraits not only evoke that enigma but ingest it in a process that renders what’s invisible visible.” – Damian Pettigrew, a documentary SME

The same statement above holds for database SMEs using the new fast ingest process. Because their data might be at one point neither visible nor invisible. From DBA point of view I mean…

Memoptimized Rowstore fast ingest is a new feature of Oracle 19c and is all about high performance data streaming. Here is an example from Oracle which is ultra fast, arguably 25 million inserts per second on two socket server:

Memoptimized Rowstore was introduced in Oracle 18c and here are few good blog posts on the subject:

Oracle Database 18c: Introduction to Memoptimized RowStore (Memory of a Free Festival) by Richard Foote
MemOptimized RowStore in Oracle Database 18c by Philipp Salvisberg

The fast ingest functionality of Memoptimized Rowstore enables fast data inserts into an Oracle Database from applications that ingest small but high volume transactions with almost no transactional overhead. The inserted data is temporarily buffered in the large pool before writing it to disk in bulk in a deferred, asynchronous manner.

Memoptimized Rowstore provides the following two functionalities:

Fast ingest:
– Fast ingest optimizes the processing of high-frequency, single-row data inserts into a database
– Fast ingest uses the large pool for buffering the inserts before writing them to disk, so as to improve data insert performance

Fast lookup:
– Fast lookup enables fast retrieval of data from for high-frequency queries
– Fast lookup uses a separate memory area in the SGA called the memoptimize pool for buffering the data queried from tables
– For using fast lookup, you must allocate appropriate memory size to the memoptimize pool using MEMOPTIMIZE_POOL_SIZE

The memoptimize pool contains two parts: (1) the memoptimize buffer area which occupies 75% of the memoptimize pool and (2) a hash index that is a non-persistent segment data structure having several units containing a number of hash buckets. The hash index occupies 25% of the memoptimize pool.

Memoptimize buffer area

There are two steps for using fast ingest for inserting data:

1. Enable a table for fast ingest: specify the MEMOPTIMIZE FOR WRITE clause in the CREATE TABLE or ALTER TABLE statement: Example on how to enable a table for Fast Ingest

2. Specify a hint for using fast ingest for an insert operation by specifying the MEMOPTIMIZE_WRITE hint in the INSERT statement: Example on specifying a hint for using Fast Ingest for data inserts

Note that a table can be configured for using both fast ingest and fast lookup but these 4 points should be always considered:

– Because fast ingest buffers data in the large pool, there is a possibility of data loss in the event of a system failure. To avoid data loss, a client must keep a local copy of the data after performing inserts, so that it can replay the inserts in the event of a system failure before the data is written to disk. A client can use the DBMS_MEMOPTIMIZE package subprograms to track the durability of the inserts. After inserts are written to disk, a client can destroy its local copy of the inserted data.

Use the GET_APPLY_HWM_SEQID function to find the low high-water mark of sequence numbers of the records that have been successfully written to disk across all the sessions:

select DBMS_MEMOPTIMIZE.GET_APPLY_HWM_SEQID from dual;

Equivalently, use the GET_WRITE_HWM_SEQID function to return the high-water mark sequence number of the record that has been written to the large pool for the current session.

select DBMS_MEMOPTIMIZE.GET_WRITE_HWM_SEQID from dual;

– Queries do not read data from the large pool, hence data inserted using fast ingest cannot be queried until it is written to disk.

If you need to flush all the fast ingest data from the large pool to disk for the current session, just run:

exec DBMS_MEMOPTIMIZE.WRITE_END;

– Parent-child transactions must be synchronized to avoid errors. For example, foreign key inserts and updates of rows inserted into the large pool can return errors, if the parent data is not yet written to disk.

– Index operations are supported by fast ingest similar to the regular inserts. However, for fast ingest, database performs index operations while writing data to disk, and not while writing data into the large pool.

You can disable a table for fast ingest by specifying the NO MEMOPTIMIZE FOR WRITE clause in the ALTER TABLE statement. You can view the fast ingest data in the large pool using the V$MEMOPTIMIZE_WRITE_AREA view. You can also view and control the fast ingest data in the large pool using the subprograms of the packages DBMS_MEMOPTIMIZE and DBMS_MEMOPTIMIZE_ADMIN.

The DBMS_MEMOPTIMIZE_ADMIN package has only one procedure WRITES_FLUSH which does not accept any input or output parameters.

exec DBMS_MEMOPTIMIZE.WRITES_FLUSH;

Here is one more example from Oracle:

As you see, you can lose data using this new technique. Use it with caution unless you can afford that or re-insert the data.

If you need to remove or populate fast lookup data for a certain table from the memoptimize pool, use DBMS_MEMOPTIMIZE.DROP_OBJECT and DBMS_MEMOPTIMIZE.POPULATE respectively.

Check MemOptimized RowStore in Oracle Database 19c by Philipp Salvisberg for a good performance comparison between PL/SQL and Java Thin.

What is Preview Versions for Autonomous Database

In Autonomous, Cloud, DBA, Init.ora, New features, Oracle database on October 18, 2019 at 12:14

A first preview is not exactly a pleasant experience for producers, directors and actors. In the IT world, it is exactly the opposite: DBAs can use the new version of the database before it becomes mainstream.

The “Preview Versions for Autonomous Database” has been available since mid-October 2019. The current preview version of Autonomous Database is Oracle Database 19c. More precisely, the version is 19.4.0.

Databases provisioned or cloned with a preview version display the end date of the preview period at the top of the Autonomous Database details page in the Oracle Cloud Infrastructure Console.

Note that the preview for my database below will end on December 2, 2019. All resources created with the preview will be terminated at the end of the preview period.

The basis details of preview versions for Autonomous Database can be found in the Oracle documentation.

Connecting to the preview database is as to a standard ADB:

Here are some of the default settings in Oracle Database Preview Version 19.4.0 for Autonomous Database:

Real-Time Statistics: Enabled by default
– Oracle automatically gathers online statistics during conventional DML operations
– By gathering some statistics automatically during DML operations, the database augments the statistics gathered by DBMS_STATS
– Fresh statistics enable the optimizer to produce more optimal plans
– EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; forces the database to write optimizer statistics to the data dictionary
– NO_GATHER_OPTIMIZER_STATISTICS prevents the collection of real-time statistics

High-Frequency Automatic Optimizer Statistics Collection: Enabled by default
– High-Frequency Automatic Optimizer Statistics Collection complements the standard automatic statistics collection job
– By default, the high-frequency statistics collection occurs every 15 minutes 
– Turn on/off: EXEC DBMS_STATS.SET_GLOBAL_PREFS(‘AUTO_TASK_STATUS’,’ON’);
– Change the default period: EXEC DBMS_STATS.SET_GLOBAL_PREFS(‘AUTO_TASK_INTERVAL’,’300’);
– Monitor: DBA_AUTO_STAT_EXECUTIONS
– The high-frequency automatic statistics task will not start during the maintenance window

SQL Quarantine: Disabled and not supported in Autonomous Database
– Check ORA-56955: quarantined plan used for details on SQL Quarantine

High-Frequency SQL Plan Management Evolve Advisor Task: Enabled by default
– High-Frequency Automatic Optimizer Statistics Collection complements the standard automatic statistics collection job
– By default, the high-frequency statistics collection occurs every 15 minutes 
– Turn on/off: EXEC DBMS_STATS.SET_GLOBAL_PREFS(‘AUTO_TASK_STATUS’,’ON’);
– Change the default period: EXEC DBMS_STATS.SET_GLOBAL_PREFS(‘AUTO_TASK_INTERVAL’,’300’);
– Monitor: DBA_AUTO_STAT_EXECUTIONS
– The high-frequency automatic statistics task will not start during the maintenance window

Automatic Indexing: Disabled by default
– Check Automatic Indexing in 19c for more details on Automatic Indexing

New 19c spfile parameters
– All 6 new init.ora parameters in 19c are set to their default values (click on the links below to see the values):

ADG_REDIRECT_DML
DATA_GUARD_MAX_IO_TIME
DATA_GUARD_MAX_LONGIO_TIME
ENABLE_IMC_WITH_MIRA
LOB_SIGNATURE_ENABLE
MAX_DATAPUMP_PARALLEL_PER_JOB

Preview is not free of charge. Preview provides you test/development capacity and capabilities so it is charged identically to production environments. Also, preview is not available for free service instances.

Preview instances are fully supported but should not be used for production purposes.

At the end, you may want to check out the new static data dictionary views in 19c:

• ALL_TRIGGERS_AE, DBA_TRIGGERS_AE, and USER_TRIGGERS_AE
• DBA_AUTO_INDEX_CONFIG
• DBA_AUTO_STAT_EXECUTIONS
• DBA_PDB_SNAPSHOTFILE
• DBA_RAT_CAPTURE_SCHEMA_INFO
• DBA_REGISTRY_BACKPORTS
• DBA_SQL_QUARANTINE

and also the new dynamic performance views in 19c:

• GV$AQ_PARTITION_STATS and V$AQ_PARTITION_STATS
• GV$ASM_ACFSAUTORESIZE and V$ASM_ACFSAUTORESIZE
• GV$ASM_DBCLONE_INFO and V$ASM_DBCLONE_INFO
• GV$MEMOPTIMIZE_WRITE_AREA and V$MEMOPTIMIZE_WRITE_AREA
• GV$SQL_TESTCASES and V$SQL_TESTCASES

The Power of Autonomous Database Security

In Autonomous, Cloud, Data, DBA, Security and auditing on September 9, 2019 at 13:24

“The most secure database is the one having no users connected.” You can quote me on that, I have learned it the hard way – from experience.

Database security in short means protecting the data. Markus Strauss’ article on Traditional Database Security reveals exactly the same.

Let us look into 3 recent examples of where data was not very well protected (to put it mildly), covered by CNN and CNBC:

An entire nation just got hacked: records of more than 5 million Bulgarians got stolen by hackers from the country’s tax revenue office.

A hacker gained access to 100 million Capital One credit card applications and accounts: in one of the biggest data breaches ever, a hacker gained access to more than 100 million Capital One customers’ accounts and credit card applications earlier this year.

Marriott says its Starwood database was hacked for approximately 500 million guests: “The Marriott just revealing a massive data breach involving guest reservation database at its Starwood brand. Marriott says it was unauthorized access since 2014. This includes up to 500 million guests… For approximately 327 million of these guests, the information that was exposed includes a combination of name, mailing address, phone number, email address, you ready for this – passport number… Starwood preferred guest account information, date of birth, gender, arrival and departure information… including reservation dates and communication preference. As you know, when you go to a hotel, especially internationally, they take your passport. Often times, they take a copy of your passport.”

So, granted traditional database security does not protect data well, how about looking into something new, innovative and at the same time something which has been developed and improved for more than 40 years? The Oracle Autonomous Database might be the answer (arguably is the answer). Tom Haunert’s interview with Vipin Samar (SVP of Database Security) gives an excellent overview of what Autonomous Database Security is all about.

Here is a list of 10 security benefits of Oracle’s Autonomous Database (benefits over any other database for all it matters):

1. There is no DBA access, no root access, no Operating System access… Still you can create users, roles, etc. just as before. But certain the commands are blacklisted.
2. There are no customer-managed keys: Oracle manages the keys.
3. Oracle automatically applies all security updates/patches to ensure data is not vulnerable to known attack vectors.
4. All data is encrypted using transparent data encryption.
5. Still database security features such as Virtual Private Database and Data Redaction are available.
6. Network connections from clients to the Autonomous Database are also encrypted using the client credentials wallet.
7. Data is encrypted everywhere: SQL*Net traffic, data in tablespaces and data in backups.
8. It is now possible to specify an access control list that blocks all IP addresses that are not in the list from accessing the database.
9. Oracle has been engaging with external assessment entities and independent auditors to meet a broad set of international and industry-specific compliance standards for service deployments in Oracle Cloud such as ISO 27001, SOC1, SOC2, PCI DSS, HIPAA/HITECH, and FedRAMP.
10. All operations are being audited.

The first one above is rather controversial point of debate among the DBA community. In order to ensure the security and the performance of the Autonomous Database, some SQL commands are restricted: ADMINISTER KEY MANAGEMENT, ALTER PROFILE, ALTER TABLESPACE, CREATE DATABASE LINK, CREATE PROFILE, CREATE TABLESPACE, DROP TABLESPACE. For DB links, you should use DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK to create database links in ADB.

Several DBA statements are restricted: ALTER PLUGGABLE DATABASE, ALTER DATABASE, ALTER SYSTEM, ALTER SESSION, ALTER USER, ALTER TABLE, CREATE TABLE and CREATE USER. To ensure the security and the performance of Autonomous Database, some Oracle XML DB features are also restricted. Same holds for Oracle Text, Oracle Spatial and Graph and APEX.

Oracle ADB is a database which is both Autonomous and Secure and as Mike Faden says: from self-securing database cloud services to the new cloud perimeter, Oracle technology protects your most valuable investment—your data.

And here are the 4 Areas of Self-Securing of Autonomous Database:

– Self-securing starts with the security of the Oracle Cloud infrastructure and database service. Security patches are automatically applied every quarter or as needed, narrowing the window of vulnerability. Patching includes the full stack: firmware, operating system [OS], clusterware, and database. There are no steps required from the customer side.
– Oracle encrypt customer data everywhere: in motion, at rest, and in backups. The encryption keys are managed automatically, without requiring any customer intervention. And encryption cannot be turned off.
– Administrator activity on Oracle Autonomous Data Warehouse Cloud is logged centrally and monitored for any abnormal activities. Oracle have enabled database auditing using predefined policies so that customers can view logs for any abnormal access: UNIFIED_AUDIT_TRAIL
– Built upon Oracle Database Vault, unique to Oracle Cloud, operations personnel have privilege to do all administrative tasks without any ability to ever see any customer data.

And finally something about the passwords in the Oracle Autonomous Database. They still have to be carefully chosen. Because as people say, “passwords are like underwear: make them personal, make them exotic, and change them on a regular basis.”

Oracle Autonomous Database: Dedicated vs Serverless

In Autonomous, Databases, DBA, Exadata, Oracle database, Oracle Engineered Systems on July 22, 2019 at 11:45

This blog post describes the 5 main differences between ATP-D & ATP-S and the 5 main ATP-D physical characteristics and constraints.

Autonomous Transaction Processing Dedicated (ATP-D) is a deployment choice that enables us to provision autonomous databases into dedicated Exadata cloud infrastructure instead of a shared infrastructure with other tenants.

ATP-D can be used for both OLTP or hybrid workloads for databases of any size. ATP-D is specifically good when you need highest governance, consistent performance and operational control.

For now, dedicated infrastructure means a Quarter Exadata Rack. Half and Full will be soon available too.

Besides complete physical storage isolation, ATP-D provides private IP networking, secure connections using transport layer security (TLS) credentials, and customization of software image lifecycle to align with application lifecycle.

The Fleet Administrator (think of a DBA for Autonomous Cloud) needs to create first the Exadata Infrastructure, then the CDB and only at the end the PDB. Recall that for ATP-S, you directly create the PDB.

These are the 5 main differences between ATP-D and ATP-S:

– Private IPs are not yet supported for serverless ADB deployments but they are on the short-term roadmap
– Private IPs are supported with ATP-Dedicated

– Serverless edition has no minimums or maximums for terms of usage
– Dedicated edition has a minimum term of one month and the minimum OCPU purchase is 1 OCPU per database node and up to the maximum number of OCPUs per rack: $26.88 per hours which means about $645 per day

– Loading data from object stores via DBMS_CLOUD is the recommended method for loading large data sets
– DBMS_CLOUD to load data is not applicable for ATP-D because DBMS_CLOUD is not available on ATP-D

– In ATP-D, the database version is 19c which is required for Auto-Indexing which is on by default
– Support for 19c / Auto-Indexing on ATP-S is on the roadmap

– ADB (serverless) does have auto-scaling – you can select auto scaling during provisioning or later using the Scale Up/Down button on the Oracle Cloud Infrastructure console
– ATP-D does not have auto-scaling support

Here are the 5 main ATP-D physical characteristics and constraints:

1. Quarter rack X7 Exadata Infrastructure:
– 2 severs: 92 OCPUs and 1.44TB RAM
– 3 Storage Servers: 76.8TB Flash and 107TB Disk

2. Cluster / Virtual Cloud Network:
– 1 Cluster per quarter rack

3. Autonomous Container Database:
– Maximum of 4 CDBs per Cluster
– The default data and temporary tablespaces for the database are configured automatically
– The name of the default data tablespace is DATA
– The database character set is Unicode AL32UTF8
– Compression is not enabled by default – use the table_compression clause if needed

4. Autonomous Database:
– High Availability SLA – Maximum 200 DBs
– Extreme Availability SLA – Maximum 25 DBs
– Placement Logic – Open on 1 server < 16 OCPU

5. Types of Users: Fleet Admin, Database Admin and Database User as Fleet Admin activities separated from DB Admin using IAM privileges

Note that now, there are 2 tabs for possible database connections – the serverless style DB connection and application connection:

Fleet Administrator: Fleet administrators create, monitor and manage Autonomous Exadata Infrastructure and Autonomous Container Database resources – a fleet administrator must be an Oracle Cloud user whose permissions permit the management of these resources and permit the use of the networking resources that need to be specified when creating these resources

Database Administrator: DBAs create, monitor and manage Autonomous Databases. Additionally, they create and manage Oracle Database users within these databases, and provide others the information necessary access the database – when creating an Autonomous Database resource, the DBA defines and gains access to the ADMIN administrative user account for the database

Database User: Database users are the developers who write applications that connect to and use an Autonomous Database to store and access the data. Database users do not need Oracle Cloud accounts: they gain network connectivity to and connection authorization information for the database from the database administrator

Few useful links:

A Using Oracle Database Features in Autonomous Transaction Processing
FAQs for Autonomous Transaction Processing – Dedicated
Data Center Regions for PaaS and IaaS
Oracle broadens the audience for Automated Transaction Database
2 Ways Oracle’s Autonomous Database Just Got More Useful

Bottom line: even with Autonomous, DBAs will be still needed!

ORA-56955: quarantined plan used

In DBA, New features, Oracle database, Security and auditing, SQL on May 29, 2019 at 13:15

“The way that worms and viruses spread on the Internet is not that different from the way they spread in the real world, and the way you quarantine them is not that different, either” – David Ulevitch

And now, in Oracle 19c, you can do the same with SQL:

 SQL> SELECT client, COUNT(*) OVER (PARTITION BY price) CLIENT_COUNT 
 FROM sales WHERE price IN (2, 91984);
  *
 ERROR at line 1:
 ORA-56955: quarantined plan used
 Elapsed: 00:00:00.00

Error “ORA-56955: quarantined plan used” is new in the Oracle database, it comes when the SQL run fulfills the quarantine conditions.

It is important to differentiate Oracle SQL Quarantines in 19c from Oracle Object Quarantines in 18c. There is also the concept of Offload Quarantines.

1. A good way to start understanding what SQL quarantines are about is to watch the following short video from Rich Niemiec:

2. Check also page 23 of the Optimizer In Oracle Database 19c white paper. “The new Oracle Database 19c feature SQL Quarantine can be used to eliminate the overhead of runaway queries. When DBRM detects a SQL statement is exceeding a resource or run-time limit, the SQL execution plan used by the statement is quarantined. If the SQL statement is executed again and it is using the same SQL execution plan then it will be terminated immediately. This can significantly reduce the amount of system resource that would otherwise be wasted.”

Think of SQL Quarantines as a way to prevent unnecessary SQL being run in the database, of course based on your own definition of unnecessary SQL. You can prevent the use of “bad” execution plans and exhausting the databases from resources.

In the database, there might be SQL statements with high utilization of CPU and IO: you can prevent them from being started so once they are quarantined they no longer consume system resources because they are terminated prior to their execution.

Note that SQL quarantines work only in 19c on Exadata and DBCS/ExaCS. Check out the Database Licensing Information User Manual:

3. You can quarantine a statement based on:

– SQL_ID and one of its execution plan
– SQL_ID and all of its executions plans
– specific SQL_TEXT

You quarantine a statement in 2 steps:

(A) create a SQL Quarantine by using (for example) DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID
(B) add thresholds by using DBMS_SQLQ.ALTER_QUARANTINE

Here are some examples and some more.

4. For some interesting and non-documented stuff check the article by Mahmoud Hatem entitled Oracle 19c : The QUARANTINE hint.

For instance, it shows how you can test by setting “_exadata_feature_on”=true in order to get SQL QUARANTINE feature to work on a non-Exadata box.

The following parameters can affect a quarantine kick off:

CPU_TIME
ELAPSED_TIME
IO_MEGABYTES
IO_REQUESTS
IO_LOGICAL
PHV

There is also the special value called ALWAYS_QUARANTINE.

5. All details can be of course found in the SQL Quarantine documentation.

The following columns of the V$SQL and GV$SQL views show the quarantine information of execution plans of SQL statements:

– SQL_QUARANTINE: This column shows the name of the quarantine configuration for an execution plan of a SQL statement.
– AVOIDED_EXECUTIONS: This column shows the number of times an execution plan of a SQL statement was prevented from running after it was quarantined.

There is a new view in 19c called DBA_SQL_QUARANTINE which displays information about quarantine configurations.

Good news also for admin users of the Autonomous Database: you have full access to the feature:

And note that a DBA can also transfer quarantine configurations from one database to another database using the DBMS_SQLQ package subprograms: CREATE_STGTAB_QUARANTINE, PACK_STGTAB_QUARANTINE, and UNPACK_STGTAB_QUARANTINE.

If you plan to visit Oracle OpenWorld this year (September 16-19, 2019), as of now, there are a couple of presentations on SQL Quarantine:

– Oracle Database 19c: SQL Tuning Using Plan Stability Methods SPM/SQL Quarantine: by Soumendra Paik, Senior Principal Technical Support Engineer, Oracle
– What’s New in Oracle Optimizer, by Nigel Bayliss, Senior Principal Product Manager, Oracle

Question: how do you quarantine a statement based on a sub-string of the query? Like, how can you quarantine statements starting with ‘select *‘?

What else besides Automatic Indexing is new in Oracle Database 19c?

In DBA, Init.ora, New features, Oracle database, Security and auditing, SQL on April 23, 2019 at 10:53

Automatic Indexing (AI) is probably the most important new feature of Oracle Database 19c and AI is arguably one of the best example of AI in the IT industry. But there is much more that came along with 19c.

Here is my choice of the top 10 least known (for now at least) new features of Oracle Database 19.2 (in no order whatsoever):

1. Security: if the database password file name or location has been changed and the metadata cache needs to be refreshed with the details of the updated database password file, you can do it simply with the following command:

SQL> alter system flush passwordfile_metadata_cache;

System altered.

In 19c, most of the Oracle Database supplied schema-only accounts now have their passwords removed to prevent users from authenticating to these accounts. But DBAs can still assign passwords to the default schema-only accounts.

2. SQL: the LISTAGG aggregate function now supports duplicate elimination by using the new DISTINCT keyword.

There are also a new bitvector SQL operators can be used to speed up COUNT DISTINCT operations within a SQL query.

3. Active Data Guard DML redirection: You can now run DML on the Active Data Guard standby database. When run on the standby side, the update is passed to the Primary database where it is executed and the resulting redo of the transaction will update the standby after which control will be returned to the application.

4. There are 6 new init.ora parameters in 19c. DBAs can now tune in DataGuard the amount of wait time by using DATA_GUARD_MAX_IO_TIME and DATA_GUARD_MAX_LONGIO_TIME. You can check details for all six:

ADG_REDIRECT_DML
DATA_GUARD_MAX_IO_TIME
DATA_GUARD_MAX_LONGIO_TIME
ENABLE_IMC_WITH_MIRA
LOB_SIGNATURE_ENABLE
MAX_DATAPUMP_PARALLEL_PER_JOB

5. You can now create Materialized Views containing bitmap-based COUNT(DISTINCT) functions. This means that the MVs are based on SQL aggregate functions that use bitmap representation to express the computation of COUNT(DISTINCT) operations.

SQL> create materialized view MV_SALES as
select CLIENT, 
BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(PRICE),'RAW') bm_price
from SALES
group by CLIENT,BITMAP_BUCKET_NUMBER(PRICE);  

Materialized view created.

6. Looks like there is now automatic resolution of SQL plan regressions: “SQL plan management searches for SQL statements in the Automatic Workload Repository (AWR). Prioritizing by highest load, it looks for alternative plans in all available sources, adding better-performing plans to the SQL plan baseline. Oracle Database also provides a plan comparison facility and improved hint reporting.”

7. Real-Time Statistics is also a new cool feature. “Oracle automatically gathers online statistics during conventional DML operations. Statistics can go stale between execution of DBMS_STATS statistics gathering jobs. By gathering some statistics automatically during DML operations, the database augments the statistics gathered by DBMS_STATS. Fresh statistics enable the optimizer to produce more optimal plans.”

8. Hybrid Partitioned Tables: now large portions of a table can reside in external partitions, for example in the Oracle Cloud. With this new feature, you can also easily move non-active partitions to external files, such as Oracle Data Pump files, for a cheaper storage solution. Hybrid partitioned tables support all existing external table types for external partitions: ORACLE_DATAPUMP, ORACLE_LOADER, ORACLE_HDFS, ORACLE_HIVE.

9. Data Pump:

– Oracle Data Pump allows tablespaces to stay read-only during TTS import.
– Oracle Data Pump can work in test mode for transportable tablespaces.
– Oracle Data Pump supports resource usage limitations with the introduction of two new parameters: MAX_DATAPUMP_JOBS_PER_PDB and MAX_DATAPUMP_PARALLEL_PER_JOB.
– Data Pump no longer enables secure, password-protected roles by default. Beginning with 19c, you must explicitly enable password-protected roles for an individual export or import job. A new command-line parameter has been added, ENABLE_SECURE_ROLES=YES | NO that can be used to explicitly enable or disable these types of roles for an individual export or import job.
– The new Data Pump command-line parameter CREDENTIAL enables secure import into a managed service from dump files in the Oracle Object Store Service.

10. SQL Quarantine: “SQL statements that are terminated by Oracle Database Resource Manager due to their excessive consumption of CPU and I/O resources can be automatically quarantined. The execution plans associated with the terminated SQL statements are quarantined to prevent them from being executed again.”

Check out the new Oracle package DBMS_SQLQ – cool stuff!

An Interview with Kerry Osborne

In DBA on March 19, 2019 at 15:28

No introductions needed, the only and only Kerry Osborne:

Tell us what you remember about your first experiences with Oracle 2

I was just out of college – my first job. The first day they handed me a set of Oracle manuals (I think there was only one book actually). Maybe a half an inch thick. It was running on Vax/VMS and it stack dumped on a pretty regular basis. I figured out a couple sure fire ways to make it it crash. 🙂 The company was already using it and had written a bunch of Fortran OCI stuff that was pretty cool. (I had actually worked on some of the Fortran code while I was still in college – that’s how I got the job). That system had a fairly large subroutine library in object file format only. They had some how lost the source code. Fortunately we had a little bit of documentation that at least showed the input and output parameters of each subroutine. It was my first experience with having to reverse engineer software, which came in handy later in my career when I started trying to figure out how the Oracle optimizer worked. 🙂

Your ponytail and cap Kerry are in a way trademarked by you. How did it all start?

I don’t really know. I just started wearing a cap most of the time after I got out of college and got used to it and now it’s just like putting on my pants in the morning. No I don’t sleep with my cap on (unless I’m laying on my back and need something to block out the light). 🙂 Tim Hall wrote a very funny blog post about my cap at one point. https://oracle-base.com/blog/2014/09/26/an-interview-with-kerry-osborne/

The pony tail thing is a bit of a nod to my native American heritage. My great grandmother was a Cherokee (just a handed down story, but I believe it to be true). But the real reason I let my hair grow is that my brain wants to optimize pretty much every process that I encounter. So at one point I went to get a haircut and as I was sitting in the barber chair, I started thinking about how to optimize that process. I actually wrote about it on my blog here: http://kerryosborne.oracle-guy.com/2009/05/shopping-trip/ But the bottom line is that I calculated that over the course of my life I would loose almost a year by repeatedly going to get my hair cut. And since the quickest way to do anything is not to do it at all. I stopped.

Which is your favorite Oracle version?

I don’t really have a favorite. (I guess 11.2.0.4 is my favorite of the ones commonly in use at the moment)

Arguably you are the most famous DBA worldwide, how many presentations have you given in total at conferences, meetings, webcast, etc.

Nah – I’m definitely not! I don’t really know how many talks I’ve delivered. I don’t keep up with that stuff very well (that’s why I’m not an active Oracle Ace Director anymore actually – too time consuming to keep up with that and fill out the required paperwork). Oddly enough, when I was in my 20’s, public speaking was my greatest fear. In fact, the first paper I submitted to IOUW (the predecessor to OOW) got accepted, but I backed out on doing the presentation because I was too scared. I had to convince one of my co-workers to go and deliver the paper for me. I worked hard for a few years to try to rid myself of that irrational fear, but the thing that really allowed me to get over it was when I started teaching week long classes. There’s nothing like repetitive practice, and standing in front of a class 8 hours a day for a week at a time provided plenty of that. So I still have a little pit in my stomach before every talk, but I actually enjoy it now. 🙂

How do you see the future of the Oracle database both on premise and in the cloud?

I think Oracle will continue to be a vital platform in enterprises. I believe they have the best SQL engine on the planet. They are great at managing transactions, consistency and concurrency. So for those reasons, and the fact that there are tons of existing applications built on their platform, I believe that they will be around and highly valuable for the foreseeable future. I think that many systems will eventually run in public clouds but I don’t believe that the whole landscape will migrate to the cloud. I believe most companies will end up with a hybrid cloud strategy, combining on-prem with multiple public clouds. However, there are a number of other tools available that are making a major push into the Data Warehouse / Reporting / Analytics space. So this is the area where I see the most competition for Oracle.

Do you think the Autonomous Database puts at risk the jobs of many DBAs around the world?

Not really. I do think that the job of a DBA is changing. There are various flavors of DBA’s and some of them (maybe all of them) will need to change their focus. By that I mean, that they’ll need to be more conversant with development. They need to be automating repetitive tasks. They need to be solving higher level problems. But I don’t think Oracle’s Autonomous Database offerings will eliminate a lot of jobs. It will just change them. That said, I think the biggest area of change will be for the people that are doing tasks that are repetitive. Adding storage, installing databases, patching databases, etc… These types of activities will be automated. So start learning how to automate them yourself instead of waiting for someone else to do it. That sets you up with the skill to automate other tasks and moves you into a space that will have more demand. And by the way, that’s basically a development task, so at a very high level, I guess I’m saying that I think people should be moving away from infrastructure type jobs and towards development oriented jobs.

How did you manage to create Enkitec as an exquisite top skill group? Such a talent is seldom concentrated in one single company!

The word exquisite is a bit over the top. 🙂 But I am very proud of the team that came together at Enkitec. I did a short 10 min Ted Talk styled presentation at OakTable World on recruiting world class talent a few years ago. A couple of the key points were that there are some basic requirements – team members need to be smart, have a good technical background, have worked for a reasonably long period of time (10+ years), and have excellent communication skills. But they need 2 other things that are even more important. They need to have the ability to apply their skills to creatively solve tough problems. This is not a given based on having the basic requirements covered. It’s a unique characteristic, which we can improve on, but which I believe is at least partly an innate ability. The 2nd important requirement is that they need to NOT have a big ego. The ability to work together as a team and share knowledge requires people that don’t view themselves as smarter/better than the people around them and with a view that everyone around them has value and something to offer, something they can learn from. This was one of the keys to building the team that we built. Nothing is perfect, but I am very proud of the results and the team we built. There is way too much to say in a brief answer, so maybe it’s better to just point to that video: https://www.youtube.com/watch?v=vI9do1DEm08

You have probably given more keynotes than anyone else. Which are you recent and upcoming topics of interest?

I don’t think that’s true, but I do a few here and there. I just did one called “How to Stay Relevant” at the Rocky Mountain User Group Conference in February. I’m also speaking at the UKOUG’s conference in Dublin in April and I am hoping to do another keynote at the Bulgarian User Group meeting this summer. The “How to Stay Relevant” talk is about the disruption that’s currently going on in our industry and it focuses on the effects on those of us who work with data (and Oracle specifically). In terms of what I’m interested in these days, distributed systems are high on the list. Any platform that moves the work to the data and that allows access via a SQL interface is of interest to me.

What is your take on ML, AI and Big Data? Are these just new trends in IT that come and go?

No, I don’t think they are just buzz words! Unfortunately the hype cycle can quickly cause over-used terms to become a turn off. But the general idea of artificial intelligence is not going to just fade away as some trendy ideas do. So with regards to AI (and the subset of Machine Learning) I think we are still early in the adoption and will see more and more of it. We’ll also see a lot of things that are labeled as AI, which are really not. So we’ll have to learn to be educated consumers. Oracle’s optimizer does some things that could be viewed as AI for example. The ability to recognize that it’s made a mistake in it’s determination of a plan is very smart, but also very narrowly focused and does not really use “AI techniques” to make those determinations. But I have no doubt that the kernel developers are thinking about ways to forge ahead into that space. And there are a lot of successes already, specifically with narrowly focused applications (so called ANI). The champions of most intellectual games are AI engines now, just as an example. Artificial General Intelligence is still a long way off in my view, but the Law of Accelerating Returns could kick in and blow us all away much more rapidly than we expect.

Big Data is also a trendy term that has been around a long time and that has been over used. But when I think of big data these days, I think more about how we deal with the ever increasing volumes (often produced by machines) that we are called on to manage. We also have increasingly complex kinds of analytics and more advanced processing (AI/ML) that we need to do on the large data sets. This requires highly scalable, distributed computing platforms that are capable of dealing with the volume in a predictable (scalable) way as well as in an economical way. Hadoop is somewhat synonymous with the term Big Data because it was the first (and still is the most popular) such framework in the open source community. As with most technology swings though, Hadoop has gone through a lot of hype (when people thought it would solve all of our technological problems), then a period of disillusionment (when everyone realized this technology, like all others, has it’s limitations/tradeoffs), and is now entering the phase of reasonable expectations, which has led to widespread adoption. This is the classic hype cycle that Gartner uses so heavily. But my view is that Big Data (Hadoop or other proprietary or public cloud based offerings) is definitely not going away, and in fact I believe it will continue to be a huge force of change in our industry.

What single piece of advice would you give to the new DBAs entering the database profession?

Focus on the data, not the database. …

Hard to just leave it at just that: Focus on supporting or becoming a cross over developer. Focus on architecture and optimization topics. … But the one liner is very quotable, right?

Some DBAs come from sysadmin background and some from apps/applications. How did you become a DBA?

I started as an application developer. Then I started getting frustrated with the lack of control (power) to do what I wanted at the database level and I moved into a DBA role. Then I got frustrated with having to ask sysadmins for help and I became a sysadmin. Then I started doing jobs that combined all 3 aspects. I eventually got focused on optimization of existing systems which combined all of the jobs in my background and I started to learn about the internals of Oracle. And the more complicated the optimizer became, the more important it was to know how it made it’s decisions. I never was the absolute best at any of these jobs btw, but I think being competent in all these areas gave me a lot of advantages that I wouldn’t have had otherwise. Probably the main advantage was that it made it possible for me to look at problems from multiple angles. I’ve worked with a ton of DBAs over the years and some of the very best are the guys and gals with multiple skills sets (Network Admin/DBA, Storage Admin/DBA, Unix Admin/DBA, Developer/DBA, …) And of course having experience building software is a huge plus for anyone in the “computer” business. It gives you the ability to think to yourself, “Now if I had been the developer on this, how would I have coded it?”. This is an extremely powerful view into problems when trying to resolve software issues.

If you can add one new feature to the Oracle database, what would that be?

There are too many features already. Focus on fixing the bugs!

Tell us about Gluent and its flagship product?

I thought you’d never ask. …

We founded Gluent to address one of the biggest issues we’ve seen over the last decade or so in our work with large databases in the enterprise space. Fundamentally, Gluent allows existing applications to take advantage of big data platforms, and here’s the most important part, without re-writing the applications. I think the reason for founding the company is best illustrated by a story about a large medical company that reportedly had one of the largest Teradata clusters in the world. The whispered cost was $40M a year maintaining that system and there was a desire to migrate off that platform for cost reasons. A 6 month project was undertaken to see if it was feasible without degrading the performance of the system. The results were pretty amazing. The performance was increased on average over 1000X across a huge number of queries (all the queries from a days worth of work). The annual cost of the proposed solution was about $1M. Nevertheless, the company decided not to re-platform the system. The reason? They were calculating a 3 year ROI, and despite the fact that the projection showed a saving of nearly $120M, their assessment of the risk and cost to re-write the very complex application out weighed the savings. This is an extreme example of a story that plays out every day in the enterprise space and is the reason why Gluent was initially formed.

We have a few core beliefs:

We believe data volumes are exploding
We believe that legacy apps are not going away
We believe that data will continue to be born in silos but will need to joined with other data
We believe that most new apps will be built on distributed back-ends and on data that is stored in open data formats
We believe that legacy skills need to be transitioned, not thrown away
We believe that most data will migrate to a centralized storage/compute platform
We believe that companies need to be able to change the wings on the plane while it is in the air

After an implementation of Gluent, the resulting system has a hybrid architecture which blends a distributed back-end with a relational database. The goal being for both components to do what they are best at. The relational database being really good at managing transactions, concurrency and consistency and the distributed back-end excelling at throughput, scalability, and doing so economically. It also allows hybrid cloud deployments where the database component can remain on-prem while the distributed back-end can be deployed in the cloud with some or all of the data in relatively low cost object storage. This sort of architecture allows a great deal of flexibility around data placement between some in object storage, some local to a distributed cluster, and some local to the database tier. It also provides flexibility with regards to security, as some (sensitive) data can be contained strictly on-prem, while less sensitive data is moved out to the cloud, as an example.

The ultimate goal is to connect any applications to any data and become a data sharing platform. We believe that data will continue to be born in silos, but that it should be synchronized (once) to a distributed backend and then presented to any engine/system that wants to access it (and has permissions to do so). We also have as a gleam in our eye, the concept of Gluent as a Service. Which would remove the need to manage the distributed backend. We are already certified on S3/EMR for example (Azure and Google coming in the next couple of months) which allows up to spin up a cluster on demand as needed and shut it down when not in use. We are not quite there yet with the complete Gluent as a Service offering, but we are getting close. 🙂

I could go on (and on and on …). But hopefully you get the idea. We believe that our approach allows existing applications to take advantage of these magical new platforms, but without breaking anything. That’s our secret sauce.

Which is the biggest mistake you have ever made as a DBA?

Ha – that made me laugh! Early in my career, when I first gained sysadmin privileges, I was still doing DBA and development work. So one night I was working remotely via a 1200 baud modem on a program of some sort (probably Fortran OCI accessing Oracle V3). Anyway, each test run was taking several minutes and I noticed that there were several other processes running night time batch jobs on the system and they were burning a lot of CPU. So I decided that make use of my newly gained sysadmin privileges and I upped my priority so that my test runs wouldn’t have to compete so much for CPU time. Well in those days, the operating systems weren’t as smart about prioritization as they are now. So my process was effectively getting all the CPUI time until it completed (and was much faster – yay!). Unfortunately, at some point I made a change that threw my code into an infinite loop. I tried to break out after a few minutes and unfortunately the modem connection dropped. Since my original process was getting all the available CPU time, there wasn’t even enough left to get logged back into another session to kill the rogue process. The program finally died when it had used up all the disk space. The real sysadmins were not happy with me the next morning!

If you can ask yourself a question, and not answer it, what would that be?

What do you want to be when you grow up?
I still haven’t figured that out!

AI in AI: Artificial Intelligence in Automatic Indexing

In DBA on March 7, 2019 at 17:30

“By far, the greatest danger of Artificial Intelligence is that people conclude too early that they understand it.” — Eliezer Yudkowsky

CNBC: 40% of A.I. start-ups in Europe have almost nothing to do with A.I.

Oracle 19c brings one key feature which does not exist in database systems: Automatic Indexing. Something very similar does exit in Azure SQL Database but with some limitations.

For a very long time, both DBAs and Developers, have been struggling (really struggling) with what indexes should be created, what type of indexes they should be created as and what indexes should be dropped from the database. Automatic Index creation (AI Creation) means the explicit creation of new indexes and also dropping existing, unused indexes without human intervention.

In the long run, this is to be arguably one of the most important features in the Oracle database. I have already covered the basics in a previous blog post entitled Automatic Indexing in 19c. The expert system works in the following way passing through the stages of identification, verification and decision making:

Based on the captured workload, Oracle’s expert system identifies the index candidates which are created first as UNUSABLE & INVISIBLE (metadata only):

Then, there is the verification process. Some indexes will become VALID (physical segments are created) but will still stay INVISIBLE to the optimizer.

Later, Oracle decides if some of these indexes can become VISIBLE and this happens based on how the performance increases and how these new indexes affect other activities in the database.

Look for possible error using this query:

select EX.execution_type, EX.execution_name,F.message
from DBA_ADVISOR_FINDINGS F, DBA_ADVISOR_EXECUTIONS EX 
WHERE F.EXECUTION_NAME = EX.EXECUTION_NAME AND F.TYPE = 'ERROR';

If you need a detailed report from (say) the last 30 days, here is how to obtain it:

spool report
select dbms_auto_index.report_activity(sysdate-30,null,'text','all','all') report from dual;
spool off

A sample report shows beside the index candidates, space used, fatal errors also the overall improvement factor and also the SQL statement improvement factor:

When using/implementing the feature, have in mind the following:

– AUTO_INDEX_MODE must be set in every PDB: even set on container level it is not cascading to the pluggable databases
– Manually created indexes are nor dropped by default, you need to set separately AUTO_INDEX_RETENTION_FOR_MANUAL
– Follow the expert system runs from CDB_AUTO_INDEX_EXECUTIONS
– Hint for an INVISIBLE VALID index (for example /*+ index(clients SYS_AI_64uvm6wb5168u) */): I have seen how the index becomes VISIBLE in a second (if really useful)

For more details, check the recent bog post 19c Auto Index: the dictionary views by Franck Pachot

Automatic Indexing is by far one of the best examples of Artificial Intelligence and Machine Learning in the IT Industry. Really! I still remember a 5TB Oracle database I used to administer (mission critical one, a 24×7 system) where the indexes were almost 4.5TB in size while the real data was like half a TB only.

Automatic Indexing in 19c

In Autonomous, Database tuning, Databases, DBA, Oracle database on February 18, 2019 at 17:38

One of the most impressive new features of Oracle Database 19c is Automatic Indexing. Arguably, this is the most interesting innovation in the database world for a rather long time.

I remember some years ago when a DBA asked me at an Oracle conference: “Julian, why are half of the presentations at Oracle database conferences only about performance tuning? Is the Oracle database performing that badly that people should tune it all the time?” Sigh…

With 19c and ADB (Oracle Autonomous Database), things look very different now, don’t they? Automatic Indexing provides what database systems need: continuous optimization of the database workload, stable & solid performance and almost no human interaction. Let me share some of my early experience with Automatic Indexing and where human interaction is needed.

For now (February 18th, 2019), Oracle 19c is only available on Exadata (Linux 7.4) and in order to enable Automatic Indexing you need to do the following:

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

As you can guess from the picture above, the so called expert system of Automatic Indexing runs every 15th minute for as long as one hour. Note that I disabled the job from 4:43 till 5:56. The Resource Manager plan limits the task to 1 CPU only and the next run is skipped if the job has not been completed within the 15 minutes.

Here are details on how Automatic Indexing works but what is most important to remember is as follows:

– The auto index candidates are created as invisible auto indexes
– If the performance of SQL statements is not improved from the auto indexes, then the indexes are marked as unusable and the corresponding SQL statements are blacklisted
– Auto indexes cannot be used for any first time SQL run against the database
– Auto indexes are created as either single, concatenated indexes or function-based indexes and they all use advanced low compression
– The unused auto indexes are deleted after 373 days (can be changed)
– The unused non-auto indexes (manual indexes) are never deleted by the automatic indexing process but can be deleted automatically if needed

The Auto Indexing can be disabled at any time or can be set to set to reporting mode (new auto indexes as created asinvisible indexes, so that they cannot be used in SQL) with the following commands:

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

 

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');

Here is a way to ask Oracle to create new auto indexes in a separate tablespace called AUTO_INDEX_TS:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_INDEX_TS');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

You can easily check the configuration for Automatic Indexing for the root container and the PDBs from CDB_AUTO_INDEX_CONFIG;

If you need a report of what happened during the expert system activity (either last 3 days or during the last activity), here is a way to generate it:

set long 300000
select DBMS_AUTO_INDEX.REPORT_ACTIVITY(SYSTIMESTAMP-3,SYSTIMESTAMP,'TEXT','ALL','ALL') from dual;
select DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY('TEXT','ALL','ALL') from dual;

These are the most important views about Auto Indexing:

DBA_AUTO_INDEX_EXECUTIONS: history of execution of automatic indexing tasks
DBA_AUTO_INDEX_STATISTICS: statistics related to auto indexes
DBA_AUTO_INDEX_IND_ACTIONS: actions performed on auto indexes
DBA_AUTO_INDEX_SQL_ACTIONS: actions performed on SQL statements for verifying auto indexes
DBA_AUTO_INDEX_CONFIG: configuration settings related to auto indexes
DBA_AUTO_INDEX_VERIFICATIONS: stats about PLAN_HASH_VALUE, AUTO_INDEX_BUFFER_GETS, etc.

The new package DBMS_AUTO_INDEX can be used for 3 main things:

1. Configuration of the parameters related to Auto Indexing
2. Drop *all* the indexes except the ones used for constraints
3. Report the activity of the “expert system”:

Finally, here are some additional resources:

Automatic Indexing in Oracle Database 19c
Oracle Database 19c is now available!
Managing Auto Indexes

How to check if I have any auto indexes in my database: select auto, count(*) from dba_indexes group by auto;