Mastering Oracle SQLT: Fast Diagnostics, Plan Binding, and XPLORE Parameter Tuning

1. Overview of SQLT

SQLTXPLAIN (SQLT) is an Oracle COE‑provided performance‑diagnostic utility that creates a set of diagnostic files for a given SQL statement, helping to investigate poorly performing or wrong‑result queries.

The tool produces execution plans, statistics, CBO parameters, 10053 trace files, and historical performance data, and also offers utilities such as rapid plan binding.

2. SQLT Method Family

SQLT implements seven primary methods: XTRACT, XECUTE, XTRXEC, XTRSBY, XPLAIN, XPREXT, and XPREXC. All except XPLAIN support bind‑peeking; XPLAIN runs via EXPLAIN PLAN FOR and does not.

Guidelines:

Prefer XTRACT or XTRXEC when the SQL is still in memory or present in AWR.

Use XECUTE for most other cases.

For Data Guard or read‑only standby, choose XTRSBY.

Reserve XPLAIN for last‑resort scenarios.

These methods generate files that are automatically packaged for analysis.

3. Generating Diagnostic Files

Place the target SQL in sqlt/run and run the appropriate method. Example SQL:

select *
from test1
where test1.status in (select test2.status from test2
where object_name like 'PRC_TEST%');

The query returns an INVALID status count, indicating a skewed distribution that should drive an index‑nested‑loops plan.

Running @sqltxtrxec prompts for the SQL_ID and the SQLT password, then creates the diagnostic bundle.

dingjun123@ORADB> @sqltxtrxec
Enter value for 1: aak402j1r6zy3
Enter value for 2: XXXXXX

After extraction, the main file contains the execution plan, CBO settings, statistics, and history, allowing quick identification of issues such as stale statistics.

4. Fast Plan Binding with SQL PROFILE

SQL PROFILE adds optimizer hints without rewriting the SQL. Two binding approaches are provided:

Direct binding : Use when a good historic plan exists but the current plan is poor.

Replace binding : Use when no good plan exists; create a hinted SQL that yields the desired plan and replace the original.

Direct binding example using coe_xfr_sql_profile.sql:

SQL> @coe_xfr_sql_profile.sql
Enter value for 1: 0hzkb6xf08jhw
PLAN_HASH_VALUE: 3071332600   -- efficient plan

Replace binding example using coe_load_sql_profile with a hinted SQL (see section 5) and the original/modified SQL_IDs:

dingjun123@ORADB> @coe_load_sql_profile
Enter value for 1: aak402j1r6zy3
Enter value for 2: 6rbnw92d7djwk
Enter value for 3: 313848035

After binding, the query executes with an index scan and nested loops, confirming the plan improvement.

5. Hinted SQL for Replace Binding

select/*+
BEGIN_OUTLINE_DATA
USE_NL(@"SEL$5DA710D3" "TEST1"@"SEL$1")
LEADING(@"SEL$5DA710D3" "TEST2"@"SEL$2" "TEST1"@"SEL$1")
INDEX_RS_ASC(@"SEL$5DA710D3" "TEST2"@"SEL$2" ("TEST2"."OBJECT_NAME"))
INDEX_RS_ASC(@"SEL$5DA710D3" "TEST1"@"SEL$1" ("TEST1"."STATUS"))
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
UNNEST(@"SEL$2")
OUTLINE_LEAF(@"SEL$5DA710D3")
ALL_ROWS
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
* from test1
where test1.status in (select test2.status from test2
where object_name like 'PRC_TEST%');

6. Using XPLORE for Parameter/BUG Diagnosis

When a query runs extremely slowly after migration (e.g., one‑hour runtime versus ~1 s), and standard checks (statistics, SQL PROFILE, optimizer parameters) show no issues, XPLORE can iterate through known parameters and fix controls to locate the root cause.

XPLORE runs methods such as XEXCUTE and XPLAIN. The generated HTML report lists multiple plan hashes; inspecting them revealed that the _optimizer_squ_bottomup parameter being set to FALSE prevented null‑aware transformation, causing a HASH JOIN and full table scan.

Resetting the parameter restored normal execution time.

7. Summary

SQLT offers a comprehensive toolbox for Oracle SQL performance troubleshooting: generating detailed diagnostics, quickly binding optimal execution plans via SQL PROFILE, and employing the XPLORE feature to automatically test parameter and bug combinations. Mastering these capabilities enables rapid resolution of complex SQL performance problems.