Conversations are the next generation in natural language queries

Autonomous Database Select AI has just introduced a powerful new capability that lets you have conversations with your data. What’s the difference between a natural language query and a conversation? A lot!

Conversations provide a natural way to analyze your data. Ask a question, review the result, and then follow up with questions to clarify or seek more information. It’s simple because we all have been doing this since we learned to speak. 

Let’s look at an example. Below we started with a top-level question asked by an analyst for a movie streaming service. The analyst then followed up with additional questions:

• “What are our total streams?”
• “Break that out by genre”
• “Add customer segment”
• “Keep the top 5 customer segments and genres by total views. Include a rank in the result”

It’s a typical conversation you may have with a colleague (although perhaps a little bossy 😉).  You can see in the demo below how Select AI lets you have this same conversation with your data, with help from an LLM that interprets the conversational thread and generates SQL:

Notice that Select AI now supports a new kind of SQL: 

SELECT AI

Start with our total streams

The “SELECT” keyword will look familiar to those who know SQL. After that, everything changes. SQL in Autonomous Database has been enhanced with a new “AI” keyword. This indicates that the subsequent text will be natural language. You don’t need to worry about where the data resides or how to formulate the query. Just ask a question. Your natural language will be translated into Oracle SQL using an LLM. 

And at the end of the demo, it asks Select AI to explain the generated query - both the SQL code and what the code means. With all the back and forth in the conversation, this explanation can be really helpful when confirming the results.

How to enable conversations

You enable conversations in your Select AI profile. The Select AI profile captures information about your AI provider, the LLM used to translate natural language into queries, the source tables to use for the queries, and more. For conversations, you need to use an OpenAI GPT model (from Azure or directly from OpenAI). In the profile, simply set the conversations flag to “true” to enable the capability:

BEGIN

  DBMS_CLOUD_AI.CREATE_PROFILE (

    profile_name => 'your_profile',

    attributes =>       

        '{"provider": "azure",

          "azure_resource_name": "your-resource",                    

          "azure_deployment_name": "your-deployment",

          "credential_name": "AI_CREDENTIAL",

          "comments":"true", 

          "conversation": "true",         

          "object_list": [

            {"owner": "moviestream", "name": "GENRE"},

            {"owner": "moviestream", "name": "CUSTOMER"},

            {"owner": "moviestream", "name": "PIZZA_SHOP"},

            {"owner": "moviestream", "name": "STREAMS"},            

            {"owner": "moviestream", "name": "MOVIES"},

            {"owner": "moviestream", "name": "ACTORS"}              

          ]          

          }'

    );

END;

/

Then, enable the profile for your session:

BEGIN

    DBMS_CLOUD_AI.SET_PROFILE (

        profile_name => 'your_profile'

    );

END;

/

That’s it! Start having conversations with your data.

Summary    

Conversations are a real breakthrough for natural language queries. They let you explore your data in any direction in the most natural way possible. You can get started using conversations today. If you’re using Autonomous Database – it’s already deployed. Or spin up an Autonomous Database Always Free instance and give it a try. Don’t forget to go to the Autonomous Database Getting Started page to find webcasts, tutorials, demos, and more.

Source: oracle.com

Continue reading

Enhanced PDB automation on Exadata and Base Database Services

We are announcing new Pluggable Database (PDB) automation capabilities on Exadata Database Service on Cloud@Customer (ExaDB-C@C), Exadata Database Service on Dedicated Infrastructure (ExaDB-D), and Base Database Service (BaseDB) in all OCI commercial regions. The new set of enhancements brings the automation via OCI Console/API/Terraform for the following PDB-specific features to the Exadata Database and Base Database services:

◉ Backup and restore

◉ Remote clone

◉ Refreshable clone

◉ Relocate

◉ View open modes per VM

Key Benefits

◉ Leverage new PDB automation capabilities for end-to-end application development and deployment in the cloud.

◉ Access Oracle Multitenant features via OCI Console/API/Terraform on Exadata Database Service and Base Database Service.

Let's go over the core user journeys!

Note: For ExaDB-C@C, ExaDB-D, and BaseDB services in the OCI console, we refer to Container Database (CDB) as 'Database'. Any mention of CDB below is for clarity and brevity only.

PDB backup

When you create a PDB, you can specify if you would like to take an immediate backup of that PDB. This option is also available during local clone, remote clone, and relocate operations if the CDB is configured with the auto-backup feature. The PDB backup destination will always be the same as that of the CDB, and the backups cannot be accessed directly or created on demand. Oracle recommends immediately backing up the PDB after you create, clone, and relocate it, to ensure your PDB is recoverable before the next CDB auto-backup is completed successfully.

