Oracle y yo

SQL> create table t
2 (column1 number not null,
3 constraint pk_t PRIMARY KEY (column1));

Table created.

2.- Le insertamos datos

SQL> insert into t (select rownum from dba_objects);

72137 rows created.

SQL> commit;

Commit complete.

3.- Llevamos a cabo una consulta para ver su plan de ejecución

SQL> explain plan for
2 select * from t
3 where column1 = 10000;

Explained.

SQL> set linesize 130
SQL> set pagesize 0
SQL>
SQL> select * from table(DBMS_XPLAN.DISPLAY);
Plan hash value: 1517170033

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| PK_T | 1 | 13 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - access("COLUMN1"=10000)

13 rows selected.

Como se puede apreciar, ocupa de forma clara , el índice PK_T

4.- Dejamos el índice como INVISIBLE y vemos nuevamente el plan de ejecución

SQL> alter index pk_t invisible;

Index altered.

SQL> explain plan for
2 select * from t
3 where column1 = 10000;

Explained.

SQL> select * from table(DBMS_XPLAN.DISPLAY);
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 91 | 1183 | 32 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 91 | 1183 | 32 (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("COLUMN1"=10000)

13 rows selected.

Como se puede apreciar, ahora el índice no es VISIBLE , pero en el fondo no es visible para el optimizador, por el cual queda fuera de los planes de ejecución que el optimizador pueda generar.

5.- Aunque utilicemos explicitamente el nombre del índice con un hint, este no será utilizado

SQL> explain plan for
2 select /*+ index(t pk_t) */ * from t where column1 = 20000;

Explained.

SQL> select * from table(DBMS_XPLAN.DISPLAY);
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 91 | 1183 | 32 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 91 | 1183 | 32 (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("COLUMN1"=20000)

13 rows selected.

6.- Podemos ver el estado de un índice INVISIBLE consultando cualquier vista *_INDEXES

SQL> select owner , index_name , visibility from dba_indexes where index_name like 'PK_T';

OWNER INDEX_NAME VISIBILIT
------------------------------ ------------------------------ ---------
SYS PK_T INVISIBLE

7.- Existe un parámetro de inicialización llamado OPTIMIZER_USE_INVISIBLE_INDEXES, que le indica al optimizador si toma o no en cuenta los índices en estado INVISIBLE cuando esta realizando los planes de ejecución, por defecto está en FALSE, o sea, no toma en cuenta los índices INVISIBLES.

SQL> show parameter optimizer_use_invi

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes boolean FALSE

8.- Pero si cambiamos este parámetro y lo dejamos en TRUE, toma en cuenta los índices aunque ellos estén INVISIBLES

SQL> alter system set optimizer_use_invisible_indexes=TRUE scope=both;

System altered.

SQL> select owner , index_name , visibility from dba_indexes where index_name like 'PK_T';

OWNER INDEX_NAME VISIBILIT
------------------------------ ------------------------------ ---------
SYS PK_T INVISIBLE

SQL>
SQL> explain plan for
select /*+ index(t pk_t) */ * from t where column1 = 20000; 2

Explained.

SQL> select * from table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1517170033

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| PK_T | 1 | 13 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - access("COLUMN1"=20000)

13 rows selected.

Lee también sobre

Nueva característica de los índices UNUSABLES

Creando un índice INVISIBLE

Espero que les sirva

De Lunes a Viernes la cantidad de Switch en mi base de datos es 4 por hora, o sea, cada 15 minutos se generá un archive, por ende cada 15 minutos se actualiza mi base de datos StandBy , por lo tanto, si se llegase a producir un error o corrupción de mi base de datos principal, a lo más, perdería 15 minutos (por la información de redo que queda en los grupos de redologs) .

Pero el fin de semana, no se hace ningún Switch , o sea, el grupo current será durante todo el fin de semana el mismo grupo, y no puedo darme el lujo de perder el grupo current del fin de semana, pues son pocas transacciones , pero críticas.

¿Podría colocar un cron el fin de semana para que se ejecute cada 30 minutos y efectúe un Switch de Redo? , si.. pero sería poco elegante

¿Cómo lo soluciono? ... mmmmm

Log Switch on hour basis

DIA              10  11  12  13  14  15  16  17  18  19  20  21  22  23  TOTAL
---------------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- -----
WED, 28-OCT-2009 -   -   -   -   -   -   -   -   -   -   2   -   -   -       2

Ahora, procedemos a cambiar el parámetro ARCHIVE_LAG_TARGET , con lo cual aceleramos los Switch de Redo y provocaremos un gran impacto ¿cuál? , pues el de minimizar la información pérdida si se llega a corromper nuestro Grupo de Redo current, ¿por qué? , pues porque la información será pasada de forma más rápida a mi base de datos StandBy

Ejemplo práctico :

Modificamos el ARCHIVE_LAG_TARGET y lo dejamos en 2 minutos

SQL> alter system set archive_lag_target=120 scope=both;

System altered.

Con lo anterior , si analizamos el archivo de alerta , veremos que efectivamente se producen Switch de Redo cada 2 minutos

Sun Jan 31 22:45:58 2010
Thread 1 cannot allocate new log, sequence 18
Current log# 1 seq# 17 mem# 0: /home/oracle/app/oracle/oradata/db10g/redo01.log
Thread 1 advanced to log sequence 18
Current log# 2 seq# 18 mem# 0: /home/oracle/app/oracle/oradata/db10g/redo02.log
Sun Jan 31 22:49:02 2010
Thread 1 advanced to log sequence 19
Current log# 3 seq# 19 mem# 0: /home/oracle/app/oracle/oradata/db10g/redo03.log
Sun Jan 31 22:53:57 2010
Thread 1 advanced to log sequence 20
Current log# 1 seq# 20 mem# 0: /home/oracle/app/oracle/oradata/db10g/redo01.log
Sun Jan 31 22:59:01 2010
Thread 1 advanced to log sequence 21
Current log# 2 seq# 21 mem# 0: /home/oracle/app/oracle/oradata/db10g/redo02.log
Sun Jan 31 23:01:05 2010
Thread 1 advanced to log sequence 22
Current log# 3 seq# 22 mem# 0: /home/oracle/app/oracle/oradata/db10g/redo03.log
Sun Jan 31 23:03:05 2010
Thread 1 advanced to log sequence 23
Current log# 1 seq# 23 mem# 0: /home/oracle/app/oracle/oradata/db10g/redo01.log
Sun Jan 31 23:05:03 2010
Thread 1 cannot allocate new log, sequence 24
Current log# 1 seq# 23 mem# 0: /home/oracle/app/oracle/oradata/db10g/redo01.log
Thread 1 advanced to log sequence 24
Current log# 2 seq# 24 mem# 0: /home/oracle/app/oracle/oradata/db10g/redo02.log

Miramos la actividad de nuestra instancia , en cuanto a Switch por horas, y claro... se refleja efectivamente el cambio

DIA              10  11  12  13  14  15  16  17  18  19  20  21  22  23  TOTAL
---------------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- -----
WED, 28-OCT-2009 -   -   -   -   -   -   -   -   -   -   2   -   -   -       2
SUN, 31-JAN-2010 -   -   -   -   -   -   -   -   -   -   -   -   19  2      21

En resumidas cuentas el parámetro ARCHIVE_LAG_TARGET nos permite acelerar los Switch de Redo, ¿para qué nos puede servir eso? , pues para mantener una sincronización eficaz con nuestra base de datos StandBy o simplemente para que la cantidad de información que podríamos perder en un crash , siempre sea la mínima

Esto es aplicable tanto para bases de datos primarias (con StandBy) , como para base de datos StandAlone

Pues bien , la información que todo mortal necesita

Archive_lag_target en Oracle9i
Archive_lag_target en Oracle10g
Archive_lag_target en Oracle11gr1
Archive_lag_target en Oracle11gr2

ALTER SYSTEM SET SQL_TRACE=TRUE;

Esta forma es una manera sencilla de tracer una sesión , pero existe otra mucho más potente, la activación del evento 10046

Acá va una pequeña explicación

El Trace Event 10046 es una forma de generar trace de una consulta en partícular, muy parecida a SQL_TRACE, pero esta última adolece de un problema que es la necesidad de ejecutar el comando desde la misma sesión que esta provocando el evento de espera.

Puede tracear procesos background de Oracle y los archivos resultantes los deja en la carpeta BDUMP (pre 11gr1) , si estamos traceando procesos de usuarios, los achivos de trace resultantes los deja en la carpeta UDUMP

Nota : Cuando se indica BDUMP , se está haciendo mención a la carpeta que esta mencionada en el parámetro background_dump_dest
Y cuando se indica UDUMP , el parámetro asociado es el user_dump_dest.

¿Qué es lo que hace tan potente esta herramienta?, pues bien , este evento proporciona muchos más detalles del comportamiento de una sentencia SQL (o de varias) , para esto , se pueden setear niveles de traceo.

¿Qué niveles de traceo están disponibles?

Level 0 : El traceo está deshabilitado
Level 1 : Traceo standar , tal cual lo crea SQL_TRACE
Level 4 : Genera un Level 1 + información de las variables bind
Level 8 : Genera un Level 1 + información de los eventos de espera
Level 12 : Genera un Level 8 + información de las variables bind

El tema de los niveles hay que tomarlo con mucha calma, pues se puede generar muchísima información con un nivel 12, aunque muchísima de esa información es de un nivel muy bajo y nos puede dar pistas de lo que está sucediendo con una sentencia o un grupo de sentencias en partìcular.

¿Qué parámetros son necesarios para utilizar el Trace Event 10046?

Para que el Trace Event 10046 pueda ser utilizado de forma óptima, debiesen setearse los parámetros TIMED_STATISTICS en TRUE y tener especial cuidado en el parámetro MAX_DUMP_FILE_SIZE, pues este parámetro es el que controla el tamaño máximo de los archivos de trace.

¿Cómo usar este evento?
Pues existen muchas formas de utilizarlo, aquí mencionaremos la más sencilla y en posteriores post , indicaremos unas más avanzadas.

Para una sesión en partícular

SQL> alter session set timed_statistics = true;
SQL> alter session set max_dump_file_size = unlimited;
SQL> alter session set events '10046 trace name context forever, level 12';

REM Se ejecutan todos los scripts , consultas y programas habidos y por haber y se desactiva el Trace Event

SQL> alter session set events '10046 trace name context off';

Nota : Cabe mencionar lo potente y agresivo que es el nivel 12, se debe manejar con precaución.

Ejemplo práctico

SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL>
SQL>
SQL> alter system set timed_statistics=TRUE;
System altered.

SQL>
SQL> alter system set max_dump_file_size = unlimited;
System altered.

SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.

Y si queremos ver como avanza nuestra trace , pues en otra pantalla de Sql*Plus, mostramos el contenido del parámetro user_dump_dest

SQL> show parameter user_dump

Nos vamos a ese directorio y al último trace generado , le colocamos un tail -f para apreciar la cantidad de información que genera.

Después de eso, en nuestra sesión traceada , ejecutamos algunos comandos

SQL> select * from v$instance;
SQL> create table ejemplo as select * from dba_objects;
SQL> drop table ejemplo;

Al archivo generado , le aplicamos tkprof

[oracle@oracle10g udump]$ tkprof orcl_ora_2238.trc salida.txt

Y ya tenemos , un archivo formateado , con absolutamente toda la información del proceso que acabamos de realizar

¿Qué información se visualiza?

La información que se muestra , es muy parecida al SQL_TRACE , pero mucho más potente , podríamos hablar entonces de un DON SQL_TRACE

********************************************************************************

select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1,
spare2
from
obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null
and linkname is null and subname is null

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 81 0.03 0.02 0 0 0 0
Fetch 81 0.03 0.11 7 237 0 75
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 166 0.06 0.14 7 237 0 75

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=2 pr=0 pw=0 time=246 us)
0 INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=234 us)(object id 37)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 7 0.05 0.10

