本文深入探讨了SQL查询优化策略，通过筛选具有高执行次数和缓冲获取次数的查询，并计算其命中率和每运行次读取的平均次数，重点分析用户IO等待时间。通过SQL查询示例，展示了如何有效识别和优化数据库性能瓶颈。
SELECT *
FROM v$sqlarea v
WHERE executions > 0
AND buffer_gets > 0
AND (buffer_gets - disk_reads) / buffer_gets < 0.8
ORDER BY v.USER_IO_WAIT_TIME DESC
SELECT executions,
disk_reads,
buffer_gets,
ROUND((buffer_gets - disk_reads) / buffer_gets, 2) hit_radio,
ROUND(disk_reads / executions, 2) reads_per_run,
sql_text
FROM v$sqlarea v
WHERE v.PARSING_SCHEMA_NAME = 'JUQI'
AND executions > 0
AND buffer_gets > 0
AND (buffer_gets - disk_reads) / buffer_gets < 0.8
ORDER BY 4 DESC
FROM v$sqlarea v
WHERE executions > 0
AND buffer_gets > 0
AND (buffer_gets - disk_reads) / buffer_gets < 0.8
ORDER BY v.USER_IO_WAIT_TIME DESC
SELECT executions,
disk_reads,
buffer_gets,
ROUND((buffer_gets - disk_reads) / buffer_gets, 2) hit_radio,
ROUND(disk_reads / executions, 2) reads_per_run,
sql_text
FROM v$sqlarea v
WHERE v.PARSING_SCHEMA_NAME = 'JUQI'
AND executions > 0
AND buffer_gets > 0
AND (buffer_gets - disk_reads) / buffer_gets < 0.8
ORDER BY 4 DESC
扫一扫
753
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
