Skip navigation.

Feed aggregator

Notes on predictive modeling, November 2, 2014

DBMS2 - Sun, 2014-11-02 05:49

Following up on my notes on predictive modeling post from three weeks ago, I’d like to tackle some areas of recurring confusion.

Why are we modeling?

Ultimately, there are two reasons to model some aspect of your business:

  • You generally want insight and understanding.
    • This is analogous to why you might want to do business intelligence.
    • It commonly includes a search for causality, whether or not “root cause analysis” is exactly the right phrase to describe the process.
  • You want to do calculations from the model to drive wholly or partially automated decisions.
    • A big set of examples can be found in website recommenders and personalizers.
    • Another big set of examples can be found in marketing campaigns.
    • For an example of partial automation, consider a tool that advises call center workers.

How precise do models need to be?

Use cases vary greatly with respect to the importance of modeling precision. If you’re doing an expensive mass mailing, 1% additional accuracy is a big deal. But if you’re doing root cause analysis, a 10% error may be immaterial.

Who is doing the work?

It is traditional to have a modeling department, of “data scientists” or SAS programmers as the case may be. While it seems cool to put predictive modeling straight in the hands of business users — some business users, at least — it’s rare for them to use predictive modeling tools more sophisticated than Excel. For example, KXEN never did all that well.

That said, I support the idea of putting more modeling in the hands of business users. Just be aware that doing so is still a small business at this time.

“Operationalizing” predictive models

The topic of “operationalizing” models arises often, and it turns out to be rather complex. Usually, to operationalize a model, you need:

  • A program that generates scores, based on the model.
  • A program that consumes scores (for example a recommender or fraud alerter).

In some cases, the two programs might be viewed as different modules of the same system.

While it is not actually necessary for there to be a numerical score — or scores — in the process, it seems pretty common that there are such. Certainly the score calculations can create a boundary for loose-coupling between model evaluation and the rest of the system.

That said:

  • Sometimes the scoring is done on the fly. In that case, the two programs mentioned above are closely integrated.
  • Sometimes the scoring is done in batch. In that case, loose coupling seems likely. Often, there will be ETL (Extract/Transform/Load) to make the scores available to the program that will eventually use them.
  • PMML (Predictive Modeling Markup Language) is good for some kinds of scoring but not others. (I’m not clear on the details.)

In any case, operationalizing a predictive model can or should include:

  • A process for creating the model.
  • A process for validating and refreshing the model.
  • A flow of derived data.
  • A program that consumes the model’s outputs.

Traditional IT considerations, such as testing and versioning, apply.

What do we call it anyway?

The term “predictive analytics” was coined by SPSS. It basically won. However, some folks — including whoever named PMML — like the term “predictive modeling” better. I’m in that camp, since “modeling” seems to be a somewhat more accurate description of what’s going on, but I’m fine with either phrase.

Some marketers now use the term “prescriptive analytics”. In theory that makes sense, since:

  • “Prescriptive” can be taken to mean “operationalized predictive”, saving precious syllables and pixels.
  • What’s going on is usually more directly about prescription than prescription anyway.

Edit: Ack! I left the final paragraph out of the post, namely:

In practice, however, the term “prescriptive analytics” is a strong indicator of marketing nonsense. Predictive modeling has long been used to — as it were — prescribe business decisions; marketers who use the term “prescriptive analytics” are usually trying to deny that very obvious fact.

Categories: Other

Analytics for lots and lots of business users

DBMS2 - Sun, 2014-11-02 05:45

A common marketing theme in the 2010s decade has been to claim that you make analytics available to many business users, as opposed to your competition, who only make analytics available to (pick one):

  • Specialists (with “PhD”s).
  • Fewer business users (a thinner part of the horizontally segmented pyramid — perhaps inverted — on your marketing slide, not to be confused with the horizontally segmented pyramids — perhaps inverted — on your competition’s marketing slides).

Versions of this claim were also common in the 1970s, 1980s, 1990s and 2000s.

Some of that is real. In particular:

  • Early adoption of analytic technology is often in line-of-business departments.
  • Business users on average really do get more numerate over time, my three favorite examples of that being:
    • Statistics is taught much more in business schools than it used to be.
    • Statistics is taught much more in high schools than it used to be.
    • Many people use Excel.

Even so, for most analytic tools, power users tend to be:

  • People with titles or roles like “business analyst”.
  • More junior folks pulling things together for their bosses.
  • A hardcore minority who fall into neither of the first two categories.

Asserting otherwise is rarely more than marketing hype.

Related link

Categories: Other

Does Increasing An Oracle Background Process OS Priority Improve Performance?

Does Increasing An Oracle Background Process OS Priority Improve Performance?
Does increasing an Oracle Database background process operating system priority improve performance? As you might expect, the answer is, "It depends."

In this posting I will explain the results of an experiment where I increase the Oracle Database 12c log writer background processes operating system priority.

In my experiment I created a clear CPU bottleneck and the top wait event was log file parallel write. I gathered some data. Next I increased all the log writer background process priorities. Again, I gathered some data and then I analyzed the two data sets. The results were disappointing, not surprising, but a fundamental rule in performance tuning was demonstrated and reinforced.

You can download the "analysis pack" which contains the raw experimental data, histogram, statistical R results and the R statical package commands HERE. You can download total time delta reporting script (ttpctx.sql) I show below, which is contained within my OraPub System Monitor (OSM) Toolkit HERE.

Before I could gather some data, I needed to create the appropriate system load, the data gather scripts and the data analysis scripts. Here's a quick overview of each.

Increasing The LGWR Processes PriorityIf you are not familiar with changing Oracle Database background OS process priority, I blogged about how to do this HERE.

My experiment consisted of creating two situations and statistically comparing them to see if increasing the LGWR background process OS priority increased performance. The only difference in the "two situations" was the instance parameter, _high_priority_processes. For the "normal priority" part of the experiment, the default "LMS*|VKTM" was used. For the "high priority" part of the experiment the parameter was changed to "LMS*|VKTM|LG*". The "LG*" caused the increase in the Linux OS priority of all log writer processes from the default 19 to 41.

Ready for some version specifics? Oracle continues to make OS priority related instance parameter changes... even within 12c releases. Since this experiment was done with Oracle Database version the parameter default was "LMS*|VKTM" not "LMS*" as with version Also, in VKTM is placed into a new parameter, _highest_priority_processes.

Generating The DML With CPU Bottleneck Load
To generate the DML workload, I used my OP Load Generator. You can download it HERE. It's quick, easy and I'm familiar with it. I kept increasing the number of DML processes until the CPU utilization was pegged at 100%. While the database server was clearly out of CPU power, the log writer background processes were rarely seen as the top CPU consuming process. I used the "top" program to monitor the process activity. The top CPU consuming processes were almost always the Oracle server/foreground/shadow processes. As I'll explain in the Results section below, this has significant ramifications on the results.

Oracle Time Based Analysis Summary
The data collection script was similar to the one I used back in 2012 when collecting data regarding Oracle's commit write facility. Essentially, I collected multiple three minute samples of the delta elapsed time, user commits, total non-idle wait time and CPU consumption. In both the normal and high priority runs, around 99% of the database time was CPU consumption and 1% of the time Oracle non-idle wait time. Also the top wait event (~70%) was log file parallel write (display name: log file redo write).

If you want master an Oracle Time Based Analysis, check out my online seminar, Tuning Oracle Using An AWR Report. It will teach you how to using an AWR report to optimize Oracle performance so users will feel the difference.

The OraPub System Monitor Toolkit script ttpctx.sql report below was taken during the "normal priority" log writer load.

SQL> @ttpctx

Database: prod35 16-MAY-14 06:07pm
Report: ttpctx.sql OSM by OraPub, Inc. Page 1
Total Time Activity (39 sec interval)

Avg Time Time Wait
Time Component % TT % WT Waited (ms) (sec) Count(k)
------------------------------------- ------- ------- ----------- ----------- --------
CPU consumption: Oracle SP + BG procs 98.91 0.00 0.000 238.716 0
log file redo write 0.73 67.56 8.082 1.770 0
control file parallel write 0.18 16.41 33.077 0.430 0
target log write size 0.05 4.20 6.111 0.110 0
oracle thread bootstrap 0.03 3.05 40.000 0.080 0
os thread creation 0.02 1.53 20.000 0.040 0
commit: log file sync 0.01 0.76 10.000 0.020 0
enq: CR - block range reuse ckpt 0.01 0.76 20.000 0.020 0
Disk file operations I/O 0.00 0.00 0.000 0.000 0

