Oracle一个奇怪的问题
Oracle一个奇怪的问题
用如下方式删除重复数据，将要删除的数据的rowid暂存至表zkm.rid_tmp，根据rowid再去删除数据。
没10000条commit一次。
declare cursor date_query_cur is select rid from zkm.rid_tmp; TYPE rowid_table_type is table of rowid index by pls_integer; v_rowid rowid_table_type; begin open date_query_cur; loop fetch date_query_cur bulk collect into v_rowid limit 10000; exit when v_rowid.count=0; forall j in v_rowid.first .. v_rowid.last DELETE FROM zkm.t WHERE rowid = v_rowid(j); commit; end loop; close date_query_cur; end; /
需要注意几点：
1.如下表所示，每半个小时执行次数在30-60次之间，证明通过dba_hist_sqlstat.executions_delt所统计的delete并不是执行一次delete这里就算一次，而是10000算一次（因为commit的原因？还是通过游标10000次删除的原因？）。
从"Rows Pers"=10000侧面可以看出来。因为正常通过rowid去delete只会删除1条数据，这里都10000了。
SnapId Date time PLAN_HASH_VALUE MODULE ETime(s) Pers CTime(s) Pers Executions Rows Pers Buffer Gets Pers Physical Reads Pers IO Wait Pers cc_wait Pers ap_wait Pers cl_wait Pers ---------- ------------------------------ --------------- ------------------------------ ------------- ------------- ---------- ---------- ---------------- ------------------- ------------ ------------ ------------ ------------ 54175 2022-05-26 09:30:33-10:00:36 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 46.1386836 3.29830769 39 10000 259332.231 7000.25641 43.0752281 .000001641 0 .145492154 54176 2022-05-26 10:00:36-10:30:40 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 54.8434407 3.43648485 33 10000 257631 8705.06061 51.719808 0 0 .15965203 54177 2022-05-26 10:30:40-11:00:43 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 46.0453049 3.18607692 39 10000 259661.179 7254.46154 43.1752654 0 0 .064074949 54178 2022-05-26 11:00:43-11:30:01 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 51.6431527 3.37805882 34 10000 264151.735 8074.17647 48.5969076 0 0 .103723912 54179 2022-05-26 11:30:01-12:00:07 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 45.2379832 3.175 40 10000 261219.35 7062.475 42.3765546 .000000675 0 .058194375 54180 2022-05-26 12:00:07-12:30:12 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 37.3985019 2.948125 48 10000 260330.333 5669.25 34.673717 9.3750E-07 0 .049348208 54181 2022-05-26 12:30:12-13:00:18 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 52.9956311 3.32997059 34 10000 260698.794 8656.64706 49.9966299 0 0 .105279559 54182 2022-05-26 13:00:18-13:30:01 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 43.3067134 3.04934146 41 10000 260111.317 6958.87805 40.5663086 2.0732E-06 0 .047719585 54183 2022-05-26 13:30:01-14:00:04 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 40.0942986 2.98077778 45 10000 262733.2 6318.22222 37.3756915 0 0 .046632067 54184 2022-05-26 14:00:04-14:30:07 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 47.3907737 3.28244737 38 10000 263101.974 7473.23684 44.4342167 0 0 .075710421 54185 2022-05-26 14:30:07-15:00:11 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 47.3892795 3.19971053 38 10000 256644.605 7308.52632 44.507671 0 0 .071251632 54186 2022-05-26 15:00:11-15:30:14 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 62.108705 3.67093103 29 10000 259787.241 10156.8276 58.8473315 1.1034E-06 0 .144354448 54187 2022-05-26 15:30:14-16:00:18 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 54.5762394 3.44309091 33 10000 258143.606 8593.39394 51.5013165 .000321545 0 .085701818 54188 2022-05-26 16:00:18-16:30:21 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 46.0659272 3.16574359 39 10000 261624.923 7087.41026 43.1582399 0 0 .072834667 54189 2022-05-26 16:30:21-17:00:05 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 63.7958419 3.86064286 28 10000 268707 10254.4286 60.3881478 1.6071E-06 0 .128788929 54190 2022-05-26 17:00:05-17:30:10 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 54.6321264 3.44857576 33 10000 256531.273 8575.51515 51.5924517 1.1515E-06 0 .062090879 54191 2022-05-26 17:30:10-18:00:16 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 43.0344876 3.15516667 42 10000 263830.786 6626.02381 40.198422 1.1905E-06 0 .040858024 54192 2022-05-26 18:00:16-18:30:22 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 52.9356955 3.44823529 34 10000 263235.941 8301.14706 49.8703773 0 0 .059861765 54193 2022-05-26 18:30:22-19:00:28 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 43.05601 3.07019048 42 10000 257652.095 6667.28571 40.2641769 1.2143E-06 0 .067352214 54194 2022-05-26 19:00:28-19:30:34 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 44.9886602 3.03285 40 10000 261991.3 7057.15 42.2368728 0 0 .071019375 54195 2022-05-26 19:30:34-20:00:40 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 58.1630376 3.36377419 31 10000 246484.581 9419.09677 55.0887976 1.1613E-06 0 .183723806 54196 2022-05-26 20:00:40-20:30:46 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 40.0961685 2.77666667 45 10000 244056.556 6202.97778 37.5336216 1.3778E-06 0 .087194444 54197 2022-05-26 20:30:46-21:00:51 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 45.0960939 2.88625 40 10000 243245.325 7343.175 42.4550479 0 0 .086240975 54198 2022-05-26 21:00:51-21:30:55 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 43.8887256 2.91770732 41 10000 251922.317 6735 41.2121246 0 0 .078502 54199 2022-05-26 21:30:55-22:00:04 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 49.9309978 3.19651429 35 10000 257119.457 7886.02857 47.0285205 0 0 .090307257 54200 2022-05-26 22:00:04-22:30:02 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 48.4763627 3.52978378 37 10000 261523.892 7308.18919 45.0127491 .026104568 0 .365836892 54201 2022-05-26 22:30:02-23:00:05 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 45.0044974 3.91405 40 10000 264870.35 6375.675 40.2427313 .17831425 0 1.2574678 54202 2022-05-26 23:00:05-23:30:09 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 40.9839712 3.50954545 44 10000 242537.932 5910.34091 37.0182268 1.4318E-06 0 1.04184252 54203 2022-05-26 23:30:09-00:00:12 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 32.1418003 2.92241071 56 10000 234981.625 4582.57143 28.9603985 .000129304 0 .65265275 54204 2022-05-27 00:00:12-00:30:15 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 28.0874542 2.78832813 64 10000 235763.344 3952.96875 24.995487 5.3125E-07 0 .652394938 54205 2022-05-27 00:30:15-01:00:19 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 30.0844776 2.45908333 60 10000 219708.067 4164.26667 27.5920389 0 0 .292613483 54206 2022-05-27 01:00:19-01:30:22 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 30.9982297 2.69839655 58 10000 230677.569 4228.37931 28.1704383 1.3448E-06 0 .446379155 54207 2022-05-27 01:30:22-02:00:26 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 33.9273797 2.69313208 53 10000 220863.151 4988.16981 31.2512629 2.4528E-07 0 .314962189 54208 2022-05-27 02:00:26-02:30:29 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 27.2377911 2.50465152 66 10000 225453.985 3948.62121 24.5766937 6.0606E-07 0 .401365197 54209 2022-05-27 02:30:29-03:00:03 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 32.9091917 2.86587037 54 10000 246900.519 4475.2963 29.8255186 3.1481E-07 0 .527375444 54210 2022-05-27 03:00:03-03:30:09 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 29.522846 2.89713115 61 10000 254165.23 4077.29508 26.3008325 1.8689E-06 0 .596785951 54211 2022-05-27 03:30:09-04:00:15 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 17.8178953 2.08526733 101 10000 208781.366 2312.61386 15.5784641 .000012297 0 .31921401 54212 2022-05-27 04:00:15-04:30:21 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 24.3832962 2.64893243 74 10000 254837.703 3074.86486 21.5759746 3.3378E-06 0 .409791257 54213 2022-05-27 04:30:21-05:00:27 1357163612 sqlplus@xxxxx1 (TNS V1-V3) 9.25794415 1.55796933 163 9950.52761 180128.276 1057.78528 7.61990873 1.6503E-06 0 .167205896
另外，awrsqlrpt也可以看出（单次消耗都挺高，一次delete不可能那么高消耗），
| Stat Name | Statement Total | Per Execution | % Snap Total |
|---|---|---|---|
| Elapsed Time (ms) | 517,447,646 | 34,723.37 | 79.42 |
| CPU Time (ms) | 44,606,830 | 2,993.35 | 47.61 |
| Executions | 14,902 | ||
| Buffer Gets | 3,865,615,424 | 259,402.46 | 76.92 |
| Disk Reads | 77,431,174 | 5,196.03 | 42.44 |
| Parse Calls | 0 | 0.00 | 0.00 |
| Rows | 149,020,000 | 10,000.00 | |
| User I/O Wait Time (ms) | 473,901,091 | ||
| Cluster Wait Time (ms) | 3,551,692 | ||
| Application Wait Time (ms) | 0 | ||
| Concurrency Wait Time (ms) | 33,916 | ||
| Invalidations | 0 | ||
| Version Count | 7,824 | ||
| Sharable Mem(KB) | 226,666 | |
2.version count特别高，目前通过
select * from v$sql_shared_cursor where sql_id='c9u088gzpgtxx';
暂时确定是统计信息更新导致的。
v$sql_shared_cursor.roll_invalid_mismatch几乎等于'Y'，并且v$sql_shared_cursor.reason为：
<ChildNode><ChildNumber>7</ChildNumber><ID>34</ID><reason>Rolling Invalidate Window Exceeded(2)</reason><size>0x0</size><details>already_processed</details></ChildNode>
