Oh the joys of Oracle character set support.
See the dbd_rebind_ph() code in dbdimp.c. Especially from the line
"csform = phs->csform;" onwards.
You can explicitly set the csform and csid using bind_param(..., { ... })
Have fun!
But do please write up what you find as a patch the the Oracle docs.
It's entirely possible that the code is doing the wrong thing.
Tim.
On Thu, Aug 23, 2007 at 04:20:16PM +0200, Peter J. Holzer wrote:
> While investigating a performance problem I found a curious behaviour.
>
> I have an oracle database (10.2.0.3) and matching client, DBI 1.50 and
> DBD::Oracle 1.19. Database and client are set to use the AL32UTF8 charset.
>
> Now I do a simple query on one of the tables:
> select id, name from datasets ds where name = ?
> The table has about 300k rows and an index on the name column.
>
> The name I'm searching for contains only ASCII characters, but depending
> on the source, the string may have the utf8 flag set.
> 36 sub datasets_by_name {
> 37 my ($dbh, $name) = @_;
> 38 my $t0 = time;
> 39 unless ($ENV{PREPARE_ONCE} && $sth) {
> 40 $sth = $dbh->prepare("select id, name from datasets ds where
> name = ?");
> 41 }
> 42 my $r = $dbh->selectall_hashref($sth, 'id', {}, $name);
> 43 my $t1 = time;
> 44 print $t1-$t0, "\n";
> 45 }
>
> The function datasets_by_name invokes the query above. If it prepares
> the query on each request, the timings look like this:
>
> 0.00154304504394531
> 0.00111699104309082
> 0.27887487411499
> 0.00116086006164551
> 0.00110697746276855
>
> I.e., searching for a byte string takes about 1 millisecond and
> searching for a utf8 string takes about 280 milliseconds! Oracle
> enterprise manager tells me that in the slow case the index is ignored
> and a full table scan is performed.
>
> But wait, it gets even better. If the statement handle for the query is
> cached and reused, the timings look like this:
>
> 0.00197005271911621
> 0.000653982162475586
> 0.280965089797974
> 0.2483971118927
> 0.250221967697144
>
> I.e., the information that the index can't be used (which I don't
> understand in the first place) seems to be "sticky".