PDB restore - In-place and Out-of-place restore

You can now restore a PDB within the same CDB (in-place restore) or to a new CDB (out-of-place restore, applicable to ExaDB-D and BaseDB).  In-place restore allows you to choose to restore to the latest or a specified timestamp. This is not applicable to PDBs on a Data Guard enabled CDB.

Out-of-place restore of a PDB(s) is achieved when you restore a PDB to a new CDB. 'Create database from backup' is enhanced to allow restore of a PDB or a set of PDBs to a new CDB. You can specify a PDB or a group of PDBs by the 'Choose the PDBs to restore' option. By default, the PDB(s) will always be restored on a new CDB to be created on an existing VM Cluster or in a new DB System.

Remote clone

You can create a copy of the PDB within the same CDB (local clone) or to a different CDB (remote clone). The destination CDB can be of the same database version or higher. You can optionally provide an existing common user name (a user created in the CDB$root container) that the database link will use to connect. There is an option to take the backup of the PDB after remote cloning. PDB remote cloning works across Data Guard environments.

Refreshable Clone

You can create a refreshable copy of a PDB (refreshable clone). Providing an existing common user name is mandatory for the database link connection. A refreshable clone PDB can be refreshed at will by going to the PDB details page and initiating the action 'Refresh.' To be able to refresh successfully depends on the availability of the archive redo log files.

To disconnect the refreshable clone PDB from the source, you can convert it to a regular PDB. Once converted, the PDB will be opened read-write, and all the lifecycle operations can be performed. There is an option to take a backup of the PDB after converting from a refreshable clone to a regular PDB. The operation to convert a refreshable clone to a regular PDB when performed on a Data Guard primary will also create a PDB on its standby and be available in mount mode.

PDB relocate

You can move a PDB to another CDB. The destination CDB can be in a different compartment, VM cluster, or DB system. The destination container database can be on the same database release version or higher. You can provide a new PDB name while relocating a PDB to a different CDB. Optionally provide an existing common user name the database link will use to connect. There is an option to backup the PDB after relocation to the destination CDB is complete. The time to relocate depends on the size of the PDB, and network bandwidth during file copy from source to destination. Once relocated, the PDB will be opened read-write on the destination. If the destination is a Data Guard primary, a PDB will be created on the standby and will be available in mount mode.

PDB open modes

A PDB can be opened on a specific VM (a database instance) in the case of a multi-node RAC CDB. If the open mode across the VMs is the same, you will see either of these values: Read Write, Read Only, Mounted. If the open mode across the VMs differs, you will see which VM PDB is opened in Read Write mode by hovering the mouse over the information icon. You cannot change the open modes of the PDB through the OCI console or API. However, you can start or stop a PDB. Starting a PDB and stopping a PDB will bring it into read-write and mount mode accordingly. On standby, starting a PDB and stopping a PDB will bring it into read-only and mount mode depending on the Data Guard type (Active Data Guard / Regular Data Guard).

Considerations

• Enhanced PDB automation is supported on database releases 19c and later.
• PDB clone (remote, refreshable) and relocate are supported across the Compartment, VM Cluster, DB System (BaseDB), VCN, Network (ExaDB-C@C), and database version (same or higher). The same is not supported across the Availability Domain (AD) and Exadata Infrastructure.
• A Data Guard standby cannot be either a source or a destination for the PDB clone and PDB relocate operations.
• A refreshable clone PDB created on a Data Guard primary will not appear on standby - its existence will be deferred until converted to a regular PDB.
• Currently, PDB automation is supported for the databases using TDE wallet-based encryption (Oracle-managed keys).

Source: oracle.com

Continue reading

Oracle 1Z0-076 Exam Prep: Tips & Resources

Are you planning to take the Oracle 1Z0-076 exam and become a certified Oracle Database 19c: Data Guard Administration? If yes, you might wonder how to prepare for this challenging exam and what resources to use. This article will share tips and resources to help you ace the 1Z0-076 exam and boost your career as an Oracle Data Guard expert.

What Is the Oracle 1Z0-076 Exam?

The Oracle 1Z0-076 certification exam for Oracle Database 19c: Data Guard Administrations. It tests your knowledge and skills on Oracle Data Guard concepts, configuration, management, optimization, monitoring, data protection, high availability, and disaster recovery. The exam consists of 74 multiple-choice questions you must answer in 120 minutes. The passing score is 61%.

Why Should You Take the Oracle 1Z0-076 Exam?

