RE: Bug in Oracle driver ?

If the query has where clause=20

where division_id =3D :1 and division_order_id =3D :2

execution tooks way too long taking into account that division_id =
(VARCHAR2) + division_order_id (VARCHAR2) is the primary key in this =
table.


A simple addition forcing division_order_id to be VARCHAR, dramatically =
improves performance

where division_id =3D :1 and division_order_id =3D '' || :2


So, the code obviously binds the second parameter incorrectly.

Thank you,

Igor


-----Original Message-----
From: Tim Bunce [mailto:Tim.Bunce@pobox.com]=20
Sent: Wednesday, December 21, 2005 10:23 AM
To: Igor Korolev
Cc: dbi-users@perl.org; c.w.huling@pobox.com; Tim.Bunce@pobox.com
Subject: Re: Bug in Oracle driver ?

On Wed, Dec 21, 2005 at 09:29:18AM -0600, Igor Korolev wrote:
> Wayne, Tim,
>=20
> Any ideas why DBD::Oracle incorrectly binds even non-numeric value =
'1130979589-6919-090637' from the
> below example ?

I don't understand the question.

Tim.

> Thank you,
>=20
> Igor
>=20
> _____________________________________________
> From: Igor Korolev
> Sent: Thursday, December 15, 2005 4:28 PM
> To: 'dbi-users@perl.org'
> Subject: RE: Bug in Oracle driver ?
>=20
> Hello,
>=20
> It looks like the Oracle driver incorrectly binds VARCHAR2 =
variables in case a number is passed from
> perl.
>=20
> Here is an example. This table has a primary key on division_id + =
division_order_id, so such selects
> should run very quickly (and they do run very quickly from sqlplus =
or toad).
>=20
> However, if I execute this using selectrow_array method of DBI.pm
>=20
> my $sql =3D 'select ' . join(',',@$sum_fields) . ' from ' . $table =
..
> qq[ where division_id =3D :1 and division_order_id =
=3D :2];
>=20
> It takes up to 1 second per select:
>=20
> Order [1], select took: [0.730479001998901]
> Order [2], select took: [0.441529989242554]
> Order [1130979589-6919-090637], select took: [0.462771892547607]
> Order [3], select took: [0.424475193023682]
> Order [947647153], select took: [0.513856172561646]
> Order [4], select took: [0.49094295501709]
> Order [6], select took: [0.523995161056519]
> Order [7], select took: [0.558786153793335]
> Order [8], select took: [0.452331066131592]
> Order [131635406], select took: [0.451704978942871]
>=20
> However, a simple addition forcing VARCHAR2, dramatically improves =
performance
>=20
> my $sql =3D 'select ' . join(',',@$sum_fields) . ' from ' . $table =
..
> qq[=20
];
>=20
> Order [1], select took: [0.00937604904174805]
> Order [2], select took: [0.00504183769226074]
> Order [1130979589-6919-090637], select took: [0.0115630626678467]
> Order [3], select took: [0.00729489326477051]
> Order [947647153], select took: [0.00545406341552734]
> Order [4], select took: [0.00602984428405762]
> Order [6], select took: [0.00556111335754395]
> Order [7], select took: [0.00576210021972656]
> Order [8], select took: [0.00520086288452148]
> Order [131635406], select took: [0.00546598434448242]
>=20
> Thank you,
>=20
> Igor

RE: Bug in Oracle driver ?

If the two statements have different performance characteristics, they
likely have different execution plans.

Have you run "explain plan" on the two statements (with the bind variable
placeholders in place, rather than literals)? Or checked the plans actually
used through v$sql_plan and the bind variable types through
v$sql_bind_metadata? (assuming a recent version of Oracle - what version are
you on?)

You can also run your script with DBI_TRACE=3, this will show binding and
types, for example:

<- prepare= DBI::st=HASH(0x8718eb4) at bind.pl line 8
-> execute for DBD::Oracle::st (DBI::st=HASH(0x8718eb4)~0x8585d40
'1130979589-6919-090637') thr#8585008
bind :p1 <== '1130979589-6919-090637' (type 0)
bind :p1 <== '1130979589-6919-090637' (size 22/23/0, ptype 4, otype
1)

otype 1 is VARCHAR2. Are you getting something different?

Are you running with the cost-based optimiser? Do you have valid optimiser
statistics on the schema?

If you're running the older rule-based optimiser, it has a habit of tending
towards index-based access where this may not actually be the fastest route.
"Tricks" like prefixing strings to columns would make it use full table
scans or filters for parts of the plans instead; for certain shapes of data
and queries this can be faster. But that's not a DBD::Oracle issue, that's
an Oracle tuning issue.

For newer versions of Oracle, then "bind variable peeking" is one thing to
consider when you have differences between running statements with literals
versus using bind variables.

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

> -----Original Message-----
> From: Igor Korolev [mailto:ikorolev@digitalriver.com]
> Sent: 21 December 2005 16:35
> To: Tim Bunce
> Cc: dbi-users@perl.org; c.w.huling@pobox.com
> Subject: RE: Bug in Oracle driver ?
>
> If the query has where clause
>
> where division_id = :1 and division_order_id = :2
>
> execution tooks way too long taking into account that
> division_id (VARCHAR2) + division_order_id (VARCHAR2) is the
> primary key in this table.
>
>
> A simple addition forcing division_order_id to be VARCHAR,
> dramatically improves performance
>
> where division_id = :1 and division_order_id = '' || :2
>
>
> So, the code obviously binds the second parameter incorrectly.
>
> Thank you,
>
> Igor