DML does it block readers of data

[hrishy]

Hi All

I have a table with 940,000 records.I open a sqlplus session and delete from this table.In another sqlplus session i do select count(*) from the same table..Now this seleect count(*) seems to take a while..My understanding of oracle is that in oracle readers of data do not block other DML's or is my understanding wrong..I am using undo tablepsaces and running 9i..

regards
Hrishy

[hrishy]

Hi

All i think i found my answer..The table with autotrace on shows too high consistent gets..i think the highwater mark is way too high :-)

regards
Hrishy

[rajabalachandra]

hi,

if you have not committed in the first session
the second session will scan through the
rollback segment to read all deleted but
not committed records.so there will be a delay.

this is not blocking;just read consistency.

even if you have committed then any session
will scan till the end of the high water mark.
so there will be a delay, even if there is 0 rows.


Raja

[hrishy]

Hi Raja

You are bang on target with your response..I appreciate that :-).Just wundering with the consistent gets can i guess the higwater mark :-)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
110104 consistent gets
55739 physical reads
83824 redo size
194 bytes sent via SQL*Net to client
248 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

My consistent gets with 940,000 records is very high..from the consistent gets of 110,104 can we guess where the highwater mark was..the explain paln shows that it did use a index fast full scan.

regards
Hrishy

[abhaysk]

Originally posted by hrishy
Just wundering with the consistent gets can i guess the higwater mark
regards
Hrishy

I would think the ans will be 'NO'

[hrishy]

Hi Abhay

Thanks for the imput...i tested and agrred taht you acnt make out from the consistent gets :-)..

regards
Hrishy