Cernatis Inc - Fix high "latch: cache buffers chains" waits

Cernatis Inc - Fix high "latch: cache buffers chains" waits

relevant links
http://blog.tanelpoder.com/2011/06/05/cache-buffers-chains-latch-contention-using-latchprofx-sql-example/

http://tech.e2sn.com/oracle/troubleshooting/latch-contention-troubleshooting

tnsping response time

Should I use TNSPING to test my oracle net performance ?

 

Typically when TNSPING times go up in Dedicated Server configuration, it is because the system is hitting a high level of load and the listener is having to wait for a process to fork and execute the oracle dedicated server.The TNSPING program sends a packet to the listener,which goes into it’s listening queue.If there were also connect requests in the queue, then the listener will handle each request (including the tns “ping”) in the order they were received per second. If those connect requests take time, then it will take time to process the ping.TNSPING should never be used to test network performance. TNSPING’s only function is to send a connect Packet (NSPTCN) to the listener, Listener replies with a refuse Packet (NSPTRF) and a round trip time is computed. A slow TNSPING time could be anything from poor DNS resolution to a slow network to a busy listener to a busy server.

If connections are going fine ,We should not be worried about the tnsping response time.

log shipping

SQL Server 2008 R2 : LOG SHIPPING – A VERY LARGE DATABASE « SQL DBA Support . COM – Knowledge Centre for SQL DBAs

10g and 11g Statistics Tables Created by the DBMS_STATS Package

REM in 10g database

SYS> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for HPUX: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

EXEC DBMS_STATS.CREATE_STAT_TABLE(ownname=>'PERFSTAT',stattab => 'T1_STATTAB');

SQL> DESC DBO.T1_STATTAB
 Name                                          Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATID                                             VARCHAR2(30)
 TYPE                                               CHAR(1)
 VERSION                                            NUMBER
 FLAGS                                              NUMBER
 C1                                                 VARCHAR2(30)
 C2                                                 VARCHAR2(30)
 C3                                                 VARCHAR2(30)
 C4                                                 VARCHAR2(30)
 C5                                                 VARCHAR2(30)
 N1                                                 NUMBER
 N2                                                 NUMBER
 N3                                                 NUMBER
 N4                                                 NUMBER
 N5                                                 NUMBER
 N6                                                 NUMBER
 N7                                                 NUMBER
 N8                                                 NUMBER
 N9                                                 NUMBER
 N10                                                NUMBER
 N11                                                NUMBER
 N12                                                NUMBER
 D1                                                 DATE
 R1                                                 RAW(32)
 R2                                                 RAW(32)
 CH1                                                VARCHAR2(1000)


REM in 11g

SYS> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for HPUX: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> DESC PERFSTAT.T1_STATTAB
 Name                                    Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATID                                             VARCHAR2(30)
 TYPE                                               CHAR(1)
 VERSION                                            NUMBER
 FLAGS                                              NUMBER
 C1                                                 VARCHAR2(30)
 C2                                                 VARCHAR2(30)
 C3                                                 VARCHAR2(30)
 C4                                                 VARCHAR2(30)
 C5                                                 VARCHAR2(30)
 N1                                                 NUMBER
 N2                                                 NUMBER
 N3                                                 NUMBER
 N4                                                 NUMBER
 N5                                                 NUMBER
 N6                                                 NUMBER
 N7                                                 NUMBER
 N8                                                 NUMBER
 N9                                                 NUMBER
 N10                                                NUMBER
 N11                                                NUMBER
 N12                                                NUMBER
 D1                                                 DATE
 R1                                                 RAW(32)
 R2                                                 RAW(32)
 CH1                                                VARCHAR2(1000)
 CL1                                                CLOB


IN Step 33 of manual upgrade checklist to 11gR2.

Upgrade Statistics Tables Created by the DBMS_STATS Package
If you created statistics tables using the DBMS_STATS.CREATE_STAT_TABLE procedure, then upgrade these tables by executing the
following procedure:

EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('PERFSTAT','T1_STATTAB');

about "db file parallel read/write"

'db file parallel read' occurs during recovery. The datablocks that need to be changed are read from various datafiles and are placed in non-contiguous buffer blocks. The server process waits till all the blocks are read in to the buffer.
interesting chart here.
http://oracledba-vinod.blogspot.com/2009/08/db-file-parallel-readwrite.html

shrinking datafile

TIP #21: Shrink datafiles

Oracle Datafiles can be shrinked if chunk of free space exists at the end of datafiles.
This URL has some useful query for shrinking :
http://www.oracle.com/technology/oramag/oracle/04-sep/o54asktom.html

--- to shrink datafiles:

select 'alter database datafile ''' file_name ''' resize ' ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) 'm;' cmd from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) and ceil(blocks*&&blksize/1024/1024)- ceil((nvl(hwm,1)* &&blksize)/1024/1024 ) > 0;

$ cat resize_advisor.sql
set pages 1000

SELECT a.file_id,
a.file_name,
a.filesize,
b.freesize,
(a.filesize - b.freesize) usedsize,
c.hwmsize,
c.hwmsize - (a.filesize - b.freesize) unsedsize_belowhwm,
a.filesize - c.hwmsize-1 canshrinksize
FROM (SELECT file_id, file_name, round(bytes / 1024 / 1024) filesize FROM dba_data_files) a,
(SELECT file_id, round(SUM(dfs.bytes) / 1024 / 1024) freesize FROM dba_free_space dfs GROUP BY file_id) b,
--(SELECT file_id, round(MAX(block_id) * 8 / 1024) HWMsize FROM dba_extents GROUP BY file_id) c
(SELECT file_id, round(MAX(block_id + blocks) * (select value/1024 from v$parameter where name ='db_block_size') / 1024) HWMsize FROM dba_extents GROUP BY file_id) c
WHERE a.file_id = b.file_id
AND a.file_id = c.file_id
ORDER BY unsedsize_belowhwm DESC;

-- resize advisor for one tablespace
cat resize_advisor_tbs.sql
set pages 1000

SELECT a.file_id,
a.file_name,
a.filesize,
b.freesize,
(a.filesize - b.freesize) usedsize,
c.hwmsize,
c.hwmsize - (a.filesize - b.freesize) unsedsize_belowhwm,
a.filesize - c.hwmsize - 1 canshrinksize
FROM (SELECT file_id, file_name, round(bytes / 1024 / 1024) filesize FROM dba_data_files where tablespace_name='&tbs_name') a,
(SELECT file_id, round(SUM(dfs.bytes) / 1024 / 1024) freesize FROM dba_free_space dfs GROUP BY file_id) b,
(SELECT file_id, round(MAX(block_id + blocks) * (select value/1024 from v$parameter where name ='db_block_size') / 1024) HWMsize FROM dba_extents GROUP BY file_id) c
WHERE a.file_id = b.file_id
AND a.file_id = c.file_id
ORDER BY unsedsize_belowhwm DESC;



--- To find which objects have extents at the end of datafile.
Relocating these objects makes shrinking of relevant datafile possible.

select *
from (
select owner, segment_name,
segment_type, block_id
from dba_extents
where file_id =
( select file_id
from dba_data_files
where file_name = &FILE )
order by block_id desc
)
where rownum <= 5;

Partition Pruning Links

Partition Pruning

Partition Pruning and Joins [ID 179518.1]

Ask Tom "partition pruning"