• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

Difference between queries.

Hello,

  I would like to know what does this two difference queries do when i run it in the database.
 
  Because what i need is to know if the database is accessing data from memory instead of disk.

  Please explain me what each script does, and if which one of the scripts do what i need.

a) select round(((sum(decode(name,'db block gets',value))
       + sum(decode(name,'consistent gets',value)))
       - sum(decode(name,'physical reads',value))) /
       (sum(decode(name,'db block gets',value))
       + sum(decode(name,'consistent gets',value))) * 100) "Buffer Cache Hit Ratio"
FROM V$SYSSTAT;



b ) select sum(gets) "Data Dict. Gets",
sum(getmisses) "Data Dict. Cache Misses",
round((1-(sum(getmisses)/sum(gets)))*100) "DATA DICT CACHE HIT RATIO",
round(sum(getmisses)*100/sum(gets)) "% MISSED"
FROM V$ROWCACHE;
0
joe_echavarria
Asked:
joe_echavarria
  • 6
  • 6
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
The docs have the information on the individual values like "db block gets".
http://download.oracle.com/docs/cd/E11882_01/server.112/e25513/stats002.htm#i375475


I assume you found these scripts online:  What did the source say they did?

I would look for metrics that mention cache. Here is one that you don't have mentioned:  consistent gets from cache
0
 
joe_echavarriaDatabase AdministratorAuthor Commented:
No, i did not find it online.  

The tech support of my application wanted to check if the database is accessing more on disk that what it is on memory, so first he requested to run the first script and we had 86 for "Buffer Cache Hit Ratio",  then he requested to analyzed all the tables and then run the second query.
I wanted to know the difference between the queries , whey he might change it the second one.
0
 
slightwv (䄆 Netminder) Commented:
I'm not familiar with those EXACT select statements.  There are probably hunderds of different 'tuning' SQL scripts out there.

I bet if you Google for parts of those SQL statements you'll even find those exact select statements.

My gut feeling is those are probably telling you about the same thing just using two different V$ views.

One uses V$SYSSTAT the other V$ROWCACHE.

These views are probably looking at the same thing.  The docs tell you what those views are for:

http://download.oracle.com/docs/cd/E11882_01/server.112/e25513/dynviews_3093.htm#REFRN30272

http://download.oracle.com/docs/cd/E11882_01/server.112/e25513/dynviews_2152.htm#REFRN30211

>>database is accessing more on disk that what it is on memory

Now for the flaw in that request:  Without ongoing and constant measurements and tuning, a one-time execution of these will not really tell you anything.  The v$ views are over time since instance startup.

If you get a lot of block in memory and a high cache hit ratio it might be 'dumb luck' and not 'normal'.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
joe_echavarriaDatabase AdministratorAuthor Commented:
That is what i need to know, if both queries shows the same information and if not what is the difference between them.
0
 
joe_echavarriaDatabase AdministratorAuthor Commented:
When you run them they show different info.   I guess then that the question will be what is the differene between "Buffer Cache Hit Ratio"                                              and  "Data Dict. Gets",   "Data Dict. Cache misses",           " DATA DICT CACHE HIT RATIO", and "% Missed".
Does anyone can help me ?



0
 
AkenathonCommented:
The queries are completely unrelated. They look at two separate areas of the SGA: DB buffer cache and dictionary cache respectively. The only similarity is that they are both caches, and that both queries return the average hit ratio the caches had since the instance last started.

If the point was finding how many USER reads are coming from memory vs. disk, the first one is the one to use. The second one (dictionary cache hit %) is only useful for SYSTEM reads (i.e. reads that the DBMS does transparently, either to perform an internal task, or needed to fulfill a user request in a recursive fashion).

Finally: if it's tuning what you want, you probably need to place these %s in the context of your app type (e.g. OLTP vs OLAP), and look at much more data, including the wait events. Try an AWR/ADDM report, it will give you much more indicators.
0
 
slightwv (䄆 Netminder) Commented:
>>Try an AWR/ADDM report, it will give you much more indicators.

Remember that ADDM requires a separate license.
0
 
joe_echavarriaDatabase AdministratorAuthor Commented:
How can i confirm if my database has  AWR/ADDM licensed ?
0
 
slightwv (䄆 Netminder) Commented:
Contact your DBA, Software License people or Oracle Support.
0
 
joe_echavarriaDatabase AdministratorAuthor Commented:
There is no command that can be use to find that information  ?
0
 
slightwv (䄆 Netminder) Commented:
No.

Like many Oracle options, they are 'available' and can be turned on and off.  you should only turn them on once you have purchased the appropriate licenses.
0
 
joe_echavarriaDatabase AdministratorAuthor Commented:
So there is no way to find out which licenses has been purchase in the database by doing some query , or some other command ?
0
 
slightwv (䄆 Netminder) Commented:
You can keep asking but the answer will be the same:  No.

Oracle does not take license keys to 'activate' or 'deactivate' pieces of the software.

If you have your CSI for support I believe you can log into to Oracle Support and see what licenses are attached to that CSI but your company might have more than 1 CSI.
0
 
AkenathonCommented:
Agreed, you can't query the database to find out whether you have made a payment for it or not.

One way or the other, I think your original question has been answered. The concepts manual goes into more detail about those (and other) parts of the SGA.
0
 
AkenathonCommented:
@slightwv: I don't remember the exact wording, but the intention was actually the opposite: to warn of the dangers of using unlicensed features and hoping no trace will be left... because the violation IS recorded, even if he does it once. Just SELECTing from v$active_session_history requires a licence, he probably didn't know that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 6
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now