Search

OakieTags

Who's online

There are currently 0 users and 24 guests online.

Recent comments

Oakies Blog Aggregator

Apex patch for 5.1

If you are running Application Express, there is a new patch available.  Lots of fixes which you can read about here

http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-511-patch-set-notes-3661846.html

I just downloaded patch 25341386 and followed the installation instructions and it went through with no problems at all in just a few minutes.


Verifying Application Express version...
...have version 5.1.0.00.45

PL/SQL procedure successfully completed.

Verifying Application Express version...
...have version 5.1.0.00.45

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Session altered.

Stopping and disabling APEX jobs

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Session altered.


Grant succeeded.

Verifying Application Express version...
...have version 5.1.0.00.45

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Session altered.


...
...
...

Validating Application Express
(09:02:38) Starting validate_apex for APEX_050100
(09:02:41) Checking missing sys privileges
(09:02:41) Recompiling
(09:02:42) Checking for objects that are still invalid
(09:02:42) Key object existence check
(09:02:42) Setting DBMS Registry for APEX to valid
(09:02:42) Exiting validate_apex

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

timing for: Complete Patch
Elapsed: 00:02:33.76

Enjoy !

Index out of range

I’ve waxed lyrical in the past about creating suitable column group statistics whenever you drop an index because even when the optimizer doesn’t use an index in its execution path it might have used the number of distinct keys of the index (user_indexes.distinct_keys) in its estimates of cardinality.

I’ve also highlighted various warnings (here (with several follow-on URLs) and here) about when the optimizer declines to use column group statistics. One of those cases is when a predicate on one of the columns goes “out of  range” – i.e. is below the column low_value or above the column high_value. Last night it suddenly crossed my mind that if we drop an index and replace it with a column group we might see an example of inconsistent behaviour: what happens when the index exists but the predicate is out of range – would you find that dropping the index and replacing it with a column group would give you different cardinality estimates for out of range predicates ?

Here’s the demonstration of what happened when I created a simple test on 12.1.0.2:


rem
rem     Script:         index_v_colgrp.sql
rem     Author:         Jonathan Lewis
rem
rem     Last tested
rem             12.1.0.2
rem

create table t1
nologging
as
with generator as (
        select
                rownum id
        from dual
        connect by
                level <= 1e4
)
select
        rownum                          id,
        mod(rownum-1,100)               n1,
        mod(rownum-1,100)               n2,
        lpad('x',100,'x')               padding
from
        generator       v1,
        generator       v2
where
        rownum <= 1e6 -- > comment to avoid WordPress format problem
;

begin dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'T1',
                method_opt       => 'for all columns size 1'
        );
end;
/

create index t1_i1 on t1(n1, n2);

set autotrace traceonly explain

I’ve created a table with 1M rows, where n1 and n2 are perfectly correlated – there are 100 distinct pairs of values (ranging from (0,0) to (99,99)). Now with autotrace enabled I’ll execute three queries – two with an index on the table of which one will be the baseline plan for predicates that are “in-range” and the other will take the predicates out of range, and the third after I’ve dropped the index and substituted a matching column group to see what I get for the “out of range” plan. The plans may produce different paths as the index disappears, of course, but what we’re only interested in the cardinality estimates in this experiment.

Here’s the code to run the three queries:


select  padding
from    t1
where
        n1 = 50
and     n2 = 50
;

select  padding
from    t1
where
        n1 = 110
and     n2 = 110
;

drop index t1_i1;

begin
        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'T1',
                method_opt       => 'for columns (n1, n2) size 1'
        );
end;
/

select  padding
from    t1
where
        n1 = 110
and     n2 = 110
;

And the three execution plans:


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 |  1044K|  2142   (4)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   | 10000 |  1044K|  2142   (4)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N1"=50 AND "N2"=50)


---------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |    79 |  8453 |    83   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |    79 |  8453 |    83   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T1_I1 |    79 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N1"=110 AND "N2"=110)


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    79 |  8453 |  2142   (4)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |    79 |  8453 |  2142   (4)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N1"=110 AND "N2"=110)

In summary:

  • With the index in place and the predicates in range the optimizer used user_indexes.distinct_keys to calculate cardinality.
  • With the index in place and the predicates (or just one of them, in fact) out of range the optimizer used the individual column selectivities with linear decay.
  • With a column group instead of an index the optimizer behaved exactly as it used to with the index in place.

