Re: Monitoring Insers & Updates

Run a trace, and from tkprof, you can find the number of cpu seconds it took to insert and to update. If the test you are doing is the only thing going on at the time, you can turn SQL_TRACE to TRUE globally. If you can't do that because of other activity on the database, your programmer can turn SQL_TRACE on for his session only, from his program. If he can't do that, you can identify his session and turn SQL_TRACE on for his session only, with the DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION procedure. As for the inserts, if you have indexes on the table, if you start with an empty table the inserts would be much faster than after you have say, 80,000 rows in the table, because the engine is attempting to rebalance the index. (So for example, if you have a table that you insert into daily, with a primary key, the first 1000 rows, will go much faster than 1000 rows some days later.) By the same token, updates, (which in principle should take longer, since you have to find the row, update it, and generate rollback for it), will exhibit a similar behaviour if one of the columns you update is the leading column on an index on the table.
I hope this helps,
Florin

goyakat_at_my-deja.com wrote:

> I've done all sorts of memory tweaking & now my main apps programmer
> wants to know how long it takes to do 1,000 inserts & then how long to
> do a 1,000 updates. He wants to know if it's the database or the app
> that's sluggish.
>
> AND I can't seem to find any scripts in all the tuning books for this
> (there are plenty of scripts for testing memory use, rollback segs,
> logs). HELP!
>
> Isn't there a time variable I can turn on (or print start time) - run
> the insert script & then print end time?? Where is this magical time(s)
> kept in the data dictionaries??
>
> thanks.
> erin
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Sun Aug 15 1999 - 20:54:32 CDT