RE: Bug in Oracle driver ?

Andy,

Thank you for your explanation.

Here are the two queries with the explain plans along with the last time =
they were analyzed. This is a cost-based database. Oracle version =
9.2.0.5. Results are absolutely the same.


TABLE_NAME OWNER LAST_ANAL
------------------------------ ------------------------------ ---------
RCN_FILE_QUEUE DWADMIN 17-DEC-05
RCN_ITEM_SALES_DATA DWADMIN 17-DEC-05
RCN_PAYMENT_TRANSACTION DWADMIN 17-DEC-05
RCN_ORDER_SUMMARY DWADMIN 17-DEC-05


select =
division_id,division_order_id,order_date,order_total,transac tion_total,tr=
ansaction_currency,
sales_total,sales_currency,usd_transaction_total,usd_sales_t otal,exchange=
_rate
from rcn_order_summary
where division_id =3D :1 and division_order_id =3D :2

------------------------------------------------------------ -------------=
------------
| Id | Operation | Name | Rows | =
Bytes | Cost |
------------------------------------------------------------ -------------=
------------
| 0 | SELECT STATEMENT | | 1 | =
50 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| RCN_ORDER_SUMMARY | 1 | =
50 | 3 |
|* 2 | INDEX UNIQUE SCAN | PK_RCN_ORDER_SUMMARY | 1 | =
| 2 |
------------------------------------------------------------ -------------=
------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("RCN_ORDER_SUMMARY"."DIVISION_ID"=3D:Z AND
"RCN_ORDER_SUMMARY"."DIVISION_ORDER_ID"=3D:Z)


------------------------------------------------------------ -------------=
--------------------------
select =
division_id,division_order_id,order_date,order_total,transac tion_total,tr=
ansaction_currency,
sales_total,sales_currency,usd_transaction_total,usd_sales_t otal,exchange=
_rate
from rcn_order_summary
where division_id =3D :1 and division_order_id =3D '' || :2


------------------------------------------------------------ -------------=
------------
| Id | Operation | Name | Rows | =
Bytes | Cost |
------------------------------------------------------------ -------------=
------------
| 0 | SELECT STATEMENT | | 1 | =
50 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| RCN_ORDER_SUMMARY | 1 | =
50 | 3 |
|* 2 | INDEX UNIQUE SCAN | PK_RCN_ORDER_SUMMARY | 1 | =
| 2 |
------------------------------------------------------------ -------------=
------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("RCN_ORDER_SUMMARY"."DIVISION_ID"=3D:Z AND
"RCN_ORDER_SUMMARY"."DIVISION_ORDER_ID"=3D''||:Z)=20

-----Original Message-----
From: Andy Hassall [mailto:andy@andyh.co.uk]=20
Sent: Wednesday, December 21, 2005 2:19 PM
To: Igor Korolev
Cc: dbi-users@perl.org
Subject: 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=3D3, this will show binding =
and
types, for example:

<- prepare=3D DBI::st=3DHASH(0x8718eb4) at bind.pl line 8
-> execute for DBD::Oracle::st (DBI::st=3DHASH(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]=20
> 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 ?
>=20
> If the query has where clause=20
>=20
> where division_id =3D :1 and division_order_id =3D :2
>=20
> execution tooks way too long taking into account that=20
> division_id (VARCHAR2) + division_order_id (VARCHAR2) is the=20
> primary key in this table.
>=20
>=20
> A simple addition forcing division_order_id to be VARCHAR,=20
> dramatically improves performance
>=20
> where division_id =3D :1 and division_order_id =3D '' || :2
>=20
>=20
> So, the code obviously binds the second parameter incorrectly.
>=20
> Thank you,
>=20
> Igor