Skip navigation.

Feed aggregator

Combined ACCESS And FILTER Predicates - Excessive Throw-Away

Randolf Geist - Mon, 2015-04-13 00:00
Catchy title... Let's assume the following data setup:

create table t1
rownum as id
, 1 as id2
, rpad('x', 100) as filler
connect by
level <= 1e4

create table t2
rownum as id
, 1 as id2
, rpad('x', 100) as filler
connect by
level <= 1e4

create table t3
rownum as id
, 1 as id2
, rpad('x', 100) as filler
connect by
level <= 1e4

exec dbms_stats.gather_table_stats(null, 't1')

exec dbms_stats.gather_table_stats(null, 't2')

exec dbms_stats.gather_table_stats(null, 't3')

-- Deliberately wrong order (FBI after gather stats) - the virtual columns created for this FBI don't have statistics, see below
create index t2_idx on t2 (case when id2 = 1 then id2 else 1 end, case when id2 = 2 then id2 else 1 end, filler, id);

create index t3_idx on t3 (id, filler, id2);
And the following execution plan (all results are from but should be applicable to other versions, too):

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 10000 | 1416K| 132 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 10000 | 1416K| 132 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T2 | 10000 | 292K| 44 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 10000 | 1123K| 88 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T3 | 10000 | 70000 | 44 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T1 | 10000 | 1054K| 44 (0)| 00:00:01 |
How long would you expect it to run to return all rows (no tricks like expensive regular expressions or user-defined PL/SQL functions)?

Probably should take just a blink, given the tiny tables with just 10000 rows each.

However, these are the runtime statistics for a corresponding execution:

| | | |
|+0 00:00:23 |+0 00:00:23 |+0 00:00:23 |
| | | |

| Id | Pid | Ord | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Execs | A-Rows| PGA | Start | Dur(T)| Dur(A)| Time Active Graph | Activity Graph ASH | Top 5 Activity ASH |
| 0 | | 6 | SELECT STATEMENT | | | | 132 (100)| | 1 | 1 | 0 | | | | | | |
|* 1 | 0 | 5 | HASH JOIN | | 10000 | 1191K| 132 (0)| 00:00:01 | 1 | 1 | 1401K | 2 | 23 | 22 | ##### ############## | @@@@@@@@@@@@@@@@@@@ (100%) | ON CPU(22) |
| 2 | 1 | 1 | TABLE ACCESS FULL | T2 | 10000 | 70000 | 44 (0)| 00:00:01 | 1 | 10K | | | | | | | |
|* 3 | 1 | 4 | HASH JOIN | | 10000 | 1123K| 88 (0)| 00:00:01 | 1 | 10K | 1930K | | | | | | |
| 4 | 3 | 2 | TABLE ACCESS FULL| T3 | 10000 | 70000 | 44 (0)| 00:00:01 | 1 | 10K | | | | | | | |
| 5 | 3 | 3 | TABLE ACCESS FULL| T1 | 10000 | 1054K| 44 (0)| 00:00:01 | 1 | 10K | | | | | | | |
How is it possible to burn more than 20 seconds of CPU time with that execution plan?

The actual rows produced correspond pretty much to the estimated cardinalities (except for the final hash join), so that doesn't look suspect at first glance.
What becomes obvious from the SQL Monitoring output is that all the time is spent on the hash join operation ID = 1.

Of course at that point (at the latest) you should tell me off for not having you shown the predicate section of the plan and the corresponding query in first place.

So here is the predicate section and the corresponding query:

Predicate Information (identified by operation id):

