Subscribe: El Blog de Leonardo Horikian
http://lhorikian.blogspot.com/feeds/posts/default
Added By: Feedage Forager Feedage Grade B rated
Language: Spanish
Tags:
appending output  complete complete  count  datos  nohup appending  nohup nohup  nohup  output nohup  select  sql  test 
Rate this Feed
Rate this feedRate this feedRate this feedRate this feedRate this feed
Rate this feed 1 starRate this feed 2 starRate this feed 3 starRate this feed 4 starRate this feed 5 star

Comments (0)

Feed Details and Statistics Feed Statistics
Preview: El Blog de Leonardo Horikian

El Blog de Leonardo Horikian



Oracle Performance



Updated: 2018-04-16T09:07:14.656-03:00

 



0 Comentarios

2017-01-27T12:08:17.345-03:00

Estimados seguidores, 

Luego de 10 años de estar trabajando en las oficinas de Oracle, me encuentro laboralmente en busca de nuevos desafíos! Si tienen alguna propuesta laboral que me pueda llegar a interesar, por favor enviarme un mensaje a través de LinkedIn. 

Saludos, 
Leonardo
(image)



Simposio HOTSOS 2012

2012-02-10T14:03:38.288-03:00

(image)

Todos los años, se realiza un evento llamado Simposio HOTSOS. Este evento esta exclusivamente dedicado a Oracle Performance Tuning.

El Simposio se realiza todos los años en el mes de Marzo en la ciudad de Dallas, Texas (USA). Esta conferencia es única a nivel mundial! Reune a los mejores expertos de Oracle del mundo!

En el año 2010 y 2011, tuve la oportunidad de poder viajar y presenciar el Simposio. Definitivamente se lo recomiendo a todas las personas que utilizan Oracle y que se especializan o que se encuentran interesadas en lo que respecta a Oracle Performance Tuning.(image)



Eliminar una consulta de la Shared Pool

2010-07-06T17:34:33.665-03:00

A partir de Oracle 10.2.0.4 en adelante, el paquete DBMS_SHARED_POOL contiene nuevos procedimientos. Uno de esos procedimientos se llama PURGE y es el encargado de eliminar determinados objetos de la Shared Pool. Estos objetos pueden ser: cursores (consultas SQL), paquetes, procedimientos, funciones, triggers, secuencias y tipos.En versiones anterior, esto no podíamos hacerlo y lo que debíamos hacer era eliminar todas las consultas de la Shared Pool mediante la sentencia "ALTER SYSTEM FLUSH SHARED_POOL".La creación del paquete DBMS_SHARED_POOL podemos encontrarla en $ORACLE_HOME/rdbms/admin/dbmspool.sqlEn Oracle 10.2.0.4 existe el bug 5614566 que provoca el mal funcionamiento del procedimiento PURGE. Pero, tenemos un manera de evitar este bug en esa versión.Comencemos viendo un ejemplo en 11gR1:SQL> desc DBMS_SHARED_POOLPROCEDURE ABORTED_REQUEST_THRESHOLD Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- THRESHOLD_SIZE NUMBER INPROCEDURE KEEP Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- NAME VARCHAR2 IN FLAG CHAR IN DEFAULTPROCEDURE PURGE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- NAME VARCHAR2 IN FLAG CHAR IN DEFAULT HEAPS NUMBER IN DEFAULTPROCEDURE SIZES Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- MINSIZE NUMBER INPROCEDURE UNKEEP Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- NAME VARCHAR2 IN FLAG CHAR IN DEFAULTPara ejecutar el procedimiento PURGE, debemos pasar como valor en el parámetro NAME el ADDRESS junto con el HASH_VALUE separados por una coma(,).Estos valores podemos encontrarlos en la vista V$SQLAREA.SQL> SELECT /* prueba */ 1 FROM DUAL; 1---------- 1SQL> SELECT address||','||hash_value name FROM v$sqlarea WHERE sql_text = 'SELECT /* prueba */ 1 FROM DUAL';NAME-------------------2E10E868,4198164882Como podemos ver, lo que hicimos fue ejecutar una consulta para que sea parseada por primera vez y almacenada en la Shared Pool. Luego buscamos en la vista V$SQLAREA los valores que debo pasar en el parámetro NAME del procedimiento PURGE.Ahora vamos a eliminar la consulta de la Shared Pool y a verificar que se haya eliminado.SQL> exec dbms_shared_pool.purge('2E10E868,4198164882','C',1);PL/SQL procedure successfully completed.SQL> SELECT address||','||hash_value name FROM v$sqlarea WHERE sql_text = 'SELECT /* prueba */ 1 FROM DUAL';no rows selectedLa consulta fue eliminada con éxito!Para hacer funcionar el procedimiento en 10.2.0.4, debemos realizar el siguiente alter en la sesión antes de ejecutar el procedimiento PURGE:SQL> alter session set events '5614566 trace name context forever';Pueden utilizar el siguiente script para hacer el purge de un determinado cursor de la Shared Pool:DECLARE name varchar2(50); version varchar2(3);BEGIN select regexp_replace(version,'\..*') into version from v$instance; if version = '10' then execute immediate q'[alter session set events '5614566 trace name context forever']'; -- bug 5614566 end if; select address||','||hash_value into name from v$sqlarea where sql_id like '&sql_id'; dbms_shared_pool.purge(name,'C',1);END;/[...]



Diferencias entre BETWEEN y >= <=

2010-07-06T13:39:44.121-03:00

Muchas personas prefieren utilizar el BETWEEN en vez de >= = CREATE TABLE test AS 2 SELECT level id, 'nombre_'||level nombre 3 FROM dual 4 CONNECT BY level EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST', estimate_percent => 100, method_opt => 'for all columns size 1');PL/SQL procedure successfully completed.SQL> DESC test Name Null? Type ------------- -------- -------------- ID NUMBER NOMBRE VARCHAR2(47)SQL> SELECT nombre 2 FROM test 3 WHERE id BETWEEN 20 AND 25;NOMBRE---------nombre_20nombre_21nombre_22nombre_23nombre_24nombre_256 rows selected.SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));Plan hash value: 1357081020------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |------------------------------------------------------------------------------------|* 1 | TABLE ACCESS FULL| TEST | 1 | 7 | 6 |00:00:00.01 | 4 |------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(("ID"=20))Como podemos ver en la sección "Predicate Information", el optimizador reemplazó la sentencia BETWEEN por "ID"=20.También podemos ver, que leyó 4 bloques de datos para recuperar los 6 registros mediante un acceso FULL SCAN.Ahora, probemos colocando el >= SELECT nombre 2 FROM test 3 WHERE id >= 20 AND id select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));Plan hash value: 1357081020------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |------------------------------------------------------------------------------------|* 1 | TABLE ACCESS FULL| TEST | 1 | 7 | 6 |00:00:00.01 | 4 |------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(("ID"=20)) Acá volvemos a ver en la a sección "Predicate Information" que el optimizador no realizó ningún reemplazo.Seguimos leyendo 4 bloques de datos para recuperar los 6 registros mediante un acceso FULL SCAN.Una cosa muy importante a destacar entre estas 2 consultas es que el hash value de ambas es el mismo (1357081020). Esto nos indica que la utilización de BETWEEN o >= = CREATE UNIQUE INDEX test_uq ON test(id, nombre);Index created.SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST', estimate_percent => 100, method_opt => 'for all columns size 1', cascade => true);PL/SQL procedure successfully completed.SQL> SELECT nombre 2 FROM test 3 WHERE id BETWEEN 20 AND 25;NOMBRE---------nombre_20nombre_21nombre_22nombre_23nombre_24nombre_256 rows selected.SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));Plan hash value: 1087767317--------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |--------------------------------------------------------------------------------------|* 1 | INDEX RANGE SCAN| TEST_UQ | 1 | 7 | 6 |00:00:00.01 | 2 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("ID">=20 AND "ID"= SELECT nombre 2 FROM test 3 WHERE id >= 20 AND id select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));Plan hash value: 1087767317--------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |---------------------[...]



Crear una base de datos de prueba en minutos!

2010-07-01T10:32:13.339-03:00

La tarea de crear una base de datos puede parecer bastante complicada para algunos o bastante agotadora para otros... pero la realidad es que podemos crear una base de datos de prueba en tan solo minutos sin utilizar entorno gráfico como el X Windows o la VNC. Tan solo ejecutando DBCA podemos crear por linea de comando y de manera no interactica una base de datos!!! En este sencillo ejemplo creé el archivo testdb.dbt copiando uno de los archivos template que se encuentran en el directorio $ORACLE_HOME/assistants/dbca/templates/. Luego modifiqué este archivo colocando algunas configuraciones para la nueva base de datos que quiero crear. Por último, ejecuté el siguiente comando ... $ dbca -Silent -CreateDatabase -gdbName testdb -sid testdb -templateName $ORACLE_HOME/assistants/dbca/templates/testdb.dbt -sysPassword oracle -systemPassword oracle -dbsnmpPassword oracle -sysmanPassword oracle 4% complete Creating and starting Oracle instance 5% complete 6% complete 7% complete 12% complete Creating database files 20% complete Creating data dictionary views 22% complete 24% complete 27% complete 28% complete 29% complete 30% complete 31% complete 32% complete 33% complete 34% complete 39% complete 41% complete 44% complete 46% complete Adding Oracle Application Express 47% complete 48% complete 49% complete 50% complete 51% complete 52% complete 53% complete 54% complete 55% complete 56% complete 57% complete 58% complete 59% complete 60% complete 62% complete Adding Oracle Warehouse Builder 63% complete 64% complete 65% complete 66% complete 67% complete 75% complete 78% complete Completing Database Creation 80% complete 83% complete 85% complete 92% complete 100% complete Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/soldb/testdb.log" for further details. Existen muchos parámetros opcionales con los que podemos ejecutar el DBCA para crear una base de datos. También, podemos ejecutar DBCA para realizar otras operaciones por linea de comando; como borrar una base de datos, crear scripts, etc. Podemos ver todas las operaciones que se pueden ejecutar con el comando DBCA -H ... $ dbca -h dbca [-silent | -progressOnly | -customCreate] { } | { [ [options] ] -responseFile } [-continueOnNonFatalErrors ] Please refer to the manual for details. You can enter one of the following command: Create a database by specifying the following parameters: -createDatabase -templateName [-cloneTemplate] -gdbName [-policyManaged | -adminManaged ] [-createServerPool ] [-force ] -serverPoolName -[cardinality ] [-sid ] [-sysPassword ] [-systemPassword ] [-emConfiguration -dbsnmpPassword -sysmanPassword [-hostUserName -hostUserPassword -backupSchedule ] [-smtpServer -emailAddress ] [-centralAgent ]] [-disableSecurityConfiguration [-datafileDestination | -datafileNames ] [-redoLogFileSize ] [-recoveryAreaDestination ] [-datafileJarLocation ] [-storageType < CFS | ASM > [-asmsnmpPassword ] -diskGroupName -recoveryGroupName [-nodelist ] [-characterSet ] [-nationalCharacterSet ] [-registerWithDirService -dirServiceUserName -dirServicePassword -walletPassword ] [-listeners ] [-variablesFile ]] [-variables ] [...]



Simposio HOTSOS 2010

2010-02-17T19:16:32.166-03:00

(image)

Todos los años, se realiza un evento llamado Simposio HOTSOS. Este evento esta exclusivamente dedicado a Oracle Performance Tuning.

El Simposio se realiza todos los años en el mes de Marzo en la ciudad de Dallas, Texas (USA). Esta conferencia es única a nivel mundial! Reune a los mejores expertos de Oracle del mundo!

El año pasado, tuve la oportunidad de poder viajar y presenciar el Simposio. Definitivamente se lo recomiendo a todas las personas que utilizan Oracle y que se especializan o que se encuentran interesadas en lo que respecta a Performance Tuning.


Este año (días 7 al 11 de Marzo) volveré a presenciar el Simposio! Nos vemos ahí!!!


Para más información: AQUI(image)



Necesitás ayuda sobre un tema en específico? Postealo AQUI !!!

2009-10-22T02:32:46.561-03:00

A pedido de mucha gente que visita el blog y que utiliza Oracle...

Este post es para que ustedes puedan sugerir los temas que tienen dudas y que les gustaría que se les explicara con más detalle en éste blog. Los temas pueden estar relacionados con Performance Tuning, Administración, Desarrollo, etc.

Los invito a todos a sugerir temas técnicos relacionados con Oracle que requieran ser explicados en detalle.

Saludos a todos!
(image)



Añadiendo columnas con valores por defecto y constraint NOT NULL en Oracle 11g

2009-10-21T00:01:51.688-03:00

Antes de la versión 11g, cuando agregabamos una columna a una tabla con valores por defecto y una constraint del tipo NOT NULL, Oracle realizaba un loqueo exclusivo en la tabla para insertar los valores en cada uno de los registros existentes en la misma.A partir de la versión 11g release 1, ésta operación se optimizó para mejorar en gran medida la utilización de los recursos del sistema y el espacio de almacenamiento de los nuevos valores. Oracle logra ésta optimización, guardando el valor por defecto en el diccionario de datos en vez de modificar todos los registros de la tabla, haciendo la ejecución de ésta operacion de manera instantánea. Gracias a ésto, obtenemos un beneficio enorme a la hora de modificar tablas con millones de registros.Por otro lado, las siguientes operaciones ADD COLUMN ahora pueden ejecutarse de manera concurrente junto con las operaciones DML:- Agregar una columna NOT NULL con un valor por defecto.- Agregar una columna NULL sin un valor por defecto.- Agregar una columna virtual.Veamos un ejemplo:- versión 9i release 2SQL_9iR2> CREATE TABLE test 2 NOLOGGING 3 AS 4 SELECT level id 5 FROM dual 6 CONNECT BY level ALTER TABLE test ADD (nombre VARCHAR2(100) DEFAULT 'LEONARDO_HORIKIAN' NOT NULL);Table altered.Elapsed: 00:01:17.62SQL_9iR2> DESC test Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER NOMBRE NOT NULL VARCHAR2(100)SQL_9iR2> exec dbms_stats.gather_table_stats(USER, 'TEST');PL/SQL procedure successfully completed.SQL_9iR2> SELECT count(*) 2 FROM test 3 WHERE nombre IS NULL; COUNT(*)---------- 0- versión 11g release 1SQL_11gR1> CREATE TABLE test 2 NOLOGGING 3 AS 4 SELECT level id 5 FROM dual 6 CONNECT BY level ALTER TABLE test ADD (nombre VARCHAR2(100) DEFAULT 'LEONARDO_HORIKIAN' NOT NULL);Table altered.Elapsed: 00:00:00.17SQL_11gR1> DESC test Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER NOMBRE NOT NULL VARCHAR2(100)SQL_11gR1> exec dbms_stats.gather_table_stats(USER, 'TEST');PL/SQL procedure successfully completed.SQL_11gR1> SELECT count(*) 2 FROM test 3 WHERE nombre IS NULL; COUNT(*)---------- 0Como podemos observar, creamos una tabla con 1 millón de registros y luego agregamos una columna con valores por defecto. En 9iR2, esa operación se ejecutó en 1 minuto 17 segundos; en cambio en 11gR1, se ejecutó de manera instantánea en tan solo 17 milisegundos!!!IMPORTANTE: Tengan en cuenta que existen bugs en 11g (leer nota Metalink 602327.1) relacionados con éste tipo de operación.[...]



TKPROF y el parámetro SYS=Y

2009-11-11T08:34:48.544-03:00

Conozco muchas personas que al ejecutar la herramienta TKPROF, lo hacen con el parámetros SYS=N para que no se incluyan en el archivo de salida las consultas recursivas que realiza internamente la base de datos. Si bien en algunos casos se hace ésto para que el archivo de salida no contenga información en exceso, muchas veces cuando buscamos la causa de un determinado problema, si colocamos SYS=N lo único que estaremos logrando será "ocultar" el causante de ese problema.Hace unos días, investigando un problema de performance en un proceso de un cliente, ejecuté la herramienta TKPROF con el parámetro SYS=Y (que es la opción por defecto) y noté que había una consulta recursiva que estaba leyendo millones de bloques de datos!La consulta es la siguiente:select min(bitmapped)from ts$ where dflmaxext =:1 and bitand(flags,1024) =1024call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 7915 0.08 0.07 0 0 0 0Execute 7915 0.17 0.14 0 0 0 0Fetch 15830 4.24 4.23 0 3245150 0 7915------- ------ -------- ---------- ---------- ---------- ---------- ----------total 31660 4.50 4.45 0 3245150 0 7915Misses in library cache during parse: 0Optimizer mode: CHOOSEParsing user id: SYS (recursive depth: 2)Rows Row Source Operation------- --------------------------------------------------- 1 SORT AGGREGATE (cr=410 pr=0 pw=0 time=691 us) 2 TABLE ACCESS FULL TS$ (cr=410 pr=0 pw=0 time=659 us)Esta consulta es interna de la base de datos. Debido a que el proceso se ejecuta con el usuario APPS y debido a que a éste usuario durante su creación se lo asignó dentro de un grupo de tablespaces temporales (new feature en 10g) llamado TEMP (que contienen los tablespaces TEMP1 y TEMP2), Oracle necesita ejecutar ésta consulta para determinar cuál de los dos tablespaces es el mejor para realizar la operación de sort que está requiriendo el proceso. Como pueden ver, esta consulta es ALTAMENTE costosa y consume muchos recursos del sistema ya que la consulta lee 3,245,150 bloques (410 * 7915) y el proceso en total lee 4,452,813 bloques! Por lo cual, ésta consulta está leyendo más del 70% del total de todos los bloques de datos del proceso!!!Para solucionar éste problema, lo que hice fue modificar el usuario APPS, sacarlo del grupo de tablespaces temporales TEMP y asignarle directamente el tablespace TEMP2 que tiene un tamaño de 39 GB a comparación del TEMP1 que tiene sólo 6 GB. Al hacer éste cambio, lo que logramos fue que Oracle deje de ejecutar esa consulta debido a que ya no tiene necesidad de determinar cual de los dos tablespaces es el mejor para ejecutar una determinada operación de sort.Al ejecutar nuevamente el proceso, podemos ver el resultado de la solución implementada:select min(bitmapped)from ts$ where dflmaxext =:1 and bitand(flags,1024) =1024call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 0.00 0.00 0 410 0 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4 0.00 0.00 0 410 0 1Misses in library cache during parse: 0Optimizer mode: CHOOSEParsing user id: SYS (recursive depth: 3)Rows Row Source Operation------- --------------------------------------------------- 1 SOR[...]



Oracle OpenWorld

2010-07-08T01:26:58.415-03:00

(image) (image)



adiós a los RAW-devices en 12g...

2009-07-13T16:16:24.161-03:00

Según la nota de Metalink 578455.1 en 12g NO se seguirán soportando RAW-devices para los archivos físicos de la base de datos (data files, redo logs, control files), OCR (Oracle Cluster Registry) o discos Voting.

IMPORTANTE!!! Este anuncio NO afecta a ASM.

En 12g, ya NO será posible ejecutar la siguiente sentencia porque intenta utilizar RAW-devices directamente...

SQL> create tablespace ABC DATAFILE '/dev/raw/ABC1.dbf' size 2GB;

Lo que tendremos que hacer, es ejecutar alguna de las siguientes sentencias para crear un diskgroup...

SQL> alter diskgroup MYDG add disk '/dev/raw/ABC1.dbf';

o

SQL> create diskgroup MYDB EXTERNAL REDUNDANCY disk 'dev/raw/ABC1.dbf'

Luego de crear el diskgroup, podemos ejecutar la sentencia que acostumbramos...

SQL> create tablespace ABC DATAFILE '+MYDG' size 2GB;

Para los que tengan que migrar desde RAW-devices, las opciones existentes incluyen ASM (Automatic Storage Management), OCFS (Oracle Cluster File System) y otros sistemas de archivos clúster.

Muchos estarán leyendo ésta nota y diciendo... OUCHHHH!!!!!! Y bueno... tendremos que irnos aconstumbrando a ésta clase de cambios ya que éste es sólo uno de los grandes cambios que veremos en 12g...(image)



Stress Test

2009-06-25T12:02:27.607-03:00

El "Stress Test" es una prueba que mide el comportamiento de nuestro sistema bajo una cierta demanda concurrente de conexiones. Esta es una de las pruebas claves que se DEBEN realizar durante el ciclo de vida del software para garantizar que nuestro sistema va a cumplir con las expectativas previstas cuando sea implementado en producción.Quiero mostrarles una simple prueba de Stress que hice para que vean qué tan importante es realizarla y qué tan mortal puede ser evitarla.Ejemplo:Vamos a crear una tabla llamada TEST que vamos a utilizar en el Stress para cargar datos.SQL_9iR2> CREATE TABLE test (id NUMBER);Tabla creada.Bien, yo tengo creados unos Shell Scripts propios para realizar el Stress Test. Los más importantes son los siguientes: - iniciar_stress.sh --> Iniciamos el Stress Test. - reporte.sh --> Genera el reporte con el detalle sobre el Stress Test.Lo que vamos a realizar es un Stress Test simulando una concurrencia de 100 conexiones simultáneas ejecutando el siguiente bloque PL/SQL anónimo:BEGIN FOR x IN 1 .. 100 LOOP INSERT INTO test VALUES (x); END LOOP;END;/Como pueden observar, el INSERT que vamos a realizar es muy sencillo y debería ejecutarse rápido, cierto? Veamos cuánto demora si lo ejecuto sin concurrencia:SQL_9iR2> BEGIN 2 FOR x IN 1 .. 100 LOOP 3 INSERT INTO test VALUES (x); 4 END LOOP; 5 END; 6 /Procedimiento PL/SQL terminado correctamente.Transcurrido: 00:00:01.25Bien, vemos que demoró 1.25 segundos. El bloque se ejecutó bastante rápido y esperamos que se comporte de la misma manera cuando realicemos el Stress Test... asi que hagamos la prueba para comprobarlo!Para comenzar el Stress Test, vamos a ejecutar el Shell Script llamado iniciar_stress.sh$ ./iniciar_stress.sh======================================================================================= STRESS TEST ===============================================================================================Autor: Leonardo Horikian - Oracle ArgentinaDescripción: Programa que inicia un StressTestApretar ENTER para continuar...=================================================================Ingresar la password del usuario STRESS_TEST:TNS alias (para local dejar en blanco):Esta instancia se encuentra configurada para soportar 769 conexiones concurrentes!Cuántas conexiones concurrentes querés ejecutar [1-769]?100=================================================================StressTest listo para ser ejecutado.Para comenzar, apretar ENTER=================================================================nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'nohup: appending output [...]



Costo super alto!

2009-05-19T18:09:48.524-03:00

En Febrero del 2008, publiqué un post llamado "¿Tunear en base al COSTO del plan de ejecución?". En ese post, les decía que NO hay que tunear en base al costo del plan de ejecución, sino al trabajo que realiza la consulta en la base de datos.Voy a mostrarles una consulta en el que el costo es super alto pero el trabajo que realiza en la base de datos no lo es:SQL_10gR2> explain plan for2 SELECT (t5.column_value).getstringval() t53 FROM TABLE(xmlsequence(extract(xmltype(''),'/x'))) t1,4 TABLE(xmlsequence(t1.column_value))t2,5 TABLE(xmlsequence(t2.column_value))t3,6 TABLE(xmlsequence(t3.column_value))t4,7 TABLE(xmlsequence(t4.column_value))t5;Explicado.SQL_10gR2> SELECT * FROM table(dbms_xplan.display('plan_table',null,'ALL'));PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------------------------------Plan hash value: 4104774429----------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 18E| 15E| 98P (2)|999:59:59 || 1 | NESTED LOOPS | | 18E| 15E| 98P (2)|999:59:59 || 2 | NESTED LOOPS | | 4451T| 31P| 12T (2)|999:59:59 || 3 | NESTED LOOPS | | 544G| 3045G| 1481M (2)|999:59:59 || 4 | NESTED LOOPS | | 66M| 254M| 181K (2)| 00:36:18 || 5 | COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE | | | | || 6 | COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE | | | | || 7 | COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE | | | | || 8 | COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE | | | | || 9 | COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE | | | | |----------------------------------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------1 - SEL$E270DE78Column Projection Information (identified by operation id):-----------------------------------------------------------1 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]2 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]3 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]4 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40]5 - VALUE(A0)[40]6 - VALUE(A0)[40]7 - VALUE(A0)[40]8 - VALUE(A0)[40]9 - VALUE(A0)[40]Wow!!! Y esos números??? Extremadamente altos no??? Bueno, según el plan de ejecución, esa consulta retorna 18 cuatrillones (18E) de registros, 15 exabytes (15E), tiene un costo de 1.8E19 (98P) y el tiempo de ejecución es de aproximadamente 1 mes (999:59:59)!!!Veamos qué sucede cuando ejecutamos la consulta y obtenemos las estadísticas de la misma:SQL_10gR2> SELECT (t5.column_value).getstringval() t52 FROM TABLE(xmlsequence(extract(xmltype(''),'/x'))) t1,3 TABLE(xmlsequence(t1.column_value))t2,4 TABLE(xmlsequence(t2.column_value))t3,5 TABLE(xmlsequence(t3.column_value))t4,6 TABLE(xmlsequence(t4.column_value))t5;T5------------------------[...]



DML Error Logging

2009-05-13T16:04:04.388-03:00

Alguna vez trataron de actualizar 10 millones de registros? Que sucedía cuando uno de los registros fallaba? Oracle realizaba un rollback automático de los cambios... y qué sucede si el registro que falló era uno de los últimos en actualizarse? Y bueno... seguramente desperdiciamos todos el tiempo de ejecución de ese proceso ya que los cambios se perdieron tan solo porque un registro falló! A muchos les habrá pasado lo mismo al ejecutar una sentencia del tipo INSERT AS SELECT cierto? En donde uno de los registros no pudo insertarse por X motivo y por consecuencia toda la sentencia falló!Generalmente, sabemos que la manera más rápida de realizar un DML es en una sola sentencia. Cuando tenemos una sentencia en donde pueden haber registros que terminen en error, se solía armar un procedimiento que recorra los datos que se quieren insertar, updatear, eliminar, etc. e ir ejecutando la senetencia de a un/o varios registros a la vez, y los registros que fallaban, se insertaban en una tabla de errores. Este procedimiento suele ser muy lento, ya que no estamos realizando un DML en una sola sentencia y en una sola vez para todos los registros; sino que estamos recorriendo los datos proceduralmente y realizando el DML de a X cantidad de registros a la vez con el fin de loguear los registros que terminaron en error e ir comiteando, a medida que se va ejecutando la sentencia DML, los registros que terminan satisfactoriamente.En Oracle 10g Release 2, existe una nueva funcionalidad llamada "DML Error Logging" que nos permite ejecutar una sentencia DML de "principio a fin"... y si en el transcurso de ejecución de esa sentencia, uno o más registros fallaran, esos registros se loguean en una tabla de errores para que luego podramos corregirlos, sin necesidad, de volver a insertar todos los registros nuevamente ya que sólo tendremos que volver a insertar los que terminaron en error.Por ejemplo: Si intentamos insertar 1 millón de registros, y sólo un registro falla, se insertarán en la tabla final 999,999 de registros, y el registro que terminó en error, se loguea en otra tabla para que luego podamos corregirlo y volverlo a insertar. Acaso no es sensacional ésto???Veamos un ejemplo muy simple:Creamos una tabla llamada TEST, en donde vamos a insertar 1 millón de registros.SQL_10gR2> CREATE TABLE test2 (3 ID NUMBER,4 NOMBRE VARCHAR2(7)5 );Tabla creada.Creamos una tabla llamada ERROR_LOG_TEST que apunta a la tabla TEST. Esta tabla va a contener todos los registros que terminen en error cuando queramos realizar un DML en la tabla TEST.SQL_10gR2> EXEC dbms_errlog.create_error_log('TEST','ERROR_LOG_TEST') ;Procedimiento PL/SQL terminado correctamente.SQL_10gR2> DESC error_log_testNombre Nulo? Tipo----------------------------------------------------- -------- ------------------------------------ORA_ERR_NUMBER$ NUMBERORA_ERR_MESG$ VARCHAR2(2000)ORA_ERR_ROWID$ ROWIDORA_ERR_OPTYP$ VARCHAR2(2)ORA_ERR_TAG$ VARCHAR2(2000)ID VARCHAR2(4000)NOMBRE VARCHAR2(4000)Bien, ahora vamos a insertar los registros en la tabla TEST.SQL_10gR2> INSERT INTO test2 SELECT level, 'nom_'||level3 FROM dual4 CONNECT BY level SELECT count(*) FROM error_log_test;COUNT(*)----------9990011 fila seleccionada.Como verán, no perdimos ningún registros. Los registros que se insertaron satisfactoriamente[...]



Oracle adquiere Sun Microsystems

2009-05-08T20:04:22.239-03:00

En momentos de crisis... pueden pasar cosas increíbles...


(image)
unbreakable Linux... y ahora? unbreakable Solaris??? =)

Para más información... AQUI

(image)



Cuántos registros hay en cada bloque de mi tabla?

2009-05-03T19:31:09.262-03:00

En el día de hoy, me llegó una e-mail de una persona preguntándome lo siguiente: "Me podrías decir cómo hago para saber cuántos registros hay en cada bloque de mi tabla?". Bueno, la verdad es que es muy fácil ver cuántos registros caben en cada bloque y también es muy fácil comprobarlo.Veamos un ejemplo:En la base de datos de prueba en la que estoy actualmente, tengo bloques de 8 KB.SQL_10gR2> show parameter db_block_sizeNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_block_size integer 8192Vamos a crear una tabla llamada TEST con 1.000 registros.SQL_10gR2> CREATE TABLE test AS2 SELECT level id, 'nom_'||level nombre3 FROM dual4 CONNECT BY level SELECT dbms_rowid.rowid_block_number(rowid) "Número de Bloque", count(*)2 FROM test3 GROUP BY dbms_rowid.rowid_block_number(rowid)4 ORDER BY dbms_rowid.rowid_block_number(rowid) ASC;Número de Bloque COUNT(*)---------------- ---------- 46196 438 46197 425 46198 1373 filas seleccionadas.Por lo que podemos observar, en el bloque 46196 tengo 438 registros, en el bloque 46197 tengo 425 registros y en el bloque 46198 tengo 137 registros. Pero cómo hacemos para comprobar que realmente es cierto? Cómo hacemos para verificar que el resultado de la consulta es verdadero? Bueno, lo que vamos a hacer, es realizar un Dump de los 3 bloques y ver la información del Trace que se genera automáticamente. Para ejecutar un Dump, primero necesitamos obtener el número del DataFile donde se encuentra almacenada nuestra tabla (segmento). Para ésto, primero vamos a obtener ésta información y luego a realizar el Dump de los bloques. Veamos...SQL_10gR2> SELECT header_file FROM dba_segments WHERE segment_name = 'TEST';HEADER_FILE----------- 41 fila seleccionada.SQL_10gR2> alter system dump datafile 4 block min 46196 block max 46198;Sistema modificado.SQL_10gR2> select spid2 from v$session s, v$process p3 where p.addr = s.paddr4 and s.audsid = sys_context('userenv','sessionid')5 /SPID------------43601 fila seleccionada.Ya se generó el Trace en el directorio especificado en el parámetro user_dump_dest. El nombre con el que se generó es test_ora_4360.trc (el número es el SPID... "System Process Identifier" que obtuvimos). Veamos las partes que más nos interesan del archivo de Trace...data_block_dump,data header at 0x4f06a7c===============tsiz: 0x1f80hsiz: 0x37epbl: 0x04f06a7cbdba: 0x0100b47476543210flag=--------ntab=1 [...]



Encriptando columnas con TDE (Transparent Data Encryption) en tablas con millones de registros

2009-05-02T03:34:47.021-03:00

Para los que no conocen TDE, es una nueva funcionalidad de 10g R2 que permite proteger datos sensibles de las columnas de nuestras tablas encriptando los datos al almacenarlos en los respectivos Data Files en el sistema operativo. Para protegernos de personas malintencionadas que quieran desencriptar los datos sin autorización, guarda las claves de encriptamiento en un módulo seguro externo a la base de datos.Mi intención en éste post no es mostrar el paso a paso de cómo implementar TDE, sino mostrarles cómo encriptar columnas con TDE en tablas con millones de datos de la manera más eficiente y con el menor impacto posible en cuanto a la performance.En el momento en el que encriptamos columnas en una tabla, sólo podemos acceder a la tabla en modo lectura... NO están permitidas las operaciones DML hasta que el encriptamiento termine. En tablas chicas, con muy pocos datos, ésto no suele perjudicarnos demasiado. Pero qué sucede en tablas con millones de registros? En este caso, el encriptamiento puede durar varias horas!!!La estrategia que vamos a utilizar para encriptar columnas en tablas con millones de registros, es utilizando el paquete DBMS_REDEFINITION.Veamos un ejemplo:Tengo una tabla llamada TEST_TDE con 1 millón de registros. Esta tabla tiene 3 columnas: ID (primary key), NOMBRE, NUM_TARJETA. La columna que vamos a encriptar es NUM_TARJETA ya que tiene los números de tarjetas, y como para mi es información sensible, quiero encriptarla y protegerla con TDE.SQL_10gR2> SELECT count(*) FROM test_tde;COUNT(*)----------10000001 fila seleccionada.SQL_10gR2> desc TEST_TDENombre Nulo? Tipo----------------------------------------- -------- ----------------------------ID NOT NULL NUMBERNOMBRE VARCHAR2(44)NUM_TARJETA NUMBERSQL_10gR2> SELECT index_name, column_name FROM dba_ind_columns WHERE table_name = 'TEST_TDE';INDEX_NAME COLUMN_NAME---------------------------------------- ----------------------------------------TEST_TDE_ID_PK ID1 fila seleccionada.SQL_10gR2> SELECT constraint_name, constraint_type FROM dba_constraints WHERE table_name = 'TEST_TDE';CONSTRAINT_NAME C------------------------------ -TEST_TDE_ID_PK P1 fila seleccionada.Bien, ahora vamos a encriptar la columna NUM_TARJETA de la manera tradicional y sin utilizar ninguna técnica. En la SESIÓN 1, voy a encriptar la columna, en la SESIÓN 2, voy a modificar el valor de uno de los registros de la tabla mientras se está realizando el procedimiento de encriptado... veamos qué sucede!SESIÓN 1:SQL_10gR2> ALTER TABLE test_tde MODIFY (num_tarjeta ENCRYPT);Tabla modificada.Transcurrido: 00:01:20.11SESIÓN 2:SQL_10gR2> UPDATE test_tde SET num_tarjeta = 123456789 WHERE id = 1000000;1 fila actualizada.Transcurrido: 00:01:17.64Como podemos observar, el encriptamiento de 1 millón de registros demoró 1 minuto 20 segundos. Mientras que se encriptaban los datos, ejecuté un UPDATE en otra sesión y demoró 1 minuto 17 segundos. Esto es debido a que la tabla quedó loqueda en la SESIÓN 1 por el proceso de encriptamiento y la SESIÓN 2 tuvo que esperar que termine de encriptar los datos para poder modificarlos. Imaginen los problemas de performance que tendrían en sus aplicaciones si tienen varios usuarios concurrentes realizando operaciones DML sobre esa tabla mientras se está ejecutando el procedimiento de encriptación!!!Si ejecutamos el mismo UPDATE luego de que los datos ya se encuent[...]



En lo posible, evitá utilizar el comando "EXPLAIN PLAN FOR" !!!

2009-04-30T16:09:05.983-03:00

"EXPLAIN PLAN FOR" tiene problemas...1er. PROBLEMA: Trata todas las Bind Variables como VARCHAR2.Veamos un ejemplo:SQL_10gR2> DESC empNombre Nulo? Tipo----------------------- -------- ------------- EMPNO NOT NULL NUMBER(4)ENAME VARCHAR2(10)JOB VARCHAR2(9)GENDER VARCHAR2(1)MGR NUMBER(4)HIREDATE DATESAL NUMBER(7,2)COMM NUMBER(7,2)DEPTNO NUMBER(2)SQL_10gR2> VAR bind NUMBER;SQL_10gR2> EXECUTE :bind := 7900Procedimiento PL/SQL terminado correctamente.SQL_10gR2> explain plan for2 SELECT * FROM emp WHERE empno = :bind;Explicado.SQL_10gR2> SELECT * FROM table(dbms_xplan.display('plan_table',null,'typical'));--------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 39 | 1 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 39 | 1 (0)| 00:00:01 ||* 2 | INDEX UNIQUE SCAN | EMP_EMPNO_PK | 1 | | 0 (0)| 00:00:01 |--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("EMPNO"=TO_NUMBER(:BIND))Como podemos ver, creé una variable del tipo NUMBER pero al ejecutar el comando "EXPLAIN PLAN FOR" utilizando esa variable, Oracle la convirtió en una variable del tipo VARCHAR2 para luego aplicarle la función de conversión TO_NUMBER y volver a convertirla en tipo NUMBER.2do. PROBLEMA: Puede NO mostrarte el plan de ejecución real que será utilizado en el ejecución de tu consulta.SQL_10gR2> DESC deptNombre Nulo? Tipo----------------------- -------- ---------------- DEPTNO NOT NULL VARCHAR2(10)DNAME VARCHAR2(14)LOC VARCHAR2(13)SQL_10gR2> SELECT * FROM dept;DEPTNO DNAME LOC---------- -------------- -------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON4 filas seleccionadas.SQL_10gR2> VAR bind NUMBER;SQL_10gR2> EXECUTE :bind := 20Procedimiento PL/SQL terminado correctamente.SQL_10gR2> explain plan for2 SELECT * FROM dept WHERE deptno = :bind;Explicado.SQL_10gR2> SELECT * FROM table(dbms_xplan.display('plan_table',null,'typical'));----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 ||* 2 | INDEX UNIQUE SCAN | DEPT_DEPTNO_PK | 1 | | 0 (0)| 00:00:01 |----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("DEPTNO"=:BIND)SQL_10gR2> SELECT * FROM dept WHERE deptno = :bind;DEPT[...]



Diferencias entre COUNT(1) y COUNT(*) - Parte 2

2009-04-30T19:10:46.930-03:00

Hace unas semanas, se me acercaron y me hicieron la siguiente pregunta:

"Me dijeron que si en una consulta se coloca el COUNT(*), por cada registro que leamos en un acceso por índice, vamos a tener que acceder también a la tabla ya que el símbolo * significa que estoy colocando todas las columnas de la tabla en la consulta, y si no tengo todas las columnas de la tabla en el índice, entonces Oracle tiene que acceder a la tabla a buscar el resto de las columnas. Es cierto?".

Bueno, no... no es cierto. Para validar el porque digo ésto, primero veamos un ejemplo:


SQL_10gR2> CREATE TABLE test AS
2 SELECT level id, 'texto_'||level texto
3 FROM dual
4 CONNECT BY level <= 100000 ;

Tabla creada.

SQL_10gR2> CREATE UNIQUE INDEX test_id_uq ON test(id) ;

Índice creado.

SQL_10gR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST',CASCADE=>TRUE) ;

Procedimiento PL/SQL terminado correctamente.

Bien, veamos el plan de ejecución de la siguiente consulta:

SQL_10gR2> SELECT COUNT(*)
2 FROM test
3 WHERE id = 100;

COUNT(*)
----------
1

1 fila seleccionada.

SQL_10gR2> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID d6urw3zfuxz32, child number 0
-------------------------------------
SELECT COUNT(*) FROM test WHERE id = 100

Plan hash value: 4041652814

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 2 | INDEX UNIQUE SCAN| TEST_ID_UQ | 1 | 1 | 1 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=100)

Como podemos observar, coloqué el COUNT con el símbolo * y accedí al índice con el ID 100; pero luego de acceder al índice NO accedí a la tabla, simplemente accedí al índice (ya que tiene la columna que estoy utilizando en el predicado). Como anteriormente dije en el post "Diferencias entre COUNT(1) y COUNT(*) - Parte 1", no existe ninguna diferencia entre el COUNT(1) y COUNT(*), pero si hay diferencia si ejecutamos COUNT(*) y COUNT(nonbre_de_columna) ya que si colocamos una columna de la tabla en el COUNT, Oracle hace un conteo sólo de los valores de esa columna que NO tengan valores nulos.(image)



Explain Plan Vs. Bind Variables

2008-09-29T11:25:27.172-03:00

Obtener el plan de ejecución de una consulta que contiene Bind Variables sin haberlas reemplazado??? NO!!! NO!!! NO!!!!!!!!!!Ya hablamos en otras ocasiones del beneficio que obtenemos al utilizar Bind Variables y también explicamos qué son. Cuando ejecutamos una consulta con Bind Variables (sin haberlas reemplazado) para obtener el plan de ejecución, el optimizador de costos (CBO) no sabe el valor de la Bind Variable; y por lo tanto, calcula la selectividad del filtro utilizando reglas definidas por defecto. Que quiere decir ésto? Que el plan de ejecución que obtenemos puede ser MUY distinto al plan de ejecución real!!! Porqué muy distinto? Porque todo depende del valor con el que se reemplazará la Bind Variable y el tipo de dato de la misma.Veamos un ejemplo:SQL_10gR> CREATE TABLE test AS  2 SELECT TO_CHAR(level) id, 'test'||level descripcion  3 FROM dual  4 CONNECT BY level DESC testName                    Null?    Type----------------------- -------- ----------------ID                               VARCHAR2(40)DESCRIPCION                      VARCHAR2(44)SQL_10gR> CREATE UNIQUE INDEX test_uq ON test(id, descripcion);Index created.SQL_10gR> EXEC dbms_stats.gather_table_stats(user, 'TEST', cascade=>true) ;PL/SQL procedure successfully completed.Supongamos que detectamos un problema grave en la performance de una de nuestras aplicaciones. Al identificar la consulta que nos está causando problemas, obtenemos el plan de ejecución de la misma para ver si está accediendo correctamente...Ejecutamos la consulta con Bind Variable:SQL_10gR> EXPLAIN PLAN FOR  2 SELECT descripcion  3 FROM test  4 WHERE id = :b1;Explained.SQL_10gR> @explainsPlan hash value: 1087767317----------------------------------------------------------------------------| Id | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time      |----------------------------------------------------------------------------|  0 | SELECT STATEMENT |         |     1 |    15 |       2 (0)|  00:00:01 ||* 1 | INDEX RANGE SCAN | TEST_UQ |     1 |    15 |       2 (0)|  00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - access("ID"=:B1)Por lo que vemos en el plan de ejecución, si filtramos la columna ID con un valor del mismo tipo de dato, el optimizador eligirá acceder por índice en vez de realizar un full scan de la tabla. Esto suena lógico sabiendo que los valores de la columna ID son únicos y que por cada valor con el que filtremos, a lo sumo obtendremos una ocurrencia del mismo valor en la tabla.Ejecutamos una consulta sin Bind Variable:SQL_10gR> EXPLAIN PLAN FOR  2 SELECT descripcion  3 FROM test  4 WHERE id = 10000;Explained.SQL_10gR> @explainsPlan hash value: 1357081020--------------------------------------------------------------------------| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time        |--------------------------------------------------------------------------|  0 | SELECT STATEMENT |      |    1 |    15 |      54 (4)|    00:00:01 ||* 1 | TABLE ACCESS FULL| TEST |    1 |    15 |      54 (4)|    00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter(TO_NUMBER("ID")=10000)Pero qué sucede si filtramos la columna ID con [...]



¿Tunear en base al COSTO del plan de ejecución?

2008-05-13T09:12:14.470-03:00

Todos los días observo que alguien está queriendo tunear una consulta en base al costo del plan de ejecución. Pero... ¿Qué es el COSTO? ¿Qué representa? La respuesta es simple: El costo representa unidades de trabajo o recursos utilizados. El optimizador usa I/O a disco, CPU y memoria como unidades de trabajo. Entonces, el costo para una determinada consulta representa una estimación de la cantidad de I/O a disco, de CPU y memoria que se utilizará para la ejecución de la consulta.Bien, con ésto ya dicho, porqué hay personas que tratan de tunear una consulta en base al costo??? El costo es simplemente un número que le asigna el optimizador de costos (CBO) a la consulta para saber qué plan de ejecución elegir entre todos los planes que genera en el momento de la optimización (el plan de ejecución que se genera con el menor costo es el que Oracle utiliza para ejecutar nuestra consulta), pero no existe un "mejor número" que debemos tener en mente para deducir si una consulta es óptima o no.NO debemos tunear en base al costo. SI debemos tunear en base a los I/O lógicos (LIO's).Veamos un ejemplo:SQL_9iR2> CREATE TABLE test AS2 SELECT level id, 'nombre_'||level nom3 FROM dual4 CONNECT BY level EXEC dbms_stats.gather_table_stats(user,'TEST') ;PL/SQL procedure successfully completed.SQL_9iR2> SET AUTOTRACE TRACEONLYSQL_9iR2> SELECT nom2 FROM test3 WHERE id = 50000 ;Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=1 Bytes=17)1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=49 Card=1 Bytes=17)Statistics---------------------------------------------------------- 5 recursive calls 0 db block gets 323 consistent gets 0 physical reads 0 redo size 335 bytes sent via SQL*Net to client 495 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processedComo podemos observar, el costo de la consulta es de 49. Si yo tuneo en base al costo no puedo saber si el plan de ejecución que se está utilizando para ésta consulta es óptimo o no porque no sé que costo sería el ideal para saberlo. Es por eso que NO debemos tunear en base al costo. Por otro lado, podríamos tunear en base a la cantidad de I/O lógicos que se estén realizando. En éste ejemplo, se realizan 323 LIO's. Si observamos la consulta, estoy seleccionando la columna NOM que corresponde al ID 50000 . Como creé la tabla de forma tal que todos los ID's sean únicos, ésta consulta me debería traer un solo registro. Si nos ponemos a pensar, hacer 323 LIO's para traer sólo un registro es demasiado. En éste momento es en donde nos damos cuenta que tenemos un problema de performance porque estamos haciendo un Full Scan de una tabla de 100.000 registros para buscar solamente el ID 50000 que nos devuelve un sólo registro.Veamos qué sucede si creamos un índice único por la columna ID,NOM...SQL_9iR2> CREATE UNIQUE INDEX test_id_nom_uq ON test(id,nom) ;Index created.SQL_9iR2> EXEC dbms_stats.gather_index_stats(user,'TEST_ID_NOM_UQ') ;PL/SQL procedure successfully completed.SQL_9iR2> SELECT nom2 FROM test3 WHERE id = 50000 ;Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=17)1 0 INDEX (RANGE SCAN) OF 'TEST_ID_NOM_UQ' (UNIQUE) (Cost=2 Card=1 Bytes=17)Statistics---------------------------------------------------------- 0 recursive calls 0 db [...]



