Server Administration :: How To Extract License / CSI In Oracle Database

11:53:51 SQL>select * from v$version
11:53:55 2 /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

All, I have an infuriating problem and I'm hoping for some advice. Well actually it is a number of problems but I'll use one example as a microcosm.I'm going to use as the example:

------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 912K(100)| |
| 1 | HASH JOIN | | 3412K| 940M| 826M| 912K (1)| 03:02:28 |
| 2 | HASH JOIN | | 3412K| 787M| 699M| 748K (1)| 02:29:39 |
| 3 | HASH JOIN | | 3610K| 657M| 557M| 560K (1)| 01:52:07 |
[code]...

Now then, this query runs perfectly and as expected (direct reads/DB scattered reads) until it hit step 13 in the plan (FTS of T6). At step 13 it basically enters into a (rather fatal) process of reading almost nothing but undo exclusively.

Now then, I wonder why this might be. Of course the natural reaction is to say "they data is changing, its read consistency (optional rookie insult)". Now, I found this hard to believe that the volume of data could be so extensively changed, keeping in mind its a good sized table and it's doing a full access.

I checked the DBA_HIST_SEG_STAT(DB_BLOCK_CHANGES_DELTA) view for the period and in the run up, i.e. the explain plan steps 2-12 took approx 20 minutes. In this window, less than 1% of the blocks in the table T6 were changed (approx 3-4k out of a 500k block table).Nearly every access of this table is reading undo, yet since this query started, 99% of the table is unchanged. But the query is behaving as if everything it reads has altered since it began.

To be clear, I understand the read consistency etc and expect it, what I DONT expect is the virtually the ENTIRE table scan operation routing via undo when I'm accessing the full table, yet hardly any blocks are altered after I start running.

Note this query/application seems to fetch the rows in blocks of 1k, its almost as if its re-executing for each fetch, but I wouldn't have expected that.

In summary:
>Query starts
>hits final table
>almost exclusive undo reads
>under 1% of block changes in read table since query began

Is there any other way anyone knows of that might cause a session to read into undo beyond a session changing data after the query executed?