Início / dba / Perguntas / 118478
Accepted
Jon Heller
Asked: 2015-10-20 07:57:00 +0800 CST

Recrie visualizações fixas

  • 772

É possível recriar visualizações fixas do Oracle? Tenho um problema de desempenho com GV$ACTIVE_SESSION_HISTORY que pode exigir a reconstrução da exibição.

Um simples select * from gv$active_session_history;corre para sempre por causa de um plano de execução ruim. Isso acontece apenas em um pequeno número de nossos bancos de dados e provavelmente tem algo a ver com as configurações NLS anteriores. O plano de execução abaixo utiliza 2 FIXED TABLE FULLoperações, pois o NLSSORTpredicado impede que um índice fixo seja utilizado:

explain plan for select * from gv$active_session_history;

select * from table(dbms_xplan.display);

Plan hash value: 2432277601

------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                           |   136K|   169M|   215 (100)| 00:00:04 |
|   1 |  VIEW              | GV$ACTIVE_SESSION_HISTORY |   136K|   169M|   215 (100)| 00:00:04 |
|   2 |   NESTED LOOPS     |                           |   136K|    62M|   215 (100)| 00:00:04 |
|   3 |    FIXED TABLE FULL| X$KEWASH                  |   136K|  3196K|    72 (100)| 00:00:02 |
|*  4 |    FIXED TABLE FULL| X$ASH                     |     1 |   454 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("S"."SAMPLE_ADDR"="A"."SAMPLE_ADDR" AND "S"."SAMPLE_ID"="A"."SAMPLE_ID" 
              AND "S"."SAMPLE_TIME"="A"."SAMPLE_TIME" AND 
              NLSSORT("S"."NEED_AWR_SAMPLE",'nls_sort=''BINARY_CI''')=NLSSORT("A"."NEED_AWR_SAMPLE",'n
              ls_sort=''BINARY_CI'''))

Para comparação, aqui está um bom plano de execução em 99% de nossos bancos de dados:

Plan hash value: 436940376

-------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                           |   100 |   127K|     0   (0)| 00:00:01 |
|   1 |  VIEW                     | GV$ACTIVE_SESSION_HISTORY |   100 |   127K|     0   (0)| 00:00:01 |
|   2 |   NESTED LOOPS            |                           |   100 |   131K|     0   (0)| 00:00:01 |
|   3 |    FIXED TABLE FULL       | X$KEWASH                  |   100 |  5200 |     0   (0)| 00:00:01 |
|*  4 |    FIXED TABLE FIXED INDEX| X$ASH (ind:1)             |     1 |  1299 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("S"."SAMPLE_ADDR"="A"."SAMPLE_ADDR" AND "S"."SAMPLE_ID"="A"."SAMPLE_ID" AND 
              "S"."SAMPLE_TIME"="A"."SAMPLE_TIME" AND "S"."NEED_AWR_SAMPLE"="A"."NEED_AWR_SAMPLE")

Aqui está o que eu tentei e soluções alternativas que não funcionam:

  1. Dicas e recursos de gerenciamento de planos. Corrigir consultas uma de cada vez não é bom o suficiente. Essa visão fixa é usada em muitas consultas do sistema, não quero modificá-las todas. Por exemplo, posso corrigir meu exemplo com uma dica como esta: select /*+ use_hash(@"SEL$3" "A"@"SEL$3") */ * from gv$active_session_history order by sample_time desc;. Mas não posso alterar as consultas do sistema que usam GV$* e não quero ter que gerenciar cada consulta individual.
  2. Reunindo estatísticas. "Rows = 1" implica em estatísticas ruins, mas já tentei coletar estatísticas e não ajudou.
  3. Estatísticas falsas. Não consegui o plano de usar uma junção de hash, mesmo depois de definir as linhas da tabela ridiculamente altas e definir a coluna distintamente baixa. Mesmo quando o otimizador pensa que a junção retorna quatrilhões de linhas, ele ainda usa um loop aninhado com duas verificações de tabela completas.

  4. Alterando as configurações do NLS. A princípio, isso parece o típico problema linguístico-classificar-ignorar-índice. Mas nls_sort e nls_comp são ambos definidos como BINARY. Quando altero nls_comp e nls_sort no nível da sessão, o predicado tem 2 funções NLSSORT:

    alter session set nls_comp='LINGUISTIC';
alter session set nls_sort='BINARY_CI';
explain plan for select * from gv$active_session_history where sql_id = '7f7bap53hb12w';
select * from table(dbms_xplan.display);

...

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(NLSSORT("A"."SQL_ID",'nls_sort=''BINARY_CI''')=HEXTORAW('37663762617035336
              86231327700')  AND "S"."SAMPLE_ADDR"="A"."SAMPLE_ADDR" AND 
              "S"."SAMPLE_ID"="A"."SAMPLE_ID" AND "S"."SAMPLE_TIME"="A"."SAMPLE_TIME" AND 
              NLSSORT("S"."NEED_AWR_SAMPLE",'nls_sort=''BINARY_CI''')=NLSSORT("A"."NEED_AWR_SAMPLE",'n
              ls_sort=''BINARY_CI'''))

    Quando eu os coloco de volta em BINARY, um dos NLSSORT desaparece, mas um permanece, impedindo o índice:

    alter session set nls_comp='BINARY';
alter session set nls_sort='BINARY';
explain plan for select * from gv$active_session_history where sql_id = '7f7bap53hb12w';
select * from table(dbms_xplan.display);

...

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("A"."SQL_ID"='7f7bap53hb12w' AND "S"."SAMPLE_ADDR"="A"."SAMPLE_ADDR" AND 
              "S"."SAMPLE_ID"="A"."SAMPLE_ID" AND "S"."SAMPLE_TIME"="A"."SAMPLE_TIME" AND 
              NLSSORT("S"."NEED_AWR_SAMPLE",'nls_sort=''BINARY_CI''')=NLSSORT("A"."NEED_AWR_SAMPLE",'n
              ls_sort=''BINARY_CI'''))

  5. Encontre a fonte de visualização. Não consigo encontrar a fonte de visualização completa. Não está em DBA_VIEWS e apenas os primeiros 4000 caracteres estão em $FIXED_VIEW_DEFINITION. Tentei grep -i v.*active_session_history *em $ORACLE_HOME/rdbms/admin/ mas não vi nada.

  6. Recompilar. alter view gv$active_session_history compile;lança um ORA-600.

Estou executando 11.2.0.4 no Solaris. Criei uma solicitação de serviço de suporte da Oracle, mas ainda não recebi uma resposta.

oracle oracle-11g-r2

1 respostas

  1. Best Answer

    Digamos que você criou o banco de dados com os parâmetros abaixo:

    NLS_COMP=LINGUISTIC
NLS_SORT=BINARY_CI

    E por criar, eu realmente quero dizer criar, do zero. Um banco de dados personalizado DBCA ou execução CREATE DATABASEe scripts de dicionário manualmente.

    Se isso aconteceu, estas serão suas propriedades NLS no nível do banco de dados:

    SQL> select * from nls_database_parameters 
     where parameter in ('NLS_COMP', 'NLS_SORT') 
     order by parameter;

PARAMETER  VALUE
---------- ----------
NLS_COMP   LINGUISTIC
NLS_SORT   BINARY_CI

    (Por padrão aqui você deve ver BINARYe BINARY, e para ser sincero, não consigo me lembrar de um único caso em que o banco de dados tenha valores diferentes - exceto o que acabei de criar em minha caixa de areia.)

    Dado o exposto, você obterá o mesmo plano de execução da sua pergunta. Você pode reiniciar a instância ou definir NLS_COMPe NLS_SORTno nível da sessão ou do sistema (instância) para os mesmos valores, isso não "consertará" o plano de execução.

    Para modificar a configuração acima, é tecnicamente possível ( mas nunca faça isso em um banco de dados real) atualizar esses valores manualmente (reexecutar os scripts do dicionário não atualizará isso):

    SQL> update props$ set value$ = 'BINARY' where name in ('NLS_COMP', 'NLS_SORT');

2 rows updated.

SQL> commit;

Commit complete.

    Depois disso (e um desligamento + inicialização), a mesma consulta usou o índice fixo sem nenhuma NLSSORTchamada implícita no filtro.

    Reverter as alterações:

    SQL> update props$ set value$ = 'BINARY_CI' where name in ('NLS_SORT');

1 row updated.

SQL> update props$ set value$ = 'LINGUISTIC' where name in ('NLS_COMP');

1 row updated.

SQL> commit;

    Desligamento, inicialização, explicação, dbms_xplan.display e está errado novamente.

    Outra opção (problemática, mas pelo menos suportada) seria recriar o banco de dados com os valores padrão ( BINARY, BINARY).

    • 3

relate perguntas