| Regarding Procedure Performance [message #65429] |
Mon, 20 September 2004 03:59  |
Rohit Jain
Messages: 7
Registered: December 2003
|
Junior Member |
|
|
Hi,
I have two problems, which is I am explaining below:-
Problem 1:
I have 2 datafiles in .DBF format. I have loaded data thru these datafiles into 2 different temporary oracle database tables using SQL*LOADER. Now, What I am doing is that, I have created few procedures thru which I am transferring data into another oracle DB tables. In each procedure I am using 2 cursor, 1 for inserting data & another is for updating that row with some other values and then commiting destination table.
Now problem is that each table thru which I am transferring data have 2 lacs of records and its taking around 10 hours for 1 lacs of records to transfer.
I am also using index & index hint in select statement of cursor. How should I improve its performance ?
Problem 2:
I have data in a table, now I want to know the path of corresponding datafile of this table.
Hope for immediate response.
Thanks and Reagards
Rohit Jain
|
|
|
|
| Re: Regarding Procedure Performance [message #65430 is a reply to message #65429] |
Mon, 20 September 2004 06:27  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>>1
are doing a commit for every record that is inserted / updated?
That would be a pain.
commit at the end.
You do have the index.
But is the sql using your index?
have you checked the execution plan?
and above all,
are there any statistics (table and index) genereted regularly.
else
first analyze the schema.
>>2
You can identify the tablespace in which table is placed.
If there is multiple datafiles in tablespace, you cannot clearly MAP the table and underlying datafiles ( and records of the same table may also be scattered across the datafiles in the same tablespace).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
