Ok, second day Oracle 11g training - what have we done today?Basically two topics were adressed:
SECUREFILES and TABLE COMPRESSION
SECUREFILES
Securefiles are (no not the new black) but the new LOB implementation for Oracle 11g.
The old LOB implementation had some drawbacks (although these usually didn't affect SAP installations, given all recommendations had been implemented correctly) so Oracle re-wrote the whole LOB stuff from scratch.
With this come some nice new features like COMPRESSION, ENCRYPTION and DEDUPLICATION.
The compression works pretty nice and uses standard compression libraries like bzip2, zlib or lzo.
Important to keep in mind when checking out the compression effects is that - just like for the old LOB implementation - lob values with less than approx. 4000 bytes will be stored in the table segment (inline) instead in the LOB segment.
As I just mentioned segments: the deferred segment creation feature discussed yesterday kept popping up again and again, as certain checks and error messages won't happen before the segment is created.
One prerequisite for using securefiles is that the table is stored in a ASSM tablespace, but with deferred segment creation Oracle lets you create the table and throws a nasty error message in your face as soon as you insert some data.
Since Oracle now has this super-fancy new LOB implementation, what about the existing tables? What about all the existing applications (like SAP) that use LOBs and don't know about securefiles?
Well, Oracle provides the DB_SECUREFILE parameter to control the behavior of the old LOB-syntax used to create tables.
Be aware that for SAP systems only PERMITTED is, well, permitted...
Don't miss SAP note
SAP Note 1426979 Oracle 11g: SecureFiles - The new way to store LOB data
to get all details about securefiles relevant for SAP.
TABLE COMPRESSION
The second big topic today was TABLE COMPRESSION.
As often, to see what's better with the new, you've to know about the old and the other.
Thus we spend some time to discuss possible (and existing) approaches to table compression.
I'm not going to repeat it here, but do yourself the favor and update your knowledge about how compression works in certain situations ('who' compresses, 'what', 'when' and 'where'?).
However, for Oracle 11g table compression FOR OLTP (yes, the compression type is actually named that way), one key point to the nice working of compression for updated and inserts is that compression is NOT DONE IMMEDIATELY!
Like in other areas Oracle decided to go for the "we'll do it just when we cannot avoid it any longer" way and it turns out that this is a quite clever idea.
Whenever a block reaches a certain (seemingly not completely deterministic) threshold, the block compression (it's B-L-O-C-K compression, not TABLE or ROW compression!) kicks in and tries to make some space in the block.
Actually this compressing is not as sophisticated as the LOB compression, but just consists of replacing values that occur multiple times by a lookup.
Interestingly this lookup is technically implemented by using a very old block storage function: to put multiple tables into one block.
Does that ring a bell? No?
In that case, go back and check what Oracle cluster storage is in the documentation 😉
So, how well is it working, this compression feature?
Is SAP going to compress each and every table now?
Surely not!
The effect of compression totally depends on what data you've got in your tables.
If you somehow can arrange a large amount of redundancy within (!) the blocks (think of inserting rows with todays date today and tomorrow with the next date and so on), in that case compression is likely to work very nicely for you.
There's again a SAP note about this feature prepared by the Oracle development:
SAP Note 1436352 Oracle 11g Advanced Compression for SAP Systems
May not look much these two topics but definitively filled the whole day 🙂
For yesterdays Savepoint click here:
Oracle 11g training, Day #1 - Savepoint!
+++UPDATE+++
I totally forgot to mention two other things we've had a look at:
1. Invisible Indexes
Ever wanted to check whether a new index would help a certain query without the risk of immediately affecting all execution plans for the table?
With the invisible indexes you can do exactly that.
The visibility is a new index attribute that basically tells the optimizer whether or not to consider the index for any plan evaluation.
Unless the new parameter OPTIMIZER_USE_INVISIBLE_INDEXES is set to TRUE the CBO would simply not use the index.
Thus, while the parameter is set to FALSE on instance level you may easily change it for your session (e.g. in ST05 - Explain Plan) or via OPT_PARAM hint for your statement and review the effects of the new index to the execution plan.
Be careful, however, since you actually do create a fully functional index, which gets maintained just as usual. This is not something like virtual indexes that shortly saw the day of light with the Oracle 9i Tuning Pack for those of you that remember this...
2. DDL_LOCK_TIMEOUT
New parameter, values is 'seconds to wait'...
Easy to explain: assume you try some DDL, say altering your table, that requires an exclusive table lock. On a productive system and a central table you'll likely see the ORA-00054 quite often for this.
Usually you'd try again a bit later and check wether you now get the table lock.
With the new parameter you now can tell Oracle to wait the specified number of seconds before giving up.
It's like a get-the-enqueue-NOWAIT-option with a wait time...
Luckily, if you explicitely run a LOCK TABLE ... IN EXCLUSIVE MODE NOWAIT then this NOWAIT will still be adhered and no waiting takes place.
Nice feature, if you ask me, but better choose the wait time wisely on your production machine...
+++
