匿名用户同一个sql执行时间不一样是什么原因？
请问一下 我有个sql 在同一个库（11g）同一个节点上执行 但是执行时间不一样 这是什么原因？执行计划是一样的
第一次ax4cxv5wn0yk5的统计信息-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 2,816,005 2,816,004.8 3.1
CPU Time (ms) 83,822 83,821.8 0.8
Executions 1 N/A N/A
Buffer Gets 1,531,468 1,531,468.0 0.8
Disk Reads 33,380 33,380.0 0.2
Parse Calls 1 1.0 0.0
Rows 0 0.0 N/A
User I/O Wait Time (ms) 1,253,965 N/A N/A
Cluster Wait Time (ms) 1,480,417 N/A N/A
Application Wait Time (ms) 0 N/A N/A
Concurrency Wait Time (ms) 27,551 N/A N/A
Invalidations 0 N/A N/A
Version Count 2 N/A N/A
Sharable Mem(KB) 1,292 N/A N/A
-------------------------------------------------------------第二次0rv4v0b3qp750的统计信息
Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 715,447 715,446.6 0.8
CPU Time (ms) 85,927 85,927.0 0.8
Executions 1 N/A N/A
Buffer Gets 1,531,441 1,531,441.0 0.8
Disk Reads 168,178 168,178.0 0.9
Parse Calls 1 1.0 0.0
Rows 0 0.0 N/A
User I/O Wait Time (ms) 257,910 N/A N/A
Cluster Wait Time (ms) 389,502 N/A N/A
Application Wait Time (ms) 0 N/A N/A
Concurrency Wait Time (ms) 19,923 N/A N/A
Invalidations 0 N/A N/A
Version Count 2 N/A N/A
Sharable Mem(KB) 1,292 N/A N/A
-------------------------------------------------------------两次执行计划一模一样
Execution Plan
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9385 (100)| |
| 1 | SORT ORDER BY | | 214 | 47936 | 9385 (2)| 00:01:53 |
| 2 | HASH JOIN | | 214 | 47936 | 9384 (2)| 00:01:53 |
| 3 | VIEW | DBA_OBJECTS | 2 | 218 | 6 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | FILTER | | | | | |
| 6 | NESTED LOOPS | | 1 | 106 | 6 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 89 | 5 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 67 | 4 (0)| 00:00:01 |
| 9 | INDEX RANGE SCAN | I_OBJ4 | 1 | | 3 (0)| 00:00:01 |
| 10 | INDEX RANGE SCAN | I_USER2 | 1 | 22 | 1 (0)| 00:00:01 |
| 11 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
| 12 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| |
| 13 | NESTED LOOPS | | 1 | 33 | 3 (0)| 00:00:01 |
| 14 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
| 15 | INDEX RANGE SCAN | I_OBJ4 | 1 | 13 | 2 (0)| 00:00:01 |
| 16 | FILTER | | | | | |
| 17 | NESTED LOOPS | | 1 | 33 | 2 (0)| 00:00:01 |
| 18 | INDEX SKIP SCAN | I_LINK1 | 1 | 16 | 1 (0)| 00:00:01 |
| 19 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
| 20 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| |
| 21 | VIEW | DBA_EXTENTS | 214 | 24610 | 9378 (2)| 00:01:53 |
| 22 | UNION-ALL | | | | | |
| 23 | NESTED LOOPS | | 1 | 174 | 85 (0)| 00:00:02 |
| 24 | NESTED LOOPS | | 1 | 85 | 23 (0)| 00:00:01 |
| 25 | TABLE ACCESS FULL | UET$ | 1 | 78 | 23 (0)| 00:00:01 |
| 26 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | 0 (0)| |
| 27 | VIEW | SYS_DBA_SEGS | 1 | 89 | 62 (0)| 00:00:01 |
| 28 | UNION ALL PUSHED PREDICATE | | | | | |
| 29 | NESTED LOOPS | | 1 | 117 | 49 (0)| 00:00:01 |
| 30 | FILTER | | | | | |
| 31 | NESTED LOOPS OUTER | | 1 | 102 | 39 (0)| 00:00:01 |
| 32 | MERGE JOIN CARTESIAN | | 1 | 85 | 38 (0)| 00:00:01 |
| 33 | NESTED LOOPS | | 1 | 32 | 4 (0)| 00:00:01 |
| 34 | NESTED LOOPS | | 1 | 10 | 2 (0)| 00:00:01 |
| 35 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | 1 (0)| 00:00:01 |
| 36 | TABLE ACCESS CLUSTER | TS$ | 1 | 3 | 1 (0)| 00:00:01 |
| 37 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| |
| 38 | TABLE ACCESS CLUSTER | SEG$ | 1 | 22 | 2 (0)| 00:00:01 |
| 39 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 1 (0)| 00:00:01 |
| 40 | BUFFER SORT | | 12 | 636 | 36 (0)| 00:00:01 |
| 41 | INDEX SKIP SCAN | I_OBJ2 | 12 | 636 | 34 (0)| 00:00:01 |
| 42 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
| 43 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| |
| 44 | VIEW | SYS_OBJECTS | 1 | 15 | 10 (0)| 00:00:01 |
| 45 | UNION ALL PUSHED PREDICATE | | | | | |
| 46 | TABLE ACCESS CLUSTER | TAB$ | 1 | 24 | 2 (0)| 00:00:01 |
| 47 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 |
| 48 | TABLE ACCESS BY INDEX ROWID | TABPART$ | 1 | 18 | 2 (0)| 00:00:01 |
| 49 | INDEX UNIQUE SCAN | I_TABPART_OBJ$ | 1 | | 1 (0)| 00:00:01 |
| 50 | FILTER | | | | | |
| 51 | TABLE ACCESS CLUSTER | CLU$ | 1 | 14 | 2 (0)| 00:00:01 |
| 52 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 |
| 53 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 19 | 2 (0)| 00:00:01 |
| 54 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |
| 55 | FILTER | | | | | |
| 56 | TABLE ACCESS BY INDEX ROWID | INDPART$ | 1 | 19 | 2 (0)| 00:00:01 |
| 57 | INDEX UNIQUE SCAN | I_INDPART_OBJ$ | 1 | | 1 (0)| 00:00:01 |
| 58 | FILTER | | | | | |
| 59 | TABLE ACCESS BY INDEX ROWID | LOB$ | 1 | 20 | 2 (0)| 00:00:01 |
| 60 | INDEX UNIQUE SCAN | I_LOB2 | 1 | | 1 (0)| 00:00:01 |
| 61 | TABLE ACCESS BY INDEX ROWID | TABSUBPART$ | 1 | 18 | 3 (0)| 00:00:01 |
| 62 | INDEX UNIQUE SCAN | I_TABSUBPART$_OBJ$ | 1 | | 2 (0)| 00:00:01 |
| 63 | FILTER | | | | | |
| 64 | TABLE ACCESS BY INDEX ROWID | INDSUBPART$ | 1 | 18 | 3 (0)| 00:00:01 |
| 65 | INDEX UNIQUE SCAN | I_INDSUBPART_OBJ$ | 1 | | 2 (0)| 00:00:01 |
| 66 | TABLE ACCESS BY INDEX ROWID | LOBFRAG$ | 1 | 17 | 1 (0)| 00:00:01 |
| 67 | INDEX UNIQUE SCAN | I_LOBFRAG$_FRAGOBJ$ | 1 | | 0 (0)| |
| 68 | FILTER | | | | | |
| 69 | NESTED LOOPS OUTER | | 1 | 91 | 7 (0)| 00:00:01 |
| 70 | NESTED LOOPS | | 1 | 74 | 6 (0)| 00:00:01 |
| 71 | NESTED LOOPS | | 1 | 49 | 4 (0)| 00:00:01 |
| 72 | NESTED LOOPS | | 1 | 10 | 2 (0)| 00:00:01 |
| 73 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | 1 (0)| 00:00:01 |
| 74 | TABLE ACCESS CLUSTER | TS$ | 1 | 3 | 1 (0)| 00:00:01 |
| 75 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| |
| 76 | TABLE ACCESS BY INDEX ROWID | UNDO$ | 1 | 39 | 2 (0)| 00:00:01 |
| 77 | INDEX RANGE SCAN | I_UNDO2 | 1 | | 1 (0)| 00:00:01 |
| 78 | TABLE ACCESS CLUSTER | SEG$ | 1 | 25 | 2 (0)| 00:00:01 |
| 79 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 1 (0)| 00:00:01 |
| 80 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
| 81 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| |
| 82 | FILTER | | | | | |
| 83 | NESTED LOOPS OUTER | | 1 | 56 | 6 (0)| 00:00:01 |
| 84 | NESTED LOOPS | | 1 | 39 | 5 (0)| 00:00:01 |
| 85 | NESTED LOOPS | | 1 | 14 | 3 (0)| 00:00:01 |
| 86 | TABLE ACCESS BY INDEX ROWID | FILE$ | 1 | 11 | 2 (0)| 00:00:01 |
| 87 | INDEX UNIQUE SCAN | I_FILE2 | 1 | | 1 (0)| 00:00:01 |
| 88 | TABLE ACCESS CLUSTER | TS$ | 1 | 3 | 1 (0)| 00:00:01 |
| 89 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| |
| 90 | TABLE ACCESS CLUSTER | SEG$ | 1 | 25 | 2 (0)| 00:00:01 |
| 91 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 1 (0)| 00:00:01 |
| 92 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
| 93 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| |
| 94 | NESTED LOOPS | | 213 | 37062 | 9293 (2)| 00:01:52 |
| 95 | HASH JOIN | | 2220 | 362K| 9292 (2)| 00:01:52 |
| 96 | VIEW | SYS_DBA_SEGS | 891 | 79299 | 9249 (1)| 00:01:51 |
| 97 | UNION-ALL | | | | | |
| 98 | NESTED LOOPS | | 889 | 101K| 6329 (2)| 00:01:16 |
| 99 | HASH JOIN | | 889 | 97790 | 6329 (2)| 00:01:16 |
| 100 | TABLE ACCESS FULL | TS$ | 10 | 30 | 5 (0)| 00:00:01 |
| 101 | HASH JOIN | | 889 | 95123 | 6324 (2)| 00:01:16 |
| 102 | TABLE ACCESS FULL | SEG$ | 63 | 1386 | 6276 (2)| 00:01:16 |
| 103 | NESTED LOOPS | | 4452 | 369K| 47 (0)| 00:00:01 |
| 104 | FILTER | | | | | |
| 105 | HASH JOIN OUTER | | 1 | 70 | 37 (0)| 00:00:01 |
| 106 | INDEX SKIP SCAN | I_OBJ2 | 12 | 636 | 34 (0)| 00:00:01 |
| 107 | TABLE ACCESS CLUSTER | USER$ | 104 | 1768 | 3 (0)| 00:00:01 |
| 108 | INDEX FULL SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 109 | VIEW | SYS_OBJECTS | 1 | 15 | 10 (0)| 00:00:01 |
| 110 | UNION ALL PUSHED PREDICATE | | | | | |
| 111 | TABLE ACCESS CLUSTER | TAB$ | 1 | 24 | 2 (0)| 00:00:01 |
| 112 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 |
| 113 | TABLE ACCESS BY INDEX ROWID | TABPART$ | 1 | 18 | 2 (0)| 00:00:01 |
| 114 | INDEX UNIQUE SCAN | I_TABPART_OBJ$ | 1 | | 1 (0)| 00:00:01 |
| 115 | FILTER | | | | | |
| 116 | TABLE ACCESS CLUSTER | CLU$ | 1 | 14 | 2 (0)| 00:00:01 |
| 117 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 |
| 118 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 19 | 2 (0)| 00:00:01 |
| 119 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |
| 120 | FILTER | | | | | |
| 121 | TABLE ACCESS BY INDEX ROWID| INDPART$ | 1 | 19 | 2 (0)| 00:00:01 |
| 122 | INDEX UNIQUE SCAN | I_INDPART_OBJ$ | 1 | | 1 (0)| 00:00:01 |
| 123 | FILTER | | | | | |
| 124 | TABLE ACCESS BY INDEX ROWID| LOB$ | 1 | 20 | 2 (0)| 00:00:01 |
| 125 | INDEX UNIQUE SCAN | I_LOB2 | 1 | | 1 (0)| 00:00:01 |
| 126 | TABLE ACCESS BY INDEX ROWID | TABSUBPART$ | 1 | 18 | 3 (0)| 00:00:01 |
| 127 | INDEX UNIQUE SCAN | I_TABSUBPART$_OBJ$ | 1 | | 2 (0)| 00:00:01 |
| 128 | FILTER | | | | | |
| 129 | TABLE ACCESS BY INDEX ROWID| INDSUBPART$ | 1 | 18 | 3 (0)| 00:00:01 |
| 130 | INDEX UNIQUE SCAN | I_INDSUBPART_OBJ$ | 1 | | 2 (0)| 00:00:01 |
| 131 | TABLE ACCESS BY INDEX ROWID | LOBFRAG$ | 1 | 17 | 1 (0)| 00:00:01 |
| 132 | INDEX UNIQUE SCAN | I_LOBFRAG$_FRAGOBJ$ | 1 | | 0 (0)| |
| 133 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | 0 (0)| |
| 134 | FILTER | | | | | |
| 135 | NESTED LOOPS OUTER | | 1 | 91 | 6 (0)| 00:00:01 |
| 136 | NESTED LOOPS | | 1 | 74 | 5 (0)| 00:00:01 |
| 137 | NESTED LOOPS | | 1 | 71 | 4 (0)| 00:00:01 |
| 138 | NESTED LOOPS | | 1 | 46 | 2 (0)| 00:00:01 |
| 139 | TABLE ACCESS BY INDEX ROWID | UNDO$ | 1 | 39 | 2 (0)| 00:00:01 |
| 140 | INDEX RANGE SCAN | I_UNDO2 | 1 | | 1 (0)| 00:00:01 |
| 141 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | 0 (0)| |
| 142 | TABLE ACCESS CLUSTER | SEG$ | 1 | 25 | 2 (0)| 00:00:01 |
| 143 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 1 (0)| 00:00:01 |
| 144 | TABLE ACCESS CLUSTER | TS$ | 1 | 3 | 1 (0)| 00:00:01 |
| 145 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| |
| 146 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
| 147 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| |
| 148 | FILTER | | | | | |
| 149 | NESTED LOOPS OUTER | | 1 | 56 | 2914 (1)| 00:00:35 |
| 150 | NESTED LOOPS | | 1 | 39 | 2913 (1)| 00:00:35 |
| 151 | NESTED LOOPS | | 1 | 36 | 2912 (1)| 00:00:35 |
| 152 | TABLE ACCESS FULL | FILE$ | 1023 | 11253 | 4 (0)| 00:00:01 |
| 153 | TABLE ACCESS CLUSTER | SEG$ | 1 | 25 | 3 (0)| 00:00:01 |
| 154 | INDEX RANGE SCAN | I_FILE#_BLOCK# | 1 | | 2 (0)| 00:00:01 |
| 155 | TABLE ACCESS CLUSTER | TS$ | 1 | 3 | 1 (0)| 00:00:01 |
| 156 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| |
| 157 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
| 158 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| |
| 159 | FIXED TABLE FULL | X$KTFBUE | 100K| 7617K| 43 (100)| 00:00:01 |
| 160 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | 0 (0)| |
------------------------------------------------------------------------------------------------------------------
我来答
添加附件
收藏
分享
问题补充
1条回答
默认
最新
跑个sql monitor看看时间差距在哪一步，以及等待事件是什么。从以上内容看起来，第一次执行ax4cxv5wn0yk5，有较长的User I/O Wait Time和Cluster Wait Time，存在rac两节点的数据块传输等待。
评论
有用 8回答交流
提交
问题信息
请登录之后查看
邀请回答
暂无人订阅该标签，敬请期待~~
墨值悬赏