Reviewing AI-Generated SQL Before It Runs on Oracle: A DBA's Checklist

A language model writes SQL that parses, runs and returns rows, and none of that tells you it is safe for your database. The model cannot see your row counts, your indexes, which of your columns allow nulls, or which edition you licensed.
So reviewing generated SQL is not about distrusting the model. It is about supplying the four facts it never had.
If your developers draft SQL with an assistant, that is fine, and in some ways it is an improvement: the statements can arrive formatted, commented and correct in their logic. The risk has moved, not gone.
The mistakes that matter here are not syntax errors, which the parser catches at once. They are plan-level and null-level mistakes, which can pass every test on a small development dataset and only show themselves on a production table many times larger.
This is the review I recommend: eight checks, each one reading something the model could not read. For a single statement it is a short read, not a project.
Most rules below are tied to a line in Oracle's own 19c documentation, because "the AI wrote something odd" is not a reason anybody can act on, while "Oracle's SQL Language Reference says this conversion can stop the index being used" is.
Key Takeaways
- The mistakes that slip through in generated SQL are at the plan level and the null level, not the syntax level. The parser catches neither.
- A number compared with a character column causes an implicit conversion, and Oracle warns that the database "might not use the index because it is defined for the pre-conversion data type".
NOT INis "equivalent to!=ALL" and "evaluates to FALSE if any member of the set is NULL" โ a generatedNOT INover a nullable column can silently return nothing.EXPLAIN PLANis a cheap first read only: Oracle says "the explained plan can differ from the actual plan used during statement execution".DBMS_XPLAN.DISPLAY_CURSORwithALLSTATS LASTshows the plan that really ran, if plan statistics were collected.- Oracle "does not issue error messages for hints that it ignores" โ so read the 19c hint report before keeping a hint a model suggested. Use the
ALLformat to see used and unused hints;TYPICALlists only the unused ones. - Let the first execution run on a dedicated account under a profile that limits
CPU_PER_CALLandLOGICAL_READS_PER_CALL, and on Enterprise Edition under a Resource Manager plan that can cancel a runaway call.

