A rather nice question was asked on OTN today. It's repeated here for easier reading

how to change the sga size and how the effects take place in 9i and 10g DB

 An early follow up remarked on various initialisation parameters, though not in my view perhaps in an entirely clear way.

In 9i you can dynamically change db_cache_size, and shared_pool_size and large_pool_size, provided you don't use db_block_buffers (which disables the feature) and you stay below sga_max_size.
However sga_max_size is not a dynamic parameter so you would need to issue
alter system set sga_max_size=<something ridiculously big> scope=spfile
and bounce the database.

On 10g you don't need to set db_cache_size etc anymore, but you need to set sga_max_size and sga_target_size

As this is a common type of question I thought a brief summary might be useful here, together with a little discussion of the history of the SGA from Oracle 7/8/8i through to 11g Release 1, if you are coming across this post in the future via a web search engine - after Oracle Database Release 12 has been made available you may find the discussion has changed again since the area of memory management in Oracle seems to be under constant evolution.

Lets start with a definition - this one is drawn straight from the 9i documentation

The System Global Area (SGA) is a shared memory region that contains data and control information for one Oracle instance. Oracle allocates the SGA when an instance starts and deallocates it when the instance shuts down. Each instance has its own SGA. Users currently connected to an Oracle server share the data in the SGA. [text ommitted]

The information stored in the SGA is divided into several types of memory structures, including the database buffers, redo log buffer, and the shared pool

 The important points to note are that the SGA is instance specific, shared across users and that it includes both data and control information. Notably it includes the instances cache of database blocks (buffer cache), the shared pool (which in turn contains shared SQL parse and execution trees and so on ). Sizing the SGA appropriately therefore is a basic system wide tuning activity since the memory you allocate here will directly affect the caches available for both data and program execution information.

Oracle's instance wide configuration is largely controlled by an initialisation parameter file - either a text file or a binary server side file (from 9i) called an spfile. This file contains as the name I have chosen here rather indicates initialisation parameters and their values for the instance. Any unlisted values are either calculated from parameters that are listed or else take their default values. Oracle reads the file at initialisation and sets up the instance accordingly.

First the history. In older versions of Oracle the size of both the SGA and it's constituent parts was fixed. Sizing the SGA was a simple matter of setting the relevant initialisation parameters. These were:

• db_block_buffers - the number of data blocks that could be cached in memory by the instance
• shared_pool_size - the size of the shared pool
• large_pool_size - the size of the large pool (not in 7)
• log_buffer - the size of the redo log buffer

 All of these parameters are fixed and the SGA could therefore only be changed by an instance restart.

This picture changed rather dramatically with the release of Oracle 9i, both the number and nature of the various SGA components changed significantly with this release assuming that you chose to use the new features. Firstly 9i allowed multiple block sizes within a single database, and therefore multiple database buffer caches were introduced. These were all controlled by the initialisation parameters

• db_cache_size
• db_nK_cache_size

Where the first listed parameter controlled the size of the default cache and the nK parameters controlled the size of the caches for non standard database blocks. These parameters were measured in size, rather than blocks. In addition Oracle introduced a new fixed parameter SGA_MAX_SIZE which specified the maximum amount of shared memory that Oracle could use for the SGA in the instance, making this change allowed  Oracle to make (some of) the existing parameters dynamic, so you could dynamically change the composition of the SGA always providing that in total the amount of memory requested did not exceed the SGA_MAX_SIZE limit.

In Oracle 10g things progressed still further. With this release Oracle added yet another new parameter SGA_TARGET and if this was set then the meaning of the 9i parameters changed, specifically if SGA_TARGET was set then the 9i parameters specified a minimum amount of memory to use for that component. In this release the idea of Oracle deciding memory allocations was introduced, the DBA told Oracle how much memory was available for the SGA as a whole (SGA_TARGET)  and then Oracle would allocate memory to the SGA components based on Oracle's assessment of the need. In this release a sensible strategy often looked like

1. Set SGA_TARGET to specify how much memory is available for Oracle to manage the SGA
2. Set the XXX_CACHE and XXX_POOL_SIZE parameters to ensure a minimum allocation for each component in case Oracle underallocates.

 In addition I often set SGA_MAX_SIZE to ensure that I had a bit of headroom.

In Oracle 11 there has been still further movement towards taking memory management out of the hands of the DBA with the introduction of the MEMORY_TARGET and MEMORY_MAX_TARGET parameters. If these two parameters are set then Oracle can manage both the SGA and the PGA automatically reallocating components as it sees appropriate between the SGA and the available PGA for all server processes. I haven't had real production experience with 11 yet but it seems likely that a sensible strategy for 11 will look something like

