An AI feature survives production when it is good, affordable, and fast. All three can be measured from inside Oracle Database: a call log shows speed and failures, the provider's token counts show what drives cost, the same test run with two models shows whether a smaller one is enough, and a semantic cache avoids calls altogether, if its cutoff is strict.
This guide measures speed and failures from the log, the tokens behind a RAG call with and without thinking, Gemini Flash against Flash Lite on classification and RAG, and a semantic answer cache, all in Oracle AI Database 26ai.
Code for This Guide
The examples are files 01 to 04 and 07 in the examples/ch28 folder of the Oracle AI code repository on GitHub, each with its output.
These examples come from AI Applications with Oracle Database 26ai and APEX 26.1, a book of 237 tested examples of AI in Oracle Database and APEX.
They read the LLM_CALLS log of the GENERATE function from how to make LLM calls reliable in PL/SQL, and test the ASK function from how to build RAG with PL/SQL in Oracle Database. The token counts come from Gemini's REST API through an APEX web credential, so no key appears in the code.
Measure Speed and Failures from the Log
When every call goes through one logging function, the log answers the first questions about speed and reliability.
Example:
-- how fast each model answers: the median and the slowest 5 percent of calls
select model, count(*) as calls,
percentile_cont(0.5) within group (order by elapsed_ms) as median_ms,
percentile_cont(0.95) within group (order by elapsed_ms) as p95_ms,
count(case when error is not null then 1 end) as failed,
count(case when attempts > 1 then 1 end) as retried
from llm_calls
group by model
order by calls desc;Output:
MODEL CALLS MEDIAN_MS P95_MS FAILED RETRIED ______________ ________ ____________ __________ _________ __________ GEMINI 247 2629 17338.2 2 3 LLAMA_LOCAL 134 380 1516.9 0 0 GEMINI_LITE 13 1389 4282.4 0 2
The median call to Gemini Flash took about 2.6 seconds, and the slowest 5 percent up to about 17 seconds: batch classifications and calls with long thinking. A few calls failed, and a few succeeded only on a retry. The local model answered in well under a second. Watch the 95th percentile, not the average: users remember the slow answers. A column for the calling feature in the log lets you run the same query per feature.
See What Drives Cost
Providers charge per token: prompt tokens at one price, and the tokens the model writes, thinking included, at a higher one. TOKEN_USAGE sends a prompt through the REST API and returns the token counts the provider reports; here it measures a RAG prompt of two articles and a question, with and without thinking.
Example:
-- the tokens of one call, from Gemini's own count (the REST API)
create or replace function token_usage (
p_prompt in clob,
p_model in varchar2 default 'gemini-flash-latest',
p_config in varchar2 default null -- generationConfig, as JSON text
) return json
is
l_body clob;
l_response clob;
begin
select json_object('contents' value json_array(json_object('parts' value
json_array(json_object('text' value p_prompt returning clob))
returning clob) returning clob),
'generationConfig' value nvl(p_config, '{}') format json
returning clob)
into l_body
from dual;
apex_util.set_workspace('ATLAS');
for attempt in 1 .. 3 loop -- a busy provider answers without usage
apex_web_service.set_request_headers('Content-Type', 'application/json');
l_response := apex_web_service.make_rest_request(
p_url => 'https://generativelanguage.googleapis.com/v1beta/models/' || p_model
|| ':generateContent',
p_http_method => 'POST',
p_body => l_body,
p_credential_static_id => 'credentials-for-gemini');
exit when json_exists(l_response, '$.usageMetadata');
dbms_session.sleep(2 * attempt);
end loop;
return json_object(
'prompt' value json_value(l_response, '$.usageMetadata.promptTokenCount'),
'thinking' value nvl(json_value(l_response, '$.usageMetadata.thoughtsTokenCount'), 0),
'answer' value json_value(l_response, '$.usageMetadata.candidatesTokenCount')
returning json);
end;
/
-- a RAG prompt, with thinking and without
with p as (
select 'Sources:' || chr(10)
|| (select listagg(dbms_lob.substr(text, 4000), chr(10)) from knowledge
where source in ('Article KB-201', 'Article KB-202'))
|| chr(10) || 'Question: Can I get my money back for a duplicate charge?' as prompt
from dual)
select 'thinking on' as call, json_serialize(token_usage(prompt)) as tokens from p
union all
select 'thinking off',
json_serialize(token_usage(prompt,
p_config => '{"thinkingConfig": {"thinkingBudget": 0}}'))
from p;Output:
Function TOKEN_USAGE compiled
CALL TOKENS
_______________ __________________________________________________
thinking on {"prompt":"137","thinking":"240","answer":"38"}
thinking off {"prompt":"137","thinking":"0","answer":"41"}The prompt is 137 tokens and the answer about 40. With thinking on, the model spent another 240 tokens thinking, six times the answer, all billed as output. With output priced several times higher than input, thinking made this call several times more expensive.
| Pattern | What to do |
|---|---|
| Generation costs far more than embedding | Embedding a whole help desk costs less than a cent; spend effort on generation calls. |
| Output costs more than input, and thinking is output | Turn thinking off where the task needs no reasoning. |
| Prompts grow with RAG | Every source added is paid on every call; send only as many as you need. |
| Calls multiply | Never call a model per row of a report or per keystroke; store results. |
Estimate a feature's monthly cost as calls per month times (prompt tokens times input price plus output tokens times output price), with measured token counts and your provider's current prices.
Test a Smaller Model
The Lite model is faster and cheaper; whether it is good enough depends on the task. AGREEMENT classifies 100 test tickets in batches of 25 with a given model and counts how many match the agents.
Example:
-- the 100 test tickets, classified in batches of 25 by a model; how many like the agents?
create or replace function agreement (p_model in varchar2) return varchar2
is
l_tickets clob;
l_answer clob;
l_right pls_integer := 0;
l_start timestamp := systimestamp;
begin
for b in 0 .. 3 loop
select json_arrayagg(json_object('ticket_id' value ticket_id,
'text' value subject || '. ' || description
returning clob) returning clob)
into l_tickets
from tickets
where mod(ticket_id, 4) = 0 and mod(ticket_id / 4, 4) = b;
l_answer := generate(
'Classify each support ticket of Atlas Software. '
|| 'Categories: Account (sign-in, users, security), '
|| 'Billing (invoices, payments, plans, tax), Bug (something does not work), '
|| 'Question (how to do something), Feature Request (something that does not exist). '
|| 'Tickets: ' || l_tickets,
p_model,
json('{"generationConfig": {"temperature": 0, "thinkingConfig": {"thinkingBudget": 0},
"responseMimeType": "application/json",
"responseSchema": {"type": "ARRAY", "items": {"type": "OBJECT",
"required": ["ticket_id", "category"], "properties": {
"ticket_id": {"type": "INTEGER"},
"category": {"type": "STRING",
"enum": ["Account", "Billing", "Bug", "Question",
"Feature Request"]}}}}}}'));
select l_right + count(*) into l_right
from json_table(l_answer, '$[*]' columns (ticket_id number path '$.ticket_id',
category varchar2(20) path '$.category')) j
join tickets t on t.ticket_id = j.ticket_id and t.category = j.category;
end loop;
return l_right || ' of 100 right in '
|| round(extract(minute from (systimestamp - l_start)) * 60
+ extract(second from (systimestamp - l_start)), 1) || ' seconds';
end;
/
select 'GEMINI' as model, agreement('GEMINI') as result from dual
union all
select 'GEMINI_LITE', agreement('GEMINI_LITE') from dual;Output:
Function AGREEMENT compiled MODEL RESULT ______________ __________________________________ GEMINI 90 of 100 right in 11.8 seconds GEMINI_LITE 91 of 100 right in 10.5 seconds
The Lite model agreed with the agents as often as Flash, 91 against 90, in slightly less time and at a fraction of the price. The same RAG test with each model:
Example:
-- the 14 document questions answered with Gemini Flash and with Flash Lite
with keys (question_id, key_fact) as (
values (1, '20'), (2, '7 days'), (3, '20 MB'), (4, '180 days'), (5, '3 days'),
(6, '3 business days'), (7, 'ten months'), (8, '11'), (9, '15 minutes'),
(10, '100'), (11, '30 June 2027'), (12, '5 GB'), (13, '14 September 2026'),
(14, '40')),
models (llm) as (values ('GEMINI'), ('GEMINI_LITE'))
select m.llm, count(*) as questions,
count(case when instr(a.answer, k.key_fact) > 0 then 1 end) as with_key_fact,
count(case when regexp_like(a.answer, '\[\d') then 1 end) as with_citation
from models m cross join doc_questions q
join keys k on k.question_id = q.question_id
cross apply (select ask(q.question, 'MINILM', m.llm) as answer from dual) a
group by m.llm;Output:
LLM QUESTIONS WITH_KEY_FACT WITH_CITATION ______________ ____________ ________________ ________________ GEMINI 14 14 14 GEMINI_LITE 14 14 14
Both answered all 14 with the key fact and a citation. For classification and answering from given sources, these tests give no reason to pay for the larger model. Tasks that need reasoning, such as SQL generation, may; run the same test with both to know.
The comparison only works because GENERATE strips thinking settings for models that do not think. The Lite model rejects them with "Request contains an invalid argument", which is one more reason to route every call through one function.
Cache Answers, with a Strict Cutoff
The cheapest call is the one you do not make. Questions repeat in many wordings, and a semantic cache stores answers with the embeddings of their questions, reusing an answer when a new question is near enough. ASK_CACHED reuses a cached answer within a distance of 0.12 by default, and otherwise asks and caches the new answer.
Example:
create table answer_cache (
question varchar2(4000) not null,
embedding vector(384, float32) not null,
answer clob not null,
cached_at date default sysdate not null
);
-- answers ASK, or reuses the answer of a cached question that is nearer than p_max_distance
create or replace function ask_cached (
p_question in varchar2,
p_max_distance in number default 0.12
) return clob
is
l_vector vector(384, float32);
l_answer clob;
procedure cache_answer is
pragma autonomous_transaction;
begin
insert into answer_cache (question, embedding, answer)
values (p_question, l_vector, l_answer);
commit;
end;
begin
select vector_embedding(all_minilm_l12_v2 using p_question as data)
into l_vector
from dual;
begin
select answer into l_answer
from answer_cache
where vector_distance(embedding, l_vector) <= p_max_distance
order by vector_distance(embedding, l_vector)
fetch first 1 row only;
exception
when no_data_found then -- nothing near enough: ask, and cache the answer
l_answer := ask(p_question);
cache_answer;
end;
return l_answer;
end;
/
-- the first question is answered and cached
select ask_cached('How many custom roles can an Enterprise account have?') as answer
from dual;
-- two more questions: how near is the cached one, and would a cutoff reuse its answer?
with q (question) as (
values ('What is the maximum number of custom roles on Enterprise?'),
('How many users can an Enterprise account have?'))
select q.question,
round(min(vector_distance(c.embedding,
vector_embedding(all_minilm_l12_v2 using q.question as data))), 3) as distance,
case when min(vector_distance(c.embedding,
vector_embedding(all_minilm_l12_v2 using q.question as data))) <= 0.12
then 'reused' else 'asked' end as at_0_12,
case when min(vector_distance(c.embedding,
vector_embedding(all_minilm_l12_v2 using q.question as data))) <= 0.25
then 'reused' else 'asked' end as at_0_25
from q cross join answer_cache c
group by q.question;Output:
Table ANSWER_CACHE created. Function ASK_CACHED compiled ANSWER ________________________________________________________________________ Enterprise accounts can create up to 20 custom roles per account [1]. QUESTION DISTANCE AT_0_12 AT_0_25 ____________________________________________________________ ___________ __________ __________ What is the maximum number of custom roles on Enterprise? 0.106 reused reused How many users can an Enterprise account have? 0.205 asked reused
The rewording is 0.106 away, and both cutoffs reuse the answer correctly. The question about users is 0.205 away, so a cutoff of 0.25 would answer it with the number of custom roles. To the embedding model, "how many users" and "how many custom roles" on an Enterprise account are nearly the same question; to a customer they are not.
A semantic cache needs a strict cutoff that only near-identical wordings pass, measured with questions that are close but different, and cached answers need an expiry so they change when the knowledge does.
Conclusion
Reduce the cost of LLM calls in Oracle by measuring first: median and 95th-percentile times from the call log, and token counts from the provider, which show that thinking tokens can cost several times the answer. Turn thinking off for simple tasks, keep RAG prompts lean, test a smaller model on your own tasks before paying for a larger one, and cache answers semantically only with a strict, measured cutoff and an expiry.
