Getting Help

I hope people won’t take this as a suggestion that I want them to start using this blog like a forum – but I’d like to highlight a note written some time ago by Randolf Geist on the OTN DBA Forum: HOW TO: Post a SQL statement tuning request – template posting It’s worth following his link to the related posting by Rob van Wijk.

If you want to post a question on the Oracle forums, or newsgroups, or the list servers, (or even raise an SR) you need to think a little carefully about the information that you know but aren’t telling everyone else about. Even following the suggestions from Randolf and Rob it’s still likely that someone will ask you for more information – but at least with their guideline you’ve given other people a possible starting point for understanding your problem.

Subquery Factoring (3)

From time to time I’ve warned people that subquery factoring should be used with a little care if all you’re trying to do is make a query more readable by extracting parts of the SQL into “factored subqueries” (or Common Table Expressions – CTEs – if you want to use the ANSI term for them). In principle, for example, the following two queries should produce the same  execution plan:

SQL Server 4

SimpleTalk have just published another of my SQL Server articles – which talks about storage methods and block dumping in SQL Server, and ends with the suggestion that the reason that SQL Server DBAs seem to be very keen on “clustered indexes” may be related to the fact that SQL Server doesn’t seem to handle free space management for heap tables very well.

In the same week: SimpleTalk interviews Tom Kyte in their “Geek of the Week” series.

Stress Test

Here’s something you probably don’t want to say at the start of a public presentation: “Does anyone have a laptop I could borrow for the next 90 minutes.”

I’ve just done my first ODTUG presentation – and everything I do runs under VMWare, which simply refused to start. I’m in my room now trying to fix it before tomorrow’s session. (Many thanks to James Haslam from UKOUG who happened to be in the room at the time and offered up his laptop.)

Update: Inevitably it’s now running perfectly with no intervention at all – so I have to go back downstairs and see if it breaks again.

Coalesce

The following question came up in an email conversation a little while ago:

Are you aware of any problems a large oltp site might have with running index coalesce during production hours, as opposed to doing index rebuilds in a maintenance window?

Errors

I wish more people were aware of problems like this:  Error found on Internet!

Memory

A client recently upgraded from 32-bit Oracle to 64-bit Oracle because this would allow a larger SGA. At the same time they increased their SGA from about 2GB to 3GB hoping to take more advantage of their 8GB of RAM. The performance of their system did not get better – in fact it got worse. You might think that they should have increased the SGA even further at this point – but read on before you give them that advice.

Russian Forum

This is probably something that anyone who needed to know already knew, but there is a very active forum in Russian for Oracle users – it’s actually just one part of a much larger Russian forum, but it’s the Oracle bit that catches my eye occasionally. If you haven’t come across it before, the lead-in URL is http://sql.ru/forum/actualtopics.aspx?bid=3

I expect to take this post down after a few days – but if anyone wants to add similarly busy links for other non-English Oracle forums, I’ll leave it open for comments. Just one link per comment, though, or they’ll be automatically spam-dunked.

Lock Modes

I usually think about locks (and the meaning of the lmode and request columns in v$lock) in terms of numbers, and never seem to remember which number goes with which name – apart from mode 6 = exclusive. So I’ve finally put up a little list somewhere I’ll always be able to find it if I need it.

I wish (2)

Continuing my little list of things in SQL Server that I’d like to see in Oracle:

Quiz Night

If you want to be good at trouble-shooting one of the most important skills you have to learn is how to examine the evidence. Here’s a section of a trace file that was published recently on the OTN database forum. There’s an interesting detail that you might pick out – how long will it take you to spot it ?

Continued Rows

Do you think you know how Oracle records access to migrated or chained rows ? You may need to check your theories. Here’s a little demonstration that may amuse you. It uses an 8KB block size, a locally managed tablespaces (LMT) with uniform extent sizes of 1MB, and freelist management rather than automatic segment space management (ASSM).

Quiz Night

A recent question on OTN gave the following information:

I’m having problem with my database (Oracle 8.1.7.2.0 on Solaris 9), which contains more than 1 row with a same value on a field that has uniqueness constraint.

Could you please help tell me how to fix this?

Here is the log from sqlplus. When I select on RI field, it shows 2 rows. But when I select on SCNUM field, it shows only 1 row. This SCNUM has an unique index on it. And it is still in VALID state

SQL> select ri, scnum from scratch1_p where ri in (536964983, 536955574);

        RI SCNUM
---------- ----------
 536955574 444393975
 536964983 444393975

So we have a unique index on the single column SCNUM, but we can report two rows with the same value for SCNUM.

Beginner/Expert

I mentioned some time ago the presentation I did at Open World 2009 and Miracle Open World called “The Beginner’s Guide to becoming an Expert”. Over the last few months I’ve had a few people email me asking me if I’m going to post the presentation on my blog. The answer is no – because there isn’t a presentation.

Testing

The old chestnut of “optimal block size” came up on OTN again a few weeks ago, with someone asking for advice on how to do some testing to decide on the optimal block size for a database. The correct answer to this question is you don’t: you assume you are going to use the default size for your platform and then think about whether there are any very specific jobs that your application does that might gain some sort of worthwhile benefit if you used a non-default size.

Nevertheless, the OP came back some time later with a few results which suggested that some of his tests showed that a 4KB block size gave significantly better performance than the same tests using 8KB and 16KB block sizes.

But there’s a problem with the conclusion. If you examine the results carefully, and think about what type of work must happen in the tests, you realise that this particular test was not about the blocksize – it was about the network and the client program. (I haven’t included a link to the posting where I explained this – it’s just a little later in the same thread. This is just to give you the option of working out why the test is wrong before you read my comments about it.)

Update 18th Aug 2010

The investigation continues – with the OP comparing the resultsof using a table with a single 2000 byte column to a table with many columns with an similar total size. Again, though, the anomaly in timing he is chasing seems to be about network traffic time, NOT about database block size.

(I’ve only sent one reply to this thread at the moment, but the OP has been good at supplying extra data in the past, so the discussion may evolve to produce further interesting information.)