Category: Tuning / Performance

Oracle11gr2 : Nueva característica de los índices UNUSABLES (UNUSABLE INDEX)



En Oracle11gr2 tenemos 2 opciones para dejar sin uso un índice

  • Puede ser mediante ALTER INDEX UNUSABLE
  • O puede ser mediante ALTER INDEX INVISIBLE

    La opción de UNUSABLE (que es el motivo de este post :yes: ) , viene con una característica nueva.

    Cuando se genera un índice en estado UNUSABLE , este índice no genera espacio a diferencia de lo que sucede en Oracle11gr1 o inferior.

    Ejemplos de está pequeña característica :

    1.- Creamos una tabla de ejemplo con un índice común y corriente del tipo B*Tree

    2.- Verificamos el estado de los objetos y del índice, más su tamaño

    3.- Alteramos nuestro índice , para dejarlo como UNUSABLE

    4.- Volvemos a preguntar por sus segmentos y vemos que siguen ocupando el mismo tamaño

    5.- Ahora creamos un índice en estado UNUSABLE

    6.- Verificamos su tamaño y nos damos cuenta que ha generado un segmento ,pero sin bloques (en realidad sólo la estructura)

    7.- Procedemos a truncar la tabla

    8.- Y nos damos cuenta que el anterior índice UNUSABLE, lo deja como válido y sin espacio en su segmento

    Espero les sirva


  • by Ligarius
    03.09.09. 11:27:58. 191 words, 5829 views. Categories: Tuning / Performance, Oracle11gR2 ,

    Sql*Plus : Como conectarse a una base de datos con problema (opción _prelim de Sql*Plus)



    A veces queremos conectarnos a una instancia Oracle, y lo hacemos , como no, con el usuario más poderoso que existe, con SYS como SYSDBA, pero ni eso resulta y simplemente no podemos conectarnos :(

    Pues bien , para esos momentos de desdicha, podemos hacer dos cosas

  • O reiniciar la instancia
  • O buscar los porque..
  • Y como somos DBA de peso, queremos saber el por que, cierto?? :yes:



    Pues bien, para realizar esa gran tarea de análisis, ocupamos un comando no documentado de Sql*Plus llamado la conección preliminar, con lo cual no nos conectamos a la instancia Oracle, pero... tenemos acceso a muchas vistas del diccionario de datos :>> , así tal cual , realizamos una conección pero sin conectarnos


    Acá va el ejemplo de ello

    Seteamos la opción oculta llamada "Conexión preliminar"

    [oracle@oracle10g oracle]$ sqlplus /nolog

    SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 26 04:20:56 2010

    Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

    SQL> set _prelim on


    Posterior a eso, realizamos la conexión a la base de datos, pero esto en realidad no realizará la conexión , de hecho si tratará de hacerlo se quedaría pegada.

    SQL> conn / as sysdba
    Prelim connection established


    Si quisieramos ejecutar alguna consulta , aparecería el siguiente mensaje

    SQL> select file_name from dba_data_files;
    select file_name from dba_data_files
    *
    ERROR at line 1:
    ORA-01012: not logged on

    SQL>


    Y esto es porque en realidad, no hemos hecho la conexión, pero si podemos obtener información estadística, por ejemplo para hacer un trace de la sesión y así poder detectar el error.

    Para realizar el comentado trace, podemos ocupar otro utilitario no documentado :>> parece cuento , este utilitario es el oradebug , para ello ocupamos los siguientes comandos.

    SQL>
    SQL> oradebug setmypid
    Statement processed.
    SQL> oradebug unlimit
    Statement processed.
    SQL> oradebug hanganalyze 3
    Hang Analysis in /u01/app/oracle/admin/orcl/udump/orcl_ora_5417.trc


    Ahora viene la explicación de los comandos utilizados

  • oradebug setmypid : Es un debug para el actual proceso que ejecuto el comando
  • oradebug hanganalyze 3 : Este comando sirve para generar un trace por HANG de la base de datos, de acuerdo al nivel que se le ha otorgado
  • Este nivel puede ser :

  • 1 y 2 : Sólo un análisis de HANG
  • 3 : Nivel 2 más los procesos con HANG
  • 4 : Nivel 3 más un vacíado de información de los bloqueadores
  • 5 : Nivel 4 más un vacíado de todos los procesos involucrados en el HANG
  • 10 : Un volcado de todos los procesos existentes (muy costoso)
  • Y claro , ahora viene la revisión del archivo de trace generado , la visualización del problema y el reinicio de la instancia Oracle ;)

    El tamaño del archivo de trace esta dado por el parámetro MAX_DUMP_FILE_SIZE , pero si queremos un tamaño mayor , ejecutamos el comando

    SQL> oradebug unlimit

    Antes de hacer el debug del trace

    Espero les sirva

    by Ligarius
    27.08.09. 18:19:56. 480 words, 28303 views. Categories: Base de datos, Tuning / Performance, Sql*Plus ,

    Oracle 11g : Analizando segmentos con DBVerify



    El famoso DBV (Database Verify) en Oracle11g sufrio una pequeña modificación, que de verdad se agradece mucho.

    No solamente es capaz de realizar análisis a datafiles de la base de datos, sino también puede realizar análisis de segmentos , esto proporciona una nueva herramienta cuando nos encontramos con cosas medias extrañas en nuestras consultas

    El como se utiliza



    Para poder ejecutar DBV sobre un segmento , necesitamos cierta información, toda proveniente desde la tabla SYS_USER_SEGS, para poder realizar la consulta debemos ser usuarios SYSDBA.

    dbv userid="pruebadbv/oracle" segment_id=0.1.88289 logfile=salida_dbv feedback=100


    El campo feedback nos provee un puntito por cada X valor que le hayamos dicho, esto esta expresado en cantidad de páginas procesadas por un puntito :)

    Segment_id = Está asociado a la información que necesitamos desde la SYS_USER_SEGS, esta información esta conformada por TABLESPACE_ID , HEADER_FILE y HEADER_BLOCK

    Un ejemplo de consulta para obetner toda está información.

    SQL> select tablespace_id , header_file , header_block from sys_user_segs where segment_name like 'TABLA_VALIDACIONES';

    TABLESPACE_ID HEADER_FILE HEADER_BLOCK
    ------------- ----------- ------------
    0 1 88289


    Este dato (0.1.88289) es el que utilizamos en nuestro dbv sobre el segmento

    Un ejemplo de salida del dbv en Oracle11g

    DBVERIFY - Verification starting : SEGMENT_ID = 0.1.88289

    DBVERIFY - Verification complete

    Total Pages Examined : 1025
    Total Pages Processed (Data) : 1016
    Total Pages Failing (Data) : 0
    Total Pages Processed (Index): 0
    Total Pages Failing (Index): 0
    Total Pages Processed (Other): 0
    Total Pages Processed (Seg) : 0
    Total Pages Failing (Seg) : 0
    Total Pages Empty : 9
    Total Pages Marked Corrupt : 0
    Total Pages Influx : 0
    Total Pages Encrypted : 0
    Highest block SCN : 1098130 (0.1098130)


    Información del DBV en Oracle11g y en Oracle10g

    Espero les sirva

    by Ligarius
    31.08.09. 07:23:48. 286 words, 11051 views. Categories: Oracle 11g, Tuning / Performance ,

    Monitorear o no monitorear , he allí el problema



    Antes nos decían que debiamos monitorear la tabla para que quedase registro de las DMLs en la tabla *_TAB_MODIFICATIONS, y claro, eso es real hasta Oracle10gr1 , en Oracle10gr2 cambia un poco el tema.

    Recordar un poco que el monitoring de una tabla, se realizaba con el comando


    ALTER TABLE nombre_tabla MONITORING;

    Y se puede apreciar si está o no monitoreada con el comando

    select owner , table_name , monitoring from dba_tables where table_name like '%NOMBRE_TABLA%';

    Cuando el parámetro STATISTIC_LEVEL está en TYPICAL, el monitoreo de todas las tablas es por defecto, lo que implica que siempre se almacenará su información de DMLs en la tabla *_TAB_MODIFICATIONS (Esto es sólo en Oracle10gr2)

    Ejemplo de la consulta :

    SQL> select owner , table_name , tablespace_name , monitoring from dba_tables where table_name like 'TABLA_FULL_%';

    OWNER TABLE_NAME TABLESPACE_NAME MON
    ------------------------------ ------------------------------ ------------------------------ ---
    SYS TABLA_FULL_7 SYSTEM YES
    SYS TABLA_FULL_6 SYSTEM YES
    SYS TABLA_FULL_5 SYSTEM YES
    SYS TABLA_FULL_4 SYSTEM YES
    SYS TABLA_FULL_3 SYSTEM YES
    SYS TABLA_FULL_2 SYSTEM YES
    SYS TABLA_FULL_1 SYSTEM YES

    Y ejemplo de consulta en la tabla que lleva registro de las DMLs en una tabla

    SQL> select table_name , inserts , updates , deletes from user_tab_modifications where table_name like '%TABLA_FULL_1%'

    TABLE_NAME INSERTS UPDATES DELETES
    ------------------------------ ---------- ---------- ----------
    TABLA_FULL_1 0 0 0

    Para llevar a cabo un pequeño ejemplo del registro de las DML , procedemos a borrar una cantidad de registros de una tabla

    SQL>delete tabla_full_6 where rownum < 1000 ;

    Ejecutamos el vaciado desde memoria de la información de DMLs sobre las tablas

    exec dbms_stats.flush_database_monitoring_info;

    Está última instrucción siempre registra las DMLs hacía una tabla, aunque se le haya realizado rollback, saca la información desde memoria y la lleva a tablas físicas, este comando se puede dejar de lado y esperar que SMON vacíe la información cada 15 minutos aproximadamente.

    Ejemplo de salida

    SQL> r
    1 select table_name , inserts , updates , deletes from user_tab_modifications
    2* where table_name like '%TABLA_FULL_1%'

    TABLE_NAME INSERTS UPDATES DELETES
    ------------------------------ ---------- ---------- ----------
    TABLA_FULL_1 0 0 1000

    Y quizás la pregunta es válida.. ¿Sirve saber la cantidad de delete a pesar de que haya rollback? , uno podría pensar hay fragmentación , una forma de analizar esto podría ser contando el número de bloques cuando :
    - No se ha realizado operación sobre la tabla
    - Se ha producido un borrado
    - Se ha realizado el rollback

    La verdad , se haga o no commit, siempre se registran los datos sobre la tabla

    Miremos el siguiente ejemplo

    Info original

    SQL> r
    1 select table_name , inserts , updates , deletes from user_tab_modifications where table_name like '%TABLA_FULL_1%'

    TABLE_NAME INSERTS UPDATES DELETES
    ------------------------------ ---------- ---------- ----------
    TABLA_FULL_1 0 0 1000

    Procedemos a eliminar registro de la tabla 1 (TABLA_FULL_1) , hacer rollback y vaciar la info desde memoria

    SQL> SQL> delete tabla_full_1 where rownum < 100;

    99 rows deleted.

    SQL> rollback;

    Rollback complete.

    SQL>
    SQL>
    SQL> exec dbms_stats.flush_database_monitoring_info;

    PL/SQL procedure successfully completed.

    SQL>

    Y consultamos nuevamente la tabla de modificaciones

    SQL> select table_name , inserts , updates , deletes from user_tab_modifications where table_name like '%TABLA_FULL_1%'
    2 ;

    TABLE_NAME INSERTS UPDATES DELETES
    ------------------------------ ---------- ---------- ----------
    TABLA_FULL_1 0 0 1099

    Como podemos ver, igual marca los datos a pesar de que haya un rollback

    ¿Y registrará las modificaciones en la *_tab_modifications pues hay un incremento o disminución de bloques?

    Podemos comprobarlo mediante la siguiente consulta

    SQL> col segment_name format a40
    SQL> r
    1* select blocks , bytes/1024/1024 MB , owner , segment_name from dba_segments where segment_name like 'TABLA_FULL_1'

    BLOCKS MB OWNER SEGMENT_NAME
    ---------- ---------- ------------------------------ ----------------------------------------
    768 6 SYS TABLA_FULL_1

    7 rows selected.

    Ahora procedemos a borrar , hacer rollback y realizar la misma consulta

    SQL> delete tabla_full_1;

    101220 rows deleted.

    SQL> SQL> rollback;

    Rollback complete.

    SQL>
    SQL>
    SQL> select blocks , bytes/1024/1024 MB , owner , segment_name from dba_segments where segment_name like 'TABLA_FULL_1';

    BLOCKS MB OWNER SEGMENT_NAME
    ---------- ---------- ------------------------------ ----------------------------------------
    768 6 SYS TABLA_FULL_1

    Y obtenemos exactamente la misma información

    ¿Y si desactivamos el monitoring de la tabla, sigue guardando registro en la *_tab_modification?
    La respuesta es si, ya que desactivar el monitoring, simplemente no se puede

    SQL> select owner , table_name , tablespace_name , monitoring from dba_tables where table_name like 'TABLA_FULL_1';

    OWNER TABLE_NAME TABLESPACE_NAME MON
    ------------------------------ ------------------------------ ------------------------------ ---
    SYS TABLA_FULL_1 SYSTEM YES

    1 rows selected.

    SQL> alter table tabla_full_1 nomonitoring;

    Table altered.

    SQL> select owner , table_name , tablespace_name , monitoring from dba_tables where table_name like 'TABLA_FULL_1';

    OWNER TABLE_NAME TABLESPACE_NAME MON
    ------------------------------ ------------------------------ ------------------------------ ---
    SYS TABLA_FULL_1 SYSTEM YES

    Con el parámetro STATISTICS_LEVEL en BASIC , nada de esto se generá de forma automática. Y tener muy en cuenta que Oracle toma en cuenta los datos de esta tabla para verificar que tablas están con más de un 10% de modificaciones (tablas stale) ante lo cual le toma estadísticas

    Una consulta bastante simple para saber a que tablas se les debiese tomar estadísticas de acuerdo a la cantidad de DMLs ejecutadas , es la siguiente

    col porcentaje format 9999D999

    select u.TIMESTAMP,
    t.last_analyzed,
    u.table_name,
    u.inserts,
    u.updates,
    u.deletes,
    d.num_rows,
    TO_CHAR(((U.inserts+u.deletes+u.updates)/decode(nvl(d.num_rows,0),0,1,d.num_rows)) * 100,'999D99') percent
    from user_tables t,USER_TAB_MODIFICATIONS u,dba_tables d
    where u.table_name = t.table_name
    and d.table_name = t.table_name
    and d.owner = 'SYS'
    and (u.inserts is not null or u.updates is not null or u.deletes is not null)
    and ((U.inserts+u.deletes+u.updates)/decode(nvl(d.num_rows,0),0,1,d.num_rows)) * 100 > 10
    order by t.last_analyzed , ((U.inserts+u.deletes+u.updates)/decode(nvl(d.num_rows,0),0,1,d.num_rows)) * 100

    Puede tener variaciones y ciertas especulaciones, pero es una orientadora...

    Otro punto a tener en cuenta, es cuando se toman estadísticas, se elimina la información sobre la *_TAB_MODIFICATIONS

    Espero les sea de utilidad

    by Ligarius
    19.08.09. 09:46:32. 1052 words, 7598 views. Categories: Oracle 10g, Tuning / Performance ,

    Tuning SQL : Lo malo de usar UNION en los select :)



    Hace poco acabo de terminar un curso de Tuning de SQL ,el código es el D19165GC20 , en ese curso hay muchos detalles los cuales uno maneja a diario, pero de lo cual en mi caso nunca he escrito.

    He aquí un ejemplo, el porque siempre se debiese ocupar el UNION ALL , en vez del UNION , esto último debido a que el UNION provoca un ordenamiento implicito de los datos dentro de la PGA , a pesar de que como todos sabemos el UNION ALL entrega todas las filas a pesar de que estas se repitan , el UNION sólo entrega aquellas diferentes.

    He aquí el caso del porque es tan caro el simple, común y pequeño UNION ;)

    Para realizar los ejemplos, hemos creado 7 tablas las cuales tienen informacion desde la dba_objects, o sea, cada una de estas tablas posee como 55 mil registros, mas o menos.

    Vaciamos la memoria, de todo bloque para que la prueba sea más acertiva.

    alter system flush shared_pool ;
    alter system flush buffer_cache ;

    Primero utilizamos el simple UNION

    select * from tabla_full_1
    union
    select * from tabla_full_2
    union
    select * from tabla_full_3
    union
    select * from tabla_full_4
    union
    select * from tabla_full_5
    union
    select * from tabla_full_6
    union
    select * from tabla_full_7
    ;

    Cantidad de filas traídas en la consulta

    50610 rows selected.

    Y observamos el plan de ejecución

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3784107405

    --------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
    --------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 708K| 28M| | 8800 (86)| 00:01:46 |
    | 1 | SORT UNIQUE | | 708K| 28M| 76M| 8800 (86)| 00:01:46 |
    | 2 | UNION-ALL | | | | | | |
    | 3 | TABLE ACCESS FULL| TABLA_FULL_1 | 101K| 4151K| | 151 (3)| 00:00:02 |
    | 4 | TABLE ACCESS FULL| TABLA_FULL_2 | 101K| 4151K| | 152 (3)| 00:00:02 |
    | 5 | TABLE ACCESS FULL| TABLA_FULL_3 | 101K| 4151K| | 152 (3)| 00:00:02 |
    | 6 | TABLE ACCESS FULL| TABLA_FULL_4 | 101K| 4151K| | 152 (3)| 00:00:02 |
    | 7 | TABLE ACCESS FULL| TABLA_FULL_5 | 101K| 4151K| | 152 (3)| 00:00:02 |
    | 8 | TABLE ACCESS FULL| TABLA_FULL_6 | 101K| 4151K| | 152 (3)| 00:00:02 |
    | 9 | TABLE ACCESS FULL| TABLA_FULL_7 | 101K| 4151K| | 152 (3)| 00:00:02 |
    --------------------------------------------------------------------------------------------

    Como lo aprecian , esta ordenando 28MB de información lo cual consume 76MB de espacio temporal (o sea , se esta utilizando disco) , lo cual produce I/O muy costoso.

    Statistics
    ----------------------------------------------------------
    1423 recursive calls
    0 db block gets
    4876 consistent gets
    4679 physical reads
    0 redo size
    2262291 bytes sent via SQL*Net to client
    37503 bytes received via SQL*Net from client
    3375 SQL*Net roundtrips to/from client
    22 sorts (memory)
    0 sorts (disk)
    50610 rows processed

    Nuevamente borramos todo vestigio desde memoria.

    alter system flush shared_pool ;
    alter system flush buffer_cache ;

    Y ejecutamos la misma consulta, pero esta vez con UNION ALL

    select * from tabla_full_1
    union all
    select * from tabla_full_2
    union all
    select * from tabla_full_3
    union all
    select * from tabla_full_4
    union all
    select * from tabla_full_5
    union all
    select * from tabla_full_6
    union all
    select * from tabla_full_7
    ;

    Trae efectivamente mas de 700 mil registros, los cuales debiesen ser procesados con un cursor, para así descartar los repetidos, es más barato y más rápido

    708540 rows selected.

    El plan de ejecución de la sentencia con UNION ALL

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2473866688

    -----------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -----------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 708K| 28M| 1061 (87)| 00:00:13 |
    | 1 | UNION-ALL | | | | | |
    | 2 | TABLE ACCESS FULL| TABLA_FULL_1 | 101K| 4151K| 151 (3)| 00:00:02 |
    | 3 | TABLE ACCESS FULL| TABLA_FULL_2 | 101K| 4151K| 152 (3)| 00:00:02 |
    | 4 | TABLE ACCESS FULL| TABLA_FULL_3 | 101K| 4151K| 152 (3)| 00:00:02 |
    | 5 | TABLE ACCESS FULL| TABLA_FULL_4 | 101K| 4151K| 152 (3)| 00:00:02 |
    | 6 | TABLE ACCESS FULL| TABLA_FULL_5 | 101K| 4151K| 152 (3)| 00:00:02 |
    | 7 | TABLE ACCESS FULL| TABLA_FULL_6 | 101K| 4151K| 152 (3)| 00:00:02 |
    | 8 | TABLE ACCESS FULL| TABLA_FULL_7 | 101K| 4151K| 152 (3)| 00:00:02 |
    -----------------------------------------------------------------------------------

    Y como se puede apreciar, no hay uso de espacio temporal , sólo se procesan los 28MB de datos.

    Statistics
    ----------------------------------------------------------
    1423 recursive calls
    0 db block gets
    51700 consistent gets
    4679 physical reads
    0 redo size
    27134781 bytes sent via SQL*Net to client
    519985 bytes received via SQL*Net from client
    47237 SQL*Net roundtrips to/from client
    21 sorts (memory)
    0 sorts (disk)
    708540 rows processed

    Para que se tenga en cuenta al momento de realizar un programa

    Espero les sirva

    by Ligarius
    17.08.09. 09:28:17. 626 words, 8236 views. Categories: Oracle 10g, Tuning / Performance ,

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