1，v$sql
用v$sql视图查询，通过sql语句获取缓存的次数来决定，buffer_gets desc排列
SQL> SELECT *
2 FROM (SELECT RANK() OVER(ORDER BY BUFFER_GETS DESC) AS RANK_BUFGETS,
3 buffer_gets,
4 TO_CHAR(100 * RATIO_TO_REPORT(BUFFER_GETS) OVER(), '999,99') PCT_BUFGETS,
5 s.sql_text
6 FROM V$SQL S)
7 WHERE RANK_BUFGETS < 11;
RANK_BUFGETS BUFFER_GETS PCT_BUFGETS SQL_TEXT
------------ ----------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 2956873070 15 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN :
2 2823167833 14 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN :
3 2049096597 11 INSERT INTO TRIGGER_OF_CORTAB_0 SELECT PROVIDERID FROM MLOG$_ON_COR_TABLE_0
4 2048987393 11 DELETE FROM MLOG$_ON_COR_TABLE_0
5 1575917593 8 INSERT INTO TRIGGER_OF_CORTABOTHER SELECT PROVIDERID FROM MLOG$_ON_COR_TABLE_OTH
6 1575808391 8 DELETE FROM MLOG$_ON_COR_TABLE_OTHER
7 1409073728 7 select distinct appid from ali_bchance_intro_pic where state=:"SYS_B_0"
8 1409073660 7 select id from ali_bchance_intro_pic where (oracle_to_unix(sysdate)-oracle_to_un
9 558996022 3 INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "STAT"."VISIT_SUPCAT_STAT"(M_ROW$$,"ID
10 520863427 3 SELECT b.session_id AS sid, NVL(b.oracle_username, :"SYS_B_00") AS username, a.o
10 rows selected
SQL>
2，dba_hist_sqlstat
从DBA_HIST_SQLSTAT视图中选出最占用资源的sql，然后通过sql_id查找视图v$sqlarea
SQL> select distinct a.sql_id, b.sql_text
2 from (select hs.sql_id,
3 hs.snap_id,
4 hs.disk_reads_delta,
5 hs.executions_delta,
6 hs.disk_reads_delta /
7 decode(hs.executions_delta, 0, 1, hs.executions_delta)
8 from dba_hist_sqlstat hs
9 where hs.disk_reads_delta > 10000
10 order by hs.disk_reads_delta desc) a
11 join v$sqlarea b
12 on a.sql_id = b.sql_id
13 where rownum < 20;
SQL_ID SQL_TEXT
------------- --------------------------------------------------------------------------------
avrr6jh7z7wvj delete from "STAT"."VISIT_SUPCAT_STAT"
fw2wwvf35jthy select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact u
96d5wskfgbwp3 select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact u
7999tyrb33gpy INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "STAT"."VISIT_SUPCAT_STAT"(M_ROW$$,"ID
b6usrg82hwsa3 call dbms_stats.gather_database_stats_job_proc ( )
47dvkng1gqxjt select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact u
azuahs9jvm4t7 select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact u
7 rows selected
SQL>
3,v$sqlarea
通过sql语句对磁盘的访问次数 disk_reads,用v$sqlarea
SQL> select *
2 from (select b.username username,
3 a.disk_reads reads,
4 a.executions exec,
5 a.disk_reads / decode(a.executions, 0, 1, a.executions) rds_exec_ratio,
6 a.sql_text Statement
7 from v$sqlarea a, dba_users b
8 where a.parsing_user_id = b.user_id
9 and a.disk_reads > 100000
10 order by a.disk_reads desc)
11 where rownum < 30;
USERNAME READS EXEC RDS_EXEC_RATIO STATEMENT
------------------------------ ---------- ---------- -------------- --------------------------------------------------------------------------------
STAT 6400061 11 581823.7272727 delete from "STAT"."VISIT_SUPCAT_STAT"
STAT 3513263 10 351326.3 INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "STAT"."VISIT_SUPCAT_STAT"(M_ROW$$,"ID
SYS 1116643 26 42947.80769230 call dbms_stats.gather_database_stats_job_proc ( )
SYS 404793 8 50599.125 BEGIN SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_01', 'SYS'); END;
SYS 319883 26 12303.19230769 call dbms_space.auto_space_advisor_job_proc ( )
SYS 314802 520 605.3884615384 insert into wri$_adv_objspace_trend_data select timepoint, space_usage, space_a
SYS 225518 1 225518 select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact u
SYS 218308 1 218308 select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact u
RADER 197115 30 6570.5 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN :
SYS 182852 1 182852 select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact u
RADER 181231 30 6041.033333333 DELETE FROM SUPPLY_LOG T WHERE T.PUBDATE SYS 160771 1 160771 select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact u
SYS 152768 1 152768 select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact u
13 rows selected
SQL>
4， v$sessmetric和v$session（SESSION回话）
逻辑读(logical_read_pct)和物理读(physical_read_pct)和cpu来排序来查看消耗较高的回话
SQL> select to_char(m.END_TIME, 'yyyy-mm-dd hh24:mi:ss') e_dttm,
2 m.INTSIZE_CSEC / 100 ints,
3 s.USERNAME usr,
4 m.SESSION_ID sid,
5 m.SESSION_SERIAL_NUM ssn,
6 m.PHYSICAL_READS prds,
7 m.LOGICAL_READS lrds,
8 m.PGA_MEMORY,
9 m.HARD_PARSES hp,
10 m.SOFT_PARSES sp,
11 m.PHYSICAL_READ_PCT prp,
12 m.LOGICAL_READ_PCT lrp,
13 ROUND(m.CPU) cpu100,
14 s.SQL_ID
15 from v$sessmetric m, v$session s
16 where (m.PHYSICAL_READS > 50 or m.CPU > 50 or m.LOGICAL_READS > 50)
17 and m.session_id = s.SID
18 and m.SESSION_SERIAL_NUM = s.SERIAL#
19 order by m.PHYSICAL_READS DESC, m.CPU desc, m.LOGICAL_READS desc;
E_DTTM INTS USR SID SSN PRDS LRDS PGA_MEMORY HP SP PRP LRP CPU100 SQL_ID
--------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------
2014-08-06 17:02:33 15.02 STAT 189 50167 782 70119 1797512 0 0 100 42.9729729 0
SQL>
5，查询使用频率最高的5个sql
SQL> select sql_text, executions
2 from (select sql_text,
3 executions,
4 rank() over(order by executions desc) exec_rank
5 from v$sql)
6 where exec_rank <= 5;
SQL_TEXT EXECUTIONS
-------------------------------------------------------------------------------- ----------
select matchbusin0_.ID as ID37_, matchbusin0_.PROVIDERID as PROVIDERID37_, match 15528215
select privilege#,level from sysauth$ connect by grantee#=prior privilege# and p 5286892
select matchbusin0_.ID as ID83_, matchbusin0_.PROVIDERID as PROVIDERID83_, match 4496001
select file# from file$ where ts#=:1 2639146
SELECT DECODE( :B1 , 0, 'INVALID', 1, 'VALID', 2, 'LOADING', 3, 'LOADED', 4, 'UP 1110848
SQL>
6，消耗磁盘读最多的5个sql
SQL> select disk_reads, sql_text
2 from (select sql_text,
3 disk_reads,
4 rank() over(order by disk_reads desc) disk_reads_rank
5 from v$sql)
6 where disk_reads_rank <= 5;
DISK_READS SQL_TEXT
---------- --------------------------------------------------------------------------------
6400061 delete from "STAT"."VISIT_SUPCAT_STAT"
3513263 INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "STAT"."VISIT_SUPCAT_STAT"(M_ROW$$,"ID
1116643 call dbms_stats.gather_database_stats_job_proc ( )
404793 BEGIN SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_01', 'SYS'); END;
319883 call dbms_space.auto_space_advisor_job_proc ( )
SQL>
7，前10个大量逻辑读的sql
SQL> select buffer_gets, sql_text
2 from (select sql_text,
3 buffer_gets,
4 dense_rank() over(order by buffer_gets desc) buffer_gets_rank
5 from v$sql)
6 where buffer_gets_rank <= 10;
BUFFER_GETS SQL_TEXT
----------- --------------------------------------------------------------------------------
2958268573 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN :
2823711103 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN :
2049490914 INSERT INTO TRIGGER_OF_CORTAB_0 SELECT PROVIDERID FROM MLOG$_ON_COR_TABLE_0
2049381689 DELETE FROM MLOG$_ON_COR_TABLE_0
1576220854 INSERT INTO TRIGGER_OF_CORTABOTHER SELECT PROVIDERID FROM MLOG$_ON_COR_TABLE_OTH
1576111631 DELETE FROM MLOG$_ON_COR_TABLE_OTHER
1409344880 select distinct appid from ali_bchance_intro_pic where state=:"SYS_B_0"
1409344812 select id from ali_bchance_intro_pic where (oracle_to_unix(sysdate)-oracle_to_un
558996022 INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "STAT"."VISIT_SUPCAT_STAT"(M_ROW$$,"ID
520863427 SELECT b.session_id AS sid, NVL(b.oracle_username, :"SYS_B_00") AS username, a.o
10 rows selected
SQL>
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27013009/viewspace-1246675/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/27013009/viewspace-1246675/
383
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
