Re: Index creation monitoring + select/inserts
Date: Wed, 7 Oct 2009 13:53:41 -0700 (PDT)
Message-ID: <36e8b704-e5e8-458c-bf90-1a0bf4665e6b_at_m7g2000prd.googlegroups.com>
On Oct 7, 12:29 pm, briandba <briandba..._at_gmail.com> wrote:
> On 7 oct, 20:22, joel garry <joel-ga..._at_home.com> wrote:
>
> > On Oct 6, 2:02 pm, briandba <briandba..._at_gmail.com> wrote:
>
> > > and another question, how can i determine the number of users select,
> > > insert, update ,delete on the database since the startup ?
>
> > Why exactly do you need to know this? It sounds like an app-side
> > issue.
>
> > jg
> > --
> > _at_home.com is bogus.http://www.computerworld.com.au/index.php?q=article/321082/an_intevie...
>
> I come from db2 and usually i monitor the number of select, insert ,
> update , full scan, index access on some tables in real time to check
> activity when asked.
> the same when the index are rebuilded or created, on large table i can
> exactly tell when it will be finished,
> we have migrated to oracle and i search the equivalent. maybe it's
> not a good solution but you know bad habits last :)
:)
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. asktom.oracle.com 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: http://dbaoracle.net/readme-cdos.htm
jg
-- _at_home.com is bogus. http://www.mirror.co.uk/news/top-stories/2009/09/27/conputer-meltdown-115875-21703149/Received on Wed Oct 07 2009 - 15:53:41 CDT