Este artigo está escrito em Inglês e Português
Life is full of coincidences… Because IIUG announced the 2013 IIUG user’s conference, to be held in Sand Diego on 21-25 April, I spent some time reviewing my 2010 presentation. The topic was “Use EXPLAIN_SQL with any Tool” and at the time I was proud of some of the achievements I reach. The basic idea was very simple: Use the function EXPLAIN_SQL() introduced in version 11.10 to obtain a query plan in XML format. Then use the engine XML capabilities to apply an XML transformation style sheet (XSLT) to finally get the plan in a manner that could be presented in any tool. Truth is, that although I think the idea is great, the fact that IBM never documented EXPLAIN_SQL properly, my lack of knowledge in XML technology (I know the basics, but I’m not efficient with it), and some technical issues, lead the idea to a stop… No progress in nearly 3 years. This triggered me to think about another approach… Because the basic problem still exists: It’s not easy for a programmer with no access to the database server host machine to obtain the query plans.
But I was talking about coincidences… While I was thinking about this issue once again, I receive almost simultaneously two direct inquires about the use of EXPLAIN_SQL (from people who saw references to my presentation).
So, then what is this article about? Well… I think I finally managed to get a way to obtain the query plans on any client in an easy way, almost with no limitations or constraints… That’s what I’m going to explain now
By default Informix writes the query plans to a file in the database server host’s filesystem. The default file name is $HOME/sqexplain.out if the user asks for the plan. If it’s the DBA that activates the plan generation then the default filename will be $HOME/sqexplain.out.SID where “SID” is the session ID.
The concept was created when most programmers and users were directly connected through a terminal window (Telnet or SSH) to the database server host. Currently this is not the case in many situations. And then you need some mechanism to move the file to the client machine, and open that file. That’s technically a mess. And even if you set it up, it’s awkward to work with it.
In version 11.10 IBM introduced a function called EXPLAIN_SQL() that allows a client tool to send the query and get back the query execution plan in XML. Then the tool should process this XML and present the query plan in some format (ideally in a graphic form). The problems are that the function inputs were never properly documented, the XML that it outputs is complex, and nearly no tools are able to do this (IBM Optim Data Studio can do it, but I’m not aware of any other tool which is able to implement this). What I explained in the IIUG presentation was a prototype on how to do that to get the query plan in the traditional text form result.
Another improvement introduced in version 10 was the ability to choose the filename. Besides the old SET EXPLAIN ON, version 10 also supported SET EXPLAIN FILE TO “path/name”. This allowed for some custom tricks like having a shared filesystem between the server and the clients. But still this is far from a good solution.
Finally, version 10 also introduced the ability to SET EXPLAIN ON AVOID_EXECUTE which will generate the query plan but will not execute the SQL statement.
Given all the issues with EXPLAIN_SQL I tried to imagine a simple way to retrieve the file from the server and present it in the client. Version 11.70.FC6 introduced some new functions that allow us to read from a file, and this would allow a custom procedure to retrieve the file and return it as a LVARCHAR or CLOB. I implemented a prototype with this, but although it worked is was a bit complex and would require version 11.70.FC6. So I kept trying to reach a more generic solution that would work on most Informix versions. And I believe I got it.
It comes in the form of three or four procedures in SPL. The purpose of these procedures is to emulate the basic functionality of activating the explain, with or without executing, resetting the explain file, and obtain the file. The procedure are presented in the end of the article and I’m going to write a few comments about each of them:
- Activates the explain output and resets the explain file. The explain filename is controlled by the DBA, not the user. So they can be put on a specific location
- I declare two global variables (available through the user session). One for the explain file name and the other to remember is the user used AVOID_EXECUTE or not
- I create the explain file name (without PATH). Here I’m concatenating the username and it’s session. This rule simulates the situation where a DBA runs onmode -Y. There are pros and cons about this. The good thing is that a user with different sessions can capture different explains. The bad thing is that the filename will be almost unique. So a method of cleaning them up should be implemented. It can be a scheduler task that calls a script or a crontab script… Just look for files where the SID does not exist on the instance
- Then I create the complete filename. Here I’m using /tmp as the file location but that’s not a very good idea. It would be better to have a separate filesystem (or directory with quota). It needs to be written by all. Remember that you should be able to eliminate files on that directory to implement the cleaning script. Otherwise you could just use a single file name for each user. So the file would be re-used. Again, quotas are important to avoid that a user grabs all the space
- Then we clear the file using a SYSTEM command. This is a major difference from the SET EXPLAIN ON statement. This statement will append to the file. The reason why I clean it, is because when I retrieve the file, I send it to the client side. I don’t process it to obtain just the last query.
- Finally I define the explain file and activate the explain (here without the AVOID_EXECUTE)
- This procedure would be the replacement for SET EXPLAIN ON
- Exactly the same thing but in this case the statement SET EXPLAIN ON AVOID EXECUTE is used. Same rules for file and file reset
- This is not really needed. It just resets the explain file (clears its content). In practice calling the set_explain_on() or set_explain_on_avoid_execute() again has the same effect
- Used just to clear the file. You need to previously have called set_explain_on() or set_explain_on_avoid_execute()
- This is not really need as calling one of the functions that starts explain has the same practical effect. But for clarity I decided to create it. It can be used between two query execution and explain file request to clear the explain file, so that on the second call the first query plan is not returned
- This is the juicy part… It retrieves the file and returns it as a CLOB. In dbaccess it will not be very readable, but with GUI tools like Optim Data Studio, SQL Squirrel, AGS Server Studio etc. it works perfectly
- It uses the native Informix function FileToCLOB() to read the explain file and return a CLOB containing it
- Most tools I tested this on (Squirrel SQL, Server Studio…) will return this as a clickable result set. Once you click it, the explain will be showed in the tool in a separate window
As an example, we can try this on stores database with the following simple instructions (it assumes the functions were already created):
email@example.com:informix-> cat test_explain.sql
EXECUTE PROCEDURE set_explain_on();
SELECT * FROM customer WHERE customer_num = 101;
EXECUTE FUNCTION get_explain();
EXECUTE PROCEDURE reset_explain();
SELECT COUNT(*) FROM customer c, orders o WHERE o.custom...