Index creation monitoring + select/inserts...

Main Page | Report this Page

Computers Forum Index » Computer - Databases - Oracle (Server) » Index creation monitoring + select/inserts...

Page 1 of 1

Index creation monitoring + select/inserts...

Author

Message

briandba...

Posted: Tue Oct 06, 2009 9:02 pm

Guest

Hi,
i'm new to oracle (i'm from db2) eand i have basics questions on
oracle 9i and 10g:
how can i view the progression of a create index or alter index
rebuild ?
there is no entry in v$session_longops for these orders

and another question, how can i determine the number of users select,
insert, update ,delete on the database since the startup ?

thanks
brian

ddf...

Posted: Tue Oct 06, 2009 10:40 pm

Guest

Comments embedded.

On Oct 6, 4:02 pm, briandba <briandba... at (no spam) gmail.com> wrote:

Quote:

Not directly. Indirectly you'd be looking for table scan operations
on the base table for the index being created/rebuilt (although there
may not be a table scan for a rebuild since Oracle will usually scan
the existing index for entries).

Quote:

and another question, how can i determine the number of users select,
insert, update ,delete on the database since the startup ?

Audit those operations. As SYS as SYSDBA execute :

audit select table, insert table, update table, delete table;

You would then query DBA_AUDIT_TRAIL for the results of those audits.

Quote:

thanks
brian

David Fitzjarrell

madhu...

Posted: Wed Oct 07, 2009 3:55 am

Guest

On Oct 6, 4:02 pm, briandba <briandba... at (no spam) gmail.com> wrote:

Quote:

Fore creation or rebuild, try querying v$session_longops to get an
indication of progress or amount of work left.
Also, for creation of index, if you query dba_segments based on
tablespace you will find a weird named segment (with numbers). That's
the "TEMP" segment as the index is being built, once index creation
finishes, the weird segment will now have the actual name of index.
The size from the dba_segments is indicative of how much of it is
built (assuming you have a fair idea of final size).

briandba...

Posted: Wed Oct 07, 2009 8:40 am

Guest

On 7 oct, 00:40, ddf <orat... at (no spam) msn.com> wrote:

Quote:

Comments embedded.

On Oct 6, 4:02 pm, briandba <briandba... at (no spam) gmail.com> wrote:

Hi,
i'm new to oracle (i'm from db2) eand i have basics questions on
oracle 9i and 10g:
how can i view the progression of a create index or alter index
rebuild ?
there is no entry in v$session_longops for these orders

Not directly. Indirectly you'd be looking for table scan operations
on the base table for the index being created/rebuilt (although there
may not be a table scan for a rebuild since Oracle will usually scan
the existing index for entries).

and another question, how can i determine the number of users select,
insert, update ,delete on the database since the startup ?

Audit those operations. As SYS as SYSDBA execute :

audit select table, insert table, update table, delete table;

You would then query DBA_AUDIT_TRAIL for the results of those audits.

ok but that's means overhead
it's hard for me to not find counter for insert/update/select/... on a
table basis
because without activating audit , i'm not able to determine if there
is user activities on a table TEST for example.
I don't want the SQL but the counters, frequently i need to tell if
there are activities like insert on a table.
Maybe in 11g it's possible ?

Quote:

thanks
brian

David Fitzjarrell

briandba...

Posted: Wed Oct 07, 2009 8:42 am

Guest

On 7 oct, 05:55, madhu <madhusree... at (no spam) gmail.com> wrote:

Quote:

On Oct 6, 4:02 pm, briandba <briandba... at (no spam) gmail.com> wrote:

Hi,
i'm new to oracle (i'm from db2) eand i have basics questions on
oracle 9i and 10g:
how can i view the progression of a create index or alter index
rebuild ?
there is no entry in v$session_longops for these orders

Fore creation or rebuild, try querying v$session_longops to get an
indication of progress or amount of work left.
Also, for creation of index, if you query dba_segments based on
tablespace you will find a weird named segment (with numbers). That's
the "TEMP" segment as the index is being built, once index creation
finishes, the weird segment will now have the actual name of index.
The size from the dba_segments is indicative of how much of it is
built (assuming you have a fair idea of final size).

