Archivo de la categoría: PLSQL

Jugando con las estadisticas de un esquema

Posted on por
Responder

Hoy vamos a ver algunas consultas practicas de las estadisticas de sistema

Esquemas o objetos de un esquema con las estadisticas bloqueadas

Veamos algunas consultas utiles para comprobar cuales son las tablas que estan bloqueadas o para trabajar con las estadistias del esquema APPSCHEMA

Tablas conlas estadisticas bloqueadas para un esquema


select owner, table_name, stattype_locked
from dba_tab_statistics
where
stattype_locked is not null and OWNER=’APPSCHEMA’;

Cuando fueron bloqueadas las estadisticas en estas tablas?


set linesize 200
set pagesize 0
col owner format a20;
col table_name format a30;
Select table_name,last_analyzed from dba_tables
where table_name in
(select table_name from dba_tab_statistics where stattype_locked is not null and OWNER=’APPSCHEMA’)
order by last_analyzed asc ;

Tenemos una metaconsulta para desbloquearlas todas?

  select 'exec dbms_stats.unlock_table_stats('||chr(39)||'APPSCHEMA'||chr(39)||','||chr(39)||TABLE_NAME||chr(39)||' );'
       from dba_tab_statistics where stattype_locked is not null and OWNER='APPSCHEMA';

Ver las fechas de las estadisticas que han sido analizadas en los ultimos 2 dias

set pagesize 9000;
column owner format a20;
column TABLE_NAME format a30;
column PARTITION_NAME format a30;
select owner,table_name,partition_name,to_char(LAST_ANALYZED,'dd-mm-yy hh:mi'),NUM_ROWS,STATTYPE_LOCKED from dba_TAB_STATISTICS
where OWNER='APPSCHEMA' 
and LAST_ANALYZED  < sysdate-2
order by LAST_ANALYZED asc ;

Ver la fecha en la que fueron obtenidas las estadisticas de una tabla en concreto

set pagesize 9000;
column owner format a20;
column TABLE_NAME format a30;
column PARTITION_NAME format a30;
select owner,table_name,partition_name,to_char(LAST_ANALYZED,'dd-mm-yy hh:mi'),NUM_ROWS,STATTYPE_LOCKED from dba_TAB_STATISTICS
where TABLE_NAME='APPSCHEMA' 
order by LAST_ANALYZED asc ;

Como salvamos las estadisticas de un determinado esquema

Creamos una tabla de estadisticas llamada «MISTATS bajo el esquema APPSCHEMA

exec dbms_stats.create_stat_table(ownname => 'APPSCHEMA', stattab => 'MISTATS');

Si quisiesemos guardar solo las estadisticas de una tabla

  
exec dbms_stats.export_table_stats(ownname=>'APPSCHEMA', tabname=>'MISTATS', statown=>'APPSCHEMA', stattab=>'MISTATS', cascade=>true);

Si queremos salvar los de un esquema

EXEC DBMS_STATS.export_schema_stats('APPSCHEMA','MISTATS',NULL,'APPSCHEMA');

Mas informacion en

Bucle sleep en codigo PL-SQL

Posted on por
Responder

Vamos a ver una entrada rapidisima y sencillisima para dummies.

Como introducimos una espera en codigo PL-SQL

La respuesta es sencillisima, con la funcion DBMS_LOCK.SLEEP
Veamos por ejemplo como forzar dos esperas de 1 minuto para obtener un hang analyzer 

$ORACLE_HOME/bin/sqlplus -s "/as sysdba" << EOF
oradebug setmypid;
oradebug unlimit;
oradebug hanganalyze 3;
exec dbms_lock.sleep(60);
-- Wait upto 1 minute before getting the second hanganalyze
oradebug hanganalyze 3;
-- Wait upto 1 minute before getting the second hanganalyze
exec dbms_lock.sleep(60);
oradebug hanganalyze 3;
oradebug tracefile_name;
EOF

ROW_LIMITING Mejoras importantes en la visualización de datos en 12c

Posted on por
Responder

Las consultas que ordenan los datos y obtienen partes de ellos ordenados son llamadas TOP_N consultas. Al contrario que en otros motores de bases de datos, hasta el momento Oracle resolvia estas consultas mediante el ROWNUM y otras tecnicas similares
.
En la version 12c Oracle ya nos brinda la funcionalida de ROW_LIMITING, que viene a ser el uso de un offset y un principio y fin en estas consultas. La sintaxis para usarla en la cláusula SELECT es:

[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
    { ROW | ROWS } { ONLY | WITH TIES } ]

Algunas consultas serín:

SELECT val  FROM   tabla1  ORDER BY campo 
   FETCH FIRST 5 ROWS;

SELECT val  FROM   tabla1  ORDER BY campo 
   FETCH FIRST 10 PERCENT ROWS ONLY;

SELECT val  FROM   tabla1  ORDER BY campo
    OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

Si lo quisiesemos con duplicados 

SELECT val  FROM   tabla1   ORDER BY campo DESC
  FETCH FIRST 5 ROWS WITH TIES;

Cosas a tener en cuenta

  • Si no especificamos offset este será cero
  • Igualmente, si ponemos un offset negativo sera cero también
  • Si ponemos un valor nulo en offset,rowcount o porcentaje, la consulta no devolver nada
  • Los valores de offset,rowcount o porcentaje son truncados en caso de no ser enteros
  • Si el porcentaje que indicas es mayor que el numero de filas que quedan devolverá todas
  • Las palabras ROW y ROWS son intercambiables

NOTA:Estas opciones no pueden usarse en clasulas FOR UPDATE , en secuencias CURRVAL/NEXTVAL o en cláusulas de fast refresh de vistas materializadas.

ms información como siempre en

Problemas con la clausula EDITIONABLE en DBMS_METADATA.GET_DDL

Posted on por
Responder

Una de las librerías mas útiles de Oracle para obtener código de una base de datos es la librería DBMS_METADATA.GET_DDL.
Con la llegada de 12c tenemos la nueva funcionalidad de objetos «EDITIONABLE/NONEDITIONABLE » , si usamos la librería DBMS_METADATA.GET_DDL, el código de aplicación que nos generará incluirá esta propiedad.

Pero que pasaría al intentar ejecutarlo en otra base de datos 11g, si ejecutamos el código 

