Home » RDBMS Server » Performance Tuning » ORACLE 9i :- TUNING data extraction from EXTERNAL TABLES
ORACLE 9i :- TUNING data extraction from EXTERNAL TABLES [message #64997] Fri, 26 March 2004 02:19 Go to next message
Sunil Bassi
Messages: 28
Registered: June 2003
Junior Member
Hi,
We are having a datafile of size 700MB. WE are uploading it into our system in the following steps using EXTERNAL TABLEs( a new feature in Oralce 9i) :-
1. We are loading the data into external table .
2. we are inserting the data into our tmp table using
insert /*+APPEND*/ into temp select * from external table.
This 2nd step iteself takes 1 hr to finish. How can we tune this. what initalization parameters are to be taken care of..

pls suggest
Thanks
Sunil
Re: ORACLE 9i :- TUNING data extraction from EXTERNAL TABLES [message #64998 is a reply to message #64997] Fri, 26 March 2004 02:51 Go to previous messageGo to next message
Frank Naude
Messages: 4565
Registered: April 1998
Senior Member
Hi,

See section "Performance Hints When Using External Tables" in chapter 11 of the Oracle9i Database Utilities Manual.

Best regards.

Frank
Re: ORACLE 9i :- TUNING data extraction from EXTERNAL TABLES [message #65002 is a reply to message #64998] Fri, 26 March 2004 19:20 Go to previous messageGo to next message
Jitendra Agrawal
Messages: 71
Registered: December 2003
Member
Hi,

One thing that I noticed when we were facing similar problem in loading external file: Initial extents and next extents for my table were very small.

You should use a large extent value.

Regards,
Jitendra Agrawal

http://www.telemune.com/
Re: ORACLE 9i :- TUNING data extraction from EXTERNAL TABLES [message #65005 is a reply to message #64997] Sun, 28 March 2004 09:47 Go to previous messageGo to next message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
Is the table you load into defined as NOLOGGING (or else is the database in NOARCHIVELOG mode)? Otherwise APPEND has no effect on redo logging. (AUTOTRACE in SQL*Plus is an easy way to see how much redo you are generating.)

Also, is this table in a locally managed tablespace (LMT)? These perform a lot faster than the old dictionary managed kind.

TKPROF might shed some light on what is taking the time.
Import data from text files in Oracle 9i without using External Tables. [message #65668 is a reply to message #65005] Fri, 26 November 2004 18:08 Go to previous messageGo to next message
Ayazuddin
Messages: 1
Registered: November 2004
Junior Member
That's nice, but will you plz tell me how can I import data from text files in Oracle 9i without using External Tables? It's really urgent.
Re: Import data from text files in Oracle 9i without using External Tables. [message #65670 is a reply to message #65668] Sat, 27 November 2004 00:58 Go to previous message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
Small volumes: UTL_FILE.
Larger volumes: SQL*Loader.
Previous Topic: which version of tomcat is compatible with oracle 9i
Next Topic: keep and recycle pools in oracle 9i
Goto Forum:
  


Current Time: Tue Sep 29 10:24:46 CDT 2020