1. Set MEMORY_MAX_TARGET to tell Oracle how much memory there will be for it to use
2. Set MEMORY_TARGET to tell Oracle how much memory to use for now for automatic memory management
3. Set the XXX_CACHE and XXX_POOL_SIZE parameters to ensure a minimum allocation for each component in case Oracle underallocates.

A common question that comes up on various forums from time to time is the vexed question of how to set credentials for enterprise manager to perform the various os tasks that it is able to on Windows hosts. The root cause of this is almost certainly the fact that the error message that is fed back when EM fails to authenticate correctly to the OS is

Invalid username or password

When more often it should really read

Insufficient privileges.

There are 4 system privileges that you need to give to the os account that you use for EM authentication to the O/S these are

• Logon as a batch job
• Act as part of the operating system
• Adjust memory quotas for a process
• Replace a process level token

On Windows 2000 machines the third is named Increase memory quotas. In addition it seems sensible to me to create an account specifically for this purpose - either local to the server or a domain account - and grant these rights to the account, make it a member of the ORA_DBA group if necessary and revoke the logon interactively privilege from the user.

You can adjust these settings as an administrator using the group policy editor - or persuade your sysadmins to create a domain wide policy along these lines using the User Rights Assignment tree under Windows Settings. The group policy editor can be fired up by choosing Start |run gpedit.msc.

Welcome to Log Buffer #74 and a big thanks to Pythian in general and Dave Edwards in particular for the opportunity to participate in this weekly review of the DBA blogging world.

Oracle

I'm primarily an Oracle guy and this week has been pretty busy with a lot of coverage of the UKOUG conference in Birmingham so that's where I'm going to start. John Scott of Apex fame has been covering the conference on a daily basis, starting here . Those of you who don't have UKOUG membership and so can't get at the content can get a sneak preview of the sort of thing presented there in Jonathan Lewis' its the data paper also presented at conference. Meanwhile an entertaining and highly useful insight into the life of a conference speaker involving teaspoons, technology, lack of sleep and empty rooms is Marco Gralike's description of his day over on the AMIS technology blog

Away from the conference decipher infosys have been covering Oracle New Features in 11g - the latest on invisible indexes is here, and perhaps the most important news for followers of the rdbms technology this week is the news that the guys that code the Cost Based Optimiser have started a blog.

SQLServer

Over in the world of my second RDBMS SQL*Server SSI Bits and Bobs are pondering the best way to implement logging in your application - you may not agree, but it's a great topic for dbas and developers to consider. Andy Warren has also been pondering he wants to kill the BEGIN END construct. Mark Seeman meanwhile is demonstrating an automated script installer over on the MSDN blogs. Euan Garden is also in reflective mode wondering whether the absence of a dependency on IIS in sql 2008 reporting services really matters

Postgres

Postgres has it's own news round-up and you can find David Fetter's PostGres Weekly News right here. Depez is sharing some interesting performance metric on shared buffer usage right here

DB2 and mySQL

In the db2 world Scott Hayes over on DB2 Mag is drilling into io performance. Mark Robson has a rather important reminder about mySQL dumps, namely that they aren't text files and they aren't editable. Database administration often seems like magic, but the mySQL Data Charmer has news of a free seminar that will help you demystify the process of lua scripting, and Hakan has news of Falcon previews for those who want to get up to speed with the upcoming release

General

Ronald over at the mySQL Technical Notes and Articles blog asks a question of broad general interest to DBAs everywhere,

What is the optimal OS partition layout for a database server? I’ve seen so many different configurations for OS partitions of recent time, none to my satisfaction.

There's a good discussion going on over at the blog, but as an Oracle guy I'd refer readers of all to the Oracle contribution to exactly this issue from 1991 - the OFA Paper

Meanwhile in a move that I'm sure all database professionals will appreciate Brian Kelly points us to an upcoming tribute to Jim Gray

Finally and this should really be read in general terms rather than in personality terms a potentially rather nasty and embarassing instance of inappropriately obtained material being presented with due credit is documented here , especially note the comments. Marco Gralike has a really nice example of the normal and correct way to do this sort of thing.

Day 1 for me continued with a great session by Riyaj Shamsudeen on analytical SQL. This was exactly my sort of presentation - packed with examples, demonstrations and proof of what he was saying. In particular Riyaj did a great job on comparing the performance of old style queries that one could use to answer analytical questions and the newer syntax. Of particular benefit is the fact that in general if one needs to add another analytical metric then there is pretty much no further io cost to be incurred.

