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

這些 SQL 語句顯示快照每小時採集一次，採集的資料保留 7 天。要修改設定 — 例如，快照時間間隔為 20 分鐘，保留時間為兩天 — 您可以發出以下命令。引數以分鐘為單位。

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;

http://www.blogjava.net/envoydada/archive/2007/12/27/170920.html

資料庫效能常用SQL

相關文章

資料庫效能 常用SQL

相關文章

資料庫效能常用SQL