Tuning em tabelas de 1 bilhão de linhas

[dr_gori]

Vou colocar aqui um exemplo de query que eu estou tentando otimizar.
Abaixo, as tabelas relacionadas e a quantidade de linhas de cada uma:

Selecionar tudo

SQL> SELECT table_name, num_rows, partitioned, blocks, avg_row_len, compression
  2  FROM all_tables
  3  WHERE table_name IN (
  4    'GL_JE_HEADERS'
  5  , 'GL_JE_BATCHES'
  6  , 'GL_LEDGERS'
  7  , 'GL_JE_LINES'
  8  , 'GL_LOOKUPS'
  9  , 'GL_CODE_COMBINATIONS'
 10  , 'GL_IMPORT_REFERENCES'
 11  )
 12  /
 
TABLE_NAME                       NUM_ROWS PARTITIONED     BLOCKS AVG_ROW_LEN COMPRESSION
------------------------------ ---------- ----------- ---------- ----------- -----------
GL_JE_BATCHES                     1881900 NO               67374         248 DISABLED
GL_JE_HEADERS                     4358320 NO              203131         293 DISABLED
GL_CODE_COMBINATIONS              9087650 NO              223193         114 DISABLED
GL_IMPORT_REFERENCES           1044946030 NO            30487669         203 DISABLED
GL_JE_LINES                    1132691380 YES           22311097         140 
GL_LEDGERS                            206 NO                  13         299 DISABLED
 
6 rows selected
 
SQL> 

Repare que eu tenho 2 tabelas com mais de 1 bilhão de linhas.
Uma delas, tem Partition pelo campo PERIOD_NAME. (ou seja, cada MÊS está em uma partição).

Minha query, é assim:

Selecionar tudo

SELECT gled.name ledger_name
            , gjb.name batch_name
            , gjh.name journal_name
            , gjh.doc_sequence_value document_number
            , gjc.user_je_category_name
            , gjh.default_effective_date accouting_date
            , gl.meaning status
            , gjh.currency_code
            , gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' || gcc.segment4 || '.' || gcc.segment5 || '.' || gcc.segment6 || '.' || gcc.segment7 || '.' || gcc.segment8 aff
            , gjl.entered_dr gl_dr_entered
            , gjl.entered_cr gl_cr_entered
            , gjl.accounted_dr gl_dr_accounted
            , gjl.accounted_cr gl_cr_accounted
            , gir.reference_6
            , gir.reference_2
            , gjl.entered_dr
            , gjl.entered_cr
         FROM gl_je_headers gjh
            , gl_je_batches gjb
            , gl_je_sources_vl gjs
            , gl_je_categories_vl gjc
            , gl_ledgers gled
            , gl_je_lines gjl
            , gl_lookups gl
            , gl_code_combinations gcc
            , gl_import_references gir
        WHERE gjh.period_name = &P_PERIOD
          AND gjs.je_source_name = gjh.je_source
          AND gjc.je_category_name = gjh.je_category
          AND gled.ledger_id = gjh.ledger_id
          AND gjl.je_header_id = gjh.je_header_id
          AND gjb.je_batch_id = gjh.je_batch_id
          AND gl.lookup_type = 'BATCH_STATUS'
          AND gl.lookup_code = gjh.status
          AND gcc.code_combination_id = gjl.code_combination_id
          AND gir.je_header_id = gjl.je_header_id
          AND gir.je_line_num = gjl.je_line_num
          AND gir.reference_3 = 'GLJE'

Onde o parâmetro é justamente o mês. (&P_PERIOD)

O plano de execução está da seguinte forma: (repare que todos índices estão sendo usados perfeitamente).
A única coisa que eu observo são os Nested Loops (que são necessários), mas as tabelas são muito grandes.
Alguém já passou por uma situação assim? Na minha opinião, o plano de execução está bom...

Selecionar tudo

