select">sys@OCN>select latch#,name,gets,misses,sleeps from v$latch where name like cache buffer%;
LATCH# NAME GETS MISSES SLEEPS
---------- ------------------------------ ---------- ---------- ----------
93 cache buffers lru chain 54360446 21025 238
98 cache buffers chains 6760354603 1680007 27085
99 cache buffer handles 554532 6 0

select">sys@OCN>select addr,LATCH#,CHILD#,gets,misses,sleeps from v$latch_children
2 where name = cache buffers chains and rownum < 21;
ADDR LATCH# CHILD# GETS MISSES SLEEPS
-------- ---------- ---------- ---------- ---------- ----------
91B23B74 98 1024 10365583 3957 33
91B23374 98 1023 5458174 964 25
91B22B74 98 1022 4855668 868 15
91B22374 98 1021 5767706 923 22
91B21B74 98 1020 5607116 934 31
91B21374 98 1019 9389325 1111 25
91B20B74 98 1018 5060207 994 31
91B20374 98 1017 18204581 1145 18
91B1FB74 98 1016 7157081 920 23

select">sys@OCN>select dbarfil,dbablk
from x$bh
where hladdr in
(select addr
from (select addr
from v$latch_children
order by sleeps desc)
where rownum < 11);
DBARFIL DBABLK
---------- ----------
4 6498
40 14915
15 65564
28 34909

select distinct a.owner,a.segment_name from
dba_extents a,
(select dbarfil,dbablk
from x$bh
where hladdr in
(select addr
from (select addr
from v$latch_children
order by sleeps desc)
where rownum < 11)) b
where a.RELATIVE_FNO = b.dbarfil
and a.BLOCK_ID <= b.dbablk and a.block_id + a.blocks > b.dbablk;

OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------------------ ------------------
ALIBABA BIZ_SEARCHER TABLE
ALIBABA CMNTY_USER_MESSAGE TABLE
ALIBABA CMNTY_VISITOR_INFO_PK INDEX

select object_name
from dba_objects
where data_object_id in
(select obj
from x$bh
where hladdr in
(select addr
from (select addr
from v$latch_children
order by sleeps desc)
where rownum < 11)) ;

OBJECT_NAME
------------------------------------
I_CCOL2
RESOURCE_PLAN$
DUAL
FGA_LOG$
AV_TRANSACTION

select distinct a.owner,a.segment_name,a.segment_type from
dba_extents a,
(select dbarfil,dbablk
from (select dbarfil,dbablk
from x$bh order by tch desc) where rownum < 11) b
where a.RELATIVE_FNO = b.dbarfil
and a.BLOCK_ID <= b.dbablk and a.block_id + a.blocks > b.dbablk;
OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------------------ ------------------
ALIBABA CMNTY_USER_MESSAGE TABLE
ALIBABA MEMBER_PK INDEX
ALIBABA OFFER_DRAFT_GMDFY_IND INDEX

select object_name
from dba_objects
where data_object_id in
(select obj
from (select obj
from x$bh order by tch desc) where rownum < 11) ;
OBJECT_NAME
---------------------------------------------------
DUAL
MEMBER_PK
SAMPLE_GROUP_PK
CMNTY_USER_MESSAGE_TD_BSM_IND
OFFER_DRAFT_MID_GMDFY_IND
OFFER_MID_GPOST_IND

select sql_text
from stats$sqltext a,
(select distinct a.owner,a.segment_name,a.segment_type from
dba_extents a,
(select dbarfil,dbablk
from (select dbarfil,dbablk
from x$bh order by tch desc) where rownum < 11) b
where a.RELATIVE_FNO = b.dbarfil
and a.BLOCK_ID <= b.dbablk and a.block_id + a.blocks > b.dbablk) b
where a.sql_text like %||b.segment_name||% and b.segment_type = TABLE
order by a.hash_value,a.address,a.piece;
SQL_TEXT
----------------------------------------------------------------
SELECT SEQ_SMS_TRANSACTION.nextval FROM DUAL
SELECT SEQ_BIZ_EXPRESS.nextval FROM DUAL
SELECT SEQ_SAMPLE.nextval FROM DUAL

select sql_text
from v$sqltext a,
(select distinct a.owner,a.segment_name,a.segment_type from
dba_extents a,
(select dbarfil,dbablk
from (select dbarfil,dbablk
from x$bh order by tch desc) where rownum < 11) b
where a.RELATIVE_FNO = b.dbarfil
and a.BLOCK_ID <= b.dbablk and a.block_id + a.blocks > b.dbablk) b
where a.sql_text like %||b.segment_name||% and b.segment_type = TABLE
order by a.hash_value,a.address,a.piece;
SQL_TEXT
----------------------------------------------------------------
SELECT NULL FROM DUAL FOR UPDATE NOWAIT
SELECT SEQ_SMS_TRANSACTION.nextval FROM DUAL
SELECT SEQ_BIZ_EXPRESS.nextval FROM DUAL
SELECT SEQ_IM_GROUP.nextval FROM DUAL
SELECT SEQ_SAMPLE.nextval FROM DUAL
=DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY=$ NLS_COMP=

select">sys@OCN>select * from v$waitstat;
CLASS COUNT TIME
------------------ ---------- ----------
data block 1726977 452542
sort block 0 0