1、收集數據庫性能報表
Oracle 在10g以前的使用的是 Statspack做性能故障診斷的。Oracle Database 10g 提供了一個顯著改進的工具：自動工作負載信息庫 (AWR)。AWR 和數據庫一起安裝。數據庫裝好后，，快照由一個稱為 MMON 的新的后臺進程及其從進程自動地每小時采集一次（snap）
要查看當前的設置，您可以使用下面的語句：

select snap_interval, retention

from dba_hist_wr_control;



SNAP_INTERVAL       RETENTION

------------------- -------------------

+00000 01:00:00.0   +00007 00:00:00.0

begin

dbms_workload_repository.modify_snapshot_settings (

interval => 20,

retention => 2*24*60

);

end;

AWR 使用幾個表來存儲采集的統計數據，所有的表都存儲在新的名稱為 SYSAUX 的特定表空間中的 SYS 模式下，并且以 WRM$_* 和 WRH$_* 的格式命名。前一種類型存儲元數據信息（如檢查的數據庫和采集的快照），后一種類型保存實際采集的統計數據。（您可能已經猜到，H 代表“歷史數據 (historical)”而 M 代表“元數據 (metadata)”。）在這些表上構建了幾種帶前綴 DBA_HIST_ 的視圖，這些視圖可以用來編寫您自己的性能診斷工具。視圖的名稱直接與表相關；例如，視圖 DBA_HIST_SYSMETRIC_SUMMARY 是在WRH$_SYSMETRIC_SUMMARY 表上構建的。 AWR 歷史表采集的信息比 Statspack 多許多，這些信息包括表空間使用率、文件系統使用率、甚至操作系統統計數據。這些表的完整的列表可以從數據字典中看到。

oracle用戶登陸

# su - oracle

$ sqlplus '/as sysdba'

在壓力測試或者sql測試前

sql> execute dbms_workload_repository.create_snapshot();

測試完成后，再次生成快照

sql> execute dbms_workload_repository.create_snapshot();

如果有了兩次生成的快照后，生成報表

sql> @ ?/rdbms/admin/awrrpt.sql;

回車，然后輸入一個報表名字，生成html格式的報表文件

 

Version 10.2

AWR Objects Source {ORACLE_HOME}/rdbms/admin/dbmsawr.sql First Availability 10.1 Background Process MMON - Automatic data purging every 7 days by default Constants Name Retention Data Type Value MAX_INTERVAL 100 years NUMBER 52560000 MIN_INTERVAL 10 minutes NUMBER 10 MAX_RETENTION 100 years NUMBER 52560000 MIN_RETENTION 1 day NUMBER 1440 Data Types AWRRPT_TEXT_TYPE AWRRPT_HTML_TYPE AWRRPT_TEXT_TYPE_TABLE AWRRPT_HTML_TYPE_TABLE SYS AWRRPT_ROW_TYPE Dependencies dba_hist_baseline dba_hist_snapshot - - awrrpt_html_type plitblm awrrpt_html_type_table wrm$_baseline awrrpt_text_type wrm$_snapshot awrrpt_type_table wrm$_snap_error dbms_swrf_lib wrm$_wr_control dbms_swrf_report_internal AWR_REPORT_HTML Display the AWR report in HTML dbms_workload_repository.awr_report_html( l_dbid     IN NUMBER, l_inst_num IN NUMBER, l_bid      IN NUMBER, l_eid      IN NUMBER, l_options IN NUMBER DEFAULT 0) RETURN awrrpt_text_type_table PIPELINED; awrrpt_text_type_table is VARCHAR2(150) See AWR Report demo linked at the bottom of the page AWR_REPORT_TEXT Display the AWR report in ASCII text dbms_workload_repository.awr_report_text( l_dbid     IN NUMBER, l_inst_num IN NUMBER, l_bid      IN NUMBER, l_eid      IN NUMBER, l_options IN NUMBER DEFAULT 0) RETURN awrrpt_text_type_table PIPELINED; awrrpt_text_type_table is VARCHA

Version 11.1

