Search

OakieTags

Who's online

There are currently 0 users and 44 guests online.

Recent comments

Affiliations

April 2012

Clustered Indexes

… which, for those in the know, means something to do with SQL Server. (The closest physical feature in Oracle  is the index-organized table, the closest sounding name for an Oracle feature is the index cluster - which, just to add to the confusion, must have a specific index that is called the cluster index).

Redgate has arranged for an online debate between an Oracle specialist (me) and a SQL Server specialist (Grant Fritchey) to talk about the strengths and weaknesses of the two mechanisms, discuss why Oracle users seem to be biased towards one implementation and SQL Server users towards the other, and then see where the conversation takes us. This will be followed by a Q&A session.

Table High Water Mark and How Empty the Table Is

On a recent post about reducing the high water mark in a table by shrinking it someone asked if you could tell how much “space” there was in a table that could be recovered by compressing it.

Yes. As pseudo-code:

select number_of_rows*average_row_length. That is how much data there is.
Divided by the block size, to give how many blocks-worth of data you have.
Divided this by a “overhead” fudge factor to allow for wasted space (see later) of about 20%
Compare to the number of blocks used by the table.

Here is some code to do it, showing some extra data and how we calculate the effective percentage of space used (pct_spc_used). It runs against an example table ‘HWM’

scsi_id and UDEV issues (update)…

Last month I wrote about a problem I saw with scsi_id and UDEV in  OL5.8. As it screwed up all my UDEV rules is was a pretty important issue for me. It turned out this was due to a mainline security fix (CVE-2011-4127) affecting the latest kernels of both RHEL/OL5 and RHEL/OL6. The comments on the previous post show a couple of workarounds.

Over the weekend I started to update a couple of articles that mentioned UDEV rules (here and here) and noticed the problem had dissapeared. I updated two VMs (OL5.8 and OL6.2) with the latest changes, including the UEK updates and ran the tests again and here’s what I got.

Hotsos Symposium 2012 Summary

For those of you who don't follow Twitter (and, let's face it, I still think there are solid reasons to avoid the beast or at least treat it with caution), the 10th Annual Hotsos Symposium finished almost two months ago. It's just taken me a while to blog about it! (Unfortunately, blogging activity has taken a back-seat compared to, well, a bunch of other activities ...)

I'm going to try to talk less about the various dinners and meet-ups with names who most people don't recognise anyway, but Hotsos probably has the highest percentage at any conference of people I know and don't see often (maybe not UKOUG) so there was plenty of food, chat and some somewhat more gentle drinking than most conferences. Or at least the nights finish much earlier than I'm used to at other conferences!

Monday

Broken links fixed :-)

Ok, it took only a year or so, but I’ve fixed most of the broken links (to my scripts etc) in my blog :-)

Please let me know if you hit any more broken links from now on….

_gc_fusion_compression

We know that database blocks are transferred between the nodes through the interconnect, aka cache fusion traffic. Common misconception is that packet transfer size is always database block size for block transfer (Of course, messages are smaller in size). That’s not entirely true. There is an optimization in the cache fusion code to reduce the packet size (and so reduces the bits transferred over the private network). Don’t confuse this note with Jumbo frames and MTU size, this note is independent of MTU setting.

SQLTXPLAIN

Just in on the Oracle-L list server – if you want to hear from the author of SQLTXPLAIN (MOS 215187.1) how to install and use the main features, Carlos Sierra is presenting a one hour seminar on May 15th. In his words:

If you or someone you know may want to attend, please register following link below. Capacity for this 1hr SQLTXPLAIN Webinar on May 15 is limited, so please register early so you can reserve one connection. Feel free to share this link with your customer(s).

https://oracleaw.webex.com/oracleaw/onstage/g.php?d=590415036&t=a

Event Information: Using SQLTXPLAIN to diagnose SQL statements performing poorly

The event is scheduled for 8:00 am in San Francisco, which is 4:00 pm BST (GMT+1) if you’re in the UK.

 

Friday Philosophy – It’s not “Why Won’t It Work!” it’s “What Don’t I Understand?”

I had a tricky performance problem to solve this week. Some SQL was running too slow to support the business need. I made the changes and additions I could see were needed to solve the problem and got the code running much faster – but it would not run faster consistently. It would run like a dream, then run slow, then run like a dream again 2 or 3 times and then run like a wounded donkey 3 or 4 times. It was very frustrating.

For many this would provoke the cry of “Why won’t it work!!!”. But I didn’t, I was crying “What don’t I understand???”. {I think I even did a bit of fist-pounding, but only quietly as my boss was sitting on the desk opposite me.}

I think I’ve always been a bit like that in respect of How Things Work”, but it has been enhanced within me by being blessed to work with or meet people for whom it is more important for them to understand why something is not working than fixing it.

Little things I didn’t know: difference between _enable_NUMA_support and numactl

In preparation for a research project and potential UKOUG conference papers I am researching the effect of NUMA on x86 systems.

NUMA is one of the key features to understand in modern computer organisation, and I recommend reading “Computer Architecture, Fifth Edition: A Quantitative Approach” from Hennessy and Patterson (make sure you grab the 5th edition). Read the chapter about cache optimisation and also the appendix about the memory hierarchy!

Now why should you know NUMA? First of all there is an increasing number of multi-socket systems. AMD has pioneered the move to a lot of cores, but Intel is not far behind. Although AMD is currently leading in the number of cores (“modules”) on a die, Intel doesn’t need to: the Sandy-Bridge EP processors are way more powerful on a one-to-one comparison than anything AMD has at the moment.

Brain Teaser: 10046 Extended SQL Trace Shows a FETCH Call with c=306350000, e=299174653, p=0, cr=22298 – How is that Possible?

April 26, 2012 Last week I posted a popular article that questioned the effectiveness of showing a job candidate a raw 10046 trace file that was captured at level 12, asking the job candidate to evaluate whether or not the SQL statement in the 10046 trace is efficient.  Many of the commenters suggested that the [...]