Oracle Database performance: statistics, plans and bind variables

A slow query needs a verifiable explanation before indexes or parameters are changed. When a team changes several elements at once, it may get an improvement but loses the ability to know which change produced it and to correct a later regression.

This article describes a five-stage diagnostic method (scoping, statistics, observed plan, bind variables and decision), using the Oracle Database 19c documentation as reference. Before applying it, record the installed version, patch level and licensed options, because every recommendation must match the real environment.

Scoping the query and its context

The analysis starts with the affected business operation: an order search, an accounting close, an inventory lookup. Record when the delay occurs, which parameters the application receives and whether the user needs all results or only the first page. That distinction defines which execution must be reproduced.

The minimum evidence includes the exact SQL, its identifier (SQL_ID), the child cursor under study, the time observed by the application, the volume returned and the concurrency at that moment, without sensitive data. A capture in an empty environment is useful to review the query but does not show its behavior during an operations close.

The comparison needs values representative of the real distribution. If orders per customer are being investigated, customers with little and with heavy activity must both be included. The expected functional result is defined before tuning, so that a faster query that omits records is not accepted as a solution.

Optimizer statistics

The optimizer uses statistics to estimate cardinalities and costs when choosing a plan. Statistics describe table sizes and the distribution of column values; they are not a measurement of the running query. A histogram, for example, represents a skewed distribution that an average cannot capture, according to the optimizer statistics concepts.

The first check is whether automatic collection is enabled and whether its maintenance windows match the load pattern. When a load requires statistics before the next window, DBMS_STATS complements the automatic mechanism. The statistics gathering guide recommends AUTO_SAMPLE_SIZE as the default, letting the database determine the sample.

For a table in one's own schema, the query below shows the last analysis date and the stale statistics flag. The name must be written as stored in the data dictionary; the view returns rows per table, partition and subpartition depending on the object.

SELECT table_name, partition_name, subpartition_name,
       num_rows, last_analyzed, stale_stats
FROM user_tab_statistics
WHERE table_name = 'ORDERS';

The USER_TAB_STATISTICS reference documents this view. NUM_ROWS comes from statistics and does not replace a current count. The STALE_STATS flag is interpreted together with recent loads and with the symptom being explained.

If statistics are to be gathered, the object is delimited, a reference of prior behavior is kept and current preferences are documented. The effect is tested before extending it to a whole schema. Indiscriminate collection during an incident makes it impossible to separate the necessary fix from secondary changes.

The plan of the cursor actually executed

EXPLAIN PLAN shows the plan Oracle would generate for a statement, but it does not prove which plan a previous application execution used: context and values may differ. To study the observed execution, the loaded cursor is queried with DBMS_XPLAN.DISPLAY_CURSOR.

The following example uses the HR sample schema and SQL*Plus commands, so that schema must exist and be accessible. The gather_plan_statistics hint requests runtime statistics; the block should run in a dedicated session and the plan should be queried immediately afterwards, with no intervening statements.

VARIABLE department NUMBER
EXEC :department := 50;

SELECT /*+ gather_plan_statistics */ employee_id, last_name
FROM hr.employees
WHERE department_id = :department;

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

DISPLAY_CURSOR requires read privileges on the fixed views Oracle documents. The ALLSTATS LAST format shows statistics for the last execution if they were collected; if they appear empty, the capture must be verified before drawing conclusions. In a concurrent application, the SQL_ID and child cursor are identified explicitly, so another session's execution is not attributed to the test.

The reading compares, for each plan operation, estimated rows with observed rows and with the number of times the operation started. A relevant discrepancy points to where the applied predicates should be reviewed. The full output is kept so another team member can evaluate the hypothesis, rather than a partial screenshot of the most expensive operator.

Bind variables and value distribution

Bind variables keep a statement's text constant while its values change, which allows cursor sharing and avoids unnecessary parsing. The application must bind them with appropriate types and execute the SQL consistently. Replacing each value with a different literal multiplies statement texts and hides the reuse problem.

During a hard parse, Oracle can examine a variable's value to estimate cardinality; the mechanism is called bind peeking. With a skewed distribution, a plan chosen for one value may be unsuitable for others. Oracle also documents adaptive cursor sharing, which allows several plans for the same statement with bind variables.

A testing criterion follows: it cannot be assumed that a single plan serves every customer or period. Tests include frequent and infrequent values, keep their execution order and record the cursor used in each case. Every result is compared with the business requirement before accepting a change.

From diagnosis to decision

Every intervention responds to an explicit hypothesis: statistics that do not represent a recent load, a wrong cardinality estimate or an access pattern returning more records than necessary. One element is changed at a time and tests are repeated with the same values. Acceptance includes correct results and behavior under the real load.

Additional tools require verifying entitlement first. AWR and ASH are part of the Oracle Diagnostics Pack, with conditions that vary by edition and contracted service; an enabled parameter does not prove a license exists. The official 19c licensing information is the reference.

• Affected operation, SQL and values that reproduce the delay identified.
• Object statistics and recent loads reviewed before gathering again.
• Plan of the observed cursor obtained, with runtime statistics confirmed.
• Proposal tested with values from different parts of the distribution.
• Change documented with its acceptance evidence and rollback procedure.

Xterra Engine applies this method in Oracle Database performance reviews, with each hypothesis and its evidence documented.