Insert using /*+ append */ hint not reduced my time - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 29

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

  1. #11
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  2. #12
    Join Date
    Jun 2005
    Posts
    12
    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

  3. #13
    Join Date
    Jan 2001
    Posts
    2,828
    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

  4. #14
    Join Date
    Jan 2001
    Posts
    2,828
    Quote 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

  5. #15
    Join Date
    Jun 2005
    Posts
    12
    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

  6. #16
    Join Date
    Jan 2001
    Posts
    2,828
    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 09:11 AM.

  7. #17
    Join Date
    May 2005
    Location
    Toronto Canada
    Posts
    57
    Quote 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

  8. #18
    Join Date
    Jun 2005
    Posts
    12
    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

  9. #19
    Join Date
    Jan 2001
    Posts
    2,828
    Quote 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

  10. #20
    Join Date
    Jun 2005
    Posts
    12
    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
  •  


Click Here to Expand Forum to Full Width