Mostrando entradas con la etiqueta Oracle. Mostrar todas las entradas
Mostrando entradas con la etiqueta Oracle. Mostrar todas las entradas

martes, 25 de enero de 2011

Sp_who en Oracle

En el tiempo que llevo trabajando con SQL Server creo que el comando que mas veces ejecuté es el sp_who o su versión extendida sp_who2. Este comando cuando se está administrando una base es muy comodo por su sencillez y nos da un buen resumen de que está ocurriendo en el servidor. Claramente cuando hay alguna situación fuera de lo normal esto es solo la puerta de entrada para despues controlar las DMVs, los contadores del sistema operativo, etc, pero siempre es un buen comienzo. Muchas personas les pasa que acostumbradas a ambientes SQL Server se sientan frente a un sistema Oracle y buscan algo equivalente y no lo encuentran, así que veamos como sería una consulta equivalente en Oracle.
No contamos con un stored procedure prearmado que nos brinde la información, pero al igual que en SQL Server, esta información se nutre de las sesiones, así que consultaremos la V_$SESSION al igual que el sp_who original.
En este caso para que les sea mas comodo acomodé los campos para que queden igual que en la versión original.

SELECT sid, status, Username, terminal, blocking_session_status, schemaname, a.name Command_Action, logon_time, program
FROM SYS.V_$SESSION s
INNER JOIN AUDIT_ACTIONS a ON s.command=a.action;

Como podrán ver aparte de consultar la vista de sesiones hice un join con la tabla AUDIT_ACTIONS que es la que contiene las descripciones de cada evento para que sepamos que está haciendo la session.
Lo que recomiendo es guardar esta consulta en un script a mano y poco a poco irse internalizando mas con la SYS.V_$SESSION visto que tiene mucho para darnos. Quien no esté acostumbrado se va a sorprender al encontrar muchos mas campos que en su equivalente de SQL Server.
Pueden encontrar mas detalles en:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2088.htm

Por último si lo que se desea es hacer algo mas semejante al sp_who2 y no tienen ganas de internalizarse en los demas campos, puede hacerse una función que nos retorne los resultados de la consulta. Esta función sería:

-- Creacion de funcion que retorna como resultado una tabla
CREATE OR REPLACE function sp_who2 return sp_who2_tab PIPELINED
IS
CURSOR cur0
IS
SELECT sid, status, Username, terminal, blocking_session_status, schemaname, a.name Command_Action, logon_time, program
FROM SYS.V_$SESSION s
INNER JOIN AUDIT_ACTIONS a ON s.command=a.action;

out_rec sp_who2_rec := sp_who2_rec(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
BEGIN
OPEN cur0;
LOOP
FETCH cur0 INTO out_rec.sid, out_rec.status, out_rec.Username,
out_rec.terminal, out_rec.blocking_session_status, out_rec.schemaname, out_rec.Command_Action, out_rec.logon_time, out_rec.program;
EXIT WHEN cur0%NOTFOUND;
PIPE ROW (out_rec);
END LOOP;
CLOSE cur0;
RETURN;

END;

Y como sucede con las funciones que retornan tablas en Oracle, la forma de utilizarla sería:
SELECT * FROM TABLE(sp_who2);
Claramente a esto hay que sumarle la creación de los objetos necesarios (record y table).
Espero que les haya servido!

lunes, 17 de enero de 2011

Drop and Create

Muchas veces al armar los scripts figuran creación de objetos, y obviamente es deseable que nuestro script no falle por mas que dicho objeto ya se encuentre creado. En el caso de los stored procedures, en Oracle, tenemos la opción de CREATE OR REPLACE, pero cuando nos manejamos con tablas esta opción, lamentablemente, no existe ni en Oracle ni en SQL Server.
Para eliminar y crear una tabla desde cero en SQL Server es bastante conocido el método (de echo en la versión 2008 en adelante ya lo tenemos a un click del managment). El código es:

-- Prueba 1: Controlo si existe la tabla, si es necesario la elimino y la vuelvo a crear
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.prueba_creacion') AND type in (N'U'))
DROP TABLE dbo.prueba_creacion

CREATE TABLE prueba_creacion(clave INT);

De esta forma controlamos si la tabla existe, y en caso afirmativo la eliminamos. Luego podemos proceder a crearla sin problema.
Otra es la historia cuando la tabla es una tabla temporaria. Lo intuitivo sería ahcer lo mismo con el nombre de la tabla temporaria, pero esto arrojaría error. ¿porqué? porque la tabla temporaria se encuentra alojada en la tempdb. El código para poder realizar lo mismo con tablas temporarias sería:

-- Prueba 2: Controlo si existe la tabla temporaria, si es necesario la elimino y la vuelvo a crear
IF NOT OBJECT_ID('tempdb.dbo.#prueba_creacion_temp') IS NULL
DROP TABLE dbo.#prueba_creacion_temp

CREATE TABLE #prueba_creacion_temp(clave INT);

Pueden ver que se código no importa cuantas veces se ejecute nunca arrojará error.
Por último es interesante resaltar que en oracle no podemos realizar ninguno de estos controles debido a que el parser arrojará error la primera vez al hacer un DROP TABLE de una tabla que aún no existe. Por este motivo la forma de realizar lo mismo en Oracle sería:

-- Prueba 3: Controlo si existe la tabla en oracle, si es necesario la elimino y la vuelvo a crear

BEGIN
EXECUTE IMMEDIATE 'DROP TABLE prueba_creacion';
EXCEPTION WHEN OTHERS THEN NULL;
END;

CREATE TABLE prueba_creacion(clave INT);

De esta forma se ejecutará siempre el DROP TABLE y arrojará error de sintaxis cuando no exista la tabla, pero el error será en el sql dinámico y no en todo el script, y estará capturado por el WHEN OTHERS, haciendo que sea transparente para el resto de la ejecución.

jueves, 6 de enero de 2011

SQL Server Denali - Sequence

Estuve probando el SQL 11 y una de las cosas que mas contento me puso es la incorporación de las SECUENCIAS tal como lo maneja Oracle. ¿Qué es esto? básicamente es un objeto en el motor que podemos crear y nos arroja números de forma ordenada según el criterio que hayamos definido. El principal objetivo es no depender de claves de tipo IDENTITY.
En este artículo hablaré de 3 cosas:
1) Como se utilizan las secuencias (sequence)
2) Comparar con Oracle el manejo de secuencias
3) Stress test comparando secuencias con campos identity

