Datafile Dump

SQL> select header_file, header_block from dba_segments where segment_name = 'ABC_I';

 

HEADER_FILE HEADER_BLOCK

----------- ------------

          5       289161

 

SQL> alter system dump datafile 5 block 289162;

 

System altered.







row#0[8019] flag: ------, lock: 0, len=17

col 0; len 3; (3):  43 42 41

col 1; len 3; (3):  47 46 45

col 2; len 6; (6):  01 44 69 02 00 00

Row Lock Contention

Tuning idea:
------------------

1. I'm thinking to reorganize hot tables as well as their indexes, but by instinct it seems to not give so much value to avoid the huge row lock wait time.

2. I'm also seeing if I can reduce the number of rows per block, by increasing PCTFREE and diminishing PCTUSED, so the contention will spread over many blocks instead of one heavy block.

Question
As SQL stment related to those locked tables are select ... for update, how could I tune this kind of stment?
Does someone have other idea to come up with this row lock contention?

Ans
Increase the initrans value of that object appearing in the select statement.


------------------------------------------------------------------------------------------------------

But I have first to know how many initrans are allocated in the targetted table.
To do that, I need to do the following:

1. record one block number

SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid),COUNT(*) NUMBER_ROWS_PER_BLOCK FROM  SIMANG_D.INWARD GROUP BY DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) Order by COUNT(*) desc
/

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) NUMBER_ROWS_PER_BLOCK

--------------------------------------------------------------------------------
---------------------
226534 5
226530 5
226504 5
226533 5
226538 5
226535 5
226525 5
226526 5
226532 5
228092 4

2. Dump the contents of a block through the following command

ALTER SYSTEM DUMP DATAFILE 4 BLOCK 226525;

3. see the result

Block header dump: 0x010374dd
Object id on Block? Y
seg/obj: 0xd863 csc: 0x00.cafe8 itc: 169 flg: E typ: 1 – DATA (max itl=169) brn: 1 bdba: 0x10374d1 ver: 0x01 opc: 0
inc: 0 exflg: 0

from this example, the initrans value is 169.

So, I need first to have the data locally then, check the initrans value before change it.
furthermore as I know, the new initrans won't affect current block, but new allocated, so I have to Rebuild the table by changing the initrans value.

Question.
What is the overhead when dumping a contents of a block from the production database as I specified above?

Detect Index Leaf Block Contention

Index block contention can be tricky to diagnose and repair, especially since Oracle does not have many direct tuning knobs for tuning for contention.

The wait “enq: TX – index contention” indicates a wait on an index, and in a RAC environment you might also see “gc” (global cache) waits on indexes:

gc buffer busy waits on Index Branch Blocks
gc buffer busy waits on Index Leaf Blocks
gc current block busy on Remote Undo Headers
gc current split
gcs ast xid
gcs refuse xid


There are many ways to Fixing Oracle index contention (RAC / NON-RAC ENV.)

There are three techniques that are used to relieve this index contention issue:

       1. Reverse key indexes
       2. Sequences with the cache and noorder options
       3. Using hash partitioned global indexes
       4. Adjusting the index block size


select sid, sql_text
from  v$session s, v$sql q
where    sid in (select sid from v$session where state in ('WAITING')
   and   wait_class != 'Idle'
   and   event='enq: TX - index contention'
   and (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id));

WAIT EVENTS

Detection / Contention and Solution:

DB File Scattered Read

DB File Sequential Read

Read By Other Session / Buffer busy wait / Segment buffer busy wait / Block Contention

Free Buffer Wait

Log file sync

Log file parallel write

Log File Switch

Log Buffer Space

Direct Path Writes

Direct Path Reads

Shared pool latch

Library cache latch

Latch Free Waits

Cache Buffer LRU Chain Latch

Cache Buffer Chain Latch

Enqueue Wait

Row cache objects latch

Idle Event

RDBMS IPC Message

Row Lock Contention

Index Leaf Block Contention


RAC : Wait Events

current read block - locally instance read block
cr block - consistent read block

• Block Oriented Wait  

                  gc current block 2-way

                  gc current block 3-way

                  gc cr block 2-way

                  gc cr block 3-way