Referencias :
EVENT: 10046 "enable SQL statement tracing (including binds/waits)" [ID 21154.1]
Recommended Method for Obtaining 10046 trace for Tuning [ID 376442.1]

Espero les haya servido

PD : A quienes se pregunten , ¿que relación hay entre Darth Vader y el evento 10046? , en realidad ninguna.. pero se ve bien ..

SQL> r
1 select to_char(BEGIN_TIME,'dd-mm-yyyy hh24:mi:ss') "Fecha Inicio" ,
2 to_char(END_TIME,'dd-mm-yyyy hh24:mi:ss') "Fecha Termino" ,
3 INTSIZE_CSEC ,
4 GROUP_ID ,
5 METRIC_ID ,
6 METRIC_NAME ,
7 VALUE ,
8 METRIC_UNIT
9 from v$sysmetric
10* where metric_name like '%Buffer Cache Hit Ratio%'

Y podríamos recopilar información como la que sigue

Fecha Inicio Fecha Termino INTSIZE_CSEC GROUP_ID METRIC_ID METRIC_NAME VALUE METRIC_UNIT
------------------- ------------------- ------------ ---------- ---------- ------------------------- ------- ----------------------------------------
11-11-2009 20:11:15 11-11-2009 20:12:15 6000 2 2000 Buffer Cache Hit Ratio 100.00 % (LogRead - PhyRead)/LogRead
11-11-2009 20:12:45 11-11-2009 20:13:00 1500 3 2000 Buffer Cache Hit Ratio 100.00 % (LogRead - PhyRead)/LogRead

