I enabled an huge 70G table for inmemory population, I expected the inmemory population to take a while, but the population didn’t complete even after letting it run for a day. Why?
ASH data
Initial review of the server shows no issues, no resource starvation. This must be a problem with Oracle processes itself. I started digging further, and ASH data shows that in numerous samples the process was seen reading block using single block I/I calls. Also object_id matches with the table I was trying to populate.
select * from (
select start_time, end_time, sql_id,event, current_obj#, cnt_on_cpu + cnt_waiting tot_cnt,
rank () over ( order by (cnt_on_cpu + cnt_waiting) desc ) rnk
from (
select
min(start_time) start_time,
max(end_time) end_time,
sql_id,event,current_obj#,
sum(decode(session_state,'ON CPU',1,0)) cnt_on_cpu,
sum(decode(session_state,'WAITING',1,0)) cnt_waiting
from
( select
first_value(sample_time) over( order by sample_time ) start_time,
last_value(sample_time) over( order by sample_time
rows between unbounded preceding and unbounded following ) end_time,
sql_id,event, session_state, current_obj#
from
(select * from v$active_session_history ash where session_id= &&sid and session_serial#=&&serial_number)
)
group by sql_id, event, current_obj#
)
)
where rnk
/
START_TIME END_TIME SQL_ID EVENT CURRENT_OBJ# TOT_CNT RNK
------------------------- ------------------------- ------------- ------------------------------ ------------ ---------- ----------
18-AUG-14 08.42.03.702 AM 18-AUG-14 09.02.06.463 AM db file sequential read 168967 990 1
168967 156 2
direct path read 168967 50 3
bdwtqttka2w2y -1 3 4
bdwtqttka2w2y direct path read 168967 1 5
24uqc4aqrhdrs 168967 1 5
-1 1 5
Read the rest of this entry »