So my concern that substituting column groups for indexes was unfounded – the optimizer was being silly (legal disclaimer: that’s just my opinion) with indexes, and the silly (ditto) behaviour with column groups hasn’t changed anything.

I’ll have to go back a couple of versions of Oracle to repeat these tests – maybe this behaviour with user_indexes.distinct_keys in place is relatively recent, but it’s another reason why execution plans may change suddenly and badly as time passes when “nothing changed”.

 

12.2 Online Conversion of a Non-Partitioned Table to a Partitioned Table (A Small Plot Of Land)

In my previous post, I discussed how you can now move heap tables online with Oracle Database 12.2 and how this can be very beneficial in helping to address issues with the Clustering Factor of key indexes. A problem with this technique is that is requires the entire table to be effectively reorganised when most of […]

Oak Table World, Collaborate 2017 Edition!

 

We weren't sure we'd get Oak Table World for Collaborate going, but Bryn made sure that it happened, (better thank him when you see him!)   We'll be in the South Seas C Ballroom all day Wednesday, so come learn database knowledge that everyone else is too frightened to talk about!

Submit your business card in the bowl at the front door and get a chance to win an AWESOME Nintendo NES Classic System with 30 games!

Wednesday    
Time Speaker Title of Abstract
Wednesday, April 5    
8:30am - 9:30am Keynote – no sessions  
9:45am - 10:45am RAC Cache Fusion internals - Riyaj Shamsudeen Demos of RAC cache fusion using wireshark.
11:00am - 12:00pm Bryn Llewellyn Why Use PL/SQL?
12:00pm – 1:30pm Lunch- See Ted Slots below  
1:30pm - 2:30pm Tim Gorman dNFS
2:45pm - 3:45pm Kellyn Pot'Vin-Gorman War of the Indices-  Oracle vs. SQL Server
3:45pm - 4:15pm Break – no sessions  
4:15pm - 5:15pm  Mauro Pagano  Chasing the Optimizer
Oakie Hour?  Panel with questions? BYOP, (Bring your own problem! Reception starts at 7pm; How much trouble can we get into??  
     
     
Ted Talks- 12pm-1:30pm 10 minutes long, six total slots Title of Abstract
12:05-12:15 Jonathan Gennick Learnings from SQL Server and Power BI: And Why I didn't Use Oracle
12:20-12:30 Bryn Llewellyn Ten rules for doing a PL/SQL performance experiment  

 Do you have a 10 minute TED talk you'd like to present at lunchtime?  Send an email to dbakevlar at gmail to submit and possibly speak! Sponsors for this great event?  Check'em out!

Managing Inbound Traffic on an AWS Instance

style="display:inline-block;width:320px;height:100px"
data-ad-client="ca-pub-5103295461547706"
data-ad-slot="5182487270">

“Enough already with the DHCP complaints!”  It’s Friday, so it means soon, we can stop worrying about such stuff and can get onto better things…

Until then, I’m sure other folks will be working with an AWS instance, go to log in after setting it up the initial time and want to know why the ssh connection that worked fine the day before is now hanging.  In fact, if anything has been done to renew their DHCP release and change their IP Address, this could occur.

There’s always more than one way to skin a cat and along with setting aliases for your favored IP address, the second option is to follow the next steps.

What’s Your Number?

So you can get to your instances via the EC2 console and use the Delphix admin console without any issue, but if you try to ssh into a source or target, (aka instances) the prompt just hangs.  The problem is that all that lovely automation that built out this incredible environment for the Delphix AWS Trial, also built out the security group setting for inbound traffic for you, tying the access to your IP address.  This information was entered in the terraform.tfvars file and the IP in this file no longer matches your new IP Address.

  • Get the new IP Address.  Number of methods- but I’m lazy and just type in “What is my ip address” in Google.

Update Your Security Group Rule

  1. In the the AWS EC2 Console, click on the instance you’re trying to log into.
  2. Click on the Description tab in the lower part of the screen.

https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/03/Screen-Shot-2... 300w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/03/Screen-Shot-2... 768w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/03/Screen-Shot-2... 1070w" sizes="(max-width: 501px) 100vw, 501px" data-recalc-dims="1" />

3. In this tab, you’ll see a listing for Security Groups.  Click on the group name to the right of Security Groups.

4. Click on Edit

https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/03/Screen-Shot-2... 300w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/03/Screen-Shot-2... 768w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/03/Screen-Shot-2... 1478w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/03/Screen-Shot-2... 1200w" sizes="(max-width: 514px) 100vw, 514px" data-recalc-dims="1" />

5. You’ll see what was your previous IP address, (if you haven’t updating your terraform.tfvars since the build, you can verify this.)

6. Update the IP address with your new one and click on SAVE.

Now try to SSH into the box.  You’ll need to do this for any and all instances that you want to connect to via a terminal.

Hint hint:  This is also the location to add a new workstation that needs access to work on the instances.

Happy Friday!



Tags:  


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [Managing Inbound Traffic on an AWS Instance], All Right Reserved. 2017.

The post Managing Inbound Traffic on an AWS Instance appeared first on DBA Kevlar.

Announcing Trivadis Performance Days 2017

Trivadis Performance Days 2017

It is a great pleasure to announce the next Performance Days! This year the event will take place the 13-14 September in Zurich.

Given that detailed information about the event as well as online subscription are available at https://trivadis.com/performance-days-2017, in this short post I limit myself to thanking and pointing out who the speakers that accepted my invitation are:

With so many talented and knowledgeable speakers no doubt the event will be awesome! See you there.

Can You Say That An Oracle Database is nn% secure?

I often get this type of question from customers and also from people I speak to and even a few times by email. The question is "can you tell us how secure our database is?", is it 10% secure, is....[Read More]

Posted by Pete On 23/03/17 At 03:22 PM

min/max Upgrade

A question came up on the OTN database forum a little while ago about a very simple query that was taking different execution paths on two databases with the same table and index definitions and similar data. In one database the plan used the “index full scan (min/max)” operation while the other database used a brute force “index fast full scan” operation.

In most circumstances the starting point to address a question like this is to check whether some configuration details, or some statistics, or the values used in the query are sufficiently different to result in a significant change in costs; and the first simple procedure you can follow is to hint each database to use the plan from the opposite database to see if this produces any clues about the difference – it’s a good idea when doing this test to use one of the more verbose formatting options for the call to dbms_xplan.

In this case, though, the OP discovered a note on MoS reporting exactly the problem he was seeing:

Doc ID 2144428.1: Optimizer Picking Wrong ‘INDEX FAST FULL SCAN’ Plan vs Correct ‘INDEX FULL SCAN (MIN/MAX)’

which referred to

Bug 22662807: OPTIMIZER PICKING INDEX FFS CAN INSTEAD OF MIN/MAX

Conveniently the document suggested a few workarounds:

  • alter session set optimizer_features_enable = ‘11.2.0.3’;
  • alter session set “_fix_control” = ‘13430622:off’;
  • delete object stats [Ed: so that dynamic sampling takes place … maybe a /*+ dynamic_sampling(alias level) */ hint would suffice].

Of the three options my preference would (at least in the short term) be the _fix_control one. Specifically, from the v$system_fix_control view, we can see that it addresses the problem very precisely with the description: “index min/max cardinality estimate fix for filter predicates”.

The example in the bug note showed a very simple statement (even more simple than the OP’s query which was only a single table query anyway), so I thought I’d build a model and run a few tests to see what was going on. Luckily, before I’d started work, one of the other members of the Oak Table network sent an email to the list asking if anyone knew how the optimizer was costing an example he’d constructed – and I’ve finally got around to looking at his example, and here’s the model and answer(s), starting with the data set:


rem
rem     Script:         test_min_max.sql
rem     Dated:          March 2017
rem
rem     Last tested
rem             12.1.0.2
rem             11.2.0.4
rem             11.2.0.3
rem

create table min_max_test nologging
as
with ids as (
        select /*+ Materialize */ rownum  id from dual connect by rownum <= 50000 -- > comment to protect formatting
),
line_nrs as (
        select /*+ Materialize */  rownum line_nr from dual connect by rownum <= 20 -- > comment to protect formatting
)
select
        id, line_nr ,rpad(' ', 800, '*') data
from
        line_nrs, ids
order by
        line_nr, id
;

begin
        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'min_max_test',
                method_opt       => 'for all columns size 1'
        );
