Home » RDBMS Server » Performance Tuning » Regarding Procedure Performance
Regarding Procedure Performance [message #65429] Mon, 20 September 2004 03:59 Go to next message
Rohit Jain
Messages: 7
Registered: December 2003
Junior Member

   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 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator

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.
first analyze the schema.

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).
Previous Topic: The query too slow
Next Topic: Delete is taking time
Goto Forum:

Current Time: Sun Mar 03 03:45:18 CST 2024