1 - access(CASE "T1"."ID2" WHEN 1 THEN "T1"."ID2" ELSE 1 END =CASE
2 THEN "T1"."ID2" ELSE 1 END =CASE "T2"."ID2" WHEN 2 THEN "T2"."ID2"
filter("T3"."ID2"=CASE WHEN ("T1"."ID">"T2"."ID") THEN
"T1"."ID" ELSE "T2"."ID" END )

3 - access("T3"."ID"="T1"."ID")

select /*+
leading(t1 t3 t2)
--, t3.id2
--, case when > then else end
, t2
, t3
1 = 1
and case when t1.id2 = 1 then t1.id2 else 1 end = case when t2.id2 = 1 then t2.id2 else 1 end
and case when t1.id2 = 2 then t1.id2 else 1 end = case when t2.id2 = 2 then t2.id2 else 1 end
and =
and t3.id2 = case when > then else end
There are two important aspects to this query and the plan: First, the join expression (without corresponding expression statistics) between T1 and T2 is sufficiently deceptive to hide from the optimizer that in fact this produces a cartesian product (mimicking real life multi table join expressions that lead to bad estimates) and second, the table T3 is joined to both T1 and an expression based on T1 and T2, which means that this expression can only be evaluated after the join to T1 and T2.
With the execution plan shape enforced via my hints (but could be a real life execution plan shape preferred by the optimizer) T3 and T1 are joined first, producing an innocent 10K rows row source, which is then joined to T2. And here the accident happens inside the hash join operation:

If you look closely at the predicate section you'll notice that the hash join operation has both, an ACCESS operation and a FILTER operation. The ACCESS operation performs based on the join between T1 and T2 a lookup into the hash table, which happens to be a cartesian product, so produces 10K times 10K rows, and only afterwards the FILTER (representing the T3 to T1/T2 join expression) is applied to these 100M rows, but matching only a single row in my example here, which is what the A-Rows shows for this operation.

So the point is that this excessive work and FILTER throwaway isn't very well represented in the row source statistics. Ideally you would need one of the following two modifications to get a better picture of what is going on:

Either the FILTER operator should be a separate step in the plan, which in theory would then look like this:

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Execs | A-Rows|
| 0 | SELECT STATEMENT | | | | 132 (100)| | 1 | 1 |
|* 1a| FILTER | | 10000 | 1191K| 132 (0)| 00:00:01 | 1 | 1 |
|* 1b| HASH JOIN | | 10000 | 1191K| 132 (0)| 00:00:01 | 1 | 100M |
| 2 | TABLE ACCESS FULL | T2 | 10000 | 70000 | 44 (0)| 00:00:01 | 1 | 10K |
|* 3 | HASH JOIN | | 10000 | 1123K| 88 (0)| 00:00:01 | 1 | 10K |
| 4 | TABLE ACCESS FULL| T3 | 10000 | 70000 | 44 (0)| 00:00:01 | 1 | 10K |
| 5 | TABLE ACCESS FULL| T1 | 10000 | 1054K| 44 (0)| 00:00:01 | 1 | 10K |

Predicate Information (identified by operation id):

1a- filter("T3"."ID2"=CASE WHEN ("T1"."ID">"T2"."ID") THEN
"T1"."ID" ELSE "T2"."ID" END )
1b- access(CASE "T1"."ID2" WHEN 1 THEN "T1"."ID2" ELSE 1 END =CASE
2 THEN "T1"."ID2" ELSE 1 END =CASE "T2"."ID2" WHEN 2 THEN "T2"."ID2"
3 - access("T3"."ID"="T1"."ID")
Which would make the excess rows produced by the ACCESS part of the hash join very obvious, but is probably for performance reasons not a good solution, because then the data would have to flow from one operation to another one rather than being processed within the HASH JOIN operator, which means increased overhead.

Or an additional rowsource statistics should be made available:

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Execs | A-Rows|AE-Rows|
| 0 | SELECT STATEMENT | | | | 132 (100)| | 1 | 1 | 1 |
|* 1 | HASH JOIN | | 10000 | 1191K| 132 (0)| 00:00:01 | 1 | 1 | 100M |
| 2 | TABLE ACCESS FULL | T2 | 10000 | 70000 | 44 (0)| 00:00:01 | 1 | 10K | 10K |
|* 3 | HASH JOIN | | 10000 | 1123K| 88 (0)| 00:00:01 | 1 | 10K | 10K |
| 4 | TABLE ACCESS FULL| T3 | 10000 | 70000 | 44 (0)| 00:00:01 | 1 | 10K | 10K |
| 5 | TABLE ACCESS FULL| T1 | 10000 | 1054K| 44 (0)| 00:00:01 | 1 | 10K | 10K |
Which I called here "Actually evaluated rows" and in addition to this case here of combined ACCESS and FILTER operations could also be helpful for other FILTER cases, for example even for simple full table scan to see how many rows were evaluated, and not only how many rows matched a possible filter (what A-Rows currently shows).

In a recent OTN thread this topic came up again, and since I also came across this phenomenon a couple of times recently I thought to put this note together. Note that Martin Preiss has submitted a corresponding database idea on the OTN forum.

Expanding on this idea a bit further, it could be useful to have an additional "Estimated evaluated rows (EE-Rows)" calculated by the optimizer and shown in the plan. This could also be used to improve the optimizer's cost model for such cases, because at present it looks like the optimizer doesn't consider additional FILTER predicates on top of ACCESS predicates when calculating the CPU cost of operations like HASH JOINs.

Note that this problem isn't specific to HASH JOIN operations, you can get similar effects with other join methods, like NESTED LOOP joins, or even simple INDEX lookup operations, where again the ACCESS part isn't very selective but only the FILTER applied afterwards filters matching rows.

Here are some examples with the given setup:

select /*+
leading(t1 t3 t2)
--, t3.id2
--, case when > then else end
, t2
, t3
1 = 1
and case when t1.id2 = 1 then t1.id2 else 1 end = case when t2.id2 = 1 then t2.id2 else 1 end
and case when t1.id2 = 2 then t1.id2 else 1 end = case when t2.id2 = 2 then t2.id2 else 1 end
and =
and t3.id2 = case when > then else end

| Id | Pid | Ord | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Execs | A-Rows| PGA | Start | Dur(T)| Dur(A)| Time Active Graph | Activity Graph ASH | Top 5 Activity ASH |
| 0 | | 6 | SELECT STATEMENT | | | | 10090 (100)| | 1 | 1 | | | | | | | |
| 1 | 0 | 5 | NESTED LOOPS | | 10000 | 1416K| 10090 (1)| 00:00:01 | 1 | 1 | | | | | | | |
|* 2 | 1 | 3 | HASH JOIN | | 10000 | 1123K| 88 (0)| 00:00:01 | 1 | 10K | 1890K | | | | | | |
| 3 | 2 | 1 | TABLE ACCESS FULL| T3 | 10000 | 70000 | 44 (0)| 00:00:01 | 1 | 10K | | | | | | | |
| 4 | 2 | 2 | TABLE ACCESS FULL| T1 | 10000 | 1054K| 44 (0)| 00:00:01 | 1 | 10K | | | | | | | |
|* 5 | 1 | 4 | INDEX RANGE SCAN | T2_IDX | 1 | 30 | 1 (0)| 00:00:01 | 10K | 1 | | 3 | 33 | 32 | ################### | @@@@@@@@@@@@@@@@@@@ (100%) | ON CPU(32) |

Predicate Information (identified by operation id):

2 - access("T3"."ID"="T1"."ID")
5 - access(CASE "T1"."ID2" WHEN 1 THEN "T1"."ID2" ELSE 1 END
="T2"."SYS_NC00004$" AND CASE "T1"."ID2" WHEN 2 THEN "T1"."ID2" ELSE 1
END ="T2"."SYS_NC00005$")
filter("T3"."ID2"=CASE WHEN ("T1"."ID">"T2"."ID") THEN
"T1"."ID" ELSE "T2"."ID" END )

select /*+
leading(t1 t3 t2)
--, t3.id2
--, case when > then else end
, t2
, t3
1 = 1
and case when t1.id2 = 1 then t1.id2 else 1 end = case when t2.id2 = 1 then t2.id2 else 1 end
and case when t1.id2 = 2 then t1.id2 else 1 end = case when t2.id2 = 2 then t2.id2 else 1 end
and =
and t2.filler >= t1.filler
and = case when t1.id2 > t3.id2 then t1.id2 else t3.id2 end


| Id | Pid | Ord | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Execs | A-Rows| PGA | Start | Dur(T)| Dur(A)| Time Active Graph | Activity Graph ASH | Top 5 Activity ASH |
| 0 | | 7 | SELECT STATEMENT | | | | 20092 (100)| | 1 | 1 | | | | | | | |
| 1 | 0 | 6 | SORT AGGREGATE | | 1 | 223 | | | 1 | 1 | | | | | | | |
| 2 | 1 | 5 | NESTED LOOPS | | 1 | 223 | 20092 (1)| 00:00:01 | 1 | 10K | | | | | | | |
|* 3 | 2 | 3 | HASH JOIN | | 10000 | 1123K| 88 (0)| 00:00:01 | 1 | 10K | 1900K | | | | | | |
| 4 | 3 | 1 | TABLE ACCESS FULL| T3 | 10000 | 70000 | 44 (0)| 00:00:01 | 1 | 10K | | | | | | | |
| 5 | 3 | 2 | TABLE ACCESS FULL| T1 | 10000 | 1054K| 44 (0)| 00:00:01 | 1 | 10K | | | | | | | |
|* 6 | 2 | 4 | INDEX RANGE SCAN | T2_IDX | 1 | 108 | 2 (0)| 00:00:01 | 10K | 10K | | 2 | 34 | 34 | #################### | @@@@@@@@@@@@@@@@@@@ (100%) | ON CPU(34) |

Predicate Information (identified by operation id):

3 - access("T3"."ID"="T1"."ID")
6 - access(CASE "T1"."ID2" WHEN 1 THEN "T1"."ID2" ELSE 1 END
="T2"."SYS_NC00004$" AND CASE "T1"."ID2" WHEN 2 THEN "T1"."ID2" ELSE 1
END ="T2"."SYS_NC00005$" AND "T2"."FILLER">="T1"."FILLER" AND
"T2"."ID"=CASE WHEN ("T1"."ID2">"T3"."ID2") THEN "T1"."ID2" ELSE
filter("T2"."ID"=CASE WHEN ("T1"."ID2">"T3"."ID2") THEN
"T1"."ID2" ELSE "T3"."ID2" END )

The former one exhibits exactly the same problem as the HASH JOIN example, only that the FILTER is evaluated in the inner row source of a NESTED LOOP join after the index access operation.

The latter one shows as variation the classic partial "index access" due to a range comparison in between - although the entire expression can be evaluated on index level, the access part matches every index entry, so the range scan actually needs to walk the entire index at each loop iteration and the FILTER is then applied to all the index values evaluated.

Updated Technical Best Practices

Anthony Shorten - Sun, 2015-04-12 22:52

A minor update has been added to the Technical Best Practices in response to some customer feedback. There are some instructions on how to build a data model for data modelling tools such as SQL Developer Data Modeler and Enterprise Manager's Data Masking and Subsetting Pack (formerly known as Test Data Management Pack).

The SQL to build the model has been updated to include some additional constraints available in OUAF 4.2 and above.

Customers intending to build the data model using this technique should refer to the updated instructions in Technical Best Practices for Oracle Utilities Application Framework Based Products (Doc Id: 560367.1) available from My Oracle Support.

In what ways is buckthorn harmful?

FeuerThoughts - Sun, 2015-04-12 16:54
I spent 10-15 hours a week in various locations of still-wooded Chicago (and now nearby Lincolnwood) cutting down buckthorn. Some people have taken me to task for it ("Just let it be, let nature take it's course, etc.). So I thought I would share this excellent, concise sum up of the damage that can be wrought by buckthorn.
And if anyone lives on the north side of Chicago and would like to help out, there is both "heavy" work (cutting large trees and dragging them around) and now lots of "light" work (clipping the new growth from the stumps from last year's cutting - I don't use poison). 
It's great exercise and without a doubt you will be helping rescue native trees and ensure that the next generation of those trees will survive and thrive!
From The Landscape Guys
Buckthorn should be on America's "Most Wanted" list, with its picture hanging up in every US Post Office! Here are a few of the dangers of Buckthorn:
a) Buckthorn squeezes out native plants for nutrients, sunlight, and moisture. It literally chokes out surrounding healthy trees and makes it impossible for any new growth to take root under its cancerous canopy of dense vegetation.
b) Buckthorn degrades wildlife habitats and alters the natural food chain in and growth of an otherwise healthy forest. It disrupts the whole natural balance of the ecosystem.
c) Buckthorn can host pests like Crown Rust Fungus and Soybean Aphids. Crown Rust can devastate oat crops and a wide variety of other grasses. Soybean Aphids can have a devastating effect on the yield of soybean crops. Without buckthorn as host, these pests couldn't survive to blight crops.
d) Buckthorn contributes to erosion by overshadowing plants that grow on the forest floor, causing them to die and causing the soil to lose the integrity and structure created by such plants.
e) Buckthorn lacks "natural controls" like insects or diseases that would curb its growth. A Buckthorn-infested forest is too dense to walk through, and the thorns of Common Buckthorn will leave you bloodied.
f) Buckthorn attracts many species of birds (especially robins and cedar waxwings) that eat the berries and spread the seeds through excrement. Not only are the birds attracted to the plentiful berries, but because the buckthorn berries have a diuretic and cathartic effect, the birds pass the seeds very quickly to the surrounding areas of the forest. This makes Buckthorn spread even more widely and rapidly, making it harder for us to control and contain.
Categories: Development

RAC Attack! was another great success at C15LV

Yann Neuhaus - Sun, 2015-04-12 14:49

The RAC Attack  - install a RAC in your own laptop - is a great success at Las Vegas.

The idea is to help people follow the RAC Attack cookbook which is available at:

It is a complex configuration and there is always problems to troubleshoot:

  • get Virtual Box be able to run a 64-bits guest, and that might involve some BIOS settings
  • be able to install VirtualBox, and we have people with their company laptop where some security policies makes things difficule
  • Network configuration is not simple and any misconfiguration will make things more difficult later

So it is a very good exercise for troubleshooting.

The organisation way excellent: Organisation by Ludovico Caldara, infrastructure by Erik Benner, food sponsored by OTN, and Oracle software made available on USB sticks thanks to Markus Michalewicz. Yes the RAC Product Manager did the racattack installation.

 It's also a very good networking event where people meet people around the technology, thanks to IOUG Collaborate.

More Ninjas graduating the Dojo! #racattack @ioug @racsig #c15lv @OracleDBDev @Mythics

— Erik Benner (@Erik_Benner) April 12, 2015

When people manage to get a VM with the OS installed, they can get the red tee-shirt. Look at the timelapse of the full day and you will see more and more red T-shirts:

Do you wonder why we are so happy to see people having only the OS installed? Because it's the most difficult part. Creating a cluster on a laptop is not easy. You have to create the VM, you have to setup networking, DNS, etc.

Once this setup is good, then installing Grid Infrastructure and Database is straightforward with graphical installer.

GSV 2015 Review

Michael Feldstein - Sun, 2015-04-12 11:06

By Michael FeldsteinMore Posts (1024)

The basic underlying theme of the 2015 GSV Ed Innovation conference is “more is more.” There were more people, more presentations, more deal-making, more celebrities…more of everything, really. If you previously thought that the conference and the deal-making behind it was awesome, you would probably find this year to be awesomer. If you thought it was gross, you would probably think this year was grosser. Overall, it has gotten so big that there is just too much to wrap your head around. I really don’t know how to summarize the conference.

But I can give some observations and impressions.

More dumb money: Let’s start with a basic fact: There is more money coming into the market.

If there is more total money coming in, then it stands to reason that there is also more dumb money coming in. I definitely saw plenty of stupid products that were funded, acquired, and/or breathlessly covered. While it wasn’t directly conference-related, I found it apropos that Boundless was acquired right around the time of the conference. I have made my opinions about Boundless clear before. I have no opinion about Valore’s decision to acquire them, in large part because I don’t know the important details. It might make sense for a company like Valore to acquire Boundless for their platform—if the price is right. But this doesn’t appear to be a triumph for Boundless or their investors. To the contrary, it smells like a bailout of Boundless’ investors to me, although I admit that have no evidence to prove that. If the company were doing so awesomely, then I don’t think the investors would have sold at this point. (Boundless, in typical Boundless fashion, characterizes the transaction as a “merger” rather than an “acquisition.” #Winning.) Of course, you wouldn’t know that this is anything less than the total takeover of education from the breathless press coverage. Xconomy asks whether the combined company will be the “Netflix of educational publishing.”


So yeah, there’s plenty of dumb money funding dumb companies, aided and abetted by dumb press coverage. But is there proportionally more dumb money, or is there just more dumb money in absolute terms as part of the overall increase in investment? This is an important question, because it is a strong indicator of whether the idiocy is just part of what comes when an immature industry grows or whether we are in a bubble. This particular kind of market analysis is somewhat outside my wheelhouse, but my sense, based on my fragmented experience of the conference added to other recent experiences and observations, is that it’s a bit of both. Parts of the market have clearly gotten ahead of themselves, but there also are some real businesses emerging. Unsurprisingly, some of the biggest successes are not the ones that are out to “disrupt” education. Apparently the ed tech company that got the most money last year was which, in addition to being a good bet, doesn’t really compete head-on with colleges (and, in fact, sells to schools). Phil has written a fair bit about 2U; that company only exists because they have been able to get high-end schools to trust them with their prestige brands. This brings me to my next observation:

More smart money: 2U is a good example of a company that, if you had described it to me in advance, I probably would have told you that it never could work. The companies that do well are likely to be the ones that either figure out an angle that few people see coming or execute extremely well (or, in 2U’s case, both).[1] 2U is also one of very few ed tech that have made it to a successful IPO (although there are more that have been successfully sold to a textbook publisher, LMS vendor, or other large company). I am seeing more genuinely interesting companies getting funding and recognition. Three recent examples: Lumen Learning getting angel funding, Acrobatiq winning the ASU-GSV Return on Education Award, and Civitas closing Series C funding a couple of months ago. I also had more interesting and fewer eye-rolling conversations at the conference this year than in past years. Part of that is because my filters are getting better, but I also think that the median educational IQ of the conference attendees has risen a bit as at least some of the players learn from experience.

Textbooks are dead, dead, dead: McGraw Hill Education CEO David Levin was compelled to start his talk by saying, essentially, “Yeah yeah yeah, everybody hates textbooks and they are dying as a viable business. We get it. We’re going to have all digital products for much less money than the paper textbooks very soon, and students will be able to order the paper books for a nominal fee.” He then went on to announce a new platform where educators can develop their own content.

Pay heed, OER advocates.

I saw Mark Cuban: He has noticeably impressive pecs. Also,

Arizona is nicer than Massachusetts in early April.

  1. Corollary: Companies trying to be the “Netflix of education” or the “Uber of education” or the “Facebook of education” will usually turn out to be as ridiculous—meaning “worthy of ridicule”—as they sound.

The post GSV 2015 Review appeared first on e-Literate.

SQLCL – The New SQL*Plus

The Anti-Kyte - Sun, 2015-04-12 10:33

To borrow a well-known saying, One-Day International Cricket is a game played by two sides for 100 overs…and then the Aussies win the World Cup.
Something else that doesn’t seem to change much over time is SQL*Plus. The command line interface to Oracle Databases has been around, in it’s current guise, since 1985.
Whilst there have been some changes here and there, it’s basic functionality has remained largely unchanged over the intervening 30 years.
Now, however, it looks like things are about to change as Oracle lavish some attention on the noble CLI.

You may be wondering how this is in any way relevant in the modern world of GUI development.
Well, there are still some things that you need the command line for.
Interactive connection to a database from a server that’s not running a desktop environment would be one.

More common though, are those jobs that need to run unattended. These will include batch jobs managed by a scheduling tool external to the Oracle RDBMS, such as Autosys, or even good old CRON.
Increasingly, it will also include jobs that are initiated as part of Continuous Integration or Release Management testing.

SQL*Plus for the 21st Century is currently going by the name of SQLCL (SQL Command Line). It has also been known as SDSQL (SQLDeveloper SQL) and even SQL*Plus++ (my personal favourite).

Whilst the currently available versions of SQLCL are very much in the Early Adopter stage, there is enough there to show the direction in which things are moving.
Whilst the decision has been taken to ensure that SQLCL is fully backward-compatible with the current SQL*Plus, some of the new features may well have significant implications in the way that the Oracle CLI is used in the future.

What I’m going to cover here is :

  • How SQLCL differs “structurally” from SQL*Plus
  • Improvements in command-line interaction incorporating Linux-like and IDE features, including glorious technicolour!

All of which leaves the ageing geek in me unashamedly excited.

Before I go on, I should take this opportunity to say thanks to Jeff Smith and the team for answering the questions I had about some of the new features.

Download and Installation

The first thing you’ll notice about SDSQL is that, unlike the Oracle Client of which SQL*Plus is a component, it is a single file.
This is a pretty good start as you don’t have to figure out which files you need to download before you get going.

Instead, you simply need to head over to the SQLDeveloper Download Page and download Command Line SDSQL – All Platforms.
You are rewarded with posession of 11MB worth of :

Once you’ve unzipped the tool, go to the bin directory. In my case :

cd /opt/sqlcl/bin

Here you will find the following files :

  • sql.bat – a Windows batch script
  • sql – a bash script
  • sql.exe – a Windows executable

On a Windows client, you can just run sql.exe and be on your way. The bash script provided ultimately executes this command :

java  -Djava.awt.headless=true -Dapple.awt.UIElement=true -jar /opt/sqlcl/sqlcl/lib/oracle.sqldeveloper.sqlcl.jar

In order for SQLCL to run whether on Windows or Linux, you need Java installed. To be more precise, you need a Java Runtime Environmnent (JRE) of version 1.7 or higher.

This in itself is not a major issue. However, it’s worth bearing this dependency in mind if you’re working in a large organization with a separate Server Admin team as you’ll probably need to negotiate some process hurdles to get a suitable JRE onto your servers if there isn’t one already in place.

Database Connections

As SQLCL isn’t part of the client, it does not assume that there is a tnsnames.ora hanging around for it to read (although it will happily read one that’s pointed to by the TNS_ADMIN environment variable).

I think it’s fair to say that the “native” connection method is to use the EZConnect syntax which has the format :


So, connecting to my local XE database interactively can look something like this :

SQLcl: Release 4.1.0 Beta on Fri Apr 10 15:49:47 2015

Copyright (c) 1982, 2015, Oracle.  All rights reserved.

Username? (''?) mike
Password? (**********?) ********
Database? (''?) localhost:1526/XE
Connected to:
Oracle Database 11g Express Edition Release - 64bit Production 


…where my database is on the current machine, the TNS Listener is on port 1526 and the SID is XE.

Of course, having an Oracle Client installed and a TNS entry for my database specified, I can also still do this :

SQLcl: Release 4.1.0 Beta on Fri Apr 10 15:49:47 2015

Copyright (c) 1982, 2015, Oracle.  All rights reserved.

Username? (''?) mike
Password? (**********?) ********
Database? (''?) XE
Connected to:
Oracle Database 11g Express Edition Release - 64bit Production 


There are other connection methods available. Barry McGillin has an example of connecting via LDAP.

Of course, using the EZConnect syntax all the time, especially when you open another connection from within your session could involve a fair amount of typing. Fortunately, The developers have thought of this…


The NET command allows you to “save network details and assign it a shortcut command”.
For example, to create an alias for the database running on a Developer Day VirtualBox image :

net alias dday=;

NOTE – you need to terminate the string with a semi-colon at the end of the line.

To test it, we first need to enable NET in our SQLCL session (it’s set to OFF by default) …

SQL> set net on
SQL> show net
net: ON

So, whilst connected to XE as mike, I can use the NET alias I’ve just created to connect to the Developer Day database :

SQL> conn hr@dday
Password? (**********?) ************

SQL> show user
USER is "HR"

SQL> select name from v$database;


The Command Line, but not as we know it

When it comes to command-line editing and scrollback, SQL*Plus is, well, a bit basic.
If you’re on Windows, you can scroll back through previous commands. Unfortunately, even this is not a feature on Linux.
There have been efforts to make the SQL*Plus a bit more friendly over the years, notably the RLWRAP project.
If you haven’t discovered the joys of that particular software, you are about to have a new experience at the SQL prompt…

Tab Completion

Type the following…

select * from hr.reg

when you hit the TAB key, the table name is automagically completed for you.
OK, it uppercases the table name to REGIONS but hey, if you’re that worried about code formatting…well, we’ll get to that in a bit.

In the meantime, just marvel at the fact that it’s almost like being at the $ prompt.

Multi-Line Console Editing

If you think that’s good…

select * 
from hr.regions

I run this and then realise I really should have added a predicate to avoid having to wade through a massive result set ( OK, it’s only 4 rows, but just bear with me here).
Rather than having to fiddle about with the arcane SQL*Plus editing commands, I can simply use the list command to show me what’s currently in the buffer ( i.e. the query I’ve just run) and then hit the up arrow.

I’m now magically transported to the end of the last line of the statement. I can add a new line, simply by hitting RETURN and then add my predicate (NOTE – it’s probably a good idea to hit back arrow, forward arrow if you’re doing this as otherwise SQLCL thinks you want to cycle through the previous commands you’ve issued).

SQL> select *
  2  from hr.regions
  3* where REGION_ID = 1
  4  /

---------- -------------------------
         1 Europe                   


The uppercase REGION_ID indicates that I got this column name using tab completion.
Barry McGillin has a demonstration of this feature here.


Rather than simply remembering the last SQL or PL/SQL that was executed, SQLCL retains a history of the last 100 commands executed. Note that this is the last 100 commands for any user connecting on the current client.
This includes SQL*Plus commands, such as describe.

You can scroll back and forward through the previous commands using the arrow keys – nothing new for Windows clients but it is for Linux.

Alternatively, you can get a listing by typing :


This will give you a numbered listing of the commands in the history.

Incidentally, if you use the built-in SQLCL help command for history, you’ll get this listing :

SQL>help history
history [<index> | FULL | USAGE | HELP ]

SQL>history full
1  select 1 from dual;
2  select 2
>  from dual;
3  select 3 from dual
>  where 1=1;

SQL>history usage
1  (2) select 1 from dual; 
2  (11) select 2 from dual; 
3  (2) select 3 from dual where 1=1; 

SQL>history 3
1  select 3 from dual
2* where 1=1;

However, if you try the help option of the history command, you get something slightly different …

SQL> history help 

history [<index> | FULL | USAGE | TIME | HELP | CLEAR]


The full option appears to simply list all of the commands in the history ( the same as simply issuing the HISTORY command on it’s own).

When we look at some of the other options, it gets a bit interesting :

SQL> history
  1  select name from v$database
  2  select sysdate from dual
SQL> history usage
  1  (1) select name from v$database
  2  (4) select sysdate from dual
SQL> history time
  1  (00.176) select name from v$database
  2  (05.415) select sysdate from dual

The USAGE option keeps a count of the number of times a particular command has been executed.
The TIME option shows the total execution time for the statement (thanks Jeff).

In some circumstances, this could prove handy, especially when you realise that you need to set timing on…just after you’ve kicked off your script.

If you want to cleardown the history…

SQL> history clear
History Cleared

One point to note – as with it’s Linux counterpart, HISTORY retains any command executed using that client, not simply any command when connected as a particular user. This is something you may need to be aware of on servers where the Oracle “client” is shared by multiple users.


You know how much fun it is when you have a master release script, either for deployment to an environment or, just as common these days, to refresh a Continuous Integration environment.

Such a script may well look something like this :

prompt Deploying HR_DEV...

prompt Sequences
prompt ---------


prompt Tables
prompt ------


prompt Deployment complete

With SQLCL, the script becomes somewhat simpler :

prompt Deploying HR_DEV...

prompt Sequences
prompt ---------

cd sequences

prompt Tables
prompt ------

cd ../tables

prompt Deployment complete

Yes, you can now change directory from SQLCL, in the same way that you would at the OS level. As well as being rather convenient, this should serve to save a fair bit of typing, especially for scripts such as this one.

If all that isn’t quite enough Linux-like goodness for you then you’d probably be quite interested in…


As you may guess from the name, ALIAS allows you to save and invoke SQL or PL/SQL blocks under a pre=defined name.
There are already some aliases defined. You can find them by simply typing :

SQL> alias

If you want to see the definition of the tables alias….

SQL> alias list tables
 select table_name "TABLES"from user_tables

You can run this simply by typing the alias name at the prompt. For example, if you connect as the HR user …

SQL> tables


 8 rows selected 


Of course, you can add your own alias. If you’re an inveterate clock-watcher, for example….

SQL> alias current_time=select to_char(sysdate, 'HH24:MI') from dual;

The alias is now saved so, whenever I want to know if it’s nearly hometime….

SQL> current_time



According to the help, you can define aliases for PL/SQL blocks, and even reference bind variables.

Choosing you’re output format with SQLFORMAT

The new SQLFORMAT parameter deserves particular attention. Before I get into some of it’s more obvious benefits, take a look at this :

set sqlformat ansiconsole
with ts_details as
    select rpad(tablespace_name,30, ' ')||' '||lpad(floor(used_percent), 3, ' ') as ts_line,
    from dba_tablespace_usage_metrics
        when used_percent > 70 then '@|bg_red '||ts_line||'|@'
        when used_percent < 1 then '@|bg_green '||ts_line||'|@'
        else '@|bg_yellow '||ts_line||'|@' 
    end as ts_usage_percentage
from ts_details

Now at first glance this script looks rather odd. Have I had an attack of fat-fingers ? Well, let’s run it and see…

Contrived example to show traffic-lighting

Contrived example to show traffic-lighting

Yep, SQL*Plus now does colour. OK, so I set the threshold values to make sure I got records to show in all three colours, but you get the idea.

There’s a rather more comprehensive script by Kris Rice here, which details many colour options.

One of the neat features in SQLDeveloper has always been the ability to generate the output from a query in a pre-defined format.
This ability is still there in SQLCL. To use a fashionable example…

SQL> select /*json*/ * from hr.regions;

{"region_id":1,"region_name":"Europe"},{"region_id":2,"region_name":"Americas"},{"region_id":3,"region_name":"Asia"},{"region_id":4,"region_name":"Middle East and Africa"},{}]}


