查看哪个sql使用临时段比较多

最新推荐文章于 2024-05-24 09:56:20 发布

转载最新推荐文章于 2024-05-24 09:56:20 发布 · 550 阅读

本文提供了一段SQL查询代码，用于分析Oracle数据库中SQL工作区的性能指标，包括内存使用情况、执行次数等，有助于优化数据库性能。

参考《oracle性能优化实务》

select sql_id,operation_type as op, operation_id as id,
  round(estimated_optimal_size/1024/1024,2) as e_opt,  
  round(estimated_onepass_size/1024/1024,2) as e_one,
  round(last_memory_used/1024/1024,2) as l_mem,
  Last_execution as last,
  total_executions as tot, optimal_executions as opt,
  onepass_executions as one, 
  multipasses_executions as mult,
  round(active_time/1000000,2) as sec,   
  round(max_tempseg_size/1024/1024,2) as tmp_m, 
  round(last_tempseg_size/1024/1024,2) as tmp_L
from v$sql_workarea 
where 
  max_tempseg_size is not null 
order by max_tempseg_size desc;