Excessive transaction tables consistent reads – undo records applied in 11.2.0.3 – Part II

To reproduce the problem, flashback query was used.

Normal case:


select /*+full(a)*/ count(1) from iftp_dw.ar as of timestamp to_date('2012.10.05 01:00', 'yyyy.mm.dd hh24:mi:ss') a;


COUNT(1)
----------
2014667

Elapsed: 00:00:10.54

Execution Plan
----------------------------------------------------------
Plan hash value: 1818353562


-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48151 (1)| 00:09:38 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| AR | 1993K| 48151 (1)| 00:09:38 |
-------------------------------------------------------------------


Statistics
----------------------------------------------------------
3 recursive calls
0 db block gets
793951 consistent gets
87183 physical reads
1116 redo size
529 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Bad case: after 3hours still running:
select /*+full(a)*/ count(1) from iftp_dw.ar as of timestamp to_date('2012 .10.05 00:00', 'yyyy.mm.dd hh24:mi:ss') a;

V$SQL statistics:

disk reads: 20365019

buffer gets: 21754220

What happened arround 00:00 A.M (AWR stats):

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s