Descubra como gerenciar sessões problemáticas no Oracle 11g e Oracle 12c e aproveite ao máximo os recursos de índices virtuais.
Manter o bom desempenho do Oracle exige um gerenciamento eficaz das sessões e dos processos em execução. Quando um processo começa a impactar negativamente o banco de dados, saber como suspendê-lo ou otimizá-lo se torna essencial. Neste post, abordaremos técnicas para matar ou suspender sessões, cancelar instruções SQL sem desconectar a sessão e utilizar índices virtuais no Oracle 11g e 12c. Com essas dicas, você pode garantir que seu ambiente de banco de dados continue funcionando de forma eficiente e sem interrupções.
⇒ Rogue Session (11g e 12c)
Um processo está rodando no Oracle e está causando lentidão nos outros processos.
A solução seria matar este processo.
SQL> alter system kill session;Mas se o processo for importante?
Podemos suspender o processo.
SQL> COL USERNAME FORMAT A20;
SQL> COL OSUSER FORMAT A20;
SQL> COL MACHINE FORMAT A20;
SQL> SELECT A.SID, A.SERIAL#, A.USERNAME, A.SERVER, B.SPID, A.OSUSER, A.MACHINE, A.PROGRAM, A.LOGON_TIME
FROM V$SESSION A, V$PROCESS B WHERE A.PADDR=B.ADDR AND A.SID in (SELECT A.SID FROM V$SESSION A, V$PROCESS B
WHERE A.STATUS='ACTIVE' AND A.USERNAME IS NOT NULL AND A.PADDR=B.ADDR);Utilize o SPID.
SQL> oradebug setorapid nnn;
SQL> oradebug suspend;Para resumir o processo.
SQL> oradebug resume;Há duas circunstâncias em que suspender e depois resumir um processo não é adequado. Esses são:
- Se outros processos utilizam locks e o processo esteja mantendo em um modo incompatível.
- Se o processo for dependente da consistência de leitura nas informações do segmento rollback que seriam sobrescritas enquanto estiverem suspensas.
⇒ Cancel SQL (12c)
O comando foi introduzido no Oracle Database 18c, podendo cancelar determinada instrução SQL sem desconectar a sessão.
SQL> alter system cancel sql '123,456';
-- sid,serial#Somente a query atual será cancelada e feito rollback das transações. As demais query’s serão executadas.
⇒ Virtual Index (11g e 12c)
Podemos criar um índice virtual ou fake index para validar se o índice criado será utilizado pelo otimizador sem termos realmente criado o mesmo. A definição do virtual index existe no dicionário de dados, mas não possui segmentos de dados.
SQL> create table exemplo as select * from dba_tables;Faça um explain em uma query na tabela.
SQL> set autotrace traceonly explain
SQL> select * from exemplo where table_name='AUD$';
Execution Plan
----------------------------------------------------------
Plan hash value: 760791384
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1430 | 23 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EXEMPLO | 1 | 1430 | 23 (0)| 00:00:01 |
-----------------------------------------------------------------------------Crie um índice virtual na tabela “nosegment”
SQL> create index exemplo_idx1 on exemplo (table_name) nosegment; --DATA DICTIONARY ONLYColoque o parâmetro “use_nosegment_indexes” como True para sua sessão.
SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true;Execute a query novamente para verificar a utilização do índice.
SQL> set autotrace traceonly explain
SQL> select * from exemplo where table_name='AUD$';
Execution Plan
----------------------------------------------------------
Plan hash value: 1811059678
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1430 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EXEMPLO | 1 | 1430 | 00:00:01 |
|* 2 | INDEX RANGE SCAN | EXEMPLO_IDX1 | 7 | | 00:00:01 |Agora podemos dropar o índice virtual e criar o índice para tabela.
SQL> DROP INDEX EXEMPLO_IDX1;
SQL> CREATE INDEX EXEMPLO_IDX1 ON EXEMPLO (TABLE_NAME);⇒ Invisible Index (11g e 12c)
Podemos criar um índice invisível para que o optimizer ignore o índice sem o parâmetro “OPTIMIZER_USE_INVISIBLE_INDEXES” na instância ou sessão.
SQL> create table T as select * from DBA_OBJECTS;
SQL> create index T_IDX on T(OWNER) invisible;
SQL> set autotrace traceonly explain
SQL> select * from T where owner = 'SYS';
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4547 | 612K| 386 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 4547 | 612K| 386 (1)| 00:00:01 |
--------------------------------------------------------------------------Agora vamos alterar o parâmetro “OPTIMIZER_USE_INVISIBLE_INDEXES” para nossa sessão.
SQL> alter session set optimizer_use_invisible_indexes = true;
SQL> select * from T where owner = 'SYS';
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4547 | 612K| 169 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 4547 | 612K| 169 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 4547 | | 12 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------O Índice está marcado como Invisible na view DBA_INDEXES.
SQL> select index_name, visibility from dba_indexes where index_name = 'T_IDX';
INDEX_NAME VISIBILITY
---------------------------------------- -----------
T_IDX INVISIBLE⇒ SQLSTATS
As definições de colunas na V$SQLSTATS são idênticas às das views V$SQL e V$SQLAREA. No entanto, a exibição V$SQLSTATS difere de V$SQL e V$SQLAREA, pois é mais rápida, mais escalável e tem retenção de dados maiores. (As estatísticas ainda podem aparecer nesta view, mesmo depois que o cursor tiver saído da shared pool).
SQL> select sql_fulltext
from v$sql
where buffer_gets > 1000000 or
executions > 10000 or
disk_reads > 100000;Melhor uso.
SQL> select sql_fulltext
from v$sqlstats
where buffer_gets > 1000000 or
executions > 10000 or
disk_reads > 100000;Referências
https://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2131.htm#REFRN30396
