技術ブログ
Oracle Database 12c R1 より、一時表のデータ更新におけるREDO生成量をさらに削減する機能が導入されています。
ただし、Oracle Database 11g以前から一時表のデータ更新に伴うREDO生成量は、通常の表よりも削減されていました。まずはこの点をsnapperを使って確認してみましょう。
SQL> SELECT DISTINCT sid FROM V$MYSTAT;
SID
----------
61
SQL> CREATE TABLE tbl_temp (id number, str char(2000));
表が作成されました。
SQL> insert into tbl_temp select level, level from dual connect by level <= 10000;
10000行が作成されました。
別のセッションでINSERT実行前にsnapperを実行しておきます。なお、スクリプトのファイル名が snapper12c.sql となっていますが気にしないでください。
SQL> @snapper12c stats 10 1 61
Sampling SID 61 with interval 10 seconds, taking 1 snapshots...
-- Session Snapper v3.52 by Tanel Poder @ E2SN ( http://tech.e2sn.com )
----------------------------------------------------------------------------------------------------------------------------------------------------
SID, USERNAME , TYPE, STATISTIC , DELTA, HDELTA, HDELTA/SEC, %TIME, GRAPH
----------------------------------------------------------------------------------------------------------------------------------------------------
61, RYWATABE , STAT, Requests to/from client , 1, 1, .1,
61, RYWATABE , STAT, opened cursors cumulative , 233, 233, 23.3,
61, RYWATABE , STAT, user calls , 2, 2, .2,
61, RYWATABE , STAT, recursive calls , 785, 785, 78.5,
61, RYWATABE , STAT, recursive cpu usage , 18, 18, 1.8,
61, RYWATABE , STAT, session logical reads , 41758, 41.76k, 4.18k,
61, RYWATABE , STAT, CPU used when call started , 13, 13, 1.3,
61, RYWATABE , STAT, CPU used by this session , 39, 39, 3.9,
61, RYWATABE , STAT, DB time , 159, 159, 15.9,
61, RYWATABE , STAT, concurrency wait time , 26, 26, 2.6,
61, RYWATABE , STAT, user I/O wait time , 8, 8, .8,
61, RYWATABE , STAT, non-idle wait time , 40, 40, 4,
61, RYWATABE , STAT, non-idle wait count , 504, 504, 50.4,
61, RYWATABE , STAT, session uga memory , 458416, 458.42k, 45.84k,
61, RYWATABE , STAT, messages sent , 87, 87, 8.7,
61, RYWATABE , STAT, session pga memory , 5570560, 5.57M, 557.06k,
61, RYWATABE , STAT, enqueue requests , 790, 790, 79,
61, RYWATABE , STAT, enqueue releases , 788, 788, 78.8,
61, RYWATABE , STAT, physical read total IO requests , 313, 313, 31.3,
61, RYWATABE , STAT, physical read total bytes , 4857856, 4.86M, 485.79k,
61, RYWATABE , STAT, physical write total IO requests , 129, 129, 12.9,
61, RYWATABE , STAT, physical write total multi block requests , 28, 28, 2.8,
61, RYWATABE , STAT, physical write total bytes , 19841024, 19.84M, 1.98M,
61, RYWATABE , STAT, cell physical IO interconnect bytes , 24698880, 24.7M, 2.47M,
61, RYWATABE , STAT, max cf enq hold time , 10, 10, 1,
61, RYWATABE , STAT, total cf enq hold time , 50, 50, 5,
61, RYWATABE , STAT, total number of cf enq holders , 42, 42, 4.2,
61, RYWATABE , STAT, db block gets , 29264, 29.26k, 2.93k,
61, RYWATABE , STAT, db block gets from cache , 29264, 29.26k, 2.93k,
61, RYWATABE , STAT, db block gets from cache (fastpath) , 7336, 7.34k, 733.6,
61, RYWATABE , STAT, consistent gets , 12494, 12.49k, 1.25k,
61, RYWATABE , STAT, consistent gets from cache , 12494, 12.49k, 1.25k,
61, RYWATABE , STAT, consistent gets pin , 11622, 11.62k, 1.16k,
61, RYWATABE , STAT, consistent gets pin (fastpath) , 11622, 11.62k, 1.16k,
61, RYWATABE , STAT, consistent gets examination , 872, 872, 87.2,
61, RYWATABE , STAT, consistent gets examination (fastpath) , 872, 872, 87.2,
61, RYWATABE , STAT, logical read bytes from cache , 342081536, 342.08M, 34.21M,
61, RYWATABE , STAT, physical reads , 5, 5, .5,
61, RYWATABE , STAT, physical reads cache , 5, 5, .5,
61, RYWATABE , STAT, physical read IO requests , 5, 5, .5,
61, RYWATABE , STAT, physical read bytes , 40960, 40.96k, 4.1k,
61, RYWATABE , STAT, db block changes , 23569, 23.57k, 2.36k,
61, RYWATABE , STAT, change write time , 16, 16, 1.6,
61, RYWATABE , STAT, free buffer requested , 3495, 3.5k, 349.5,
61, RYWATABE , STAT, commit cleanouts , 8, 8, .8,
61, RYWATABE , STAT, commit cleanouts successfully completed , 8, 8, .8,
61, RYWATABE , STAT, switch current to new buffer , 3, 3, .3,
61, RYWATABE , STAT, shared hash latch upgrades - no wait , 7, 7, .7,
61, RYWATABE , STAT, calls to kcmgcs , 7320, 7.32k, 732,
61, RYWATABE , STAT, calls to kcmgas , 104, 104, 10.4,
61, RYWATABE , STAT, calls to get snapshot scn: kcmgss , 354, 354, 35.4,
61, RYWATABE , STAT, redo entries , 17976, 17.98k, 1.8k,
61, RYWATABE , STAT, redo size , 22487908, 22.49M, 2.25M, <===
61, RYWATABE , STAT, redo synch time , 2, 2, .2,
61, RYWATABE , STAT, redo synch time (usec) , 22717, 22.72k, 2.27k,
61, RYWATABE , STAT, redo synch time overhead (usec) , 80, 80, 8,
61, RYWATABE , STAT, redo synch time overhead count ( 2ms) , 1, 1, .1,
61, RYWATABE , STAT, redo synch writes , 1, 1, .1,
61, RYWATABE , STAT, redo write info find , 1, 1, .1,
61, RYWATABE , STAT, file io service time , 20, 20, 2,
61, RYWATABE , STAT, file io wait time , 1723, 1.72k, 172.3,
61, RYWATABE , STAT, undo change vector size , 370988, 370.99k, 37.1k,
61, RYWATABE , STAT, no work - consistent read gets , 7401, 7.4k, 740.1,
61, RYWATABE , STAT, rollback changes - undo records applied , 42, 42, 4.2,
61, RYWATABE , STAT, transaction rollbacks , 42, 42, 4.2,
61, RYWATABE , STAT, immediate (CURRENT) block cleanout applications , 1, 1, .1,
61, RYWATABE , STAT, deferred (CURRENT) block cleanout applications , 7, 7, .7,
61, RYWATABE , STAT, active txn count during cleanout , 621, 621, 62.1,
61, RYWATABE , STAT, cleanout - number of ktugct calls , 621, 621, 62.1,
61, RYWATABE , STAT, commit batch/immediate requested , 42, 42, 4.2,
61, RYWATABE , STAT, commit immediate requested , 42, 42, 4.2,
61, RYWATABE , STAT, commit batch/immediate performed , 42, 42, 4.2,
61, RYWATABE , STAT, commit immediate performed , 42, 42, 4.2,
61, RYWATABE , STAT, IMU Flushes , 3, 3, .3,
61, RYWATABE , STAT, IMU undo allocation size , 144, 144, 14.4,
61, RYWATABE , STAT, TBS Extension: tasks created , 14, 14, 1.4,
61, RYWATABE , STAT, KTFB alloc req , 52, 52, 5.2,
61, RYWATABE , STAT, KTFB alloc space (block) , 28966912, 28.97M, 2.9M,
61, RYWATABE , STAT, KTFB alloc time (ms) , 815390, 815.39k, 81.54k,
61, RYWATABE , STAT, KTFB apply req , 10, 10, 1,
61, RYWATABE , STAT, KTFB apply time (ms) , 98, 98, 9.8,
61, RYWATABE , STAT, Heatmap BlkLevel Flushed , 1, 1, .1,
61, RYWATABE , STAT, Heatmap BlkLevel Flushed to BF , 1, 1, .1,
61, RYWATABE , STAT, Heatmap BlkLevel Ranges Flushed , 1, 1, .1,
61, RYWATABE , STAT, table scans (short tables) , 47, 47, 4.7,
61, RYWATABE , STAT, table scan rows gotten , 248879, 248.88k, 24.89k,
61, RYWATABE , STAT, table scan blocks gotten , 7182, 7.18k, 718.2,
61, RYWATABE , STAT, table fetch by rowid , 75, 75, 7.5,
61, RYWATABE , STAT, cluster key scans , 52, 52, 5.2,
61, RYWATABE , STAT, cluster key scan block gets , 52, 52, 5.2,
61, RYWATABE , STAT, rows fetched via callback , 1, 1, .1,
61, RYWATABE , STAT, index fetch by key , 61, 61, 6.1,
61, RYWATABE , STAT, index scans kdiixs1 , 128, 128, 12.8,
61, RYWATABE , STAT, HSC Heap Segment Block Changes , 5005, 5.01k, 500.5,
61, RYWATABE , STAT, Heap Segment Array Inserts , 5000, 5k, 500,
61, RYWATABE , STAT, Heap Segment Array Updates , 2, 2, .2,
61, RYWATABE , STAT, sql area evicted , 4, 4, .4,
61, RYWATABE , STAT, CCursor + sql area evicted , 3, 3, .3,
61, RYWATABE , STAT, session cursor cache hits , 212, 212, 21.2,
61, RYWATABE , STAT, cursor authentications , 2, 2, .2,
61, RYWATABE , STAT, buffer is pinned count , 2, 2, .2,
61, RYWATABE , STAT, buffer is not pinned count , 396, 396, 39.6,
61, RYWATABE , STAT, workarea executions - optimal , 12, 12, 1.2,
61, RYWATABE , STAT, parse count (total) , 107, 107, 10.7,
61, RYWATABE , STAT, parse count (hard) , 23, 23, 2.3,
61, RYWATABE , STAT, execute count , 234, 234, 23.4,
61, RYWATABE , STAT, bytes sent via SQL*Net to client , 195, 195, 19.5,
61, RYWATABE , STAT, bytes received via SQL*Net from client , 371, 371, 37.1,
61, RYWATABE , STAT, SQL*Net roundtrips to/from client , 1, 1, .1,
61, RYWATABE , STAT, sorts (memory) , 99, 99, 9.9,
61, RYWATABE , STAT, sorts (rows) , 784, 784, 78.4,
61, RYWATABE , TIME, hard parse elapsed time , 45534, 45.53ms, 4.55ms, .5%, | |
61, RYWATABE , TIME, repeated bind elapsed time , 387, 387us, 38.7us, .0%, | |
61, RYWATABE , TIME, parse time elapsed , 54539, 54.54ms, 5.45ms, .5%, |@ |
61, RYWATABE , TIME, DB CPU , 390941, 390.94ms, 39.09ms, 3.9%, |@ |
61, RYWATABE , TIME, sql execute elapsed time , 1569667, 1.57s, 156.97ms, 15.7%, |@@ |
61, RYWATABE , TIME, hard parse (sharing criteria) elapsed time , 4437, 4.44ms, 443.7us, .0%, | |
61, RYWATABE , TIME, DB time , 1594932, 1.59s, 159.49ms, 15.9%, |@@ |
61, RYWATABE , WAIT, Disk file operations I/O , 12477, 12.48ms, 1.25ms, .1%, | |
61, RYWATABE , WAIT, Data file init write , 60417, 60.42ms, 6.04ms, .6%, |@ |
61, RYWATABE , WAIT, control file sequential read , 2118, 2.12ms, 211.8us, .0%, | |
61, RYWATABE , WAIT, control file parallel write , 30629, 30.63ms, 3.06ms, .3%, | |
61, RYWATABE , WAIT, buffer busy waits , 260594, 260.59ms, 26.06ms, 2.6%, |@ |
61, RYWATABE , WAIT, log file sync , 22702, 22.7ms, 2.27ms, .2%, | |
61, RYWATABE , WAIT, db file sequential read , 1952, 1.95ms, 195.2us, .0%, | |
61, RYWATABE , WAIT, db file single write , 4622, 4.62ms, 462.2us, .0%, | |
61, RYWATABE , WAIT, SQL*Net message to client , 3, 3us, .3us, .0%, | |
61, RYWATABE , WAIT, SQL*Net message from client , 9412738, 9.41s, 941.27ms, 94.1%, |@@@@@@@@@@|
-- End of Stats snap 1, end=2013-08-29 04:24:03, seconds=10
PL/SQLプロシージャが正常に完了しました。
SQL>
同様のINSERT文を、一時表に対して実行して確認してみます。
SQL> CREATE GLOBAL TEMPORARY TABLE tbl_temp (id number, str char(2000)) ON COMMIT PRESERVE ROWS; 表が作成されました。 SQL> insert into tbl_temp select level, level from dual connect by level <= 10000; 10000行が作成されました。
SQL> @snapper12c stats 10 1 61
Sampling SID 61 with interval 10 seconds, taking 1 snapshots...
-- Session Snapper v3.52 by Tanel Poder @ E2SN ( http://tech.e2sn.com )
----------------------------------------------------------------------------------------------------------------------------------------------------
SID, USERNAME , TYPE, STATISTIC , DELTA, HDELTA, HDELTA/SEC, %TIME, GRAPH
----------------------------------------------------------------------------------------------------------------------------------------------------
61, RYWATABE , STAT, Requests to/from client , 1, 1, .09,
61, RYWATABE , STAT, opened cursors cumulative , 10, 10, .91,
61, RYWATABE , STAT, user calls , 2, 2, .18,
61, RYWATABE , STAT, recursive calls , 47, 47, 4.27,
61, RYWATABE , STAT, session logical reads , 19780, 19.78k, 1.8k,
61, RYWATABE , STAT, CPU used when call started , 13, 13, 1.18,
61, RYWATABE , STAT, CPU used by this session , 15, 15, 1.36,
61, RYWATABE , STAT, DB time , 27, 27, 2.45,
61, RYWATABE , STAT, non-idle wait count , 1, 1, .09,
61, RYWATABE , STAT, session uga memory , 516416, 516.42k, 46.95k,
61, RYWATABE , STAT, messages sent , 1, 1, .09,
61, RYWATABE , STAT, session pga memory , 458752, 458.75k, 41.7k,
61, RYWATABE , STAT, enqueue requests , 748, 748, 68,
61, RYWATABE , STAT, enqueue releases , 744, 744, 67.64,
61, RYWATABE , STAT, db block gets , 17232, 17.23k, 1.57k,
61, RYWATABE , STAT, db block gets from cache , 17232, 17.23k, 1.57k,
61, RYWATABE , STAT, db block gets from cache (fastpath) , 8, 8, .73,
61, RYWATABE , STAT, consistent gets , 2548, 2.55k, 231.64,
61, RYWATABE , STAT, consistent gets from cache , 2548, 2.55k, 231.64,
61, RYWATABE , STAT, consistent gets pin , 2516, 2.52k, 228.73,
61, RYWATABE , STAT, consistent gets pin (fastpath) , 2516, 2.52k, 228.73,
61, RYWATABE , STAT, consistent gets examination , 32, 32, 2.91,
61, RYWATABE , STAT, consistent gets examination (fastpath) , 32, 32, 2.91,
61, RYWATABE , STAT, logical read bytes from cache , 162037760, 162.04M, 14.73M,
61, RYWATABE , STAT, db block changes , 24818, 24.82k, 2.26k,
61, RYWATABE , STAT, consistent changes , 19757, 19.76k, 1.8k,
61, RYWATABE , STAT, change write time , 2, 2, .18,
61, RYWATABE , STAT, free buffer requested , 3368, 3.37k, 306.18,
61, RYWATABE , STAT, calls to kcmgcs , 9175, 9.18k, 834.09,
61, RYWATABE , STAT, calls to kcmgas , 4, 4, .36,
61, RYWATABE , STAT, calls to get snapshot scn: kcmgss , 12, 12, 1.09,
61, RYWATABE , STAT, redo entries , 5012, 5.01k, 455.64,
61, RYWATABE , STAT, redo size , 751380, 751.38k, 68.31k, <==
61, RYWATABE , STAT, temp space allocated (bytes) , 28311552, 28.31M, 2.57M,
61, RYWATABE , STAT, undo change vector size , 347016, 347.02k, 31.55k,
61, RYWATABE , STAT, no work - consistent read gets , 8, 8, .73,
61, RYWATABE , STAT, active txn count during cleanout , 20, 20, 1.82,
61, RYWATABE , STAT, cleanout - number of ktugct calls , 20, 20, 1.82,
61, RYWATABE , STAT, IMU Flushes , 1, 1, .09,
61, RYWATABE , STAT, IMU ktichg flush , 1, 1, .09,
61, RYWATABE , STAT, KTFB alloc req , 4, 4, .36,
61, RYWATABE , STAT, KTFB alloc space (block) , 262144, 262.14k, 23.83k,
61, RYWATABE , STAT, KTFB alloc time (ms) , 212, 212, 19.27,
61, RYWATABE , STAT, KTFB apply req , 4, 4, .36,
61, RYWATABE , STAT, KTFB apply time (ms) , 43, 43, 3.91,
61, RYWATABE , STAT, table scans (short tables) , 1, 1, .09,
61, RYWATABE , STAT, cluster key scans , 1, 1, .09,
61, RYWATABE , STAT, cluster key scan block gets , 1, 1, .09,
61, RYWATABE , STAT, index fetch by key , 2, 2, .18,
61, RYWATABE , STAT, index scans kdiixs1 , 7, 7, .64,
61, RYWATABE , STAT, HSC Heap Segment Block Changes , 5000, 5k, 454.55,
61, RYWATABE , STAT, Heap Segment Array Inserts , 5000, 5k, 454.55,
61, RYWATABE , STAT, session cursor cache hits , 6, 6, .55,
61, RYWATABE , STAT, buffer is not pinned count , 10, 10, .91,
61, RYWATABE , STAT, workarea executions - optimal , 1, 1, .09,
61, RYWATABE , STAT, parse count (total) , 4, 4, .36,
61, RYWATABE , STAT, parse count (hard) , 1, 1, .09,
61, RYWATABE , STAT, execute count , 10, 10, .91,
61, RYWATABE , STAT, bytes sent via SQL*Net to client , 195, 195, 17.73,
61, RYWATABE , STAT, bytes received via SQL*Net from client , 371, 371, 33.73,
61, RYWATABE , STAT, SQL*Net roundtrips to/from client , 1, 1, .09,
61, RYWATABE , STAT, sorts (memory) , 3, 3, .27,
61, RYWATABE , STAT, sorts (rows) , 1, 1, .09,
61, RYWATABE , TIME, hard parse elapsed time , 2273, 2.27ms, 206.64us, .0%, | |
61, RYWATABE , TIME, repeated bind elapsed time , 18, 18us, 1.64us, .0%, | |
61, RYWATABE , TIME, parse time elapsed , 2729, 2.73ms, 248.09us, .0%, | |
61, RYWATABE , TIME, DB CPU , 156977, 156.98ms, 14.27ms, 1.4%, |@ |
61, RYWATABE , TIME, sql execute elapsed time , 269177, 269.18ms, 24.47ms, 2.4%, |@ |
61, RYWATABE , TIME, DB time , 271488, 271.49ms, 24.68ms, 2.5%, |@ |
61, RYWATABE , WAIT, SQL*Net message to client , 4, 4us, .36us, .0%, | |
61, RYWATABE , WAIT, SQL*Net message from client , 9623807, 9.62s, 874.89ms, 87.5%, |@@@@@@@@@ |
-- End of Stats snap 1, end=2013-08-29 04:17:25, seconds=11
PL/SQLプロシージャが正常に完了しました。
通常の表（永続表）ではredo sizeが 22.49M でしたが、一時表ではredo sizeが 751.38k に削減されています。
REDO生成量が削減された理由は、一時表領域におけるデータ更新ではREDOが生成されないためです。通常の表はデータを永続表領域に確保しますが、 一時表は一時表領域に確保します。REDO生成量削減によるI/O量削減のトレードオフとして、ロールフォワードができなくなります。したがって、一時表はバックアップからのリカバリにより復元することができません。 ただ、この制約は一般に問題となりません。
次にOracle Database 12c新機能のTemporay Undoを有効にして、REDO生成量を確認します。
SQL> alter session set temp_undo_enabled=true; セッションが変更されました。 SQL> insert into tbl_temp select level, level from dual connect by level <= 10000; 10000行が作成されました。
SQL> @snapper12c stats 10 1 61
Sampling SID 61 with interval 10 seconds, taking 1 snapshots...
-- Session Snapper v3.52 by Tanel Poder @ E2SN ( http://tech.e2sn.com )
----------------------------------------------------------------------------------------------------------------------------------------------------
SID, USERNAME , TYPE, STATISTIC , DELTA, HDELTA, HDELTA/SEC, %TIME, GRAPH
----------------------------------------------------------------------------------------------------------------------------------------------------
61, RYWATABE , STAT, Requests to/from client , 1, 1, .06,
61, RYWATABE , STAT, opened cursors cumulative , 1, 1, .06,
61, RYWATABE , STAT, user calls , 2, 2, .13,
61, RYWATABE , STAT, recursive calls , 29, 29, 1.81,
61, RYWATABE , STAT, recursive cpu usage , 1, 1, .06,
61, RYWATABE , STAT, session logical reads , 19718, 19.72k, 1.23k,
61, RYWATABE , STAT, CPU used by this session , 15, 15, .94,
61, RYWATABE , STAT, DB time , 46, 46, 2.88,
61, RYWATABE , STAT, non-idle wait count , 2, 2, .13,
61, RYWATABE , STAT, session uga memory , 523952, 523.95k, 32.75k,
61, RYWATABE , STAT, session pga memory , 262144, 262.14k, 16.38k,
61, RYWATABE , STAT, enqueue requests , 741, 741, 46.31,
61, RYWATABE , STAT, enqueue releases , 737, 737, 46.06,
61, RYWATABE , STAT, db block gets , 17215, 17.22k, 1.08k,
61, RYWATABE , STAT, db block gets from cache , 17215, 17.22k, 1.08k,
61, RYWATABE , STAT, consistent gets , 2503, 2.5k, 156.44,
61, RYWATABE , STAT, consistent gets from cache , 2503, 2.5k, 156.44,
61, RYWATABE , STAT, consistent gets pin , 2502, 2.5k, 156.38,
61, RYWATABE , STAT, consistent gets pin (fastpath) , 2502, 2.5k, 156.38,
61, RYWATABE , STAT, consistent gets examination , 1, 1, .06,
61, RYWATABE , STAT, consistent gets examination (fastpath) , 1, 1, .06,
61, RYWATABE , STAT, logical read bytes from cache , 161529856, 161.53M, 10.1M,
61, RYWATABE , STAT, db block changes , 24814, 24.81k, 1.55k,
61, RYWATABE , STAT, consistent changes , 24812, 24.81k, 1.55k,
61, RYWATABE , STAT, free buffer requested , 3393, 3.39k, 212.06,
61, RYWATABE , STAT, calls to kcmgcs , 9169, 9.17k, 573.06,
61, RYWATABE , STAT, calls to kcmgas , 1, 1, .06,
61, RYWATABE , STAT, calls to get snapshot scn: kcmgss , 2, 2, .13,
61, RYWATABE , STAT, redo entries , 1, 1, .06,
61, RYWATABE , STAT, redo size , 272, 272, 17,<==
61, RYWATABE , STAT, temp space allocated (bytes) , 29360128, 29.36M, 1.84M,
61, RYWATABE , STAT, undo change vector size , 426788, 426.79k, 26.67k,
61, RYWATABE , STAT, active txn count during cleanout , 1, 1, .06,
61, RYWATABE , STAT, cleanout - number of ktugct calls , 1, 1, .06,
61, RYWATABE , STAT, IMU Flushes , 1, 1, .06,
61, RYWATABE , STAT, IMU ktichg flush , 1, 1, .06,
61, RYWATABE , STAT, HSC Heap Segment Block Changes , 5000, 5k, 312.5,
61, RYWATABE , STAT, Heap Segment Array Inserts , 5000, 5k, 312.5,
61, RYWATABE , STAT, session cursor cache count , 1, 1, .06,
61, RYWATABE , STAT, workarea executions - optimal , 1, 1, .06,
61, RYWATABE , STAT, parse count (total) , 1, 1, .06,
61, RYWATABE , STAT, execute count , 1, 1, .06,
61, RYWATABE , STAT, bytes sent via SQL*Net to client , 195, 195, 12.19,
61, RYWATABE , STAT, bytes received via SQL*Net from client , 371, 371, 23.19,
61, RYWATABE , STAT, SQL*Net roundtrips to/from client , 1, 1, .06,
61, RYWATABE , STAT, sorts (memory) , 1, 1, .06,
61, RYWATABE , STAT, sorts (rows) , 1, 1, .06,
61, RYWATABE , WAIT, Disk file operations I/O , 66, 66us, 4.13us, .0%, | |
61, RYWATABE , WAIT, SQL*Net message to client , 4, 4us, .25us, .0%, | |
61, RYWATABE , WAIT, SQL*Net message from client , 16000000, 16s, 1s, 100.0%, |@@@@@@@@@@|
-- End of Stats snap 1, end=2013-08-29 04:20:19, seconds=16
PL/SQLプロシージャが正常に完了しました。
Temporay Undoを有効にしなかったときのredo size 751.38k から、272バイトまで削減されています。
Temporay Undoを有効にして、REDO生成量が削減された理由は、UNDOデータがUNDO表領域に格納されなくなったためです。 Temporay Undoを有効にしない状況でも、一時表領域内のデータ更新についてはREDOは生成されませんが、UNDO表領域におけるデータ更新についてはREDOが生成されます。Temporay Undoを有効にすることで、 UNDO表領域を使用することがなくなり、ひいてはUNDO表領域におけるデータ更新に関わるREDOが生成されなくなるというわけです。
プロフィール
渡部 亮太
・Oracle ACE
・AWS Certified Solutions Architect - Associate
・ORACLE MASTER Platinum Oracle Database 11g, 12c 他多数
カテゴリー
アーカイブ
2024年
2023年
2022年
2021年
2020年
2019年
2018年
2017年
2016年
2015年
2014年
2013年
2012年
2000年