最初由 ccnaide 发布
[B]最近在对数据库调优,想对每条SQL语句的执行时间作个统计,请问有什么方法可以看到? [/B]
优化总有目的的把,首先确认问题方向,是什么类型问题,
如果是IO 争用,那么优化物理/逻辑IO最大 的那些,
如果是sql解析,就要修改程序语句写法。
每条sql 都看有那么多精力吗?
这个语句可以得到sql的很多信息
[PHP]SELECT sql_text, username, disk_reads_per_exec, buffer_gets, disk_reads,
parse_calls, sorts, executions, rows_processed, hit_ratio,
first_load_time, sharable_mem, persistent_mem, runtime_mem, cpu_time,
elapsed_time, address, hash_value
FROM (SELECT sql_text, b.username,
ROUND ((a.disk_reads
/ DECODE (a.executions, 0, 1, a.executions)
),
2
) disk_reads_per_exec,
a.disk_reads, a.buffer_gets, a.parse_calls, a.sorts,
a.executions, a.rows_processed,
100
- ROUND (100 * a.disk_reads / GREATEST (a.buffer_gets, 1), 2)
hit_ratio,
a.first_load_time, sharable_mem, persistent_mem, runtime_mem,
cpu_time, elapsed_time, address, hash_value
FROM SYS.v_$sqlarea a, SYS.all_users b
WHERE a.parsing_user_id = b.user_id
AND b.username NOT IN ('SYS', 'SYSTEM')
ORDER BY disk_reads_per_exec DESC)
[/PHP]
|