2009 August | Tanel Poder's blog: IT & Mobile for Geeks and Pros

latch: cache buffers chains latch contention – a better way for finding the hot block

August 27, 2009 By Tanel Poder 30 Comments

Here’s a treat for Oracle performance professionals and geeks who are looking for more systematic ways for cache buffers chains (CBC) latch contention troubleshooting. Cache buffers chains latches are taken when a process wants to walk through a cache buffer hash chain, looking if the block with required DBA (data block address) is in buffer cache. If the block happens to be in cache, then in most cases it has to be pinned first before use and unpinned after use, to make sure no-one else can perform an incompatible operation on that block at the same time. The modification of pin structures (pinning/unpinning) is also protected by CBC latches.

CBC latch contention can happen for multiple reasons, but one reason is that there is some really hot block in a SMP system with high number of CPUs (or CMT system with high number of threads like Sun T-series servers). Sometimes there happen to be multiple moderately hot blocks “under” the same CBC latch, which can result in latch contention again.

Traditionally DBAs used to look up the child latch address from V$SESSION_WAIT, sql_trace output or ASH and then look up all buffers protected by that latch from X$BH, using HLADDR column (HLADDR stands for Hash Latch Address). I also have a script for that, bhla.sql (Buffer Headers by Latch Address), which reports me all blocks currently in buffer cache, “under” that particular latch and the corresponding data block addresses and object names:

[Read more...]

Filed Under: Cool stuff, Oracle Tagged With: Internals, Performance, Tools, Troubleshooting

Select COUNT(*) and COUNT(column) are different things!

August 21, 2009 By Tanel Poder 12 Comments

Every now and then I see someone wondering why Oracle is “returning wrong results” for some count queries when counting using COUNT(column_name) instead of COUNT(*) or COUNT(<constant>).

Oracle is actually returning correct results, its just that sometimes the people asking the questions haven’t realized that COUNT(column) is something semantically different from COUNT(*).

COUNT(*) operation counts all rows fed to it by execution plan branch under it.

COUNT(column) operation on the other hand counts all non-null values in that column from rows fed to it by execution plan branch under it.

And here’s a little example:

[Read more...]

Filed Under: Oracle Tagged With: SQL, Troubleshooting

My public appearances and final AOT seminar calendar for 2009

August 13, 2009 By Tanel Poder 2 Comments

Advanced Oracle Troubleshooting for DBAs and Performance Engineers

These are the public seminars I will do in 2009. I won’t add any more for this year as my calendar is almost full.

When I published this list last time I told there would be an addition for San Francisco Bay Area people. The event is added below (11-12 Nov).

9-11. September – Hotsos @ Dallas, TX – Postponed to later. I’ll keep you updated.

14-16. September – Hotsos @ Washington, DC – https://portal.hotsos.com/education/SEM/

5-6. October – Miracle @ London, UK – http://www.miraclebenelux.nl/tanel/

8-9. October – Miracle @ Helsinki, Finland – http://www.miracleoy.fi/

2-3. November – Miracle @ Utrecht, Netherlands – http://www.miraclebenelux.nl/tanel/

5-6. November – Miracle @ Brussels, Belgium – http://www.miraclebenelux.nl/tanel/

11-12. November – San Francisco Bay Area, CA – http://www.nocoug.org (more details to follow)

3-4. December – Miracle @ Birmingham, UK (right after UKOUG conference) – http://www.miraclebenelux.nl/tanel/

Conferences 2009

13. Nov NoCOUG Conference – topic TBD

2.Dec UKOUG Tech and EBS Conference

Latch and Mutex Contention Troubleshooting in Oracle - 1 hour session
Practical Oracle Capacity Planning – 2 hour masterclass
Note that I will be doing my 2-day Advanced Oracle Troubleshooting seminar right after UKOUG on 3-4 Dec in Birmingham. This should be a convenient opportunity to fry your brains even more during the same trip.

6. Dec CMG Conference

How To Do Capacity Planning in Oracle – half day workshop, with Dr Neil Gunther

I will also be around San Francisco when Oracle OpenWorld takes place, I’m not speaking at OOW, but will speak at another special event that same week – so stay tuned ;-)

Filed Under: Cool stuff, Oracle Tagged With: Performance, Training, Troubleshooting

Alter system kill session and ORA-00031: session marked for kill

August 13, 2009 By Tanel Poder 18 Comments

I’m sure you are all familiar with this situation:

SQL> alter system kill session '152,33';
alter system kill session '152,33'
*
ERROR at line 1:
ORA-00031: session marked for kill

The session trying to issue the kill will hang for 60 seconds and then return this “session marked for kill” message. And the target session does not get killed at all.

So why is that?

The issue is in what this alter system kill command is doing. It’s not actually killing the target session (like kill -9 would do for OS processes). It just sets a bit in the target sessions state object, which marks that the target session should end. But its entirely up the target session to check this bit and act on it!

So, intead of ALTER SYSTEM KILL SESSION, the command should look something like ALTER SYSTEM ASK SESSION TO COMMIT SUICIDE.

All the kill session command is doing is ASK the target session to clean up and exit – via setting that bit.

Now, normally the target sessions are nice and check that bit often enough in their code, act on it and die.

But sometimes when the target session happens to be busy looping in some tight loop (due a bug perhaps) or is hung, then it never gets to check that “please die” bit and never exits.

[Read more...]

Filed Under: Oracle Tagged With: Administration, Internals, Troubleshooting