{"id":1689,"date":"2019-10-14T16:11:55","date_gmt":"2019-10-14T08:11:55","guid":{"rendered":"http:\/\/www.llku.com\/?p=1689"},"modified":"2019-10-14T16:11:55","modified_gmt":"2019-10-14T08:11:55","slug":"oracle%e6%95%b0%e6%8d%ae%e5%ba%93%e8%af%84%e4%bc%b0undo-%e8%a1%a8%e7%a9%ba%e9%97%b4%e5%a4%a7%e5%b0%8f","status":"publish","type":"post","link":"https:\/\/www.llku.com\/?p=1689","title":{"rendered":"Oracle\u6570\u636e\u5e93\u8bc4\u4f30undo \u8868\u7a7a\u95f4\u5927\u5c0f"},"content":{"rendered":"

\u5982\u4f55\u4f30\u7b97Oracle\u6570\u636e\u5e93\u6240\u9700\u7684UNDO\u8868\u7a7a\u95f4\u7684\u5927\u5c0f:<\/p>\n

How To Size UNDO Tablespace For Automatic Undo Management (\u6587\u6863 ID 262066.1)<\/p>\n

\u8981\u786e\u5b9aOracle\u9700\u8981\u7684UNDO \u8868\u7a7a\u95f4\u7684\u5927\u5c0f\uff0c\u9700\u8981\u4ee5\u4e0b\u4e09\u6761\u4fe1\u606f\uff1a<\/p>\n

UR \u4ee5\u79d2\u4e3a\u5355\u4f4d\u7684UNDO_RETENTION<\/p>\n

UPS \u6bcf\u79d2\u751f\u6210\u7684\u8fd8\u539f\u6570\u636e\u5757\u7684\u6570\u91cf<\/p>\n

DBS db_block_size<\/p>\n

UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)<\/p>\n

UNDO_RETENTION\u662f\u4e00\u4e2a\u53c2\u6570\uff0c\u6b64\u53c2\u6570\u63a7\u5236\u4e3a\u63d0\u4f9b\u8bfb\u4e00\u81f4\u6027\u800c\u4fdd\u7559\u7684\u8fd8\u539f\u6570\u636e\u91cf\uff0c\u4ee5\u79d2\u4e3a\u5355\u4f4d\u5b9a\u4e49\uff0c\u53ef\u4ee5\u5728\u521d\u59cb\u5316\u6587\u4ef6\u4e2d\u8bbe\u7f6e\uff0c\u6216\u4f7f\u7528 ALTER SYSTEM \u547d\u4ee4\u6765\u52a8\u6001\u4fee\u6539\u3002<\/p>\n

SQL>ALTER SYSTEM SET UNDO_RETENTION=900;<\/p>\n

SQL> show parameter undo_retention<\/p>\n

NAME TYPE VALUE<\/p>\n

———————————— ———– ——————————<\/p>\n

undo_retention integer 900<\/p>\n

\u5982\u679c\u503c\u4e3a900\uff0c\u5219\u53ef\u4ee5\u4f7f\u8fd8\u539f\u6570\u636e\u4fdd\u7559 15 \u5206\u949f\uff0c\u5f53\u7136\u9700\u8981\u8db3\u591f\u7684\u5b58\u50a8\u7a7a\u95f4\u624d\u884c\u3002<\/p>\n

\u90a3\u4e48\u5982\u4f55\u8ba1\u7b97\u6bcf\u79d2\u751f\u6210\u7684\u8fd8\u539f\u6570\u636e\u5757\u7684\u6570\u91cf\u5462\uff0c\u53ef\u4ee5\u901a\u8fc7v$undostat\u89c6\u56fe\u7684begin_time\u3001end_time\u548cundoblks\u4e09\u4e2a\u5b57\u6bb5\u7684\u503c\u67e5\u8be2\u51fa\u6765\uff0c\u8ba1\u7b97\u7684SQL\u8bed\u53e5\u5982\u4e0b\uff1a<\/p>\n

SQL>\u00a0SELECT (UR * (UPS * DBS)) + (DBS * 24) AS “Bytes” FROM (SELECT value AS UR \u00a0FROM v$parameter WHERE name = ‘undo_retention’),(SELECT (SUM(undoblks)\/SUM(((end_time -begin_time)*86400))) AS UPS \u00a0FROM v$undostat), \u00a0(SELECT value AS DBS FROM v$parameter \u00a0WHERE name = ‘db_block_size’);<\/p>\n

Bytes<\/p>\n

———-<\/p>\n

445814.844<\/p>\n

\u8be6\u89e3\uff1a<\/p>\n

\u4e00\u822c\u5e94\u8be5\u5728\u4e00\u5929\u4e2d\u6570\u636e\u5e93\u8d1f\u8f7d\u6700\u7e41\u91cd\u7684\u65f6\u5019\u8fdb\u884c\u8ba1\u7b97\u3002<\/p>\n

\u5bf9\u4e8eUNDO\u8868\u7a7a\u95f4\u5927\u5c0f\u7684\u5b9a\u4e49\u9700\u8981\u8003\u8651UNDO_RETNETION\u53c2\u6570\u3001\u4ea7\u751f\u7684UNDO BLOCKS\/\u79d2\u3001UNDO BLOCK\u7684\u5927\u5c0f\u3002undo_retention\uff1a\u5bf9\u4e8eUNDO\u8868\u7a7a\u95f4\u7684\u6570\u636e\u6587\u4ef6\u5c5e\u6027\u4e3aautoextensible,\u5219undo_retenion\u53c2\u6570\u5fc5\u987b\u8bbe\u7f6e\uff0cUNDO\u4fe1\u606f\u5c06\u81f3\u5c11\u4fdd\u7559\u81f3undo_retention \u53c2\u6570\u8bbe\u5b9a\u7684\u503c\u5185\uff0c\u4f46UNDO\u8868\u7a7a\u95f4\u5c06\u4f1a\u81ea\u52a8\u6269\u5c55\u3002\u5bf9\u4e8e\u56fa\u5b9aUNDO\u8868\u7a7a\u95f4\uff0c\u5c06\u4f1a\u901a\u8fc7\u8868\u7a7a\u95f4\u7684\u5269\u4f59\u7a7a\u95f4\u6765\u6700\u5927\u9650\u5ea6\u4fdd\u7559UNDO\u4fe1\u606f\u3002\u5982\u679cFIXED UNDO\u8868\u7a7a\u95f4\u6ca1\u6709\u5bf9\u4fdd\u7559\u65f6\u95f4\u4f5cGUARANTEE\uff08alter tablespace xxx retention guarantee;\uff09\uff0c\u5219undo_retention\u53c2\u6570\u5c06\u4e0d\u4f1a\u8d77\u4f5c\u7528\u3002\uff08\u8b66\u544a\uff1a\u5982\u679c\u8bbe\u7f6eUNDO\u8868\u7a7a\u95f4\u4e3aretention guarantee\uff0c\u5219\u672a\u8fc7\u671f\u7684\u6570\u636e\u4e0d\u4f1a\u88ab\u590d\u5199\uff0c\u5982\u679c\u8868\u7a7a\u95f4\u4e0d\u591f\u5219\u4f1a\u5bfc\u81f4DML\u64cd\u4f5c\u5931\u8d25\u6216\u8005transation\u6302\u8d77\uff09<\/p>\n

Oracle 10g \u6709\u81ea\u52a8Automatic Undo Retention Tuning \u8fd9\u4e2a\u7279\u6027\u3002\u8bbe\u7f6e\u7684 undo_retention \u53c2\u6570\u53ea\u662f\u4e00\u4e2a\u6307\u5bfc\u503c,\uff0cOracle \u4f1a\u81ea\u52a8\u8c03\u6574 Undo (\u4f1a\u8de8\u8fc7 undo_retention \u8bbe\u5b9a\u7684\u65f6\u95f4) \u6765\u4fdd\u8bc1\u4e0d\u4f1a\u51fa\u73b0 Ora-1555 \u9519\u8bef.\u3002\u901a\u8fc7\u67e5\u8be2V$UNDOSTAT\uff08\u8be5\u89c6\u56fe\u8bb0\u5f554\u5929\u4ee5\u5185\u7684UNDO\u8868\u7a7a\u95f4\u4f7f\u7528\u60c5\u51b5\uff0c\u8d85\u8fc74\u5929\u53ef\u4ee5\u67e5\u8be2DBA_HIST_UNDOSTAT\u89c6\u56fe\uff09 \u7684 tuned_undoretention \uff08\u8be5\u5b57\u6bb5\u572810G\u7248\u672c\u624d\u6709\uff0c9I\u662f\u6ca1\u6709\u7684\uff09\u5b57\u6bb5\u53ef\u4ee5\u5f97\u5230Oracle \u6839\u636e\u4e8b\u52a1\u91cf\uff08\u5982\u679c\u662f\u6587\u4ef6\u4e0d\u53ef\u6269\u5c55\uff0c\u5219\u4f1a\u8003\u8651\u5269\u4f59\u7a7a\u95f4\uff09\u91c7\u6837\u540e\u7684\u81ea\u52a8\u8ba1\u7b97\u51fa\u6700\u4f73\u7684 retenton \u65f6\u95f4.\u3002\u8fd9\u6837\u5bf9\u4e8e\u4e00\u4e2a\u4e8b\u52a1\u91cf\u5206\u5e03\u4e0d\u5747\u5300\u7684\u6570\u636e\u5e93\u6765\u8bf4,\uff0c\u5c31\u4f1a\u5f15\u53d1\u6f5c\u5728\u7684\u95ee\u9898–\u5728\u6279\u5904\u7406\u7684\u65f6\u5019\u53ef\u80fd Undo \u4f1a\u7528\u5149\uff0c \u800c\u4e14\u8fd9\u4e2a\u72b6\u6001\u5c06\u4e00\u76f4\u6301\u7eed\uff0c \u4e0d\u4f1a\u91ca\u653e\u3002<\/p>\n

SQL\u67e5\u8be2tuned_undoretention\uff1a<\/p>\n

select to_char(begin_time,’DD-MON-RR HH24:MI’) begin_time,to_char(end_time,’DD-MON-RR HH24:MI’) end_time,tuned_undoretention from v$undostat order by end_time;<\/p>\n

\u68c0\u67e5\u4e00\u5929\u5e73\u5747\u6bcf\u79d2\u4ea7\u751f\u7684UNDO BLOCK<\/p>\n

select (sum(undoblks)\/sum((end_time-begin_time)*86400) from v$undostat;<\/p>\n

\u751f\u6210\u7684\u7ed3\u679c\u662fUNDO BLOCK\uff0c\u5982\u679c\u9700\u8981\u8ba1\u7b97\u51fa\u5b9e\u9645\u5927\u5c0f\uff0c\u5219\u9700\u8981\u4e58\u4ee5db_block_size\uff08\u901a\u8fc7show parameter db_block_size\u67e5\u51fa\u6765\uff09<\/p>\n

\u5982\u4f55\u8ba1\u7b97\u5408\u9002\u7684UNDO\u8868\u7a7a\u95f4\u5927\u5c0f\uff1a<\/p>\n

select (UR*(UPS*DBS))+(DBS*24) as “bytes” from (select value as UR from v$parameter where name=’undo_retention’),(select (sum(undoblks)\/sum(((end_time-begin_time)*86400))) as ups from v$undostat),(select value as DBS from v$parameter where name=’db_block_size’);<\/p>\n

\u8f6c\u81ea\uff1ahttp:\/\/blog.itpub.net\/31397003\/viewspace-2147515\/<\/p>\n","protected":false},"excerpt":{"rendered":"