The OSM report below was taken during the "high priority" log writer load.

SQL> @ttpctx

Database: prod35 16-MAY-14 09:25pm
Report: ttpctx.sql OSM by OraPub, Inc. Page 1
Total Time Activity (41 sec interval)

Avg Time Time Wait
Time Component % TT % WT Waited (ms) (sec) Count(k)
------------------------------------- ------- ------- ----------- ----------- --------
CPU consumption: Oracle SP + BG procs 98.92 0.00 0.000 238.733 0
log file redo write 0.83 77.01 8.272 2.010 0
control file parallel write 0.08 7.28 14.615 0.190 0
target log write size 0.05 4.98 5.909 0.130 0
oracle thread bootstrap 0.03 3.07 40.000 0.080 0
os thread creation 0.02 1.92 25.000 0.050 0
commit: log file sync 0.01 0.77 10.000 0.020 0
enq: CR - block range reuse ckpt 0.01 0.77 20.000 0.020 0
enq: RO - fast object reuse 0.00 0.38 10.000 0.010 0

Data Collection
For the normal priority load 27 three minute samples where collected. For the high priority situation there were 30 three minute samples collected. (I forgot why there was only 27 samples collected for the normal priority.)  I collected the elapsed time, total non-idle wait time, total CPU consumption (v$sys_time_model: db_cpu + background cpu time) and total user commits.

In this experiment more user commits processed per second means better performance.