Un ejemplo de como utilizar este nuevo objeto sería el siguiente:

USE SQLDENA
GO

-- La forma tradicional de hacer un "autonumerico" es
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PruebaSecuencia]') AND type in (N'U'))
DROP TABLE [dbo].PruebaSecuencia
GO

CREATE TABLE PruebaSecuencia(
id INT PRIMARY KEY IDENTITY,
dato1 VARCHAR(100)
)
GO

-- Pero ahora con las secuencias lo podemos hacer de la siguiente forma
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PruebaSecuencia]') AND type in (N'U'))
DROP TABLE [dbo].PruebaSecuencia
GO

-- Creamos la tabla que SIN campo identity
CREATE TABLE PruebaSecuencia(
id INT PRIMARY KEY,
dato1 VARCHAR(100)
)
GO

-- Creo la secuencia, se puede definir como se incrementa, etc
-- Como se puede ver son muy flexibles permitiendo configurar como se van a comportar
CREATE SEQUENCE dbo.NuevaSec
AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999
CACHE 20
;
GO

-- Obtengo unos valores de ejemplo
SELECT NEXT VALUE FOR dbo.NuevaSec
GO 4

-- Reinicio la secuencia para el ejemplo
ALTER SEQUENCE dbo.NuevaSec RESTART
GO

-- Ejemplo de como la usaria
INSERT INTO PruebaSecuencia
select next value for dbo.NuevaSec as nro, 'Valor1' as dato1

-- O tambien...
INSERT INTO PruebaSecuencia (id, dato1)
VALUES (next value for dbo.NuevaSec, 'Valor1')

-- Que valor ingrese ultimo?
select current_value from sys.sequences where name = 'NuevaSec'

-- Esto me permite pasarle el valor a otra rutina que tenga que insertar en base a lo ingresado


-- La forma de realizar esto mismo en ORACLE hubiera sido:
/* PRUEBA ORACLE */
CREATE TABLE PruebaSecuencia(
id INT PRIMARY KEY,
dato1 VARCHAR2(100)
)

CREATE SEQUENCE NuevaSec
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999
CACHE 20;

-- Obtengo unos valores de ejemplo
SELECT NuevaSec.NEXTVAL FROM DUAL

-- Ejemplo de como la usaria
INSERT INTO PruebaSecuencia
select NuevaSec.NEXTVAL as nro, 'Valor1' as dato1 FROM DUAL

-- O tambien...
INSERT INTO PruebaSecuencia (id, dato1)
VALUES (NuevaSec.NEXTVAL, 'Valor1')

-- Que valor ingrese ultimo?
select NuevaSec.CURRVAL FROM DUAL

/* FIN PRUEBA ORACLE */

Comparando ambas formas creo que por suerte mantuvieron una sintaxis muy similar en lo que corresponde a la creación del objeto, y como oracle no tiene su hermoso CREATE OR REPLACE para las secuencias entonces es totalmente consistente. En lo que respecta a su uso diario, me parece mucho mas comoda y agradable la sintaxis de Oracle, principalmente la forma de ver el valor actual que es algo que no entiendo como no incorporaron. Por otra parte en Oracle es muy incomodo reiniciar una secuencia (es necesario restarle el valor actual) , mientras que SQL ha simplificado esta tarea para evitarnos dolores de cabeza.

Volviendo al SQL Server, realice un stress test con ambas tablas, una con identity y otra sin, utilizando las siguientes instrucciones
-- test1
INSERT INTO PruebaSecuencia (dato1)
VALUES ('Valor1')
GO 50000

-- test2
INSERT INTO PruebaSecuencia (id, dato1)
VALUES (next value for dbo.NuevaSec, 'Valor1')
GO 50000

Los resultados fueron parejos tardando un poco menos el ejemplo con la secuencia pero no de forma significativa. Es importante notar que la prueba no la realicé en un gran servidor sino en una pc virtual, y según varios test esta diferencia suele ser mayor.