I am not sure how to do it in toad.never used toad much .
How about requesting your DBA's help
I'll do that.
You can find my blog entry on tracing here
You might try changing the first SELECT in your query to use the DRIVING_SITE hint. So, it should look something like:
If you are aliasing remotetablename, then you will need to use the alias instead of remotetablename in the hint.
SELECT /*+ DRIVING_SITE (remotetablename) */
as long as we're at it, did you try changing the init.ora parameters:
Last edited by marist89; 07-05-2005 at 11:52 AM.
On the subject of direct path INSERT,
Is a direct-path INSERT actually taking place at all? A couple of quick ways to check:
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.
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?
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 = ...".
Are you talking about the NOLOGGING in the create table command?
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.
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?
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 .
Last edited by mallma; 07-06-2005 at 02:10 AM.
Originally Posted by mallma
If you are getting that error it means direct path insert is indeed actually working :-D
Click Here to Expand Forum to Full Width