同條SQL,有時(shí)buffer get會暴增
2024-07-21 02:33:21
供稿：網(wǎng)友
同樣一條SQL ,有的時(shí)候 buffer get 會暴增?! Oracle_L 中有人提了一個(gè)這樣的問題： I have a batch PRocess that executes individual transactions, normally a transaccion e.g. a simple select would take 8-10 buffer gets but in the batch processing it takes 45 buffer gets.
Zhu Chao (Chao_ping,這家伙現(xiàn)在一篇文章都不寫，只能從郵件列表里看到他的蹤跡) 給了一個(gè)解釋：the job is processing some very hot blocks. So it always need to reverse back and find the CR block from buffer, so it will generate some more buffer gets for that execution.
假如是因?yàn)镠ot Block 的原因,那么主要的癥狀應(yīng)該是 Wait. 假如這個(gè) SQL 在運(yùn)行的時(shí)候數(shù)據(jù)已經(jīng)發(fā)生了變化,那么為了維持一致性不可避免的會生成回滾,所以這個(gè)解釋更為準(zhǔn)確一些:If a query does a consistent get on a block that has been changed since that query began or that had uncommitted changes at the time that that query began, then it is necessary to rollback those changes for read consistency. The consistent changes statistics counts the number changes rolled back. However, most consistent gets do not require any sUCh rollback, and so it is normal for the number of consistent gets to be much greater than the number of consistent changes. This is reflected in the no work - consistent read gets statistic
我們不妨來做個(gè)例子.假定我們現(xiàn)在有兩個(gè)session,首先在第一個(gè)窗口做如下操作
SQL> create table t as select * from all_objects where rownum<1001;Table created.SQL> select owner,object_name from t where owner='SYS' for update;
在第二個(gè)Session,進(jìn)行一個(gè)簡單的統(tǒng)計(jì):SQL> SELECT COUNT(*) FROM T;......Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1055 consistent gets
0 physical reads
1352 redo size
379 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed注重到產(chǎn)生了 1055 個(gè)一致讀.接著我們r(jià)ollback 第一個(gè)Session ,然后在第二個(gè)Session中繼續(xù)這個(gè)統(tǒng)計(jì):SQL> SELECT COUNT(*) FROM T;
.....Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
29 consistent gets
0 physical reads
0 redo size
379 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
只有 29 個(gè)一致讀了.驗(yàn)證了前面的解釋.
