Using Explain Plan with Perl DBI/DBD

From: Ken Chesak <ken.chesak_at_dhs.state.tx.us>
Date: Tue, 12 Oct 1999 10:08:50 -0500
Message-ID: <7tvj1u$c5k$1_at_news.tdh.state.tx.us>

I am running Perl 5.005_02 on NT with Oracle 7.3. I have the current dbi/dbd modules. I am trying to set up the explain plan to check out some sql. Is this the best way to tune SQL? Does some one have an example that works. After the explain plan, I plan to use TKPROF.

First problem I ran into was log_msg in Oracle.pm. All those values have been changed to trace_msg.

My current error occurs on the first SQL with explain plan, this is the trace log information. I have execute the SQL in SQLPlus and it works fine. When using EXPLAIN PLAN does the SQL actually execute?

-> prepare for DBD::Oracle::db (DBI::db=HASH(0xe062cc)~0xe38c24 'EXPLAIN
PLAN

                SET STATEMENT_ID = 'strip1' for
                insert into t_rate
                select distinct resident_internal_id
                    from assessment
                    where effective_date <= ?')

   dbd_preparse scanned 1 distinct placeholders    <- prepare= DBI::st=HASH(0xde4e24) at tune_strip.pl line 235.
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0xe39180)~INNER)
   <- DESTROY= undef at tune_strip.pl line 241.
-> execute for DBD::Oracle::st (DBI::st=HASH(0xde4e24)~0xe391d4
'01/01/1998'

      bind :p1 <== '01/01/1998' (type 0)
      bind :p1 <== '01/01/1998' (size 10/11/0, ptype 4, otype 1)
   !! ERROR: 1036 'ORA-01036: illegal variable name/number (DBD: obndra

failed)

   <- execute= undef at tune_strip.pl line 242.
-> errstr in DBD::_::common for DBD::Oracle::db
(DBI::db=HASH(0xe062cc)~0xe3
c24)

Thanks

Ken Chesak
ken.chesak_at_dhs.state.tx.us Received on Tue Oct 12 1999 - 17:08:50 CEST