Diccionario de Oracle (III) - USER_TABLES - USER_IND_COLUMNS - USER_TAB_COLUMNS

Para ver las tablas del diccionario que podemos consultar para ver información de nuestras tablas, realizamos esta consulta:

SELECT * FROM DICT WHERE TABLE_NAME LIKE '%TABLES%'

• USER_TABLES Description of the user's own relational tables

Y vemos lo que tenemos en USER_TABLES

SELECT * FROM USER_TABLES

Entre otros campos, podemos ver:
TABLE_NAME : Nombre de la tabla
NUM_ROWS : Número de filas de la tabla
LAST_ANALYZED : Fecha en que se analizo por última vez.


Si queremos ver información de las columnas

SELECT * FROM DICT WHERE TABLE_NAME LIKE '%COLUMN%'

• USER_IND_COLUMNS COLUMNs comprising user's INDEXes and INDEXes on user's TABLES
• USER_TAB_COLUMNS Columns of user's tables, views and clusters

Y miramos las tablas que nos interesan:

SELECT * FROM USER_IND_COLUMNS

Entre otros campos, tenemos:
INDEX_NAME: Indice
TABLE_NAME: Tabla sobre la que está el índice
COLUMN_NAME: Columna indexada


SELECT * FROM USER_TAB_COLUMNS

Entre otros campos tenemos:
TABLE_NAME: Nombre de la tabla o vista
COLUMN_NAME: Nombre de la columna
DATA_TYPE: Tipo de datos de la columna
DATA_LENGTH: Longitud definida en la columna en bytes

Diccionario de Oracle (II) - USER_INDEXES y Indices basados en función

SELECT * FROM DICT WHERE TABLE_NAME LIKE '%INDEX%'



Entre otros, vemos:

• USER_INDEXES Description of the user's own indexes
• ALL_INDEXES Descriptions of indexes on tables accessible to the user

SELECT * FROM USER_INDEXES

Podemos ver información sobre los índices, entre otros:
INDEX_NAME: nombre del indice
TABLE_NAME: Tabla indexada
STATUS: Si es valido o no
LAST_ANALYZED: Fecha en que se analizo por última vez.
FUNCIDX_STATUS: ENABLED o DISABLED. Indica si la funcion en que se basa un índice esta correctamente compilada o no.


Si tenemos un índice basado en función, eso es,
un índice que en su definición contenga la llamada a una función, por ej:

CREATE INDEX indx_mes ON mi_tabla(
TO_CHAR(fecha,'MM'));

CREATE INDEX indx_nif ON mi_tabla (
PQT_UTILIDADES.FNT_NORMALIZA_NIF(nif));

En el primer caso no vamos a tener ningún problema ya que es una función de Oracle (To_Char), pero en el segundo caso, es una función definida por nosotros en un paquete PL/SQL que podría quedar descompilado.

Eso lo podríamos ver con esta Query:

SELECT * FROM USER_INDEXES
WHERE INDEX_TYPE ='FUNCTION-BASED NORMAL'
AND FUNCIDX_STATUS ='DISABLED'

Si esto pasa, el índice se tiene que volver a crear, o bien con el código CREATE INDEX o bien con la sentencia REBUILD:

ALTER INDEX indx_nif REBUILD;

Tuvimos un problema con un índice basado en función.

Era una función que estaba dentro de un paquete con muchas otras funciones,
Siempre que modificábamos cualquier función (aunque no fuera la que utilizaba el índice), este quedaba descompilado.

Por eso es recomendable tener la función a parte.

Si modificas la función, obviamente tendrás que reconstruir el índice,
pero no te pasará que modificando cualquier función del paquete dónde se encuentra la utilizada por el índice, este deje de funcionar.

Diccionario de Oracle (I) - USER_SOURCE

Podemos ver el diccionario de Oracle con estas consultas

SELECT * FROM SYS.DICTIONARY SELECT * FROM DICT

Con una SELECT en la vista del diccionario de Oracle
podemos ver los objetos de Oracle que podemos consultar.

Unos ejemplos de los que más he utilizado:

Por ejemplo para buscar el código de las funciones o procedimientos almacenados
SELECT * FROM DICT WHERE TABLE_NAME LIKE '%SOURCE%'

USER_SOURCE: Source of stored objects accessible to the USER

SELECT * FROM USER_SOURCE

Las columnas que podemos ver son:
NAME: Nombre del objeto
TYPE: Tipo del objeto (FUNCTION,PROCEDURE,PACKAGE,...)
LINE: Numero de línea dentro del código
TEXT: Código

Ejemplo práctico.

• Creamos una función:

CREATE OR REPLACE FUNCTION FNT_AHORA RETURN DATE IS FECHA DATE; BEGIN SELECT SYSDATE INTO FECHA FROM DUAL; RETURN FECHA; END;

• Buscamos en la tabla USER_SOURCE

SELECT * FROM USER_SOURCE WHERE NAME='FNT_AHORA'


En esta tabla podemos buscar cualquier línea de código.

Es muy útil cuando tenemos decenas o centenares de funciones y procedimientos, en distintos paquetes, etc..

Nos permite localizar de forma rápida todas las líneas de código dónde aparece un texto en concreto (para localizar dónde utilizamos llamadas a cierta función, o dónde se nombra una tabla o una columna en concreto,...)

sys_context - Obtener información de la sesión

sys_context( namespace, parameter, [ length ] )

Surgió la necesidad de obtener el usuario del S.O. con el que estaba
logeado el usuario que había iniciado la sesión de Oracle, y lo obtuvimos así:

Para obtener el usuario del S.O:

SELECT sys_context('USERENV', 'OS_USER') FROM dual

Otros ejemplos:

Para obtener el nombre de la base de datos:
SELECT sys_context('USERENV', 'DB_NAME') FROM dual

Para obtener el eschema actual de la base de datos:
SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM dual


Existen muchos otros parámetros.

Dejo este enlace
http://www.techonthenet.com/oracle/functions/sys_context.php

Los parametros posibles para el namespace 'USERENV' son estos:

• AUDITED_CURSORID: Returns the cursor ID of the SQL that triggered the audit
• AUTHENTICATION_DATA: Authentication data
• AUTHENTICATION_TYPE: Describes how the user was authenticated. Can be one of the following values: Database, OS, Network, or Proxy
• BG_JOB_ID: If the session was established by an Oracle background process, this parameter will return the Job ID. Otherwise, it will return NULL.
• CLIENT_IDENTIFIER: Returns the client identifier (global context)
• CLIENT_INFO: User session information
• CURRENT_SCHEMA: Returns the default schema used in the current schema
• CURRENT_SCHEMAID: Returns the identifier of the default schema used in the current schema
• CURRENT_SQL: Returns the SQL that triggered the audit event
• CURRENT_USER: Name of the current user
• CURRENT_USERID: Userid of the current user
• DB_DOMAIN: Domain of the database from the DB_DOMAIN initialization parameter
• DB_NAME: Name of the database from the DB_NAME initialization parameter
• ENTRYID: Available auditing entry identifier
• EXTERNAL_NAME: External of the database user
• FG_JOB_ID: If the session was established by a client foreground process, this parameter will return the Job ID. Otherwise, it will return NULL.
• GLOBAL_CONTEXT_MEMORY: The number used in the System Global Area by the globally accessed context
• HOST:nName of the host machine from which the client has connected
• INSTANCE: The identifier number of the current instance
• IP_ADDRESS: IP address of the machine from which the client has connected
• ISDBA: Returns TRUE if the user has DBA privileges. Otherwise, it will return FALSE.
• LANG: The ISO abbreviate for the language
• LANGUAGE: The language, territory, and character of the session. In the following format: language_territory.characterset
• NETWORK_PROTOCOL: Network protocol used
• NLS_CALENDAR: The calendar of the current session
• NLS_CURRENCY: The currency of the current session
• NLS_DATE_FORMAT: The date format for the current session
• NLS_DATE_LANGUAGE: The language used for dates
• NLS_SORT: BINARY or the linguistic sort basis
• NLS_TERRITORY: The territory of the current session
• OS_USER: The OS username for the user logged in
• PROXY_USER: The name of the user who opened the current session on behalf of SESSION_USER
• PROXY_USERID: The identifier of the user who opened the current session on behalf of SESSION_USER
• SESSION_USER: The database user name of the user logged in
• SESSION_USERID: The database identifier of the user logged in
• SESSIONID: The identifier of the auditing session
• TERMINAL: The OS identifier of the current session

Diferencia entre dos fechas en formato H MIN SEG

Ejemplo práctico:

Creamos una tabla con dos fechas
CREATE TABLE TABLA1 (FECHA_INICIO DATE, FECHA_FIN DATE)

Insertamos dos registros de ejemplo:
Este con la misma fecha
INSERT INTO TABLA1 VALUES (SYSDATE, SYSDATE)

Esta con la fecha actual, y una fecha aleatoria superior
INSERT INTO TABLA1 VALUES (SYSDATE, SYSDATE+0.3568)

Con esta query obtenemos el tiempo entre Fecha_inicio y Fecha_fin
SELECT
FECHA_INICIO, FECHA_FIN, floor(((FECHA_FIN-FECHA_INICIO)*24*60*60)/3600) || ' H ' || floor((((FECHA_FIN-FECHA_INICIO)*24*60*60) - floor(((FECHA_FIN-FECHA_INICIO)*24*60*60)/3600)*3600)/60) || ' MIN ' || round((((FECHA_FIN-FECHA_INICIO)*24*60*60) - floor(((FECHA_FIN-FECHA_INICIO)*24*60*60)/3600)*3600 - (floor((((FECHA_FIN-FECHA_INICIO)*24*60*60) - floor(((FECHA_FIN-FECHA_INICIO)*24*60*60)/3600)*3600)/60)*60) )) || ' SEG ' TIEMPO
FROM TABLA1

Control de sesiones activas y de querys que tardan mucho

Si tenemos permisos sobre las tablas adecuadas, podremos ejecutar esta query para ver las sesiones activas en la Base de Datos

Con esta query podemos ver los usuarios conectados, la sentencia SQL que están ejecutando, la hora en que han hecho login, el programa usado,...

SELECT
SQL_TEXT, --Sentencia SQL
USERNAME, --Nombre usuario
SCHEMANAME, --Nombre Eschema
OSUSER, --Usuario del SO
LOGON_TIME, --Hora de Login de esta sesión
STATUS, --INACTIVE o ACTIVE
MACHINE, --La maquina en la que se abre la sesión
PROGRAM --Programa usado, SQLTools.exe, TOAD.exe
FROM
SYS.V_$SESSION S, SYS.V_$SQLAREA A WHERE A.HASH_VALUE=S.SQL_HASH_VALUE
ORDER BY LOGON_TIME DESC;

Y con la siguiente query podemos ver la previsión del tiempo restante para las sentencias
que llevan cierto tiempo en ejecución:

Veremos el usuario, la sentencia, el % que lleva completado, el tiempo que lleva y la hora prevista de fin.

SELECT S.OSUSER, --Usuario de SO
S.USERNAME, --Usuario
A.SQL_TEXT, --Query
SL.OPNAME, --Operación que esta realizando (Table Scan, Sort/Merge,..)
trunc((SL.SOFAR*100)/SL.TOTALWORK,2) AS EVOL, --Evolución en %
SL.SOFAR, --Tiempo que lleva
SL.TOTALWORK, --Tiempo total
To_Char(SYSDATE, 'dd/mm/yyyy HH24:MI:SS') AS T_NOW, --Ahora. Día y hora actual.
To_Char(SYSDATE+((((1/24)/60)/60)*SL.TIME_REMAINING), 'dd/mm/yyyy HH24:MI:SS') AS T_STOP --Día y hora cuando acabará.
FROM
V$SESSION_LONGOPS SL, V$SESSION S ,V$SQLAREA A
WHERE
SL.SOFAR <> SL.TOTALWORK AND
SL.SID = S.SID AND
A.HASH_VALUE=S.SQL_HASH_VALUE;

Hay que tener en cuenta que según de que sentencia se trate, puede tener varias operaciones, con lo cual veremos que va del 0 al 100% varias veces. Es decir, no podemos asegurar de immediato lo que va a tardar, pero si que nos puede dar una idea aproximada.
Muchas veces nos basta con saber si se trata de una prevision de segundos, minutos, horas...

Analyze table y DBMS_STATS.GATHER_TABLE_STATS

Oracle dispone de herramientas que nos permiten mejorar el rendimiento de la base de datos.

Las estadísticas que Oracle tiene almacenadas tienen un impacto real en el "como" Oracle va a resolver nuestras consultas.

Cuando tenemos tablas y/o índices de un tamaño considerable, podemos notar un incremento en el tiempo de ejecución de las Querys, si las estadísticas no están bien computadas.

