Insert using /*+ append */ hint not reduced my time

[abhaysk]

Its a SQLPLUS command, "Set ArraySize".. I hope your using SQLPLUS client for your data loads..


Rgds
Abhay.

[mallma]

Hi Abhay,
I have a procedure for this dataload . Job runs this procedure 5 times a day.
How can I set the array size.

thanks
mallma

[hrishy]

Hi

Curious to know whats

1)Your pctfree and pctused for this table
2)when you run the insert can you just run a tkprof of it ? or lets know what the session is waiting for
3)Autotrace statistics


regards
Hrishy

[hrishy]

I would guess that fetch of 30K records from remote DB.. is taking most of ur time.. you can do a simple test for urself and check it..

Wass ur Arraysize? ( it does matter for data over n/w )

@Hrishy,

Append indeed works if even indexes are there.. but only for table.. for indexes its a usual way to have redo recorded.. And again here your suggestion without even thinking?

Rgds
Abhay.

I always drop or disable indexes on the table where i am doing a dataload and rebuild those indexes in parallel and nologging mode.Thats why i said i think so..wasnt really confident :-)

regards
Hrishy

[mallma]

Hi Hrishy,
Pctfree is 10
pct used is 40
How can I run the TKprof when I am executing the procedure
also how can i trace the statistics. I am executing the procedure which loads the data, from TOAD.

Thanks
mallma

[hrishy]

Hi

In order to trace a session do this.You need sysdba privileges for doing this.If you have a DBA you can take his help

from sqlplus do

1)select username, spid from v$process;
2)oradebug setospid
3)oradebug unlimit (so ur trace file is large enough)
4)oradebug event 10046 trace name context forever, level 12
5)oradebug tracefile_name
6)oradebug event 10046 trace name context off

can you also post your pl/sql code here using the code tags.

on seeting the arary size in TOAD

http://www.toadsoft.com/faq2.html#DataGrid9

regards
Hrishy

[John Spencer]

I have a procedure for this dataload . Job runs this procedure 5 times a day.

Just out of curiosity, does you procedure do something like this?

FOR r IN (SELECT stuff FROM table@remote) LOOP
   INSERT /*+ APPEND */ INTO local_table
   VALUES(r.col1, r.col2, r.col3 ... );
   COMMIT;

John

[mallma]

Hi,
I am not suppose to post my query here because of restriction,but i can tell you the scenareo,

Procedure p1 is
local variables;
begin
--I have loops to wait until the remote is getting or loading data from another remote.
--it sleeps for 1hour, if the remote is loaded data from another remote then I will load --data from this remote,

--here my insert to load from remote DB
INSERT /*+ APPEND */ into mytablename
(
columnnames
:
:
) --Totally 125 columns
SELECT
columnnames
.
:

from
remotetablename@dblinkname,
:
;
--totally 22 table from remoteDB
Where
Conditions
n-1 join conditions(n is number of tables)
includes some other conditions also
union all
SELECT
columnnames
.
:

from
remotetablename@dblinkname,
:
;
--totally 22 table from remoteDB
Where
Conditions
n-1 join conditions(n is number of tables)
includes some other conditions also

union all

SELECT
columnnames
.
:

from
remotetablename@dblinkname,
:
;
--totally 22 table from remoteDB
Where
Conditions
n-1 join conditions(n is number of tables)
includes some other conditions also



end p1;



--- This insert alone is taking 145 seconds.
the same insert is executed within the remote DB itself is taking 35seconds and the select alone is taking 1 second.

When the insert is executed from another DB(my databse) its taking 145seconds to load the data.


Hrishy,
I didn't execute the commands to trace the session from sqlplus,because I dont have
permission to do that, even I dont have access to execute my procedure. somebody else will execute it .I can see the output.

first I want to know is the time taken is reasonable or not?

Thanks
mallma

[hrishy]

Hi,

Hrishy,
I didn't execute the commands to trace the session from sqlplus,because I dont have
permission to do that, even I dont have access to execute my procedure. somebody else will execute it .I can see the output.

first I want to know is the time taken is reasonable or not?

Thanks
mallma

Well Mallma thats the million dollar question :-).You can ask your DBA to set the trace on and send across the ouput of the trace so u can upload it here i am interested in the trace file and want to know how exactly is oracle spending its time when inserting the rows.

You could also try executing the pl/sql on the server instead of the round trip to your server -->to your pc--->then to another server




regards
Hrishy

[mallma]

Hi Hrishy,
The person executing the script is not a DBA but he has the privillage to set the trace on.
can you please tell me how can he get the trace on in TOAD.co'z he is NOTusing the SQLPLUS.

thanks
mallma