Consultas de diagnóstico para el ajuste de consultas

Utilice las consultas a continuación para identificar problemas con las consultas o con sus tablas subyacentes que pueden afectar el rendimiento de las consultas. Le recomendamos usar estas consultas con los procesos de ajuste de consultas descritos en Análisis y mejora de las consultas.

Identificación de consultas que deben ajustarse con prioridad

La siguiente consulta identifica las 50 instrucciones que se han ejecutado en los últimos 7 días y que más tiempo demandaron. Puede utilizar los resultados para identificar las consultas que están tardando excesivamente. También puede identificar las consultas que se ejecutan con frecuencia (las que aparecen más de una vez en el conjunto de resultados). Estas consultas suelen ser prioritarias para realizar ajustes destinados a mejorar el rendimiento del sistema.

Esta consulta también proporciona un recuento de los eventos de alerta asociados a cada consulta identificada. Estas alertas proporcionan detalles que puede utilizar para mejorar el rendimiento de la consulta. Para obtener más información, consulte Revisión de alertas de consultas.

select trim(database) as db, count(query) as n_qry, 
max(substring (qrytext,1,80)) as qrytext, 
min(run_minutes) as "min" , 
max(run_minutes) as "max", 
avg(run_minutes) as "avg", sum(run_minutes) as total,  
max(query) as max_query_id, 
max(starttime)::date as last_run, 
sum(alerts) as alerts, aborted
from (select userid, label, stl_query.query, 
trim(database) as database, 
trim(querytxt) as qrytext, 
md5(trim(querytxt)) as qry_md5, 
starttime, endtime, 
(datediff(seconds, starttime,endtime)::numeric(12,2))/60 as run_minutes,     
alrt.num_events as alerts, aborted 
from stl_query 
left outer join 
(select query, 1 as num_events from stl_alert_event_log group by query ) as alrt 
on alrt.query = stl_query.query
where userid <> 1 and starttime >=  dateadd(day, -7, current_date)) 
group by database, label, qry_md5, aborted
order by total desc limit 50;

Identificación de tablas con sesgo de datos o con filas desordenadas

La siguiente consulta identifica las tablas que tienen una distribución irregular de datos (sesgo de datos) o un alto porcentaje de filas desordenadas.

Un valor bajo de skew indica que los datos de la tabla están distribuidos correctamente. Si una tabla tiene un valor de skew de 4,00 o menor, considere la opción de modificar su estilo de distribución de datos. Para obtener más información, consulte Distribución de datos poco óptima.

Si una tabla tiene un valor de pct_unsorted mayor que 20 por ciento, considere la opción de ejecutar el comando VACUUM. Para obtener más información, consulte Filas desordenadas o mal ordenadas.

Revise también los valores mbytes y pct_of_total de cada tabla. Estas columnas identifican el tamaño de la tabla y el porcentaje de espacio bruto en disco que consume la tabla. El espacio de disco en bruto incluye el espacio que reserva Amazon Redshift para uso interno, por lo que supera su capacidad nominal, la cual corresponde a la cantidad de espacio de disco disponible para el usuario. Utilice esta información para verificar que tiene espacio libre en el disco que equivalga al menos a 2,5 veces el tamaño de su tabla más grande. Si tiene este espacio disponible, el sistema podrá guardar los resultados intermedios en el disco cuando procese consultas complejas.

