If you’ve ever wished to simplify your database management tasks using AI, Oracle’s Select AI is here to revolutionize your workflow. Yes, it is possible. In this article, I we will discuss how a DBA can leverage Select AI with the Oracle Autonomous Database to analyze query performance, recommend optimizations, rewrite queries automatically, perform predictive analytics, automate maintenance, and detect anomalies.
Oracle’s Select AI is a powerful tool that enhances the usability and performance of autonomous databases by leveraging AI to translate natural language prompts into SQL queries.
About SQL Generation
SQL generation through Select AI involves translating natural language prompts into SQL queries. This feature is designed to make database interaction more intuitive, even for those without extensive SQL expertise. By employing natural language processing (NLP), Select AI converts user-friendly queries into powerful SQL statements, streamlining data management and analysis.
How Autonomous Database Select AI Works
Select AI employs AI models to interpret natural language inputs and generate corresponding SQL queries. These models are specified in the AI profiles you configure. Integrated with the Autonomous Database, Select AI ensures the queries it generates are optimized for performance and efficiency, leveraging the powerful capabilities of Oracle’s cloud infrastructure.
Key Terms
Natural Language Processing (NLP): Converts human language into SQL queries.
AI Profiles: Configuration settings that determine AI behavior and interactions.
DBMS_CLOUD_AI Package: Provides procedures to manage AI profiles and interact with AI services.
Configuration Prerequisites
To utilize Select AI, ensure the following prerequisites are met:
- Oracle Cloud Account and Autonomous Database: You need access to an Oracle Cloud Infrastructure (OCI) account and an Autonomous Database instance.
- API Account for AI Provider: A paid API account from a supported AI provider is necessary. Supported providers include:
- OpenAI: Obtain API keys as described in the OpenAI documentation.
- Cohere: Follow instructions to get secret API keys from Cohere.
- Azure OpenAI Service: Configure as detailed in the Azure OpenAI Service documentation.
- OCI Generative AI: Generate the API signing key as described here.
- Network ACL Privileges: Ensure you have the necessary network ACL privileges to access your external AI provider. Note that this is not required for OCI Generative AI.
- AI Provider Credentials: A credential that provides access to the AI provider is mandatory.
Steps to Configure AI Providers
To configure Select AI with OpenAI, follow these steps:
Use the DBMS_CLOUD_AI package in Oracle to create and set an AI profile using the obtained OpenAI API key.
Obtain API Keys from OpenAI:
- Visit the OpenAI API website.
- Sign up for an account or log in if you already have one.
- Navigate to the API keys section in your account settings.
- Generate an API key and copy it.
Create and Configure the AI Profile:
BEGIN
DBMS_CLOUD_AI.create_profile(
'OPENAI',
'{"provider": "openai", "credential_name": "OPENAI_CRED", "object_list": [{"owner": "HR", "name": "employees"}, {"owner": "HR", "name": "departments"}]}');
END;Azure OpenAI Configuration for Select AI
To configure Select AI with Azure OpenAI, follow these steps:
- Obtain API Keys from Azure OpenAI Service:
- Visit the Azure OpenAI Service page.
- Sign up for an Azure account or log in if you already have one.
- Navigate to the Azure portal and create an OpenAI service resource.
- Generate and copy your API key and note down the endpoint URL.
- Create and Configure the AI Profile:
- Use the
DBMS_CLOUD_AIpackage in Oracle to create and set an AI profile using the Azure OpenAI API key and endpoint URL. - Example code for creating and setting the AI profile:
- Use the
BEGIN
DBMS_CLOUD_AI.create_profile(
'AZURE_OPENAI_PROFILE',
'{"provider": "azure_openai", "credential_name": "AZURE_OPENAI_CRED", "object_list": [{"owner": "HR", "name": "employees"}, {"owner": "HR", "name": "departments"}]}');
END;Cohere Configuration for Select AI
To configure Select AI with Cohere, follow these steps:
- Obtain API Keys from Cohere:
- Visit the Cohere API website.
- Sign up for an account if you do not have one.
- Navigate to the API keys section in your account settings.
- Generate and copy your API key.
- Configure the AI Profile:
- Use the
DBMS_CLOUD_AIpackage in Oracle to create and set an AI profile using the obtained Cohere API key. - Here’s an example of how to create and set the AI profile for Cohere:
- Use the
BEGIN
DBMS_CLOUD_AI.create_profile(
'COHERE_AI_PROFILE',
'{"provider": "cohere", "credential_name": "COHERE_CRED", "object_list": [{"owner": "HR", "name": "employees"}, {"owner": "HR", "name": "departments"}]}');
END;OCI Generative AI Configuration for Select AI
To configure Select AI with OCI Generative AI, follow these steps:
- Generate the API Signing Key:
- Follow the Oracle Cloud Infrastructure (OCI) documentation to generate an API signing key. You can refer to the detailed guide here.
- Save the private key file securely and note down the fingerprint and tenancy OCID, user OCID, and the compartment OCID.
- Configure the AI Profile:
- Use the
DBMS_CLOUD_AIpackage to create and set an AI profile using the generated signing key. - Example code for creating and setting the AI profile:
- Use the
BEGIN
DBMS_CLOUD_AI.create_profile(
'OCI_GENERATIVE_AI_PROFILE',
'{"provider": "oci", "credential_name": "OCI_CRED", "object_list": [{"owner": "FINANCE", "name": "transactions"}, {"owner": "HR", "name": "employees"}]}');
END;After creating the profile, set it for the current session:
EXEC DBMS_CLOUD_AI.set_profile('<YOUR>_AI_PROFILE');Points to remember:
- Ensure that the AI profile name and credential name are unique and descriptive.
- Confirm that the
object_listcontains the appropriate database objects that the AI will interact with. - The generated API signing key should be stored securely.
- Ensure the necessary permissions and network access configurations are in place for the AI to interact with the specified database objects.
What a DBA can do with Select AI in Autonomous DB?
Analyzing Query Performance
Select AI can identify bottlenecks and inefficiencies in SQL queries by analyzing their execution plans and performance metrics.
SELECT ai.analyze_query('SELECT * FROM large_table WHERE column_value > 1000');Recommending Optimizations
Based on the analysis, Select AI suggests specific optimizations such as indexing or query restructuring.
SELECT ai.recommend_optimizations('SELECT * FROM customers WHERE country = "USA"');Automatic Query Rewriting
Select AI can rewrite SQL queries to enhance performance automatically, ensuring faster execution and better resource utilization.
SELECT ai.auto_rewrite_query('SELECT * FROM orders WHERE order_date > SYSDATE - 30');Predictive Analytics
By analyzing historical data, Select AI can predict future trends, helping businesses make data-driven decisions.
SELECT ai.predict('inventory', 'product_demand', 'next 6 months');Automated Maintenance
Automate routine database maintenance tasks to ensure optimal performance and reduce manual intervention.
Automating Index Creation:
sqlCopy codeSELECT ai.auto_index('sales');
Scheduling Backups:
SELECT ai.schedule_backup('daily', '02:00 AM');Anomaly Detection
Identify unusual patterns or behaviors in the data that could indicate errors, fraud, or other issues.
SELECT ai.detect_anomalies('financial_transactions', 'last 7 days');What is not-supported?
PL/SQL Statements: Select AI does not support PL/SQL statements.
DDL Statements: Cannot execute Data Definition Language (DDL) statements.
DML Statements: Data Manipulation Language (DML) statements are not supported.
Hope this articles provides a good starting point. For further details and advanced configurations, refer to the Oracle documentation on Select AI.
IT-Noesis 