Re: few perf tuning related questions;

Dear Lun Wing San,
thank you very much for your reply; all you are saying makes a lot of sense: it matches what i know about Sybase behaviour in this regard (i come from Sybase environment); Sybase System XI is ANSI compatible and this is exaclty what it does regarding select/update management.

In regard of many processes hanging on SELECT, i know for sure we do not use SELECT FOR UPDATE; could there be any other reason for what we are observing?

Thanks again

Vlad

On Wed, 20 Nov 1996, Lun Wing San (Oracle) wrote:

> Vladimir Stavitsky wrote:
>
> > 2. Locks issues. I have highly interactive web site with some 50 users
> > doing selects on a table at the same time; a few updates/inserts (say up
> > to 5) might be initiated in parallel with those selects. The question is
> > twofold: do i have to explicitly lock the table? Or Oracle takes care of
> > that? Important thing is that updates/inserts never ever collide,
> > meaning they access ALWAYS different rows. They do collide though with
> > selects, but i do not care as to what status is read - old or new.
> > Second question is - if i do not use any explicit locking, is there a
> > possibility of deadlocks because of the internal locking in the above
> > scenario? And, also, what kind of performance would you expect on
> > SunUltra2 with 4 cpu and some 256 RAM? The table accessed has some
> > 15,000 rows. What i am seeng is that there are a lot of processes doing
> > select running forever - just hanging.
>
> The following situation applies for the read committed isolation level:
>
> Oracle will not impose any lock when you issue SELECT ... FROM table unless you use
> FOR UPDATE option. Even you have an update on the same row, another use can still be
> select that row. All of them are automatically carried out by Oracle if you use the
> default lock setting in the initialization parameter file.
>
> If the transaction contains an update without rollback or commit, another user will
> only see the old information. Alternatively, if the update is committed or rollback,
> another user will see the new information.
>
> For the deadlock, Oracle will detect and resolves it automatically by rollback.
>
> If you found that some processes are hanging during selection. It is better to check
> whether a select ... for update is issued.
>
> ---
> Name : Lun Wing San
> Title : Oracle Application Developer of Hong Kong Productivity Council
> Oracle Database Administrator and System Administrator of QRC
> Phone : (852)27885841
>

                  he who hesitates is probably right

http://www.webvalue.com      Vlad Stavitsky          vlads_at_lvalue.com
---------------------------------------------------------------------