thanks i will check this, it's works too for online rebuild ?

Srivenu Kadiyala...

Posted: Wed Oct 07, 2009 5:34 pm

Guest

You can turn on Monitoring on the table and query
DBA_TAB_MODIFICATIONS.
(call dbms_stats.flush_database_monitoring_info before querying
DBA_TAB_MODIFICATIONS)
regards
srivenu

joel garry...

Posted: Wed Oct 07, 2009 6:22 pm

Guest

On Oct 6, 2:02 pm, briandba <briandba... at (no spam) gmail.com> wrote:

Quote:

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 (no spam) home.com is bogus.
http://www.computerworld.com.au/index.php?q=article/321082/an_inteview_brian_kernighan_co-developer_awk_ampl&fp=&fpid=

briandba...

Posted: Wed Oct 07, 2009 7:29 pm

Guest

On 7 oct, 20:22, joel garry <joel-ga... at (no spam) home.com> wrote:

Quote:

On Oct 6, 2:02 pm, briandba <briandba... at (no spam) 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 (no spam) 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 Smile

John Hurley...

Posted: Wed Oct 07, 2009 9:21 pm

Guest

On Oct 6, 5:02 pm, briandba <briandba... at (no spam) gmail.com> wrote:

snip

Quote:

If I were you I would stop and not pass go until you have purchased
and read cover to cover a couple of times Tom Kyte's book "Expert
Oracle Database Architecture".

Follow that up with reading a couple of times Cary Millsap's book
"Optimizing Oracle Performance".

Taking that kind of path will stop you from barking up the wrong
trees.

You want to understand from the get go how oracle does things. It
does not matter how you would do things in DB2 ... just let it go!
( From an old IMS and DB2 DBA ... now an old Oracle DBA ).

Good luck and have fun and read read read!

joel garry...

Posted: Sun Oct 11, 2009 12:56 am

Guest

On Oct 10, 2:54 am, briandba <briandba... at (no spam) gmail.com> wrote:

Quote:

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 Smile

Not long after I posted my answer, a user came up to me and asked how
long until her (infrequent data generation) process would take. Of
course, I had to say I didn't know, but did she try it in the test
system? She had, and it had taken 4 hours. So I said I'd look and
see if I can figure it out (sometimes I can using the tools
available), but it would likely take less than 4 hours.

So I started poking around, and immediately saw in EM that it was
doing something I almost never see, lots of parallel read waits. So I
delved into that in the docs and on the intertubes, and found this was
one of those examples where one person published incorrect/out of date
information, and it has propagated all over the place. Fortunately,
Tanel Poder had published a few comments on it, and I learned
something new. It is well known that for some operations Oracle will
ask the I/O system for a single block, and for others it will ask for
multiple blocks. Obviously, if you are doing a full table or index
scan you want to suck up a lot of blocks. Some situations it is kind
of a tossup whether to get single or multiple blocks. Turns out there
is an internal table that tracks some of those situations - Oracle
will try doing multi block reads, and track how often it has to
discard the extra blocks. Then it will decide based on previous
results whether one way is better than another. Of course, if it is
doing this, and you do something infrequent but similar, it may decide
wrong. You can research which way is best for your sql, and tell it
how if it insists on doing it wrong, but that is a bit late when
someone is asking you about something already running.

Now, as it turned out, it completed in about an hour in production.
Is this due to smarts, the different configuration, or what (both
going to the same SAN)? I'll never know, but if it is 4 times faster,
I'll pretend I don't care, though I'm curious and wish I had time to
dig into it more. Of course, my boss hates that it doesn't work like
DBase, and would certainly get upset if I got uppity and asked if he
preferred it take 4 hours. Why the user would even be asking after
she already knew it could take 4 hours... well, that's a user.

Some people say the proper answer is to get plans and metrics for
everything before it is put in production. I think that is impossible
for an ERP.

jg
--
at (no spam) home.com is bogus.
http://www.mercurynews.com/science/ci_13518073?source=rss

Page 1 of 1

All times are GMT The time now is Sat Dec 12, 2009 3:55 am Computers Forum Index