Next up was a session chairing duty for Phil Marshal's presentation on Index Compression, This was a nice straightforward and clear introduction to the subject, again evidence based. Two particular highlights for me were, the fact that a compressed index can end up larger than an uncompressed index - especially if the cardinality of the base table is high - and that most tests that demonstrate the space benefits of index compression overstate the case because they compare an index before being compressed with the same index uncompressed - however since this sort of test builds a compressed index by a rebuild operation the correct comparitor would be with a freshly rebuilt index (no compression).

I ended my conference day with Tom Kyte again talking - and this time demoing - 11g new features. First up Tom talked about tablespace level encryption in 11g, this feature overcomes two disadvantages of column encryption in earlier versions. These were the fact that you can't do an index range scan against an encrypted column (since adjacent data values when encrypted don't get stored next to each other so there is no range to scan). Perhaps rather more importantly referential integrity doesn't work because the encrypted value in a pk will not the the same as that same value encrypted in the fk column. In addition Tom neatly demonstrated the possibility of data theft - say from backup media - by the simple expedient of running strings against a datafile - complex hacking at its best!  Tom also covered virtual columns - giving you for example the ability to create a foreign key on a function, improvements to datapump import/export including two neat additions that make scripting and using a lot easier - the ability to reuse dumpfiles and the ability to compress the dump file - especially important if you are in the habit of transferring the dump either directly or indirectly - via an intermediate file - across a network link. Finally we had that rare opportunity the experience of Tom looking entirely stumped. The last feature Tom was demonstrating was the automatic creation of interval partitions on the arrival of a new row. Tom's code looked like this

create table t(ts timestamp,audit_text varchar2(10));

insert into t values ('02-Dec-2007','xx');

Unfortunately for Tom the partition created was for the year 2020 not the expected result. Fortunately, as I make this class of mistake frequently enough to recognise it was easy enough to show that Tom was relying on a default format for the timestamp value rather than using a to_timestamp function. Failing to do this meant that Oracle was interpreting the timestamp supplied as 7am on the morning of December 2nd 2020.  I have to say that Tom handled this surprise fantastically well - both generating laughs and promising to post the full explanation - or the bug number - on his blog.

 The evening was mostly spent at the Blogger's dinner, being introduced to Russian beer by Alex Gorbachev who impressively managed to blog about the event at 2am the morning before a presentation.

As a number of others have mentioned UKOUG 2007 is in full swing at the ICC in Birmingham.  I arrived last night and after the usual night before catch up with old friends and a surprisngly nice burger courtesy of all-bar-one it was off to bed and then this morning I broke the habit of a lifetime and sat in on the keynotes. - well the second keynote. This was Tom Kyte who was using the theme of unconventional innovation to talk about the history of innovation in the Oracle Database and the latest innovations in 11g. It has also to be the only time I have ever seen Tom present without sqlplus.

The first part of the keynote made me feel pretty old really. Tom asked the entire hall to stand and then to sit down when he called out the release at which they started working with Oracle. From my vantage point at the back it was pretty clear to see that there have been waves of fresh adopters/users both of 9i and 8i - the vast majority of the hall however went back to 7.3.4. We did have a smattering of Oracle 5 and 4 users withus and even one delegate who worked on 3. I had to sit down at 6 (which was released nearly 20 years ago in 1988!). The majority of the presentation though was a look at the new features in 11g. Highlights for me included :

• truly transparent access to the Oracle OLAP engine in the database via the query rewrite mechanism
• data mining access again through sql
• the data recovery advisor designed to answer the three core questions you'll encounter in a recovery scenario:
  Can I fix it?
  How do I fix it?
  How long will it take?

Incidentally Tom asked how many people had done a recovery in the last 30 days - maybe I misinterpreted the question but I was surprised at how few people put their hands up - we do test recoveries - or at least database duplications from the backup media - at least monthly. 

Next off I put my E-Business suite hat on and went to a session on devising a strategy for an upgrade to R12. I have to say that I was rather disappointed with this since approximately half the presentation showed off improvements in R12, and the remaining half essentially suggested that the drivers behind an upgrade strategy were much as they had always been. There was an interesting snippet though suggesting that in 2003 - 4 years after the release of 11i 60% of Oracle's customers were still on 10.7. Given the message that applications support will continue up to and beyond the fusion time frame and the suggestion by Larry Ellison that fusion apps may arrive in 2008 I was left a little bit wondering if R12 will be the release that never was for many people.

Finally before lunch it was off to hear the excellent Toon Koppelaars talking about the theory and practice of database constraint design. Starting with set theory and using just the emp and dept tables Toon took us through the implementation of database integrity constraints from theory to practice. Sadly we had to cut the session short, but an excellent discussion was held around set theory, logic, requirements specifications, mid-tier application design and so on.