|
在11g，oracle提供dbms_spm包来管理SQL Plan，SPM是一个预防机制，它记录并评估sql的执行计划，将已知的高效的sql执行计划建立为SQL Plan Baselines，SQL Plan Baseline的功能是保持SQL的性能而不必关注系统的改变。
1 Capturing SQL Plan Baselines
在SQL Plan BaseLines捕获阶段，Oracle记录SQL的执行计划并检测该执行计划是否已经改变，如果SQL改变后的执行计划是安全的，则SQL就使用新的执行计划，因此，Oracle维护单个SQL执行计划的历史信息，Oracle维护的SQL执行计划的历史仅仅针对重复执行的SQL，SQL Plan Baseline可以手工load，也可以设置为自动捕获。
A Automatic Plan Capture
如果要激活自动的SQL Plan Capture，则需要设置OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES，该参数默认为False，如果设置为True，则表示海底捞月活自动捕获SQL Plan，则系统会自动创建并维护SQL Plan History，SQL Plan History包括优化器关注的：比如an execution plan, SQL text, outline, bind variables, and compilation environment。
B Manual Plan Loading
也可以手动装载一个存在的SQL Plan作为SQL Plan Baseline，手动装装的SQL Plan并不校验它的性能：
--从SQL Tuning Set中装载:
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'tset1');
END;
/
--从Cursor Cache中装载
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '7qqnad1j615m7');
END;
/
2 Selecting SQL Plan Baselines
在SQL Plan选择阶段，SQL每一次编绎，优化器使用基于成本的方式，建立一下best-cost的执行计划，然后去匹配SQL Plan Baselines中的SQL Plan，如果找到了匹配的SQL Plan，则会使用这个执行计划，如果没有找到匹配的SQL Plan,优化器就会去SQL Plan History中去搜索成本最低的SQL Plan，如果优化器在SQL Plan History中找不到任务匹配的SQL Plan，则该SQL Plan被作为一个Non-Accept Plan被存入SQL Plan History，新的SQL Plan直到它被验证不会引起一下性能问题才会被使用。
--如何激活使用SQL Plan Baselins
SQL> alter system set OPTIMIZER_USE_SQL_PLAN_BASELINES = true;
3 Evolving SQL Plan Baselines
在SQL Plan Baselines的演变阶段，Oracle评估新的Plan的性能并将性能较好的Plan存放SQL Plan Baselines中，可以使用dbms_spm package的过程EVOLVE_SQL_PLAN_BASELINE将新的SQL Plan存入已经存在的SQL Plan Baselines中，新的Plan将会作为已经Accept Plan加入到SQL Plan Baselines中。
SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => 'SYS_SQL_593bc74fca8e6738');
DBMS_OUTPUT.PUT_LINE(report);
END;
/
4 相关的数据字典
dba_sqlset_plans
dba_advisor_sqlplans
dba_sql_plan_baselines
5 用dbms_xplan显示SQL Plan
在11g中，Oracle增强了dbms_xplan包的显示功能，不仅可以显示单个sql的执行计划，还可以用来显示sql tuning set，以及sql plan baselines等，在此就仅仅对显示sql plan baselines做测试：
select *
from table(dbms_xplan.display_sql_plan_baseline(sql_handle => '&SYS_SQL_',
| 11g新特性:Pending Statistics |
作者 xzh2000 16:30 |  静态链接网址 |  最新回复 (0) |  引用 (0) | 技术交流 |
|
从11g开始，表与索引的统计信息收集完毕后，可以选择收集的统信息立即发布，也可以选择使新收集的统计信息处于pending状态，待确定处于pending状态的统计信息是安全的，再使处于pending状态的统计信息发布，这样就会避免一些因为收集统计信息立即发布而导致SQL执行计划走错的灾难。
1 如何判断是否有pending的统计信息需要生效？
SQL> Select dbms_stats.get_prefs('PUBLISH') publish from dual;
PUBLISH
--------------------------
TRUE
dbms_stats的get_prefs函数返回true，表示对象的统计信息收集后立即生效，如果返回flase，收集的统计信息将处于pending状态。
2 如果查看相关的视图
A 立即生效的统计信息可以通过以下字典可以查看
user_tab_stats
user_ind_stats
B pending状态的统计信息可以通过以下字典可以查看
user_tab_pending_stats
user_ind_pending_stats
3 如何设置表或schema的统计信息的publish状态
用dbms_stats的set_table_prefs或者set_schema_prefs过程可以在表级或schema表设置它们的统计信息是否立即生效，当我们设置tmp_test表的统计信息收集后处于pending状态，那该表收集统计信息后，将存放于user_tab_pending_stats字典中。
SQL> Exec dbms_stats.set_table_prefs('yekai','tmp_test','publish','false');
PL/SQL procedure successfully completed.
SQL> select count(*) from user_tab_pending_stats;
COUNT(*)
----------
0
SQL> exec dbms_stats.gather_table_stats('yekai','tmp_test');
PL/SQL procedure successfully completed.
SQL> select count(*) from user_tab_pending_stats;
COUNT(*)
----------
1
4 如何测试并使用处于pending状态的统计信息
在11g，新的参数optimizer_pending_statistics将可以来解决这个问题，当我们在session级设置optimizer_pending_statistics为true时，我们就可以使用存放在user_*_pending_stats字典中的统计信息啦，当我们确保该处于pending状态的统计信息是正确时，我们就可以决定是否使它们立即生效。
SQL> alter session set optimizer_pending_statistics = TRUE;
5 如何发布处于pending状态的统计信息
当测试过统计信息有效后，我们可以选择发布pending状态的统计信息
SQL> exec dbms_stats.publish_pending_stats('yekai','tmp_test');
如果我们不需要该处于pending状态的统计信息，可以选择删除这个pending的统计信息
SQL> exec dbms_stats.publish_pending_stats('yekai','tmp_test');
| 11g新特性:Extended Statistics Collect |
|
|
在11g中，dbms_stats package提供了组合列的统计信息的收集，如果在where条件中使用了组合列进行查询，则优化器将会得到更准确的统计信息，进而输出执行计划时，可以输出更接近与真实数据的统计结果，11g的Extended Statistics包含了组合列与表达式统计信息的收集，表过式统计信息收集主要是针对函数索引字段，下面就组合列统计信息的收集做一下测试：
SQL> create index idx_object_objtype on tmp_objects(object_type,owner) compute statistics;
Index created.
SQL> select index_name,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,CLUSTERING_FACTOR,NUM_ROWS
from user_indexes
where index_name='IDX_OBJECT_OBJTYPE';
INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
------------------------------ ----------- ------------- ----------------- ----------
IDX_OBJECT_OBJTYPE 2 11240 203 208057 3189632
SQL>select count(*)
from tmp_objects
where owner='SYSTEM' and object_type='TABLE';
--------------------------------------------------------------------------------------
| Id | Operation |Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 58 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
|* 2 | INDEX RANGE SCAN|IDX_OBJECT_OBJTYPE| 15712 | 230K| 58 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE' AND "OWNER"='SYSTEM')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
33 consistent gets
0 physical reads
0 redo size
SQL> declare
v_name varchar2(30);
begin
v_name := dbms_stats.create_extended_stats('yekai','tmp_objects','(object_type,owner)');
end;
SQL> select dbms_stats.show_extended_stats_name('yekai','tmp_objects','(object_type,owner)') as ex_name
from dual;
EX_NAME
---------------------------------
SYS_STU_UCG1E7VH65UFFJ55F00#FU
SQL> select *
from user_stat_extensions
where table_name='TMP_OBJECTS'
TABLE_NAME EXTENSION_NAME EXTENSION CREATOR DROPPA
------------ -------------------------------- -------------------------------- ------------ ------
TMP_OBJECTS SYS_STU_UCG1E7VH65UFFJ55F00#FU ("OBJECT_TYPE","OWNER") USER YES
SQL> BEGIN
dbms_stats.gather_table_stats(
ownname => 'yekai',
tabname => 'tmp_objects',
method_opt => 'for all columns size skewonly for columns (cust_state_province,country_id) skewonly');
END;
SQL> select e.extension col_group, t.num_distinct, t.histogram
from user_stat_extensions e, user_tab_col_statistics t
where e.extension_name = t.column_name and t.table_name = 'TMP_OBJECTS';
COL_GROUP NUM_DISTINCT HISTOGRAM
------------------------- ------------ ------------------
("OBJECT_TYPE","OWNER") 203 FREQUENCY
SQL>select count(*)
from tmp_objects
where owner='SYSTEM' and object_type='TABLE';
--------------------------------------------------------------------------------------
| Id | Operation |Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 44 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
|* 2 | INDEX RANGE SCAN|IDX_OBJECT_OBJTYPE| 11673 | 170K| 44 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE' AND "OWNER"='SYSTEM')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
33 consistent gets
0 physical reads
0 redo size
说明：请大家对比收集组合列统计信息前后对tmp_objects进行查询的执行计划，你会发现组合列统计信息收集前SQL的Bytes是230k Cost是58，组合列统计信息收集后SQL的Bytes是170k，Cost是44，这就是组合列统计信息的优势。
|
| 11g新特性:Auto Optimizer Statistics Collection |
| |
|
在oracle 10g中，优化统计信息的收集是通过scheduler job来自动完成的，dba可以通过dbms_scheduler package的enable procedure与disable procedure来启动与关闭，在oracle 11g中，oracle提供了全新的package dbms_auto_task_admin来完成统计信息的收集工作，测试脚本如下所示：
启动：
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/
关闭：
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
| 11g新特性:常驻连接池DRCP之一 |
|
|
在oracle中，进程的频繁创建是一个比较消耗资源的开消，在11g之前，oracle提供专用器进程与共享服务器进程供用户选择适合自己的进程机制，在11g中，oracle又推出DRCP(database resident connection pool)，类似于sqlrelay那种轻量级中间件的进程机制为客户服务，DRCP适用于web application（比如php/apache），可以提供成千上万的连接，使用DRCP可以共享主机资源。
一 什么时间使用DRCP
1 使用较小内存的、大量的客户端连接
2 客户端应用是相似的，可以共享或重用会话
3 客户端占用数据库连接的周期相当短
4 会话不需要跨客户请求
5 客户端有众多的主机与进程
二 Dedicated Servers,Shared Servers与DRCP的区别
|
| 11g新特性:常驻连接池DRCP之二 |
| |
|
三Dedicated Servers,Shared Servers与DRCP的内存需求
一般情况下，由于每个会话需要消耗400k的内存，每个进程需要消耗4m的内存，现在我们以DRCP的pool size是100，shared server的shared server进程是100为例，假如有5000个客户端连接到这些环境，则这些主机的内存分配如下：
A Dedicated Server
Memory used = 5000 X (400 KB + 4 MB) = 22 GB
B Shared Server
Memory used = 5000 X 400 KB + 100 X 4 MB = 2.5 GB
Out of the 2.5 GB, 2 GB is allocated from the SGA.
C Database Resident Connection Pooling
Memory used = 100 X (400 KB + 4 MB) + (5000 X 35KB)= 615 MB
四 使用DRCP时，当实例有活动pooled server，有以下限制：
1 不能shutdown database;
2 不能停掉DRCP;
3 不能用database link连接到不同实例的DRCP;
4 不能使用Advanced Security Option (ASO)，比如encryption等 | |
| 11g新特性:常驻连接池DRCP之三 |
|
|
五 如何在DB中配置DRCP？
在11g中，系统默认包含了一个连接池：SYS_DEFAULT_CONNECTION_POOL，这个池默认被创建，但默认该池是不启动的，如果要启动默认的连接池，必须显式地执行启动DRCP的命令，oracle提供dbms_connection_pool package来执行这个工作，如果连接池被显式地启动，必须显式地被停掉，当实例宕掉时，如果DRCP是活动的，则实例启动时，DRCP也将自动实动。
启动DRCP需要以下步骤：
A 用sysdba权限连接到sqlplus
B 执行exec dbms_connection_pool.start_pool();
C 查看DRCP的状态
六 客户端如何连接到DRCP？
如果是专用服务器连接，则SERVER=DEDICATED，如果是DRCP连接，则SEVER=POOLED。如果在tnsnames.ora中指定了SERVER=POOLED，但并没有在实例中启动DRCP，则当客户请求连接时，DB会报ORA-12520错误。如果要指定客户端请求到DRCP，则客户端的tnsnames.ora中的连接字符串必须指定连接类型是POOLED，配置方式如下所示：
dhcp11g =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.5.26)(PORT = 1521))
(CONNECT_DATA =
(SERVER = POOLED)
(SERVICE_NAME = dbtest)
)
)
说明：11g的客户端才可以使用DRCP，如果10g的客户端在tnsnames.ora中指定了SERVER=POOLED，则连接时报ORA-56606。
六 如何在DB中掉停DRCP
A 用sysdba权限连接到sqlplus
B 执行exec dbms_connection_pool.stop_pool();
七 如何调整DRCP的配置？
DRCP启动时使用的是默认配置参数，如果要调整DRCP的配置参数，需要调用dbms_connection_pool包的configure_pool过程去调整DHCP的高级选项，也可以调用dbms_connection_pool包的alter_param过程只仅改指定的参数而不影响其它的参数，DRCP可调整的参数可以查看dba_cpool_info表，每个参数的具体含义请自己查阅administrator guide手册的p128/129页，具体的语法如下所示：
SQL> exec print_table('select * from dba_cpool_info');
CONNECTION_POOL : SYS_DEFAULT_CONNECTION_POOL
STATUS : ACTIVE
MINSIZE : 4
MAXSIZE : 40
INCRSIZE : 2
SESSION_CACHED_CURSORS : 20
INACTIVITY_TIMEOUT : 300
MAX_THINK_TIME : 120
MAX_USE_SESSION : 500000
MAX_LIFETIME_SESSION : 86400
-----------------
SQL> exec dbms_connection_pool.alter_param ('','minsize','10');
PL/SQL procedure successfully completed.
SQL> exec print_table('select * from dba_cpool_info');
CONNECTION_POOL : SYS_DEFAULT_CONNECTION_POOL
STATUS : ACTIVE
MINSIZE : 10
MAXSIZE : 40
INCRSIZE : 2
SESSION_CACHED_CURSORS : 20
INACTIVITY_TIMEOUT : 300
MAX_THINK_TIME : 120
MAX_USE_SESSION : 500000
MAX_LIFETIME_SESSION : 86400
-----------------
PL/SQL procedure successfully completed.
八 如何恢复DHCP的默认选项
A 用sysdba权限连接到sqlplus
B 执行exec dbms_connection_pool.restore_defaults;
九 关于DHCP相关的字典
dba_cpool_info
v$cpool_stats
v$cpool_cc_stats
十 测试DHCP的总结
在实际的使用，如果要使用DHCP，则需要注意在tnsnames.ora中创建两个TNS连接字符串，将前台短的连接都指向DHCP连接，把后台长的连接则向DEDICATED SERVER连接，这样就可以满足一般轻量权的web application.
| 11g新特性:database replay |
|
|
在11g中，很多功能oracle都引导用户通过OEM来操作，但喜欢用API的dba，照样可以写脚本来完成自己的工作，下面给大家介绍一下database replay的配置方法，database replay可以捕捉整个数据库的负载，并且传递到需要进行测试数据库上，然后重演负载以测试系统调优后的效果，当然也可以为升级硬件进行压力测试。
A 创建目录
create directory sqlplay as /home/oracle11g/worksh/sqlplay';
B 启动捕获进程
SQL> BEGIN
2 DBMS_WORKLOAD_CAPTURE.START_CAPTURE (
3 name => 'capture_by_yekai',
4 dir => 'sqlplay',
5 duration => 600);
6 END;
7 /
BEGIN
*
ERROR at line 1:
ORA-20222: Invalid DB State or Input. Input "sqlplay" is not a valid DIRECTORY object!
ORA-06512: at "SYS.DBMS_WORKLOAD_CAPTURE", line 783
ORA-06512: at line 2
说明：这个地方dir参数必须是大写的，否则将会报错。
SQL> BEGIN
2 DBMS_WORKLOAD_CAPTURE.START_CAPTURE (
3 name => 'capture_by_yekai',
4 dir => 'SQLPLAY',
5 duration => 600);
6 END;
7 /
PL/SQL procedure successfully completed.
C 如果在启动捕获进程时，没有指定duration的话，将必须调用finish_capture过程结束捕获workload.
BEGIN
DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE();
END;
/
D 监控database replay的捕获情况
select * from DBA_WORKLOAD_CAPTURES;
E 处理捕获信息
SQL> BEGIN
2 DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE (
3 capture_dir => 'SQLPLAY');
4 END;
5 /
PL/SQL procedure successfully completed.
F 创建replay目录
create or replace directory sqlreplay as '/home/oracle/worksh/sqlplay'
说明：在初始化前，需要把捕获在sqlplay目录内的文件都拷贝到新的sqlreplay目录内
G 初始化replay
> BEGIN
2 DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY (
3 replay_name => 'replay_by_yekai',
4 replay_dir => 'SQLREPLAY');
5 END;
6 /
PL/SQL procedure successfully completed.
H 进准备重演状态
> BEGIN
2 DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY (synchronization => TRUE);
3 END;
4 /
PL/SQL procedure successfully completed.
I 启动database replay客户端
$wrc system/oracle mode=replay replaydir=./replay
I 执行重演
> BEGIN
2 DBMS_WORKLOAD_REPLAY.START_REPLAY ();
3 END;
4 /
PL/SQL procedure successfully completed.
J 结束重演
> BEGIN
2 DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY ();
3 END;
4 /
D 监控database replay的重演情况
select * from DBA_WORKLOAD_REPLAYS;
| 11g新特性:全表扫描的变化 |
| |
|
在oracle 11g以前的版本中，如果对大表进行全表扫描，通过v$session_wait可以看到wait event是：db file scattered read；在11g中，如果对大表进行全表扫描，通过v$session_wait可以看到wait event是：direct path read；也就是说，在11g中，大表全表扫描是将数据块直接读入会话的pga区域。
10g：
[oracle10g@csdba worksh]$ sh list_sql.sh
--------------------------------------------------------------------------
YEKAI(159,235) ospid=5318 hash_value=1577916882 execs=918 els_time=1.04
(TNS V1-V3) disk_reads=0 buffer_gets=43658.46
SELECT COUNT(*) FROM TMP_OBJECTS WHERE OBJECT_ID = :B1
--------------------------------------------------------------------------
|SELECT STATEMENT |----- 1577916882 ----| | | 8609 |
|SORT AGGREGATE | | 1 | 13 | |
| TABLE ACCESS FULL |TMP_OBJECTS | 483 | 6K| 8609 |
--------------------------------------------------------------------------
----------------------------alter system kill session-----------------------
alter system kill session '159,235';
[oracle10g@csdba worksh]$ sh list_sql.sh
--------------------------------------------------------------------------
YEKAI(159,235) ospid=5318 hash_value=1577916882 execs=919 els_time=1.04
(TNS V1-V3) disk_reads=0 buffer_gets=43658.52
SELECT COUNT(*) FROM TMP_OBJECTS WHERE OBJECT_ID = :B1
--------------------------------------------------------------------------
|SELECT STATEMENT |----- 1577916882 ----| | | 8609 |
|SORT AGGREGATE | | 1 | 13 | |
| TABLE ACCESS FULL |TMP_OBJECTS | 483 | 6K| 8609 |
--------------------------------------------------------------------------
----------------------------alter system kill session-----------------------
11g：
[oracle11g@csdba worksh]$ sh list_sql.sh
--------------------------------------------------------------------------
YEKAI(137,491) ospid=5324 hash_value=1577916882 execs=719 els_time=1.34
(TNS V1-V3) disk_reads=43713.11 buffer_gets=68684.45
SELECT COUNT(*) FROM TMP_OBJECTS WHERE OBJECT_ID = :B1
--------------------------------------------------------------------------
|SELECT STATEMENT |----- 1577916882 ----| | | 11936 |
|SORT AGGREGATE | | 1 | 13 | |
| TABLE ACCESS FULL |TMP_OBJECTS | 482 | 6K| 11936 |
--------------------------------------------------------------------------
----------------------------alter system kill session-----------------------
alter system kill session '137,491';
[oracle11g@csdba worksh]$ sh list_sql.sh
--------------------------------------------------------------------------
YEKAI(137,491) ospid=5324 hash_value=1577916882 execs=720 els_time=1.34
(TNS V1-V3) disk_reads=43713.2 buffer_gets=68684.58
SELECT COUNT(*) FROM TMP_OBJECTS WHERE OBJECT_ID = :B1
--------------------------------------------------------------------------
|SELECT STATEMENT |----- 1577916882 ----| | | 11936 |
|SORT AGGREGATE | | 1 | 13 | |
| TABLE ACCESS FULL |TMP_OBJECTS | 482 | 6K| 11936 |
--------------------------------------------------------------------------
----------------------------alter system kill session-----------------------
大家看测试，很明显，在11g中，大表全表扫描时数据块不经过sga而直接进pga，就会造成每次进行大表全表扫描，物理读都是很大，而在10g中，由于全表扫描的数据块在sga中已经存在，所以执行全表扫描时，它的物理读为0；这种变迁，体现了oracle在优化策略上的进步，就是假定大表频繁全表扫描这种现象，在产生库上是不常有的，通过把数据直接读入pga，进而减少了cache buffer的繁忙交换程度，提高了cache buffer的使用效率。。。
| 11g新特性:实时sql监控增强 |
|
|
在11g以前的版本,SQL的运行情况可以通过监控v$session_longops来了解,当某个操作执行时间超过6秒,就会被v$session_longops感知,通常可以监控到比如全表扫描,全索引扫描,哈希联接,并行查询等;在11g中,当sql并行运行时,马上会被real-time monitor到,当sql单进程运行时,如果运行时间超过5秒,它也会被监控到。
可以通过v$sql_monitor与v$sql_plan_monitor视图查看sql执行的统计信息，可以联合v$active_session_history，v$session，v$session_longops，v$sql, v$sql_plan等视图，查看sql更多的信息。v$sql_monitor收集关键的一些指标，比如：elapsed time, CPU time, number of reads and writes, I/O wait time and various other wait times等，这些信息是每秒刷新一次，当sql执行完比，并不会立即把它从v$sql_monitor中删除，至少保留1分钟，real-time sql monitor也包括收集sql执行计划的统计信息，可以通过v$sql_plan_monitor视图来查看被监控sql的执行计划，这些统计数据也是每秒更新一次，当sql执行完结，它们至少被保留1分钟。
如何生成sql监控报表：
方法一：
variable my_rept CLOB;
BEGIN
:my_rept :=DBMS_SQLTUNE.REPORT_SQL_MONITOR();
END;
/
print :my_rept
方法二：
set long 10000000
set longchunksize 10000000
set linesize 200
select dbms_sqltune.report_sql_monitor from dual;
如何激活或禁止real-time sql monitor?
real-time sql monitor需要statistics_level参数等于all或typical，且CONTROL_MANAGEMENT_PACK_ACCESS参数必须是DIAGNOSTIC+TUNING（默认就是如此），还有两个语句级的hints可以激活或禁止real-time sql monitor:/*+ monitor */与/*+ no_monitor */，这两个参数也必须在CONTROL_MANAGEMENT_PACK_ACCESS参数是DIAGNOSTIC+TUNING下才生效，案例：
强制sql使用实时监控：
select /*+ monitor */ count(*) from test where title = 'abc';
取消sql使用实时监控：
select /*+ no_monitor */ count(*) from test where title = 'abc';
> set long 10000000
> set longchunksize 10000000
> set linesize 200
> select dbms_sqltune.report_sql_monitor from dual;
REPORT_SQL_MONITOR
--------------------------------------------------------------
SQL Monitoring Report
SQL Text
--------------------------------------------------------------
SELECT COUNT(*) FROM TEST WHERE OBJECT_ID = :B1
--------------------------------------------------------------
Global Information
Status : DONE (ALL ROWS)
Instance ID : 1
Session ID : 122
SQL ID : 2ywfyn7r0ywky
SQL Execution ID : 16777216
Plan Hash Value : 1950795681
Execution Started : 08/16/2007 15:48:24
First Refresh Time : 08/16/2007 15:48:28
Last Refresh Time : 08/16/2007 15:48:30
--------------------------------------------------------------------
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Reads |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | |
--------------------------------------------------------------------
| 4.30 | 1.36 | 0.01 | 2.93 | 1 | 94869 | 94613 |
--------------------------------------------------------------------
SQL Plan Monitoring Details
==================================================================
| Id | Operation | Name | Rows | Cost | Time |
| | | | (Estim) | | Active(s) |
==================================================================
| 0 | SELECT STATEMENT | | | 35310 | 1 |
| 1 | SORT AGGREGATE | | 1 | | 1 |
| 2 | TABLE ACCESS FULL | TEST | 126 | 35310 | 5 |
==================================================================
接上表:
===========================================================
Start | Starts | Rows | Activity | Activity Detail |
Active | | (Actual) | (percent) | (sample #) |
===========================================================
+6 | 1 | 1 | | |
+6 | 1 | 1 | | |
+2 | 1 | 0 | 100.00 | Cpu (5) |
=========================================================== |
11g在awr report方面，还是增加了一些吸引眼球的地方，比如增加了对OS等主机配置的报告，对于dba来讲，在接手一个新环境时,或做现场服务时，该报表将会简少一些额外的工作，比如报表中已经可以显示主机名，操作系统，CPU个数，物理内存等，最让人关注的还是增加了主机负载的一些情况，具体变化请看下文：
主机方面:
Host Name Platform CPUs Cores Sockets Memory(GB)
----------- ------------------ ---- ----- ------- ----------
log51 Linux IA (32-bit) 4 2 2 3.96
LOAD PROFILE:
Load Profile Per Second Per Trans Per Exec Per Call
~~~~~~~~~~~~ ----------- ---------- --------- --------
DB Time(s): 1.0 6.5 0.11 0.29
DB CPU(s): 1.0 6.4 0.11 0.29
Redo size: 1,059.2 6,813.2
Logical reads: 73,493.3 472,724.2
Block changes: 5.7 36.5
Physical reads: 73,465.3 472,544.3
Physical writes: 0.6 3.7
User calls: 3.4 22.1
Parses: 2.1 13.3
Hard parses: 0.0 0.0
W/A MB processed: 9,217.0 59,285.9
Logons: 0.0 0.1
Executes: 8.9 57.3
Rollbacks: 0.1 0.4
Transactions: 0.2
说明:在load profile section，增加了DB Time(s)，DB CPU(s)，W/A MB processed，Rollbacks等，当然也去掉了sorts的统计信息，如果想看memery sort与disk sort等情况，需要在Instance Efficiency Percentages部分来观察,Instance Efficiency Percentages section没有原化。
HOST CPU & LOAD AVG:
Host CPU (CPUs: 4 Cores: 2 Sockets: 2)
~~~~~~~~ Load Average
Begin End %User %System %WIO %Idle
--------- --------- --------- --------- --------- ---------
1.01 1.20 14.2 11.6 0.1 74.2
Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 25.1
% of busy CPU for Instance: 97.2
%DB time waiting for CPU - Resource Mgr: 0.0
Memory Statistics
~~~~~~~~~~~~~~~~~ Begin End
Host Mem (MB): 4,054.0 4,054.0
SGA use (MB): 600.0 600.0
PGA use (MB): 126.6 126.8
% Host Mem used for SGA+PGA: 17.92 17.92
Operating System Statistics - Detail Snaps: 137-138
Snap Time Load %busy %user %sys %idle %iowait
--------------- -------- -------- -------- -------- -------- --------
16-Aug 08:00:35 1.0 N/A N/A N/A N/A N/A
16-Aug 09:00:37 1.2 25.8 14.2 11.6 0.1 74.2
-------------------------------------------------------------
关于TOP SQL部分,主要按以下方面进行排序:
SQL ordered by Elapsed Time
SQL ordered by CPU Time
SQL ordered by Gets
SQL ordered by Reads
SQL ordered by Executions
SQL ordered by Parse Calls
SQL ordered by Sharable Memory
SQL ordered by Version Count
| 11g新特性:增强的add column |
|
|
在11g以前的版本中,如果表上存在未提交的事务,则对表进行DDL操作将失败,在11g中,这个限制已经被取消啦,执行ADD COLUMN操作时,并不会受未提交事务的影响,另外,ADD COLUMN还有一个增强,就是在一个非常大的表上执行ADD COLUMN xxx DATATYPE DEFAULT yyy NOT NULL时,它不会再去将默认值更新到表中已经存在的记录,这样改进的好处是非常大的,DBA对表结构的变更将更加容易,而且不会产生大量的library cache lock/pin等,更不会阻塞事务,它是怎么做到的呢?
其实现方法很简单,ORACLE引擎仅仅依赖数据字典中记录的字段的默认值来对新增字段中为空的数据进行解释,比如在test表有1000万的记录，执行alter table TEST add test_add_column number default 1 not null操作，它不会将默认值更新到存在的1000万条记录，当我们进行查询select count(*) from test where test_add_column = 1时，ORACLE引擎扫描到test_add_column存在大量为NULL的记录时,它根据数据字典中记录的test_add_column字段的默认值,将NULL解释为1...
| 11g stream新特性列表 |
|
|
从最初学习9iR2的stream，以及10gR1，10gR2的stream，到现在的11gR1，可以说oracle在stream上面压了很大的成钱，stream也越来越得到用户的接受，在11gR1中，stream比之前的版本也有较大的改进，比如同步捕获，合并的捕获与应用进程等，以下就简单地列下新增的功能点，感兴趣的可以自己看文档。。。
1 同步捕获
2 支持xmltype类型的字段
3 数据加密
4 分割与合并复制目标
5 跟踪LCRs的被处理过程
6 比较并聚合共享数据库对象
7 当stream异常时OEM自动报警
8 stream性能顾问
9 stream job使用oracle scheduler
10 通知服务改善
11 合并捕获与应用进程的优化
12 新的错误消息代码
| 11g新特性：DDL锁超时 |
|
|
在11g中，oracle提供了一个新的可动态调整的参数ddl_lock_timeout，该参数允许存在未提交的事务的表上，执行DDL操作，如果事务在ddl_lock_timeout允许的时间内提交，则DDL操作将会成功，否则就会报ORA-00054错误，如果对该表执行ADD COLUMN操作，则会直接提交表上发生的未提交的事务，所以用11g时，有些东西还是要注意的，当然也不排除这是11g的一个bug哦。。。
11g以前的版本测试如下：
SESSION 1:
16:18:34 SQL> delete from tmp_lg_log where rownum < 2;
1 row deleted.
SESSION 2:
16:21:10 SQL> alter table tmp_lg_log add rn number;
alter table tmp_lg_log add rn number
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
11g中测试DDL锁超时：
SESSION 1:
SQL> create table tmp_lg_log (id number,name varchar2(32),rn1 number, rn2 number);
Table created.
SQL> insert into tmp_lg_log values(1,'abc',1,2);
1 row created.
SESSION 2:
SQL> show parameter lock
NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM
------------------------- ----------- ---------------------
ddl_lock_timeout integer 0
--修改锁超时时间
SQL> alter system set ddl_lock_timeout=60 scope=both;
System altered.
--执行ddl(drop column)的操作,事务未在60s内提交,所以报timeout错误
SQL> alter table tmp_lg_log drop column rn2;
alter table tmp_lg_log drop column rn2
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
--执行ddl(add column)的操作,DDL则立即成功
SQL> alter table tmp_lg_log add rn4 number;
Table altered. | |
| 11g新特性：stream同步捕获 |
| |
|
在测试9iR2,10gR1,10gR2的stream时,非常希望能够有种同步capture的机制,该同步capture机制与log-based real-time capture机制是不同的,它应该是一种内部触发的机制,比如当有活动事务时,DB引擎判断该表配置有stream的同步capture标志,就可以直接把dml打包成LCRs并压入队列...
幸运的是,在10gR1中,已经看到了该功能已经被实现,但通过文档可以看到,oracle建议该功能仅适用于需要复制的表比较少的情况下,也可以理解需要复制的表dml不是很频繁,当然如果是全库进行复制,log-based real-time capture可能更高效一些,毕竟扫描一个500m以上的redo log成本也是很高的.
| | | | | | |
