Announcing Oracle Database 23ai : General Availability

General Availability 

We are pleased to announce the general availability of Oracle Database 23ai. Over the last four years, Oracle Database Development has worked hard to deliver the next long-term support release of the Oracle Database, with a focus on AI and developer productivity. Given the focus on AI in this release of the database, we decided to change the database's name from Oracle Database 23c to Oracle Database 23ai. This reflects the focus of this release as well as the climate in which it is being released. 

Game-Changing Innovations

Oracle Database 23ai has focused on three key areas.

◉ AI for Data

◉ Dev for Data

◉ Mission Critical for Data

In this Blog, we'll describe some of the critical features of this release that highlight our work on these important focus areas and provide details when you see them on your platform of choice.

AI for Data

Our goal with AI for Data is two-fold:

1. To enable app developers to add AI functionality into their data-driven applications more easily

2. To incorporate GenAI capabilities into our products so that all users of Oracle Database, from data analysts to app developers and DBAs, can be more productive.

This is achieved by employing AI to streamline Oracle's data management processes, offering AI algorithms for uncovering new insights from your data, facilitating natural language interactions for database queries, and empowering users to store AI-generated vectors for swift and efficient object similarity searches. Let's delve into these features further.

Augmenting a new generation of AI models

People are feeling the impact of AI Large Language Models (LLM), exemplified by offerings such as ChatGPT, Google Gemini, Cohere, and LLaMA. Whilst LLMs are having a profound impact on the way we perform everyday tasks, one of the challenges that these engines face is that they are restricted to answering questions in the specific time window of their creation. They also lack context and details of the data held within your organization. To make these and other LLMs useful, we need to augment their existing understanding with data relevant to the questions you ask about the data held within your database. 

In Oracle Database 23ai, we are introducing AI Vector Search, a powerful new technology that enables you to leverage a new generation of AI models to generate and store vectors. These vectors, sometimes referred to as embeddings, are multi-dimensional representations of documents, images, videos, sound, etc. By encoding these objects as vectors, you gain the ability to look for similarities between them using mathematical calculations. The real power of Oracle Database23ai's solution is that you can combine these similarity searches with searches on your business data using simple SQL. Anyone with a basic understanding of SQL can create a powerful statement combining similarity and other search criteria. These types of queries provide LLMs with additional context, augmenting their knowledge and making their responses more accurate and relevant to your customers' or organizations' questions. To enable this functionality, we've added a new data type, new vector indexes, and extensions to the SQL language to make it incredibly simple to query vectors alongside your existing business data by leveraging Oracle Database 23ai's advanced analytical capabilities.

Secure encoding of data

To enable the considerable benefits that LLMs offer, we first need to enrich our understanding of our datasets and the objects within them. The models to encode these objects are potentially large and complex pieces of code. As a result, it has become common practice to offload this processing to third-party services. This can require you to share potentially sensitive information with third parties with the inherent risks that entails.

To ensure the secure encoding of objects, Oracle Database 23ai allows you to load your own trusted AI models directly into the database through its support of the ONNX standard. This enables Oracle Database 23ai to encode objects as they are inserted into the database. This enables the secure near real-time inferencing of objects and ensures that data does not need to be extracted from the database and handed off to a third-party service.

Asking questions naturally

While SQL is an incredibly powerful language, it can be a little challenging to get started with. To enable a broader range of users to ask the most complex questions of the Oracle Database, we are integrating with LLMs such as Cohere and Llama to allow you to ask questions using natural language.  So you can simply ask a question like "Show me the last 4 quarters' sales of products that are popular with the younger generation". Oracle Database 23ai will share the table's metadata to answer the question with the LLM along with the user's question. The LLM understands the meaning of "younger generation" and converts it into a data range as a filter in the SQL query, which it returns to the database. We, of course, aren't limiting Oracle Database 23ai to just allowing you to query the information within the database. You'll also have the ability to ask questions on all aspects of the Oracle Database. 

Finding new insights in data

Since the release of Oracle Database 9iR2 over 20 years ago, the database has featured built-in Machine learning (ML) algorithms, enabling you to quickly look for patterns and trends in data held within its tables and predict customer behaviors. These ML algorithms give you the ability to use sophisticated ML algorithms without the need for complex ETL operations to extract the data and write back results. These models could then be used to classify, cluster, and predict as your data is inserted or loaded into the Oracle Database, providing tangible business benefits like customer recommendations and fraud detection. Since that release, we've improved and added new ML algorithms and capabilities to the Oracle Database, creating one of the industry's most sophisticated and capable data mining platforms.

Available in all editions of the Oracle Database

AI Vector Search is part of Oracle Database 23ai and is available at no additional charge in Enterprise Edition, Standard Edition 2, Database Free, and all Oracle Database cloud services.

Feedback from early testers

AI Vector Search generated a great deal of interest even before the launch, and many customers and partners were keen to try out and provide feedback on the latest capabilities like AI Vector Search. Here's a sample of the feedback we received.

“We are happy to see AI Vector Search added to Oracle Database. We appreciate that we can run AI Vector Search in the same Oracle Database as our other workloads, which allows us to provide a reliable and secure solution.”

Shinichiro Otsuka, NRI Certified IT Architect, Nomura Research Institute, Ltd.

“AI Vector Search changes the way we think of the business by bringing AI to your data: for instance we can use natural language to query our documentation to find answers to questions customers are asking. This makes the process of serving customers much faster and the quality of the service improves as well. Being able to use the power of Generative AI with our own data makes a huge difference."

Heli Helskyaho, CEO, Miracle Finland Oy

“We have been consulting on various business transformation projects using advanced digital technology. Oracle Database 23ai AI Vector Search is very simple to implement by adding a vector column to the current relational database system. I am thrilled that the vector data is within a single record with existing data so that similarity reordering can be done with a single SQL statement. This makes it easy to combine large language models and company data to create and operate enterprise-level retrieval augmented generation systems.”

Shigeru Urushibara, Chairman, UL Systems Inc.

"With the decreasing domestic workforce in Japan, XCAT‘s customers want to adopt labor-saving measures. Oracle Database 23ai’s AI Vector Search allows customers to use SQL for vector retrieval, making it an excellent tool for engineers with little or no AI knowledge to handle vector data. In addition, the ability to manage AI data at no additional cost is a major attraction for many Oracle Database customers."

Mitsunori Yamane, President, Cross Cat Co., Ltd.

