Oracle NZ - Francisco Munoz Alvarez

By Francisco Munoz Alvarez

How to find Sessions Generating Lots of Redo

To find sessions generating lots of redo, you can use either of the following methods. Both methods examine the amount of undo generated. When a transaction generates undo, it will automatically generate redo as well.

The methods are:

1) Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values indicate a session generating lots of redo.

The query you can use is:

SQL> SELECT s.sid, s.serial#, s.username, s.program,

2 i.block_changes

3 FROM v$session s, v$sess_io i

4 WHERE s.sid = i.sid

5 ORDER BY 5 desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.

2) Query V$TRANSACTION. These view contains information about the amount of undo blocks and undo records accessed by the transaction (as found in the USED_UBLK and USED_UREC columns).

The query you can use is:

SQL> SELECT s.sid, s.serial#, s.username, s.program,

2 t.used_ublk, t.used_urec

3 FROM v$session s, v$transaction t

4 WHERE s.taddr = t.addr

5 ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by the session.

You use the first query when you need to check for programs generating lots of redo when these programs activate more than one transaction. The latter query can be used to find out which particular transactions are generating redo.

Useful Scripts

To see the redo generated since instance started:

col name format a30 heading ‘Statistic|Name’

col value heading ‘Statistic|Value’

start title80 “Redo Log Statistics”

spool rep_out\&db\red_stat

SELECT name, value

FROM v$sysstat

WHERE name like ‘%redo%’

order by name

/

spool off

pause Press enter to continue

ttitle off

The redo generated during my session since the session started:

select value redo_size

from v$mystat, v$statname

where v$mystat.STATISTIC# = v$statname.STATISTIC#

and name = ‘redo size’

/

The redo generated by current user sessions:

select v$session.sid, username, value redo_size

from v$sesstat, v$statname, v$session

where v$sesstat.STATISTIC# = v$statname.STATISTIC#

and v$session.sid = v$sesstat.sid

and name = ‘redo size’

and value > 0

and username is not null

order by value

/

Provide a current status for redo logs:

column first_change# format 999,999,999 heading Change#

column group# format 9,999 heading Grp#

column thread# format 999 heading Th#

column sequence# format 999,999 heading Seq#

column members format 999 heading Mem

column archived format a4 heading Arc?

column first_time format a25 heading First|Time

break on thread#

set pages 60 lines 132 feedback off

start title132 ‘Current Redo Log Status’

spool rep_out\&db\log_stat

select thread#, group#, sequence#,

bytes, members,archived,status,first_change#,

to_char(first_time,’dd-mon-yyyy hh24:mi’) first_time

from sys.v_$log

order by thread#, group#;

spool off

pause Press Enter to continue

set pages 22 lines 80 feedback on

clear breaks

clear columns

ttitle off

/

Provide redo log groups and log switch (archive generation) information:

set echo on

set linesize 150

set pagesize 500

column day format a16 heading ‘Dia’

column d_0 format a3 heading ‘00′

column d_1 format a3 heading ‘01′

column d_2 format a3 heading ‘02′

column d_3 format a3 heading ‘03′

column d_4 format a3 heading ‘04′

column d_5 format a3 heading ‘05′

column d_6 format a3 heading ‘06′

column d_7 format a3 heading ‘07′

column d_8 format a3 heading ‘08′

column d_9 format a3 heading ‘09′

column d_10 format a3 heading ‘10′

column d_11 format a3 heading ‘11′

column d_12 format a3 heading ‘12′

column d_13 format a3 heading ‘13′

column d_14 format a3 heading ‘14′

column d_15 format a3 heading ‘15′

column d_16 format a3 heading ‘16′

column d_17 format a3 heading ‘17′

column d_18 format a3 heading ‘18′

column d_19 format a3 heading ‘19′

column d_20 format a3 heading ‘20′

column d_21 format a3 heading ‘21′

column d_22 format a3 heading ‘22′

column d_23 format a3 heading ‘23′

column Total format 9999

column status format a8

column member format a40

column archived heading ‘Archived’ format a8

column bytes heading ‘Bytes|(MB)’ format 9999

Ttitle ‘Log Info’ skip 2

select l.group#,f.member,l.archived,l.bytes/1078576 bytes,l.status,f.type

from v$log l, v$logfile f

where l.group# = f.group#

/

Ttitle off

prompt =========================================================================================================================

Ttitle ‘Log Switch on hour basis’ skip 2

select to_char(FIRST_TIME,’DY, DD-MON-YYYY’) dia,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’00′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’00′,1,0))) d_0,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’01′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’01′,1,0))) d_1,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’02′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’02′,1,0))) d_2,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’03′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’03′,1,0))) d_3,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’04′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’04′,1,0))) d_4,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’05′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’05′,1,0))) d_5,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’06′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’06′,1,0))) d_6,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’07′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’07′,1,0))) d_7,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’08′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’08′,1,0))) d_5,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’09′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’09′,1,0))) d_9,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’10′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’10′,1,0))) d_10,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’11′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’11′,1,0))) d_11,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’12′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’12′,1,0))) d_12,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’13′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’13′,1,0))) d_13,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’14′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’14′,1,0))) d_14,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’15′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’15′,1,0))) d_15,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’16′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’16′,1,0))) d_16,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’17′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’17′,1,0))) d_17,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’18′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’18′,1,0))) d_18,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’19′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’19′,1,0))) d_19,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’20′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’20′,1,0))) d_20,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’21′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’21′,1,0))) d_21,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’22′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’22′,1,0))) d_22,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’23′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’23′,1,0))) d_23,

count(trunc(FIRST_TIME)) Total

from v$log_history

group by to_char(FIRST_TIME,’DY, DD-MON-YYYY’)

