Latch: Cache Buffers Chains

latch: cache buffers chains 的例子

SELECT OBJECT_NAME, SUBOBJECT_NAME
FROM DBA_OBJECTS
WHERE DATA_OBJECT_ID IN
(SELECT DATA_OBJECT_ID
FROM (SELECT OBJ DATA_OBJECT_ID, FILE#, DBABLK, CLASS, STATE, TCH
FROM X$BH
WHERE HLADDR IN (SELECT ADDR
FROM (SELECT ADDR
FROM V$LATCH_CHILDREN
ORDER BY (GETS + MISSES + SLEEPS) DESC)
WHERE ROWNUM < 10)
ORDER BY TCH DESC)
WHERE ROWNUM < 10);
 
OBJECT_NAME                            SUBOBJECT_NAME
- - - - - -                               - - - - - -
FILE$                
PK_TRD_USR_CALL_RST_RNK                
LST_ASGN_USR                
ADDR                
LST_ASGN_USR                
ACSS_HIST                      SYS_P50
M01_CUST                
TRADE_CUST                          TFN_TRD01_DAT_TD336
IDX_TRD01_CALL_RST_CALL_DT          TFN_TRD01_IDX_P04
IDX_TRD01_TRADE_CALL_RST_APO_DT           TFN_TRD01_IDX_P04

一次Latch: cache buffer chains等待事件的学习

Posted on 2011/05/19 by adam

Latch: cache buffer chains等待时间的含义:

“buffer cache中block的header是被放置到hash chains上,而hash chains又是放在hash bucket中,多个hash bucket被一个cache buffers chains latch保护。当多个session并发访问同一个数据块上的数据,每个session都要首先获得cache buffers chains latch,这样将造成cache buffers chains latch的争用。”

一、查询各等待事件的含义:

search@WEBDB>r
1 select name, parameter1, parameter2, parameter3
2 from v$event_name
3* where name like 'latch%'
NAME PARAMETER1 PARAMETER2 PARAMETER3
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
latch: cache buffers chains address number tries
latch: redo writing address number tries
latch: redo copy address number tries
latch: Undo Hint Latch address number tries
latch: In memory undo latch address number tries
latch: MQL Tracking Latch address number tries
latch: row cache objects address number tries
latch: shared pool address number tries
latch: library cache address number tries
latch: library cache lock address number tries
latch: library cache pin address number tries
latch activity address number process#
latch free address number tries
latch: session allocation address number tries
latch: messages address number tries
latch: enqueue hash chains address number tries
latch: ges resource hash list address number tries
latch: gcs resource hash address number tries
latch: cache buffers lru chain address number tries
latch: checkpoint queue latch address number tries
latch: cache buffer handles address number tries
latch: object queue header operation address number tries
latch: object queue header heap address number tries
latch: redo allocation address number tries
latch: KCL gc element parent latch address number tries
latch: undo global data address number tries
latch: Change Notification Hash table latch address number tries
latch: virtual circuit queues address number tries
latch: parallel query alloc buffer address number tries
二、查询等待相关的sql语句

select sql_text, sql_fulltext, t2.sid, t2.event, t2.wait_class, t2.p1, t2.p2, t2.p3, t2.state
from v$sqlstats t1 inner join v$session t2 on t1.SQL_ID = t2.sql_id
inner join v$session_wait t3 on t2.sid = t3.sid
where t3.wait_class <> 'Idle'
三、查询latch相关的统计数据

select latch#, name, gets, misses, sleeps from v$latch where latch#=98 order by sleeps;
select latch#,name,gets,misses,sleeps from v$latch where name like 'cache buffer%';
参考资料:

模拟试验–latch free之cache buffers chains

某数据库经历了严重的cache buffers chains

Wait Event Enhancements in Oracle 10g

latch: cache buffers chains

发表于 2011 年 06 月 17 日 由 惜分飞
链接:http://www.xifenfei.com/1109.html
标题:latch: cache buffers chains
作者:惜分飞©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]
当一个数据块读入sga区,相应的buffer header会被放置到hash列表上,我们称其这hash chains,chain在中文的意为链条或串的意思,表达就是关连性.如果一个进程想访问或修改hash chain上的block,它首先要获得”cache buffers chains” latch。
原因一:低效率的SQL语句(主要体现在逻辑读过高)
cache buffers chains latch很大程度与逻辑读有关,所以要观注v$sql中BUFFER_GETS/EXECUTIONS大的语句。
同时每一个逻辑读需要一个latch get 操作及一个cpu操作,这样的sql也会很耗cpu资源。
原因二:热块(访问过于频繁)
找出热点块方法一:
- - 找出p1raw
select p1,p1raw from v$session_wait where event='latch: cache buffers chains';

- - 找到对象
SELECT /*+ RULE */
E.OWNER || '.' || E.SEGMENT_NAME SEGMENT_NAME,
E.PARTITION_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 = '00000002576EE018'- - p1raw
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;
找出热点块方法二:
- - 直接找出热点块
SELECT OBJECT_NAME, SUBOBJECT_NAME
FROM DBA_OBJECTS
WHERE DATA_OBJECT_ID IN
(SELECT DATA_OBJECT_ID
FROM (SELECT OBJ DATA_OBJECT_ID, FILE#, DBABLK, CLASS, STATE, TCH
FROM X$BH
WHERE HLADDR IN (SELECT ADDR
FROM (SELECT ADDR
FROM V$LATCH_CHILDREN
ORDER BY (GETS + MISSES + SLEEPS) DESC)
WHERE ROWNUM < 10)
ORDER BY TCH DESC)
WHERE ROWNUM < 10);

老杨同志的一个case 客户数据库出现大量cache buffer chains latch

http://yangtingkun.itpub.net/post/468/526943

客户产品数据库上午出现了严重的性能问题,简单记录一下问题的诊断和解决过程。

可以看到,数据库的DB TIME已经涨到了非常高的地步,这说明系统正经受着非常严重的性能问题:

Snap Id

Snap Time

Sessions

Cursors/Session

Begin Snap:

7170

31-3月 -12 08:00:50

395

10.5

End Snap:

7172

31-3月 -12 09:00:27

689

11.7

Elapsed:

59.61 (mins)

DB Time:

17,270.93 (mins)

从TOP 5看,等待事件中最明显的是latch: cache buffers chains,从当前系统的状态也可以看到这一点:

SQL> SELECT EVENT, COUNT(*) FROM GV$SESSION GROUP BY EVENT HAVING COUNT(*) > 5 ORDER BY 2 DESC;

EVENT COUNT(*)
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
latch: cache buffers chains 1043
SQL*Net message from client 500
SQL*Net message to client 35
rdbms ipc message 29
gc cr request 25
latch free 13
gcs remote message 10
gc buffer busy 7

8 rows selected.

系统中部分SQL的执行时间已经长达几个小时:

SQL ordered by Elapsed Time
Elapsed Time (s)

CPU Time (s)

Executions

Elap per Exec (s)

% Total DB Time

SQL Id

SQL Module

SQL Text

341,821

11,917

112

3051.97

32.99

8v9mhuxam279p

JDBC Thin Client