Dev for Data

Oracle Database 23ai focus was to make the experience of developing applications simpler by removing the complexity associated with your database interaction. Removing complexity from the application development process means you get more opportunities to focus on the intricacies of creating elegant applications that meet your customer's requirements rather than getting bogged down in technical details. Moreover, reducing complexity can lead to faster development cycles, this is crucial in today's fast-paced digital landscape, where market demands can shift rapidly.

JSON or Relational, a difficult choice?

Oracle 23ai introduces a number of key technologies to reduce the complexity for developers. Perhaps the most innovative and important of them is the introduction of JSON Relational Duality. This technology allows you to leverage the power of relational and the simplicity of JSON development approaches all within a single app. JSON provides an elegant data modeling approach where all of the relevant data needed to answer a query is contained in a single object without the need to perform complex joins across tables. The relational approach provides a flexible, storage-efficient, consistent data model that is easy to query with languages such as SQL. Historically the problems come when you are forced to choose which database modelling approach to take at the start of your application lifecycle. Decisions made at this stage may have implications much later on as the business requirements change. The beauty of JSON Relational Duality is that you can benefit from both approaches without the need to use complex and unwieldy Object Relational Mapping frameworks (ORMs). 

By creating simple JSON Duality views on top of relational tables you can build applications that use documents to query and update the underlying data sets. Data duplication, a fundamental issue in document modeling, is avoided, and even the complexities of database-level concurrency control are hidden from you as Oracle Database 23ai manages document-level serializability on your behalf. You can continue to use simple HTTP PUT, POST, and GET operations or use language-specific API such as Oracle's SODA API or even MongoDB API directly against the Oracle database. With JSON Duality Views the old phrase of "You can't have your cake and eat it" no longer holds true.

SQL just got a little easier

SQL remains one of the most popular development languages in use with application developers today. Its versatility when asking the simplest or most complex questions of your business data has made it the cornerstone of data management, a position it is likely to hold for the foreseeable future. That is not to say there are areas where it could be improved. In Oracle Database 23ai we listened to common enhanced requests and looked at what could be made simpler or offer more compelling ways to interact with the database. Examples of just a few of the SQL improvements we've made include the addition of two new datatypes "Boolean" and "Vector", we added "Data Usecase Domains" to allow you to enrich the meaning of datatypes used in table definitions, the removal of the need for "from dual" in simple SQL statements to improve readability, making it easier to aggregate over the "interval" datatype, the introduction of table value constructors to make it easy to specify multiple rows in insert select or merge statements. These are just a few of the many improvements we've made to SQL.

As with AI Vector Search, we also appreciate receiving a lot of useful feedback from customers and partners who participated in the Oracle Database 23ai Beta program. Here's a sample of the comments we received -

“JSON Relational Duality Views are a game changer for application development by finally solving the problem of object-relational mapping. And, we at last have ACID-compliant transactions in a multi-tier or web architecture.”

Peter de Vaal, Principal Consultant, Transfer Solutions

“JSON Relational Duality gives you the best of both worlds: Relational experts can work on the same data as Document database experts without having to learn a new skill. App developers have the flexibility they need, and database management is simpler. Oracle has taken a massive leap beyond other databases.”

Patrick Barel, Sr. Oracle Consultant, Qualogy

“Oracle Database 23ai with JSON Relational Duality Views and Oracle APEX have allowed us to create full-featured, standards-compliant applications for working with JSON healthcare data.”

Rade Pobulic, Technical Director, VDEL Informationstechnik & Consulting GmbH

Property Graphs modelling complex relationships

Graph databases provide a fresh perspective on modeling intricate relationships found in real-world scenarios. Yet, until recently, developers grappled with proprietary languages and having to ship data tp databases tailored for managing these relationships. This hindered the widespread acceptance of this groundbreaking technology. Fortunately, the industry has taken a collaborative approach to bring graphs to the masses, culminating in the establishment of standards. These standards empower developers with the versatility and familiarity of SQL, fostering a more accessible landscape. Oracle Database 23ai stands as the pioneering commercial database to embrace this newfound standard. With these advancements, we anticipate a significant surge in the integration of Graph databases within enterprise ecosystems.

As with JSON Duality Views, you can express your intent on how you would like to use your data held within existing relational tables or your JSON collections through the user of property graph views. With a simple view, you can enrich the data to model the potentially complex relationships that your data holds. A new SQL extension makes it much simpler to write powerful queries to find a deeper understanding of information that would have been hard to describe using standard SQL. We've also added a new set of sophisticated graph algorithms (shortest path, classification, connects, etc. ), which will be released shortly after GA.

The property graph Property Graph is available in all editions of Oracle Database 23ai.

Mission Critical for Data

Oracle Database is renowned for powering some of the world's most vital applications. Over the years, it has established itself as a stalwart guardian of businesses' most precious asset: data. Unrivalled in its breadth of features, Oracle Database excels in safeguarding critical systems against unforeseen downtime. A prime example is Oracle Real Applications Clusters (RAC), a cutting-edge technology that enables all-active, shared disk databases, ideal for the most mission-critical scenarios. Oracle Active DataGuard and Oracle GoldenGate offer unparalleled flexibility in crafting both homogenous and heterogeneous architectures for data replication. 

Shard better, faster, simpler

For use cases where businesses are looking to distribute their data over larger distances, either for regulatory reasons where the data of users must be stored in the country the customer resides in or to reduce the latency of access associated with globally distributed applications: We introduced support for database sharding in Oracle Database 12c. Sharding distributes a dataset across a number of Oracle databases but is managed as a single logical data set. This sharded architecture allowed the database to be scaled up or down by adding and removing shards and allowing Oracle to automatically rebalance the dataset. Oracle is able to do this online and with little impact on the business as the concept of sharding is baked directly into the database.

In Oracle Database 23ai our Globally Distributed Database technology introduces support for Raft replication. This is a consensus-based protocol that facilitates the automatic configuration of replication across all shards. With raft, replication holds copies called replication units across all of the shards. In the advent of a shard failure or reconfiguration, Oracle Database 23ai automatically rebalances the data. The new Raft protocol also enables sub-second failover to ensure continued business continuity. 

Oracle 23ai brings a number of improvements across the board for our high availability products, such as the new "Local Rolling Database Maintenance" to simplify the impact of rolling patches in a cluster by standing up another instance on the same node that is receiving the patch. We are also introducing support for a new Data Guard Per Pluggable Database (PDB) allowing Pluggable Database level disaster protection between two Container databases (CDB) with each CDB running an active workload. We've also improved the integration and management of PDBs within Oracle Clusterware. 