Oracle Data Guard is a powerful feature of Oracle Database that enables you to create and maintain one or more standby databases to protect your data from failures, disasters, human errors, and data corruption. Oracle Data Guard also provides high availability, scalability, and performance benefits for your database applications.

By taking the Oracle 1Z0-076 exam and becoming a certified Oracle Database 19c: Data Guard Administration, you can prove your proficiency and expertise in Oracle Data Guard and gain a competitive edge in the job market. You can also improve your career prospects and credibility as an Oracle Data Guard professional.

According to ZipRecruiter, the average salary of an Oracle Data Guard Administrator in the US is $115,000 annually. The demand for Oracle Data Guard Administrators is also expected to grow as more and more organizations adopt Oracle Database 19c and leverage its Data Guard capabilities.

How to Prepare for the Oracle 1Z0-076 Exam: Tips and Resources

Preparing for the Oracle 1Z0-076 exam requires a lot of dedication, practice, and revision. Here are some tips and resources that can help you prepare effectively and efficiently for the exam:

• Review the exam objectives and topics: The first step in your preparation is to review the exam objectives and issues and understand what the exam expects from you. You can reference the official website and list the topics you must study and master.
• Study the official Oracle documentation: The official Oracle documentation is the best source of information and guidance for Oracle Data Guard 19c. You can find the documentation on the Oracle website and read the relevant sections that cover the exam topics. The documentation provides detailed explanations, examples, and Oracle Data Guard 19c best practices.
• Take an online course: If you prefer a more structured and interactive way of learning, you can take an online course that covers the Oracle 1Z0-076 exam topics. Many online platforms, such as Udemy, Pluralsight, and Oracle University, offer high-quality and affordable courses for Oracle Data Guard 19c. You can choose a course that suits your learning style, budget, and schedule. An online course can help you learn the concepts, techniques, and best practices for Oracle Data Guard 19c from experienced instructors and experts.
• Practice with hands-on labs: One of the best ways to reinforce your learning and test your skills is to practice with hands-on labs. Hands-on labs permit you to apply your knowledge and gain practical experience with Oracle Data Guard 19c. You can use a virtual machine, a cloud service, or a physical server to set up your own Oracle Data Guard environment and perform various tasks and scenarios that simulate the actual exam. You can also use the Oracle LiveLabs service, which provides free access to Oracle Cloud environments and guided workshops for Oracle Data Guard 19c.
• Take practice tests: Another effective way to prepare for the Oracle 1Z0-076 exam is to take practice tests. Practice tests can help you evaluate your knowledge, recognize your strengths and weaknesses, and familiarize yourself with the exam format and question types. You should take practice tests regularly and review your results and feedback carefully. It would help if you also aimed to score at least 80% on the practice tests before taking the exam.
• Revise and relax: The final step in your preparation is to revise and relax. You should review the exam objectives, topics, official Oracle documentation, online course materials, and practice test questions and answers. You should also make a summary or a cheat sheet of the key points and concepts you must remember for the exam. You should also relax and rest well before the exam day. You should avoid cramming and stressing yourself out. You should also eat well, sleep well, and stay hydrated. You should also arrive at the exam center early and be confident and calm.

Apply Your Knowledge and Skills in Real or Simulated Scenarios

Preparing for the Oracle 1Z0-076 exam requires applying your knowledge and skills in actual or simulated scenarios. The best way to master Oracle Data Guard 19c is to practice it in a realistic and relevant context, where you can apply the concepts, techniques, and tools you have learned.

You can apply your knowledge and skills in actual or simulated scenarios by:

• Setting up your own Oracle Data Guard 19c environment: You can set up your own Oracle Data Guard 19c environment by using Oracle VirtualBox, Oracle Cloud, or Oracle Database Appliance. You can then create, configure, manage, optimize, monitor, protect, and recover your Oracle Data Guard 19c databases and experiment with different scenarios and options.
• Taking online or offline Oracle Data Guard 19c courses or labs: You can take online or offline Oracle Data Guard 19c courses or labs that provide hands-on and guided learning experiences on Oracle Data Guard 19c features and functions. These courses or labs are found on Oracle University, Oracle Learning Library, or Oracle LiveLabs.
• Participating in online or offline Oracle Data Guard 19c projects or challenges: You can join in online or offline Oracle Data Guard 19c projects or challenges that provide real-world and complex problems or tasks on Oracle Data Guard 19c features and functions. You can find some of these projects or challenges on Oracle Developer Community, Oracle Code, or Oracle Hackathons.

