Re: Set Explain

>David Williams wrote: > >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 My Perl script does not do all that you ask, however the first requirement can be satisfied by passing the sqexplain.out file through a filter that replaces quoted strings with quoted constant strings. The other requirements .. well, maybe they can be incorporated into the script but then again, maybe its better to write your C program. What my script does do is order the SQLs by the decreasing number of occurences, and decreasing estimated cost. You can choose to look only at a single table or only at prepared statements by exploiting the patterns in the SQL statements. However, feel free to hack the script if you feel up to it. BTW, thanks a lot for all your help in my previous queries. Heres the script: ------------------------------- cut here ------------------------------------- #!/usr/local/bin/perl # # sqlprof # # Perl script to scan through a sqexplain.out file and identify the number # of times that a particular SQL was called and the Estimated cost of the # query (includes INSERTs, UPDATEs and DELETEs), in descending order of the # number of times the query was called. The first argument is the output of # SET EXPLAIN ON that needs to be scanned. The default is sqexplain.out in # the current directory but it can be anything if specified. The second # argument is the pattern to be matched and should be a quoted string. For # instance if we need to find all PREPAREd statements then we would look for # a ? character or if we wanted to find TEMP tables we could look for "TEMP". # The default pattern is a null, ie it matches everything. # # Author: Sujit Pal # Dated: 10/25/96 # if (($#ARGV == 0) && ($ARGV[0] eq "--") || ($#ARGV > 1)) { die "Usage: sqlprof [sqexplain_out_file] [pattern]\\n"; } if ($#ARGV == -1) { $sqexpl_fname = "sqexplain.out"; $pattern = "#"; } if ($#ARGV == 0) { if (index($ARGV[0], "\\"") < 0) # This is a filename { $sqexpl_fname = $ARGV[0]; $pattern = "#"; } else # This is a pattern { $sqexpl_fname = "sqexplain.out"; $pattern = $ARGV[0]; } } if ($#ARGV == 1) { $sqexpl_fname = $ARGV[0]; $pattern = $ARGV[1]; $upattern =~ tr/A-Z/a-z/; } open(SQEXPL, $sqexpl_fname) || die "Cant stat $sqexpl_fname\\n"; chop(@sqexplls = <SQEXPL>); close(SQEXPL); # # Grep out the SQL statements from the file # @sqllns = grep(/select|update|insert|delete/i, @sqexplls); # # Count the number of occurences # foreach (@sqllns) { if ($sqprofs{$_} eq "") { $sqprofs{$_} = 1; } else { $sqprofs{$_}++; } } # # Make a mapping between the SQL and its estimated cost. Create an # associative array of costs with the statement as key # $i = 0; foreach (@sqllns) { $sqlstmts{$i} = $_; $i++; } @festcosts = grep(/Estimated Cost/, @sqexplls); $i = 0; foreach (@festcosts) { ($const, $cost) = split(/:/, $_); $estcosts{$i} = $cost; $i++; } foreach (keys(%sqlstmts)) { if ($costs{$sqlstmts{$_}} eq "") { $costs{$sqlstmts{$_}} = $estcosts{$_}; } } # # Sort the two arrays by descending order of repetitions and descending # order of estimated costs and print # if ($pattern eq "#") { $ppattern = "None"; } else { $ppattern = $pattern; } $= = 60; foreach (sort count_plus_cost (keys(%sqprofs))) { $sqlstmt = $_; $sqlstmt =~ s/^ //; if ($pattern ne "#") { if (index($sqlstmt, $pattern) < 0 || index($sqlstmt, $upattern) < 0) { next; } } $rptcnt = $sqprofs{$_}; $cost = $costs{$_}; write; } sub count_plus_cost { if ($sqprofs{$a} < $sqprofs{$b}) { 1; } elsif ($sqprofs{$a} == $sqprofs{$b}) { if ($costs{$a} < $costs{$b}) { 1; } elsif ($costs{$a} == $costs{$b}) { 0; } else { -1; } } else { -1; } } format STDOUT = @<<<<< @<<<<<<<< ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< $rptcnt, $cost, $sqlstmt ~~ ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< $sqlstmt . format STDOUT_TOP = SQL Usage Profile for Pattern: @<<<<<<<<<<<< $ppattern Filename: @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Page-#: @<<< $sqexpl_fname $% Count Est.Cost Query ----- -------- -------------------------------------------------------------- . ------------------------------- cut here -------------------------------------