Hello Bob:
I ran explains ... got different execution paths and then
discovered a "=" in one where clause and, in the other
query, there was a "like" ... huge difference in Oracle performance.
Thanks. That was a good process for isolating a problem. Those
2:00 A.M. efforts under the gun still cause anxiety.
Hal
> -----Original Message-----
> From: Bob Showalter [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, September 26, 2002 5:54 AM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: Re: Perl DBI vs SQLPLUS
>
> ----- Original Message -----
> From: <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>
> Sent: Thursday, September 26, 2002 2:15 AM
> Subject: Perl DBI vs SQLPLUS
>
>
> >
> >
> > > I don't know if this is the way to trespond to your question and I
> > > apologize
> > > in advance if this is not appropriate.
> > >
> > > I've got a question regarding SQL*PLUS and DBI
> > > The following query in Oracle, using sql*plus, takes 2.3 seconds.
> > >
> > > Using perl DBI, it takes over 4 minutes.
>
> Can't understand why DBI whould be any slower. Are you *positive* it is
> the
> *exact* same query?
>
> Try writing $oracle_query out do a file and then run that using sqlplus.
> Also, run an EXPLAIN PLAN on it and see what the execution plan looks
> like.
>
> > >
> > > Any ideas what to do?
> > >
> > > $oracle_query = 'select c.object_name,c.column_name, c.column_desc
> > > from abbr_ref b, meta_column_master c, meta_node_ref o
> > > where (b.word like \'%'.$wanted_value.'%\'
> > > or b.abbr like \''.$wanted_value.'%\'
> > > or c.column_name like \''.$wanted_value.'\'
> > > or upper(c.bus_name) like \'%'.$wanted_value.'%\'
> > > or upper(c.column_desc) like \'%'.$wanted_value.'%\')
> > > and b.abbr = o.node
> > > and o.column_name like c.column_name
>
> This join condition looks suspiscious. Shouldn't that be o.column_name =
> c.column_name ? Oracle probably can't optimize that join.
>
> > > group by c.object_name,c.column_name,c.column_desc
> > > order by 1,2,3';
> > >
> > >
> > > I am using the following to fetch the rows:
> > > while(($object_name,$column_name,$column_desc) = $sth->fetchrow_array)
> {
>
> That should be fine.
>
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
