Monday, January 29, 2018

Remote debugging with SQL Developer

For all of us who use SQL Developer, we know how to debug with SQL Developer. Compile for debug, breakpoint and go. However folks are confused by what “remote debugging” is and how it works. At its most basic it allows us to run a procedure in a session and debug it from another session.
Let’s say we have a simple function like this
demo@ORA12C> create or replace function foo(x int)
  2  return number as
  3  begin
  4     if mod(x,2) = 0 then
  5             return x+2;
  6     else
  7             return x+1;
  8     end if;
  9  end;
 10  /
Function created. 
 
We compile that function for debug as normal like this.
 
demo@ORA12C> alter function foo compile debug; 
 
Function altered. 
 
Now for remote debugging, we want to go to another session and run this function from there, for clarity we can do it with SQL*Plus, before that however we need to switch on the remote debugger listener, so that we can attach to a session.  
 
So right click the connection from the SQL Developer and say “Remote Debug”, which will pop up a little window like this.
 



We are running the database server on this same machine, so we don’t need to add any other information, but if you are connecting to database running on a different machine, add that host name to this LOCAL ADDRESS field and choose the appropriate port.  

When you click OK on this, Run manager is show with the listener details.



 
Now from SQL*Plus we run our function like this.
 



Once we run the anonymous block, the remote debugger kicks in and we stop at the appropriate breakpoint in the code.



From there you can debug the code further in the SQL Developer. This works for all the application (Java,.Net,C,C++,Phyton,R,Ruby etc) calling PL/SQL code.

Monday, January 22, 2018

Online TDE in 12.2

One of the new exciting security feature introduced in Oracle 12cR2 is the ability to encrypt Tablespaces “online”.
 
In the past implementing TDE requires creating a new encrypted tablespace and then moving the data from original tablespace to the encrypted tablespace, which means a down time of your systems unless you use active data guard which minimizes TDE conversion implementation.
 
 The following are the steps to be followed for online TDE.
 
o   Configuring a key store.
o   Creating a key store
o   Open the key store
o   Setup the TDE master encryption key
o   Encrypt your data
 
Key store is a container that stores the TDE master encryption key in software key store (regular file system/ ASM file system/ ASM disk group) or Hardware security module (HSM) or Oracle Key vault key store.
 
Oracle database checks the sqlnet.ora file on the database server for the directory location of the keystore. Whether it is a software/HSM/Oracle vault key store.
 
o   It attempts to use the keystore in the location specified by the parameter (ENCRYPTION_WALLET_LOCATION) in the sqlnet.ora file
o   If the ENCRYPTION_WALLET_LOCATION parameter is not set, then it attempts to use the keystore in the location that is specified by the parameter WALLET_LOCATION in the sqlnet.ora file
o   If the WALLET_LOCATION parameter is also not set, then database look for the keystore in the default location available in the V$ENCRYPTION_WALLET
 
demo@ORA12C> conn syskm/Password-1@ora12c as SYSKM
Connected.
syskm@ORA12C> column wrl_parameter new_val f
syskm@ORA12C> select wrl_parameter from v$encryption_wallet;
 
WRL_PARAMETER
--------------------------------------------------------------------------------------
D:\APP\VNAMEIT\VIRTUAL\ADMIN\ORA12C\WALLET
 
syskm@ORA12C> administer key management create keystore '&f' identified by foobar;
old   1: administer key management create keystore '&f' identified by foobar
new   1: administer key management create keystore 'D:\APP\VNAMEIT\VIRTUAL\ADMIN\ORA12C\WALLET' identified by foobar
administer key management create keystore 'D:\APP\VNAMEIT\VIRTUAL\ADMIN\ORA12C\WALLET' identified by foobar
*
ERROR at line 1:
ORA-46633: creation of a password-based keystore failed
 
The wallet location doesn’t exists, so we need to create the specific directories at that location.
 
syskm@ORA12C> $mkdir D:\APP\VNAMEIT\VIRTUAL\admin\ORA12c\WALLET
 
syskm@ORA12C> administer key management create keystore '&f' identified by foobar;
old   1: administer key management create keystore '&f' identified by foobar
new   1: administer key management create keystore 'D:\APP\VNAMEIT\VIRTUAL\ADMIN\ORA12C\WALLET' identified by foobar
 
keystore altered.
 
 
Then open the keystore and setup the master key in this keystore.
 
syskm@ORA12C> administer key management set keystore open identified by foobar;
 
keystore altered.
 
syskm@ORA12C> administer key management set key identified by foobar WITH BACKUP;
 
keystore altered.
 
 
After you run these commands, the eWallet.p12 file, which is the keystore, appears in the keystore location.
 
