Full Table Scans – Entenda como o Oracle lê tabelas e segmentos

Full Table Scans – Blocos e Segmentos

Já alguma vez pensaram como é que o Oracle sabe em que segmento está a vossa tabela, quantos extents tem, quandos blocos serão lidos num full table scan? Com certeza que muitos dirão que número de blocos lidos é o número total de blocos constituintes da tabela (segmento em que está) e isso pode ser consultado na user_segments ou na dba_segments, ora a resposta é errada e já vos mostro a seguir se lerem atentamente o exemplo.

Para já criaremos um tablespace pequeno em autoexend:

SQL> create tablespace ts_fts datafile '/home/oracle/app/oracle/oradata/testSID/ts_fts.dbf' size 100m autoextend on extent management local uniform size 64k;

Tablespace created.

Criaremos em seguida uma tabela no tablespace criado anteriormente com a informação proveniente da tabela dba_source.

SQL> create table tb_fts1 tablespace ts_fts as  select * from dba_source;  
Table created.

Pronto, para já temos as condições necessárias à prova. A query abaixo, é uma query à dba_segments que para além de muita outra informação tem alguma que vamos necessitar.

Por exemplo temos o número de extents da tabela, o número de blocos, o tamanho e mais importante que tudo as colunas HEADER_FILE e HEADER_BLOCK. Estas colunas são responsável por guardar o ficheiro e o bloco que contem as informações para cada segmento criado (por ex tabela, indice) e permite ao Oracle saber onde começa o segmento, neste caso específico representa a tabela criada tb_fts1.

SQL>  select header_file, header_block,extents,blocks, (bytes/1024) from dba_segments where segment_name = UPPER('tb_fts1');

HEADER_FILE HEADER_BLOCK    EXTENTS     BLOCKS (BYTES/1024)
----------- ------------ ---------- ---------- ------------
          8          130       1323      10584        84672

Sabendo já em que ficheiro e em que bloco particularmente começa o segmento que estamos a analisar, vamos fazer um dump com a seguinte sintaxe:

SQL> ALTER SYSTEM DUMP DATAFILE 8 BLOCK 130;  

System altered.

Criará um ficheiro .trc no vossa directoria de dump (user_dump_dest), naveguem num editor até a uma parte semelhante a esta:

-----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1323   #blocks: 10584
                  last map  0x020019f8  #maps: 2      offset: 2716
      Highwater::  0x020029d5  ext#: 1322   blk#: 5      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 10581
  mapblk  0x020019f8  offset: 507
                   Unlocked

A informação que aqui temos é importante, mas não é nova alguma dela. Podemos perceber  facilmente o número de extents e blocos (tal e qual vimos na dba_segments), mas a parte que importa realçar é a que refere “#blocks below”. Significa o número de blocos lidos (partindo do principio que não há blocos em cache) abaixo da HWM (High Water Mark) e portanto o número de blocos lidos _CASO_ o Oracle decida por um Full Table Scan (direct path read, neste caso).

Portanto importa reter que a tabela é composta por 10584 blocos, mas apenas 10581 serão lidos aquando de um Full Table Scan. Isto acontece essencialmente pois existem blocos não formatados que são criados acima da HWM e como tal são descartados na leitura.

Outra coisa importante de reter é que o Oracle faz um single block read, para ler o bloco que contém os HEADER do segmento a cada query efectuada, para juntamente com esta informação, a informação referente à cache (e a quantos blocos destes estão) tomar a decisão de um FTS (direct path read).

O exemplo a seguir espelha a teoria, demonstra um trace 10046 level 12 (ver google se tiverem dúvidas acerca disto) e um COUNT na tabela.

SQL> alter session set tracefile_identifier=FTS_DPR;
Session altered.

SQL> exec dbms_stats.gather_table_stats('SYS', 'TB_FTS1', CASCADE=>TRUE);
PL/SQL procedure successfully completed.

SQl> ALTER SYSTEM FLUSH BUFFER_CACHE;
SQL> alter session set events='10046 trace name context forever, level 12';
Session altered.

SQL> select count(1) from tb_fts1;

  COUNT(1)
----------
    623146

SQL>  alter session set events='10046 trace name context off';
Session altered.

Como disse acima, esperamos perceber que o Oracle tenha feita um single block read no HEADER do segmento. Vamos tentar encontra-lo no trace.

PARSING IN CURSOR #19 len=28 dep=0 uid=0 oct=3 lid=0 tim=1313842019152251 hv=3471431694 ad='8c45c880' sqlid='4nzp1fm7fmq0f'
select count(1) from tb_fts1
END OF STMT
PARSE #19:c=1000,e=1075,p=4,cr=4,cu=0,mis=0,r=0,dep=0,og=1,plh=1126436865,tim=1313842019152250
EXEC #19:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1126436865,tim=1313842019152589
WAIT #19: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=18 tim=1313842019152623
WAIT #19: nam='db file sequential read' ela= 15 file#=8 block#=130 blocks=1 obj#=73718 tim=1313842019152707
WAIT #19: nam='direct path read' ela= 53 file number=8 first dba=131 block cnt=13 obj#=73718 tim=1313842019152925
WAIT #19: nam='direct path read' ela= 47 file number=8 first dba=145 block cnt=15 obj#=73718 tim=1313842019153006
WAIT #19: nam='direct path read' ela= 36 file number=8 first dba=161 block cnt=15 obj#=73718 tim=1313842019153180
...

Como podem ver, o primeiro db file sequencial read é feito ao FILE#=8, BLOCK#=130 que corresponde ao HEADER do segmento da tabela que estamos a analisar. Como neste caso o CBO decidiu por um FTS (direct path read).

Artigos originais em: www.lcmarques.com

Abraço