Home » SQL & PL/SQL » SQL & PL/SQL » Database Links
Database Links [message #292630] Wed, 09 January 2008 08:16 Go to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
select 1 from dual@abc.world;
Could you please tell me is there anything wrong in testing whether a database link is setup correctly by executing the above query. I would like to know whether I have missed anything really obvious.

Database from where the query is executed is Oracle 10g Release 10.2.0. Target database version is Oracle 9i Release 9.2.0

Regards

Raj

P.S : Removed extra lines

[Updated on: Wed, 09 January 2008 08:17]

Report message to a moderator

Re: Database Links [message #292639 is a reply to message #292630] Wed, 09 January 2008 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is ok.

Regards
Michel
Re: Database Links [message #292644 is a reply to message #292639] Wed, 09 January 2008 08:59 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Michael,

Thanks for your reply.

O.k now comes the next question. I ran the query using sqlplusw on my local machine and it hanged (no response). So, I killed the process (only the client side) and I don't have access to kill my session on the server side it was still active. (To be honest, i realised it very late that the process is still active on the server side). When I got told about it, I checked the IO Usage and I was shocked. Can you believe it the mentioned query produced 300,000 consistent gets in a period of 1 Month. Yes I mean 300,000 consistent gets and it is contributing to 78% of the CPU Usage in the database (all of a sudden today). Is there any explanation for that ? I have been advised not to use this query because it is killing the database.

Having said that I would also like to know is there any other way of validating whether the DB Link is valid ? Could you please let me know.

Regards

Raj
Re: Database Links [message #292646 is a reply to message #292644] Wed, 09 January 2008 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand whatr you say.
" it the mentioned query produced 300,000 consistent gets in a period of 1 Month. Yes I mean 300,000 consistent gets and it is contributing to 78% of the CPU Usage in the database", so if 300,000 LIO in a month is 78% CPU of the database CPU then your database is really doing nothing.
By the way, I don't know what it is doing and I'm not sure this is the session that execute the first query. Didn't you restart since a month?

There is no other way to check a database link than to do a query through it.

Regards
Michel

[Updated on: Wed, 09 January 2008 09:06]

Report message to a moderator

Re: Database Links [message #292651 is a reply to message #292646] Wed, 09 January 2008 09:12 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
No, what I mean is this query (select from dual) has been sitting in the database and running/executing or trying to run/execute in the database for the last one month. Database has not been bounced in the last month. So over a period of time it has performed 300,000 consistent gets. All I would like to know how the mentioned query performed 300,000 consistent gets. This is what is puzzling me.

Does this make sense now.

Regards

Raj
Re: Database Links [message #292652 is a reply to message #292651] Wed, 09 January 2008 09:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe each time it tries to send you the result it read again in the cache.
Activate a trace on the process then you will know.

Regards
Michel
Re: Database Links [message #292657 is a reply to message #292652] Wed, 09 January 2008 09:30 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Michael,

Thanks for the info. Database has been bounced so I cannot do it. thanks anyway.

Regards

Raj
Previous Topic: Dynamically Execute a Procedure
Next Topic: List the flag change
Goto Forum:
  


Current Time: Wed Dec 07 18:50:20 CST 2016

Total time taken to generate the page: 0.08606 seconds