Capturar variáveis de SO com dbms_system.get_env

Através do pacote DBMS_SYSTEM do Oracle RDBMS podemos fazer várias coisas, como:

• Escrever no alertlog (DBMS_SYSTEM.KSDWRT)
• Alterar parâmetros de outra sessão (DBMS_SYSTEM.SET_INT_PARAM_IN_SESSION e DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION)
• Habilitar e desabilitar SQL Traces (DBMS_SYSTEM.SET_EV)
• e também, permite capturarmos variáveis do Sistema Operacional (SO) para dentro do banco de dados, como veremos a seguir:

SQL> var VAR varchar2(255)
SQL> exec dbms_system.get_env('ORACLE_HOME',:VAR);

PL/SQL procedure successfully completed.

SQL> print :VAR

VAR
--------------------------------------------------------------------------------
/orabin/app/oracle/product/12.2.0.1/dbhome_1

Exportando uma variável de teste:

Banco=cdb1-> export TESTE="variavel de teste"
Banco=cdb1-> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 3 03:47:54 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> var VAR varchar2(255)
SQL> exec dbms_system.get_env('TESTE',:VAR);

PL/SQL procedure successfully completed.

SQL> print :VAR

VAR
--------------------------------------------------------------------------------
variavel de teste

Exemplo criando uma function para retorno da variável:

SQL> create or replace function getvar (envvar varchar2) return varchar2 as
output varchar2(4000);
begin
dbms_system.get_env(envvar, output);
return output;
end;
/

Function created.

SQL> select getvar('ORACLE_HOME') from dual;

GETVAR('ORACLE_HOME')
--------------------------------------------------------------------------------
/orabin/app/oracle/product/12.2.0.1/dbhome_1

Exemplo criando uma procedure para retorno da variável:

SQL> create or replace procedure getvar2 (envvar varchar2) as
output varchar2(4000);
begin
dbms_system.get_env(envvar, output);
dbms_output.put_line(output);
end;
/

Procedure created.

SQL> set serveroutpu on
SQL> exec getvar2('TESTE');
variavel de teste

PL/SQL procedure successfully completed.

SQL> exec getvar2('ORACLE_HOME');
/orabin/app/oracle/product/12.2.0.1/dbhome_1

PL/SQL procedure successfully completed.

Mais informações →

Flush de uma única SQL da library cache [ SHARED_POOL ]

Uma instance de banco de dados Oracle contém varias estruturas de memória, uma delas é a SHARED POOL composta pela library cache(cache de biblioteca), dictionary cache(cache de dicionário), result cache(cache de resultado), buffers de mensagens de execução paralela e estruturas de controle.

Dentro da library cache encontramos basicamente os SQLs compartilhados, functions, procedures, packages, (…) e planos de execução. É comum vermos DBAs executando um ALTER SYSTEM FLUSH SHARED_POLL para “limpar” esta área simplesmente para forçar um hard parse de um único SQL, mas como descrito acima, um flush na shared pool vai limpar varias outras coisas (sql, planos, functions, packages,…) o que pode gerar um alto custo para um banco carregar toda esta estrutura novamente.

A partir desta necessidade, a Oracle implementou a partir da versão Oracle database 11g a procedure PURGE dentro da package DBMS_SHARED_POOL que permite efetuar a liberação de uma única SQL, package, sequence… da library cache.

SINTAXE:

PARÂMETROS:

Parâmetro	Descrição
NAME	Nome do objeto a ser eliminado. Este valor é identificado pela concatenação das colunas ADDRESS e HASH_VALUE da v$sqlarea. Atualmente, objetos como tabelas e views não podem ser eliminados.
FLAG	(Opcional) Se não for especificado, então o pacote pressupõe que o primeiro parâmetro é o nome de um pacote/procedure/function.
HEAPS	Heaps a ser purgado. Default é 1, ou seja, pilha 0, o que significa que todo o objeto seria purgado.
SCHEMA	Nome do usuário ou do esquema ao qual pertence o objeto.
OBJNAME	Nome do objeto a ser eliminado.
NAMESPACE	Parâmetro numerico que indica o namespace da library cache em que o objeto está sendo procurado.
HASH	Valor de hash de 16bytes para o objeto.

Exemplo:

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where sql_text like '%1 from dual';
 
no rows selected
 
SQL> select 1 from dual;
 
         1
----------
         1
 
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where sql_text like '%1 from dual';
 
ADDRESS          HASH_VALUE
---------------- ----------
C000000760B1DB38 2866845384
 
SQL> exec DBMS_SHARED_POOL.PURGE ('C000000760B1DB38,2866845384','C');
 
PL/SQL procedure successfully completed.
 
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where sql_text like '%1 from dual';
 
no rows selected

NOTA: Melhorias disponibilizadas no 10gR2

No patchset 10.2.0.4 foi incluido a procedure PURGE na package DBMS_SHARED_POOL.
No 10.2.0.2 e 10.2.0.3 é necessário efetuar o download e instalação do RDBMS path 5614566 para ter acesso as melhorias na package DBMS_SHARED_POOL. (verificar nota 751876.1)

Referências:

http://docs.oracle.com/cd/B28359_01/server.111/b28318/memory.htm
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_shared_pool.htm#CHDCBEBB
MOS (MY ORACLE SUPPORT): How To Flush an Object Out The Library Cache [SGA] Using The DBMS_SHARED_POOL Package (Doc ID 457309.1)
DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4 (Doc ID 751876.1)
DBMS_SHARED_POOL Not Installed or Package Body Missing (ORA-6550, ORA-4067) (Doc ID 121571.1)

Mais informações →

Oracle Wait Interface (OWI) – Por onde começar?

Embora introduzido no Oracle 7, o Oracle Wait Interface (OWI) continua sendo hoje uma ferramenta inestimável para os DBAs, através dela é possível obter informações que ajudam na solução de problemas e na busca de melhorias na performance de todo o ambiente de banco de dados.

O Oracle Wait Interface nada mais é que uma coleção de visões dinâmicas de performance e arquivo de rastreamento SQL(SQL trace file) que promovem estatísticas de desempenho sobre gargalos de um processo mais conhecidas como eventos de espera(wait events) ou sintomas.

Utilizando o OWI podemos identificar todos os gargalos de espera que incidiram desde o inicio ao termino de um processo, incluindo esperas por operações de I/O, locks, network, dentre outros. Proporcionando um direcionamento para a solução ou redução do impacto causado pelo gargalo de espera.

Atualmente na versão 12.1.0.1 contamos com 1.567 eventos de espera(wait events), diferentemente de seus antecessores. O Oracle 11.2.0.3 por exemplo, conta com 1.152 eventos de espera, na versão 10g pouco mais de 800 enquanto na versão 7.3 contava com apenas 104 eventos de espera. (A quantidade de eventos de espera depende da versão, configuração e opções instaladas no RDBMS).

Wait Events são as condições em que uma sessão está esperando que algo aconteça. Elas podem ser a nível de sistema, onde afeta todo o banco de dados ou a nível de sessão em que afeta uma única atividade de usuário no banco de dados.

Conforme documentação Oracle, os wait events são elencados em três visões dinâmicas de performance:

V$SESSION_WAIT exibe os eventos para os quais as sessões acabaram de concluir ou estão esperando.

V$SYSTEM_EVENT exibe o número total de tempo que todas as sessões tem aguardado por eventos de espera.

V$SESSION_EVENT é semelhante ao V$SYSTEM_EVENT, mas mostra todas as esperas para cada sessão.

