What cause the big diffrence on the same sql ?

最新推荐文章于 2022-11-08 10:40:14 发布

转载最新推荐文章于 2022-11-08 10:40:14 发布 · 115 阅读

本文对比了两种基于ROWID的批量删除操作在Oracle数据库中的性能差异及执行计划。第一种情况仅涉及少量数据删除，执行迅速；而第二种情况则处理大量数据，导致较高的CPU时间和磁盘I/O开销。

1.DELETE FROM PUB_QUEUE
WHERE
ROWID = :B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        0      0.00       0.00          0          0          0           0
Execute    261      0.00       0.18          0        261       1093         261
Fetch        0      0.00       0.00          0          0          0           0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total      261      0.00       0.18          0        261       1093         261

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: 29 (LSS)   (recursive depth: 1)

Rows     Execution Plan
------- ---------------------------------------------------
      0 DELETE STATEMENT   MODE: CHOOSE
      0   DELETE OF 'PUB_QUEUE'
      0    TABLE ACCESS (BY USER ROWID) OF 'PUB_QUEUE' (TABLE)
               PARTITION:ROW LOCATION

2.DELETE FROM PUB_QUEUE
WHERE
ROWID = :B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        0      0.00       0.00          0          0          0           0
Execute    511    253.46     248.94          2    6625899       3932         511
Fetch        0      0.00       0.00          0          0          0           0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total      511    253.46     248.94          2    6625899       3932         511

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: 37 (LSS)   (recursive depth: 1)

Rows     Execution Plan
------- ---------------------------------------------------
      0 DELETE STATEMENT   MODE: CHOOSE
      0   DELETE OF 'PUB_QUEUE'
      0    TABLE ACCESS   MODE: ANALYZED (BY USER ROWID) OF 'PUB_QUEUE'
               (TABLE) PARTITION:ROW LOCATION

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/3637/viewspace-694842/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/3637/viewspace-694842/