Ixora News - June 2001

Welcome to the June 2001 issue of Ixora News.
To unsubscribe send a blank email to quit-news@ixora.com.au.

In this issue ...

Searching questions	- Improvements to the Ixora search engine
Briefly busy	- Catching buffer busy waits with SQL
Temporary confusion	- Sorting out temporary tablespaces
Expert guidance	- A new book from Tom Kyte

Searching questions

We've made several improvements to the Ixora search engine this month. It is now easier than ever to find exactly what you're looking for, because the search engine understands Oracle terminology. It knows that rollback segments are sometimes called undo segments, that CBO means the cost based optimizer, that the dictionary cache is also called the row cache, that Oracle Parallel Server is called Real Application Clusters at 9i, and so on. Whatever you search for, documents that use alternative terminology will be included at the end of the search results. We've also increased the number of other web sites that are indexed, and indexed the Oracle documentation too. So even if there is nothing relevant to your search on the Ixora web site itself, you can still find the quality information that you are looking for elsewhere.

Briefly busy

It is difficult catch buffer busy waits and in particular the wait parameters when querying V$SESSION_WAIT because the waits are sometimes so transient despite being very frequent. One approach is to query V$SESSION_WAIT multiple times within a single query. This prolongs the query execution time and greatly increases your chances of catching sample wait parameters without resorting to tracing. Our new APT script buffer_busy_sample.sql illustrates this technique which is of general usefulness in quite a number of situations.

Temporary confusion

Under Oracle8i there are three types of tablespace that can be used for temporary segments, and there is some confusion about the differences. In short, you should use the most recent type of temporary tablespace available. The others are only preserved for backward compatibility.

Release Syntax Contents Extent Management Files Nologging

to 7.2 create tablespace ... any dictionary datafiles implicit for temporary segments

7.3 & 8.0 create tablespace ... temporary ... temporary only dictionary datafiles implicit

8.1 create temporary tablespace ... temporary only local, uniform tempfiles explicit

Release	Syntax	Contents	Extent Management	Files	Nologging
to 7.2	create tablespace ...	any	dictionary	datafiles	implicit for temporary segments
7.3 & 8.0	create tablespace ... temporary ...	temporary only	dictionary	datafiles	implicit
8.1	create temporary tablespace ...	temporary only	local, uniform	tempfiles	explicit

There is also some confusion about terminology because the term temporary tablespace is variously used for all three. Where distinction is needed, we use the terms tempfile tablespace for the last type, temporary contents tablespace for either of the last two types, and permanent contents temporary tablespace and temporary contents datafile tablespace for the first two types respectively, with the simple term temporary tablespace being reserved to refer to the tablespace assigned to a user for temporary segment creation, or extent allocation.

The motivation for using a temporary contents tablespace rather than a permanent contents temporary tablespace is to reduce the risk of contention for the ST enqueue. The motivation for using a tempfile tablespace rather than a temporary contents datafile tablespace is to reduce backup and recovery time-frames. The extent management difference is insignificant, as almost all extent management is done via the sort extent pool, and there is no real difference in the logging behaviour.

Expert Guidance

Tom Kyte's new book "Expert One on One: Oracle" has just been released, and can now be ordered from Amazon.com. This is a very important book for Oracle developers. It develops the thesis that database applications should seek to exploit the chosen database's strengths rather than attempting to be database independent. Tom has a very clear understanding of Oracle's strengths and he explains them in a way that will inspire and motivate you to make the most of them. You'll never want to touch a different database again!

Tom also tells many cautionary tales about the perils of ignorance. There are many things about the Oracle database that you do not need to know, but there are some that are vital to your success as an Oracle developer. This book tells you what is important, and seldom bores you with what is not. If you read this book and take it to heart, you will avoid many pitfalls that could otherwise make your project fail.