Tuesday, May 26, 2009

Extracting Oracle DDL Statements


select
dbms_metadata.get_ddl('TABLE','C_FEATURE','QA')
From dual;


parameters are
object type, object name, owner schema name 


Wednesday, May 20, 2009

Cache Buffers Chains

Find latches with high contention

select addr,gets,misses,sleeps
from v$latch_children
where name='cache buffers chains'
and misses > 100 order by 3 desc;

ADDR GETS MISSES SLEEPS
---------------- ---------- ---------- ----------
000000015BF383D8 38773656 8016 6
000000015B6E0968 11215621 4215 0
000000015B5A27B8 26410038 3834 0
000000015B58B5D0 14755159 2289 6



use the address to get the file id and block number


select file#,dbablk,state,class from x$bh where hladdr='000000015BF383D8';

FILE# DBABLK STATE CLASS
---------- ---------- ---------- ----------
1 78757 1 1
11 614483 1 4
9 249625 1 1
11 714956 1 1
3 33047 1 1


using the block number find out the object and the owner


select owner,segment_name
from dba_extents
where file_id=&p1
and &p2 between
block_id and block_id + blocks -1;

Enter value for p1: 11
Enter value for p2: 614483

OWNER SEGMENT_NAME
--------- ------------------
TRNSTG STATIC_CACHE_QUEUE


Investigate the sqls accessing the objects for hot blocks


Metalink (Doc ID: 163424.1) provides following query for identifying hot blocks


column segment_name format a35
select /*+ RULE */
e.owner ||'.'|| e.segment_name segment_name,
e.extent_id extent#,
x.dbablk - e.block_id + 1 block#,
x.tch,
l.child#
from
sys.v$latch_children l,
sys.x$bh x,
sys.dba_extents e
where
x.hladdr = &address and
e.file_id = x.file# and
x.hladdr = l.addr and
x.dbablk between e.block_id and e.block_id + e.blocks -1
order by x.tch desc ;


Metalink recommendations

Depending on the TCH column (The number of times the block is hit by a SQL
statement), you can identify a hotblock. The higher the value of the TCH column,
the more frequent the block is accessed by SQL statements.

In order to reduce contention for this object the following mechanisms can be put in place:

1) Examine the application to see if the execution of certain DML and SELECT statements
can be reorganized to eliminate contention on the object.

2) Decrease the buffer cache -although this may only help in a small amount of cases.

3) DBWR throughput may have a factor in this as well.
If using multiple DBWR's then increase the number of DBWR's.

4) Increase the PCTFREE for the table storage parameters via ALTER TABLE
or rebuild. This will result in less rows per block.

5) Consider implementing reverse key indexes
(if range scans aren't commonly used against the segment)