Simpler Navigation coming for Servers and Operating Systems
Coming soon: a much simpler Servers and Operating Systems section of the Community. We will combine many of the older boards, and you won't have to click through so many levels to get at the information you need. If you are looking for an older board and do not find it, check the consolidated boards, as the posts are still there.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle Performance Tunning...You will have interesting.

KY.Chuang
Advisor

Oracle Performance Tunning...You will have interesting.

Dear all,
Recently I readed a book about Oracle performance tunning.
I flow this book to gather 3 methods to test and tunning Oracle performance.

(1) calculate Data Buffer Hit Ratio.
SQL>
select round((1-(sum(decode(name, 'physical reads', value, 0))
/ (sum(decode(name, 'db block gets', value, 0))
+ sum(decode(name, 'consistent gets', value,0))))),2)
"Data Buffer Hit Ratio"
from V$SYSSTAT;
------------------------------
If Data Buffer Hit Ratio close to 1 it mean you have excellent performace.
Otherwise you must to add DB_BLOCK_BUFFERS to improve Oracle performace.


(2) calculate Library cache Hit Ratio.
SQL>
select sum(pins) "Total Pins"
,sum(reloads) "Total Reloads"
,round((1-sum(reloads)/sum(pins)),2) "Hit Ratio"
from V$librarycache;
------------------------------
If Library cache Hit Ratio close to 1 it mean you have excellent performace.
Otherwise you must to add SHARED_POOL_SIZE to improve Oracle performace.

(3)calculate Data Dictionary Hit Ratio
SQL>
select round((1-(sum(getmisses)/sum(gets))),2) "Dictionary Hit Ratio"
from V$rowcache;
------------------------------
If Data Dictionary Hit Ratio close to 1 it mean you have excellent performace.
Otherwise you must to add SHARED_POOL_SIZE to improve Oracle performace.

^^
Finally, Everybody think which this kinds of tunning is suitable ???
Otherwise you have more better suggestions, Please tell us to discuss.
Service is King
5 REPLIES
Steven E. Protter
Exalted Contributor

Re: Oracle Performance Tunning...You will have interesting.

Don't forget about OS tuning.


Performance Doc
http://www1.itrc.hp.com/service/cki/search.do?category=c0&docType=Security&docType=Patch&docType=EngineerNotes&docType=BugReports&docType=Hardware&docType=ReferenceMaterials&docType=ThirdParty&searchString=UPERFKBAN00000726&mode=id&admit=-682735245+1053636168960+28353475&searchCrit=allwords&printable=true

The document above was written by one of HP's oracle os tuning experts.

No matter what you do with the database if the OS is messed up you'll have a dog on your hands.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
malay boy
Trusted Contributor

Re: Oracle Performance Tunning...You will have interesting.

Defitenely the book was right.
But from my experince most of the problem are coming from bad sql OR bad application.

You tune your database the chances for performance increase are less compared to tune the application.

my 2 cents.

regards
mB
There are three person in my team-Me ,myself and I.
Bill Hassell
Honored Contributor

Re: Oracle Performance Tunning...You will have interesting.

Note that there are MANY discussions of Oracle performance adjustments here in the ITRC forums. The above items will indeed improve performance but if you run the 32bit version of Oracle, you'll have very big problems getting the amount of shared memory mapped into Oracle (see many forum discussions on this). If you have less than 4Gb of RAM for Oracle (32 or 64 bit versions) then the opsystem may have to page heavily and performance will be very bad.

Use the Forums' search capability to locate:

Oracle performance memory

and read the discussions.


Bill Hassell, sysadmin
Yogeeraj_1
Honored Contributor

Re: Oracle Performance Tunning...You will have interesting.

hi,

The best approach you can adopt for Oracle Performace Tuning is to use Statspack (provided by Oracle).

You should be using statspack on a constant basis.

Every morning, you should take a snapshot, every afternoon another, every evening, yet another.

Now you have a history. You can compare a statspack from today (bad performance) with last weeks at the same time (good performance) and look for major differences.

Also, people must "quantify" things. Eg: Screen 1 typically takes less then 1 second, today it is taking 60 seconds. -- Ah ha, maybe we lost an index on some of the tables surrounding screen 1, lets look at that. Are there specific components "going slow" or is the entire thing going slow.

Statspack will help you identify the top sql, the big wait events, contention points, bad performance metric (eg: the soft parse ratio is my personal favorite).

If you need any further guidance, please let us know.

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
BLADE_1
Frequent Advisor

Re: Oracle Performance Tunning...You will have interesting.

Hi,

Performance tuning in Oracle has to be a structured approach. Getting the Hit Ratios right is a step towards that ultimate goal. As said by yogi statspack is a very good utility to identify the bottlenecks and give a good insight on the database health and functioning.

Using OEM diagnostic and tuning packs is quicker and easier to use. Most of the experienced DBA's would agree that tuning expensive SQL statements and code refinement is a definitive approach. The most important Tuning related table is V$SQLAREA or you can also use sql analyze in OEM to give the same results.

Tuning is an ongoing process and OS,Network,Database all play a significant role.

Rgds
BLADE
"Always Sharp"
fortune favours the brave