How to Slash SQL Tuning Time: One‑Click Database Insights & Scripts

Why SQL Tuning Takes Too Long

Many DBAs spend an hour or more just to locate the root cause of a slow SQL, often extending to a full day because they chase symptoms instead of the overall picture. The article uses a fictional "Xiao Wang" scenario to illustrate wasted time caused by excessive back‑and‑forth communication and lack of holistic diagnostics.

Common Pitfalls

Frequent interactive queries – repeatedly asking for index, execution plan, table stats, etc., consumes valuable time.

Missing the main contradiction – tackling local symptoms without understanding whether the issue is systemic or isolated.

Unclear objectives – optimizing SQLs that the customer does not actually care about.

Ignoring operation difficulty – not assessing whether a SQL has high optimization potential before spending effort.

One‑Click Collection of Database‑wide Information

Gathering a complete view of the database is the first step. The following scripts automate the extraction of AWR, ADDM, ASH and other performance reports. sqlplus "/ as sysdba" @d:\mkdb.sql – prepares the test environment. sqlplus "/ as sysdba" @spooldb.sql – generates five key files: recent 1‑hour ADDM, recent 30‑minute ASH, 1‑hour and 7‑day AWR, and a comprehensive spool* file containing version, parameters, host info, abnormal tables/indexes, log switches, sequences, triggers, foreign keys, etc.

These files give a clear picture of resource consumption, partitioning issues, parallelism settings, and index health.

Getting Detailed SQL Information

After the database overview, focus on the problematic SQLs. sqlplus "/ as sysdba" @d:\mksql.sql – creates a workload with deliberately slow statements.

Collect execution plans, table and index statistics, and runtime metrics (logical reads, physical reads, sorts, recursive calls, etc.).

The collected data enables rapid identification of missing or invalid indexes, partitioned‑table anomalies, and other bottlenecks without further interaction with the original requester.

Practical Example

The article walks through a real‑world case where the one‑click scripts reveal that a SQL is scanning a large table because an index is either missing or disabled. By rebuilding the index, performance improves dramatically, demonstrating the value of having all relevant information at hand.

Conclusion and Further Resources

Automating the collection of both database‑wide and SQL‑specific diagnostics cuts tuning time from hours or days to minutes. The author provides a GitHub repository with the ready‑to‑run scripts:

https://github.com/liangjingbin99/shouhuo/tree/master/%E7%AC%AC02%E7%AB%A0

Using these tools, DBAs can quickly pinpoint resource‑intensive SQLs, assess index health, and apply targeted optimizations, leading to faster issue resolution and higher overall system efficiency.