Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, shahid.

Asked: May 10, 2013 - 4:50 am UTC

Last updated: May 14, 2013 - 12:50 pm UTC

Version: 11.2.0.3

Viewed 10K+ times! This question is

You Asked

Hi Tom,

1) During import is it that after the whole table is imported oracle commits the data, if the whole table is not imported and import is terminated in that case is the whole table rolled back?

2) If i have a huge table 5 crore rows can i do something in imp/impdp that after 1 crore rows the impdp will commit the rows so tht we have the data as well undo freeing up.

Regards
Shahid

and Tom said...

import has a "COMMIT" parameter. It won't do it after a fixed number of records, but rather after each array insert it performs which you can control with the buffer parameter (although if your table has a long or lob, arraysize will be ONE).


Since an insert generates very very very very little undo (just a "delete+rowid" entry), it would be uncommon for you to need to do this, very very very very uncommon.

also, you should be using data pump which will permit you to do a direct path insert with an impdp operation. Direct path operations bypass undo generation.

http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_overview.htm#CJAFDGIC

making this conversation unnecessary :)

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

shahid shaikh, May 13, 2013 - 6:21 am UTC

Thanks for the Reply it was really helpful,

Just wanted to know that in impdp can we control after how many record we can commit or does impdp too follows buffer full logic and then commits the data after flushinf the buffer.

Regards
Shahid
Tom Kyte
May 13, 2013 - 1:06 pm UTC

impdb is consistent, it does a table and then commits. Remember direct path = no undo. Redo will be less because of fewer commits.

shahid shaikh, May 14, 2013 - 4:22 am UTC

Thanks for the reply,

Just for conformation you mean to say in normal imp its after buffer flsuh the records are commited and in impdp its after the whole table is imported its commited. It means in impdp if only few records are inserted in a table and system crashes that table will not be imported or only the metadata will be imported and all rows are rolled back.

Thanks
Shahid
Tom Kyte
May 14, 2013 - 11:44 am UTC

both imp and impdp might have the table created (or not). DDL automatically commits so the act of creating the table and the act of filling the table are divorced from each other.

If the table was created by either - it will still be created after the crash.


If impdp is interrupted during the load, then the load will be consistently and entirely rolled back.

If import is interrupted during the load, by default the load will be consistently and entirely rolled back.

import has a bad option allowing you to commit batches, in that case if import crashes - whatever was loaded and committed will remain - leaving your data in an inconsistent state from which you'll have to truncate or drop it in order to resume your import. impdp does not suffer this deficiency.

Fewer commits will generate fewer redo

Shankar Sen, May 14, 2013 - 12:07 pm UTC

Redo will be less because of fewer commits
your comment in above post confused me. Can you please explain a bit more how number of commits is related to the volume of redo generation?

Thanks in advance
Tom Kyte
May 14, 2013 - 12:50 pm UTC

every time you commit ,there is additional information generated.

if you commit 1,000,000 records one at a time, you generate 1,000,000 commit records.

if you commit 1,000,000 records once, you generate one commit record.

big_table%ORA11GR2> create table t ( x int, y char(30) );

Table created.

big_table%ORA11GR2> exec runStats_pkg.rs_start;

PL/SQL procedure successfully completed.

big_table%ORA11GR2> begin
  2          for i in 1..1000000
  3          loop
  4                  insert into t (x,y) values ( i, 'x' );
  5                  commit;
  6          end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

big_table%ORA11GR2> exec runStats_pkg.rs_middle;

PL/SQL procedure successfully completed.

big_table%ORA11GR2> begin
  2          for i in 1..1000000
  3          loop
  4                  insert into t (x,y) values ( i, 'x' );
  5          end loop;
  6          commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

big_table%ORA11GR2> exec runStats_pkg.rs_stop(100000);
Run1 ran in 8196 cpu hsecs
Run2 ran in 2688 cpu hsecs
run 1 ran in 304.91% of the time

Name                                  Run1        Run2        Diff
STAT...cell physical IO interc     204,800           0    -204,800
STAT...physical read bytes         204,800           0    -204,800
STAT...physical read total byt     204,800           0    -204,800
LATCH.simulator hash latch         296,713      86,915    -209,798
STAT...session pga memory         -131,072     196,608     327,680
STAT...redo ordering marks         366,752      12,030    -354,722
STAT...deferred (CURRENT) bloc     502,910          10    -502,900
STAT...messages sent               508,157         455    -507,702
LATCH.Consistent RBA               508,160         373    -507,787
LATCH.lgwr LWN SCN                 508,520         367    -508,153
LATCH.mostly latch-free SCN        512,329         374    -511,955
STAT...commit cleanouts          1,000,034         292    -999,742
STAT...user commits              1,000,002           1  -1,000,001
STAT...commit cleanouts succes   1,000,024          20  -1,000,004
LATCH.session allocation         1,000,234          70  -1,000,164
LATCH.session idle bit           1,000,264          89  -1,000,175
STAT...opened cursors cumulati   2,000,336   1,000,071  -1,000,265
STAT...calls to get snapshot s   2,000,536   1,000,087  -1,000,449
LATCH.shared pool                1,001,652       1,039  -1,000,613
STAT...redo entries              2,036,225   1,029,248  -1,006,977
LATCH.messages                   1,020,710       1,912  -1,018,798
STAT...calls to kcmgas           1,366,863      12,057  -1,354,806
LATCH.redo writing               1,524,398       1,348  -1,523,050
LATCH.redo allocation            1,528,000       2,801  -1,525,199
STAT...db block changes          4,037,263   2,041,313  -1,995,950
STAT...session logical reads     3,068,258   1,071,741  -1,996,517
LATCH.DML lock allocation        2,000,495          37  -2,000,458
STAT...db block gets             3,066,122   1,065,639  -2,000,483
STAT...db block gets from cach   3,066,122   1,065,639  -2,000,483
STAT...enqueue requests          2,001,183         557  -2,000,626
STAT...enqueue releases          2,001,184         557  -2,000,627
STAT...recursive calls           3,002,365   1,000,558  -2,001,807
LATCH.undo global data           3,022,545      17,555  -3,004,990
LATCH.enqueue hash chains        4,006,110       1,925  -4,004,185
LATCH.cache buffers chains      12,294,175   5,258,539  -7,035,636
STAT...undo change vector size 159,786,564  91,972,248 -67,814,316
STAT...redo size               720,572,880 302,892,988-417,679,892
STAT...logical read bytes from####################################

Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
  31,455,790   6,520,620 -24,935,170    482.40%

PL/SQL procedure successfully completed.


2x the redo
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.