El problema principal (si se le puede llamar problema) es que sólo muestra 2 puntos y con eso, claramente no sabremos la tendencia de nuestra base de datos , por ende necesitamos más puntos, ¿de dónde obtenerlos?, pues del historial de esa vista , la V$SYSMETRIC_HISTORY

1 select to_char(BEGIN_TIME,'dd-mm-yyyy hh24:mi:ss') "Fecha Inicio" ,
2 to_char(END_TIME,'dd-mm-yyyy hh24:mi:ss') "Fecha Termino" ,
3 INTSIZE_CSEC ,
4 GROUP_ID ,
5 METRIC_ID ,
6 METRIC_NAME ,
7 VALUE ,
8 METRIC_UNIT
9 from v$sysmetric_history
10 where metric_name like '%Buffer Cache Hit Ratio%'
11* order by 1

Acá nos muestra la última hora de las métricas que necesitamos visualizar , lo cual ya se puede sentir como algo más elaborado , o sea, "les presentamos el comportamiento del Database Buffer Cache durante la última hora" , suena bien , está vista nos entrega información de la última hora con un total de registros de 74

Y si queremos tener una curva algo más prolongada, quizás, el último mes del Database Buffer Cache, pues allí debemos consultar la vista DBA_HIST_SYSMETRIC_HISTORY

