On Mon, Jun 06, 2005 at 11:14:06PM -0500, Tim Kirby wrote:
>
> My situation is simple. I have an Oracle database with a simple table in it
> and a query that returns maybe 25k records. I run it with sqlplus, set
> pagesize to 0 and stuff the query in (client solaris, server SGI (ick)) and
> whack - six to 16 seconds depending on time of day, loading and so forth,
> and it's done.
>
> The same query with DBI/DBD, regardless of how I try and do it, takes over a
> couple of minutes. It doesn't seem to matter what I do - how I retrieve the
> data, either with discrete fetches or with a single fetchall_arrayref().
>
> Watching the data over the network it looks as though there is a distinct
> "paging" effect - a notable pause that doesn't happen with the sqlplus
> run. I know that the DBI code is pretty efficient; for the life of me I
> can't seem to come up with a rationale; funnily enough I found someone
> else with an identical sounding app - similar record count, similar
> profile - 6 seconds sqlplus, mintes with DBI, talking to Oracle. He
> got lots of responses, and most everyone seemed to ignore the sqlplus
> data that he put in his posting and told him his database was busted.
>
> So, am I unreasonable in my expectations? I'm not expecting it to run faster
> than sqlplus - I would even be happy with half the speed - but over an order
> of magnitude difference seems extreme for the same query on the same DB
> from the same client.
You've not quoted version numbers for: oracle client, oracle server,
DBI, or DBD::Oracle. Nor URLs to the old thread you refer to.
Some old DBD::Oracle versions did have a problem setting the row cache.
Enabling trace level 3 (or higher) will show the auto row cache size
used (grep for dbd_describe). Setting $dbh->{RowCacheSize} before the
prepare() will alter the row cache size. See the docs.
Tim.
p.s. The [email protected] list is the place for such questions.
