Hi, I am using version- 11.2.0.4 of oracle.

I have a query running and its getting slow down during specific time period of the day(10PM to 9AM) which i can also verify from the data present in DBA_HIST_SQLSTAT. The number of rows generated from the query, decreased from ~25million to ~15k during those snap duration(we have ~1hrs snap duration). Now going through the details in DBA_HIST_ACTIVE_SESS_HISTORY, i see the CURRENt_OBj# continuously showing as '0' during the slow period, while during good throughput time it was populated with valid value in CURRENt_OBj#. So i believe, it was doing UNDO read during the slow period, causing the slowness. Then from the real time monitor plan i am seein ~65% of the time has spent in the "index range scan" of "IDX1" and ~35% of the time has been spent on "TABLE ACCESS BY LOCAL INDEX ROWID" of same table, so i understand its doing mostly the UNDO read for the INDEX BLOCKS. and we have this table mostly INSERT only, we never/rarely perform DELETE/UPDATE on this. This query runs ~5hr+, but during UNDO read it sometime runs for 10+ hours and complete and sometimes fail with "snapshot too old".

So i tested the scenario as below , which will replicate our case, during SELECT, we have JAVA batch which pushes records into this table in chunks(~10000) and commits them, it does conventional path insert. Actually this batch job runs 24/7 and read from the file and push record into this table. Daily we have ~150million records getting pushed into this table.

What i observed, if during the SELECT running , there is INSERTS running around(without commit or gets committed afterwards), may be before/after the SELECT query starts, but in any cases it reads huge amount of UNDO blocks(possibly INDEX blocks).

1)So is my understanding is correct that its the INDEX blocks which generate such amount of UNDO and any SELECT during that period doing index scan, will get affected by this?
2)Is the only solution is to avoid the long running the SELECT during the INSERT period? or is ther any other solution(considering the SELECT query is tuned to its best)?


------------------------------ test case--------------------------

create table t1_undotest as select * from all_objects where 1=0;

-- initially filled the table with some record

Insert into t1_undotest select * from all_objects; - (it inserts 55897 records in one shot)
Insert into t1_undotest select * from all_objects;
Insert into t1_undotest select * from all_objects;
Insert into t1_undotest select * from all_objects;
Insert into t1_undotest select * from all_objects;
Insert into t1_undotest select * from all_objects;
Insert into t1_undotest select * from all_objects;
commit;


SCENARIO-1-
--------------
Now in session-1 , i executed below statement
SELECT * from t1_undotest; (I executed with SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS, as i didnt need to results to be printed aall over)

While above query in session-1 was getting executed, I inserted record into same table from session-2 using below

Insert into t1_undotest select * from all_objects;
Insert into t1_undotest select * from all_objects;
Insert into t1_undotest select * from all_objects;
Insert into t1_undotest select * from all_objects;
Insert into t1_undotest select * from all_objects;
Insert into t1_undotest select * from all_objects;
commit;

Then i checked the UNDO records visited by the SELECT statement by executing it from SESSION-1(After the SELECT gets complted)

select 'undo: '||value from v$mystat natural join v$statname where name = 'data blocks consistent reads - undo records applied';

I see, its giving around ~8bytes UNDO.


SCENARIO-2-
-------------
Then i have tested another case,

In session-1 execute below statement (Do not commit)
Insert into t1_undotest select * from all_objects;
Insert into t1_undotest select * from all_objects;

In Session-2 executed below statement
SELECT * from t1_undotest; (I executed with SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS, as i didnt need to results to be printed aall over)
Then i checked the UNDO records visited by the SELECT statement by executing it from SESSION-1(After the SELECT gets complted)
select 'undo: '||value from v$mystat natural join v$statname where name = 'data blocks consistent reads - undo records applied';

I see, its giving around ~4900 bytes UNDO.

SCENARIO-3
-----------
CREATE INDEX on OBJECT_TYPE column:
create index idx1_test on t1_undotest(object_name);
Now repeat SCENARIO-1, this time just to let the query traverse through INDEX, i have executed below in session-1

select /*+index(t1_undotest IDX1_TEST)*/ * from t1_undotest;

What i observed , its visiting ~218904 bytes of UNDO.


SCNERAIO-4
------------
I repeated SCENARIO-2, with the forced index hint, i observed, its visiting ~79154 bytes UNDO.

SCENARIO-5
-------------
In session-1 execute below statement ( commit)

Insert into t1_undotest select * from all_objects;
Insert into t1_undotest select * from all_objects;
Commit;

In Session-2 executed below statement
select /*+index(t1_undotest IDX1_TEST)*/ * from t1_undotest; (I executed with SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS, as i didnt need to results to be printed aall over)

Then i checked the UNDO records visited by the SELECT statement by executing it from SESSION-1(After the SELECT gets complted)
select 'undo: '||value from v$mystat natural join v$statname where name = 'data blocks consistent reads - undo records applied';

I see, its giving around ~0 bytes UNDO.