Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Hotsos 2010 - Monique

There she goes, squirreling into the corner of your carry-on.  Ready for another adventure.  Tucked between a fun floppy hat and the flourescent sun-block, of which she uses plenty, pale creature that she is, Monique is situated to prove to you that a seasoned traveller knows best.  Dressed in a curly mohair (so as not to show the wrinkles that betray one new to the jet set) and sporting a pale chiffon scarf - to double as a disquise in those cities where she is already known for her somewhat silly escapades - she seems to wink at you and beckon you along.  Forget your cares, but by no means your playing cards, and step lightly into whatever dreams your next voyage should hold for you:  let Monique be your guide.  

Her very presence alleviates all the maladies brought on by the jostling of trains, the dipping of ships, the swerving of buses and even by the simple fact that you are far from home.  For she has discovered the secret to successful travel; with the exception of the luscious few minutes in the X-ray machine, where she delights in making funny faces at the operators searching for more dangerous contents.  Monique never looks back.

Charleen Kinser Designs

(With thanks to Carol Dacko for bringing along an interesting dinner companion for my own cuddly friends. Somehow I doubt they'll ever be the same - she's terribly sophisticated for my lot!)

Hotsos 2010 - What's THAT?

I heard someone bemoaning the lack of Swag at Hotsos - just an event program, couple of magazines and a small clockwork toy sponsored by Oracle. I don't think I ever come to the Symposium expecting Swag - it's just not that kind of event. But you do tend to get good speaker gifts, including the best speaker gift I ever received. This year, it's a digital photo frame.

As for that clockwork toy, the Cuddly Toys think it's *brilliant*!!! At first, their reaction was "What's THAT?!" then when I wound it up and let it dance in front of them, they fell in love.

The only thing is that it doesn't run for long per wind-up, so my right wrist is aching from them continually beggind me to "Do it again, Douglas!"

Then again, their reaction wasn't quite as relaxed as when they met Carol Dacko's friend while we were at the restaurant. Apologies for the quality of the photo, it was dark in there, but at least it proves that Little H and Chris were allowed out for dinner and that not all Cuddly Toys are made the same.

They were just stunned by their new friend. What's THAT?

All will be revealed in the next post ....

fast refresh of join-only MVs: _mv_refresh_use_stats and locking log stats

A devastating performance degradation of materialized view fast refreshes can happen in versions after 9i - and can be healed rather easily by simply setting the hidden parameter _mv_refresh_use_stats or, a bit surprisingly, by locking statistics on the logs. The problem can manifest at least in the currently-latest patchsets of 10g, 11gR1 and 11gR2 (10.2.0.4, 11.1.0.7 and 11.2.0.1), seems to hit a lot of people, and its root cause are the utilization of wrong hints by the Oracle refresh engine.

We will investigate the join-only MV case only, since this is the case I have investigated after a question by Christo Kutrovsky, factoring in some observations by Taral Desai and some Support notes; I have some clues that something similar may happen for other types of MVs.

The test case sets up this very common scenario for fast refreshes:

1 - two big base tables joined together by the MV;
2 - only a small fraction of rows modified (actually one deleted, two updated, one inserted);
3 - all tables and indexes with fresh statistics collected;
4 - MV logs with no statistic collected AND with not-locked statistics;
5 - indexes present on the joined columns;
6 - indexes present on the rowid columns of the MV.

Points 1 and 2 make for the ideal scenario for incremental ("fast") refreshes to be effective; 3 is very common as well, since you normally have many other statements issued on the tables; the relevance of 4 will be clear later, but it happens very often in real life, since people might perhaps consider collecting stats on the log, but locking their statistics is usually not made, at least in my experience.

To understand the importance of points 5 and 6, please check this post of mine; note how those indexes are a necessary prerequisite for the sanity of the DEL and INS steps of the MV process. Without them, the refresh cannot be incremental since it has no physical way to read and propagate only the modified rows and those related to them, but it must scan (uselessly) most of the base tables and MV. But in other for the refresh to be incremental ("fast"), those indexes have to be actually used...

the issue

Let's illustrate the issue focusing on the DEL step (the easier to discuss about). In the above mentioned post, we have seen that the DEL step uses a single SQL statement whose text, leaving out minor technical details and hints, is:

/* MV_REFRESH (DEL) */
delete from test_mv
 where test_t1_rowid in
       (
select * from
       (
select chartorowid (m_row$$)
  from mlog$_test_t1
 where snaptime$$ > :1
       ) as of snapshot (:2)
       )

In 9.2.0.8, we get this very healthy plan:

-------------------------------------------------
|Id|Operation             |Name                 |
-------------------------------------------------
| 0|DELETE STATEMENT      |                     |
| 1| DELETE               |                     |
| 2|  NESTED LOOPS        |                     |
| 3|   VIEW               |                     |
| 4|    SORT UNIQUE       |                     |
| 5|     TABLE ACCESS FULL|MLOG$_TEST_T1        |
| 6|   INDEX RANGE SCAN   |TEST_MV_TEST_T1_ROWID|
-------------------------------------------------

That is: get the rowid of all modified rows from the log, and use the rowid-based index to delete the "old image" of them from the MV (inserting their "new image" is the job of the INS step). This is truly incremental, since the resource usage and elapsed time are proportional to the number of rows logged in the MV log, not to the dimension of the tables.

In 10.2.0.4, 11.1.0.7 and 11.2.0.1 the plan becomes:

------------------------------------------
|Id|Operation              |Name         |
------------------------------------------
| 0|DELETE STATEMENT       |             |
| 1| DELETE                |TEST_MV      |
| 2|  HASH JOIN RIGHT SEMI |             |
| 3|   TABLE ACCESS FULL   |MLOG$_TEST_T1|
| 4|   MAT_VIEW ACCESS FULL|TEST_MV      |
------------------------------------------

Oops, the indexes are not used ... hence the DEL step overhead is proportional to the size of the MV, and that can be definitely unacceptable.

That is due to the engine injecting an HASH_SJ hint in the outermost nested subquery:

... WHERE "TEST_T1_ROWID" IN (SELECT /*+ NO_MERGE  HASH_SJ  */ ...

This is recognized as a bug in many scenarios (start from Oracle Support note 578720.1 and follow the references to explore some of them) even if I have not found a clear and exhaustive note that documents the behaviour.

remedy one: set "_mv_refresh_use_stats"

To get back to the healthy plan, simply set "_mv_refresh_use_stats" to "true" (ask Oracle Support first of course for permission); this makes for a set of hint much more adequate for a fast refresh:

... WHERE "TEST_T1_ROWID" IN (SELECT /*+ NO_MERGE  NO_SEMIJOIN  */ ...

Note: The root cause for this bug is probably due to a change hinted in note 875532.1 - in 10.2.0.3 the meaning of _mv_refresh_use_stats was reversed, but not the default, hence (by mistake?) activating a different piece of the engine code.

The very same problem happens for the INS step; I won't go into much details here (please check the test case spools provided above if interested), but in 9.2.0.8 the base table modified rows are directly fetched using the rowid contained in the log:

-----------------------------------------------------
|Id|Operation                      |Name            |
-----------------------------------------------------
| 0|INSERT STATEMENT               |                |
| 1| TABLE ACCESS BY INDEX ROWID   |TEST_T2         |
| 2|  NESTED LOOPS                 |                |
| 3|   VIEW                        |                |
| 4|    NESTED LOOPS               |                |
| 5|     VIEW                      |                |
| 6|      SORT UNIQUE              |                |
| 7|       TABLE ACCESS FULL       |MLOG$_TEST_T1   |
| 8|     TABLE ACCESS BY USER ROWID|TEST_T1         |
| 9|   INDEX RANGE SCAN            |TEST_T2_J2_1_IDX|
-----------------------------------------------------

Instead, in 10.2.0.4, 11.1.0.7 and 11.2.0.1 we get the following plan:

--------------------------------------------------
|Id|Operation                   |Name            |
--------------------------------------------------
| 0|INSERT STATEMENT            |                |
| 1| TABLE ACCESS BY INDEX ROWID|TEST_T2         |
| 2|  NESTED LOOPS              |                |
| 3|   VIEW                     |                |
| 4|    HASH JOIN RIGHT SEMI    |                |
| 5|     TABLE ACCESS FULL      |MLOG$_TEST_T1   |
| 6|     TABLE ACCESS FULL      |TEST_T1         |
| 7|   INDEX RANGE SCAN         |TEST_T2_J2_1_IDX|
--------------------------------------------------

Whose resource consumption is, of course, proportional to the size of the base table.

Even in this case, this is due to the nasty HASH_SJ hint:

... FROM "TEST_T1" "MAS$" WHERE ROWID IN (SELECT  /*+ HASH_SJ */  ...

If you set _mv_refresh_use_stats, you get back the 9.2.0.8 plan - and thus you are back to incremental for both the DEL and INS steps. As a side note, a cardinality hint is used, where the cardinality is set to the correct value (6 in my test case):

... FROM "TEST_T1" "MAS$" WHERE ROWID IN (SELECT  /*+ CARDINALITY(MAS$ 6) NO_SEMIJOIN ...

 
remedy two: collect and lock statistics on the logs

Very interestingly, instead of setting the hidden parameter, you have another way to get back to the healthy plan: gather statistics on the MV logs when they are empty AND lock them (as suggested in note 578720.1, albeit not in this scenario and even if setting the parameter is not necessary; thanks to Taral Desai for pointing me to the note). In this case, no hint at all is injected beside a NO_MERGE for the DEL step:

... WHERE "TEST_T1_ROWID" IN (SELECT /*+ NO_MERGE  */ ...
... FROM "TEST_T1" "MAS$" WHERE ROWID IN (SELECT  ...

So, the engine is confident that the CBO will come out with a good plan, and it does not inject any "intelligent" hint. Possibly, and intriguing, this is because by locking the statistics, I am assuring the engine that these statistics are representative of the data anytime. So, locking the statistics is not meant only as a way to prevent dbms_stats from changing them ... it is deeper than that. At least in this case, you are taking responsibility for them, and Oracle will take that in consideration.

Hotsos 2010 – Is it a Chicken, a Duck? No wait it’s Bob!

Bob Sneed being a hero and not to afraid to dance during the Hotsos Disco Night were others would chicken out…

Hotsos 2010 – Presenters, Presentations, Presenting

I never find it very easy to try to capture the atmosphere during a conference, the presenting part, the presentations or the discussions, for example, you could have with the presenters. Hotsos is such a cool and unique event were you have the opportunity, to listen but also to interact. The amount of people that attend isn’t that big, only a few hundreds, but they share all the same passion, the passion to improve on performance, mostly Oracle related. It has only two tracks and its not uncommon that people present and than go afterwards to a presentation to listen in what the other presenter has to say.

Due to the fact that it is manageable in terms of choice, located on a convenient location and well organized, you have the opportunity to pick just the thing you like and most of the time not miss out on “the other” presentation you would have liked to see. Besides that people stay in (overnight in the Hotel), so discussions about the technology, the method or an example during a presentation will be discussed in far more detail than you normally would do, from presenter to presenter or from presenter to the guy that attended and vice versa. The fact that all have the passion for performance or that they realize that performance is a beast with various angles to approach, bounds, and every point, every question is one to be heard and/or discussed. On equal terms. If you have seen my video impressions of Hotsos in 2009, you get a bit of what I am trying to say.

Anyway, not being a native speaker and with my new “flip” at hand (Ultra HD), I just show you how much fun and interesting stuff is going on, probably this will do more right to those presentations than me trying to explain what all those new ideas which were that popped into my head during this years Hotsos Symposium…although…sometimes technology helps and sometimes it just doesn’t…or is it a VMware thing Doug…?

My agenda is not that much different then the one from Doug. The moment it is mentioning RAC related stuff, then I probably would have been attending the other guy.

The Monday started great with a small introduction of Tom Kyte’s generally unknown other qualities…

…Enjoy…

Ohhh…and I wasn’t the one, by the way, that was giggling… On a more serious note, the following clip will show how Tom Kyte handles date time conversions, if remembering birth dates.

Alas I didn’t manage to film a more serious piece of his keynote, so sorry. The “flip” thing was a new attempt to capture the atmosphere and I wasn’t sure how it would workout…and as you noticed, using the zoom function on the “Ultra HD” flip, is not a good idea…

The conceptual presentation of Alex Gorbatchev was cool and well thought out. To give you an impression I have two clips that are a good representation of the humor and the topics that were discussed during his “Battle Against Any Guess” presentation. The first part is a good example why it is sometimes handy to be lazy…

In his more serious section of his presentation, Alex discusses the use of checklists…

As always, Cary Millsap’s presentation was very useful and I really love his way of presenting (it looks easy – but it isn’t – very clearly discussing the topic). In this case the presentation topic was “Lessons Learned” about testing and the boundaries you should respect, if your test set-up should be useful. Apparently he tested his idea’s, once again, this time using his daughter as a test subject addressing the issue: “Can she lift 15 pounds…?”

Besides Sales guys being addressed, Cary sums it up on how cool it is to test stuff until it breaks…

Doug Burns had a very unlucky day. Sometimes the demo setup was working, sometimes it didn’t. Having a presentation that only consisted out of demo’s, and only a few slides for starters at hand, he had to improvise. In the end, as you can see here on his blog site, he had only time for 30 minutes doing his demo’s. Seen the presentation already during Oracle Open World in 2009 during a “Unconference” session, I can only say: wow, what did those people miss out… but alas, that’s the risk of doing demo’s. Sometimes it goes wrong…

So here how Doug tries to save the presentation via going in on more detail on Swingbench and tips and advice while using Oracle Enterprise Manager…or with other words: What you can do with a bit of Scottish humor to save the day…

…and how we finally got to the part were I guy saved Doug’s presentation (regarding the demo’s) using a wireless gadget, that probably fixed the issue with the routing table that messed it all up. Oh and a tip to all those life savers out there: “Never type in your secret password while it is being displayed on the big screen…” (we now you bluffed regarding changing it…). ;-)

The last clip is from Kevin Closson’s presentation while he explains a bit about “Why do we still think a CPU is a CPU…”.

Apparently he likes cuddly toys as well…

Ohhh, did I tell that I did well regarding my presentation? I did. How I know? Due to the fact that I encountered most of my audience afterwards (4) during the Disco Night event on Tuesday and they all liked it (total of 7!). They bought me beer even. Cool. Should do it more often, this presenting thing. The other 200+ people who went to Tanel Poder’s presentation, which is their bad luck. If they only knew what they were missing out on. I mean even Doug was positive in his Scottish way trying to tell me. So in all, not bad for a newbie during Hotsos. At that time slot all 200+ plus 7 people had a good show for their money; if only I could have attended Tanel’s… Hmmm.

;-)

PS. Before I forget. I would like to thank Toon Koppelaars for reviewing my presentation and giving me some useful advice.

Always Check Your Backups

So this is a 2 node RAC cluster on RHEL that was recently upgraded from 10.2.0.2 to 10.2.0.4. Since then clusterware restarts crs every few hours (8 in fact). A little research suggests that this can indicate problems with the automated OCR backup.  The first thing therefore is to check the state of the backups. [server] [...]

Thank you for

Thank you for all those who attended my webcast today on SQL Plan Management for All India Oracle User Group. It was a privilege to present before you and to be able to address your questions. I am sorry I couldn;t read all the questions properly; since it was extremely difficult to see the questions scrolling up in the tiny chat window. Also, as the webcast was designed, I couldn't hear anything the attendees were saying.

The presentation and the associated SQL scripts are available here. The article I referred to can be found here.

If you have a question regarding that specific webcast, please post a comment here and I will address it here. Please, limit your questions to the material discussed in the webcast only.

Hands On Labs with Data Guard

I have been planning on publishing more Hands On Labs for Data Guard on Oracle’s Technology Network using the Amazon Elastic Cloud (EC2) as the lab systems. But while I am working on more of them I thought I should post a note about the “OpenWorld 2009 Active Data Guard Hands On Lab” that we have already published. Please head for http://www.oracle.com/technology/deploy/availability/htdocs/adg_hol_2009.html for a set of exercises designed to introduce you to Active Data Guard in Oracle 11g Release 2 as well as some basic configuration and testing exercises.  On that page is the download link for the handbook where you will find instructions on how to get going with my Amazon Cloud Machine (AMI)  image and all the exercises.

I am hoping to do a new and improved version of this hands on lab at OpenWorld 2010 which will include Active Data Guard functionality that I just did not have time to get into last year’s lab.  That new Hands On Lab will be published on OTN after OpenWorld.

Keep an eye on this category for announcements on any other training.

Advertisements

Back Again

So if all goes well the blog should be back again, now powered by WordPress. The blogger posts have made it back, but apparently 2006-2008 never existed. Fortunately it’s probably only me that cares. Those who know me personally will know that I moved jobs, and therefore needed to understand what the “private blogging” position [...]

On "Is a computer science degree a good goal?"

Dan Fink's "Is a computer science degree a good goal?" has gotten my wheels going. I think it's important to note this:

Computer Science ≠ Information Technology

Not only are these two disciplines not equal, neither is a subset of the other.

One of my most memorable culture shocks coming out of school into the Oracle domain was how many people didn't understand the difference between computer science, which is a specialized branch of mathematics, and information technology, which is a specialized branch of business administration. They both deal with computers (the IT major more than the CS one, actually), so of course there's risk that people will miss the distinction.

Over dinner Friday night with some of my friends from Percona, we touched on one of the problems. It's difficult for a technical major in school to explain even to his family and friends back home what he's studying. I remember saying once during my senior year as a math major, "I haven't seen a number bigger than 1 since I was a sophomore." I heard a new one tonight: "I got to the level where the only numbers in my math books were the page numbers."

It's difficult for people who don't study computer science to understand who you are or how the min/max kd-trees and deterministic finite automata and predicate calculus and closures that you're studying are different from the COBOL and SQL and MTBFs and ITIL that the IT majors are studying. It's easy to see why laypeople don't understand how these sets of topics arrange into distinctly different categories. What continually surprises me is how often even IT specialists don't understand the distinction. I guess even the computer science graduates soften that distinction when they take jobs doing tasks (to make a living, of course) that will be automated within ten years by other computer scientist graduates.

I agree with Dan and the comments from Tim, Robyn, Noons, Gary, and David about where the IT career path is ultimately headed in the general case. What I don't believe is that the only career path for computer scientists and mathematicians is IT. It's certainly not the only career path for the ones who can actually create things.

I believe that college (by which I mean "University" in the European sense) is a place where the most valuable skill you learn is how to learn, and that, no matter what your major, as long as you work hard and apply yourself to overcoming the difficult challenges, there will be things in this world for you to do to earn your way.

I really hope that the net effect of a depressed, broken, and downward-trending IT industry is not that it further discourages kids from engaging in math and computer science studies in school. But I don't want for so many of our kids today who'll be our adults of tomorrow to become just compartmentalized, highly specialized robots with devastatingly good skills at things that nobody's really willing to pay good money for. I think that the successful human of the future will need to be able to invent, design, create, empathize, teach, see (really see), listen (not just hear), learn, adapt, and solve.

...Just exactly like the successful human of the past.