internal mechanism of export/import

All posts relating to Oracle database administration.

Moderator: Tim...

internal mechanism of export/import

Postby sayampu » Mon Apr 16, 2012 1:51 pm

Hi Tim,

Could you explain how the internal mechanism of export and import would be done.

I mean what are the memory areas(sga,undo,temp tablespaces) will be used while performing export/import. So please explain the internal process of export/import jobs.

Thanks in advance.

Regards
Sayampu
sayampu
Member
 
Posts: 27
Joined: Wed Feb 15, 2012 8:58 am

Re: internal mechanism of export/import

Postby Tim... » Mon Apr 16, 2012 2:13 pm

Hi.

Not much to say really.

Export: It just queries the data and writes it to a file. In terms of the database interaction, this is just like any query of the data.

Import: Depending on the type of import, this will either be:

- Exactly the same as regular row-by-row inserts if using conventional path loading.
- Exactly the same as direct path loads, building an entire block and inserting it beyond the high watermark in the table.

Data pump doesn't do anything miraculous. It is just a client tool that is interacting with the database.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: internal mechanism of export/import

Postby sayampu » Mon Apr 16, 2012 3:49 pm

Thanks for your quick response Tim.

So You mean to say that it is just like a query (SELECT,INSERT,UPDATE...ETC).

Thanks

Sayampu
sayampu
Member
 
Posts: 27
Joined: Wed Feb 15, 2012 8:58 am

Re: internal mechanism of export/import

Postby Tim... » Mon Apr 16, 2012 4:39 pm

Hi.

Not update. Just SELECT and INSERT.

Remember, there is a big different in performance between conventional path and direct path loads.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: internal mechanism of export/import

Postby sayampu » Tue Apr 17, 2012 10:24 am

Thanks again Tim.

So while importing it'll perform insert operation, then definitely it uses undo tablespace, buffer cache(for select),PGA(for select) . Everything will be done like normal query.

Conventional Path, It gets the data from buffer cahe
Direct Path, gets directly from datafiles (fastest method).-------- Will it impact on performance ?

Regards

Sayampu
sayampu
Member
 
Posts: 27
Joined: Wed Feb 15, 2012 8:58 am

Re: internal mechanism of export/import

Postby Tim... » Tue Apr 17, 2012 1:52 pm

Hi.

When using conventional path, then everything comes through the buffer cache. When doing direct path, whole blocks are built up and written to the datafile, rather than doing a row-by-row insert. It is like the difference between:

Code: Select all
INSERT INTO my_table
SELECT * FROM my_table2;


and

Code: Select all
INSERT /*+APPEND */ INTO my_table
SELECT * FROM my_table2;


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: internal mechanism of export/import

Postby sayampu » Thu Apr 19, 2012 1:16 pm

Sorry for the late response Tim......

Direct path will impact on database performance?

In fact you rise a new questions here. If i use append hint i think it will insert the data after high water mark and not go for scanning free blocks.That is why it's fast . right?

Thanks

Sayampu
sayampu
Member
 
Posts: 27
Joined: Wed Feb 15, 2012 8:58 am

Re: internal mechanism of export/import

Postby Tim... » Thu Apr 19, 2012 1:25 pm

Hi.

Yes and yes.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: internal mechanism of export/import

Postby sayampu » Fri Apr 20, 2012 3:05 pm

Thanks for the clarification Tim.
sayampu
Member
 
Posts: 27
Joined: Wed Feb 15, 2012 8:58 am

Re: internal mechanism of export/import

Postby Tim... » Fri Apr 20, 2012 3:10 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 9 guests

cron