-
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
-
Hi Hrishy,
I'll do that.
Thanks
mallma
-
You can find my blog entry on tracing here
Jeff Hunter
-
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
-
-
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.
-
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
-
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 02:10 AM.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|