12

Oracle 19c Automatic Indexing: Currently Broken In Autonomous ATP Databases? (Wh...

 3 years ago
source link: https://richardfoote.wordpress.com/2020/12/17/oracle-19c-automatic-indexing-currently-broken-in-autonomous-atp-databases-what-in-the-world/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

Oracle 19c Automatic Indexing: Currently Broken In Autonomous ATP Databases? (What In The World) December 17, 2020

Posted by Richard Foote in 19c, Automatic Indexing, Autonomous Transaction Processing, Oracle Indexes.
trackback

I’ve been playing with the free tier Oracle Autonomous Databases for quite some time, but unfortunately in recent times, I’ve hit a bit of a snag. The Automatic Index capability appears to be currently broken…

The Automatic Indexing task appears to have stopped running, yes even with the AUTO_INDEX_MODE set to “IMPLEMENT” and with a range of SQL workloads in badly need of new indexes.

I even terminated and created a new Autonomous ATP environment and once all set for Automatic Indexing (by setting AUTO_INDEX_MODE to ‘IMPLEMENT’, AUTO_INDEX_SCHEMA to a number of schemas and running index deficient SQL workloads), still no go, with the Automatic Indexing tasks no longer running.

My environments are specifically the always free Autonomous ATP environments running 19c from the Sydney data centre. 21c is not yet available in Sydney, but 19c is meant to be the stable recommended environment, so…

Note: As it’s the Autonomous Database environments, the ADMIN power user only has restricted privileges and can’t manually run procedures such as dbms_auto_index_internal.task_proc().

I’ve had a look through MOS and was unable to find anything of use.

I’ve communicated with a couple of folks and they also seem to be having similar issues.

If anyone else is having problems running Automatic Indexing in the Autonomous Database environments or have suggestions on how to address these issues, would love to hear from you.

Comments»

83556eda02c5e838f2c42959fb84ce7e?s=32&d=identicon&r=G 1. Jens - December 21, 2020

Hi Richard,

Same problem here…
I´m running an ATP DB in the Frankfurt Datacluster.

I tested this example:
https://richardfoote.wordpress.com/2019/08/19/oracle-19c-automatic-indexing-my-first-auto-index-absolute-beginners/

I can´t run this:
exec dbms_auto_index_internal.task_proc(true);

If I execute the query several times in the example and wait several 20 Mins the report still shows this:

REPORT
——————————————————————————-
GENERAL INFORMATION
——————————————————————————-
Executions completed : 0
Executions interrupted : 0
Executions with fatal error : 0
——————————————————————————-

Leave a Reply Cancel reply


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK