?
Solved

Difference between queries.

Posted on 2011-10-31
17
Medium Priority
?
280 Views
Last Modified: 2012-05-12
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
Comment
Question by:joe_echavarria
  • 6
  • 6
  • 3
15 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37059014
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
 
LVL 1

Author Comment

by:joe_echavarria
ID: 37059276
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37059394
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 1

Author Comment

by:joe_echavarria
ID: 37060517
That is what i need to know, if both queries shows the same information and if not what is the difference between them.
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 37060521
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
 
LVL 11

Accepted Solution

by:
Akenathon earned 2000 total points
ID: 37061402
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37062529
>>Try an AWR/ADDM report, it will give you much more indicators.

Remember that ADDM requires a separate license.
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 37062997
How can i confirm if my database has  AWR/ADDM licensed ?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37063017
Contact your DBA, Software License people or Oracle Support.
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 37063094
There is no command that can be use to find that information  ?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37063158
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
 
LVL 1

Author Comment

by:joe_echavarria
ID: 37063464
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37063524
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
 
LVL 11

Expert Comment

by:Akenathon
ID: 37064376
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
 
LVL 11

Expert Comment

by:Akenathon
ID: 37071350
@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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses
Course of the Month13 days, 13 hours left to enroll

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question