Re: Suggestions for first exam ...

Notes in-line

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____Finland__September 22nd - 24th
____Norway___September 25th - 26th
____UK_______December (UKOUG conference)

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA__October
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Richard Foote" <richard.foote_at_bigpond.com> wrote in message
news:rGgdb.126227$bo1.111770_at_news-server.bigpond.net.au...


>

> Coincidently again (first the last time), I've noticed a new article

 by


> Jonathan Lewis at dbazine which nicely describes why the unbalanced

 Oracle


> index is myth of utter crappy proportions (perhaps my complaint had

 an


> effect ?)

>


I wrote it because I came across yet another bloody
"expert" explaining that sequences cause Oracle's
indexes to get extremely deep on the right so that
indexes need to be rebuilt.

There is another  article coming out soon (I think)
that points out why it is usually a waste of time
to rebuild indexes at all - and gives an example
showing how a regular rebuild can make things
worse.




> Howard, you might be interested to know that Jonathan used the term

 "skewed"


> and to describe the concept of "data skewness" where incrementally

> increasing insert values in the "RHS" of the index in combination

 with


> deletes on the "LHS" of the index results in a balanced index (as it

 *must*


> be) but one in which many of the index blocks on the left hand side

 are far


> less dense in their data content. I quite like the term but it's

 still open


> for mis-interpretation.

>


I suggested that the industry needed a term like 'skewed' to
describe an index where the data density in the leaf blocks
was greater on one side than the other.  Personally, this is
one of those areas of discussion (like 'stripe size') where I
explain what I mean rather than relying on a simple term.

Talking of OCP - have you seen the latest offering from
Oracle Magazine about the 9i Performance Tuning exam.
Examples of what might be in the exam:

<QUOTE>

What init.ora parameter determines the amount of memory
that a server process should use for a sort process?

A. SORT_AREA_SIZE

B. SORT_AREA_RETAINED_SIZE

C. SORT_MEMORY

D. SORT_DISK


</QUOTE>

    Note that this is the 9i exam - so where is the right
    answer in this list ?  PGA_AGGREGATE_TARGET
    is the 'correct' and 'approved' approach to sort tuning
    in 9i - sort_area_size is soooo Oracle 8.


<QUOTE>
Consider the following SQL statement:

    select last_name, first_name,
        department, salary
      from employee
      order by last_name;

As the DBA, assume that you did some research
and found the following:
1. There is one index on the employee table that
    is based on the employee_id column.

2. The user SCOTT who executes the statement
    has the permanent tablespace USER01 assigned
    as both his default and temporary tablespaces.

3. The sort operation is too large to fit within the
    memory space specified by SORT_AREA_SIZE.

</QUOTE>

    Note that this is the 9i exam - and some versions
    of 9 may be different on some platforms, of course,
    but when I tried to emulate the second observation
    on my 9.2.0.3 system, I get
        ORA-12910, or ORA-12911
    i.e. a permanent tablespace cannot be used as a
    temporary tablespace, and a temporary tablespace
    cannot be used as a permanent tablespace.