RE: Bug in Oracle driver ?

RE: Bug in Oracle driver ?

am 27.12.2005 18:50:20 von ikorolev

Andy,

I still think that this is a bug in the DBD Oracle driver. =
DBI_TRACE=3D3 produced exact same results for the two queries.
So, the problem is how the driver passes these values to Oracle.

Original query:

selectrow_array in DBD::_::db for DBD::Oracle::db =
(DBI::db=3DHASH(0x2d1fd4)~0x109394 'select division_id,division
_order_id,order_date,order_total,transaction_total,transacti on_currency,s=
ales_total,sales_currency,usd_transaction_tot
al,usd_sales_total,exchange_rate from rcn_order_summary where =
division_id =3D :1 and division_order_id =3D :2' undef 'atla
ntic' '1')
2 -> prepare for DBD::Oracle::db (DBI::db=3DHASH(0x109394)~INNER =
'select division_id,division_order_id,order_date,orde
r_total,transaction_total,transaction_currency,sales_total,s ales_currency=
,usd_transaction_total,usd_sales_total,exchan
ge_rate from rcn_order_summary where division_id =3D :1 and =
division_order_id =3D :2' undef)
New DBI::st (for DBD::Oracle::st, parent=3DDBI::db=3DHASH(0x109394), =
id=3D)
=
dbih_setup_handle(DBI::st=3DHASH(0x109370)=3D>DBI::st=3DHASH (0x110648), =
DBD::Oracle::st, 10937c, Null!)
dbih_make_com(DBI::db=3DHASH(0x109394), DBD::Oracle::st, 204)
dbd_preparse scanned 2 distinct placeholders
dbd_st_prepare'd sql SELECT
dbd_describe SELECT (EXPLICIT, lb 80)...
fbh 1: 'DIVISION_ID' NO null , otype 1-> 5, dbsize 32/33, =
p32.s0
fbh 2: 'DIVISION_ORDER_ID' NO null , otype 1-> 5, dbsize 64/65, =
p64.s0
fbh 3: 'ORDER_DATE' NULLable, otype 12-> 5, dbsize 7/76, p75.s0
fbh 4: 'ORDER_TOTAL' NULLable, otype 2-> 5, dbsize 22/134, =
p22.s6
fbh 5: 'TRANSACTION_TOTAL' NULLable, otype 2-> 5, dbsize =
22/134, p22.s6
fbh 6: 'TRANSACTION_CURRENCY' NULLable, otype 1-> 5, dbsize =
3/4, p3.s0
fbh 7: 'SALES_TOTAL' NULLable, otype 2-> 5, dbsize 22/134, =
p22.s6
fbh 8: 'SALES_CURRENCY' NULLable, otype 1-> 5, dbsize 3/4, =
p3.s0
fbh 9: 'USD_TRANSACTION_TOTAL' NULLable, otype 2-> 5, dbsize =
22/134, p22.s6
fbh 10: 'USD_SALES_TOTAL' NULLable, otype 2-> 5, dbsize 22/134, =
p22.s6
fbh 11: 'EXCHANGE_RATE' NULLable, otype 2-> 5, dbsize 22/134, =
p22.s6
dbd_describe'd 11 columns (row bytes: 241 max, 133 est avg, cache: =
74)
2 <- prepare=3D DBI::st=3DHASH(0x109370) at DBI.pm line 938.
-> execute for DBD::Oracle::st (DBI::st=3DHASH(0x109370)~0x110648 =
'atlantic' '1')
bind :p1 <== 'atlantic' (type 0)
bind :p1 <== 'atlantic' (size 8/9/0, ptype 4, otype 1)
bind :p1 <== 'atlantic' (size 8/8, otype 1, indp 0)
bind :p1 done
bind :p2 <== '1' (type 0)
bind :p2 <== '1' (size 1/2/0, ptype 4, otype 1)
bind :p2 <== '1' (size 1/1, otype 1, indp 0)
bind :p2 done=20

Modified (quick) query:

selectrow_array in DBD::_::db for DBD::Oracle::db =
(DBI::db=3DHASH(0x106870)~0x110420 'select division_id,division
_order_id,order_date,order_total,transaction_total,transacti on_currency,s=
ales_total,sales_currency,usd_transaction_tot
al,usd_sales_total,exchange_rate from rcn_order_summary where =
division_id =3D :1 and division_order_id =3D '' || :2' undef
'atlantic' '1')
2 -> prepare for DBD::Oracle::db (DBI::db=3DHASH(0x110420)~INNER =
'select division_id,division_order_id,order_date,orde
r_total,transaction_total,transaction_currency,sales_total,s ales_currency=
,usd_transaction_total,usd_sales_total,exchan
ge_rate from rcn_order_summary where division_id =3D :1 and =
division_order_id =3D '' || :2' undef)
New DBI::st (for DBD::Oracle::st, parent=3DDBI::db=3DHASH(0x110420), =
id=3D)
=
dbih_setup_handle(DBI::st=3DHASH(0x1093e8)=3D>DBI::st=3DHASH (0x1106c0), =
DBD::Oracle::st, 1093f4, Null!)
dbih_make_com(DBI::db=3DHASH(0x110420), DBD::Oracle::st, 204)
dbd_preparse scanned 2 distinct placeholders
dbd_st_prepare'd sql SELECT
dbd_describe SELECT (EXPLICIT, lb 80)...
fbh 1: 'DIVISION_ID' NO null , otype 1-> 5, dbsize 32/33, =
p32.s0
fbh 2: 'DIVISION_ORDER_ID' NO null , otype 1-> 5, dbsize 64/65, =
p64.s0
fbh 3: 'ORDER_DATE' NULLable, otype 12-> 5, dbsize 7/76, p75.s0
fbh 4: 'ORDER_TOTAL' NULLable, otype 2-> 5, dbsize 22/134, =
p22.s6
fbh 5: 'TRANSACTION_TOTAL' NULLable, otype 2-> 5, dbsize =
22/134, p22.s6
fbh 6: 'TRANSACTION_CURRENCY' NULLable, otype 1-> 5, dbsize =
3/4, p3.s0
fbh 7: 'SALES_TOTAL' NULLable, otype 2-> 5, dbsize 22/134, =
p22.s6
fbh 8: 'SALES_CURRENCY' NULLable, otype 1-> 5, dbsize 3/4, =
p3.s0
fbh 9: 'USD_TRANSACTION_TOTAL' NULLable, otype 2-> 5, dbsize =
22/134, p22.s6
fbh 10: 'USD_SALES_TOTAL' NULLable, otype 2-> 5, dbsize 22/134, =
p22.s6
fbh 11: 'EXCHANGE_RATE' NULLable, otype 2-> 5, dbsize 22/134, =
p22.s6
dbd_describe'd 11 columns (row bytes: 241 max, 133 est avg, cache: =
74)
2 <- prepare=3D DBI::st=3DHASH(0x1093e8) at DBI.pm line 938.
-> execute for DBD::Oracle::st (DBI::st=3DHASH(0x1093e8)~0x1106c0 =
'atlantic' '1')
bind :p1 <== 'atlantic' (type 0)
bind :p1 <== 'atlantic' (size 8/9/0, ptype 4, otype 1)
bind :p1 <== 'atlantic' (size 8/8, otype 1, indp 0)
bind :p1 done
bind :p2 <== '1' (type 0)
bind :p2 <== '1' (size 1/2/0, ptype 4, otype 1)
bind :p2 <== '1' (size 1/1, otype 1, indp 0)
bind :p2 done

-----Original Message-----
From: Igor Korolev=20
Sent: Wednesday, December 21, 2005 4:52 PM
To: 'Andy Hassall'
Cc: dbi-users@perl.org
Subject: 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

Re: Bug in Oracle driver ?

am 28.12.2005 13:56:56 von Tim.Bunce

On Tue, Dec 27, 2005 at 11:50:20AM -0600, Igor Korolev wrote:
> Andy,
>
> I still think that this is a bug in the DBD Oracle driver. DBI_TRACE=3 produced exact same results for the two queries.
> So, the problem is how the driver passes these values to Oracle.

Trace level 9 will include the OCI API calls. But they're bound to be
the same as the only think you're changing is the SQL.

I've not seen a response to my suggestion that you try other
values of ora_ph_type.

Tim.

RE: Bug in Oracle driver ?

am 02.01.2006 00:35:03 von Andy

> I still think that this is a bug in the DBD Oracle driver.
> DBI_TRACE=3 produced exact same results for the two queries.
> So, the problem is how the driver passes these values to Oracle.

Since the type was shown to be the same in both cases by the trace, doesn't
this seems to move the probable location of the issue towards Oracle, rather
than DBD::Oracle?

You should be able to get hold of the actual execution plans used by looking
into v$sql_plan - this would at least give some information on why one takes
long than the other, because so far there's no definite explanation for the
differences in elapsed time. You'll need the address/hash_value from v$sql,
or v$session.sql_address/sql_hash_value.

Also see:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DIS PLAYID:103534539
05351

.... for a way to use DBMS_XPLAN to get nicely formatted execution plans from
cached SQL (in 10g DBMS_XPLAN has extra functionality to this directly).

Or use Oracle's tracing functionality.

Another way of looking at it - can you reduce this to a small self-contained
test case that can be tested on other databases?

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