Does anyone know about the book "PL/SQL Annotated Archieves by Looney"...
I wanted to buy a book for dba scripts we do have some here but onestly it is very hard for me to know what the values(results) mean. What the results should look like...
ex: Results of this query...
select initcap(namespace) Library_Name,
reloads Reloads, invalidations Invalid
Is there any other book or site where I can read what these mean ( not just this script there are lots of them, I am trying to understand).
Does anyone know a good book on Oracle Networking ?
Oracle Class notes is best if you could get it. If not there is OSBORNE publishers book on Networking. That was on SQL*Net. Not sure they updates it on Net8.
Go Kevin Loney Site and throw Question to his e-mail or publishers they will get back to you on the terminology he used in the book if he didn't mentioned anywhere in the book and its hard to interpret for DBA's in the field.
They Have to... Then only it makes sense for what he has written...
Oracle has a lot of dynamic performance views, and the example you told is just one of them (it's impossible for your book to discuss'em all). The names after the columns names are just aliases, the fields are best explained in Oracle documentation:
That link refers to Oracle 8.1.6, but I think there's no difference. You can also download all of the documentation in PDF format. It's much easier to search for specific information.
Thanks you, sreddy and Adriano
I should have been more specific, I am not talking about the alieses but the actual fields. Also my main problem(question) is how do I know what returned by these queries is correct, or bad and needs any database changes.
Here is a example with the results from my test database.
(this is not the only query, I was trying to know many others that we use here).
SQLWKS> select initcap(namespace) Library_Name,
2> gets Gets,
3> gethitratio Get_Hit_Ratio,
4> pins Pins,
5> pinhitratio Pin_Hit_Ratio,
6> reloads Reloads, invalidations Invalid
7> from v$librarycache
LIBRARY_NAME GETS GET_HIT_RA PINS PIN_HIT_RA RELOADS INVALID
--------------- ---------- ---------- ---------- ---------- ---------- ----------
Sql Area 3665223 .860848849 9395870 .880328378 124178 4872
Table/Procedure 1662037 .976147342 2750160 .93404711 76232 0
Body 1992 .858433735 2272 .685739437 432 0
Trigger 192 .671875 192 .453125 19 0
Index 3179 .000629129 3180 .000628931 0 0
Cluster 30177 .997912317 40014 .996751137 8 0
Object 0 1 0 1 0 0
Pipe 0 1 0 1 0 0
8 rows selected.
In this example what should be the value of GETHITRATIO ?
If it is wrong what do I need to change... this is a kind of information I wanted to read.
so that I know
Well, I never measured performance before using V$LIBRARY_CACHE, but just read about that view and I will give it a try:
- GETHITSRATIO and PINHITSRATIO gives information about the number of times an information (a lock or a pin) was retrieved from the library cache and the number of times it was requested. Since Oracle searches recent used data/sql from SGA before accessing database/recompiling to improve performance, it is desirable to have a higher rate (near 1, or 100%) here.
- RELOADS gives the number of reloads of an object from disk because another pin was performed since creation of the object handle. It's desirable to have a lower number here (in other words, minimum access to disk)
- INVALID gives the number of times an object was marked invalid because a dependent object was modified. I think it's a good idea to minimize this parameter too.
Analyzing your results, your database seems ok. You have a high number of reloads, but it's difficult to predict why, it only suggests that your data is being constantly updated (maybe the triggers?)
The ratio for the indexes is low, but I don't understand why. Maybe because the same is not used often and is not always available in memory. Or, indexes access direct datafiles instead of memory.
It seemed quite intuitive, I'm not that sure. If someone could give more objective information, we'd all be pleased. That stuff gave me a doubt, too... How EXACTLY works access to SGA before retrieve information from database? It depends on the object type? Is there any preference for any particular kind of object? I searched Oracle Documentation, but it was vague. Any links, documents, bookmarks?
You better to read once the Oracle documentation "Oracle8i Designing and Tuning for Performance". There you will come across the terminology and columns meanings in the views etc., You feel better in interpreting results after reading the manual.
hi sonali ak
one of the best known books that i have come across for oracle networking is from exam cram oracle db a networking guide by barbara ann pascavage .i must agree that brabara has expalianed in simple english everything you wanted to know about networking and net8 alothough the book is for exam purposes i often end up using it for troubleshooting it for network .no doubt the book has received 5* on mazon reveiews.
Sonali, there are some things that you have to know and be clear to undestand the tuning issues and concepts. First find a book or some URL, that explains about the oracle architecture. This is tne most important and critical thing to undestand the tuning options and issues. Next go to the URL that adrianomp had posted on this thread and check the contents. That explains all the parameter options of init.ora to the views. You can then take a performance tuning book and then try to make some sense out of these concepts. There are a number of sites that talks in depth on performance tuning. Check those sites too. In oracle it would not help you, to performance tune, just one parameter, its a matter of striking a balance. It can only be achieved out of trial and error basis most of the times. So follow the tips and you would be better.
Life is a journey, not a destination!
Regarding Oracle Networking ,please read OCP TEST Network Administration by Comdex.If any queires please write to me at email@example.com
rohit Nirkhe,Oracle DBA,OCP
Click Here to Expand Forum to Full Width