Search My Oracle Blog

Custom Search

23 February, 2007

Interpreting Explain Plans

Here are a couple of links to notes on interpreting Explain Plans :

14 February, 2007

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



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



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}.

13 February, 2007

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.

Aggregated by

Aggregated by
This blog is being aggregated by

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016