By applying your knowledge and skills in actual or simulated scenarios, you can develop your practical and analytical abilities and prepare yourself for the exam and the job.

Conclusion

The Oracle 1Z0-076 exam is a challenging but rewarding exam that can help you become a certified Oracle Database 19c: Data Guard Administration and advance your career as an Oracle Data Guard professional.

Following the tips and resources we shared in this article, you can prepare effectively and efficiently for the exam and achieve your certification goal.

We wish you all the best for your exam and your future endeavors.

Continue reading

Oracle Database 23c: New feature - Direct Joins for UPDATE and DELETE Statements

Oracle Database 23c introduces a host of powerful features aimed at enhancing database performance and query optimization. Among these innovations, Direct Join stands out as an efficient mechanism to streamline update and delete operations involving multiple related tables.

23c now allows you to use direct joins to other tables in UPDATE and DELETE statements in the FROM clause, in this article we will use SQL code examples to HR schema, so you can practice and learn it.

The main benefit is to make the coding of these Direct Joins simpler, using less code and more readable for SQL developers.

Scenario 1: Updating Salaries based on Department and City

Consider the scenario where we need to update the salaries of employees working in the Marketing department in the city of Toronto by increasing their salaries by 10%.

Prior to Oracle Database 23c, we might have used a query similar to this:

Once we identify the records that need to be updated, we proceed to design our UPDATE statement to modify only those records. Therefore, in Oracle versions prior to 23c, the filter used in the UPDATE statement is quite similar to the filter used in the SELECT statement.

Prior to Oracle Database 23c, the common approach to formulate the UPDATE statement involved employing an inner query within the WHERE clause.

However, in 23c, we can harness the power of Direct Join to optimize this update operation:

By utilizing Direct Join, we simplify the query, eliminate subqueries, and improve overall performance.

Scenario 2: Delete employee Neena's job history where she worked as AC_ACCOUNT

Suppose we need to delete employee Neena's job history where she worked as AC_ACCOUNT.

Prior to Oracle Database 23c, we might have used a query similar to this:

Once we identify the records that need to be deleted, we proceed to design our DELETE statement to remove only those records. Therefore, in prior to 23c, the filter used in the DELETE statement is quite similar to the filter used in the SELECT statement.

Prior to Oracle Database 23c, the common approach to formulating the DELETE statement involved employing a subquery or inner query within the WHERE clause.

However, 23c, we can harness the power of Direct Join to optimize this DELETE operation:

Source: oracle.com

Continue reading

Accelerate your Informed Decision-Making: Enable Path Analytics on Knowledge Graphs

With zero lines of code and just a few clicks you can create a property graph view from an RDF knowledge graph. This enables you to run path analysis on a knowledge graph. And why is that interesting? A knowledge graph captures relationships between data entities, providing a way to capture knowledge (facts that describe a concept or domain) from experts. Path analytics on such a knowledge graph can identify entities in your data that are connected to each other, via the concepts captured in the knowledge graph. Path analytics can explain how to reach a goal – an entity in the graph - and ensures that information is not missed in critical decision-making.

Figure 1: UI to create a property graph from an RDF knowledge graph.

 

You might be new to graph theory and graph analytics, so let me explain a bit about graphs along with some examples. Let us first understand knowledge graphs.

What is a Knowledge Graph?

Knowledge graphs capture information about a domain. They organize information as connected networks. In a knowledge graph the entities and relationships between them are represented using the formal semantics of the standards defined by the W3C (World Wide Web Consortium), and this enables knowledge (the facts about a concept or domain) to be consumed through APIs. The complex layers of meaning in data can be represented so that they can be disambiguated by applications. Knowledge graphs can be used to provide context to data so application developers can understand the data better and analysts can make more informed decisions.

For example, consider job candidates and their resumes. Representing their attributes as a knowledge graph can help recruiters make more informed decisions, because the graph captures the relationships between the different concepts that make up a candidate’s skillset. A knowledge graph can capture how the different entities – education, skills, experience – can be related to each other. Such a knowledge graph is referred to as an ontology.

Consider an ontology on the topic AL/ML. AI/ML is a general concept and several sub-topics can be attributed to it as you see in Figure 2.

Figure 2: Ontology of the “AI/ML” topic with sub-topics

 

These topics and sub-topics are related to various tools and programming skills, as shown in Figure 3. As you can see some tools and skills are related to more than one sub-topic.

Figure 3: Ontology of the “AI/ML” topic with sub-topics and related tools and programming skills

 

Then there are companies that have products related to these topics, as show in Figure 4.

