Search

Top 60 Oracle Blogs

Recent comments

August 2013

Death of the Storage Array

When I wrote this article for The Register in October 2010, there was a torrent of naysayers and witch hunters spewing their opinions in the comments section. I don’t have a problem with that, I was only expressing an opinion myself, after all. I don’t actually own a time machine and so any of my … Continue reading "Death of the Storage Array"

Fast refresh of aggregate-only materialized views with SUM – algorithm

In this post I will illustrate the algorithm used by Oracle (in 11.2.0.3) to fast refresh a materialized view (MV) containing only the SUM aggregate function:

create materialized view test_mv
build immediate
refresh fast on demand
with rowid
as
select gby        as mv_gby,
       count(*)   as mv_cnt_star,
       sum  (dat) as mv_sum_dat,
       count(dat) as mv_cnt_dat
  from test_master
 where whe = 0
 group by gby
;

Note that count(dat) is specified - you could avoid that if column dat is constrained to be not-null (as stated in the documentation), but I'm not covering that corner case here.

The MV log is configured to "log everything":

ShowMOS: How to get rid of the annoying “The Page has Expired” dialog in My Oracle Support

So, how many of you do hate the dialog below?

Good news – there is a fix! (or well, a hack around it ;)
Before showing the fix, you can vote & give your opinion here:
Do you love or hate the MOS “page expired” dialog? The fix is actually super-simple. The page expiration dialog that grays out the browser screen is just a HTML DIV with ID DhtmlZOrderManagerLayerContainer, overlaying the useful content.

C-MOS: How to get rid of the annoying “The Page has Expired” dialog in My Oracle Support

So, how many of you do hate the dialog below?

MOS page expired dialog

Good news – there is a fix! (or well, a hack around it ;)

Before showing the fix, you can vote & give your opinion here:

Distributed Queries – 3

A comment I’ve made many times in the past about distributed queries is that Oracle doesn’t try to retrieve histogram information from remote databases when optimizing a query. Checking back through previous posts, though, I don’t think I’ve ever mentioned it on the blog – so here’s an example demonstrating the point.

I’ll start with 11.2.0.3, with some fixed system stats (as they could make a difference to repeatability), and the following data set.

Upcoming Talks: OakTable World and Strata + Hadoop World

I haven’t had much time over the past year to do many blog posts, but in the next few months I’ll be doing a few talks about what I’ve been working on over that time, Cloudera Impala, an Open Source MPP SQL query engine for Hadoop.  Hope to see you at one of them.

OakTable World – September 23-24, 2013, San Francisco, CA
SQL on Hadoop – 11:00am Tuesday, September 24th.

Strata + Hadoop World – October 28-30, 2013, New York, NY
Practical Performance Analysis and Tuning for Cloudera Impala – 2:35pm Wednesday, October 30th.

Oak Table World 2013 (OTW13)

Session from OTW 2012
Event date: 
Mon, 2013-09-23 - Tue, 2013-09-24

Please, please, please instrument your code!

I’m always telling people to instrument their code. Invariably they don’t. Then this happens:

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: