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

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

  1. #21
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Mallma

    I am not sure how to do it in toad.never used toad much .

    How about requesting your DBA's help

    regards
    Hrishy

  2. #22
    Join Date
    Jun 2005
    Posts
    12
    Hi Hrishy,
    I'll do that.

    Thanks
    mallma

  3. #23
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You can find my blog entry on tracing here
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #24
    Join Date
    May 2005
    Location
    Toronto Canada
    Posts
    57
    You might try changing the first SELECT in your query to use the DRIVING_SITE hint. So, it should look something like:

    Code:
    SELECT /*+ DRIVING_SITE (remotetablename) */
           columnnames
    FROM remotetablename@dblinkname
    If you are aliasing remotetablename, then you will need to use the alias instead of remotetablename in the hint.

    TTFN
    John

  5. #25
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    as long as we're at it, did you try changing the init.ora parameters:
    fast=true
    silver_bullet_level=16


    Last edited by marist89; 07-05-2005 at 12:52 PM.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #26
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    On the subject of direct path INSERT,
    I have given Append hint in the insert query which is loding data from remote DB,eventhough the time taken by the insert query is not reduced.
    Is a direct-path INSERT actually taking place at all? A couple of quick ways to check:

    1) Attempt to count the rows in the table immediately after the INSERT completes (before any COMMIT or ROLLBACK). If you get "ORA-12838: cannot read/modify an object after modifying it in parallel" then it was a direct-path INSERT.

    2) Using SQL*Plus AUTOTRACE (or equivalent - I don't have TOAD but I'm sure it has a session stats page), is the redo size smaller when using the APPEND hint than without?

    Are you talking about the NOLOGGING in the create table command?
    It is a table/index/mview attribute and can only appear in CREATE or ALTER syntax. If your database is running in NOARCHIVELOG mode, everything is in effect NOLOGGING ("SELECT log_mode FROM v$database" to check); if not, check the table with "SELECT logging FROM user_tables WHERE table_name = ...".

    However as others have suggested, the performance issue is most likely nothing to do with the INSERT but more to do with the SELECT and its execution plan.

  7. #27
    Join Date
    Jun 2005
    Posts
    12
    Hi John,
    Thanks for the response, to use the DRIVING_SITE hint I have 22 table used by select query from Remote Database, I should specify all the 22 table name in the palce of "remotetablename" or any one of the table?

    thanks
    mallma

  8. #28
    Join Date
    Jun 2005
    Posts
    12
    Hi willian,
    I have given select statement for counting the row immediatly after the INSERT there is no COMMIT or ROLLBACK. I am getting the same error.
    ORA-12838: cannot read/modify an object after modifying it in parallel
    ORA-06512: at "DW.P1", line 710
    ORA-06512: at line 2

    Autotrace for the INSERT is not displaying in TOAD, only for the select statements i can get the Autotrace.I am not sure i am looking the correct one. I will update you on this .

    thanks
    mallma
    Last edited by mallma; 07-06-2005 at 03:10 AM.

  9. #29
    Join Date
    Jan 2001
    Posts
    2,828
    Quote Originally Posted by mallma
    Hi willian,
    I have given select statement for counting the row immediatly after the INSERT there is no COMMIT or ROLLBACK. I am getting the same error.
    ORA-12838: cannot read/modify an object after modifying it in parallel
    ORA-06512: at "DW.P1", line 710
    ORA-06512: at line 2

    Autotrace for the INSERT is not displaying in TOAD, only for the select statements i can get the Autotrace.I am not sure i am looking the correct one. I will update you on this .

    thanks
    mallma

    Hi Mallma

    If you are getting that error it means direct path insert is indeed actually working :-D

    regards
    Hrishy

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