Re: So Many Redo

On Jan 25, 12:19 am, Emre <e..._at_emre.com> wrote:
> Hi All I saw some interesting things in v$log_history view as that
> THEREAD-SEQUNCE# FIRST_CHANGE FIRST_TIME SWITCH_CHANGE#
> 1 2860 1003068839 1/23/2007 4:52:38 PM 1003075595
> 1 2861 1003075595 1/23/2007 4:52:40 PM 1003082486
> 1 2862 1003082486 1/23/2007 4:52:48 PM 1003089304
> My Oracle has 3 redolog and size is 50 mb and usual log switch time is 1 hour
>
> I am wondering something about this output;
>
> 1- Output seems like system has fulled all redo log in 10 sec then if dbwn didnt
> write blocks those are in first redo then system has stoped and wait to write
> complete ?If it has happened how can I see this wait event about the past

See metalink Note:223117.1, among many other places (like the docs), where the wait events are described.

You must be doing some big import or sqlload. You may need to retune your redo to account for this. Depending on your version, you may want to have much larger redo and use timeouts or cron jobs to switch according to your SLA when they are not filling totally.

So, what was your version and OS again?

> 2- To see which sql statement has caused to this event I wrote a query ;
>
> select s.sql_text, a.sql_id
> from v$sqlarea s
> JOIN v$active_session_ history a ON (s.SQL_ID = a.SQL_ID)

Oh, you must be on some sort of 10g. See Note:243132.1

> where TO_CHAR(a.sample_ time, 'DD/MM/YYYY HH24:MI') = '23/01/2007 16:52'
>
> But there arent some sql_id entries those are in v$active_session_history in
> v$sqlare view.How can I see which sql statements in specified time ?

Maybe a statspack might help you, if these aren't very rare events. Or other tuning aids, if you are licensed for them Note:276103.1 .

jg

--
@home.com is bogus.
http://zdnet.com.au/news/hardware/soa/HP_accused_of_spying_on_Dell/0,130061702,339273237,00.htm