my code stock.com

Enrique Castellanos

PROTOCOLO PARA LA REVISIÓN DE QUERIES
by Enrique Castellanos

PROCEDIMIENTO PARA LA REVISIÓN DE QUERIES (LITERALES Y QUE OCUPEN MAS DE 1 MB EN MEMORIA), CURSORES ABIERTOS Y NÚMERO DE CONEXIONES ESTABLECIDAS A LA BASE DE DATOS.

PRE-REQUISITOS:

• Contar con un usuario de consulta de la aplicación correspondiente.
        Ejemplo: sicoffe08_consulta, siaff08_consulta, etc
• Tener acceso a la base de datos correspondiente
• Tener asignado privilegios de select sobre las vistas:
v$session, v$open_cursor, v$sql, v$sqlarea, v$sqltext

Snippet options

Download: Download snippet as protocolo-para-la-revision-de-queries.sql.
Copy snippet: For this you need a free my code stock.com account.
Embed code : You will find the embed code for this snippet at the end of the page, if you want to embed it into a website or a blog!

-- queries literales 

Select substr(sql_text,1,49) QUERY, module, count(*) CUANTOS from v$sqlarea
where executions <= 1
group by substr(sql_text,1,49), module
having count(*) > 1
order by cuantos desc;

El script anterior muestra cuantos queries literales se encontraron para cada instrucción.
En esta salida se debe  diferenciar el modulo de de donde proviene, es  importante para determinar si es la aplicación en revisión la que esta generando los queries.
El desarrollador debe ubicar en la columna de MODULE los que aparecen vacíos, con el valor JDBC Thin Client  o con el signo de interrogación (?) al inicio, ya que esto indica que proviene de OAS o del Tuxedo.

Por tal motivo se revisan cuantos números de repeticiones tiene para cada caso.

Tomando como referencia el que tiene mas repeticiones para su revisión.
En este caso, se tomo uno que inicia con “?” y se deben descartar los que contengan las siguientes cadenas: 

DBMS_SCHELUDER, TOAD, SQLPLUS y DATA PUMP

Después de copiar el query que tiene muchas repeticiones, se procede a buscar en el SQLAREA (memoria) las repeticiones de este.

Buscamos la parte del query que reporta, con el siguiente script:

select * from V$sqlarea
where upper(sql_text) like upper('QUERY COPIADO')

En la parte de QUERY COPIADO se debe insertar el query que se copio en el paso anterior con el signo % al final y entonces ejecutarlo y se corrobora que sea el modulo, el hash_value y el SQL_TEXT

Para facilitar la revisión, se copia la columna de SQL_TEX a un Excel para hacer la revisión.

En donde se corrobora que efectivamente son queries literales por que solo van cambiando un valor en todo el query y requiere de su corrección.


Para el caso en el que el query de la columna SQL_TEX no se vea completa, entonces es necesario revisarlo por su HASH_VALUE

Entonces se ejecuta, por ejemplo, el siguiente query.

Select sql_text from v$sqltext where hash_value = '3842535422' order by piece



--------------- CONEXIONES

select username, machine,sid, to_char(logon_time,'DD/MM/YY HH:MI'),  count(*) from v$session where username is not null
and username not in ('SYS','SYSTEM')
group by username, machine,sid,to_char(logon_time,'DD/MM/YY HH:MI');


En el ejemplo:

Nos interesan las conexiones provenientes de los servidores del OAS y de la aplicación SICOFFE08, por tal motivo lo importante es identificar el SID de cada un a de estas conexiones.

Aquí se considera riesgoso que haya muchas conexiones (arriba de 10)  provenientes de la aplicación, en el ejemplo anterior las conexiones provienen de un servidor OAS :
 HASQA02.hacienda.gob.mx

-- QUERIES QUE OCUPAN MAS DE 1 MB EN MEMORIA

select hash_value, module, sharable_mem from v$sqlarea
where sharable_mem >= 1048576
order by sharable_mem desc;

Se deben identificar los queries mayores a 1MB, tomando en cuenta las columnas MODULO y SHARABLE_MEM
(BYTES).

Para revisar el código completo del query que esta ocupando mucha memoria, se toman los datos de cada columna para aplicarlos en otro script, por ejemplo del punto anterior son los siguientes:

HASH VALUE
2913260219
MODULE
[email protected](S001)
SHARABLE_MEM
4412802

Con los datos anteriores se ejecuta el siguiente query:

select sql_text, '[email protected] (S001)', '4412802' from v$sqltext where hash_value = 2913260219 order by piece;

----------- CURSORES ABIERTOS
select sid, user_name, hash_value, count(*) "cuantos" from v$open_cursor
where user_name is not null
and user_name not in ('SYS','SYSTEM')
group by sid, user_name, HASH_VALUE
having count(*) > 1
order by 4 desc;
Aquí es importante identificar el VALOR_HASH para obtener el query completo. La salida contiene los cursores abiertos, indica el usuario (que normalmente es el nombre de la aplicacion) y el numero de cursores abiertos

Para obtener el query que reporta el anterior punto, se debe considerer el HASH_VALUE y con este dato se corre el siguiente script.

select distinct sql_text from v$sqltext
where hash_value = 1074193004




Create a free my code stock.com account now.

my code stok.com is a free service, which allows you to save and manage code snippes of any kind and programming language. We provide many advantages for your daily work with code-snippets, also for your teamwork. Give it a try!

Find out more and register now

You can customize the height of iFrame-Codes as needed! You can find more infos in our API Reference for iframe Embeds.