数据库性能常用SQL - Derek.Guo BLOG

数据库性能常用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;

posted on 2007-12-27 16:34 Derek.Guo 阅读(1917) 评论(0) 编辑收藏所属分类: Database

留言簿(7)

随笔分类(83)

文章分类

技术网站

积分与排名

最新随笔

最新评论

阅读排行榜


只有注册用户登录后才能发表评论。




网站导航: 博客园 IT新闻 Chat2DB C++博客博问管理
相关文章: mysql常用的hint Mysql innodb引擎优化 Oracle 10g Recycle Bin Oracle中分区表的使用 ORACLE CTXCAT-CATSEARCH ORACLE JOBS Oracle 分区表(Partition) 数据库性能常用SQL SQL SERVER事务处理