-
Its a SQLPLUS command, "Set ArraySize".. I hope your using SQLPLUS client for your data loads..
Rgds
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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
-
Originally Posted by abhaysk
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
-
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
Last edited by hrishy; 07-04-2005 at 08:11 AM.
-
Originally Posted by mallma
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?
Code:
FOR r IN (SELECT stuff FROM table@remote) LOOP
INSERT /*+ APPEND */ INTO local_table
VALUES(r.col1, r.col2, r.col3 ... );
COMMIT;
John
-
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
-
Originally Posted by mallma
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
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|