Caching data made simple

Caching data in the application tier can offer considerable benefits. It is often used to make applications more responsive and reduce the load on the database, as queries are offloaded to the cache typically located closer to the code. However, to achieve these benefits, developers face a number of challenges; one of the biggest is ensuring that the cache is consistent with the data held within the backend database or risk serving stale and potentially incorrect data. Developers are often forced to create complex code to manage these scenarios and often have to resort to design patterns such as "time to live" where data is periodically flushed from the cache and reloaded. These and many other challenges often mean the cache becomes difficult and expensive to maintain and, in some instances, a bigger problem than the one it was designed to solve.

In Oracle Database 23c we are looking to solve the problems of caches built against the Oracle Database with a new feature called "True Cache". True Cache is a diskless Oracle Instance running in front of the primary database. The True Cache instances leverage Active Data Guard technology to ensure it is automatically updated as data changes in the primary database instance. One of the most important features of this new caching technology is its simple configuration and minimal code changes that are needed to leverage the benefits it offers. 

Making SQL execution safer

One of the challenges that organisations face is continued attempts by malicious actors to steal data or disrupt their day-to-day activities. In Oracle Database 23ai, to prevent the execution of unauthorised SQL, be they via SQL-Injection attacks or simply by unauthorised staff, we are introducing SQL Firewall. SQL Firewall allows you to train the Database on what SQL is permitted to be executed. The "allow list" of SQL can then be used to either log unauthorised SQL or blog its execution. Additional criteria can be added to allow exceptions for IP ranges or authorised calling programs. The power of SQL Firewall is that it is part of the Oracle Database, and as a result, it imposes very little overhead on the execution of SQL. Setting up and training SQL Firewall is incredibly simple being able to be done from a GUI or via a few simple calls to stored procedures. 

Over 300+ new features

I discussed the cloud release of Oracle Database 23ai and described some of the 300+ new features. Some other notable features include 

• Java Script Stored Procedures
• Priority Transactions
• Data Usecase Domains
• Schema privileges
• Annotations
• Boolean datatype
• Developer Role
• Read-Only-Per-PDB Standby
• JSON Schema
• Real-Time SQL Plan Management
• Performance Improvements
• Lock Free Reservations
• Improved Microservice Support
• XML and JSON Search Index Enhancements
• And many many more

Platform Rollout

Oracle Database 23ai is now available on Oracle Exadata Cloud@Customer, OCI Exadata Database Service and OCI Base Database Service. It is also available in the Azure Oracle Database Service. For developers, Oracle Database 23ai is now available in Always Free Autonomous Database as well for download in the Autonomous Database 23ai Container Image and Oracle Database 23ai Free. We are also pleased to announce that Oracle GoldenGate 23ai and GoldenGate 23ai Free as also available for download. On-premises versions of Oracle Database 23ai for Linux (OL & RHEL 8/9) and Windows will be out shortly with other platforms rolling out over the coming months. For further details please check the Oracle Support note Doc ID 742060.1

The general availability of Oracle Database 23ai marks a new phase in this important release. Thousands of developers have contributed to its design, development and testing resulting in what we believe is the very best enterprise database in the market.

Source: oracle.com

Continue reading

Why run Oracle Database on Arm

Why run Oracle Database on Arm

CPUs or Central Processing Units have been the core talking point in the computing space. Moore's law ("the number of transistors in a dense integrated circuit doubles approximately every two years" still stays valid. Processor speed is always measured in terms of instructions per second. New CPU architectures now measure millions or billions of instructions per second. A rise in demand for mobile gadgets has also shaped the path of today's processors. Power consumption and heat production are two significant factors in a smaller form factor (mobile devices). In recent years, the technological landscape has witnessed a considerable rise in the popularity of Arm processors due to their unique low power consumption capabilities. RISC-based architectures, of which Arm is part, focus on simplicity and efficiency. The RISC-based architecture uses a simplified set of instructions, each performing a single operation, aiming to execute instructions quickly. Arm processors offer unique capabilities in the mobile and handheld segment, but they have now been realized to be helpful in the server market. In this blog, we will explore why Arm processors are better suited for desktops and servers and explore why relational databases can perform exceptionally well on Arm.

Arm-based computing started with mobile and embedded devices; the technology (Arm-based) has now evolved to support hyperscale data centers and cloud computing. Oracle began to offer Arm-shaped VMs because they scale linearly, provide predictable performance, and provide the highest density of cores, all at a lower price point. With Arm-based cloud computing, customers can run existing workloads less expensively and build new applications with superior economics and performance. To help developers transition, make, and run Arm-based workloads, Oracle and other cloud vendors have accelerated the Arm developer ecosystem with the best tools and platform possible. Arm can provide all the capabilities of x86 computing, including virtualization.

What about current workloads:

x86, for decades, has been a de-facto standard for running workloads of various types. Workloads over time have become quite complex, resulting in millions and billions of instructions per second. Arm, with its simple design, offers a complementary substitute for some of the workloads that run on x86 architecture. Oracle Databases running on x86 platforms can seamlessly migrate to Arm architecture using standard methods. Arm architecture provides the following benefits:

• Efficiency and Power Consumption: Arm processors are renowned for their efficiency and low power consumption compared to x86 processors. Arm's reduced instruction set architecture (RISC) approach enables processors to execute instructions more efficiently, resulting in improved power efficiency and reduced heat generation. By consuming less power, Arm processors can allocate more resources to managing tasks, benefiting the overall performance of a system.
• Technology trends: The needs of hyper-growth markets, such as cloud-based supercomputing and artificial intelligence, dictate that we turn to specialized hardware where we can add more servers into data centers while staying within energy budgets. Arm is highly customizable, meaning cloud providers can tailor chips to their needs rather than depend on chip vendors. Most cloud vendors are coming up with their implementation of Arm-based chips to fit their needs. For example, NVIDIA has introduced its Grace Hopper super chip, based on Arm, to deliver a CPU+GPU coherent memory model to accelerate AI and high-performance computing applications.
• Scalability and Customization: Some of the Arm processors available in the market boast 128 cores per processor. One of the Arm vendors boasts 192 processor cores, the highest core count of any available data center server processor. Due to their scalability and customization capabilities, Arm processors have gained significant traction in the mobile and embedded systems markets. Unlike x86, Arm processors are designed to be highly customizable, allowing manufacturers to tailor them to specific use cases. The higher core count also eliminates the need for dual CPU (x86-based systems). This flexibility enables system designers to optimize Arm-based systems for database workloads, enhancing performance and efficiency. Additionally, the modular nature of Arm-based systems allows for easier integration of different components, making them adaptable to various database requirements.
• Increased Parallel Processing: Relational databases often deal with large volumes of data and complex queries, necessitating efficient parallel processing capabilities. Arm processors, featuring multiple cores and superior multithreading capabilities, excel in this aspect. With the proliferation of multicore Arm processors, database operations can be parallelized, enabling faster query execution and improved overall database performance. This parallel processing prowess of Arm processors is particularly beneficial for demanding workloads in relational databases, where simultaneous execution of multiple queries is essential.
• Enhanced Memory Bandwidth: Memory bandwidth plays a crucial role in database performance, especially when dealing with high data transfer rates. Arm processors often feature memory architectures designed for optimal bandwidth utilization, which can improve the speed of data access and retrieval. This increased memory bandwidth contributes to a more responsive and efficient database system, enabling faster query execution and reduced latency.
• Cost-Effectiveness: Arm processors have established a reputation for cost-effectiveness, making them an attractive choice for organizations seeking to optimize their IT infrastructure. The Arm ecosystem offers manufacturers various hardware options, resulting in competitive pricing and increased affordability. For businesses running relational databases, deploying Arm-based systems can be a financially viable solution without compromising performance.
• Security: Ampere Altra processors based on Arm (available in Oracle Cloud) run on a single thread per core processor, eliminating potential thread-security issues and isolating workloads. When run as a single thread per core, there is no sharing of the execution engine, registers, and L1/L2 cache between threads, which minimizes the attack surface for exploits.

Why Database Engines on Arm:

• Customization for Database Needs: Arm develops the Arm architecture and licenses the IP to allow its licensees/partners/customers to subsequently build and sell the chip within their designs/products, such as system-on-chip or system-on-modules designs. SoC (system-on-chip) is an efficient design model where significant components of a computer or electronic system reside on a small, low-power board. This approach differs from other CPU manufacturers, such as Intel or AMD, that design and manufacture their chips. The ability to license Arm design gives database vendors, like Oracle, the flexibility to design their chips based on Arm architecture.
• Scalability: Database engines do require scalability. Oracle Database runs high transactional applications and requires vertical and horizontal scalability. Arm processors, compared to their x86 counterparts, are highly dense and provide more cores on a CPU.
• Endianness: Endianness describes the order in which a sequence of bytes is stored in computer memory. Big-Endian systems store the most significant byte (big end) first in the order, and Little-Endian systems store the least significant bytes (little end) first. Arm architecture, by design, is bi-endian, which means it can support both big-endian and little-endian byte orderings. To complement its x86 offering, Oracle will use little-endian byte ordering for its Arm implementations and interoperability with x86 counterparts.
• Portability: Oracle is adopting Arm architecture with Ampere One and Ampere AltraMax processors and will use little-endian format for byte ordering. Using little-endian format means migrating running databases on x86-based systems to Arm-based systems or vice versa will be easier. Generally available migration techniques like backup and restore will work across x86 and Arm-based systems.

 

Oracle Database Availability on Arm

Oracle Database Enterprise Edition is available on the Arm platform (Ampere Altra brands) in the cloud with flexible shapes (VM.Standard.A1.Flex) for Oracle Base Database Service (BaseDB). VM.Standard.A1.Flex is based on Arm computer processors and CPU cores. VM.Standard.A1.Flex shapes for BaseDB support any number of OCPUs, up to a maximum of 64, in a single VM. Each OCPU includes 8 GB of memory and 1 Gbps of network bandwidth, up to a maximum of 40 Gbps. VM.Standard.A1.Flex shapes offer balanced and higher-performance block volume storage that can scale up to 40 TB in data capacity. When provisioned with higher-performance storage, customers can achieve a maximum of 500K IOPS for a single VM.

Oracle Database on the Arm platform was released from Database release version 19.19. This database version complements the Oracle Database you have been running on x86 and other platforms, including RAC (on-premises currently), Data Guard, and others. Oracle Database on Arm is available on-premises and in Oracle Cloud Infrastructure. Currently, Oracle licenses are certified and licensed for Ampere Altra and Ampere Altra Max processors with a core factor of .25. Please refer to the core-factor table here: https://drive.google.com/file/d/14qOc_TP_NPe-kNeyCXbUtkK8Npg1prwJ/view?usp=sharing. All other Arm processors, not referred to in the core factor table, will use a core factor of 1 for licensing purposes.

Source: oracle.com

Continue reading

Where is the Complexity? Part 2

Introduction

In my previous blog post, I described how a simple Teller microservice to transfer funds from one account to another account could be implemented as REST services in Spring Boot. What I didn’t cover were all the possible ways a simple application like this could fail. I only mentioned a couple that could have been implemented. However, to solve this problem of various failures such as network failure, service failure, database failure, etc., one can choose to use a distributed transaction pattern such as the popular Saga pattern. In this post, I’ll examine what it takes to move from the simplistic minimal or no error handling version of these microservices to leveraging Eclipse MicroProfile Long Running Actions. As you’ll learn in reading this post, adding Sagas adds considerable complexity to the application code. Ideally using a distributed transaction pattern should simplify the developer’s life, not make it more complex. Continue reading to see how much complexity the developer takes on in using Sagas.

To use the Saga pattern with Eclipse MicroProfile Long Running Actions (LRA), the developer must keep track of information that is essentially unrelated to the business problem being solved. The basic flow for the LRA in this use case is:

1. Teller microservice is called to transfer X from account A in department 1 to account B in department 2
2. Teller microservice begins an LRA. This causes LRA headers to be included in subsequent calls
3. Teller microservice calls withdraw on department1 for account A
1. Under the covers, department 1 sees the LRA headers and joins the LRA by enlisting with the LRA coordinator
2. Department 1 withdraws X from account A and returns to the teller microservice
4. Teller microservice calls deposit on department 2 for account B
1. Under the covers, department 2 sees the LRA headers and joins the LRA by enlisting with the LRA coordinator
2. Department 2 deposits X into account B and returns to the teller microservice
5. Teller microservice closes the LRA which causes
1. LRA Coordinator to call back to the complete endpoint for each joined participant
2. Each participant’s complete endpoint then performs any final steps for the LRA, more on this later
6. Teller microservice returns success

