Search

Top 60 Oracle Blogs

Recent comments

August 2013

MV Refresh

Here’s a funny little problem I came across some time ago when setting up some materialized views. I have two tables, orders and order_lines, and I’ve set up materialized view logs for them that allow a join materialized view (called orders_join) to be fast refreshable. Watch what happens if I refresh this view just before gathering stats on the order_lines table.

I have a little script that start with “set echo on”, then calls two packaged procedures, one to refresh the join view, the other to collect stats on the order_lines table; here’s the output from that script:

Adding a node to a 12c RAC cluster

This post is not in chronological order, I probably should have written something about installing RAC 12c first. I didn’t want to write the tenth installation guide for Clusterware so I’m focusing on extending my two node cluster to three nodes to test the new Flex ASM feature. If you care about installing RAC 12c head over to RAC Attack for instructions, or Tim Hall’s site. The RAC Attack instructions are currently being worked at for a 12c upgrade, you can follow/participate the work on this free mailing list.

The cluster I installed is based on KVM on my lab server. I have used Oracle Linux 6.4 with UEK2 for the host OS. It is a standard, i.e. not a Flex Cluster but with Flex ASM configured. My network configuration is as shown:

The Broken Statistics: "parse count (total)" and "session cursor cache hits"

The values provided by the “parse count (total)” and “session cursor cache hits” statistics are subject to several bugs. And, what’s worse, for years Oracle didn’t care to fix it. This is my impression, at least.

Then, when few weeks ago I read in the Oracle Support note 13837105.8 (Bug 13837105 – statistics “parse count (total)” and “session cursor cache hits” miscounted) that the bug introduced in 11.2.0.3 was fixed, I hoped that others bugs in this area were fixed as well.

Unfortunately, it’s not the case. What a disappointment!

Even though several posts were already wrote about this topic (e.g. here and here; check the comments as well…), I thought it could be useful to summarize what the current status is.

OSWOUG Conference

Thank you very much who all attended my day long seminars in Portland, OR and Seattle, WA for Oregon and Southern Washington Oracle User Groups (OSWOUG). Listening to one speaker for 5 hours definitely was not easy. I understand that and appreciate your gesture.

Attached please find the various slides and scripts I used in the demo (Remember: this is a 2 MB file).

http://www.proligence.com/pres/oswoug13/oswoug13.zip

In addition, please read my following blog posts I referenced during my talks.

http://arup.blogspot.com/2011/01/how-oracle-locking-works.html
http://arup.blogspot.com/2010/12/100-things-you-probably-didnt-know.html
http://arup.blogspot.com/2011/07/who-manages-exadata-machine.html

Hope you found the sessions worthwhile, educational and entertaining.

LAOTN Tour (Southern Leg) : It’s a wrap!

After fluking a business class flight out to Latin America, I wasn’t looking forward to a cattle-class ride home, but when I got to Sao Paulo airport I got a free upgrade to business class. :) A number of things did not go well during my outbound journey with AirFrance and I was extremely critical on my customer satisfaction survey. I can only imagine this was the reason for my upgrade on the way back, but maybe I just got lucky…

Oracle 12c: Scalar Subquery Unnesting transformation

I promised to write about Oracle 12c new features quite a while ago (when 12c got officially released), but I was actually on (a long) vacation then and so many cool 12c-related white-papers and blog entries started popping up so I took it easy for a while. I plan to be focusing on the less known low-level internal details anyway as you see from this blog entry.

As far as I can remember, Oracle has been able to unnest regular subqueries since 8i and merge views since Oracle 8.0.

First, a little terminology:

Oracle 12c: Scalar Subquery Unnesting transformation

I promised to write about Oracle 12c new features quite a while ago (when 12c got officially released), but I was actually on (a long) vacation then and so many cool 12c-related white-papers and blog entries started popping up so I took it easy for a while. I plan to be focusing on the less known low-level internal details anyway as you see from this blog entry.

As far as I can remember, Oracle has been able to unnest regular subqueries since 8i and merge views since Oracle 8.0.

First, a little terminology:

Oracle 12c: Scalar Subquery Unnesting transformation

I promised to write about Oracle 12c new features quite a while ago (when 12c got officially released), but I was actually on (a long) vacation then and so many cool 12c-related white-papers and blog entries started popping up so I took it easy for a while. I plan to be focusing on the less known low-level internal details anyway as you see from this blog entry.

As far as I can remember, Oracle has been able to unnest regular subqueries since 8i and merge views since Oracle 8.0.

First, a little terminology:

Update: I have changed the terminology section below a bit, thanks to Jason Bucata’s correction. A scalar subquery sure can also be used in the WHERE clause (as you can see in the comments). So, I clarified below that this blog post is comparing the “Scalar Subqueries in SELECT projection list” to “Regular non-scalar Subqueries in WHERE clause”. I am writing a Part 2 to explain the scalar subqueries in WHERE clause.

NVL()

Here’s a little detail that I hadn’t noticed before (and it goes back to at least 8i). This is running on 11.2.0.3, and table t1 is just all_objects where rownum <= 20000:

latency heatmaps in D3 and Highcharts

See Brendan Gregg’s blog on how important and cool heatmaps can be for showing latency information and how average latency hides what is really going on:

 
Now if we want to create heatmap graphics, how can we do it? Two popular web methods for displaying graphics are Highcharts and D3. Two colleges of mine whipped up some quick examples in both Highcharts and D3 to show latency heatmaps and those two examples are shown below. The data in the charts is random just for the purposes of showing examples of these graphics in actions.