end;
/

create index mmt_ln_id on min_max_test (line_nr, id) nologging;
create index mmt_id    on min_max_test (id)          nologging;

The table has two critical columns: each id has 20 line_nr values associated with it, but the way the data was generated means that the line numbers for a given id are scattered across 20 separate table blocks.

There are two indexes – one on the id which will allow us to find all the rows for a given id as efficiently as possible, and one (slightly odd-looking in this context) that would allow us to find a specific row for a given line_nr and id very efficiently. Two things about these indexes – in a live application they should both be compressed on the first (only, in the case of index mmt_id) column, and secondly the necessity of the mmt_id index is questionable and it might be an index you could drop if you reversed the order of the columns in mmt_ln_id. The thing about these indexes, though, is that they allow us to demonstrate a problem. So let’s query the data – twice, hinting each index in turn:


set serveroutput off

select
        /*+ index(t(id)) */
        min(line_nr)
from
        min_max_test t
where
        id = :b1
;

select * from table(dbms_xplan.display_cursor);

select
        /*+ index(t(line_nr, id)) */
        min(line_nr)
from
        min_max_test t
where
        id = :b1
;

select * from table(dbms_xplan.display_cursor);

It’s fairly safe to make a prediction about the execution plan and cost of the first query – it’s likely to be a range scan that accesses a couple of branch blocks, a leaf block and 20 separate table blocks followed by a “sort aggregate” – with a cost of about 23.