SELECT STATEMENT  
 NESTED LOOPS  
  NESTED LOOPS  
   NESTED LOOPS  
    NESTED LOOPS  
     NESTED LOOPS  
      NESTED LOOPS  
       NESTED LOOPS  
        NESTED LOOPS  
         NESTED LOOPS  
          TABLE ACCESS BY INDEX ROWID FND_LOOKUP_VALUES
           INDEX RANGE SCAN FND_LOOKUP_VALUES_U1
          TABLE ACCESS BY INDEX ROWID GL_JE_HEADERS
           INDEX RANGE SCAN GL_JE_HEADERS_N2
         TABLE ACCESS BY GLOBAL INDEX ROWID GL_JE_LINES
          INDEX RANGE SCAN GL_JE_LINES_U1
        TABLE ACCESS BY INDEX ROWID GL_IMPORT_REFERENCES
         INDEX RANGE SCAN GL_IMPORT_REFERENCES_N1
       INDEX UNIQUE SCAN GL_JE_SOURCES_TL_U1
      TABLE ACCESS BY INDEX ROWID GL_LEDGERS
       INDEX UNIQUE SCAN GL_LEDGERS_U2
     TABLE ACCESS BY INDEX ROWID GL_JE_CATEGORIES_TL
      INDEX UNIQUE SCAN GL_JE_CATEGORIES_TL_U1
    TABLE ACCESS BY INDEX ROWID GL_JE_BATCHES
     INDEX UNIQUE SCAN GL_JE_BATCHES_U1
   INDEX UNIQUE SCAN GL_CODE_COMBINATIONS_U1
  TABLE ACCESS BY INDEX ROWID GL_CODE_COMBINATIONS

Aqui tem mais detalhes: (como o custo, cardinalidade, etc)

Qualquer ajuda é bem vinda!

[dr_gori]

Essa é uma idéia que eu tive:
Colocar as tabelas grandes "pra fora", e rodando as pequenas num SubSelect (a).
O custo diminuiu pra 170.000. (antes estava 3.5 milhões o custo)

Selecionar tudo

 SELECT A.*
            , gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' || gcc.segment4 || '.' || gcc.segment5 || '.' || gcc.segment6 || '.' || gcc.segment7 || '.' || gcc.segment8 aff
            , gjl.entered_dr gl_dr_entered
            , gjl.entered_cr gl_cr_entered
            , gjl.accounted_dr gl_dr_accounted
            , gjl.accounted_cr gl_cr_accounted
            , gir.reference_6
            , gir.reference_2
            , gjl.entered_dr
            , gjl.entered_cr
       FROM (
             SELECT gjh.je_header_id 
                  , gled.name ledger_name
                  , gjb.name batch_name
                  , gjh.name journal_name
                  , gjh.doc_sequence_value document_number
                  , gjc.user_je_category_name
                  , gjh.default_effective_date accouting_date
                  , gl.meaning status
                  , gjh.currency_code
               FROM gl_je_headers gjh
                  , gl_je_batches gjb
                  , gl_je_sources_vl gjs
                  , gl_je_categories_vl gjc
                  , gl_ledgers gled
                  , gl_lookups gl
            WHERE gjh.period_name = :P_PERIOD
              AND gjs.je_source_name = gjh.je_source
              AND gjc.je_category_name = gjh.je_category
              AND gled.ledger_id = gjh.ledger_id
              AND gjb.je_batch_id = gjh.je_batch_id
              AND gl.lookup_type = 'BATCH_STATUS'
              AND gl.lookup_code = gjh.status
            ) a
          , gl_code_combinations gcc
          , gl_je_lines gjl
          , gl_import_references gir
     WHERE    gir.je_header_id = a.je_header_id
          AND gir.je_line_num = gjl.je_line_num
          AND gir.reference_3 = 'GLJE'          AND gcc.code_combination_id = gjl.code_combination_id
          AND gjl.je_header_id = a.je_header_id

[fbifabio]

@dr_gori,

A tabela GL_JE_HEADERS tem aproxidamente 1.5 GB e no plano de execução ela é a tabela que tem mais linhas sendo acessadas, particione ela também que é quase certeza de que você terá um bom ganho de desempenho. Veja o plano de execução e mostre aqui p/ nós.

