April 2016

EM13c- BI Publisher Reports

How much do you know about the big push to BI Publisher reports from Information Publisher reporting in Enterprise Manager 13c?  Be honest now, Pete Sharman is watching…. </p />
</p></div></div>

    	  	<div class=

Column Groups

Patrick Jolliffe alerted the Oracle-L list to a problem that appears when you combine fixed length character columns (i.e. char() or nchar())  with column group statistics. The underlying cause of the problem is the “blank padding” semantics that Oracle uses by default to compare varchar2 with char, so I’ll start with a little demo of that. First some sample data:

Wednesday Philosophy – A Significant Day (but only to me)

Today is a significant day. Well, to me it is – to the rest of you it’s just a Wednesday in the latter half of April, in the mid 20-10’s. Because we count in 10s (probably due to the number of flexible pointy bits on our front limbs, but that is a much debated argument) we have “magic” numbers of 10, 100, 1000 and multiples thereof. As geeks we also have 2,4,8,16,32 etc. And as nerds (but nerds who appreciate certain literature) we have 42. But today is not significant to me for any of those magic numbers.

nVision Performance Tuning: Coalescing Tree Leaves

I have blogged recently about the effectiveness of the Tree Performance options in improving the performance of nVision reports.
However, a consequence of the Tree Performance Access Method suppress join; use literal values is that the resulting SQL in nVision will have a criteria for every leaf on each of the selected nodes on that tree.

nVision Tree Performance Options|

VirtualBox 5.0.18

VirtualBox 5.0.18 has been released.

The downloads and changelog are in the usual places.

So far I’ve only installed it on Windows 7, but I’ll no doubt be doing an install on OS X El Crapitan and Oracle Linux tonight. </p />
</p></div>

    	  	<div class=

Subtle variations in optimizer stats

Subtle variances in the way you work with statistics can have a significant impact on how your optimizer plans work out…so you need to take care.

Let’s look at the following example


SQL> create table T (
  2    x varchar2(20) , y varchar2(100));

Table created.

SQL> insert into T
  2  select 'x' , rpad('z',100) from all_objects;

94117 rows created.

SQL> insert into T
  2  select 'X' , rpad('z',100) from all_objects;

94117 rows created.

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true)

PL/SQL procedure successfully completed.

SQL> create index TX on T ( upper(x) ) ;

Index created.

So you can see that in reality, ALL of the rows have a single value for UPPER(X), namely “X”. So let’s look at an execution plan.

Quick tip on Function Based Indexes

For “normal” indexes, USER_IND_COLUMNS will contain the column name(s) for an index, but things (appear to) go astray when looking at function-based indexes.


SQL> create table blah ( x varchar2(30));

Table created.

SQL> create index blah_ix on blah ( upper(x));

Index created.

SQL> select column_name from user_ind_columns
  2  where index_name = 'BLAH_IX'
  3  /

COLUMN_NAME
------------------------------
SYS_NC00002$

Don’t panic. Simply take a look at USER_IND_EXPRESSIONS to find the function that you used.

Video : Flashback Query

Today’s video is a quick demo of flashback query.

If you prefer to read articles, rather than watch videos, you might be interested in these articles.

A little known RI clause

Most people are aware of this standard referential integrity control, namely you can’t delete the parent if children exist

Spot the Oracle Faces

My wife has been going through old photo’s from her mother today, trying to find a picture of Uncle Stan. In the box of photographs was also a magazine – an Oracle magazine!

Oracle Magazine award winners 2003!

Oracle Magazine award winners 2003!