Re: Suggestions for first exam ...

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:bl61qb$mec$1$8302bc10_at_news.demon.co.uk... <snip>
>
> 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.

Hi Jonathan,

Looking forward to it :)

The problem is of course, especially for those new to Oracle, is learning who to believe. You buy a book, it's been written by someone who appears to have a long list of publications and history in Oracle, surely what they write is correct and accurate. Well, not always and unfortunately for someone new, they adopt these recommended bad practices and so a myth spreads. Bit like a vicious PC virus really with not enough anti-virus protectors around ;)

>
> 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.

Yes !! I had a chuckle when I read these. I actually quite enjoy reading this section of the mag, it's soooo funny and coming from the ranks of Oracle instructors, it's even funnier. P_G_T is one of my fav 9i features and this question is quite tragic in a funny "sort" of way (pun intended;)

>
> <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.
>

The other problem I had with this question is that it suggests creating an index to eliminate the sort. That's a huge "it depends" in my book, especially considering that it could very well be much faster to read the whole table (as it must) efficiently via a FTS and perform the sort than read a large table very inefficiently via a single block at a time, poorly clustered index.

I'm 7, 8, 8i and 9i OCP (Oracle kinda pushed it's instructors to get certified and it was thankfully free) and I can tell you, I was definitely a better DBA when I walked out than when I walked in.

Honest :)

Richard Received on Sun Sep 28 2003 - 08:23:11 CDT