Its a SQLPLUS command, "Set ArraySize".. I hope your using SQLPLUS client for your data loads..
Rgds
Abhay.
Printable View
Its a SQLPLUS command, "Set ArraySize".. I hope your using SQLPLUS client for your data loads..
Rgds
Abhay.
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
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
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 :-)Quote:
Originally Posted by abhaysk
regards
Hrishy
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
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
Just out of curiosity, does you procedure do something like this?Quote:
Originally Posted by mallma
JohnCode:FOR r IN (SELECT stuff FROM table@remote) LOOP
INSERT /*+ APPEND */ INTO local_table
VALUES(r.col1, r.col2, r.col3 ... );
COMMIT;
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
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.Quote:
Originally Posted by mallma
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
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