• Message Oriented Wait 

                  gc current grant 2-way

                  gc current grant 3-way

                  gc cr grant 2-way

                  gc cr grant 3-way (Normally this wait event is not possible, but "_cr_grant_local_role" => turn 3-way CR grants off, make it automatic, or turn it on) 

• Contention Oriented Wait 

                  gc current block busy (cluster cache contention)

                  gc cr block busy

                  gc current buffer busy (local cache contention)

                  gc cr buffer busy (Remote cache contention)

• Load Oriented Wait  

                  gc current block congested

                  gc cr block congested

                  gc current grant congested

                  gc cr grant congested

OTHERS:

gc current block lost : Lost blocks due to Interconnect or CPU. Indicates interconnect issues and contention.

gc cr block lost : Lost blocks due to Interconnect or CPU. Indicates interconnect issues and contention.

gc current multi block request : Full table or index scans.

gc cr multi block request : Full table or index scans.

gc cr request : The time it takes to retrieve the data from the remote cache. Oracle may not pick
                        private interconnect and instead route traffic over slower public network.
                        RAC event similar to buffer busy waits, tune SQL to request less data, tune network
                        latency between RAC nodes, localize data access.

gc current/cr failure/retry : A block is requested and a failure status received or some other
                                            exceptional event has occured.

Reference :

ID 1911398.1 - LMS & CPU issue.

HUGE ARCHIVE LOG GENERATION

Checks Points. In Case face huge archive log generation type of issues.

1. Check Jobs, schedules.
2. Check DB activity like SQL,TABLE,PROCEDURE, TRIGGER.
3. Session wise SQL redo generates.
4. Redo log group full / small redo log file size.
5. DB link distributed work.
6. Session lock.
7. Dead locks.
8. Generate AWR/ASH/ADDM report (see section Top 5 Wait Event,Physical Segemnt/Logical).

Note:- Non Impact (SMON redo generation, fregmentation)



***********************************************************************************


## Archiving generation report:
Per day the volume in MBytes of archived logs generated, deleted and of those that haven't yet been deleted by RMAN

