É 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:
- 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. - Reunindo estatísticas. "Rows = 1" implica em estatísticas ruins, mas já tentei coletar estatísticas e não ajudou.
- 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.
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'''))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.- 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.
