現象如下：我在OA生産庫上執行如下語句，要3分鐘左右猜出結果，但是我在其他生産庫上執行結果幾乎是秒出，會是什麼原因？
我擔心的原因，如果哪天出現session阻塞，那我豈不是要完蛋，阻塞session查不出來，kill不掉，到時估計就隻能重新開機應用或者資料庫了（PS：之前就出現過一次，不過當時并未将注意力放在這個上面）
SQL> select sid,lmode,type,request,block from v$lock where type in ('TX','TM') order by 1,3;
SID LMODE TY REQUEST BLOCK
---------- ---------- -- ---------- ----------
9 6 TX 0 2
634 6 TX 0 2
1899 6 TX 0 2
2847 6 TX 0 2
3155 6 TX 0 2
3313 6 TX 0 2
3461 6 TX 0 2
5186 6 TX 0 2
5360 6 TX 0 2
6764 6 TX 0 2
8186 3 TM 0 2
8186 6 TX 0 2
9107 6 TX 0 2
13 rows selected.
這結果出來要3分多鐘
分析下語句的執行計劃
SQL> set linesize 150;
Execution Plan
----------------------------------------------------------
Plan hash value: 3010872788
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 105 | 2 (100)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 105 | 2 (100)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 105 | 1 (100)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN | | 1 | 54 | 0 (0)| 00:00:01 |
|* 4 | FIXED TABLE FULL | X$KSUSE | 1 | 32 | 0 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 1 | 22 | 0 (0)| 00:00:01 |
|* 6 | FIXED TABLE FULL | X$KSQRS | 1 | 22 | 0 (0)| 00:00:01 |
| 7 | VIEW | GV$_LOCK | 10 | 510 | 0 (0)| 00:00:01 |
| 8 | UNION-ALL | | | | | |
|* 9 | FILTER | | | | | |
| 10 | VIEW | GV$_LOCK1 | 2 | 102 | 0 (0)| 00:00:01 |
| 11 | UNION-ALL | | | | | |
|* 12 | FIXED TABLE FULL| X$KDNSSF | 1 | 77 | 0 (0)| 00:00:01 |
|* 13 | FIXED TABLE FULL| X$KSQEQ | 1 | 77 | 0 (0)| 00:00:01 |
|* 14 | FIXED TABLE FULL | X$KTADM | 1 | 77 | 0 (0)| 00:00:01 |
|* 15 | FIXED TABLE FULL | X$KTATRFIL | 1 | 77 | 0 (0)| 00:00:01 |
|* 16 | FIXED TABLE FULL | X$KTATRFSL | 1 | 77 | 0 (0)| 00:00:01 |
|* 17 | FIXED TABLE FULL | X$KTATL | 1 | 77 | 0 (0)| 00:00:01 |
|* 18 | FIXED TABLE FULL | X$KTSTUSC | 1 | 77 | 0 (0)| 00:00:01 |
|* 19 | FIXED TABLE FULL | X$KTSTUSS | 1 | 77 | 0 (0)| 00:00:01 |
|* 20 | FIXED TABLE FULL | X$KTSTUSG | 1 | 77 | 0 (0)| 00:00:01 |
|* 21 | FIXED TABLE FULL | X$KTCXB | 1 | 77 | 0 (0)| 00:00:01 |
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SADDR"="S"."ADDR" AND TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("R
ADDR")=TO_CHAR("R"."INST_ID")||RAWTOHEX("R"."ADDR"))
4 - filter("S"."INST_ID"=USERENV('INSTANCE'))
6 - filter("R"."KSQRSIDT"='TM' OR "R"."KSQRSIDT"='TX')
9 - filter(USERENV('INSTANCE') IS NOT NULL)
12 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
"INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
13 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
14 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
15 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
16 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
17 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
18 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
19 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
20 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
21 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
"INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0)
做level12的10046事件
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> select sid,lmode,type,request,block from v$lock where type in ('TX','TM') order by 1,3;
SID LMODE TY REQUEST BLOCK
---------- ---------- -- ---------- ----------
634 6 TX 0 2
2847 6 TX 0 2
3461 6 TX 0 2
3631 6 TM 0 2
5186 6 TX 0 2
5829 6 TX 0 2
6458 6 TX 0 2
9426 6 TX 0 2
8 rows selected.
SQL> alter session set events '10046 trace name context off';
tkprof出來的trace檔案
TKPROF: Release 11.2.0.3.0 - Development on Fri Mar 1 13:54:30 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: oaprod1_ora_16214.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SQL ID: c4ghdvdm6vbfv Plan Hash: 3010872788
select sid,lmode,type,request,block
from
v$lock where type in ('TX','TM') order by 1,3
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 310.83 402.58 952800 0 8 8
total 4 310.86 402.60 952800 0 8 8
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
8 8 8 SORT ORDER BY (cr=0 pr=952800 pw=952800 time=402582277 us cost=2 size=105 card=1)
8 8 8 HASH JOIN (cr=0 pr=952800 pw=952800 time=382540704 us cost=1 size=105 card=1)
134180992 134180992 134180992 MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=107803810 us cost=0 size=54 card=1)
10048 10048 10048 FIXED TABLE FULL X$KSUSE (cr=0 pr=0 pw=0 time=145542 us cost=0 size=32 card=1)
134180992 134180992 134180992 BUFFER SORT (cr=0 pr=0 pw=0 time=51017140 us cost=0 size=22 card=1)
13354 13354 13354 FIXED TABLE FULL X$KSQRS (cr=0 pr=0 pw=0 time=26869 us cost=0 size=22 card=1)
480 480 480 VIEW GV$_LOCK (cr=0 pr=0 pw=0 time=18205 us cost=0 size=510 card=10)
480 480 480 UNION-ALL (cr=0 pr=0 pw=0 time=18081 us)
472 472 472 FILTER (cr=0 pr=0 pw=0 time=17267 us)
472 472 472 VIEW GV$_LOCK1 (cr=0 pr=0 pw=0 time=17139 us cost=0 size=102 card=2)
472 472 472 UNION-ALL (cr=0 pr=0 pw=0 time=16784 us)
0 0 0 FIXED TABLE FULL X$KDNSSF (cr=0 pr=0 pw=0 time=12111 us cost=0 size=77 card=1)
472 472 472 FIXED TABLE FULL X$KSQEQ (cr=0 pr=0 pw=0 time=4068 us cost=0 size=77 card=1)
0 0 0 FIXED TABLE FULL X$KTADM (cr=0 pr=0 pw=0 time=108012 us cost=0 size=77 card=1)
0 0 0 FIXED TABLE FULL X$KTATRFIL (cr=0 pr=0 pw=0 time=29 us cost=0 size=77 card=1)
0 0 0 FIXED TABLE FULL X$KTATRFSL (cr=0 pr=0 pw=0 time=12 us cost=0 size=77 card=1)
0 0 0 FIXED TABLE FULL X$KTATL (cr=0 pr=0 pw=0 time=150 us cost=0 size=77 card=1)
0 0 0 FIXED TABLE FULL X$KTSTUSC (cr=0 pr=0 pw=0 time=452 us cost=0 size=77 card=1)
0 0 0 FIXED TABLE FULL X$KTSTUSS (cr=0 pr=0 pw=0 time=172 us cost=0 size=77 card=1)
0 0 0 FIXED TABLE FULL X$KTSTUSG (cr=0 pr=0 pw=0 time=169 us cost=0 size=77 card=1)
8 8 8 FIXED TABLE FULL X$KTCXB (cr=0 pr=0 pw=0 time=63711 us cost=0 size=77 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
asynch descriptor resize 174 0.00 0.00
Disk file operations I/O 1 0.00 0.00
CSS initialization 2 0.02 0.02
CSS operation: action 2 0.01 0.02
CSS operation: query 6 0.00 0.00
direct path write temp 30839 0.51 29.56
direct path read temp 63516 0.26 64.82
SQL*Net message from client 2 80.46 80.46
SQL ID: 06nvwn223659v Plan Hash: 0
alter session set events '10046 trace name context off'
Parse 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
Parse 2 0.02 0.02 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
total 6 310.86 402.60 952800 0 8 8
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 80.46 102.99
![基于同軸芳綸/聚乳酸長絲的連續纖維增強複合材料三維列印前言：增材制造技術（AdditiveManufacturing）使[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)