Re: Wrong execution plan
Date: Wed, 10 Jun 2009 19:42:23 +0200
Message-Id: <6E95558E-DFF1-4448-B07A-A48CF308B869_at_gmail.com>
Joerg,
There are sometimes NLS_SORT issues, even if you are not beware of them.
see this testcase:
drop table ue;
create table ue (no number, na varchar2(2));
CREATE UNIQUE INDEX PK_ue ON ue (na);
alter table ue add constraint pk_ue primary key (na);
insert into ue values (58, 'Xx');
insert into ue values (75, 'uu');
commit;
set autotrace on
alter session set nls_sort='binary';
select /*+ INDEX(UE) */ na, dump(na, 16) d from ue order by na;
alter session set nls_sort='german';
select /*+ INDEX(UE) */ na, dump(na, 16) d from ue order by na;
the relevant output:
nls_sort='binary';
NA D
-- ----------------------------------------Xx Typ=1 Len=2: 58,78
uu Typ=1 Len=2: 75,75
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 6 | 1 (0)| 00:00:01 | | 1 | INDEX FULL SCAN | PK_UE | 2 | 6 | 1 (0)|00:00:01 |
#####################################################
nls_sort='german';
NA D
-- ----------------------------------------uu Typ=1 Len=2: 75,75
Xx Typ=1 Len=2: 58,78
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 6 | 2 (50)| 00:00:01 | | 1 | SORT ORDER BY | | 2 | 6 | 2 (50)| 00:00:01 | | 2 | INDEX FULL SCAN| PK_UE | 2 | 6 | 1 (0)|00:00:01 |
as you can see, the execution plan differs (and if there are many rows , the SORT ORDER BY might be costy).
I'm not sure if you hit such an issue, just wanted to show the NLS_ settings can be of interrest, even without conversion.
hth,
Martin
-- http://www.freelists.org/webpage/oracle-l Am 10.06.2009 um 16:57 schrieb Joerg Jost:Received on Wed Jun 10 2009 - 12:42:23 CDT
> Am Mittwoch, den 10.06.2009, 15:26 +0100 schrieb Nigel Thomas:
>> Jorg >> >> Just two possibilities to quickly rule out before all the more >> complex >> possibilities are debated: >> * check that the datatypes of the PL/SQL variables are all >> appropriate and no implicit datatype conversions are taking >> place - I expect pos_num is a NUMBER; is l_tmp_num1 also? >> * check that the there's no NLS conversion issues (language or >> character set) >> >
> Hi Nigel,
>
> your first point, the variables are declared with po.pos_num%type. Of
> course this is also right for the other ones. So this should be safe.
>
> Your second, i am not absolutely sure to get the right point. The
> database runs in NLS_CS we8iso8859p15 with language german_germany.
>
> There is no conversion in this procedure, as far as i can see. There
> is
> no to_char, to_number or something similar.
>
> Thx
>
> Jörg
>
> --
> You can have it: Fast, Right or Cheap, pick 2 of the 3.
> Fast + Right is Expensive
> Fast + Cheap will be incorrect.
> Right + Cheap will take a while.
> --
> http://www.freelists.org/webpage/oracle-l
> >
-- http://www.freelists.org/webpage/oracle-l
- application/pkcs7-signature attachment: smime.p7s