No entraré en detalles técnicos porque realmente no los conozco todos, pero siendo práctico, en mi día a día, he notado mejoras considerables cuando las tablas y los índices han tenido las estadísticas bien computadas.

Lo primero que aprendí en el tema de analizar las tablas fue el comando ANALYZE:

ANALYZE TABLE mi_tabla ESTIMATE STATISTICS SAMPLE 5 PERCENT;

Con este comando analizo la tabla 'mi_tabla' estimando las estadísticas con un 5% de datos. Normalmente con está estimación ya es suficiente (un 5% de una tabla de millones de registros puede dar una idea bastante exacta de como están los datos)

Muchas veces puedes ver un cambio en el plan de ejecución de Oracle antes y después de analizar las tablas implicadas en una consulta. Sobretodo si son tablas grandes o si han crecido respecto la última vez que se computaron estadísticas.

Ahora, con las nuevas versiones de Oracle utilizo el DBMS_STATS.GATHER_TABLE_STATS un Procedure que permite analizar las estadísticas de las tablas, columnas e índices de manera más óptima.

Este proceso tiene más parámetros de los que voy a comentar, pero como siempre, me baso en lo que yo utilizo o he utilizado (El resto se puede consultar en la página de Oracle)


SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'usuario'
,TabName => 'mi_tabla'
,Estimate_Percent => 5
,Method_Opt => 'FOR ALL COLUMNS SIZE AUTO'
,Degree => 4
,Cascade => TRUE
);

• OwnName: Esquema dónde se encuentra la tabla a analizar.

• TabName: Nombre de la tabla a analizar.

• Estimate_Percent: Porcentaje de filas a estimar.El rango valido es de 0.000001 a 100.En este caso hemos utilizado un 5, o sea un 5%.Es más recomendable usar la constante DBMS_STATS.AUTO_SAMPLE_SIZE, para que el propio Oracle determine el porcentaje suficiente para obtener unas buenas estadísticas. Aunque la primera vez que se ejecuté puede tardar mucho.

• Method_Opt: Hemos utilizado el valor por defecto FOR ALL COLUMNS SIZE AUTO.

• Degree: Grado de paralelismo. Cuando ejecutamos el procedimiento en horario de oficina no utilizamos ningún paralelismo para no sobrecargar la máquina. Por la noche si que podemos usarlo, siempre bajo la aprobación del DBA.

• Cascade: Valor importante. TRUE indica que computamos también las estadísticas de todos los índices relacionados con esta tabla. Utilizar esta función es equivalente a utilizar la función GATHER_INDEX_STATS

Y aquí un ejemplo de función utilizada para computar las estadísticas de todas las tablas que empiezan por 'mi_%'.

FUNCTION fnt_analize RETURN NUMBER IS
CURSOR tablas IS
SELECT table_name FROM user_tables WHERE table_name LIKE 'mi_%';

V_USUARIO VARCHAR2(30);

BEGIN

SELECT user INTO V_USUARIO FROM dual;

FOR tabla IN tablas LOOP

SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => V_USUARIO
,TabName => tabla.TABLE_NAME
,Estimate_Percent => 5
,Method_Opt => 'FOR ALL COLUMNS SIZE AUTO '
,Cascade => TRUE
);

END LOOP;

RETURN 1;

EXCEPTION
WHEN OTHERS THEN
Dbms_Output.put_line(SQLERRM);
RETURN 3;
END fnt_analize;

Normalmente es recomendable computar estadísticas cuando la tabla ha variado considerablemente su tamaño (tanto si hemos realizado inserts como deletes).

Si estamos en un proceso de migración de datos, o cargas masivas dónde cargamos millones de registros diariamente, puede que sea recomendable analizar las tablas cada cierto período y no esperar a que se haya finalizado la migración/carga.

Presentación

Hola!!

En este Blog, como su nombre indica, dejaré mis cosas del SQL. Casos, problemas, soluciones.. que me he encontrado en mi día a día, por si pueden servir de ayuda a quienquiera que lo vea!

Este blog no pretende ser una guía, ni un manual completo. Simplemente explicaré las cosas que yo he probado y que me han funcionando, sabiendo que cada función tiene muchas más opciones y más parámetros de los que yo conozco... pero para esto están las webs oficiales de Oracle y los manuales de SQL! :D

¡Un saludo!