syskm@ORA12C> $dir &f
 Volume in drive D is DATA
 Volume Serial Number is 50D3-A8AC
 
 Directory of D:\APP\VNAMEIT\VIRTUAL\ADMIN\ORA12C\WALLET
 
01/18/2018  05:50 PM    <DIR>          .
01/18/2018  05:50 PM    <DIR>          ..
01/18/2018  05:50 PM             3,848 ewallet.p12
01/18/2018  05:50 PM             2,400 ewallet_2018011812204795.p12
               2 File(s)          6,248 bytes
               2 Dir(s)  228,193,591,296 bytes free
 
 
With this keystore defined, we are ready to test our Encryption process.
 
First let’s create the tablespace with no Encryption defined, and grant the tablespace quota to application schema.
 
syskm@ORA12C> conn rajesh/oracle@ora12c
Connected.
rajesh@ORA12C> create tablespace ts_clear
  2  datafile 'D:\APP\VNAMEIT\VIRTUAL\ORADATA\ORA12C\ts_clear_01.dbf' size 100M ,
  3     'D:\APP\VNAMEIT\VIRTUAL\ORADATA\ORA12C\ts_clear_02.dbf' size 100M;
 
Tablespace created.
 
rajesh@ORA12C> alter user demo quota unlimited on ts_clear;
 
User altered.
 
 
Now let’s create a couple of objects on this newly created tablespace.
 
 
rajesh@ORA12C> conn demo/demo@ora12c
Connected.
demo@ORA12C> create table t
  2  tablespace ts_clear
  3  as
  4  select * from all_users;
 
Table created.
 
demo@ORA12C> create index t_idx on t(lower(username))
  2  tablespace ts_clear;
 
Index created.
 
 
Since this new tablespace is not yet encrypted, we could see the data remain in clear piece of text in their data files.
 
demo@ORA12C> conn rajesh/oracle@ora12c
Connected.
rajesh@ORA12C> alter system checkpoint;
 
System altered.
 
rajesh@ORA12C> $find /I "scott" D:\APP\VNAMEIT\VIRTUAL\ORADATA\ORA12C\ts_clear_*.dbf
 
---------- D:\APP\VNAMEIT\VIRTUAL\ORADATA\ORA12C\TS_CLEAR_01.DBF
        ♣SCOTT♥┬☻
♣scott♠☻Ç
 
---------- D:\APP\VNAMEIT\VIRTUAL\ORADATA\ORA12C\TS_CLEAR_02.DBF
 
 
Now the interesting part is online Tablespace encryption conversion: we can run the below ALTER TABLESPACE command to encrypt an existing tablespace.
 
rajesh@ORA12C> alter tablespace ts_clear
  2  encryption ONLINE
  3  using 'AES192'
  4  encrypt file_name_convert=('ts_clear_01.dbf','ts_clear_enc_01.dbf',
  5     'ts_clear_02.dbf','ts_clear_enc_02.dbf') ;
 
Tablespace altered.
 
Once encrypted no more clear piece of text in their data files.
 
rajesh@ORA12C> $find /I "scott" D:\APP\VNAMEIT\VIRTUAL\ORADATA\ORA12C\ts_clear_*.dbf
 
---------- D:\APP\VNAMEIT\VIRTUAL\ORADATA\ORA12C\TS_CLEAR_ENC_01.DBF
 
---------- D:\APP\VNAMEIT\VIRTUAL\ORADATA\ORA12C\TS_CLEAR_ENC_02.DBF
 
rajesh@ORA12C>

Wednesday, January 17, 2018

Sequence.nextval in 12c comparision with triggers

One of the cool thing in 12c is that the ability to default a sequence.nextval to a column.
While prior to 12c, we could achieve the same using a simple trigger in place.
Here is a simple test case to show – how things got improved in 12c by defaulting sequence.nextval to a column.
demo@ORA12C> create table t1(x int,y varchar2(300));
 
Table created.
 
demo@ORA12C> create sequence s;
 
Sequence created.
 
demo@ORA12C> create table t2(x int default s.nextval,y varchar2(300));
 
Table created.
 
demo@ORA12C> create or replace trigger t1_trig
  2  before insert on t1
  3  for each row
  4  begin
  5     if :new.x is null then
  6             :new.x := s.nextval;
  7     end if;
  8  end;
  9  /
 
Trigger created.
 
So we created two tables
o   Table T1 with trigger in place to populate the column X with sequence.nextval
o   Table T2 with sequence.nextval defaulted to the column X
 
demo@ORA12C> insert into t1(y) select object_name from big_table;
 
1000000 rows created.
 
demo@ORA12C> insert into t2(y) select object_name from big_table;
 
1000000 rows created.
 
demo@ORA12C> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
Tkprof shows this.
 
insert into t1(y) select object_name from big_table
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1    150.21     151.03      18570      32498      54095     1000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    150.21     151.04      18570      32498      54095     1000000
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 108 
Number of plan statistics captured: 1
 
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD TABLE CONVENTIONAL  T1 (cr=82511 pr=18570 pw=0 time=243474242 us starts=1)
   1000000    1000000    1000000   TABLE ACCESS FULL BIG_TABLE (cr=24709 pr=18570 pw=0 time=743301 us starts=1 cost=5125 size=38000000 card=1000000)
 
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  direct path read                                3        0.01          0.04
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************
 
insert into t2(y) select object_name from big_table
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     12.44      12.96      18570      26527     105359     1000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     12.44      12.96      18570      26527     105359     1000000
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 108 
Number of plan statistics captured: 1
 
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD TABLE CONVENTIONAL  T2 (cr=76539 pr=18570 pw=0 time=82169362 us starts=1)
   1000000    1000000    1000000   SEQUENCE  S (cr=68585 pr=18570 pw=0 time=316161124 us starts=1)
   1000000    1000000    1000000    TABLE ACCESS FULL BIG_TABLE (cr=18574 pr=18570 pw=0 time=519706 us starts=1 cost=5125 size=38000000 card=1000000)
 
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  PGA memory operation                            1        0.00          0.00
  direct path read                                4        0.05          0.08
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
 
A comparison from runstats tool report the same.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
demo@ORA12C> truncate table t1;
 
Table truncated.
 
demo@ORA12C> truncate table t2;
 
Table truncated.
 
demo@ORA12C> exec runstats_pkg.rs_start;
 
PL/SQL procedure successfully completed.
 
demo@ORA12C> insert into t1(y) select object_name from big_table;
 
1000000 rows created.
 
demo@ORA12C> exec runstats_pkg.rs_middle;
 
PL/SQL procedure successfully completed.
 
demo@ORA12C> insert into t2(y) select object_name from big_table;
 
1000000 rows created.
 
demo@ORA12C> exec runstats_pkg.rs_stop(1000);
Run1 ran in 3595 cpu hsecs
Run2 ran in 624 cpu hsecs
run 1 ran in 576.12% of the time
 
Name                                  Run1        Run2        Diff
STAT...redo size                86,105,192  86,103,620      -1,572
LATCH.active service list            2,919         918      -2,001
STAT...recursive cpu usage           2,465         212      -2,253
STAT...Elapsed Time                  3,672       1,167      -2,505
STAT...DB time                       3,674       1,165      -2,509
STAT...CPU used when call star       3,596         622      -2,974
STAT...CPU used by this sessio       3,596         622      -2,974
STAT...dirty buffers inspected       2,519       6,232       3,713
LATCH.simulator hash latch           8,922      13,084       4,162
LATCH.simulator lru latch            3,766       8,244       4,478
LATCH.JS queue state obj latch       7,560       2,394      -5,166
LATCH.cache buffers lru chain        4,504      10,010       5,506
LATCH.enqueue hash chains          317,189     306,351     -10,838
LATCH.object queue header oper      28,528      41,116      12,588
LATCH.checkpoint queue latch         9,486      22,116      12,630
LATCH.cache buffers chains         867,666     884,259      16,593
STAT...session uga memory           65,432          56     -65,376
STAT...session pga memory max       65,536           0     -65,536
STAT...session pga memory         -131,072     -65,536      65,536
STAT...logical read bytes from########################    -122,880
STAT...session uga memory max      192,584      65,424    -127,160
STAT...session cursor cache hi   1,050,001      50,003    -999,998
STAT...execute count             1,050,006      50,006  -1,000,000
STAT...opened cursors cumulati   1,050,006      50,006  -1,000,000
STAT...calls to get snapshot s   1,100,086     100,084  -1,000,002
STAT...recursive calls           1,050,090      50,084  -1,000,006
LATCH.shared pool                1,001,123         687  -1,000,436
STAT...Effective IO time           306,332   5,162,302   4,855,970
STAT...file io wait time        36,925,506  20,782,391 -16,143,115
 
Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
   5,623,724   4,660,079    -963,645    120.68%
 
PL/SQL procedure successfully completed.
 
demo@ORA12C>
demo@ORA12C>
 
So when you upgrade/migrate to 12c, think about revisiting those triggers to default values if possible, since they yield more benefits to performance (in-terms of CPU and number of recursive calls).