SQLFORMAT allows you to specify the output format separately from the code. For example :

SQL> set sqlformat csv
SQL> select * from hr.regions;

4,"Middle East and Africa"


The fact that the output format is separated from the script means that you can use the same script to generate multiple formats.
Ultimately, this means less code, not to mention less complex (or, at least, fiddly) code.

For example, instead of :

select employee_id||','||first_name||','||last_name
from hr.employees

… you can now have…

set sqlformat csv
select employee_id, first_name, last_name,
    email, phone_number
from hr.employees

…and if you need to create an additional feed file in a different format, you simply need to type…

set sqlformat xml

…and execute exactly the same code again.

There are several SQLFORMAT options. The ones that I’ve tested are :

  • default
  • ansiconsole
  • csv
  • insert – lists resuts as an insert statement
  • loader – pipe-delimited
  • delimited – same as csv
  • xml
  • html
  • fixed – fixed width
  • text
  • json

This heading is a slight misquote of Terry Pratchett’s L-Space equation.
The theory of L-space is that a large collection of accumulated knowledge, such as a library, can act like a black hole.
If you want to have a go at bending the fabric of space and time armed with nothing more than a command line…


The humble DESCRIBE command in SQL*Plus has been around for a while. I’d guess it’s probably as old as the tool itself.
In that time, things have changed a bit.
It’s now possible to have comments on columns. There’s also this new-fangled PL/SQL, together with it’s associated packages.
If, like me, you’ve hankered after a describe that’s just a bit more able to handle these more modern developments, well, you’re in luck.

