DBLINKS and Buffer Cache

[dbtoo2001]

I've searched on multiple sites, documentation, metalink, google... But have not come up with anything to resolve an issue with a production database.

We use a third party application, which was written such that it uses dblinks to access tables between schemas. They say it was written that way so that it could be implemeneted with multiple servers.
We have them in one instance, three schemas. Can't change their code.

We've tried, bumping up the buffer cache, setting sdu/tdu, loopback (which worked - but caused a log file from the application to fill with their message.) There has been no user perceived improvements in response time and the buffer cache ratio gyrates between 10 and 90% (almost the same curves as logical/physical reads, and sqlnet/dblink to and from client.)

I believe the buffer cache ratio is bad because almost every query 'thinks' it doesn't have the data locally. I can't support my theory with documented facts.

Any Ideas?

 

[SantaMufasa]

DBToo,

I feel your pain...Although I do not have a definitive answer for you, I am guessing that the data blocks are resident in your db buffer cache since where else could they be once they've been "read in" via the db link?

Also, a much more flexible strategy for your applications provider to use is for them to code with object references with are synonyms that can apply easily to objects in other schemas or to database links to object on other servers. (I have never heard of a development strategy to hardcode database links into application code.)

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[dbtoo2001]

I'm fairly certain that it's not actually hardcoded, but it is the way it was implemented. Not being applications, I can't persuade them to change it, and the applications people are very reluctant to do so, even though it was suggested.

Is there a method to create a synonym, by which the dblink could be 'overridden'?

 

[SantaMufasa]

Sure...if they always use synonyms in their applications code (which look just like table references anyway), then whatever the current synonym assignment is to, it overrides anything (and everything) else...db links or other-schema tables references. That's exactly why I'm recommending their (the applications developers') use of synonyms...the synonyms can reference anything they want and they override (by definition) any other references.

Did I deal with the question to your satisfaction, DBToo?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[dbtoo2001]

I'm probably tired, but,
they reference in their code.. select yada from table1@abc

there is a dblink called abc which points right back to the same instance on the same machine, user xyz schema.

so, what should the synonym be to make it so the abc dblink is not used, and the synonym points to the xyz user schema.

There are many things I haven't had exposure to, one of which is writing things with synonyms. I see them in the applications, but not sure how to use them to circumvent this process. (Today I learned all about log miner and sql loader. oh boy, somebody is in big trouble! they should have rtfm first. )


Thanks Mr. H!

 

[SantaMufasa]

Good comments and questions, DBToo. Here is what I suggest:

create synonym whatever for table1@abc;

Then, your code above should read:

select yada from whatever;

...and the flexibility of synonyms allows you to then say:

drop synonym whatever;
create synonym whatever for abc.table1;

...and the beauty of this method is that your application code does not need to change nor have reparsing...it just executes with the same, original code. Cool, huh.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[dbtoo2001]

What you suggest is good for starting out, writing your own code, or the way it 'should' have been done. I can't get the system to be 'faked' out with the synonyms.
Thanks anyway.

 

[SantaMufasa]

DBToo,

This only works if 1) you have access to the source code of the application and 2) you go through the code and replace the dblinked references with the synonyms.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[dbtoo2001]

Yes, that is what I determined. The code is generated out of the third party tool. Thanks Dave of Sandy, Utah, USA!