PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: mpeppler at itf dot ch (Michael Peppler)
Subject: Trapping SHOWPLAN output in sybperl
Date: Feb 1 1996 7:34AM

[Note: the code below requires sybperl 2.03]

It's sometimes quite usefull to be able to run scripts, or queries,
with SHOWPLAN and/or STATISTICS {TIME|IO} on, so that you can
programmatically analyse the way the queries run.

This can be done by writing a custom message handler that traps the
SHOWPLAN/STATISTICS messages:


# Message numbers for statistics and showplan output:
@sh_msgs = (3612 .. 3615, 6201 .. 6225);

# Create a lookup table with each of the message numbers:
@showplan_msg{@sh_msgs} = (1) x scalar(@sh_msgs);

# The message handler:
sub showplan_handler {
    my ($db, $message, $state, $severity, $text, $server, $procedure, $line)
	= @_;
    
    # Don't display 'informational' messages:
    if ($severity > 10) {
	print STDERR ("Sybase message ", $message, ", Severity ", $severity,
	       ", state ", $state);
	print STDERR ("\nServer `", $server, "'") if defined ($server);
	print STDERR ("\nProcedure `", $procedure, "'") if defined ($procedure);
	print STDERR ("\nLine ", $line) if defined ($line);
	print STDERR ("\n    ", $text, "\n\n");

    }
    elsif($showplan_msg{$message}) {
        # Ok, it's one of the messages we're looking for:
        
	print STDERR ($text, "\n");
    }
    elsif ($message == 0) {
	print STDERR ($text, "\n");
    }
    
    0;
}

dbmsghandle(\&showplan_handler);

Once we've done that, we simply need to enable the apropriate options:

$dbh = new Sybase::DBlib  'mpeppler', $password;

$dbh->dbsetopt(DBSHOWPLAN);
$dbh->dbsetopt(DBSTAT, "IO");
#$dbh->dbsetopt(DBSTAT, "TIME");    # I don't find that option very usefull
#$dbh->dbsetopt(DBNOEXEC);          # Set this if you only want to see the 
                                    # showplan, but not execute anything.


You can clear (ie disable) these features using dbclropt():

$dbh->dbclropt(DBSHOWPLAN);


Now it wouldn't be too difficult to write a script that extracts the
text of the stored procedures in a database and runs them through this
sort of filter with SHOWPLAN and NOEXEC set, and flagging any table
scans that are seen. If none of your stored procs do any updates, then
you can simply extract the names of the procedures, the parameteres
they take, and run them, which would give you the SHOWPLAN of the
current compiled query plan, not the one that the optimizer would use
if you created the procedure now (they could be quite different...)

Michael