Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 70045
Status: resolved
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: stas [...] sysd.org
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in:
  • 0.08189_01
  • 0.08190-TRIAL
  • 0.08190_01
  • 0.08190_02
  • 0.08190_03
  • 0.08191
  • 0.08192
  • 0.08193
  • 0.08193_01
  • 0.08194
  • 0.08195
Fixed in: (no value)



Subject: Oracle LOB/non-LOB handling is still messy!
Download (untitled) / with headers
text/plain 949b
After the update to the newest version of DBIx::Class (with LOB handling code rewritten), some of my code started running very, VERY slow. Trace revealed the bottleneck: UPDATE LIVRARIA_1129 SET ATUALIZADO = :p1, DATA_ATUALIZACAO = sysdate WHERE ( ( UTL_RAW.CAST_TO_VARCHAR2(RAWTOHEX(DBMS_LOB.SUBSTR(ISBN13, 2000, 1))) = :p2 ) ) The funny thing is: I have *NO* LOB columns in this table! 'ISBN13' is actually a TEXT one. So I tried to disable all the new LOB handling code, and got this: ORA-00932: inconsistent datatypes: expected - got BLOB (DBD ERROR: error possibly near <*> indicator at char 88 in 'UPDATE LIVRARIA_1129 SET ATUALIZADO = :p1, DATA_ATUALIZACAO = sysdate WHERE ( ISBN13 = :<*>p2 )') Somehow, my TEXT is still interpreted as LOB. Now, if I at least ensure that the RAWTOHEX transcoding only apply to non-plaintext LOB, my code gets several hundreds times faster. Not sure if it is a correct way of doing that, still.
Subject: oracle-lob-fix.patch
diff --git a/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm b/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm index f582b94..450e564 100644 --- a/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm +++ b/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm @@ -544,12 +544,29 @@ sub _prep_for_execute { my $col_equality_re = qr/ (?<=\s) ([\w."]+) (\s*=\s*) $/x; + # After the update to the newest version of DBIx::Class (with LOB handling code rewritten), + # some of my code started running very, VERY slow. Trace revealed the bottleneck: + # + # UPDATE LIVRARIA_1129 SET ATUALIZADO = :p1, DATA_ATUALIZACAO = sysdate + # WHERE + # ( ( UTL_RAW.CAST_TO_VARCHAR2(RAWTOHEX(DBMS_LOB.SUBSTR(ISBN13, 2000, 1))) = + # :p2 ) ) + # + # The funny thing is: I have *NO* LOB columns in this table! 'ISBN13' is actually a TEXT one. So I tried to disable all the new LOB handling code, and got this: + # + # ORA-00932: inconsistent datatypes: expected - got BLOB (DBD ERROR: error possibly near <*> indicator at char 88 in 'UPDATE LIVRARIA_1129 SET ATUALIZADO = :p1, DATA_ATUALIZACAO = sysdate WHERE ( ISBN13 = :<*>p2 )') + # + # Somehow, my TEXT is still interpreted as LOB. Now, if I at least ensure that the RAWTOHEX transcoding only apply to non-plaintext LOB, my code gets several hundreds times faster. + # Not sure if it is a correct way of doing that, still. + for my $b_idx (0 .. $#$bind) { my $bound = $bind->[$b_idx]; if ( $lob_bind_indices->{$b_idx} and + $self->_is_binary_lob_type($bound->[0]->{sqlt_datatype}) + and my ($col, $eq) = $sql_parts[0] =~ $col_equality_re ) { my $data = $bound->[1];
Subject: Re: [rt.cpan.org #70045] Oracle LOB/non-LOB handling is still messy!
Date: Fri, 05 Aug 2011 16:00:35 +0200
To: bug-DBIx-Class [...] rt.cpan.org
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 1.6k
Stanislaw Pusep via RT wrote: Show quoted text
> Fri Aug 05 09:35:34 2011: Request 70045 was acted upon. > Transaction: Ticket created by SYP > Queue: DBIx-Class > Subject: Oracle LOB/non-LOB handling is still messy! > Broken in: 0.08189_01, 0.08190-TRIAL, 0.08190_01, 0.08190_02, 0.08190_03, 0.08191, 0.08192, 0.08193, 0.08193_01, 0.08194, 0.08195 > Severity: Important > Owner: Nobody > Requestors: stas@sysd.org > Status: new > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=70045 > > > > After the update to the newest version of DBIx::Class (with LOB handling > code rewritten), some of my code started running very, VERY slow. Trace > revealed the bottleneck: > > UPDATE LIVRARIA_1129 SET ATUALIZADO = :p1, DATA_ATUALIZACAO = sysdate > WHERE > ( ( UTL_RAW.CAST_TO_VARCHAR2(RAWTOHEX(DBMS_LOB.SUBSTR(ISBN13, 2000, 1))) > = > :p2 ) ) > > The funny thing is: I have *NO* LOB columns in this table! 'ISBN13' is > actually a TEXT one. So I tried to disable all the new LOB handling > code, and got this: > > ORA-00932: inconsistent datatypes: expected - got BLOB (DBD ERROR: error > possibly near <*> indicator at char 88 in 'UPDATE LIVRARIA_1129 SET > ATUALIZADO = :p1, DATA_ATUALIZACAO = sysdate WHERE ( ISBN13 = :<*>p2 )') > > Somehow, my TEXT is still interpreted as LOB. Now, if I at least ensure > that the RAWTOHEX transcoding only apply to non-plaintext LOB, my code > gets several hundreds times faster. > Not sure if it is a correct way of doing that, still. >
The attached patch - can you tell me what is it against? My tree does not contain f582b94. Basically I want to ensure you are still seeing this with 0.08195.
Download (untitled) / with headers
text/plain 1.9k
Em Sex Ago 05 10:00:52 2011, RIBASUSHI escreveu: Show quoted text
> Stanislaw Pusep via RT wrote:
> > Fri Aug 05 09:35:34 2011: Request 70045 was acted upon. > > Transaction: Ticket created by SYP > > Queue: DBIx-Class > > Subject: Oracle LOB/non-LOB handling is still messy! > > Broken in: 0.08189_01, 0.08190-TRIAL, 0.08190_01, 0.08190_02,
> 0.08190_03, 0.08191, 0.08192, 0.08193, 0.08193_01, 0.08194, 0.08195
> > Severity: Important > > Owner: Nobody > > Requestors: stas@sysd.org > > Status: new > > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=70045 > > > > > > > After the update to the newest version of DBIx::Class (with LOB
> handling
> > code rewritten), some of my code started running very, VERY slow.
> Trace
> > revealed the bottleneck: > > > > UPDATE LIVRARIA_1129 SET ATUALIZADO = :p1, DATA_ATUALIZACAO =
> sysdate
> > WHERE > > ( ( UTL_RAW.CAST_TO_VARCHAR2(RAWTOHEX(DBMS_LOB.SUBSTR(ISBN13, 2000,
> 1)))
> > = > > :p2 ) ) > > > > The funny thing is: I have *NO* LOB columns in this table! 'ISBN13'
> is
> > actually a TEXT one. So I tried to disable all the new LOB handling > > code, and got this: > > > > ORA-00932: inconsistent datatypes: expected - got BLOB (DBD ERROR:
> error
> > possibly near <*> indicator at char 88 in 'UPDATE LIVRARIA_1129 SET > > ATUALIZADO = :p1, DATA_ATUALIZACAO = sysdate WHERE ( ISBN13 = :<*>p2
> )')
> > > > Somehow, my TEXT is still interpreted as LOB. Now, if I at least
> ensure
> > that the RAWTOHEX transcoding only apply to non-plaintext LOB, my
> code
> > gets several hundreds times faster. > > Not sure if it is a correct way of doing that, still. > >
> > The attached patch - can you tell me what is it against? My tree does > not contain f582b94. Basically I want to ensure you are still seeing > this with 0.08195.
Sorry, I've branched the Git repository to research this issue! The patch is against Release 0.08195 (commit 58a4b69c06f9b35876be3815eec598344f6a009d).
Subject: Re: [rt.cpan.org #70045] Oracle LOB/non-LOB handling is still messy!
Date: Fri, 05 Aug 2011 16:20:20 +0200
To: bug-DBIx-Class [...] rt.cpan.org
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 2.3k
Stanislaw Pusep via RT wrote: Show quoted text
> Queue: DBIx-Class > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=70045 > > > Em Sex Ago 05 10:00:52 2011, RIBASUSHI escreveu:
>> Stanislaw Pusep via RT wrote:
>>> Fri Aug 05 09:35:34 2011: Request 70045 was acted upon. >>> Transaction: Ticket created by SYP >>> Queue: DBIx-Class >>> Subject: Oracle LOB/non-LOB handling is still messy! >>> Broken in: 0.08189_01, 0.08190-TRIAL, 0.08190_01, 0.08190_02,
>> 0.08190_03, 0.08191, 0.08192, 0.08193, 0.08193_01, 0.08194, 0.08195
>>> Severity: Important >>> Owner: Nobody >>> Requestors: stas@sysd.org >>> Status: new >>> Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=70045 > >>> >>> >>> After the update to the newest version of DBIx::Class (with LOB
>> handling
>>> code rewritten), some of my code started running very, VERY slow.
>> Trace
>>> revealed the bottleneck: >>> >>> UPDATE LIVRARIA_1129 SET ATUALIZADO = :p1, DATA_ATUALIZACAO =
>> sysdate
>>> WHERE >>> ( ( UTL_RAW.CAST_TO_VARCHAR2(RAWTOHEX(DBMS_LOB.SUBSTR(ISBN13, 2000,
>> 1)))
>>> = >>> :p2 ) ) >>> >>> The funny thing is: I have *NO* LOB columns in this table! 'ISBN13'
>> is
>>> actually a TEXT one. So I tried to disable all the new LOB handling >>> code, and got this: >>> >>> ORA-00932: inconsistent datatypes: expected - got BLOB (DBD ERROR:
>> error
>>> possibly near <*> indicator at char 88 in 'UPDATE LIVRARIA_1129 SET >>> ATUALIZADO = :p1, DATA_ATUALIZACAO = sysdate WHERE ( ISBN13 = :<*>p2
>> )')
>>> Somehow, my TEXT is still interpreted as LOB. Now, if I at least
>> ensure
>>> that the RAWTOHEX transcoding only apply to non-plaintext LOB, my
>> code
>>> gets several hundreds times faster. >>> Not sure if it is a correct way of doing that, still. >>>
>> The attached patch - can you tell me what is it against? My tree does >> not contain f582b94. Basically I want to ensure you are still seeing >> this with 0.08195.
> > Sorry, I've branched the Git repository to research this issue! > The patch is against Release 0.08195 (commit > 58a4b69c06f9b35876be3815eec598344f6a009d).
Right, this is an acceptabl-ish workaround on your part, even though it does not address the real issue. The next version will include a switch for this, defaulting to off. We apparently still have too many problems in this codepath to leave it on by default. Check back on Monday. Cheers!
Download (untitled) / with headers
text/plain 2.5k
Em Sex Ago 05 10:20:33 2011, RIBASUSHI escreveu: Show quoted text
> Stanislaw Pusep via RT wrote:
> > Queue: DBIx-Class > > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=70045 > > > > > Em Sex Ago 05 10:00:52 2011, RIBASUSHI escreveu:
> >> Stanislaw Pusep via RT wrote:
> >>> Fri Aug 05 09:35:34 2011: Request 70045 was acted upon. > >>> Transaction: Ticket created by SYP > >>> Queue: DBIx-Class > >>> Subject: Oracle LOB/non-LOB handling is still messy! > >>> Broken in: 0.08189_01, 0.08190-TRIAL, 0.08190_01, 0.08190_02,
> >> 0.08190_03, 0.08191, 0.08192, 0.08193, 0.08193_01, 0.08194, 0.08195
> >>> Severity: Important > >>> Owner: Nobody > >>> Requestors: stas@sysd.org > >>> Status: new > >>> Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=70045 > > >>> > >>> > >>> After the update to the newest version of DBIx::Class (with LOB
> >> handling
> >>> code rewritten), some of my code started running very, VERY slow.
> >> Trace
> >>> revealed the bottleneck: > >>> > >>> UPDATE LIVRARIA_1129 SET ATUALIZADO = :p1, DATA_ATUALIZACAO =
> >> sysdate
> >>> WHERE > >>> ( ( UTL_RAW.CAST_TO_VARCHAR2(RAWTOHEX(DBMS_LOB.SUBSTR(ISBN13,
2000, Show quoted text
> >> 1)))
> >>> = > >>> :p2 ) ) > >>> > >>> The funny thing is: I have *NO* LOB columns in this table!
'ISBN13' Show quoted text
> >> is
> >>> actually a TEXT one. So I tried to disable all the new LOB
handling Show quoted text
> >>> code, and got this: > >>> > >>> ORA-00932: inconsistent datatypes: expected - got BLOB (DBD ERROR:
> >> error
> >>> possibly near <*> indicator at char 88 in 'UPDATE LIVRARIA_1129
SET Show quoted text
> >>> ATUALIZADO = :p1, DATA_ATUALIZACAO = sysdate WHERE ( ISBN13 = :
<*>p2 Show quoted text
> >> )')
> >>> Somehow, my TEXT is still interpreted as LOB. Now, if I at least
> >> ensure
> >>> that the RAWTOHEX transcoding only apply to non-plaintext LOB, my
> >> code
> >>> gets several hundreds times faster. > >>> Not sure if it is a correct way of doing that, still. > >>>
> >> The attached patch - can you tell me what is it against? My tree
does Show quoted text
> >> not contain f582b94. Basically I want to ensure you are still
seeing Show quoted text
> >> this with 0.08195.
> > > > Sorry, I've branched the Git repository to research this issue! > > The patch is against Release 0.08195 (commit > > 58a4b69c06f9b35876be3815eec598344f6a009d).
> > Right, this is an acceptabl-ish workaround on your part, even though
it Show quoted text
> does not address the real issue. The next version will include a
switch Show quoted text
> for this, defaulting to off. We apparently still have too many
problems Show quoted text
> in this codepath to leave it on by default. Check back on Monday. > > Cheers!
Great, thank you!
Download (untitled) / with headers
text/plain 280b
Hi stas, how do you have a TEXT data type in Oracle? Oracle does not have a TEXT data type as far as I know. The SQLT mapping for TEXT is CLOB, in which case our code works correctly. Why do you have data_type set to text and what is the CREATE TABLE statement for that table?
Download (untitled) / with headers
text/plain 824b
Em Dom Ago 07 06:23:56 2011, RKITOVER escreveu: Show quoted text
> Hi stas, > > how do you have a TEXT data type in Oracle? Oracle does not have a > TEXT data type as far as I > know. > > The SQLT mapping for TEXT is CLOB, in which case our code works > correctly. > > Why do you have data_type set to text and what is the CREATE TABLE > statement for that table?
Nice point! The CREATE TABLE has: "ISBN13" VARCHAR2(13 BYTE) NOT NULL ENABLE, While the schema definition has: "ISBN13", { data_type => "TEXT", is_nullable => 0, size => undef }, I patched the schema so data_type is VARCHAR2 and everything worked fine. I assume that the DBIx::Class Oracle LOB handling was upgraded to a more consistent definition, which broke our under-defined legacy code :) Thank you very much and sorry for the misreport!
Download (untitled) / with headers
text/plain 1.1k
On Mon Aug 08 10:34:34 2011, SYP wrote: Show quoted text
> Em Dom Ago 07 06:23:56 2011, RKITOVER escreveu:
> > Hi stas, > > > > how do you have a TEXT data type in Oracle? Oracle does not have a > > TEXT data type as far as I > > know. > > > > The SQLT mapping for TEXT is CLOB, in which case our code works > > correctly. > > > > Why do you have data_type set to text and what is the CREATE TABLE > > statement for that table?
> > Nice point! > The CREATE TABLE has: > "ISBN13" VARCHAR2(13 BYTE) NOT NULL ENABLE, > > While the schema definition has: > "ISBN13", > { data_type => "TEXT", is_nullable => 0, size => undef }, > > I patched the schema so data_type is VARCHAR2 and everything worked > fine. > I assume that the DBIx::Class Oracle LOB handling was upgraded to a more > consistent definition, which broke our under-defined legacy code :) > Thank you very much and sorry for the misreport!
Not exactly a misreport, but glad it worked for you at the end nevertheless. Next dbic version will see the lob handling turned off by default, with a storage flag to request it on per-query basis (the amount of work it needs to do is too fragile). Cheers!


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.