At a glance
| Supported databases | Oracle Database (Enterprise Edition with packs; included in some cloud tiers and Free) |
| Query analysis | Deep per-statement analysis by the Automatic Tuning Optimizer |
| Execution plans | Alternative plan search across cursor cache, AWR and SQL tuning sets |
| Wait statistics | Not part of the advisor (ADDM and AWR, in the Diagnostics Pack) |
| Index recommendations | Yes, or a recommendation to run SQL Access Advisor |
| Slow-query detection | Works on high-load SQL identified by ADDM and AWR |
| Tuning automation | Nightly automatic task; can accept SQL profiles automatically |
| Deployment | Built into the database; managed in Enterprise Manager or PL/SQL |
| Licensing | Oracle Tuning Pack plus Diagnostics Pack on Enterprise Edition |
If you already license the Diagnostics and Tuning Packs on Enterprise Edition, SQL Tuning Advisor is the first tool to point at a problem statement: it can verify optimiser estimates, propose a SQL profile and accept it for you. If you do not, every way of calling it needs those packs, so confirm your licences before running DBMS_SQLTUNE.
Overview
SQL Tuning Advisor runs the optimiser in a special tuning mode, the Automatic Tuning Optimizer, against high-load statements. It performs statistical analysis (missing or stale statistics), SQL profiling (checking cardinality and cost estimates by sampling or partial execution), access path analysis (new indexes, or a pointer to SQL Access Advisor), structural analysis of the SQL (such as NOT IN against NOT EXISTS or implicit conversions) and alternative plan analysis across the cursor cache, AWR and SQL tuning sets. Each recommendation comes with a rationale and an estimated benefit.
You can run it on demand through Enterprise Manager Cloud Control or the DBMS_SQLTUNE package, or let the automatic task SYS_AUTO_SQL_TUNING_TASK run in the nightly maintenance window, by default for up to an hour, on high-load SQL from AWR. The automatic task can implement SQL profiles itself (ACCEPT_SQL_PROFILES), but never creates indexes, gathers statistics or creates plan baselines. Licensing requires Enterprise Edition with the Diagnostics and Tuning Packs, or an Oracle cloud service tier that includes them.
Key features
- Statistics, SQL profiling, access path, SQL structure and alternative plan analyses per statement
- Recommendations for SQL profiles, indexes, statistics gathering, restructured SQL and SQL plan baselines, with estimated benefit
- LIMITED or COMPREHENSIVE scope (COMPREHENSIVE adds SQL profiling)
- Input from ADDM findings, AWR, the cursor cache or SQL tuning sets
- Automatic SQL Tuning task in the maintenance window, with optional automatic acceptance of SQL profiles
- Interfaces through Enterprise Manager Cloud Control, DBMS_SQLTUNE and DBMS_AUTO_SQLTUNE
- Remote tuning of an Active Data Guard standby's workload through the primary
Integrations and ecosystem
Pros and cons
Pros
- Built into the database with direct access to optimiser internals
- SQL profiles fix plans without changing application code
- Automatic nightly tuning task for high-load SQL
- Recommendations include a rationale and estimated benefit
Cons
- Requires Enterprise Edition plus the Diagnostics and Tuning Packs, all paid on premises
- Not available on Standard Edition 2
- Restructured SQL suggestions may not be strictly equivalent, so each needs review
- Oracle only; no cross-database view
Pricing
SQL Tuning Advisor is part of the Oracle Tuning Pack. Oracle's licensing manual lists the Tuning Pack as an extra-cost pack for Enterprise Edition, requiring the Oracle Diagnostics Pack as well; it is not available for Standard Edition 2 or Personal Edition. It is included in the Enterprise Edition tiers of Base Database Service and in Exadata Database Service, and the 26ai manual also lists it as included in Oracle AI Database Free. Any use, including through DBMS_SQLTUNE or the underlying views, needs the licence. Pack list prices are on Oracle's price list, which was not checked for this profile.
Pricing checked on the vendor's official page on 7 November 2026. Prices change; confirm before buying.
Who it is best for
Oracle DBAs on Enterprise Edition (or a cloud tier that includes the packs) who need to tune specific high-load statements and want SQL profiles applied without code changes.
Typical use cases
- Tuning a high-load statement flagged by ADDM
- Accepting a SQL profile for a packaged application query you cannot rewrite
- Running the nightly automatic task to catch regressions in repeated SQL
- Evaluating a SQL tuning set before an upgrade
Who should look elsewhere
- Standard Edition 2 shops, where the Tuning Pack is not available: see Oracle database tools.
- Teams that want one monitor across Oracle and other engines: see database monitoring tools.
Verdict
SQL Tuning Advisor gives Oracle DBAs statement-level tuning advice with an unusual depth of access to the optimiser, and SQL profiles that fix plans without code changes. Its value depends on licensing: it needs Enterprise Edition with both the Diagnostics and Tuning Packs, so many teams on Standard Edition 2 rely on third-party tools instead.
Frequently asked questions
Is SQL Tuning Advisor free?
Not on standard on-premises licences. It is part of the Oracle Tuning Pack, an extra-cost pack for Enterprise Edition that also requires the Diagnostics Pack. It is included in the Enterprise Edition tiers of Base Database Service, in Exadata Database Service and, per the 26ai licensing manual, in Oracle AI Database Free.
Can I use it on Standard Edition 2?
No. Oracle's licensing manual lists the Tuning Pack as not available on Standard Edition 2 or Base Database Service Standard Edition.
Does the automatic task change my database?
Only by accepting SQL profiles, and only when ACCEPT_SQL_PROFILES allows it (the default AUTO setting behaves as TRUE only if a statement already has a profile). It never creates indexes, gathers statistics or creates plan baselines.
How do I stop accidental use without a licence?
Set the CONTROL_MANAGEMENT_PACK_ACCESS initialisation parameter to DIAGNOSTIC or NONE; DIAGNOSTIC+TUNING enables both packs.
Sources
- Analyzing SQL with SQL Tuning Advisor (Oracle SQL Tuning Guide, 26ai)
- Oracle Database Licensing Information User Manual (19c)
- Oracle AI Database Licensing Information User Manual (26ai)
Checked November 2026.
Listed in: Performance & Query Optimization.
How we research reviews and what star ratings mean: our editorial method.