SELECT count(*) total FROM ( s…

244,752

3,380

147

1664.98

23.62

44zutxspd664c

JDBC Thin Client

SELECT count(*) total FROM ( s…

52,419

11,331

3

17472.91

5.06

a6aqkm30u7p90

JDBC Thin Client

select cf.cardid, (CASE WHEN c…

38,767

532

11

3524.24

3.74

8b8kbrqmg7qf9

JDBC Thin Client

SELECT count(*) total FROM ( s…

37,146

2,524

2

18573.15

3.58

8fpf0vtjzb583

JDBC Thin Client

SELECT count(*) total FROM ( s…

30,796

2,331

5

6159.20

2.97

9wxzf70vub4wg

JDBC Thin Client

SELECT count(*) total FROM ( s…

29,991

2,506

1

29991.41

2.89

5vdncfn06sxz8

JDBC Thin Client

SELECT count(*) total FROM ( s…

24,762

875

3

8254.06

2.39

8vyda1jxu2nsc

JDBC Thin Client

SELECT count(*) total FROM (SE…

17,845

699

2

8922.50

1.72

99g0x7u3jv28v

JDBC Thin Client

SELECT count(*) total FROM (SE…

17,470

1,295

0

1.69

08qmyqnjkpgru

JDBC Thin Client

select * from (select aa.*, ro…

其实根据这些现象,基本上可以判断问题了。肯定是SQL执行计划的改变导致了当前的性能问题。而类似绑定变量窥探之类的问题只会影响个别的SQL,而这么大面积的执行计划的改变,几乎可以断定是统计信息造成的。

询问了一下客户最近的操作,原来昨天夜里通过数据库链的方式导入了一部分数据。而今天一早问题就出现了。

其实问题已经很明显了,在通过数据库链加载数据后,并没有重新收集统计信息,且由于加载时间是在半夜,这批数据也没有赶上每天22:00的统计信息自动收集的工作。这就使得Oracle在生成执行计划时,会依赖数据加载前的统计信息,从而造成了错误的执行计划。

首先解决问题的根源问题,对加载过数据的表重新收集统计:

SQL> SELECT 'EXEC DBMS_STATS.GATHER_TABLE_STATS(''USER1'', ''' || TABLE_NAME || ''', CASCADE => TRUE)' FROM DBA_TABLES WHERE OWNER = 'USER1' AND LAST_ANALYZED < TRUNC(SYSDATE);

'EXECDBMS_STATS.GATHER_TABLE_STATS(''USER1'','''||TABLE_NAME||''',CASCADE=>TRUE)'
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
EXEC DBMS_STATS.GATHER_TABLE_STATS('USER1', 'TABLE_1', CASCADE => TRUE)
EXEC DBMS_STATS.GATHER_TABLE_STATS('USER1', 'TABLE_2', CASCADE => TRUE)
.
.
.
EXEC DBMS_STATS.GATHER_TABLE_STATS('USER1', 'TABLE_3', CASCADE => TRUE)
EXEC DBMS_STATS.GATHER_TABLE_STATS('USER1', 'TABLE_4', CASCADE => TRUE)

12 rows selected.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('USER1', ' TABLE_1', CASCADE => TRUE)

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('USER1', ' TABLE_2', CASCADE => TRUE)

PL/SQL procedure successfully completed.

.
.
.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('USER1', ' TABLE_3', CASCADE => TRUE)

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('USER1', ' TABLE_4', CASCADE => TRUE)

PL/SQL procedure successfully completed.

虽然问题的根源已经被解决,但是当前运行的SQL并不会改变执行计划,因此还需要找到问题的SQL,从后台将其结束:

SQL> SELECT 'kill -9 ' || spid from v$session s, v$process p where s.username = 'USER2' and s.paddr = p.addr and event = 'latch: cache buffers chains';

'KILL-9'||SPID
- - - - - - - - - - - - - - - - - - - -
kill -9 28321
kill -9 25384
kill -9 23697
kill -9 7239
.
.
.
kill -9 9331
kill -9 13759

740 rows selected.

SQL> SELECT 'kill -9 ' || spid from gv$session s, gv$process p where s.username = 'USER2' and s.paddr = p.addr and event = 'latch: cache buffers chains' and s.inst_id = p.inst_id and s.inst_id = 2;

'KILL-9'||SPID
- - - - - - - - - - - - - - - - - - - -
kill -9 23992
kill -9 5289
kill -9 21067
kill -9 16816
kill -9 16820
kill -9 26767
.
.
.
kill -9 14981
kill -9 26678
kill -9 26682

258 rows selected.

分别在两个节点杀掉这些执行计划存在问题的会话,释放被大量占用的系统资源。

由于Oracle的执行计划并非在收集统计信息后马上生效,因此还有个别的SQL仍然沿用错误的执行计划:

SQL> select distinct inst_id, sql_id from gv$session where event = 'latch: cache buffers chains';

INST_ID SQL_ID
- - - - - - - - - - - - - - - - - - - - - - -
1 39gvg7vbcm8jx
1 a6aqkm30u7p90

SQL> select address, hash_value from v$sqlarea where sql_id = 'a6aqkm30u7p90';

ADDRESS HASH_VALUE
- - - - - - - - - - - - - - - - - - - - - - - - - -
C000000EB7ED3420 3248739616

SQL> exec dbms_shared_pool.purge('C000000EB7ED3420,3248739616','C')

PL/SQL procedure successfully completed.

SQL> select address, hash_value from v$sqlarea where sql_id ='39gvg7vbcm8jx';

ADDRESS HASH_VALUE
- - - - - - - - - - - - - - - - - - - - - - - - - -
C000001037B8E308 3603538493

SQL> exec dbms_shared_pool.purge('C000001037B8E308’,‘3603538493', 'C')

PL/SQL procedure successfully completed.

由于当前的数据库版本是10.2.0.5,因此可以很方便的使用dbms_shared_pool将执行计划错误的SQL清除出共享池,强制其重新生成执行计划。

SQL> select event, count(*) from gv$session where username like != user GROUP BY EVENT order by 2 desc;

EVENT COUNT(*)
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
SQL*Net message from client 370
SQL*Net message to client 15
gc cr request 10
latch free 4
Streams AQ: waiting for messages in the queue 1

5 rows selected.

数据库中SQL执行计划错误除了导致大量的latch: cache buffers chains等待以外,还存在gc cr request和latch free等这些等待事件,经分析同样是由于错误的执行计划所致。将这些会话采用相同的方法清除后,系统负载恢复到正常范围:

Snap Id

Snap Time

Sessions

Cursors/Session

Begin Snap:

7188

31-3月 -12 14:55:17

257

20.3

End Snap:

7189

31-3月 -12 15:03:07

256

19.9

Elapsed:

7.84 (mins)

DB Time:

70.46 (mins)

某数据库经历了严重的cache buffers chains

作者:oral 发表于:2010-12-29 点击: 5,322 views
某数据库今天经历了严重的cache buffers chains以及频繁的enqueue,造成了数据库长时间不可用。

我们进行一下分析:

SQL> select a.event,count(*) from v$session_wait a,v$session b where a.sid=b.sid and b.username is not null group by a.event order by count(*);

EVENT COUNT(*)
—————————————– ———-
latch free 2
enqueue 7
SQL*Net message to client 2
direct path read 2
SQL*Net message from client 296
SQL>
SQL> select inst_id,sid,seq#,event,p1,p2,p3,state from gv$session_wait where rownum<21;

INST_ID SID SEQ# EVENT P1 P2 P3 STATE
———- ———- ———- ———————– ———- – ——– ———- ——————-
1 26 25302 latch free 4422329650 98 0 WAITED KNOWN TIME
1 127 3850 latch free 4422292702 98 0 WAITED KNOWN TIME
1 1 38185 pmon timer 300 0 0 WAITING
1 2 19845 rdbms ipc message 300 0 0 WAITING
1 3 3715 rdbms ipc message 300 0 0 WAITING
1 4 11015 rdbms ipc message 300 0 0 WAITING
1 8 8132 rdbms ipc message 180000 0 0 WAITING
1 9 58091 rdbms ipc message 500 0 0 WAITING
1 12 36139 rdbms ipc message 30000 0 0 WAITING
1 11 36554 rdbms ipc message 6000 0 0 WAITING
1 5 30945 rdbms ipc message 290 0 0 WAITING
1 6 27184 rdbms ipc message 300 0 0 WAITING
1 64 25293 enqueue 1415053318 65538 9059669 WAITING
1 103 37814 enqueue 1415053318 65538 9059669 WAITING
1 83 42391 enqueue 1415053318 65538 9059669 WAITING
1 154 10850 enqueue 1415053318 65538 9059669 WAITING
1 332 59789 enqueue 1415053318 65538 9059669 WAITING
1 421 43323 enqueue 1415053318 65538 9059669 WAITING
1 290 38585 enqueue 1415053318 65538 9059669 WAITING
1 7 52745 smon timer 300 0 0 WAITING

20 rows selected

SQL> select addr,latch#,child#,name from v$latch_children where addr in (select p1raw from v$session_wait where sid=26);

ADDR LATCH# CHILD# NAME
—————- ———- ———- —————————————————————-
00000405A52F83E8 98 968 cache buffers chains

SQL> select addr,latch#,child#,name from v$latch_children where addr in (select p1raw from v$session_wait where sid=127);

ADDR LATCH# CHILD# NAME
—————- ———- ———- —————————————————————-
00000405A52F83E8 98 968 cache buffers chains

SQL> select latch#, name, gets, misses, sleeps from v$latch where latch#=98 and sleeps>0 order by sleeps;

LATCH# NAME GETS MISSES SLEEPS
———- —————————————————————- ———- ———- ———-
98 cache buffers chains 5730958866 1966426833 140223822

SQL>
SQL> select latch#,name,gets,misses,sleeps from v$latch where name like ‘cache buffer%’;
LATCH# NAME GETS MISSES SLEEPS
———- —————————————————————- ———- ———- ———-
93 cache buffers lru chain 257203842 71984 2430
98 cache buffers chains 5731239263 1966450151 140225169
99 cache buffer handles 2220708770 802949769 207697

SQL>
SQL>
SQL> select sql_text from v$sqltext a where a.hash_value =
2 (select sql_hash_value from v$session b where b.sid = ‘&sid’) order by piece asc; –26

SQL_TEXT
—————————————————————-
delete from big_aero_task b where not exists( select * from big_
small_aero_task bst where b.big_aero_task_no=bst.big_aero_task_no)

SQL> select sql_text from v$sqltext a where a.hash_value =
2 (select sql_hash_value from v$session b where b.sid = ‘&sid’) order by piece asc; –127

SQL_TEXT
—————————————————————-
delete from big_aero_task b where not exists( select * from big_
small_aero_task bst where b.big_aero_task_no=bst.big_aero_task_no)

初步了解和latch free可能相关的SQL
SQL> select latch#, name, gets, misses, sleeps from v$latch where sleeps>0 order by sleeps desc;

LATCH# NAME GETS MISSES SLEEPS
———- ———————————————- ———- ———- ———-
98 cache buffers chains 5735703972 1967719474 140255461
147 row cache objects 8244048102 2333854449 64801473
157 library cache 2699793717 150052835 18192600
156 shared pool 8391271266 154642210 8574410
102 simulator lru latch 416943920 961593 749954
146 row cache enqueue latch 1602369746 1391027788 237875
99 cache buffer handles 2291212724 803353237 207749
103 simulator hash latch 2127124959 23713272 81707
158 library cache pin 1803136014 26908166 26821
4 session allocation 1793772940 190018904 26037
7 session idle bit 2697838834 897415 15867
159 library cache pin allocation 7384975893 1740551 14329
17 enqueue hash chains 2876026086 487408 14218
100 multiblock read objects 852180388 582774 9164
150 user lock 24958944 133507 2680
93 cache buffers lru chain 257363898 72055 2432
115 redo allocation 2175436345 15257589 1631
16 enqueues 2657559634 1353465 1380
193 child cursor hash table 1703573283 278335 1086
200 parallel query alloc buffer 11037 1757 702

LATCH# NAME GETS MISSES SLEEPS
———- ———————————————- ———- ———- ———-
97 checkpoint queue latch 4343320637 18480 79
139 undo global data 737765851 22624 50
132 dml lock allocation 176331818 66529 48
3 process allocation 5490206 55 24
205 temporary table state object allocation 36091 46 20
33 channel handle pool latch 10983729 38 17
88 mostly latch-free SCN 40852293 48940 14
15 messages 308696977 161017 10
202 hash table column usage latch 1475189 45 8
133 list of block allocation 8000029 6684 7
135 dummy allocation 13666111 1578 7
136 transaction branch allocation 404088339 8215 6
134 transaction allocation 1564298668 2122 5
208 SQL memory manager workarea list latch 417986859 8374 5
34 channel operations parent latch 26061496 67 4
2 post/wait queue 37615104 2188 2
96 active checkpoint queue latch 22351638 87022 2
138 sort extent pool 11873491 152 2
6 process group creation 10980094 41 1
122 loader state object freelist 6277602 16 1
113 redo writing 153294165 23729 1

LATCH# NAME GETS MISSES SLEEPS
———- ———————————————- ———- ———- ———-
155 global tx hash mapping 1122647093 1613 1
199 parallel query stats 686 188 1
10 object stats modification 23222 2 1
89 lgwr LWN SCN 40728439 5502 1
91 Consistent RBA 40475395 98 1

46 rows selected

SQL>
SQL> select addr, latch#, gets, misses, sleeps from v$latch_children where sleeps>0 and latch# = 98 order by sleeps desc;

ADDR LATCH# GETS MISSES SLEEPS
—————- ———- ———- ———- ———-
00000405A76D8EC0 98 881890062 2177386905 38750487
00000405A3554EE8 98 2282220177 2320518265 10192921
00000405A34649A8 98 2706896732 78649665 5232232
00000405A76D7CC0 98 472511284 1001020577 4304705
00000405A75A1700 98 2063348442 57257426 3650993
00000405A7634D00 98 3870861723 857326389 3598482
00000405A5525428 98 245045860 964686273 2557217
00000405A5325748 98 1620550519 23522084 2374275
00000405A5325508 98 2020650388 51331130 1474288
00000405A54F9088 98 967665519 499258117 1139308
00000405A5415C88 98 3791147000 280794792 1129990
00000405A545E7C8 98 3166430825 38271598 1107518
00000405A34F7FA8 98 3452080332 189249866 1082238
00000405A76DE8C0 98 3895646842 28832482 1058103
00000405A3596768 98 3251125368 158654962 1012493
00000405A3428A08 98 1936336065 201789541 824865
00000405A76D3280 98 3652415903 227435545 798522
00000405A36382E8 98 564495960 119754566 792875
00000405A35034C8 98 2651320857 230670072 656562
00000405A36C0A88 98 1532244563 49564231 652258

从这里已经可以看出系统的cache buffers chains很重

SQL> select username,sid,opname, round(sofar*100 / totalwork,0) || ‘%’ as progress, time_remaining,sql_text from v$session_longops, v$sql
2 where time_remaining <> 0 and sql_address = address and sql_hash_value = hash_value;

USERNAME SID OPNAME PROGRESS TIME_REMAINING SQL_TEXT
————— ———- ———— ———————– ————– ——————-
没有长时间操作SQL
SQL>
SQL> col owner format a15
SQL> col object_name format a30
SQL> col osuser format a15
SQL> col username format a15
SQL> col machine format a20
SQL> col program format a25
SQL> select ls.sid,ls.serial#,ls.status status,o.owner,o.object_name,
2 decode(ls.type, ‘RW’, ‘Row wait enqueue lock’, ‘TM’, ‘DML enqueue lock’, ‘TX’, ‘Transaction enqueue lock’, ‘UL’, ‘User supplied lock’) type,
3 decode(ls.lmode, 1, null, 2, ‘Row Share’, 3, ‘Row Exclusive’, 4, ‘Share’, 5, ‘Share Row Exclusive’, 6, ‘Exclusive’, null) lmode,
4 ls.id1,ls.id2,ls.username,ls.osuser,ls.machine,ls.program
5 from sys.dba_objects o, ( select s.osuser, s.username, l.type, l.lmode, s.sid, s.serial#,s.status, s.machine, s.program, l.id1, l.id2
6 from v$session s, v$lock l where s.sid = l.sid ) ls where o.object_id = ls.id1 and o.owner <> ‘SYS’ and username is not null order by ls.sid;

SID SERIAL# STATUS OWNER OBJECT_NAME TYPE LMODE
——- ———- ——– ——- ———————– ——————— —————-
64 55650 ACTIVE MJ BIG_SMALL_AERO_TASK DML enqueue lock Row Exclusive
83 59580 ACTIVE MJ BIG_SMALL_AERO_TASK DML enqueue lock Row Exclusive
103 15752 ACTIVE MJ BIG_SMALL_AERO_TASK DML enqueue lock Row Exclusive
127 58628 ACTIVE MJ BIG_AERO_TASK DML enqueue lock Row Exclusive
127 58628 ACTIVE MJ OPERATION_LOG DML enqueue lock Row Exclusive
127 58628 ACTIVE MJ BIG_SMALL_AERO_TASK DML enqueue lock Row Exclusive
154 26510 ACTIVE MJ BIG_AERO_TASK DML enqueue lock Row Exclusive
154 26510 ACTIVE MJ OPERATION_LOG DML enqueue lock Row Exclusive
154 26510 ACTIVE MJ BIG_SMALL_AERO_TASK DML enqueue lock Row Exclusive
290 49273 ACTIVE MJ BIG_AERO_TASK DML enqueue lock Row Exclusive
290 49273 ACTIVE MJ OPERATION_LOG DML enqueue lock Row Exclusive
290 49273 ACTIVE MJ BIG_SMALL_AERO_TASK DML enqueue lock Row Exclusive
332 60098 ACTIVE MJ BIG_AERO_TASK DML enqueue lock Row Exclusive
332 60098 ACTIVE MJ OPERATION_LOG DML enqueue lock Row Exclusive
332 60098 ACTIVE MJ BIG_SMALL_AERO_TASK DML enqueue lock Row Exclusive
421 38553 ACTIVE MJ BIG_SMALL_AERO_TASK DML enqueue lock Row Exclusive

16 rows selected

导致的enqueue很频繁

SQL>
SQL> select k.ksmfsadr,ksmfsnam,ksmfstyp,ksmfssiz,kslldnam,kslldlvl
2 from x$ksmfsv k,x$kslld a where k.ksmfsnam = ‘ksqeql_’ and kslldnam = ‘enqueues’;

KSMFSADR KSMFSNAM KSMFSTYP KSMFSSIZ KSLLDNAM KSLLDLVL
—————- ———- ———- ———- ————— ———-
000000038000A0B0 ksqeql_ ksllt 248 enqueues 5

SQL>
SQL> select latch#,name from v$latch where latch#=98;

LATCH# NAME
———- —————————————————————-
98 cache buffers chains

SQL>
SQL> select event,p1,p2,p3,wait_time from v$session_wait where event not like ‘%SQL%’ and event not like ‘%ipc%’ and event not like ‘%pmon%’;

EVENT P1 P2 P3 WAIT_TIME
—————————– ———- ———- ———- ———-
latch free 1503285062 156 0 -1
latch free 4422330606 98 0 -1
latch free 1503285136 156 0 -1
latch free 4422293934 98 0 -1
latch free 4422331523 98 0 -1
latch free 4422294854 98 0 -1
direct path read 206 1054546 63 -1
smon timer 300 0 0 0
jobq slave wait 0 0 0 0
jobq slave wait 0 0 0 0
jobq slave wait 0 0 0 0
jobq slave wait 0 0 0 0
jobq slave wait 0 0 0 0
jobq slave wait 0 0 0 0
jobq slave wait 0 0 0 0
jobq slave wait 0 0 0 0
jobq slave wait 0 0 0 0
wakeup time manager 0 0 0 0

18 rows selected

SQL> select latch#,name from v$latch where latch#=98;

LATCH# NAME
———- —————————————————————-
98 cache buffers chains

SQL> select latch#,name from v$latch where latch#=156;

LATCH# NAME
———- —————————————————————-
156 shared pool

还有shared pool产生的latch free
SQL>
SQL> select name,wait_time from v$latch where name = ‘enqueues’;

NAME WAIT_TIME
—————————————————————- ———-
enqueues 37585447

SQL>
SQL> select addr,latch#,child#,gets,misses,sleeps from v$latch_children where name = ‘cache buffers chains’ and rownum < 21;

ADDR LATCH# CHILD# GETS MISSES SLEEPS
—————- ———- ———- ———- ———- ———-
00000405A55E18E8 98 32768 593229945 3782730 849
00000405A785D780 98 32767 178088162 257302 104
00000405A3720A28 98 32766 433019205 2532031 2260
00000405A55E17C8 98 32765 360966711 1738480 599
00000405A785D660 98 32764 535163296 3085572 1410
00000405A3720908 98 32763 120563975 101446 75
00000405A55E16A8 98 32762 602516992 5254508 6684
00000405A785D540 98 32761 201110649 265896 146
00000405A37207E8 98 32760 185920646 19609844 140784
00000405A55E1588 98 32759 592919575 6600180 6423
00000405A785D420 98 32758 645144049 5532200 1669
00000405A37206C8 98 32757 254359836 491317 366
00000405A55E1468 98 32756 1068992894 18135987 46725
00000405A785D300 98 32755 143728131 121223 79
00000405A37205A8 98 32754 70153418 5600 98
00000405A55E1348 98 32753 764495028 5510140 2789
00000405A785D1E0 98 32752 71971759 2298 47
00000405A3720488 98 32751 68506656 2002 100
00000405A55E1228 98 32750 66477749 2104 63
00000405A785D0C0 98 32749 75531679 2357 92

20 rows selected

SQL>
SQL> select dbarfil,dbablk from x$bh where hladdr in(select addr from (select addr from v$latch_children order by sleeps desc)) and rownum < 11;

DBARFIL DBABLK
———- ———-
11 220087
45 871657
32 99442
32 66674
45 707817
45 675049
15 445401
15 379865
45 478441
45 281833

10 rows selected

查找到热点块对应的datafile id和block number

SQL> select file_id, file_name from dba_data_files where file_id in (45,15,11,32);

FILE_ID FILE_NAME
———- ——————————————————————————–
32 /ERDB/ereadye/fxwtbapace10.dbf
11 /ERDB/ereadye/user02.dbf
15 /ERDB/ereadye/user04.dbf
45 /ERDB/ereadye/FXWTBSPACE18.dbf

SQL>
SQL> select distinct a.owner,a.segment_name from dba_extents a,
2 (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
3 where a.RELATIVE_FNO = b.dbarfil and a.BLOCK_ID <= b.dbablk and a.block_id + a.blocks > b.dbablk;

cannot return data.

SQL>
SQL> select owner, table_name, num_rows from dba_tables where owner=’MJ’ and table_name in (‘BIG_AERO_TASK’,'BIG_SMALL_AERO_TASK’);

OWNER TABLE_NAME NUM_ROWS
————— —————————— ———-
MJ BIG_AERO_TASK 16886
MJ BIG_SMALL_AERO_TASK 71916

SQL> select count(*) from mj.big_aero_task;

COUNT(*)
———-
24615

SQL> select count(*) from mj.big_small_aero_task;

COUNT(*)
———-
104371

SQL> explain plan for
2 delete from big_aero_task b where not exists( select * from big_small_aero_task bst where b.big_aero_task_no=bst.big_aero_task_no);

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
——————————————————————————–
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost |
—————————————————————————–
| 0 | DELETE STATEMENT | | | | |
| 1 | DELETE | BIG_AERO_TASK | | | |
| 2 | FILTER | | | | |
| 3 | TABLE ACCESS FULL | BIG_AERO_TASK | | | |
| 4 | TABLE ACCESS FULL | BIG_SMALL_AERO_TASK | | | |
—————————————————————————–
Note: rule based optimization, PLAN_TABLE’ is old version

12 rows selected

全表扫描!
SQL>
SQL> show parameter optimizer_mode

NAME TYPE VALUE
———————————— ———– ——————————
optimizer_mode string RULE
SQL>
SQL> select table_name,index_name,column_name from user_ind_columns where table_name = ‘BIG_SMALL_AERO_TASK’;

TABLE_NAME INDEX_NAME COLUMN_NAME
—————————— —————————— ——————————————————————————–
BIG_SMALL_AERO_TASK BIG_SMALL_AERO_UNIQ AERO_TASK_NO

SQL> select table_name,index_name,column_name from user_ind_columns where table_name = ‘BIG_AERO_TASK’;

TABLE_NAME INDEX_NAME COLUMN_NAME
—————————— —————————— ——————————————————————————–
BIG_AERO_TASK PK_BIG_AERO_TASK BIG_AERO_TASK_NO
看起来索引忘记建了,这在一个已经运行多年的生产系统上发生简直不可思议,但是,它确实发生了。
在BIG_SMALL_AERO_TASK表上创建了基于BIG_AERO_TASK_NO的索引,再HINT执行计划,解决了enqueue问题。

但是,很明显,问题没有彻底解决,系统的cache buffers chains很重,系统存在的问题不少

SQL> select sql_text,buffer_gets,sharable_mem,users_opening,fetches,executions
2 from v$sqlarea where rownum<21 order by buffer_gets desc;

SQL_TEXT BUFFER_GETS SHARABLE_MEM USERS_OPENING FETCHES EXECUTIONS
———————————– ———– ———— ————- ———- ———-
ROLLBACK 736935724 7530 852 0 123155
SELECT 1 FROM DUAL 222934317 12972 51 74303378 74303427
COMMIT 5373023 7008 1830 0 5990287
DELETE from ems_message_pub 4682447 14861 0 0 61309
DELETE from ems_message_aoc 3985090 14717 0 0 61309
delete from cgo_task 1100192 14207 0 0 166
select WFCODE from SYS_TODO 733388 17621 0 183347 183347
DELETE from v_flight_info 33685 15667 0 0 1
SELECT user from sys.dual 19401 12979 138 6467 6467
DELETE from cgo_task_table 5568 14652 0 0 3
SELECT USER FROM DUAL 1080 26032 0 360 360
commit 166 7337 0 0 166
select ‘x’ from dual 84 13983 0 28 28
SELECT * FROM “FLIGHT_CREW” 78 5315029 0 0 0
select * from v$version 59 25490 1 6 6
select * from airport 46 22871 0 36 1
select null from dual 12 13688 0 4 4
select sysdate from dual 12 5554 4 4 4
commit 0 12248 0 0 360
SET TRANSACTION READ WRITE 0 7076 0 0 74737413

20 rows selected
SQL>
SQL> select sql_text,buffer_gets,sharable_mem,users_opening,fetches,executions
2 from v$sqlarea where rownum<21 order by executions desc;

SQL_TEXT BUFFER_GETS SHARABLE_MEM USERS_OPENING FETCHES EXECUTIONS
———————————– ———– ———— ————- ———- ———-
SET TRANSACTION READ WRITE 0 7076 0 0 74740999
SELECT 1 FROM DUAL 222941667 12972 50 74305828 74305876
COMMIT 5373150 7008 1843 0 5990428
ROLLBACK 736935737 7530 854 0 123157
DELETE from ems_message_aoc 3985415 14717 0 0 61314
DELETE from ems_message_pub 4682828 14861 0 0 61314
SELECT user from sys.dual 19404 12979 139 6468 6468
commit 0 12248 0 0 361
SELECT USER FROM DUAL 1083 26032 0 361 361
commit 167 7337 0 0 167
delete from cgo_task 1106325 14207 0 0 167
select ‘x’ from dual 90 13983 0 30 30
select * from v$version 62 25490 0 7 7
select null from dual 18 14496 0 6 6
select sysdate from dual 12 5554 4 4 4
DELETE from cgo_task_table 5568 14652 0 0 3
select * from airport 46 22871 0 36 1
DELETE from v_flight_info 33685 15667 0 0 1
select * from plan_table 23 32195 0 0 0
SELECT * FROM “FLIGHT_CREW” 80 5315029 0 0 0

20 rows selected

TOP SQL看起来问题也很多,要检查解析,绑定变量的使用!还有,频繁的commit和rollback的必要性!

oracle实验记录 (buffer_cache分析(1))

http://space.itpub.net/?uid-12020513-action-viewspace-itemid-620667

buffer cache中获取block 过程
DML or select时候 oracle根据 SQL语句执行计划,找到block,构造一个 叫buffer descriptor的block描述内存的结构(主要含block 物理地址 ,type,object id),这个block存在 session的 pga中,oracle应用buffer descriptor记录的信息 运用hash算法,得到需要的block所在的hash bucket(确定block在哪条hash chain上),从 上面 挂的 第一个buffer header搜索到最后一个buffer header,
hash chain上 搜索逻辑 :
1.比较buffer header 上所记录的block地址,不符合条件SKIP 此buffer header
2.skip status为cr的buffer header
3.如果buffer header状态为 reading则等待,直到状态改变后比较buffer header 记录的block地址是否符合
4.若发现block地址符合的buffer header,查该buffer header是否位于正在使用的list上,如果是 则判断已存在的lock mode,与要求的lock mode是否兼容,如果兼容则返回该buffer header中记录的block地址,将当前process id放入buffer header 所处的正在使用的list上
5.如果lock mode不兼容,用buffer header所指向的block中的内容构建一个xcurrent的block 和一个cr状态的buffer header(指向新建立xcurrent状态的 复制block)
6.搜索完整个hash chain还未发现需要的buffer header,从disk读取datafile,读入到buffer cache中 相应的buffer header 挂在hash chain上

buffer cache指向block的状态分6种
1.free =可以被重用的block
2.xcurrent=已EXCLUSIVE方式获取的当前模式的block(insert,update,delete时产生),scurrent=可以与其他instance共享的当前模式block
3.cr=一致读块,永远不会写入disk
4.reading=正从disk读取出来的块
5.mreciver=正在进行介质恢复的block
6.ircovery=正在进行instance recovery的block

SQL> select distinct status from v$bh;

STATUS
- - - - - - -
xcur
free
cr

buffer header
在buffer cache中,每一个block读入 buffer cache时,都会在buffer cache中构造一个buffer header(buffer header与block一一对应)
1.存放该block在buffer cache中实际存储地址
2.存放该block的类型(data,segment header,undo header,undo block等类型)
3.由于此buffer header 所在的hash chain,是通过在buffer header保存指向前一个buffer header的指针和指向后一个buffer header的指针方式实现,所以还存指针
4.存储lru,lruw,ckptq,fileq等队列,一样是通过记录前后buffer header指针方式实现
5.当前该buffer header所对应的数据块的状态以及标记
6.该buffer header被访问的次数(touch次数)
7.正在等待该buffer header的进程列表(waiter list)及正在使用此buffer header的(user list)

bucket 与cache buffers chains latch
oracle通过bucket管理buffer cache ,oracle用hash算法将不同的buffer分配到bucket中,当需要定位需要的buffer是否在buffer cache中时,用同样的hash 算法 到相应的bucket上 搜索对应的buffer header既可 bucket 中buffer header通过cache buffer chains连接(双向链表)

查 buffer 中 默认 hash bucket数量既有多条hash chain
SQL> set linesize 132
SQL> column name format a30
SQL> column value format a25
SQL> select
2 x.ksppinm name,
3 y.ksppstvl value,
4 y.ksppstdf isdefault,
5 decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
6 decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj,x. KSPPDESC
7 from
8 sys.x$ksppi x,
9 sys.x$ksppcv y
10 where
11 x.inst_id = userenv('Instance') and
12 y.inst_id = userenv('Instance') and
13 x.indx = y.indx and
14 x.ksppinm like '%_&par%'
15 order by
16 translate(x.ksppinm, ' _', ' ')
17 /
输入 par 的值: db_block_hash
原值 14: x.ksppinm like '%_&par%'
新值 14: x.ksppinm like '%_db_block_hash%'

NAME VALUE ISDEFAULT ISMOD IS
ADJ
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - -
KSPPDESC
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
_db_block_hash_buckets 65536 TRUE FALSE FA
LSE

oracle一直在优化hash buckets数目 因为 hash bucket 越多,存放的buffer header越分散(但bucket太多的话对空间,管理 都有压力) 对于cache buffer chain latch争用越少

查看buffer header结构
SQL> create table t1 (a char(2000), b char(2000), c char(2000));

表已创建。

SQL> insert into t1 values ('a','a','a');

已创建 1 行。

SQL> insert into t1 values ('b','b','b');

已创建 1 行。

SQL> commit;

提交完成。

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

HEADER_FILE HEADER_BLOCK OWNER
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
4 1115 XH

SQL> select file#,block# ,rowid from (select dbms_rowid.rowid_relative_fno(rowi
d) file#,dbms_rowid.rowid_block_number(rowid) block# ,rowid from T1);

FILE# BLOCK# ROWID
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
4 1117 AAAMm+AAEAAAARdAAA
4 1118 AAAMm+AAEAAAAReAAA
SQL> select object_id,data_object_id from user_objects where object_name='T1';

OBJECT_ID DATA_OBJECT_ID
- - - - - - - - - - - - - - - - - - - - - - - -
51646 51646

SQL> select file#,block#,status,class# from v$bh where bjd=51646;

FILE# BLOCK# STATUS CLASS#
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
4 1118 xcur 1
4 1113 xcur 8
4 1116 xcur 1
4 1119 xcur 1
4 1114 xcur 9
4 1117 xcur 1
4 1120 xcur 1
4 1115 xcur 4

SQL>

SQL> alter session set events 'immediate trace name buffers level 1';

会话已更改。

1.dump buffer header
2.1+block header
3.2+block内容
4.dump buffer header & hash chain
5.1+dump block header &hash chain (1+4)
6.2+dump block header &hash chain (2+4)
8.dump buffer header & hash chain+ user list+waiter list
9.1+dump block header & hash chain+ user list+waiter list
10.2+dump block内容+hash chain+ user list+waiter list

BH (15FEB49C) file#: 4 rdba: 0x0100045b (4/1115) class: 4 ba: 15CF6000
set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 239
dbwrid: 0 obj: 51646 objn: 51646 tsn: 4 afn: 4
hash: [2019a6a0,2019a6a0] lru: [15feb5a0,15feb440]
ckptq: [15feb364,183eb414] fileq: [15feb36c,183eb41c] objq: [1eb25148,15feb494]
st: XCURRENT md: NULL tch: 4
flags: buffer_dirty gotten_in_current_mode redo_since_read
LRBA: [0xb.22ea.0] HSCN: [0x0.d1044] HSUB: [1]

…………………………….

BH (15FEABAC) file#: 4 rdba: 0x01000460 (4/1120) class: 1 ba: 15CDC000
set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 239
dbwrid: 0 obj: 51646 objn: 51646 tsn: 4 afn: 4
hash: [201a8d38,201a8d38] lru: [15feacb0,163f3840]
ckptq: [1a3f8304,15feac84] fileq: [2025c878,15feac8c] objq: [15fead04,1eb25148]
st: XCURRENT md: NULL tch: 2
flags: buffer_dirty gotten_in_current_mode redo_since_read
LRBA: [0xb.2300.0] HSCN: [0x0.d1044] HSUB: [1]

………………………….
在buffer cache中 一共可以找到 (select file#,block#,status from v$bh where bjd=51646;)对应的所有buffer header

分析:
hash: [2019a6a0,2019a6a0] 就是指向前一个buffer header的指针 和 指向后一个buffer header的指针,x$bh中 NXT_HASH
PRV_HASH,例中两个值相等表示这个hash chain上只有一个buffer header

lru: [15feb5a0,15feb440] x$bh中 nxt_repl,prv_repl,lru上的下一个buffer和 上一个buffer,ckptq,fileq都是buffer修改后 使用的队列
可以看出buffer header就是一个双向链表组成

class:表示buffer header对应block的类型,1=data block,2=sort block,3=save undo block,4=segment header,5=save undo header,6=free list,7=extent map,
8=1st level bmb;9=2nd level bmb;10=3rd level bmb;11=bitmap block;12=bitmap index block;13=unused;14=undo header;15=undo block。
对应v$bh中class#

rdba:buffer header对应的 块地址
SQL> variable file# number;
SQL> variable blk# number;
SQL> execute :file#:=dbms_utility.data_block_address_file(to_number('1000460','x
xxxxxx'));

PL/SQL 过程已成功完成。

SQL> execute :blk#:=dbms_utility.data_block_address_block(to_number('1000460','x
xxxxxxx'));

PL/SQL 过程已成功完成。

SQL> print file#

FILE#
- - - - - - - - - -
4

SQL> print block#
SP2-0552: 未声明绑定变量 "BLOCK#"。
SQL> print blk#

BLK#
- - - - - - - - - -
1120

另外可以 rdba: 0x01000460 ,100前3位代表file#,0460代表block#

SQL> select to_number('100','xxxx') file#,to_number('0460','xxxxxx') block# f
dual;

FILE# BLOCK#
- - - - - - - - - - - - - - - - - - - -
256 1120

整体buffer header内容 对应 x$bh,v$bh

oracle实验记录 (buffer_cache分析(2)cbc latch)

http://space.itpub.net/12020513/viewspace-620668

上一篇 / 下一篇 2009-11-26 17:32:10 / 个人分类:oracle实验记录
查看( 425 ) / 评论( 1 ) / 评分( 0 / 0 )
关于cache buffers chain latch (cbc latch)
block读入sga buffer cache中时,对应的buffer header挂在hash bucket上的hash chain上,cache buffer chains latch 可以控制多个hash bucket,确保hash chain的完整性
,当在hash cahins中 添加删除读取数据时 进程需要获得cbc latch(9I开始 读的话cbc latch可以共享)

datablock放入哪个bucket 算法为 mod(dba,_db_block_hash_buckets)

SQL> select count(*) from v$latch_children where name='cache buffers chains';

COUNT(*)
- - - - - - - - - -
1024

SQL> select count(distinct hladdr ) from x$bh; (hladdr= hash latch address)

COUNT(DISTINCTHLADDR)
- - - - - - - - - - - - - - - - - - - - -
1024

补充下逻辑读
process先在运用hash算法 得到块所在的hash bucket 然后该bucket中找块所在的cache buffer chain.找到后 在这个 chain上加一个cache buffer chains latch,latch加上之后在这个chain中需要 所要块的buffer header,通过buffer header中block信息 找到该block PIN住,释放cache buffer chains latch,然后可以访问该块,server process不会一次将所有行提取出来,是按照命令抓取,每次读一定数量的行(比如sqlplus 中set arraysize X),这些行取出之后,会经由PGA传给user.行一旦从buffer cache中取出,释放块上pin后 逻辑读结束
每个逻辑读需要一个latch get 操作 和一个cpu 操作,latch get的目标获得latch,任意一个时刻 只有一个process可以 拥有cache buffer chain latch,

process每申请一次Cache buffer chains闩,就是一次逻辑读

SQL> create table t1 (a int);

表已创建。

SQL> insert into t1 values(1);

已创建 1 行。

SQL> insert into t1 values(2);

已创建 1 行。

SQL> insert into t1 values(3);

已创建 1 行。

SQL> insert into t1 values(4);

已创建 1 行。

SQL> commit;

SQL> set autotrace trace stat
SQL> select * from t1;

统计信息
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
463 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

SQL> set arraysize 2
SQL> select * from t1;

统计信息
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
0 recursive calls
0 db block gets
5 consistent gets~~~~~~~~~~~~~~~~~~~~~多了一次逻辑读 一共4行 sqlplus限制每次输出2行 多获取一次 cache buffers chain latch 多了一次逻辑读
0 physical reads
0 redo size
593 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

备注:consisten gets 算法公式
consistent reads计算=ceil(获取行数(card)/arraysize)+used blocks(FTS的话就是HWM下BLOCK)+1
分析:ceil(num_rows/arraysize) 例如取100行 每次显示到 屏幕10行 需要取10次,oracle 访问buffer cache 中相应的 hash chain 搜索需要的buffer时需要 持有 cache buffers chains latch取完数据后释放,再取时再获取,这样需要获取10次才够显示完100行, cache buffers chains latch每获取一次就是一次逻辑读 (对于select来说就是).
+1 是多加一次segment header block scan

cache buffer chains latch主要争用原因
1.低效sql,并发会话执行语句 设法获得相同数据集,少的逻辑读 意味少的latch get操作
2.hot block
多个会话重复访问一个或多个子cache buffer chain latch时热块就产生了
通过等待时间检查是否有热块

SQL> create table t1 (a int, b int);

表已创建。

SQL> ed
已写入 file afiedt.buf

1 declare
2 begin
3 for i in 1..10000 loop
4 insert into t1 values(i,i+1);
5 end loop;
6 commit;
7* end;
8 /

PL/SQL 过程已成功完成。
SQL> select distinct file#,block# from (select dbms_rowid.rowid_relative_fno(ro
wid) file#,dbms_rowid.rowid_block_number(rowid) block# from t1);

FILE# BLOCK#
- - - - - - - - - - - - - - - - - - - -
1 61635
1 61645
1 61646
1 61647
1 61634
1 61649
1 61638
1 61648
1 61640
1 61651
1 61643

FILE# BLOCK#
- - - - - - - - - - - - - - - - - - - -
1 61650
1 61637
1 61639
1 61641
1 61642
1 61636
1 61644

已选择18行。
SQL> select object_id,data_object_id from user_objects where object_name='T1';

OBJECT_ID DATA_OBJECT_ID
- - - - - - - - - - - - - - - - - - - - - - - -
51706 51706

已写入 file afiedt.buf

1 declare
2 begin
3 for i in 1..100000 loop
4 update t1 set b=i+2 where a=i;
5 commit;
6 end loop;
7* end;
SQL> /

PL/SQL 过程已成功完成。

SQL> declare
2 begin
3 for i in 1..100000 loop
4 update t1 set b=i+2 where a=i;
5 commit;
6 end loop;
7 end;
8 /

PL/SQL 过程已成功完成。

~~~~~~~~~~~~~~~~~~~多个会话并发访问修改

SQL> select s.event,sid ,s.p1raw,s.p2,s.p3 ,s.seconds_in_wait,s.wait_time,s.stat
e from v$session_wait s where s.event='latch free';

EVENT SID
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
P1RAW P2 P3 SECONDS_IN_WAIT WAIT_TIME STATE
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
latch free 142
03C49AA0 177 0 0 -1 WAITED SHORT TIME

latch free是一个汇总

制造了latch free 事件 由于实验环境 制造的事件持有latch时间太短 没有抓到对象

而且 试验中 latch address不是 cache buffer chains latch

SQL> select name from v$latch where addr='03C49AA0';

NAME
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
dml lock allocation

1 SELECT a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name
2 FROM x$bh a, dba_objects b
3 WHERE (a.obj = b.object_id OR a.obj = b.data_object_id)
4* AND a.hladdr = '03C49AA0'
SQL> /

未选定行

SQL> col event format a30
SQL> select event,p1,p1text from v$session_wait_history where event like '%cache
buffer%';

EVENT P1 P1TEXT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
latch: cache buffers chains 538535040 address
latch: cache buffers chains 539117440 address~~~~~~~~~~~~~~~~~~这个address类型是number v$latch中卫raw
latch: cache buffers chains 538807680 address
latch: cache buffers chains 538999040 address
latch: cache buffers chains 539117440 address
latch: cache buffers chains 538807680 address
latch: cache buffers chains 538712320 address
latch: cache buffers chains 538498560 address
latch: cache buffers chains 538962560 address

已选择9行。

SQL> select event,to_char(p1,'xxxxxxxxxxx'),p1text from v$session_wait_history w
here event like '%cache buffer%';

EVENT TO_CHAR(P1,' P1TEXT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
latch: cache buffers chains 20196480 address~~~~~~~~~~~~~~~转换下 类型
latch: cache buffers chains 20224780 address
latch: cache buffers chains 201d8d80 address
latch: cache buffers chains 20207900 address
latch: cache buffers chains 201c1900 address

1 SELECT a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name
2 FROM x$bh a, dba_objects b
3 WHERE (a.obj = b.object_id OR a.obj = b.data_object_id)
4* AND a.hladdr = '03C49AA0'
SQL> /

HLADDR FILE# DBABLK TCH OBJ OBJECT_NAM
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
…………………………………………………….
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
$

20196480 1 53192 1 181 ATTRIBUTE$
20196480 1 53192 1 181 METHOD$
20196480 1 53192 1 181 PARAMETER$
20196480 1 53192 1 181 RESULT$

已选择35行。 ~~~~~~~~~~~~~~~~~~~通过历史信息 找到了一个还在占有该latch的 修改的对象(可以看到 是一些系统表)

*

关于cache buffer chains latchs 争用 与热块 (cache buffer chains latchs 是造成latch free 事件的 原因之一)

第1种方式 通过等待事件 (latch free 是个汇总事件)来查看是否是热块 具体过程是
1.

SQL> select s.event,sid ,s.p1raw,s.p2,s.p3 ,s.seconds_in_wait,s.wait_time,s.stat
e from v$session_wait s where s.event='latch free';
找到等待事件,p1raw代表 latch地址(v$latch_children中address),p2 latch#,p3尝试次数

如果有大量的latch free 且 p1raw相等(表示大家在争用这个 latch),那么可能是热块(可以通过v$latch_children 结合P1raw看 是不是cache buffer chains latch select name from v$latch_children where addr='XXXXXXX')

2.用p1raw连接 x$bh中hladdr 连接dba_objectS找到具体对象,TCH 高一般就是hot block,block在lru端从冷端到热端时候tch重置为0所以tch 0不代表一定是冷块

1 SELECT a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name
2 FROM x$bh a, dba_objects b
3 WHERE (a.obj = b.object_id OR a.obj = b.data_object_id)
4* AND a.hladdr = 'XXXXX' (hladdr=hash latch address)

解决:让块中放的行数少些,通常是修改应用

第2种方式 通过top 方式 来查找争用等待最多的cache buffers chains latch 和top 热块(按tch =touch方式)

1.从v$latch_children中找 top10(order by sleeps 相当于按等待次数排序)的 子latch信息 top 10,但这top 10争用的cache buffers chains latch并不一定是热块

SQL> select * from (select addr,child#,gets,misses,sleeps ,immediate_gets,immedi
ate_misses ,spin_gets from v$latch_children where name='cache buffers chains' or
der by sleeps desc ) where rownum<11;

ADDR CHILD# GETS MISSES SLEEPS IMMEDIATE_GETS
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
IMMEDIATE_MISSES SPIN_GETS
- - - - - - - - - - - - - - - - - - - - - - - - - -
20210780 863 14406851 27589 136 37
0 27456

201D3600 472 4830021 6128 124 211
7 6009

201BC180 323 4830264 4440 120 313
4 4326

ADDR CHILD# GETS MISSES SLEEPS IMMEDIATE_GETS
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
IMMEDIATE_MISSES SPIN_GETS
- - - - - - - - - - - - - - - - - - - - - - - - - -
20216180 899 4829460 3730 111 375
6 3624

20207900 806 4834203 5280 101 365
0 5184

2021F000 956 4830676 4656 100 349
2 4558

ADDR CHILD# GETS MISSES SLEEPS IMMEDIATE_GETS
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
IMMEDIATE_MISSES SPIN_GETS
- - - - - - - - - - - - - - - - - - - - - - - - - -
20196480 81 4829726 5823 98 203
2 5728

2019BC00 116 4830893 3463 95 322
4 3371

201CA780 415 4832435 4373 94 378
2 4281

ADDR CHILD# GETS MISSES SLEEPS IMMEDIATE_GETS
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
IMMEDIATE_MISSES SPIN_GETS
- - - - - - - - - - - - - - - - - - - - - - - - - -
201D8D80 507 4830339 4335 91 372
1 4247

已选择10行。

SQL> select addr,ts#,file#,dbarfil,dbablk,tch ,hladdr from x$bh where hladdr='2
210780';

ADDR TS# FILE# DBARFIL DBABLK TCH HLADDR
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
089AC730 2 3 3 6315 1 20210780
089AC674 2 3 3 6315 1 20210780
089AC5B8 2 3 3 6315 1 20210780
089AC4FC 2 3 3 6315 1 20210780
089AC730 0 1 1 56596 8 20210780
089AC730 0 1 1 17321 1 20210780
089AC674 0 1 1 17321 1 20210780
089AC5B8 0 1 1 17321 1 20210780
089AC4FC 0 1 1 17321 1 20210780
089AC730 2 3 3 19795 1 20210780
089AC674 2 3 3 19795 1 20210780

ADDR TS# FILE# DBARFIL DBABLK TCH HLADDR
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
089AC5B8 2 3 3 19795 1 20210780
089AC4FC 2 3 3 19795 1 20210780
089AC730 0 1 1 52491 1 20210780
089AC730 2 3 3 2443 1 20210780
089AC674 2 3 3 2443 1 20210780
089AC5B8 2 3 3 2443 1 20210780
089AC4FC 2 3 3 2443 1 20210780
089AC730 2 3 3 33275 0 20210780
089AC674 0 1 1 9111 0 20210780
089AC730 0 1 1 61633 1 20210780

已选择21行。
可以看到 top 10 中 sleeps第一的cache buffer chain latch 管理的block中(cbc latch对应多个bucket所以管理多个块是正常的) 并没有热块tch都很低(但 block在lru端从冷端到热端时候tch重置为0所以tch 0不代表一定是冷块),造成cache buffers chains latche争用2个主要原因就是 热块和chains太长

2.查看系统中10大热块(从热块角度出发)
SQL> select * from (select addr,ts#,file#,dbarfil,dbablk,tch ,hladdr from x$bh o
rder by tch desc) where rownum<11;

ADDR TS# FILE# DBARFIL DBABLK TCH HLADDR
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
089AC5A0 0 1 1 1658 274 201F7F00
089AC5A0 0 1 1 1657 273 20195080
089AC394 2 3 3 2775 269 2018E000
089AC5A0 2 3 3 2772 269 201A5700
089AC394 2 3 3 2776 269 201F0E80
089AC394 2 3 3 2774 269 201CB180
089AC5A0 2 3 3 32272 269 20223880
089AC394 2 3 3 2773 269 20208580
089AC5A0 2 3 3 32271 269 201C0A00
089AC5A0 0 1 1 1674 265 201E6200

已选择10行。

3. 连接x$bh与v$latch_chindren信息
select b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch ,b.gets,b.misses,b.sleeps fro
m (select * from (select addr,ts#,file#,dbarfil,dbablk,tch,hladdr from x$bh orde
r by tch desc ) where rownum<11) a,(select addr,gets,misses,sleeps from v$latch_
children where name ='cache buffers chains') b where a.hladdr=b.addr
SQL> /

ADDR TS# DBARFIL DBABLK TCH GETS MISSES
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
SLEEPS
- - - - - - - - - -
20223880 2 3 32272 2505 25908 0
0

20208580 2 3 2773 2502 23968 0
0

201F7F00 0 1 1658 2554 15562 0
0

ADDR TS# DBARFIL DBABLK TCH GETS MISSES
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
SLEEPS
- - - - - - - - - -
201F0E80 2 3 2776 2502 20830 0
0

201E6200 0 1 1674 2484 14678 0
0

201CB180 2 3 2774 2502 20818 0
0

ADDR TS# DBARFIL DBABLK TCH GETS MISSES
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
SLEEPS
- - - - - - - - - -
201C0A00 2 3 32271 2502 29558 0
0

201A5700 2 3 2772 2505 15735 0
0

20195080 0 1 1657 2521 18961 0
0

ADDR TS# DBARFIL DBABLK TCH GETS MISSES
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
SLEEPS
- - - - - - - - - -
2018E000 2 3 2775 2502 20768 0
0

已选择10行。
分析将 系统中top 前10的热块与 cache buffer chains latch 联系起来

通过这些块 找到 对象信息 链接dba_extents

SQL> col segment_name format a30

1* select distinct e.owner,e.segment_name,e.segment_type from dba_extents e,(s
elect b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch ,b.gets,b.misses,b.sleeps from (sel
ect * from (select addr,ts#,file#,dbarfil,dbablk,tch,hladdr from x$bh order by t
ch desc ) where rownum<11) a,(select addr,gets,misses,sleeps from v$latch_childr
en where name ='cache buffers chains') b where a.hladdr=b.addr) f where e.relat
ive_fno=f.dbarfil and e.block_id<=f.dbablk and e.block_id +e.blocks>f.dbablk
SQL> /

OWNER SEGMENT_NAME SEGMENT_TYPE
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

SYS JOB$ TABLE
SYS I_JOB_NEXT INDEX
SYS SYS_IOT_TOP_8802 INDEX

与 直接连接top 10 tch 的结果一样

1 SELECT distinct e.owner, e.segment_name, e.segment_type
2 FROM dba_extents e,
3 (SELECT *
4 FROM (SELECT addr, ts#, file#, dbarfil, dbablk, tch
5 FROM x$bh
6 ORDER BY tch DESC)
7 WHERE ROWNUM < 11) b
8 WHERE e.relative_fno = b.dbarfil
9 AND e.block_id <= b.dbablk
10* AND e.block_id + e.blocks > b.dbablk
SQL> /

OWNER SEGMENT_NAME SEGMENT_TYPE
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

SYS JOB$ TABLE
SYS I_JOB_NEXT INDEX
SYS SYS_IOT_TOP_8802 INDEX

最后抓SQL 加rule 这个hint的原因 :下面连接表比较多 避免 cbo下oracle自己尝试分析 要采用那种连接(产生执行计划 解析时间将很长)按rbo顺序模式连接

1* select /*+ rule*/ hash_value,sql_text from v$sqLAREA where (hash_value,addr
ess)in (select g.hash_value,g.address from v$sqlarea g ,(select distinct e.owner
,e.segment_name,e.segment_type from dba_extents e,(select b.addr,a.ts#,a.dbarfil
,a.dbablk,a.tch ,b.gets,b.misses,b.sleeps from (select * from (select addr,ts#,
file#,dbarfil,dbablk,tch,hladdr from x$bh order by tch desc ) where rownum<11) a
,(select addr,gets,misses,sleeps from v$latch_children where name ='cache buffer
s chains') b where a.hladdr=b.addr) f where e.relative_fno=f.dbarfil and e.bloc
k_id<=f.dbablk and e.block_id+e.blocks>f.dbablk)j where upper(g.sql_text) like '
%'||j.segment_name||'%' and j.segment_type='TABLE') ORDER BY HASH_VALUE,ADDRESS
SQL> /

HASH_VALUE
- - - - - - - - - -
SQL_TEXT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

85383888
select t.inst_id,t.addr,t.kslltnum,t.kslltcnm,n.kslldlvl, n.kslldnam,n.ks

lldhsh, t.kslltwgt,t.kslltwff,t.kslltwsl,t.kslltngt,t.kslltnfa, t.

kslltwkc,t.kslltwth,t.ksllthst0,t.ksllthst1, t.ksllthst2,t.ksllthst3,t.ks

llthst4,t.ksllthst5, t.ksllthst6,t.ksllthst7,t.ksllthst8, t.ksllth

st9,t.ksllthst10, t.ksllthst11, t.kslltwtt from x$ksllt t, x$kslld n wh

ere t.kslltcnm > 0 and t.kslltnum = n.indx

138190469

HASH_VALUE
- - - - - - - - - -
SQL_TEXT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

SELECT snap_id , SQL_ID FROM (SELECT /*+ ordered use_nl(t2) index(t2) */ t2.sn

ap_id , t1.SQLID_KEWRSIE SQL_ID FROM X$KEWRSQLIDTAB t1, WRH$_SQLTEXT t2 WH

ERE t2.dbid(+) = :dbid AND t2.SQL_ID(+) = t1.SQLID_KEWRSIE) WHERE nvl(snap_id,

0) < :snap_id

375665851
SELECT startsn.stat_id, x.keh_id, GREATEST( 0, (endsn.value - startsn.val

ue) ) as value_diff FROM (SELECT t1.* FROM WRH$_SYS_TIME_MODEL t1, WRM$_SNAPSHO

T s1 WHERE t1.dbid = s1.dbid AND t1.instance_number = s1.instance_number

HASH_VALUE
- - - - - - - - - -
SQL_TEXT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

AND t1.snap_id = s1.snap_id AND s1.bl_moved = 0 UNION ALL SELECT t2.* FROM
WRH$_SYS_TIME_MODEL_BL t2, WRM$_SNAPSHOT s2 WHERE t2.dbid = s2.dbid AND t2.in

stance_number = s2.instance_number AND t2.snap_id = s2.snap_id AND s2.bl_mo

ved <> 0) startsn , (SELECT t1.* FROM WRH$_SYS_TIME_MODEL t1, WRM$_SNAPSHOT s1
WHERE t1.dbid = s1.dbid AND t1.instance_number = s1.instance_number AND
t1.snap_id = s1.snap_id AND s1.bl_moved = 0 UNION ALL SELECT t2.* FROM WRH$_

SYS_TIME_MODEL_BL t2, WRM$_SNAPSHOT s2 WHERE t2.dbid = s2.dbid AND t2.instanc

e_number = s2.instance_number AND t2.snap_id = s2.snap_id AND s2.bl_moved <

0) endsn , X$KEHTIMMAP x WHERE startsn.snap_id = :begin_snap_id an

HASH_VALUE
- - - - - - - - - -
SQL_TEXT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

d endsn.snap_id = :end_snap_i

652170634
SELECT KEY_PART1, KEY_PART2, KEY_PART3, DECODE ( :B4 , KEY_PART1, 1, KEY_PART2,
2, KEY_PART3, 3, 0 ), DECODE ( :B3 , KEY_PART1, 1, KEY_PART2, 2, KEY_PART3, 3, 0

) FROM MGMT_BCN_AVAIL_DEF WHERE TARGET_GUID = :B2 AND METRIC_GUID = :B1

819358145
SELECT snap_id , SQL_ID FROM (SELECT /*+ ordered use_nl(t2) index(t2) */ t2.sn

HASH_VALUE
- - - - - - - - - -
SQL_TEXT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

ap_id , t1.SQLID_KEWRSIE SQL_ID FROM X$KEWRSQLIDTAB t1, WRH$_SQL_BIND_METADATA
t2 WHERE t2.dbid(+) = :dbid AND t2.SQL_ID(+) = t1.SQLID_KEWRSIE AND t2.PO

SITION(+) = 1) WHERE nvl(snap_id, 0) < :snap_id

979299602
select t1.inst_id,t1.ksllasnam, t2.ksllwnam, t1.kslnowtf, t1.kslsleep, t1.kslwsc

wsl, t1.kslwsclthg, t2.ksllwnam from x$ksllw t2, x$kslwsc t1 wh

ere t2.indx = t1.indx

HASH_VALUE
- - - - - - - - - -
SQL_TEXT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

1488414063
insert into wrh$_service_name (snap_id, dbid, service_name_hash, service_name)

select :lah_snap_id, :dbid, t2.name_hash, t2.name from x$kewrattrnew t1, v$

services t2 where t1.num1_kewrattr = t2.name_hash

1494863970
SELECT T2.TARGET_GUID FROM MGMT_TARGETS T1, MGMT_TARGETS T2 WHERE T1.TARGET_GUID

=:B2 AND T1.EMD_URL=T2.EMD_URL AND T2.TARGET_TYPE=:B1

HASH_VALUE
- - - - - - - - - -
SQL_TEXT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

2146304988
SELECT snap_id , SERVICE_NAME_HASH FROM (SELECT /*+ ordered use_nl(t2) index(t

2) */ t2.snap_id , t1.NAME_HASH SERVICE_NAME_HASH FROM V$SERVICES t1, WRH$_SERV

ICE_NAME t2 WHERE t2.dbid(+) = :dbid AND t2.SERVICE_NAME_HASH(+) = t1.NAM

E_HASH) WHERE nvl(snap_id, 0) < :snap_id

2298265060
declare begin for i in 1..100000 loop update t1 set b=i+3 where a=i; commit; end

loop; end;

HASH_VALUE
- - - - - - - - - -
SQL_TEXT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

3327029265
SELECT snap_id , SQL_ID, PLAN_HASH_VALUE FROM (SELECT /*+ ordered use_nl(t2) i

ndex(t2) */ t2.snap_id , t1.SQLID_KEWRSPE SQL_ID, t1.PLANHASH_KEWRSPE PLAN_HAS

H_VALUE FROM X$KEWRTSQLPLAN t1, WRH$_SQL_PLAN t2 WHERE t2.dbid(+) = :dbid
AND t2.SQL_ID(+) = t1.SQLID_KEWRSPE AND t2.PLAN_HASH_VALUE(+) = t1.PLANHASH_KEW

RSPE AND t2.ID(+) = 0) WHERE nvl(snap_id, 0) < :snap_id

3407318689

HASH_VALUE
- - - - - - - - - -
SQL_TEXT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

SELECT snap_id , OPTIMIZER_ENV_HASH_VALUE FROM (SELECT /*+ ordered use_nl(t2)
index(t2) */ t2.snap_id , t1.OPTENVHV_KEWROEE OPTIMIZER_ENV_HASH_VALUE FROM X$K

EWRTOPTENV t1, WRH$_OPTIMIZER_ENV t2 WHERE t2.dbid(+) = :dbid AND t2.OPTI

MIZER_ENV_HASH_VALUE(+) = t1.OPTENVHV_KEWROEE) WHERE nvl(snap_id, 0) < :snap_id

3554734773
select inst_id, kglnaobj, kglfnobj, kglobt03, kglobhs0+kglobhs1+kglobhs2+kglobhs

3+kglobhs4+kglobhs5+kglobhs6, kglobt08+kglobt11, kglobt10, kglobt01, kglobccc, k

globclc, kglhdlmd, kglhdlkc, kglobt04, kglobt05, kglobt48, kglobt35, kglobpc6, k

HASH_VALUE
- - - - - - - - - -
SQL_TEXT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

glhdldc, substr(to_char(kglnatim,'YYYY-MM-DD/HH24:MI:SS'),1,19), kglhdivc, kglob

t12, kglobt13, kglobwdw, kglobt14, kglobwap, kglobwcc, kglobwcl, kglobwui, kglob

t42, kglobt43, kglobt15, kglobt02, decode(kglobt32, 0, 'NONE',
1, 'ALL_ROWS', 2, 'FIRST_ROWS', 3, 'RULE',
4, 'CHOOSE', 'UNKNOWN'), kglobtn0, kglobcce, kglobcceh, kglobt17,
kglobt18, kglobts4, kglhdkmk, kglhdpar, kglnahsh, kglobt46, kglobt30, kglobts0,
kglobt19, kglobts1, kglobt20, kglobt21, kglobts2, kglobt06, kglobt07, decode(kgl

obt28, 0, NULL, kglobt28), kglhdadr, decode(bitand(kglobt00,64),64, 'Y', 'N'), d

ecode(kglobsta, 1, 'VALID', 2, 'VALID_AUTH_ERROR', 3, 'VALI

HASH_VALUE
- - - - - - - - - -
SQL_TEXT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

D_COMPILE_ERROR', 4, 'VALID_UNAUT

4058483781
select d.inst_id,d.kslldadr,la.latch#,d.kslldlvl,d.kslldnam,d.kslldhsh, l

a.gets,la.misses, la.sleeps,la.immediate_gets,la.immediate_misses,la.wait

ers_woken, la.waits_holding_latch,la.spin_gets,la.sleep1,la.sleep2,
la.sleep3,la.sleep4,la.sleep5,la.sleep6,la.sleep7,la.sleep8,la.sleep9,
la.sleep10, la.sleep11, la.wait_time from x$kslld d, (select kslltnum latch#

, sum(kslltwgt) gets,sum(kslltwff) misses,sum(kslltwsl) sleeps, su

HASH_VALUE
- - - - - - - - - -
SQL_TEXT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

m(kslltngt) immediate_gets,sum(kslltnfa) immediate_misses, sum(kslltwkc)
waiters_woken,sum(kslltwth) waits_holding_latch, sum(ksllthst0) spin_gets

,sum(ksllthst1) sleep1,sum(ksllthst2) sleep2, sum(ksllthst3) sleep3,sum(k

sllthst4) sleep4,sum(ksllthst5) sleep5, sum(ksllthst6) sleep6,sum(ksllths

t7) sleep7,sum(ksllthst8) sleep8, sum(ksllthst9) sleep9,sum(ksllthst10) s

leep10,sum(ksllthst11) sleep11, sum(kslltwtt) wait_time from x$ksllt g

roup by kslltnum) la where la.latch# =

已选择14行。

抓到sql后 就是优化了

See Also:
http://blog.csdn.net/lordcoohoo/article/details/8026861