使用绑定变量降低了分析SQL语句所需分配的共享内存量，因为绑定变量对于相同（或类似）SQL语句的多次执行重用单个游标。因为绑定变量减少了分析时间和内存使用，从而提高了数据库的性能和可伸缩性。特别是在具有大量并发用户时更是如此。初始化参数cursor_sharing确定哪些SQL语句可共享相同的游标。设置cursor_sharing参数为exact规定只有相同的SQL语句才能共享一个游标。设置它为FORCE表示某些字面值不同的语句可以共享一个游标。除非不同的字面值影响到执行计划的优化度，否则设置此参数为SIMILAR与设置它为FORCE的行为相同。
不过，游标共享与SQL优化具有固有的矛盾，因为指定字面值而不是绑定值给优化程序提供了更丰富的信息，使得能生成更好的计划。由于这个原因，强制游标共享极可能导致次优执行假话。在某些情形下，一条SQL语句的某些用户可能会到的很优
根据11g release 2文档《Oracle® Database Reference 11g Release 2 (11.2)》，我们可以计算出11g中数据库大小的一个理论极限:
| Item | Type of Limit | Limit Value |
|---|---|---|
| Database Block Size | Minimum | 2048 bytes; must be a multiple of operating system physical block size |
| Database Block Size | Maximum | Operating system dependent; never more than 32 KB |
| Database Blocks | Minimum in initial extent of a segment | 2 blocks |
| Database Blocks | Maximum per datafile | Platform dependent; typically 222 – 1 blocks |
| Controlfiles | Number of control files | 1 minimum; 2 or more (on separate devices) strongly recommended |
| Controlfiles | Size of a control file | Dependent on operating system and database creation options; maximum of 20,000 x (database block size) |
| Database files | Maximum per tablespace | Operating system dependent; usually 1022 |
| Database files | Maximum per database | 65533
May be less on some operating systems Limited also by size of database blocks and by the |
| Database extents | Maximum per dictionary managed tablespace | 4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier) |
| Database extents | Maximum per locally managed (uniform) tablespace | 2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier) |
| Database file size | Maximum | Operating system dependent. Limited by maximum operating system file size; typically 222 or 4 MB blocks |
MAXEXTENTS |
Default value | Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter |
MAXEXTENTS |
Maximum | Unlimited |
| Redo Log Files | Maximum number of logfiles | Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statementControl file can be resized to allow more entries; ultimately an operating system limit |
| Redo Log Files | Maximum number of logfiles per group | Unlimited |
| Redo Log File Size | Minimum size | 4 MB |
| Redo Log File Size | Maximum Size | Operating system limit; typically 2 GB |
| Tablespaces | Maximum number per database | 64 K
Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file |
| Bigfile Tablespaces | Number of blocks | A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 232 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks. |
| Smallfile (traditional) Tablespaces | Number of blocks | A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks. |
| External Tables file | Maximum size | Dependent on the operating system.An external table can be composed of multiple files. |
11g中当采用32 K块时单个表空间最大可以扩展到128 TB，而一个数据库最多拥有64 K个表空间，则可以得出单个数据库的理论最大值为128TB *64 K= 8192 PB= 8EB，该数据与Oracle 10g 数据库的容量持平，换而言之11g中数据库的容量并未增加。
Youtube视频网站一年产生的信息量大约在800TB，世界上1000强的公司所有数据库的总数据量约合1EB(平均一个公司1PB)，而64位可寻址内存的最大容量为16EB，理论上我们可以把所有这些少于8EB的信息存储到您的笔记本上安装的Oracle数据库中，而且可以将它们完全缓存到内存中以供读取。
在几年前没有人相信TB级别的数据库会在不久后普及，但是我们今天可以很自信地说随着TB级别的数据库不断涌现，无论从硬件角度或者软件角度支撑该数量级的数据库的solution都已经十分成熟了。
在11g未来的日子里，我们将注目于EB级数据库的出现！
Backup Undo Optimization是11g的新特性之一，RMAN将避免备份撤销表空间上那些已提交事务的撤销数据。且该特性无法被禁用(You can enable and disable backup optimization, but backup undo optimization is built-in behavior.)。
我们在11.2.0.1版本上具体测试一下这个新特性:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
/* 为了避免undo自动调优干扰我们的测试，修改_undo_autotune参数禁用该特性 */
SQL> alter system set "_undo_autotune"=false;
System altered.
/* 创建一个新的undo表空间，清理现场 */
SQL> create undo tablespace UNDOTEST datafile size 500M autoextend on next 50M maxsize unlimited;
Tablespace created.
SQL> alter system set undo_tablespace=UNDOTEST;
System altered.
/* 列出相关的undo参数，可以看到这里undo_retention参数设为极短的10s */
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_undo_autotune boolean FALSE
undo_management string AUTO
undo_retention integer 10
undo_tablespace string UNDOTEST
RMAN> list backup;
specification does not match any backup in the repository
RMAN> backup tablespace UNDOTEST;
RMAN> list backup of tablespace UNDOTEST;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 1.90M DISK 00:00:00 25-AUG-10
/* undo表空间初始的备份大小为1.9M */
SQL> conn maclean/maclean
Connected.
SQL> select count(*) from YOUYUS;
COUNT(*)
----------
579808
/* YOUYUS表有大约60万条数据，我们批量删除这些数据，将产生大量的undo*/
SQL> delete YOUYUS;
579808 rows deleted.
/* 此时再次执行备份undo表空间操作 */
RMAN> backup tablespace UNDOTEST;
RMAN> list backup of tablespace UNDOTEST;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 134.43M DISK 00:00:03 25-AUG-10
/* 在存在大量active undo数据的情况下，备份文件增大到134M */
SQL> commit;
Commit complete.
SQL> exec dbms_lock.sleep(20);
PL/SQL procedure successfully completed.
SQL> select status,sum(blocks) from dba_undo_extents group by status;
STATUS SUM(BLOCKS)
--------- -----------
UNEXPIRED 2696
EXPIRED 32936
/* commit后等待20s,确定没有active的撤销段 */
RMAN> backup tablespace UNDOTEST;
RMAN> list backup of tablespace UNDOTEST;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 134.49M DISK 00:00:02 25-AUG-10
/* 备份文件还要大于commit前，undo backup optimization居然没有起作用？ */
/* 这个会是BUG吗？ */
根据以上情况我提交了SR，ORACLE GCS给出的回复:
Bug 6399468: UNDO OPTIMIZATION
====> Undo optimization was changed from ‘all undo not needed’ to ‘undo not in use older than 1 hour’
In unpublished bug 6399468 DEV has confirmed the Undo optimization was changed from ‘all undo not needed’ to ‘undo not in use older than 1 hour’. For your last test case please wait for 1 hour and try backup again.
居然又是一个unpublished的BUG，Oracle DEV部门确认了backup undo optimization所避免备份的是1个小时以上未被尝试使用的undo，而非所有不再需要的undo。这是典型的开发部门和文档撰写部门间没有充分交流造成的问题！
/* 尝试等待3600s */ SQL> exec dbms_lock.sleep(3600); PL/SQL procedure successfully completed. /* 3600s还真漫长....... */ RMAN> backup tablespace UNDOTEST; RMAN> list backup of tablespace UNDOTEST; BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 10 Full 15.01M DISK 00:00:00 25-AUG-10 /* 备份集缩小到15m,undo backup optimization起到了作用！*/ That's great!
11g中引入一些功能强大的hint提示，其中就包括了IGNORE_ROW_ON_DUPKEY_INDEX。其官方定义为:
“The IGNORE_ROW_ON_DUPKEY_INDEX hint applies only to single-table INSERT operations. It is not supported for UPDATE, DELETE, MERGE, or multitable insert operations. IGNORE_ROW_ON_DUPKEY_INDEX causes the statement to ignore a unique key violation for a specified set of columns or for a specified index. When a unique key violation is encountered, a row-level rollback occurs and execution resumes with the next input row. If you specify this hint when inserting data with DML error logging enabled, then the unique key violation is not logged and does not cause statement termination.”
针对具有唯一性约束的键，若程序设计时没有考虑到插入具有重复键值的行会引发ORA-00001 unique constraint violated错误，进而可能导致程序过程终止的问题的话；直接修改程序将会是十分复杂的工程。所幸我们在11g中有了”IGNORE_ROW_ON_DUPKEY_INDEX”提示，在INSERT单表的语句中加入该hint可以让Oracle静默地(silently)忽略那些具有重复键值的插入行，而不触发ORA-00001错误，允许程序继续运行下去，这可以说是一种十分简便的折中方案。要在生产环境中使用该特性，我们有必要对比一下其同使用Exception处理违反唯一约束间的性能差别。
SQL> drop table youyus ;
Table dropped.
SQL> create table youyus (t1 int ,t2 varchar2(20),t3 varchar2(30)) tablespace users;
Table created
SQL> create unique index youyus_uk on youyus(t1) tablespace users;
Index created
/*清理现场，添加唯一约束索引*/
SQL> alter system set optimizer_dynamic_sampling=1;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
/* 以下过程在0-30000的整数内随机取200000次值，可以确保尝试INSERT大量重复t1键值的行，
以便测试使用DUP_VAL_ON_INDEX Exception时的各项性能参数;此处以及之后我们都将commit置于loop循环外，
从而避免大量commit影响我们的实验结果*/
declare
rnd int;
begin
/* 使用exception处理重复键值插入违反约束的问题*/
for i in 1 .. 200000 loop
BEGIN
select round(dbms_random.value * 30000) into rnd from dual;
insert into youyus
(t1, t2, t3)
values
(rnd, 'DUPLICATE', 'INSERT TEST');
exception
when DUP_VAL_ON_INDEX then
continue;
end;
end loop;
commit;
end;
SQL> select plsql_exec_time,cpu_time,elapsed_time,user_io_wait_time from v$sql where sql_text like 'declare%exception%';
PLSQL_EXEC_TIME CPU_TIME ELAPSED_TIME USER_IO_WAIT_TIME
--------------- ---------- ------------ -----------------
4392268 88296566 92345066 621020
/* 逝去时间92s,CPU时间为88s,PLSQL执行时间4s*/
SQL> select count(*) from youyus;
COUNT(*)
----------
29958
SQL> truncate table youyus;
Table truncated.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
/* 在插入前判断插入值是否违反唯一约束应当是一种不错的想法，不过写起来多少有些"麻烦"*/
declare
dup_count int;
rnd int;
begin
/* 使用插入前判断(check before insert)是否违反唯一约束的方式*/
for i in 1 .. 200000 loop
BEGIN
select round(dbms_random.value * 30000) into rnd from dual;
select count(*) into dup_count from youyus where t1 = rnd;
IF (dup_count = 0) then
insert into youyus
(t1, t2, t3)
values
(rnd, 'DUPLICATE', 'INSERT TEST');
END IF;
END;
END LOOP;
commit;
end;
SQL> select plsql_exec_time,cpu_time,elapsed_time,user_io_wait_time from v$sql where sql_text like 'declare%check%';
PLSQL_EXEC_TIME CPU_TIME ELAPSED_TIME USER_IO_WAIT_TIME
--------------- ---------- ------------ -----------------
2153769 15709301 18265730 679813
/* PLSQL执行时间缩短到2s,整个过程的CPU时间大幅减少到15s*/
/*** 以上对比可以得出Exception处理是一种CPU敏感操作的结论 ***/
SQL> select count(*) from youyus;
COUNT(*)
----------
29968
SQL> truncate table youyus;
Table truncated.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
declare
rnd int;
begin
/* 使用IGNORE_ROW_ON_DUPKEY_INDEX hint方式*/
for i in 1 .. 200000 loop
select round(dbms_random.value * 30000) into rnd from dual;
insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(YOUYUS,YOUYUS_UK) */
into youyus
(t1, t2, t3)
values
(rnd, 'DUPLICATE', 'INSERT TEST');
end loop;
commit;
end;
SQL> select plsql_exec_time,cpu_time,elapsed_time,user_io_wait_time from v$sql where sql_text like 'declare%IGNORE%';
PLSQL_EXEC_TIME CPU_TIME ELAPSED_TIME USER_IO_WAIT_TIME
--------------- ---------- ------------ -----------------
2377262 78452903 84209306 623539
SQL> select count(*) from youyus;
COUNT(*)
----------
29959
/***
IGNORE_ROW_ON_DUPKEY_INDEX hint模式下，
CPU_TIME对比Exception模式时减少11%，但仍远高于插入前预检查模式;
就修改程序的复杂度而言IGNORE_ROW_ON_DUPKEY_INDEX模式要低于使用Exception模式,
而Exception模式又要低于CHECK_BEFORE_INSERT模式；CHECK_BEFORE_INSERT模式的CPU成本最低，但修改程序时的成本时间最高
***/
/*需要注意的是IGNORE_ROW_ON_DUPKEY_INDEX提示与我们以往使用的hint略有不同，不正确使用它将导致报错*/
declare
rnd int;
begin
/* 使用IGNORE_ROW_ON_DUPKEY_INDEX hint方式*/
for i in 1 .. 200000 loop
select round(dbms_random.value * 30000) into rnd from dual;
insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(YOUYUS,I_AM_ERROR) */
into youyus
(t1, t2, t3)
values
(rnd, 'DUPLICATE', 'INSERT TEST');
end loop;
commit;
end;
/
ERROR at line 1:
ORA-38913: Index specified in the index hint is invalid
ORA-06512: at line 7
上述三者各环节耗时图示:
总结一句，IGNORE_ROW_ON_DUPKEY_INDEX为lazy developer专备。
IO校准(IO Calibration)特性可以帮助我们了解存储系统的真实性能，以进一步判断I/O性能问题是由数据库还是存储系统自身引起的。I/O校准特性(IO Calibration)通过对Oracle数据文件的随机I/O访问存储介质，其结论值更符合数据库IO性能的真实情况。
在使用该特性前，我们要确保满足以下条件:
SQL> show parameter timed_statistics;
NAME TYPE VALUE
———————————— ———– ——————————
timed_statistics boolean TRUE
SQL> SELECT NAME, ASYNCH_IO 2 FROM V$DATAFILE F, V$IOSTAT_FILE I 3 WHERE F.FILE# = I.FILE_NO 4 AND FILETYPE_NAME = 'Data File'; NAME ASYNCH_IO -------------------------------------------------------------------------------- --------- D:\TOOLS\ADMINSTRATORG\ORADATA\PROD\DATAFILE\O1_MF_SYSTEM_65DN8HXT_.DBF ASYNC_ON D:\TOOLS\ADMINSTRATORG\ORADATA\PROD\DATAFILE\O1_MF_SYSAUX_65DN8J18_.DBF ASYNC_ON D:\TOOLS\ADMINSTRATORG\ORADATA\PROD\DATAFILE\O1_MF_UNDOTBS1_65DN8J1S_.DBF ASYNC_ON D:\TOOLS\ADMINSTRATORG\ORADATA\PROD\DATAFILE\O1_MF_USERS_65DN8J2X_.DBF ASYNC_ON
IO Calibration特性可以通过DBMS_RESOURCE_MANAGER.CALIBRATE_IO存储过程调用；该过程会对Oracle数据文件引发一系列IO敏感的只读工作负载(由1MB大小的随机IO组成)，从而判断存储系统所能持续的最大IOPS(每秒最大IO请求数)和MBPS(每秒IO传输速率)。为了使结果更具代表性，应当保持IO Calibration测试过程中整个数据库是空闲的，没有其他IO负载损耗。
让我们具体来看一下CALIBRATE_IO存储过程的调用方法:
-- Initiate an I/O calibration -- -- Input arguments: -- num_physical_disks - Approximate number of physical disks in -- the database storage -- max_latency - Maximum tolerable latency in milliseconds for -- database-block-sized IO requests -- -- Output arguments: -- max_iops - Maximum number of I/O requests per second that can be -- sustained. The I/O requests are randomly-distributed, -- database-block-sized reads. -- max_mbps - Maximum throughput of I/O that can be sustained, -- expressed in megabytes per second. The I/O requests -- are randomly-distributed, 1 megabyte reads. -- actual_latency - Average latency of database-block-sized I/O -- requests at "max_iops" rate, expressed in milliseconds. -- PROCEDURE calibrate_io ( num_physical_disks IN PLS_INTEGER DEFAULT 1, max_latency IN PLS_INTEGER DEFAULT 20, max_iops OUT PLS_INTEGER, max_mbps OUT PLS_INTEGER, actual_latency OUT PLS_INTEGER );
其中输入参数num_physical_disks用以指定数据库存储所用物理磁盘的近似数；max_latency指定了客户所能容忍的最大IO 延迟时间，单位为ms;一般我们认为IO平均等待在10ms左右是一种较好的表现，远大于这个数字往往说明IO负载过高了，这里我们假定15ms是我们的deadline。
SQL> set serveroutput on;
SQL> DECLARE
2 lat INTEGER;
3 iops INTEGER;
4 mbps INTEGER;
5 BEGIN
6 -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (disk_count,max_latency , iops, mbps, lat);
7 DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 15, iops, mbps, lat);
8
9 DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
10 DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
11 dbms_output.put_line('max_mbps = ' || mbps);
12 end;
13 /
max_iops = 127
latency = 15
max_mbps = 38
PL/SQL procedure successfully completed
/*平均延迟为15ms时，最大iops为127,mbps为38*/
/*执行过程中也可以通过V$IO_CALIBRATION_STATUS视图了解进度*/
SQL> select * from V$IO_CALIBRATION_STATUS;
STATUS CALIBRATION_TIME
------------- --------------------------------------------------------------------------------
IN PROGRESS 11-8月 -10 12.02.35.121 下午
SQL> alter session set nls_timestamp_format='HH24:MI';
Session altered
SQL> col start_time for a10;
SQL> col end_time for a10;
SQL> select * from DBA_RSRC_IO_CALIBRATE;
START_TIME END_TIME MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY NUM_PHYSICAL_DISKS
---------- ---------- ---------- ---------- ---------- ---------- ------------------
12:55 12:59 127 38 14 15 2
/*DBA_RSRC_IO_CALIBRATE视图记录了IO CALIBRATION的历史信息*/
[gview file=”http://askmac.cn/wp-content/uploads/resource/calibrate_io.pdf”]
DBA_HIST_IOSTAT_DETAIL视图记录了不同类型和组件功能所作IO的统计数据。这个视图的数据来自于V$IOSTAT_FILE和V$IOSTAT_FUNCTION 2个动态视图的快照。值得一提的是V$IOSTAT_FILE，它是11g中新引入的动态性能视图:
SQL> select filetype_name, asynch_io, access_method, retries_on_error 2 from v$iostat_file; FILETYPE_NAME ASYNCH_IO ACCESS_METH RETRIES_ON_ERROR ---------------------------- --------- ----------- ---------------- Other ASYNC_OFF OS_LIB 0 Control File ASYNC_OFF 0 Log File ASYNC_OFF 0 Archive Log ASYNC_OFF 0 Data File Backup ASYNC_OFF 0 Data File Incremental Backup ASYNC_OFF 0 Archive Log Backup ASYNC_OFF 0 Data File Copy ASYNC_OFF 0 Flashback Log ASYNC_OFF 0 Data Pump Dump File ASYNC_OFF 0 Data File ASYNC_ON OS_LIB 0 /*ASYNCH_IO列很好地标示了Oracle对于该类型文件是否启用了异步IO,这样我们就无需通过SYSTEM CALL TRACE来确定这一点了; retries_on_error累计了物理读取的失败次数，对我们发现磁盘坏道有一定帮助。目前的11.2.0.1官方Reference没有ACCESS_METHOD列的资料，猜想可能分为OS_LIB和ASM及Exdata等多种情况，有待验证。
我们回过头来继续讨论DBA_HIST_IOSTAT_DETAIL视图!
SQL> desc dba_hist_iostat_detail;
Name Type Nullable Default Comments
--------------------- ------------ -------- ------- --------
SNAP_ID NUMBER
DBID NUMBER
INSTANCE_NUMBER NUMBER
FUNCTION_ID NUMBER
FUNCTION_NAME VARCHAR2(30)
FILETYPE_ID NUMBER
FILETYPE_NAME VARCHAR2(30)
SMALL_READ_MEGABYTES NUMBER
SMALL_WRITE_MEGABYTES NUMBER
LARGE_READ_MEGABYTES NUMBER
LARGE_WRITE_MEGABYTES NUMBER
SMALL_READ_REQS NUMBER
SMALL_WRITE_REQS NUMBER
LARGE_READ_REQS NUMBER
LARGE_WRITE_REQS NUMBER
NUMBER_OF_WAITS NUMBER
WAIT_TIME NUMBER
SQL> col SMALL_READ_MEGABYTES for 999999;
SQL> col SMALL_WRITE_MEGABYTES for 999999;
SQL> col LARGE_READ_MEGABYTES for 999999;
SQL> col LARGE_WRITE_MEGABYTES for 999999;
SQL> col FILETYPE_NAME for a25;
SQL> select snap_id,
2 function_name,
3 filetype_name,
4 SMALL_READ_MEGABYTES,
5 SMALL_WRITE_MEGABYTES,
6 LARGE_READ_MEGABYTES,
7 LARGE_WRITE_MEGABYTES,
8 WAIT_TIME
9 from dba_hist_iostat_detail
10 where rownum < 16;
SNAP_ID FUNCTION_NAME FILETYPE_NAME SMALL_ SMALL_ LARGE_ LARGE_ WAIT_TIME
---------- ------------------------------ ------------------------- ------ ------ ------ ------ ----------
1 DBWR Control File 1 0 0 0 310
1 LGWR Control File 0 0 0 0 94
1 Others Control File 122 37 0 0 71635
2 DBWR Control File 1 0 0 0 310
2 LGWR Control File 0 0 0 0 94
2 Others Control File 251 77 0 0 159025
3 Others Control File 14 7 0 0 10339
4 Others Control File 96 60 0 0 87516
5 Others Control File 151 96 0 0 139796
6 DBWR Control File 0 0 0 0 15
6 Others Control File 210 135 0 0 189114
7 DBWR Control File 0 0 0 0 15
7 Others Control File 269 174 0 0 239640
8 DBWR Control File 0 0 0 0 15
8 Others Control File 328 213 0 0 288425
15 rows selected
其中SMALL_READ/WRITE代表单块读写，LARGE_READ/WRITE代表多块读写，wait_time的单位是千分之一秒(ms)。
SQL> select snap_id,
2 function_name,
3 filetype_name,
4 SMALL_READ_MEGABYTES,
5 SMALL_WRITE_MEGABYTES,
6 LARGE_READ_MEGABYTES,
7 LARGE_WRITE_MEGABYTES,
8 WAIT_TIME
9 from dba_hist_iostat_detail
10 where filetype_name = 'Control File'
11 and (LARGE_READ_REQS > 0 or LARGE_WRITE_REQS > 0);
未选定行
/*Oracle对控制文件只做单块读写*/
SQL> col FILETYPE_NAME for a15;
SQL> col function_name for a10;
SQL> select function_name,
2 filetype_name,
3 small_read_reqs,
4 small_write_reqs,
5 large_read_reqs,
6 large_write_reqs
7 from dba_hist_iostat_detail
8 where filetype_name = 'Log File'
9 and (SMALL_READ_REQS > 0 or LARGE_READ_REQS > 0);
FUNCTION_N FILETYPE_NAME SMALL_READ_REQS SMALL_WRITE_REQS LARGE_READ_REQS LARGE_WRITE_REQS
---------- --------------- --------------- ---------------- --------------- ----------------
LGWR Log File 4 9140 0 86
LGWR Log File 4 2762 0 218
LGWR Log File 8 3512 0 222
LGWR Log File 8 4304 0 226
LGWR Log File 4 1996 0 210
LGWR Log File 8 5296 0 252
LGWR Log File 8 6016 0 254
LGWR Log File 8 7224 0 274
LGWR Log File 4 11536 0 232
LGWR Log File 8 13320 0 256
SQL> select snap_id,
2 function_name,
3 filetype_name,
4 SMALL_READ_MEGABYTES,
5 SMALL_WRITE_MEGABYTES,
6 LARGE_READ_MEGABYTES,
7 LARGE_WRITE_MEGABYTES,
8 WAIT_TIME
9 from dba_hist_iostat_detail
10 where filetype_name = 'Log File';
SNAP_ID FUNCTION_NAME FILETYPE_NAME SMALL_ SMALL_ LARGE_ LARGE_ WAIT_TIME
---------- ------------------------------ ------------------------- ------ ------ ------ ------ ----------
2 LGWR Log File 0 60 0 27 30
35 LGWR Log File 0 23 0 48 0
6 LGWR Log File 0 12 0 8 0
7 LGWR Log File 0 15 0 10 0
8 LGWR Log File 0 18 0 12 0
13 LGWR Log File 0 5 0 4 0
15 LGWR Log File 0 6 0 2 0
17 LGWR Log File 0 4 0 3 0
19 LGWR Log File 0 11 0 7 0
20 LGWR Log File 0 14 0 8 0
37 LGWR Log File 0 13 0 27 0
38 LGWR Log File 0 15 0 29 0
41 LGWR Log File 0 13 0 152 46
42 LGWR Log File 0 16 0 153 61
43 LGWR Log File 0 19 0 155 61
49 LGWR Log File 0 2 0 0 0
3 LGWR Log File 0 1 0 0 0
4 LGWR Log File 0 7 0 4 0
5 LGWR Log File 0 9 0 6 0
12 LGWR Log File 0 1 0 0 0
/*日志文件在写出时会伴有少量的日志单块读操作，日志文件的写兼有单块写和多块写2种操作*/
另外，你大概和我一样不太喜欢使用OEM界面，不过OEM界面有个很方便的功能就是显示短期内的Avg Active Session Count(AAS)。什么？那你从来没看到过这种东西？下面这张截图大概可以勾起你的回忆:
早在10g时代就有人写了一个显示短期内AAS的脚本，在11g中该脚本得到了简化了:
set echo off;
set verify off;
alter session set nls_date_format='HH24:MI';
select *
from (select nvl(wait_class, 'CPU') activity,
trunc(sample_time, 'MI') time
from v$active_session_history) v pivot(count(*) for activity in('CPU' as
"CPU",
'Concurrency' as
"Concurrency",
'System I/O' as
"System I/O",
'User I/O' as
"User I/O",
'Administrative' as
"Administrative",
'Configuration' as
"Configuration",
'Application' as
"Application",
'Network' as
"Network",
'Commit' as
"Commit",
'Scheduler' as
"Scheduler",
'Cluster' as
"Cluster",
'Queueing' as
"Queueing",
'Other' as
"Other"))
where time > sysdate - interval '&last_min' minute
order by time;
我们也可以通过ASH查找Oracle中的TOP SESSION和TOP SQL:
/*找出短期内TOP SQL的sql_id和活动历史*/
select ash.SQL_ID,
sum(decode(ash.session_state, 'ON CPU', 1, 0)) "CPU",
sum(decode(ash.session_state, 'WAITING', 1, 0)) -
sum(decode(ash.session_state,
'WAITING',
decode(en.wait_class, 'User I/O', 1, 0),
0)) "WAIT",
sum(decode(ash.session_state,
'WAITING',
decode(en.wait_class, 'User I/O', 1, 0),
0)) "IO",
sum(decode(ash.session_state, 'ON CPU', 1, 1)) "TOTAL"
from v$active_session_history ash, v$event_name en
where SQL_ID is not NULL
and en.event# = ash.event#
group by sql_id
order by sum(decode(session_state, 'ON CPU', 1, 1)) desc;
SQL_ID CPU WAIT IO TOTAL
------------- ---------- ---------- ---------- ----------
a01hp0psv0rrh 0 2 7 9
24g90qj2b7ywk 0 5 1 6
2amsp6skc6tjv 0 0 5 5
46quk68k7akpa 0 3 1 4
2ufrf9vk4kcwj 0 0 3 3
1w8m6dwy66ttn 0 0 3 3
8uxr3scz9bmxd 0 0 3 3
6htq3p9j91y0s 0 0 3 3
cvn54b7yz0s8u 0 0 3 3
92f47aa2q2rmd 0 2 1 3
/*找出变量ivl指定分钟内的TOP CPU SESSION*/
Select session_id, count(*)
from v$active_session_history
where session_state = 'ON CPU'
and SAMPLE_TIME > sysdate -(&ivl/(24 * 60))
group by session_id
order by count(*) desc;
输入 ivl 的值: 10
原值 4: and SAMPLE_TIME > sysdate -(&ivl/(24 * 60))
新值 4: and SAMPLE_TIME > sysdate -(10/(24 * 60))
SESSION_ID COUNT(*)
---------- ----------
136 4
/*找出变量ivl指定分钟内TOP WAITING SESSION*/
Select session_id, count(*)
from v$active_session_history
where session_state = 'WAITING'
and SAMPLE_TIME > SYSDATE - (&ivl / (24 * 60))
group by session_id
order by count(*) desc;
输入 ivl 的值: 10
原值 4: and SAMPLE_TIME > SYSDATE - (&ivl / (24 * 60))
新值 4: and SAMPLE_TIME > SYSDATE - (10 / (24 * 60))
SESSION_ID COUNT(*)
---------- ----------
3 11
/*找出短期内的TOP SESSION及活动历史*/
select ash.session_id,
ash.session_serial#,
ash.user_id,
ash.program,
sum(decode(ash.session_state, 'ON CPU', 1, 0)) "CPU",
sum(decode(ash.session_state, 'WAITING', 1, 0)) -
sum(decode(ash.session_state,
'WAITING',
decode(en.wait_class, 'User I/O', 1, 0),
0)) "WAITING",
sum(decode(ash.session_state,
'WAITING',
decode(en.wait_class, 'User I/O', 1, 0),
0)) "IO",
sum(decode(session_state, 'ON CPU', 1, 1)) "TOTAL"
from v$active_session_history ash, v$event_name en
where en.event# = ash.event#
group by session_id, user_id, session_serial#, program
order by sum(decode(session_state, 'ON CPU', 1, 1));
以上脚本完全可以被ashrpt报表所替代，但在短期内做针对检查仍十分有效。
有应用人员反映某套Linux上的11.2.0.1数据库系统中出现了UNION ALL后返回的结果集不正确的问题,我们具体分析下出现问题的其中一条语句:
SELECT MTL_SECONDARY_INVENTORIES.SECONDARY_INVENTORY_NAME,
MTL_SECONDARY_INVENTORIES.ORGANIZATION_ID,
MTL_SECONDARY_INVENTORIES.DESCRIPTION,
MTL_SECONDARY_INVENTORIES.AVAILABILITY_TYPE,
MTL_SECONDARY_INVENTORIES.MATERIAL_ACCOUNT,
MTL_SECONDARY_INVENTORIES.MATERIAL_OVERHEAD_ACCOUNT,
MTL_SECONDARY_INVENTORIES.RESOURCE_ACCOUNT,
MTL_SECONDARY_INVENTORIES.OVERHEAD_ACCOUNT,
MTL_SECONDARY_INVENTORIES.OUTSIDE_PROCESSING_ACCOUNT,
MTL_SECONDARY_INVENTORIES.ASSET_INVENTORY,
MTL_SECONDARY_INVENTORIES.EXPENSE_ACCOUNT,
MTL_SECONDARY_INVENTORIES.ENCUMBRANCE_ACCOUNT,
MTL_SECONDARY_INVENTORIES.ATTRIBUTE3,
MTL_SECONDARY_INVENTORIES.ATTRIBUTE5,
WORKFLOW_START_TIMES.WORKFLOW_START_TIME
FROM REPEMEAERP.MTL_SECONDARY_INVENTORIES,
REPEMEAERP.WORKFLOW_START_TIMES
WHERE MTL_SECONDARY_INVENTORIES.DW_UPDATE_DT >
TO_DATE('01/01/1900 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
AND MTL_SECONDARY_INVENTORIES.DW_UPDATE_DT <=
WORKFLOW_START_TIMES.WORKFLOW_START_TIME
AND WORKFLOW_START_TIMES.WORKFLOW_NAME =
LTRIM(RTRIM('w_int_FreqBatch_EMEA'))
/*以上是QUERY A*/
UNION ALL
/*以下是QUERY B*/
SELECT DISTINCT 'WORKORDERS',
MTL_SECONDARY_INVENTORIES.ORGANIZATION_ID,
'WORK ORDERS WITH WIP AS CATEGORY VALUE',
1,
0,
0,
0,
0,
0,
1,
0,
0,
'MOI',
'0',
WORKFLOW_START_TIMES.WORKFLOW_START_TIME
FROM REPEMEAERP.MTL_SECONDARY_INVENTORIES, EIMMAINT.WORKFLOW_START_TIMES
WHERE MTL_SECONDARY_INVENTORIES.DW_UPDATE_DT >
TO_DATE('01/01/1900 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
AND MTL_SECONDARY_INVENTORIES.DW_UPDATE_DT <=
WORKFLOW_START_TIMES.WORKFLOW_START_TIME
AND WORKFLOW_START_TIMES.WORKFLOW_NAME =
LTRIM(RTRIM('w_int_FreqBatch_EMEA'))
/
138 rows selected.
以上查询语句中，QUERY A部分(也就是UNION ALL之前的SELECT语句)单独查询时返回返回69条记录，QUERY B部分单独查询时返回15记录，UNION ALL后返回的结果却是138条记录，而非84条记录。实际上这套系统也是最近才从10g迁移到11gr2上，之前在10g中同样的应用没有出过类似的问题，可以猜测是11g中新引入的某种特性存在可能引发wrong result的Bug。
具体思路虽然有了，但仍无法确定问题的关键所在；我们来看看该SQL的执行计划:
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 2443 | 52 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 7 | 2443 | 52 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | WORKFLOW_START_TIMES | 1 | 29 | 48 (0)| 00:00:01 |
| 3 | VIEW | VW_JF_SET$9BAED2EA | 1 | 320 | 4 (0)| 00:00:01 |
| 4 | UNION ALL PUSHED PREDICATE | | | | | |
|* 5 | FILTER | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| MTL_SECONDARY_INVENTORIES | 3 | 336 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_MTL_SECONDARY_INVENTORIES | 1 | | 1 (0)| 00:00:01 |
|* 8 | FILTER | | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID| MTL_SECONDARY_INVENTORIES | 3 | 36 | 2 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IDX_MTL_SECONDARY_INVENTORIES | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("WORKFLOW_START_TIMES"."WORKFLOW_NAME"='w_int_FreqBatch_EMEA')
5 - filter(TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')<"WORKFLOW_START_TIMES"."WORKFLOW_START_TIME") 7 - access("MTL_SECONDARY_INVENTORIES"."DW_UPDATE_DT">TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "MTL_SECONDARY_INVENTORIES"."DW_UPDATE_DT"<="WORKFLOW_START_TIMES"."WORKFLOW_START_TIME"
)
8 - filter(TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')<"WORKFLOW_START_TIMES"."WORKFLOW_START_TIME") 10 - access("MTL_SECONDARY_INVENTORIES"."DW_UPDATE_DT">TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "MTL_SECONDARY_INVENTORIES"."DW_UPDATE_DT"<="WORKFLOW_START_TIMES"."WORKFLOW_START_TIME"
)
你可能从以上执行计划中发现了两处十分陌生的字眼:UNION ALL PUSHED PREDICATE和VW_JF_SET$。它们是什么！？
先来说说JF,JF是join factorization的缩写，你可以把它翻译作链接因式分解，如果你学过离散数学或者数据库原理的话，那么这种在11.2.0.1中最新推出的基于成本的变换操作对你来说并不陌生。用公式的样式来表达大概是下面这样:
YYA,YYB和YYC是3个关联的数据对象亦或者是3个关联的结果集; (YYA JOIN YYB) UNION [ALL] (YYA JOIN YYC) 可以转换成为: YYA JOIN (YYB UNION [ALL] YYC)
这样做YYA部分只需要读取一次，还可以少做一次JOIN,听上去是挺不错的吧！
下面我们来看一个Oracle使用join factorization的十分简单的实例:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> drop table yya;
drop table yya
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> drop table yyb;
drop table yyb
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create table yya as select rownum id1,rownum id2,rownum id3 from dual connect by level<=20000;
Table created.
SQL> create table yyb as select rownum id1,rownum id2,rownum id3 from dual connect by level<=20000;
Table created.
SQL> explain plan for
2 select * from yya ,yyb where yya.id1=yyb.id1
3 union all
4 select * from yya, yyb where yya.id1=yyb.id1;
Explained.
SQL> set linesize 100 pagesize 1400;
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 744914999
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40000 | 2500K| 49 (3)| 00:00:01 |
|* 1 | HASH JOIN | | 40000 | 2500K| 49 (3)| 00:00:01 |
| 2 | TABLE ACCESS FULL | YYA | 20000 | 234K| 16 (0)| 00:00:01 |
| 3 | VIEW | VW_JF_SET$6E3F6682 | 40000 | 2031K| 32 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | TABLE ACCESS FULL| YYB | 20000 | 761K| 16 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL| YYB | 20000 | 761K| 16 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("YYA"."ID1"="ITEM_1")
/*执行计划中出现了VW_JF_SET$F22B2A93,Oracle选择了使用join factorization，该执行计划总成本49*/
SQL> alter session set "_optimizer_join_factorization"=false;
Session altered.
/*隐藏参数_optimizer_join_factorization决定了优化器是否可以选用join factorization,现在我们禁用它*/
SQL> explain plan for
2 select * from yya join yyb on yya.id1=yyb.id1
3 union all
4 select * from yya join yyb on yya.id1=yyb.id1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3439541885
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40000 | 1992K| 66 (52)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
|* 2 | HASH JOIN | | 20000 | 996K| 33 (4)| 00:00:01 |
| 3 | TABLE ACCESS FULL| YYA | 20000 | 234K| 16 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| YYB | 20000 | 761K| 16 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 20000 | 996K| 33 (4)| 00:00:01 |
| 6 | TABLE ACCESS FULL| YYA | 20000 | 234K| 16 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL| YYB | 20000 | 761K| 16 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("YYA"."ID1"="YYB"."ID1")
5 - access("YYA"."ID1"="YYB"."ID1")
/*禁用链接因式分解后，Oracle使用了常规的"笨办法"，成本上升到66*/
/*有趣的是下面的测试*/
SQL> alter session set "_optimizer_join_factorization"=true;
Session altered.
SQL> create table yyc as select * from yyb;
Table created.
SQL> explain plan for
2 select * from yya,yyc where yya.id1=yyc.id1
3 union all
4 select * from yya,yyb where yya.id1=yyb.id1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4240055274
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40000 | 1992K| 66 (52)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
|* 2 | HASH JOIN | | 20000 | 996K| 33 (4)| 00:00:01 |
| 3 | TABLE ACCESS FULL| YYA | 20000 | 234K| 16 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| YYC | 20000 | 761K| 16 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 20000 | 996K| 33 (4)| 00:00:01 |
| 6 | TABLE ACCESS FULL| YYA | 20000 | 234K| 16 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL| YYB | 20000 | 761K| 16 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("YYA"."ID1"="YYC"."ID1")
5 - access("YYA"."ID1"="YYB"."ID1")
/*confused，Oracle有什么理由在这里反而不用join factorization了呢?看起来短期内join factorization的实际应用还有待"商榷"
*/
/*10053事件能解释这一问题吗?*/
SQL> alter system flush shared_pool;
System altered.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10053 trace name context forever,level 1;
Statement processed.
SQL> explain plan for
2 select * from yya join yyb on yya.id1=yyb.id1
3 union all
4 select * from yya join yyc on yya.id1=yyc.id1;
Explained.
SQL> oradebug event 10053 trace name context off;
Statement processed.
SQL> oradebug tracefile_name;
/home/maclean/app/maclean/diag/rdbms/prod/PROD/trace/PROD_ora_7907.trc
view /home/maclean/app/maclean/diag/rdbms/prod/PROD/trace/PROD_ora_7907.trc
***********************************
Cost-Based Join Factorization
***********************************
Join-Factorization on query block SET$1 (#1)
JF: Using search type: exhaustive
JF: Generate basic transformation units
Validating JF unit: (branch: {2, 3} table: {YYA, YYA})
rejected: join predicates do not match
JF: Generate transformation units from basic units
JF: No state generated.
/*优化器认为其链接谓词不符合使用join factorization的条件，JF题案被驳回，"悬案"!*/
join factorization是很棒的新技术，这点没错，但新技术往往又是horrible(可怕的)，最近我常用这个词。我们的问题是不是这个新来的引起的呢?通过join factorization关键字检索MOS，可以发现一个今年(2010)3月出现的Bug 9504322,quote:
Hdr: 9504322 11.2.0.1 RDBMS 11.2.0.1 QRY OPTIMIZER PRODID-5 PORTID-226
Abstract: WRONG RESULTS WITH UNION_ALL AND INLINE VIEWS*** 03/24/10 05:38 am ***
PROBLEM:
--------
Wrong results on 11.2 for queries of type:SELECT * FROM
(
SELECT ... FROM view, table WHERE ...
UNION ALL
SELECT ... FROM view, table WHERE NOT ...
);DIAGNOSTIC ANALYSIS:
--------------------
Problem seen between 10.2.0.4 and 11.2.0.1.
If we remove the use of inline view the correct results are returned.WORKAROUND:
-----------
N/ARELATED BUGS:
-------------REPRODUCIBILITY:
----------------
It is reproducing on generic 11.2.0.1
呵呵，似乎有点眉目了，不过实践是检验真理的唯一标准:
SQL> alter session set "_optimizer_join_factorization"=true;
Session altered.
SELECT MTL_SECONDARY_INVENTORIES.SECONDARY_INVENTORY_NAME,
MTL_SECONDARY_INVENTORIES.ORGANIZATION_ID,
MTL_SECONDARY_INVENTORIES.DESCRIPTION,
MTL_SECONDARY_INVENTORIES.AVAILABILITY_TYPE,
MTL_SECONDARY_INVENTORIES.MATERIAL_ACCOUNT,
MTL_SECONDARY_INVENTORIES.MATERIAL_OVERHEAD_ACCOUNT,
MTL_SECONDARY_INVENTORIES.RESOURCE_ACCOUNT,
MTL_SECONDARY_INVENTORIES.OVERHEAD_ACCOUNT,
MTL_SECONDARY_INVENTORIES.OUTSIDE_PROCESSING_ACCOUNT,
MTL_SECONDARY_INVENTORIES.ASSET_INVENTORY,
MTL_SECONDARY_INVENTORIES.EXPENSE_ACCOUNT,
MTL_SECONDARY_INVENTORIES.ENCUMBRANCE_ACCOUNT,
MTL_SECONDARY_INVENTORIES.ATTRIBUTE3,
MTL_SECONDARY_INVENTORIES.ATTRIBUTE5,
WORKFLOW_START_TIMES.WORKFLOW_START_TIME
FROM REPEMEAERP.MTL_SECONDARY_INVENTORIES,
REPEMEAERP.WORKFLOW_START_TIMES
WHERE MTL_SECONDARY_INVENTORIES.DW_UPDATE_DT >
TO_DATE('01/01/1900 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
AND MTL_SECONDARY_INVENTORIES.DW_UPDATE_DT <=
WORKFLOW_START_TIMES.WORKFLOW_START_TIME
AND WORKFLOW_START_TIMES.WORKFLOW_NAME =
LTRIM(RTRIM('w_int_FreqBatch_EMEA'))
/*以上是QUERY A*/
UNION ALL
/*以下是QUERY B*/
SELECT DISTINCT 'WORKORDERS',
MTL_SECONDARY_INVENTORIES.ORGANIZATION_ID,
'WORK ORDERS WITH WIP AS CATEGORY VALUE',
1,
0,
0,
0,
0,
0,
1,
0,
0,
'MOI',
'0',
WORKFLOW_START_TIMES.WORKFLOW_START_TIME
FROM REPEMEAERP.MTL_SECONDARY_INVENTORIES, EIMMAINT.WORKFLOW_START_TIMES
WHERE MTL_SECONDARY_INVENTORIES.DW_UPDATE_DT >
TO_DATE('01/01/1900 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
AND MTL_SECONDARY_INVENTORIES.DW_UPDATE_DT <=
WORKFLOW_START_TIMES.WORKFLOW_START_TIME
AND WORKFLOW_START_TIMES.WORKFLOW_NAME =
LTRIM(RTRIM('w_int_FreqBatch_EMEA'))
/
138 rows selected.
结果和我们猜想的大相径庭，join factorization并非罪魁，找不到终点让我们回到原点。
至此UNION ALL PUSHED PREDICATE有了极大的嫌疑，什么是PUSH PREDICATE？我把它叫做谓词前推，这玩样最早出现在10g上，但一直问题多多！它到底是何种OPERATION呢？让我们来看看下面的例子:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> create table youyus (t1 int,t2 varchar2(20));
Table created.
SQL> alter table youyus add primary key(t1);
Table altered.
SQL> explain plan for
2 select *
3 from youyus
4 union all
5 select * from youyus;
Explained.
/*在之后的语句中将用到这个子查询*/
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1959159425
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 50 | 4 (50)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS FULL| YOUYUS | 1 | 25 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| YOUYUS | 1 | 25 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
/*在之后的语句中将用到这个子查询,这里它的"原始"执行计划十分简单*/
SQL> explain plan for
2 select v2.t1, v2.t2
3 from (select t1 from youyus where rownum=1) v1,
4 (select *
5 from youyus
6 union all
7 select * from youyus) v2
8 where v1.t1 = v2.t1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2456530141
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 1 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 27 | 1 (0)| 00:00:01 |
| 2 | VIEW | | 1 | 13 | 1 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | INDEX FULL SCAN | SYS_C0010819 | 1 | 13 | 1 (0)| 00:00:01 |
| 5 | VIEW | | 1 | 14 | 0 (0)| 00:00:01 |
| 6 | UNION ALL PUSHED PREDICATE | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| YOUYUS | 1 | 25 | 0 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | SYS_C0010819 | 1 | | 0 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID| YOUYUS | 1 | 25 | 0 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | SYS_C0010819 | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM=1)
8 - access("YOUYUS"."T1"="V1"."T1")
10 - access("YOUYUS"."T1"="V1"."T1")
/* PUSHED PREDICATE将谓词逻辑前推到UNION ALL的子查询中，其优势在于可以避免全表扫描，利用索引*/
SQL> set linesize 100 pagesize 1400;
SQL>
SQL> explain plan for
2 select /*+ no_push_pred(v2) */ v2.t1, v2.t2
3 from (select t1 from youyus where rownum=1) v1,
4 (select *
5 from youyus
6 union all
7 select * from youyus) v2
8 where v1.t1 = v2.t1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2769827061
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 6 (17)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 38 | 6 (17)| 00:00:01 |
| 2 | VIEW | | 1 | 13 | 1 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | INDEX FULL SCAN | SYS_C0010819 | 1 | 13 | 1 (0)| 00:00:01 |
| 5 | VIEW | | 2 | 50 | 4 (0)| 00:00:01 |
| 6 | UNION-ALL | | | | | |
| 7 | TABLE ACCESS FULL| YOUYUS | 1 | 25 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL| YOUYUS | 1 | 25 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("V1"."T1"="V2"."T1")
3 - filter(ROWNUM=1)
/*no_push_pred hint让Oracle 放弃使用PUSHED PREDICATE，使用常规UNION-ALL操作后，子查询执行计划回归成全表扫描，整个计划成本上升*/
9i以后引入了bind peeking绑定变量窥视特性，但该特性常有帮当忙之嫌，所以有了11g的自适应游标特性。排除因绑定变量窥视造成的因素外，统计信息讹误也会造成执行计划偏差，这时我们就可能需要清除指定游标的缓存信息，从而达到重新解析的目的。
下面我们列举几种可以达到清除游标缓存的方法，权作抛砖引玉:
1. alter system flush shared_pool; /* 最简单最粗暴的方法，清除所有游标缓存，可能造成短期内大量解析，不推荐*/
2. dbms_shared_pool 包很早就有了，但该包名下的purge过程却要到10.2.0.4才出现，Bug 5614566最早在2006年描述了需要清除游标缓存接口的要求：
Hdr: 5614566 10.2.0.2 RDBMS 10.2.0.2 DICTIONARY PRODID-5 PORTID-176
Abstract: WE NEED A FLUSH CURSOR INTERFACE
*** 10/20/06 07:48 am ***
而且该过程在10.2.0.4中默认是无法正常使用的，需要通过设置event 5614566或者打上5614566补丁来启用；具体设置方法如下：
alter system set events ‘5614566 trace name context forever’;
该存储过程的具体argument如下:
PROCEDURE PURGE 参数名称 类型 输入/输出默认值? ------------------------------ ----------------------- ------ -------- NAME VARCHAR2 IN FLAG CHAR IN DEFAULT HEAPS NUMBER IN DEFAULT
其中NAME指定了需要清除的对象名,这里分成2种。PL/SQL对象，触发器，序列，类型和JAVA对象以其命名指定；SQL游标对象通过该SQL的address与hash_value组合指定。FLAG指定了对象的类型，若没有指定该参数，Oracle将认为之前代入的NAME参数对应到包/存储过程/函数的命名空间， 需要注意的是该参数是大小写敏感的，包括了以下各类型:
| FLAG值 | 对应对象类型 |
| P | 包/存储过程/函数 |
| Q | 序列 |
| R | 触发器 |
| T | 类型 |
| JS | Java源程序 |
| JC | Java类程序 |
| JR | Java资源 |
| JD | Java共享数据 |
| C | cursor |
HEAP参数指定了清除对象的哪些堆信息，以SQL游标为例,其最主要的信息包括在HEAP 0和HEAP 6中，HEAP 0包括了游标自身的大多数信息，而HEAP 6则存放了游标相关的执行计划。如果我们想要清除HEAP 0和HEAP 6中的信息，则2的0次方+2的6次方=1+64=65,那么我们在代入HEAP参数为65 即可；如果我们只想清除游标的执行计划则清除HEAP 6即可，代入HEAP参数为2的6次方即64。该参数的默认值为1，清除HEAP 0将会导致整个对象的缓存信息被清除掉。
下面我们来演示如何利用该存储过程来清除SQL缓存:
SQL> alter system flush shared_pool;
系统已更改。
SQL> select /* cache_me */ count(*) from youyus;
COUNT(*)
----------
9
SQL> select sql_id,
2 address,
3 hash_value,
4 executions,
5 loads,
6 version_count,
7 invalidations,
8 parse_calls
9 from v$sqlarea
10 where sql_text like '%cache_me%'
11 and sql_text not like '%v$sqlarea%';
SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS
------------- -------- ---------- ---------- ---------- ------------- ------------- -----------
25asu5a76nqmn 2F51508C 2389334644 3 1 1 0 3
SQL> select address, plan_hash_value
2 from v$sql_plan
3 where sql_id = '25asu5a76nqmn';
ADDRESS PLAN_HASH_VALUE
-------- ---------------
2F51508C 2542806819
2F51508C 2542806819
2F51508C 2542806819
SQL> exec dbms_shared_pool.purge('2F51508C,2389334644','C',64);
PL/SQL 过程已成功完成。
SQL> select sql_id,
2 address,
3 hash_value,
4 executions,
5 loads,
6 version_count,
7 invalidations,
8 parse_calls,
9 plan_hash_value
10 from v$sqlarea
11 where sql_text like '%cache_me%'
12 and sql_text not like '%v$sqlarea%';
SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE
------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------
25asu5a76nqmn 2F51508C 2389334644 4 1 1 0 4 2542806819
SQL> select * from v$sql_plan where plan_hash_value= 2542806819;
未选定行
/*执行计划消失了，而游标主体信息仍在*/
SQL> select /* cache_me */ count(*) from youyus;
COUNT(*)
----------
9
SQL> select sql_id,
2 address,
3 hash_value,
4 executions,
5 loads,
6 version_count,
7 invalidations,
8 parse_calls,
9 plan_hash_value
10 from v$sqlarea
11 where sql_text like '%cache_me%'
12 and sql_text not like '%v$sqlarea%';
SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE
------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------
25asu5a76nqmn 2F51508C 2389334644 5 1 1 0 5 2542806819
/*这里新增的一次parse call是硬解析*/
SQL> select address,operation from v$sql_plan where plan_hash_value= 2542806819;
ADDRESS OPERATION
-------- ------------------------------------------------------------
2F51508C SELECT STATEMENT
2F51508C SORT
2F51508C TABLE ACCESS
SQL> exec dbms_shared_pool.purge('2F51508C,2389334644','C',1);
PL/SQL 过程已成功完成。
SQL> select sql_id,
2 address,
3 hash_value,
4 executions,
5 loads,
6 version_count,
7 invalidations,
8 parse_calls,
9 plan_hash_value
10 from v$sqlarea
11 where sql_text like '%cache_me%'
12 and sql_text not like '%v$sqlarea%';
未选定行
SQL> select address,operation from v$sql_plan where plan_hash_value= 2542806819;
未选定行
SQL> select /* cache_me */ count(*) from youyus;
COUNT(*)
----------
9
SQL> select sql_id,
2 address,
3 hash_value,
4 executions,
5 loads,
6 version_count,
7 invalidations,
8 parse_calls,
9 plan_hash_value
10 from v$sqlarea
11 where sql_text like '%cache_me%'
12 and sql_text not like '%v$sqlarea%';
SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE
------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------
25asu5a76nqmn 2F51508C 2389334644 1 2 1 1 1 2542806819
/*清除游标heap 0后，包括执行计划的所有信息都被清除了,甚至于simulator中的信息*/
3.如果您的环境中恰好无法利用dbms_shared_pool.purge存储过程，我们也可以采用一些折中的方法来清除游标缓存；譬如通过一个无关紧要的grant/revoke操作,但这样也会造成所有该授权/撤职对象相关SQL的执行计划失效:
SQL> alter system flush shared_pool;
系统已更改。
SQL> select /* cache_me */ count(*) from youyus;
COUNT(*)
----------
9
SQL> select sql_id,
2 address,
3 hash_value,
4 executions,
5 loads,
6 version_count,
7 invalidations,
8 parse_calls,
9 plan_hash_value
10 from v$sqlarea
11 where sql_text like '%cache_me%'
12 and sql_text not like '%v$sqlarea%';
SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE
------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------
25asu5a76nqmn 2F540EA0 2389334644 1 1 1 0 1 2542806819
SQL> select address,operation,to_char(timestamp,'HH24:MI:SS') from v$sql_plan where plan_hash_value= 2542806819;
ADDRESS OPERATION TO_CHAR(
-------- ------------------------------------------------------------ --------
2F540EA0 SELECT STATEMENT 13:39:28
2F540EA0 SORT 13:39:28
2F540EA0 TABLE ACCESS 13:39:28
SQL> revoke select on youyus from scott;
撤销成功。
SQL> select sql_id,
2 address,
3 hash_value,
4 executions,
5 loads,
6 version_count,
7 invalidations,
8 parse_calls,
9 plan_hash_value
10 from v$sqlarea
11 where sql_text like '%cache_me%'
12 and sql_text not like '%v$sqlarea%';
SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE
------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------
25asu5a76nqmn 2F540EA0 2389334644 1 1 1 1 1 2542806819
/*授权/撤销会造成执行计划invalid，此处 INVALIDATIONS上升到1*/
SQL> select /* cache_me */ count(*) from youyus;
COUNT(*)
----------
9
/*重新执行SQL，将引发一次硬解析*/
SQL> select address,operation,to_char(timestamp,'HH24:MI:SS') from v$sql_plan where plan_hash_value= 2542806819;
ADDRESS OPERATION TO_CHAR(
-------- ------------------------------------------------------------ --------
2F540EA0 SELECT STATEMENT 13:40:23
2F540EA0 SORT 13:40:23
2F540EA0 TABLE ACCESS 13:40:23
/*执行计划的时间戳发生了变化，达到了重新解析游标的目的*/
4.或许你不是一个位高权重的DBA，无法执行授权/撤职命令，但如果你能分析游标所涉及对象的统计信息或者执行其他一些ddl操作，那么也可以达到同样的目的:
SQL> alter system flush shared_pool;
系统已更改。
SQL>
SQL> select /* cache_me */ count(*) from youyus;
COUNT(*)
----------
9
SQL> select sql_id,
2 address,
3 hash_value,
4 executions,
5 loads,
6 version_count,
7 invalidations,
8 parse_calls,
9 plan_hash_value
10 from v$sqlarea
11 where sql_text like '%cache_me%'
12 and sql_text not like '%v$sqlarea%';
SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE
------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------
25asu5a76nqmn 2F540EA0 2389334644 1 1 1 0 1 2542806819
SQL> analyze table youyus compute statistics;
表已分析。
SQL> select sql_id,
2 address,
3 hash_value,
4 executions,
5 loads,
6 version_count,
7 invalidations,
8 parse_calls,
9 plan_hash_value
10 from v$sqlarea
11 where sql_text like '%cache_me%'
12 and sql_text not like '%v$sqlarea%';
SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE
------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------
25asu5a76nqmn 2F540EA0 2389334644 1 1 1 1 1 2542806819
/*统计信息更新，造成了invalid*/
SQL> create index ind_youyus on youyus(t1);
索引已创建。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select /* cache_me */ count(*) from youyus;
COUNT(*)
----------
9
SQL> select sql_id,
2 address,
3 hash_value,
4 executions,
5 loads,
6 version_count,
7 invalidations,
8 parse_calls,
9 plan_hash_value
10 from v$sqlarea
11 where sql_text like '%cache_me%'
12 and sql_text not like '%v$sqlarea%';
SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE
------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------
25asu5a76nqmn 2F464EA0 2389334644 1 1 1 0 1 2542806819
SQL> alter index ind_youyus rebuild online;
索引已更改。
SQL> select sql_id,
2 address,
3 hash_value,
4 executions,
5 loads,
6 version_count,
7 invalidations,
8 parse_calls,
9 plan_hash_value
10 from v$sqlarea
11 where sql_text like '%cache_me%'
12 and sql_text not like '%v$sqlarea%';
SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE
------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------
25asu5a76nqmn 2F464EA0 2389334644 1 1 1 1 1 2542806819
/*在线重建索引也可以达到同样的目的*/
That's Great!
今天早上应用人员反映一个原本在9i上可以顺利完成的CTAS脚本，迁移到10g后运行总是报“ORA-1652: unable to extend temp segment by 128 in tablespace TS_HQY1_TEMP “无法扩展临时表空间的错误。应用人员表示该脚本涉及的数据量在迁移前后变化不大，而且令人匪夷所思的是在新的10g库上临时表空间大小已达40多个G，要远大于原9i库。很显然这不是由于临时表空间过小导致的该问题，更多的原因肯定是出在迁移后Oracle不同的行为方式上。
该脚本每月执行一次用以汇总数据，其中一个单表接近4亿行记录，GROUP BY操作涉及到的数据量十分庞大。我们来具体看一下这个SQL:
create table gprs_bill.zou_201007_cell_id as
select /* g_all_cdr01,60 */
calling_num mobile_number,
lac,
lpad(cell_id, 5, '0') cell_id,
count(*) c,
sum(call_duration) call_duration,
sum(decode(record_type, '00', 1, 0) * call_duration) moc_call_duration,
sum(decode(record_type, '01', 1, 0) * call_duration) mtc_call_duarion
from gprs_bill.g_all_cdr01
where substr(calling_num, 1, 7) in
(select mobile_prefix from gprs_bill.zou_mobile_prefix)
group by calling_num, lac, lpad(cell_id, 5, '0');
SQL> set autotrace traceonly exp
SQL> select /* g_all_cdr01,60 */
2 calling_num mobile_number,
3 lac,
4 lpad(cell_id,5,'0') cell_id,
5 count(*) c,
6 sum(call_duration) call_duration,
7 sum(decode(record_type,'00',1,0)*call_duration) moc_call_duration,
8 sum(decode(record_type,'01',1,0)*call_duration) mtc_call_duarion
9 from gprs_bill.g_all_cdr01
10 where substr(calling_num,1,7) in (select mobile_prefix from gprs_bill.zou_mobile_prefix)
11 group by
12 calling_num ,
13 lac,
14 lpad(cell_id,5,'0');
Execution Plan
----------------------------------------------------------
Plan hash value: 212866585
--------------------------------------------------------------------------------
-------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost
(%CPU)| Time |
--------------------------------------------------------------------------------
-------------------
| 0 | SELECT STATEMENT | | 229K| 9880K| | 103
3K (3)| 03:26:41 |
| 1 | HASH GROUP BY | | 229K| 9880K| 22M| 103
3K (3)| 03:26:41 |
|* 2 | HASH JOIN RIGHT SEMI| | 229K| 9880K| | 103
0K (3)| 03:26:10 |
| 3 | TABLE ACCESS FULL | ZOU_MOBILE_PREFIX | 1692 | 13536 | | 1
1 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | G_ALL_CDR01 | 388M| 13G| | 102
6K (2)| 03:25:21 |
--------------------------------------------------------------------------------
-------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MOBILE_PREFIX"=SUBSTR("CALLING_NUM",1,7))
可以看到Oracle使用了HASH GROUP BY 算法以实现数据分组；HASH算法是10g中新引入的分组算法。
下面我们来详细介绍下10g中数据分组的改动：
在10g中GROUP BY操作仍将引发排序操作，但10g中引入了新的算法，这些算法都不保证返回的数据行有序排列；在10g中如果想保证”GROUP BY”后返回的数据有序排列则需要强制使用”ORDER BY”子句，这点和9i是截然不同的。若你没有指定”ORDER BY”子句，则不能保证返回的结果正确排序。
在10g中”GROUP BY”子句更倾向于使用一种HASH算法而非原先的SORT算法来分组数据，HASH算法的CPU COST要低于原先的SORT算法。但这2种算法在10g中都不保证返回数据正常排序，当采用SORT算法时可能”碰巧”出现返回正常排序数据的状况。
MOS建议，如果迁移中出现大量不利的变化，则可以通过修改参数来确保沿用原先的算法。但需要注意的是，即便采用了以下参数仍不能保证10g后”GROUP BY”后返回的数据如9i中那样排序，你需要做的是加入显式的”ORDER BY”子句以保证Oracle为你做到这一点。
alter session set "_gby_hash_aggregation_enabled" = false; alter session set optimizer_features_enable="9.2.0"; 或者 alter session set optimizer_features_enable="8.1.7";
其中_gby_hash_aggregation_enabled隐式参数决定了Oracle是否可以启用新的HASH算法来进行数据分组(也适用于distinct等操作)。
对于以上说法我们通过实验进一步验证：
在11g中的测试如下：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> select * from youyus;
T1 T2
---------- ----------
A 10
B 10
F 30
G 30
H 40
I 40
J 40
L 20
M 20
已选择9行。
SQL> analyze table youyus compute statistics for all columns;
表已分析。
SQL> set autotrace on;
SQL> select t2,count(*) from youyus group by t2;
T2 COUNT(*)
---------- ----------
30 2
20 2
40 3
10 2
执行计划
----------------------------------------------------------
Plan hash value: 2940504347
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 8 | 3 (34)| 00:00:01 |
| 1 | HASH GROUP BY | | 4 | 8 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS FULL| YOUYUS | 9 | 18 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
/*可以看到使用了hash算法，且返回结果未按t2列大小顺序排列*/
SQL> select t2,count(*) from youyus group by t2 order by t2;
T2 COUNT(*)
---------- ----------
10 2
20 2
30 2
40 3
执行计划
----------------------------------------------------------
Plan hash value: 1349668650
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 8 | 3 (34)| 00:00:01 |
| 1 | SORT GROUP BY | | 4 | 8 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS FULL| YOUYUS | 9 | 18 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
/*加入order by子句后，又变回了SORT算法，而且正常排序*/
SQL> alter session set "_gby_hash_aggregation_enabled" = false;
会话已更改。
SQL> alter session set optimizer_features_enable="9.2.0";
会话已更改。
SQL> select t2,count(*) from youyus group by t2;
T2 COUNT(*)
---------- ----------
10 2
20 2
30 2
40 3
执行计划
----------------------------------------------------------
Plan hash value: 1349668650
-------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 8 | 11 |
| 1 | SORT GROUP BY | | 4 | 8 | 11 |
| 2 | TABLE ACCESS FULL| YOUYUS | 9 | 18 | 2 |
-------------------------------------------------------------
Note
-----
- cpu costing is off (consider enabling it)
/*optimizer_features_enable设置为9.2.0后cpu cost被off了；返回数据正确排序，但我们要记住这是"侥幸"*/
SQL> alter session set optimizer_features_enable="10.2.0.5";
会话已更改。
SQL> select t2,count(*) from youyus group by t2;
T2 COUNT(*)
---------- ----------
10 2
20 2
30 2
40 3
执行计划
----------------------------------------------------------
Plan hash value: 1349668650
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 8 | 3 (34)| 00:00:01 |
| 1 | SORT GROUP BY | | 4 | 8 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS FULL| YOUYUS | 9 | 18 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
/*optimizer_features_enable设为10.2.0.5 一切正常*/
SQL> alter session set optimizer_features_enable="11.2.0.1";
会话已更改。
SQL> select t2,count(*) from youyus group by t2;
T2 COUNT(*)
---------- ----------
10 2
20 2
30 2
40 3
执行计划
----------------------------------------------------------
Plan hash value: 1349668650
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 8 | 3 (34)| 00:00:01 |
| 1 | SORT GROUP BY | | 4 | 8 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS FULL| YOUYUS | 9 | 18 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
/*11.2.0.1中没有变化*/
SQL> alter session set optimizer_features_enable="8.1.7";
会话已更改。
SQL> alter session set "_gby_hash_aggregation_enabled" =true;
会话已更改。
/*看看optimizer_features_enable设为8.1.7，而_gby_hash_aggregation_enabled为true,这种"矛盾"情况下的表现*/
SQL> select t2,count(*) from youyus group by t2;
T2 COUNT(*)
---------- ----------
30 2
20 2
40 3
10 2
执行计划
----------------------------------------------------------
Plan hash value: 2940504347
-------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 8 | 10 |
| 1 | HASH GROUP BY | | 4 | 8 | 10 |
| 2 | TABLE ACCESS FULL| YOUYUS | 9 | 18 | 1 |
-------------------------------------------------------------
Note
-----
- cpu costing is off (consider enabling it)
/*居然仍采用了HASH GROUP BY,看起来类似_gby_hash_aggregation_enabled这类参数优先级要高于optimizer_features_enable*/
9i上的表现如下:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
SQL> analyze table youyus_9i compute statistics for all columns;
Table analyzed.
SQL> select * from youyus_9i;
T1 T2
-- ----------
A 10
B 10
F 30
G 30
H 40
I 40
J 40
L 20
M 20
9 rows selected.
SQL> alter session set optimizer_mode=ALL_ROWS;
Session altered.
SQL> select t2,count(*) from youyus_9i group by t2;
T2 COUNT(*)
---------- ----------
10 2
20 2
30 2
40 3
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=4 Bytes=8)
1 0 SORT (GROUP BY) (Cost=4 Card=4 Bytes=8)
2 1 TABLE ACCESS (FULL) OF 'YOUYUS_9I' (Cost=2 Card=21 Bytes
=42)
/*9i下虽然没有指定order by,但我们可以放心返回的数据总是排序的;*/
SQL> alter session set "_gby_hash_aggregation_enabled" =true;
alter session set "_gby_hash_aggregation_enabled" =true
*
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION
/*9i下不存在_gby_hash_aggregation_enabled隐式参数*/
That's great!
应用脚本没有数据一定要正确排序的强制要求，但使用HASH GROUP BY算法后临时表空间的使用量大幅上升，远大于之前在9i上的使用量，最后导致语句无法顺利完成。首先想到的当然是通过修改_gby_hash_aggregation_enabled参数恢复到原先的SORT算法，并观察其临时表空间使用量:
SQL> alter session set "_gby_hash_aggregation_enabled"=false;
Session altered.
SQL> select /* g_all_cdr01,60 */
2 calling_num mobile_number,
3 lac,
4 lpad(cell_id,5,'0') cell_id,
5 count(*) c,
6 sum(call_duration) call_duration,
7 sum(decode(record_type,'00',1,0)*call_duration) moc_call_duration,
8 sum(decode(record_type,'01',1,0)*call_duration) mtc_call_duarion
9 from gprs_bill.g_all_cdr01
10 where substr(calling_num,1,7) in (select mobile_prefix from gprs_bill.zou_mobile_prefix)
11 group by
12 calling_num ,
13 lac,
14 lpad(cell_id,5,'0');
Execution Plan
----------------------------------------------------------
Plan hash value: 4013005149
--------------------------------------------------------------------------------
-------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost
(%CPU)| Time |
--------------------------------------------------------------------------------
-------------------
| 0 | SELECT STATEMENT | | 229K| 9880K| | 103
3K (3)| 03:26:41 |
| 1 | SORT GROUP BY | | 229K| 9880K| 22M| 103
3K (3)| 03:26:41 |
|* 2 | HASH JOIN RIGHT SEMI| | 229K| 9880K| | 103
0K (3)| 03:26:10 |
| 3 | TABLE ACCESS FULL | ZOU_MOBILE_PREFIX | 1692 | 13536 | | 1
1 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | G_ALL_CDR01 | 388M| 13G| | 102
6K (2)| 03:25:21 |
--------------------------------------------------------------------------------
-------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MOBILE_PREFIX"=SUBSTR("CALLING_NUM",1,7))
/*重新执行出现问题的脚本*/
create table gprs_bill.zou_201007_cell_id as
select /* g_all_cdr01,60 */
calling_num mobile_number,
lac,
lpad(cell_id,5,'0') cell_id,
count(*) c,
sum(call_duration) call_duration,
sum(decode(record_type,'00',1,0)*call_duration) moc_call_duration,
sum(decode(record_type,'01',1,0)*call_duration) mtc_call_duarion
from gprs_bill.g_all_cdr01
where substr(calling_num,1,7) in (select mobile_prefix from gprs_bill.zou_mobile_prefix)
group by
calling_num ,
lac,
lpad(cell_id,5,'0');
可以看到在会话级别设置_gby_hash_aggregation_enabled为false后，Oracle不再采用10g中的HASH分组算法；因为该CTAS SQL脚本运行时间较长，我们通过动态视图V$SORT_USAGE来观察其运行期间的排序段使用量:
SQL> set time on; 14:30:59 SQL> select tablespace,contents,segtype,blocks*8/1024 from v$sort_usage where username='GPRS_BILL'; TABLESPACE CONTENTS SEGTYPE BLOCKS*8/1024 ------------------------------- --------- --------- ------------- TS_HQY1_TEMP TEMPORARY SORT 9349 14:35:59 SQL> / TABLESPACE CONTENTS SEGTYPE BLOCKS*8/1024 ------------------------------- --------- --------- ------------- TS_HQY1_TEMP TEMPORARY SORT 10011 /*5分钟内共用10011-9349=662MB 临时空间*/ 15:02:46 SQL> select target ,totalwork,sofar,time_remaining,elapsed_seconds from v$session_longops where sofar!=totalwork; TARGET TOTALWORK SOFAR TIME_REMAINING ELAPSED_SECONDS ---------------------------------------------------------------- ---------- ---------- -------------- --------------- GPRS_BILL.G_ALL_CDR01 5575890 5435796 143 5557 15:05:10 SQL> select target ,totalwork,sofar,time_remaining,elapsed_seconds from v$session_longops where sofar!=totalwork; TARGET TOTALWORK SOFAR TIME_REMAINING ELAPSED_SECONDS ---------------------------------------------------------------- ---------- ---------- -------------- --------------- GPRS_BILL.G_ALL_CDR01 5575890 5562082 14 5692 15:05:13 SQL> select tablespace,contents,segtype,blocks*8/1024 from v$sort_usage where username='GPRS_BILL'; TABLESPACE CONTENTS SEGTYPE BLOCKS*8/1024 ------------------------------- --------- --------- ------------- TS_HQY1_TEMP TEMPORARY SORT 13835 15:12:22 SQL> select tablespace,contents,segtype,blocks*8/1024 from v$sort_usage where username='GPRS_BILL'; TABLESPACE CONTENTS SEGTYPE BLOCKS*8/1024 ------------------------------- --------- --------- ------------- TS_HQY1_TEMP TEMPORARY SORT 13922 /* 排序已经完成，排序段不再增长*/
该分组操作最后排序段使用量为13922MB，在客户可以接受的范围内。看起来新引入的HASH算法虽然有CPU成本低于SORT算法的优势，但可能消耗大量临时空间，可谓有得有失。
7月13日，11g release 2 的第二个补丁集更新发布了；9i的最终版本为9.2.0.8,10g上10.2.0.5很有可能成为最终版本，我们预期今后(11g,12g)中Patch set数量会有效减少，而patch set update数量可能大幅增加；这样的更新形式可以为Oracle Database提升一定的软件形象。可以猜想11gr2的最终版本号可能是11.2.0.2/3.x。
附该psu的readme note:
Released: July 13, 2010
This document is accurate at the time of release. For any changes and additional information regarding PSU 11.2.0.1.2, see these related documents that are available at My Oracle Support (http://support.oracle.com/):
This document includes the following sections:
Patch Set Update (PSU) patches are cumulative. That is, the content of all previous PSUs is included in the latest PSU patch.
PSU 11.2.0.1.2 includes the fixes listed in Section 5, “Bugs Fixed by This Patch”.
Table 1 describes installation types and security content. For each installation type, it indicates the most recent PSU patch to include new security fixes that are pertinent to that installation type. If there are no security fixes to be applied to an installation type, then “None” is indicated. If a specific PSU is listed, then apply that or any later PSU patch to be current with security fixes.
| Installation Type | Latest PSU with Security Fixes |
|---|---|
| Server homes | PSU 11.2.0.1.2
|
| Client-Only Installations | None |
| Instant Client Installations | None
(The Instant Client installation is not the same as the client-only Installation. For additional information about Instant Client installations, see Oracle Database Concepts.) |
This section includes the following sections:
For a list of platforms that are supported in this Patch Set Update, see My Oracle Support Note 1060989.1 Critical Patch Update July 2010 Patch Availability Document for Oracle Products.
You must use the OPatch utility version 11.2.0.1.0 or later to apply this patch. Oracle recommends that you use the latest released OPatch 11.2, which is available for download from My Oracle Support patch 6880880 by selecting the 11.2.0.0.0 release.
For information about OPatch documentation, including any known issues, see My Oracle Support Note 293369.1 OPatch documentation list.
These instructions are for all Oracle Database installations.
Before you install PSU 11.2.0.1.2, perform the following actions to check the environment and to detect and resolve any one-off patch conflicts.
If you are installing the PSU to an environment that has Automatic Storage Management (ASM), note the following:
make, ar, ld, and nm.The location of these executables depends on your operating system. On many operating systems, they are located in /usr/ccs/bin, in which case you can set your PATH definition as follows:
export PATH=$PATH:/usr/ccs/bin
For an introduction to the PSU one-off patch concepts, see “Patch Set Updates Patch Conflict Resolution” in My Oracle Support Note 854428.1 Patch Set Updates for Oracle Products.
The fastest and easiest way to determine whether you have one-off patches in the Oracle home that conflict with the PSU, and to get the necessary conflict resolution patches, is to use the Patch Recommendations and Patch Plans features on the Patches & Updates tab in My Oracle Support. These features work in conjunction with the My Oracle Support Configuration Manager. Recorded training sessions on these features can be found in Note 603505.1.
However, if you are not using My Oracle Support Patch Plans, follow these steps:
unzip p9654983_11201_<platform>.zip opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./9654983
Follow these steps:
unzip p9654983_11201_<platform>.zip cd 9654983 opatch apply
After installing the patch, perform the following actions:
Apply the patch conflict resolution one-off patches that were determined to be needed when you performed the steps in Section 2.3.1.3, “One-off Patch Conflict Detection and Resolution”.
The following steps load modified SQL files into the database. For a RAC environment, perform these steps on only one node.
catbundle.sql script as follows:
cd $ORACLE_HOME/rdbms/admin sqlplus /nolog SQL> CONNECT / AS SYSDBA SQL> STARTUP SQL> @catbundle.sql psu apply SQL> QUIT
The catbundle.sql execution is reflected in the dba_registry_history view by a row associated with bundle series PSU.
For information about the catbundle.sql script, see My Oracle Support Note 605795.1 Introduction to Oracle Database catbundle.sql.
$ORACLE_HOME/cfgtoollogs/catbundle for any errors:
catbundle_PSU_<database SID>_APPLY_<TIMESTAMP>.log catbundle_PSU_<database SID>_GENERATE_<TIMESTAMP>.log
where TIMESTAMP is of the form YYYYMMMDD_HH_MM_SS. If there are errors, refer to Section 3, “Known Issues”.
These instructions are for a database that is created or upgraded after the installation of PSU 11.2.0.1.2.
You must execute the steps in Section 2.3.3.2, “Loading Modified SQL Files into the Database” for any new database only if it was created by any of the following methods:
These instructions apply if you need to deinstall the patch.
Follow these steps:
$ORACLE_HOME/rdbms/admin/catbundle_PSU_<database SID>_ROLLBACK.sql file exists for each database associated with this ORACLE_HOME. If this is not the case, you must execute the steps in Section 2.3.3.2, “Loading Modified SQL Files into the Database” against the database before deinstalling the PSU.rollback argument as follows.
opatch rollback -id 9654983
Follow these steps:
ORACLE_HOME, connect to the database using SQL*Plus as SYSDBA and run the rollback script as follows:
cd $ORACLE_HOME/rdbms/admin sqlplus /nolog SQL> CONNECT / AS SYSDBA SQL> STARTUP SQL> @catbundle_PSU_<database SID>_ROLLBACK.sql SQL> QUIT
In a RAC environment, the name of the rollback script will have the format catbundle_PSU_<database SID PREFIX>_ROLLBACK.sql.
$ORACLE_HOME/cfgtoollogs/catbundle and is named catbundle_PSU_<database SID>_ROLLBACK_<TIMESTAMP>.log where TIMESTAMP is of the form YYYYMMMDD_HH_MM_SS. If there are errors, refer to Section 3, “Known Issues”.Follow these steps for each node in the cluster, one node at a time:
rollback argument as follows.
opatch rollback -id 9654983
If there are errors, refer to Section 3, “Known Issues”.
srvctl start instance
Follow the instructions listed in Section Section 2.4.2, “Patch Post-Deinstallation Instructions for a Non-RAC Environment” only on the node for which the steps in Section 2.3.3.2, “Loading Modified SQL Files into the Database” were executed during the patch application.
All other instances can be started and accessed as usual while you are executing the deinstallation steps.
For information about OPatch issues, see My Oracle Support Note 293369.1 OPatch documentation list.
For issues documented after the release of this PSU, see My Oracle Support Note 1089071.1 Oracle Database Patch Set Update 11.2.0.1.2 Known Issues.
Other known issues are as follows.
catbundle.sql script or its rollback script:
ORA-29809: cannot drop an operator with dependent objects ORA-29931: specified association does not exist ORA-29830: operator does not exist ORA-00942: table or view does not exist ORA-00955: name is already used by an existing object ORA-01430: column being added already exists in table ORA-01432: public synonym to be dropped does not exist ORA-01434: private synonym to be dropped does not exist ORA-01435: user does not exist ORA-01917: user or role 'XDB' does not exist ORA-01920: user name '<user-name>' conflicts with another user or role name ORA-01921: role name '<role name>' conflicts with another user or role name ORA-01952: system privileges not granted to 'WKSYS' ORA-02303: cannot drop or replace a type with type or table dependents ORA-02443: Cannot drop constraint - nonexistent constraint ORA-04043: object <object-name> does not exist ORA-29832: cannot drop or replace an indextype with dependent indexes ORA-29844: duplicate operator name specified ORA-14452: attempt to create, alter or drop an index on temporary table already in use ORA-06512: at line <line number>. If this error follow any of above errors, then can be safely ignored. ORA-01927: cannot REVOKE privileges you did not grant
The following documents are references for this patch.
Note 293369.1 OPatch documentation list
Note 360870.1 Impact of Java Security Vulnerabilities on Oracle Products
Note 468959.1 Enterprise Manager Grid Control Known Issues
Note 9352237.8 Bug 9352237 – 11.2.0.1.1 Patch Set Update (PSU)
This patch includes the following bug fixes.
CPU molecules in PSU 11.2.0.1.2:
PSU 11.2.0.1.2 contains the following new CPU molecules:
9676419 – DB-11.2.0.1-MOLECULE-004-CPUJUL2010
9676420 – DB-11.2.0.1-MOLECULE-005-CPUJUL2010
PSU 11.2.0.1.2 contains the following new fixes:
8755082 – ORA-00600: [KCFIS_TRANSLATE4:VOLUME LOOKUP], [2], [WRONG DEVICE NAME], [], [], [
8890026 – ASM PARTNERING CREATES IMBALANCED PARTNERSHIPS
9170608 – STBH:DD BLOCKS PINNED FOR QUERIES THAT DO NOT REQUEST USED SPACE
9363145 – STBH:DB INSTANCES TERMINATED BY ASMB DUE TO ORA-00600 [KFDSKALLOC0]
8330783 – HANGING DB WITH “CACHE BUFFER CHAINS” AND “BUFFER DEADLOCK” WAITS DURING INSERT
8822531 – TAKING AWR SNAP HANGS
8918433 – UNPERSISTED FSFO STATE BITS CAN GET PERSISTED
9363384 – PHYSICAL STANDBY SERVICES NOT STARTED AFTER CONVERT FROM SNAPSHOT
9467635 – BROKER’S METADATA FILE UPGRADE TO 11.2 IS BROKEN
9467727 – GETSTATUS DOC YIELDS INCORRECT RESULT IF DBRESOURCE_ID PROP VALUE IS USED
8774868 – LGSBFSFO: ORA-600 [3020], [3], [138] RAISED IN RECOVERY SLAVE
8822832 – V$ARCHIVE_DEST_STATUS HAS INCORRECT VALUE FOR APPLIED_SEQ#
8872096 – ARCHIVING FORCED DURING CLOSE WHEN NO STANDBY IS PRESENT
9399090 – STBH: CONSTANT/HIGH FREQUENT LOG SWITCHES ON BEEHIVE DATABASE IN THE LAST 3 DAYS
8865718 – RECURSIVE CURSORS CONTAINING “AS OF SNAPSHOT” CLAUSE ARE NOT SHARED
8981059 – HIGH VERSION COUNT:BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,OPTIMIZER_MODE_MISMATCH
9010222 – APPS ST 11G ORA-00600 [KKSFBC-REPARSE-INFINITE-LOOP]
9067282 – TB:SH:ORA-00600:[KKSFBC-WRONG-KKSCSFLGS] WHILE RUNNING TPC-H
9255542 – ARRAY INSERT TO PARTITIONED TABLE LOOSES ROWS DUE TO CONCURRENT DDL (ORA-14403)
9488887 – FORIEGN KEY VIOLATION WITH ARRAY-INSERT AND ONLINE IDX REBUILD AFTER BUG-9255542
8834425 – ORA-240 IN RVWR PROCESS CAUSING 5MIN TRANSACTIONAL HANG
9210925 – AFTER MANUAL UPGRADE TO 11.1.0.7 PL/SQL CALLS INCORRECT FUNCTION
8505803 – PRE_PAGE_SGA RESULTS IN EXCESSIVE PAGE TABLE SIZE WHEN USING MEMORY_TARGET [AMM]
9165206 – PARTITIONING ORA-600 [KKPOLLS1] / [KKDOILSF1] – DURING PARTITION MAINTANANCE
8875671 – LX64: ORA-600 ARGS [KJPNP_CHK:!MASTER_READY],
9093300 – LOTS OF REPEATED KJXOCDR: DROP DUPLICATE OPEN MESSAGE IN LMD TRACE
8544696 – TABLE GROWTH – BLOCKS ARE NOT REUSED
8650719 – DOWNSTREAM CAPTURE ABORTS WITH ORA-26766
8856478 – RAM SECUREFILE PERF DEGRADATION WITH SF COMPRESSION ON SMALL LOBS DURING ATB MOVE
9272086 – STBH: DATA PUMP WRITER SEEMS TO BE WAITING ON WAIT FOR UNREAD MESSAGE ON BROADCA
8909984 – APPSST GSI 11G: GAPS IN AWR SNAPSHOTS
9068088 – MEDIA RECOVERY WAS HUNG ON STANDBY
9145541 – ORA-600 [25027] / ORA-600 [4097] FOR ACTIVE TX IN A PLUGGED TABLESPACE
9167285 – PKT-BUGOLTP: ORA-07445: [KCRALC()+87]
7519406 – ‘J000’ TRACE FILE REGARDING GATHER_STATS_JOB INTERMITTENTLY SINCE 10.2.0.4
8815639 – [11GR2-LNX-090813] MULTIPLE INSERT CAUSE DATA ALLOCATION ABOVE HHWM
9216806 – HIGH “ENQ: TS – CONTENTION” FOR TEMPORARY SEGMENT WHILE SQLLDR DIRECT PATH LOAD
9242411 – STRESS-BIGBH: LOTS OF OR-3113S IN BIGBH STRESS TEST
9461782 – ORA-7445 [KTSLF_SUMFSG()+54] [SIGSEGV] AND KTSLFSUM_CFS ON CALL STACK
9011088 – [11GR2]ADDING COLUMN TO COMPRESSED TABLE, DATA LOSS OCCURED.
9275072 – APPSST GSI 11G : BUFFER BUSY WAITS INSERTING INTO TABLES
9341448 – APPSST GSI 11G : BUFFER BUSY WAITS AND LATCH: CACHE BUFFERS WAITS WHEN INSERTING
9637033 – ORA-07445[KDR9IR2RST0] INSERT AS SELECT IN A COMPRESSED TABLE WITH > 255 COLUMNS
8664189 – ORA-00600 [KDISS_UNCOMPRESS: BUFFER LENGTH]
9119194 – PSRC: DISTRIBUTED QUERY SLOWER IN 10.2.0.4 COMPARED TO 10.2.0.3
8268775 – PERF: HIGH US ENQUEUE CONTENTION DURING A LOGIN STORM OR SESSION FAILOVER
8803762 – ORA-00600 [KDSGRP1] BLOCK CORRUPTION ON 11G DATABASE UPGRADE
8431487 – INSTANCE CRASH ORA-07445 [KGGHSTFEL()+192] ORA-07445[KGGHSTMAP()+241]
9713537 – ENHANCE CAUSE/ACTION FIELDS OF THE INTERNAL ERROR ORA-00600
9714832 – ENHANCE CAUSE/ACTION FIELDS OF THE INTERNAL ERROR ORA-07445