Todo evento de espera pertence a uma classe de espera(Wait Class). Através da visão V$EVENT_NAME podemos identificar os tipos de classe de espera e os tipos de eventos bem como o que representam os parâmetros(PARAMETER) P1,P2 e P3 da V$SESSION_WAIT igualmente como as colunas P1TEXT, P2TEXT e P3TEXT desta mesma view.

-- Lista das classes de espera
 
SQL> select distinct wait_class from v$event_name;
 
WAIT_CLASS
----------------------------------------------------------------
User I/O
Application
Network
Concurrency
Administrative
Configuration
Scheduler
Cluster
Other
Idle
System I/O
Commit
 
12 rows selected.
 
-- Detalhes de um evento:
 
SQL> set lines 190
SQL> col NAME for a40
SQL> col WAIT_CLASS for a20
SQL> col PARAMETER1 for a10
SQL> col PARAMETER2 for a15
SQL> col PARAMETER3 for a15
SQL> select * from v$event_name where name='db file scattered read';
 
    EVENT#   EVENT_ID NAME                                     PARAMETER1 PARAMETER2      PARAMETER3      WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------- ---------------------------------------- ---------- --------------- --------------- ------------- ----------- --------------------
       118  506183215 db file scattered read                   file#      block#          blocks             1740759767           8 User I/O

A lista abaixo descreve brevemente cada classe:

Administrative 

Espera resultante de comandos administrativos (DBA). Por exemplo, um rebuild de índice.

Application

Espera resultante do código da aplicação do usuário. Por exemplo, lock a nível de linha ou comando explícito de lock.

Cluster 

Espera relacionada aos recursos do Real Application Clusters (RAC). Por exemplo, ‘gc cr block busy’.

Commit 

Esta classe cotém apenas um evento de espera. ‘log file sync’ – espera para o redolog confirmar um commit.

Concurrency 

Espera por recursos internos do banco de dados. Por exemplo, latches.

Configuration 

Espera causada por uma configuração inadequada do banco de dados ou recursos da instance. Por exemplo, mal dimensionamento do tamanho dos log file, shared pool size.

Idle 

Indica que a sessão está inativa, esperando para trabalhar. Por exemplo, ‘SQL*Net message from client’.

Network 

Espera relacionada a eventos de rede. Por exemplo, ‘SQL*Net more data to dblink’.

Other 

Esperas que normalmente não devem ocorrem em um sistema. Por exemplo, ‘wait for EMON to spawn’)

Scheduler 

Espera relacionada ao gerenciamento de recursos. Por exemplo, ‘resmgr: become active’. 

System I/O 

Espera por background process I/O. Por exemplo, DBWR wait for ‘db file parallel write’) 

User I/O

Espera por user I/O. Por exemplo ‘db file sequential read’.

Ao longo das versões, o Oracle database ganhou outras visões de desempenho que oferecem novas informações de espera por várias perspectivas incluindo features de apoio para atender limitações do OWI como:

• ASH (Active Session History), coleta a todo segundo informações correntes de cada sessão. Seria um join da V$SESSION com a V$SESSION_WAIT porem com dados históricos das sessões ativas. 
• AWR (Automatic Workload Repository), oferece o histórico de todas as atividades das sessões. Seu tempo de retenção default é 8 dias mas pode ser ajustado conforme necessidade. Os dados capturados podem ser acessados através de views (DBA_HIST_*) e pelo AWR report facilmente acessado utilizando o Oracle Enterprise Manager. 
• ADDM (Automatic Database Diagnostic Monitor), faz uma análise automática dos dados capturados(snapshots) dentro do AWR e oferece recomendações de melhorias. 

Algumas outras views que complementam o OWI:

V$SYSTEM_WAIT_CLASS V$SESSION_WAIT_CLASS V$SESSION_WAIT_HISTORY V$EVENT_HISTOGRAM V$EVENTMETRIC V$SERVICE_EVENT V$SERVICE_WAIT_CLASS V$ACTIVE_SESSION_HISTORY V$WAITCLASSMETRIC

V$SESSTAT V$STATNAME V$SYSSTAT V$SESS_TIME_MODEL V$SYS_TIME_MODEL DBA_HIST_EVENT_NAME DBA_HIST_ACTIVE_SESS_HISTORY DBA_HIST_BG_EVENT_SUMMARY DBA_HIST_WAITCLASSMET_SUMMARY

Outro componente que faz parte do OWI (citado anteriormente) é o extended SQL Trace:

De forma simplificada, o rastreamento SQL (trace) é um “monitoramento” que pode ser habilitado sobre uma sessão/processo para identificarmos os comandos executados, tempos e gargalos para cada SQL, o que permite diagnosticar problemas de performance.

É possível habilitar um trace de diversas formas e com diferentes níveis de coleta:

Nível 0 Trace desabilitado. Igual ao SQL_TRACE = FALSE.
Nível 1 Informações de rastreamento SQL padrão (SQL_TRACE = TRUE). Este é o nível default.
Nível 4 Informações de rastreamento SQL + valores das bind variables.
Nível 8 Informações de rastreamento SQL + informações de wait events.
Nível 12 Informações de rastreamento SQL + informações de wait events + valores das bind variables.

Alguns métodos para se habilitar um trace na sua sessão:

--ativa trace nível 1
alter session set sql_trace=true;
 
--desativa trace
alter session set sql_trace=false;
 
--ativa o trace nível 8
alter session set events ‘10046 trace name context forever, level 8’;
 
--desativa o trace
alter session set events ‘10046 trace name context off’;
 
--Para quem possui o pacote DBMS_SUPPORT instalado:
 
--ativa trace nível 1
exec sys.dbms_support.start_trace;
 
--para incluir wait events e bind variables
exec sys.dbms_support.start_trace(waits => TRUE, binds=> TRUE);
 
--desativa o trace
exec sys.dbms_support.stop_trace;

Alguns métodos para se habilitar um trace em outra sessão:

--ativa o trace nível 8
execute sys.dbms_system.set_ev(SID,SERIAL#,10046,8,'');
 
--desativa o trace
execute sys.dbms_system.set_ev(SID,SERIAL#,10046,0,'');
 
--Para quem possui o pacote DBMS_SUPPORT instalado:
 
--ativa o trace nível 12
exec dbms_support.start_trace_in_session(
     sid => sid,
     serial# => serial,
     waits => true,
     binds => true);
 
--desativa o trace
exec dbms_support.stop_trace_in_session(
     sid => sid,
     serial# => serial);
 
--Utilizando o pacote DBMS_MONITOR:
 
--ativa o trace nível 12
exec dbms_monitor.session_trace_enable(
     session_id => sid,
     serial_num => serial,
     waits => true,
     binds => true);
 
--desativa o trace
exec dbms_monitor.session_trace_disable(
     session_id => sid,
     serial_num => serial);

Os arquivos de trace recebem a extensão .trc e podem ser encontrados na versão 10g em 2 diretórios dependendo da sessão em que o trace foi habilitado:

Sessão de usuário = USER_DUMP_DEST

SQL> show parameter USER_DUMP_DEST
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /oratst01/app/oracle/admin/tes
                                                 te/udump

Processos background = BACKGROUND_DUMP_DEST

SQL> show parameter BACKGROUND_DUMP_DEST
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /oratst01/app/oracle/admin/tes
                                                 te/bdump

Nas versões 11g e 12c os traces (.trc) podem ser encontrados abaixo do ADR (Automatic Diagnostic Repository) tanto para processos background como de usuários: (Diag Trace)

SQL> show parameter diagnostic_dest
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      /oratst02/app/oracle
 
SQL> col name for a20
SQL> col value for a70
SQL> select * from v$diag_info;
 
   INST_ID NAME                 VALUE
---------- -------------------- ----------------------------------------------------------------------
         1 Diag Enabled         TRUE
         1 ADR Base             /oratst02/app/oracle
         1 ADR Home             /oratst02/app/oracle/diag/rdbms/teste2/teste2
         1 Diag Trace           /oratst02/app/oracle/diag/rdbms/teste2/teste2/trace
         1 Diag Alert           /oratst02/app/oracle/diag/rdbms/teste2/teste2/alert
         1 Diag Incident        /oratst02/app/oracle/diag/rdbms/teste2/teste2/incident
         1 Diag Cdump           /oratst02/app/oracle/admin/teste2/cdump
         1 Health Monitor       /oratst02/app/oracle/diag/rdbms/teste2/teste2/hm
         1 Default Trace File   /oratst02/app/oracle/diag/rdbms/teste2/teste2/trace/teste2_ora_263.trc
         1 Active Problem Count 0
         1 Active IncidentCount 0

O nome do arquivo será: [ ORACLE_SID ]_ora_[ SPID ].trc

SQL> select spid
  2    from v$process p, v$session s
  3   where p.addr = s.paddr
  4     and sid = 2371;
 
SPID
------------
12603

..

srvtst:oracle:oratst2> ls -lrt *12603*
-rw-r--r--   1 oracle     dba           6032 Aug  8 17:02 oratst2_ora_12603.trc

Uma facilidade presente desde a versão 8.1.7 foi o TRACEFILE_IDENTIFIER que permite atribuir um “NOME” ao arquivo trace. Quando você esta realizando um trace da sua sessão:

SQL> alter session set tracefile_identifier=anderson;
 
Session altered.

agora:

srvtst:oracle:oratst2> ls -lrt *ANDERSON*
-rw-r--r--   1 oracle     dba             73 Aug  8 17:08 oratst2_ora_4796_ANDERSON.trc

Após identificar o seu arquivo trace você executa o utilitário TKPROF (Transient Kernel Profiler) para gerar e sumarizar todos os dados coletados no arquivo .trc

srvtst:oracle:oratst2> tkprof oratst2_ora_4796_ANDERSON.trc trace.txt
 
TKPROF: Release 10.2.0.5.0 - Production on Thu Aug 8 17:13:35 2013
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Agora é só visualizar o arquivo analizado (trace.txt)

SELECT COUNT(*)
FROM
 TESTE WHERE TIMESTAMP < (SYSDATE  - 365)
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.03          1         30          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     47.08     289.13    1118319    1138160          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     47.11     289.16    1118320    1138190          0           1
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 20844
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=1138160 pr=1118319 pw=0 time=289132678 us)
      0   TABLE ACCESS FULL TESTE (cr=1138160 pr=1118319 pw=0 time=289132666 us)
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file scattered read                      35022        0.63        249.26
  latch: object queue header operation           20        0.00          0.00
  db file sequential read                         1        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00
********************************************************************************

Identificando um evento de espera a nível de sessão:

SQL> set lines 190
SQL> col EVENT for a40
SQL> col P1TEXT for a10
SQL> col P2TEXT for a10
SQL> col P3TEXT for a10
SQL> col WAIT_CLASS for a20
SQL> select sw.event,
  2         sw.state,
  3         sw.p1text,
  4         sw.p1,
  5         sw.p2text,
  6         sw.p2,
  7         sw.p3text,
  8         sw.p3,
  9         sw.wait_class
 10    from v$session_wait sw
 11   where sid = 255
 12  /
 
EVENT                                    STATE               P1TEXT             P1 P2TEXT             P2 P3TEXT             P3 WAIT_CLASS
---------------------------------------- ------------------- ---------- ---------- ---------- ---------- ---------- ---------- --------------------
db file scattered read                   WAITING             file#             448 block#          52425 blocks             16 User I/O

Conhecendo o evento(sintoma) de espera da sessão você pode agir sobre o mesmo. Neste exemplo, o evento ‘db file scattered read’ representa que a sessão esta realizando um acesso FULL sobre uma tabela – FTS (FULL TABLE SCAN) ou sobre um índice – FFS (FAST FULL SCAN).

Identificando o comando SQL da sessão 255:

SQL> SELECT A.SQL_TEXT
  2    FROM V$SQLTEXT A, V$SESSION B
  3   WHERE A.ADDRESS = B.SQL_ADDRESS
  4     AND A.HASH_VALUE = B.SQL_HASH_VALUE
  5     AND B.SID = 255
  6   ORDER BY PIECE;
 
SQL_TEXT
----------------------------------------------------------------
select * from lancamentos where cd_empresa=5

Plano de execução do SQL. Observe o TABLE ACCESS FULL na coluna Operation. (representa um acesso full sobre uma tabela)

SQL> explain plan for
  2  select * from lancamentos where cd_empresa=5;
 
Explained.
 
SQL> SELECT * FROM TABLE(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 2518875852
 
---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |  9759K|  1237M|  1108K  (2)| 03:41:40 |
|*  1 |  TABLE ACCESS FULL| LANCAMENTOS |  9759K|  1237M|  1108K  (2)| 03:41:40 |
---------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("CD_EMPRESA"=5)
 
13 rows selected.

Agora é só agir sobre o problema! sintoma, evento de espera…

Identificando os TOP wait events do sistema: 

Se você já gerou algum report do AWR(Automatic Workload Repository) deve conhecer a imagem abaixo dos “Top 5 Timed Events”.

Este report mostra os wait events que mais afetaram o banco de dados no intervalo de snapshot selecionado, mas não é preciso gerar um AWR para coletá-los, posso pegar as informações utilizando as próprias views do AWR. Exemplo:

Selecionando o horário desejado: ( snap_id = 21138 )

SQL> select a.snap_id,
  2         to_char(a.begin_interval_time, 'dd/mm/yyyy hh24:mi:ss') begin_interval_time,
  3         to_char(a.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') end_interval_time
  4    from dba_hist_snapshot a
  5   order by 1 desc;
 
   SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- ------------------- -------------------
     21145 23/07/2013 18:00:53 23/07/2013 19:00:05
     21144 23/07/2013 17:01:11 23/07/2013 18:00:53
     21143 23/07/2013 16:00:27 23/07/2013 17:01:11
     21142 23/07/2013 15:00:15 23/07/2013 16:00:27
     21141 23/07/2013 14:00:02 23/07/2013 15:00:15
     21140 23/07/2013 13:00:50 23/07/2013 14:00:02
     21139 23/07/2013 12:00:37 23/07/2013 13:00:50
     21138 23/07/2013 11:00:24 23/07/2013 12:00:37
     21137 23/07/2013 10:00:11 23/07/2013 11:00:24
     21136 23/07/2013 09:00:59 23/07/2013 10:00:11
     21135 23/07/2013 08:00:46 23/07/2013 09:00:59
     21134 23/07/2013 07:00:33 23/07/2013 08:00:46
     21133 23/07/2013 06:01:09 23/07/2013 07:00:33
     21132 23/07/2013 05:00:08 23/07/2013 06:01:09

Executando a query para coletar os TOP events:

SQL> select snap_id,
  2         begin_time,
  3         end_time,
  4         (select i.instance_name
  5            from gv$instance i
  6           where i.INSTANCE_NUMBER = a.instance_number) as "INSTANCE",
  7         event_name,
  8         total_waits as "WAITS",
  9         event_time_waited as "TIME(s)",
 10         avg_wait as "AVG_WAIT(ms)",
 11         pct as "%PCT",
 12         wait_class
 13    from (select to_char(s.begin_interval_time, 'DD-MM-YYYY HH24:MI') as BEGIN_TIME,
 14                 to_char(s.end_interval_time, 'DD-MM-YYYY HH24:MI') as END_TIME,
 15                 m.*
 16            from (select ee.instance_number,
 17                         ee.snap_id,
 18                         ee.event_name,
 19                         round(ee.event_time_waited / 1000000) event_time_waited,
 20                         ee.total_waits,
 21                         round((ee.event_time_waited * 100) /
 22                               et.total_time_waited,
 23                               1) pct,
 24                         round((ee.event_time_waited / ee.total_waits) / 1000) avg_wait,
 25                         ee.wait_class
 26                    from (select ee1.instance_number,
 27                                 ee1.snap_id,
 28                                 ee1.event_name,
 29                                 ee1.time_waited_micro - ee2.time_waited_micro event_time_waited,
 30                                 ee1.total_waits - ee2.total_waits total_waits,
 31                                 ee1.wait_class
 32                            from dba_hist_system_event ee1
 33                            join dba_hist_system_event ee2 on ee1.snap_id =
 34                                                              ee2.snap_id + 1
 35                                                          and ee1.instance_number =
 36                                                              ee2.instance_number
 37                                                          and ee1.event_id =
 38                                                              ee2.event_id
 39                                                          and ee1.wait_class_id <>
 40                                                              2723168908
 41                                                          and ee1.time_waited_micro -
 42                                                              ee2.time_waited_micro > 0
 43                          union
 44                          select st1.instance_number,
 45                                 st1.snap_id,
 46                                 st1.stat_name event_name,
 47                                 st1.value - st2.value event_time_waited,
 48                                 null total_waits,
 49                                 null wait_class
 50                            from dba_hist_sys_time_model st1
 51                            join dba_hist_sys_time_model st2 on st1.instance_number =
 52                                                                st2.instance_number
 53                                                            and st1.snap_id =
 54                                                                st2.snap_id + 1
 55                                                            and st1.stat_id =
 56                                                                st2.stat_id
 57                                                            and st1.stat_name =
 58                                                                'DB CPU'
 59                                                            and st1.value -
 60                                                                st2.value > 0) ee
 61                    join (select et1.instance_number,
 62                                et1.snap_id,
 63                                et1.value - et2.value total_time_waited,
 64                                null wait_class
 65                           from dba_hist_sys_time_model et1
 66                           join dba_hist_sys_time_model et2 on et1.snap_id =
 67                                                               et2.snap_id + 1
 68                                                           and et1.instance_number =
 69                                                               et2.instance_number
 70                                                           and et1.stat_id =
 71                                                               et2.stat_id
 72                                                           and et1.stat_name =
 73                                                               'DB time'
 74                                                           and et1.value -
 75                                                               et2.value > 0) et on ee.instance_number =
 76                                                                                    et.instance_number
 77                                                                                and ee.snap_id =
 78                                                                                    et.snap_id) m
 79            join dba_hist_snapshot s on m.snap_id = s.snap_id
 80           where m.instance_number = 1
 81             and m.snap_id = 21138 --SNAP_ID capturado na query acima
 82           order by PCT desc) a
 83   where rownum <= 5 --Quantidade de linhas retornadas, especifique 10 para ter um TOP 10
 84  /
 
   SNAP_ID BEGIN_TIME       END_TIME         INSTANCE         EVENT_NAME                            WAITS      TIME(s) AVG_WAIT(ms)       %PCT WAIT_CLASS
---------- ---------------- ---------------- ---------------- ------------------------------ ------------ ------------ ------------ ---------- --------------------
     21138 23-07-2013 11:00 23-07-2013 12:00 c0090prd         DB CPU                                            66,705                    44.6
     21138 23-07-2013 11:00 23-07-2013 12:00 c0090prd         log file sync                       868,610       15,564           18       10.4 Commit
     21138 23-07-2013 11:00 23-07-2013 12:00 c0090prd         db file sequential read           7,663,832       15,562            2       10.4 User I/O
     21138 23-07-2013 11:00 23-07-2013 12:00 c0090prd         SQL*Net message from dblink         813,766       14,805           18        9.9 Network
     21138 23-07-2013 11:00 23-07-2013 12:00 c0090prd         SQL*Net break/reset to client       174,240        7,015           40        4.7 Application

Referências:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/waitevents.htm http://it.toolbox.com/blogs/living-happy-oracle/oracle-a-query-to-get-the-wait-events-part-of-the-awr-report-33420 http://vapvarun.com/study/oracle/mcgraw.hill.osborne.oracle.wait.interface.a.practical.guide.to.performance.d

Mais informações →

12c new feature: Colunas invisíveis

Mais uma nova feature que surgiu na versão 12c são as colunas invisíveis(invisible columns). Seu proposito é permitir que alterações em uma tabela possam ser feitas sem afetar os usuários e as aplicações que utilizam a tabela de modo que após as aplicações serem atualizadas para utilizarem as novas colunas podemos tornar as colunas invisíveis visíveis.

Quando uma coluna invisível é criada, consultas e operações podem utilizá-la apenas referenciando explicitamente o nome da coluna, caso contrário ela é totalmente invisível nas operações.

Por padrão em qualquer acesso genérico realizado sobre uma tabela todas as colunas invisíveis são omitidas, como no SQL*Plus:

--Criando uma tabela com coluna invisível
 
SQL> create table teste (a number, b varchar2(5) invisible);
 
Tabela criada.
 
SQL> desc teste
 Nome                                      Nulo?    Tipo
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER
 
SQL> insert into teste values (1);
 
1 linha criada.
 
SQL> select * from teste;
 
         A
----------
         1
 
SQL> insert into teste values (2,'par');
insert into teste values (2,'par')
            *
ERRO na linha 1:
ORA-00913: valores demais
 
-- referenciando explicitamente o nome da coluna invisível
 
SQL> insert into teste (a,b) values (2,'par');
 
1 linha criada.
 
SQL> select * from teste;
 
         A
----------
         1
         2
 
SQL> select a,b from teste;
 
         A B
---------- -----
         1
         2 par

Alterando a coluna invisível para visível:

SQL> alter table teste modify (b visible);
 
Tabela alterada.
 
SQL> desc teste;
 Nome                                      Nulo?    Tipo
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER
 B                                                  VARCHAR2(5)
 
SQL> select * from teste;
 
         A B
---------- -----
         1
         2 par

Adicionando mais colunas invisíveis:

SQL> alter table teste add (c char invisible, d int invisible);
 
Tabela alterada.
 
SQL> desc teste;
 Nome                                      Nulo?    Tipo
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER
 B                                                  VARCHAR2(5)
 
SQL> alter table teste modify (a invisible);
 
Tabela alterada.
 
SQL> desc teste
 Nome                                      Nulo?    Tipo
 ----------------------------------------- -------- ----------------------------
 B                                                  VARCHAR2(5)

Apesar das colunas invisíveis serem omitidas por default no SQL*Plus, ainda podemos fazê-lo mostrar as colunas invisíveis no DESCRIBE(DESC):

SQL> show colinvisible
colinvisible OFF
SQL> set colinvisible ON
SQL>
SQL> desc teste
 Nome                                      Nulo?    Tipo
 ----------------------------------------- -------- ----------------------------
 B                                                  VARCHAR2(5)
 A (INVISIBLE)                                      NUMBER
 C (INVISIBLE)                                      CHAR(1)
 D (INVISIBLE)                                      NUMBER(38)

Colunas virtuais também podem ser invisível bem como podemos utilizar uma coluna invisível como chave de particionamento durante a criação de uma tabela.

- Tipos de tabelas que não podem conter colunas invisíveis:

• External tables 
• Cluster tables 
• Temporary tables 

Um fator que sofre influência com a utilização das colunas invisíveis é a ordenação das colunas da tabela.

Normalmente as colunas são exibidas(listadas) na ordem em que elas foram criadas no CREATE TABLE, ou mesmo quando são adicionadas posteriormente elas são exibidas por ultimo na ordem da lista de colunas.

Quando utilizamos colunas invisíveis, as colunas invisíveis não são incluidas na ordem de colunas da tabela, desta forma sempre que tornamos uma coluna visível ela será a ultima da ordem de colunas e quando tornamos uma coluna invisível ocorre uma reorganização na ordem das colunas. Veja o exemplo abaixo:

SQL> create table exemplo (a int, b int invisible, c int);
 
Tabela criada.
 
SQL> col column_name for a15
SQL> select column_name, column_id
  2     from dba_tab_cols
  3    where table_name='EXEMPLO'
  4    order by 2
  5  /
 
COLUMN_NAME      COLUMN_ID
--------------- ----------
A                        1
C                        2
B
 
SQL> desc exemplo
 Nome                                      Nulo?    Tipo
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER(38)
 C                                                  NUMBER(38)
 
-- Alterando coluna B para visível
 
SQL> alter table exemplo modify (b visible);
 
Tabela alterada.
 
SQL> select column_name, column_id
  2     from dba_tab_cols
  3    where table_name='EXEMPLO'
  4    order by 2
  5  /
 
COLUMN_NAME      COLUMN_ID
--------------- ----------
A                        1
C                        2
B                        3
 
SQL> desc exemplo
 Nome                                      Nulo?    Tipo
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER(38)
 C                                                  NUMBER(38)
 B                                                  NUMBER(38)
 
-- Alterando coluna A para invisível
 
SQL> alter table exemplo modify (a invisible);
 
Tabela alterada.
 
SQL> select column_name, column_id
  2     from dba_tab_cols
  3    where table_name='EXEMPLO'
  4    order by 2
  5  /
 
COLUMN_NAME      COLUMN_ID
--------------- ----------
C                        1
B                        2
A
 
SQL> desc exemplo
 Nome                                      Nulo?    Tipo
 ----------------------------------------- -------- ----------------------------
 C                                                  NUMBER(38)
 B                                                  NUMBER(38)
 
-- Alterando coluna A para visível
 
SQL> alter table exemplo modify (a visible);
 
Tabela alterada.
 
SQL> select column_name, column_id
  2     from dba_tab_cols
  3    where table_name='EXEMPLO'
  4    order by 2
  5  /
 
COLUMN_NAME      COLUMN_ID
--------------- ----------
C                        1
B                        2
A                        3
 
SQL> desc exemplo
 Nome                                      Nulo?    Tipo
 ----------------------------------------- -------- ----------------------------
 C                                                  NUMBER(38)
 B                                                  NUMBER(38)
 A                                                  NUMBER(38)

Referência:

http://docs.oracle.com/cd/E16655_01/server.121/e17636/tables.htm#ADMIN13866

Mais informações →

Função Deterministica (FUNCTION DETERMINISTIC)

As funções deterministicas existentes desde a versão 8i são conhecidas por muitos DBA’s e Desenvolvedores apenas como necessárias para a criação de índices baseados em função (Function based index – FBI). Certamente que elas são necessárias para as FBI, mas o que elas realmente fazem? ou melhor, proporcionam? 

Quando criamos uma function (create function) podemos especificar ou omitir a palavra DETERMINISTC. Quando especificada permite que a execução da SQL salve uma copia do resultado de retorno da função para poder prover maior performance nas chamadas subsequentes com o mesmo valor de entrada na mesma SQL sem a necessidade de reexecutar a função. O otimizador escolhe a melhor performance entre obter a copia dos resultados salva ou chamar novamente a função. 

Vou demonstrar como isto funciona e o ganho de performance.

SQL> create or replace function f_exemplo(value number) return number as
  2    retorno number;
  3  begin
  4    select trunc(value / 3) into retorno from dual;
  5    return retorno;
  6  end;
  7  /
 
Function created.

Observem que criei a FUNCTION SEM A PALAVRA DETERMINISTIC. Abaixo vou criar e popular uma tabela EXEMPLO.

SQL> create table exemplo(
  2     valor number(5)
  3  )
  4  /
 
Table created.
 
SQL> insert into exemplo select 10 from dual connect by level <=100000;
 
100000 rows created.
 
SQL> commit;
 
Commit complete.

Vou habilitar um trace da minha sessão e executar a seguinte query:

SQL> alter session set tracefile_identifier=exemplo;
 
Session altered.
 
SQL> alter session set sql_trace=true;
 
Session altered.
 
SQL> select f_exemplo(valor), count(1)
  2    from exemplo
  3   where f_exemplo(valor) = 3
  4   group by f_exemplo(valor);
 
F_EXEMPLO(VALOR)   COUNT(1)
---------------- ----------
               3     100000
 
SQL> exit

Agora gerando um TKPROF e verificando o arquivo gerado (exemplo.txt)

serv1:oracle:dbteste> tkprof dbteste_ora_22508_EXEMPLO.trc exemplo.txt
 
TKPROF: Release 10.2.0.5.0 - Production on Tue Jun 11 17:30:30 2013
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
serv1:oracle:dbteste>
 

********************************************************************************
 
select f_exemplo(valor), count(1)
  from exemplo
 where f_exemplo(valor) = 3
 group by f_exemplo(valor)
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      6.93       8.23         43        156          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      6.93       8.23         43        157          0           1
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: ANDERSON
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  HASH GROUP BY (cr=156 pr=43 pw=0 time=10872483 us)
 100000   TABLE ACCESS FULL EXEMPLO (cr=156 pr=43 pw=0 time=5500249 us)
 
********************************************************************************
 
SELECT TRUNC(:B1 / 3)
FROM
 DUAL
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 200000      1.58       1.80          0          0          0           0
Fetch   200000      0.73       0.84          0          0          0      200000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   400001      2.31       2.64          0          0          0      200000
 
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: ANDERSON   (recursive depth: 1)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
 200000  FAST DUAL  (cr=0 pr=0 pw=0 time=224070 us)
 
********************************************************************************

Observem que tivemos 1 execução (Execute) da query levando 8.23 segundos (elapsed). 

Mais abaixo temos a chamada que esta query fez sobre a função f_exemplo. Tivemos 200000 execuções (Execute) resultando em 2.64 segundos totais. 

Mas porque foram 200.000 execuções e não 100.000? Porque fazemos 2 chamadas da função na mesma query, deste modo temos o dobro de execuções baseado na quantidade de registros verificados. 

Agora vou recriar a mesma função (F_EXEMPLO) porem especificando a function como DETERMINISTIC.

SQL> create or replace function f_exemplo(value number) return number deterministic as
  2  retorno number;
  3  begin
  4  select trunc(value/3) into retorno from dual;
  5    return retorno;
  6   end;
  7  /
 
Function created.

Habilitamos novamente um trace da sessão e executamos a mesma query!

SQL> alter session set tracefile_identifier=exemplo;
 
Session altered.
 
SQL> alter session set sql_trace=true;
 
Session altered.
 
SQL> select f_exemplo(valor), count(1)
  2    from exemplo
  3   where f_exemplo(valor) = 3
  4   group by f_exemplo(valor);
 
F_EXEMPLO(VALOR)   COUNT(1)
---------------- ----------
               3     100000
 
SQL> exit

Gerei um tkprof, e vejam agora. Menos de 1 segundo foi necessário para executar toda a query. 

E quantas chamadas da função F_EXEMPLO foi feita? Apenas 2, em 0.00 segundos! Uma do select outra da condição (literais), isto porque ele recuperou o retorno salvo para as mesmas entradas do SQL, no caso o VALOR.

********************************************************************************
 
select f_exemplo(valor), count(1)
  from exemplo
 where f_exemplo(valor) = 3
 group by f_exemplo(valor)
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.27       0.52         63        156          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.28       0.53         63        157          0           1
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: ANDERSON
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  HASH GROUP BY (cr=181 pr=63 pw=0 time=526929 us)
 100000   TABLE ACCESS FULL EXEMPLO (cr=181 pr=63 pw=0 time=304362 us)
 
********************************************************************************
 
SELECT TRUNC(:B1 /3)
FROM
 DUAL
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          0          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.00       0.00          0          0          0           2
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: ANDERSON   (recursive depth: 1)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      2  FAST DUAL  (cr=0 pr=0 pw=0 time=3 us)
 
********************************************************************************

COMPARATIVO:

	NON-DETERMINISTIC	DETERMINISTIC
Execuções	200000	2
Tempo	2.64	0.00

Como comentado, as funções DETERMINISTICAS também são necessárias para criar as FBI, observem:

SQL> create index exemplo_idx on exemplo(f_exemplo(valor));
 
Index created.

Agora se eu remover a DETERMINISTC da função vou receber o erro.

SQL> create index exemplo_idx on exemplo(f_exemplo(valor));
create index exemplo_idx on exemplo(f_exemplo(valor))
                                    *
ERROR at line 1:
ORA-30553: The function is not deterministic

Vale lembrar que se for alterado a semantica da função você terá que recriar manualmente os índices baseados em função (FBI) e materialized views. 

Espero que tenham gostado do artigo! dúvidas podem postar abaixo. 

Referência:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/function.htm

Mais informações →

Implementando e utilizando o PL/SQL Profiler

O Oracle Profiler é uma feature presente desde a versão 8i e através de sua utilização é possível verificar quais os objetos do banco de dados foram invocados e o tempo de duração de cada um durante a execução de um objeto PL/SQL. 

O Profiler é um recurso muito útil quando necessitamos realizar uma análise de desempenho, pois conseguimos analisar todos os processos que estão sendo executados em paralelo ao processo principal. 

Vamos descobrir mais deste ótimo recurso? 

Primeiramente vamos criar a estrutura necessária para o Oracle Profiler através da exeução de dois srcipts que estão em $ORACLE_HOME/rdbms/admin, observe que utilizamos o “?” apos o "@" pois representa o ORACLE_HOME.

SQL> @?/rdbms/admin/profload.sql
Pacote criado.
Concess?o bem-sucedida.
Sinonimo criado.
Biblioteca criada.
Corpo de Pacote criado.
Testing for correct installation
SYS.DBMS_PROFILER successfully loaded.
Procedimento PL/SQL concluido com sucesso.

segundo script a ser executado:

SQL> @?/rdbms/admin/proftab.sql
drop table plsql_profiler_data cascade constraints
           *
ERRO na linha 1:
ORA-00942: a tabela ou view n?o existe
drop table plsql_profiler_units cascade constraints
           *
ERRO na linha 1:
ORA-00942: a tabela ou view n?o existe
drop table plsql_profiler_runs cascade constraints
           *
ERRO na linha 1:
ORA-00942: a tabela ou view n?o existe
drop sequence plsql_profiler_runnumber
              *
ERRO na linha 1:
ORA-02289: a sequencia n?o existe
Tabela criada.
Comentario criado.
Tabela criada.
Comentario criado.
Tabela criada.
Comentario criado.
Sequencia criada.

Observe que no segundo script houve alguns erros na remoção dos objetos pelo fato destes ainda não existirem na nossa base de dados e que podem ser ignorados. 

Agora que já realizamos a implementação desta feature, vamos testar sua utilização primeiramente criando alguns objetos que servirão de apoio ao exemplo.

SQL> create table funcionario(cod number, nome varchar2(50));

Tabela criada.

SQL> create table empresa (cod number, fantasia varchar(100));

Tabela criada.

SQL> begin
        for x in 1..100 loop
                insert into funcionario values (x,'FUNCIONARIO '||x);
                DBMS_OUTPUT.put_line('Funcionario '||x||' cadastrado');
        end loop;
end;
/
Procedimento PL/SQL concluido com sucesso.

SQL> create or replace procedure insert_emp is
begin
        insert into empresa values (1,'ORACLE HOME');
        DBMS_OUTPUT.put_line('Empresa cadastrada');
end;
/
Procedimento criado.

SQL> create or replace procedure cadastrar is
begin
        insert_emp;
        insert_func;
end;
/

Procedimento criado.

Para iniciarmos e interrompermos a coleta basta executarmos os seguintes comandos antes e após a execução da nossa procedure. 

DBMS_PROFILER.START_PROFILER(‘qualquer comentário para identificar esta execução’); 

… 

DBMS_PROFILER.STOP_PROFILER; 

Vejamos,

SQL> execute DBMS_PROFILER.START_PROFILER('Exemplo Profiler Oracle Home');
Procedimento PL/SQL concluido com sucesso.
SQL> execute SYS.cadastrar;
Procedimento PL/SQL concluido com sucesso.
SQL> execute DBMS_PROFILER.STOP_PROFILER;
Procedimento PL/SQL concluido com sucesso.

O Profiler populou três tabelas com informações relacionadas.

PLSQL_PROFILER_RUNS contém as informações sobre cada vez que o profiler foi iniciado, incluindo o comentário passado na execução para identificação. 

PLSQL_PROFILE_UNITS contém informações sobre o código PL/SQL executado. Cada procedimento, função, pacote terá sua própria linha nesta tabela. 

PLSQL_PROFILE_DATA contém as linhas do código executado, o tempo de execução do código, entre outros. 

Primeiramente vamos identificar o RUNID da nossa execução:

SQL> set lines 190
SQL> col RUN_COMMENT for a70
SQL> select runid, run_owner, run_date, run_comment from plsql_profiler_runs;
     RUNID RUN_OWNER                        RUN_DATE RUN_COMMENT
---------- -------------------------------- -------- ----------------------------------------------------------------------
         2 SYS                              19/12/11 Exemplo Profiler Oracle Home

Realizando um select na plsql_profiler_units especificando o RUNID da coleta desejada podemos identificar varias outras informações como objetos envolvidos, tipo do objeto e o UNIT_NUMBER, a partir do qual será possível aprofundar a análise.

SQL> select runid, unit_number, unit_type, unit_owner, unit_name, unit_timestamp
  2  from plsql_profiler_units
  3  where runid = &runid
  4  order by unit_number;
Informe o valor para runid: 2
antigo   3: where runid = &runid
novo   3: where runid = 2
     RUNID UNIT_NUMBER UNIT_TYPE                        UNIT_OWNER                       UNIT_NAME                        UNIT_TIM
---------- ----------- -------------------------------- -------------------------------- -------------------------------- --------
         2           1 PACKAGE BODY                     SYS                              DBMS_PROFILER                    03/08/07
         2           2 ANONYMOUS BLOCK                                                              00/00/00
         2           3 ANONYMOUS BLOCK                                                              00/00/00
         2           4 PROCEDURE                        SYS                              CADASTRAR                        19/12/11
         2           5 PROCEDURE                        SYS                              INSERT_EMP                       19/12/11
         2           6 PACKAGE BODY                     SYS                              DBMS_OUTPUT                      03/08/07
         2           7 PROCEDURE                        SYS                              INSERT_FUNC                      19/12/11
         2           8 ANONYMOUS BLOCK                                                              00/00/00
8 linhas selecionadas.

Note que nem todos os registro retornados nos interessam como os de UNIT_OWNER = , estes podem ser removidos da consulta ( AND UNIT_OWNER ” ). Para iniciarmos a análise, partimos da procedure que executamos no início das atividades (UNIT_NUMBER=4) – (RUNID=2).

SQL> set lines 190
SQL> col TEXT for a70
SQL> select pu.unit_name, pd.line#, pd.total_occur passes, round(pd.total_time / 1000000000,5) total_time, us.text text
  2  from plsql_profiler_data pd, plsql_profiler_units pu, user_source us
  3  where pd.runid = &runid
  4  and pd.unit_number = &unit_number
  5  and pd.runid = pu.runid
  6  and pd.unit_number = pu.unit_number
  7  and us.name = pu.unit_name
  8  and us.line = pd.line#
  9  and us.type in ('PACKAGE BODY','PROCEDURE','FUNCTION');
Informe o valor para runid: 2
antigo   3: where pd.runid = &runid
novo   3: where pd.runid = 2
Informe o valor para unit_number: 4
antigo   4: and pd.unit_number = &unit_number
novo   4: and pd.unit_number = 4
UNIT_NAME                             LINE#     PASSES TOTAL_TIME TEXT
-------------------------------- ---------- ---------- ---------- ----------------------------------------------------------------------
CADASTRAR                                 1          0          0 procedure cadastrar is
CADASTRAR                                 5          2     ,00001       insert_emp;
CADASTRAR                                 6          2     ,00004       insert_func;
CADASTRAR                                 8          1          0 end;

Podemos verificar que a procedure CADASTRAR chama 2 novas procedures, já listadas na consulta acima, incluindo o tempo de execução destas onde temos a INSERT_FUNC como mais demorada ,00004 Agora analisando a procedure mais custosa (INSERT_FUNC), cujo UNIT_NUMBER é 7 podemos identificar a causa desta demora e partirmos em busca de uma solução.

SQL> select pu.unit_name, pd.line#, pd.total_occur passes, round(pd.total_time / 1000000000,5) total_time, us.text text
  2  from plsql_profiler_data pd, plsql_profiler_units pu, user_source us
  3  where pd.runid = &runid
  4  and pd.unit_number = &unit_number
  5  and pd.runid = pu.runid
  6  and pd.unit_number = pu.unit_number
  7  and us.name = pu.unit_name
  8  and us.line = pd.line#
  9  and us.type in ('PACKAGE BODY','PROCEDURE','FUNCTION');
Informe o valor para runid: 2
antigo   3: where pd.runid = &runid
novo   3: where pd.runid = 2
Informe o valor para unit_number: 7
antigo   4: and pd.unit_number = &unit_number
novo   4: and pd.unit_number = 7
UNIT_NAME                             LINE#     PASSES TOTAL_TIME TEXT
-------------------------------- ---------- ---------- ---------- ----------------------------------------------------------------------
INSERT_FUNC                               1          0          0 procedure insert_func is
INSERT_FUNC                               5        101     ,00026       for x in 1..100 loop
INSERT_FUNC                               6        100     ,01423               insert into funcionario values (x,'FUNCIONARIO '||x);
INSERT_FUNC                               7        100     ,00084               DBMS_OUTPUT.put_line('Funcionario '||x||' cadastrado');
INSERT_FUNC                              10          1          0 end;

Para efetuarmos a limpeza das tabelas do profiler basta executarmos:

SQL> delete from plsql_profiler_data;
234 linhas deletadas.
SQL> delete from plsql_profiler_units;
8 linhas deletadas.
SQL> delete from plsql_profiler_runs;
1 linha deletada.
SQL> commit;
Commit concluido.

Mais informações →

PL/SQL: ORA-00947: not enough values

Hoje um cliente solicitou apoio para identificar o que estava gerando erro na criação de sua procedure e a deixando inválida na base de dados, pois bem, para identificarmos o que estava acontecendo bastou uma simples verificação:

SQL> show error procedure teste
Errors for PROCEDURE TESTE:
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
60/12    PL/SQL: SQL Statement ignored
60/24    PL/SQL: ORA-00947: not enough values
97/12    PL/SQL: SQL Statement ignored
97/24    PL/SQL: ORA-00947: not enough values

Maravilha, identificamos que o erro é “PL/SQL: ORA-00947: not enough values” mas o que isto quer dizer?

Significa que “Não há valores suficientes”, ou seja, no INSERT está sendo passado uma quantidade inferior de valores do que é necessário ou foi especificado na instrução DML.

Vejamos:

SQL> insert into academico (codigo, nome, telefone) values (1,'ANDERSON');
insert into academico (codigo, nome, telefone) values (1,'ANDERSON')
                                               *
ERROR at line 1:
ORA-00947: not enough values

Neste exemplo podemos verificar que foi especificado 3 colunas porém apenas 2 valores foram passados no values, o mesmo erro também ocorre com instruções INSERT SELECT conforme abaixo:

SQL> insert into academico (codigo, nome, telefone) select codigo, nome from academico_tmp;
insert into academico (codigo, nome, telefone) select codigo, nome from academico_tmp
            *
ERROR at line 1:
ORA-00947: not enough values

Para resolvermos este ERRO basta especificarmos a quantidade correta de valores ou eliminar a coluna desnecessária.

SQL> insert into academico (codigo, nome, telefone) values (1,'ANDERSON',99811456);
 
1 row created.

Ou

SQL> insert into academico (codigo, nome) select codigo, nome from academico_tmp;
 
1 row created.

Mais informações →

Movendo o SPFILE em RAC

Alguns meses atrás fui envolvido em uma atividade onde era necessário remover alguns diskgroups do ASM, isto porque um disco do storage iria ser substituído por outro com capacidade de armazenamento superior, pois bem, além dos datafiles possuíamos também o SPFILE abaixo do diskgroup. E agora, como podemos movimentá-lo e garantir que a instance suba corretamente utilizando o SPFILE localizado em outro diskgroup? 

Não se trata de uma atividade complicada, pelo contrário, muito simples, porém muitos desconhecem como fazer! 

Primeiramente vamos verificar a localização do SPFILE que as instances do RAC foram iniciadas:

SQL> set lines 200
SQL> col PARAMETRO for a30
SQL> col LOCALIZACAO for a70
SQL> select i.INSTANCE_NAME "INSTANCE", NAME "PARAMETRO", VALUE "LOCALIZACAO" from gv$instance i, gv$parameter p where i.inst_id=p.inst_id and upper(p.name)='SPFILE';
 
INSTANCE         PARAMETRO                      LOCALIZACAO
---------------- ------------------------------ ----------------------------------------------------------------------
orcl1            spfile                         +DGDICT/orcl/spfileorcl.ora
orcl2            spfile                         +DGDICT/orcl/spfileorcl.ora

Agora iremos verificar o spfile registrado no OCR, para isto utilizaremos o comando “srvctl config database -d DATABASE_NAME -a”, que terá como saída:

[oracle@rac1 ~]$ $ORA_CRS_HOME/bin/srvctl config database -d orcl -a
rac1 orcl1 /u01/app/oracle/product/10.2.0/db_1
rac2 orcl2 /u01/app/oracle/product/10.2.0/db_1
DB_NAME: orcl
ORACLE_HOME: /u01/app/oracle/product/10.2.0/db_1
SPFILE: +DGDICT/orcl/spfileorcl.ora
DOMAIN: null
DB_ROLE: null
START_OPTIONS: null
POLICY:  AUTOMATIC
ENABLE FLAG: DB ENABLED

Podemos ver que o SPFILE que será utilizado em um restart se mantem o mesmo já utilizado pelas instances no startup, vamos então copiar este SPFILE para outro diskgroup ASM e ajustar a localização do SPFILE registrado no OCR: 

Note que para efetuar a copia, primeiramente foi gerado um pfile para disco e posteriormente um spfile a partir do pfile para o ASM, isto porque: 

* O ASM não suporta arquivos de sistema operacional e o PFILE por ser arquivo texto acaba por não ser suportado. 

* Não é possível criar um spfile from spfile, porém se o ambiente for 11g pode ser utilizada a new feature “create spfile=’localizacao/spfile.ora’ from memory” (entenda mais sobre a feature se for utiliza-la!)

SQL> select instance_name from v$instance;
 
INSTANCE_NAME
----------------
orcl1
 
SQL> create pfile='/usr/tmp/pfile.ora' from spfile='+DGDICT/orcl/spfileorcl.ora';
 
File created.
 
SQL> create spfile='+DGB/orcl/spfileorcl.ora' from pfile='/usr/tmp/pfile.ora';
 
File created.
 
SQL> exit

– Modificando a localização do SPFILE registrado no OCR

[oracle@rac1 ~]$ $ORA_CRS_HOME/bin/srvctl modify database -d orcl -p +DGB/orcl/spfileorcl.ora

Efetuado estes dois passos, vamos realizar uma nova verificação no OCR para certificar que está sendo apontado para o novo SPFILE.

[oracle@rac1 ~]$ $ORA_CRS_HOME/bin/srvctl config database -d orcl -a
rac1 orcl1 /u01/app/oracle/product/10.2.0/db_1
rac2 orcl2 /u01/app/oracle/product/10.2.0/db_1
DB_NAME: orcl
ORACLE_HOME: /u01/app/oracle/product/10.2.0/db_1
SPFILE: +DGB/orcl/spfileorcl.ora
DOMAIN: null
DB_ROLE: null
START_OPTIONS: null
POLICY:  AUTOMATIC
ENABLE FLAG: DB ENABLED

Iremos ajustar o init*.ora ($ORACLE_HOME/dbs/) com a localização do novo SPFILE e replicamos para os demais nodes.

[oracle@rac1 ~]$ echo "SPFILE='+DGB/orcl/spfileorcl.ora'" > $ORACLE_HOME/dbs/initorcl1.ora
[oracle@rac1 ~]$ ssh rac2 "echo \"SPFILE='+DGB/orcl/spfileorcl.ora'\" > $ORACLE_HOME/dbs/initorcl2.ora"

Agora vamos restartar o database para que inicie com o novo spfile:

[oracle@rac1 ~]$ $ORA_CRS_HOME/bin/srvctl stop database -d orcl
[oracle@rac1 ~]$ $ORA_CRS_HOME/bin/srvctl start database -d orcl

SQL> set lines 200
SQL> col PARAMETRO for a30
SQL> col LOCALIZACAO for a70
SQL> select i.INSTANCE_NAME "INSTANCE", NAME "PARAMETRO", VALUE "LOCALIZACAO" from gv$instance i, gv$parameter p where i.inst_id=p.inst_id and upper(p.name)='SPFILE';
 
INSTANCE         PARAMETRO                      LOCALIZACAO
---------------- ------------------------------ ----------------------------------------------------------------------
orcl1            spfile                         +DGB/orcl/spfileorcl.ora
orcl2            spfile                         +DGB/orcl/spfileorcl.ora

Pronto, SPFILE também foi movimentado! Já podemos remover o antigo diskgroup.

Mais informações →

Instalando o Oracle XML DB 11g

Hoje ao testar uma procedure de envio de e-mail utilizando a package UTL_MAIL no Oracle 11g obtive o seguinte erro: 

ORA-24248: XMLDB extensible security not installed 

Bom.. como descrito na mensagem precisamos instalar o Oracle XML DB, mas porque? 

Uma das melhorias propostas no Oracle 11g foi a de maior segurança no acesso aos pacotes de rede como UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP e UTL_INADDR, ou seja, uma execução de privilégio sobre estes pacotes não é mais suficiente para acessar um recurso de rede externa é necessário agora a configuração também de de uma ACL (Access Control List) que será descrita em um próximo artigo, mas ainda em nosso contexto, Essas ACLs são arquivos XML que são armazenados no repositório do banco de dados XML dentro do próprio banco de dados, o que significa que banco de dados XML devem ser instalado no banco de dados para podermos utilizar estes pacotes. 

Vamos verificar se o mesmo não está instalado:

SQL> set lines 200
SQL> col Component for a70
SQL> select comp_name "Component" from dba_registry;
 
Component
----------------------------------------------------------------------
OWB
Oracle Application Express
Oracle Enterprise Manager
Oracle Ultra Search
OLAP Catalog
Spatial
Oracle Multimedia
Oracle Text
Oracle Expression Filter
Oracle Rules Manager
Oracle Workspace Manager
Oracle Database Catalog Views
Oracle Database Packages and Types
JServer JAVA Virtual Machine
Oracle XDK
Oracle Database Java Packages
OLAP Analytic Workspace
Oracle OLAP API
 
18 linhas selecionadas.

Confirmado que não está instalado, vamos instalar o Oracle XML. Primeira precisamos criar uma tablespace para o XML DB:

SQL> create tablespace xmldb datafile '+DGDADOS/datafiles/xmldb01.dbf' size 100m autoextend on next 100m maxsize 3000m;
 
Tablespace criado.

Agora execumos o script catqm localizado dentro do $ORACLE_HOME/rdbms/admin passando os seguintes parâmetros:

SYNTAXE: 

SQL>@?/rdbms/admin/catqm.sql A B C D

onde:

A = XDB pwd
B = XDB default tablespace
C = XDB temporary tablespace
D = SecureFiles = YES/NO

Se o SecureFiles for especificado com YES (SIM), o repositório XDB usará armazenamento SecureFile. Se NO (NÃO), será utilizado LOBS. 

Para usar SecureFiles, a compatibilidade deve ser definida para 11.2. O tablespace especificado para o repositório XDB deve estar usando Automatic Segment Space Management (ASSM) para SecureFiles. 

 Execução:

SQL> @?/rdbms/admin/catqm.sql xml_password xmldb temp yes
 
SQL> ...
SQL> ...
SQL> ...

Vamos confirmar se agora está instalado:

SQL> set lines 200
SQL> col Component for a70
SQL> select comp_name "Component" from dba_registry;
 
Component
----------------------------------------------------------------------
Oracle XML Database
OWB
Oracle Application Express
Oracle Enterprise Manager
Oracle Ultra Search
OLAP Catalog
Spatial
Oracle Multimedia
Oracle Text
Oracle Expression Filter
Oracle Rules Manager
Oracle Workspace Manager
Oracle Database Catalog Views
Oracle Database Packages and Types
JServer JAVA Virtual Machine
Oracle XDK
Oracle Database Java Packages
OLAP Analytic Workspace
Oracle OLAP API
 
19 linhas selecionadas.

Pronto, Oracle XML DB instalado com sucesso e pronto para utilização, simples assim.

Para remover o mesmo basta executar:

SQL> @?/rdbms/admin/catnoqm
SQL> alter tablespace XMLDB offline;
SQL> drop tablespace XMLDB including contents;

Mais informações →