Top 10 CPU queries en Oracle

Hoy una nueva colaboración de Xavi, voy tirando de correos antiguos, aun me qudan unos cuantos. El título lo dice todo, así que os pongo el cut&paste y palante.

"Hola de nuevo,

He medio montado esta query que me parece muy útil. Extrae las 10 consultas que más cpu han consumido en una instancia oracle desde que ésta está arriba. Con una simple modificación, se puede usar también para sacar las que usan más accesos a la caché y otras.

select * from (

select distinct sql_text, round(cpu_time/executions)/1000000 cpu_time_sec_per_exec, sharable_mem, persistent_mem,

runtime_mem, executions, cast ((parse_calls/executions) as number (10,0)) parse_call_per_exec, cast((sorts/executions) as number (10,2)) sorts_per_exec,

cast ((buffer_gets/executions) as number(10,0)) buffer_gets_per_exec , cast((disk_reads/executions) as number(10,0)) disk_reads_per_exec, loads, open_versions,

to_char(to_date(first_load_time, 'YYYY-MM-DD/HH24:MI:SS'),'MM/DD HH24:MI:SS') first_load_time, rawtohex(address) address, hash_value hash_value, round(rows_processed/executions) rows_processed_per_exec,

command_type, parsing_user_id, OPTIMIZER_MODE

from v$sqlarea

where (parsing_user_id != 0)

and (executions >= 1000) order by cpu_time_sec_per_exec desc)

where rownum <=10

Con cambiar el order by puedes sacar las que tienen más buffer gets, las que acceden más a disco, etc. Esta consulta está para 9i, pero en 10g se pueden sacar aún más datos (mirad la vista v$sqlarea).

Con esta vista lo más importante a tener en cuenta es que algunos datos son acumulativos, y otros no, y que, de los acumulados, unos nos interesan acumulados y otros no. Por eso hay que tratarla un poco.

En este caso he puesto executions >=1000 porque me interesaba sacar las consultas frecuentes que más consumen. Si se quiere sacar la que más ha consumido de todos los tiempos (desde que la instancia está arriba, claro) se ha de poner un 1. Esto depende del tipo de entorno que tengáis o de lo que busquéis.

Otra posibilidad a tener en cuenta es que si el entorno no usa bind variables (o no las usa bien), puede que todas las consultas que os ocupen el top ten sean variaciones de la misma (un select con diferentes condiciones de búsqueda). Ese caso se puede añadir substr(sql_text,1,50) (o el valor más apropiado para el caso) para eliminar las duplicaciones.

Si sospecháis que el problema no es el tipo de query, sino que hay una query que para un determinado valor se dispara el coste (por un tema de duplicaciones en índices, o que no entra por índice), se puede eliminar la cláusula distinct, pero en ese caso se tiene que añadir sql_text como condición con alguna cadena, esto es, hay que ir ya a la búsqueda de una determinada query y de cuánto consume. Si se lanza sin el distinct os pueden salir montones de queries iguales. Claro que siempre podéis lanzarla sin el rownum y leeros las cinco mil líneas, o más, que os saldrán a ver si encontráis algo interesante.

Xavi"