Re: Index creation monitoring + select/inserts

From: briandba <>
Date: Sat, 10 Oct 2009 02:54:13 -0700 (PDT)
Message-ID: <>

> :)
> That's understandable.  It becomes less than predictable when you take
> the Oracle view which puts greater emphasis on concurrence issues, and
> recognizes explicitly that in a modern system, using a single-user
> worldview simply becomes nonsense, even in the degenerate case of "I'm
> the only one on the system, creating/rebuilding this index."  I agree
> that given a steady-state system, it should be an easy procedure to
> predict when a simple process will end, and Oracle has somewhat agreed
> with longops.  However, when you actually try to write such tools, you
> have to deal with the details that become difficult to generalize.  So
> you have a choice:  use the tools available, or dig in deep to write
> your own.  Since you have db2 experience, presumably you have a deep
> understanding of that engine, and you merely have to watch out for
> making invalid assumptions on how things work.   There is a lot of
> information available from Oracle, and way lots from third party
> sources.  For example, the Cary Millsap book is good for explaining
> how to tune and why, and Jonathan Lewis' book on the Optimizer is a
> bible - Jonathan even recommends a db2-centric book to understand how
> indices are physically constructed.  Richard Foote has a blog that
> delves into indices in great detail, including how to investigate what
> is happening.  In fact, there is a group called Oak Table where all
> the members publish stuff, often very deep, and tend to be very
> helpful if you ask questions the right way (often a demonstration
> including DDL and load data statements).  Also, there is the
> Enterprise Manager tool which comes along with the database and allows
> some things to be seen, and often there is a way to see what it is
> doing. often has discussions about various aspects
> of concrete problems.  And there is a lot of just-plain-crap on the
> internet.
> So there are many things that can slow you down, they may be system
> related or sql related.  Oracle allows some insight into what it
> thinks is going on, and it is a matter of training and practice to be
> able to use that - from plans to traces to statspack to advisors to
> wait analysis.  Personally, I tend to try things on a test system and
> set my expectations with that, and only bother to investigate if there
> is something beyond the bounds of my expectations.  In the past I've
> done things like try to get an idea of what is going on by looking at
> active transactions in undo, but now it just seems like unnecessary
> work.  I mean, how often do you rebuild indices?  Either you're OLTP,
> in which case it should be a rare event (definitely a hot button in
> the Oracle world!), or you have some kind of DW where you are doing it
> all the time and have metrics to set your expectations.  DSS, BI,
> heterogenous systems - well, I don't want to get started on that rant,
> let's just say there are some issues there.  When it comes down to it,
> empirical testing is the only way to tell for sure.  Then things
> change.
> So now you may see why the correct answer to your original post may be
> "why do you want to do that, what do you really want to know?"  Think
> about what may be going on - are you loading up a hardware cache?  Are
> you making Oracle create multiple views of the data in shared memory
> to support concurrent access?  Which exact way are you rebuilding the
> index - do you understand what locking may be necessary to do it
> online?  Do you even need to be doing anything?  Many have pointed out
> that the best performance comes from not doing something.
> If you are into tracing analysis, you can find exactly how much time
> Oracle is spending on various I/O scenarios - once you know that, it
> becomes easy to spot when something isn't running right, and fairly
> easy to figure out how much a given data volume will take to do
> something.  Is that any better than just timing it?  It is when you
> need to figure out a problem, you have the skills.  It then becomes a
> management decision as to how DBA-like they want their DBA's to be.
> Welcome to the Oracle online community!  A mini-faq about this group:
> jg
> --
> is bogus.

thank you for your answer, i understand that each dbms is different and i must understand the concepts behin them. but now i for some tasks my users are also accustomed to specific answers. index rebuilding time, access, select,are only a litte part. And if i understand that i must take another pat in oracle, it's harder for me to answer my customers, it will be finished when it's finised.

Anyway i'm going to buy these books ans read, read ,read :) Received on Sat Oct 10 2009 - 04:54:13 CDT

Original text of this message