Figure 4: Ontology of the “AI/ML” topic with added information on companies that have AI/ML products

 

This type of information (an ontology) is curated by experts and represented as a knowledge graph in many organizations as a way to store descriptions of entities and how they are related to each other.

Finally we include the candidates you may be considering to hire for the role that requires AI/ML expertise, and the connections they have to various entities in the ontology.

Figure 5: How candidates are connected to the AI/ML ontology

 

This network of concepts (the ontology) and how the candidates fit in this network provide a more complete picture of our candidate pool. How a candidate is connected to a required skill or topic can determine how strongly they satisfy the requirements.For example, Jane has worked for company Y, which develops products for data analytics (Data Science). John has experience in Python, has participated in Hackathons and has GitHub repositories with code related to NLP. So we can see that John has more connections to the AI/ML topic, but Jane has the work experience that almost perfectly matches the AI/ML developer role that you are looking to fill.

How can we programmatically determine the different paths that connect a candidate to the goal of being an AI/ML developer with a relevant set of skills? This brings us to property graphs.

What is a Property Graph?

Like knowledge graphs a property graph also connects entities by the relationships between them. In addition, each entity (also known as a ‘node’) and relationship (also known as an ‘edge’) can have attributes (also known as ‘properties’). Property graphs can be queried based on how nodes are connected to each other by a path and the length of each path, filtered based on properties of the node and the edge. Graph algorithms such as PageRank, Dijkstra’s shortest path and clustering can be run on property graphs.Property graphs are particularly well suited for path analysis that explores how entities are connected.

In our example, the goal would be to find candidates connected to the AI/ML node in the graph and then determine which connection is the strongest. This might be by looking at the number of hops in a path (fewer hops might imply a stronger connection), or weights assigned as a property to each edge. Or you can just determine the number of paths – the more the number of paths, the stronger the connection. Additionally, knowing and identifying the intermediate nodes in every path, rather than having a black box that identifies paths for each candidate is important. For example, that information is relevant for the candidate’s future skills development plan once they are hired. This feature, the explainability of an outcome or prediction, is enabled by graphs. Every piece of information in the graph can be traced to where it comes from and what it connects to.

What Does a Property Graph View on a Knowledge Graph Do?

The formal semantics and syntax of the knowledge graph standards provide a simple way to capture information about a domain, but do not enable flexibility in traversing paths in that graph. Property graphs are more flexible, and have evolved to enable developers to find paths, clusters, spanning trees, and other structures in a graph. Most vendors focus on one of these two graph models, whereas Oracle provides the ability to use both property graphs and RDF knowledge graphs, as well as create a property graph view from an RDF knowledge graph, all in the same database.

Let us go back to our example and see what insights we can discover from such a property graph view. Jane has worked for company Y, which develops products for data analytics (Data Science). John has experience in Python, a necessary skill for Data Scientists, and has participated in Hackathons and has GitHub repositories with code related to NLP. He worked for company X, but there is no known connection between that company and the skills we are looking for. Kavitha has taken courses in Data Science, while Yi-Ching has taken courses in Statistics, and also published papers at conferences.  Katia has experience in Cohere, which is related to LLMs. These are all different paths each candidate has to the skills the position requires. 

Consider candidate Jane

Figure 6: Ontology of the “AI/ML” topic and Jane’s path to the AI/ML topic

 

Jane – works for -> Company Y – which develops products in -> Data Science –which is a subtopic of-> AI/ML.

This is a strong path as she has actual experience working for a product company in an area related to the AI/ML topic.

Consider candidate John

Figure 7: Ontology of the “AI/ML” topic and John’s paths to the AI/ML topic

 

John – works for –> Company X

John – has experience -> Python – skill needed for -> Data Science – which is a subtopic of -> AI/ML

John – has experience -> Python – skill needed for -> NLP – which is a subtopic of -> AI/ML

John – has participated in -> Hackathons – related to -> Spark NLP – API/tool for -> NLP - which is a subtopic of -> AI/ML

John – has created -> GitHub repos – related to -> Spark NLP – API/tool for -> NLP - which is a subtopic of -> AI/ML

John has four paths to the node AI/ML, but the company he worked for, Company X, does not appear to have a connection to the AI/ML topic.

The property graph view on a knowledge graph for the AI/ML topic gives you a deeper and more complete view of applicants to help you decide which candidates to hire.

More Informed Decision Making

