SQLT - A sql tuners best friend.

Topics about Databases, Backups, Tuning, Architecture, Systems Management, etc etc.

Post Reply
bgrenn
Posts: 91
Joined: Mon Mar 16, 2009 11:47 am
Location: Rochester, NY
Contact:

SQLT - A sql tuners best friend.

Post by bgrenn »

I've been testing with 11gr2, and trying to compare plans with 10.2.0.4 utilizing "OPTIMIZER_FEATURES_ENABLED".. This parameter "should" cause the optimizer to give you same plan on an 11gr2 database that it used in 10gr2.. The idea, is you can run a replay on 11g and 10g optimizers to compare.. Well we've had a few queries that didn't work right for this (the 10gr2 plan in 11gr2 with the parameter isn't right).

Well oracle support wanted me to run .

SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly  [ID 215187.1]

I don't know how familiar everyone is with it, but this is tremendous tool.. It does install some items in the database (just to warn you) so it would require BTG.

There are multiple pieces to it, and I will probably document some more as I go (Do I smell another presentation coming up ?  Hmmm).

Here are the 3 main sections I found.

1) SQLTEXPLAIN

 This section of the tool will take a query out of memory, an Awr, or a script.  it will either evaluate it only or evaluate it and run it.  The information it provides is most of the same stuff you find in a 10053 trace (detailed optimizer trace).. the cool thing is that the output is HTML based.. very easy to read with links !!!


2) SQLT

 I actually found this accidentally.. This will take your sql in a script, and run it with every known parameter change (all_rows, index_cost_adj, optimizer_features_enabled, .....  it ran for 8 hours for my single query.

 In the end it comes out with an HTML report of all the tests, and how long they took.. You can actually find the best plan out of about 600 different Ones.. Really cool.


3) COE_PROFILE

 Now this is the coolest.  Once you find the plan you want in #2, or you have an existing plan in production (that ran at one point), you can use this to create a profile, and implement for the sql.. wahooo...  

The combination of all 3 of these is an awesome set of tools find the best plan for an obstinate query, and lock that plan it..
Post Reply