Re: Set Explain

My apologies if this has already come up in your mailbox, but my email system crashed, so I dont know if it was sent OK. :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 script does not do what you ask, although the first part of your requirements can be handled by passing the sqexplain.out file through a filter that replaces all quoted strings with quoted constant strings then filtering the file through my Perl script. My script simply orders the SQLs in the file by descending number of occurences and the estimated cost (not a good estimate, I agree, but something to go on). Your other requirements may involve extensive modifications to the script, and maybe it would be a better idea to write your C program. Feel free to hack my script if you feel up to it. BTW, thanks a lot for your help in my other queries. Heres my 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 ------------------------------------- HTH Sujit Pal