Blog Aggregator - Amardeep Sidhu

Well not quite, but I was awake at 4:30am. So I ran through my presentation a couple of times and made some final tweaks to the slides. I didn’t have any code demos this year, so there was no chance of me breaking anything.

Every performance problem is one of two things

I almost skipped James Morle’s presentation, because I thought it would cover the same ground as Jonathan Lewis’s talk on Statspack. I’m glad I didn’t though, because it was completely different. But I’m even more frustrated that this talk was scheduled at the same time as Daniel Fink’s talk on Why tune SQL?.

James’s talk was a reassessment of the YAPP tuning method:

R = S + W

That is, response time equals service time plus wait time. Most tuning methods focus on the waits. This talk’s assertion was that most fixes actually deal with skew or latency, that is, the service time. In a nutshell, Morle’s Grand Unified Theory of Performance Tuning is: All problems are caused by skew, latency or both.

Bandwidth is not the same as latency: bandwidth is how much transport you have, latency is the wasted time associated with each operation. James demonstrated the difference between latency and bandwidth with some live action in which two volunteers transported bottles of beer across the auditorium, in a suitcase or by hand. The impact of Connor McDonald’s infamous “squirrel sketch” is still rippling across the presentation pond. However, this example is easier to blog. Given two networks:

which one can transmit the most data? The answer is the WAN; the LAN’s enormous bandwidth is wasted because of the additional latency in its ping speeds. Of course, the WAN is operating more-or-less at capacity and the LAN isn’t.

Problems with bandwidth are easy to fix - they just require money and bandwidth is getting cheaper all the time. They are also easy to spot. Latency on the other hand is bounded by the laws of physics, which means it is not necessarily fixable to chucking money at it. Also, it can be hard to spot. Latency multiplication is a very common source of problems - 1ms per operation is a very short time until you do that operation a thousand times.

Common sources of latency:

• nested loop joins
• SQL*Net message to/from client
• too many commits
• “slow” I/O

Skew is also hard to spot because humans (and computers) tend to think linearly, and unconsciously split things into evenly-sized chunks.

Common sources of skew.:

• distribution of column values
• high demand for small set of resources (contention)
• response times for component SQL statements in a transaction

.
So the revised version of the YAPP formula is

R = S + W

where S = (skew * service time) and W = (skew * latency + passive wait time).

Modelling on the cheap

This presentation originally started out as some jokey test data, when the UKOUG asked some of us SIG chairs to test the conference site. But the title was sufficiently compelling for me to submit it as a real paper and, more surprisingly, for it to be accepted. I think many people found the title compelling because it focuses on a real problem: how to undertake database and application modelling when the boss won’t spring for an expensive CASE tool. I work for a consultancy; consultancies tend to put software licences in one of two categories - those for which the client pays and overhead. So I have an interest in tools and techniques which don’t require me to get a business case approved.

I talked about low tech solutions (whiteboards, Post-It notes and index cards), desktop tools (PowerPoint and Visio) and then free modelling tools (basically Schemester). There is JDeveloper but I don’t think that is really suitable for people who are developing database applications. However the rumours suggest that JDev 11g will have much more to offer on that side of things, and that means that we can hope those features will eventually be ported to SQL Developer. I feel the session went well but I shall have to wait until UKOUG publishes the evaluations.

Afterwards, somebody who introduced themselves as one of the JDev development team who worked on the original Use Case modeller said they started out modelling that tool with index cards and Post-Its. And Duncan Mills said that he didn’t think the JDev features set was any worse that Schemester. It’s a fair point: Schemester’s big advantage is that it is a mere 295KB zip to download and has a memory footprint of ~12MB of RAM. So, unlike JDev, we can run Schemester without nadgering our desktop.

I’ve been doing a lot of research lately into setting up Drupal as a Content Management System. One of Drupal’s shortcomings (by design) is the current production version doesn’t have a WYSIWYG editor. The lack of a visual editor is not a big deal since there are several editors you can just plug-in (more or less) as a module.

The first editor I tried was TinyMCE by Moxicode. I originally

A common question that comes up on various forums from time to time is the vexed question of how to set credentials for enterprise manager to perform the various os tasks that it is able to on Windows hosts. The root cause of this is almost certainly the fact that the error message that is fed back when EM fails to authenticate correctly to the OS is

Invalid username or password

When more often it should really read

Insufficient privileges.

There are 4 system privileges that you need to give to the os account that you use for EM authentication to the O/S these are

• Logon as a batch job
• Act as part of the operating system
• Adjust memory quotas for a process
• Replace a process level token

On Windows 2000 machines the third is named Increase memory quotas. In addition it seems sensible to me to create an account specifically for this purpose - either local to the server or a domain account - and grant these rights to the account, make it a member of the ORA_DBA group if necessary and revoke the logon interactively privilege from the user.

You can adjust these settings as an administrator using the group policy editor - or persuade your sysadmins to create a domain wide policy along these lines using the User Rights Assignment tree under Windows Settings shown in the screenshot below. The group policy editor can be fired up by choosing Start |run gpedit.msc.