Oracle 11. Atualizando o campo BLOB. Leitura sequencial do arquivo Db inadequadamente lenta?

Question

Eu e o Oracle (11 Enterprise) esquema com uma tabela

 CREATE TABLE USER.WSP_BUNDLE ( 
    NODE_ID     RAW(16) NOT NULL,
    BUNDLE_DATA BLOB NOT NULL 
    );
CREATE UNIQUE INDEX USER.WSP_BUNDLE_IDX ON USER.WSP_BUNDLE(NODE_ID);

e 3'rd party library (Java 6, JDBC - mais recente driver jdbc) que o manipulam.

O criador de perfis Oracle (tkprof) mostra que cerca de 50% do tempo geral a lib executa tal declaração :

update WSP_BUNDLE set BUNDLE_DATA = :1 where NODE_ID = :2

Dados do TKProf

Plan Hash: 4085453680
update WSP_BUNDLE set BUNDLE_DATA = :1  where NODE_ID = :2 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      264      0.00       0.00          0          0          0           0
Execute    400     30.59     382.88     141451    1623163    3233827         400
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      664     30.59     382.88     141451    1623163    3233827         400

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 87  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  WSP_BUNDLE (cr=8753 pr=707 pw=706 time=0 us)
      1   INDEX UNIQUE SCAN WSP_BUNDLE_IDX (cr=3 pr=0 pw=0 time=0 us cost=2 size=104 card=1)(object id 75730)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                    141251        5.53        328.04
  direct path write                             402        0.09          0.43
  SQL*Net more data from client              142158        1.04         11.89
  direct path read                              200        0.03          0.07
  Disk file operations I/O                        1        0.00          0.00
  SQL*Net message to client                     400        0.00          0.00
  SQL*Net message from client                   400        0.29          0.50
  log file switch (private strand flush incomplete)
                                                  5        0.05          0.23
  asynch descriptor resize                   139723        7.46          8.57
  buffer busy waits                               2        0.00          0.00
  log file switch (checkpoint incomplete)         3        0.18          0.27
  log file sync                                   2        0.00          0.00

Alguém poderia me explicar o que está acontecendo? Por que a atualização é tão lenta?

A tabela WSP_BUNDLE contém cerca de 200 + k linhas. Ao mesmo tempo eu tenho outras tabelas no mesmo esquema com blobs (CLOB para ser mais concreto) que contém 600 + k linhas onde atualizações semelhantes funcionam corretamente.

    

Answer 1

No Oracle, o LOB (incluindo BLOB) é armazenado como:

LOB in-the-table
• - se o LOB for menor que 3900 bytes, ele pode ser armazenado dentro da linha da tabela; por padrão, isso está ativado, a menos que você especifique DISABLE STORAGE IN ROW
• LOB normal - armazenado em um segmento separado, fora da tabela, você pode até colocá-lo em outro espaço de tabela; Para estes:
  • um mínimo de bytes CHUNK são alocados e inteiramente redefinidos (mesmo que o LOB tenha apenas 1 byte)
  • há um índice intermediário interno atrás de uma coluna LOB, que fica controversa em atualizações e pode praticamente serializá-las
  • o acesso é de vários níveis e, portanto, relativamente mais lento
  • com a opção NOCACHE, as waiters são "direct path read" - o padrão
  • com a opção CACHE, os garçons são "leitura sequencial do arquivo db"
    • em que CACHE_SIZE_THRESHOLD não é levado em consideração, portanto, um grande LOB pode desperdiçar seu cache

Portanto, se seus LOBs forem maiores que 4 kB, eles ficarão relativamente lentos, e isso pode simplesmente ser o seu caso. Eu examinaria os tamanhos.

Eu examinaria USER_LOBS (ou DBA_LOBS) para ver como as colunas LOB "boas" e "lentas" diferem em suas definições.

O ID 66431.1 da Metalink descreve isso e pode ser interessante para você, se você tiver acesso lá.

UPDATE : Fascinado pela quantidade selvagem aparentemente inexplicável de "leitura sequencial do arquivo db", eu fiz um pouco de pesquisa e descobri que coisas estranhas podem acontecer com o índice de lob após as DELETEs em massa . Apenas um palpite, mas parece muito semelhante ao seu caso. Se for isso, eu recompilar totalmente a coluna de lob . (Mover uma coluna de lob também pode reconstruir o índice de lob - não tenho certeza).