First off, let’s issue a good old DESCRIBE :

SQL> desc employees
Try the new information command: info employees

Name           Null     Type         
-------------- -------- ------------ 
FIRST_NAME              VARCHAR2(20) 
PHONE_NUMBER            VARCHAR2(20) 
SALARY                  NUMBER(8,2)  
MANAGER_ID              NUMBER(6)    
DEPARTMENT_ID           NUMBER(4)    

Interesting message, let’s give it a go (note, it’s probably a good idea to set the SQLFORMAT to ansiconsole at this point) :

SQL> set sqlformat ansiconsole
SQL> info employees
NAME             DATA TYPE           NULL  DEFAULT    COMMENTS
*EMPLOYEE_ID     NUMBER(6,0)         No                   Primary key of employees table.
 FIRST_NAME      VARCHAR2(20 BYTE)   Yes                  First name of the employee. A not null column.
 LAST_NAME       VARCHAR2(25 BYTE)   No                   Last name of the employee. A not null column.
 EMAIL           VARCHAR2(25 BYTE)   No                   Email id of the employee
 PHONE_NUMBER    VARCHAR2(20 BYTE)   Yes                  Phone number of the employee; includes country code and area code
 HIRE_DATE       DATE                No                   Date when the employee started on this job. A not null column.
 JOB_ID          VARCHAR2(10 BYTE)   No                   Current job of the employee; foreign key to job_id column of the
                                                                    jobs table. A not null column.
 SALARY          NUMBER(8,2)         Yes                  Monthly salary of the employee. Must be greater
                                                                    than zero (enforced by constraint emp_salary_min)
 COMMISSION_PCT  NUMBER(2,2)         Yes                  Commission percentage of the employee; Only employees in sales
                                                                    department elgible for commission percentage
 MANAGER_ID      NUMBER(6,0)         Yes                  Manager id of the employee; has same domain as manager_id in
                                                                    departments table. Foreign key to employee_id column of employees table.
                                                                    (useful for reflexive joins and CONNECT BY query)
 DEPARTMENT_ID   NUMBER(4,0)         Yes                  Department id where employee works; foreign key to department_id
                                                                    column of the departments table

HR.EMP_JOB_IX         NONUNIQUE   VALID                   JOB_ID                                    
HR.EMP_NAME_IX        NONUNIQUE   VALID                   LAST_NAME, FIRST_NAME                     
HR.EMP_EMAIL_UK       UNIQUE      VALID                   EMAIL                                     
HR.EMP_EMP_ID_PK      UNIQUE      VALID                   EMPLOYEE_ID                               
HR.EMP_MANAGER_IX     NONUNIQUE   VALID                   MANAGER_ID                                
HR.EMP_DEPARTMENT_IX  NONUNIQUE   VALID                   DEPARTMENT_ID                             



INFORMATION can give you even more in-depth information:

SQL> info+ employees
NAME             DATA TYPE           NULL  DEFAULT    LOW_VALUE             HIGH_VALUE            NUM_DISTINCT   HISTOGRAM  
*EMPLOYEE_ID     NUMBER(6,0)         No                   100                   206                   107            NONE       
 FIRST_NAME      VARCHAR2(20 BYTE)   Yes                  Adam                  Winston               91             NONE       
 LAST_NAME       VARCHAR2(25 BYTE)   No                   Abel                  Zlotkey               102            NONE       
 EMAIL           VARCHAR2(25 BYTE)   No                   ABANDA                WTAYLOR               107            NONE       
 PHONE_NUMBER    VARCHAR2(20 BYTE)   Yes                  011.44.1343.329268    650.509.4876          107            NONE       
 HIRE_DATE       DATE                No                   2001.   2008.   98             NONE       
 JOB_ID          VARCHAR2(10 BYTE)   No                   AC_ACCOUNT            ST_MAN                19             FREQUENCY  
 SALARY          NUMBER(8,2)         Yes                  2100                  24000                 58             NONE       
 COMMISSION_PCT  NUMBER(2,2)         Yes                  .1                    .4                    7              NONE       
 MANAGER_ID      NUMBER(6,0)         Yes                  100                   205                   18             FREQUENCY  
 DEPARTMENT_ID   NUMBER(4,0)         Yes                  10                    110                   11             FREQUENCY  

HR.EMP_JOB_IX         NONUNIQUE   VALID                   JOB_ID                                    
HR.EMP_NAME_IX        NONUNIQUE   VALID                   LAST_NAME, FIRST_NAME                     
HR.EMP_EMAIL_UK       UNIQUE      VALID                   EMAIL                                     
HR.EMP_EMP_ID_PK      UNIQUE      VALID                   EMPLOYEE_ID                               
HR.EMP_MANAGER_IX     NONUNIQUE   VALID                   MANAGER_ID                                
HR.EMP_DEPARTMENT_IX  NONUNIQUE   VALID                   DEPARTMENT_ID                             



Whilst all of this additional functionality is very welcome, the best bit, for me at least, is this…

SQL> info dbms_utility.get_time

/* Package SYS.DBMS_UTILITY */
    /*   RETURN NUMBER   */
     v_ret := SYS.DBMS_UTILITY.GET_TIME( );

SQL> info dbms_metadata.get_ddl

    /*   RETURN CLOB   */
     v_ret := SYS.DBMS_METADATA.GET_DDL(   OBJECT_TYPE   =>  p_IN_param0  /*   VARCHAR2   */,
                                           NAME          =>  p_IN_param1  /*   VARCHAR2   */,
                                           SCHEMA        =>  p_IN_param2  /*   VARCHAR2   */,
                                           VERSION       =>  p_IN_param3  /*   VARCHAR2   */,
                                           MODEL         =>  p_IN_param4  /*   VARCHAR2   */,
                                           TRANSFORM     =>  p_IN_param5  /*   VARCHAR2   */);


Yes, no longer will I have to scroll through the 86 members of DBMS_METADATA to find out the signature for GET_DDL.
I can now simply describe (OK, INFO) the package member directly. Just like going through a wormhole directly to the information I need (to stretch the L-space metaphor).


As the name suggests, DDL outputs the DDL for a given object. For example…

SQL> ddl regions


You also have the option of writing the output to a file. This command …

ddl regions regions_tab.sql

…writes the output to a file called regions_tab.sql, as well as the command line.

Whilst we’re on the subject of Time (well, saving some, at least)…


CTAS – Create Table as Select – without all of that typing…

