Category: Tuning / Performance

SQLT : Una poderosa herramienta para hacer tuning a sentencias SQL



Yo había escuchado hace un tiempo sobre una herramienta que fue generada por el Sr. Carlos Sierra para el centro de soporte Oracle, llamada SQLXEXPLAIN o comúnmente llamada SQLT , nunca la había usado, hasta ayer :)

Es una herramienta buenísima y extremadamente poderosa, que presenta en un formato HTML , todo el análisis sobre una sentencia SQL ya sea que este en un archivo SQL , en memoria o dentro del AWR, en sintésis si quieres hacer un tuning de calidad, pues he aquí la solución



He aquí un pequeño uso de esa herramienta

Uso básico del SQLT

by Ligarius
14.07.11. 09:30:47. 104 words, 10304 views. Categories: SQL / Programación, Tuning / Performance ,

Oracle Database 11g : Performance Tuning



Aquí nuevamente desde el inframundo

Hoy fuí a rendir por tercera vez el exámen 1z0-054 , me había ido mal en la primera, no me presente a la segunda :>> y hoy por fin obtuve mi certificación

Oracle Database 11g : Performance Tuning Certified Expert



El exámen tenía muchas orientaciones, claramente me sirvió impartir el curso tuning de 11g |-| jejeje

Así que ahora a seguir estudiando , ¿por qué? Pues tengo agendado para fin de Junio de este año el exámen Beta 1z1-058 : Oracle Real Application Clusters 11g release 2 and Grid Infraestructure, no lo puedo aplazar más dado que parece que se vence la fecha para darlo como beta y después sería un exámen normal de Oracle, lo compré pues estaba barato :yes: , sólo 50 doláres.

Así que eso, a seguir estudiando y a no dormirse en los laureles...

¿Cómo queda mi historial? Pues así...

1.- Certificación : Oracle Database 11g : Performance Tuning Certified Expert (OCE 11g Tuning) New
Exámenes rendidos
1z0-054 : Oracle Database 11g : Performance Tuning

2.- Certificación : Oracle Database 10g : Managing Oracle on Linux Certified Expert (OCE 10g Linux)
Exámenes rendidos
1z0-046 : Oracle Database 10g : Managing Oracle on Linux for Database Administrators

3.- Certificación : Oracle Database 10g : Real Application Cluster Administrator Certified Expert (OCE 10g RAC)
Exámenes rendidos
1z0-048 : Oracle Database 10gR2 : Administering RAC

4.- Certificación : Database 11g Administrator Certified Professional (OCP 11g DBA)
Exámenes rendidos
1z0-050 : Oracle Database 11g : New Features for Administrators

5.- Certificación : Oracle Database : SQL Certified Expert (OCE 10g SQL)
Exámenes rendidos
1z0-047 : Oracle Database SQL Expert Exam

6.- Certificación : Database 10g Administrator Certified Professional (OCP 10g DBA)
Exámenes rendidos
1z0-043 : Oracle Database 10g : Administration II

7.- Certificación : Database 10g Administrator Certified Associated (OCA 10g DBA)
Exámenes rendidos
1z0-042 : Oracle Database 10g : Administration I

8.- Certificación : Oracle Forms Developer Certified Professional (OCP Forms 6i)
Exámenes rendidos
1z0-131 : Oracle Forms Build Internet Applications I
1z0-132 : Oracle Forms Build Internet Applications I

9.- Certificación : Oracle Pl/Sql Developer Certified Associate (OCA SQL Pl/Sql 8i)
Exámenes rendidos
1z0-101 : Develop Pl/Sql Program Unit
1z0-001 : Introduction to Oracle : SQL and Pl/Sql

by Ligarius
31.05.11. 22:00:27. 385 words, 5486 views. Categories: Tuning / Performance, Certificaciones ,

Oracle 11gr1 : Índices INVISIBLES (INVISIBLE INDEX)



A partir de Oracle11g nace una nueva característica que se llama índices INVISIBLES



¿Cuál es la gracia principal? Es que puedo deshabilitar los índices, sin necesidad de dejaros UNUSABLES y sin la necesidad de borrarlos, lo cual
implica que se pueden habilitar y deshabilitar de forma muy rápida.

En el fondo dejar un índice como INVISIBLE , es dejarlo fuera del alcance del optimizador cuando esta generando los planes de ejecución

Sólo imaginenlo para un índice de 10GB ¿lo borrarían para saber com oanda su aplicación? , creo que no... pues bien un índice INVISIBLE es la solución a la problemática.

Una de las principales ventajas de los índices invisibles sobre los índices no USABLES , es que los índices INVISIBLES si están afectados por las DML que se le pueda hacer a su tabla de orígen , o sea, mientras estén INVISIBLES, siguen actualizandose ... genial!!

Un ejemplo práctico

1.- Se genera una estructura de tabla con su clave primaria

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

by Ligarius
13.04.10. 15:02:29. 713 words, 9136 views. Categories: Base de datos, Oracle 11g, Tuning / Performance ,

Parámetro ARCHIVE_LAG_TARGET : Más que un simple parámetro



Imagínense la siguiente situación

"
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

"



La forma de solucionar eso , es mediante la modificación del parámetro ARCHIVE_LAG_TARGET , este parámetro esta orientado a bases de datos con una StandBy , pues forzan el Switch de Redo según la cantidad de segundos especificado en ese parámetro.

Lo principal es saber la cantidad de switch que se provocan en la base de datos, para eso podemos analizar la siguiente nota

Analizando la cantidad de Switch de Redo

Si se fijan no hay actividad en los switch de redo, al momento de la ejecución , pero eso no indica que no haya transacciones que estén pendientes de ser almacenadas en los archives, por ende hay transacciones pendientes de ser pasadas a mi base de datos StandBy (eventualmente)
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

by Ligarius
08.02.10. 09:56:35. 782 words, 6229 views. Categories: Base de datos, Tuning / Performance ,

Hacer un tracer con el evento 10046 : Trace Event 10046



Una de las formas más sencillas de llevar a cabo un trace de alguna sesión en partícular , es mediante el simple comando

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 :P

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 .. :>>:>>:>>:>>


by Ligarius
02.02.10. 07:39:37. 863 words, 6278 views. Categories: Base de datos, Tuning / Performance ,

<< 1 2 3 4 5 6 7 8 >>