Handling errors

Now to handle the possible various failures that can occur, the following additional steps may need to be performed:

1. If the withdraw request fails, teller should cancel the LRA and return failure to its caller
1. This in turn will cause the LRA coordinator to call back to each enrolled participant at their compensate endpoint, which at this point is only the initiator
2. The initiator will get called back on its compensate endpoint if defined, at which it has nothing to do. The sample code doesn’t define a compensate or completion callback as there is nothing to do in the teller to compensate/complete the LRA as the teller maintains no state.
2. Likewise, if the deposit request to account B fails, teller should cancel the LRA.
1. This in turn will cause the LRA coordinator to call back to each enrolled participant at their compensate endpoint, which at this point is likely just the initiator and department 1. It might also include department 2 if department 2 had a chance to join before failing to deposit.
1. The initiator will get called back on its compensate endpoint if defined, at which it has nothing to do
2. Department 1 will get called back on its compensate endpoint, at which point it needs to figure out how to compensate the previous withdraw operation. This is where the fun begins. More on this shortly!
3. Department 2 may also get called back on its compensate endpoint and go through the same drill as department 1.
3. If both withdraw and deposit succeed, the teller should close the LRA. This causes the LRA coordinator to:
1. Call the completion endpoint on the teller
1. The teller really has nothing to do at this point
2. Call the completion endpoint on department 1
1. Department 1’s completion endpoint needs to clean-up any bookkeeping information it has on the LRA
3. Call the completion endpoint on department 2
1. Department 2’s completion endpoint needs to clean-up any bookkeeping information it has on the LRA

And we’re done! Well, sort of. Let’s examine in more detail what was mentioned about the fun beginning and bookkeeping. When a participant is called at its complete or compensate endpoints, all that is passed is the LRA Id in the REST headers. What it means to complete or compensate the transaction is completely up to the application code provided by the developer. So typically, this means creating some sort of log or journal to record the changes that were made as part of the LRA such that the microservice knows what needs to be done to complete or compensate the transaction.

Here is the updated Teller microservice code from TransferResource.java looks like:

    @Autowired

    @Qualifier("MicroTxLRA")

    RestTemplate restTemplate;

    @Value("${departmentOneEndpoint}")

    String departmentOneEndpoint;

    @Value("${departmentTwoEndpoint}")

    String departmentTwoEndpoint;

    @RequestMapping(value = "transfer", method = RequestMethod.POST)

    @LRA(value = LRA.Type.REQUIRES_NEW, end = true, cancelOnFamily = {HttpStatus.Series.CLIENT_ERROR, HttpStatus.Series.SERVER_ERROR})

    public ResponseEntity<?> transfer(@RequestBody Transfer transferDetails,

                                      @RequestHeader(LRA_HTTP_CONTEXT_HEADER) String lraId) {

        LOG.info("Transfer initiated: {}", transferDetails);

        try {

            ResponseEntity<String> withdrawResponse = withdraw(transferDetails.getFrom(), transferDetails.getAmount());

            if (!withdrawResponse.getStatusCode().is2xxSuccessful()) {

                LOG.error("Withdraw failed: {} Reason: {}", transferDetails, withdrawResponse.getBody());

                return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR)

                        .body(new TransferResponse("Withdraw failed. " + withdrawResponse.getBody()));

            }

            ResponseEntity<String> depositResponse = deposit(transferDetails.getTo(), transferDetails.getAmount());

            if (!depositResponse.getStatusCode().is2xxSuccessful()) {

                LOG.error("Deposit failed: {} Reason: {} ", transferDetails, depositResponse.getBody());

                return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR)

                        .body(new TransferResponse("Deposit failed"));

            }

        } catch (Exception e) {

            LOG.error("Transfer failed with exception {}", e.getLocalizedMessage());

            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR)

                    .body(new TransferResponse("Transfer failed. " + e.getLocalizedMessage()));

        }

        LOG.info("Transfer successful: {}", transferDetails);

        return ResponseEntity

                .ok(new TransferResponse("Transfer completed successfully"));

    }

You’ll notice that we’ve switched the RestTemplate to one provided by MicroTx that provides filters to automate things like passing MicroTx transaction headers. We added the @LRA annotation to indicate that a new LRA needs to be started. As well we’ve removed the redepositWithdrawnAmount method as the LRA will ensure corrective action takes place by eventually calling the department 1’s compensate endpoint. Finally, because of the “end = true” option in the LRA annotation, the LRA will automatically be closed (or canceled if the return status is a server or client error, 4xx or 5xx) when the transfer method completes.

So far, the changes are minimal. Let’s see what changes need to be made to the department deposit and withdraw services. In the first blog post, these services were contained in the AccountResource.java file. In this post, we’ve split that class into 3 separate classes (AccountAdminService, DepositService, and WithdrawService) as we want to register completion and/or compensation callbacks that are unique to the withdraw or deposit services.

Let’s look at the changes to how the withdraw service is implemented in WithdrawService.java: 

@RestController

@RequestMapping("/withdraw")

public class WithdrawService {

    private static final Logger LOG = LoggerFactory.getLogger(WithdrawService.class);

    @Autowired

    IAccountOperationService accountService;

    @Autowired

    JournalRepository journalRepository;

    @Autowired

    AccountTransferDAO accountTransferDAO;

    @Autowired

    IAccountQueryService accountQueryService;

    /**

     * cancelOnFamily attribute in @LRA is set to empty array to avoid cancellation from participant.

     * As per the requirement, only initiator can trigger cancel, while participant returns right HTTP status code to initiator

     */

    @RequestMapping(value = "/{accountId}", method = RequestMethod.POST)

    @LRA(value = LRA.Type.MANDATORY, end = false, cancelOnFamily = {})