CTAS regions new_regions

… generates DDL to create a new table called new_regions…

   (	"REGION_ID",

select * from REGIONS

As CTAS does use DBMS_METADATA, you can amend the output using that package’s SET_TRANFORM_PARAM procedure. For example :

SQL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', false)
anonymous block completed

SQL> ctas regions new_regions

   (	"REGION_ID",
select * from REGIONS

UPDATE – Jeff has posted a much better example of this feature here.

Remember, CTAS simply generates the DDL command to create the table and writes it to the buffer (and a file, if specified). It does not actually execute the command.


The LOAD command offers an alternative way of populating a table from a CSV file.

Let’s try using a topical example…

create table icc_wc_winners
    year number(4),
    country varchar2(30)

The csv file is in a sub-directory called cricket :


At present, LOAD has a bit of a problem recognising that you’ve chnaged directory using the CD command. However, the developers have fixed this and it should be available in a future release.
In the meantime though, we can get this to work by specifying the relative path for the csv file :

SQL> load icc_wc_winners cricket/wc_winners.csv
--Number of rows processed: 11
--Number of rows in error: 0
0 - SUCCESS: Load processed without errors

NOTE – you need to issue a commit for these records to be saved permanently.


Being a rather particular breed, developers tend to like their code formatted in a certain way. The trouble is, if you ask ten developers what that way should be, you’re likely to get ten different answers.
It is for this reason that Coding Standards documents tend to illicit an inordinate amount of controversy.
As I’ve noted previously, one possible solution to this is to let developers format their code in whatever way they see fit and then just run the code through a formatting tool that converts it to whatever the standard is.
All the main IDEs have their own formatting solutions. However, you can now do this at the command line.

For example, let’s try this query :

select dept.department_name, emp.first_name||' '||emp.last_name as empname
from employees emp
inner join departments dept
  on emp.department_id = dept.department_id
order by 1,2  

Using the new FORMAT command, we can, now apply the default SQLDeveloper formatting…

SQL> format buffer
  2    dept.department_name,
  3    emp.first_name
  4    ||' '
  5    ||emp.last_name AS empname
  6  FROM
  7    employees emp
  8  INNER JOIN departments dept
  9  ON
 10    emp.department_id = dept.department_id
 12*   1,2

Better still, we can even take the source file for the query, run it through the formatter and save the output to a new file :

SQL> format file hr.sql hr_yuk.sql

The resulting file looks like this :

  ||' '
  ||emp.last_name AS empname
  employees emp
INNER JOIN departments dept
  emp.department_id = dept.department_id
  1,2 /

As you can probably tell by the output filename I’ve chosen, I’m not particularly keen on the default formatting.
I’ll admit, this is largely subjective on my part. However, I would like the code to be formatted according to my preferences rather than the default.
Once again, the developers have a solution for this which should be available in the near future.
With this functionality in place, you could potentially leave the contentious business of formatting code to the standard until just before deployment. Running each program through a formatter setup to meet your coding standards may be an appealing option.


One of the major advantages IDE’s have over the command line is the ability to compare two schemas in different databases.
Up until now, this has not been possible from SQL*Plus without the use of a database link.
The BRIDGE command may well change all that.

At present, it’s functionality is restricted to creating a table in your current connection based on the results of a query run against a second connection that you specify. For example, to create a table in my current schema with the details of the tables in a remote hr schema….

SQL> bridge hr_tabs as "jdbc:oracle:thin:hr/the_hr_password@"(select table_name from user_tables);
Table hr_tabs : insert succeeded 
SQL> select * from hr_tabs;


 7 rows selected 


…where the_hr_password is the password for the HR user on the target database.

One point to note here is that bridge executes the remote connection and the Create Table independently of each other.
Therefore, it’s quite possible for the connection to fail and the table to be created anyway.

NOTE – it should be possible to get around this behaviour by specifiying WHENEVER SQLERROR EXIT. I’ve not managed to get this to work as I’d expect, but this could be because I’m doing something silly.

Another thing to be aware of is that the BRIDGE command you’ve issued will be retained in the HISTORY, complete with uid/password.
This is currently on the developers’ to-do list.


The last of the new commands to cover is APEX.

If you simply issue this command without any arguments, it will list all of the APEX Applications where the user you are currently connected as is defined as the owning schema.
The exception to this is if you’re connected as SYS as SYSDBA where you’ll get a full list of all APEX applications.

So, connected as user OSSCA :

SQL> apex
OSSCA2     100             Sample Database Application  Run and Develop  26-FEB-15              
OSSCA2     101             OSSCA UI                     Run Only         26-FEB-15              

APEX also provides the facility to export an application. By spooling the output, you can create the appropriate file :

spool my_apex_export.sql
apex export 100
spool off

…creates the APEX export file my_apex_export.sql, as well as outputting to the screen.


Whilst it’s still very early days for SQLCL, it’s potential impact is significant. I make no apologies for being excited about it.

Where to go for more SQLCL stuff

There’s a fair amount of information out there about SQLCL.

A good starting point would be Jeff’s presentation, which is available here.

Both Kris Rice and Barry McGillin are members of the development team and post regularly on the subject of SQLCL.

If you find any glitches, or have any questions, then the place to go is the SQLDeveloper OTN Forum.

Finally, as it’s around Easter Time, try entering the following at the SQLCL command line :

show sqldev2

Filed under: Oracle, SQL Tagged: alias, APEX, bridge, cd, colours in sqlcl, ctas, dbms_metadata.set_transform_param, ddl, format, history, information, load, multi-line editing in sqlcl, net, sdsql, sqlcl, sqlformat, tab completion in sqlcl

Bluemix IBM Containers Simple NodeJS Demo

Pas Apicella - Sun, 2015-04-12 01:40
I decided to give the IBM Containers service a quick test drive today. I used this very simple example available at the following GitHub URL

1. Clone the source code as follows

pas@192-168-1-4:~/temp$ git clone
Cloning into 'ibm-containers-simple-node-example'...
remote: Counting objects: 63, done.
remote: Total 63 (delta 0), reused 0 (delta 0), pack-reused 63
Unpacking objects: 100% (63/63), done.
Checking connectivity... done.

2. Build as shown below.

pas@Pass-MacBook-Pro:~/bluemix-apps/CONTAINERS/ibm-containers-simple-node-example$ docker build -t simplenode:latest .
Sending build context to Docker daemon 921.6 kB
Sending build context to Docker daemon
Step 0 : FROM
 ---> e9991fa102c2
Step 1 : ADD . /node
 ---> e06d64586fcb
Removing intermediate container 115a1f348c9c
Step 2 : WORKDIR /node
 ---> Running in bbbbb3e2044c
 ---> 341de1dca2a2
Removing intermediate container bbbbb3e2044c
Step 3 : RUN npm install
 ---> Running in c5157b73d74a
npm WARN package.json ibm-containers-simple-node-example@0.0.0 No repository field.
npm ERR! fetch failed
npm ERR! fetch failed
npm ERR! fetch failed
npm ERR! fetch failed
npm ERR! fetch failed
npm ERR! fetch failed
npm ERR! fetch failed
npm ERR! fetch failed
npm ERR! fetch failed
ejs@2.3.1 node_modules/ejs

express@4.12.3 node_modules/express
├── merge-descriptors@1.0.0
├── utils-merge@1.0.0
├── cookie-signature@1.0.6
├── methods@1.1.1
├── cookie@0.1.2
├── fresh@0.2.4
├── escape-html@1.0.1
├── range-parser@1.0.2
├── finalhandler@0.3.4
├── content-type@1.0.1
├── vary@1.0.0
├── parseurl@1.3.0
├── serve-static@1.9.2
├── content-disposition@0.5.0
├── path-to-regexp@0.1.3
├── depd@1.0.1
├── qs@2.4.1
├── proxy-addr@1.0.7 (forwarded@0.1.0, ipaddr.js@0.1.9)
├── debug@2.1.3 (ms@0.7.0)
├── send@0.12.2 (destroy@1.0.3, ms@0.7.0, mime@1.3.4)
├── type-is@1.6.1 (media-typer@0.3.0, mime-types@2.0.10)
├── etag@1.5.1 (crc@3.2.1)
├── on-finished@2.2.0 (ee-first@1.1.0)
└── accepts@1.2.5 (negotiator@0.5.1, mime-types@2.0.10)
 ---> 6a8303faa142
Removing intermediate container c5157b73d74a
Step 4 : EXPOSE 8080
 ---> Running in 2ef2338c54f0
 ---> 92fcbb0123ac
Removing intermediate container 2ef2338c54f0
Step 5 : ENTRYPOINT node /node/index.js
 ---> Running in 9abbc0dd1b10
 ---> 0c09d70174b1
Removing intermediate container 9abbc0dd1b10
Successfully built 0c09d70174b1

3. Log into IBM Containers

pas@Pass-MacBook-Pro:~/bluemix-apps/CONTAINERS$ ice login
API endpoint:



Targeted org

Select a space (or press enter to skip):
1. dev
2. apple
3. lemon
4. pas

Space> 1
Targeted space dev

API endpoint: (API version: 2.19.0)
Space:          dev
Authentication with container cloud service at completed successfully
You can issue commands now to the container service

Proceeding to authenticate with the container cloud registry at
Login Succeeded

4. Retrieve your current namespace

pas@Pass-MacBook-Pro:~/bluemix-apps/CONTAINERS$ ice namespace get

5. Tag the image to point to your private registry.

pas@Pass-MacBook-Pro:~/bluemix-apps/CONTAINERS/ibm-containers-simple-node-example$ ice --local tag -f simplenode:latest
Target is local host. Invoking docker with the given arguments...

6. Push the image to your private registry.

pas@Pass-MacBook-Pro:~/bluemix-apps/CONTAINERS/ibm-containers-simple-node-example$ ice --local push
Target is local host. Invoking docker with the given arguments...
The push refers to a repository [] (len: 1)
Sending image list
Pushing repository (1 tags)
Image 511136ea3c5a already pushed, skipping
Image a1a958a24818 already pushed, skipping
Image d0955f21bf24 already pushed, skipping
Image 9fec74352904 already pushed, skipping
Image a3d3e588211d already pushed, skipping
Image 19a35b496c0d already pushed, skipping
Image 1adb38c035f4 already pushed, skipping
Image f3c84ac3a053 already pushed, skipping
Image bf94729cc072 already pushed, skipping
Image cbc052ac72f1 already pushed, skipping
Image 12433edaed33 already pushed, skipping
Image c4a2c78444b7 already pushed, skipping
Image 300247e5330a already pushed, skipping
Image e9991fa102c2 already pushed, skipping
e06d64586fcb: Image successfully pushed
341de1dca2a2: Image successfully pushed
6a8303faa142: Image successfully pushed
92fcbb0123ac: Image successfully pushed
0c09d70174b1: Image successfully pushed
Pushing tag for rev [0c09d70174b1] on {}

7. Start a new container in IBM Containers from the image you just pushed.

pas@Pass-MacBook-Pro:~/bluemix-apps/CONTAINERS/ibm-containers-simple-node-example$ ice run --name simplenode_1 -p 8080 apples01/simplenode:latest

8.  Verify it's up and running

pas@Pass-MacBook-Pro:~/bluemix-apps/CONTAINERS/ibm-containers-simple-node-example$ ice ps

Container Id                         Name                   Group      Image                          Created      State    Private IP      Public IP       Ports

2261936e-4324-4f28-b800-10099471a306 simplenode_1                      apples01/simplenode:latest     Apr 10 23:13 Running                    [8080]

9. The following commands assign a public IP address to my container

pas@Pass-MacBook-Pro:~/bluemix-apps/CONTAINERS/ibm-containers-simple-node-example$ ice ip list
Number of addresses: 0
pas@Pass-MacBook-Pro:~/bluemix-apps/CONTAINERS/ibm-containers-simple-node-example$ ice ip request
Successfully obtained ip: ""
pas@Pass-MacBook-Pro:~/bluemix-apps/CONTAINERS/ibm-containers-simple-node-example$ ice ip list
Number of addresses: 1

Ip Address

pas@Pass-MacBook-Pro:~/bluemix-apps/CONTAINERS/ibm-containers-simple-node-example$ ice ip bind simplenode_1
Successfully bound ip

10. Access the container as shown below.

More Information
Categories: Fusion Middleware

opatch hangs on /sbin/fuser oracle

Vikram Das - Sat, 2015-04-11 18:30
Pipu pinged me today about opatch hanging. The opatch log showed this:

[Apr 11, 2015 5:24:13 PM]    Start fuser command /sbin/fuser $ORACLE_HOME/bin/oracle at Sat Apr 11 17:24:13 EDT 2015

I had faced this issue once before, but was not able to recall what was the solution.  So I started fresh.

As oracle user:

/sbin/fuser $ORACLE_HOME/bin/oracle hung

As root user

/sbin/fuser $ORACLE_HOME/bin/oracle hung

As root user

lsof hung.

Google searches about it brought up a lot of hits about NFS issues.  So I did df -h.

df -h also hung.

So I checked /var/log/messages and found many messages like these:

Apr 11 19:44:42 erpserver kernel: nfs: server not responding, still trying

That server has a mount called /R12.2stage that has the installation files for R12.2.
So I tried unmounting it:
umount /R12.2stageDevice Busy
umount -f /R12.2stageDevice Busy
umount -l /R12.2stage
df -h didn't hang any more.
Next I did strace /sbin/fuser $ORACLE_HOME/bin/oracle and it stopped here:
open("/proc/12854/fdinfo/3", O_RDONLY)  = 7fstat(7, {st_mode=S_IFREG|0400, st_size=0, ...}) = 0mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2b99de014000read(7, "pos:\t0\nflags:\t04002\n", 1024) = 20close(7)                                = 0munmap(0x2b99de014000, 4096)            = 0getdents(4, /* 0 entries */, 32768)     = 0close(4)                                = 0stat("/proc/12857/", {st_mode=S_IFDIR|0555, st_size=0, ...}) = 0open("/proc/12857/stat", O_RDONLY)      = 4read(4, "12857 (bash) S 12853 12857 12857"..., 4096) = 243close(4)                                = 0readlink("/proc/12857/cwd", " (deleted)"..., 4096) = 27rt_sigaction(SIGALRM, {0x411020, [ALRM], SA_RESTORER|SA_RESTART, 0x327bc30030}, {SIG_DFL, [ALRM], SA_RESTORER|SA_RESTART, 0x327bc30030}, 8) = 0alarm(15)                               = 0write(5, "@\20A\0\0\0\0\0", 8)          = 8write(5, "\20\0\0\0", 4)                = 4write(5, "/proc/12857/cwd\0", 16)       = 16write(5, "\220\0\0\0", 4)               = 4read(6,  
It stopped here. So I did Ctrl+C# # ps -ef |grep 12857oracle   12857 12853  0 Apr10 pts/2    00:00:00 -bashroot     21688  2797  0 19:42 pts/8    00:00:00 grep 12857
Killed this process
# kill -9 12857
Again I did strace /sbin/fuser $ORACLE_HOME/bin/oracle and it stopped at a different process this time that was another bash process.  I killed that process also.
I executed it for 3rd time: strace /sbin/fuser $ORACLE_HOME/bin/oracle
This time it completed.
Ran it without strace
/sbin/fuser $ORACLE_HOME/bin/oracle
It came out in 1 second.
Then I did the same process for lsof
strace lsof
and killed those processes were it was getting stuck.  Eventually lsof also worked.
Pipu retried opatch and it worked fine.
Stale NFS mount was the root cause of this issue.  It was stale because the source server was down for Unix security patching during weekend. 
Categories: APPS Blogs

Why LinkedIn Matters

Michael Feldstein - Sat, 2015-04-11 12:54

By Michael FeldsteinMore Posts (1024)

A few folks have asked me to elaborate on why I think LinkedIn is the most interesting—and possibly the most consequential—company in ed tech.

Imagine that you wanted to do a longitudinal study of how students from a particular college do in their careers. In other words, you want to study long-term outcomes. How did going to that college affect their careers? Do some majors do better than others? And how do alumni fare when compared to their peers who went to other schools? Think about how you would get the data. The college could ask alumni, but it would be very hard to get a good response rate, and even then, the data would go stale pretty quickly. There are governmental data sources you could look at, but there are all kinds of thorny privacy and regulatory issues.

There is only one place in the world I know of where bazillions of people voluntarily enter their longitudinal college and career information, keep it up-to-date, and actually want it to be public.


LinkedIn is the only organization I know of, public or private, that has the data to study long-term career outcomes of education in a broad and meaningful way. Nobody else comes close. Not even the government. Their data set is enormous, fairly comprehensive, and probably reasonably accurate. Which also means that they are increasingly in a position to recommend colleges, majors, and individual courses and competencies. An acquisition like gives them an ability to sell an add-on service—“People who are in your career track advanced faster when they took a course like this one, which is available to you for only X dollars”—but it also feeds their data set. Right now, schools are not reporting individual courses to the company, and it’s really too much to expect individuals to fill out comprehensive lists of courses that they took. The more that LinkedIn can capture that information automatically, the more the company can start searching for evidence that enables them to reliably make more fine-grained recommendations to job seekers (like which skills or competencies they should acquire) as well as to employers (like what kinds of credentials to look for in a job candidate). Will the data actually provide credible evidence to make such recommendations? I don’t know. But if it does, LinkedIn is really the only organization that’s in a position to find that evidence right now. This is the enormous implication of the acquisition that the press has mostly missed, and it’s also one reason of many why Pando Daily’s angle on the acquisition—“Did LinkedIn’s acquisition of Lynda just kill the ed tech space?“—is a laughable piece of link bait garbage. The primary value of the acquisition wasn’t content. It was data. It was providing additional, fine-grained nodes on the career graphs of their users. Which means that LinkedIn is likely to do more acquisitions and more partnerships that help accomplish the same end, including providing access of that data for companies and schools to do their own longitudinal outcomes research. Far from “killing ed tech,” this is the first step toward building an ecosystem.

Credit: Social Media Delivered


The post Why LinkedIn Matters appeared first on e-Literate.

APEX 5.0: Plug-In Attribute Enhancements

Patrick Wolf - Sat, 2015-04-11 08:51
In Oracle APEX 5.0 we have added several new features to Custom Plug-in Attributes to improve the usability of your Plug-ins when they are used by other developers. Show in Wizard Some Plug-ins do make use of the maximum number of 25 … Continue reading →
Categories: Development

Basic (newbie) install CoreOS on VirtualBox – Getting started with Docker

Marco Gralike - Sat, 2015-04-11 06:18
I got intrigued by this Dutch article mentioning Docker and CoreOS. So on this Saturday,…

LinkedIn: I Told You So (Sorta)

Michael Feldstein - Sat, 2015-04-11 05:54

By Michael FeldsteinMore Posts (1024)

In December 2012, I tweeted:

Let it be known that I was the first to predict that Coursera will be acquired by LinkedIn.

— Michael Feldstein (@mfeldstein67) December 5, 2012

At the time, Coursera was the darling of online ed startups. Since then, it has lost its way somewhat, while has taken off like a rocket. Which is probably one big reason why LinkedIn chose to acquire (rather than Coursera) for $1.5 billion. I still think it’s possible that they could acquire a MOOC provider as well, but Udacity seems like a better fit than Coursera at this point.

I’ve said it before and I’ll say it again: LinkedIn is the most interesting company in ed tech.

The post LinkedIn: I Told You So (Sorta) appeared first on e-Literate.

MariaDB and MaxScale

DBMS2 - Fri, 2015-04-10 10:48

I chatted with the MariaDB folks on Tuesday. Let me start by noting:

  • MariaDB, the product, is a MySQL fork.
  • MariaDB, product and company alike, are essentially a reaction to Oracle’s acquisition of MySQL. A lot of the key players are previously from MySQL.
  • MariaDB, the company, is the former SkySQL …
  • … which acquired or is the surviving entity of a merger with The Monty Program, which originated MariaDB. According to Wikipedia, something called the MariaDB Foundation is also in the mix.
  • I get the impression SkySQL mainly provided services around MySQL, especially remote DBA.
  • It appears that a lot of MariaDB’s technical differentiation going forward is planned to be in a companion product called MaxScale, which was released into Version 1.0 general availability earlier this year.

The numbers around MariaDB are a little vague. I was given the figure that there were ~500 customers total, but I couldn’t figure out what they were customers for. Remote DBA services? MariaDB support subscriptions? Something else? I presume there are some customers in each category, but I don’t know the mix. Other notes on MariaDB the company are:

  • ~80 people in ~15 countries.
  • 20-25 engineers, which hopefully doesn’t count a few field support people.
  • “Tiny” headquarters in Helsinki.
  • Business leadership growing in the US and especially the SF area.

MariaDB, the company, also has an OEM business. Part of their pitch is licensing for connectors — specifically LGPL — that hopefully gets around some of the legal headaches for MySQL engine suppliers.

MaxScale is a proxy, which starts out by intercepting and parsing MariaDB queries.

  • As you might guess, MaxScale has a sharding story.
    • All MaxScale sharding is transparent.
    • Right now MaxScale sharding is “schema-based”, which I interpret to mean as different tables potentially being on different servers.
    • Planned to come soon is “key-based” sharding, which I interpret to mean as the kind of sharding that lets you scale a table across multiple servers without the application needing to know that is happening.
    • I didn’t ask about join performance when tables are key-sharded.
  • MaxScale includes a firewall.
  • MaxScale has 5 “well-defined” APIs, which were described as:
    • Authentication.
    • Protocol.
    • Monitoring.
    • Routing.
    • Filtering/logging.
  • I think MaxScale’s development schedule is “asynchronous” from that of the MariaDB product.
  • Further, MaxScale has a “plug-in” architecture that is said to make it easy to extend.
  • One plug-in on the roadmap is replication into Hadoop-based tables. (I think “into” is correct.)

I had trouble figuring out the differences between MariaDB’s free and enterprise editions. Specifically, I thought I heard that there were no feature differences, but I also thought I heard examples of feature differences. Further, there are third-party products included, but plans to replace some of those with in-house developed products in the future.

A few more notes:

  • MariaDB’s optimizer is rewritten vs. MySQL.
  • Like other vendors before it, MariaDB has gotten bored with its old version numbering scheme and jumped to 10.0.
  • One of the storage engines MariaDB ships is TokuDB. Surprisingly, TokuDB’s most appreciated benefit seems to be compression, not performance.
  • As an example of significant outside code contributions, MariaDB cites Google contributing whole-database encryption into what will be MariaDB 10.1.
  • Online schema change is on the roadmap.
  • There’s ~$20 million of venture capital in the backstory.
  • Engineering is mainly in Germany, Eastern Europe, and the US.
  • MariaDB Power8 performance is reportedly great (2X Intel Sandy Bridge or a little better). Power8 sales are mainly in Europe.
Categories: Other


Jonathan Lewis - Fri, 2015-04-10 10:27

There’s a live example on OTN at the moment of an interesting class of problem that can require some imaginative thinking. It revolves around a design that uses a row in one table to hold the low and high values for a range of values in another table. The problem is then simply to count the number of rows in the second table that fall into the range given by the first table. There’s an obvious query you can write (a join with inequality) but if you have to join each row in the first table to several million rows in the second table, then aggregate to count them, that’s an expensive strategy.  Here’s the query (with numbers of rows involved) that showed up on OTN; it’s an insert statement, and the problem is that it takes 7 hours to insert 37,600 rows:

    SELECT 'ISRP-734', to_date('&DateTo', 'YYYY-MM-DD'),
           SNE.ID AS HLR
           SA_NUMBER_RANGES SNR          -- 10,000 rows
    ,      SA_SERVICE_SYSTEMS SSS        --  1,643 rows
    ,      SA_NETWORK_ELEMENTS SNE       --    200 rows
    ,      SA_MSISDNS M                  --    72M rows
    AND    SNR.ID_TYPE = 'M'
    AND    M.STATE  = 'AVL'

The feature here is that we are counting ranges of MSISDN: we take 10,000 number ranges (SNR) and join with inequality to a 72M row table. It’s perfectly conceivable that at some point the data set expands (not necessarily all at once) to literally tens of billions of rows that are then aggregated down to the 37,500 that are finally inserted.

The execution plan shows the optimizer joining the first three tables before doing a merge join between that result set and the relevant subset of the MSISDNs table – which means the MSISDNs have to be sorted and buffered (with a probably spill to disc) before they can be used. It would be interesting to see the rowsource execution stats for the query – partly to see how large the generated set became, but also to see if the ranges involved were so large that most of the time went in constantly re-reading the sorted MSISDNs from the temporary tablespace.

As far as optimisation is concerned, there are a couple of trivial things around the edges we can examine: we have 10,000 number ranges but insert 37,600 results, and the last stages of the plan generated those results so we’ve scanned and aggregated the sorted MSISDNs 37,600 times. Clearly we could look for a better table ordering that (eliminated any number ranges early), then did the minimal number of joins to MSISDN, aggregated, then scaled up to 37,600: with the best join order we might reduce the run time by a factor of 3 or more. (But that’s still a couple of hours run time.)

What we really need to do to make a difference is change the infrastructure in some way – prefereably invisibly to the rest of the application. There are a number of specific details relating to workload, read-consistency, timing, concurrency, etc. that will need to be considered, but broadly speaking, we need to take advantage of a table that effectively holds the “interesting” MSISDNs in sorted order. I’ve kept the approach simple here, it needs a few modifications for a production system. The important bit of the reports is the bit that produces the count, so I’m only going to worry about a two-table join – number ranges and msidn; here’s some model data:

execute dbms_random.seed(0)

create table msisdns
with generator as (
        select  --+ materialize
                rownum id
        from dual
        connect by
                level <= 1e4
        trunc(dbms_random.value(1e9,1e10))      msisdn
        generator       v1,
        generator       v2
        rownum <= 1e6

create table number_ranges
with generator as (
        select  --+ materialize
                rownum id
        from dual
        connect by
                level <= 1e4
        trunc(dbms_random.value(1e9,1e10))      from_number,
        trunc(dbms_random.value(1e9,1e10))      to_number
        generator       v1
        rownum  <= 1000

update number_ranges set
        from_number = to_number,
        to_number = from_number
        to_number < from_number


I’ve created a table of numbers with values between 10e9 and 10e10 to represent 1 million MSISDNs, and a list of 1,000 number ranges – making sure that the FROM number is not greater than the TO number. Now I need a “summary” table of the MSISDNs, which I’m going to create as an index-organized table:

create table tmp_msisdns (
        constraint tmp_pk primary key (msisdn, counter)
organization index
        row_number() over(order by msisdn)      counter

This is only a demonstration so I’ve haven’t bothered with production-like code to check that the MSISDNs I had generated were unique (they were); and I’ve casually included the row_number() as part of the primary key as a performance fiddle even though it’s something that could, technically, allow some other program to introduce bad data if I made the table available for public use rather than task specific.

Finally we get down to the report. To find out how many MSISDN values there are between the FROM and TO number in a range I just have to find the lowest and highest MSISDNs from tmp_msisdn in that range and find the difference between their counter values, and add 1. And there’s a very fast way to find the lowest or highest values when you have the appropriate index – the min/max range scan – but you have to access the table twice, once for the low, once for the high. Here’s the necessary SQL, with execution plan from

        nr.from_number, nr.to_number,
--      fr1.msisdn, fr1.counter,
--      to1.msisdn, to1.counter,
        1 + to1.counter - fr1.counter range_count
        number_ranges   nr,
        tmp_msisdns     fr1,
        tmp_msisdns     to1
        fr1.msisdn = (
                select min(msisdn) from tmp_msisdns where tmp_msisdns.msisdn >= nr.from_number
and     to1.msisdn = (
                select max(msisdn) from tmp_msisdns where tmp_msisdns.msisdn <= nr.to_number

| Id  | Operation                       | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT                |               |       |       |  4008 (100)|          |
|   1 |  NESTED LOOPS                   |               |  1000 | 38000 |  4008   (1)| 00:00:01 |
|   2 |   NESTED LOOPS                  |               |  1000 | 26000 |  2005   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL            | NUMBER_RANGES |  1000 | 14000 |     2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN             | TMP_PK        |     1 |    12 |     2   (0)| 00:00:01 |
|   5 |     SORT AGGREGATE              |               |     1 |     7 |            |          |
|   6 |      FIRST ROW                  |               |     1 |     7 |     3   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN (MIN/MAX)| TMP_PK        |     1 |     7 |     3   (0)| 00:00:01 |
|*  8 |   INDEX RANGE SCAN              | TMP_PK        |     1 |    12 |     2   (0)| 00:00:01 |
|   9 |    SORT AGGREGATE               |               |     1 |     7 |            |          |
|  10 |     FIRST ROW                   |               |     1 |     7 |     3   (0)| 00:00:01 |
|* 11 |      INDEX RANGE SCAN (MIN/MAX) | TMP_PK        |     1 |     7 |     3   (0)| 00:00:01 |

Predicate Information (identified by operation id):
   4 - access("FR1"."MSISDN"=)
   7 - access("TMP_MSISDNS"."MSISDN">=:B1)
   8 - access("TO1"."MSISDN"=)
  11 - access("TMP_MSISDNS"."MSISDN"<=:B1)

Execution time – with 1 million MSISDNs and 1,000 ranges: 0.11 seconds.

For comparative purposes, and to check that the code is producing the right answers, here’s the basic inequality join method:

        nr.from_number, nr.to_number, count(*) range_count
        number_ranges   nr,
        msisdns         ms
        ms.msisdn >= nr.from_number
and     ms.msisdn <= nr.to_number
group by
        nr.from_number, nr.to_number
order by

| Id  | Operation             | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT      |               |       |       |       |   472K(100)|          |
|   1 |  HASH GROUP BY        |               |   707K|    14M|  6847M|   472K (17)| 00:00:19 |
|   2 |   MERGE JOIN          |               |   255M|  5107M|       | 13492  (77)| 00:00:01 |
|   3 |    SORT JOIN          |               |  1000 | 14000 |       |     3  (34)| 00:00:01 |
|   4 |     TABLE ACCESS FULL | NUMBER_RANGES |  1000 | 14000 |       |     2   (0)| 00:00:01 |
|*  5 |    FILTER             |               |       |       |       |            |          |
|*  6 |     SORT JOIN         |               |  1000K|  6835K|    30M|  3451   (7)| 00:00:01 |
|   7 |      TABLE ACCESS FULL| MSISDNS       |  1000K|  6835K|       |   245  (14)| 00:00:01 |

Predicate Information (identified by operation id):
   5 - filter("MS"."MSISDN"<="NR"."TO_NUMBER")
   6 - access("MS"."MSISDN">="NR"."FROM_NUMBER")

The two queries produced the same results (apart from ordering); but the second query took 2 minutes 19.4 seconds to complete.



In a moment of idle curiosity I recreated the data with 40 Million rows in the MSISDNs table to get some idea of how fast the entire report process could go when re-engineered (remember the OP has 72M rows, but select the subset flagged as ‘AVL’). It took 1 minute 46 seconds to create the IOT – after which the report for 1,000 number ranges still took less than 0.2 seconds.






Licensing Oracle in a public cloud: the CPU calculation impact

Pythian Group - Fri, 2015-04-10 09:18

First of all a disclaimer: I don’t work for Oracle nor do I speak for them. I believe this information to be correct, but for licensing questions, Oracle themselves have the final word.

With that out of the way, followers of this blog may have seen some of the results from my testing of actual CPU capacity with public clouds like Amazon Web Services, Microsoft Azure, and Google Compute Engine. In each of these cases, a CPU “core” was actually measured to be equivalent to an x86 HyperThread, or half a physical core. So when provisioning public cloud resources, it’s important to include twice as many CPU cores as the equivalent physical hardware. The low price and elasticity of public cloud infrastructure can however offset this differential, and still result in a cost savings over physical hardware.

One place this difference in CPU core calculation can have a significant impact, however, is software licensing. In this post I’ll look at Oracle database licensing in particular.

Oracle databases can be licensed using many metrics, including unlimited use agreements, embedded licenses, evaluation/developer licenses, partner licenses, and many more. But for those without a special agreement in place with Oracle, there are two ways to license products: Named User Plus (NUP) and processor licenses. NUP licenses are per-seat licenses which have a fixed cost per physical user or non-user device. The definition of a user is very broad, however. Quoting the Oracle Software Investment Guide:

Named User Plus includes both humans and non-human operated devices. All human users and non-human operated devices that are accessing the program must be licensed. A non-human operated device can be many things, such as a temperature-monitoring device. It is important to note that if the device is operated by a person, then this person must be licensed. As described in illustration #1, the 400 employees who are operating the 30 forklifts must be licensed because the forklift is not a “non-human operated device”.

So, if the application has any connection outside the organization (batch data feeds and public web users would be examples), it’s very difficult to fit the qualifications to count as NUP licenses.

Now, this leaves per-processor licenses, using processor cores that can potentially run the database software as licensing metric. When running in a public cloud, however, there is an immediate issue, which is your Oracle instance could presumably run on any of the thousands of servers owned by the cloud provider, so unique physical processors are virtually impossible to count. Fortunately, Oracle has provided a way to properly license Oracle software in public cloud environments: Licensing Oracle Software in the Cloud Computing Environment. It sets out a few requirements, including:

  • Amazon EC2, Amazon S3, and Microsoft Azure are covered under the policy.
  • There are limits to the counting of sockets and the number of cores per instance for Standard Edition and Standard Edition One.

But most importantly is the phrase customers are required to count each virtual core as equivalent to a physical core. Knowing that each “virtual core” is actually half a physical core, it can shift the economics of public cloud usage for Oracle database significantly.

Here’s an example of a general-purpose AWS configuration and a close equivalent on physical hardware. I’m excluding costs of external storage and datacenter costs (power, bandwidth, etc) from the comparison.

  • m3.2xlarge
  • 8 virtual / 4 physical CPU cores (from an E5-2670 processor at 2.6GHz)
  • 30GB RAM
  • 2x80GB local SSD storage
  • 3-year term

Total: $2989 upfront

A physical-hardware equivalent:

  • A single quad-core E5-2623 v3 processor at 3GHz
  • 32GB RAM
  • Oracle standard edition one
  • 2x120GB local SSD
  • 3-year 24×7 4hr on-site service

I priced this out at and came out with a total of $3761.

Now let’s add in an Oracle license. From the Oracle Price List, a socket of Standard Edition One costs $5800, with an additional $1276/year for support. Due to the counting of CPU cores, our AWS hardware requires two sockets of licensing. So instead of saving $772, we end up paying $9628 more.

 Standard Edition One

If we were to use Oracle Enterprise edition (excluding any options or discounts), that becomes an extra $157,700. Not small change anymore.

 Enterprise Edition

So before you make the jump to put your Oracle databases on a public cloud, check your CPU core counts to avoid unexpected licensing surprises.

Categories: DBA Blogs


Dominic Brooks - Fri, 2015-04-10 08:59

Just been tearing my hair out on some unexpected SQL behaviour in SQL Developer.
Eventually I managed to get to the root cause of the problem as illustrated below.
There is a silent failure which can cause unexpected results from your SQL.


SQL> var ps number
SQL> exec :ps := 4001644945;

PL/SQL procedure successfully completed.

SQL> select :ps from dual;



SQL Developer v3.2 and v4.0 via “Run As Script(F5)”:

var ps number
exec :ps := 4001644945;
select :ps from dual;

anonymous block completed

Looks like SQL Developer is running into an integer problem and silently “dealing” with it.

var ps number
exec :ps := 2147483647
select :ps from dual;
exec :ps := 2147483648
select :ps from dual;

anonymous block completed

anonymous block completed

No such problem when you do a Run (F9) and use the bind dialog.
One to watch out for if you do a lot of sql execution via var like me.
Think I’ve seen it before. Think I might even have blogged about it before!

Come See Integrigy at Collaborate 2015

Come see Integrigy's session at Collaborate 2015 in Las Vegas ( Integrigy is presenting the following paper:

IOUG #763
Detecting and Stopping Cyber Attacks against Oracle Databases
Monday, April 13th, 9:15 - 11:30 am
North Convention, South Pacific J

If you are going to Collaborate 2015, we would also be more than happy to talk with you about your Oracle security or questions. If you would like to talk with us while at Collaborate, please contact us at


Tags: Conference
Categories: APPS Blogs, Security Blogs

Log Buffer #418: A Carnival of the Vanities for DBAs

Pythian Group - Fri, 2015-04-10 08:20

This Log Buffer edition has collected some of the valuable blog posts from different databases like Oracle, SQL Server and MySQL.


  • Accessing HDFS files on a local File system using mountable HDFS – FUSE
  • enq: TM – contention
  • The Four A’s of Data Management
  • ODI, Big Data SQL and Oracle NoSQL
  • Using the RIDC Client to Interface with Oracle Webcenter Content

SQL Server:

  • SQL Server 2014 has introduced a rebuilt Cardinality Estimator (CE) with new algorithms
  • Creating a multi-option parameter report for SQL Server Reporting Services
  • Re-factoring a database object can often cause unexpected behavior in the code that accesses that object
  • What is Database Continuous Integration?
  • Deleting Historical Data from a Large Highly Concurrent SQL Server Database Table


For years it was very easy to defend InnoDB’s advantage over competition. Covering index reads were saving I/O operations and CPU everywhere, table space and I/O management allowed focusing on database and not on file systems or virtual memory behaviors, and for the past few years InnoDB compression was the way to have highly efficient OLTP.

InnoDB locks and deadlocks with or without index for different isolation level.

pquery binaries with statically included client libs now available!

MySQL Group Replication – mysql-5.7.6-labs-group-replication.

MySQL 5.7 aims to be the most secure MySQL Server release ever, and that means some significant changes in SSL/TLS.

Categories: DBA Blogs

Log Buffer #417: A Carnival of the Vanities for DBAs

Pythian Group - Fri, 2015-04-10 08:06

This Log Buffer travels wide and deep to scour through the Internet to bring some of the most valuable and value-adding blog posts from Oracle, SQL Server and MySQL.


What is SQLcl ? SQLcl is a new command line interface like SQL*PLUS coming along with SQL Developper 4.1 Early Adopter. It’s a lightweight tool (only 11MB) developed by the SQL Developer team, which is fully compatible with Windows and Unix/Linux.  Also, you don’t need to install it so it’s totally portable.

Find Users with DBA Roles.

Virtual Compute Appliance 2.0.2 Released.

In case you are not familiar with WLST (the WebLogic Scripting Tool), it is a powerful scripting runtime for administering WebLogic domains.

The following article gives some useful hints-and-tips Richard used recently in helping people customizing tables and lists-of-values using Page Composer.

SQL Server:

With the idea of a generic Dacpac defined by international standard, comes the potential for a Visual Studio developer. This uses SSDT to create a generic database model to a SQL-92 compliant standard that can then be deployed to any one of the major RDBMSs.

Using the APPLY operator to reduce repetition and make queries DRYer.

Image a situation when you use the SQL Server RAND() T-SQL function as a column in a SELECT statement, and the same value is returned for every row as shown below. In this tip, Dallas Snider explains how you can get differing random values on each row.

This articles describes two ways to shred Unicode Japanese character from xls files into SQL Server table using SSIS.

Arshad Ali demonstrates how you can use the command line interface to tune SQL queries and how you can use SQL Server Profiler to capture the workload for tuning with Database Engine Tuning Advisor.


Postgres-Performance seit 7.4.

The Ubuntu 12.04.3 LTS release only provides MySQL 5.1 and MySQL 5.5 using the default Ubuntu package manager.

As part of a MySQL 5.5 to MySQL 5.6 upgrade across several Ubuntu servers of varying distros an audit highlighted a trivial but interesting versioning identification error in Ubuntu’s packaging of MySQL.

MySQL 5.6 will now automatically recreate the InnoDB redo log files during a MySQL restart if the size (or number) of these logs changes, i.e. a change to innodb_log_file_size.

Mermaids have the same probability of fixing your permission problems, but people continue believing in the FLUSH PRIVILEGES myth.

Categories: DBA Blogs

Disable Lock Escalation in SQL Server

Pythian Group - Fri, 2015-04-10 07:55

If a lot of rows or pages are locked, the SQL Server escalates to a table-level lock, to save resources. Each single lock takes approx. 100 bytes. So if you have many locks it takes a lot of resources to manage them. (There is a great blog about lock escalation, if you want some more info:


Until SQL Server 2008, there was no way to change the lock escalation for a single table. You could deactivate the escalation for the server by using the Trace Flags:

  • 1211 - Disables Lock Escalation completely – allows to use 60% of the allocated memory – if 60% of memory is used and more locking is needed you will get an out-of-memory error.
  • 1224 - Disables Lock Escalation until the memory threshold of 40% allocated memory is reached – after that Lock Escalation is enabled.


But that was in most cases not a good choice and caused a lot of performance problems. In SQL-Server 2008 and above there is a new table option (ALTER-TABLE) that can be used to change the default Lock-Escalation. This helps you if you have a table where you want to disable the escalation or if the table is partitioned.


On a partitioned table activating the AUTO Option can improve concurrency, by escalating the locks to the partition-level and not to the table-level.

ALTER TABLE – table option:


  • AUTO (should be considered if you have a partitioned table)
  • If tables is partitioned – the locks will be escalated to the partition-level
  • If table is not partitioned – the locks will be escalated to the table-level
  • Default behavior
  • Locks are escalated to the table-level
  • Lock escalation to the table-level is deactivated in most cases
  • In some necessary cases it’s allowed to escalate to the table-level

This is a cool feature, that are many developers are not aware of.

Thanks for Reading!

Categories: DBA Blogs