AN EXPT: Ur Views Appreciated

[quester]

I created a table "SAMP" from Session #1 and INSERTED abt 400000 records.

I then did a SELECT * FROM SAMP; and the query started to fetch and display the rows. Now while this continued to fetch...

I DROPPED the table SAMP from Session #2 and it did drop the table.

But the Session #1 remains uneffected continuing to fetch the data...infact I guess its still going on (seems like fetching all of the data).

Can someone explain how can this be possible? Is the data coming from the DB Buffers OR is the data coming from Data Blocks of the Database (Unreleased space of the Table, though the Table is dropped) !?!

Quester

[ggnanaraj]

Originally posted by quester
Is the data coming from the DB Buffers OR is the data coming from Data Blocks of the Database (Unreleased space of the Table, though the Table is dropped) !?!
Quester

Must be from the data block buffers in the SGA, I suppose.

[quester]

Originally posted by ggnanaraj
Must be from the data block buffers in the SGA, I suppose.

I thought so too...but not very convincing. In the first place, I was wondering how did it allow the table to be dropped?? Does this mean there is no LOCK enabled on the table when the rows are fetched?? Infact while it is retrieving, it even allows the table to be TRUNCATED from another session.. I am a little confused..

[grjohnson]

Quester... why would a row be locked when reading it???

Anyway, I don't think it's in the Buffer Cache since performing a FTS reads into the LRU end on the buffer cache at DB_FILE_MULTIBLOCK_READ_COUNT. Personally, I'd say the process has already completed and all it's doing is spooling to the terminal. I'm sure when you run the query the second time, you get a table does not exist.

[quester]

Originally posted by grjohnson
Quester... why would a row be locked when reading it???

Anyway, I don't think it's in the Buffer Cache since performing a FTS reads into the LRU end on the buffer cache at DB_FILE_MULTIBLOCK_READ_COUNT. Personally, I'd say the process has already completed and all it's doing is spooling to the terminal. I'm sure when you run the query the second time, you get a table does not exist.

Yeah, doesn't make sense for the rows to be locked while reading. If it's not in the buffer cache, where is the data before getting displayed??? I am sure that Oracle fecthes the data batch-by-batch from the database, specically for long runnig queries.

Obviusly it will give an error when I run for the second time, but while the process of fetching is still going on (almost for 20 mins), where the data pulled from??

[adewri]

Quester you are right... it does go bach by batch...

i tried it out.

SQL> create table test (enumber number);

SQL> declare
  2  x number;
  3  begin
  4  for x in 1.. 80000 loop
  5  insert into test values (100000);
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> commit;

Now i fetched all the rows from the table and at the same time from another session dropped the table.

SQL> select * from test;

   ENUMBER
----------
    100000
    100000
    100000
    100000
    100000
    100000
    100000
    100000
    100000
    100000
    100000
         .
         .
         .
    100000
ERROR:
ORA-08103: object no longer exists



48165 rows selected.

So my db cache must smaller than yours, yours might be big...
Try on a smaller SGA

my test db has the following memory structure

Total System Global Area  101784268 bytes
Fixed Size                   453324 bytes
Variable Size              75497472 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes

db_cache_size 25165824
shared_pool_size 50331648

HTH

[quester]

Originally posted by adewri
Quester you are right... it does go bach by batch...

i tried it out.

SQL> create table test (enumber number);

SQL> declare
  2  x number;
  3  begin
  4  for x in 1.. 80000 loop
  5  insert into test values (100000);
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> commit;

Now i fetched all the rows from the table and at the same time from another session dropped the table.

SQL> select * from test;

   ENUMBER
----------
    100000
    100000
    100000
    100000
    100000
    100000
    100000
    100000
    100000
    100000
    100000
         .
         .
         .
    100000
ERROR:
ORA-08103: object no longer exists



48165 rows selected.

So my db cache must smaller than yours, yours might be big...
Try on a smaller SGA

my test db has the following memory structure

Total System Global Area  101784268 bytes
Fixed Size                   453324 bytes
Variable Size              75497472 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes

db_cache_size 25165824
shared_pool_size 50331648

HTH

Yeah u r right. I guess my SGA size must be big, I shall check out that. Anyway, it is a clear indication that : (a) The data is coming from SGA and (b) There are no locks in anyway when the query is read.

Thanks,
Quester

[grjohnson]

Interesting...

You need to calculate the follwoing things.

a) DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT (8 * 8) = 64 Blocks per read.
b) FIND OUT THE AVERAGE ROWS per block (e.g. 100 per block)

Therefore 64 * 100 = 6400 rows per batch...

It really depends on your terminal to, there could be a delay between processing and displaying on your terminal, you may even have to take into account network performance also.

[abhaysk]

Originally posted by quester
I created a table "SAMP" from Session #1 and INSERTED abt 400000 records.

I then did a SELECT * FROM SAMP; and the query started to fetch and display the rows. Now while this continued to fetch...

Can someone explain how can this be possible? Is the data coming from the DB Buffers OR is the data coming from Data Blocks of the Database (Unreleased space of the Table, though the Table is dropped) !?!

Quester

After the select statement, the rows feteched into data buffer are returned to client....and say u drop the table in other session...then server stops fetching data from data blocks to buffer & throws error....but the contents in the buffer will be getting outputted to client...since there might be n/w contraints in flow of data from server to client... u will feel that data is getting displayed even after drop command is issued...

after some time u get this error message....

ERROR:
ORA-08103: object no longer exists

Regards
Abhay.

[quester]

Request jmodic, Tarry Singh or Pando to share their thoughts. something tells me we r missing something important here. something tells me there is more to understand here than merely "assuming" that the data is coming from SGA.

when a table is dropped, the extents r not released immd. the data dict gets updated, but the data block headers do not get immd. erased unless the tablespace is defragmented. does this in anyway add some thinking to us for this topic?