ããLGWRè¿ç¨æç
§é¡ºåºåå¨çº¿æ¥å¿ï¼ä¸é´ä¸ä¼è·³è·ï¼èä¸LGWRè¿ç¨ä¸ä¼å¨åä¸ä¸ªæ¥å¿å¿«å2次ï¼å³ä½¿ä¸æ¬¡åå
¥çæ¥å¿å¿«åªå å 个åèï¼ä¸æ¬¡ä¸ä¼åç¨äºï¼è¿å°±é ææ¥å¿ç©ºé´ç浪费ãOracleåä¸æ¬¡Commitï¼å°±ä¼è§¦åLGWRè¿ç¨è¿è¡æ¥å¿ç¼å²å°æ¥å¿æ件çåå
¥æä½ï¼å æ¤å¯ä»¥è¯´æ´æ¹ç¸åæ°æ®éçåæä¸ï¼å¦ææ交è¿äºé¢ç¹ï¼äº§ççæ¥å¿å¯è½å°±ä¼è¶å¤ï¼å³ä½¿ç¬¬ä¸æ¬¡Commitå ç¨çæ¥å¿åä»å¯ä»¥åå¨ä¸ä¸æ¬¡éè¦åå
¥çæ¥å¿ç¼å²ï¼é£ä¹ä¸ä¸æ¬¡Commitä¼å次å ç¨ä¸ä¸ªæ°çæ¥å¿åã
ããå®éªï¼
ãã1ãç³»ç»çæ¥å¿å大å°æ¯512åèã
ããSQL> select max(lebsz) from sys.x$kccle;
ããMAX(LEBSZ)
ãã----------
ãã512
ãã2ãåå»ºä¸¤å¼ ç¸åæ°æ®éç表ã
ããSQL> select count(*) from t1;
ããCOUNT(*)
ãã----------
ãã11188
ããSQL> select count(*) from t2;
ããCOUNT(*)
ãã----------
ãã11188
ãã3ãæ¥çå é¤t1表åç³»ç»ç浪费æ¥å¿ç©ºé´éã
ããSQL> select name, value from v$sysstat where name like '%wastage%';NAME VALUE---------------------------------------------------------------- ----------redo wastage 2080604ãéæ¡å é¤t1表çè®°å½ã
ããSQL> begin
ãã2 for i in 1 .. 11188 loop
ãã3 delete from t1 where rownum < 2;
ãã4 commit;
ãã5 end loop;
ãã6 end;
ãã7 /
ãã5ãå次æ¥çæ¥å¿ç©ºé´æµªè´¹éã
ããSQL> select name, value from v$sysstat where name like '%wastage%';NAME VALUE---------------------------------------------------------------- ----------redo wastage 1118740SQL> select 1118740-208060 from dual;
ãã1118740-208060
ãã--------------
ãã910680
ãã浪费æ¥å¿ç©ºé´éæ¯910680åèã
ãã6ãæ¥çå½åè¿ç¨çSIDã
ããSQL> select distinct sid from v$mystat;
ããSID
ãã----------
ãã215
ããè¿èæ¥åºå½åè¿ç¨æ¶èçredoéæ»å¤§å°ã
ããSQL> select b.name, a.value from v$sesstat a, v$statname b2 where a.statistic#=b.statistic#
ãã3 and b.name like '%redo size%'
ãã4 and a.sid=215;
ããNAME VALUE
ãã-------------------- ----------
ããredo size 9103304
ããå¯ç¥æ¥å¿ç©ºé´æµªè´¹æ¯çæ10%
ããSQL> select 910680/9103304 from dual;
ãã910680/9103304
ãã--------------
ãã.100038404
ãã7ãæ¥ä¸æ¥éæ©ä¸æ¬¡æ§å é¤t2表记å½ï¼ä¹åè®°å½ä¸æ¥å¿ç©ºé´æµªè´¹å¤§å°ã
ããSQL> select name, value from v$sysstat where name like '%wastage%';NAME VALUE
ãã-------------------- ----------
ããredo wastage 1130636
ããSQL> delete from t2;
ãã11188 rows deleted.
ããSQL> commit;
ããCommit complete.
ãã8ãæ¥çå½åæ¥å¿ç©ºé´æµªè´¹ã
ããSQL> select name, value from v$sysstat where name like '%wastage%';NAME VALUE
ãã-------------------- ----------
ããredo wastage 1132060
ãã9ã计ç®æ¥å¿æµªè´¹ç©ºé´æ¯çã
ããSQL> select 1132060-1130636 from dual;
ãã1132060-1130636
ãã---------------
ãã1424
ããSQL> select b.name, a.value from v$sesstat a, v$statname b2 where a.statistic#=b.statistic#
ãã3 and b.name like '%redo size%'
ãã4 and a.sid=215;
ããNAME VALUE
ãã-------------------- ----------
ããredo size 13154544
ããSQL> select 1424/13154544 from dual;
ãã1424/13154544
ãã-------------
ãã.000108252
ããä»ç»æçï¼æ¥å¿ç©ºé´æµªè´¹æ¯çä»
为0.01%ã
ããç»è®ºï¼
ãã1ãLGWRè¿ç¨æç
§é¡ºåºå°æ¥å¿ç¼å²åå
¥æ¥å¿åï¼ä¸ä¼å¨åä¸ä¸ªæ¥å¿åä¸åå
¥ä¸¤æ¬¡ï¼å°±å¯è½é æä¸ä¸æ¬¡åå
¥çæåä¸ä¸ªæ¥å¿åä¼æ空é´ç浪费ï¼ä½ä¸ä¸æ¬¡ä¸è½å使ç¨ï¼åªè½å次åå
¥ä¸ä¸ªæ°çæ¥å¿åã
ãã2ãç¸åæ´æ¹æ°æ®éçåæä¸ï¼å¤æ¬¡æ交Commitè¦æ¯ä¸æ¬¡Commit浪费æ´å¤çæ¥å¿å空é´ã
温馨提示:答案为网友推荐,仅供参考