Open letter to Larry Ellison

My opinion about AWR/ASH is pretty clear - don't run something in my (or my client's) database that I can't look at and can't disable easily.

Mark Brinsmead has started a grass roots campaign to 'unrestrict' access to AWR/ASH data. I strongly suggest you take a look and sign if you agree.

Toeing the Party Line

Along with my membership in the BAARF Party, I am also a member of the BAAG party. BAAG stands for Battle Against Any Guess (though there some interesting organizations also using that acronym). Several events, including Dave Ensor's talk at MSDBF and my own recent experience with a nasty bit of sql that seemed to defy tuning have made me very sensitive to the issue of being able to repeat our successes in diagnosis and resolution of Oracle issues.

Way to go Alex!

Peter, Regular Expressions and Statspack

On my recent trip to Scotland, I was very fortunate to spend time with a dear friend, Peter Robson and his family. Their hospitality and warmth is simply beyond words (and his tenacity in dealing with British Airways Misplaced Luggage Department was amazing...but that is another story).

Peter's speciality (other than trying to blow up an island in the Firth of Forth during his geologist years) is SQL. During our jaunts about town and country before and after the Miracle Scotland DB Forum, we chatted about the new Regular Expressions in Oracle 10g. Like analytical functions, they seem to be slow to adopt, but very powerful. On the last day of my visit, the latest copy of UKOUG's publication Oracle Scene arrived with Peter's article about regular expressions...actually two copies arrived so I was able to bring back my own copy to the states.

Yesterday, I was looking at extracting the actual text of a sql statement that contained a lot of extraneous spaces. While I think that proper formatting and spacing are vital for writing supportable code, when I am extracting the actual text for a performance report, I really don't want to see that. I started the process of creating my own recursive REPLACE() function (REPLACE only works on one pass through...not enough to get all the double spaces), when I thought "I wonder if this can be done using regular expressions?"

And the answer is....drum roll please...

ABSOLUTELY!

In fact, in Peter's article he uses just this situation as an example for REGEXP_REPLACE. Here's the code I used to make the sql text more report friendly...

SELECT REGEXP_REPLACE(sql_text,'( ){2,}',' ') parsed_piece
FROM stats$sqltext
WHERE hash_value = &sql_hash_value
ORDER BY piece;

Enjoy!