Search

Top 60 Oracle Blogs

Recent comments

November 2015

Wrong Results

Here’s a little gem in 12c that arrived in my email a few days ago: a query where the result depends on the SQL*Plus arraysize!

The email had a short description, and a script to create a small data set that would demonstrate the problem. I’m not going to show you the query, or the result set, but here’s a sample of the output from an SQL*Plus session after creating the data. This is, by the way, on a “single-user” system – there is no way that some other session is changing the data – especially after the opening “set transaction”:

Parsing URLs from plsql

Just the other day I needed to parse a URL, and could not find one that acted like I was used to in python. So tired of always missing out in plsql, I want to do something about it.
So yesterday I decided to create a package with different URL utilities that I need from time to time. The package is
called url_ninja. It can split an URL into the different components (scheme, authority, path, parameters, query, fragments)
and back again to a complete URL. It can parse an array into a query string and it can parse a query string into an array.

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 41: Why has my SQL execution plan changed?—A checklist

If the inputs to the CBO change, the plan can change. For example, changes to bind variables can cause the plan to change when it is hard parsed. Therefore one should never be surprised when plans change.(read more)

Oracle ACE program

What is the Oracle ACE program?

The Oracle ACE program is a community advocacy program for Oracle technology evangelists and enthusiasts, sponsored by and managed by Oracle Corporation.  As stated on the ACE overview page at “http://oracle.com/technetwork/community/oracle-ace”, it is both a network as well as a resource for everyone in the Oracle community.  It is not a certification program, and there is no course to study and no test to pass.  Rather, the ACE program is a recognition program by one’s colleagues and peers, and joining is a process of nomination, review, and acceptance.

Who are Oracle ACEs?

Dr. Paranoid's Blog

Friday Philosophy – We Could Be Heroes! {just for one day}.

At Open World I overheard a snippet of conversation which went something like this:

Bob – “How’s it going? Did the last talk go down well?”
Bill – “Sure, it was on time, the audience seemed to like it.”
Bob – “Will you be here next year?”
Bill – “Errmm….” pause…*sigh*… “I don’t know…. I’ll see how I feel.”
Bob – “Oh? In what way?”
Bill – “It just that, at Open World… I have to fend off two dozen people just to go have a pee!”

Wild horses could not drag out of me the name of the person who said that (though several pints and the offer of a curry might do the trick – try me). It both made me smile and made me think. There are down-sides to becoming highly respected in your sphere.

Say DevOps one more time …

Screen Shot 2015-11-04 at 4.02.56 PM

Credit: Matthias Weinberger (CC2)

What is Devops?

As has been noted many of times, DevOps is a term that is hard to define.

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:

filter
	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
	filter
		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.