5和6 objbc oracle_oracle 11g新特性1-CSDN博客

本文链接：https://blog.csdn.net/weixin_39748928/article/details/111494590

在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

program=sqlplus@csdba (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

program=sqlplus@csdba (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

program=sqlplus@csdba (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

program=sqlplus@csdba (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';

Sys@ORA11G> set long 10000000

Sys@ORA11G> set longchunksize 10000000

Sys@ORA11G> set linesize 200

Sys@ORA11G> 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

--------------------------------------------------------------------

--------------------------------------------------------------------

| 4.30 | 1.36 | 0.01 | 2.93 | 1 | 94869 | 94613 |

--------------------------------------------------------------------

SQL Plan Monitoring Details

==================================================================

==================================================================

| 0 | SELECT STATEMENT | | | 35310 | 1 |

| 1 | SORT AGGREGATE | | 1 | | 1 |

| 2 | TABLE ACCESS FULL | TEST | 126 | 35310 | 5 |

==================================================================

接上表:

===========================================================

===========================================================

+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成本也是很高的.