Re: SET EXPLAIN: Need Help NOW!

> > Can someone please help me. > > We have several developers all running code in the same directory > with the SET EXPLAIN ON option set. The output is going to > "sqexplain.out". Because we are all writing to the file, it is > VERY difficult to analyze the "sqexplain.out" file. > > Does anyone know of a way to change where the EXPLAIN output > goes to? If so, please E-Mail me. THanks!!! > > Doug Pisik > Sr Systems Engineer > Home Depot > dsp01@homedepot.com > Two answers: 1) try this. What this code does is to read your sqexplain.out file looking for queries that are more expensive than some threshold which you set. I find that 5000 is a good dividing line, things that take under 5000 are generally too quick to worry about, but if you want you can set it to 1 for all I care. It writes each of the queries involved to it's own explain.nnnn file - you can then look at each one individually. The other joy is that it tosses away all the dross (queries under 'threshold'). Be warned, you still have to wipe the sqexplain.out file your self - or cat /dev/null ? sqexplain.out if you want to keep permissions. chk_cost.awk: BEGIN { threshold=3000 ofn_suf=0 ofnm="explain.0" output_sw=0 } { if ($1=="QUERY:") { if (output_sw == 0) { close(ofnm) cmd=sprintf("rm -f %s", ofnm) system(cmd) } output_sw=1 ofn_suf++ ofnm=sprintf("explain.%d",ofn_suf) } if ($1=="Estimated" && $3 < threshold) { output_sw = 0 } print $0 >> ofnm } Then: awk -f chk_cost.awk < sqexplain.out Notice that you can set the threshold to whatever you care to. Also notice that I haven't bothered to figure out how to close the file in awk - this means that after 2000 someodd queries the code is going to choke with "too many open files". -------------------- 2) Another option? Have them run from their home directories - then the sqexplain file is written there instead. Yes you have to set up $PATH and $DBPATH, but then you should be doing that anyway. cheers j. _____________________________________________________________________________ Jack Parker - Hewlett Packard, DMD/IS Boise, Idaho, USA jparker@hpbs3645.boi.hp.com _____________________________________________________________________________ "I'm with the IRS, I'm here to help you" _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________