OraFAQ Forum: Performance Tuning » Shared Pool sizing

Home » RDBMS Server » Performance Tuning » Shared Pool sizing

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Shared Pool sizing [message #188926]

Tue, 22 August 2006 08:13

kudur_kv
Messages: 75
Registered: February 2005

Member

This is a problem I am facing on 8i - 8.1.7.4

My production database is an OLTP DB. the performance had been quite slow and users were complaining big time.

The following are the steps that I carried out.

1) resize the DB_blocks (almost doubled the existing value)
2) resize the shared pool size (again doubled it)
3) resize the log buffer (increased by around 10%)
4) resize the sort area size ( again double)

The above parameters were chosen to be changed based on the wait events from statspack reports.

I do understand that some of the changes might not exactly be according to the usual/standard practices.

Well, after the changes to the SGA were made and gather schema stats package run, the DB is doing better than before. But there is scope for improvement.

My question is, What should be the ratio of the Buffer pool to that of the Shared Pool for an OLTP DB? Is there any thumb rule as such?

Appreciate your frank opinions. Thanks in advance.

KV

Report message to a moderator

Re: Shared Pool sizing [message #189011 is a reply to message #188926]

Tue, 22 August 2006 15:05

tongucy
Messages: 8
Registered: August 2006

Junior Member

hit ratios are always wanted as high as possible but I believe it would be better to solve your customer's problems in session based tuning.

I mean if they are unhappy of a specific part of your application you take traces and analyze these, this will be point-shot for the problems.

After sql tracing you will use TKPROF which is run from the operating system prompta and the syntax is:

tkprof filename1 filename2 [waits=yes|no] [sort=option] [print=n]
[aggregate=yes|no] [insert=filename3] [sys=yes|no] [table=schema.table]
[explain=user/password] [record=filename4] [width=n]

Sample output from TKPROF is as follows:

SELECT * FROM emp, dept
WHERE emp.deptno = dept.deptno;

call count cpu elapsed disk query current rows
---- ------- ------- --------- -------- -------- ------- ------
Parse 1 0.16 0.29 3 13 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.03 0.26 2 2 4 14

Misses in library cache during parse: 1
Parsing user id: (8) SCOTT

Rows Execution Plan
------- ---------------------------------------------------

14 MERGE JOIN
4 SORT JOIN
4 TABLE ACCESS (FULL) OF 'DEPT'
14 SORT JOIN
14 TABLE ACCESS (FULL) OF 'EMP'

for more details;
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm#PFGRF01010

Report message to a moderator

Re: Shared Pool sizing [message #189031 is a reply to message #189011]

Tue, 22 August 2006 18:25

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>> hit ratios are always wanted as high as possible
HIGH hit ratio's are not always indication of a 'good' system.
Infact you can programmatically get any hitratio you want.
I beleive OP is not talking about the hit ratio.

Report message to a moderator

Re: Shared Pool sizing [message #189067 is a reply to message #188926]

Wed, 23 August 2006 01:54

tongucy
Messages: 8
Registered: August 2006

Junior Member

>> I beleive OP is not talking about the hit ratio.
I cant understand how you figure this out from this quesiton of the op; "My question is, What should be the ratio of the Buffer pool to that of the Shared Pool for an OLTP DB? Is there any thumb rule as such?"

Also what is the relation of these two again; >> HIGH hit ratio's are not always indication of a 'good' system. <-> "hit ratios are always wanted as high as possible"

>> Infact you can programmatically get any hitratio you want.
So I sugested session based tuning and sql trace.

Report message to a moderator

Re: Shared Pool sizing [message #189119 is a reply to message #189067]

Wed, 23 August 2006 04:55

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>Also what is the relation of these two again
http://www.orafaq.com/forum/t/23453/0/
Without "tuning" anything, you can get hit ratio you want.
Hit ratio based tuning is completely flawed. Sure , low/high hit ratio does indicate something. But it is not a measure of performance.
>>So I sugested session based tuning and sql trace.
I never disagreed on that
regards

Report message to a moderator

Re: Shared Pool sizing [message #189120 is a reply to message #188926]

Wed, 23 August 2006 04:57

kudur_kv
Messages: 75
Registered: February 2005

Member

Thank you for your opiniors but Apologies to say that both of you have somewhat misunderstood my question.

My question was regarding the sizes of the Buffer cache and the Shared pool and not the hit ratio figures. Mahesh is right. High hit ratio systems are not always on the best side of performance.

Let me try to re-phrase my question.

When you have a server with 8 GB ram, and 4 GB is dedicated to oracle, What should be the sizes of
1) Total SGA size
2) Buffer cache
3) Shared Pool, Sort area size
4) log buffer etc.

The ratio I was referring to was with respect to the sizes of the SGA components and not the hit ratios.

Thanks in advance! Smile

Report message to a moderator

Re: Shared Pool sizing [message #189158 is a reply to message #189120]

Wed, 23 August 2006 07:52

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

I do not have a definitive answer for this.
Mostly it depends on your need/application/usage. There is no Rule-of-thumb thingies here.
I would start less and grow as needed.
Search this forum / google /asktom.oracle.com for general discussions.
This is a very good read.
http://asktom.oracle.com/pls/ask/f?p=4950:8:16168244148449450248::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1513005503967

Log buffer: A value of 512 kb - 1mb is enough in most cases. Rarely, you need somtehing more than that.
A similar discussion.
http://www.orafaq.com/forum/m/167405/42800/?srch=log+buffer#msg_167405

Edit:
Obviously, i failed to read that you are using 8i.
Again, already well explained here.
http://asktom.oracle.com/pls/ask/f?p=4950:8:1167377577044025848::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:437619854172

[Updated on: Wed, 23 August 2006 07:57]

Report message to a moderator

Re: Shared Pool sizing [message #189202 is a reply to message #188926]

Wed, 23 August 2006 10:59

kudur_kv
Messages: 75
Registered: February 2005

Member

Thank you very much for the link on shared pool. Really good link.

Thank you again.

KV

Report message to a moderator

Previous Topic:	Udmp Traces
Next Topic:	Oracle 9.0.2.7 enterprise problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 03 03:35:33 CDT 2024