Oracle Performance Tunning...You will have interesting.

[KY.Chuang]

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.

[Steven E. Protter]

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

[malay boy]

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

[Bill Hassell]

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.

[Yogeeraj_1]

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

[BLADE_1]

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"