LAB – Hints and Tricks

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 ONLY

Coloque 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