10053 viewer

I’ve been trying to find a way to post an executable for several weeks because I’ve been sent a simple viewer for 10053 trace files written by Hans-Peter Sloot of Atos Origin and Robert van der Ende. They wrote this viewer because trace files from event 10053 can be enormous, and scrolling back and fore through them to cross reference the interesting bits can be extremely tedious. Their “tree-viewer” allows you to see all the important headings and expand only the detail you’re interested in.

Back from MOW

My trip back from Miracle Open World was extremely uneventful compared to the travail (and travel) of most of the foreign speakers who were there – 13 hours across five trains to get from Copenhagen to Amsterdam, and the worst delay was a train arriving 30 second late in Osnabruck.  Then the airports reopened before I had to switch to my “emergency exit” plan of taking a ferry from Hoek van Holland to Harwich and a train home.

The reason I mention the trip from MOW is just an introduction to SQL Server. Every year MOW starts with a surprise “infotainment” session on the evening before the main event is due to start, and this year the surprise for most people was a presentation about SQL Server; the surprise for me was that Mogens only told me 15 minutes before it was due to start that I was the one giving it.

DW Introduction

Greg Rahn has been writing a short series on “Core Performance Fundamentals of Oracle Data Warehousing”. Here’s his catalogue of the first four or five articles in the series.

Viewing Figures

Although I wasn’t convinced by the 25,000,000 view figures I reported (temporarily) last week, I’m inclined to trust my page view counter which is currently showing a little of 1,500,000 page views; and, as I have in the past, I thought I would produce a summary of what’s been of most interest to visitors:

If we look at the complete lifetime of the blog, the top five are:

Cartesian Merge Join	19,894
NOT IN	18,493
dbms_xplan in 10g	17,324
Bind Variables	13,773
Statspack Examples	13,076

But restricting the figures to just the last year this changes to:

NOT IN	7,976
dbms_xplan in 10g	6,799
Statspack Examples	6,576
SQL Profiles (10g)	5,376
Cartesian Merge Join	4,992

Perhaps the drop in popularity of the item on Cartesian Merge Joins is an indication that people are getting over the belief that it is automatically a bad thing – which is particularly important given the way that you are more likely to see them appearing in 10g than you used to in earlier versions of Oracle.  (But maybe it’s an indication of a take-up of 11g, where the optimiser has changed yet again.)

Index Rebuild ?

While searching on Metalink for clues about an oddity relating to an index rebuild, I came across Bug 6767655  – reported in 10.2.0.3 and fixed in 11.2.  The problem is described as follows:

When having two sessions running concurrently, one doing inserts to a partitioned table, and the other doing partition maintenance operations, there is a chance of the DML session core dumping in kauxs_do_journal(),  i.e.:

ORA-7445: exception encountered: core dump [kauxs_do_journal()+21] [SIGSEGV]  [Address not mapped to object] [0x2] [] []

DIAGNOSTIC ANALYSIS:
The problem seems to be a timing issue – or at least there is a timing component involved. When doing the partition split, an ORA-54 is normally raised, however by running the split in a loop, it will eventually succeed (and thus causing problems for the session running the DML).

Row count

Here’s a quick and dirty script I wrote some time ago when looking at a problem relating to “buffer busy waits”. We were seeing a large number of waits on a particular table (which we could identify easily from the “Segments by …” part of the AWR report , and I wanted to be able to see how much data was in the block while it was under stress. The point of being able to query the block (repeatedly) in near real time was that it always seemed to be empty if you waited a couple of minutes.  

Predicate (again)

I often make a fuss about making sure that people include the predicate section when looking at execution plans. Here’s another example demonstrating why it can be so helpful. We start with an SQL statement that happens to have a large number of child cursors – and every child cursor has exactly the same plan_hash_value (which almost guarantees they all have the same execution plan):

Analysing Statspack 12

[Further Reading on Statspack]

Part 12 is about a 2-node RAC system.  Someone recently posted links to a matched pair of AWR reports in this thread on the OTN Database General forum and, after I had made a couple of quick comments on them, gave me permission to use them on my blog.

Rule Rules

Everybody knows you shouldn’t be using the Rule-based optimizer (RBO) any more – everyone, that is, except some of the folks at Oracle Corp.

I had a conversation a few weeks ago with someone who was having a problem with their standby database on 10.2 because a query against v$archive_gap was taking a very long time to complete. Now that’s an easy to address (in principle) – collect stats on the underlying X$ objects using the call dbms_stats.gather_fixed_objects_stats() and the magic of cost-based optimisation takes over and solves everything.

Record-Breakers

Browsing around the internet recently I came across this result:

“During February, 2010, jonathanlewis.wordpress.com was positioned by Compete.com as the 33 most visited website in the United States. In order to be ranked in traffic in number 33, jonathanlewis.wordpress.com had 25,165,482 visits.”

Pretty impressive, isn’t it.

On the other hand, WordPress tells me that I got just 45,000 visits in Feb – so which one do I want to believe ? And how did the other one get a result that was so far out ?

Failed Login

Here’s a piece of code I found recently running every half hour on a client site:

SQL_ID = 2trtpvb5jtr53
SELECT
        TO_CHAR(current_timestamp AT TIME ZONE :"SYS_B_0", :"SYS_B_1") AS curr_timestamp,
        COUNT(username) AS failed_count
FROM
        sys.dba_audit_session
WHERE
        returncode != :"SYS_B_2"
AND     TO_CHAR(timestamp, :"SYS_B_3") >= TO_CHAR(current_timestamp - TO_DSINTERVAL(:"SYS_B_4"), :"SYS_B_5")

Analyze This – 2

If you’ve run the scripts from Analyze This, I hope you found that the query gave you two different execution plans. This is my output from the test (with a little cosmetic tidying):