La información viaja así ...... después de una hora en memoria en las vistas V$SYSMETRIC y V$SYSMETRIC_HISTORY , el proceso Background MMON descarga la información de estás vistas al diccionario de datos , esa información la encontramos en la vista DBA_HIST_SYSMETRIC_HISTORY

Si piensan un poco , lo anterior es lo que sucede cuando el MMON captura la información estadística desde la SGA , o sea, son los Snapshots del AWR , entre más Snapshots existan , la curva puede ser más amplia cuando analicemos información estadística, que interesante!!!

La forma en que pueden realizar los gráficos mediante la información proveniente de estas tablas, la pueden encontrar muy detallada, paso a paso en el siguiente artículo

Graficar en Excel información proveniente desde Statspack

Hay que extrapolar un poco , la información en este caso no viene del Statspack , proviene de las vistas de Oracle, pero para el caso, es aplicable 500%

¿Qué otra información es útil como métrica?

La verdad muchas otras, como por ejemplo

* CPU Usage Per Sec
* Disk Sort Per Sec
* Host CPU Utilization (%)
* Leaf Node Splits Per Sec
* Library Cache Hit Ratio
* Memory Sorts Ratio
* PGA Cache Hit %
* Physical Reads Per Sec
* Redo Generated Per Sec
* Soft Parse Ratio
* Etc,etc.

La información está , es llegar y graficar

Mayor descripción de la vista V$SYSMETRIC

De la vista V$SYSMETRIC_HISTORY

Y de la vista DBA_HIST_SYSMETRIC_HISTORY

Espero que les sirva

[oracle@SQL]$ sqlplus usuario/password

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 10 20:58:22 2009

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> start file
select COUNT(*)
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 887
Session ID: 134 Serial number: 162

SQL>

Tratamos de hacer un trace, se cae, tratamos de generar un evento de traceo , se cae y así , por siempre y para siempre..

Lo mejor es abrir un caso en Oracle, ellos debiesen solucionarlo (no siempre es así, pero les tengo fé)

