資料庫效能 常用SQL

season0891發表於2014-01-09
資料庫SQL

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; 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/90618/viewspace-1068785/，如需轉載，請註明出處，否則將追究法律責任。

相關文章