All started with dba-willage post when someone asked how to list static and dynamic parameter..

Answer was to select  v$parameter table.I made a comment that also oradebug ( love it ) can be used to list dynamically modified parameter ( one of the previous post )  and got the answer that oradebug is useless when ISMODIFIED column or v$parameter is enough..

Since I like internal stuff a posted a query that can do the same but not using oradebug but underluying  x$ tables..  and this will list underscore parameters and session or system modified too…

Demo:

SQL> alter session set “_replace_virtual_columns”=true;

Session altered.

SQL> select
  2    a.ksppinm  name,
  3    b.kspftctxvl  value,
  4    b.kspftctxdf  isdef,
  5    decode(bitand(b.kspftctxvf,7),1,’MODIFIED’,4,’SYSTEM_MOD’,'FALSE’)  ismod,
  6    decode(bitand(b.kspftctxvf,2),2,’TRUE’,'FALSE’)  isadj
  7  from
  8    sys.x$ksppi  a,
  9    sys.x$ksppcv2  b
 10  where a.inst_id = userenv(‘Instance’) and
 11    b.inst_id = userenv(‘Instance’) and
 12    a.indx+1 = b.kspftctxpn and
 13    decode(bitand(b.kspftctxvf,7),1,’MODIFIED’,4,’SYSTEM_MOD’,'FALSE’)  <> ‘FALSE’
 14  order by
 15    translate(a.ksppinm, ‘ _’, ‘ ‘)
 16  /

NAME                                          VALUE                                         ISDEF  ISMOD      ISADJ
——————————————— ——————————————— —— ———- —–
cursor_sharing                                exact                                         TRUE   SYSTEM_MOD FALSE
_replace_virtual_columns                      TRUE                                          TRUE   MODIFIED   FALSE

Here is script:
select
  a.ksppinm  name,
  b.kspftctxvl  value,
  b.kspftctxdf  isdef,
  decode(bitand(b.kspftctxvf,7),1,’MODIFIED’,4,’SYSTEM_MOD’,'FALSE’)  ismod,
  decode(bitand(b.kspftctxvf,2),2,’TRUE’,'FALSE’)  isadj
from
  sys.x$ksppi  a,
  sys.x$ksppcv2  b
where a.inst_id = userenv(‘Instance’) and
  b.inst_id = userenv(‘Instance’) and
  a.indx+1 = b.kspftctxpn and
  decode(bitand(b.kspftctxvf,7),1,’MODIFIED’,4,’SYSTEM_MOD’,'FALSE’)  <> ‘FALSE’
order by
  translate(a.ksppinm, ‘ _’, ‘ ‘)
/

I have been vey busy these days but today finally I got some time ..

Well few weeks ago I did a post on encrypted tablespace in 11g  and one of the questions posted by Martin Beger ( Sorry I did not have a chance to replay earlier ) was that data are unencrypted in buffer cache.

I did some reading and research ( still working on my case as time permits )  and found that actually that’s a true statement.

So how it works?

1)  When you do select data are moved from storage ( encrypted data ) to buffer cache.

2) Data are DECRYPTED before loading buffer cache

3) Data are in CLEAR TEXT UNENCRYPTED inside buffer cache

Since most of  operations  ( index scans , table access ) are performed on clear text data  which are in buffer cache there is no differences in peformances  between encrypted tablespace ( unencrypted in buffer cache ) and unencrypted data.That’s the trick why encrypted tablespaces has better performances over regular TDE on table ( Have to test regular TDE on table – next workshop ).

Eventually we have to record data back to storage.

DB writer process during checkpoint will encrypted data before writing to disk (  I have proved this in my previous blog).

Now there is interesting point.According to Oracle   DIRECT PATH operations perform encryption inline.When log writer process write log buffer data to redo logs they are ENCRYPTED.If I am not mistaken in my case from previous post data were UNENCRYPTED inside redo log files but I did not use DIRECT PATH. That would be next test.