“Is there a connection between two entities Y and Z?” or “how do you reach an entity B from entity A?” are important questions in many applications. In this example, we can have a more complete picture of how a candidate is related to the required skills by asking such property graph path analytics questions. The good news is that you can run such analytics using a property graph view on knowledge graphs, created through a new “no-code” Graph Studio interface. I am sure you are very excited about the new possibilities this may give you – I will follow up with another blog with an example of how you may go about creating a property graph view on an RDF knowledge graph to run graph analytics. Stay tuned.

Source: oracle.com

Continue reading

MicroTx Enterprise Edition Features

Oracle recently announced the availability of MicroTx Enterprise Edition. This new release adds many new enterprise features making MicroTx Enterprise Edition suitable for a wide variety of enterprise applications. This blog post will go into the details of these new features.

No Limit on Transaction Throughput

One of the most important changes in the MicroTx Enterprise Edition is the elimination of the transaction throughput limit. MicroTx Free only allows 4,800 transactions per hour as it is intended for development and not production or enterprise deployments. With the release of MicroTx Enterprise Edition, this limitation no longer exists and MicroTx Enterprise Edition deployments can handle hundreds to thousands of transactions per second. This level of throughput exceeds the requirements of almost all applications. To put transaction volumes in perspective, Visa claims to have processed 276 billion transactions in the 12 months ending in September 30, 2023, which works out to an average of 8,700 transactions per second.

Admin Console

Enterprise users need a way to monitor and manage their distributed transactions. With the new administration console added to MicroTx Enterprise Edition, users can observe what transactions are in flight, performance metrics for the coordinator cluster, commit/rollback transactions with heuristic outcomes, and more.

Transaction Coordinator Clusters

For enterprise users, this release of MicroTx Enterprise Edition provides greatly improved reliability, availability, scalability, and performance (RASP) features. Much of this comes from a couple of key features including the ability to run multiple copies of the MicroTx Enterprise Edition transaction coordinator as part of a cluster. This allows MicroTx Enterprise Edition to scale out and scale in to meet the demands of your application. Should you need more transaction throughput, you can simply increase the number of replicas of the MicroTx Enterprise Edition transaction coordinator. 

For the clustering support to improve performance, the service mesh to which MicroTx Enterprise Edition is deployed must support request affinity. This means that requests from an initiator and the other participants in a transaction will land on the same transaction coordinator instance. This is typically provided by the service mesh by hashing some HTTP header and using that hash to determine which transaction coordinator instance the request should be delivered to. This also allows the transaction coordinator to cache transaction state in memory as all related requests will usually end up on the same instance. Should a request, as part of a transaction, land on a different instance due to failure or scaling operations, that instance can recover the transaction state from the persistent transaction state maintained in etcd or Oracle Database.

Persistent Transaction State

To ensure transactions can be properly handled in the presence of failures, the state of the transaction needs to be persisted in case a transaction coordinator fails. MicroTx Enterprise Edition supports persisting transaction state to either etcd or Oracle Database. Either can be used just by setting the appropriate configuration information.

By storing the transaction state in a shared persistent store, the MicroTx Enterprise Edition transaction coordinator can recover from a variety of failures. Should a coordinator instance fail, another transaction coordinator can take over handling any inflight transactions being processed by the failed coordinator. Whether that failure is a crash, network failure, operator error, or pretty much any other reason, subsequent requests will be handled by one of the remaining transaction coordinator instances. This ensures transactions can be successfully processed even amid most failures. There are always some failures that can cause a heuristic outcome, such as a database prematurely committing or rolling back its portion of a transaction.

RAC Support for XA Transactions

Oracle RAC databases require that transaction branches not span RAC instances. This means a transaction manager must be aware of the RAC instance participants are using, or use singleton database services that are only active on one RAC instance at a time. MicroTx Enterprise Edition handles this by having the RAC instance ID reported to the transaction coordinator when a participant microservice enlists in a transaction. This allows the coordinator to determine if a new transaction branch is required or not. Without this capability, microservices using a RAC database would have to use singleton database services.

Common XID

In the XA pattern for distributed transactions, each transaction branch needs to be prepared and committed. More branches equates to more messages that need to be sent when committing the transaction. To help prevent the proliferation of transaction branches, MicroTx Enterprise Edition will try to reuse branches if possible. It does this by having the participants report what resource manager they’re using when they enlist in the transaction. The coordinator uses this information to decide if a new transaction branch needs to be created. If there is already an existing transaction branch in the resource manager, the coordinator will return the same XID (global transaction identifier and branch qualifier), thus eliminating the need for another branch. If this is the first time the resource manager is being enlisted in the transaction, the coordinator will return a new unique branch qualifier. In the case where all participants are using the same resource manager such as Oracle database, the transaction will be committed one phase saving a lot of messages and eliminating the need for the coordinator to write to its transaction log. This can result in dramatic performance improvements.

