vikey123 发表于 2014-10-30 16:34:21

关于update问题

10g R2版本以后的数据库，更新test表的某个列值，但这个表的列有普通索引，更新的时候执行计划走的是索引，那么commit之后，是应该先更新表还是索引？应该用什么方法来验证？？？求指导~

Maclean Liu(刘相兵 发表于 2014-10-30 17:14:23

SQL> create table testz1 as select * from dba_objects;

Table created.

SQL>
SQL> create index ind_z1 on testz1 (owner);

Index created.


SQL> create index ind_z2 on testz1(object_id);

Index created.



SQL> exec dbms_stats.gather_table_stats('','TESTZ1');

PL/SQL procedure successfully completed.



alter system set "_trace_pin_time"=1 scope=spfile;
alter session set events '10046 trace name context forever,level 8';


set autotrace on;
set linesize 200 pagesize 2000
update testz1 set owner='ZZ',  object_id=72025 where object_id=72018;
commit;

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/s01/admin/G10R25/udump/g10r25_ora_31334.trc

to be continued .........

Maclean Liu(刘相兵 发表于 2014-10-31 09:47:06

pin ktewh25: kteinicnt dba 4002d1:4 time 345509041
pin ktewh26: kteinpscan dba 4002d1:4 time 345509171
pin kdswh01: kdstgr dba 4002d2:1 time 345509210
pin kdswh01: kdstgr dba 4002d2:1 time 345509248
pin kdiwh09: kdiixs dba 416f89:1 time 347456430
pin kduwh01: kdusru dba 416d49:1 time 347456560
pin ktuwh01: ktugus dba 800019:19 time 347456667
pin ktuwh09: ktugfb dba 80051b:20 time 347456721
pin release        33 ktuwh09: ktugfb dba 80051b:20
pin release       279 kduwh01: kdusru dba 416d49:1
pin release       221 ktuwh01: ktugus dba 800019:19
pin kdiwh17: kdifind dba 416e22:1 time 347457018
pin kdiwh22: kdifind dba 416f89:1 time 347457059
pin release       835 kdiwh22: kdifind dba 416f89:1
pin kdiwh17: kdifind dba 416e22:1 time 347457945
pin kdiwh22: kdifind dba 416f89:1 time 347457996
pin release        61 kdiwh22: kdifind dba 416f89:1
pin kdiwh17: kdifind dba 416aa2:1 time 347458059
pin kdiwh22: kdifind dba 416e89:1 time 347458240
pin release       544 kdiwh22: kdifind dba 416e89:1
pin kdiwh17: kdifind dba 416aa2:1 time 347458795
pin kdiwh22: kdifind dba 416e90:1 time 347458850
pin release       150 kdiwh22: kdifind dba 416e90:1
pin ktuwh02: ktugus dba 800019:19 time 347460542
pin release       515 ktuwh02: ktugus dba 800019:19









Maclean Liu(刘相兵 发表于 2014-10-31 09:48:34

以上我们只分析 pin不分析pin release

pin ktewh25: kteinicnt dba 4002d1:4 time 345509041
pin ktewh26: kteinpscan dba 4002d1:4 time 345509171
pin kdswh01: kdstgr dba 4002d2:1 time 345509210
pin kdswh01: kdstgr dba 4002d2:1 time 345509248
pin kdiwh09: kdiixs dba 416f89:1 time 347456430
pin kduwh01: kdusru dba 416d49:1 time 347456560
pin ktuwh01: ktugus dba 800019:19 time 347456667
pin ktuwh09: ktugfb dba 80051b:20 time 347456721
pin kdiwh17: kdifind dba 416e22:1 time 347457018
pin kdiwh22: kdifind dba 416f89:1 time 347457059
pin kdiwh17: kdifind dba 416e22:1 time 347457945
pin kdiwh22: kdifind dba 416f89:1 time 347457996
pin kdiwh17: kdifind dba 416aa2:1 time 347458059
pin kdiwh22: kdifind dba 416e89:1 time 347458240
pin kdiwh17: kdifind dba 416aa2:1 time 347458795
pin kdiwh22: kdifind dba 416e90:1 time 347458850
pin ktuwh02: ktugus dba 800019:19 time 347460542

Maclean Liu(刘相兵 发表于 2014-10-31 09:55:04

kdiixs  kdiixs - KDI Initialize indeX Scan state       pin  datafile 1 block 94089
kdusru    Single Row Update                                pin  datafile 1 block 93513
ktugus 、ktugfb   undo


kdifind   kdifind : FIND the leaf block associated with the key

416e22    datafile 1 block  93730
416f89    datafile 1 block 94089

Maclean Liu(刘相兵 发表于 2014-10-31 10:00:02

datafile 1 block 94089   ==  >  INDEX_Z2  索引
datafile 1  block 93513 ==>  TESTZ1    表

datafile 1 block 93730 =>   INDEX_Z2  索引
416aa2 => datafile 1 block 92834       =>   INDEX_Z1 索引

Maclean Liu(刘相兵 发表于 2014-10-31 10:00:45

执行计划如下：


Execution Plan
----------------------------------------------------------
Plan hash value: 2529996310

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT  |        |     1 |    11 |     2   (0)| 00:00:01 |
|   1 |  UPDATE           | TESTZ1 |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| IND_Z2 |     1 |    11 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

Maclean Liu(刘相兵 发表于 2014-10-31 10:02:32

大致可以理解为

当执行计划 通过访问  INDEX RANGE SCAN| IND_Z2 作为访问路径做单条更新时， 首先 kdiixs   初始化索引扫描，获得rowid后 以kdusru     去更新表 ， 更新完表后 以 kdifind    持key遍历leaf block做index修改。

vikey123 发表于 2014-10-31 16:12:28

Maclean Liu(刘相兵 发表于 2014-10-31 10:02 static/image/common/back.gif
大致可以理解为

当执行计划 通过访问  INDEX RANGE SCAN| IND_Z2 作为访问路径做单条更新时， 首先 kdiixs ...

非常感谢刘大的详细分析指导

sunguo40 发表于 2015-1-13 21:52:04

精辟 一针见血  学习了
页: [1]
查看完整版本: 关于update问题