It’s a little harder to make a prediction about the second query. The optimizer could infer that the min(line_nr) has to be close to the left hand section of the index, and could note that the number of rows in the table is the same as the product of the number of distinct values of the two separate columns, and it might note that the id column is evenly distributed (no histogram) across the data, so it might “guess” that it need only range scan all the entries for the first line_nr to find the appropriate id. So perhaps the optimizer will use the index min/max range scan with a cost that is roughly 2 branch blocks plus total leaf blocks / 20 (since there are 20 distinct values for line_nr); maybe it would divide the leaf block estimate by two because “on average” – i.e. for repeated random selections of value for id – it would have to scan half the leaf blocks. There were 2,618 leaf blocks in my index, so the cost should be close to either 133 or 68.

Here are the two plans – range scan first, min/max second:


select  /*+ index(t(id)) */  min(line_nr) from  min_max_test t where id = :b1
-----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |       |       |    23 (100)|          |
|   1 |  SORT AGGREGATE                      |              |     1 |     8 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| MIN_MAX_TEST |    20 |   160 |    23   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | MMT_ID       |    20 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ID"=:B1)


select  /*+ index(t(line_nr, id)) */  min(line_nr) from  min_max_test t where  id = :b1
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |    22 (100)|          |
|   1 |  SORT AGGREGATE             |           |     1 |     8 |            |          |
|   2 |   FIRST ROW                 |           |     1 |     8 |    22   (0)| 00:00:01 |
|*  3 |    INDEX FULL SCAN (MIN/MAX)| MMT_LN_ID |     1 |     8 |    22   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("ID"=:B1)

Spot on with the estimate for the simple range scan – but what did we do wrong with the estimate for the min/max scan ? You might notice in the first example the “table access by rowid batched” and realise that this is running on 12c. Here’s the plan if I get if I set the optimizer_features_enable back to 11.2.0.3 before running the second query again:


select  /*+ index(t(line_nr, id)) */  min(line_nr) from  min_max_test t where  id = :b1
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |   136 (100)|          |
|   1 |  SORT AGGREGATE             |           |     1 |     8 |            |          |
|   2 |   FIRST ROW                 |           |     1 |     8 |   136   (1)| 00:00:01 |
|*  3 |    INDEX FULL SCAN (MIN/MAX)| MMT_LN_ID |     1 |     8 |   136   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("ID"=:B1)

Using the 11.2.0.3 optimizer model the plan has a cost that’s very close to our prediction – we’ll see why there’s a slight difference in a moment. If we set the optimizer_features_enable to 11.2.0.4 the cost drops back to 22. So for our example 11.2.0.3 will use the simple “index range scan” and an upgrade to 11.2.0.4 (or higher) will switch to the “index full scan (min/max)”. If you look at the OTN posting the impact of the change in costing is exactly the other way around – 11.2.0.3 uses the min/max path, 11.2.0.4 uses the simple index range scan.

The techy bit

You really don’t need to know this – experimenting with the optimizer_features_enable (or _fix_control) will give you plans that show you all the numbers you need to see to check whether or not you’ve run into this particular problem – but if you’re interested here’s a little bit from the two 10053 trace files. We need only look at a few critical lines. From the 11.2.0.3 costing for the min/max scan:


Index Stats::
  Index: MMT_ID  Col#: 1
  LVLS: 2  #LB: 2202  #DK: 50000  LB/K: 1.00  DB/K: 20.00  CLUF: 1000000.00  NRW: 1000000.00
  Index: MMT_LN_ID  Col#: 2 1
  LVLS: 2  #LB: 2618  #DK: 1000000  LB/K: 1.00  DB/K: 1.00  CLUF: 125000.00  NRW: 1000000.00

SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for MIN_MAX_TEST[T]
  Column (#1): ID(NUMBER)
    AvgLen: 5 NDV: 50536 Nulls: 0 Density: 0.000020 Min: 1.000000 Max: 50000.000000
  Table: MIN_MAX_TEST  Alias: T
    Card: Original: 1000000.000000  Rounded: 20  Computed: 19.787874  Non Adjusted: 19.787874

 ****** Costing Index MMT_LN_ID
  Access Path: index (Min/Max)
    Index: MMT_LN_ID
    resc_io: 135.000000  resc_cpu: 961594
    ix_sel: 1.000000  ix_sel_with_filters: 1.9788e-05
    Cost: 135.697679  Resp: 135.697679  Degree: 1

I was running 12.1.0.2 so there were a few extra bits and pieces that I’ve deleted (mostly about SQL Plan Directives and in-memory). Critically we can see that the stats collection has a small error for the ID column – 50,536 distinct values (NDV) instead of exactly 50,000. This seems to have given us a cost for the expected index range of: 2 (blevel) + ceiling(2618 (leaf blocks) * 50536 / 1000000) = 2 + ceil(132.3) = 135, to which we add a bit for the CPU and get to 136. (Q.E.D.)

Then we switch to costing for 11.2.0.4:


SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for MIN_MAX_TEST[T]
  Column (#1): ID(NUMBER)
    AvgLen: 5 NDV: 50536 Nulls: 0 Density: 0.000020 Min: 1.000000 Max: 50000.000000
  Table: MIN_MAX_TEST  Alias: T
    Card: Original: 1000000.000000  Rounded: 20  Computed: 19.787874  Non Adjusted: 19.787874

 ****** Costing Index MMT_LN_ID
  Access Path: index (Min/Max)
    Index: MMT_LN_ID
    resc_io: 21.787874  resc_cpu: 156872
    ix_sel: 1.000000  ix_sel_with_filters: 1.9788e-05
    Cost: 22.324608  Resp: 22.324608  Degree: 1

We still have the small error in the number of distinct values for id, so the estimated number of rows that we need to access from the table for a given id (before “aggregating” to find its minimum line_nr) is 19.787874 (Computed: / Non Adjusted:) rather than exactly 20. Notice, then, that the cost of using the index is 19.787874 + 2 which looks suspiciously like adding the blevel to the number of table blocks to get a cost and forgetting that we might have to kiss a lot of frogs before we find the prince. Basically, in this example at least, it looks like the costing algorithm has NOTHING to do with the mechanics of what actually has to happen at run-time.

Footnote

This is only an initial probe into what’s going on with the min/max scan; there are plenty more patterns of data that would need to be tested before we could have any confidence that we had produced a generic model of how the optimizer does its calculations – the only thing to note so far is that there IS a big change as  you move from 11.2.0.3 to later versions: the case on OTN showed the min/max scan disappearing on the upgrade, the example above shows the min/max disappearing on the downgrade – either change could be bad news for parts of a production system.

There are a couple of related bugs that might also be worth reviewing.

  • Bug 11834402 : CBO CHOOSES A SLOW INDEX FULL SCAN OVER A MUCH FASTER INDEX RANGE SCAN
  • Bug 13430622 : INDEX SCAN IN VERY SLOW FOR ONE PREDICATE AND FAST FOR OTHERS

There is a note, though that this last bug was fixed in 12.1

Footnote 2

When experimenting, one idea to pursue as the models get more complex and you’re using indexes with more than two columns is to test whether the presence of carefully chosen column group statistics might make a difference to the optimizer’s estimates of cardinality (hence cost) of the min/max scan.

 

PFCLScan - A Security Scanner For Oracle Databases - New Website

Our software product PFCLScan can be used to assess your Oracle databases for security issues that could make your data vulnerable to loss or attack. PFCLScan initially had its own website, PFCLScan.com but since the restyle and redesign of our....[Read More]

Posted by Pete On 22/03/17 At 08:24 PM

Iceland is Awesome….and Colder than Utah

I ended up speaking at two events this last week.  Now if timezones and flights weren’t enough to confuse someone, I was speaking at both an Oracle AND a SQL Server event- yeah, that’s how I roll these days.

Utah Oracle User Group, (UTOUG)

I arrived last Sunday in Salt Lake, which is just a slightly milder weather and more conservative version of Colorado, to speak at UTOUG’s Spring Training Days Conference.  I love this location and the weather was remarkable, but even with the warm temps, skiing was still only a 1/2 hour drive from the city.  Many of the speakers and attendees took advantage of this opportunity by doing just that while visiting.  I chose to hang out with Michelle Kolbe and Lori Lorusso.  I had a great time at the event and although I was only onsite for 48hrs, I really like this event so close to my home state.

I presented on Virtualization 101 for DBAs and it was a well attended session.  I really loved how many questions I received and how curious the database community has become about how this is the key to moving to the cloud seamlessly.

There are significant take-aways from UTOUG.  The user group, although small, is well cared for and the event is using some of the best tools to ensure that they get the best bang for the buck.  It’s well organized and I applaud all that Michelle does to keep everyone engaged.  It’s not an easy endeavor, yet she takes this challenge on with gusto and with much success.

SQL Saturday Iceland

After spending Wednesday at home, I was back at the airport to head to Reykjavik, Iceland for their SQL Saturday.  I’ve visited Iceland a couple times now and if you aren’t aware of this, IcelandAir offers up to 7 day layovers to visit Iceland and then you can continue on to your final destination.  Tim and I have taken advantage of this perk on one of our trips to OUGN, (Norway) and it was a great way to visit some of this incredible country.  When the notification arrived for SQL Saturday Iceland, I promptly submitted my abstracts and crossed my fingers.  Lucky for me,  accepted my abstract and I was offered the chance to speak with this great SQL Server user group.

After arriving before 7am on Friday morning at Keflavik airport, I realized that I wouldn’t have a hotel room ready for me, no matter how much I wanted to sleep.  Luckily there is a great article on the “I Love Reykjavik” site offering inside info on what to do if you do show up early.  I was able to use the FlyBus to get a shuttle directly to and from my hotel, (all you have to do is ask the front desk to call them the night before you’re leaving and they’ll pick you back up in front of your hotel 3 hrs before your flight.)  Once I arrived, I was able to check in my bags with their front desk and headed out into town.

I stayed at Hlemmur Square, which was central to the town and the event and next to almost all of the buses throughout the city.  The main street in front of it, Laugavegur, is one of the main streets that runs East-West and is very walkable.  Right across this street from the hotel was a very “memorable” museum, the Phallilogical Museum.  I’m not going to link to it or post any pictures, but if you’re curious, I’ll warn you, it’s NSFW, even if it’s very, uhm…educational.  It was recommended by a few folks on Twitter and it did ensure I stayed awake after only 2 hours of sleep in 24 hours!

As I wandered about town, there are a few things you’ll note about Iceland-  the murals of graffiti is really awesome and Icelandic folks like good quality products-  the stores housed local and international goods often made from wool, wood, quality metal and such. The city parliment building is easily accessible and it’s right across from the main shopping area and new city development.

https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/03/Screen-Shot-2... 300w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/03/Screen-Shot-2... 768w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/03/Screen-Shot-2... 1400w, https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/03/Screen-Shot-2... 1200w" sizes="(max-width: 455px) 100vw, 455px" data-recalc-dims="1" />

On Saturday, I was quick to arrive at Iceland’s SQL Saturday, as I had a full list of sessions I wanted to attend.  I was starting to feel the effects of Iceland weather on my joints, but I was going to make sure I got the most out of the event.  I had connected with a couple of the speakers at the dinner the night before, but with jet lag, you hope you’ll make a better impression on the day of the event.

I had the opportunity to learn about the most common challenges with SQL Server 2016 and that Dynamic Data Masking isn’t an enterprise solution.  Due to lacking discovery tools, the ability to join to non-masked objects and common values, (i.e. 80% of data is local and the most common location value would easily be identified, etc.) the confidential data of masked objects could be identified.

I also enjoyed an introduction to containers with SQL Server and security challenges.  The opening slide from Andy says it all:

https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/03/Screen-Shot-2... 300w" sizes="(max-width: 455px) 100vw, 455px" data-recalc-dims="1" />

Makes you proud to be an American, doesn’t it? </p />
</p></div></div>

    	  	<div class=