Borndba

Transparent Data Encryption, this a new feature introduced in 10gR2 in encrypting the sensitive column. I read something interesting on it's impact on performance and storage. Thought let me document it.

Overhead of encrypting or decrypting will be 5%. If a encrypted column is indexed, in the index the value is stored as cipher text. On storage front, the impact is huge, for every encrypted column there is additional overhead of 20bytes due to integrity check. TDE will pad encrypted value to 16 bytes also. Incase if the non-encrypted column requires 10 bytes, when you encrypt it will additionally take 6 bytes. If salt is specified when defining the column, it will additionally consume 16 bytes. 

So altogether if a column is encrypted it will require 33 and 48bytes of additional storage per rows.

So, to get encryption enabled, need to sacrifies a lot of performance and storage front.

Earlier to 9iR2, if you change the _spin_count it will affect the spinning of all the latches. This affects the CPU resource in particular. From 9iR2, you can assign a latch to classes, and each class can have different _spin_count. Let us assume that library cache latch is having high SLEEPS and if you like to tune only this latch you can assign library cache latch to a class. There are totally 8 classes. The X$KSLLCLASS (Kernel service lock latch class) view contains information about these classes.

sys@10.2.0.2.0> select indx,spin,yield,waittime from x$ksllclass;

      INDX       SPIN      YIELD   WAITTIME
---------- ---------- ---------- ----------
         0      16000          0          1
         1      16000          0          1
         2      16000          0          1
         3      16000          0          1
         4      16000          0          1
         5      16000          0          1
         6      16000          0          1
         7      16000          0          1

8 rows selected.

From above output, note INDX=1 and the corresponding spin. We will be assigning this class to library cache and change the spin count to 20000 from 16000.

sys@10.2.0.2.0> select latch#,name from v$latchname where name='library cache';

    LATCH# NAME
---------- --------------------------------------------------
       214 library cache

Latch number of "library cache" is 214.

sys@10.2.0.2.0> alter system set "_latch_class_1"=20000 scope=spfile;

System altered.

Assigning class_1 with 20000.

sys@10.2.0.2.0> alter system set "_latch_classes"="214:1" scope=spfile;

System altered.

Assigning the latch to class 1.

sys@10.2.0.2.0>

sys@10.2.0.2.0> shutdown abort
ORACLE instance shut down.
sys@10.2.0.2.0> startup
ORACLE instance started.

Total System Global Area   71303168 bytes
Fixed Size                  1287836 bytes
Variable Size              62916964 bytes
Database Buffers            4194304 bytes
Redo Buffers                2904064 bytes
Database mounted.
Database opened.

Bouncing the instance to make the changes effective.

sys@10.2.0.2.0> select indx,spin,yield,waittime from x$ksllclass;

      INDX       SPIN      YIELD   WAITTIME
---------- ---------- ---------- ----------
         0      16000          0          1
         1      20000          0          1
         2      16000          0          1
         3      16000          0          1
         4      16000          0          1
         5      16000          0          1
         6      16000          0          1
         7      16000          0          1

8 rows selected.

Please note the INDX=1, now the spin count=20000

sys@10.2.0.2.0> select a.kslldnam, b.kslltnum, b.class_ksllt
  2  from   x$kslld a, x$ksllt b
  3  where  a.kslldadr = b.addr
  4  and    b.class_ksllt > 0;

KSLLDNAM                                             KSLLTNUM CLASS_KSLLT
-------------------------------------------------- ---------- -----------
process allocation                                          3           2
library cache                                             214           1

The above query gives the output of to which class number the latch is assigned.

Freelists is list of blocks which can be used by segment on shortage of space. When delete occurs and freespace within the block goes below pctused, this block will be put in freelist. The block which is added last will be released first. Like B3 is added to freelist, then after a while B1 gets added then the chain looks like.

FL: B3
FL: B1 ==> B3.
On shortage of space B1 will be released before B3.

Type of Freelists:
Txfl  ==>  Transaction Freelist
Prfl  ==>  Process Freelist
Sgfl  ==>   Segment Freelist
Msfl  ==>  Master Freelist
Infl  ==> Instance Freelist

