Search

Top 60 Oracle Blogs

Recent comments

August 2013

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":

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:

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.