Re: Performance-Question

In article <DFons9.L2_at_merlin.unterland.de>

           harry_at_merlin.unterland.de "Harald Becker" writes:  

: if we loaded the data using SQL-loader with "direct path", our
: response-time for a query like
:
: SELECT COUNT(*) FROM TABLE;
:
: is about 5 Minutes. (Yes, I know, oracle does a full table-scan)
:
: If we are loading the data with a program, that does an insert
: for each row of the data, we have response-times for that query
: of 2 hours and above.
:
: The query was executed immediately after the load had finished.
:

Here is a possibility:

When you do a direct load, all the blocks added to the table are 'clean'. i.e. the 'interested transaction' table on each block is empty.

When your program inserts the row, every block that has an insert has an interested transaction entry added to it for the transaction id of your insert program.

When you run select count(*), Oracle discovers that every block has a transaction stamp, checks that the transaction that generated it is over, tidies up the block (as necessary) and removes the stamp.

Since you have a lot of blocks, you then find that the resulting changed block gets written back to disc: this could be the main cause of the extra time.

Suggestion:

    When doing the select count(*), run:

        select * from v$filestat;
   before and after the query.

I suspect that with the direct load, you would just see stats showing db_file_multi_block reads on the table's tablespace. With the program load you would also see a lot of single block writes on the table's tablespace

-- 
Jonathan Lewis