AWR Objects Source {ORACLE_HOME}/rdbms/admin/dbmsawr.sql First Availability 10.1 Background Process MMON - Automatic data purging every 7 days by default Constants Name Retention Data Type Value MAX_INTERVAL 100 years NUMBER 52560000 MIN_INTERVAL 10 minutes NUMBER 10 MAX_RETENTION 100 years NUMBER 52560000 MIN_RETENTION 1 day NUMBER 1440 Data Types AWRRPT_TEXT_TYPE AWRRPT_HTML_TYPE AWRRPT_TEXT_TYPE_TABLE AWRRPT_HTML_TYPE_TABLE SYS AWRRPT_ROW_TYPE Dependencies dba_hist_baseline dba_hist_snapshot - - awrrpt_html_type plitblm awrrpt_html_type_table wrm$_baseline awrrpt_text_type wrm$_snapshot awrrpt_type_table wrm$_snap_error dbms_swrf_lib wrm$_wr_control dbms_swrf_report_internal File that create the AWR schema {ORACLE_HOME}/rdbms/admin/catawr.sql {ORACLE_HOME}/rdbms/admin/catawrpd.sql {ORACLE_HOME}/rdbms/admin/catawrtb.sql {ORACLE_HOME}/rdbms/admin/catawrwv.sql -- must be run as SYSDBA   ADD_COLORED_SQL (new 11g) Routine to add a colored SQL ID. If an SQL ID is colored, it will always be captured in every snapshot, independent of its level of activities (i.e. does not have to be a TOP SQL). Capturing will occur if the SQL is found in the cursor cache at snapshot time. dbms_workload_repository.add_colored_sql( sql_id IN VARCHAR2, dbid   IN NUMBER DEFAULT NULL); desc wrm$_colored_sql SELECT * FROM wrm$_colored_sql; SELECT dbid FROM gv$database; SELECT sql_id FROM gv$sql WHERE rownum < 101; exec dbms_workload_repository.add_colored_sql('5rygsj4dbw6jt', 1692970157); SELECT * FROM wrm$_colored_sql;   ASH_REPORT_HTML (new 11g) Display the ASH report in HTML dbms_workload_repository.ash_report_html( l_dbid         IN NUMBER,  l_inst_num     IN NUMBER,  l_btime        IN DATE, l_etime        IN DATE, l_options      IN NUMBER DEFAULT 0, l_slot_width   IN NUMBER DEFAULT 0, l_sid          IN NUMBER DEFAULT NULL, l_sql_id       IN VARCHAR2 DEFAULT NULL, l_wait_class   IN VARCHAR2 DEFAULT NULL, l_service_hash IN NUMBER DEFAULT NULL, l_module       IN VARCHAR2 DEFAULT NULL, l_action       IN VARCHAR2 DEFAULT NULL, l_client_id    IN VARCHAR2 DEFAULT NULL, l_plsql_entry  IN VARCHAR2 DEFAULT NULL) RETURN awrrpt_html_type_table PIPELINED; SELECT dbid FROM gv$database; SELECT inst_id FROM gv$instance; SELECT sample_time FROM gv$active_session_history ORDER BY 1; set pagesize 0 set linesize 121 spool c:\temp\ash_rpt.html SELECT * FROM TABLE(dbms_workload_repository.ash_report_html(1692970157, 1, SYSDATE-30/1440, SYSDATE-1/1440)); spool off Alternative ASH HTML Report define report_type = 'html'; define begin_time = '-30' define duration = ''; define report_name = 'c:\temp\ashrpt.html'; @?/rdbms/admin/ashrpt Alternative ASH HTML Report define report_type = 'html'; define begin_time = '-30' define duration = ''; define report_name = 'c:\temp\ashrpt.html'; @?/rdbms/admin/ashrpti   ASH_REPORT_TEXT (new 11g) Display the ASH report in TEXT dbms_workload_repository.ash_report_text( l_dbid         IN NUMBER,  l_inst_num     IN NUMBER,  l_btime        IN DATE, l_etime        IN DATE, l_options      IN NUMBER DEFAULT 0, l_slot_width   IN NUMBER DEFAULT 0, l_sid          IN NUMBER DEFAULT NULL, l_sql_id       IN VARCHAR2 DEFAULT NULL, l_wait_class   IN VARCHAR2 DEFAULT NULL, l_service_hash IN NUMBER DEFAULT NULL, l_module       IN VARCHAR2 DEFAULT NULL, l_action       IN VARCHAR2 DEFAULT NULL, l_client_id    IN VARCHAR2 DEFAULT NULL, l_plsql_entry  IN VARCHAR2 DEFAULT NULL) RETURN awrrpt_text_type_table PIPELINED; SELECT dbid FROM gv$database; SELECT inst_id FROM gv$instance; SELECT sample_time FROM gv$active_session_history ORDER BY 1; set pagesize 0 set linesize 121 spool c:\temp\ash_rpt.html SELECT * FROM TABLE(dbms_workload_repository.ash_report_text(1692970157, 1, SYSDATE-30/1440, SYSDATE-1/1440)); spool off Alternative ASH Text Report define report_type = 'text'; define begin_time = '-30' define duration = ''; define report_name = 'c:\temp\ashrpt.txt'; @?/rdbms/admin/ashrpt Alternative ASH Text Report define report_type = 'text'; define begin_time = '-30' define duration = ''; define report_name = 'c:\temp\ashrpt.txt'; @?/rdbms/admin/ashrpti   AWR_DIFF_REPORT_HTML (new 11g) This table function displays the AWR Compare Periods Report in HTML format. The output  is one column of VARCHAR2(5000). dbms_workload_repository.awr_diff_report_html( dbid1     IN NUMBER, inst_num1 IN NUMBER, bid1      IN NUMBER, eid1      IN NUMBER, dbid2     IN NUMBER, inst_num2 IN NUMBER, bid2      IN NUMBER, eid2      IN NUMBER) RETURN awrrpt_html_type_table PIPELINED; TBD   AWR_DIFF_REPORT_TEXT (new 11g) This table function displays the AWR Compare Periods Report in TEXT format. The output  is one column of VARCHAR2(240). dbms_workload_repository.awr_diff_report_text( awr_diff_report_text(dbid1 IN NUMBER, inst_num1 IN NUMBER, bid1      IN NUMBER, eid1      IN NUMBER, dbid2     IN NUMBER, inst_num2 IN NUMBER, bid2      IN NUMBER, eid2      IN NUMBER) RETURN awrdrpt_text_type_table PIPELINED; TBD   AWR_REPORT_HTML Display the AWR report in HTML dbms_workload_repository.awr_report_html( l_dbid     IN NUMBER, l_inst_num IN NUMBER, l_bid      IN NUMBER, l_eid      IN NUMBER, l_options  IN NUMBER DEFAULT 0) RETURN awrrpt_text_type_table PIPELINED; awrrpt_text_type_table is VARCHAR2(150) See AWR Report demo linked at the bottom of the page   AWR_REPORT_TEXT Display the AWR report in ASCII text dbms_workload_repository.awr_report_text( l_dbid     IN NUMBER, l_inst_num IN NUMBER, l_bid      IN NUMBER, l_eid      IN NUMBER, l_options  IN NUMBER DEFAULT 0) RETURN awrrpt_text_type_table PIPELINED; awrrpt_text_type_table is VARCHAR2(80) See AWR Report demo linked at the bottom of the page   AWR_SQL_REPORT_HTML (new 11g) Display the AWR SQL report in HTML dbms_workload_repository.awr_sql_report_html( l_dbid     IN NUMBER,  l_inst_num IN NUMBER,  l_bid      IN NUMBER,  l_eid      IN NUMBER, l_sqlid    IN VARCHAR2, l_options  IN NUMBER DEFAULT 0) RETURN awrrpt_html_type_table PIPELINED; SELECT dbid FROM gv$database; SELECT inst_id FROM gv$instance; set pagesize 0 set linesize 121 col instart_fmt noprint; col inst_name format a12 heading 'Instance'; col db_name format a12 heading 'DB Name'; col snap_id format 99999990 heading 'Snap Id'; col snapdat format a18 heading 'Snap Started' just c; col lvl format 99 heading 'Snap|Level'; set heading on; break on inst_name on db_name on host on instart_fmt skip 1; ttitle off; SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT, di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID, TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT, s.snap_level LVL FROM dba_hist_snapshot s, dba_hist_database_instance di WHERE di.dbid = s.dbid AND di.instance_number = s.instance_number AND di.startup_time = s.startup_time ORDER BY snap_id; SELECT sql_id FROM gv$active_session_history WHERE TRUNC(sql_exec_start) = TRUNC(SYSDATE); spool c:\temp\awr_sql_rpt.html SELECT * FROM TABLE(dbms_workload_repository.awr_sql_report_html(1692970157, 1, 1230, 1231, 'a01hp0psv0rrh')); spool off   AWR_SQL_REPORT_TEXT (new 11g) Display the AWR SQL report in TEXT dbms_workload_repository.awr_sql_report_text( l_dbid     IN NUMBER,  l_inst_num IN NUMBER,  l_bid      IN NUMBER,  l_eid      IN NUMBER, l_sqlid    IN VARCHAR2, l_options  IN NUMBER DEFAULT 0) RETURN awrsqrpt_text_type_table PIPELINED; SELECT dbid FROM gv$database; SELECT inst_id FROM gv$instance; set pagesize 0 set linesize 121 col instart_fmt noprint; col inst_name format a12 heading 'Instance'; col db_name format a12 heading 'DB Name'; col snap_id format 99999990 heading 'Snap Id'; col snapdat format a18 heading 'Snap Started' just c; col lvl format 99 heading 'Snap|Level'; set heading on; break on inst_name on db_name on host on instart_fmt skip 1; ttitle off; SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT, di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID, TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT, s.snap_level LVL FROM dba_hist_snapshot s, dba_hist_database_instance di WHERE di.dbid = s.dbid AND di.instance_number = s.instance_number AND di.startup_time = s.startup_time ORDER BY snap_id; SELECT sql_id FROM gv$active_session_history WHERE TRUNC(sql_exec_start) = TRUNC(SYSDATE); spool c:\temp\awr_sql_rpt.txt SELECT * FROM TABLE(dbms_workload_repository.awr_sql_report_text(1692970157, 1, 1230, 1231, 'a01hp0psv0rrh')); spool off   CREATE_BASELINE (new 11g parameter) Creates a baseline returns the baseline_id Overload 1 dbms_workload_repository.create_baseline( start_snap_id IN NUMBER, end_snap_id   IN NUMBER, baseline_name IN VARCHAR2, dbid          IN NUMBER DEFAULT NULL, expiration    IN NUMBER DEFAULT NULL) RETURN NUMBER; SELECT dbid FROM gv$database; set linesize 121 col startup_time format a40 SELECT snap_id, startup_time FROM dba_hist_snapshot ORDER BY 1,2; SELECT baseline_name, dbid FROM dba_hist_baseline; set serveroutput on DECLARE  i dba_hist_baseline.baseline_id%TYPE; BEGIN   i := dbms_workload_repository.create_baseline(1199, 1207,   'UW_BASE', 1692970157);   dbms_output.put_line(TO_CHAR(i)); END; / SELECT baseline_id, baseline_name FROM dba_hist_baseline; Overload 2 dbms_workload_repository.create_baseline( start_snap_id IN NUMBER, end_snap_id   IN NUMBER, baseline_name IN VARCHAR2, dbid          IN NUMBER DEFAULT NULL expiration    IN NUMBER DEFAULT NULL); SELECT dbid FROM gv$database; SELECT snap_id, startup_time FROM dba_hist_snapshot ORDER BY 1,2; SELECT baseline_name, dbid FROM dba_hist_baseline; exec dbms_workload_repository.create_baseline(1199, 1207, 'UW_BASE', 1692970157); SELECT baseline_name, dbid FROM dba_hist_baseline;   CREATE_BASELINE_TEMPLATE (new 11g) Creates a Baseline Template for a single time period. There will be a MMON task that will use these inputs to create a Baseline for the time period when the time comes. Overload 1 dbms_workload_repository.create_baseline_template( start_time    IN DATE,  end_time      IN DATE, baseline_name IN VARCHAR2, template_name IN VARCHAR2, expiration    IN NUMBER DEFAULT NULL, dbid          IN NUMBER DEFAULT NULL); desc dba_hist_baseline_template SELECT dbid, template_id, template_name, template_type FROM dba_hist_baseline_template; SELECT baseline_name, dbid FROM dba_hist_baseline; exec dbms_workload_repository.create_baseline_template(SYSDATE+1/1440, SYSDATE+5/1440, 'UW_BASE2', 'UW_TEMPLATE', 1); SELECT dbid, template_id, template_name, template_type FROM dba_hist_baseline_template; Overload 2 dbms_workload_repository.create_baseline_template( day_of_week          IN VARCHAR2, hour_in_day          IN NUMBER,  duration             IN NUMBER, start_time           IN DATE,  end_time             IN DATE, baseline_name_prefix IN VARCHAR2, template_name        IN VARCHAR2, expiration           IN NUMBER DEFAULT 35, dbid                 IN NUMBER DEFAULT NULL); TBD   CREATE_SNAPSHOT Create snapshot and return snapshot ID Overload 1 dbms_workload_repository.create_snapshot( flush_level IN VARCHAR2 DEFAULT 'TYPICAL') RETURN NUMBER; Flush Levels ALL TYPICAL set linesize 121 col begin_interval_time format a30 col end_interval_time format a30 SELECT snap_id, startup_time, begin_interval_time, end_interval_time FROM dba_hist_snapshot ORDER BY 1,2; set serveroutput on DECLARE  i dba_hist_snapshot.snap_id%TYPE; BEGIN   i := dbms_workload_repository.create_snapshot;   dbms_output.put_line(TO_CHAR(i)); END; / SELECT snap_id, startup_time, begin_interval_time, end_interval_time FROM dba_hist_snapshot ORDER BY 1,2; Overload 2 dbms_workload_repository.create_snapshot( flush_level IN VARCHAR2 DEFAULT 'TYPICAL'); Flush Levels ALL TYPICAL set linesize 121 col begin_interval_time format a30 col end_interval_time format a30 SELECT snap_id, startup_time, begin_interval_time, end_interval_time FROM dba_hist_snapshot ORDER BY 1,2; exec dbms_workload_repository.create_snapshot; SELECT snap_id, startup_time, begin_interval_time, end_interval_time FROM dba_hist_snapshot ORDER BY 1,2;   DROP_BASELINE Drop a baseline dbms_workload_repository.drop_baseline( baseline_name IN VARCHAR2, cascade       IN BOOLEAN DEFAULT FALSE, dbid          IN NUMBER  DEFAULT NULL); Cascade False Drop baseline but not snapshots True Drops baseline and snapshots SELECT baseline_name, dbid FROM dba_hist_baseline; exec dbms_workload_repository.drop_baseline('UW_BASE', FALSE, 1692970157); SELECT baseline_name, dbid FROM dba_hist_baseline;   DROP_BASELINE_TEMPLATE (new 11g) Drops a Baseline Template dbms_workload_repository.drop_baseline_template( template_name IN VARCHAR2, dbid          IN NUMBER DEFAULT NULL); SELECT dbid, template_id, template_name, template_type FROM dba_hist_baseline_template; exec dbms_workload_repository.drop_baseline_template('UW_TEMPLATE'); SELECT dbid, template_id, template_name, template_type FROM dba_hist_baseline_template;   DROP_SNAPSHOT_RANGE Drop a range of snapshots dbms_workload_repository.drop_snapshot_Range( low_snap_id  IN NUMBER, high_snap_id IN NUMBER dbid         IN NUMBER DEFAULT NULL); set linesize 121 col startup_time format a40 SELECT snap_id, startup_time FROM dba_hist_snapshot ORDER BY 1,2; exec dbms_workload_repository.drop_snapshot_range(1105, 1199); SELECT snap_id, startup_time FROM dba_hist_snapshot ORDER BY 1,2;   MODIFY_BASELINE_WINDOW_SIZE (new 11g) Modifies the window size for the default moving window baseline Installation default is 8 days dbms_workload_repository.modify_baseline_window_size( window_size IN NUMBER, dbid        IN NUMBER DEFAULT NULL ); set linesize 121 col baseline_name format a30 SELECT dbid, baseline_name, baseline_type, moving_window_size FROM dba_hist_baseline; exec dbms_workload_repository.modify_baseline_window_size(5); SELECT dbid, baseline_name, baseline_type, moving_window_size FROM dba_hist_baseline; exec dbms_workload_repository.modify_baseline_window_size(8);   MODIFY_SNAPSHOT_SETTINGS Modifies the interval between snapshots and/or the retention of snapshots in the repository Overload 1 dbms_workload_repository.modify_snapshot_settings( retention IN NUMBER DEFAULT NULL, interval  IN NUMBER DEFAULT NULL, topnsql   IN NUMBER DEFAULT NULL, dbid      IN NUMBER DEFAULT NULL); Defaults Retention 7 days = 10080 minutes Interval 60 minutes * * Reset to 15-30 min. maximum between snapshots set linesize 121 col retention format a20 col snap_interval format a20 SELECT retention, snap_interval, topnsql FROM wrm$_wr_control; SELECT dbid FROM gv$database; exec dbms_workload_repository.modify_snapshot_settings(14400, 20, 1000, 1692970157); SELECT retention, snap_interval, topnsql FROM wrm$_wr_control; Overload 2 dbms_workload_repository.modify_snapshot_settings( retention IN NUMBER   DEFAULT NULL, interval  IN NUMBER   DEFAULT NULL, topnsql   IN VARCHAR2 DEFAULT NULL, dbid      IN NUMBER   DEFAULT NULL); Defaults Retention 7 days = 10080 minutes Interval 60 minutes * * Reset to 15-30 min. maximum between snapshots set linesize 121 col retention format a20 col snap_interval format a20 SELECT retention, snap_interval, topnsql FROM wrm$_wr_control; SELECT dbid FROM gv$database; exec dbms_workload_repository.modify_snapshot_settings(14400, 20, '1001', 1692970157); SELECT retention, snap_interval, topnsql FROM wrm$_wr_control;   REMOVE_COLORED_SQL (new 11g) Routine to remove a colored SQL ID. If an SQL ID is colored, it will always be captured in every snapshot, independent of its level of activities (i.e. does not have to be a TOP SQL). dbms_workload_repository.remove_colored_sql( sql_id IN VARCHAR2, dbid   IN NUMBER DEFAULT NULL ); desc wrm$_colored_sql SELECT * FROM wrm$_colored_sql; exec dbms_workload_repository.remove_colored_sql('5rygsj4dbw6jt', 1692970157); SELECT * FROM wrm$_colored_sql;   RENAME_BASELINE (new 11g) Rename a baseline dbms_workload_repository.rename_baseline( old_baseline_name IN VARCHAR2, new_baseline_name IN VARCHAR2, dbid              IN NUMBER DEFAULT NULL); SELECT dbid, baseline_name, baseline_type FROM dba_hist_baseline; exec dbms_workload_repository.rename_baseline('UW_BASE', 'UW_BASE2'); SELECT dbid, baseline_name, baseline_type FROM dba_hist_baseline; exec dbms_workload_repository.rename_baseline('UW_BASE', 'UW_BASE');   SELECT_BASELINE_DETAILS (new 11g) Display baseline statistics dbms_workload_repository.select_baseline_metrics( l_baseline_id IN NUMBER, l_beg_snap IN NUMBER DEFAULT NULL, l_end_snap IN NUMBER DEFAULT NULL, l_dbid     IN NUMBER DEFAULT NULL) RETURN awrbl_details_type_table PIPELINED; SELECT dbid, baseline_id, baseline_name, baseline_type FROM dba_hist_baseline; set linesize 121 col start_snap_time format a30 col end_snap_time format a30 SELECT * FROM TABLE(dbms_workload_repository.select_baseline_details(1));   SELECT_BASELINE_METRIC (new 11g) Display metric stats for a baseline dbms_workload_repository.select_baseline_metric( l_baseline_name IN VARCHAR2, l_dbid          IN NUMBER DEFAULT NULL, l_instance_num  IN NUMBER DEFAULT NULL) RETURN awrbl_metric_type_table PIPELINED; SELECT dbid, baseline_id, baseline_name, baseline_type FROM dba_hist_baseline; set pagesize 0 set linesize 121 SELECT * FROM TABLE(dbms_workload_repository.select_baseline_metric(0));

2、如何找到消耗資源最多的sql語句

-- 邏輯讀多的SQL

select * from (select buffer_gets, sql_text

from v$sqlarea

where buffer_gets > 500000

order by buffer_gets desc) where rownum<=30;

-- 執行次數多的SQL   

  select sql_text,executions from

  (select sql_text,executions from v$sqlarea order by executions desc)

   where rownum<81;

-- 讀硬盤多的SQL 

  select sql_text,disk_reads from

  (select sql_text,disk_reads from v$sqlarea order by disk_reads desc)

   where rownum<21;    

--根據操作系統進程找sql語句

根據進程id找sid

select sid from v$session

  where paddr in ( select addr from v$process where spid=&pid) ;  

根據sid找sql語句

select SQL_TEXT 

  from  V$SQLTEXT

  where HASH_VALUE

      =  ( select SQL_HASH_VALUE  from v$session

              where sid = &sid)

  order by PIECE;