Veldig mange som jobber med SQL tuning og eksikveringsplaner (explain plan) kjenner ikke disse predikat begrepene. Jeg må innrømme at jeg har tunet SQL-er i mange år uten å ta hensyn til betydningen av disse predikatene. Dette blir som å kjøre (race)bil uten å vite at man har et 5 gir. På et kurs med Jonathan Lewis for et par år tilbake ble jeg første gang introdusert for filter og access predikatene.
Mange som jobber med SQL har kanskje aldri hørt disse begrepene. La oss derfor se kort på et lite explain plan eksempel:
SQL> create table t2 as select t1.* from all_objects t1, all_objects t2 where rownum<=1000000;
Tabell opprettet.
SQL> update t2 set object_id=rownum;
1000000 rader oppdatert.
SQL> commit;
Bekreftelse fullført.
SQL> alter table t2 add constraint t2_pk primary key (object_id);
Tabell endret.
SQL> create index t_own_type_idx on t2 (owner, object_type);
Indeks opprettet.
SQL> select * from t2 where owner='PUBLIC' and object_type='SYNONYM' ;
476046 rader valgt.
Utf?ringsplan
----------------------------------------------------------
Plan hash value: 3050712455
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16949 | 1605K| 360 (1)| 00:00:05 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 16949 | 1605K| 360 (1)| 00:00:05 |
|* 2 | INDEX RANGE SCAN | T2_OWN_TYPE_IDX | 16949 | | 63 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SYNONYM')
Statistikk
----------------------------------------------------------
0 recursive calls
0 db block gets
10521 consistent gets
0 physical reads
0 redo size
49668929 bytes sent via SQL*Net to client
10996 bytes received via SQL*Net from client
954 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
476046 rows processed
I dette eksemplet har jeg først laget en test tabell med 1 million rader basert på all_objects tabellen, samt oppretter en indeks på owner og object_type.
Så kjører jeg en spørring der jeg begrenserer radutvalget basert på owner og object_type. Her ser vi at vi får et access predikativ. Dette betyr at filtreringen på owner og object_type skjer i det oracle henter rader fra indeksen.
La oss nå se på et eksempel der oracle benytter et filter predikativ:
SQL> select * from t where owner='PUBLIC' and object_type='SYNONYM' and object_name='T2';
Ingen rader valgt
Utf?ringsplan
----------------------------------------------------------
Plan hash value: 3050712455
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 360 (1)| 00:00:05 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 97 | 360 (1)| 00:00:05 |
|* 2 | INDEX RANGE SCAN | T2_OWN_TYPE_IDX | 16949 | | 63 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='T2')
2 - access("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SYNONYM')
Statistikk
----------------------------------------------------------
1 recursive calls
0 db block gets
8627 consistent gets
0 physical reads
0 redo size
1351 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
Nå ser vi at oracle først benytter det samme access predikativet som i sted, og filtrer vekk rader som ikke har owner=’PUBLIC’ og object_type=’SYNONYM’:
2 - access("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SYNONYM')
Men i og med at indeksen vår ikke inneholder object_name kolonnen, kan oracle ikke filtrere vekk rader som ikke har navn “T2” ved access mot indeksen. Dermed må oracle utsette denne filtreringen i et eget filter predikat:
1 - filter("OBJECT_NAME"='T2')
Hvis vi ser på tallene her, ser vi at den første spørringen henter ut 476046 rader ved å utføre 10521 consistent gets (logiske les). I den andre spørringen hentes 0 rader, men oracle må fremdeles utføre 8627 consistent gets. Dette fordi oracle først må hente ut alle rader med owner=’PUBLIC’ og object_type=’SYNONYM’. Det betyr at veldig mange rader må først hentes ut, for så å bli filtrert vekk. I typisk OLTP systemer ser vi derfor at slike filter predikater svært sjeldent er å foretrekke. Når det gjelder databarehus og rapportings systemer er dette nødvendigvis ikke riktig (Quiz: Hvorfor ikke?).
Hva skjer hvis vi lager indeksen på nytt inklusiv object_name kolonnen:
SQL> drop index t2_own_type_idx;
Indeks fjernet.
SQL> create index t2_own_type_name_idx on t2 (owner, object_type, object_name);
Indeks opprettet.
SQL> analyze index t2_own_type_name_idx compute statistics;
Indeks analysert.
SQL> select * from t2 where owner='PUBLIC' and object_type='SYNONYM' and object_name='T2';
Ingen rader valgt
Utf?ringsplan
----------------------------------------------------------
Plan hash value: 227535362
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1940 | 23 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 20 | 1940 | 23 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T2_OWN_TYPE_NAME_IDX | 20 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SYNONYM' AND "OBJECT_NAME"='T2')
Statistikk
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1351 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
Her ser vi at vi har fått fjernet filter predikativet. I dette tilfellet gav dette en vesentlig forskjell.
Spørringen henter fremdeles ut 0 rader, mens vi nå kun har 3 consistent gets.
Access predikater vil derfor alltid hente ut kun de rader som vi faktisk etterspør, mens filter predikater vil hente ut alle rader fra rad kilden (access source using access predicates). Husk derfor å alltid se på access og filter predikater når vi tuner SQL-er. Disse kan gi oss nyttige hint med henhold til eventuelt nye indekser.
Post a Comment