Blog Aggregator - Amardeep Sidhu

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.

Today I have received a copy of
 Swiss Oracle Usergroup’s Newsletter 1/2008.
It contains my article on some 11g New Features related to DDL.
I had posted about them a while ago in my 11g Top New Features Series.
You can read about DDL LOGGING and DDL LOCKING in 11g in my 11g posts.
Thank you SOUG!
=;-)
[…]

Have you ever witnessed a piece of technology so grand that you knew it would change the world eventually? When digital cameras hit the mass market, I knew your traditional 35mm camera was dead. Sure, the quality of film photography was better in the beginning, and I still think film takes better pictures. But for everyday snapshots, you just can’t beat that little digital camera.

During

I expressed this summer my delight over the 11g fast resync option for extended (geographical) RAC clusters, for which the mirrored failure groups have to be on different sites, like having one of the failure group on array 1 on site 1 and the mirrored copy on array 2 on site 2. The major 10g […]

Having attended the two conferences so close together makes it easy for me to compare them. There are obvious differences. San Francisco in November is warmer, sunnier and all together more attractive than Birmingham in December. Open World is much much larger. But the UKOUG felt more tiring, at least to me. This is because the UKOUG packs a lot more into each day.

Open World is distributed across several sites. In order to give delegates sufficinet time to get from a session in the Westin St Francis to Moscone South there is a half hour gap between sessions. There are no sessions whilst the key notes are on. There are no sessions during lunch hour. The UKOUG schedules sessions with only ten minutes between them. The streams are staggered, so that there were always sessions during lunch. This obviously provides value for the delegates by offering a vast number of sessions to attend. But it also makes us feel guilty about skipping a session in order to go round the exhibition hall.

Another difference is the networking opportunities. Oracle have started putting a lot more effort into providing portals for delegates to get in contact with each other and places where people can meet and converse. This is easier because Moscone Center has some big spaces which can be used for such purposes.

Both conferences are valuable. I learned a lot from attending both. But Open World was much more relaxed. I think the fact that I have just blogged about UKOUG Wednesday more than two weeks after the end of the conferences says it all.

Managing RMAN backups with FRA (Flashback Recovery Area) is definitively not "out of the box". Consider the following situation: the RMAN backup are performed in the Flashback Recovery Area in a first step (before going to tape). In a second step, the backup are saved on tape from the FRA with the following policies : DAILY, WEEKLY, MONTHLY. Each policy has its own retention (5 days, 5 weeks, 13 months).

One of the numerous hidden FRA “feature”. However the benefit of the FRA in a Dataguard environment largely covers the several drawbacks.

Well not quite, but I was awake at 4:30am. So I ran through my presentation a couple of times and made some final tweaks to the slides. I didn’t have any code demos this year, so there was no chance of me breaking anything.

Every performance problem is one of two things

I almost skipped James Morle’s presentation, because I thought it would cover the same ground as Jonathan Lewis’s talk on Statspack. I’m glad I didn’t though, because it was completely different. But I’m even more frustrated that this talk was scheduled at the same time as Daniel Fink’s talk on Why tune SQL?.

James’s talk was a reassessment of the YAPP tuning method:

R = S + W

That is, response time equals service time plus wait time. Most tuning methods focus on the waits. This talk’s assertion was that most fixes actually deal with skew or latency, that is, the service time. In a nutshell, Morle’s Grand Unified Theory of Performance Tuning is: All problems are caused by skew, latency or both.

Bandwidth is not the same as latency: bandwidth is how much transport you have, latency is the wasted time associated with each operation. James demonstrated the difference between latency and bandwidth with some live action in which two volunteers transported bottles of beer across the auditorium, in a suitcase or by hand. The impact of Connor McDonald’s infamous “squirrel sketch” is still rippling across the presentation pond. However, this example is easier to blog. Given two networks:

which one can transmit the most data? The answer is the WAN; the LAN’s enormous bandwidth is wasted because of the additional latency in its ping speeds. Of course, the WAN is operating more-or-less at capacity and the LAN isn’t.

Problems with bandwidth are easy to fix - they just require money and bandwidth is getting cheaper all the time. They are also easy to spot. Latency on the other hand is bounded by the laws of physics, which means it is not necessarily fixable to chucking money at it. Also, it can be hard to spot. Latency multiplication is a very common source of problems - 1ms per operation is a very short time until you do that operation a thousand times.

Common sources of latency:

• nested loop joins
• SQL*Net message to/from client
• too many commits
• “slow” I/O

Skew is also hard to spot because humans (and computers) tend to think linearly, and unconsciously split things into evenly-sized chunks.

Common sources of skew.:

• distribution of column values
• high demand for small set of resources (contention)
• response times for component SQL statements in a transaction

.
So the revised version of the YAPP formula is

R = S + W

where S = (skew * service time) and W = (skew * latency + passive wait time).

Modelling on the cheap

This presentation originally started out as some jokey test data, when the UKOUG asked some of us SIG chairs to test the conference site. But the title was sufficiently compelling for me to submit it as a real paper and, more surprisingly, for it to be accepted. I think many people found the title compelling because it focuses on a real problem: how to undertake database and application modelling when the boss won’t spring for an expensive CASE tool. I work for a consultancy; consultancies tend to put software licences in one of two categories - those for which the client pays and overhead. So I have an interest in tools and techniques which don’t require me to get a business case approved.

I talked about low tech solutions (whiteboards, Post-It notes and index cards), desktop tools (PowerPoint and Visio) and then free modelling tools (basically Schemester). There is JDeveloper but I don’t think that is really suitable for people who are developing database applications. However the rumours suggest that JDev 11g will have much more to offer on that side of things, and that means that we can hope those features will eventually be ported to SQL Developer. I feel the session went well but I shall have to wait until UKOUG publishes the evaluations.

Afterwards, somebody who introduced themselves as one of the JDev development team who worked on the original Use Case modeller said they started out modelling that tool with index cards and Post-Its. And Duncan Mills said that he didn’t think the JDev features set was any worse that Schemester. It’s a fair point: Schemester’s big advantage is that it is a mere 295KB zip to download and has a memory footprint of ~12MB of RAM. So, unlike JDev, we can run Schemester without nadgering our desktop.

I’ve been doing a lot of research lately into setting up Drupal as a Content Management System. One of Drupal’s shortcomings (by design) is the current production version doesn’t have a WYSIWYG editor. The lack of a visual editor is not a big deal since there are several editors you can just plug-in (more or less) as a module.

The first editor I tried was TinyMCE by Moxicode. I originally

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 shown in the screenshot below. The group policy editor can be fired up by choosing Start |run gpedit.msc.

Upgrading Oracle Applications is never an easy process. I like to test my upgrades both for completeness as well as performance so I can give my users a semi-accurate picture of how long things will take.

I’m doing a direct upgrade from 11.5.9.CU2 to R12 (probably CU3). I got to the point where the db objects are upgraded and received the following message:
There are now 126521 jobs remaining (