Dears,
I have one situation which is tormenting me; it occurred this couple of months at least into two different running production databases. The last one happens today. Look below:
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
I’ve executed the following query againts gv$active_session_history at 8h20
select event, count(1)
from gv$active_session_history
where sample_time between to_date('15072015 07:30:00', 'ddmmyyyy hh24:mi:ss')
and to_date('15072015 09:30:00', 'ddmmyyyy hh24:mi:ss')
group by event
order by 2 desc;
Then re-executed the same query at 8h30
select event, count(1)
from gv$active_session_history
where sample_time between to_date('15072015 07:30:00', 'ddmmyyyy hh24:mi:ss')
and to_date('15072015 09:30:00', 'ddmmyyyy hh24:mi:ss')
group by event
order by 2 desc;
Then re-executed the same query at 9h30
select event, count(1)
from gv$active_session_history
where sample_time between to_date('15072015 07:30:00', 'ddmmyyyy hh24:mi:ss')
and to_date('15072015 09:30:00', 'ddmmyyyy hh24:mi:ss')
group by event
order by 2 desc;
And starting from 10h30 the same query begins returning data
select event, count(1)
from gv$active_session_history
where sample_time between to_date('15072015 07:30:00', 'ddmmyyyy hh24:mi:ss')
and to_date('15072015 09:30:00', 'ddmmyyyy hh24:mi:ss')
group by event
order by 2 desc;
EVENT COUNT(1)
-------------------------------- ----------
2209
control file parallel write 444
db file sequential read 249
log file parallel write 150
db file scattered read 148
Backup: MML create a backup piece 108
Backup: MML write backup piece 101
control file sequential read 64
db file parallel write 56
What can impeach MNON to write in v$active_session_history for more than an hour?
Thanks
Mohamed Houri