sql – 第2页

Does DB Time include wait on cpu queue time?

2010/08/16 by admin 3 Comments

DB TIME1=DB CPU2+ Foreground NO-Idle wait time

1：Amount of elapsed time (in microseconds) spent performing Database user-level calls. This does not include the elapsed time spent on instance background processes such as PMON.

2:Amount of CPU time (in microseconds) spent on database user-level calls. This does not include the CPU time spent on instance background processes such as PMON.

10g中引入了基于时间统计信息的调优模型，其核心或者说最值得我们关心的大概是DB TIME和AASC 2项指标。DB TIME是Oracle在数据库层对用户级别(不包括后台进程的CPU和非空闲等待时间)各类操作耗时的一个总计，它直接反映了数据库前台的”工时”。DB TIME是否包含了前台进程在CPU队列上的等待时间呢，换而言之我们的命题是”Does DB TIME equal to DB CPU+Foreground NO-Idle wait time + DB CPU ON QUEUE ?”

让我们来看看以下测试：

[maclean@rh2 ~]$ cat /proc/cpuinfo |grep processor|wc -l
2
/*这是一台双核的pc服务器*/

打开session A:
begin
/* first one busy on cpu */
loop
null;
end loop;
end;
/*死循环调用NULL，会尝试独占一个逻辑CPU,没有其他等待事件*/

打开SESSION B:
begin
/* second one busy on cpu */
loop
null;
end loop;
end;

打开SESSION C:
begin
/* third one busy on cpu */
loop
null;
end loop;
end;

打开SESSION D:
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.

/*手动收集AWR 快照*/

/*等待几分钟，以便收集信息*/

SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
/*再次手动收集快照*/

我们来看看AWR报告中的Time Model Statistics:

DB TIME为344s,而DB CPU为226s。以上匿名PL/SQL块仅引起CPU争用(不会产生其他前台等待事件)，而这里DB TIME要远大于DB CPU，可见DB TIME=DB CPU+Foreground wait time+Cpu on queue; 我们也可以观察以下OEM界面上的AAS图形:

ASH报告也正确反映了这一事实，虽然它把cpu time和wait on cpu queue归并做了Cpu+wait for CPU:

可以看到CPU ON QUEUE有时被计为Wait time部分。当以上3个回话同时执行CPU敏感的匿名块过程时，其瞬时的Average Session Count为3，而该pc服务器上只有2个逻辑CPU，可以认为实例在该短期内存出现严重阻塞，表现到OS层也就是短期内持续队列较高。

[maclean@rh2 ~]$ vmstat 2
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 3  0      0 752180  93912 2127440    0    0   194   168  536  302  6  1 90  3  0
 3  0      0 752056  93912 2127508    0    0     0     0 1012  661 100  0  0  0  0
 2  0      0 751436  93928 2127548    0    0     0   932 1131  750 99  1  0  0  0
 2  0      0 751436  93928 2127596    0    0     0    16 1012  644 99  0  0  0  0
 2  0      0 751444  93928 2127596    0    0     0     0 1011  695 100  0  0  0  0

Filed Under: Oracle Tagged With: database, ORA, Oracle, RAC, sql

8i查询DBA_FREE_SPACE视图极慢的问题

2010/08/13 by admin Leave a Comment

还是那套古老的8.1.7.4，在该系统上检查表空间使用情况的SQL运行缓慢，其SQL如下:

SELECT D.TABLESPACE_NAME,
       SPACE "SUM_SPACE(M)",
       SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
       ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
       FREE_SPACE "FREE_SPACE(M)"
  FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) D,
       (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F
 where d.tablespace_name = f.tablespace_name(+)
 order by "USED_RATE(%)" desc;
/*很面熟的DBA常用脚本吧？*/

经确认其中对DBA_FREE_SPACE视图的查询耗费了大量时间，8i中该视图的默认定义是:

select ts.name,
       fi.file#,
       f.block#,
       f.length * ts.blocksize,
       f.length,
       f.file#
  from sys.ts$ ts, sys.fet$ f, sys.file$ fi
 where ts.ts# = f.ts#
   and f.ts# = fi.ts#
   and f.file# = fi.relfile#
   and ts.bitmapped = 0
/*以上查询DMT表空间上的FREE EXTENT*/
union all
/*以下查询LMT表空间上的FREE EXTENT*/
select /*+ ordered use_nl(f) use_nl(fi) */
 ts.name,
 fi.file#,
 f.ktfbfebno,
 f.ktfbfeblks * ts.blocksize,
 f.ktfbfeblks,
 f.ktfbfefno
  from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
 where ts.ts# = f.ktfbfetsn
   and f.ktfbfetsn = fi.ts#
   and f.ktfbfefno = fi.relfile#
   and ts.bitmapped <> 0
   and ts.online$ in (1, 4)
   and ts.contents$ = 0

/*也许你感到奇怪，实际上8i中就有了本地管理模式的表空间了，只是很少有人用。("In Oracle 8i the EXTENT MANAGEMENT clause was introduced into the CREATE TABLESPACE statement allowing extent management to be LOCAL or DICTIONARY. Locally Manages Tablespaces (LMT) have a bitmap of the blocks, or groups of blocks, they contain allowing them to track extent allocation without reference to the data dictionary.")*/

/*因字典管理模式下FET$基表往往较大，导致UNION ALL以上部分在连接操作时会产生大量的逻辑读，最终导致了对DBA_FREE_SPACE视图的查询十分缓慢。*/

Oracle 提供了官方的视图并不意味着我们非它不可用，可以通过修改DBA_FREE_SPACE的定义，或另建一个具有相同功能但查询SQL构造不同的视图来加快查询速度:

explain plan for

select /*+use_hash (tsfi, fet2 ) */

 tsfi.tablespace_name,

 tsfi.file_id,

 fet2.block_id,

 tsfi.blocksize * fet2.blocks,

 fet2.blocks,

 tsfi.relfile#

  from (select /*+ use_hash ( ts, fi ) */

         ts.name      tablespace_name,

         fi.file#     file_id,

         ts.BLOCKSIZE,

         fi.relfile#,

         ts.ts#

          from sys.ts$ ts, sys.file$ fi

         where ts.ts# = fi.ts#

           and ts.online$ in (1, 4)) tsfi,

       (select f.block# block_id, f.length blocks, f.file# file_id, f.ts#

          from sys.fet$ f

        union all

        select f.ktfbfebno  block_id,

               f.ktfbfeblks blocks,

               f.ktfbfefno,

               ktfbfetsn

          from sys.x$ktfbfe f) fet2

 where fet2.file_id = tsfi.relfile#

   and fet2.ts# = tsfi.ts# /*此查询需SYSDBA权限*/ ;

Explained

select * from  table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 717737944

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |    20 |  1560 |     9  (12)| 00:00:01 |
|*  1 |  HASH JOIN           |          |    20 |  1560 |     9  (12)| 00:00:01 |
|*  2 |   HASH JOIN          |          |     4 |   104 |     6  (17)| 00:00:01 |
|   3 |    TABLE ACCESS FULL | FILE$    |     4 |    36 |     2   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL | TS$      |     5 |    85 |     3   (0)| 00:00:01 |
|   5 |   VIEW               |          |   101 |  5252 |     3   (0)| 00:00:01 |
|   6 |    UNION-ALL         |          |       |       |            |          |
|   7 |     TABLE ACCESS FULL| FET$     |     1 |    52 |     3   (0)| 00:00:01 |
|   8 |     FIXED TABLE FULL | X$KTFBFE |   100 |  5200 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("FET2"."FILE_ID"="FI"."RELFILE#" AND
              "FET2"."TS#"="TS"."TS#")
   2 - access("TS"."TS#"="FI"."TS#")
   4 - filter("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4)

/*改写后可以大幅减少逻辑读从而提高性能*/

/*可以建立DBA_FREE_SPACE功能相同的替代品，并代入到表空间使用率的脚本中*/
CREATE OR REPLACE VIEW DBA_FREE_SPACE_NEW (
  TABLESPACE_NAME,
  FILE_ID,
  BLOCK_ID,
  BYTES,
  BLOCKS,
  RELATIVE_FNO
) AS
select /*+use_hash (tsfi, fet2 ) */
 tsfi.tablespace_name,
 tsfi.file_id,
 fet2.block_id,
 tsfi.blocksize * fet2.blocks,
 fet2.blocks,
 tsfi.relfile#
  from (select /*+ use_hash ( ts, fi ) */
         ts.name      tablespace_name,
         fi.file#     file_id,
         ts.BLOCKSIZE,
         fi.relfile#,
         ts.ts#
          from sys.ts$ ts, sys.file$ fi
         where ts.ts# = fi.ts#
           and ts.online$ in (1, 4)) tsfi,
       (select f.block# block_id, f.length blocks, f.file# file_id, f.ts#
          from sys.fet$ f
        union all
        select f.ktfbfebno  block_id,
               f.ktfbfeblks blocks,
               f.ktfbfefno,
               ktfbfetsn
          from sys.x$ktfbfe f) fet2
 where fet2.file_id = tsfi.relfile#
   and fet2.ts# = tsfi.ts#  /*建此视图需SYSDBA权限*/ ;

Filed Under: Oracle Tagged With: ORA, Oracle, RAC, sql

Segment in recyclebin? Is it free?

2010/08/13 by admin 5 Comments

考过10g ocp的朋友大概都看到过这样的问题，回收站中的对象所占空间是否算作free space？

纸上得来终觉浅，我们实地考察一下:

SQL> set long 99999999;
/*DBA_FREE_SPACE视图列出了数据库中所有表空间上空闲的区间,利用该视图我们可以计算表空间使用率等
注意该视图不会列出本地管理模式中offline的数据文件(或表空间)上的相关区间信息*/

SQL> select text from dba_views where view_name='DBA_FREE_SPACE';

TEXT
--------------------------------------------------------------------------------
select ts.name, fi.file#, f.block#,
       f.length * ts.blocksize, f.length, f.file#
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# = f.ts#
  and f.ts# = fi.ts#
  and f.file# = fi.relfile#
  and ts.bitmapped = 0
union all
select /*+ ordered use_nl(f) use_nl(fi) */
       ts.name, fi.file#, f.ktfbfebno,
       f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where ts.ts# = f.ktfbfetsn
  and f.ktfbfetsn = fi.ts#
  and f.ktfbfefno = fi.relfile#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
select /*+ ordered use_nl(u) use_nl(fi) */
       ts.name, fi.file#, u.ktfbuebno,
       u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where ts.ts# = rb.ts#
  and rb.ts# = fi.ts#
  and u.ktfbuefno = fi.relfile#
  and u.ktfbuesegtsn = rb.ts#
  and u.ktfbuesegfno = rb.file#
  and u.ktfbuesegbno = rb.block#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
select ts.name, fi.file#, u.block#,
       u.length * ts.blocksize, u.length, u.file#
from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb
where ts.ts# = u.ts#
  and u.ts# = fi.ts#
  and u.segfile# = fi.relfile#
  and u.ts# = rb.ts#
  and u.segfile# = rb.file#
  and u.segblock# = rb.block#
  and ts.bitmapped = 0

/*可以看到后2个子查询链接中存在recyclebin$基表*/

SQL> show user;
User is "system"

SQL> purge recyclebin;

Done

SQL> create table YOUYUS tablespace users as select * from dba_objects;

Table created

SQL> select sum(bytes) from dba_free_space where tablespace_name='USERS';

SUM(BYTES)
----------
    851968

SQL> drop table YOUYUS;

Table dropped

SQL> col ORIGINAL_NAME   for a10;
SQL> col ts_name for a10;
SQL> select original_name,operation,type,ts_name,space from dba_recyclebin;

ORIGINAL_N OPERATION TYPE                      TS_NAME         SPACE
---------- --------- ------------------------- ---------- ----------
YOUYUS     DROP      TABLE                     USERS            1152
/* 这里的SPACE单位是standard block size,1152 * 8k=9216k */

SQL> select sum(bytes)  from dba_free_space where tablespace_name='USERS';

SUM(BYTES)
----------
  10289152
/* 可以看到YOUYUS表被回收后，USERS表空间上的FREE EXTENT空间也随之增长了;10289152-851968=9216k 与YOUYUS表的大小吻合*/

col name for a10;
/*通过以下查询可以发现数据库中本地管理模式表空间上已被回收对象可以被覆盖重用的区间信息*/
select /*+ ordered use_nl(u) use_nl(fi) */
       ts.name, fi.file#, u.ktfbuebno,
       u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where ts.ts# = rb.ts#
  and rb.ts# = fi.ts#
  and u.ktfbuefno = fi.relfile#
  and u.ktfbuesegtsn = rb.ts#
  and u.ktfbuesegfno = rb.file#
  and u.ktfbuesegbno = rb.block#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0;
NAME            FILE#  KTFBUEBNO U.KTFBUEBLKS*TS.BLOCKSIZE KTFBUEBLKS  KTFBUEFNO
---------- ---------- ---------- ------------------------- ---------- ----------
USERS               4        184                     65536          8          4
USERS               4        192                     65536          8          4
USERS               4        200                     65536          8          4
USERS               4        208                     65536          8          4
USERS               4        216                     65536          8          4
USERS               4        224                     65536          8          4
USERS               4        232                     65536          8          4

So We can reuse segment space which resided in recyclebin!
That' great!

Filed Under: Oracle Tagged With: sql

ora-600 [17182]错误一例

2010/08/12 by admin 4 Comments

这是一套古老的系统，SUNOS 5.8，Oracle 8.1.7.4。最近老革命途遇新问题，告警日志烽烟掠起：

Errors in file /u01/app/oracle/admin/CULPRODB/udump/culprodb_ora_7913.trc:
ORA-00600: internal error code, arguments: [17182], [32438472], [], [], [], [], [], []
Thu Jul 15 16:19:29 2010
Errors in file /u01/app/oracle/admin/CULPRODB/udump/culprodb_ora_7913.trc:
ORA-00600: internal error code, arguments: [17182], [32438472], [], [], [], [], [], []
Thu Jul 15 16:19:30 2010
Errors in file /u01/app/oracle/admin/CULPRODB/udump/culprodb_ora_7913.trc:
ORA-00600: internal error code, arguments: [17182], [32438472], [], [], [], [], [], []

如果你像我一样对600着迷，那么点击这里欣赏一下这个trace文件。报错期间运行的SQL及调用栈信息:

ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [17182], [32438472], [], [], [], [], [], []
Current SQL statement for this session:
select * from olsuser.cardmaster where cm_card_no between '2336330010201570013' and '2336330010201580004' union
select * from olsuser.cardmaster where cm_card_no between '2336330012402300018' and '2336330012402310009' union
select * from olsuser.cardmaster where cm_card_no between '2336330052400220016' and '2336330052400230007' union
select * from olsuser.cardmaster where cm_card_no between '2336330015103900012' and '2336330015138100032' union
select * from olsuser.cardmaster where cm_card_no between '2336330055100910018' and '2336330055100920009'
----- Call Stack Trace -----
calling                   call     entry
location                  type     point
--------------------      -------- --------------------
ksedmp()+220              CALL     ksedst()+0
kgeriv()+268              PTR_CALL 0000000000000000
kgesiv()+140              CALL     kgeriv()+0
kgesic1()+32              CALL     kgesiv()+0
kghfrf()+204              CALL     kgherror()+0
kkscls()+1592             CALL     kghfrf()+0
opicca()+248              CALL     kkscls()+0
opiclo()+8                CALL     opicca()+0
kpoclsa()+60              CALL     opiclo()+0
opiodr()+2540             PTR_CALL 0000000000000000
ttcpip()+5676             PTR_CALL 0000000000000000
opitsk()+2408             CALL     ttcpip()+0
opiino()+2080             CALL     opitsk()+0
opiodr()+2540             PTR_CALL 0000000000000000
opidrv()+1656             CALL     opiodr()+0
sou2o()+16                CALL     opidrv()+0
main()+172                CALL     sou2o()+0
_start()+380              CALL     main()+0
/*8.1.7中stack trace还附带着寄存器信息，但我们可读不懂:)  */

opicca->kkscls->kghfrf->kgherror(heap层报错)->kgesic1。问题主要发生在调用kghfrf函数的时候，《famous summary stack trace from Oracle Version 8.1.7.4.0 Bug Note》一文罗列了Oracle的一些stack summary,其中kghfrx函数的作用是”Free extent. This is called when a heap is unpinned to request that it”；可以猜测kghfrf函数是用来释放某种内存结构的。在MOS上输入”kghfrf 8.1.7.4″关键词，可以找到Note 291936.1:

ORA-00600 [17182] on Oracle 8.1.7.4.0 After a CTRL-C or Client Termination
Applies to:
Oracle Server – Enterprise Edition – Version: 8.1.7.4
This problem can occur on any platform.
Checked for relevance on 06-Mar-2007

Oracle RDBMS Server Versions prior to 9i
Symptoms
1. Intermittent heap corruptions errors like ORA-00600 [17182] are reported in the alert.log file.

2. There is no impact to the database other than the process which encounters the errors getting killed.

3. From the trace file generated for this ORA-00600 error, check if the top few functions are :

kgherror kghfrf kkscls opicca

Cause
If the trace file shows that kkscls calls kghfrf, then it is related to:

Bug 2281320 — ORA-600[17182] POSSIBLE AFTER CTRL-C OR CLIENT DEATH
Solution
The problem is when we call kghfrf to free a chunk of memory, we expect that this chunk to have been allocated from the Heap Memory and hence have a valid header, although internally we have used Frame Memory managed chunk. As a result, kghfrf errors out with the “Bagic Magic Number” in the Memory Chunk header error message.

If you are running Oracle 8174, encounter this ORA-00600 [17182], and the call stack indicates the following functions { kgherror kghfrf kkscls }, then download and apply Patch 2281320 from MetaLink.

This issue has been fixed in Oracle Server 8.1.7.5 and later versions.

Note 2281320.8 is not limited to dblinks and can occur during normal database operation as well.

该文档叙述描述在9i以前版本中可能因堆损坏而出现该ORA-00600 [17182]错误，该错误不会导致致命问题或数据库损坏，最坏的情况是遭遇该错误的服务进程被杀死。与该问题匹配的主要依据是stack trace为kgherror kghfrf kkscls opicca，同我们的实际情况一致。可以通过打上one-off patch 2281320或者升级到8.1.7.5来避免该内部错误的发生，当然也可以置之不理，显然它不会造成太大的麻烦。
此外kghfrf函数用以释放内存chunk，Oracle development起初以为所有这些可能被释放的chunk都是从堆内存中分配而来，因此都该有一个有效的header;而实际上它们可能是以帧式内存管理的chunk。kghfrf因读取到这种chunk header中的错误幻数(Bagic Magic Number)而误入歧途了。

Filed Under: Oracle Tagged With: database, internal error, ORA, ora-00600, Oracle, Oracle Bug, oracle server, RAC, sql

如何公开Oracle trace文件？

2010/08/11 by admin 3 Comments

隐式参数_trace_files_public决定了Oracle产生的trace文件是否公开，该参数默认值为FALSE，也就是非DBA/OINSTALL组的用户是没有权限读取数据库产生的trace文件的;在某些场合中我们需要让非DBA组的用户也能访问trace文件，就可以通过修改该参数实现。请看下面的例子:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> col name for a20
SQL> col value for a20
SQL> col describ for a40
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  2   FROM SYS.x$ksppi x, SYS.x$ksppcv y
  3   WHERE x.inst_id = USERENV ('Instance')
  4   AND y.inst_id = USERENV ('Instance')
  5   AND x.indx = y.indx
  6  AND x.ksppinm LIKE '%_trace_files_public%'
  7  order by x.ksppinm;

NAME                 VALUE                DESCRIB
-------------------- -------------------- ----------------------------------------
_trace_files_public  FALSE                Create publicly accessible trace files

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug ipc;
Information written to trace file.
SQL> oradebug tracefile_name;
/s01/10gdb/admin/YOUYUS/udump/youyus_ora_10268.trc
SQL> !ls -l /s01/10gdb/admin/YOUYUS/udump/youyus_ora_10268.trc
-rw-r----- 1 maclean oinstall 4206 Aug 11 20:51 /s01/10gdb/admin/YOUYUS/udump/youyus_ora_10268.trc
/*所产生的trace文件权限为640,非oinstall组用户无权限读取该文件*/

SQL> alter system set "_trace_files_public"=true;
alter system set "_trace_files_public"=true
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
/*修改该参数需要重启实例*/

SQL> alter system set "_trace_files_public"=true scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2084264 bytes
Variable Size             922747480 bytes
Database Buffers          637534208 bytes
Redo Buffers               14692352 bytes
Database mounted.
Database opened.

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug ipc;
Information written to trace file.
SQL> oradebug tracefile_name;
/s01/10gdb/admin/YOUYUS/udump/youyus_ora_10430.trc
SQL> ! ls -l /s01/10gdb/admin/YOUYUS/udump/youyus_ora_10430.trc
-rw-r--r-- 1 maclean oinstall 5471 Aug 11 20:54 /s01/10gdb/admin/YOUYUS/udump/youyus_ora_10430.trc
/*other组用户也具有了读权限*/

SQL> ! ls -l /s01/10gdb/admin/YOUYUS/
total 24
drwxr-x--- 2 maclean oinstall 4096 Aug 11 20:56 adump
drwxr-x--- 2 maclean oinstall 4096 Aug 11 20:54 bdump
drwxr-x--- 2 maclean oinstall 4096 Aug  5 21:35 cdump
drwxr-x--- 2 maclean oinstall 4096 Aug  5 21:36 dpdump
drwxr-x--- 2 maclean oinstall 4096 Aug  5 21:37 pfile
drwxr-x--- 2 maclean oinstall 4096 Aug 11 20:54 udump
/*请注意修改_trace_files_public为true,并不会修改trace所在目录的权限，Oracle默认建立bdump/udump等trace目录时分配的权限为750,other组用户无法进入这些目录，需要修改目录权限为755,即o+r+x*/

SQL> ! chmod o+r+x /s01/10gdb/admin/YOUYUS/*dump

SQL>  ! ls -l /s01/10gdb/admin/YOUYUS/
total 24
drwxr-xr-x 2 maclean oinstall 4096 Aug 11 20:56 adump
drwxr-xr-x 2 maclean oinstall 4096 Aug 11 20:54 bdump
drwxr-xr-x 2 maclean oinstall 4096 Aug  5 21:35 cdump
drwxr-xr-x 2 maclean oinstall 4096 Aug  5 21:36 dpdump
drwxr-x--- 2 maclean oinstall 4096 Aug  5 21:37 pfile
drwxr-xr-x 2 maclean oinstall 4096 Aug 11 20:54 udump

/*需要注意的另一点是修改_trace_files_public参数并不会引起既有的trace文件的权限被修改，典型的例子是alert log告警日志*/
[maclean@rh2 bdump]$ ls -l
total 20
-rw-r----- 1 maclean oinstall 12971 Aug 11 21:17 alert_YOUYUS.log
-rw-r--r-- 1 maclean oinstall   690 Aug 11 21:12 youyus_lgwr_10514.trc

SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  2   FROM SYS.x$ksppi x, SYS.x$ksppcv y
  3   WHERE x.inst_id = USERENV ('Instance')
  4   AND y.inst_id = USERENV ('Instance')
  5   AND x.indx = y.indx
  6  AND x.ksppinm LIKE '%_trace_files_public%'
  7  order by x.ksppinm;

NAME                 VALUE                DESCRIB
-------------------- -------------------- ----------------------------------------
_trace_files_public  FALSE                Create publicly accessible trace files

SQL> alter system set "_trace_files_public"=true scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2084264 bytes
Variable Size             922747480 bytes
Database Buffers          637534208 bytes
Redo Buffers               14692352 bytes
Database mounted.
Database opened.
SQL> !ls -l
total 32
-rw-r----- 1 maclean oinstall 21189 Aug 11 21:20 alert_YOUYUS.log
-rw-r--r-- 1 maclean oinstall   690 Aug 11 21:12 youyus_lgwr_10514.trc
-rw-r--r-- 1 maclean oinstall   690 Aug 11 21:20 youyus_lgwr_11136.trc

Filed Under: Oracle Tagged With: database, ORA, Oracle, Oracle Bug, RAC, sql

11g中AWR新快照视图

2010/08/09 by admin 2 Comments

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报表所替代，但在短期内做针对检查仍十分有效。

Filed Under: Oracle Tagged With: ORA, Oracle, Oracle 11g新特性, RAC, sql

UNION ALL returning wrong results?

2010/08/06 by admin 5 Comments

有应用人员反映某套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/A

RELATED 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操作后，子查询执行计划回归成全表扫描，整个计划成本上升*/

Filed Under: Oracle Tagged With: database, ORA, Oracle, Oracle 11g新特性, Oracle Bug, RAC, sql

Oracle中清除游标缓存的几种方法

2010/08/05 by admin 3 Comments

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!

Filed Under: Oracle Tagged With: ORA, Oracle, Oracle 11g新特性, Oracle Bug, RAC, sql

10g中HASH GROUP BY引起的临时表空间不足

2010/08/04 by admin 4 Comments

今天早上应用人员反映一个原本在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算法的优势，但可能消耗大量临时空间，可谓有得有失。

Filed Under: Oracle Tagged With: database, ORA, Oracle, Oracle 11g新特性, RAC, sql

dbms_stats收集模式在9i和10g上的区别

2010/08/03 by admin 8 Comments

大约2个月前，一位业内人士问我为什么9i CBO迁移到10g上会出现许多执行计划改变导致的性能，他当然是为了能考考我；实际上我接触过的环境大多在8i/9i下没有使用CBO优化模式，从8i/9i的RBO模式跨越到10g上较为成熟的CBO优化模式，这当中出现执行计划讹误可以说是情理之中的；而9i CBO到10上的CBO问题也不少，我首先想到的是统计信息收集上存在区别，但具体是什么区别却又说不上。那位业内人士听了我的回答，笑，笑而不语。

Oracle十分博大，博大到可以称为Oracle的世界，很多东西长期不用就会遭人淡忘；我们来复习下9i和10g上统计信息收集的一些改动。

在9i中收集统计信息时其默认的MOTHOD_OPT模式为’FOR ALL COLUMNS SIZE 1’，使用这种模式时Oracle只收集所有列上最基础的统计信息，包括了最小/大值，distinct值等信息;但是不会收集列上的直方图。对那些数据均匀分布和没有出现在SQL语句中where子句中作为条件的列来说，这样的统计信息完全足够了。然而如果列上的数据分布并不均匀就可能导致CBO的执行计划成本计算不准确，这时我们需要手动对这些列上的直方图进行统计。

10g上对dbms_stats包中默认的METHOD_OPT模式做了修正，这显然是引起9i CBO迁移到10g CBO后易发地执行计划变化的一个重要因素，也是那位业内人士所要问的题眼。

新的默认METHOD_OPT值为”FOR ALL COLUMNS SIZE AUTO”，这意味着Oracle将通过内部算法自动决定那些列上需要收集统计信息，而那些列上不需要。是否收集直方图取决于列上数据的分布情况和与对应表相关的工作负载,这种工作负载可以解释为数据库中存在某些需要参考这些列的详细信息来计算执行成本的SQL语句。

这种方式听上去十分理想，似乎Oracle可以默默无闻地为我们抓取所有急需的统计信息。

然而问题是在许多环境中Oracle没有做出是否需要收集列上直方图的正确决定。实践证明Oracle有可能收集许许多多不必要的直方图，同时又放弃了许多需要收集的直方图。

在轻量级的应用环境中这种直方图收集不当的问题造成的影响大多数时间不为人们所察觉，相反在performance critical或已经形成性能瓶颈的环境中则可能是一场不大不小的麻烦。

此外Oracle还改变了列上密度(density)信息的计算方式。该值常被Oracle用来确定谓词选择性，当突然出现额外不必要的直方图时可能造成的广泛显著地性能影响(当然好的影响也可能出现，只是概率上……)。

显然这些莫名出现的不速之客也会给共享池造成影响，library cache与row cache相关的闩可能短期内车水马龙，如果您的应用数据表上有成百上千的列那么情况可能更糟(所以说开发要遵循范式，没有规矩的最后结果往往是应用不可用，项目失败。别告诉我你的应用苟且地活着，那同样意味着项目失败)！

Filed Under: Oracle Tagged With: ORA, Oracle, RAC, sql