count(*)

It’s quite surprising that I still see people arguing about the fastest way to “count the rows in a table”; usually with suggestions that one or other of the following queries will be faster than the rest:

• select count(*) from tab;
• select count(1) from tab;
• select count(primary_key_column) from tab;

IOTs and blocksize

A question came up on the Oracle database forum a few months ago asking:

What are the benefits and the downside of using IOTs on 16k blocks? Would you recommend it?

I think the best response to the generic question about block sizing came from Greg Rahn in another thread on the forum:

If someone has to ask what block size they need. The answer is always 8KB.***

 

Manual Optimisation 3

[Back to Manual Optimisation part 2]

This little series started from a note I wrote about manual optimisation where I took advantage of a sort operation in a non-mergeable view to produce sorted data from a final nested loop join without including an “order by” that would have produced a large sort operation.

In fact, as I showed in a follow-up post, this was taking a convenient pagination mechanism to an extreme – and you might decide (with good reason, as Tom Kyte did) that it was an extreme that should not be used.

Going too fast

I received an email a litle while ago with an unusual problem. It said:

“One of the jobs which used to take more than one hour to complete is now completing in less than 10 minutes. Neither the application developer nor we (the DBA’s) made *any* changes in the environment/code/database. I can’t work out why it’s got better!”

 

It’s not often that “going faster” is a problem – but there’s a very good reason for being worried about jobs that go faster for no apparent reason – one day your luck is going to run out and the jobs are going to go slower again – and people really notice when things slow down.

Travelogue

In the last five years I’ve visited more than thirty different countries and seen a lot of wonderful sights – some natural,some man-made. But Sunday was the first time I’ve walked into a hotel room and been overwhelmed by the view.

I was in Athens, staying at the Hilton, in a room facing the Acropolis – and most of the wall was glass, so the impact of the view as I walked into the room was staggering.

The picture, inevitably, doesn’t do justice to the scene. When I tried to include the sweep of modern Athens the Acropolis got lost in the picture; when I tried to capture the Acropolis I lost the sense of how it grew out of the surrounding cityscape – this is the best I could do.

When you’re there, the feeling is of an immense sea of modern buildings, with the Acropolis as a giant focal point that spreads a layer of solidity and calm over everything about it. It is an extraordinary contrast.

Let’s Pretend

Here’s a summary of a question that appeared on the Oracle Forum some time ago:

I had been seeing frequent log file switching (resulting in “checkpoint not complete” reports) at night when some export dumps have to occur simultaneously, so I increased my redo log file sizes from 5MB to 10MB.

I now have a user who complains that the system is slow, and I see that the buffer cache hit ratio (BCHR) has dropped to about 90% from what was usually 95% or higher.

Can anyone tell me whether increasing the log file size could cause a performance decrease (and thus buffer cache hit ratio decrease)?

Upgrades

I posted a summary of page hits a little while ago when the WordPress statistics hit 750,000 page views, and commented at the time that a couple of the most popular items seemed to be related to the types of issue you get when you upgrade to 10g.

Well, it occurred to me this morning to take a closer look at the stats, because WordPress has a page which gives a fairly comprehensive break down of the views made over time of each page. I’ve repeated the top three from the previous list, and added one more, with a brief comment about their popularity over time:

• Cartesian merge join – always popular, but a bit of a jump from Mar 2008
• Bind Variables – no signifcant change since original publication
• dbms_xplan in 10g - sharp jump from Mar 2008
• Not In – very sharp jump from Mar 2008

It would be very easy to read too much into these numbers – but one of the side effects of an upgrade to 10g is the impact of “Cost Based Query Transformation” and the clever (sometimes too clever) things it does to execution plans, and three of the top four articles are likely to be of interest to people seeing these side effects.  On the other hand, 10g doesn’t really do anything dramatically different with bind variable issues, and the views for that topic haven’t changed.

Would it be reasonable to suggest that this might be an indication of a surge of people migrating to 10g in the March time-frame ?

XMLDB

I don’t really have anything to do with XMLDB (beyond the fact that it’s an application built in an Oracle database, of course, and subject to tuning and bugs just like any other application), so it was nice to get an email last night from Marco Gralike telling me that the next release of XMLDB was going to include a little enhancement I had suggested to solve a performance issue he was facing with a simple “count(*)” query.

My suggestion was to add a not null constraint to an index on the hidden sys_nc_oid$ column that is the object ID on object table. As I said in a posting on Oracle-L, I couldn’t think of any reason why this would be illegal – and now the constraint is (or will be) official.

Full details are on Marco’s blog.

Analysing Statspack (10)

[Back to part 9][Forward to part 11]

The following question appeared some time ago on the Oracle Forum:

Event                       Waits   Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time                              1,167                           86.9
db file sequential read   111,047       223            2              16.6 User I/O
log file sync              77,390       132            2               9.9 Commit
log file parallel write    78,060       124            2               9.2 System I/O
db file scattered read     79,233        10            0               0.7 User I/O

From AWR I am seeing a lot of CPU taking most call time %. Can anyone explain what this means. I have looked on metalink and there are a number of doc regarding top events in general.

So what could we guess from this minimal slice of an AWR report.