Interpreting Explain Plans

Here are a couple of links to notes on interpreting Explain Plans :
http://www.akadia.com/services/ora_interpreting_explain_plan.html
and
http://jonathanlewis.wordpress.com/2007/02/01/execution-order

Creating Database Links

Normallly, the DBA can create objects (Tables/Views etc) in other schemas without knowing the passwords for those other schemas. However, he cannot do so for Database Links. A "CREATE DATABASE LINK SCOTT.DBLINK AS ..." command actually still creates a DBLink with the name "SCOTT.DBLINK" in the DBA's Schema instead of in SCOTT's schema.

What I do is is to use EXECUTE IMMEDIATE as in this code :

grant create database link to scott;

create or replace procedure scott.tmpcrtdblink

as

begin

execute immediate 'create database link remote_db_app connect to remote_shadow_user identified by remote_password using ''tns_connect_string'' ' ;

end;

/

execute scott.tmpcrtdblink;

drop procedure scott.tmpcrtdblink;

revoke create database link from scott;

select db_link, username, host from dba_db_links where owner = 'SCOTT';

The remote_shadow_user is NOT the owner of the base tables in the remote_db but another "shadow" account with SELECT only privileges on the base tables.
Similarly, if other users (eg TOM) locally need to access the remote database , they have their own dblinks --- particularly so when SCOTT and TOM are accessing different tables and/or with different privileges in remote_db (thus, using different "shadow" accounts in the remote_db as well).
Since I really don't have database accounts for end-users but only accounts for applications / application schemas, dblinks are required only between applications and so it becomes easy to come up with meaningful names for the "shadow" account {use a name which representswhich application in which database will use this account} and the dblink name {use a name which represents which database and whichapplication/schema/shadow the dblink connects to}.

Buffer Cache Hit Ratio GOOD or BAD ?

Recently, after a major upgrade of Database and Application version, and also to a new server, we had a Cache Hit Ratio of 99.62%. GOOD, you say ?
Very bad it was. That 99.62% also meant extremely high latch waits ("cache buffer chains") and very high Logical Reads.
With tuning, we brought Latch Waits down from 79.6% to 27.5% of the total Response Time, Logical Reads per DataBlock Changed down from 223 to 87 and an overall 74% reduction in Response Time.
BUT, our Hit Ratio [even after increasing the Buffer Cache by 50%] was down to 97.45%.
I was happy.
Very fortunately, we had Oracle Consultants who knew better than to look at the Cache Hit Ratio -- to focus on Logical Reads. Bring in people with the right attitude and setup the right KPIs and you will focus on the correct issues.

Updated 23-Feb : So, what was the problem ? The "cache buffer chains" latch waits was the indicator -- hot blocks, very high logical reads (reading the same blocks repeatedly) giving us a false Hit Ratio.

Updated 05-Mar : Latch waits are now down to less than 4% of total Response Time. Further tuning will be undertaken before we start looking at Physical Reads.