    public ResponseEntity<?> withdraw(@RequestHeader(LRA_HTTP_CONTEXT_HEADER) String lraId,

                                      @PathVariable("accountId") String accountId, @RequestParam("amount") double amount) {

        try {

            this.accountService.withdraw(accountId, amount);

            accountTransferDAO.saveJournal(new Journal(JournalType.WITHDRAW.name(), accountId, amount, lraId, ParticipantStatus.Active.name()));

            LOG.info(amount + " withdrawn from account: " + accountId);

            return ResponseEntity.ok("Amount withdrawn from the account");

        } catch (NotFoundException e) {

            return ResponseEntity.status(HttpStatus.NOT_FOUND).body(e.getMessage());

        } catch (UnprocessableEntityException e) {

            LOG.error(e.getLocalizedMessage());

            return ResponseEntity.status(HttpStatus.UNPROCESSABLE_ENTITY).body(e.getMessage());

        } catch (Exception e) {

            LOG.error(e.getLocalizedMessage());

            return ResponseEntity.internalServerError().body(e.getLocalizedMessage());

        }

    }

As you can see the withdraw service got more complex as we needed to introduce a journal to keep track of changes made by an LRA. We keep this journal so when asked to compensate we’ll know how to compensate the request. The withdraw service updates the account balance immediately and tracks the change in its journal.

    /**

     * Update LRA state. Do nothing else.

     */

    @RequestMapping(value = "/complete", method = RequestMethod.PUT)

    @Complete

    public ResponseEntity<?> completeWork(@RequestHeader(LRA_HTTP_CONTEXT_HEADER) String lraId) {

        LOG.info("withdraw complete called for LRA : " + lraId);

        Journal journal = accountTransferDAO.getJournalForLRAid(lraId, JournalType.WITHDRAW);

        if (journal != null) {

            String lraState = journal.getLraState();

            if (lraState.equals(ParticipantStatus.Completing.name()) ||

                    lraState.equals(ParticipantStatus.Completed.name())) {

                // idempotency : if current LRA stats is already Completed, do nothing

                return ResponseEntity.ok(ParticipantStatus.valueOf(lraState));

            }

            journal.setLraState(ParticipantStatus.Completed.name());

            accountTransferDAO.saveJournal(journal);

        }

        return ResponseEntity.ok(ParticipantStatus.Completed.name());

    }

    /**

     * Read the journal and increase the balance by the previous withdrawal amount before the LRA

     */

    @RequestMapping(value = "/compensate", method = RequestMethod.PUT)

    @Compensate

    public ResponseEntity<?> compensateWork(@RequestHeader(LRA_HTTP_CONTEXT_HEADER) String lraId) {

        LOG.info("Account withdraw compensate() called for LRA : " + lraId);

        try {

            Journal journal = accountTransferDAO.getJournalForLRAid(lraId, JournalType.WITHDRAW);

            if (journal != null) {

                String lraState = journal.getLraState();

                if (lraState.equals(ParticipantStatus.Compensating.name()) ||

                        lraState.equals(ParticipantStatus.Compensated.name())) {

                    // idempotency : if current LRA stats is already Compensated, do nothing

                    return ResponseEntity.ok(ParticipantStatus.valueOf(lraState));

                }

                accountTransferDAO.doCompensationWork(journal);

            } else {

                LOG.warn("Journal entry does not exist for LRA : {} ", lraId);

            }

            return ResponseEntity.ok(ParticipantStatus.Compensated.name());

        } catch (Exception e) {

            LOG.error("Compensate operation failed : " + e.getMessage());

            return ResponseEntity.ok(ParticipantStatus.FailedToCompensate.name());

        }

    }

    @RequestMapping(value = "/status", method = RequestMethod.GET)

    @Status

    public ResponseEntity<?> status(@RequestHeader(LRA_HTTP_CONTEXT_HEADER) String lraId,

                                    @RequestHeader(LRA_HTTP_PARENT_CONTEXT_HEADER) String parentLRA) throws Exception {

        return accountTransferDAO.status(lraId, JournalType.WITHDRAW);

    }

    /**

     * Delete journal entry for LRA (or keep for auditing)

     */

    @RequestMapping(value = "/after", method = RequestMethod.PUT)

    @AfterLRA

    public ResponseEntity<?> afterLRA(@RequestHeader(LRA_HTTP_ENDED_CONTEXT_HEADER) String lraId, @RequestBody String status) {

        LOG.info("After LRA called for lraId : {} with status {} ", lraId, status);

        accountTransferDAO.afterLRA(lraId, status, JournalType.WITHDRAW);

        return ResponseEntity.ok().build();

    }

The remaining portion of the WithdrawService class is dealing with completing or compensating the LRA, allowing the department's view of the LRA status, and registering a callback when the LRA is completed or compensated. If the LRA is canceled and the compensation endpoint is called by the coordinator, it redeposits the withdrawn amount and sets its LRA state to compensated. If the LRA is completed, then the completion endpoint is called which has nothing to do as the funds have already been withdrawn and committed, so just its LRA state is updated. When the LRA is finally done, either completed or compensated, the after callback is made by the coordinator. In this case the callback simply deletes the journal entry as it is no longer needed.

Let's look at the deposit service DepositService.java:

@RestController

@RequestMapping("/deposit")

public class DepositService {

    private static final Logger LOG = LoggerFactory.getLogger(DepositService.class);

    @Autowired

    IAccountOperationService accountService;

    @Autowired

    JournalRepository journalRepository;

    @Autowired

    AccountTransferDAO accountTransferDAO;

    /**

     * cancelOnFamily attribute in @LRA is set to empty array to avoid cancellation from participant.

     * As per the requirement, only initiator can trigger cancel, while participant returns right HTTP status code to initiator

     */

    @RequestMapping(value = "/{accountId}", method = RequestMethod.POST)

    @LRA(value = Type.MANDATORY, end = false, cancelOnFamily = {})

    public ResponseEntity<?> deposit(@RequestHeader(LRA_HTTP_CONTEXT_HEADER) String lraId,

                                     @PathVariable("accountId") String accountId, @RequestParam("amount") double amount) {

        accountTransferDAO.saveJournal(new Journal(JournalType.DEPOSIT.name(), accountId, amount, lraId, ParticipantStatus.Active.name()));

        return ResponseEntity.ok("Amount deposited to the account");

    }

    /**

     * Increase balance amount as recorded in journal during deposit call.

     * Update LRA state to ParticipantStatus.Completed.

     */

    @RequestMapping(value = "/complete", method = RequestMethod.PUT)

    @Complete