1. Why Generated SQL Needs Its Own Review Step
A model writes SQL from the shape of your question and the shape of your table names. It has no access to your optimizer statistics, your index list, your constraint definitions or your edition, so it produces a statement that is correct as English and unverified as a plan.
Think about what is missing from the prompt. For example, the model does not know that ORDERS holds 40 rows in development and 40 million in production. It does not know that CUSTOMER_ID on the orders table was never declared NOT NULL because a 2009 interface needed to load headers before customers existed.
It does not know that your ACCOUNT_NO column is a VARCHAR2 even though every value in it looks like a number. It does not know whether you run Standard Edition 2, where several of the controls in this article simply do not exist.
None of those are the model's fault. They are facts that live in your data dictionary, and the review is the step where you go and read them. This is the same discipline I describe in my guide to diagnosing any Oracle error with a systematic method: look for the answer in the database before you look for it in an opinion.
The three failure modes to look for
- Wrong plan, right answer. The statement returns exactly the correct rows and reads far more blocks than it needs to. It can go unnoticed until concurrency rises.
- Right plan, wrong answer. A null-handling mistake quietly removes rows. This is the dangerous one, because a report that returns fewer rows can look like a quiet day of business.
- Correct everywhere except here. The statement uses a feature your edition or licence does not cover, or a package your application schema cannot execute.
2. The Review in Order: Read, Measure, Then Run It Somewhere Safe
Do the three stages in order, because each one is cheaper than the next. Reading costs nothing.
Explaining costs a parse. Running costs whatever the statement decides to cost, which is the thing you are trying to find out.
| Stage | What you use | What it catches |
|---|---|---|
| Read the text | Your eyes, plus DBA_TAB_COLUMNS and DBA_IND_COLUMNS (or their ALL_ versions) | Literals, type mismatches, NOT IN, borrowed hints, SELECT * |
| Explain it | EXPLAIN PLAN + DBMS_XPLAN.DISPLAY | Full scans on big tables, unused hints, converted predicates |
| Run it under limits | A restricted account, a profile, DISPLAY_CURSOR with ALLSTATS LAST | Estimate-versus-actual gaps, real buffer gets, runaway calls |
The first stage is the cheapest, and it needs no privileges beyond read access to the dictionary views it queries. Much of checks one to five below happens there.
Note which views those are. Oracle's Database Reference says a DBA_ view "displays all relevant information in the entire database" and that DBA_ views "can be queried only by users with the SYSDBA system privilege or SELECT ANY DICTIONARY privilege, or SELECT_CATALOG_ROLE role, or by users with direct privileges granted to them". A reviewer without that access can use the ALL_ versions, which cover only what the current user can access.
3. Check One: Literals Where Bind Variables Belong
A model can write literals. Asked for a query that finds customer 90210, it may write customer_id = 90210, and an application that loops over customers with that pattern produces one distinct statement per customer, each with its own SQL_ID in the shared pool.
It is an easy problem to miss, because it is invisible in a unit test. One execution is fast.
Thousands of executions with thousands of different literals mean thousands of separate statements to parse and keep. The fix is ordinary: the application binds the value. What the review has to do is notice.
If you are inspecting a system that has already been running, V$SQL has a column that helps. Oracle's reference describes SQL_ID as the "SQL identifier of the parent cursor in the library cache", and FORCE_MATCHING_SIGNATURE as the "Signature used when the CURSOR_SHARING parameter is set to FORCE" โ the setting under which literals are replaced with system-generated binds.
So statements that differ only in their literal values can share one FORCE_MATCHING_SIGNATURE while having different SQL_IDs. Grouping on it shows the families.
-- Families of statements that differ only in their literals (SQL, any OS).
-- Needs SELECT on V$SQL. Read-only: it changes nothing.
SELECT force_matching_signature,
COUNT(DISTINCT sql_id) AS distinct_statements,
SUM(executions) AS executions,
MIN(sql_text) AS one_example
FROM v$sql
WHERE force_matching_signature <> 0
GROUP BY force_matching_signature
HAVING COUNT(DISTINCT sql_id) > 20
ORDER BY distinct_statements DESC
FETCH FIRST 20 ROWS ONLY;
A family with hundreds of members and one execution each is worth opening: it is likely a statement that should have used binds. The threshold of 20 is only a starting filter, not an Oracle limit.
Note that this query reads the shared pool as it is right now; a cursor that has aged out is not in it, so run it when the system is busy rather than at the end of a quiet night.
There is a second reason to care, beyond parsing. A literal inside the statement text is a value inside your shared pool, readable by anyone who can select from V$SQL.
If the generated code pastes a national ID or an account number into the text, you have put customer data somewhere you were not planning to protect. My Oracle database security and hardening guide covers who should hold SELECT ANY DICTIONARY and the V$ views in the first place.
4. Check Two: An Implicit Conversion That Can Cost You Your Index
When a generated predicate compares a character column with a number, Oracle converts the character value, not the number, and the optimizer might not use the plain index on that column.
Oracle's SQL Language Reference covers both halves of this. On the direction of the conversion: "When a condition compares a character value and a NUMBER value, Oracle implicitly converts the character value to a NUMBER value, rather than converting the NUMBER value to a character value." On the consequence: "If implicit data type conversion occurs in an index expression, then Oracle Database might not use the index because it is defined for the pre-conversion data type. This can have a negative impact on performance."
So this innocent-looking generated line:
-- ACCOUNT_NO is VARCHAR2 and indexed; the literal is a NUMBER
SELECT * FROM accounts WHERE account_no = 90210;
becomes, in the optimizer's eyes, a comparison on TO_NUMBER(account_no), while the plain index on ACCOUNT_NO holds the character values. The statement still returns the right row.
What Oracle warns about is the access path: the database might not use the index. If it does not, it has to find the row another way, and on a large table that can mean a full scan. The plan tells you which happened, so read it instead of assuming either outcome.
You do not have to take my word for the symptom, because Oracle's own SQL Tuning Guide prints it. In its hint report example the query filters e.job_id < 5 on a character column, and the predicate section of the plan reads:
Predicate Information (identified by operation id):
2 - filter(TO_NUMBER("E"."JOB_ID")<5)
That TO_NUMBER("E"."JOB_ID") is the tell. When you read a plan for generated SQL, the predicate section is a good first place to look, before the operations.
A function wrapped around a column name that you did not write is a conversion. On an indexed column it is a reason to check, in the Operation column of the same plan, whether the index you expected is still there.
How to check it before you explain anything
Before you explain anything, read the column types the statement touches:
-- The declared type of every column the generated statement filters on
-- (DBA_TAB_COLUMNS needs DBA_ view access; ALL_TAB_COLUMNS has the same columns)
SELECT column_name, data_type, data_length, data_precision, nullable
FROM dba_tab_columns
WHERE owner = 'APP'
AND table_name = 'ACCOUNTS'
AND column_name IN ('ACCOUNT_NO','CUSTOMER_ID','STATUS_CODE')
ORDER BY column_name;
Oracle's Database Reference says DBA_TAB_COLUMNS "describes the columns of all tables, views, and clusters in the database", while ALL_TAB_COLUMNS covers those "accessible to the current user". OWNER and TABLE_NAME name the table, view, or cluster; DATA_TYPE is the "Data type of the column"; DATA_LENGTH is the "Length of the column (in bytes)"; and DATA_PRECISION is the "Decimal precision for NUMBER data type; binary precision for FLOAT data type; NULL for all other data types".
That one query helps with check two and check three at the same time, because it also returns NULLABLE. Read that column precisely: it "Indicates whether a column allows NULLs. The value is N if there is a NOT NULL constraint on the column or if the column is part of a PRIMARY KEY. The constraint should be in an ENABLE VALIDATE state."
So N is your evidence that nulls are not allowed. A Y means the dictionary records no such constraint for that column; it does not tell you whether any row holds a null today.
Dates deserve their own sentence. A generated statement that compares a DATE column with a string literal leaves the conversion to the session's globalization settings such as NLS_DATE_FORMAT, and Oracle warns that implicit datetime conversion "may return an unexpected year depending on the value of the NLS_DATE_FORMAT parameter". Write the conversion explicitly, with a format mask or a DATE literal.