Y cuando vamos al ORACLE_BASE, nos damos cuenta de que no existe, claro estamos en 11gr2

Nos colocamos el machete en la boca y nos metemos a los directorios de log y trace (llamado diag) y nos encontramos con muchos de ellos

[oracle@SQL]$ ls -ltr
total 136
drwxr-x--- 2 oracle oinstall 4096 Nov 6 00:17 hm
drwxr-x--- 2 oracle oinstall 4096 Nov 6 00:17 alert
drwxr-x--- 2 oracle oinstall 4096 Dec 10 20:37 metadata
drwxr-x--- 2 oracle oinstall 4096 Dec 10 20:37 ir
drwxr-x--- 4 oracle oinstall 4096 Dec 10 20:58 incident
drwxr-x--- 8 oracle oinstall 12288 Dec 10 20:58 cdump
drwxr-x--- 2 oracle oinstall 4096 Dec 10 20:58 sweep
drwxr-x--- 2 oracle oinstall 20480 Dec 10 20:58 stage
drwxr-x--- 2 oracle oinstall 4096 Dec 10 20:58 lck
drwxr-x--- 4 oracle oinstall 4096 Dec 10 21:09 incpkg
drwxr-x--- 4 oracle oinstall 69632 Dec 10 21:20 trace
[oracle@SQL]$ pwd
/u01/app/oracle/diag/rdbms/inst1/inst1
[oracle@SQL]$

Es ahora donde nos entra la desesperación y nos preguntamos ¿Qué #$"°| le envíamos a Oracle?

Pues bien , allí es donde ingresa el ADRCI, el utilitario para hacernos la vida más fácil con la nueva estructura de diagnóstico que nos provee Oracle

He aquí un pequeño ejercicio

1.- Nos conectamos al adrci

[oracle@SQL]$ adrci

ADRCI: Release 11.2.0.1.0 - Production on Thu Dec 10 21:03:05 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

ADR base = "/u01/app/oracle"
adrci>

2.- Verificamos los ORACLE_HOME existente (son mas de 1 ya que estamos trabajando con el DIAG)

adrci> show homes
ADR Homes:
diag/tnslsnr/maq1/listener
diag/rdbms/inst1/inst1
adrci>

3.- Para poder trabajar con el adrci, se debe por obligación seleccionar uno de estos ORACLE HOMES

adrci> set homepath diag/rdbms/inst1/inst1
adrci>
adrci> show homes
ADR Homes:
diag/rdbms/inst1/inst1
adrci>

4.- Una vez seleccionado nuestro ORACLE_HOME, procedemos a ver los "problemas" e "incidentes"
Sólo como introducción , pues generaré un post al respecto, un problema genera múltiples incidentes y un problema es casi siempre crítico, o sea, solucionando el problema se acaban los incidentes (suena a ITIL )

adrci> show problem

ADR Home = /u01/app/oracle/diag/rdbms/inst1/inst1:
*************************************************************************
PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
1 ORA 7445 [_intel_fast_memcmp()+30] 275 2009-12-10 20:58:26.261000 -03:00
1 rows fetched

5.- Aparece claramente el problema, un error ORA-07445, ahora queremos ver los incidentes que ha originado ese problema

adrci> show incident

ADR Home = /u01/app/oracle/diag/rdbms/inst1/inst1:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
251 ORA 7445 [_intel_fast_memcmp()+30] 2009-12-10 20:41:07.496000 -03:00
275 ORA 7445 [_intel_fast_memcmp()+30] 2009-12-10 20:58:26.261000 -03:00
2 rows fetched

adrci>

Como se puede apreciar, ha generado varios incidentes , pero sólo necesitamos uno de ellos como para comenzar a procesar la información y poder "empaquetar" todos los archivos necesarios.

6.- Para ello , tomamos el INCIDENT_ID que necesitamos y ejecutamos el siguiente comando

adrci> ips pack incident 275 in /home/oracle
Generated package 2 in file /home/oracle/ORA7445_i_20091210210936_COM_1.zip, mode complete
adrci>

7.- Una vez ejecutado el comando , tenemos un zip con TODA LA INFORMACIÓN necesaria para soporte Oracle y casi ... no nos despeinamos

Más información del ADRCI

Espero les sirva...