1. 查看消耗内存最多的sql(v$sqlarea)
1 select b.username , 2 a.buffer_gets , --所有子游标运行这条语句导致的读内存次数 3 a.executions, --所有子游标的执行这条语句次数 4 a.buffer_gets/decode(a.executions,0,1,a.executions),--这条语句执行一次读取内存次数 5 a.sql_text SQL 6 from v$sqlarea a,dba_users b 7 where a.parsing_user_id = b.user_id and a.buffer_gets >10000 8 order by buffer_gets desc;
2.查看消耗磁盘多的sql(v$sqlarea)
1 select b.username , 2 a.disk_reads , --所有子游标运行这条语句导致的读磁盘次数 3 a.executions, --所有子游标的执行这条语句次数 4 a.disk_reads/decode(a.executions,0,1,a.executions),----这条语句执行一次读取磁盘次数 5 a.sql_text SQL 6 from v$sqlarea a,dba_users b 7 where a.parsing_user_id = b.user_id and a.DISK_READS >10000 8 order by disk_reads desc;
3.查看执行次数多的SQL(v$sqlarea)
1 select sql_text, executions 2 from v$sqlarea 3 where rownum<81 4 order by executions desc
4.查看排序多的SQL(v$sqlarea)
1 select sql_text, sorts 2 from v$sqlarea 3 order by sorts desc 4 where rownum<21;
5.分析的次数太多,执行的次数太少,要用绑变量的方法来写sql(v$sqlarea)
1 select substr(sql_text,1,80) "sql",count(*),sum(executions) "totexecs" 2 from v$sqlarea 3 where executions<5 --sql_text 执行次数小于5 4 group by substr(sql_text,1,80) 5 having count(*)>30 --sql_text 分析次数大于30 6 order by 2;
6.前5位用户I/O等待最高的SQL语句 (v$sqlarea)
1 select sql_text, sql_id, elapsed_time, cpu_time, user_io_wait_time 2 from sys.v$sqlarea 3 where rownum < 6 4 order by 5 desc
7.查看当前用户&username执行的SQL(v$sqltext_with_newlines,v$session)
1 select sql_text 2 from v$sqltext_with_newlines 3 where(hash_value, address) in 4 (select sql_hash_value, sql_address 5 from v$session 6 where username='&username') 7 order by address, piece;