Kudos to Carlos Sierra

I have been playing more and more with Oracle's SQLTXPLAIN; or rather, let me give the full credit to Carlos Sierra, who has written and fully supports this nifty little gem.

Jonathan Lewis get me hooked on dbms_xplan, and I like it a lot! Just the other day I picked up +PEEKED_BINDS as an option from various posters (Tanel Poder, Greg Rahn, Jonathan Lewis) and started using it right away. And I never really tackled SQLTXPLAIN just because.... I had used the earlier version (TRCANLYZR...or something). Until recently.

Carlos has really done a bit of quality work on this piece; he has provided excellent documentation and some good examples. He has shown a wonderful amount of foresight in generating various traces (10053, 10046) and packages functionality like generating testcases, comprehensive web reports and sql plan comparisons. The last of those I had an opportunity to explore today, and it helped me find a problem with some indexes (that will be a seperate post after the dust settles). I just really like the presentation and functionality of this tool. The "main" html report is very tasteful with expand/collaspe java snippets, go flow, and tons of useful information. The comparison did a great job at highlighting areas of concern, and his "Observations" often make me think about something, even if only to affirm. I like the flexibility of grabbing a sql_id, sql hash or plan_hash.

True, it is not perfect yet. Very close, though. I do not like how the install scripts ask for a "schema user" or "application user". To work around that, I create a new role (SQLTXPLAIN_ROLE) and provide that as my "application_user". Then, whenever I want to run SQLTXPLAIN, I just grant/revoke that role to the real application userid. Also, altering the parameters are a little kludgy. I love that Mr. Sierra has made the parameters available and has provided documentation for them (a pleasant surprise), but it would be nice to specify them at run time in a more "normal" fashion. Like, having a flag to turn off the table exports. And what about using datapump instead? =)

One other thing that I am still working on is to get it to work as a client-side piece. After working through a few kinks (ie, my bads) with Sierra, the only portion that bothers me a little is that it keeps asking me for the password for exporting tables. For the most part, I want to skip that.

Anyway, this tool is AWESOME! I love it! I will still use dbms_xplan a bit (afterall, this tool does as well), since it is much lighter weight. But I am definitely adding SQLTXPLAIN to my toolbox and telling others about it. I have also had a few exchanges with Carlos, and he is definitely on the ball.

I am extremely curious about one thing yet. There are a number of interesting tables in the SQLTXPLAIN schema. What is SQLT$_10053_PARSE for? I have never seen it populated with any information. Is there a tool that parses the 10053? Can I have it?!? =)

Now I wonder what other tools BDE has hidden away....