Experimental Results
I used the free statistics package "R" ( to analyze the data. I demonstrate how to get, install and use "R" in my online video seminar, Using Skewed Data To Your Advantage.

With the normal log writer process priority, an average of 984.5 commits/sec and a median of 983.0 commits/sec occurred. With the LG* high process priority, an average of 993.6 commits/sec and a median of 991.0 commits/sec occurred. While the "high priority" situation was able to process more commits per second, is this statistically significant?

The red "smoothed" histogram is the normal priority situation and the blue smoothed histogram is when the log writers were set to the higher priority. The more separated the two histograms the more "different" the sample sets, the more likely there is a statistically significant difference and the more likely a user would feel the difference. Looking at the above histograms plot, there does not appear to be a real difference. But let's do a proper significance test!

Because both sample sets are normally distributed (details are in the Analysis Pack), I could use a simple t-test. R produced a p-value of 0.04451. To be statistically "different" I want the p-value to be less than 0.05 and it is. What does this mean?

While statistically and numerically the commit rates are different, I wouldn't expect any special performance tuning award! In fact, the hassles with cycling a production instance and setting underscore/hidden parameters would make it very unlikely I would increase the OS priority of the log writer background processes. I want to see a big performance difference.

To Summarize... What I Learned... Again
This situation is a perfect example of focusing on the wrong thing! While there is a clear operating system CPU bottleneck and the top wait event is about redo, the log writers are not suffering from a want/need of CPU resources. I suspect the server processes want more CPU resources, but they are NOT the processes we increased their OS priority.

If the log writers were suffering from a lack of CPU resources and fighting for CPU resources, I would expect to see them consuming CPU resources along with the Oracle server processes. And I would definitely expect to see them near the top of the "top" process monitor... especially when their priority has been increased!

Because of this "misguided" tuning effort, this experiment does not build a case for or against changing the log writer priority. What it reinforces is in our quest to optimize performance, make sure we focus on the right thing.

As a side note, this is a great statistical analysis example for two reasons. First, our samples sets look similar, but statistically they are not. Second, while they are statistically different, the performance impact will not be very different. And my guess is the users won't feel a thing... except frustration and anger.

To Super Summarize
When considering increasing a background process's operating system priority, make sure the process is in need of CPU and is not able to get it. In this situation, the DBA could have been mislead by the CPU bottleneck. But upon closer inspection of the log writers from an operating system perspective and knowing the wait event "log file parallel write" is probably more about IO than CPU (Oracle perspective) it would be unlikely that increasing the log writer processes OS priority would help increase the commits per second.

Thanks for reading!


Categories: DBA Blogs

StatsPack and AWR Reports -- Bits and Pieces -- 2

Hemant K Chitale - Sat, 2014-11-01 08:52
This is the second post in a series on reading StatsPack and AWR reports.
(The first is available here)

Comparing Reports :

Here are two 9.2 StatsPack extracts from one database:

Extract A  : 9.2 StatsPack
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 56,031.63 3,084.68
Logical reads: 68,286.24 3,759.32
Block changes: 314.88 17.33
Physical reads: 842.92 46.40
Physical writes: 134.76 7.42
User calls: 271.32 14.94
Parses: 146.46 8.06
Hard parses: 7.37 0.41
Sorts: 93.83 5.17
Logons: 0.33 0.02
Executes: 296.70 16.33
Transactions: 18.16

Extract B : 9.2 StatsPack
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 89,615.76 2,960.48
Logical reads: 210,302.81 6,947.42
Block changes: 541.83 17.90
Physical reads: 1,465.04 48.40
Physical writes: 161.68 5.34
User calls: 213.82 7.06
Parses: 125.28 4.14
Hard parses: 6.13 0.20
Sorts: 104.31 3.45
Logons: 0.35 0.01
Executes: 664.81 21.96
Transactions: 30.27

Extract B shows a higher volume of activity -- Transactions per second, Redo Size per second, Logical Reads per second.
Actually, although these are both from the same database but for two different time-windows on different dates.  The fact that the time-windows aren't the same make the comparisons difficult.  Extract A is for an 11-hour window on day 1 and Extract B is for a 6-hour window on day 6.  The two windows aren't strictly comparable.  You have to be very careful when comparing StatsPack or AWR reports, even when from the same database if they don't cover comparable time-windows with similar loads.  The first Report includes non-peak hours, the second is for Peak hours only. The level of activity averaged out over 11hours that include non-peak hours isn't strictly comparable with a 6hour peak window.

Extract C : 10.2 AWR
Load Profile
Per SecondPer TransactionRedo size: 520,776.15 50,948.36Logical reads: 353,525.71 34,585.98Block changes: 1,854.93 181.47Physical reads: 14,285.23 1,397.55Physical writes: 295.84 28.94User calls: 1,265.14 123.77Parses: 822.64 80.48Hard parses: 15.40 1.51Sorts: 168.09 16.44Logons: 0.16 0.02Executes: 1,040.31 101.78Transactions: 10.22 
Extract D : 10.2 AWRLoad Profile
Per SecondPer TransactionRedo size: 517,862.01 54,681.39Logical reads: 288,341.09 30,446.12Block changes: 1,879.27 198.43Physical reads: 12,820.96 1,353.77Physical writes: 323.90 34.20User calls: 1,115.78 117.82Parses: 719.39 75.96Hard parses: 17.28 1.82Sorts: 95.74 10.11Logons: 0.15 0.02Executes: 935.33 98.76Transactions: 9.47 
Extracts C and D are from the same database for the same time-window on 2 consecutive workdays.  They are comparable.  A difference of 10% for some statistic may not be significant.  Understand that every business, every activity, every system will have some "normal" fluctuations day-to-day.


Categories: DBA Blogs

The First PASS Summit Bloggers’ Meetup

Pythian Group - Fri, 2014-10-31 13:02

We are stoked to announce the first ever PASS Summit Bloggers’ Meetup!

What: PASS Summit Bloggers’ Meetup 2014
When: Thursday, November 6th, 5pm – 7pm
Where: Sports Restaurant & Bar, 140 4th Ave North, Suite 130, Seattle, WA 98109
How: Please comment with “COUNT ME IN” if coming — we need to know attendance numbers.

We’re excited to meet old friends, and make new ones in Seattle this year. Pythian will sponsor munchies and drinks. There will be a networking contest with some cool prizes, plus everyone will get their very own PASS Summit Bloggers Meetup shirt! Meetups are a great way to make new friends in the community, so come on out — all bloggers are welcome!

If you are planning to attend, please comment here with the phrase “COUNT ME IN”. This will help us ensure we have the attendance numbers right. Please provide your blog URL with your comment — it’s a Bloggers Meetup after all! Make sure you comment here if you are attending so that we have enough room, food, and drinks.

The location is perfect to get ready for the appreciation event — minutes walk from EMP Museum! Snacks and drinks before the big event and mingle with fellow bloggers. What can be better?

Of course, do not forget to blog and tweet about this year’s bloggers meetup using #Summit14 #sqlpass. See you there!

Categories: DBA Blogs


Jonathan Lewis - Fri, 2014-10-31 11:31

No, not the 10th posting about first_rows() this week – whatever it may seem like – just an example that happens to use the “calculate costs for fetching the first 10 rows” optimizer strategy and does it badly. I think it’s a bug, but it’s certainly a defect that is a poster case for the inherent risk of using anything other than all_rows optimisation.  Here’s some code to build a couple of sample tables:


create table t1
with generator as (
	select	--+ materialize
		rownum id 
	from dual 
	connect by 
		level <= 1e4
	rownum					id,
	trunc(dbms_random.value(1,1000))	n1,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
	generator	v1,
	generator	v2
	rownum <= 1e6

create index t1_n1 on t1(id, n1);

create table t2
with generator as (
	select	--+ materialize
		rownum id 
	from dual 
	connect by 
		level <= 1e4
	rownum					id,
	trunc(dbms_random.value(10001,20001))	x1,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
	generator	v1,
	generator	v2
	rownum <= 1e6

create index t2_i1 on t2(x1);

		ownname		 => user,
		tabname		 =>'T1',
		estimate_percent => 100,
		method_opt	 => 'for all columns size 1'

		ownname		 => user,
		tabname		 =>'T2',
		estimate_percent => 100,
		method_opt	 => 'for all columns size 1'


create or replace view  v1
	id, n1, small_vc, padding
from	t1 
where	n1 between 101 and 300
union all
	id, n1, small_vc, padding
from	t1 
where	n1 between 501 and 700

The key feature of this demonstration is the UNION ALL view and what the optimizer does with it when we have first_rows_N optimisation – this is a simplified model of a production problem I was shown a couple of years ago, so nothing special, nothing invented. Here’s a query that behaves badly:

	/*+ gather_plan_statistics */
where =
and	t2.x1 = 15000

I’m going to execute this query in three different ways – as is, using all_rows optimisation; as is, using first_rows_10 optimisation, then using all_rows optimisation but with the necessary hints to make it follow the first_rows_10 execution path. Here are the resulting plans from an instance of (the same thing happens in

first_rows_10 plan
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT                     |       |     1 |    35 |   107   (0)| 00:00:01 |
|*  1 |  HASH JOIN                           |       |     1 |    35 |   107   (0)| 00:00:01 |
|   2 |   VIEW                               | V1    |    24 |   600 |     4   (0)| 00:00:01 |
|   3 |    UNION-ALL                         |       |       |       |            |          |
|*  4 |     TABLE ACCESS FULL                | T1    |    12 |   240 |     2   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL                | T1    |    12 |   240 |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID BATCHED| T2    |   100 |  1000 |   103   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN                  | T2_I1 |   100 |       |     3   (0)| 00:00:01 |

all_rows plan
| Id  | Operation                              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT                       |       |    40 |  1400 |   904   (1)| 00:00:01 |
|   1 |  NESTED LOOPS                          |       |    40 |  1400 |   904   (1)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED  | T2    |   100 |  1000 |   103   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                    | T2_I1 |   100 |       |     3   (0)| 00:00:01 |
|   4 |   VIEW                                 | V1    |     1 |    25 |     8   (0)| 00:00:01 |
|   5 |    UNION ALL PUSHED PREDICATE          |       |       |       |            |          |
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1    |     1 |    20 |     4   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN                  | T1_N1 |     1 |       |     3   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1    |     1 |    20 |     4   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN                  | T1_N1 |     1 |       |     3   (0)| 00:00:01 |

first_rows_10 plan hinted under all_rows optimisation
| Id  | Operation                    | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | 
|   0 | SELECT STATEMENT             |       |   200 |  8600 |       |  6124   (3)| 00:00:01 | 
|*  1 |  HASH JOIN                   |       |   200 |  8600 |    17M|  6124   (3)| 00:00:01 |
|   2 |   VIEW                       | V1    |   402K|    12M|       |  5464   (3)| 00:00:01 | 
|   3 |    UNION-ALL                 |       |       |       |       |            |          | 
|*  4 |     TABLE ACCESS FULL        | T1    |   201K|  3933K|       |  2731   (3)| 00:00:01 | 
|*  5 |     TABLE ACCESS FULL        | T1    |   201K|  3933K|       |  2733   (3)| 00:00:01 | 
|   6 |   TABLE ACCESS BY INDEX ROWID| T2    |   100 |  1000 |       |   103   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN          | T2_I1 |   100 |       |       |     3   (0)| 00:00:01 |

I’m not sure why the first_rows_10 plan uses “table access by rowid batched”, but I’d guess it’s because the optimiser calculates that sorting the index rowids before visiting the table may have a small benefit on the speed of getting the first 10 rows – eventually I’ll get around to checking the 10053 trace file. The important thing, though, is the big mistake in the strategy, not the little difference in table access.

In the first_rows_10 plan the optimizer has decided building an in-memory hash table from the UNION ALL of the rows fetched from the two copies of the t1 table will be fast and efficient; but it’s made that decision based on the assumption that it will only get 10 rows from each copy of the table – and at run-time it HAS to get all the relevant t1 rows to build the hash table before it can get any t2 rows. We can get some idea of the scale of this error when we look at the hinted plan under all_rows optimisation – it’s a lot of redundant data and a very expensive hash table build.

In contrast the all_rows plan does an efficient indexed access into the t2 table then, for each row, does a join predicate pushdown into the union all view using an indexed access path. If we only wanted to fetch 10 rows we could stop after doing a minimum amount of work. To demonstrate the error more clearly I’ve re-run the experiment for the first two plans from SQL*PLus, setting the arraysize to 11, the pagesize to 5, and stopping after the first 10 rows. Here are the plans showing the rowsource execution stats:

first_rows_10 plan
| Id  | Operation                            | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
|   0 | SELECT STATEMENT                     |       |      1 |        |   107 (100)|     12 |00:00:00.43 |   35150 |       |       |          |
|*  1 |  HASH JOIN                           |       |      1 |      1 |   107   (0)|     12 |00:00:00.43 |   35150 |    24M|  3582K|   23M (0)|
|   2 |   VIEW                               | V1    |      1 |     24 |     4   (0)|    400K|00:00:06.48 |   35118 |       |       |          |
|   3 |    UNION-ALL                         |       |      1 |        |            |    400K|00:00:04.20 |   35118 |       |       |          |
|*  4 |     TABLE ACCESS FULL                | T1    |      1 |     12 |     2   (0)|    200K|00:00:00.71 |   17559 |       |       |          |
|*  5 |     TABLE ACCESS FULL                | T1    |      1 |     12 |     2   (0)|    200K|00:00:00.63 |   17559 |       |       |          |
|   6 |   TABLE ACCESS BY INDEX ROWID BATCHED| T2    |      1 |    100 |   103   (0)|     28 |00:00:00.01 |      32 |       |       |          |
|*  7 |    INDEX RANGE SCAN                  | T2_I1 |      1 |    100 |     3   (0)|     28 |00:00:00.01 |       4 |       |       |          |

all_rows plan
| Id  | Operation                              | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
|   0 | SELECT STATEMENT                       |       |      1 |        |   904 (100)|     12 |00:00:00.01 |     213 |
|   1 |  NESTED LOOPS                          |       |      1 |     43 |   904   (1)|     12 |00:00:00.01 |     213 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED  | T2    |      1 |    100 |   103   (0)|     28 |00:00:00.01 |      32 |
|*  3 |    INDEX RANGE SCAN                    | T2_I1 |      1 |    100 |     3   (0)|     28 |00:00:00.01 |       4 |
|   4 |   VIEW                                 | V1    |     28 |      1 |     8   (0)|     12 |00:00:00.01 |     181 |
|   5 |    UNION ALL PUSHED PREDICATE          |       |     28 |        |            |     12 |00:00:00.01 |     181 |
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1    |     28 |    212K|     4   (0)|      8 |00:00:00.01 |      93 |
|*  7 |      INDEX RANGE SCAN                  | T1_N1 |     28 |      1 |     3   (0)|      8 |00:00:00.01 |      85 |
|   8 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1    |     28 |    213K|     4   (0)|      4 |00:00:00.01 |      88 |
|*  9 |      INDEX RANGE SCAN                  | T1_N1 |     28 |      1 |     3   (0)|      4 |00:00:00.01 |      84 |

If I had set the optimizer_mode to first_rows_10 because I really only wanted to fetch (about) 10 rows then I’ve managed to pay a huge overhead in buffer visits, memory and CPU for the privilege – the all_rows plan was much more efficient.

Remember – we often see cases where the first_rows(n) plan will do more work to get the whole data set in order to be able to get the first few rows more quickly (the simplest example is when the optimizer uses a particular index to get the first few rows of a result set in order without sorting rather than doing a (faster) full tablescan with sort. This case, though, is different: the optimizer is choosing to build a hash table as if it only has to put 10 rows into that hash table when it actually HAS to build the whole has table before it can take any further steps – we don’t get 10 rows quicker and the rest more slowly; we just get 10 very slow rows.


It’s possible that this is an example of bug 9633142: (FIRST_ROWS OPTIMIZER DOES NOT PUSH PREDICATES INTO UNION INLINE VIEW) but that’s reported as fixed in 12c, with a couple of patches for However, setting “_fix_control”=’4887636:off’, does bypass the problem. (The fix control, introduced in has description: “remove restriction from first K row optimization”)

Data Warehouse Appliance Offerings

Chris Foot - Fri, 2014-10-31 11:15


Information Technology units will continue to be challenged by the unbridled growth of their organization’s data stores. An ever-increasing amount of data needs to be extracted, cleansed, analyzed and presented to the end user community. Data volumes that were unheard of a year ago are now commonplace. Day-to-day operational systems are now storing such large amounts of data that they rival data warehouses in disk storage and administrative complexity. New trends, products, and strategies, guaranteed by vendors and industry pundits to solve large data store challenges, are unveiled on a seemingly endless basis.

Choosing the Large Data Store Ecosystem

Choosing the correct large data store ecosystem (server, storage architecture, OS, database) is critical to the success of any application that is required to store and process large volumes of data. This decision was simple when the number of alternatives available was limited. With the seemingly endless array of architectures available, that choice is no longer as clear cut. Database administrators now have more choices available to them than ever before. In order to correctly design and implement the most appropriate architecture for their organization, DBAs must evaluate and compare large data store ecosystems and not the individual products.

Traditional Large Data Store Technologies

Before we begin our discussion on the various advanced vendor offerings, we need to review the database features that are the foundation of the customized architectures we will be discussing later in this article. It is important to note that although each vendor offering certainly leverages the latest technologies available, the traditional data storage and processing features that DBAs have been utilizing for years remain critical components of the newer architectures.


Partitioning data into smaller disk storage subsets allows the data to be viewed as a single entity while overcoming many of the challenges associated with the management of large data objects stored on disk.

Major database vendor products offer optimizers that are partition aware and will create query plans that access only those partitioned objects needed to satisfy the query’s data request (partition pruning). This feature allows administrators to create large data stores and still provide fast access to the data.

Partitioning allows applications to take advantage of “rolling window” data operations. Rolling windows allow administrators to roll off what is no longer needed. For example, a DBA may roll off the data in the data store containing last July’s data as they add this year’s data for July. If the data is ever needed again, administrators are able to pull the data from auxiliary or offline storage devices and plug the data back into the database.

Query Parallelism

Query parallelism improves data access performance by splitting work among multiple CPUs. Most database optimizers are also parallel aware and are able to break up a single query into sub queries that access the data simultaneously.

Without parallelism, a SQL statement’s work is performed by a single process. Parallel processing allows multiple processes to work together to simultaneously process a single SQL statement or utility execution. By dividing the work necessary to process a statement among multiple CPUs, the database can execute the statement more quickly than if the work was single-threaded.

The parallel query option can dramatically improve performance for data-intensive operations associated with decision support applications or very large database environments. Symmetric multiprocessing (SMP), clustered, or massively parallel systems gain the largest performance benefits from the parallel query option because query processing can be effectively split up among many CPUs on a single system.

Advanced Hardware and Software Technologies

Let’s continue our discussion by taking a high-level look at the advanced data warehouse offerings from the three major database competitors, Oracle, Microsoft and IBM. Each of the vendors’ offerings are proprietary data warehouse ecosystems, often called appliances, that consist of hardware, OS and database components. We’ll complete our review by learning more about Hadoop’s features and benefits.

Oracle Exadata

Oracle’s Exadata Machine combines their Oracle database with intelligent data storage servers to deliver very high performance benchmarks for large data store applications. Exadata is a purpose-built warehouse ecosystem consisting of hardware, operating system and database components.

Oracle Exadata Storage Servers leverage high speed interconnects, data compression and intelligent filtering and caching features to increase data transfer performance between the database server and intelligent storage servers. In addition, the Exadata architecture is able to offload data intensive SQL statements to the storage servers to filter the results before the relevant data is returned to the database server for final processing.

Exadata uses PCI flash technology rather than flash disks. Oracle places the flash memory directly on the high speed PCI bus rather than behind slow disk controllers and directors. Each Exadata Storage Server includes 4 PCI flash cards that combine for a total of 3.2 TB of flash memory. Although the PCI flash can be utilized as traditional flash disk storage, it provides better performance when it is configured as a flash cache that sits in front of the disks. Exadata’s Smart Flash Cache will automatically cache frequently accessed data in the PCI cache, much like its traditional database buffer cache counterpart. Less popular data will continue to remain on disk. Data being sent to the PCI Flash cache is also compressed to increase storage capacity.

Exadata also offers an advanced compression feature called Hybrid Columnar Compression (HCC) to reduce storage requirements for large databases. Exadata offloads the compression/decompression workload to the processors contained in the Exadata storage servers.

These technologies enable Exadata to deliver high performance for large data stores accessed by both decision support and online operational systems. The Exadata machine runs an Oracle database which allows Oracle-based applications to be easily migrated. Oracle describes the Exadata architecture as “scale out” capable meaning multiple Exadata servers can be lashed together to increase computing and data access horsepower . Oracle RAC, as well as Oracle’s Automatic Storage Management (ASM), can be leveraged to dynamically add more processing power and disk storage.

Microsoft SQL Server PDW

SQL Server Parallel Data Warehouse (PDW) is a massively parallel processing (MPP) data warehousing appliance designed to support very large data stores. Like Oracle’s Exadata implementation, the PDW appliance’s components consist of the entire database ecosystem including hardware, operating system and database.

Database MPP architectures use a “shared-nothing” architecture, where there are multiple physical servers (nodes), with each node running an instance of the database and having its own dedicated CPU, memory and storage.

Microsoft PDW’s architecture consists of:

  • The MPP Engine
    • Responsible for generating parallel query execution plans and coordinating the workloads across the system’s compute nodes
    • Uses a SQL Server database to store metadata and configuration data for all of the databases in the architecture
    • In essence, it acts as the traffic cop and the “brain” of the PDW system
  • Computer Nodes
    • Each compute node also runs an instance of the SQL Server database
    • The compute nodes’ databases are responsible for managing the user data

As T-SQL is executed in the PDW system, the queries are broken up to run simultaneously over multiple physical nodes, which utilizes parallel execution to provide high performance data access. The key to the success when using PDW is to select the appropriate distribution columns that are used to intelligently distribute the data amongst the nodes. The ideal distribution column is one that is accessed frequently, is able to evenly distribute data based on the column’s values and has low volatility (doesn’t change a lot).

Microsoft Analytics Platform (APS)- Hadoop and SQL Server Integration

Microsoft’s Analytics Platform System (APS) combines massively parallel processing offering (PDW) with HDInsight, their version of Apache Hadoop. Microsoft has partnered with Hortonworks, a commercial Hadoop software vendor that provides a Windows-based, 100% Apache Hadoop distribution. Please see section below for more detailed information on the Hadoop engine.

Integrating a Hadoop engine into SQL Server allows Microsoft to capture, store, process and present both structured (relational) and unstructured (non-relational) data within the same logical framework. Organizations wanting to process unstructured data often turned to Apache Hadoop environments which required them to learn new data storage technologies, languages and an entirely new processing architecture.

Microsoft’s Polybase provides APS users with the ability to query both structured and non-structured data with a single T-SQL based query. APS application programmers are not required to learn MapReduce or HiveQL to access data stored in the APS platform. Organizations using APS do not incur the additional costs associated with to re-training their existing staff or hiring personnel with experience in Hadoop access methods.

IBM PureData Systems for Analytics

Not to be outdone by their database rivals, IBM also provides a proprietary appliance called IBM PureData System for Analytics. The system, powered by Netezza, once again, combines the hardware, database and storage into a single platform offering. Pure Data Analytics is an MPP system utilizing IBM Blade Servers and dedicated disk storage servers that, like its competitors, is able to intelligently distribute workloads amongst the processing nodes.

IBM leverages field-programmable gate arrays (FPGAs) which are used in their FAST engines. IBM runs the FAST engine on each node to provide compression, data filtering and ACID compliance on the Netezza systems. The real benefit of FAST is that the FPGA technology allows the engines to be custom tailored to the instructions that are being sent to them for processing. The compiler divides the query plan into executable code segments, called snippets, which are sent in parallel to the Snippet Processors for execution. The FAST engine is able to customize the filtering according to the snippet being processed.

IBM’s Cognos, Data Stage, and InfoSphere Big Insights software products are included in the offering. IBM’s goal is to provide a total warehouse solution, from ETL to final data presentation, to Pure Data Analytics users.

In addition, IBM also provides industry-specific warehouse offerings for banking, healthcare, insurance, retail and telecommunications verticals. IBM’s “industry models” are designed to reduce the time and effort needed to design data warehousing systems for the organizations in these selected business sectors. IBM provides the data warehouse design and analysis templates to accelerate the data warehouse build process. IBM consulting assists the customer to tailor the architecture to their organization’s unique business needs.

Non-Database Vendor Technologies

New “disruptive” products that compete with the traditional database vendor offerings continue to capture the market’s attention. The products range the spectrum from No-SQL products that provide easy access to unstructured data to entirely new architectures like Apache’s Hadoop.

Major database vendors will make every effort to ensure that disruptive technologies gaining market traction become an enhancement, not a replacement, for their traditional database offerings. Microsoft’s APS platform is an excellent example of this approach.

Apache Hadoop

Apache’s Hadoop is a software framework that supports data-intensive distributed applications under a free license. The Hadoop software clusters’ commodity servers offer scalable and affordable large-data storage and distributed processing features in a single architecture.

A Hadoop cluster consists of a single master and multiple worker nodes. The master provides job control and scheduling services to the worker nodes. Worker nodes provide storage and computing services. The architecture is distributed, in that the nodes do not share memory or disk.

A distributed architecture allows computing horsepower and storage capacity to be added without disrupting on-going operations. Hadoop’s controlling programs keep track of the data located on the distributed servers. In addition, Hadoop provides multiple copies of the data to ensure data accessibility and fault tolerance.

Hadoop connects seamlessly to every major RDBMS through open-standard connectors providing developers and analysts with transparent access through tools they are familiar with. When used simply as a large-data storage location, it is accessible through a variety of standards-based methods such as FUSE or HTTP. Hadoop also offers an integrated stack of analytical tools, file system management and administration software to allow for native exploration and mining of data.

The Hadoop architecture is able to efficiently distribute processing workloads amongst dozens and hundreds of cost-effective worker nodes. This capability dramatically improves the performance of applications accessing large data stores. Hadoop support professionals view hundreds of gigabytes as small data stores and regularly build Hadoop architectures that access terabytes and petabytes of structured and unstructured data.

One of Hadoop’s biggest advantages is speed. Hadoop is able to generate reports in a fraction of the time required by traditional database processing engines. The reductions can be measured by orders of magnitude. Because of this access speed, Hadoop is quickly gaining acceptance in the IT community as a leading alternative to traditional database systems when large data store technologies are being evaluated.


As stated previously, there is an endless array of offerings that focus on addressing large data store challenges. Large data store architecture selection is the most important decision that is made during the warehouse development project. A correctly chosen architecture will allow the application to perform to expectations, have the desired functionality and be easily monitored and administered. Incorrect architecture decisions may cause one or more of the following problems to occur: poor performance, limited functionality, high total cost of ownership, complex administration and tuning, lack of scalability, poor vendor support, poor reliability/availability and so on. All market- leading database vendors understand the importance of addressing the challenges inherent with large data stores and have released new products and product enhancements designed to simplify administration and improve performance.

The post Data Warehouse Appliance Offerings appeared first on Remote DBA Experts.

USA War Casualties

Nilesh Jethwa - Fri, 2014-10-31 10:35 maintains documented list of all fatalities for Iraq and Afghanistan wars.

Analysing the dataset for Afghanistan, we summarize the results by the year

NOTE: This contains only Afghanistan metrics. We will later update the visuals to reflect Iraq war.



USA war fatalities by year

We are approaching the levels of 2002 and hope for the best that we don’t have to suffer another wars.

Here is another view by year and month


InfoCaptor : Analytics & dashboards


The dataset contains the age of each person died in the war so summarizing by Age


War Deaths by Age

Checking it against the year


Why so many young deaths between age 20 and 30 for the year 2014?


Where did most of the deaths occur?



Where were the soldiers from?


Deaths by Rank


InfoCaptor : Analytics & dashboards


Cause of Death

Attack Types




Helicopter Crash is the one of the top death cause in Non Hostile situations

Speaking at the Spanish Virtual PASS Chapter

Pythian Group - Fri, 2014-10-31 10:09

Title : Recuperación de desastres y soluciones de alta disponibilidad con SQL Server
Event link:
Event description: “Esta presentación presenta las soluciones de recuperacion de desastres (Disaster Recovery) y alta disponibilidad (High Availability) con SQL Server y ofrece escenarios creativos por usar las soluciones para reportages (Reporting), BI y almacen de datos (Datawarehouse). ”

Please feel free to register!

Categories: DBA Blogs

Log Buffer #395, A Carnival of the Vanities for DBAs

Pythian Group - Fri, 2014-10-31 07:44

This Log Buffer Edition ventures out in the fields of Oracle, SQL Server and MySQL and picks some of the coolest blog posts for this week.


In Oracle, if we add a column to a table which is NOT NULL, we are allowed to do it directly, in a single statement, as long as we supply a DEFAULT value to populate any pre-existing rows.

There have been rumblings from the HPC community indicating a general suspicion of and disdain for Big Data technology which would lead one to believe that whatever Google, Facebook and Twitter do with their supercomputers is not important enough to warrant seriousness—that social supercomputing is simply not worthy.

Work-around Instance Migration Limits of BPM Suite 11g.

Oracle Event Processing 12c: java errors when deploying a new OEP project.

Creating a WebLogic 12c Data Source Connection to Pivotal GemFireXD 1.3.

SQL Server:

It sounds simple enough. Either your column will always have a value or it may not. Yet somehow such a seemingly simple decision can become a never-ending debate where database schema begins to resemble superstition and designing effective tables seems more contentious than you expected it to be.

There’s a joke doing the rounds at SQL conferences and seminars: three DBAs walk into a NoSQL bar and leave when they can’t find a table.

Stairway to AlwaysOn Level 3: Infrastructure 101.

How to compare two databases and email the results to every one who needs to know.

Free eBook: Troubleshooting SQL Server: A Guide for the Accidental DBA.


Set up an SSL-encrypted connection between Sphinx and MySQL.

Sometimes users ask for something that doesn’t really make sense. On the first glance. But then you start asking and realize that the user was right, you were wrong, and it is, actually, a perfectly logical and valid use case.

PECL/mysqlnd_ms needs updates for MySQL Group Replication.

Why should you migrate from MySQL to MariaDB?

The Perfect Server – CentOS 7 (Apache2, Dovecot, ISPConfig 3).

Categories: DBA Blogs

Open Source Virtualization Project at Risk [VIDEO]

Chris Foot - Fri, 2014-10-31 04:02


Hi, welcome to RDX. Virtualization and cloud technology pretty much go hand-in-hand.

Many popular cloud providers, such as Amazon and Rackspace, use Xen, an open-source virtualization platform to optimize their environments.

According to Ars Technica, those behind the Xen Project recently released a warning to those using its platform. Apparently, a flaw within the program's hypervisor allows cybercriminals to corrupt a Xen virtual machine, or VM. From there, perpetrators could read information stored on the VM, or cause the server hosting it to crash. Monitoring databases hosted on Xen VMs is just one necessary step companies should take. Reevaluating access permissions and reinforcing encryption should also be priorities. 

Thanks for watching! Be sure to visit us next time for any other advice on security vulnerabilities. 

The post Open Source Virtualization Project at Risk [VIDEO] appeared first on Remote DBA Experts.

OSB12c: Errorhandling in REST

Darwin IT - Fri, 2014-10-31 03:39
Yesterday, I had an OSB consulting day at a customer. We looked into a REST service that was to be extended with update functionality. Since calling an update service of an EIS (Enterprise Information System) can go wrong with all sorts of errors, it is important to be able to return a fault-message with the errors, jason format.

Now in OSB12c it's very apparent how you define possible fault-messages and even how the should be formatted in JSON:

In this sample case we created a more or less simple xsd for faults (dutch: fouten). To test with different fault messages we simply duplicated the 'fouten' element in the xsd to 'fouten2'. You can assign different HTTP-status codes to the different fault.

So this is configuration is pretty simple and straight forward. But it is not quite clear in the documents how you would return a specific fault within your error-handlers in the pipeline.

Internally OSB works not only 'XML'-based but actually SOAP-based. So the trick in the end is to replace the body with a soap-fault message and the selection of the REST/JSON errormessage is done based on the structure of the document in the details-section of the SOAP-Fault. In the screen above, you would define for each fault message an xsd-element and apparently it validates the soap-fault-details content against each XSD defined, and the xsd against which the detail-content is valid points to the returned fault, with the corresponding HTTP Status.

So we created a XQuery transformation as follows:
xquery version "1.0" encoding "utf-8";

(:: OracleAnnotationVersion "1.0" ::)

declare namespace ns2="";
(:: import schema at "../PS/Schemas/fouten.xsd" ::)
declare namespace ns1="";
(:: import schema at "../BS/Schemas/XMLSchema_-130439696.xsd" ::)
declare namespace soap-env="";

declare variable $input as element() (:: schema-element(ns1:ServiceErrorMessage) ::) external;

declare function local:func($input as element() (:: schema-element(ns1:ServiceErrorMessage) ::)) as element() (:: schema-element(ns2:fouten) ::) {
for $detail in $input/ns1:detail
<ns2:ErrorMessage>{fn:concat("ERROR: ", fn:data($detail/ns1:message))}</ns2:ErrorMessage></ns2:ErrorMessages>

Of course the actual fault detail must follow the xsd for that particular fault. We tested but the faultcode or fault string does not have any affect in selection of the REST-fault or HTTP statuscode.
With the xquery above we got the 'fault' returned as defined in the REST definition, as shown in the screendump above.
 In our example, if we would changed the contents of this xquery and replace the tag <ns2:fouten> to <ns2:fouten2> then we got the other fault (fault2), with the corresponding HTTP-status.
A detail with contents that does not correspond to any of the defined fault-xsd's would result in HTTP-status 500: internal server error. So it is important to have a proper transformation where the returning fault-detail is valid to at least one of the fault-xsd's.

Another conclusion is that since the fault selection is apparently based on the detail-contents against the registered fault-xsd-elements, you apperently can't have different faults with the same xsd. Since JSON is 'namespace-less', you probably can solve this by defining several copies of the same xsd with a different namespace, one for each fault. The choosen namespace in the xquery would then be the selector for the fault. But since the underlying element names are the same, it would not differ in the resulting JSON-message. Of course in an XML result it would differ.

MySQL : What management tools do you use?

Tim Hall - Fri, 2014-10-31 02:35

A quick question out to the world. What management tools do you use for MySQL?

We currently have:

  • MySQL Workbench : It’s OK, but I don’t really like it. It feels like half a product compared to tools I’ve used for other database engines…
  • phpMyAdmin : I’ve used this on and off for over a decade for my own website. While I’m typing this sentence, they’ve probably released 4 new versions. :) We have an installation of this which we use to access our MySQL databases should the need arise.
  • mysql Command Line : I use the command line and a variety of scripts for the vast majority of the things I do.

When I’m working with Oracle, my first port of call for any situation is to use SQL*Plus along with a variety of scripts I’ve created over the years. The performance stuff in Cloud Control (if you’ve paid for the Diagnostics and Tuning option) is the big exception to that of course.

I still consider myself a newbie MySQL administrator, but I’ve found myself spending more and more time at the command line, to the point where I rarely launch MySQL Workbench or phpMyAdmin these days. I’m wondering if that is common to other MySQL administrators, or if it is a carry over from my Oracle background…

Enquiring minds need to know!



MySQL : What management tools do you use? was first posted on October 31, 2014 at 9:35 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Index Advanced Compression vs. Bitmap Indexes (Candidate)

Richard Foote - Thu, 2014-10-30 23:59
A good question from Robert Thorneycroft I thought warranted its own post. He asked: “I have a question regarding bitmapped indexes verses index compression. In your previous blog titled ‘So What Is A Good Cardinality Estimate For A Bitmap Index Column ? (Song 2)’ you came to the conclusion that ‘500,000 distinct values in a 1 […]
Categories: DBA Blogs

Better Communication

Michael Dinh - Thu, 2014-10-30 19:09

Sometimes I get excited over simple things. Today, I learned something new, something about human behaviors.

Basically, there are 4 quadrants for human behaviors.

If one does not understand the other person’s behavior, then communication can be very difficult.

To find more about human behaviors, read The DISC Model of Human Behavior – A Quick Overview.

Which quadrant are you in, versus your manager?

Flipped Classrooms: Annotated list of resources

Michael Feldstein - Thu, 2014-10-30 17:03

I was recently asked by a colleague if I knew of a useful article or two on flipped classrooms – what they are, what they aren’t, and when did they start. I was not looking for any simple advocacy or rejection posts, but explainers that can allow other people to understand the subject and make up their own mind on the value of flipping.

While I had a few in mind, I put out a bleg on Google+ and got some great responses from Laura Gibbs, George Station, and Bryan Alexander. Once mentioned, Robert Talbert and Michelle Pacansky-Brock jumped into the conversation with additional material. It seemed like a useful exercise to compile the results and share a list here at e-Literate. This list is not meant to be comprehensive, but a top level of the articles that I have found useful.

There are other useful article out there, but this list is a good starting place for balanced, non-hyped descriptions of the flipped classroom concept.[1] Let me know in the comments if there are others to include in this list.

  1. I did not include any directly commercial sites or articles in the list above. Michelle’s book was included as the introduction is freely available.

The post Flipped Classrooms: Annotated list of resources appeared first on e-Literate.

Oracle Priority Support Infogram for 30-OCT-2014

Oracle Infogram - Thu, 2014-10-30 15:38

From Upgrade your Database - NOW!: ORA-20000 Unable to gather statistics concurrently: Resource Manager is not enabled ORA-06512: at "SYS.DBMS_STATS"
November's events with MySQL, from Oracle’s MySQL Blog.
MySQL for Excel 1.3.3 has been released, from the MySQL on Windows blog.
2 Minute Tech Tip: Passing Essbase Security to OBIEE, from Arch Beat.
Updated Statement of Direction for Oracle Business Intelligence Analytics (OBIA), from the Data Integration blog.
ODI 12c and DBaaS in the Oracle Public Cloud, from Data Integration.
From SOA & BPM Partner Community Blog: What’s New in Oracle SOA Suite 12c?
By the Poolside: An Introduction to ADF BC Application Module Pooling , from WebLogic Partner Community EMEA
Heating Up Your OpenStack Cloud, from the Fore!blog.
From Insights from an Oracle University Classroom: Resolving Enterprise Routing & Dialing Plan complexity with Enterprise Communications Broker (ECB)
From the Oracle Demantra blog: Oracle Demantra 12.2.4 RDBMS 12C Important Updates to Consider
APEX and MAF: seeing is believing, from the Blueberry Coder.
Mobile Persistence Extension for Oracle MAF, from WebLogic Partner Community EMEA
From Oracle E-Business Suite Technology:
New OA Framework 12.1 Release Update Pack 4 Now Available
October Updates to AD and TXK for EBS 12.2
Updated Case Study: Oracle's Own E-Business Suite 12 Environment
Migrating EBS Environments to Unicode
New Simplified Home Page in EBS 12.2.4
From Oracle E-Business Suite Technology:
PDOI New Features and Enhancements!
Webcast: Inventory Period Close - Stuck Transactions
CRM Service: New Consolidated RUP Patch Released
Webcast: Demo on 12.2.4 Demantra - APCC New Features
Using the Concurrent Request "Transfer Journals to GL - Assets"
How to Customize Your Field Service Wireless Application
Oracle Cost Management - Upgrade 11i to Release 12 Assistant
ORAchk adds Order Management and Process Manufacturing
To PIM or not to PIM - What is that Abbreviation?

patches:Oracle Forms Object POXOPROC.pll did not generate successfully: wrong number or types of arguments in call to 'UPDATE_WO_ADD_DES_DIR_ITEM'

Webcast Q&A: Next-Generation Accounts Payable Process Automation

WebCenter Team - Thu, 2014-10-30 14:41
On October 16, Nilay Banker, Founder & CEO, Inspyrus and Chris Preston, Sr. Director Customer Strategies, Oracle presented on a webcast "Next-Generation Accounts Payable Process Automation." We had a ton of questions come in during the webcast, and Nilay has graciously answered and addressed them for us in this blog post Q&A.
A lot of your competitors claim they can provide 80% automation. How is your offering different? When our competitor’s talk about 80% automation, they are talking about what you could potentially get with OCR. They provide really poor integration with your ERP system and that is where the real problem is. That is the traditional approach where after OCR, about 82% of invoices end up with exceptions in your ERP system and so your AP personnel have to manually resolve those invoices one-by-one. o Our next generation approach provides you end-to-end automation. Not only do we provide best-in-class OCR, but we have cracked the code on how we integrate real-time with your ERP systems and provide exception-free creation of invoices and 2-way and 3-way matching.
Can your cloud offering integrate with our on-premise ERP systems?  We have Oracle E-Business Suite and JD Edwards. Yes, our cloud offering can integrate with your on-premise of cloud ERP systems. A lot of our customers have different ERP systems. We can integrate with multiple ERP systems seamlessly and provide real-time integration, and unified Application, Workflow and Analytics across all your multiple ERP systems.
We receive invoices via mail, email and EDI. Can your offering provide us a consistent process for all these? Yes. Irrespective of how you receive your invoices, we provide a consistent Application, Workflow and Analytics for all of these.
We have Oracle E-Business Suite and SAP for our ERP systems. Will your solution integrate with both our ERP systems? Yes, our solutions comes pre-integrated with Oracle E-Business Suite and SAP and if you have both ERP systems, a single instance of our application will be integrated with both.
What is your typical implementation timeline? Our standard implementation takes between 8 to 10 weeks.
Is the solution set specific to Oracle's eBusiness suite or can this solution bolt on to something like MS Dynamics to replace the AP transactions processing? The solution is available for most major ERP systems including MS Dynamics. Also available for SAP, PeopleSoft & JD Edwards.
Can you talk more about how Suppliers can be included? Suppliers can be included in a couple of ways:
  1. As part of the invoice processing workflow if you want to reject invoices with missing information and send it back to the suppliers for them to resubmit.
  2. Provide suppliers self-service access to view status of invoices, payments and to create invoices online including PO flips.
How about list of values within the tool - master data and transactional data ? Will you draw from the ERP? The application is integrated real-time with Oracle EBS, SAP, PeopleSoft and JD Edwards. All master and transactional data are drawn from the ERP system.
How does this solution compare to BPM? BPM is a technology. What we are presenting here is a specialized pre-built Solution that is based on (leverages) Oracles BPM technology, along with Imaging, Content Management, OCR/OFR and SOA integration.
Would this solution work if we have a mix of invoices where some are match to po and some are match to receipt? Yes, that is very common.
And is this an Inspyrus solution or out of the box with WebCenter Imaging? This solution from Inspyrus comes out-of-the-box with Oracle WebCenter Imaging, Forms Recognition and Capture.
Does the application also integrate with some of the cloud based application like SAP's Business By Design? Yes, it does.
I wasn't clear about how the system can create the GL code without PO matching? Business rules can be applied to automate GL-coding for non-PO invoices.
Does this support Project related invoice coding for those that use E Business Suite - Project Accounting? Yes, project coding is supported out-of-the-box.
Does this application have an integration to Oracle Fusion? Integration with Oracle Fusion will be available in Feb 2015.
What kind of validations happens when it hits the unified workflow? Whatever is required for the successful creation of the invoice in the ERP system. Basically, validation against every setup, rule, config of the ERP system.
What is the backbone (Content Storage, Workflow, etc.) of the application? Underlying technology is Oracle WebCenter Content & Oracle BPM.
Need to know if the application has its own database to store scanned copies of invoices or we need to have content management tool? This solution from Inspyrus and Oracle comes out-of-the-box with Oracle WebCenter Imaging, Forms Recognition and Capture - includes WebCenter Content which is the content management repository.
Does this solution utilize Oracle IPM? This is built on top of Oracle IPM. You can reach out to Nancy Tiano. We have been talking to Land O'Lakes recently.
For invoices emailed - e.g. a .pdf file, can they be automatically sent for OCR? Yes, every attachment in the email is automatically sent for OCR.
Does OCR support all language invoices? There is support for about 24 languages. Most major ones.
Is SOA Suite a requirement for delivery to the downstream ERP systems? Including Oracle EBS, MS Dynamics, SAP, etc? WebCenter Imaging includes run-time licenses of BPM Suite and SOA Suite. SOA Suite is used as the integration tool for integration with all the mentioned ERP systems.
Can this be integrated into other ERP systems besides the ones mentioned? We develop integrations to other ERP systems all the time. Yes, we can build new integrations as needed.
Is this replacing workspace and IPM application? This leverages Oracle WebCenter Capture, Forms Recognition and Imaging.
Do you have the capability of electronic signatures on invoices? Yes.
How do you handle disputes or discrepencies for invoices with exceptions? These are handled as part of the invoice processing workflow.
How does the application "standardize" emailed invoice files for ingestion into capture? WebCenter Capture can monitor email inboxes. It extracts invoices that are attached to these email inboxes. These attachments could come into a variety of different formats eg. PDF, Word, Excel, PNG, JPG etc. Capture converts that into a standard TIFF format.
Would we need to configure our matching rules within the tool? No, we use matching rules that are setup in your ERP system.
The solution does consolidate the number of applications and move the matching upstream, but how will this assist in reducing the 82% exceptions? Are the exceptions just being moved upstreamed? We enrich the data prior to validation and scrub it.
How much does this cost in terms of software license if we want to implement? This is a packaged offering and some customers have unique requirements. SO best if you can reach out to your Oracle sales rep or Inspyrus (
Can you throw some more light on Unified workflow, in other words are we using oracle workflow tool , ability to customize workflow? Workflows are built using Oracle BPM Suite. Plenty of capabilities to customize that.
Does this solution use SOA Suite 12c? Still under certification.
What's involved to get to automated GL coding? If there are specific business rules that you can tell us to automate GL coding - say for a particular vendor or for certain descriptions on invoice lines, we can automate GL coding.
Can you override GL coding on Invoices matched to a PO? Or is this functionality only for non-PO matched invoices? Yes, we can do that.
Can suppliers submit their invoices directly in this case or it provides only status access to their invoices? Yes, suppliers can submit their invoices online.
Is the integration to the ERP systems unidirectional or bidirectional? Our integration is real-time with the ERP system. We don't need to store any ERP information in our system. We do bring in information about payments back into our system - thus making it bidirectional.
There are different formats used by suppliers, how does OCR recognize content of invoices and processs them? WebCenter Forms Recognition is a Pattern-Recognition based OCR engine. It does not rely on the use of templates or anchors or tags as other OCR engines do. It understands and recognizes patterns of information and so has a much higher rate of extraction than other template-based OCR engines.
Is complex approval rules able to be used with this application? Yes, we can handle all kinds of complex approval rules.
100% of our invoices are coded to a Project/Task/Expenditure Type in E Business Suite. Does this support full coding validation against Project related invoices?
Yes, it does.
Can vendors integrate into this solution as well, as in submitting invoices EDI to the cloud offereing (instead of emailing to customer who then turns around and uploaded into AP solution)? Absolutely. Then can send EDI invoices to our Cloud.
Will the 3 way match verify the Project number, from the Oracle Projects module? Yes, it can.
Can we self-host? Yes, this can be hosted in your data center.
Is Oracle SOA required for downstream integration to other ERP, including SAP, etc? Oracle SOA comes embedded in this solution. That is the integration framework we use to integrate with all the ERP systems.
Do we have to buy a particular machine for imaging? If you can reach out to your Oracle sales rep or Inspyrus ( - they can provide you details.
Do you have the capability for invoices t obe signed off electronically by an authorized signer? Yes, all approvals are electronic.
Is one of the 24 languages covered by OCR Chinese? Simplified Chinese - yes
Is there a charge for suppliers submitting directly? No additional charge to suppliers for submitting directly.
Does it provide invoice attachments?. Yes, it does.
Who provides the OCR? Supplier? We do the OCR as part of the solution.
Does Inspryus handle non PO invoices that require multiple approvers? Yes.
How is this exception free? There would not be any exceptions when the invoices are pushed to the ERP system.
Do we need to have separate server for attachments? No, the solution comes with a content management system where attachments/images are stored - WebCenter Content.
Do suppliers submit invoices directly to Inspyrus or EBS? They can do that via email or send EDI invoices.
How much does the whole implementation cost? This is a packaged offering and some customers have unique requirements. SO best if you can reach out to your Oracle sales rep or Inspyrus (
Will it integrate with an ancient PO/RO system that is not Oracle? Yes, we have the ability to integrate with 3rd party PO systems.
Can you briefly explain how this is based on Oracle WebCenter? We have WebCenter right now and we want to know how we can utilize it. Yes, it is built on Oracle WebCenter. You can reach out to Inspyrus for more information.
After the OCR data extraction, if there are any errors/mistakes, how are they corrected before pushing into the ERP? Inspyrus provides a unified application where these are corrected - as part of the workflow.
You replied that all your approvals are electronic - can they be visible like a digital signature in pdf? We do not touch the pdf - for compliance reasons. The electronic approvals are available as a separate document tied to the invoice record.
What is the cost of the system? This is a packaged offering and some customers have unique requirements. So best if you can reach out to your Oracle sales rep or Inspyrus (
Automatic GL coding. What criteria\invoices should satisfy for Automatic GL coding proper work?

If there are specific business rules that you can tell us to automate GL coding - say for a particular vendor or for certain descriptions on invoice lines, we can automate GL coding.

In case you missed the webcast, you can view the on demand version here

Work-around Instance Migration Limits of BPM Suite 11g

Jan Kettenis - Thu, 2014-10-30 12:46
The following describes a work-around for 2 situations for which instance patching and migration is not supported, being changing the level of an activity, and removal of an embedded sub-process. In short this work-around consists of re-implementation of the activities to move, and emptying the reusable sub-process.

There are a couple of restrictions for the Oracle BPM Suite that can make that process instances cannot be patched (deployment using same revision number) or migrated (deployment using new revision number, and then move from old to new revision). Two of them are that you cannot change the scope of an activity (like moving it in our out of an embedded sub-process) or removal of an embedded sub-process. For both situations there is a work-around, that I can demonstrate with one case.

More information about instance patching and migration can be found here for BPM 11g ( and here for BPM 12c (12.1.3).
The Work-Around
Suppose you have a process (A) like this:

And you want to change it to this (in real life you may want to move the activity inside to a totally different location, or even remove the embedded sub-process altogether) to this model (D):
As migration of running instances is not supported:
  1. Because of changing the scope of the activity, and
  2. Because of removal of the embedded sub-process
you will get an error like this when trying to do so:

 However, what you can do, is change the model like this (C):

The trick is that the Say Goodbye activity has not been moved outside, but re-implemented. In this case I created a new activity with the same name, and reused the existing task definition. So I only had to redo the data mappings.

You won't win any prize for most beautiful process model with this but it works. In practice you want to collapse the embedded sub-process and rename it to something like "Empty".
The Proof of the PuddingTo make sure it actually works for running processes I used an extra step between (A) and (C), being this model (B):
 First I started with the first model, created 2 instances with one in Say Hello, and the other in Say Goodbye. I then deployed the last model with "Keep running instances" checked (instance patching). The result was that both instances were automatically migrated (i.e. just kept on running).

Then I created a start situation of 3 instances, each of them being in a different activity. The most interesting is the one in Say Goodbye in the re-usable sub-process, as in this case the token is in an activity that is going to be removed.
 In line with the documentation, when deploying model (C) all instances were put I status Pending Migration. Using Alter Flow I was able to migrate the instances in the first and last activity as-is. After that I could successfully complete them.

The interesting one though, is in the second activity:

I was able to migrate that with Alter Flow by moving the token from the embedded sub-process (not the activity inside the embedded sub-process!) to the last activity:

The engine never knew what hit it ;-)

Quiz night

Jonathan Lewis - Thu, 2014-10-30 12:43

Here’s a little puzzle that came up on OTN recently.  (No prizes for following the URL to find the answer) (Actually, no prizes anyway). There’s more in the original code sample than was really needed, so although I’ve done a basic cut and paste from the original I’ve also eliminated a few lines of the text:

execute dbms_random.seed(0)

create table t
select rownum as id,
       100+round(ln(rownum/3.25+2)) aS val2,
       dbms_random.string('p',250) aS pad
from dual
connect by level <= 1000
order by dbms_random.value;

  dbms_stats.gather_table_stats(ownname          => user,
                                tabname          => 'T',
                                method_opt       => 'for all columns size 254'

column endpoint_value format 9999
column endpoint_number format 999999
column frequency format 999999

select endpoint_value, endpoint_number,
       endpoint_number - lag(endpoint_number,1,0)
                  OVER (ORDER BY endpoint_number) AS frequency
from user_tab_histograms
where table_name = 'T'
and column_name = 'VAL2'
order by endpoint_number

alter session set optimizer_mode = first_rows_100;

explain plan set statement_id '101' for select * from t where val2 = 101;
explain plan set statement_id '102' for select * from t where val2 = 102;
explain plan set statement_id '103' for select * from t where val2 = 103;
explain plan set statement_id '104' for select * from t where val2 = 104;
explain plan set statement_id '105' for select * from t where val2 = 105;
explain plan set statement_id '106' for select * from t where val2 = 106;

select statement_id, cardinality from plan_table where id = 0;

The purpose of the method_opt in the gather_table_stats() call is to ensure we get a frequency histogram on val2; and the query against the user_tab_columns view should give the following result:

-------------- --------------- ---------
           101               8         8
           102              33        25
           103             101        68
           104             286       185
           105             788       502
           106            1000       212

Given the perfect frequency histogram, the question then arises why the optimizer seems to calculate incorrect cardinalities for some of the queries; the output from the last query is as follows:

------------------------------ -----------
101                                      8
102                                     25
103                                     68
104                                    100           -- expected prediction 185
105                                    100           -- expected prediction 502
106                                    100           -- expected prediction 212

I’ve disabled comments so that you can read the answer at OTN if you want to – but see if you can figure out the reason before reading it. (This reproduces on 11g and 12c – and probably on earlier versions all the way back to 9i).

I haven’t done anything extremely cunning with hidden parameters, materialized views, query rewrite, hidden function calls, virtual columns or any other very dirty tricks, by the way.