order by to_date(substr(to_char(FIRST_TIME,’DY, DD-MON-YYYY’),5,15) )

/

Ttitle off

How to check for LOGGING/NOLOGGING objects in the DB:

Two example methods of querying the database for this information:

select owner , table_name, index_name
from dba_indexes
where logging=’NO’;

select tablespace_name, logging
from dba_tablespaces

/

Kind Regards,

Francisco Munoz Alvarez

Let start this New Year with a new mentality or a new resolution if you prefer call this way, let stop being reactive and start being proactive. Being proactive will reduce your database administration costs and increase your level of efficiency, and best of all, save your hours of sleep when you are on call.

Why check the problems only when they are critical, or when is too late and the database is down, or the users are screaming?

Being proactive is the best approach to keep your DB healthy and to show your company, or your clients that you really care about them.

Many DBA’s expend most of their time being firefighters only, fixing problems and working on user’s requests all the time. They don’t do any proactive work; this mentality only will cause an overload of work to them, thousands of dollars of overtime, several hours without access to the data to the users,  poor performance to the applications, and what is worse of all, several unhappy users thinking that you doesn’t have the knowledge needed to take care of their data.

Let’s mention a small example, you have the archive log area alert set to fire when it is 95% full, and this happens in the middle of the night, some DBA’s will take seriously the alert and solve the problem quickly, others will wait until the next day to take care of it because they are tired, or sleeping, or they are in a place without internet access at the moment the alert arrived. Will be a lot easier if they set a proactive alert to be fire when 75% or 85%, or even better ,take a look in the general health status of the DB before leave their work shift, to try to detect and solve any possible problem before be a real problem and be awake in the middle of the night or during the weekend (Remember how important is your personal and family time). I’ll always recommend to DBA’s to run 2 checklists daily, one in the start of their shift and other before they leave their shift.

I know several DBA’s that complain all the time that they got so many calls when they are on call, but they don’t do anything to solve the root problem, they only expend their time to solve the symptoms.

Here you can find an Oracle checklist script that will help to make your life a little easier (This is not my complete script, but will be a good start for you). This script is a compilation of several normal checklists and you can setup them with your own requirement and thresholds and always remember to have a baseline to compare. This script will not only help you to detect future or current problems, but also will help you to detect possible tuning requirement.

Here is an example of the script first phase outcome:

– ———————————————————————– –
– Oracle Instance Information
– ———————————————————————– –
Cpu_Count                                    4   |      Host_Name             OLIVER
Instance_Name                      prod   |      Database_Status    ACTIVE
Status                                     OPEN   |      Startup_Time        10-01-2009 19:50
Version                            11.1.0.7.0   |      Instance_Role        PRIMARY_INSTANCE
Database Space (Mb)       36604    |     SGA (Mb)                 511
Nb. Datafiles                                 43    |    Nb. Tempfiles          1

Archive destination LOCATION=E:\oracle\oradata\prod\archive
Database log mode ARCHIVELOG
Background Dump Dest d:\oracle\diag\rdbms\prod\prod\trace
Spfile D:\ORACLE\PRODUCT\11.1\PROD\DATABASE\SPFILEPROD.ORA
Redo size (Kb) 102400

– ———————————————————————– –
– Instance CheckList –
– ———————————————————————– –
Instance Status                                  OK    |      Listener Status      OK
– ———————————————————————– –
– Performance Memory CheckList –
– ———————————————————————– –
Total Sessions < 700                           OK - 19
Active sessions number <15               OK - 9
Data Buffer Hit Ratio > 80                   OK - 97
L.Buffer Reload Pin Ratio > 99         OK - 99
Row Cache Miss Ratio < 0.015         NO - 1.351
Dict.Buffer Hit Ratio > 80                     OK - 99
Log Buffer Waits = 0                             NO - 110
Log Buffer Retries < 0.0010               OK - 0
Switch number (Daily Avg) < 5          OK - 1
Jobs Broken = 0                                     OK 0
Shared_Pool Failure = 0                    OK - 0
– ———————————————————————– –
– Storage CheckList                                            –
– ———————————————————————– –
Dba_Tablespaces Status                   OK   |       V$Log Status             OK
V$Datafile Status                                  OK   |       V$Tempfile Status   OK
V$Recover_File                                    OK   |       V$Recovery_Log      OK
Tablespace in Backup Mode = 0     OK - 0
Tablespace < 95%                               OK- 0
Objects Invalid = 0                                NO - 147
Indexes unusable = 0                          OK - 0
Trigger Disabled = 0                            NO- 5
Constraint Disabled = 0                       NO - 2
Objects close max extents = 0           OK - 0
Objects can not extent = 0                  NO - 552
User Objects on Systems = 0              NO - 26
FK Without Index = 0                           NO - 138
– ———————————————————————– –
– Datagard CheckList                                          –
– ———————————————————————– –
Datagard Errors = 0                               OK- 0
Datagard Gap = 0                                OK - 0
Archives not Aplied < 5                       OK - 2
– ———————————————————————- –
– Installed options :
– ———————————————————————- –
- Objects option
- Connection multiplexing option
- Connection pooling option
- Database queuing option
- Incremental backup and recovery option
- Instead-of triggers option
- Parallel load option
- Proxy authentication/authorization option
- Plan Stability option
- Coalesce Index option
- Transparent Application Failover option
- Sample Scan option
- Java option
- OLAP Window Functions option

You also have several tools available in the market that can help you to monitor and setup your DB alerts, and help you with the proactive monitoring like: Grid Control, Enterprise Manager, Insider (FourthElephant), Spotlight (Quest) or if you prefer, your own scripts. The idea is to use them always on a proactive way, never reactive.

Let’s change our mentality, let stop being a firefighter and start to be a real hero!

Cheers,

Francisco Munoz Alvarez