Freitag, 19. September 2008

simple tuning using decode

Currently I have to assist in tuning of a really time critic application (in comparison to other applications/projects I have to support). There I found 2 statements called within an PL/SQL procedure:
  1. select count(id)
    from berx1
    where val=1 and type is null
  2. select count(id)
    from berx1
    where val=1 and type is null and dim= 2
(The statements are reduced to test-cases, my test-table is
create table berx1 (id number, val number, type number, dim number);

insert into berx1 values (1, 1, NULL, 1);
insert into berx1 values (2, 1, 1, 2);
insert into berx1 values (3, 1, NULL, 3);
insert into berx1 values (4, 1, 1, 1);
insert into berx1 values (5, 2, NULL, 2);
insert into berx1 values (6, 2, 1, 3);
insert into berx1 values (7, 2, NULL, 1);
insert into berx1 values (8, 2, 1, 2);
insert into berx1 values (9, 3, NULL, 3);
insert into berx1 values (10, 3, 1, 1);
insert into berx1 values (11, 3, NULL, 2);
insert into berx1 values (12, 3, 1, 3);
insert into berx1 values (13, 4, NULL, 1);
insert into berx1 values (14, 4, 1, 2);
insert into berx1 values (15, 4, NULL, 3);
insert into berx1 values (16, 4, 1, 1);
insert into berx1 values (17, 5, NULL, 2);
insert into berx1 values (18, 5, 1, 3);
)
Even both of them are very fast (with an index on val), As we have to save every slice of time we can, I merged the statements into one:
SELECT count(id) count
, NVL( SUM( DECODE(dim, 2 -- if 'dim' is 2
, 1 -- add 1 to sum
, 0) -- otherwise add nothing
), 0
) count_dim
FROM berx1 where val=1 and type is null;
What did I expect so far? (not tested into detail!)
  • less context-switches between PL/SQL and SQL
  • less soft parses / library cache lookups
  • less activity in the buffer cache
In fact, at the end of the optimization I hope I can modify the program logic, maybe we can save a lot more time on other places and also get rid of this statement. But this is still matter of investigation with the application-designers.
Kommentar veröffentlichen