5. Check Three: NOT IN Against a Column That Allows Nulls
This is the check that justifies the whole review, because it changes the answer rather than the speed, and it reports no error at all.
Oracle's documentation on the IN condition describes NOT IN as "Equivalent to !=ALL. Evaluates to FALSE if any member of the set is NULL." The manual then demonstrates it. This statement returns the string for each row:
SELECT 'True' FROM employees
WHERE department_id NOT IN (10, 20);
and this one returns no rows at all:
SELECT 'True' FROM employees
WHERE department_id NOT IN (10, 20, NULL);
Oracle explains why: the WHERE clause becomes department_id != 10 AND department_id != 20 AND department_id != null, the third comparison results in UNKNOWN, and so the whole expression is FALSE. The manual adds the line that matters most to a reviewer: "This behavior can easily be overlooked, especially when the NOT IN operator references a subquery."
That is a shape a model can produce when you ask for customers without orders:
-- If ORDERS.CUSTOMER_ID allows nulls and one row is null,
-- this returns ZERO rows and raises nothing.
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE c.customer_id NOT IN (SELECT o.customer_id FROM orders o);
Before rewriting it, decide what the question means. "Customers without orders" usually means customers for whom no order row has a matching CUSTOMER_ID. NOT EXISTS says exactly that, because Oracle defines EXISTS as "TRUE if a subquery returns at least one row":
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id);
This is a different question, not an automatic equivalent of the NOT IN version, and the difference is in the nulls. An order row with a null CUSTOMER_ID matches nobody, so it no longer empties the result. And a customer whose own CUSTOMER_ID is null has no matching order row either, so NOT EXISTS returns that customer, while NOT IN would not.
If both columns are declared NOT NULL (NULLABLE = 'N' above), the two forms give the same rows. If either can be null, choose the meaning on purpose and write that one down next to the query.
There is a second, related trap in the same manual page: "if a NOT IN condition references a subquery that returns no rows at all, then all rows will be returned." Empty subquery, everything matches. Both behaviours are correct three-valued logic: the statement can be right about the standard and wrong about your table.
While you are there, remember the general rule from Oracle's description of nulls: "To test for nulls, use only the comparison conditions IS NULL and IS NOT NULL. If you use any other condition with nulls and the result depends on the value of the null, then the result is UNKNOWN." So a generated predicate of the form column != 'X' leaves out, without a word, the rows where that column is null.
Sometimes that is what the business wants. It should be a decision, not an accident.
6. Check Four: Hints the Model Borrowed from Somewhere Else
A model may add hints to a statement. A hint copied from an example was written for somebody else's data and possibly an older release, so it is a promise about data that may have changed since.
Oracle's position is not subtle. The SQL Tuning Guide says: "Oracle provides several tools, including SQL Tuning Advisor, SQL plan management, and SQL Performance Analyzer, to address performance problems not solved by the optimizer. Oracle strongly recommends that you use these tools instead of hints because they provide fresh solutions as the data and database environment change."
The second line is the one that makes borrowed hints so hard to find: "The database does not issue error messages for hints that it ignores." A hint lives inside a comment (Oracle: "A hint is embedded within a SQL comment"), so a misspelled hint, a hint naming an index that was dropped, or a hint referencing an alias that no longer exists can stay in your code as decoration without the behaviour.
Make the database tell you
In 19c, the hint usage report in DBMS_XPLAN answers the question for you. Oracle's SQL Tuning Guide says "Hint tracking is enabled by default", that the DISPLAY family of functions (including DISPLAY_CURSOR) generates the report "when you specify the value HINT_REPORT in the format parameter", and then draws the line that matters for a review: "The value TYPICAL displays only the hints that are not used in the final plan, whereas the value ALL displays both used and unused hints."
Read that carefully, because it decides how you read the output. A hint that appears in a TYPICAL report is an unused one. A hint that does not appear in a TYPICAL report tells you nothing by its absence: it may well have been used.
The format to use in a review: ALL
To see every hint with its status, ask for the ALL format. This is Oracle's own tutorial query and the hint section of its sample output:
EXPLAIN PLAN FOR
SELECT /*+ INDEX(e emp_emp_id_pk) */ COUNT(*)
FROM employees e
WHERE e.employee_id = 5;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));
Hint Report (identified by operation id/Query Block Name/Object Alias)
Total hints for statement: 1
----------------------------------------------------------------------
2 - SEL$1 / E@SEL$1
- INDEX(e emp_emp_id_pk)
The hint is listed with no U in front of it, and the header counts no unused hints. The plan above that section in Oracle's sample shows the INDEX UNIQUE SCAN on EMP_EMP_ID_PK that the hint asked for.
What an unused hint looks like
Oracle's next example uses TYPICAL, which "shows only unused hints":
EXPLAIN PLAN FOR
SELECT /*+ INDEX_RS(e emp_manager_ix) */ COUNT(*)
FROM employees e
WHERE e.job_id < 5;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'TYPICAL'));
Oracle's own output for that example ends with:
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
2 - SEL$1 / E@SEL$1
U - INDEX_RS(e emp_manager_ix)
The U marks the hint as unused; Oracle's text says so directly: "The U in the preceding hint usage report indicates that the INDEX_RS hint was not used." The manual notes that for many unused hints the report also explains why, giving reasons such as "hint overridden by another in parent query block".
Run the ALL report before you accept any hint a model offers, and run it again on the hints already sitting in your application. For a statement that has already run, the same report is available from DISPLAY_CURSOR.
One warning, because the two formats are easy to confuse. Never delete a hint just because it is missing from a TYPICAL report: TYPICAL lists only unused hints, so a hint that is not there may be one the optimizer used.
A good rule: a hint stays only if it has a comment next to it naming the person who proved it, the date, and the plan it replaced. Any other hint is a candidate to remove, after you have compared the plan with and without it, and the fix goes through the documented route โ statistics first, then SQL plan management. The Oracle performance tuning guide on this site works through that route in detail.
7. Check Five: SELECT *, DISTINCT and Functions on Indexed Columns
Three smaller habits, grouped because they share a cause: a statement can look complete without being narrow.
SELECT * in application code
A model may write SELECT * because it does not know which columns you want. In application code that can send columns nobody reads, and code that depends on the number or order of columns can break when a column is added.
It can also take away a plan that reads only an index. Oracle's SQL Tuning Guide says the optimizer considers an index fast full scan "when a query only accesses attributes in the index", and that table access by rowid "might not occur" when "the index contains all needed columns".
A star asks for every column of the table. Unless an index (or a join of indexes) happens to hold all of them, that index-only option is gone. The review replaces the star with the column list the code actually reads.
DISTINCT used as a bandage
When a generated join produces duplicates, a model may add DISTINCT to make them disappear. That can hide a join condition that is wrong or incomplete, and removing duplicates can add work of its own.
Before accepting a DISTINCT, take it out and look at the duplicates. If whole rows repeat exactly, that is a sign worth checking: a join condition may be missing, or the query may join a table the question did not need.
If the duplicates differ in one column, the question itself may be ambiguous, and the business needs to answer it. Either way, keep the DISTINCT only when you can say why the duplicates are legitimate.
A function wrapped around an indexed column
A predicate like WHERE UPPER(last_name) = 'KHAN' or WHERE TRUNC(created_date) = TRUNC(SYSDATE) asks about an expression, not the column. Oracle's SQL Tuning Guide lists "The query predicate applies a function to the indexed column" among its typical reasons for a full table scan, and explains: "Unless the index is a function-based index, the database indexes the values of the column, not the values of the column with the function applied."
Either rewrite the predicate as a range, as in created_date >= TRUNC(SYSDATE) AND created_date < TRUNC(SYSDATE) + 1, or create a function-based index deliberately, as a reviewed change with its own storage and maintenance cost. The one thing not to do is leave the predicate and hope.
To see whether a function-based index already exists, query the dictionary. DBA_IND_EXPRESSIONS "lists expressions of function-based indexes on all tables and clusters in the database", and its COLUMN_EXPRESSION column is the "Function-based index expression defining the column". Without DBA_ view access, ALL_IND_EXPRESSIONS describes them "on tables accessible to the current user".
For plain indexes, DBA_IND_COLUMNS "describes the columns of all the indexes on all tables and clusters in the database" (ALL_IND_COLUMNS: "indexes on all tables accessible to the current user"). That is where you confirm which index the predicate was supposed to use.
8. Check Six: The Plan You Explained Is Not the Plan That Ran
EXPLAIN PLAN is an estimate made in your session. The plan that ran is a fact recorded in the cursor. Review generated SQL against the fact.
Oracle's SQL Tuning Guide sets the boundary clearly: "EXPLAIN PLAN output shows how the database would have run the SQL statement when the statement was explained. Because of differences in the execution environment and explain plan environment, the explained plan can differ from the actual plan used during statement execution."
It is more specific about binds, which matters because fixing check one turns literals into binds: "With bind variables in general, the EXPLAIN PLAN output might not represent the real execution plan." And one case is not supported at all: "Oracle Database does not support EXPLAIN PLAN for statements performing implicit type conversion of date bind variables" โ which is a neat closing of the loop with check two.
Getting the real plan with row counts
Run the statement, then ask the cursor what happened. The ingredient that is easy to miss is that the plan statistics have to be collected in the first place.
The DBMS_XPLAN documentation states the condition: the IOSTATS family works "assuming that basic plan statistics are collected when SQL statements are executed (either by using the gather_plan_statistics hint or by setting the parameter statistics_level to ALL)". It also defines the two format keywords: "ALLSTATS - A shortcut for 'IOSTATS MEMSTATS'", and LAST to see only the statistics for the last execution.
-- 1. Run the candidate statement with plan statistics collected
SELECT /*+ gather_plan_statistics */ c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id);
-- 2. Ask the cursor what it actually did (NULL, NULL = the last statement
-- this session ran). Needs SELECT/READ on V$SQL_PLAN, V$SESSION,
-- V$SQL_PLAN_STATISTICS_ALL and V$SQL.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
Oracle's own example uses the same format with an explicit SQL_ID, DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp', NULL, 'ALLSTATS LAST'), which is how you examine a statement somebody else ran.
Note the privileges, because they decide where this review can happen. The documentation says the package "runs with the privileges of the calling user, not the package owner (SYS)", and that "The table function DISPLAY_CURSOR requires SELECT or READ privileges on the following fixed views: V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL. This function also requires SELECT/READ permissions on V$SQL." A developer who cannot select those views cannot do this check, which is an argument for a small, deliberately granted reviewer role rather than for handing out DBA.
What you are reading in the output
With ALLSTATS LAST, the plan gains E-Rows and A-Rows columns: the rows the optimizer estimated and the rows that actually appeared, per operation, plus buffer gets. The comparison between those two columns is the whole point of the exercise.
For example, an operation that estimated one row and produced four hundred thousand can explain much of what happens downstream of it. Possible causes include missing or stale statistics and a predicate the optimizer could not estimate well; the cause is not necessarily anything the model wrote.
My guide to AWR and ASH performance analysis picks the story up from there, once the statement is already in production.
9. Check Seven: Run the First Execution Somewhere It Cannot Hurt You
Everything above is reading. At some point the statement has to run, and the first execution of an unreviewed query is when a missing join condition shows what it costs. Put limits around it before you press enter.

