Re: Set Explain

In article <5dqe1q$rc7@cssun.mathcs.emory.edu>, CSC CIS <spal@watson.den.csci.csc.com> writes >Doug > >The application I had in mind when I suggested this was a standalone = >menu driver which calls executables using the RUN command. Each = >executable calls a common init function where the SET EXPLAIN ON is = >invoked. > >You are right when you imply that to do this for a reasonable sized = >application, one would generate mountains of explain output, which will = >become useless because nobody will find the time to read it. I also = >agree with you that the best solution is to train the programmers to = >write good SQL. > >To handle the problem, I wrote a Perl script that reads the = >sqexplain.out file and sorts the SQLs by descending order of number of = >occurences and the estimated cost and sends the results to stdout. This = >helps to isolate SQL related performance bottlenecks before it goes to = >the client (assuming of course that your test database is about the same = >or similar size as the clients). > >BTW, I would be happy to post my script if anybody thinks it would be = >useful for them. (Unashamed plug, haha :-)) > >Sujit Pal > Well I was going to get an sqexplain.out from work and write some C on my Linux box at ome but plug away.....my idea was to have a version that stripped out constants from the sql i.e. any 'word' in single quotes and any numbers. This means two runs like:- select from person where personno = 1 AND select from person where personno = 3 would be treated the same. Also you could parse which tables are being sequentially scaned and use systables.nrows to deciede if this was significant i.e. > 500 rows. Also you could do the same from Dynamic Hash Join under online 7. -- David Williams