Search

Top 60 Oracle Blogs

Recent comments

April 2010

cursor: pin S waits, sporadic CPU spikes and systematic troubleshooting

I recently consulted one big telecom and helped to solve their sporadic performance problem which had troubled them for some months. It was an interesting case as it happened in the Oracle / OS touchpoint and it was a product of multiple “root causes”, not just one, an early Oracle mutex design bug and a Unix scheduling issue – that’s why it had been hard to resolve earlier despite multiple SRs opened etc.

Martin Meyer, their lead DBA, posted some info about the problem and technical details, so before going on, you should read his blog entry and read my comments below after this:

Problem:

So, the problem was, that occasionally the critical application transactions which should have taken very short time in the database (<1s), took 10-15 seconds or even longer and timed out.

Symptoms:

cursor: pin S waits, sporadic CPU spikes and systematic troubleshooting

I recently consulted one big telecom and helped to solve their sporadic performance problem which had troubled them for some months. It was an interesting case as it happened in the Oracle / OS touchpoint and it was a product of multiple “root causes”, not just one, an early Oracle mutex design bug and a Unix scheduling issue – that’s why it had been hard to resolve earlier despite multiple SRs opened etc.

cursor: pin S waits, sporadic CPU spikes and systematic troubleshooting

I recently consulted one big telecom and helped to solve their sporadic performance problem which had troubled them for some months. It was an interesting case as it happened in the Oracle / OS touchpoint and it was a product of multiple “root causes”, not just one, an early Oracle mutex design bug and a Unix scheduling issue – that’s why it had been hard to resolve earlier despite multiple SRs opened etc.

KGH: NO ACCESS – Buffer cache inside streams pool too!

Some time ago I wrote that since Oracle 10.2, some of the buffer cache can physically reside within shared pool granules.
I just noticed this in an 11.2 instance:
SQL select * from v$sgastat where name like ‘KGH%’; POOL NAME BYTES
------------ -------------------------- ----------
streams pool KGH: NO ACCESS 4186144 So, it looks that also streams pool can surrender parts of its memory granules to buffer cache, if it’s unable to flush everything out from the granule for complete granule handover.

KGH: NO ACCESS – Buffer cache inside streams pool too!

Some time ago I wrote that since Oracle 10.2, some of the buffer cache can physically reside within shared pool granules.
I just noticed this in an 11.2 instance:
SQL select * from v$sgastat where name like ‘KGH%’; POOL NAME BYTES
------------ -------------------------- ----------
streams pool KGH: NO ACCESS 4186144 So, it looks that also streams pool can surrender parts of its memory granules to buffer cache, if it’s unable to flush everything out from the granule for complete granule handover.

KGH: NO ACCESS – Buffer cache inside streams pool too!

Some time ago I wrote that since Oracle 10.2, some of the buffer cache can physically reside within shared pool granules.

I just noticed this in an 11.2 instance:

KGH: NO ACCESS – Buffer cache inside streams pool too!

Some time ago I wrote that since Oracle 10.2, some of the buffer cache can physically reside within shared pool granules.
I just noticed this in an 11.2 instance:
SQL select * from v$sgastat where name like ‘KGH%’; POOL NAME BYTES
------------ -------------------------- ----------
streams pool KGH: NO ACCESS 4186144 So, it looks that also streams pool can surrender parts of its memory granules to buffer cache, if it’s unable to flush everything out from the granule for complete granule handover.

KGH: NO ACCESS – Buffer cache inside streams pool too!

Some time ago I wrote that since Oracle 10.2, some of the buffer cache can physically reside within shared pool granules.
I just noticed this in an 11.2 instance:
SQL select * from v$sgastat where name like ‘KGH%’; POOL NAME BYTES
------------ -------------------------- ----------
streams pool KGH: NO ACCESS 4186144 So, it looks that also streams pool can surrender parts of its memory granules to buffer cache, if it’s unable to flush everything out from the granule for complete granule handover.

ORA-01719 is partially relaxed

You most likely have seen this error before: ORA-01719: outer join operator (+) not allowed in operand of OR or IN Cause: An outer join appears in an or clause. Action: If A and B are predicates, to get the effect of (A(+) or B), try (select where (A(+) and not B)) union all (select [...]

DB Optimizer 2.5 Beta Opportunity

I now have the opportunity to offer a 6 week beta trial of DB Optimizer 2.5 which shows the performance of the databases, identifying load, bottlenecks, top SQL and top Users as well as offering SQL tuning advice, index analysis and visual SQL tuning is now available for beta testing.
More information on database performance in DB Optimizer at
More information on visual SQL tuning and analysis at
To participate in this beta, you will need to complete the application form, as well as DBOptimizer Beta Agreement.