Txfl:
Each segment will have in minimum 16 Txfl, it grows as needed. During a transaction if space is required then oracle will first look into the txfl. At the same time if it wants to release the space it will give it to txfl.
Prfl:
Freelist allocated to a process. A process will not scan prfl allocated to other process.
Sgfl:
Synonym for Prfl.
Msfl:
It is introduced in later part of V6. It is preallocated Prfl which all processes can access known as Master Freeist.
Infl:
No idea on this.

Please note the algorithm when a transaction faces space issue,
• Search the space in it’s own Txfl. If it exist it will use otherwise
• Search the space in Prfl, it it doesn’t find then
• Search the space in Msfl. If it finds one it will copy the chunk to Prfl and use it otherwise
• Search the commited Txfl. If it finds one it will copy the chunk to Msfl from there it will be copied to Prfl. If if doesn’t find one
• Checks whether it can bump the high water mark level. It will bump 5 blocks at a time or defined by _bump_high_water_mark parameter. The added values will be passed to Prfl and used. If not
• Goes to fet$ and allocate a segment. Even if there is no space
• Errors out with unable to extent tablespace error.

Also note index block will be put to freelist only if the block is completely empty.
Event 10044 is used for tracing Freelist Undo operation and event 10045 for tracing freelist update operation -ktsrsp,ktsunl

Type of Event:
There are 4 types of events.
1. Immediate Dump Events
2. Error Stack Event
3. Change Behavior Event
4. Process Event

Immediate Dump Events:
This event generate trace file immediately in the udump area. These parameters cannot be issued in Parameter file. Some of the event involved are Trace_Buffer_On/Off,Hanganalyze,Latches,Processstate,SystemState,Instantiationstate,Refresh_Os_Stats,Crossic,ContextArea,Headdump,shared_server_state,Drop_Segments library_cache,full_dumps, granulelist, dump_all_comp_granule_addrs, buffers.
Error Stack Events:
If you need to capture the processes throwing out ORA error, you can use this event. This is very useful in diagnosing 4031, 942, 4020, 1555 etc., errors.
Change Behavior Event:
You need to be very careful before setting this event. This event can change the behaviors of oracle kernel and may render your db unsupported if used without approval of oracle support. There are n-number of events fails under this category. These events _generally_ doesn’t have levels associated with it. To name few are 10512 (disabling rollback shrinking by smon), 10170 changes the costing algorithm etc.,
Process Trace Events:
This is less/no harm events. This event is helpful in diagnosing behaviors of the process. Some of the events are 10046, 10053, 10032, 10033 etc.,

If you set an event, event information is stored in the PGA. Since it is stored in the PGA which is private to a session, you can’t find out event set in the other sessions. In case if you want to find out the events set in the current session, you can use below block.

set serveroutput on
declare
  event_level number;
 begin
  for i in 10000..10999 loop
     sys.dbms_system.read_ev(i,event_level);
     if (event_level > 0) then
        dbms_output.put_line('Event '||to_char(i)||' set at level '||
                             to_char(event_level));
     end if;
  end loop;
 end;
/

Excluding few tables while exporting or importing can be done by changing definition of export related dictionary tables. In 10g, expdp/impdp has got the feature to do so. Changing the dictionary definition is not supported by oracle.

There will be a view called exu10tab in catexp.sql. This sql file will be available in $ORACLE_HOME/rdbms/admin folder. Search for the view name “exu10tab”. The definition of view will be like below,

CREATE OR REPLACE VIEW exu10tab AS
SELECT *
FROM sys.exu10tabs t$
WHERE t$.secondaryobj = 0 ;

If you want to exclude Table “T” from the export add a where condition like below,

CREATE OR REPLACE VIEW exu10tab AS
SELECT *
FROM sys.exu10tabs t$
WHERE t$.secondaryobj = 0
and NAME NOT IN (‘T’);

Once export is completed, revert make to older setting.

If you have in pre 10g version, you have similar table like exu9tab or exu8tab.