Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Martha.

Asked: August 25, 2004 - 8:42 am UTC

Last updated: September 10, 2004 - 1:32 pm UTC

Version: 9.2.0.5.0

Viewed 1000+ times

You Asked

Hi Tom,

I am working with Oracle 9i database (2 terabyte). I have DB_BLOCK_SIZE = 16KB,DB_CACHE_SIZE = 2.5 GB,DB_BLOCK_BUFFER = DB_CACHE_SIZE % DB_BLOCK_SIZE = 153600. If I assume to have tables with Avg. row length of 200 bytes and Rows that can fit into a DB BLOCK = DB_BLOCK_SIZE % 200 = 81.92 then I can say that the rows that would be cached is 153600 * 82 = 12595200 rows that could be pinned. What do you recommend to cache? How much memory should I have to allow sorts? How about the large_pool_size? How about shared_pool_size? Now is set to 2 GB and sga_max_size is 6 GB. Is that too high?
We are using dedicated server mode.

Thanks in advance for your recommendations and suggestions.

and Tom said...



we cache blocks, we cache multiple versions of blocks (eg: same block can be in the buffer cache many times as of different points in time), we cache rollback, we cache dictionary data, but -- we do not cache rows -- it is not even useful to think of it like that at all. I can find no reason to in any case.


It is impossible to say 'this is how your memory should be allocated' without intimate knowledge of you, your system and your data.


Data warehouse? different than OLTP... different than mixed workload.... 100 users? or 10,000 users? lots of unique sql and plsql? or few sql statments and no plsql. sort big time? or just a couple of rows.


So, do you have an experienced DBA on site you can consult with? One who can look at these factors and come up with a best first guess -- and then show you how to use tools like enterprise manager or statspack to monitor how well you've done?

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

init.ora parms

jc, August 25, 2004 - 12:37 pm UTC

Should the formula for "db block = db_block_size * 200 = 81.92" have been "db_block_buffers = db_block_size * 200 = 81.92"?

I have a similar situation - total DB size is about 400 GB, but will grow to 1.4 TB in the future. There are batch and online processes and users running throughout the work week, and there are reporting processes that will be running in the near future. It's difficult to balance the reqs for the separate types of processing. By the way, the system isn't in production, yet.

Right now, I'm using a best-guess approach to tuning the PGA/SGA. I plan to use the WORKAREA_SIZE_PLOICY = AUTO and a static PGA_AGGREGATE_SIZE of about 4.3 GB (out of 16 GB total RAM for server). The SGA is less straight-forward, though, and am unsure of sizings for the db_cache_buffer, large_pool_size, etc, parms. After conducting testing in the future prod environment, should I take my best shot at those settings, and use the 9iR2 tools/advice views to tune those settings following deployment to production? I appreciate any time you can devote to this. Thank you.

Regards.

Tom Kyte
August 25, 2004 - 1:32 pm UTC

that 'formula' is a meaningless random number. ignore it, "average rows per block in my entire database" -- why? whatfor?

but it was x%y, not x*y. x%y is generally "integer division" (like trunc(x/y))


statspack will help you get the PGA, it'll tell you what it should be in fact.

statspack will help you with the rest as well -- the advisors in there will tell you "if you added n% to the buffer cache, based on your observed workload the physical IO would move by m%"

and so on. (oem will show you the same information in a pretty to look at graph)

questions

abc, September 10, 2004 - 1:14 am UTC

Hi Tom ,

1. whats the maximum size of SGA?
2. if db_block_size=8kb and i have to create 16kb tablespace then how to set and what parameter ?
3.for ASM what init.ora parameter we set?
4.whats level 0 backup mens in rman?
5.direct=y in exp means what?

thnaks



Tom Kyte
September 10, 2004 - 8:17 am UTC

give the documentation a try, you might find it enjoyable to discover some of this on your own!

(really -- honest)

How does Oracle Understand ...dwh or oltp ?

Pravesh Karthik fro chennai, September 10, 2004 - 1:28 pm UTC

Tom,

Data warehouse? different than OLTP... different than mixed workload.... - i understood the line. but my question is -- How does Oracle Understand that my application is set for dwh or oltp ?

We run statspack in 20-30 minute window, 9ir2. Follow the advisories. 2 times a day at bussiness hours.

Thanks,
Pravesh Karthik


Tom Kyte
September 10, 2004 - 1:32 pm UTC

it doesn't really matter, the advisories work the same for either.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.