Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

SQL Health-Check 101

leave a comment »

SQLTXPLAIN (SQLT) is a tool widely used to analyze SQL statements in terms of performance. It has a small drawback although: It requires to create a new schema name SQLTXPLAIN, then over a hundred schema objects on it. Some Production environments have small and un-frequent maintenance  windows that make it hard to plan on an expedite installation of SQLT. In such cases, this other tool SQL Health-Check (SQLHC) provides a quick workaround.

SQLHC is a script available in My Oracle Support (MOS) under Document Id: 1366133.1. This script does not install any objects into the database, and still provides a very valuable set of Observations which are the result of over 100 health-checks around one SQL statement. It also includes some extra information such as Tables, Indexes and Columns statistics, Execution Plans, SQL performance metrics, etc.

SQLHC does not compete with SQLTXPLAIN in terms of extension, but when installing SQLTXPLAIN is not a short-term option, then SQLHC is a second best.

Advertisements

Written by Carlos Sierra

April 1, 2012 at 1:57 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: