Search

Top 60 Oracle Blogs

Recent comments

June 2007

Session-level statspack

Statspack is a useful tool for easily gathering and reporting some Oracle’s historical workload statistics. However it has its limitations and problems:

One of them that in past it used to record only Oracle statistics, measured from inside Oracle. This made Oracle and people using Oracle ignore other crucial statistics like OS workload below Oracle. For example statspack may show you heavy latch contention as the performance problem, while looking at vmstat output one would see that the server just has been heavily overloaded with numerous other jobs (like multiple backup, export and compress jobs overrunning their run-window) and the latch contention is just a symptom of CPU starvation.

This has been somewhat addressed by adding V$OSSTAT to Oracle 10g and statspack now gathers this info as well. Now we need people to start looking into those stats.

Session-level statspack

Statspack is a useful tool for easily gathering and reporting some Oracle’s historical workload statistics. However it has its limitations and problems:

One of them that in past it used to record only Oracle statistics, measured from inside Oracle. This made Oracle and people using Oracle ignore other crucial statistics like OS workload below Oracle. For example statspack may show you heavy latch contention as the performance problem, while looking at vmstat output one would see that the server just has been heavily overloaded with numerous other jobs (like multiple backup, export and compress jobs overrunning their run-window) and the latch contention is just a symptom of CPU starvation.

This has been somewhat addressed by adding V$OSSTAT to Oracle 10g and statspack now gathers this info as well. Now we need people to start looking into those stats.

A gotcha with parallel index builds, parallel degree and query plans

Reading the following article about PARALLEL hint by Jonathan Lewis made me remember a somewhat related gotcha with parallelism.
Often when creating (or rebuilding) an index on a large table, doing it with PARALLEL x option makes it go faster – usually in case when your IO subsystem is not the bottleneck and you have enough spare CPU capacity to throw in.
A small example below:
Tanel@Sol01> create table t1 as select * from all_objects; Table created.

A gotcha with parallel index builds, parallel degree and query plans

Reading the following article about PARALLEL hint by Jonathan Lewis made me remember a somewhat related gotcha with parallelism.
Often when creating (or rebuilding) an index on a large table, doing it with PARALLEL x option makes it go faster – usually in case when your IO subsystem is not the bottleneck and you have enough spare CPU capacity to throw in.
A small example below:
Tanel@Sol01> create table t1 as select * from all_objects; Table created.

My version of SQL string to table tokenizer

This one’s a short post on a fairly random topic as unfortunately I don’t have time today to come up with anything deeper :)
I needed to come up with a delimited string to table tokenizer for an Oracle development project. There are quite a few examples out there how to do that, including Adrian Billington’s www.oracle-developer.net and the http://technology.amis.nl/blog/?p=1631.
So far the simplest solution I had seen was using a bunch of INSTR’s, SUBSTR’s and DECODE’s in a CONNECT BY loop.

My version of SQL string to table tokenizer

This one’s a short post on a fairly random topic as unfortunately I don’t have time today to come up with anything deeper :)
I needed to come up with a delimited string to table tokenizer for an Oracle development project. There are quite a few examples out there how to do that, including Adrian Billington’s www.oracle-developer.net and the http://technology.amis.nl/blog/?p=1631.
So far the simplest solution I had seen was using a bunch of INSTR’s, SUBSTR’s and DECODE’s in a CONNECT BY loop.

Advanced Oracle Troubleshooting Guide: When the wait interface is not enough [part 1]

Welcome to read my first real post on this blog!
If I ever manage to post any more entries, the type and style of content will be pretty much as this one: Oracle problem diagnosis and troubleshooting techniques with some OS and hardware touch in it. And internals! ;-)
Nevertheless, I am also a fan of systematic approaches and methods, so I plan to propose some less known OS and Oracle techniques for reducing guesswork in advanced Oracle troubleshooting even further.

Advanced Oracle Troubleshooting Guide: When the wait interface is not enough [part 1]

Welcome to read my first real post on this blog!
If I ever manage to post any more entries, the type and style of content will be pretty much as this one: Oracle problem diagnosis and troubleshooting techniques with some OS and hardware touch in it. And internals! ;-)
Nevertheless, I am also a fan of systematic approaches and methods, so I plan to propose some less known OS and Oracle techniques for reducing guesswork in advanced Oracle troubleshooting even further.