查询优化器统计信息
搜集统计信息：
不是实时的：
SQL> conn /as sysdba
Connected.
SQL> grant select on dba_objects to scott;
Grant succeeded.
SQL> conn scott/tiger
Connected.
SQL> create table t as select * from dba_objects;
Table created.
SQL> select NUM_ROWS,BLOCKS from user_tables where table_name='T';
NUM_ROWS BLOCKS
---------- ----------
统计信息不会实时的写入数据字典
SQL> exec dbms_stats.gather_table_stats('scott','t');
PL/SQL procedure successfully completed.
SQL> select NUM_ROWS,BLOCKS from user_tables where table_name='T';
NUM_ROWS BLOCKS
---------- ----------
50401 713
1.通过手动收集
2.自动收集：通过数据库作业：Administration->Jobs->GATHER_STATS_JOB
正在审核的统计信息
如何使SCOTT模式中E表的统计信息处于未发布状态：
新创建表e：
create table e as select * from emp;
e表创建后，统计信息没有搜集，因此，我们看不到统计信息：
select num_rows, to_char(last_analyzed,'mm/dd/yy hh24:mi:ss') from user_tables where table_name = 'E';
NUM_ROWS TO_CHAR(LAST_ANALYZ
---------- -------------------
将e表的统计信息发布选项设置为false
begin
dbms_stats.set_table_prefs (
ownname => 'SCOTT',
tabname => 'E',
pname => 'PUBLISH',
pvalue => 'FALSE'
);
end;
一旦您将发布首选项设置为false，收集的统计信息就将被标记为正在审核，下面是现在该表的统计信息：
收集统计信息：
begin
dbms_stats.gather_table_stats (
ownname => 'SCOTT',
tabname => 'E',
estimate_percent=> 100
);
end;
/
查看统计信息：
NUM_ROWS TO_CHAR(LAST_ANAL
---------- -----------------
可以看到行数没变，同时last_analyzed 值也没变，那么，刚收集的统计信息发生了什么变化呢？统计信息被标记为正在审核
正在审核的统计信息可在视图 USER_TAB_PENDING_STATS 中看到：
select num_rows, to_char(last_analyzed,'mm/dd/yy hh24:mi:ss') from user_tab_pending_stats
where table_name = 'E';
NUM_ROWS BLOCKS TO_CHAR(LAST_ANALYZ
---------- ---------- -------------------
14 4 2013-10-24 01:17:56
现在表有14行，显示正在审核的统计信息，同时显示有收集时间，想让优化程序看到这些正在审核的统计信息，您可以发布它们：
begin
dbms_stats.publish_pending_stats('SCOTT', 'E');
end;
/
检查视图user_tab_pending_stats，会发现它现在是空的
select num_rows, to_char(last_analyzed,'mm/dd/yy hh24:mi:ss') from user_tab_pending_stats
where table_name = 'E';
o rows selected
如果现在检查USER_TABLES，会看到最新的统计信息：
select num_rows,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') from user_tables where table_name='E';
NUM_ROWS TO_CHAR(LAST_ANALYZ
---------- -------------------
14 2013-10-24 01:17:56
AWR
1.AWR的定义：Automatic Workload Repository（自动工作量资料档案库 ）
AWR中存的是快照，快照是由MMON（Memory Monitor ）进程生成，MMON进程每隔一段时间会
收集数据库的统计信息并且根据统计信息自动生成大量的指标，并将统计信息以及指标写入到磁盘中这
个过程就是对数据库执行一次快照
2.AWR的作用：AWR提供了收集数据库的诊断信息，对潜在问题进行分析以及进行自我调整的功能
3.AWR的管理：创建，删除，配置
3.1. 查看当前的AWR保存策略
col SNAP_INTERVAL format a20
col RETENTION format a20
select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
262089084 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT
以上结果表示,每小时产生一个SNAPSHOT，保留7天
3.2. 调整AWR配置
AWR配置都是通过dbms_workload_repository包进行配置
3.2.1 调整AWR产生snapshot的频率和保留策略，如将收集间隔时间改为30分钟一次
并且保留5天时间（单位都是分钟）：
begin
dbms_workload_repository.modify_snapshot_settings(interval=>60, retention=>7*24*60);
end;
/
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
1318209987 +00000 00:30:00.0 +00005 00:00:00.0 DEFAULT
3.2.2 关闭AWR，把interval设为0则关闭自动捕捉快照
begin
dbms_workload_repository.modify_snapshot_settings(interval=>0);
end;
/
3.2.3 手工创建一个快照
begin
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
end;
/
3.2.4 查看快照
col SNAP_ID for 99
col STARTUP_TIME for a25
col BEGIN_INTERVAL_TIME for a25
col END_INTERVAL_TIME for a25
col SNAP_LEVEL for 9
set linesize 1000
set pagesize 100
select SNAP_ID,STARTUP_TIME,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME,SNAP_LEVEL from DBA_HIST_SNAPSHOT
order by 1;
3.2.5 手工删除指定范围的快照
begin
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id =>38, high_snap_id =>42);
end;
/
mmon收集统计信息的三个级别：
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
basic:自动优化功能基本关闭
tipical:默认值
all:用于手动诊断sql的其他信息
BASELINE
基线：
是一组已经存储的统计信息和指标，可用于跨越时间进行对比
当MMON进程保存AWR快照时，会根据统计信息生成大量的指标，这些都自动生成，而基线的生成必须有DBA完成
过一段时间会清除快照，默认是7天后清除，基准是快照的一个或多个对，将一直保存到专门删除为止，可将从基线
派生的指标与当前活动级别派生的指标进行比较，从而帮助确定活动和行为中的更改
创建基线：
execute dbms_workload_repository.create_baseline(start_snap_id=>18,end_snap_id=>19,baseline_name=>'pridapm');
ADDM
实验：生成ADDM（Automatic Database Diagnostic Monitor ）报告
conn system/a
设置ADDM的敏感度
begin
dbms_advisor.set_default_task_parameter('ADDM','DB_ACTIVITY_MIN',30);
end;
/
创建AWR快照：
execute dbms_workload_repository.create_snapshot;
通过创建表并运行匿名PL/SQL块来生成一些活动来模拟工作负荷：
create table tmptab as select * from all_objects;
begin
for i in 1..10 loop
insert into tmptab select * from all_objects;
delete tmptab;
end loop;
commit;
end;
再次创建快照：
execute dbms_workload_repository.create_snapshot;
使用database control，以用户system登录到数据库
在数据库主页上，单击related links区域中的advistor central连接，列出的第一个报告是
作为快照结果生成的ADDM报告
选择最新ADDM报告对应的单选按钮，并单击view result按钮。对报告进行研究：它将显示近几
分钟的活动高峰，下面有多个发现。单击发现，可看到活动性质以及导致活动的语句。
删除此实验表：
drop table tmptab purge;
ADVISOR
使用顾问程序体系结构
顾问程序：
实验：使用STA进行sql优化
使用oracle提供的程序包进行sql优化：
conn /as sysdba
grant ADVISOR to scott;
conn scott/tiger
create table manual_sta(id varchar2(10),name varchar2(128));
insert into manual_sta select object_id,object_name from dba_objects;
commit;
在id列上创建一个索引，并收集统计信息。
create index idx_manual_sta on manual_sta(id);
exec dbms_stats.gather_table_stats(user,'manual_sta',cascade=>true);
调用STA对SQL语句进行调优:
declare
l_task_id varchar2(20);
l_sql varchar2(2000);
begin
l_sql := 'select * from manual_sta where id=2000';
l_task_id := dbms_sqltune.create_tuning_task(
sql_text=>l_sql,
user_name=>'SCOTT',
scope=>'COMPREHENSIVE',
time_limit=>30,
task_name=>'MANUAL_STA'
);
end;
执行sql调优任务
begin
dbms_sqltune.execute_tuning_task('MANUAL_STA');
end;
显示调优结果
set serveroutput on size 999999
set long 99999999
select dbms_sqltune.report_tuning_task('MANUAL_STA') from dual;
显示结果中会建议建一个函数索引
表空间告警
1.安装hr用户
检查hr是否存在，如果不存在，如果不存在先创建hr
2.将表空间的空间阈值定义成默认值，如：
exec DBMS_SERVER_ALERT.SET_THRESHOLD(-
dbms_server_alert.tablespace_pct_full,-
NULL,NULL,NULL,NULL,1,1,NULL,-
dbms_server_alert.object_type_tablespace,NULL);
3. 创建实验表空间tbsalert，大小为120MB，文件名为alert1.dbf，不要设置自动扩展,不要为表空间设置阈值：
create tablespace tbsalert datafile '/u01/app/oracle/oradata/orcl/alert1.dbf' size 120M;
4.改变表空间tbsalert的告警阈值，55%为普通警告，70%为严重警告：
begin
DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
DBMS_SERVER_ALERT.OPERATOR_GE,
'55',
DBMS_SERVER_ALERT.OPERATOR_GE,
'70',
1,
1,
null,
DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
'TBSALERT');
end;
/
5.检查新的阈值：
select warning_value,critical_value
from dba_thresholds
where metrics_name='Tablespace Space Usage' and
object_name='TBSALERT';
6.从数据字典dba_alert_history中查询表空间tbsalert的reason和resolution：
select reason,resolution
from dba_alert_history
where object_name='TBSALERT';
REASON RESOLUT
-------------------------------------------------------- -------
Threshold is updated on metrics "Tablespace Space Usage" cleared
7.创建测试环境，在表空间tbsalert上创建新表，如：
conn hr/hr
create table employees1 tablespace tbsalert as select * from hr.employees;
create table employees2 tablespace tbsalert as select * from hr.employees;
create table employees3 tablespace tbsalert as select * from hr.employees;
create table employees4 tablespace tbsalert as select * from hr.employees;
create table employees5 tablespace tbsalert as select * from hr.employees;
alter table employees1 enable row movement;
alter table employees2 enable row movement;
alter table employees3 enable row movement;
BEGIN
FOR i in 1..10 LOOP
insert into employees1 select * from employees1;
insert into employees2 select * from employees2;
insert into employees3 select * from employees3;
insert into employees4 select * from employees4;
insert into employees5 select * from employees5;
commit;
END LOOP;
END;
/
insert into employees1 select * from employees1;
insert into employees2 select * from employees2;
insert into employees3 select * from employees3;
commit;
8.检查表空间tbsalert使用空间比例：
select sum(bytes) * 100 / 125829120
from dba_extents
where tablespace_name = 'TBSALERT';
SUM(BYTES)*100/125829120
------------------------
60
等待大约10分钟，运行以下查询：
select reason from dba_outstanding_alerts where object_name = 'TBSALERT';
REASON
--------------------------------------------------------
Tablespace [TBSALERT] is [60 percent] full
9.往表空间tbsalert增加数据：
insert into employees4 select * from employees4;
commit;
insert into employees5 select * from employees5;
commit;
查看表空间tbsalert的空间比例：
select sum(bytes) * 100 / 125829120
from dba_extents
where tablespace_name = 'TBSALERT';
SUM(BYTES)*100/125829120
------------------------
75
等待大约10分钟，执行查询：
select reason
from dba_outstanding_alerts
where object_name = 'TBSALERT';
REASON
--------------------------------------------------------
Tablespace [TBSALERT] is [75 percent] full
10. 减少表空间tbsalert的空间大小：
delete employees1 where department_id=50;
commit;
delete employees2 where department_id=50;
commit;
delete employees3 where department_id=50;
commit;
11. 对表employees1，employees2，employees3进行空间收缩：
alter table employees1 shrink space;
alter table employees2 shrink space;
alter table employees3 shrink space;
查看表空间的空间比例：
select sum(bytes) * 100 / 125829120
from dba_extents
where tablespace_name = 'TBSALERT';
SUM(BYTES)*100/125829120
------------------------
53.90625
12. 等待大约10分钟，执行如下查询：
select reason, message_level
from dba_outstanding_alerts
where object_name = 'TBSALERT';
no rows selected
发现没有数据，空间问题解决之后，告警自动消失，进入告警历史：
select OBJECT_NAME, OBJECT_TYPE, REASON
from dba_alert_history
where OBJECT_NAME='TBSALERT';
OBJECT_NAME OBJECT_TYPE REASON
----------- ----------- --------------------------------------------------------
TBSALERT TABLESPACE Threshold is updated on metrics "Tablespace Space Usage"
TBSALERT TABLESPACE Tablespace [TBSALERT] is [53 percent] full
13. 将表空间tbsalert的空间阈值设置为数据库默认的：
begin
DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
null,
null,
null,
null,
null,
null,
null,
DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
'TBSALERT');
end;
/
select warning_value, critical_value
from dba_thresholds
where metrics_name = 'Tablespace Space Usage'
and object_name = 'TBSALERT';
no rows selected
14. 删除表空间tbsalert：
drop tablespace tbsalert including contents and datafiles;