Home » Open Source » Programming Interfaces » Perl DBI vs SQLPLUS vs TOAD (Solaris 10)
Perl DBI vs SQLPLUS vs TOAD [message #495939] Wed, 23 February 2011 06:57 Go to next message
vvanjanikumar
Messages: 3
Registered: February 2011
Location: india
Junior Member
Hi,

We have a query executing from perl.

my $sth = $dbh->prepare($sql)
my $rc = $sth->execute
$ref = $sth->fetchall_arrayref()

the sql was executing with a good plan (output in < 3 min) until some time in Dec 2010.

We did analyze some of the tables as part of a DB maintenance release.

Post that execution plan changed with some full table scans and total execution time of more than 1.5 hours.

We verified the query and did some tests in sqlplus and toad and could see that the results are obtained in less than 3 min.

We tried sql profiles as well, but couldn't make any changes to the execution plans.

Can you please advice how to proceed.

Cheers,
Veera
Re: Perl DBI vs SQLPLUS vs TOAD [message #495940 is a reply to message #495939] Wed, 23 February 2011 06:59 Go to previous messageGo to next message
vvanjanikumar
Messages: 3
Registered: February 2011
Location: india
Junior Member
perl version is 5.8.8
oracle 10g RAC version 10.2.0.4.0
Solaris 10
Toad 9.5
SQL*Plus: Release 10.2.0.1.0
Re: Perl DBI vs SQLPLUS vs TOAD [message #495944 is a reply to message #495939] Wed, 23 February 2011 07:43 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You did many things but we can't know what you have.
Do you think we can make any advice on something we don't see?

Post code, post explain plan, post execution session, post table row numbers, post indexes, post constraints, pmost NLS parameters (in ALL environment), post everything that make a change in an execution plan.

Regards
Michel
Re: Perl DBI vs SQLPLUS vs TOAD [message #496084 is a reply to message #495944] Thu, 24 February 2011 06:21 Go to previous message
vvanjanikumar
Messages: 3
Registered: February 2011
Location: india
Junior Member
Apologies for the confusion.

Thanks for the response.
We have fixed the issue by using sql hints.

Cheers,
Veera
Previous Topic: PL/SQL and AJAX
Next Topic: Python and Array of CLOBs using cx_Oracle
Goto Forum:
  


Current Time: Sun Dec 21 19:04:57 CST 2014

Total time taken to generate the page: 0.04741 seconds