    public ResponseEntity<?> completeWork(@RequestHeader(LRA_HTTP_CONTEXT_HEADER) String lraId) {

        try {

            LOG.info("deposit complete called for LRA : " + lraId);

            Journal journal = accountTransferDAO.getJournalForLRAid(lraId, JournalType.DEPOSIT);

            if (journal != null) {

                String lraState = journal.getLraState();

                if (lraState.equals(ParticipantStatus.Completing.name()) ||

                        lraState.equals(ParticipantStatus.Completed.name())) {

                    // idempotency : if current LRA stats is already Completed, do nothing

                    return ResponseEntity.ok(ParticipantStatus.valueOf(lraState));

                }

                accountTransferDAO.doCompleteWork(journal);

            } else {

                LOG.warn("Journal entry does not exist for LRA : {} ", lraId);

            }

            return ResponseEntity.ok(ParticipantStatus.Completed.name());

        } catch (Exception e) {

            LOG.error("Complete operation failed : " + e.getMessage());

            return ResponseEntity.ok(ParticipantStatus.FailedToComplete.name());

        }

    }

and the doCompleteWork method looks like:

    public void doCompleteWork(Journal journal) throws Exception {

        try {

            Account account = accountQueryService.getAccountDetails(journal.getAccountId());

            account.setAmount(account.getAmount() + journal.getJournalAmount());

            accountService.save(account);

            journal.setLraState(ParticipantStatus.Completed.name());

            journalRepository.save(journal);

        } catch (Exception e) {

            journal.setLraState(ParticipantStatus.FailedToComplete.name());

            journalRepository.save(journal);

            throw new Exception("Failed to complete", e);

        }

    }

which finally adds the amount deposited to the account. If we look at the compensate callback for deposit, it just sets the LRA status in the journal to compensated:

    /**

     * Update LRA state to ParticipantStatus.Compensated.

     */

    @RequestMapping(value = "/compensate", method = RequestMethod.PUT)

    @Compensate

    public ResponseEntity<?> compensateWork(@RequestHeader(LRA_HTTP_CONTEXT_HEADER) String lraId) {

        LOG.info("Account deposit compensate() called for LRA : " + lraId);

        Journal journal = accountTransferDAO.getJournalForLRAid(lraId, JournalType.DEPOSIT);

        if (journal != null) {

            String lraState = journal.getLraState();

            if (lraState.equals(ParticipantStatus.Compensating.name()) ||

                    lraState.equals(ParticipantStatus.Compensated.name())) {

                // idempotency : if current LRA stats is already Compensated, do nothing

                return ResponseEntity.ok(ParticipantStatus.valueOf(lraState));

            }

            journal.setLraState(ParticipantStatus.Compensated.name());

            accountTransferDAO.saveJournal(journal);

        }

        return ResponseEntity.ok(ParticipantStatus.Compensated.name());

    }

    @RequestMapping(value = "/status", method = RequestMethod.GET)

    @Status

    public ResponseEntity<?> status(@RequestHeader(LRA_HTTP_CONTEXT_HEADER) String lraId,

                                    @RequestHeader(LRA_HTTP_PARENT_CONTEXT_HEADER) String parentLRA) throws Exception {

        return accountTransferDAO.status(lraId, JournalType.DEPOSIT);

    }

Finally the after callback will be triggered and it will delete the associated journal entry by calling the afterLRA method on the accountTransferDAO.

    /**

     * Delete journal entry for LRA (or keep for auditing)

     */

    @RequestMapping(value = "/after", method = RequestMethod.PUT)

    @AfterLRA

    public ResponseEntity<?> afterLRA(@RequestHeader(LRA_HTTP_ENDED_CONTEXT_HEADER) String lraId, @RequestBody String status) {

        LOG.info("After LRA Called : " + lraId);

        accountTransferDAO.afterLRA(lraId, status, JournalType.DEPOSIT);

        return ResponseEntity.ok().build();

    }

The accountTransferDAO afterLRA method is what actually deletes the journal entry:

    public void afterLRA(String lraId, String lraStatus, JournalType journalType){

        Journal journal = getJournalForLRAid(lraId, journalType);

        if (Objects.nonNull(journal) && isLRASuccessfullyEnded(lraStatus)) {

            journalRepository.delete(journal);

        }

    }

As the above code shows, a lot of additional work is required the application developer to ensure data consistency. However even with all these changes, the above code doesn't handle two calls to deposit or withdraw in the same LRA. The journal implementation only records the last update.

Source: oracle.com

Continue reading

The 5 major benefits of ECPUs in the Oracle Autonomous Database

Oracle Autonomous Database (ADB) recently introduced a more advanced ECPU billing metric and is now retiring the legacy OCPU billing metric for Autonomous Data Warehouse and Autonomous Transaction Processing in the next 12 months. Oracle recommend switching from the OCPU billing metric to the ECPU billing metric, which will not incur any downtime or service interruptions.

ECPUs will provide the same great price-performance as OCPUs and continuous improvements in price-performance over time. Updating to the ECPU billing metric provides the following benefits:

• 50% lower entry cost: The smallest Autonomous Database that can be provisioned with ECPUs is 50% less expensive ($0.672 per hour vs $1.3441 per hour with OCPUs)
• Finer granularity for database scaling: Each incremental increase in ECPU database size is only $0.336
• Lower storage costs: Autonomous Data Warehouse storage price reduced from $118.40 to $25.00 per TB per month and Autonomous Transaction Processing storage can be provisioned in increments of 1GB (this is a huge thing!), with a minimum of 20GB – this brings the ADW in-database storage price on par with the object storage and thus this helps to build data lakes solely on the architectural requirements and not focusing on cost
• Up to 87% lower costs with database consolidation: Elastic Resource Pools, available on ECPU ADB Serverless databases, help consolidate deployments leading to major cost savings
• New features for Autonomous Database may only be available with ECPU’s

Note that the prices mentioned above are the current list prices for Autonomous Databases with the License Included license type.

There are also differences in backups between OCPU’s and ECPU’s. ECPU’s backup storage is billed separately, and the backup retention period may be selected between 1 and 60 days. With OCPU’s, 60-days of backup storage is included in the storage price. This new ECPU customer-controllable backup is beneficial because customers can now control the backup storage size and further reduce the cost of dev/test environments. Here is how I reduced the size from 60 to 31 days (later on I did reduce it to 7 days).

I did scale down my database in 2 phases: (1) I switched to the ECPU model (1 OCPU –> 4 ECPUs) and then (2) reduced the ECPU count from 4 to 2 and the storage from 1024GB to 20GB (those two in one go with no downtime).

Here are some general guidelines related to the new ECPU metric:

1. Provision all new Autonomous Data Warehouse and Autonomous Transaction Processing databases or clones with the ECPU billing metric
2. Update all existing databases to the ECPU billing metric, which is a simple and seamless button click or API call
3. Note that if you choose not to update your existing databases’ billing metric at this time, Oracle may convert your databases from the OCPU billing metric to the ECPU billing metric in the future

Updating your Autonomous Database Serverless to the ECPU billing metric will have no impact to your service and incur no downtime. Oracle Autonomous Database will be retiring the OCPU-based SKUs and replacing them with the ECPU-based SKUs. Starting in August 2023, some new Autonomous Database features may be available only on ECPU’s. For example, Elastic Resource Pools are only available with ECPU’s.

Note that ECPU’s have also already been introduced for MySQL Heatwave on AWS, and other services may also offer ECPU’s in the future.

Source: juliandontcheff.wordpress.com

Continue reading

Performance Tuning in the Oracle Autonomous Database: Creating Indexes for JSON Collections

The Oracle Database and in particular the Oracle Autonomous Database offers multiple technologies to accelerate queries and improve database performance over JSON data, including indexes, materialized views, in-memory column storage, and Exadata storage-cell pushdown.

Simple Oracle Document Access (SODA) is a NoSQL query language for storing, editing and deleting JSON documents. A SODA collection is a set of documents (in particular JSON) that is backed by an Oracle Database table or view. By default, creating a SODA document collection creates the following in the Oracle Database:

◉ Persistent default collection metadata.

◉ A table for storing the collection, in the database schema to which your SODA client is connected.

If the init.ora parameter compatible is at least 20, then SODA uses JSON data type by default for JSON content, and the version method is UUID. If the init.ora parameter compatible is less than 20, then SODA uses BLOB textual data by default for JSON content and the data is character data. 

Collection metadata is composed of multiple components. The kind of database you use determines the collection metadata that is used by default, and which of its field values you can modify for custom metadata.

Let us consider a collection of movies within the ADMIN schema in a 19c Oracle Autonomous JSON Database (think of one JSON file per movie). After opening JSON from Database Actions, we will create an index on the MOVIES colection:

Creating an index for a JSON collection requires the name and the type. The different options are Functional, Spatial and Search.

Before creating a functional index on the runtime field (we will be running order-by queries against it), let us see what are the field needed (fields to enter):

• For a functional type index, the fields to enter are:
• Unique: make all indexed values unique
• Index Nulls: use the index in order-by queries
• Path Required: the path must select a scalar value, even a JSON null value
• Properties: select the property that you want to index on, or easier is to just type * to display all available document properties in the collection
• Composite Index: use more than one property
• Advanced: change the storage properties of the indexed property
• For search index, the options are:
• Dataguide off-on: create JSON data guide for collection
• Text Search off-on: index all properties in documents to support full-text search based on string equality
• Range Search off-on: choose on to support range search when string-range search or temporal search (equality or range) is required
• Spatial index is used to index GeoJSON geographic data. The selected property should be of GeoJSON type.
• Path Required: the path must select a value, even if it is a JSON null value
• Lax: the targeted field does not need to be present or does not have a GeoJSON geometry object as its value

The properties of the selected index appear in JSON format below the listed indexes:

We can also index a singleton scalar field using SQL, here is how:

CREATE INDEX YEAR_NDX ON movies (json_value(data, '$.year.number()' ERROR ON ERROR));

Item method numberOnly() is used in the path expression that identifies the field to index, to ensure that the field value is numeric. As I have the year field in one of the JSON files as a string, I am getting the following error if I use the method numberOnly(): ORA-01722: invalid number. Method numberOnly() is used instead of method number(), because number() allows also for conversion of non-numeric fields to numbers. Clearly as some moview are produced during the same year I cannot make the index unique: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found.

Creating a collection relational views of JSON documents is simple, select the columns we need, say title, year and runtime:

In SQL, we can use the view and order the movies by runtime:

Regardless of your database release you can create whatever Oracle Database indexes you need directly, using:

(1) the JSON Page of Using Oracle Database Actions, check Creating Indexes for JSON Collections

(2) Simple Oracle Document Access (SODA)

(3) SQL — see Indexes for JSON Data in Oracle Database JSON Developer’s Guide.

Using the JSON page in ADB is perhaps the easiest approach to indexing JSON data.

The static data dictionary views USER_SODA_COLLECTIONS lists the basic features of all of your SODA collections. Correspondently, you have also DBA_SODA_COLLECTIONS and ALL_SODA_COLLECTIONS. Collection metadata, expressed in JavaScript Object Notation (JSON) can be obtained from the last column, called JSON_DESCRIPTOR.

Users will typically work with JSON collections using native language drivers, for example, SODA for Java or SODA for Python. SODA native language drivers generally provide more throughput (operations per second) than the REST driver (SODA for REST). It is recommended to configure the SODA drivers as follows:

• Enable SODA Metadata Cache: The SODA driver needs to know the metadata of each JSON collection (the column names, types, etc.). By enabling the metadata cache, roundtrips to the database can be saved, improving latency and throughput.
• Enable Statement Cache: Statement caching improves performance by caching executable statements that are used repeatedly, such as in a loop or in a method that is called repeatedly. For Java, the statement cache is enabled using JDBC.
• For load-balanced systems: turn off DNS caching: Load balancing allows to distribute SODA operations across different nodes. If DNS caching is turned on, then all connections are likely to use the same node and nullifying the load balancing. For Java, the following system property should be set: inet.addr.ttl=0

The database performance tuning techniques also apply to SODA: for example, SODA collections can be partitioned or sharded, and queries can be accelerated using indexes and/or materialized views.

How to monitor all that? You can turn on performance monitoring of the SQL operations that underlie a SODA read or write operation, by adding a SQL hint to the SODA operation.

Use only hint MONITOR (turn on monitoring) or NO_MONITOR (turn off monitoring). You can use this to pass any SQL hints, but MONITOR and NO_MONITOR are the useful ones for SODA, and an inappropriate hint can cause the optimizer to produce a suboptimal query plan.

Note in addition that when using SODA with Autonomous Database the following restrictions apply:

• Automatic indexing is not supported for SQL and PL/SQL code that uses the SQL/JSON function json_exists.
• Automatic indexing is not supported for SODA query-by-example (QBE)

Final note: since Auto Indexing is disabled by default in the Autonomous Database, you can enable it by running: EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

Source: juliandontcheff.wordpress.com

Continue reading