select trim(pgn.nspname) as schema, 
trim(a.name) as table, id as tableid, 
decode(pgc.reldiststyle,0, 'even',1,det.distkey ,8,'all') as distkey, dist_ratio.ratio::decimal(10,4) as skew, 
det.head_sort as "sortkey", 
det.n_sortkeys as "#sks", b.mbytes,  
decode(b.mbytes,0,0,((b.mbytes/part.total::decimal)*100)::decimal(5,2)) as pct_of_total, 
decode(det.max_enc,0,'n','y') as enc, a.rows, 
decode( det.n_sortkeys, 0, null, a.unsorted_rows ) as unsorted_rows , 
decode( det.n_sortkeys, 0, null, decode( a.rows,0,0, (a.unsorted_rows::decimal(32)/a.rows)*100) )::decimal(5,2) as pct_unsorted 
from (select db_id, id, name, sum(rows) as rows, 
sum(rows)-sum(sorted_rows) as unsorted_rows 
from stv_tbl_perm a 
group by db_id, id, name) as a 
join pg_class as pgc on pgc.oid = a.id
join pg_namespace as pgn on pgn.oid = pgc.relnamespace
left outer join (select tbl, count(*) as mbytes 
from stv_blocklist group by tbl) b on a.id=b.tbl
inner join (select attrelid, 
min(case attisdistkey when 't' then attname else null end) as "distkey",
min(case attsortkeyord when 1 then attname  else null end ) as head_sort , 
max(attsortkeyord) as n_sortkeys, 
max(attencodingtype) as max_enc 
from pg_attribute group by 1) as det 
on det.attrelid = a.id
inner join ( select tbl, max(mbytes)::decimal(32)/min(mbytes) as ratio 
from (select tbl, trim(name) as name, slice, count(*) as mbytes
from svv_diskusage group by tbl, name, slice ) 
group by tbl, name ) as dist_ratio on a.id = dist_ratio.tbl
join ( select sum(capacity) as  total
from stv_partitions where part_begin=0 ) as part on 1=1
where mbytes is not null 
order by  mbytes desc;

Identificación de consultas con bucles anidados

La siguiente consulta identifica consultas que han registrado eventos de alerta por bucles anidados. Para obtener más información acerca de cómo reparar una condición de bucle anidado, consulte Bucle anidado.

select query, trim(querytxt) as SQL, starttime 
from stl_query 
where query in (
select distinct query 
from stl_alert_event_log 
where event like 'Nested Loop Join in the query plan%') 
order by starttime desc;

Revisión de los tiempos de espera de las colas de consultas

La siguiente consulta muestra cuánto tiempo han esperado las consultas recientes hasta que se abriera un lugar en una cola de consultas para poder ejecutarse. Si advierte una tendencia de tiempos de espera prolongados, puede modificar la configuración de colas de consultas para obtener un mejor rendimiento. Para obtener más información, consulte Implementación de WLM manual.

select trim(database) as DB , w.query, 
substring(q.querytxt, 1, 100) as querytxt,  w.queue_start_time, 
w.service_class as class, w.slot_count as slots, 
w.total_queue_time/1000000 as queue_seconds, 
w.total_exec_time/1000000 exec_seconds, (w.total_queue_time+w.total_Exec_time)/1000000 as total_seconds 
from stl_wlm_query w 
left join stl_query q on q.query = w.query and q.userid = w.userid 
where w.queue_start_Time >= dateadd(day, -7, current_Date) 
and w.total_queue_Time > 0  and w.userid >1   
and q.starttime >= dateadd(day, -7, current_Date) 
order by w.total_queue_time desc, w.queue_start_time desc limit 35;

Revisión de las alertas de consultas por tablas

La siguiente consulta identifica las tablas que han registrado eventos de alerta y también identifica qué tipo de alertas se activan con mayor frecuencia.

Si el valor de minutes de una fila con una tabla identificada es elevado, controle si esa tabla necesita un mantenimiento de rutina, como tener que ejecutar ANALYZE o VACUUM en esa tabla.

Si el valor count es elevado para una fila, pero el valor de table es nulo, ejecute una consulta con STL_ALERT_EVENT_LOG para el valor de event asociado a fin de investigar por qué esa alerta se activa con tanta frecuencia.

select trim(s.perm_table_name) as table, 
(sum(abs(datediff(seconds, s.starttime, s.endtime)))/60)::numeric(24,0) as minutes, trim(split_part(l.event,':',1)) as event,  trim(l.solution) as solution, 
max(l.query) as sample_query, count(*) 
from stl_alert_event_log as l 
left join stl_scan as s on s.query = l.query and s.slice = l.slice 
and s.segment = l.segment and s.step = l.step
where l.event_time >=  dateadd(day, -7, current_Date) 
group by 1,3,4 
order by 2 desc,6 desc;

Identificación de tablas con estadísticas faltantes

La siguiente consulta proporciona un recuento de las consultas que está ejecutando con tablas a las cuales le faltan estadísticas. Si esta consulta devuelve alguna fila, fíjese en el valor de plannode para determinar cuál es la tabla afectada y, luego, ejecute el comando ANALYZE en esa tabla.

select substring(trim(plannode),1,100) as plannode, count(*) 
from stl_explain 
where plannode like '%missing statistics%' 
group by plannode 
order by 2 desc;