Simple Oracle/Perl Question

From: Student <kennylim_at_techie.com>
Date: Sun, 13 May 2001 16:40:49 -0700
Message-ID: <9dn5p0$4l0$1@slb2.atl.mindspring.net>

Hi All,

I am a student doing a simple experimentation and had stumbled into a problem when an analyze compute or estimate stats and flush shared pool statement is being used, the explain plan for my select statement in the *.trc will not be generated.

The explain plan is generated if I manually construct an iteration of inserts statement followed up by commit, analyze, flush shared pool statement.

I am wondering if this is a bug or is there a step that I had missed. I had checked the perl docs for both dbd and dbi but so far to no availability.

Any pointers would be greatly appreciated if someone whom had happen to come across this problem.

Please do let me know if need more information.

dbd::oracle 1.06
dbi 1.15

Thanks You All and you have a pleasant sunday.

Student-

---

snippets of my perl code:

---

 for ( $j = 0; $j < ($N_Loops+1); ++$j)

  {

   emp1_loop_counter();
   emp1_insert();
   emp1_commit();
   emp1_update_stats();

   print "\n>>>>>Transaction Is Commited!<<<<<\n";    sleep 1;
   emp1_select();
   print

"\n\n***********************************************************************

\n";

  }

sub emp1_update_stats

{

$sth3 = $dbh->prepare ("analyze table emp1 compute statistics");
$sth3->execute( );

 print ">>>>>Table Statistics Is Updated!<<<<<\n";

$sth4 = $dbh->prepare ("alter system flush shared_pool");
$sth4->execute( );

 print ">>>>>Shared Pool Is Flushed!<<<<<\n\n";

}

snippets of my trace file based on the query:

---

PARSING IN CURSOR #8 len=71 dep=0 uid=41 oct=3 lid=41 tim=443249 hv=2794230381 ad='56f1a80'
select /*+ Record: 1000 Rows */ * from emp1 where last_name = 'Horton' END OF STMT
PARSE #8:c=4,e=4,p=0,cr=41,cu=0,mis=1,r=0,dep=0,og=4,tim=443249 XCTEND rlbk=0, rd_only=1
EXEC #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=443249 FETCH #8:c=0,e=0,p=0,cr=1,cu=4,mis=0,r=0,dep=0,og=4,tim=443249 Received on Sun May 13 2001 - 18:40:49 CDT