Layer one: a profile on the account that runs it
A profile is the simplest guard to set up. Read Oracle's own note at the top of the CREATE PROFILE page first: "Oracle recommends that you use the Database Resource Manager rather than this SQL statement to establish resource limits." Where Resource Manager is not available (layer two explains when), the profile is the guard you have.
Oracle's reference defines the two per-call limits that matter here. CPU_PER_CALL specifies "the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds". LOGICAL_READS_PER_CALL specifies "the permitted number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch)".
Per-call limits and per-session limits behave differently
This is the part to get right, because the same profile can hold both kinds and Oracle treats them differently when they are hit. The CREATE PROFILE reference lists three cases:
- A per-call limit (
CPU_PER_CALL,LOGICAL_READS_PER_CALL): "the database aborts the operation, rolls back the current statement, and returns an error, leaving the current transaction intact." The session carries on. CONNECT_TIMEorIDLE_TIME: "the database rolls back the current transaction and ends the session. When the user process next issues a call, the database returns an error."- Any other session limit (for example
CPU_PER_SESSIONorLOGICAL_READS_PER_SESSION): "the database aborts the operation, rolls back the current statement, and immediately returns an error. The user can then commit or roll back the current transaction, and must then end the session."
For a single runaway statement, the per-call limits are the ones you want: they stop the statement and keep the session and its transaction. A session limit is a blunter tool, and CONNECT_TIME is the bluntest, because it takes uncommitted work with it.
-- A profile for a DEDICATED account used only to try out generated SQL.
-- Every number here is an illustration, not a recommendation.
CREATE PROFILE sql_review LIMIT
CPU_PER_CALL 3000 -- hundredths of a second: 3000 = 30 s of CPU per call
LOGICAL_READS_PER_CALL 1000000 -- data blocks read per call (parse, execute or fetch)
-- CONNECT_TIME is in MINUTES: a session may last at most 60 minutes,
-- then Oracle rolls back its open transaction and ends the session
CONNECT_TIME 60;
-- Assigns this profile to the account in place of the profile it had.
ALTER USER sql_reviewer PROFILE sql_review;
Note the unit change between the lines. CPU_PER_CALL is in hundredths of a second, but Oracle defines CONNECT_TIME as "the total elapsed time limit for a session, expressed in minutes". So CONNECT_TIME 60 is one hour of elapsed session time, and when it runs out the session is ended and its uncommitted transaction rolled back.
What ALTER USER ... PROFILE changes
A user is assigned one profile, and ALTER USER ... PROFILE replaces whatever profile the account had before. Oracle's own example states the timing: "In subsequent sessions, sh is restricted by limits in the new_profile profile." A session that is already connected is not the place to test it; connect again.
Everything the new profile does not name still applies, from the DEFAULT profile. Oracle's example for a profile that sets only some limits says: "Since the app_user profile omits a limit for IDLE_TIME and for password limits, the user is subject to the limits on these resources specified in the DEFAULT profile."
That includes the password settings, such as failed-login locking and password lifetime. Two practical consequences: use a dedicated review account rather than re-profiling a person's or an application's account, and look at the account's current profile before you change it:
-- Which profile the review account has now (DBA_USERS.PROFILE = "User resource profile name")
SELECT username, profile
FROM dba_users
WHERE username = 'SQL_REVIEWER';
-- Is enforcement of resource limits on? (SQL*Plus command, not SQL)
SHOW PARAMETER resource_limit
Both lines need privileges. DBA_USERS is a DBA_ view, with the access rule quoted in section 2.
For the second, the SQL*Plus User's Guide says: "You need SELECT ON V_$PARAMETER object privileges to use the PARAMETERS clause". SHOW PARAMETER takes a string and shows "a subset of parameters whose names include that string", so read the line named resource_limit.
The switch that makes resource limits count
Unlike password limits, resource limits are enforced only when resource limits are enabled: "Enable resource limits dynamically with the ALTER SYSTEM statement or with the initialization parameter RESOURCE_LIMIT. This parameter does not apply to password resources. Password resources are always enabled."
The same page adds: "You can specify resource limits for users regardless of whether the resource limits are enabled. However, Oracle Database does not enforce the limits until you enable them." In 19c the Database Reference lists the default value of RESOURCE_LIMIT as true, so the check is that nobody has set it to false.
Check it before you trust the profile; a profile that is not enforced is worse than no profile, because you believe in it. The numbers in the example are illustrations, close in shape to Oracle's own app_user example (which uses CPU_PER_CALL 3000 and CONNECT_TIME 45), not a recommendation for your system โ set them from what your own normal statements consume.
Layer two: Database Resource Manager, on Enterprise Edition
Above the profile sits Database Resource Manager, which can act on a call that is already running or refuse one before it starts. Two mechanisms are directly useful for a review session.
First, the switch group. Oracle documents three reserved consumer group names โ "The following consumer group names are reserved: CANCEL_SQL, KILL_SESSION, and LOG_ONLY" โ and explains the first: "If the group name is CANCEL_SQL, then the current call is canceled when switch criteria are met." Used with SWITCH_TIME, which "specifies the time (in CPU seconds) that a call can execute before an action is taken", that gives you a hard stop on a call that overruns.
Second, the pre-execution estimate. MAX_EST_EXEC_TIME "specifies the maximum execution time (in CPU seconds) allowed for a call. If the optimizer estimates that a call will take longer than MAX_EST_EXEC_TIME, the call is not allowed to proceed and ORA-07455 is issued." Note Oracle's own two caveats, which you should quote to anyone who wants to rely on this: "If the optimizer does not provide an estimate, this directive has no effect", and the estimate comes from the optimizer, so "the accuracy of the estimate is dependent on many factors, especially the quality of the optimizer statistics." A pre-execution guard built on estimates is exactly as good as your statistics are.
Before you plan around any of this: the Licensing Information User Manual's manageability table lists Database Resource Manager as not available in Standard Edition 2, and available in Enterprise Edition and EE-ES. If you are on SE2, layer one is your layer.
10. What the Licence Covers, Said Honestly
Every check in this article uses EXPLAIN PLAN, DBMS_XPLAN, the data dictionary and the V$ views. I have deliberately not built the review on a monitoring report, and the reason is a licensing line worth knowing by heart.
Oracle Tuning Pack is listed as an extra cost pack on EE and EE-ES, and its feature list includes "Real-time SQL and PL/SQL Monitoring". The manual then removes the usual escape route: "Any and all methods of accessing Oracle Tuning Pack functionality, whether through Enterprise Manager Console, Desktop Widgets, command-line APIs, or direct access to the underlying data, requires an Oracle Tuning Pack license." It also records a dependency: "Oracle Tuning Pack requires Oracle Diagnostics Pack."
Two practical consequences for this review. The Resource Manager LOG_ONLY switch group records "information about the session ... in real-time SQL monitoring", so reach for it knowing what it writes to.
And CONTROL_MANAGEMENT_PACK_ACCESS is the parameter that governs access to both packs in the database server, with NONE disabling both. I am not your licensing adviser, and this article is not a legal opinion: read the manual for your release and talk to your account team before you build a routine on a pack feature.
11. The Checklist
Keep this list beside you. It fits on one screen on purpose, because a checklist that needs scrolling is easier to skip.
| # | Check | How you prove it |
|---|---|---|
| 1 | Values are bound, not pasted in | Read the text; FORCE_MATCHING_SIGNATURE families in V$SQL |
| 2 | Every literal matches its column's declared type | DBA_TAB_COLUMNS; no TO_NUMBER( in the plan's predicates |
| 3 | No NOT IN over a nullable column | NULLABLE in DBA_TAB_COLUMNS (N = a validated NOT NULL or PRIMARY KEY); for "no matching row" write NOT EXISTS, choosing the null meaning on purpose |
| 4 | Every hint is used, and justified in a comment | The hint report in DBMS_XPLAN.DISPLAY(format => 'ALL') (used and unused hints); in TYPICAL a listed hint is an unused one, and a missing hint proves nothing |
| 5 | Column list instead of *; every DISTINCT explained | Read the text; take the DISTINCT out and look at the duplicates |
| 6 | No function around an indexed column in a predicate | Plan predicates; DBA_IND_COLUMNS and DBA_IND_EXPRESSIONS |
| 7 | The real plan was read, not the explained one | DISPLAY_CURSOR with ALLSTATS LAST; compare E-Rows with A-Rows |
| 8 | The first execution ran under a limit | A dedicated account with a per-call profile (CPU_PER_CALL, LOGICAL_READS_PER_CALL), RESOURCE_LIMIT true; Resource Manager on EE |
Two more lines that are not technical and matter as much. Which account runs it: generated DDL and DML should never arrive through an account that holds DBA or SELECT ANY TABLE, because a mistaken table name in a privileged session can find a real table somewhere.
What leaves the building: if a developer pastes a table definition into a hosted model to get help, your schema has left your organisation. That is a policy question, not a SQL question, and I write about where to draw that line in my note on the AI skills a DBA actually needs.

