Index suggestion from the access advisor

Test case : create table t(x varchar2(8) primary key, y varchar2(30)); insert into t(x,y) select to_char(rownum,’FM00000000′), object_name from all_objects where rownum select * from t where x=00000001; X Y ——– —————————— 00000001 CON$ He received the expected data. Let’s check his plan SQL> explain plan for select * from t where x=00000001; SQL> select *… Continue reading Index suggestion from the access advisor

Monitoring details on your explain plan

We know how to generate an explain plan. SET AUTOT ON, the good old ambulance in Toad (which is no longer an ambulance those days), the explain plan button in SQL Developer or simply EXPLAIN PLAN create table t1 partition by hash(object_id) partitions 16 as select * from dba_objects; exec dbms_stats.gather_table_stats(user, ‘T1’) explain plan for… Continue reading Monitoring details on your explain plan

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 Production on Fr… Continue reading American = fast

scp tuning

I twitted yesterday : laurentsch copying 1TB over ssh sucks. How do you fastcopy in Unix without installing Software and without root privilege? I got plenty of expert answers. I have not gone to far in recompile ssh and I did not try plain ftp. Ok, let’s try first to transfer 10 files of 100M… Continue reading scp tuning

make count(*) faster

I just install Oracle Enterprise Linux on my new notebook. I wanted to check how far could I improve the performance of a count(*) SQL> drop table big_emp; table big_emp dropped. 258ms elapsed SQL> create table big_emp as with l as(select 1 from dual connect by level alter table big_emp add primary key(empno) table big_emp… Continue reading make count(*) faster