SQL> select event,count(*) from v$session group by event order by 2;
EVENT COUNT(*)
---------------------------------------------------------------- ----------
Streams AQ: qmn slave idle wait 1
Streams AQ: qmn coordinator idle wait 1
Streams AQ: waiting for messages in the queue 1
ASM background timer 1
ARCH wait on SENDREQ 1
Streams AQ: waiting for time management or cleanup tasks 1
ges remote message 1
log file parallel write 1
jobq slave wait 1
gc cr request 1
LNS wait on SENDREQ 1
EVENT COUNT(*)
---------------------------------------------------------------- ----------
log file sequential read 1
db file scattered read 1
smon timer 1
pmon timer 1
gc cr multi block request 1
latch: session allocation 1
DIAG idle wait 1
class slave wait 2
log file sync 2
latch free 3
SQL*Net message to client 4
EVENT COUNT(*)
---------------------------------------------------------------- ----------
gcs remote message 6
rdbms ipc message 14
latch: cache buffers chains 31
SQL*Net message from client 195
26 rows selected.
Elapsed: 00:00:00.01
SQL> SELECT name, gets, misses, sleeps,immediate_gets, immediate_misses FROM v$latch WHERE name = 'cache buffers chains';
NAME GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
-------------------------------------------------- ---------- ---------- ---------- -------------- ----------------
cache buffers chains 1.4473E+12 1.2728E+11 79920092 1.1851E+10 780538
Elapsed: 00:00:02.34
SQL> SELECT * FROM ( SELECT addr, ts#, file#, dbarfil, dbablk, tch FROM x$bh ORDER BY tch DESC) WHERE ROWNUM < 11;
ADDR TS# FILE# DBARFIL DBABLK TCH
---------------- ---------- ---------- ---------- ---------- ----------
0000002A97354590 7 2 2 2268277 9487
0000002A97354590 7 2 2 2268203 9404
0000002A97354590 7 2 2 2267429 9308
0000002A97354590 7 2 2 2268261 9306
0000002A97354590 7 2 2 2267372 9294
0000002A97354590 7 2 2 2267685 9060
0000002A97354590 7 2 2 2267305 9033
0000002A97354590 7 2 2 2267946 8961
0000002A97354590 7 2 2 2267508 7294
0000002A97354590 7 2 2 2267289 7290
10 rows selected.
Elapsed: 00:00:10.12
SQL> SELECT * FROM (SELECT O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE, SUM(TCH) TOUCHTIME FROM X$BH B, DBA_OBJECTS O WHERE B.OBJ = O.DATA_OBJECT_ID AND B.TS# > 0 GROUP BY O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE ORDER BY SUM(TCH) DESC) WHERE ROWNUM <= 10;
OWNER OBJECT_NAME OBJECT_TYPE TOUCHTIME
------------------------------ ------------------------------ ------------------- ----------
PISUSER TXN_LOG TABLE PARTITION 18245680
PISUSER TXN_TOT TABLE PARTITION 1536117
PISUSER BUS_APPEND TABLE 1376977
PISUSER ELEC_CARD_INFO TABLE 1053742
PISUSER AGENT_AUTH_OUT TABLE 1042929
PISUSER PSAMID_INFO TABLE 981717
PISUSER TXN_LOG_P_IDX5 INDEX PARTITION 786350
PISUSER T_DEPT TABLE 766087
PISUSER TMS_UPDATE_INFO TABLE 661674
PISUSER CHARGE_TXN_LOG TABLE PARTITION 594844
10 rows selected.
Elapsed: 00:00:08.18
SQL> select * from (select count(*), sql_id, nvl(o.object_name,ash.current_obj#) objn, substr(o.object_type,0,10) otype,CURRENT_FILE# fn, CURRENT_BLOCK# blockn from v$active_session_history ash , all_objects o where event like 'latch: cache buffers chains' and o.object_id (+)= ash.CURRENT_OBJ# group by sql_id, current_obj#, current_file#, current_block#, o.object_name,o.object_type order by count(*) desc )where rownum <=10;
COUNT(*) SQL_ID OBJN OTYPE FN BLOCKN
---------- ------------- ---------------------------------------- -------------------- ---------- ----------
4866 1yu0th95cyv4n -1 0 0
1273 asahm4k3u2703 -1 0 0
428 -1 0 0
336 aytavjs5pjf9m -1 0 0
172 69uh3w6uj12kd -1 0 0
158 349dd829jv4u3 -1 0 0
149 bbd8ha0y47hc8 -1 0 0
90 10m02vfz675x2 -1 0 0
40 17h2fbn9xyj8u -1 0 0
31 58qu4p0vw00x9 -1 0 0
10 rows selected.
Elapsed: 00:00:02.41
SQL> select sql_fulltext from v$sqlarea where sql_id='1yu0th95cyv4n';
SQL_FULLTEXT
--------------------------------------------------------------------------------
select count(*) into :b0 from txn_log where (((((((balancedate=:b1 and agentid
Elapsed: 00:00:00.22
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('1yu0th95cyv4n',0));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1yu0th95cyv4n, child number 0
-------------------------------------
select count(*) into :b0 from txn_log where (((((((balancedate=:b1 and agentid=:b2) and usrid=:b3) and
txnamt=:b4) and busid=:b5) and txnid=:b6) and txnstatus='1') and revstatus<>'2')
Plan hash value: 2633337795
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 64 | | | | |
| 2 | PARTITION RANGE SINGLE | | 1 | 64 | 4 (0)| 00:00:01 | KEY | KEY |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| TXN_LOG | 1 | 64 | 4 (0)| 00:00:01 | KEY | KEY |
|* 4 | INDEX RANGE SCAN | TXN_LOG_P_IDX5 | 1 | | 3 (0)| 00:00:01 | KEY | KEY |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("USRID"=:B3 AND "AGENTID"=:B2 AND "TXNAMT"=:B4 AND "TXNSTATUS"='1' AND "REVSTATUS"<>'2'))
4 - access("BUSID"=:B5 AND "TXNID"=:B6 AND "BALANCEDATE"=:B1)
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
23 rows selected.
Elapsed: 00:00:01.12
EVENT COUNT(*)
---------------------------------------------------------------- ----------
Streams AQ: qmn slave idle wait 1
Streams AQ: qmn coordinator idle wait 1
Streams AQ: waiting for messages in the queue 1
ASM background timer 1
ARCH wait on SENDREQ 1
Streams AQ: waiting for time management or cleanup tasks 1
ges remote message 1
log file parallel write 1
jobq slave wait 1
gc cr request 1
LNS wait on SENDREQ 1
EVENT COUNT(*)
---------------------------------------------------------------- ----------
log file sequential read 1
db file scattered read 1
smon timer 1
pmon timer 1
gc cr multi block request 1
latch: session allocation 1
DIAG idle wait 1
class slave wait 2
log file sync 2
latch free 3
SQL*Net message to client 4
EVENT COUNT(*)
---------------------------------------------------------------- ----------
gcs remote message 6
rdbms ipc message 14
latch: cache buffers chains 31
SQL*Net message from client 195
26 rows selected.
Elapsed: 00:00:00.01
SQL> SELECT name, gets, misses, sleeps,immediate_gets, immediate_misses FROM v$latch WHERE name = 'cache buffers chains';
NAME GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
-------------------------------------------------- ---------- ---------- ---------- -------------- ----------------
cache buffers chains 1.4473E+12 1.2728E+11 79920092 1.1851E+10 780538
Elapsed: 00:00:02.34
SQL> SELECT * FROM ( SELECT addr, ts#, file#, dbarfil, dbablk, tch FROM x$bh ORDER BY tch DESC) WHERE ROWNUM < 11;
ADDR TS# FILE# DBARFIL DBABLK TCH
---------------- ---------- ---------- ---------- ---------- ----------
0000002A97354590 7 2 2 2268277 9487
0000002A97354590 7 2 2 2268203 9404
0000002A97354590 7 2 2 2267429 9308
0000002A97354590 7 2 2 2268261 9306
0000002A97354590 7 2 2 2267372 9294
0000002A97354590 7 2 2 2267685 9060
0000002A97354590 7 2 2 2267305 9033
0000002A97354590 7 2 2 2267946 8961
0000002A97354590 7 2 2 2267508 7294
0000002A97354590 7 2 2 2267289 7290
10 rows selected.
Elapsed: 00:00:10.12
SQL> SELECT * FROM (SELECT O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE, SUM(TCH) TOUCHTIME FROM X$BH B, DBA_OBJECTS O WHERE B.OBJ = O.DATA_OBJECT_ID AND B.TS# > 0 GROUP BY O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE ORDER BY SUM(TCH) DESC) WHERE ROWNUM <= 10;
OWNER OBJECT_NAME OBJECT_TYPE TOUCHTIME
------------------------------ ------------------------------ ------------------- ----------
PISUSER TXN_LOG TABLE PARTITION 18245680
PISUSER TXN_TOT TABLE PARTITION 1536117
PISUSER BUS_APPEND TABLE 1376977
PISUSER ELEC_CARD_INFO TABLE 1053742
PISUSER AGENT_AUTH_OUT TABLE 1042929
PISUSER PSAMID_INFO TABLE 981717
PISUSER TXN_LOG_P_IDX5 INDEX PARTITION 786350
PISUSER T_DEPT TABLE 766087
PISUSER TMS_UPDATE_INFO TABLE 661674
PISUSER CHARGE_TXN_LOG TABLE PARTITION 594844
10 rows selected.
Elapsed: 00:00:08.18
SQL> select * from (select count(*), sql_id, nvl(o.object_name,ash.current_obj#) objn, substr(o.object_type,0,10) otype,CURRENT_FILE# fn, CURRENT_BLOCK# blockn from v$active_session_history ash , all_objects o where event like 'latch: cache buffers chains' and o.object_id (+)= ash.CURRENT_OBJ# group by sql_id, current_obj#, current_file#, current_block#, o.object_name,o.object_type order by count(*) desc )where rownum <=10;
COUNT(*) SQL_ID OBJN OTYPE FN BLOCKN
---------- ------------- ---------------------------------------- -------------------- ---------- ----------
4866 1yu0th95cyv4n -1 0 0
1273 asahm4k3u2703 -1 0 0
428 -1 0 0
336 aytavjs5pjf9m -1 0 0
172 69uh3w6uj12kd -1 0 0
158 349dd829jv4u3 -1 0 0
149 bbd8ha0y47hc8 -1 0 0
90 10m02vfz675x2 -1 0 0
40 17h2fbn9xyj8u -1 0 0
31 58qu4p0vw00x9 -1 0 0
10 rows selected.
Elapsed: 00:00:02.41
SQL> select sql_fulltext from v$sqlarea where sql_id='1yu0th95cyv4n';
SQL_FULLTEXT
--------------------------------------------------------------------------------
select count(*) into :b0 from txn_log where (((((((balancedate=:b1 and agentid
Elapsed: 00:00:00.22
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('1yu0th95cyv4n',0));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1yu0th95cyv4n, child number 0
-------------------------------------
select count(*) into :b0 from txn_log where (((((((balancedate=:b1 and agentid=:b2) and usrid=:b3) and
txnamt=:b4) and busid=:b5) and txnid=:b6) and txnstatus='1') and revstatus<>'2')
Plan hash value: 2633337795
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 64 | | | | |
| 2 | PARTITION RANGE SINGLE | | 1 | 64 | 4 (0)| 00:00:01 | KEY | KEY |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| TXN_LOG | 1 | 64 | 4 (0)| 00:00:01 | KEY | KEY |
|* 4 | INDEX RANGE SCAN | TXN_LOG_P_IDX5 | 1 | | 3 (0)| 00:00:01 | KEY | KEY |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("USRID"=:B3 AND "AGENTID"=:B2 AND "TXNAMT"=:B4 AND "TXNSTATUS"='1' AND "REVSTATUS"<>'2'))
4 - access("BUSID"=:B5 AND "TXNID"=:B6 AND "BALANCEDATE"=:B1)
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
23 rows selected.
Elapsed: 00:00:01.12