American = fast

I had the incredible behavior of having the same query running 10x faster in sqlplus depending on two different PCs. After analysis, I realised the super fast pc was American… at least in nls_lang

Slow client: PC setup in German, NLS_LANG is set to GERMAN_SWITZERLAND.WE8MSWIN1252

C:\>set NLS_LANG=GERMAN_SWITZERLAND.WE8MSWIN1252

C:\>sqlplus scott/tiger@db01

SQL*Plus: Release 11.2.0.3.0 Production on Fr Jul 6 10:30:25 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Verbunden mit:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set autot on exp
SQL> select job,count(*) FROM emp group BY job order by job;

JOB COUNT(*)
--------- ----------
ANALYST 2
CLERK 4
MANAGER 3
PRESIDENT 1
SALESMAN 4

Ausführungsplan
----------------------------------------------------------
Plan hash value: 2389703825

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 40 | 5 (40)| 00:00:01 |
| 1 | SORT ORDER BY | | 5 | 40 | 5 (40)| 00:00:01 |
| 2 | HASH GROUP BY | | 5 | 40 | 5 (40)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 112 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

Now let’s try with the PC setup with american_america

C:\>set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

C:\>sqlplus scott/tiger@db01

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 6 10:31:57 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set autot on exp
SQL> select job,count(*) FROM emp group BY job order by job;

JOB COUNT(*)
--------- ----------
ANALYST 2
CLERK 4
MANAGER 3
PRESIDENT 1
SALESMAN 4

Execution Plan
----------------------------------------------------------
Plan hash value: 637087546

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 40 | 4 (25)| 00:00:01 |
| 1 | SORT GROUP BY | | 5 | 40 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 112 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

The “cost” and “plan” changed. Why this? Maybe an optimizer bug, but it is related to NLS_SORT, which is GERMAN for nls_lang=GERMAN_SWITZERLAND and BINARY for nls_lang=AMERICAN_AMERICA.

Binary!

6 thoughts on “American = fast”

  1. Joaquin Gonzalez

    Hi Laurent,

    I think it’s related with nls_sort parameter being differente to binary. When you change language part of nls_lang to german, then nls_sort also changes.

    Joaquín González.

  2. Martin Berger

    Laurent,
    here my testcase on a DB with UTF character set:

    create table bsort (str varchar2(10));

    insert into bsort values (‘s’);
    insert into bsort values (‘ß’);
    insert into bsort values (‘ss’);
    insert into bsort values (‘r’);
    insert into bsort values (‘t’);

    commit;

    select str, dump(str,16) d from bsort;

    alter session set NLS_SORT=’BINARY’;
    select str, count(*) from bsort group by str order by str;

    STR COUNT(*)
    ———- ———-
    r 1
    s 1
    ss 1
    t 1
    ß 1

    alter session set NLS_SORT=’GERMAN’;
    select str, count(*) from bsort group by str order by str ;

    STR COUNT(*)
    ———- ———-
    r 1
    s 1
    ß 1
    ss 1
    t 1

    I’d say SORT GROUP BY is doing an implicit binary sort, whereas linguistic sorts needs a real SORT ORDER BY?

    Martin

  3. Laurent Schneider Post author

    Thanks for this good example with ß. For some obscure reason, ß is ss in GERMAN and sz in XSWISS.

    Usually NLS_SORT does not make such a big difference in a statement like “select * from t order by x”, but with a completly different plan the difference may be impressive (user reported 10 seconds instead of 1min30 for a complex query)

  4. Martin Berger

    Maybe this might give even more fun?
    select str, count(*) from bsort
    group by str order by NLSSORT(str, ‘NLS_SORT = binary’);
    select str, count(*) from bsort
    group by str order by NLSSORT(str, ‘NLS_SORT = German’);

    In my test even NLSSORT(str, ‘NLS_SORT = binary’) led to
    HASH GROUP BY.
    So if I am really exact, it’s more expensive as if I’m AMERICAN, and don’t care?

  5. joel garry

    “Please note that different sort settings for client and database instance would also result in low query performance due to resorting.” Noted in doc Bug 10449800

    Might be worth tracing to verify.

Comments are closed.