DB_FILE_MULTIBLOCK_READ_COUNT (MBRC)

2007-10-16T17:11:46.035-03:00

El parámetro DB_FILE_MULTIBLOCK_READ_COUNT especifica la cantidad de bloques que van a ser leídos en cada I/O a través de un Full Scan.En Oracle 10g Release 2, el valor por default de éste parámetro, es el valor que corresponde a la cantidad máxima de I/O que se puede realizar de forma más eficiente. En ambientes OLTP o Batch, éste parámetro suele setearse en valores entre 4 y 16 bloques. En ambientes DSS o Data Warehouse, éste parámetro suele setearse en un valor mayor.Este parámetro afecta al Optimizador de Costos (CBO) ya que si seteamos un valor alto, el CBO puede ser influenciado en elegir realizar un Full Scan en vez de acceder por índice. Como en los ambientes DSS o Data Warehouse suelen utilizarse planes de ejecución que incluyen Full Scan, aumentar el valor de éste parámetro puede ser beneficioso.El máximo valor que podemos setear depende del sistema operativo.La fórmula que se utiliza para buscar el valor de éste parámetro es:DB_FILE_MULTIBLOCK_READ_COUNT = ( (MAX I/O SIZE) / DB_BLOCK_SIZE )Si elegimos un valor mayor al máximo soportado, Oracle simplemente elige el valor máximo que puede utilizar.Veamos un ejemplo de cómo buscar el valor máximo del parámetro DB_FILE_MULTIBLOCK_READ_COUNT sin utilizar la fórmula:Primero, veamos el valor actual del MBRC:SQL_9iR2> show parameter multiblockNAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM------------------------------ ----------- ------------------------------db_file_multiblock_read_count integer 16Observemos el Explain Plan de una de las tablas:SQL_9iR2> explain plan for 2 SELECT /*+ full(test) noparallel(test) nocache(test) */ count(*) 3 FROM test ;Explained.SQL_9iR2> @explainsPlan hash value: 3740828345---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 46709 (1)| 00:10:55 || 1 | SORT AGGREGATE | | 1 | | || 2 | TEST ACCESS FULL | TEST | 20M| 46709 (1)| 00:10:55 |---------------------------------------------------------------------------------------El costo de la consulta es 46709. Veamos qué sucede si seteo el valor del parámetro muy alto:SQL_9iR2> ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT = 50000 ;Session altered.SQL_9iR2> show parameter multiblockNAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM------------------------------ ----------- ------------------------------db_file_multiblock_read_count integer 64Bien, como podemos observar, seteamos el parámetro en 50.000 bloques; pero como excedemos el máximo permitido, Oracle setea el máximo valor que puede alcanzar.Ahora veamos si Oracle realmente está utilizando el nuevo MBRC que acabamos de setear:SQL_9iR2> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8' ;Session altered.SQL_9iR2> SELECT /*+ full(test) noparallel(test) nocache(test) */ count(*) 2 FROM test ; COUNT(*)---------- 526730281 row selected.SQL_9iR2> ALTER SESSION SET EVENTS '10046 trace name context off' ;Session altered.SQL_9iR2> @trace_file_nameTRACE_FILE_NAME-----------------------------testdb_ora_11379.trc1 row selected.[test@linux_test udump]$ cat testdb_ora_11379.trc | grep "scattered read" | awk '{ split ($11,listado,[...]



Problemas utilizando Analytic Functions junto con PL/SQL en 8i

2007-09-30T11:22:32.971-03:00

La idea de éste post no es explicar el funcionamiento de las Analytic Functions, sino el error PLS-00103 al utilizar Analytic Functions.La mejor manera de explicar éste problema, es realizando un ejemplo.La tabla de prueba que vamos a utilizar en nuestro ejemplo, contiene los siguientes registros:SQL> SELECT * FROM test ; ID NIVEL SALARIO---------- ---------- ---------- 10 1 2500 20 2 3000 30 1 3500 40 2 4000 50 1 4500 60 2 5000 70 1 5500 80 2 6000 90 1 6500 100 2 700010 rows selected.Primero vamos a realizar el ejemplo en una base de datos 8.1.7.4.0 SQL_8i> SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY nivel ORDER BY salario DESC) AS rank 2 FROM test ; ID NIVEL SALARIO RANK---------- ---------- ---------- ---------- 90 1 6500 1 70 1 5500 2 50 1 4500 3 30 1 3500 4 10 1 2500 5 100 2 7000 1 80 2 6000 2 60 2 5000 3 40 2 4000 4 20 2 3000 510 rows selected.Como podemos observar, si ejecutamos esa consulta con Analytic Functions en SQL*Plus, funciona a la perfección. Veamos qué sucede si intentamos ejecutar la consulta dentro de PL/SQL:SQL_8i> CREATE PROCEDURE pr_test 2 IS 3 BEGIN 4 -- 5 FOR cur IN ( SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY nivel ORDER BY salario DESC) AS rank 6 FROM test ) LOOP 7 -- 8 dbms_output.put_line('ID: '||cur.id|| 9 ' - NIVEL: '||cur.nivel|| 10 ' - SALARIO: '||cur.salario|| 11 ' - RANK: '||cur.rank) ; 12 -- 13 END LOOP ; 14 -- 15 END pr_test ; 16 /Warning: Procedure created with compilation errors.SQL_8i> show errorsErrors for PROCEDURE PR_TEST:LINE/COL ERROR----------- ------------------------------------------------------------------------5/63 PLS-00103: Encountered the symbol "(" when expecting one of the following: , fromEste error se debe a que según la Nota 147808.1, no podemos utilizar Analytic Functions dentro de PL/SQL en versiones anteriores a 9i. Podemos utilizar éste tipo de funciones en SQL, pero no en PL/SQL. Desde las versiones de la 9i en adelante, podemos utilizar Analytic Functions tanto en SQL como en PL/SQL.Hay 2 formas de solucionar éste problema:- Crear vistas utilizando Analytic Function y luego hacer referencia a esas vistas dentro de PL/SQL. - Usar Dynamic SQL.Veamos la implementación de esas 2 soluciones:Utilizando una vista...SQL_8i> CREATE VIEW pr_test_view AS 2 SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY nivel ORDER BY salario DESC) AS rank 3 FROM test ;View created.SQL_8i> CREATE OR REPLACE PROCEDURE pr_test 2 IS 3 BEGIN 4 -- 5 FOR cur IN ( SELECT id, nivel, salario, rank 6 FROM pr_test_view ) LOOP 7 -- 8 dbms_output.put_line('ID: '||cur.id|| 9 ' - NIVEL: '||cur.nivel|| 10 [...]



Problemas en Vistas Materializadas - Parte 2

2007-09-24T12:53:58.725-03:00

Como pudimos ver en el post "Problemas en Vistas Materializadas - Parte 1", podemos identificar con el paquete DBMS_MVIEW (procedimiento EXPLAIN_MVIEW), los problemas que pueden haber en nuestra Vista Materializada e implementar las soluciones necesarias. La contra de ese procedimiento es que nos muestra los errores que tenemos en la Vista Materializada, pero no nos dice cómo solucionarlos.En Oracle 10g se introduce el paquete DBMS_ADVISOR (procedimiento TUNE_MVIEW) que nos dice qué cambios debemos implementar en nuestra Vista Materializada para soportar las capacidades que necesitamos.Veamos un ejemplo:SQL_10gR2> CREATE TABLE test1 AS 2 SELECT level id, level*level total 3 FROM dual 4 CONNECT BY level CREATE TABLE test2 AS 2 SELECT level id, 'nom_'||level nom 3 FROM dual 4 CONNECT BY level COMMIT ;Commit complete.SQL_10gR2> ALTER TABLE test2 ADD CONSTRAINT id_2_pk PRIMARY KEY (id) ;Table altered.SQL_10gR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST1') ;PL/SQL procedure successfully completed.SQL_10gR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST2') ;PL/SQL procedure successfully completed.SQL_10gR2> SELECT test2.id, 2 test2.nom, 3 SUM(test1.total) sum_total 4 FROM test1, 5 test2 6 WHERE test1.id = test2.id(+) 7 GROUP BY test2.id, 8 test2.nom 9 ORDER BY id ; ID NOM SUM_TOTAL---------- ---------- ---------- 1 nom_1 1 2 nom_2 4 3 nom_3 9 4 nom_4 16 5 nom_5 25 6 nom_6 36 7 nom_7 49 8 nom_8 64 9 nom_9 81 10 nom_10 10010 rows selected.Bien, ya creé mi ambiente de prueba. Ahora voy a crear las tablas de Log necesarias para la capacidad de REFRESH FAST. Pero las voy a crear de forma errónea para demostrar el poder que nos brinda el paquete DBMS_ADVISOR.SQL_10gR2> CREATE MATERIALIZED VIEW LOG ON test1 2 WITH ROWID, SEQUENCE (id) ;Materialized view log created.SQL_10gR2> CREATE MATERIALIZED VIEW LOG ON test2 2 WITH ROWID, SEQUENCE (id,nom) ;Materialized view log created.Procedemos a crear la Vista Materializada:SQL_10gR2> CREATE MATERIALIZED VIEW TEST_MV 2 NOCOMPRESS 3 LOGGING 4 BUILD IMMEDIATE 5 USING INDEX 6 REFRESH FAST ON DEMAND 7 USING DEFAULT LOCAL ROLLBACK SEGMENT 8 DISABLE QUERY REWRITE 9 AS 10 SELECT test2.id, 11 test2.nom, 12 SUM(test1.total) sum_total, 13 COUNT(*) cnt, 14 COUNT(test1.total) cnt_sum 15 FROM test1, 16 test2 17 WHERE test1.id = test2.id(+) 18 GROUP BY test2.id, 19 test2.nom 20 ORDER BY id ;FROM test1, *ERROR at line 15:ORA-32401: materialized view log on "CDW"."TEST2" does not have new valuesEfectivamente como supusimos, tenemos errores en las tablas de Log que creamos.Ahora vamos a ejecutar DBMS_ADVISOR.TUNE_MVIEW y ver los resultados en la tabla USER_TUNE_MVIEW...SQL_10gR2> DECLARE 2 l_nombre VARCHAR2(100) := 'test_1' ; 3 BEGIN 4 DBMS_ADVISOR.TUNE_MVIEW(l_nombre, 'CREATE MATERIALIZED VIEW TEST_MV 5 NOCOMPRESS 6 LOGGING 7 BUILD IMMEDIATE 8 USING INDEX 9 REFRESH FAST ON DEMAND 10 USING DEFAULT LOCAL ROLLBACK SEGMENT 11 DISABLE QUERY REWRITE 12 AS 13 SELECT test2.id, 14 test2.nom, 15 SUM(test1.total) sum_total, 16 COUNT(*) cnt, 17 COUNT(test1.total) cnt_sum 18 FROM test1, [...]



Exchange Partition

2007-09-24T11:08:50.033-03:00

Exchange Partition permite cargar en tablas particionadas datos en forma rápida y con muy poco impacto para los usuarios que se encuentran activos.En resumen, lo que hace la sentencia Exchange Partition es modificar el diccionario de datos y simular que los datos que ya tenemos cargados en una tabla, corresponden a una partición determinada de otra tabla.Veamos un ejemplo muy sencillo para entender mejor éste tema:SQL_10gR2> CREATE TABLE datos_1 AS 2 SELECT level id, timestamp'2000-11-02 09:00:00' fecha 3 FROM dual 4 CONNECT BY level CREATE TABLE datos_2 AS 2 SELECT level id, timestamp'2001-09-10 13:00:00' fecha 3 FROM dual 4 CONNECT BY level CREATE TABLE test 2 ( id, fecha ) 3 PARTITION BY RANGE ( fecha ) 4 ( 5 PARTITION year_2000 VALUES LESS THAN ( timestamp'2000-12-02 00:00:00' ), 6 PARTITION year_2001 VALUES LESS THAN ( timestamp'2001-10-10 00:00:00' ) 7 ) 8 AS 9 SELECT 1, timestamp'2000-11-02 09:00:00' 10 FROM dual 11 WHERE 1 = 0 ;Vamos a realizar un alter para modificar el diccionario de datos y relacionar cada una de las 2 tablas que creamos con la respectiva partición de la tabla TEST...SQL_10gR2> ALTER TABLE test 2 EXCHANGE PARTITION year_2000 3 WITH table datos_1 4 WITHOUT VALIDATION ;Table altered.Elapsed: 00:00:00.03SQL_10gR2> ALTER TABLE test 2 EXCHANGE PARTITION year_2001 3 WITH table datos_2 4 WITHOUT VALIDATION ;Table altered.Elapsed: 00:00:00.02SQL_10gR2> SELECT count(*) 2 FROM test ; COUNT(*)---------- 2000001 row selected.SQL_10gR2> SELECT count(*) 2 FROM datos_1 ; COUNT(*)---------- 01 row selected.SQL_10gR2> SELECT count(*) 2 FROM datos_2 ; COUNT(*)---------- 01 row selected.Como podemos ver, con el Exchange Partition no tardamos casi nada en cargar los datos en la tabla particionada ya que en realidad no estamos cargando los datos, simplemente se modifica el diccionario de datos.Pueden notar que agregué la sentencia WITHOUT VALIDATION. Que es ésto? WITHOUT VALIDATION suele ser una operación rápida porque sólo realiza modificaciones en el diccionario de datos. Si la tabla o tabla particionada que colocamos en el Exchange Partition tiene una primary key o unique constraint habilitado, entonces el Exchange Partition se realiza como WITH VALIDATION para mantener la integridad de las constraints. Vamos a ejecutar nuevamente los 2 alter anteriores sin la sentencia WITHOUT VALIDATION...SQL_10gR2> ALTER TABLE test 2 EXCHANGE PARTITION year_2000 3 WITH table datos_1 ;Table altered.Elapsed: 00:00:01.00SQL_10gR2> ALTER TABLE test 2 EXCHANGE PARTITION year_2001 3 WITH table datos_2 ;Table altered.Elapsed: 00:00:01.05Si hubiera ejecutado esos alter con un Trace, el reporte del Trace me mostraría, entre otras sentencias, las siguientes...select 1from "DATOS_1" where TBL$OR$IDX$PART$NUM("TEST", 0, 3,1048576,"FECHA") != :1call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 1 0 0Fetch 1 0.04 0.04 0 65 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 3 0.04 0.04 0 66 0 0Mis[...]