XA Transaction Promotion

When starting an XA transaction, if enabled, MicroTx Enterprise Edition will actually start a local transaction. The transaction will remain a local transaction until such time as another participant may become involved in the transaction. At that point, MicroTx Enterprise Edition will promote the local transaction to a full XA transaction. This makes the cost of starting a transaction minimal until such time it may need to be promoted. Currently the decision to promote occurs whenever a call is made to another microservice. At that point MicroTx Enterprise Edition promotes the transaction even though the called microservice may not end up being a participant.This currently only works with Oracle database.

Grafana Dashboard

Included in the MicroTx Enterprise Edition release are Grafana dashboards. The MicroTx Enterprise Edition coordinator provides metrics that can be collected by Prometheus and visualized using Grafana as shown in this picture.

Summary

This new Enterprise Edition of MicroTx provides all the features, performance, and resiliency required for enterprise applications. Developer should feel comfortable relying on MicroTx Enterprise Edition to help address their data consistency requirements in microservice based applications. Start with MicroTx Free while developing and testing your microservices, and then switch to MicroTx Enterprise Edition when it comes time to move to production.

Source: oracle.com

Continue reading

Introducing Select AI - Natural Language to SQL Generation on Autonomous Database

Introducing Autonomous Database Select AI - enabling you to query your data using natural language. Combining generative AI large language models (LLMs) with Oracle SQL empowers you to describe what you want - declarative intent - and let the database generate the SQL query relevant to your schema. Some LLMs might be good at generating SQL, but being able to run that SQL against your database is another matter. Select AI enables generating SQL that is specific to your database.

Since LLMs are trained on huge volumes of text data, they can understand the nuances and even intended meaning in most natural language queries. Using natural language to generate SQL reduces the time required to generate queries, simplifies query building, and helps to minimize or eliminate specialized SQL knowledge. Using natural language, deriving information from your database that involves writing queries with multiple joins, nested subqueries, and other SQL constructs becomes much easier and faster. While analytics tools make it easy to query and understand database data, the ability to interact with your SQL database using natural language prompts can help increase productivity of expert and non-expert SQL users to query their database without writing queries.

By learning effective SQL query patterns from curated training data, LLMs can produce more efficient queries - enabling them to perform better. As part of Oracle Autonomous Database, Select AI inherits all security and authentication features of the database.

By virtue of being integrated with Oracle SQL, this capability is available through any SQL IDE, SQL Developer Web, Oracle Application Express (APEX), and Oracle Machine Learning Notebooks. Any application that invokes SQL and has an AI provider account also has access to Select AI.

Read on to explore how to take advantage of this new capability in Autonomous Database. 

LLMs are not enough

Some LLMs are good at generating SQL, often correctly inferring what the user wants and inventing table and column names in the structure of a valid SQL query. Of course, this query works only if your database has tables with columns as generated. In a sense, the LLM hallucinates the tables and columns needed to produce the SQL query. You can use such a generated query as a template and manually change table and column names as needed, but what if you didn't need to?

Consider the following prompt “Generate the SQL to find the total amount spent by each customer on iPhones.” Below, we used OpenAI's ChatGPT and see the use of Customers and Purchases tables, a group-by clause, the aggregation using SUM, and a filter using the product name. Such LLMs provide a great start for SQL generation, but they need context in the form of the user’s specific tables and columns. You could manually add details to your prompt with the table definition, but no doubt you’d prefer that the system identifies relevant tables and columns and automatically augment the prompt. This augmented prompt enables the LLM to produce a much more relevant query. 

To produce a runnable query, we need to anchor this to a specific schema, and this is where Select AI comes in. By setting up a profile, you can by default use your local schema, but you can also optionally specify the schema(s) and the table(s) you want to be considered when generating queries. 

As depicted below, the original natural language query is augmented with metadata from schema(s) identified in the user’s profile. Feeding this to the LLM allows the LLM to produce a SQL query runnable against your database, and ideally provide the results you’re looking for. 

We say ideally because even with an augmented prompt, LLMs aren’t perfect and so it will be important to vet the results from the query and possibly the query itself. 

Examples using Select AI

Let’s look at a few examples. We’ll start simply. How many customers in San Francisco are married? Using the keyword “AI”, we run the SQL command (shown here in OML Notebooks using the %sql interpreter) “SELECT AI how many customers in San Francisco are married”. We see the result as well as the SQL query generated by our LLM adding the keyword “showsql” after “SELECT AI”. 

