1.0 Introduction
One of the first things I do whenever a new version of Oracle comes out is to check what new initialisation parameters we have been given and what old ones have been taken away. My theory is that inspecting parameters in this way is a pretty good guide to new and obsolete functionality – which is the thing we are actually worried about, of course.
There are 289 parameters listed in the 11g Enterprise Edition's V$PARAMETER view, as compared to 10g Release 2's 258. The difference is made up of 34 new parameters and 3 obsoleted ones. Having laboriously computed the differences myself, I then discovered I could just have read all about it here, but that's life!
In this article, I'm going to run through the differences and give a hint of what the changes mean to core database functionality. Because there are so many of them, I'll knock off about 7 per article: that should keep you busy for the week!
ASM_PREFERRED_READ_FAILURE_GROUPS
Obviously only of relevance if you're running ASM. Failure groups have been around since ASM was invented: they let you specify which set of disks should be used to provide redundancy to which other disks in a diskgroup. This new parameter is intended for clustered ASM instances and specifically for 'extended clusters' -where nodes in a cluster are separated by kilometres of dark fibre. The disk group being used for redundant information might be the disk group closest to your node -and the new parameter lets your instance? read from the redundant copy of the data rather than the 'live' copy which is stored on a remote node (and which would therefore take you longer to read from if you had to... which you were forced to do in 10g).
For further details, see: this part of the official documentation
CLIENT_RESULT_CACHE_LAG & CLIENT_RESULT_CACHE_SIZE
The Client Result Cache is a new feature that allows a client to remember the results of queries that it issues repetitively. Second and subsequent submissions of the same query can fetch results from local memory instead of having to waltz back to the database and re-execute the query. These parameters govern how that client cache behaves.
A quick introduction to the entire concept of the client cache and how you can write queries to make use of it (basically, stick in a /*+ result_cache */ hint in your code!) is available here.
The LAG parameter is measured in milliseconds, and defaults to 5000 (hence, 5 seconds!) After that time, repeats of earlier queries will go back to the database to get updates. Within that time, results will come from the client cached copy of the earlier results.
The SIZE parameter is measured in bytes and defaults to zero, meaning the client cacheing feature is switched off by default, because no client memory is available for caching. Set it to a non-zero value, however, and the feature is enabled. The parameter is a one-size-fits-all affair: all client connections can use up to this amount of local memory. You can't have some connections able to use more than others.
COMMIT_LOGGING
This new parameter governs how LGWR behaves. In versions prior to 10g, if a user says 'commit', LGWR has had to immediately flush their redo to the redo logs. This will still happen if the new parameter is set to a value of IMMEDIATE. But you can also set the parameter to a value of BATCH -and at that point, LGWR will wait to collate a chunky batch of redo to write to disk before actually writing to disk.
The usual sources are a bit thin on detail! However, the short version would appear to be that the new parameter tries to turn setting 10g's COMMIT_WRITE, which could be set in 8 different ways, into a simple 'on/off' affair.
By batching up redo before flushing it to disk, you can reduce waits associated with redo generation and thus improve performance and throughput. On the other hand, it means a committed transaction is not 100% guaranteed to be recoverable any more, because there's a gap between when you commit and when your transactional data is finally written to physical disk. Good for high-volume OLTP sites, probably (because if you lose a transaction because of the delayed writing, it's likely to be small enough to be repeated without too much drama. Meanwhile, the huge quantity of transactions will benefit by not forever driving LGWR into the ground).
COMMIT_WAIT
Related to the above. This parameter can accept values of NOWAIT, WAIT and FORCE_WAIT.
By setting COMMIT_LOGGING to IMMEDIATE and COMMIT_WAIT to WAIT, you appear to achieve the same thing as you could have done in 10g by setting COMMIT_WRITE to 'IMMEDIATE,WAIT' -but you do it with a parameter that doesn't require quotation marks and commas to set, which makes it rather easier to set in code, for example.
CONTROL_MANAGEMENT_PACK_ACCESS
Standard Edition users may rejoice at this point. This parameter can take the values NONE, DIAGNOSTIC or DIAGNOSTIC+TUNING. Set it to NONE and the Automatic Workload Repository, ADDM and all the other Enterprise Edition-only advanced tuning features introduced in 10g will no longer be accessible in Enterprise Manager (the links still work, but you get sent to a nag-screen (“You haven't bought the license but we think you should”!) instead of seeing any real, meaningful content.
Contrary to some reports doing the rounds on the Internet, however, the parameter does NOT switch off MMON, the background process responsible for waking up every hour and populating the AWR tables you aren't allowed to query:
SQL> show parameter control_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> !
oracle@plutonium$ ps -ef | grep mmon
oracle 6546 1 0 09:46 ? 00:00:01 ora_mmon_orcl
oracle 7285 7245 0 09:51 pts/1 00:00:00 grep mmon
oracle@plutonium$ exit
exit
SQL> alter system set control_management_pack_access='NONE';
System altered.
SQL> !
oracle@plutonium$ ps -ef | grep mmon
oracle 6546 1 0 09:46 ? 00:00:02 ora_mmon_orcl
oracle 7356 7336 0 09:51 pts/1 00:00:00 grep mmon
oracle@plutonium$ exit
exit
SQL> startup force ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 293603456 bytes
Database Buffers 121634816 bytes
Redo Buffers 6131712 bytes
Database mounted.
Database opened.
SQL> !
oracle@plutonium$ ps -ef | grep mmon
oracle 7391 1 2 09:51 ? 00:00:01 ora_mmon_orcl
oracle 7543 7523 0 09:52 pts/1 00:00:00 grep mmon
Note how, in this example, MMON is on – I change the parameter value – MMON is still on – I bounce my instance -and MMON stays on regardless!
In short, the effect of the parameter is simply to switch Enterprise Manager into a 'license-safe' configuration, and that's it. It does nothing to stop your server resources being consumed to no purpose.
The only other slight fly in the ointment is that the default value for this parameter is DIAGNOSTIC+TUNING... which means that a whole slew of expensive features you're not supposed to touch are still switched on by default... which in turn means that you're still in some jeopardy of inadvertently breaching the terms of your Oracle license and rendering yourself liable to any upgrade fees Oracle may feel inclined to demand during a license audit!
DB_LOST_WRITE_PROTECT
A 'lost write' is what happens when Oracle flushes a data block to disk, the file system reports the write has taken place... but in fact it's lying through its teeth and the write never actually completed. The trouble starts when Oracle next reads the block: if it thinks the block is 'fresh' and contains the data it was supposed to had it been saved properly, Oracle will rely on its contents as being up-to-date. Use those contents to update something else and you've just corrupted that something else.
The parameter takes values of TYPICAL, FULL or NONE. NONE is the default, meaning no lost-write protection takes place. TYPICAL means the database protects read-write tablespaces. FULL means it protects read-only tablespaces as well as read-write ones.
'Protection' in this context means recording, in the log buffer, the fact that a block is read and the SCN of the block being read. If you subsequently ever do recovery, therefore, you will be able to compare the SCN the block should be at (as recorded in the redo) and the SCN it is actually at (as recorded in the block itself). If the block turns out to be older than it ought to be, you know you've got a lost write on your hands. More to the point, Oracle will know and warn you with a new ORA-00752 error message.
There's no fix for a lost-write problem, apart from restoring the database from a backup and performing recovery. But at least you will know what the problem is with a new degree of certainty. Of course, this certainty comes at a cost: flooding your log buffer with details of every block being read. Expect to clobber your database's performance as a result by around 10-20%. The feature is especialyl intended for no-data-loss Data Guard environments, where no data loss or corruption can be tolerated and a performance hit is a price worth paying. It doesn't have to be used in a Data Guard setting, though... but I'm not sure I'd chuck that much performance out of the door for it!
The official documentation has more to say on the topic, of course.
DB_SECUREFILE
Large Objects (BLOBs and CLOBs) can now be encrypted and compressed within the database, provided they are stored within ASSM tablespace (so ASSM stops being quite so evil in 11g!). LOBs whichhave the ability to be compressed and encrypted are called 'securefiles' and the DB_SECUREFILE parameter controls whether LOBS are created as securefiles automatically or not. LOBs which are not created as 'securefiles' are now said to be 'basicfiles' -which is what you've been using in versions 8.0 to 10g.
The parameter takes values NEVER, PERMITTED, ALWAYS and IGNORE, and the default value is PERMITTED. Never means you can never create securefile LOBs; Permitted means you can create securefile LOBs but you'll have to specify the fact manually at the time you create them; Always means all LOBs will automatically be created as secure, even if you forget to ask for it. Ignore is a bit like NEVER: practically, you won't be able to create securefile LOBs. But whereas an attempt to do so when NEVER is set will cause an error, an attempt to do so when IGNORE is set will just be silently ignored.
Manually requesting a securefile LOB looks like this:
SQL> create table t(
2 col1 blob,
3 col2 varchar2(10))
4 lob(col1) store as securefile;
create table t(
*
ERROR at line 1:
ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace "SYSTEM"
You'll note that ASSM is a requirement for this new feature (and that will teach me, trying to create a user table in SYSTEM tablespace!)
SQL> create table t(
2 col1 blob,
3 col2 varchar2(10))
4 tablespace users
5 lob (col1) store as securefile (compress);
Table created.
SQL> select * from t;
no rows selected
This time, the table creation works fine -and you'll notice another new feature in 11g: you can select from a table containing BLOBS in 11g without the select statement blowing up!
DB_ULTRA_SAFE
This parameter is an admission by Oracle that incrementally adding data protection to their database over several versions eventually ends up with a pile of parameters that it's difficult to get your head around!
As things stand, we have DB_BLOCK_CHECKING (which goes back to at least 8.0 days, I think); DB_BLOCK_CHECKSUM (8i); and now, in 11g, we have DB_LOST_WRITE_PROTECT. All three parameters are designed to detect corrupt or otherwise iffy blocks of data. Setting three parameters is a pain; not being quite sure of the interactions between them is potentially a major problem.
Enter DB_ULTRASAFE. It takes values of OFF, DATA_ONLY or DATA_AND_INDEX, and OFF is the default. If set to DATA_ONLY, you've actually just set DB_BLOCK_CHECKING to MEDIUM, DB_BLOCK_CHECKSUM to FULL and DB_LOST_WRITE_PROTECT to TYPICAL. Setting the new parameter to DATA_AND_INDEX means you've done almost the same, except that DB_BLOCK_CHECKING is now set to FULL rather than MEDIUM.
In other words, you set one parameter to effectively set three others under the hood. It's a bit like setting FAST_START_MTTR_TARGET and thereby causing values for LOG_CHECKPOINT_INTERVAL and FAST_START_IO_TARGET to be computed for you. Less hassle, in a word.
I'll just mention, though, that personally I find it a real hassle to remember that ULTRA_SAFE does have an underscore whereas SECUREFILE doesn't! Can we have some consistency in word-split policy, please, Oracle?!
Additionally, I find the new 11g fashion for having parameters with very wordy arguments annoying. ULTRASAFE could have had values of OFF, PART and FULL (or 0, 1 and 2)... but instead, we are saddled with 'DATA_AND_INDEX', which is a pain to type. 10g did it better with things like 'BASIC', 'TYPICAL' and 'FULL' for STATISTICS_LEVEL, for example. Clearly, new staff have been hired in the meantime... but it's not an improvement!
DDL_LOCK_TIMEOUT
OK, rant over: This one I like! It's measured in seconds, takes values from 0 to 1,000,000 and says how long a DDL statement should wait to acquire the necessary exclusive table locks before blowing itself up. In earlier versions where the behaviour was not configurable, a DDL statement would immediately blow up if it couldn't acquire the necessary locks. This remains the default behaviour in 11g, because the default value for the parameter is 0 seconds.
This, for example, is from 10g Release 2:
In session 1:
SYS@orcl> create table t(col1 char(4));
Table created.
SYS@orcl> insert into t(col1) values ('AAAA');
1 row created.
Note there is no commit at the end of that insert, so a shared lock on the table is still held. Now in session 2:
SYS@orcl> drop table t;
drop table t
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
Now, in 11g, without configuring anything, we see the same sort of thing. In session 1:
SQL> create table t(col1 char(4));
Table created.
SQL> insert into t(col1) values ('AAAA');
1 row created.
...and in the second session:
SQL> drop table t;
drop table t
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
The error message gets a little longer, but otherwise the behaviour remains unchanged: instant blow-up. However, the second session can now do this:
alter session set ddl_lock_timeout=50;
...and the drop table command will now merely appear to hang for 50 seconds. If a commit is issued in session 1, thereby releasing the shared table lock, session 2 will automatically spring back into life and complete successfully. Although in some respects it merely extends the ability we've had for a while to specify a WAIT time for a DML statement, it's nice to have the same functionality for DDL, too.
DIAGNOSTIC_DEST
Another nice one! In previous versions, we have had to set BACKGROUND_DUMP_DEST to be the location for process trace files and the alert log; USER_DUMP_DEST for user-requested trace files; CORE_DUMP_DEST and so on and on. With 11g, you now set this one new parameter to some path on your file system and the myriad trace files, alert logs and other diagnostic output files are then created within subdirectories automatically created within that main path.
USER_DUMP_DEST and the rest still exist, incidentally: presumably there are applications out there that expect to be able to set them. But the value set for the old parameters is silently ignored: DIAGNOSTIC_DEST takes precedence. For example:
SQL> show parameter user_dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /oracle/diag/rdbms/orcl/orcl/t
race
This is the default setting for my USER_DUMP_DEST parameter ...and it happens to be pointing at a location within the DIAGNOSTIC_DEST (which I haven't set myself, but it defaults to $ORACLE_BASE, which in my case is simply /oracle). So the _DUMP_DEST parameters 'pick up' a setting which is effectively that of the new DIAGNOSTIC_DEST parameter. Let's over-ride that behaviour:
SQL> alter system set user_dump_dest='/home/oracle';
System altered.
SQL> show parameter user_dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /home/oracle
SQL> alter database backup controlfile to trace;
Database altered.
So I've forced USER_DUMP_DEST to have a user-specified value, and the new setting has been accepted and is visible. The request to produce a tracefile backup of the control file would, in all previous versions of Oracle, have caused a file to be written to the new USER_DUMP_DEST directory -in my case, the /oracle/home directory. But not any more:
SQL> !
[oracle@plutonium ~]$ ls
database
[oracle@plutonium ~]$ pwd
/home/oracle
Definitely sitting in /home/oracle as I am, I cannot find any evidence of any text file output from Oracle at all!
[oracle@plutonium ~]$ cd /oracle/diag/rdbms/orcl/orcl/trace
[oracle@plutonium trace]$ ls
alert_orcl.log orcl_ora_3470.trc orcl_ora_3470.trm
But if I travel to the DIAGNOSTIC_DEST, lo! the trace file appears (and you'll notice that the alert log is also there, meaning that BACKGROUND_DUMP_DEST must pick up the new DIAGNOSTIC_DEST value, too).
This might sound trivial, but it isn't: the entire diagnostic capabilities of the database have now been wrapped up into a file-based repository, the location of which is given by this new parameter. It's grandly called the Automatic Diagnostic Repository or ADR for short. What's more, an entire management infrastructure has been put in place that lets you manage this repository: for example, there's an automatic (but configurable) retention policy, so old trace files will get automatically deleted (by the MMON background process); there's an entire command-line tool called adrci to allow you to view the respository, change its retention policy and so on; and there's a new view called V$DIAG_INFO which lets you see the repository's key locations from within the database itself. It's one of the rare 'extremely nice to have' features in 11g (in my view!), and it's most welcome.
Just one minor word of warning: the new ADR is available regardless of your edition. Standard Edition users can make use of it without a problem, for example. But Standard Edition users might want to turn off MMON, because they are not licensed to use the Automatic Workload Repository that MMON spends most of its time populating. But switching off MMON in 11g would now mean loss of Automatic Diagnostic Respository functionality... choose your poison, basically!
More information and documentation available from the usual place, of course.
ENABLE_DDL_LOGGING
Weird. I do a search for this in Google... nothing. I do a search for this on the 11g documentation website... nothing! So there's not a lot to go on!!
Fortunately, the parameter's name is fairly self-explanatory: DDL statements (create table, drop table etc) will now be recorded in the Alert Log (with the exact time when they occurred) if the parameter is set to TRUE. They won't be if it's set to FALSE, which is both the default value and the behaviour in previous versions.
Interesting to note, though, that whilst setting the parameter to true causes both the time and the exact syntax of the DDL statement to be recorded in the alert log, not a whisper is to be found about which user issued the command! Seems a bit of an omission to me, but there you go: it's better than a kick in the teeth when it comes to trying to work out the precise time at which to halt an incomplete recovery.
GLOBAL_TXN_PROCESSES
This one's a bit of a snooze, I'm afraid. It's only of use in a RAC, it controls the number of GTXn background processes each instance? in a RAC is allowed to run, it defaults to a value of 1, and you don't muck around with it unless you're doing distributed transactions. It's default setting means that big transactions don't spill across multiple instances in a RAC without being able to share locks and other resources. In earlier versions, they could (and did) and as a result could end up 'loosely coupled', with odd results that looked anything but transactionally consistent.
It's definitely advanced exotica, so read the official story here for further details.
JAVA_JIT_ENABLED
Java has been in the Oracle database since 8i days and it's always been run as an interpreted language by default: the code is turned into executable byte code only at run-time. As a result, Java code run in the database has acquired a bit of a reputation for running slowly (and hence should be whipped out and thrown on an Application Server pronto!). This parameter might just change that perception, because it does 'just-in-time' compiling of Java source into native machine code and subsequent management of that code (expiration, invalidation and so on): Java code should therefore run 'much faster' than before because it's not having to be forever interpreted.
The parameter defaults to TRUE for Linux, though the official documentation points out that the default value for the parameter is operating-system dependent.
The fun-and-games aspect of this arise from the fact that the just-in-time compilation is managed by an MMON slave: switch MMON off because as a Standard Edition user you have no use for the Automatic Workload Repository and you've just clobbered your ability to do JIT Java Compilation!
LDAP_DIRECTORY_SYSAUTH
You have been able to use LDAP directories such as Oracle Internet Directory to locate Oracle services for a long time: I documented it here a while back. You can also use an LDAP directory to authenticate ordinary users, and you've been able to do that for a while (it's nice: it saves users having different usernames and passwords for the domain and the database, for example). But you haven't been able to use an LDAP directory to authenticate privileged users -those that connect 'as sysdba' or 'as sysoper'. I wasn't actually aware of that fact until now, so you learn something every day... and now that I've just learnt it, this parameter means I can un-learn it, because if it's set to YES, then privileged users can be authenticated by an LDAP directory.
MEMORY_MAX_TARGET and MEMORY_TARGET
Fun and games. The rules about what happens when you set these parameters and the older ones they're designed to replace are legion! I'm not going to document them all here!
Suffice it to say that in 10g and 9i, you were able to automate the allocation of memory by setting SGA?_TARGET and PGA_AGGREGATE_TARGET. The first parameter governed the size of the shared memory -basically 'the instance- and the second governed the size of the private, working memory areas (sort areas and so on) allocated to each user's server process.
The obvious question was 'why have two parameters', and the new answer in 11g is, 'fair enough: have one instead'. MEMORY_TARGET on its own now does the job of both older parameters and thereby automates the total memory management of an Oracle server in one step.
Targets are only targets, however, and Oracle can, if it feels it needs to, allocate more memory than the target: the MEMORY_MAX_TARGET puts a hard ceiling on the amount of memory that can be consumed, however.
Now for just some of those 'interaction rules' I mentioned earlier:
- If you don't set MEMORY_MAX_TARGET explicitly, it gets set to be the same as MEMORY_TARGET.
- If you set both SGA_TARGET and MEMORY_TARGET, SGA_TARGET acts as a minimum below which the SGA cannot shrink.
- Likewise, setting both PGA_AGGREGATE_TARGET and MEMORY_TARGET means the PGA_AGGREGATE_TARGET acts as a minimum amount of PGA memory that is available.
I suppose this is a good thing: but I'm currently working with a database that has a 100% miss rate on the library cache because its code wouldn't know a bind variable if it came up and hit it on the head with a big mallet, and using 10g's automatic memory management facilities on that database resulted in ever-larger allocations of memory to the shared pool (to absolutely no effect because it is the code that's bad, not the amount of memory) and ever-smaller amounts of buffer cache (where the memory could have actually been doing useful work). So whilst it's lovely to have automatic memory management if you're a mainstream database, if you are in any way 'odd', it's not yet subtle enough to cope... and being able to configure it with one parameter instead of two doesn't help that situation any. Just call me a grump, I suppose... but I daresay this is a much more convenient way of doing things for many.
Anyway, if you are thinking the feature can work for you, there's more information in the usual place, of course.
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
This one takes the prize for being the parameter with the longest name (in case you're wondering, it's 3 characters longer than anything else... and I might add that nearly all the longest-named parameters were released in 11g. Someone at Oracle Corporation needs to take concision pills, I think!)
Anyway, SQL Plans were a brilliant feature of 10g -though you had to be running Enterprise Edition and have paid for the extra diagnostic and tuning packs to be able to use it. The problem is a simple one: you fire off a piece of SQL to the database and you expect it to turn it into a perfect execution plan in a matter of a few tenths of seconds?! The fact that it usually does exactly that in so little time is nothing short of a miracle, if you ask me... but there are bound to be 'iffy' bits of SQL that confuse the hell out of it and as a result it ends up getting its plans horribly wrong. In such case, if only you could give the optimiser advanced warning! Feed it the query half an hour before you need to run it, for example, and let it think about it really hard! Chances are, with lots of time to think it over, it will come up with a better plan: and that's precisely what creating a SQL Plan in 10g is all about.
Using the SQL Tuning Advisor in 10g, you could get the optimiser to think about a SQL statement really hard. It would maybe get a chance to check if the table statistics were accurate; if not, it would re-calculate them. It would part-run the query to test whether it really was running as fast as it expected it to; if not, it would re-jig its plans and try again. It would check if histograms existed, were accurate, needed to be recalculated... and if it thought fresher histograms would help, it would calculate them. And taking all that into account, it would produce a bundle of hints called a SQL Plan, and future executions of that same SQL statement would then be able to take advantage of all that preparatory 'research'.
Well, this new feature in 11g means you don't have to run the SQL Tuning Advisor to create a SQL Plan. If the parameter is set to TRUE (it's FALSE by default), then every SQL statement thrown at the database gets a plan created for it automatically. When the same statement is subsequently re-run, it can take advantage of the plan created by the first run. As the statement gets repeated over and over, the optimiser keeps an eye on how long it runs, and updates the plan if it runs faster or slower, trying to keep the plan up-to-date as the structure of the table and the nature of its data changes over time.
It's a good feature, if you ask me. The bad news is, of course, it's using the same functionality you would have to buy the Enterprise Edition plus packs to make use of in 10g... and you accordingly still need the Enterprise Edition 11g. As far as I can tell from the 'Family of Products' document Oracle produces to explain licensable options, however, you don't need the Diagnostics and Tuning packs to make use of 'SQL Plan Management' (which I assume covers this bit of functionality). Don't take my word for it, of course: talk to your Oracle Sales Representative for the official version.
No comments:
Post a Comment