CREATE OR REPLACE EDITIONABLE PROCEDURE "ESQUEMA"."PRUEBA" (

Nos encontraremos con el error 

ERROR en línea 1:
ORA-00922: falta la opción o no es válida

¿Como mitigamos este error?
Actualmente hacemos las llamadas de la forma

DBMS_METADATA.GET_DDL('${TIPO}','${OBJETO}','${ESQUEMA}')

Simplemente tendremos que poner un parámetro adicional de compatibilidad de código ,fijándolo a la 11.2.0, que es la última versión donde no está definido el EDITIONABLE en el código 

DBMS_METADATA.GET_DDL('${TIPO}','${OBJETO}','${ESQUEMA}','11.2.0')

Como siempre ,más información en soporte

    Doc ID 2079143.1How to Remove EDITIONABLE/NONEDITIONABLE Clause when DBMS_METADATA.GET_DDL

Cuando se usan los indices en una consulta con LIKE

Posted on por

Hoy vamos ha hacer una pequeña entrada de tunning.

Muchas veces vemos a la hora de hacer ajustes de rendimiento como las consultas que se ejecutan la base de datos tienen la clausula LIKE, pero su comportamiento es distinto segun como se ejecuta.
¿Cuando se hace un INDEX RANGE y cuando un FULL SCAN? , esto es principalmente lo que veremos hoy

Lo primero,¿ que es lo que hace la claúsula LIKE ?
La clausula LIKE mira si una cadena determinada coincide con un determinado patrón. Mediante el carácter % podemos hacer búsquedas genéricas con esa cadena .
Hemos de destacar que, LIKE contempla la búsqueda de longitud cero, es decir ,que la búsqueda de LIKE ‘PEPE% ‘ incluye tanto PEPESI como PEPE.

Una vez tenemos claro que hace la clausula LIKE, veamos de que manera podemos utilizarlo:

  • CADENA%
  • %CADENA
  • %CADENA%
  • CADENA1%CADENA2

Vamos a ver el comportamiento en cada una de las consultas. Tenemos una tabla llamada Imagen con un campo varchar NOMBRE 

SQL> desc imagen
 Name                 Null?    Type
 ------------------ -------- ---------------
 ID                  NOT NULL NUMBER
 NOMBRE                       VARCHAR2(256)
 RUTA                         VARCHAR2(256)

Sobre este campo NOMBRE hemos creado un y un UNIQUE INDEX llamado IMAGEN_NOMBRE_UNI, vamos ha hacer los 4 tipos de búsqueda y comparar los planes:

CADENA%

Si ejecutamos una búsqueda en la que miramos el final de la cadena, la base de datos intentará utilizar primero el índice 

set autotrace traceonly;
SQL> select * from  imagen where nombre like 'D80%';
|Id | Operation                  | Name           |Rows |Bytes| 
------------------------------------------------------------------
| 0 | SELECT STATEMENT            |                  | 1 |143|3(0)|
| 1 |  TABLE ACCESS BY INDEX ROWID| IMAGEN           | 1 |143|3(0)|
|*2 |   INDEX RANGE SCAN          | IMAGEN_NOMBRE_UNI| 1 |   |2(0)|
-------------------------------------------------------------------

El optimizador conoce como comienza la cadena, con lo que llevará a cabo un INDEX RANGE SCAN para llevar a cabo la consulta con el menor coste posible.

%CADENA

EN este caso estamos buscando el final de la cadena entre el contenido de nuestra columna NOMBRE, si miramos el resultado del plan de ejecución

set autotrace traceonly;
SQL> select * from  imagen where nombre like '%009';
----------------------------------------------------
| Id  | Operation    | Name   | Rows  | Bytes |
-------------------------------------------------
| 0| SELECT STATEMENT  |       |778 |108K| 93 (2)
|*1|  TABLE ACCESS FULL| IMAGEN|778 |108K| 93 (2)
-------------------------------------------------

El optimizador desconoce como comienza la cadena, con lo que llevará a cabo un TABLE ACCESS FULL de toda la tabla.En este caso no usa el índice ya que no le sirve de nada el indice si debe de comparar la parte final de la cadena

%CADENA%

Que ocurrirá si buscamos la parte central de una cadena?

set autotrace traceonly;
SQL> select * from  imagen where nombre like '%009%';
----------------------------------------------------
| Id  | Operation    | Name   | Rows  | Bytes |
-------------------------------------------------
| 0| SELECT STATEMENT  |       |778 |108K| 93 (2)
|*1|  TABLE ACCESS FULL| IMAGEN|778 |108K| 93 (2)
-------------------------------------------------

El optimizador desconoce como comienza la cadena, con lo que llevará a cabo un TABLE ACCESS FULL de toda la tabla.
En este caso no usa el índice ya que no le sirve de nada el indice si debe de comparar la parte final de la cadena , es el mismo comportamiento que cuando lanzamos la consulta con %CADENA

CADENA1%CADENA2

Que ocurrirá si buscamos la parte central de una cadena?

set autotrace traceonly;
SQL>  select * from imagen where nombre like 'D80_10%G';

|Id | Operation                  | Name           |Rows |Bytes| 
------------------------------------------------------------------
| 0 | SELECT STATEMENT            |                  | 1 |143|3(0)|
| 1 |  TABLE ACCESS BY INDEX ROWID| IMAGEN           | 1 |143|3(0)|
|*2 |   INDEX RANGE SCAN          | IMAGEN_NOMBRE_UNI| 1 |   |2(0)|
-------------------------------------------------------------------

Este caso es muy similar al de CADENA%, el optimizador si que conoce el comienzo de la cadena, con lo que llevará a cabo un INDEX RANGE SCAN para llevar a cabo la consulta en el menor.
Hay que tener en cuenta que, contra mas restrictiva sea la búsqueda mas rápido será y tendrá menos coste para la base de datos. es decir que, aunque en los dos casos use el índice, el coste de buscar CAD%CADENA2 será mayor que el de buscar CADENA1%CADENA2