Hi, Mike,
adding $sql_result =~ s/:(\d+)/:p$1/g; to the code did solve the
problem, but change $dbh->state to $dbh->err does not help without
adding the line.
The only concern I have here is by such change, it might go through a
defferent path than the real one
Thanks you very much.
The following is my code. Let me know if you have any more comments,
that is really helpful.
#!/usr/local/bin/perl -w
########################################################################
#########
# Purpose : Explain plan for all the script in the database
# Created : 20020208
# Author :
########################################################################
#########
sub trap_die
{
my @param = @_;
$message = $param[0];
$message_body = $param[1];
$CommandString = "/bin/echo \"$message_body\" | /bin/mailx -s
\"$message\" cshao\@traffic.com ";
system ( $CommandString );
}
sub print_date
{ use Time::localtime;
my $tm = localtime;
printf("at time %04d-%02d-%02d %02d:%02d:%02d\n",
$tm->year+1900,
($tm->mon)+1,$tm->mday,$tm->hour,$tm->min,$tm->sec);
}
my $i = 0;
my @instance = ('dev');
my @username = ('sys');
my @passwd = ('xxxx');
my
($dbh,$addr,$hashval,$parse_user,$sth,@row,$stmt_id,$sql_text,$error);
my ($sql,$csr,$data,$userdata ,$rv,$maildata,$message_body,$message );
my ($sql_explain,$sql_result,$errorstat,$length);
my ($failure,$counter);
while ($i <= $#instance ) {
use strict;
use DBI;
eval {
$dbh = DBI->connect ( "dbi:Oracle:$instance[$i]",
"$username[$i]",
"$passwd[$i]",
{
PrintError => 0,
RaiseError => 0,
AutoCommit => 1
}
) || die "Database Connection not made
$DBI::errstr" ;# Create a Database
#check to see whether system.sqltemp exist
$rv = &getValue("select count(*) from dba_tables where table_name =
'SQLTEMP' and owner = 'SYSTEM'");
if( $rv == 0)
{
$sql = "create table sqltemp
(
ADDR VARCHAR2 (8)
,HASHVAL INTEGER
,SQL_TEXT VARCHAR2(2000)
,DISK_READS NUMBER
,EXECUTIONS NUMBER
,PARSE_CALLS NUMBER
,PARSE_USER VARCHAR2(30)
,STMT_ID VARCHAR2(100)
) tablespace users";
$dbh->do($sql);
print "table system.sqltemp crated\n";
}
#check to see whether system.plan_table exist
$rv = &getValue("select count(*) from dba_tables where table_name =
'PLAN_TABLE' and owner = 'SYSTEM'");
if( $rv == 0)
{
$sql = "create table PLAN_TABLE (
statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operation varchar2(30),
options varchar2(30),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30))tablespace
users";
$dbh->do($sql);
print "table system.plan_table
crated\n";
} #end of if
#truncate table system.plan_table and system.sqltemp
$failure = 1;
$counter = 0;
while (( $failure != 0 ) && ( $counter < 50))
{
$sql = "truncate table system.plan_table";
$dbh -> do($sql);
$rv = &getValue("select count(*) from system.plan_table");
if ($rv > 0) {$failure = 1;$counter = $counter + 1;}
else {$failure = 0;}
}
if ($counter ==50)
{
$sth->finish();
$dbh->disconnect();
trap_die("$instance[$i].Explain_plan failed","Error dropping
system.plan_table\n");
}
#truncate system.sqltemp table
$failure = 1;
$counter = 0;
while (( $failure != 0 ) && ( $counter < 50 ))
{
$sql = "truncate table system.sqltemp";
$dbh -> do($sql);
$rv = &getValue("select count(*) from system.sqltemp");
if ($rv > 0) {$failure = 1;$counter = $counter + 1;}
else {$failure = 0;}
}
if ($counter ==50)
{
$sth->finish();
$dbh->disconnect();
trap_die("$instance[$i].Explain_plan failed","Error dropping
system.sqltemp\n");
}
#populate the sqltemp table
$sql = " INSERT INTO system.sqltemp
SELECT RAWTOHEX(SA.address) addr
,SA.hash_value hash
,SA.sql_text sql_text
,SA.DISK_READS diskrds
,SA.EXECUTIONS execs
,SA.PARSE_CALLS parses
,DU.username username
,SUBSTR(RAWTOHEX(SA.address)||':'||TO_CHAR(SA.hash_value) ,
1,30) stmt_id
FROM v\$sqlarea SA,DBA_USERS DU
WHERE
command_type in (2,3,6,7)
AND
SA.parsing_schema_id != 0
AND SA.parsing_schema_id = DU.user_id
AND DU.username not like '%SYS%'
AND DU.username <> 'QDBA'
AND DU.username <> 'PERFSTAT'";
$dbh -> do($sql);
# get the username etc from system.sqltemp
$sql = "SELECT
addr,hashval,parse_user,stmt_id,sql_text,length(sql_text) from
system.sqltemp where parse_user not like '%SYS%' ";
$csr = $dbh->prepare($sql);
$csr->execute();
$userdata = $csr->fetchall_arrayref();
foreach $data (@$userdata)
{
$addr = $data->[0];
$hashval = $data->[1];
$parse_user = $data->[2];
$stmt_id = $data->[3];
$length = $data->[5];
$sql_result = "";
$sql = "alter session set current_schema=$parse_user";
$dbh->do($sql);
#get the sql statement
if ($length > 990)
{
$sql_explain = "select sql_text from v\$sqltext
where address = hextoraw('$addr') and hash_value = $hashval order by
piece";
$sth = $dbh->prepare($sql_explain);
$sth->execute();
while ( my @row = $sth->fetchrow_array )
{
$sql_text = $row[0];
#print "$sql_text\n";
$sql_result = $sql_result.$sql_text;
}
}
else
{
$sql_result = $data->[4];
}
$sql_result =~ s/:(\d+)/:p$1/g;
$failure = 1;
$counter = 0;
while (( $failure != 0 ) && ( $counter < 4 ))
{
$sql_explain = "explain plan set statement_id = '$stmt_id' into
system.plan_table for $sql_result";
$dbh->do($sql_explain);
#remove the end line from Java if explain failed
chop ($sql_result);
if (($dbh->state ne "") && ($dbh->errstr eq ""))
{
$failure = 1;
$counter = $counter + 1;
print "DO_explain failed",$dbh->state,"$stmt_id\n";
$error= $dbh->state;
}
elsif (($dbh->state ne "") && ( $dbh->errstr ne ""))
{
$errorstat = $dbh->state;
$error = $dbh->errstr;
$error = substr ($error,0,30);
$sql = "INSERT INTO system.plan_table(statement_id,remarks)
VALUES ('$stmt_id', '$error')";
$dbh->do($sql);
$failure = 0;
print "insert error $stmt_id\n";
}
else {
$failure = 0;
print "succeed no hassle $stmt_id\n";
}
} #end of failure check
} #end of the userdata while
#check to see how many rows is not explained
$rv = &getValue("select count(*) from system.sqltemp where stmt_id not
in (
select statement_id from system.plan_table)");
if ($rv > 0)
{
# get parser_user and stmt_id
$sql = "SELECT parse_user,stmt_id from system.sqltemp
where stmt_id not in ( select statement_id from
system.plan_table) ";
$message = substr("For $instance[$i]",0,8)."\n";
$message_body = $message_body.$message;
$csr = $dbh->prepare($sql);
$csr->execute();
$maildata = $csr->fetchall_arrayref();
foreach $data (@$maildata)
{
$parse_user = $data->[0];
$stmt_id = $data->[1];
$message = sprintf("%10s
%20s\n",$parse_user,$stmt_id);
$message_body = $message_body.$message;
} #end of the userdata while
$message = "is not explained\n";
$message_body = $message_body.$message;
}
#get parse user,stmt_id which is explained ERROR
$rv = &getValue("select count(*) from system.plan_table
where remarks is not null");
if ($rv > 0)
{
# get parser_user and stmt_id
$message = substr("For $instance[$i]",0,8)."\n";
$message_body = $message_body.$message;
$sql = "SELECT parse_user,stmt_id from system.sqltemp where
stmt_id in (select statement_id from system.plan_table
where remarks is not null)";
$csr = $dbh->prepare($sql);
$csr->execute();
$maildata = $csr->fetchall_arrayref();
foreach $data (@$maildata)
{
$parse_user = $data->[0];
$stmt_id = $data->[1];
$message = sprintf("%10s
%20s\n",$parse_user,$stmt_id);
$message_body = $message_body.$message;
} #end of the userdata while
$message = "is explained ERROR\n";
$message_body = $message_body.$message;
}
print "$message_body\n";
# Close Database Handle
$csr->finish();
$dbh->disconnect();
}; # end of eval
if ($@) {
$dbh->disconnect();
print "$@\n";
}
$i++;
} # end of while loop
if (defined $message_body)
{
trap_die ("Explain Report","$message_body");
}
#subroutine to get the value
sub getValue {
my @para = @_;
my $sql_value = $para[0];
my $csr_sub = $dbh->prepare($sql_value);
$csr_sub->execute();
return $csr_sub->fetchrow_arrayref()->[0];
$dbh->finish();
} ## end of subfunction
