Oraclewizard.com Database Security | The database holds the crown jewels of your organization.

#ODEVCHOICE #OOW15 Thank you

Posted on October 31, 2015 by rlockard

I’m honored and humbled. Last month I received an email from Steven Feuerstein letting me know I was selected as a finalist in the Oracle Developers Choice Awards for my work in Database Security and outreach to the community. This in an of itself is a great honor. The folks who selected the finalist are people whom I have a great deal of respect for.

Everyone who made finalist is seriously good at what they do and I’m happy to call many of them friends. The first conference I spoke at was Harmony when Candy and I met Heli for the first time. My second time to Harmony, I took my mother so she could see Finland and Heli took it on herself to see that my mother enjoyed herself. Heli is a graceful and classy lady who is as sharp as they come.

Now part of me understands wanting “your candidate” to win. Heck, every time the Steelers play the Ravens I secretly hope the first string quarterback get knocked off the field. I get it; however this is a contest among ladies and gentlemen. Voting someone down so your candidate will win, well that’s just not cricket.

What amazes me about this community is some of the things you never hear, like “RTFM” (Read the fine manual) I’ve never hear, “you don’t know that, what are you stupid.” I’ve worked in shops where it was common to here these phrases. None of us are Tom Kyte (unless your Tom and you happen to be reading this.) so we don’t have encyclopedic knowledge and there are people in the DB Security and design arena who are much better then me. I ask them questions all the time when I can’t figure something out.

What do I hear a lot of from this community. “How can I help you?” “Don’t know the answer, lets figure it out.” “Well the answer is it depends.” This community got me away from using ROT, and uttering the phrase <EVIL EXPRESSION> Best Practice </EVIL EXPRESSION>. This community also gave me a passion for my work, along with many friends who I look forward to seeing at every conference. It is the community that gave me back my passion for my work and the community that keeps me striving to improve and continue helping. Thank you very much.

I want to take this opportunity to thank Jennifer Nicholson, Laura Ramsey and Steve Feuerstein along with everyone else for their hard work to put together the YesSQL event and the Oracle Developers Choice Awards. I also want to thank everyone who voted in the first Oracle Developers Choice Awards. These are your awards where you can recognize the people who have helped you.

Again thank you very much, I’m honored and humbled.

Posted in Database Stuff | Leave a comment

#infosec #LetsSecureThisTogether This is going to piss some people off. The C suite needs to have this conversation.

Posted on October 12, 2015 by rlockard

Get the words best practice out of your vocabulary.” I have been at many customer sites that needed my expertise, and someone says to me in a meeting, “well what is the best practice to secure our information.” I’m going to tell you right now, the bad guys are reading the same best practice white papers and poking holes through them left and right. In addition, that audit report you received saying you are in compliance with <state your regulation> may be factually correct at that moment in time, but your information is still not secure.

For each of your systems, bring five of your senior system administrators into a room and ask them a simple question. “How would you compromise your system?” Then sit back and listen. If they are good inside of thirty minutes you are going to start hearing things that will scare you. Let me give you some examples from my life.

We are generating audit reports but no one is actually reviewing them. At one customer, I generated audit reports that showed invalid logins’, ip address and username logins when the the connections were simultaneous, and a host of other audit reports. I then went to our security people and asked them how often they wanted this report, daily, or weekly. I was told to review them and only bring things up to them when I find something interesting. Well for one, under the concept of separation of duties your SA should not have sole responsibility for reviewing audit logs. Yes, they should but a copy needs to be sent out for review. As the Oracle DBA I can logon as oracle or logon as sys, I am god. I can do almost anything I want and cover my tracks. Can you spell Edward Snowden? Can you say with complete confidence that you do not have an Edward Snowden in your shop?

The patch schedule is to drawn out. One shop we were twelve months behind in our Oracle CPU patches because of the perception that patching the database would impact the development schedule. This is one of those times where I got maybe a little bit forceful in a meeting and pushed for getting everything patched. But the customer was adamant, “you will not do anything that will slowdown development and testing.” We finally got everything patched when it was announced there was going to be an audit. The audit happened, and there was no findings. Our immediate management was happy, me, I was not so happy. When a patch comes out, the bad guys are reading what is being fixed and they are quite adapt at exploiting the weaknesses that are being patched.

A webserver that is miss-configured, I walked into one customer and as I was setting up my audit scripts, I noticed there were over a thousand invalid login attempts from a handful of webservers every thirty minutes. The DBAs were not talking to the webserver SA’s and the webserver SA’s would allow the invalid login attempts to continue if the application that webserver supported was no longer in use and again, no one was actually reviewing the audit logs. In fact this was a known issue that was explained as “normal” to the security group. This is the perfect way to hide password cracking attempts. One of the webservers that was in the DMZ had not been used for over a six months, but was still running. When I dug into the audit trail to see what was actually going on, I found several attempts to connect to the database as sys, system, admin, sa, root and a host of other attempts. That server had been compromised along with a few others. When I brought the evidence up to management they were shocked. Finally passwords were changed on the webservers and those webservers that were no longer in use were pulled from the network and a complete scan of the network was completed.

Excessive privileges to developers and developers using a common development account. Yup, this still happens. I walk into a shop, and given the application username password to do all my work. Folks your audit trail is now toast. At that same shop all developers have the passwords for sys and system. I never got a good reason for this. NO ONE should ever logon at sys. Application accounts should never be used for development.

This is just a small fraction of the issues I have seen in different shops.

All of these shops either followed “best practices” or modified their practice when the learned there was going to be an audit. Everyone of those shops are staffed with professional SA’s who can tell you where the weakness is. And every shop I have ever worked in has weaknesses. Your job in the C suite is to ask these professional SA’s to come into a meeting and have a safe and secure conversation on how they would crack the system. If they can not come up with anything you are either working at NSA, they are trying to hide something or you need smarter SA’s.

And don’t just do this once and say, we’re done, schedule these meetings either quarterly or semiannually. This is a conversation between the C suite and your professional SA’s. You need to understand where your risk are, these people are real smart and if you listen to them they can tell you what’s wrong and what needs to be done to fix it.

Once you have this information, give your professional SA’s and their management the tools and resources they need to close these security holes.

Posted in Database Stuff | Tagged CSuite, infosec | Leave a comment

How to kick your Oracle Career up several notches or How speaking at Oracle users groups gave me back my passion for my profession. #ACE #ILOVEMYJOB #ODEVCHOICE

Posted on October 2, 2015 by rlockard

Please vote in the Oracle Developers Choice Awards. You need to log into your OTN account. You don’t have to vote for me, but I would appreciate it.

Oracle Developers Choice Awards

Here is the link for database design that is the category I’m in

Oracle Developers Choice Awards Database Design

The list of people who helped me get to where I am is way to long. But I would like to thank John King and Jeff Smith for the Oracle #ACE nomination and recommendation. Cary Millsap for always being able to bounce ideas off of. Heli from Finland for her grace and how she made my mom feel at comfortable when we were in Helsinki.

Practically Perfect PL/SQL by Steven Feuerstein

Oracle User Group Conferences

Posted in Database Stuff | Tagged #career | Leave a comment

Don’t get invited to sit in front of a Congressional Committee. #Oracle #TDE August 27, 3PM EST #INFOSEC

Posted on August 26, 2015 by rlockard

#Oracle #TDE Ghost Data Teaser

Posted on August 5, 2015 by rlockard

Here is a teaser for the Oracle Transparent Data Encryption presentation

We look at having an existing table with existing indexes. A policy comes out that says we need to encrypt SSN and Credit Card Numbers. Once we encrypt the columns and rebuild the indexes, does the unencrytped data in the index get encrypted?

Watch and find out.

Posted in Database Stuff, encryption, infosec, Security | Tagged Encryption, GHOST, infosec, oracle | Leave a comment

Oracle #OTN #OPSEC #TMTT

Posted on August 3, 2015 by rlockard

Oracle Technology Network Two Minute Tech Tip. Oracle Transparent Data Encryption.

Posted in Database Stuff, encryption, infosec, Security | Tagged Encryption, GHOST, infosec, oracle | Leave a comment

Oracle Transparent Data Encryption Baseline Practices webinar

Posted on July 16, 2015 by rlockard

I will be giving a webinar on Oracle Transparant Data Encryption Baseline Practices August 27, 2015 at 3PM. Sponsored by @odtug

Why “Baseline Practices?” well best practices does not seem to be working so we are going to start improving “your game” by setting the baseline and getting you to think how information is secured.

This one-hour presentation includes how to, gotchas (where data can leak in clear text), and baseline practices for implementing Oracle Transparent Data Encryption. We will walk through setting up Oracle Transparent Data Encryption and establish baseline practices to secure your information, explaining where and how to encrypt the data, and where and how data leaks in plain text and HEX format. We will also explore these questions: When sending data to a standby database, when does the data stay encrypted and when can it transfer over the network in clear text? When using Oracle Streams, does data go across the network in clear text? When gathering statistics on encrypted data, where can the data be exposed unencrypted? As we discuss each of the leaks, we will also review how to mitigate the leakage and eliminate ghost data.

Posted in Database Stuff, Security | Tagged Encryption, infosec, OPSEC, oracle | Leave a comment

#Oracle #TDE #dataleak #Histograms

Posted on July 7, 2015 by rlockard

While at #KSCOPE15, I was asked about encrypted data showing up in histograms. So, I ran a few experiments and learned that encrypted data does indeed leak. I contacted Oracle through an old friend to get their input.

Here is the reply I received.

================================================================

The attack vector here is via SELECT on histogram tables. The protection profile for TDE by design does not address DB users with proper access to such tables. The gotcha here is that many people don’t realize they should control access to STATS tables as well as the tables with sensitive data.

Possible ways to workaround:

1. Configure Database Vault policy so user who tries to query sensitive columns on these views/tables is denied access

2. Do not collect stats on sensitive columns in the first place

===================================================================

Here is my experiment:

  1 Test 1)   Note, we are putting this into a non-encrypted tablespace, we are going to explicitly encrypt column D1.
  2 
  3 --CREATE A TEST TABLE
  4 RLOCKARD@pdev > CREATE TABLE t1 (id NUMBER GENERATED AS IDENTITY, d1 VARCHAR2(255)) TABLESPACE not_sensitive;
  5 Table created.
  6 
  7 -- ENCRYPT THE DATA
  8 RLOCKARD@pdev > alter table t1 modify (d1 encrypt using 'AES256');
  9 Table altered.
 10 
 11 --INSERT SOME TEST DATA
 12 RLOCKARD@pdev > insert into t1 (D1) (select 'Encrypt your data' from dual connect by level <= 10);
 13 10 rows created.
 14 RLOCKARD@pdev > insert into t1 (D1) (select 'Is this encrypted?' from dual connect by level <= 5);
 15 5 rows created.
 16 RLOCKARD@pdev > insert into t1 (D1) (select 'Practice Secure Computing' from dual connect by level <= 20);
 17 20 rows created.
 18 RLOCKARD@pdev > commit;
 19 Commit complete.
 20 
 21 -- GATHER STATISTICS ALONG WITH HISTOGRAMS.
 22 RLOCKARD@pdev > begin
 23   dbms_stats.gather_table_stats(null,'T1', method_opt=> 'for all columns size skewonly');
 24 end;
 25 /
 26 PL/SQL procedure successfully completed.
 27 -- THIS LOOKS GOOD
 28 RLOCKARD@pdev > select
 29         endpoint_number,
 30         endpoint_actual_value
 31 from dba_tab_histograms
 32 where owner = 'RLOCKARD'
 33   and table_name = 'T1'
 34   and column_name = 'D1';
 35   2    3    4    5    6    7
 36 ENDPOINT_NUMBER ENDPOINT_ACTUAL_VALUE
 37 --------------- --------------------------------
 38              10
 39              15
 40              35
 41 
 42 -- HOWEVER, WHEN WE DIG  A BIT FURTHER IT'S QUITE EASY TO
 43 -- TRANSLATE ENDPOINT_VALUE INTO THE FIRST CHARACTERS OF THE
 44 -- DATA THEREBY EXPOSING THE INFORMATION.
 45 -- NOTE THIS QUERY IS FROM Jonathan Lewis blog at: https://jonathanlewis.wordpress.com/category/oracle/statistics/histograms/
 46 
 47 
 48 RLOCKARD@pdev > ed
 49 Wrote file afiedt.buf
 50 
 51   1  select
 52   2          endpoint_number,
 53   3          endpoint_number - nvl(prev_endpoint,0) frequency,
 54   4          hex_val,
 55   5          chr(to_number(substr(hex_val, 2,2),'XX')) ||
 56   6          chr(to_number(substr(hex_val, 4,2),'XX')) ||
 57   7          chr(to_number(substr(hex_val, 6,2),'XX')) ||
 58   8          chr(to_number(substr(hex_val, 8,2),'XX')) ||
 59   9          chr(to_number(substr(hex_val,10,2),'XX')) ||
 60  10          chr(to_number(substr(hex_val,12,2),'XX')) ||
 61  11          chr(to_number(substr(hex_val,14,2),'XX')) ||
 62  12          chr(to_number(substr(hex_val,16,2),'XX')),
 63  13          endpoint_actual_value
 64  14  from    (
 65  15          select
 66  16                  endpoint_number,
 67  17                  lag(endpoint_number,1) over(
 68  18                          order by endpoint_number
 69  19 ) prev_endpoint,
 70  20 to_char(endpoint_value,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')hex_val,
 71  21                  endpoint_actual_value
 72  22          from
 73  23                  dba_tab_histograms
 74  24          WHERE
 75  25                  owner = 'RLOCKARD'
 76  26          AND     table_name = 'T1'
 77  27          and     column_name = 'D1'
 78  28          )
 79  29  order by
 80  30*         endpoint_number
 81  31  /
 82 
 83 ENDPOINT_NUMBER    FREQUENCY HEX_VAL                         CHR(TO_N ENDPOINT_ACTUAL_VALUE
 84 --------------- ------------ ------------------------------- -------- --------------------------------
 85              10           10 456E6372797079E93CBEA1A5000000 Encrypye
 86              15            5 49732074686967A04440BE12C00000 Is thig
 87              35           20 5072616374698217A0D44499800000 Practi?
 88 
 89 3 rows selected.
 90 
 91 
 92 
 93 
 94 TEST 2)
 95 Important note: THIS IS ALL PSEUDO DATA, NOTING IS REAL.
 96 
 97 -- the test customers table contains pseudo ssn's and cc numbers for demo purposes.
 98 -- reality is, because cc_nbr and ssn are distinct, histograms should not be gathered,
 99 -- however a "lazy" DBA may use the 'for all columns size skewonly' method_opt
100 -- therefore, by using the defaults you will get out 254 rows with data that should be encrypted.
101 
102 create table t3 as select id, fname, lname, city, state, cc_nbr, ssn from customers;
103 alter table t3 modify (cc_nbr encrypt using 'AES256', SSN encrypt using 'AES256');
104 
105 begin
106   dbms_stats.gather_table_stats(null,'T3', method_opt=> 'for all columns size skewonly');
107 end;
108 /
109 
110 desc t3
111 
112 RLOCKARD@pdev > desc t3
113  Name Null?    Type
114  ------------------------------------------------------------------------ -------- -------------------------------------------------
115  ID NOT NULL NUMBER
116  FNAME VARCHAR2(25)
117  LNAME VARCHAR2(25)
118  CITY VARCHAR2(25)
119  STATE VARCHAR2(25)
120  CC_NBR VARCHAR2(16) ENCRYPT
121  SSN VARCHAR2(11) ENCRYPT
122 
123 RLOCKARD@pdev >
124 
125 
126 select
127         endpoint_number,
128         endpoint_actual_value
129 from dba_tab_histograms
130 where owner = 'RLOCKARD'
131   and table_name = 'T3'
132   and column_name = 'SSN';
133 
134 RLOCKARD@pdev > l
135   1  select
136   2          endpoint_number,
137   3          endpoint_actual_value
138   4  from dba_tab_histograms
139   5  where owner = 'RLOCKARD'
140   6    and table_name = 'T3'
141   7*   and column_name = 'SSN'
142 RLOCKARD@pdev > /
143 
144 ENDPOINT_NUMBER ENDPOINT_ACTUAL_VALUE
145 --------------- --------------------------------
146            4247 778294598
147            4269 782777484
148            4291 785731383
149            4313 788768328
150            4335 792928354
151            4357 795685465
152            4379 798987731
153            4401 812732627
154            4424 815857391
155            4446 818188243
156 ========SNIP A LOT======
157 
158 RLOCKARD@pdev > SELECT * FROM T3 WHERE SSN='778294598';
159 
160         ID FNAME LNAME                     CITY STATE                     CC_NBR
161 ---------- ------------------------- ------------------------- ------------------------- ------------------------- ----------------
162 SSN
163 -----------
164      41742 Monica Gaestel                    Strattanville Pennsylvania              3483712444144721
165 778294598
166 
167 
168 1 row selected.

Posted in Database Stuff, Security | Tagged Encryption, infosec, OPSEC, oracle | Leave a comment

Oracle Transparent Data Encryption Performance. Oracle 12.1.0.2 on Linux 5

Posted on July 7, 2015 by rlockard

Some performance test on Oracle TDE. I have three tables t1, t2 and t3. All three tables have identical data and 1.6M rows. I’m going to come back and visit this a bit more later, but want to get some initial performance numbers out there.

 1 RLOCKARD@pdev > desc t1
 2  Name                                   Null?    Type
 3  -------------------------------------- -------- -----------------
 4  ID                                     NOT NULL NUMBER
 5  NBR                                             NUMBER
 6 
 7 RLOCKARD@pdev > desc t2
 8  Name                                   Null?    Type
 9  -------------------------------------- -------- -----------------
10  ID                                     NOT NULL NUMBER
11  NBR                                             NUMBER
12 
13 RLOCKARD@pdev > desc t3
14  Name                                   Null?    Type
15  -------------------------------------- -------- -----------------
16  ID                                     NOT NULL NUMBER
17  NBR                                    NOT NULL NUMBER ENCRYPT
18 
19 TABLE_NAME                TABLESPACE_NAME
20 ------------------------- ------------------------------
21 REGION                    USERS
22 T3                        USERS
23 T1                        DAT
24 PRODUCTS                  BIG_SENSITIVE
25 T2                        BIG_SENSITIVE
26 FNAMES                    USERS
27 ZIP                       USERS
28 STORES                    USERS
29 NETWORKS                  USERS
30 CUSTOMERS                 USERS
31 LNAMES                    USERS
32 ZIP_CODES                 USERS
33 SALES                     USERS
34 REGIONS                   USERS
35 STORE_USERS               USERS
36 
37 15 rows selected.
38 
39 RLOCKARD@pdev >
40

Table T1 resides in the DAT tablespace that is not encrypted. Selecting sum(nbr) from t1 where nbr between 40 and 45 executed in .27 seconds. here there are a bunch of direct path reads followed by 188182 microseconds

 1 RLOCKARD@pdev > @no_encrypt
 2 RLOCKARD@pdev > set timing on
 3 RLOCKARD@pdev > alter session set timed_statistics = true;
 4 
 5 Session altered.
 6 
 7 Elapsed: 00:00:00.03
 8 RLOCKARD@pdev > alter session set statistics_level = all ;
 9 
10 Session altered.
11 
12 Elapsed: 00:00:00.05
13 RLOCKARD@pdev > alter session set sql_trace = true ;
14 
15 Session altered.
16 
17 Elapsed: 00:00:00.06
18 RLOCKARD@pdev > alter session set max_dump_file_size=unlimited;
19 
20 Session altered.
21 
22 Elapsed: 00:00:00.02
23 RLOCKARD@pdev > alter session set tracefile_identifier = owi_trace;
24 
25 Session altered.
26 
27 Elapsed: 00:00:00.00
28 RLOCKARD@pdev >
29 RLOCKARD@pdev > BEGIN
30   2    dbms_monitor.session_trace_enable (waits=>true);
31   3  END;
32   4  /
33 
34 PL/SQL procedure successfully completed.
35 
36 Elapsed: 00:00:00.27
37 RLOCKARD@pdev > select sum(nbr) from t1 where nbr between 40 and 45;
38 
39   SUM(NBR)
40 ----------
41    4055760
42 
43 1 row selected.
44 
45 Elapsed: 00:00:00.27
46 RLOCKARD@pdev >
47 RLOCKARD@pdev > BEGIN
48   2    dbms_monitor.session_trace_disable;
49   3  END;
50   4  /
51 
52 PL/SQL procedure successfully completed.
53 
54 Elapsed: 00:00:00.01
55

 1 WAIT #46912559622408: nam='direct path read' ela= 42 file number=78 first dba=3144 block cnt=8 obj#=92336 tim=1436288730005400
 2 WAIT #46912559622408: nam='direct path read' ela= 45 file number=78 first dba=3152 block cnt=8 obj#=92336 tim=1436288730005873
 3 WAIT #46912559622408: nam='direct path read' ela= 43 file number=78 first dba=3160 block cnt=8 obj#=92336 tim=1436288730006338
 4 WAIT #46912559622408: nam='direct path read' ela= 44 file number=78 first dba=3168 block cnt=8 obj#=92336 tim=1436288730006835
 5 WAIT #46912559622408: nam='direct path read' ela= 41 file number=78 first dba=3176 block cnt=8 obj#=92336 tim=1436288730007325
 6 WAIT #46912559622408: nam='direct path read' ela= 45 file number=78 first dba=3184 block cnt=8 obj#=92336 tim=1436288730007807
 7 WAIT #46912559622408: nam='direct path read' ela= 42 file number=78 first dba=3192 block cnt=8 obj#=92336 tim=1436288730008267
 8 WAIT #46912559622408: nam='direct path read' ela= 38 file number=78 first dba=3202 block cnt=6 obj#=92336 tim=1436288730008762
 9 WAIT #46912559622408: nam='direct path read' ela= 43 file number=78 first dba=3208 block cnt=8 obj#=92336 tim=1436288730009241
10 WAIT #46912559622408: nam='direct path read' ela= 45 file number=78 first dba=3216 block cnt=8 obj#=92336 tim=1436288730009633
11 WAIT #46912559622408: nam='direct path read' ela= 41 file number=78 first dba=3224 block cnt=8 obj#=92336 tim=1436288730010093
12 WAIT #46912559622408: nam='direct path read' ela= 73 file number=78 first dba=3232 block cnt=8 obj#=92336 tim=1436288730010585
13 WAIT #46912559622408: nam='direct path read' ela= 44 file number=78 first dba=3240 block cnt=8 obj#=92336 tim=1436288730011082
14 WAIT #46912559622408: nam='direct path read' ela= 43 file number=78 first dba=3248 block cnt=8 obj#=92336 tim=1436288730011545
15 WAIT #46912559622408: nam='direct path read' ela= 44 file number=78 first dba=3256 block cnt=8 obj#=92336 tim=1436288730012024
16 WAIT #46912559622408: nam='direct path read' ela= 45 file number=78 first dba=3296 block cnt=8 obj#=92336 tim=1436288730012491
17 WAIT #46912559622408: nam='direct path read' ela= 44 file number=78 first dba=3304 block cnt=8 obj#=92336 tim=1436288730012992
18 WAIT #46912559622408: nam='direct path read' ela= 41 file number=78 first dba=3312 block cnt=8 obj#=92336 tim=1436288730013200
19 WAIT #46912559622408: nam='direct path read' ela= 40 file number=78 first dba=3320 block cnt=8 obj#=92336 tim=1436288730013423
20 FETCH #46912559622408:c=0,e=188182,p=3113,cr=6199,cu=0,mis=0,r=1,dep=0,og=1,plh=3724264953,tim=1436288730013681
21 STAT #46912559622408 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=6199 pr=3113 pw=0 time=188179 us)'
22 STAT #46912559622408 id=2 cnt=95344 pid=1 pos=1 obj=92336 op='TABLE ACCESS FULL T1 (cr=6199 pr=3113 pw=0 time=180339 us cost=827 size=95049 card=31683)'
23 WAIT #46912559622408: nam='SQL*Net message from client' ela= 317 driver id=1413697536 #bytes=1 p3=0 obj#=92336 tim=1436288730014177
24 FETCH #46912559622408:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3724264953,tim=1436288730014283
25 WAIT #46912559622408: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=92336 tim=1436288730014321
26 WAIT #46912559622408: nam='SQL*Net message from client' ela= 760 driver id=1413697536 #bytes=1 p3=0 obj#=92336 tim=1436288730015101
27 CLOSE #46912559622408:c=0,e=18,dep=0,type=0,tim=1436288730015216
28

Now table T2 resides in an encrypted tablespace. Lets run that same query and see what happens. Okay, the execution time went up to 1.67 seconds. Here on the FETCH line we see 1567559 microseconds of CPU time to decrypt the data after a bunch of direct path reads.

 1 WAIT #46912560638840: nam='direct path read' ela= 48 file number=77 first dba=4928 block cnt=8 obj#=92341 tim=1436288983345072
 2 WAIT #46912560638840: nam='direct path read' ela= 44 file number=77 first dba=4936 block cnt=8 obj#=92341 tim=1436288983349077
 3 WAIT #46912560638840: nam='direct path read' ela= 47 file number=77 first dba=4944 block cnt=8 obj#=92341 tim=1436288983353083
 4 WAIT #46912560638840: nam='direct path read' ela= 49 file number=77 first dba=4952 block cnt=8 obj#=92341 tim=1436288983357078
 5 WAIT #46912560638840: nam='direct path read' ela= 48 file number=77 first dba=4960 block cnt=8 obj#=92341 tim=1436288983361086
 6 WAIT #46912560638840: nam='direct path read' ela= 50 file number=77 first dba=4968 block cnt=8 obj#=92341 tim=1436288983365107
 7 WAIT #46912560638840: nam='direct path read' ela= 52 file number=77 first dba=4976 block cnt=8 obj#=92341 tim=1436288983369245
 8 WAIT #46912560638840: nam='direct path read' ela= 47 file number=77 first dba=4984 block cnt=8 obj#=92341 tim=1436288983373257
 9 WAIT #46912560638840: nam='direct path read' ela= 39 file number=77 first dba=4994 block cnt=6 obj#=92341 tim=1436288983377270
10 WAIT #46912560638840: nam='direct path read' ela= 48 file number=77 first dba=5000 block cnt=8 obj#=92341 tim=1436288983381286
11 WAIT #46912560638840: nam='direct path read' ela= 48 file number=77 first dba=5008 block cnt=8 obj#=92341 tim=1436288983384340
12 WAIT #46912560638840: nam='direct path read' ela= 48 file number=77 first dba=5016 block cnt=8 obj#=92341 tim=1436288983388373
13 WAIT #46912560638840: nam='direct path read' ela= 49 file number=77 first dba=5024 block cnt=8 obj#=92341 tim=1436288983392414
14 WAIT #46912560638840: nam='direct path read' ela= 47 file number=77 first dba=5032 block cnt=8 obj#=92341 tim=1436288983396450
15 WAIT #46912560638840: nam='direct path read' ela= 47 file number=77 first dba=5040 block cnt=8 obj#=92341 tim=1436288983400460
16 WAIT #46912560638840: nam='direct path read' ela= 46 file number=77 first dba=5048 block cnt=8 obj#=92341 tim=1436288983404468
17 WAIT #46912560638840: nam='direct path read' ela= 45 file number=77 first dba=5056 block cnt=8 obj#=92341 tim=1436288983408470
18 WAIT #46912560638840: nam='direct path read' ela= 49 file number=77 first dba=5064 block cnt=8 obj#=92341 tim=1436288983412478
19 WAIT #46912560638840: nam='direct path read' ela= 17 file number=77 first dba=5072 block cnt=1 obj#=92341 tim=1436288983416457
20 FETCH #46912560638840:c=0,e=1567559,p=3095,cr=3099,cu=0,mis=0,r=1,dep=0,og=1,plh=3321871023,tim=1436288983420903
21 STAT #46912560638840 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=3099 pr=3095 pw=0 time=1567554 us)'
22 STAT #46912560638840 id=2 cnt=95344 pid=1 pos=1 obj=92341 op='TABLE ACCESS FULL T2 (cr=3099 pr=3095 pw=0 time=1559639 us cost=827 size=95049 card=31683)'
23 WAIT #46912560638840: nam='SQL*Net message from client' ela= 320 driver id=1413697536 #bytes=1 p3=0 obj#=92341 tim=1436288983421412
24 FETCH #46912560638840:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3321871023,tim=1436288983421496
25 WAIT #46912560638840: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=92341 tim=1436288983421533
26 WAIT #46912560638840: nam='SQL*Net message from client' ela= 648 driver id=1413697536 #bytes=1 p3=0 obj#=92341 tim=1436288983422201
27 CLOSE #46912560638840:c=0,e=18,dep=0,type=0,tim=1436288983422305
28

 1 RLOCKARD@pdev > @tablespace_encryption.sql
 2 RLOCKARD@pdev > set timing on
 3 RLOCKARD@pdev >
 4 RLOCKARD@pdev > alter session set timed_statistics = true;
 5 
 6 Session altered.
 7 
 8 Elapsed: 00:00:00.00
 9 RLOCKARD@pdev > alter session set statistics_level = all ;
10 
11 Session altered.
12 
13 Elapsed: 00:00:00.00
14 RLOCKARD@pdev > alter session set sql_trace = true ;
15 
16 Session altered.
17 
18 Elapsed: 00:00:00.01
19 RLOCKARD@pdev > alter session set max_dump_file_size=unlimited;
20 
21 Session altered.
22 
23 Elapsed: 00:00:00.00
24 RLOCKARD@pdev > alter session set tracefile_identifier = owi_trace;
25 
26 Session altered.
27 
28 Elapsed: 00:00:00.00
29 RLOCKARD@pdev >
30 RLOCKARD@pdev > BEGIN
31   2    dbms_monitor.session_trace_enable (waits=>true);
32   3  END;
33   4  /
34 
35 PL/SQL procedure successfully completed.
36 
37 Elapsed: 00:00:00.00
38 RLOCKARD@pdev > select sum(nbr) from t2 where nbr between 40 and 45;
39 
40   SUM(NBR)
41 ----------
42    4055760
43 
44 1 row selected.
45 
46 Elapsed: 00:00:01.67
47 RLOCKARD@pdev >
48 RLOCKARD@pdev > BEGIN
49   2    dbms_monitor.session_trace_disable;
50   3  END;
51   4  /
52 
53 PL/SQL procedure successfully completed.
54 
55 Elapsed: 00:00:00.00
56 RLOCKARD@pdev >
57

Table T3 resides in the DAT tablespace and has the nbr column encrypted. Execution time 19.86 seconds.

 1 RLOCKARD@pdev > @column_encryption
 2 RLOCKARD@pdev > set timing on
 3 RLOCKARD@pdev > alter session set timed_statistics = true;
 4 
 5 Session altered.
 6 
 7 Elapsed: 00:00:00.00
 8 RLOCKARD@pdev > alter session set statistics_level = all ;
 9 
10 Session altered.
11 
12 Elapsed: 00:00:00.00
13 RLOCKARD@pdev > alter session set sql_trace = true ;
14 
15 Session altered.
16 
17 Elapsed: 00:00:00.01
18 RLOCKARD@pdev > alter session set max_dump_file_size=unlimited;
19 
20 Session altered.
21 
22 Elapsed: 00:00:00.00
23 RLOCKARD@pdev > alter session set tracefile_identifier = owi_trace;
24 
25 Session altered.
26 
27 Elapsed: 00:00:00.00
28 RLOCKARD@pdev >
29 RLOCKARD@pdev > BEGIN
30   2    dbms_monitor.session_trace_enable (waits=>true);
31   3  END;
32   4  /
33 
34 PL/SQL procedure successfully completed.
35 
36 Elapsed: 00:00:00.00
37 RLOCKARD@pdev > select sum(nbr) from t3 where nbr between 40 and 45;
38 
39   SUM(NBR)
40 ----------
41    4055760
42 
43 1 row selected.
44 
45 Elapsed: 00:00:19.86
46 RLOCKARD@pdev >
47 RLOCKARD@pdev > BEGIN
48   2    dbms_monitor.session_trace_disable;
49   3  END;
50   4  /
51 
52 PL/SQL procedure successfully completed.
53 
54 Elapsed: 00:00:00.00
55 RLOCKARD@pdev >
56 RLOCKARD@pdev >
57

Looking at the 10046 trace file, we see a large number of “direct path read’ then FETCH c=0, e=19848382. c is the CPU used and e is the elapsed time in micro seconds. So, after reading all the data into the SGA, the CPU spent 19.848382 seconds to decrypt the data.

 1 WAIT #46912556873704: nam='direct path read' ela= 47 file number=10 first dba=17536 block cnt=8 obj#=92434 tim=1436288197076658
 2 WAIT #46912556873704: nam='direct path read' ela= 46 file number=10 first dba=17544 block cnt=8 obj#=92434 tim=1436288197087637
 3 WAIT #46912556873704: nam='direct path read' ela= 45 file number=10 first dba=17552 block cnt=8 obj#=92434 tim=1436288197098738
 4 WAIT #46912556873704: nam='direct path read' ela= 43 file number=10 first dba=17560 block cnt=8 obj#=92434 tim=1436288197109734
 5 WAIT #46912556873704: nam='direct path read' ela= 48 file number=10 first dba=17568 block cnt=8 obj#=92434 tim=1436288197120785
 6 WAIT #46912556873704: nam='direct path read' ela= 45 file number=10 first dba=17576 block cnt=8 obj#=92434 tim=1436288197131809
 7 WAIT #46912556873704: nam='direct path read' ela= 45 file number=10 first dba=17584 block cnt=8 obj#=92434 tim=1436288197142990
 8 WAIT #46912556873704: nam='direct path read' ela= 45 file number=10 first dba=17592 block cnt=8 obj#=92434 tim=1436288197154216
 9 WAIT #46912556873704: nam='direct path read' ela= 67 file number=10 first dba=17600 block cnt=8 obj#=92434 tim=1436288197167921
10 WAIT #46912556873704: nam='direct path read' ela= 59 file number=10 first dba=17608 block cnt=8 obj#=92434 tim=1436288197179630
11 WAIT #46912556873704: nam='direct path read' ela= 46 file number=10 first dba=17616 block cnt=8 obj#=92434 tim=1436288197190774
12 WAIT #46912556873704: nam='direct path read' ela= 56 file number=10 first dba=17624 block cnt=8 obj#=92434 tim=1436288197201914
13 WAIT #46912556873704: nam='direct path read' ela= 44 file number=10 first dba=17632 block cnt=8 obj#=92434 tim=1436288197213080
14 WAIT #46912556873704: nam='direct path read' ela= 47 file number=10 first dba=17640 block cnt=8 obj#=92434 tim=1436288197224429
15 WAIT #46912556873704: nam='direct path read' ela= 44 file number=10 first dba=17648 block cnt=8 obj#=92434 tim=1436288197235618
16 WAIT #46912556873704: nam='direct path read' ela= 56 file number=10 first dba=17656 block cnt=8 obj#=92434 tim=1436288197247424
17 FETCH #46912556873704:c=0,e=19848382,p=14178,cr=14181,cu=0,mis=0,r=1,dep=0,og=1,plh=463314188,tim=1436288197269519
18 STAT #46912556873704 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=14181 pr=14178 pw=0 time=19848378 us)'
19 STAT #46912556873704 id=2 cnt=95344 pid=1 pos=1 obj=92434 op='TABLE ACCESS FULL T3 (cr=14181 pr=14178 pw=0 time=19110498 us cost=3863 size=212000 card=4000)'
20 WAIT #46912556873704: nam='SQL*Net message from client' ela= 296 driver id=1413697536 #bytes=1 p3=0 obj#=92434 tim=1436288197270054
21 FETCH #46912556873704:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=463314188,tim=1436288197270136
22 WAIT #46912556873704: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=92434 tim=1436288197270200
23 WAIT #46912556873704: nam='SQL*Net message from client' ela= 584 driver id=1413697536 #bytes=1 p3=0 obj#=92434 tim=1436288197270805
24 CLOSE #46912556873704:c=0,e=17,dep=0,type=0,tim=1436288197270917
25

Posted in Database Stuff | Tagged Encryption, infosec, oracle, tde | Leave a comment

#Oracle #In-Memory

Posted on April 13, 2015 by rlockard

I started to play with in-memory to see what performance improvements I can get. I set up four tables, zip, customers, products and sales and executed a simple queries against them.

Sales has 3M rows, and customers has 110K rows, zip 4271 rows and products 6 rows.

SELECT p.name, sum(s.price),count(*)
FROM customers c,
       products p,
       sales s,
       zip_codes z
WHERE c.ID = s.cust_id
    AND p.ID = s.product_id
    AND c.zip = z.zip
GROUP BY p.NAME;

Run 1) using a default install of Oracle 12.1.0.2. Execution time 17.21 seconds

 1 SQL> sho parameter inmemory
 2 
 3 NAME                                 TYPE        VALUE
 4 ------------------------------------ ----------- ------------------------------
 5 inmemory_clause_default              string
 6 inmemory_force                       string      DEFAULT
 7 inmemory_max_populate_servers        integer     0
 8 inmemory_query                       string      ENABLE
 9 inmemory_size                        big integer 0
10 inmemory_trickle_repopulate_servers_ integer     1
11 percent
12 optimizer_inmemory_aware             boolean     TRUE
13 
14 
15 SQL> l
16   1  SELECT p.name, sum(s.price),count(*)
17   2  FROM customers c,
18   3       products p,
19   4       sales s,
20   5       zip_codes z
21   6  WHERE c.ID = s.cust_id
22   7    AND p.ID = s.product_id
23   8    AND c.zip = z.zip
24   9* GROUP BY p.NAME
25 SQL> /
26 
27 Elapsed: 00:00:17.21
28 
29 Execution Plan
30 ----------------------------------------------------------
31 Plan hash value: 1738379704
32 
33 ----------------------------------------------------------------------------------------------
34 | Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
35 ----------------------------------------------------------------------------------------------
36 |   0 | SELECT STATEMENT              |              |     1 |    47 |  3118   (1)| 00:00:01 |
37 |   1 |  HASH GROUP BY                |              |     1 |    47 |  3118   (1)| 00:00:01 |
38 |   2 |   NESTED LOOPS                |              |     1 |    47 |  3117   (1)| 00:00:01 |
39 |   3 |    NESTED LOOPS               |              |     1 |    47 |  3117   (1)| 00:00:01 |
40 |*  4 |     HASH JOIN                 |              |     1 |    29 |  3116   (1)| 00:00:01 |
41 |*  5 |      HASH JOIN                |              |     1 |    16 |   353   (1)| 00:00:01 |
42 |   6 |       TABLE ACCESS FULL       | ZIP          |  4271 | 21355 |    11   (0)| 00:00:01 |
43 |   7 |       TABLE ACCESS FULL       | CUSTOMERS    |   110K|  1181K|   342   (1)| 00:00:01 |
44 |   8 |      TABLE ACCESS FULL        | SALES        |  3142K|    38M|  2755   (1)| 00:00:01 |
45 |*  9 |     INDEX UNIQUE SCAN         | SYS_C0011426 |     1 |       |     0   (0)| 00:00:01 |
46 |  10 |    TABLE ACCESS BY INDEX ROWID| PRODUCTS     |     1 |    18 |     1   (0)| 00:00:01 |
47 ----------------------------------------------------------------------------------------------
48 
49 Predicate Information (identified by operation id):
50 ---------------------------------------------------
51 
52    4 - access("C"."ID"="S"."CUST_ID")
53    5 - access("C"."ZIP"="Z"."ZIP")
54    9 - access("P"."ID"="S"."PRODUCT_ID")
55 
56 
57 Statistics
58 ----------------------------------------------------------
59        2313  recursive calls
60           0  db block gets
61     3155556  consistent gets
62       16351  physical reads
63       34844  redo size
64         882  bytes sent via SQL*Net to client
65         551  bytes received via SQL*Net from client
66           2  SQL*Net roundtrips to/from client
67         179  sorts (memory)
68           0  sorts (disk)
69           5  rows processed

So we turn on In-Memory by setting inmemory_size to a value > 100M. (100M is the smallest value you can use) We then add the sales and customers table to the In-Memory column store using the alter table <tablename> inmemory statement. Once I made the change I did a stop / start of the instance and executed the query again. Oh, I did one more thing to load the column store, select * from sales; and select * from customers; If you want to load the column store when the database instance starts up then use the alter statement alter table <tablename> inmemory priority critical; (there are five levels of priority critical, high, medium, low and none. (none is the default)

Run 2) Execution time 10.73 seconds. Yea, that’s all I did and it’s that simple. Now imagine the performance improvement if I actually tried!

 1 SQL> alter system set INMEMORY_SIZE=500M scope=spfile;
 2 
 3 System altered.
 4 
 5 SQL> alter table sales inmemory;
 6 
 7 Table altered.
 8 
 9 SQL> alter table customers inmemory;
10 
11 Table altered.
12 
13 SQL>
14

 1 SQL> sho parameter inmemory
 2 
 3 NAME                                 TYPE        VALUE
 4 ------------------------------------ ----------- ------------------------------
 5 inmemory_clause_default              string
 6 inmemory_force                       string      DEFAULT
 7 inmemory_max_populate_servers        integer     1
 8 inmemory_query                       string      ENABLE
 9 inmemory_size                        big integer 500M
10 inmemory_trickle_repopulate_servers_ integer     1
11 percent
12 optimizer_inmemory_aware             boolean     TRUE
13 SQL>
14 
15 
16 SQL> set timing on
17 SQL> SELECT p.name, sum(s.price),count(*)
18 FROM customers c,
19      products p,
20      sales s,
21      zip z
22 WHERE c.ID = s.cust_id
23   AND p.ID = s.product_id
24   AND c.zip = z.zip
25 GROUP BY p.NAME
26 /
27   2    3    4    5    6    7    8    9   10
28 
29 Elapsed: 00:00:10.73
30 
31 Execution Plan
32 ----------------------------------------------------------
33 Plan hash value: 1408061610
34 
35 -------------------------------------------------------------------------------------------
36 | Id  | Operation                     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
37 -------------------------------------------------------------------------------------------
38 |   0 | SELECT STATEMENT              |           |     1 |    47 |   215  (17)| 00:00:01 |
39 |   1 |  HASH GROUP BY                |           |     1 |    47 |   215  (17)| 00:00:01 |
40 |*  2 |   HASH JOIN                   |           |     1 |    47 |   214  (17)| 00:00:01 |
41 |*  3 |    HASH JOIN                  |           |     1 |    34 |    76   (2)| 00:00:01 |
42 |   4 |     MERGE JOIN CARTESIAN      |           | 25626 |   575K|    62   (0)| 00:00:01 |
43 |   5 |      TABLE ACCESS FULL        | PRODUCTS  |     6 |   108 |     3   (0)| 00:00:01 |
44 |   6 |      BUFFER SORT              |           |  4271 | 21355 |    59   (0)| 00:00:01 |
45 |   7 |       TABLE ACCESS FULL       | ZIP       |  4271 | 21355 |    10   (0)| 00:00:01 |
46 |   8 |     TABLE ACCESS INMEMORY FULL| CUSTOMERS |   110K|  1181K|    14   (8)| 00:00:01 |
47 |   9 |    TABLE ACCESS INMEMORY FULL | SALES     |  3142K|    38M|   130  (20)| 00:00:01 |
48 -------------------------------------------------------------------------------------------
49 
50 Predicate Information (identified by operation id):
51 ---------------------------------------------------
52 
53    2 - access("C"."ID"="S"."CUST_ID" AND "P"."ID"="S"."PRODUCT_ID")
54    3 - access("C"."ZIP"="Z"."ZIP")
55 
56 Note
57 -----
58    - dynamic statistics used: dynamic sampling (level=2)
59    - 1 Sql Plan Directive used for this statement
60 
61 
62 Statistics
63 ----------------------------------------------------------
64        3894  recursive calls
65           0  db block gets
66        9231  consistent gets
67       14236  physical reads
68           0  redo size
69         882  bytes sent via SQL*Net to client
70         551  bytes received via SQL*Net from client
71           2  SQL*Net roundtrips to/from client
72         526  sorts (memory)
73           0  sorts (disk)
74           5  rows processed
75 
76 SQL>

There will be more to come, but if you want to read more about Oracle in-memory there is an excellent white paper at http://www.oracle.com/technetwork/database/in-memory/overview/twp-oracle-database-in-memory-2245633.html

Okay, I looked at the memory usage and the size of my tables I loaded into the column store and made a change. The customers table is over 1G, therefore I changed the inmemory_size to 2G. Let’s see what that does for my performance.

 1 SQL> alter system set inmemory_size=2G scope=spfile;
 2 
 3 System altered.
 4 
 5 SQL> shutdown immediate
 6 Database closed.
 7 Database dismounted.
 8 ORACLE instance shut down.
 9 SQL> startup
10 ORACLE instance started.
11 
12 Total System Global Area 2449473536 bytes
13 Fixed Size                  2948320 bytes
14 Variable Size             243270432 bytes
15 Database Buffers           50331648 bytes
16 Redo Buffers                5439488 bytes
17 In-Memory Area           2147483648 bytes
18 Database mounted.
19 Database opened.
20 SQL>
21

Okay, 6.02 seconds. Nice try but I think can do better. Physical reads went from 16,351 without in-memory to 14,236 with 500M used for in-memory and down to 1,084 when I sized in-memory appropriately.

 1 SQL> @qry1
 2 
 3 NAME                                SUM(S.PRICE)   COUNT(*)
 4 ----------------------------------- ------------ ----------
 5 Posidon Odin                           527301055     627150
 6 Scuba Pro G500 Regulator               453549388     628716
 7 Stop Stink                            1874278.96     627888
 8 Scuba Fins                            75210608.1     629888
 9 Scuba Pro Mask                        46894525.5     628395
10 
11 Elapsed: 00:00:06.02
12 
13 Execution Plan
14 ----------------------------------------------------------
15 Plan hash value: 1408061610
16 
17 -------------------------------------------------------------------------------------------
18 | Id  | Operation                     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
19 -------------------------------------------------------------------------------------------
20 |   0 | SELECT STATEMENT              |           |     1 |    47 |   215  (17)| 00:00:01 |
21 |   1 |  HASH GROUP BY                |           |     1 |    47 |   215  (17)| 00:00:01 |
22 |*  2 |   HASH JOIN                   |           |     1 |    47 |   214  (17)| 00:00:01 |
23 |*  3 |    HASH JOIN                  |           |     1 |    34 |    76   (2)| 00:00:01 |
24 |   4 |     MERGE JOIN CARTESIAN      |           | 25626 |   575K|    62   (0)| 00:00:01 |
25 |   5 |      TABLE ACCESS FULL        | PRODUCTS  |     6 |   108 |     3   (0)| 00:00:01 |
26 |   6 |      BUFFER SORT              |           |  4271 | 21355 |    59   (0)| 00:00:01 |
27 |   7 |       TABLE ACCESS FULL       | ZIP       |  4271 | 21355 |    10   (0)| 00:00:01 |
28 |   8 |     TABLE ACCESS INMEMORY FULL| CUSTOMERS |   110K|  1181K|    14   (8)| 00:00:01 |
29 |   9 |    TABLE ACCESS INMEMORY FULL | SALES     |  3142K|    38M|   130  (20)| 00:00:01 |
30 -------------------------------------------------------------------------------------------
31 
32 Predicate Information (identified by operation id):
33 ---------------------------------------------------
34 
35    2 - access("C"."ID"="S"."CUST_ID" AND "P"."ID"="S"."PRODUCT_ID")
36    3 - access("C"."ZIP"="Z"."ZIP")
37 
38 Note
39 -----
40    - dynamic statistics used: dynamic sampling (level=2)
41    - 1 Sql Plan Directive used for this statement
42 
43 
44 Statistics
45 ----------------------------------------------------------
46        3793  recursive calls
47           0  db block gets
48        9239  consistent gets
49        1084  physical reads
50           0  redo size
51         882  bytes sent via SQL*Net to client
52         551  bytes received via SQL*Net from client
53           2  SQL*Net roundtrips to/from client
54         491  sorts (memory)
55           0  sorts (disk)
56           5  rows processed
57 
58 SQL>
59

Okay, I decided to do a little better. I added three indexes to support the foreign key relationships and see what that does. 3.79 seconds, 44 physical reads. My work here is done.

 1 SQL> create index sales_cust_id on sales(cust_id);
 2 
 3 Index created.
 4 
 5 Elapsed: 00:00:17.77
 6 SQL> create index product_id on sales(product_id);
 7 
 8 Index created.
 9 
10 1* create index customers_zip on customers(zip)
11 SQL> /
12 
13 Index created.
14 
15 Elapsed: 00:00:00.58
16 
17 Elapsed: 00:00:24.23
18 SQL> exec dbms_stats.gather_schema_stats(user);
19 
20 PL/SQL procedure successfully completed.
21 
22 Elapsed: 00:00:22.57
23 
24 SQL> @qry1
25 
26 NAME                                SUM(S.PRICE)   COUNT(*)
27 ----------------------------------- ------------ ----------
28 Posidon Odin                           527301055     627150
29 Scuba Pro G500 Regulator               453549388     628716
30 Scuba Fins                            75210608.1     629888
31 Scuba Pro Mask                        46894525.5     628395
32 Stop Stink                            1874278.96     627888
33 
34 Elapsed: 00:00:03.79
35 
36 Execution Plan
37 ----------------------------------------------------------
38 Plan hash value: 463445694
39 
40 ----------------------------------------------------------------------------------------------------------
41 | Id  | Operation                         | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
42 ----------------------------------------------------------------------------------------------------------
43 |   0 | SELECT STATEMENT                  |              |     5 |   235 |       |  4081   (4)| 00:00:01 |
44 |   1 |  HASH GROUP BY                    |              |     5 |   235 |       |  4081   (4)| 00:00:01 |
45 |   2 |   MERGE JOIN                      |              |     5 |   235 |       |  4080   (4)| 00:00:01 |
46 |   3 |    TABLE ACCESS BY INDEX ROWID    | PRODUCTS     |     6 |   108 |       |     2   (0)| 00:00:01 |
47 |   4 |     INDEX FULL SCAN               | SYS_C0011426 |     6 |       |       |     1   (0)| 00:00:01 |
48 |*  5 |    SORT JOIN                      |              |     5 |   145 |       |  4078   (4)| 00:00:01 |
49 |   6 |     VIEW                          | VW_GBC_13    |     5 |   145 |       |  4077   (4)| 00:00:01 |
50 |   7 |      HASH GROUP BY                |              |     5 |   140 |       |  4077   (4)| 00:00:01 |
51 |*  8 |       HASH JOIN                   |              |  3102K|    82M|       |  4000   (2)| 00:00:01 |
52 |   9 |        INDEX FAST FULL SCAN       | ZIP_ZIP      |  4271 | 21355 |       |     4   (0)| 00:00:01 |
53 |* 10 |        HASH JOIN                  |              |  3102K|    68M|  2368K|  3988   (2)| 00:00:01 |
54 |  11 |         TABLE ACCESS INMEMORY FULL| CUSTOMERS    |   110K|  1074K|       |    14   (8)| 00:00:01 |
55 |  12 |         TABLE ACCESS INMEMORY FULL| SALES        |  3142K|    38M|       |   130  (20)| 00:00:01 |
56 ----------------------------------------------------------------------------------------------------------
57 
58 Predicate Information (identified by operation id):
59 ---------------------------------------------------
60 
61    5 - access("P"."ID"="ITEM_1")
62        filter("P"."ID"="ITEM_1")
63    8 - access("C"."ZIP"="Z"."ZIP")
64   10 - access("C"."ID"="S"."CUST_ID")
65 
66 Note
67 -----
68    - dynamic statistics used: dynamic sampling (level=2)
69    - 1 Sql Plan Directive used for this statement
70 
71 
72 Statistics
73 ----------------------------------------------------------
74         139  recursive calls
75           0  db block gets
76         189  consistent gets
77          44  physical reads
78           0  redo size
79         882  bytes sent via SQL*Net to client
80         551  bytes received via SQL*Net from client
81           2  SQL*Net roundtrips to/from client
82          13  sorts (memory)
83           0  sorts (disk)
84           5  rows processed
85 
86 SQL>
87

Posted in Database Stuff | Tagged Analytic, In-Memory, Performance | Leave a comment

OTN Two Minute Tech Tip – SYS_CONTEXT to help secure your database

Posted on April 1, 2015 by rlockard

My Two minute tech tip with OTN on using sys_context to help you secure your database. We can use the values returned from sys_context along with Virtual Private Databases, Fine Grained Auditing, and Redaction to lock down the sensitive data.

Posted in Database Stuff, Security | Leave a comment

#infosec #encrypt backups with #rman

Posted on March 25, 2015 by rlockard

Why should you encrypt your backups?
Why should you test your backups?

I will be speaking on Holistic Database Security at #kscope15 and #harmony15

Posted in Database Stuff, Security | Tagged infosec, oracle, rman | Leave a comment

#infosec #Oracle data #redaction

Posted on March 23, 2015 by rlockard

I had a rather disturbing conversion with my wife a couple nights ago, she had called a service provider to discuss a bill. The customer service rep asked her for her social security number to verify her identity. When my wife told the customer service rep she was not comfortable giving out that information, the customer service rep told her that he has her full social security number on the screen in front of him. WTF. This is a large corporation, and I’m wondering if they are ignoring the problem until there is a leak and they get embarrassed because after all it cost money to protect data.

Do you store sensitive information? Does you’re user base have access to that information? Does you’re user base need to see that sensitive information? I have always operated on the principle of “need to know.” This simply states, does a person need to know something in order to do their job? If the answer to that is no, then don’t give them the information.

In the customers table we are storing two very sensitive pieces of information, SSN and Credit Card Number. Now sit back and think, what would happen to your company / job / reputation if this information were to leak?

 1 SQL> desc customers
 2  Name                                      Null?    Type
 3  ----------------------------------------- -------- ----------------------------
 4  ID                                        NOT NULL NUMBER
 5  SSN                                                VARCHAR2(25)
 6  FNAME                                              VARCHAR2(55)
 7  LNAME                                              VARCHAR2(55)
 8  UPDATE_DATE                                        DATE
 9  CREDIT_CARD_NBR                                    VARCHAR2(19)
10  CREDIT_LIMIT                                       NUMBER
11 
12

Before we start, lets populate customers.ssn and customers.credit_card_nbr with random data just to make sure there is no live data is involved in this demonstration.

 1 SQL> @redaction_demo
 2 SQL> conn rlockard@pdev
 3 Enter password:
 4 Connected.
 5 SQL>
 6 SQL> update customers set ssn =
 7   2         ceil(dbms_random.value(0,9)) ||
 8   3         ceil(dbms_random.value(0,9)) ||
 9   4         ceil(dbms_random.value(0,9)) || '-' ||
10   5         ceil(dbms_random.value(0,9)) ||
11   6         ceil(dbms_random.value(0,9)) || '-' ||
12   7         ceil(dbms_random.value(0,9)) ||
13   8         ceil(dbms_random.value(0,9)) ||
14   9         ceil(dbms_random.value(0,9)) ||
15  10         ceil(dbms_random.value(0,9)),
16  11         credit_card_nbr =
17  12         ceil(dbms_random.value(0,9)) ||
18  13         ceil(dbms_random.value(0,9)) ||
19  14         ceil(dbms_random.value(0,9)) ||
20  15         ceil(dbms_random.value(0,9)) || ' ' ||
21  16         ceil(dbms_random.value(0,9)) ||
22  17         ceil(dbms_random.value(0,9)) ||
23  18         ceil(dbms_random.value(0,9)) ||
24  19         ceil(dbms_random.value(0,9)) || ' ' ||
25  20         ceil(dbms_random.value(0,9)) ||
26  21         ceil(dbms_random.value(0,9)) ||
27  22         ceil(dbms_random.value(0,9)) ||
28  23         ceil(dbms_random.value(0,9)) || ' ' ||
29  24         ceil(dbms_random.value(0,9)) ||
30  25         ceil(dbms_random.value(0,9)) ||
31  26         ceil(dbms_random.value(0,9)) ||
32  27         ceil(dbms_random.value(0,9));
33 
34 1666 rows updated.
35 
36 SQL>
37 SQL> commit;
38

So lets connect at app_user and see what we can see. Umm, I don’t like that. Imagine the damage that could be caused if a less then honest employee went on a shopping spree. I know of once such case where a customer service rep had access to information like this. When the theft was discovered, we estimate over $250,000 was stolen over a six month period.

 1 SQL> conn app_user/app_user@pdev
 2 Connected.
 3 SQL> select ssn, credit_card_nbr from rlockard.customers;
 4 /* snip a lot */
 5 428-72-3919               2671 6148 1798 1588
 6 251-71-5615               7925 4145 5815 3778
 7 345-69-2348               9457 1637 2244 2527
 8 967-55-1843               7474 9595 9119 7212
 9 969-21-1324               4642 3219 4458 6715
10

What if we could create a policy that only let the user see the last four digits of the ssn and the last for digits of the credit card number? We use the dbms_redact.add_policy to a table column. in this case we are redacting rlockard.customers.ssn and giving it a policy name of cust_sensitive. There is also the expression; expression when evaluated to true the policy will be used. We are also passing function_type that is dbms_redact.partial and function_parameters. Function parameters uses the predefined constant dbms_redact.redact_us_ssn_f5. This redacts the first five characters of the social security number.

 1 SQL> conn rlockard@pdev
 2 Enter password:
 3 Connected.
 4 SQL>
 5 SQL> declare
 6   2  begin
 7   3     dbms_redact.add_policy (
 8   4        object_schema =>  'RLOCKARD',
 9   5        object_name =>    'CUSTOMERS',
10   6        policy_name =>    'CUST_SENSITIVE',
11   7        expression =>     '1=1',
12   8        column_name =>    'SSN',
13   9        function_type => dbms_redact.partial,
14  10        function_parameters     => DBMS_REDACT.REDACT_US_SSN_F5
15  11     );
16  12  end;
17  13  /
18 
19 PL/SQL procedure successfully completed.
20

Okay that takes care of SSN, and oh by the way “expression => ‘1=1’ is not required on Oracle 12C. function_parameters uses the constant constant ‘DBMS_REDACT.REDACT_US_SSN_F5 that redacts the first five characters of the SSN.

Now lets take care of credit_card_number. In this case because we have already added the cust_sensitive policy we are going to alter the policy to add a column (this is the action parameter.) We then add credit_card_nbr and use the predefined constant dbms_redact.redact_cc16_f12.

 1 SQL>
 2 SQL> BEGIN
 3   2  DBMS_REDACT.ALTER_POLICY (
 4   3     object_schema          => 'RLOCKARD',
 5   4     object_name            => 'CUSTOMERS',
 6   5     policy_name            => 'CUST_SENSITIVE',
 7   6     column_name            => 'CREDIT_CARD_NBR',
 8   7     action                 => DBMS_REDACT.ADD_COLUMN,
 9   8     function_type          => DBMS_REDACT.PARTIAL,
10   9     function_parameters    => DBMS_REDACT.REDACT_CCN16_F12,
11  10     expression             => '1=1'
12  11  );
13  12  END;
14  13  /
15 
16 PL/SQL procedure successfully completed.
17

Well Oracle also provides us with the constant DBMS_REDACT.REDACT_CCN16_F12 that redacts the first twelve characters of a sixteen digit credit card number. So what does this look like.

 1 SQL>
 2 SQL> conn app_user/app_user@pdev
 3 Connected.
 4 select ssn, credit_card_nbr from rlockard.customers;
 5 /* snip a lot */
 6 XXX-XX-1979               ****-****-****-8336
 7 XXX-XX-4224               ****-****-****-2794
 8 XXX-XX-3475               ****-****-****-2386
 9 XXX-XX-8333               ****-****-****-5569
10 
11 SSN                       CREDIT_CARD_NBR
12 ------------------------- -------------------
13 XXX-XX-6396               ****-****-****-1936
14 XXX-XX-8118               ****-****-****-7557
15 XXX-XX-8122               ****-****-****-9645
16 XXX-XX-3169               ****-****-****-2112
17 XXX-XX-2722               ****-****-****-1597

Well we have connected as app_user, and the data is now redacted to a point where it is semi-secure. I would not be comfortable with this being public, but it’s a big improvement over displaying the entire social security number and credit card number.

Posted in Database Stuff, Security | Tagged infosec, oracle, redaction | Leave a comment

#Infosec

Posted on March 4, 2015 by rlockard

I recently have been receiving emails for consulting work. This is a good thing because I’m back on the market. However; some of the emails I have been receiving are asking for PII. This information can be used for identity theft.

Here’s an example: Asking for my date of birth and the last four digits of my Social Security Number. There is no good reason for any head hunter to be asking for that information. If you receive a query for a job asking for this information, hit the delete button.

Posted in Security | Tagged Identity Theft, infosec | Leave a comment

RDBMS Vs NoSQL

Posted on February 5, 2015 by rlockard

What are the use cases for the RDBMS vs. NoSQL?

http://https://www.youtube.com/watch?v=pzZb2hvnaW8

Posted in Database Stuff | Leave a comment

Insert Performance analysis part 2

Posted on November 20, 2014 by rlockard

I promised I would have more today. Here is the raw data from my analysis. As you can see as rejection (dup_val_on_index) increased, the execution time for select + insert decreased and execution time increased for insert with exception.

This specific use case is a transactional system requiring a unique index on ins_demo.column1 and we expected 20% of the time the unique index would be violated.

Here the two insert functions we are testing for performance. They are really quite simple. The first one is doing an insert into ins_demo and if there is a dup_val_on_index then return false.

The second function does a select against ins_demo on the unique key. If the key is not found then insert the value and return true, if the key is found then return false.

1 Begin
2 insert into ins_demo values (p_value);
3 return true;
4 exception when dup_val_on_index then
5 return false;
6 end;

 1 begin
 2 select count(*)
 3 into dummy
 4 from ins_demo
 5 where column1 = p_value;
 6 
 7 if dummy = 0 then
 8 insert into ins_demo values (p_value);
 9 return true;
10 else
11 return false;
12 end if;
13 end;

Here are the performance number for the two functions. I wrote a driver function that called the two functions 100,000 times and each once for a specific reject rate.

This bubble chart shows execution time on the y axis, rejection rate on the x axis and bubble size is the insert cpu time.

We should all strive to analyze changes prior to making recommendations. If I had gone with my knee jerk reaction to the select+insert and insisted on insert with an exception, system performance would have suffered.

This weekend I will run the same experiment using the merge command. I expect the merge command to be the fastest option, but as always, we require hard data before we make any recommendations.

Posted in Database Stuff | Leave a comment

Insert performance on a unique constraint.

Posted on November 19, 2014 by rlockard

In the past, I have read about the performance of exception handlers and all that is said is, There is performance overhead. Okay, the generic answer of “performance overhead” tells us nothing.

One of the great pleasures of what I do is code reviews. I get to learn techniques from other developers and I get to pass on some of my knowledge. Code reviews are a win-win all around.

Recently, I reviewed some code that had the following code block to enforce a unique constraint. (tables and variables have been changed to protect intellectual property) There is a unique constraint on ins_demo.column1, so the select will do a index unique scan for every select. The use case for this example is a transactional system where different users will be imputing data.

begin select count(*) into dummy from ins_demo where column1 = p_value;

if dummy = 0 then insert into ins_demo values (p_value); return true; else return false; end if; end;

My knee jerk reaction is to change this from a select+insert to insert with an exception handler to improve performance.

Begin insert into ins_demo values (p_value); return true; exception when dup_val_on_index then return false; end;

Before making recommendations, make sure you first do no harm. So, in this case I decided to run some performance test against the select+insert and the insert with an exception. The test will attempt to do 100,000 inserts starting will 100% success to 100% failures.

The results of the test have a few surprises.

Select+Insert 100% success:

execution time = 21.93 seconds

Insert CPU= 9.6

Select CPU = 2.55

Insert with exception 100% success:

execution time = 14.29 seconds

Insert CPU = 9.95

Recursive CPU = 8.96

Select+Insert 100% reject due to duplicate rows:

execution time = 5.86 seconds

Insert CPU = 0

Select CPU = 1.73

Insert with exception 100% rejected due to duplicate rows:

execution time = 135.03 seconds

Insert CPU = 16.85

Exception CPU = 20.7

We can learn a few things from this.

1) In the select + insert method, as the number of rejections increased execution time decreased, insert CPU decreased and select CPU decreased. This is due to a couple of things. A unique index scan is faster when the data is found and as the number of rejected rows increase due to unique index violation, the fewer times an insert is executed.

2) In the insert with exception handler, execution time increases as the number of rows are rejected due to unique index violations increases. This is because Oracle attempts to do the insert and then must roll the transaction back.

I will post a more detailed analysis with the raw performance data tomorrow.

Posted in Database Stuff, Life of a Oracle DBA, pl/sql performance | Leave a comment

displaying histograms of date and number data

Posted on September 4, 2014 by rlockard

It started with a bit of pl/sql in an ole’ library of mine. Knowing the old code was not the best way to display histogram data and If you can do something in one sql statement then that’s how you should do it.

Frequently when walking into a new environment we need to learn about the data. The cardinality along with the clustering of the data has a big impact on the CBO, so understanding your data is critical to optimizing the performance of your database.

First I need to load up a table with some test data. updt_stat simply takes the order date and returns a status. This is to simulate and order entry system.

 1 create or replace function updt_stat (pdate in date) return varchar2 as
 2 value varchar2(10);
 3 tmp   number;
 4 begin
 5   tmp := dbms_random.value(0,10);
 6   case
 7     when pdate > sysdate - 50 and tmp > 9
 8         then value := 'Canceled';
 9     when pdate <= sysdate - 50
10         then value := 'Shipped';
11     when pdate >= sysdate - 50 and pdate < sysdate - 1
12         then value := 'Picked';
13     when pdate >= sysdate
14         then value := 'Ordered';
15     else
16         value := 'Back Order';
17   end case;
18   return value;
19 end;
20 /
21

Then lets create a test table.

1 create table t1 (
2   id number     primary key,
3   order_date    date            not null,
4   col1          number          not null,
5   status        varchar2(25)    not null);
6 
7 alter table t1 modify id default t1_seq.nextval;

Now populate the table with some test data.

 1 declare
 2 --create or replace procedure simulate_order_entry as
 3 
 4   i     number;     -- an index variable
 5   sdate date;       -- the date we are going to insert.
 6 
 7   cursor c1 is
 8   select dbms_random.value(0,10000) col1
 9   from dual
10   connect by level <=36500;
11 begin
12 
13   -- one year of data
14   select sysdate-365 into sdate from dual;
15   i := 1;
16 
17   -- start the loop, 36,500 times
18   -- one year of data
19 
20   for rec in c1
21   loop
22     if ( i / 100 = 1 ) then
23       i := 1;
24       sdate := sdate + 1;
25     end if;
26 
27     insert into t1 (order_date, col1, status)
28     values
29     (sdate, rec.col1, updt_stat(sdate));
30 
31     i := i+1;       -- i++
32   end loop;
33   commit;
34 end;
35 /
36

It’s always been easy to display a histogram of text data. We’ve been doing this for decades.

 1 RLOCKARD@pdev > select status, count(*)
 2 from t1
 3 group by status;
 4   2    3
 5 STATUS                      COUNT(*)
 6 ------------------------- ----------
 7 Shipped                        31284
 8 Ordered                          240
 9 Picked                          4382
10 Back Order                        86
11 Canceled                         508
12

But what about getting a histogram of the order date. Oracle provides the function width_bucket that solves this problem.

 1 RLOCKARD@pdev > select min(order_date),
 2        max(order_date),
 3        width_bucket(order_date, sysdate-365, sysdate+20, 12) bucket,
 4        count(*)
 5 from t1
 6 group by width_bucket(order_date, sysdate-365, sysdate+20, 12)
 7 order by 1;
 8   2    3    4    5    6    7
 9 MIN(ORDER MAX(ORDER     BUCKET   COUNT(*)
10 --------- --------- ---------- ----------
11 03-SEP-13 03-SEP-13          0         99
12 04-SEP-13 05-OCT-13          1       3168
13 06-OCT-13 06-NOV-13          2       3168
14 07-NOV-13 08-DEC-13          3       3168
15 09-DEC-13 09-JAN-14          4       3168
16 10-JAN-14 10-FEB-14          5       3168
17 11-FEB-14 14-MAR-14          6       3168
18 15-MAR-14 15-APR-14          7       3168
19 16-APR-14 17-MAY-14          8       3168
20 18-MAY-14 18-JUN-14          9       3168
21 19-JUN-14 20-JUL-14         10       3168
22 21-JUL-14 21-AUG-14         11       3168
23 22-AUG-14 06-SEP-14         12       1553
24 
25

What about displaying numeric data. We change the parameters of width_bucket to numeric data.

 1 RLOCKARD@pdev > select min(col1),
 2        max(col1),
 3        width_bucket(col1, 0, 10000, 10) bucket,
 4        count(*)
 5 from t1
 6 group by width_bucket(col1, 0, 10000, 10)
 7 order by 1;
 8   2    3    4    5    6    7
 9  MIN(COL1)  MAX(COL1)     BUCKET   COUNT(*)
10 ---------- ---------- ---------- ----------
11 .013540186 999.847819          1       3593
12 1000.32556 1999.71215          2       3619
13 2000.26786 2999.44229          3       3593
14 3000.33048 3999.69273          4       3746
15 4000.37659 4999.86446          5       3721
16 5000.02576 5999.80972          6       3565
17  6000.0222 6999.70225          7       3677
18 7000.05808 7999.67615          8       3644
19 8000.11224 8999.93555          9       3644
20 9000.31992 9999.63753         10       3698
21

Posted in Database Stuff | Tagged histogram, width_bucket | Leave a comment

Why does the Oracle cbo interest me so much?

Posted on July 31, 2014 by rlockard

There is a short answer and a very long answer to this. I will try and keep the answer somewhere in the middle of short and long.

The first assumption we must accept and from my observations over the past several years, most Oracle developers don’t understand the Cost Based Optimizer, yet they use it everyday.

Way back in the dark ages, full table scans were evil and we used hints to get rid of this dreaded access path. As you can see from the bellow query, we got a full table scan of t1

So we solve this by adding a hint.

That’s better, we think but lets look at a few things. Did the hint really help? The first query had 70,308 consistent gets, 3,884 physical reads and cost was 1,065. The second query where we thought we were being cleaver had 1,057,564 consistent gets, 5868 physical reads and the cost was 990K.

Well, maybe I was not so cleaver by forcing Oracle to use an index, maybe the CBO is smart enough to put together the correct plan and the full table scan is not evil after all.

A few years ago I was at ODTUG and heard Maria Colgan speaking about the CBO and I was hooked. Maria made me realize I need to understand what the CBO was doing. Yes sometimes the CBO gets it wrong, but there are ways to correct that.

I’m now putting together a series of presentations on understanding the CBO using the scientific approach. Why not just publish how the CBO works? Why not publish ROT (Rule Of Thumb) for the CBO. Well the answer to that is quite simple. Every environment is different and it’s much better to understand your data and how the CBO works with your data and queries. There will be a presentations that will go through how to read an explain plan, however the other presentations will teach you how to establish what the CBO is doing in your environment and you will be able to explain it. That makes you smarter and more valuable.

Stay tuned, I will be posting blurbs and hints on the CBO.

Posted in Cost Based Optimizer, Database Stuff | Tagged CBO, oracle | Leave a comment

A must read for anyone who #manages people

Posted on May 2, 2014 by rlockard

I have had the pleasure to work with some great managers who know about big boy/girl rules and sadly some micromanagers who well micromanage. Those who know me well can imagine just how well I’ve gotten along with micromanagers.

http://dbakevlar.com/2011/07/the-care-and-feeding-of-good-skilled-employees/

Posted in Other Stuff | Tagged management | Leave a comment

Expand your DBA skills to include #Bigdata

Posted on May 1, 2014 by rlockard

Big data is more then having petabytes of information. Data is coming at you fast. The data may be structured or unstructured. Expand your DBA skills to be more valuable to your employer and master big data.

DBA to Big Data DBA

Posted in Database Stuff | Leave a comment

Oracle 10053 trace viewer

Posted on April 2, 2014 by rlockard

There are two trace files that I use when tuning. Cary Millsap @CaryMillsap and Method-R @MethodR have a great tool for analyzing the 10046 trace file. There is the profiler and my personal favorite mrskew http://method-r.com/store .

But the 10053 trace file is cumbersome to read and interpret. Jonathan Lewis @JLOracle posted this 10053 trace file viewer on oaktable.net. http://www.oaktable.net/contribute/10053-viewer .

If you are an Oracle DBA, Developer or Performance Engineer, these should be in your tool box.

Posted in Cost Based Optimizer, Database Stuff | Tagged 10053 10046 | Leave a comment

A quick reference to Outer joins

Posted on April 2, 2014 by rlockard

I frequently have to stop and think what side the (+) goes on for outer joins. I found this graphic originally from: http://oakweb02.oaktable.net/node/6628

Posted in Database Stuff | Leave a comment

What about NULL

Posted on March 24, 2014 by rlockard

What is NULL? Is NULL a space? Is NULL a NULL string?

NULL is unknown. If null is unknown then you can not do anything with null. You can not perform any operation on null:

Y := 5+UNKNOWN then Y=UNKNOWN
You can not compare null to anything:

IF UNKNOWN<5 THEN … END IF.

Well that will never evaluate to true because just don’t know.

This is part of what makes NULL so difficult to work with. NULL complicates logic. So just say no to null.

Posted in Database Stuff | Leave a comment

Beyond the basic select statement: Using the WITH clause inline SUBQUERY

Posted on March 22, 2014 by rlockard

We looked at the with statement with embedded PL/SQL, now lets look at another use of the with statement. This time we are going to use it with embedded subqueries.

Query A This query searches a stocks table, returns the median closing price for a quarter, the standard deviation and plus and minus one standard deviation.

The top two arrows point to the subquerys and the bottom arrow points to referencing the subqueries. The subqueries are named SDEV and MED, those can then be referenced in the from clause.

Query B shows the normal subquery we are accustom to seeing and returns the same values as Query A.

Query A and Query B are equivalent.

So, what execution plan did the CBO come up with for Query A

And what plan did the CBO come up with for Query B

I’ll dig into how the CBO came up with these execution plans later.

Posted in Beyond the basic select statement, Database Stuff | Tagged SELECT WITH | Leave a comment