The next query highlights using the LLM’s broader knowledge. To “find the top 3 baby boomer big spenders” requires an understanding of what a “baby boomer” is, or more importantly, what date-of-birth years apply, and that finding the top 3 big spenders requires ordering by AMOUNT_SOLD. This finds those customers who buy big ticket items. The LLM draws on its broader understanding of these concepts to correctly generate an applicable query. 

You can use the chat option to interact with the LLM as you would a chatbot with a single message. In the following example, a developer may need to create a table. By describing the table you want, it generates a CREATE TABLE statement you can use directly or modify to suite your needs. 

You can also use ‘chat’ to ask “what is Oracle Autonomous Database?” or understand what a query is doing, as shown below. 

DBMS_CLOUD_AI Package

Enabling this feature is a new package, DBMS_CLOUD_AI, in Autonomous Database that enables the use of LLMs for generating SQL from natural language prompts. The package provides access to user specified LLMs along with knowledge of the user’s accessible database metadata. This enables producing runnable SQL queries applicable to that schema. The DBMS_CLOUD_AI package currently integrates with AI providers such as OpenAI and Cohere, with others planned to follow. To use this feature, you need an account with the AI provider and must supply your API credentials to Autonomous Database. 

Getting started

To get started with Select AI, sign into your Autonomous Database instance with administrator privileges and add your user (here MY_USER) to the ACL list and grant access to the DBMS_CLOUD_AI package:

BEGIN  

    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(

         host => 'api.openai.com',

         ace  => xs$ace_type(privilege_list => xs$name_list('http'),

                             principal_name => 'MY_USER',

                             principal_type => xs_acl.ptype_db)

   );

END;

grant execute on DBMS_CLOUD_AI to MY_USER;

Create a database credential to your AI provider account. In the case of OpenAI, the password is the uniquely generated token for API usage.

BEGIN

  DBMS_CLOUD.DROP_CREDENTIAL (

   credential_name  => 'OPENAI_CRED');

  DBMS_CLOUD.CREATE_CREDENTIAL(

    credential_name => 'OPENAI_CRED',

    username => 'OPENAI',

    password => '...' );

END;

Create your DBMS_CLOUD_AI profile. Here, we list the schema(s) we want to be considered (e.g., SH) and, optionally, any tables (e.g., customers, sales, etc.).

BEGIN

  DBMS_CLOUD_AI.drop_profile(profile_name => 'OPENAI');

  DBMS_CLOUD_AI.create_profile(

      profile_name => 'OPENAI',

      attributes => '{"provider": "openai",

                      "credential_name": "OPENAI_CRED",

                      "object_list": [{"owner": "SH", "name": "customers"},  

                                      {"owner": "SH", "name": "sales"},

                                      {"owner": "SH", "name": "products"},

                                      {"owner": "SH", "name": "countries"}]

       }');

END;

Lastly, set your DBMS_CLOUD_AI profile, which must be done in each database session. 

BEGIN

  DBMS_CLOUD_AI.SET_PROFILE(

     profile_name => 'OPENAI'

  );

END;

Now, you’re ready to invoke your first Select AI prompt. 

Note that if you're using a stateless environment, like the Database Actions SQL worksheet in Autonomous Database, use the DBMS_CLOUD_AI.GENERATE function in your SQL query. This allows you to specify the AI profile directly for a stateless invocation, as shown in the following example.

Benefits and limitations

The ability to generate SQL queries using natural language to get useful results sounds amazing. You get ease of use without SQL expertise. This feature is seamlessly integrated with your database and because it’s SQL, it readily integrates with your analytics tools and applications. It can help speed up the information retrieval process since you don’t have to write the SQL yourself or wait for a SQL expert to write it for you. The SQL is also specific to your database tables. 

Despite how well LLMs perform – even with augmented prompts – they’re not perfect. As such, the results and possibly the corresponding queries should be reviewed before relying on the results. The way you write the prompt – as with any LLM interaction – can have a significant effect on the output. 

While large language models (LLMs) are adept at generating useful and relevant content, they also can generate incorrect and false information - known as hallucinations. This includes SQL queries that do not produce the desired results. As a result of being trained on a broad set of text documentation and content, typically from the Internet, LLMs may have incorporated patterns from invalid or malicious content, which may impact the accuracy and security of your results. 

As noted above, Select AI augments the user-specified prompt with database metadata to mitigate hallucinations from the LLM. The augmented prompt is then sent to the user specified LLM to produce the query. Select AI automates the process that the user could do manually.

Source: oracle.com

Continue reading