SELECT SUM_ARCH.DAY,
         SUM_ARCH.GENERATED_MB,
         SUM_ARCH_DEL.DELETED_MB,
         SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
    FROM (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
                      GENERATED_MB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
         (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
                      DELETED_MB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
   WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');

***********************************************************************************

## Date & Time wise log generation.

set pages 1000
select to_char(COMPLETION_TIME,'DD-MM-YYYY hh24:mm') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by to_char(COMPLETION_TIME,'DD-MM-YYYY hh24:mm'),thread# order by 1 ;

Note: See the output Date & time when archive log frequently generates. After that create AWR/ASH report and see the TOP wait events.

***********************************************************************************

##  Hour wise archive generation:

SELECT to_date(first_time) DAY,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23", count(*) TOT
from
v$log_history
where to_date(first_time) > sysdate -8
GROUP by
to_char(first_time,'YYYY-MON-DD'), to_date(first_time)
order by to_date(first_time)
/

***********************************************************************************

##  Archive generation number with volume:  day/hour wise

select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576/1024) GB,count(*)  Archives_Generated from v$archived_log group by trunc(COMPLETION_TIME,'DD'), thread# order by 1;

select to_char(COMPLETION_TIME,'DD-MON-YY HH24') Hour, round(sum(BLOCKS*BLOCK_SIZE)/1048576/1024) gb, count(*)  Archives from v$archived_log group by to_char(COMPLETION_TIME,'DD-MON-YY HH24') order by 1 ;

***********************************************************************************

##  Currently session wise redo generation:

select b.inst_id,
       lpad((b.SID || ',' || lpad(b.serial#,5)),11) sid_serial,
       b.username,
       machine,
       b.osuser,
       b.status,
       a.redo_mb
from (select n.inst_id, sid,
             round(value/1024/1024) redo_mb
        from gv$statname n, gv$sesstat s
        where n.inst_id=s.inst_id
              and n.name = 'redo size'
              and s.statistic# = n.statistic#
        order by value desc
     ) a,
     gv$session b
where b.inst_id=a.inst_id
  and a.sid = b.sid
and   rownum <= 30
;

***********************************************************************************

##   Which segments are generating redo logs:

SELECT to_char(begin_interval_time,'YY-MM-DD HH24') snap_time,
        dhso.object_name,
        sum(db_block_changes_delta) BLOCK_CHANGED
  FROM dba_hist_seg_stat dhss,
       dba_hist_seg_stat_obj dhso,
       dba_hist_snapshot dhs
  WHERE dhs.snap_id = dhss.snap_id
    AND dhs.instance_number = dhss.instance_number
    AND dhss.obj# = dhso.obj#
    AND dhss.dataobj# = dhso.dataobj#
    AND begin_interval_time BETWEEN to_date('15-10-31 08:00','YY-MM-DD HH24:MI')
                                AND to_date('15-10-31 10:00','YY-MM-DD HH24:MI')
  GROUP BY to_char(begin_interval_time,'YY-MM-DD HH24'),
           dhso.object_name
  HAVING sum(db_block_changes_delta) > 0
ORDER BY sum(db_block_changes_delta) desc ;

***********************************************************************************

##  What sql was causing the redo log generation:

SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24') WHEN,
       dbms_lob.substr(sql_text,4000,1) SQL,
       dhss.instance_number INST_ID,
       dhss.sql_id,
       executions_delta exec_delta,
       rows_processed_delta rows_proc_delta
  FROM dba_hist_sqlstat dhss,
       dba_hist_snapshot dhs,
       dba_hist_sqltext dhst
  WHERE upper(dhst.sql_text) LIKE '%USR_RACUNI_MV%'
    AND ltrim(upper(dhst.sql_text)) NOT LIKE 'SELECT%'
    AND dhss.snap_id=dhs.snap_id
    AND dhss.instance_number=dhs.instance_number
    AND dhss.sql_id=dhst.sql_id
    AND begin_interval_time BETWEEN to_date('15-10-31 07:00','YY-MM-DD HH24:MI')
                                AND to_date('15-10-31 16:00','YY-MM-DD HH24:MI');


***********************************************************************************

## Session wise programe redo entris genrate


col username for a15
col program for a20
col name for a20

select * from
(select /*+ first_rows */ a.username,a.sid,a.program,b.name,c.value
from v$session a,v$statname b,v$sesstat c
where a.sid=c.sid and b.statistic#=c.statistic# and b.name='redo entries' order by  c.value desc)
where rownum < 11
;


***********************************************************************************

## For RAC Env.
Author: Riyaj Shamsudeen

spool redosize.log
col begin_interval_time format a30
set lines 160 pages 1000
col end_interval_time format a30
set colsep '|'
alter session set nls_date_format='DD-MON-YYYY';
with redo_sz as (
SELECT  sysst.snap_id, sysst.instance_number, begin_interval_time ,end_interval_time ,  startup_time,
VALUE - lag (VALUE) OVER ( PARTITION BY  startup_time, sysst.instance_number
                ORDER BY begin_interval_time, startup_time, sysst.instance_number) stat_value,
EXTRACT (DAY    FROM (end_interval_time-begin_interval_time))*24*60*60+
            EXTRACT (HOUR   FROM (end_interval_time-begin_interval_time))*60*60+
            EXTRACT (MINUTE FROM (end_interval_time-begin_interval_time))*60+
            EXTRACT (SECOND FROM (end_interval_time-begin_interval_time)) DELTA
  FROM sys.wrh$_sysstat sysst , DBA_HIST_SNAPSHOT snaps
WHERE (sysst.dbid, sysst.stat_id) IN ( SELECT dbid, stat_id FROM sys.wrh$_stat_name WHERE  stat_name='redo size' )
AND snaps.snap_id = sysst.snap_id
AND snaps.dbid =sysst.dbid
AND sysst.instance_number=snaps.instance_number
and begin_interval_time > sysdate-90
)
select instance_number,
  to_date(to_char(begin_interval_time,'DD-MON-YYYY'),'DD-MON-YYYY') dt
, sum(stat_value) redo1
from redo_sz
group by  instance_number,
  to_date(to_char(begin_interval_time,'DD-MON-YYYY'),'DD-MON-YYYY')
order by instance_number, 2
/
spool off;