12. What to Let a Model Write, and What to Write Yourself
None of this is an argument against using assistants. They can make parts of the work faster.
The useful division is about blast radius, not about capability.
Let a model draft freely: reporting queries over a reviewed view, the boilerplate of a PL/SQL package, test data generators, regular expressions, the second and third variants of a query you already understand, and explanations of someone else's SQL that you then verify. In all of those the cost of being wrong is a wrong answer you will notice.
Write by hand, or review line by line before anyone runs it: anything with DELETE, UPDATE or MERGE in it; anything that creates or drops an object; anything that runs inside a maintenance window where nobody is watching; anything touching your largest tables; and every statement that will be executed thousands of times a day, because there the difference between a good plan and a tolerable one is the difference between a quiet afternoon and a queue of phone calls.
The last point is the honest summary of the whole article. A model gives you a plausible statement in seconds.
Your database gives you the facts, and it gives them readily โ one dictionary query for the types and nullability, one EXPLAIN PLAN for the shape, one DISPLAY_CURSOR for the truth. Reading those three answers is what turns a generated draft into production SQL, and it is some of the cheapest time in the whole development cycle.
Frequently Asked Questions
Is AI-generated SQL safe to run on an Oracle production database?
Treat it exactly like SQL from a developer you have never worked with: read it, get a real execution plan for it, and let the first execution run under an account whose profile limits CPU_PER_CALL and LOGICAL_READS_PER_CALL. A model cannot see your row counts, your indexes, which columns allow nulls or which edition you licensed, so a statement can be valid SQL and still be wrong for your database.
Why might AI-generated SQL not use my index?
One possible reason is an implicit data type conversion, for example a number literal compared with a character column. Oracle's SQL Language Reference says that if implicit data type conversion occurs in an index expression, the database might not use the index because it is defined for the pre-conversion data type. The predicate section of the plan shows it as TO_NUMBER("COLUMN") rather than the column name, and the Operation column shows whether the index was used.
What is wrong with NOT IN in generated SQL?
Oracle's documentation says NOT IN is equivalent to !=ALL and evaluates to FALSE if any member of the set is NULL. So a NOT IN subquery against a column that allows nulls can return no rows at all, with no error and no warning. NOT EXISTS does not behave that way, but it is not an automatic equivalent when either column can be null: use it when you mean "rows with no matching row", and choose the null meaning on purpose.
Is EXPLAIN PLAN enough to review a generated query?
No. Oracle's SQL Tuning Guide states that because of differences in the execution environment and explain plan environment, the explained plan can differ from the actual plan used during statement execution, and that with bind variables the EXPLAIN PLAN output might not represent the real execution plan. Use EXPLAIN PLAN as a cheap first read, then run the statement and get the real plan with DBMS_XPLAN.DISPLAY_CURSOR.
How do I see the plan that actually ran, with real row counts?
Collect basic plan statistics, either with the gather_plan_statistics hint or by setting STATISTICS_LEVEL to ALL, then call DBMS_XPLAN.DISPLAY_CURSOR with the format ALLSTATS LAST. ALLSTATS is a shortcut for IOSTATS MEMSTATS, and LAST restricts the output to the last execution. The caller needs SELECT or READ on V$SQL_PLAN, V$SESSION, V$SQL_PLAN_STATISTICS_ALL and V$SQL.
Should I keep the hints a model puts in my SQL?
Not without checking them. Oracle strongly recommends using SQL Tuning Advisor, SQL plan management and SQL Performance Analyzer instead of hints, because those provide fresh solutions as the data and database environment change.
Oracle also notes that the database does not issue error messages for hints that it ignores, so a borrowed hint can sit in your code for years doing nothing. The 19c hint report with the ALL format shows both used and unused hints; the TYPICAL format shows only the unused ones, so a hint missing from a TYPICAL report may have been used.
How can I stop a generated query from running away on first execution?
Two layers. On a dedicated account, a profile with the per-call limits CPU_PER_CALL and LOGICAL_READS_PER_CALL aborts the call and rolls back the current statement while leaving the transaction intact; resource limits are enforced only while RESOURCE_LIMIT is true. Session limits behave differently: CONNECT_TIME, in minutes, rolls back the current transaction and ends the session.
Above that, Database Resource Manager can switch a session to the reserved CANCEL_SQL group or refuse a call whose estimate exceeds MAX_EST_EXEC_TIME with ORA-07455. Resource Manager is listed as not available in Standard Edition 2.
Does reviewing generated SQL need a Tuning Pack licence?
The checks in this article are built on EXPLAIN PLAN, DBMS_XPLAN, the data dictionary and V$ views. Oracle's Licensing Information User Manual lists Real-time SQL and PL/SQL Monitoring among Oracle Tuning Pack features, states that any and all methods of accessing Tuning Pack functionality require a Tuning Pack licence, and that Tuning Pack requires Diagnostics Pack. Check CONTROL_MANAGEMENT_PACK_ACCESS and your own contract before building a habit on a monitoring report.
๐ง Who reviews the SQL your team is generating?
If your developers have started drafting queries with an assistant and nobody is reading the plans, the first symptom can be a gradual slowdown rather than an outage. I review the statements, the plans and the guard rails around them, and leave your team with the checklist.
๐ฉ Book an Oracle ReviewReferences & Further Reading
- ๐ Oracle Database 19c SQL Language Reference - Data Type Comparison Rules (implicit conversion and indexes)
- ๐ Oracle Database 19c SQL Language Reference - IN Condition (NOT IN and nulls)
- ๐ Oracle Database 19c SQL Tuning Guide - Influencing the Optimizer (hints and the hint report)
- ๐ Oracle Database 19c SQL Tuning Guide - Generating and Displaying Execution Plans (EXPLAIN PLAN restrictions)
- ๐ Oracle Database 19c PL/SQL Packages Reference - DBMS_XPLAN (DISPLAY_CURSOR, ALLSTATS LAST, privileges)
- ๐ Oracle Database 19c SQL Language Reference - CREATE PROFILE (CPU_PER_CALL, LOGICAL_READS_PER_CALL, CONNECT_TIME, RESOURCE_LIMIT, DEFAULT profile)
- ๐ Oracle Database 19c Administrator's Guide - Managing Resources with Database Resource Manager (CANCEL_SQL, MAX_EST_EXEC_TIME)
- ๐ Oracle Database 19c Licensing Information User Manual - Tuning Pack and Database Resource Manager availability
- ๐ Oracle Database 19c SQL Tuning Guide - Optimizer Access Paths (index fast full scan, function on an indexed column)
- ๐ Oracle Database 19c SQL Language Reference - ALTER USER (assigning a profile)
- ๐ Oracle Database 19c Reference - RESOURCE_LIMIT (default value)
- ๐ Oracle Database 19c Reference - V$SQL (SQL_ID, FORCE_MATCHING_SIGNATURE)
- ๐ Oracle Database 19c Reference - DBA_IND_EXPRESSIONS (function-based index expressions)
- ๐ Oracle Database 19c Reference - About Static Data Dictionary Views (who can query DBA_ views)
- ๐ SQL*Plus User's Guide and Reference 19c - SHOW (SHOW PARAMETER, V_$PARAMETER privilege)
All Oracle quotations were read on 5 and 14 November 2026 from the Oracle Database 19c documentation. Plan output shown is Oracle's own documented example output, not a measurement from a customer system. Profile and Resource Manager values in the examples are illustrative starting points, not recommendations: confirm every limit, privilege and licence against the documentation for your exact release and edition before production use.