Depois mude os parâmetros do otimizador abaixo, veja se melhora alguma coisa e poste o novo plano de novo aqui p/ nós:

Selecionar tudo

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 50;
ALTER SESSION SET OPTIMIZER_INDEX_CACHING = 100;

Se ainda não melhorou suficientemente o desempenho da query, outra coisa q você poderia tentar fazer p/ é usar um Hash Join na tabela gl_je_headers através do uso do hint USE_HASH , após o SELECT , como no exemplo abaixo:

Selecionar tudo

SELECT /*+ USE_HASH (gjh gjl) */

No exemplo acima, liguei gjh e gjl, mas o ideal é "tentar" forçar o Hash join nas tabelas que acessam mais linhas (ver coluna cardinalidade do plano de execução).

Por fim, compactar as tabelas grandes tbém daria um ganho de desempenho no SELECT, porém tenha ciência de que isso irá piorar o desempenho de instrução UPDATE e INSERT.


[]s

[dr_gori]

Esqueci de dizer uma coisa:
* Essas tabelas são CORE do EBS. Ou seja, a gente não tem autonomia pra alterar elas em nenhum aspecto. (nem criar índice, nem criar sequer um COMMENT numa coluna Muito menos compactar, criar partition, etc). Apenas se a Oracle disser que PODE!

Então isso reduz basicamente as opções a HINTS e reescrever a query.

Tentei o USE_HASH, sem sucesso. O custo aumentou absurdamente.
Sobre os ALTER SESSION, eu teria que colocar no programa pra ver se isso daria uma diminuição.
Eu liberei a minha alteração (no post anterior), que reduziu 20 vezes o custo.
Esta alteração está sendo testada no momento.

Caso ainda coninue ruim, farei o teste com os ALTER SESSION e colocarei aqui os resultados.
Obrigado pelas dicas!

[fbifabio]

Já que vai ser difícil executar os comandos ALTER SESSION, tente apenas incluir esse hint e veja se melhora algo, ok?

Selecionar tudo

/*+ FIRST_ROWS *

/

Uma outra opção p/ tentar melhorar o desempenho seria substituir os acessos INDEX RANGE SCAN por acesso FTS com paralelismo. Para isso acrescente esse hint somente naquelas tabelas q você identificar que foi realizado INDEX RANGE SCAN dentro do plano de execução, ok?

Selecionar tudo

 /*+ PARALLEL (NOME_TABELA, DEFAULT) */

[dr_gori]

FIRST_ROWS piorou bastante o custo. (Já tinha tentado)
O uso do PARALLEL, eu costumo apenas colocar quando eu sou obrigado a ter um FULL TABLE SCAN. (dai, uso o paralelismo). Costumo não abusar dele.

FELIZMENTE, minha alteração do sub-select deu certo! O usuário já testou e aprovou a alteração. O custo diminuiu 20 vezes. (de mais de 24 horas pra alguns minutos).

Obrigado Fabio pelas dicas. Eu já fiz muito tuning em queries, mas esse pra mim foi um desafio por causa do numero enorme de linhas E também porque o plano de execução não tinha nada muito ruim. É botando a mão nesses casos "reais" que a gente mais aprende.

Valeu !!!
Fica o tópico pra futura consulta

[DanielNN]

Opa Thomas e Fábio,

Sobre criação de índices. Tem certeza de que não se pode criar índices próprios para as tabelas core, com outro OWNER? (Já vi isso sendo feito o que me deixou em dúvida).

Outra coisa, já mechi bastante com estas tabelas do GL e a partir delas concluí que é realmente melhor utilizar o padrão ANSI de SQL do que esperar pelo otimizador do Oracle, desta forma consegue-se fazer uso de certos índices que pelo otimizador não são acessadas.

Também cheguei a utilizar alguns HINTS para elas(queries do GL) mas o desgraçado era instável. Melhorava por alguns dias mas após alguma alteração de estrutura interna(pelo DBA) o troço desandava para pior que antes dos HINTS.