转自http://blog.itpub.net/23155850/viewspace-683737/
1、查看值得怀疑的SQL
select substr(to_char(s.pct, '99.00'), 2) || '%' load, s.executions executes, p.sql_text from (select address, disk_reads, executions, pct, rank() over(order by disk_reads desc) ranking from (select address, disk_reads, executions, 100 * ratio_to_report(disk_reads) over() pct from sys.v_$sql where command_type != 47) where disk_reads > 50 * executions) s, sys.v_$sqltext pwhere s.ranking <= 5 and p.address = s.addressorder by 1, s.address, p.piece;
2、查看消耗内存多的sql
select b.username ,a. buffer_gets ,a.executions,a.disk_reads/decode(a.executions,0,1,a.executions),a.sql_text SQLfrom v$sqlarea a,dba_users bwhere a.parsing_user_id = b.user_id and a.disk_reads >10000 order by disk_reads desc;
3、查看逻辑读多的SQL
select * from (select buffer_gets, sql_text from v$sqlarea where buffer_gets > 500000 order by buffer_gets desc)where rownum <= 30;
4、查看执行次数多的SQL
select sql_text, executions from (select sql_text, executions from v$sqlarea order by executions desc)where rownum < 81;
5、查看读硬盘多的SQL
select sql_text, disk_reads from (select sql_text, disk_reads from v$sqlarea order by disk_reads desc)where rownum < 21;
6、查看排序多的SQL
select sql_text, sorts from (select sql_text, sorts from v$sqlarea order by sorts desc)where rownum < 21;
7、分析的次数太多，执行的次数太少，要用绑变量的方法来写sql
set pagesize 600;set linesize 120;select substr(sql_text, 1, 80) "sql", count(*), sum(executions) "totexecs" from v$sqlareawhere executions < 5group by substr(sql_text, 1, 80)having count(*) > 30order by 2;
8、游标的观察
set pages 300;select sum(a.value), b.name from v$sesstat a, v$statname bwhere a.statistic# = b.statistic# and b.name = 'opened cursors current'group by b.name;select count(0) from v$open_cursor;select user_name, sql_text, count(0) from v$open_cursorgroup by user_name, sql_texthaving count(0) > 30;
9、查看当前用户&username执行的SQL
select sql_text from v$sqltext_with_newlineswhere (hash_value, address) in (select sql_hash_value, sql_address from v$session where username = '&username')order by address, piece;