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!
77 comentarios:
Hola Leonardo,
En primer lugar felicitarte por tu blog.
¿El proceso "exchange partition" es util para traspasar datos de una tabla a otra que guarde los historicos de la anterior?
Muchas gracias,
Hola!
felicidades por tu blog
Hola amigo, gracias por el favor anterior, mira tengo un inconveniente, tengo una tabla donde guarde los especification de mis objetos (paquetes, procedimientos y funciones) x usuarios, (no el body, solo el specification), ejemplo
"mauricio","PACKAGE","PAQUETE_CARGA","create or replace PAQUETE_CARGA is PROCEDURE CREA_TABLA(vp_borde varchar2,vp_ancho varchar2); function gef_semaforos(Pv_IdModulo VARCHAR2) return varchar2; END PAQUETE_CARGA;"
--
MEDIANTE UN CURSOR QUE ME RETORNA EL REGISTRO DEL PAQUETE PREVIAMENTE CARGADO EN MI TABLA, QUIERO MANDARLO A EJECUTAR DESDE OTRO USUARIO,
POR EJEMPLO ASI:
DECLARE
CURSOR BUSCA_OBJETOS (CV_USUARIO VARCHAR2)is
SELECT *
FROM AMBIENTE_NULO A
WHERE A.OWNER= CV_USUARIO;
--Variables
cadena long;
begin
FOR i IN BUSCA_OBJETOS(PV_USUARIO) LOOP
cadena:=i.text;
execute immediate(cadena);
BEGIN --bitacora
INSERT INTO BITACORA_NULO(ID_BITACORA_NULO,DESC_BITACORA,FECHA,USUARIO_AMBIENTE)
VALUES(SEQ_BITACORA_ID.NEXTVAL,'OBJETO CREADO'||i.name,SYSDATE,PV_USUARIO);
EXCEPTION
WHEN OTHERS THEN
INSERT INTO BITACORA_NULO(ID_BITACORA_NULO,DESC_BITACORA,FECHA,USUARIO_AMBIENTE)
VALUES(SEQ_BITACORA_ID.NEXTVAL,'OBJETO NO CREADO,'||i.name,SYSDATE,PV_USUARIO);
END;
commit;
END LOOP;
end;
---ahora cuando lo ejecuto, si lo crea, pero no compilado, me sale un exception asi : ORA-24344: success with compilation error,
repito si lo crea, pero tengo q abrir ese paquete y dar F8 o ejecutar para que se compile, ayudame con esto amigo,
gracias, esperare tu respuesta
Hola Mauricio Cobo, José Rivera, Paul Baño,
Antes que nada, no entiendo porque tienes una tabla donde guardas los especification de tus objetos x usuarios, ya que esta información la tienes en la vista DBA_SOURCE.
Por ejemplo, si quieres buscar el especification de un paquete de un determinado usuario, ejecutas la siguiente consulta...
SELECT text
FROM dba_source
WHERE type = 'PACKAGE'
AND owner = 'INGRESAR_EL_USUARIO'
AND name = 'INGRESAR_EL_NOMBRE_DEL_PAQUETE'
ORDER BY line;
Para resolver tu inconveniente, lo que puedes hacer es lo siguiente: Luego de ejecutar y crear el paquete, ejecutas la siguiente línea de código...
BEGIN
EXECUTE IMMEDIATE 'ALTER PACKAGE aca_va_el_nombre_del_paquete COMPILE';
END;
/
Saludos
Hola Tunning Oracle,
Exchange Partition es útil cuando se quiere convertir la partición de una tabla, en una tabla no particionada. Y por el contrario, permite convertir una tabla, es una partición de una tabla particionada. Oracle logra realizar éste tipo de operaciones, intercambiando los segmentos de datos y de índices en el diccionario de datos. Debido a que no existe un movimiento de datos durante la ejecución de ésta operación, Oracle NO genera redo ni undo; esto hace que la operación se ejecute rápidamente y sin impactar en la performance del sistema.
Si queres ver como funciona Exchange Partition y cual es su función, podes visitar el post http://lhorikian.blogspot.com/2007/09/exchange-partition.html
Saludos
Hola Leonardo, te felicito por tu blog,
tengo una duda, estoy realizando un query dinamico con un par de filtros de acuerdo a ciertas condiciones, pero mi duda es: Que es mas optimo usar para reemplazar las variables ?
1.- utilizando USING con BIND_VARIABLES ó
2.- realizandolo con SYS_CONTEXT y dbms_session.set_context
gracias de antemano.
Hola Crea tu Idea,
Yo NO recomiendo para nada la utilización de consultas dinámicas. En caso de que sea totalmente necesario que tengas que hacerlo, lo más óptimo es utilizar el paquete DBMS_SQL para escribirlas ya que de ésta manera podrás lograr parsear la consulta una sola vez y ejecutarla la cantidad de veces que necesites utilizando Bind Variables.
Saludos
Buenos días!
Primeramente: Felicidades por tu blog. En serio, no sé las veces que lo habré consultado ya... Y suele ser una de las primeras referencias desde consultas en Google sobre Oracle!!
A ver si consigo explicar lo que me está pasando:
Estoy intentando compliar un paquete en el que acabo de añadir una función que como primera instrucción hace un
SELECT ...
BULK COLLECT INTO ...
FROM UNA_VISTA
De antemano te comento que no me da nunca error de compilación, el dato y el tipo de dato en el que vuelca la select mediante BULK COLLECT es todo correcto. Vamos, que copy-paste de cosas hechas de manera similar en el mismo paquete. Creo que la única diferencia consiste en que en este caso, la SELECT es de una vista.
Bien, pues en cuanto esté esa sentencia BULK COLLECT no consigo compilar el paquete. Siempre me da fin del archivo de comunicación - no conectado a oracle. SIEMPRE pierde la conexión al intentar compilarlo. En cuanto quito lo de BULK COLLECT compila perfectamente, sin problemas, pero de la otra manera, siempre pierde la comunicación.
Este error me está dejando anonadado, no sé por donde atacarlo, la verdad.
Estoy con Oracle 10g
Por favor, ¿podrías ayudarme? ¿Te puedo dar algún dato más?
Muchas gracias de antemano!!
Un saludo
Hola Jorge,
Este caso parece ser el indicado para abrir un TAR para que lo investigue el soporte de Oracle ya que al parecer es un Bug.
Lo que puedes hacer es lo siguiente. Luego de compilar el paquete y que te muestre el error ORA-03113 (end-of-file on communication channel), fijate en el directorio UDUMP de tu servidor si se creó algún archivo de trace respecto de éste error. Generalmente, se crea un archivo de trace en ese directorio el cual puede ayudarte a investigar en Metalink si hay algún parche que puedes aplicar para solucionar éste Bug o si existe algún otro tipo de solución.
Saludos
Hola Leonardo,
No se si es posible crear en Oracle, un job que se ejecute por ejemplo de 8:00 a 14:00 y de 15:00 a 02:00.
Se lanzará un procedure que refrescará vistas materializadas, y deseo que se ejecute cuando menos puedo afectar al rendimiento de oracle.
Es posible? Como?
Muchas Gracias y enhorabuena por tu blog!
Hola,
Te muestro cómo ejecutar un Job a las 8 AM y 15 PM. Para ésto, vas a tener que crear 2 Jobs, cada uno con una determinada hora...
ALTER SESSION SET nls_date_format = '(DY) MON DD, YYYY HH24:MI';
## EJECUTAR UN JOB A LAS 8 AM TODOS LOS DÍAS ##
DECLARE
v_jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT (job => v_jobno,
what => 'procedimiento_a_ejecutar;',
next_date => TRUNC(SYSDATE) + 8/24,
interval => 'TRUNC(SYSDATE + 1) + 8/24');
DBMS_OUTPUT.PUT_LINE('Job: '||v_jobno);
COMMIT;
END;
/
## EJECUTAR UN JOB A LAS 15 PM TODOS LOS DÍAS ##
DECLARE
v_jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT (job => v_jobno,
what => 'procedimiento_a_ejecutar;',
next_date => TRUNC(SYSDATE) + 15/24,
interval => 'TRUNC(SYSDATE + 1) + 15/24');
DBMS_OUTPUT.PUT_LINE('Job: '||v_jobno);
COMMIT;
END;
/
Saludos
Hola Leonardo, y gracias por la ayuda de mi comentario anterior:
Te escribo ahora por que tengo una duda sobre un cursor que estoy realizando, mi cursor tiene en particular un parametro que le puedo enviar con un codigo de una persona ejemplo '000078'. O puedo enviar el texto 'TODO' , si el parametro es TODO debera devolver todos los registros de todas las personas.
Mi duda es la siguiente, es conveniente realizar un cursor que cumpla estas condiciones de la siguientw manera
cursor c_per (
Parametrofechaini date,
Parametrofechafin date,
ParamCodigo. Varchar2) is
SELECT codigo, nombre, monto
FROM personas_monto
WHERE fecha > parametrofechaini
AND fecha <= parametrofechafin
AND codigo = DECODE(paramCodigo,'TODO',codigo,paramCodigo);
o me convendria realizarlo por separado es decir En el cuerpo del codigo preguntar si el parametro paramCodigo = 'TODO'
abro un cursor que tenga la consulta identica pero sin el filtro del codigo para que me devuelva todos los codigo de las personas,
y si el paramCodigo es diferente de 'TODO' abro un cursor que contenga la consulta identica al cursor mencionado pero con el filtro
codigo = paramCodigo
sin DECODE porq obviamente ya se que el codigo k necesito es de una persona en particular.
Esto de abrir un cursor o otro deacuerdo al valor del paramCodigo lo realizaria con una funcion que abra el cursor_01 ó el cursor_02.
Gracias. De antemano por la ayuda que me brindes.
LA VERDAD Q ES UTIL E INTERESANTE,GRACIAS POR LA INFO,ME GUSTARIA Q VIERA MI BLO WWW.VOZDEROSARIO.BLOGSPOT.COM
Hi, it's a very great blog.
I could tell how much efforts you've taken on it.
Keep doing!
Hola Leonardo felicitaciones por la pagina ya que de hecho nos ayuda mucho a gente involucrada en este mundillo Oracle
Mi pregunta es sobre la utilizacion del parametro sample en un expdp.
El escenario es que necesito tener un ambiente de pruebas con el 10% de informacion de las tablas del servidor de produccion, se que con la clausula sample se puede asignar el porcentaje, pero mi duda es si los registros que me exportara son en el caso de tablas padre e hijo sus correspondencias, no se si me dejo entender mi esquema tiene 15 tablas y todas se relacionan mediante un codigo unicos (Es un esquema estrella)y lo que necesito es sacar el 10% pero que los registros se correspondan.
Gracias de antemano por tu respuesta
Hola Leonardo.
Estoy comenzando con oracle y me he encontrado en mi trabajo con unas vistas con un SQL muy grande y con el analizador de consultas retorna un costo muy grande, he revisado los índices de las tablas para ver si falta alguno pero creo que no, el problema es que según el Explain Plan no utiliza los índices y entonces hace tabla access full a las tablas mas grandes, buscando la manera de forzar el uso de los índices veo que puedo hacerlo con HINTs el problema es que según nuestro dba debemos minimizar su uso y dudo que me permitan cambiar la vista utilizando HINTs, mi pregunta concreta es por que en la mayoría de foros sobre tunning dicen esto
Evitar el uso de llamadas a funciones PL/SQL en sentencias SQL, veo casi obvia la respuesta(por la recursividad) pero si creo un par de funciones para la vista en mi SQL veo en el Explain Plan una reducción de mas del 80% del costo, pareciera en este caso que no es tan mal usar funciones por que fuerzo el uso del índice, puedes por favor explicarme por que es que no debo utilizar funciones y que puedo hacer para forzar el uso de mis índices.
Hola Programación Basica,
Antes que nada, te doy un consejo: NO te bases en el costo a la hora de realizar tuning sobre una sentencia SQL (sólo tómalo como referencia). Para saber si tu sentencia SQL es performante, hay que basarse en la cantidad de bloques de datos que estamos leyendo tanto desde disco como desde memoria.
El motivo por el cual se produce un decremento en el costo utilizando funciones, es proque el optimizador de costos, NO tiene en cuenta ni la recursividad de la llamada a la función ni el código que se encuentra dentro de la misma a la hora de calcular el costo (el costo se calcula mediante una fórmula interna del optimizador).
El uso de funciones hay que evitarlas en lo posible. Porqué? Por varios motivos:
- Si utlizamos funciones, el plan de ejecución JAMAS va a integrar en el plan de ejecución "padre" con la consulta que se encuentre dentro de la función. Esto quiere decir, que si utilizamos una función que contiene una sentencia SQL, el plan de ejecución del SQL no nos va a mostrar el SQL contenido dentro de la función. Funciona como una caja negra en este caso.
- Si utilizamos funciones, suprimimos el uso de indices. Con esto quiere decir, que si tenemos una función sobre una columna indexada, la función va a producir que se suprima el acceso por índice sobre esa columna. En caso de que tengamos que utilizar funciones sobre columnas indexadas, hay que considerar el uso de indices de funiones (FBI - function-based indexes).
- Si utilizamos funciones, generamos llamadas recursivas que pueden decrementar en GRAN medida la performance de la sentencia SQL.
Espero haber aclarado tu duda.
Saludos
Hola Crea tu Idea,
La mejor manera de realizar lo que necesitas, es preguntar en el código el valor que viene como parámetro. Si el valor es 'TODO' ejecutas un determinado cursor, sino ejecutas el otro cursor. Esto es mucha mas óptimo que realizar una sola consulta con el DECODE ya que evitamos tener que colocar una función (en este caso el DECODE) sobre una columna (en este caso paramCodigo) que puede estar indexada.
Como hablamos anteriormente en este blog, el uso de funciones sobre columnas, suprime el uso de indices sobre las mismas.
Saludos
Hola John,
No. El parámetro SAMPLE toma un determinado porcentaje de registros de manera "random". No tiene en cuenta que los registros se correspondan.
Lo que podes hacer, es utilizar el parámetro QUERY para tomar de cada tabla ciertos registros y hacerlos corresponder de esta manera.
Saludos
Saludos Leonardo.
Espero que todo este bien por aya, y queria preguntarte si tu me puedes ayudar con un ejemplo de RMAN para que sirve y como lo puedo utilizar para respaldar mi base de datos. Ahora estoy usando el datapump pero aun asi se demora mucho, la base de datos tiene un tamaño de 350Gb aproximadamente y este respaldo se lo hace todos los dias. Que me puedes sugerir, y muchas gracias por la atencion prestada.
Ivan
Hola Leonardo,
Soy DBA Oracle hace algún tiempo y durante este he consultado a decenas de colegas sin salir de este escollo.
Si revisamos la documentación de ORACLE o si vamos a metalink, o mismo si realizamos una instalación de BBDD desde cero mediante un wizard siempre obtenemos lo mismo, el seteo del parámetro "sessions" como valor default es la resultante de la siguiente recomendación "Default value Derived: (1.1 * PROCESSES) + 5".
Comprendo a la perfección que es una recomendación y no implica que sea una regla memotécnica a seguir, pero aún así me planteo lo siguiente. Puedo entender que la BBDD utiliza cierta cantidad de procesos que no requieren de una sesión, lo que no puedo comprender de ningúna forma es su opuesto. Una sesión necesita "como mínimo" un proceso para establecerce contra la BBDD, ergo nuca podría tener mas sesiones que procesos...
Ejemplifico, instalo y seteo procesos a un valor de 100, luego por la resultante de la formula seteo sessions a 115. En el mejor escenario práctico no disponemos de ningún proceso sin sesión asociada, entonces, establezco las primeras 100 sesiones cada una de ellas asociada a su proceso.
Cuando quiero establecer mi sesion nro 101, la BBDD no tiene más procesos disponibles, ergo, no permite la conexión ni aún siendo SYS, la resultante, hay que liquidar alguna sessión de usuario vía OS para poder acceder.
La pregunta: ¿En que caso me son útiles esas 15 sesiones excedentes?
Mil gracias y genial el blog y tus explicaciones.
Para aclarar este tema, tenemos que comenzar hablando de las conexiones.
Una conexión es una circuito físico entre el usuario y la base de datos. Como ya sabemos, las conexiones pueden ser en modo dedicado o compartido. Las sesiones pueden establecer ninguna, una o más de una conexión.
Los procesos son utilizados por la sesión para ejecutar sentencias.
Algunas veces, la relación es de uno a uno (CONEXIÓN --> SESIÓN --> PROCESO) como una conexión del tipo dedicada. Algunas veces, tenemos una relación de uno a muchos entre las conexiones y las sesiones (1 CONEXIÓN --> 2 SESIONES --> 1 PROCESO) como es el caso del comando AUTOTRACE.
Veamos un ejemplo:
SQL> select sid, serial#, username from v$session where username is not null;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
164 12 SYS
SQL> select username, program from v$process;
USERNAME PROGRAM
--------------- ----------------------------------------------------------------
PSEUDO
SYSTEM ORACLE.EXE (PMON)
SYSTEM ORACLE.EXE (VKTM)
SYSTEM ORACLE.EXE (DIAG)
SYSTEM ORACLE.EXE (DBRM)
SYSTEM ORACLE.EXE (PSP0)
SYSTEM ORACLE.EXE (SHAD)
SYSTEM ORACLE.EXE (DIA0)
SYSTEM ORACLE.EXE (MMAN)
SYSTEM ORACLE.EXE (DBW0)
SYSTEM ORACLE.EXE (LGWR)
SYSTEM ORACLE.EXE (CKPT)
SYSTEM ORACLE.EXE (SMON)
SYSTEM ORACLE.EXE (RECO)
SYSTEM ORACLE.EXE (MMON)
SYSTEM ORACLE.EXE (MMNL)
SYSTEM ORACLE.EXE (D000)
SYSTEM ORACLE.EXE (S000)
SYSTEM ORACLE.EXE (FBDA)
SYSTEM ORACLE.EXE (SMCO)
SYSTEM ORACLE.EXE (QMNC)
SYSTEM ORACLE.EXE (W000)
SYSTEM ORACLE.EXE (q000)
SYSTEM ORACLE.EXE (q001)
SYSTEM ORACLE.EXE (CJQ0)
25 rows selected.
SQL> set autotrace on statistics;
SQL> select sid, serial#, username from v$session where username is not null;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
125 11 SYS
164 12 SYS
SQL> select username, program from v$process;
USERNAME PROGRAM
--------------- ----------------------------------------------------------------
PSEUDO
SYSTEM ORACLE.EXE (PMON)
SYSTEM ORACLE.EXE (VKTM)
SYSTEM ORACLE.EXE (DIAG)
SYSTEM ORACLE.EXE (DBRM)
SYSTEM ORACLE.EXE (PSP0)
SYSTEM ORACLE.EXE (SHAD)
SYSTEM ORACLE.EXE (DIA0)
SYSTEM ORACLE.EXE (MMAN)
SYSTEM ORACLE.EXE (DBW0)
SYSTEM ORACLE.EXE (LGWR)
SYSTEM ORACLE.EXE (CKPT)
SYSTEM ORACLE.EXE (SMON)
SYSTEM ORACLE.EXE (RECO)
SYSTEM ORACLE.EXE (MMON)
SYSTEM ORACLE.EXE (MMNL)
SYSTEM ORACLE.EXE (D000)
SYSTEM ORACLE.EXE (S000)
SYSTEM ORACLE.EXE (FBDA)
SYSTEM ORACLE.EXE (SMCO)
SYSTEM ORACLE.EXE (QMNC)
SYSTEM ORACLE.EXE (W000)
SYSTEM ORACLE.EXE (q000)
SYSTEM ORACLE.EXE (q001)
SYSTEM ORACLE.EXE (CJQ0)
25 rows selected.
Como podes ver, antes de iniciar el AUTOTRACE ON STATISTICS, sólo habia 1 sesión y 25 procesos; pero cuando iniciamos el AUTOTRACE, vemos 2 sesiones pero la misma cantidad de procesos. Esto es debido a que si habilitamos AUTOTRACE ON STATISTICS, Oracle utiliza OTRA sesión para poder consultar las estadísticas de tu sesión ACTUAL sin que esta consulta afecte las estadísticas de tu sesión!
Como te darás cuenta, hay casos (como el del ejemplo) en los que es necesario tener más cantidad de sesiones seteadas que número de procesos.
Espero haber respondido la pregunta.
Saludos
Hola Leonardo,
Muy bueno tu blog es de gran ayuda. Mi consulta es sobre el RMAN, no tengo muy claro como utilizarlo. En mi trabajo tenemos una base 10g de una 320GB, y queremos utilizar RMAN. Hasta ahora lo que hemos usado es expdp.
Muchas gracias y esperare tu respuesta.
Ivan.
Hola Iván,
Para empezar, una aclaración. Datapump y Exp/Imp NO son herramientas utilizadas para hacer backup de base de datos. No están diseñadas para eso.
La herramienta que debes utilizar es RMAN. Con RMAN tienes mucha flexibilidad a la hora de realizar tus backups. Una de las ventajas es que puedes hacer backups completos o incrementales.
Hacer backup con RMAN es muy fácil si ya tienes los parámetros de RMAN del entorno seteados correctamente. Si tienes tu entorno de RMAN seteado, puedes hacer un backup de tu base de datos completa tan solo ejecutando...
BACKUP DATABASE;
Te aconsejo que leas el manual de RMAN que se encuentra en el OTN ( http://download.oracle.com/docs/cd/E11882_01/backup.112/e10642/toc.htm ).
Saludos
Buenas tardes
Tengo un problema con una vista materializada "fast refresh on commit":
1. la tabla base sobre la que agrupa los registros tiene 30.000.000 de registros apro.
2. la insercion de registros en la tabla es rápida y no da problemas
3.- el borrado o actualización de la cantidad de cualquier registro, tarda 4 minutos en procesar "commit".
la versión de la base de datos es Oracle Database 10g Enterprise Edition Release 10.1.0.5.0
¿se te ocurre alguna explicación al respecto?
Hola Leonardo, tengo un problema al generar un reporte
Cuando genero por todos los beneficiarios me sale este error:
Terminado con error:
REP-69: Internal error java.lang.OutOfMemoryError
y cuando lo hago por un beneficiario si me lo genera.
La salida del reporte es en pdf
Espero tu ayuda Gracias
Hola Jessica,
Generalmente, cuando se produce este error, es debido a que hay que incrementar el tamaño de los parámetros de JVM.
Saludos
Hola Leonardo, estoy comenzando a estudiar oracle y aunque fue una duda que ya te trataron veo que estoy haciendo lo correcto pero me sigue saliendo el mismo error ORA-24344: correcto con error de compilación
Te explico tengo un paquete donde tengo los siguientes procedimientos:
PROCEDURE recompile AS
CURSOR objinv IS SELECT object_name,object_type FROM user_objects WHERE status='INVALID';
TYPE objinv_tab IS TABLE OF objinv%ROWTYPE;
obj objinv_tab;
BEGIN
OPEN objinv;
FETCH objinv BULK COLLECT INTO obj;
CLOSE objinv;
FOR I IN 1..obj.COUNT LOOP
IF obj(i).object_type='PROCEDURE' OR obj(i).object_type='FUNCTION' OR obj(i).object_type='PACKAGE' THEN
comp(obj(i).object_name,obj(i).object_type);
--DBMS_OUTPUT.PUT_LINE ('objeto:'||obj(i).object_name||'->'||obj(i).object_type);
END IF;
END LOOP;
END;
PROCEDURE comp(name user_objects.object_name%TYPE, type user_objects.object_type%TYPE) IS
stm VARCHAR2(200):='ALTER '||type||' '||name||' COMPILE';
BEGIN
EXECUTE IMMEDIATE stm;
END;
Intento que compile usando el 'Native Dynamic SQL', al compilar el paquete me sale sin errores pero cuando hago:
EXECUTE compile_pkg.recompile;
Sale el error que te digo, asi como el error ORA-06512 y una advertencia, no entiendo que estoy haciendo mal, me podrías ayudar? gracias!! soy nueva en tu blog
Hola Diana P,
El código que me mostras debería funcionar correctamente. A menudo, puedes encontrarte con los errores ORA-24344 y ORA-06512 cuando intentas compilar un objeto que contiene errores; en caso contrario, el objeto debería compilar sin problemas.
Veamos un ejemplo:
1) Creo el procedimiento RECOMPILE y COMP tal cual me lo enviaste.
2) Ejecuto el procedimiento RECOMPILE para ver si tengo objetos descompilados ...
SQL> EXECUTE recompile;
ALTER PROCEDURE TEST_PROC COMPILE
ERROR:
ORA-24344: success with compilation error
ORA-06512: at "SYSTEM.COMP", line 5
ORA-06512: at "SYSTEM.RECOMPILE", line 14
ORA-06512: at line 1
3) Veamos el motivo por el cual el procedimiento TEST_PROC no se compiló ...
SQL> select name, text, type from user_errors;
NAME TEXT TYPE
------------------------------ ------------------------------------------------------------ ------------
TEST_PROC PL/SQL: ORA-00942: table or view does not exist PROCEDURE
Por lo que vemos, hay una tabla en el procedimiento que no existe.
4) Creamos la tabla faltante y volvemos a ejecutar el procedimiento RECOMPILE ...
SQL> create table TEST (id number);
Table created.
SQL> EXECUTE recompile;
PL/SQL procedure successfully completed.
Como vemos, el objeto ya quedó compilado y el procedimiento finalizó sin errores.
Saludos
Hola Rafael,
Se me ocurre que la vista materializada no se esta refrescando de la manera "fast refresh on commit" sino "complete".
Estas seguro que esta vista materializada es apta para "fast refresh"? Hay consultas que al ser 'complejas', no se les permite ser utilizadas para un refresco rápido en las vistas materializadas.
Realizar una insert es muy diferente a realizar un update/delete. Por lo tanto, la performance de estas operaciones va a cambiar respecto del tipo de acceso, entre otros factores.
Te recomiendo que realices un trace cuando hagas el update y te fijes el tipo de acceso que se esta utilizando para realizar el update en el plan de ejecución.
Por otro lado, te recomiendo leer los posts "Problemas en Vistas Materializadas - Parte 1" y Problemas en Vistas Materializadas - Parte 2" que se encuentran publicados en este Blog que te pueden ayudar a entender porqué no esta funcionando el fast refresh en tu consulta.
Saludos
Hola Leonardo, muchas gracias por compartir el conocimiento a traves de tu blog.
Estoy incursionando en el área de performance & tunning de Oracle DB, mi pregunta es la siguiiente:
¿Oracle almacena los planes de ejecución en algún lugar?
¿Existe algun histórico de planes de ejecución?
¿Si es así, puedo saber si en una consulta ha cambiado el plan de ejecución viendo algun ID de plan de ejecución?
Mil gracias por tu ayuda.
Angel Reyes.
angelreyes_2@yahoo.com
Hola Anónimo,
Oracle almacena los planes de ejecución que se encuentran en la Shared Pool (especificamente en la Library Cache) y disponemos de varias vistas que podemos consultar para ver esta información.
1) V$SQL_PLAN
Contiene los planes de ejecución de cada cursor cargado en la Library Cache.
2) V$SQL_PLAN_STATISTICS
Contiene estadísticas a nivel de línea del plan de ejecución de cada cursor cargado en la Library Cache.
3) V$SQL_PLAN_STATISTICS_ALL
Contiene estadísticas sobre la memoria utilizada a nivel de línea del plan de ejecución de cada cursor cargado en la Library Cache en las operaciones del tipo Sort o Hash-Join.
4) V$SQL_PLAN_MONITOR
Contiene estadísticas a nivel de línea del plan de ejecución de cada cursor cargado en la Library Cache y que se encuentra en modo monitoreo en V$SQL_MONITOR.
Es importante destacar que la información almacenada en las vistas es actualizada constantemente cada vez que ocurre algún cambio en la Library Cache. Adicionalmente, cada vez que se baja la base de datos, toda la información de las vistas se borra para que al subir la base de datos comiencen a cargarse nuevamente. Entonces, la información en las vistas es "histórica" mientras la base de datos que encuentre operativa.
Saludos
Hola Leonardo
Mil gracias por el blog, he obtenido alguna info importante.
Quiero preguntarte dos cosas:
1. Dónde consigo libros o capacitacion sobre optimización y/o tunnig de consultas SQl y PLSQL?
2. He estado realizando mejoras a una consulta que se demora 1 hora y crea una vista materializada. esta tiene muschas funciones en la clause SELECT y las consultas de estas funciones tenian un alto costo, al punto de bajar una de costo 1144 a 45.
El problemas es que luego de realizar el trabajo sucio (optimizar todas las funciones), resulta que al ejecutar la VM anterior y la nueva (con mis mejoras) tienen igual tiempo de ejecucion... No entiendo el porqué
Alguna idea?
Mil gracias
Saludos desde Medellin - Colombia
Hola Leonardo
Mil gracias por el blog, he obtenido alguna info importante.
Quiero preguntarte dos cosas:
1. Dónde consigo libros o capacitacion sobre optimización y/o tunnig de consultas SQl y PLSQL?
2. He estado realizando mejoras a una consulta que se demora 1 hora y crea una vista materializada. esta tiene muschas funciones en la clause SELECT y las consultas de estas funciones tenian un alto costo, al punto de bajar una de costo 1144 a 45.
El problemas es que luego de realizar el trabajo sucio (optimizar todas las funciones), resulta que al ejecutar la VM anterior y la nueva (con mis mejoras) tienen igual tiempo de ejecucion... No entiendo el porqué
Alguna idea?
Mil gracias
Saludos desde Medellin - Colombia
Hola Leonardo.
El caso que tengo se me presenta al realizar el trace a un proceso en Oracle (11g) que basicamente, contiene un cursor que realiza lecturas a un par de tablas, variando el valor del rango de manera constante. Por ej:
cursor temp is
select a.m, b.n
from a, b
where a.v= b.v
and a.v between valor1 and valor2;
donde valor1 y valor2 varian de manera constante en cada ejecucion del cursor.
El caso es que al revisar el archivo formateado mediante TKPROF, veo algo como:
***********************
SQL_ID: Un_valor
Plan Hash: 0
PLAN DE EJECUCION del cursor con el rango entre valor1 y valor2
***********************
SQL_ID: El_mismo_de_arriba
Plan Hash: Otro_valor
PLAN DE EJECUCION del cursor con el rango entre valor1 y valor2
Mi pregunta aca es la siguiente:
Por qué veo dos veces la consulta en el archivo resultado del tkprof??? Las unicas diferencias entre los dos es que el plan hash value en el primero es cero, y en el siguiente tiene un valor...luego se incrementa el rango y sigue igual...
De antemano gracias.
Felicitaciones Leonardo por la calidad de este Blog.
Mi consulta es la siguiente:
Como puedo obtener el fuente(script) de un JOB ?
He tratado con DBA_SOURCE pero el tipo de objeto('JOB') no aparece en la base de datos.
Gracias.
HOLA LEONARDO:
MUCHAS FELICIDADES POR TU BLOG!
TENGO UNA CONSULTA RESPECTO A COMO CREAR EN ORACLE 10G UN SOLO JOB PARA CORRER EL GATHERSTAT JOBS EN MULTIPLES TABLAS?
Que tal Leonardo, no se si este en el post indicado pero me podrias ayudar con informacion de oracle worflow... no lo puedo instalar me da problemas....
Muchas Gracias.
Buen dia Leonardo,
Te mando cordiales saludos. Primeramente te felicito por tu blog, esta muy bien y ayuda a muchos de los que apenas empezamos en Oracle. Como inquietud te platique que tenemos una aplicacion no propia la cual usa Oracle11. Lo que quiero saber son las consultas que mas se hacen y el tiempo que toman estas consultas. He leido sobre las estadisticas pero al parecer esto solo optimiza, lo que necesito es que me de informacion del estatus actual. O si es posible algo asi como las bitacoras de mysql donde en un archivo se ven todas las consultas que se hacen. Probe con logminer pero no me registra los select. De antemano te agradezco y quedo al pendiente de cualquier comentario. Saludos.
Hola. Felicidades por el blog!
Me gustaría que me aconsejaras algún libro sobre Performance tuning de Oracle. Mi perfil es de Dirección de proyectos pero con mucho interés en todo lo referente a tuning. Con esto te quiero decir que tampoco necesito algo de nivel muy avanzado. Si es en español, genial, pero como lo veo difícil, en inglés tampoco hay problema. Muchísimas gracias.
Hola Juan Ignacio,
En español no te recomiendo ninguno. En inglés hay muchos libros buenos. Te recomiendo algunos libros excelentes...
- Effective Oracle by Design (Autor: Thomas Kyte).
- Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions (Autor: Thomas Kyte).
- Troubleshooting Oracle Performance (Autor: Christian Antognini).
- Cost-Based Oracle Fundamentals (Autor: Jonathan Lewis).
- Optimizing Oracle Performance (Autor: Cary Millsap).
Saludos
Hola Patricia,
Si queres obtener estadísticas sobre "algunas" tablas y realizar esto mediante la ejecución de un job, entonces tenes que crear un procedimiento en donde coloques las sentencias de obtención de estadísticas de las tablas que necesites. Luego, desde el job realizás la llamada a este procedimiento.
Ejemplo:
CREATE OR REPLACE PROCEDURE scott.prc_obtener_est_tablas
IS
BEGIN
DBMS_STATS.GATHER_TABLE_STATS ('hr', 'employees');
DBMS_STATS.GATHER_TABLE_STATS ('hr', 'dept');
END;
/
EXEC DBMS_JOB.SUBMIT(:v_JobNo, 'scott.prc_obtener_est_tablas;');
Saludos
Hola Ivan,
Para realizar backups deja de utilizar DataPump y comienza a utilizar RMAN como herramienta de backup.
Con RMAN uno de los beneficios que tienes es que puedes realizar backups completos e incrementales. Por ejemplo, puedes armar una estrategia de backup en donde todos los domingos realizas backup completos de tu base de datos y durante la semana incrementales. De esta manera, ahorras mucho tiempo para realizar los backups; y sobre todo, utilizas menos espacio en disco.
Saludos
Hola Ismael,
Te puedo ayudar en la instalación de oracle worflow. Por favor, decime qué error estas teniendo al instalarlo.
Saludos
Hola Anónimo,
El fuente de un job lo podes ver en el campo WHAT de la tabla DBA_JOBS.
Saludos
Hola 1,
Por favor, ejecuta la siguiente consulta y muestrame el resultado de la misma.
select sql_id, plan_hash_value, child_number, executions, parse_calls, buffer_gets, is_bind_sensitive, is_bind_aware
from v$sql
where sql_id = '......';
Saludos
Hola Dorian,
1) En inglés hay muchos libros buenos. Te recomiendo algunos libros excelentes...
- Effective Oracle by Design (Autor: Thomas Kyte).
- Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions (Autor: Thomas Kyte).
- Troubleshooting Oracle Performance (Autor: Christian Antognini).
- Cost-Based Oracle Fundamentals (Autor: Jonathan Lewis).
- Optimizing Oracle Performance (Autor: Cary Millsap).
2) Para ayudarte mejor, necesitaría que me comentes qué clase de código se encuentra dentro de las funciones que mencionás.
Te recomiendo que elimines las funciones que tenes en el SELECT ya que éstas generan recursividad decrementando en gran medida la performance de la consulta.
Si dentro de la función tenes una consulta, entonces eliminá la función y colocá la consulta directamente en la consulta padre que llama a la función.
Saludos
Un gran Blog
Hola Leonardo,
tengo un problema que no consigo saber como realizar de la mejor forma...
Tengo una tabla T1 particionada por rango y a su vez con 16 subparticiones hash (S1..S16). Las particiones son por mes año... P_YYYYMM
Tengo otra tabla identica T1_off en la cual quiero ir llevando mensualmente las particiones mas viejas de la tabla T1.
Mi duda es: ¿como puedo mover una particion especifica de la tabla T1 a la tabla T1_off en oracle 11g?
Muchas gracias...
Hola Charlie,
Porqué queres llevar las particiones de una tabla a otra? Cuál es la necesidad por la cual necesitas realizar esto?
Las tablas se encuentran en la misma base de datos? En el mismo esquema?
Saludos
Hola Anónimo,
Todas las consultas que se ejecutan se almacenan en un área de memoria llamada "Library Cache". A medida que esta área se va llenando, las consultas que fueron ejecutadas con menos frecuencia, son eliminadas de esta área para ser espacio a nuevas consultas.
Para ver las consultas que se encuentran en esta área, podés consultas a varias vistas del diccionario de datos. Algunas son V$SQL, V$SQLAREA, V$SQLTEXT.
Por ejemplo, unas consultas que podés ejecutar son:
select sesion.sid,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null;
select sesion.sid,
sql_text
from v$sqltext sqltext, v$session sesion
where sesion.sql_hash_value = sqltext.hash_value
and sesion.sql_address = sqltext.address
and sesion.username is not null
order by sqltext.piece;
Saludos
Hola Leonardo lo que sucede es que necesito ingresar 10 millones de registros a una tabla, la tabla solo tiene 4 campos,estos campos son generados aleatoriamente e ingresados mediante un procedimiento, he dejado el pc mas de 8 horas en este proceso y no termina, el sql plus se bloquea y pues me toca cerrarlo existe alguna manera de poder ingresar estos valores de una forma mas rapida... muchas gracias este es el procedimiento q realiza la insercion....
procedure llenado_ingresos2 is
v_id_a_buscar number;
v_identificacion number;
begin
for v_numero in 1 .. 10000000 loop
v_id_a_buscar:=aleatorio(1,100000);
select identificacion into v_identificacion from usuarios where id=v_id_a_buscar;
insert into ingresos (USUARIO,FECHA,HORA,ID_SESION)
VALUES (v_identificacion,sysdate-aleatorio(1,1820),sysdate + numtodsinterval( aleatorio(1,86400) , 'SECOND'),v_numero);
end loop;
end llenado_ingresos2;
y esta es la funcion que calcula el aleatorio.
function aleatorio(v_minimo number,v_maximo number) return number is
v_aleatorio number default 0;
begin
v_aleatorio:=v_minimo+ABS(MOD(dbms_random.random,(v_maximo-v_minimo)+1));
return v_aleatorio;
end aleatorio;
la tabla usuarios contiene 100.000 registros.. espero me puedas ayudar muchas gracias...
Hola desdelau,
Hay varias cosas que se pueden modificar en el procedimiento que enviaste para mejorar la performance.
Te hago algunas preguntas para poder ayudarte mejor:
1) El campo ID de la tabla USUARIOS tiene un índice único creado? Podrías enviarme el plan de ejecución de esa consulta?
2) No entiendo cual es el propósito de la función ALEATORIO ya que por lo que veo la utilizas en todo; para el cálculo de la fecha, hora, etc.
El llamado a esa función genera mucha recursividad por lo cual quiero entender bien el motivo por el cual la utilizas tanto en el procedimiento.
Saludos
Hola Leonardo no se si sea permitido pero adjunte lo necesario para que entiendas mejor mi problema... espero me puedas ayudar y muchas gracias por tu respuesta http://www.mediafire.com/?z718prue5xq7wpu
Hola desdelau,
Por lo que veo en los archivos que me adjuntaste, la tabla USUARIOS no tiene índice en el campo ID.
Comencemos por crear un índice único compuesto por los campos ID e IDENTIFICACION...
CREATE UNIQUE INDEX USUARIOS_IDEN_ID_UQ ON USUARIOS(ID, IDENTIFICACION);
Luego, te pido que obtengas estadísticas sobre la tabla USUARIOS con sus respectivos índices...
EXEC DBMS_STATS.GATHER_TABLE_STATS(UPPER('colocar_el_esquema_de_la_tabla'), 'USUARIOS'), cascade => true, estimate_percent => dbms_stats.auto_sample_size, no_invalidate => false);
Por último, reescribí el código que me enviaste para realizar un Bulk Insert cada 10,000 utilizando colecciones en memoria.
Volvé a ejecutar el proceso y comentame si logramos una mejoría en el tiempo de respuesta de la ejecución del mismo.
Nota: El código que reescribí no pude compilarlo. A lo mejor compila con error y tendremos que revisarlo.
procedure llenado_ingresos2
is
v_id_a_buscar number;
v_identificacion number;
type t_array_number is table of number index by binary_integer;
type t_array_date is table of date index by binary_integer;
t_usuario t_array_number;
t_fecha t_array_date;
t_hora t_array_date;
t_id_session t_array_number;
begin
for v_numero in 1 .. 10000000 loop
v_id_a_buscar := aleatorio(1,100000);
select identificacion into v_identificacion from usuarios where id = v_id_a_buscar;
t_usuario(v_numero) := v_identificacion;
t_fecha(v_numero) := sysdate-aleatorio(1,1820);
t_hora(v_numero) := sysdate+numtodsinterval(aleatorio(1,86400),'SECOND');
t_id_session(v_numero) := v_numero;
if (t_usuario.count = 10000) then
forall x in t_usuario.first .. t_usuario.last
insert into ingresos (USUARIO, FECHA, HORA, ID_SESION)
values (t_usuario(x), t_fecha(x), t_hora(x), t_id_session(x));
commit;
t_usuario.delete;
t_fecha.delete;
t_hora.delete;
t_id_session.delete;
end if;
end loop;
forall x in t_usuario.first .. t_usuario.last
insert into ingresos (USUARIO, FECHA, HORA, ID_SESION)
values (t_usuario(x), t_fecha(x), t_hora(x), t_id_session(x));
commit;
t_usuario.delete;
t_fecha.delete;
t_hora.delete;
t_id_session.delete;
end llenado_ingresos2;
Saludos
Hola leonardo, la tabla usuarios tiene un indice, CREATE UNIQUE INDEX USUARIOS_PK ON USUARIOS
(IDENTIFICACION) pero solo toma la identificación osea q debo eliminarlo y poner el indice q tu me dices ?
Hola desdelau,
Tenemos que crear un índice que contenga el campo ID debido a que en la consulta a la tabla USUARIOS se esta accediendo por este campo y si no tenes índice creado vamos a estar realizando un FULL SCAN sobre la tabla 10.000.000 de veces.
Podes optar por dropear el índice que ya habias creado o crear el siguiente índice:
CREATE UNIQUE INDEX USUARIOS_IDEN_ID_UQ ON USUARIOS(IDENTIFICACION, ID);
EXEC DBMS_STATS.GATHER_TABLE_STATS(UPPER('colocar_el_esquema_de_la_tabla'), 'USUARIOS'), cascade => true, estimate_percent => dbms_stats.auto_sample_size, no_invalidate => false);
Nota: Fijate que coloqué en la creación del índice el campo IDENTIFICACIONES en la primer posición ya que supongo que al ser un campo PK lo utilizas mucho para acceder a la tabla.
Saludos
Hola leonardo ejecute lo siguiente y me aparece error en la parte donde dice cascade....
EXEC DBMS_STATS.GATHER_TABLE_STATS(UPPER('daniel'),'USUARIOS'),cascade => true, estimate_percent => dbms_stats.auto_sample_size, no_invalidate => false);
Entonces ejecute esto no mas..
EXEC DBMS_STATS.GATHER_TABLE_STATS(UPPER('daniel'),'USUARIOS');
y sale procedimiento ejecutado correctamente ahora no se como hacer para ejecutar lo otro q me dices... ya cree el indice q me dijiste.. gracias y de verdad q pena la molestia...
Hola desdelau,
La sentencia que te envié te dió error porque había un paréntesis de más. Ejecuta nuevamente esta sentencia y me avisas si te anduvo bien...
EXEC DBMS_STATS.GATHER_TABLE_STATS(UPPER('daniel'),'USUARIOS',cascade => true, estimate_percent => dbms_stats.auto_sample_size, no_invalidate => false);
Si esta sentencia no dió error, entonces compila el procedimiento que te había enviado (el que tiene las colecciones de memoria) y luego ejecutalo.
Saludos
Hola leonardo ya puse a ejecutar el procedimiento que me enviaste, muchas gracias y pues sera esperar a ver que sucede.Estaré informando y muchas gracias por tu tiempo...
Hola leonardo lastimosamente apagaron mi pc, te cuento que durante las 8 horas que estuvo ejecutando el procedimiento alcanzo a ingresar 4260000 registros. No se que opinas, mi maquina no es muy potente que digamos..¿que hago lo dejo encendido hasta que termine ?... muchas gracias
Hola desdelau,
Por favor, abre una sesion de SQL*Plus, ejecuta las siguientes sentencias y enviamos el resultado:
select /*+ gather_plan_statistics */ identificacion from usuarios where id = v_id_a_buscar;
Nota: reemplazar v_id_a_buscar por un valor que retorne datos.
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
Saludos
Bueno leonardo aqui los resultados...
La consulta :
select /*+ gather_plan_statistics */ identificacion from usuarios where id = 1;
Dio como resultado lo siguiente :
IDENTIFICACION
--------------
389190
La segunda consulta :
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
Dio como resultado lo siguiente:
http://www.subirimagenes.com/privadas-segundaimagenaenviar-1760307.html
Hola desdelau,
No logro ver la imagen que enviaste.
Saludos
Hola leonardo que pena mira aca esta la imagen
http://www.mediafire.com/i/?8imypm2dmbo8y9p
Hola desdelau,
El plan de ejecución que me enviaste no es sobre la consulta de la tabla USUARIOS.
Pregunta: Ejecutaste las dos sentencias que te envié una detras de la otra?
Podes volver a hacerlo?
Saludos
Hola leonardo mira aca esta la ejecucion de las dos sentencias... que pena y muchas gracias...
Hola desdelau,
No adjuntaste ningún archivo en tu último post.
Saludos
Hola Leonardo, que pena no se que paso ando un poco loco jejeje mira aca esta http://www.mediafire.com/i/?p6b3si9842529bn gracias...
Saludos :)
Hola desdelau,
Ejecuta las siguientes sentencias:
DROP INDEX USUARIOS_IDEN_ID_UQ;
CREATE UNIQUE INDEX USUARIOS_ID_IDEN_UQ ON USUARIOS(ID, IDENTIFICACION);
EXEC DBMS_STATS.GATHER_TABLE_STATS(UPPER('daniel'),'USUARIOS',cascade => true, estimate_percent => dbms_stats.auto_sample_size, no_invalidate => false);
Abre una sesión de SQL*Plus, ejecuta las siguientes sentencias y enviame el resultado:
select /*+ gather_plan_statistics */ identificacion from usuarios where id = 1;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
Saludos
pyHola leonardo, mira aca esta el reslultado de la ejecución de las dos sentencias...
http://www.mediafire.com/?r2wydg8y2nfy86a
Gracias...
Hola desdelau,
Por favor, ejecuta el proceso y me comentas cómo te fue.
Saludos
Hola Leonardo, una pregunta : elimino los datos de la tabla ingresos para volver a ejecutar el procedimiento de llenado ? gracias...
Hola desdelau,
Ejecuta el siguiente comando para borrar todos los registros de la tabla INGRESOS y luego vuelve a ejecutar el proceso.
TRUNCATE TABLE ingresos;
Saludos
Hola leonardo me aparece este error...
TRUNCATE TABLE ingresos
*
ERROR en línea 1:
ORA-02266: claves únicas/primarias en la tabla referidas por claves ajenas
activadas.
porque no eliminamos con delete ?
Hola desdelau,
A diferencia del comando DELETE, el TRUNCATE limpia la HWM (High Water Mark) de la tabla.
Si queres podes ejecutar el comando DELETE en este caso para evitar el error que te esta mostrando.
Saludos
Hola leonardo por motivos de trabajo no he podido podido acceder a windows a dejar que siga ejecutandose el procedimiento.. a penas lo haga te cuento...
Muchas gracias por tu ayuda..
Publicar un comentario