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

Report message to a moderator

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: 4587
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

Report message to a moderator

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/

Report message to a moderator

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.

Report message to a moderator

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.

Report message to a moderator

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.

Report message to a moderator

Previous Topic: which version of tomcat is compatible with oracle 9i
Next Topic: keep and recycle pools in oracle 9i
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Dec 23 00:00:24 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.