Top 60 Oracle Blogs

Recent comments

November 2015

Little things worth knowing: automatic generation of extended statistics in 12c Part II

In my last post I demonstrated an example how to use a PL/SQL package to monitor a workload in order to identify correlated columns. Such columns are good candidates for the creation of extended statistics since the optimiser does not assume that information stored in different columns may be related. Before starting my tests I invoked DBMS_STATS.SEED_COL_USAGE, which in turn increased Oracle’s attention level trying to find correlated columns. Eventually these have been identified (as shown in the output of DBMS_STATS.REPORT_COL_USAGE) and a subsequent call to DBMS_STATS.GATHER_TABLE_STATS caused extended statistics to be created, including histograms. This is one way you can get extended statistics automatically, but it requires you to enable monitoring of a workload by invoking a PL/SQL API.

Filter Hash

One of the most irritating features of solving problems for clients is that the models I build to confirm my diagnosis and test my solutions often highlight further anomalies, or make me ask questions that might produce some useful answers to future problems.

Recently I had cause to ask myself if Oracle would push a filter subquery into the second tablescan of a hash join – changing a plan from this:

	hash join
		table access full t1
		table access full t2
	table access by rowid t3
		index range scan t3_i1

to this:

hash join
	table access full t1
		table access full t2
		table access by rowid t3
			index range scan t3_i1

or, perhaps more likely, to this:

Barnes and Noble Maker’s Faire- Raspberry Pi Power, Activate!

This Saturday I’ll be part of a nationwide event being put on by the Raspberry Pi Organization and Barnes and Noble book stores.  Makers Faire’s have been gaining international attention as the place to see really cool tech and inventions “in the wild” by everyday people making a difference.

IDC report: Virtual Data 461% ROI over 5 years

read the full IDC report on Virtual Data here.



Here are some key sound bites from the report:

“Data has turned IT departments into business enablers. Data acts as a tax on IT operations when IT departments cannot do what businesses need them to do because they are too busy ‘keeping the lights on.’ ”

“When we bought Delphix, the original reason was all of the terabytes we were avoiding. But now, the main benefit for us is the agility that it gives us — the increase in productivity. We didn’t realize when we bought it that the agility would be so great.”

Microsoft Outlook : When Bad UX Attacks!

I guess there are lots of problems with the User eXperience (UX) of Microsoft Outlook, but the one that kills me is the popup menu in the folders pane.

I’m not sure how other people use this, but for me, the number one thing I do is “Delete All”, closely followed by “Mark All as Read”. I have a bunch or rules that “file” irrelevant crap, which I later scan through and typically delete en masse.

So what’s the problem?

The folder operations are higher up the menu, so I’m constantly doing “Delete Folder”, rather than “Delete All”, which drives me mad. Especially when I don’t notice and all my rules start failing.

Column Groups

I think column groups can be amazingly useful in helping the optimizer to generate good execution plans because of the way they supply better details about cardinality; unfortunately we’ve already seen a few cases (don’t forget to check the updates and comments) where the feature is disabled, and another example of this appeared on OTN very recently.

Modifying the example from OTN to make a more convincing demonstration of the issue, here’s some SQL to prepare a demonstration:

Make it easy or die (software is eating the world)


Software is eating the world

  • We see taxis being beaten out by Uber and Lyft.
  • We see hotels being undercut by Airbnb.
  • We see brokerage firms undercut by Ameritrade and Etrade.
  • We see retailers under cut by Amazon.
  • Video stores have been supplanted by Netflix

Today it’s all about developing software that makes access to your product easier.

We are also seeing the same thing happen in IT where cloud such as AWS is undercutting industry hardware vendors like Oracle, Netapp, EMC

Steve Jobs : the journey of simplicity

#37404e;">Steve Jobs sets a great perspective on the journey of simplicity. It starts from simple, goes through complexity and ends up in simplicity. 
#37404e;" />
#37404e;" />#37404e;">“When you start looking at a problem and it seems really simple, you don’t really understand the c#37404e;">omplexity of the problem. Then you get into the problem, and you see that it’s really complicated, and you come up with all these convoluted solutions. That’s sort of the middle, and that’s where most people stop. But the really great person will keep on going and find the key, the underlying principle of the problem — and come up with an elegant, really beautiful solution that works.” – Steve Jobs


Diff’ing AWR reports

#555555;">I don’t know if you are ever asked to compare to AWR periods. AWR period comparison is pretty easy if you have access to the two periods in the same AWR repository. AWR in the same repository can be compared with

#555555;">SELECT * FROM TABLE(
         [db_id ],
         [instance id],
         120, -- start snapshot id
         121, -- end snapshot id
         [db_id of target,
         [instance id] ,
         122, -- start snapshot id
         123  -- end snapshot id));

#555555;">and it can be run for single instance as

Pragma UDF – Speeding Up your PL/SQL Functions Called From SQL

A new feature for PL/SQL was introduced in V12, pragma UDF. UDF stands for User Defined Functions. It can speed up any SQL you have that uses PL/SQL functions you created yourself.

{please see this second post on some limitations of pragma UDF in respect of IN & RETURN data types and parameter defaults}.

We can create our own functions in PL/SQL and they can be called from both PL/SQL and SQL. This has been possible since V7.3 and is used extensively by some sites to extend the capabilities of the database and encapsulate business logic.