Re: LGWR switching statistic
- From: Vishal Gupta <vishal@xxxxxxxxxxxxxxx>
- To: <John.Hallas@xxxxxxxxxxxxxxxxxx>, "dmarc-noreply@xxxxxxxxxxxxx" <dmarc-noreply@xxxxxxxxxxxxx>, "Amir.Hameed@xxxxxxxxx" <Amir.Hameed@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 02 Oct 2014 01:02:33 +0100
I have been using following for a very long time.
For Daily/Hourly log switchBy Count
http://www.vishalgupta.com/sqlscripts/logswitchfreq.sql
For Daily/Hourly log switch By Size
http://www.vishalgupta.com/sqlscripts/logswitchsize.sql
Regards,
Vishal Gupta
From: John Hallas <John.Hallas@xxxxxxxxxxxxxxxxxx>
Reply-To: <John.Hallas@xxxxxxxxxxxxxxxxxx>
Date: Monday, 29 September 2014 07:49
To: "dmarc-noreply@xxxxxxxxxxxxx" <dmarc-noreply@xxxxxxxxxxxxx>,
"Amir.Hameed@xxxxxxxxx" <Amir.Hameed@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx"
<oracle-l@xxxxxxxxxxxxx>
Subject: RE: LGWR switching statistic
> I have had this since 8i produces a nice display
>
>
> rem
> rem success determined by maxloghistory setting
> rem
> set pages 34
> set lines 240
>
> select to_date(substr(first_time,1,9),'dd-mm-yy') day,
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'00',1,0)),'999') "00",
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'01',1,0)),'999') "01",
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'02',1,0)),'999') "02",
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'03',1,0)),'999') "03",
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'04',1,0)),'999') "04",
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'05',1,0)),'999') "05",
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'06',1,0)),'999') "06",
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'07',1,0)),'999') "07",
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'08',1,0)),'999') "08",
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'09',1,0)),'999') "09",
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'10',1,0)),'999') "10",
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'11',1,0)),'999') "11",
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'12',1,0)),'999') "12",
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'13',1,0)),'999') "13",
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'14',1,0)),'999') "14",
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'15',1,0)),'999') "15",
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'16',1,0)),'999') "16",
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'17',1,0)),'999') "17",
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'18',1,0)),'999') "18",
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'19',1,0)),'999') "19",
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'20',1,0)),'999') "20",
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'21',1,0)),'999') "21",
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'22',1,0)),'999') "22",
> to_char(sum(decode(substr(to_char(first_time,'DD/MM/YY
> HH24-MI-SS'),10,2),'23',1,0)),'999') "23"
> from sys.v_$log_history where trunc(first_time) > sysdate -31
> group by to_date(substr(first_time,1,9),'dd-mm-yy')
> order by to_date(substr(first_time,1,9),'dd-mm-yy')
> /
>
>
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
> Behalf Of David Fitzjarrell
> Sent: 25 September 2014 22:14
> To: Amir.Hameed@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
> Subject: Re: LGWR switching statistic
>
>
> Not in 11.2; since I haven't a 12c database to check I can't say if that's
> been added. It's a simple query to write, really:
>
>
>
> select trunc(first_time,'DD') day, count(sequence#)
> FROM sys.V_$LOG_HISTORY
> WHERE TRUNC(first_time) >= TRUNC(SYSDATE)-&num_days+1
> GROUP BY TRUNC(first_time,'DD')
> order by 1;
>
>
>
> Gives you counts, by day. You can modify that to give hourly counts if you
> want them.
>
>
>
> David Fitzjarrell
>
> Principal author, "Oracle Exadata Survival Guide"
>
>
>
> On Thursday, September 25, 2014 2:25 PM, "Hameed, Amir"
> <Amir.Hameed@xxxxxxxxx> wrote:
>
>
> Hi,
>
> Is there a database statistic available that can tell how many times LGWR
> switched redo log files?
>
>
>
> Thanks,
>
> Amir
>
>
> ______________________________________________________________________
> Wm Morrison Supermarkets Plc is registered in England with number 358949. The
> registered office of the company is situated at Gain Lane, Bradford, West
> Yorkshire BD3 7DL. This email and any attachments are intended for the
> addressee(s) only and may be confidential.
>
> If you are not the intended recipient, please inform the sender by replying to
> the email that you have received in error and then destroy the email.
> If you are not the intended recipient, you must not use, disclose, copy or
> rely on the email or its attachments in any way.
>
> This email does not constitute a contract in writing for the purposes of the
> Law of Property (Miscellaneous Provisions) Act 1989.
>
> Our Standard Terms and Conditions of Purchase, as may be amended from time to
> time, apply to any contract that we enter into. The current version of our
> Standard Terms and Conditions of Purchase is available at:
> http://www.morrisons.co.uk/gscop
>
> Although we have taken steps to ensure the email and its attachments are
> virus-free, we cannot guarantee this or accept any responsibility,
> and it is the responsibility of recipients to carry out their own virus
> checks.
> ______________________________________________________________________
Other related posts:
