internal mechanism of export/import

[sayampu]

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

[Tim...]

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...

[sayampu]

Thanks for your quick response Tim.

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

Thanks

Sayampu

[Tim...]

Hi.

Not update. Just SELECT and INSERT.

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

Cheers

Tim...

[sayampu]

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

[Tim...]

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...

[sayampu]

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

[Tim...]

Hi.

Yes and yes.

Cheers

Tim...

[sayampu]

Thanks for the clarification Tim.

[Tim...]