Hi , We're building a production-grade Oracle Select AI Agent for NL2SQL over Oracle Autonomous Database using: * AI Profile * Team * Agent * Task * SQL Tool * Semantic Views * Oracle APEX as the UI The agent is working well, but as we've added more business rules and guardrails, our instructions have grown to approximately: * SQL Tool: ~86K characters * Task: ~11K characters Most of these instructions enforce behavior (SQL patterns, validation, formatting, ranking rules, hallucination prevention, etc.), while the business model itself is still relatively small (currently only two Semantic Views). My questions are: * Is this the right direction, or is such a large prompt a sign that some of the logic should move elsewhere? * What are Oracle's best practices for deciding whether business logic belongs in:* Prompt instructions * Semantic Views * Helper views/tables * Other metadata? * As the agent grows to dozens of Semantic Views and hundreds of KPIs, what is the recommended architecture to keep it maintainable and scalable? * Are there any Oracle best practices or reference architectures for building enterprise-grade Select AI Agents that remain accurate over time? I'd really appreciate guidance from the Oracle engineering team on how you would architect this kind of solution for long-term production use. Thanks!

Hi Avivb, Your current approach is probably too much logic in the prompt. A prompt that is 86K characters for the SQL Tool and 11K for the Task is usually a sign that stable business rules should move into the database layer instead of staying in instructions. Oracle has internal Select AI lab material and AI strategy guidance that point in this direction, and Oracle also has separate AI governance material for approved use and policy. ADB Select AI - Lab Steps.htmlHTML AI Data Strategy Portal Policy for development and use of AI in Oracle products and services. The simple rule: * Prompt instructions: keep only behaviour rules, such as safety, clarification, SQL output format, and when to reject or validate. * Semantic Views: put the business meaning here, such as entities, KPI definitions, labels, and common dimensions. * Helper views / tables: put repeated joins, calculations, filters, and approved logic here. * Metadata / AI Profile: use this for object selection, annotations, and what the agent is allowed to see. In plain terms: the prompt should tell the agent how to behave, the database should hold what the business means.For a production setup that will grow to dozens of Semantic Views and hundreds of KPIs, I would recommend: * Split by domain, not one giant model. * Keep a small prompt for agent behaviour. * Put reusable business logic in views. * Put KPI definitions in Semantic Views or governed metadata. * Use AI Profiles to expose only the right objects. * Review and version the business layer separately from the prompt. https://docs.oracle.com/en-us/iaas/autonomous-database-serverless/doc/select-ai-data-gateway.html Regards, Arindam.

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle AI and Analytics

Child Item

Best Practices for Scaling Oracle Select AI Agents in Production

avivb

Hi ,

We're building a production-grade Oracle Select AI Agent for NL2SQL over Oracle Autonomous Database using:

AI Profile
Team
Agent
Task
SQL Tool
Semantic Views
Oracle APEX as the UI

The agent is working well, but as we've added more business rules and guardrails, our instructions have grown to approximately:

SQL Tool: ~86K characters
Task: ~11K characters

Most of these instructions enforce behavior (SQL patterns, validation, formatting, ranking rules, hallucination prevention, etc.), while the business model itself is still relatively small (currently only two Semantic Views).

My questions are:

Is this the right direction, or is such a large prompt a sign that some of the logic should move elsewhere?
What are Oracle's best practices for deciding whether business logic belongs in:
- Prompt instructions
- Semantic Views
- Helper views/tables
- Other metadata?
As the agent grows to dozens of Semantic Views and hundreds of KPIs, what is the recommended architecture to keep it maintainable and scalable?
Are there any Oracle best practices or reference architectures for building enterprise-grade Select AI Agents that remain accurate over time?

I'd really appreciate guidance from the Oracle engineering team on how you would architect this kind of solution for long-term production use.

Thanks!

Find more posts tagged with

Accepted answers

Arindam Sadhukhan-Oracle

Hi Avivb,

Your current approach is probably too much logic in the prompt. A prompt that is 86K characters for the SQL Tool and 11K for the Task is usually a sign that stable business rules should move into the database layer instead of staying in instructions. Oracle has internal Select AI lab material and AI strategy guidance that point in this direction, and Oracle also has separate AI governance material for approved use and policy. ADB Select AI - Lab Steps.htmlHTML AI Data Strategy Portal Policy for development and use of AI in Oracle products and services.

The simple rule:

Prompt instructions: keep only behaviour rules, such as safety, clarification, SQL output format, and when to reject or validate.
Semantic Views: put the business meaning here, such as entities, KPI definitions, labels, and common dimensions.
Helper views / tables: put repeated joins, calculations, filters, and approved logic here.
Metadata / AI Profile: use this for object selection, annotations, and what the agent is allowed to see.

In plain terms: the prompt should tell the agent how to behave, the database should hold what the business means.For a production setup that will grow to dozens of Semantic Views and hundreds of KPIs, I would recommend:

Split by domain, not one giant model.
Keep a small prompt for agent behaviour.
Put reusable business logic in views.
Put KPI definitions in Semantic Views or governed metadata.
Use AI Profiles to expose only the right objects.
Review and version the business layer separately from the prompt.

https://docs.oracle.com/en-us/iaas/autonomous-database-serverless/doc/select-ai-data-gateway.html

Regards,

Arindam.

All comments

Arindam Sadhukhan-Oracle

Hi Avivb,

The simple rule:

Prompt instructions: keep only behaviour rules, such as safety, clarification, SQL output format, and when to reject or validate.
Semantic Views: put the business meaning here, such as entities, KPI definitions, labels, and common dimensions.
Helper views / tables: put repeated joins, calculations, filters, and approved logic here.
Metadata / AI Profile: use this for object selection, annotations, and what the agent is allowed to see.

Split by domain, not one giant model.
Keep a small prompt for agent behaviour.
Put reusable business logic in views.
Put KPI definitions in Semantic Views or governed metadata.
Use AI Profiles to expose only the right objects.
Review and version the business layer separately from the prompt.

https://docs.oracle.com/en-us/iaas/autonomous-database-serverless/doc/select-ai-data-gateway.html

Regards,

Arindam.

Bhaskar Konar

Very helpful & useful information.

Thanks for sharing @Arindam Sadhukhan-Oracle!

avivb

Thank you!