Oracle Database Discussions

Hi all!
I have running a simple flashback version query on table and query completes after 2 or 3 hours and requires too many CR. How can I avoid this?
SGA = 384M (yes, it is test database)
PGA = 128M

query like this

SELECT VERSIONS_STARTSCN, VERSIONS_OPERATION, PK1, PK2, PK3, .., PKn, ROWID
FROM
TAB1 VERSIONS BETWEEN SCN :1 AND :2 WHERE VERSIONS_XID IS NOT NULL AND VERSIONS_XID=:3

It is known to me that between SCN :1 AND SCN :2 were changed about 150,000 rows on TAB1.
Number of PK (primary keys) is 7.

so for first 45 minutes I have gathered statistics and delta values for the session were:

buffer is not pinned count 5,722
calls to kcmgas 251
cleanout - number of ktugct calls 115,351
cleanouts and rollbacks - consistent read gets 54,598
cleanouts only - consistent read gets 85
commit txn count during cleanout 115,351
consistent changes 42,957
consistent gets 734,395,038
consistent gets - examination 734,388,980
consistent gets from cache 734,395,037
CR blocks created 5
data blocks consistent reads - undo records applied 734,230,512
free buffer inspected 4,645
free buffer requested 6,301
hot buffers moved to head of LRU 424
immediate (CR) block cleanout applications 54,683
no work - consistent read gets 5,977
physical read bytes 51,576,832
physical read IO requests 6,296
physical read total bytes 51,576,832
physical read total IO requests 6,296
physical reads 6,296
physical reads cache 6,296
rollbacks only - consistent read gets 108,025
session logical reads 734,395,097
session pga memory 720,896
session uga memory 720,104
session uga memory max 720,104
shared hash latch upgrades - no wait 251
transaction tables consistent read rollbacks 5
transaction tables consistent reads - undo records applied 42,957
user I/O wait time 2,240
workarea memory allocated 608

CPU usage was high during query that shows me TKPROF

call count cpu elapsed disk query current rows
--
Parse 2 0.01 0.05 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 161 *14534.71* 15092.33 175067 *3371218093* 0 159975

total 165 *14534.73* 15092.40 175067 *3371218093* 0 159975

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited

db file sequential read 174767 0.34 503.43
latch: cache buffers chains 23 0.00 0.00
direct path write temp 20 0.00 0.00
direct path read temp 20 0.03 0.16

I noticed that in v$session during the query common value of ROW_WAIT_OBJ# is object of primary key index for the table TAB1.
What is the main reason of high CR?

Edited by: user597485 on Jun 16, 2009 1:48 AM

Edited by: user597485 on Jun 16, 2009 1:51 AM