我遇到一个奇怪的问题,发现在并发时执行很慢。贴上相关信息。这是在我一个session连接时操作的详细记录:
SQL> SELECT count(*)
2 FROM (select *
3 from v_rd_record t
4 where 1 = 1
5 AND (t.VEN_CODE like '%A1093%')
6 AND (t.ORG_ID = '2')) totalTable;已用时间: 00: 00: 17.07Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=42 Card=1 Bytes=188)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=42 Card=210 Bytes=39480)
3 2 HASH JOIN (Cost=42 Card=343 Bytes=62083)
4 3 TABLE ACCESS (FULL) OF 'SRM_VENDOR_ORG' (Cost=38 Car
d=567 Bytes=5670) 5 3 NESTED LOOPS (Cost=3 Card=343 Bytes=58653)
6 5 INDEX (UNIQUE SCAN) OF 'PK_SRM_SUBSIDIARY' (UNIQUE
) 7 5 TABLE ACCESS (BY INDEX ROWID) OF 'SRM_RDRECORD' (C
ost=2 Card=1 Bytes=168) 8 7 INDEX (RANGE SCAN) OF 'RD_SUB_FK' (NON-UNIQUE) (
Cost=1 Card=13726) 9 2 INDEX (UNIQUE SCAN) OF 'PK_SRM_VENDOR' (UNIQUE)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
69867 consistent gets
38749 physical reads
0 redo size
377 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed这是我在并发10-15个session以后的执行情况:
SQL> SELECT count(*)
2 FROM (select *
3 from v_rd_record t
4 where 1 = 1
5 AND (t.VEN_CODE like '%A1093%')
6 AND (t.ORG_ID = '2')) totalTable;已用时间: 00: 01: 11.01Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=42 Card=1 Bytes=188)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=42 Card=210 Bytes=39480)
3 2 HASH JOIN (Cost=42 Card=343 Bytes=62083)
4 3 TABLE ACCESS (FULL) OF 'SRM_VENDOR_ORG' (Cost=38 Car
d=567 Bytes=5670) 5 3 NESTED LOOPS (Cost=3 Card=343 Bytes=58653)
6 5 INDEX (UNIQUE SCAN) OF 'PK_SRM_SUBSIDIARY' (UNIQUE
) 7 5 TABLE ACCESS (BY INDEX ROWID) OF 'SRM_RDRECORD' (C
ost=2 Card=1 Bytes=168) 8 7 INDEX (RANGE SCAN) OF 'RD_SUB_FK' (NON-UNIQUE) (
Cost=1 Card=13726) 9 2 INDEX (UNIQUE SCAN) OF 'PK_SRM_VENDOR' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
69865 consistent gets
38820 physical reads
0 redo size
377 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed从执行情况我看不出来哪有什么问题啊,请各位达人帮帮我,急死了。
另外执行计划中出现了table full这样的现象正确吗?
SQL> SELECT count(*)
2 FROM (select *
3 from v_rd_record t
4 where 1 = 1
5 AND (t.VEN_CODE like '%A1093%')
6 AND (t.ORG_ID = '2')) totalTable;已用时间: 00: 00: 17.07Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=42 Card=1 Bytes=188)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=42 Card=210 Bytes=39480)
3 2 HASH JOIN (Cost=42 Card=343 Bytes=62083)
4 3 TABLE ACCESS (FULL) OF 'SRM_VENDOR_ORG' (Cost=38 Car
d=567 Bytes=5670) 5 3 NESTED LOOPS (Cost=3 Card=343 Bytes=58653)
6 5 INDEX (UNIQUE SCAN) OF 'PK_SRM_SUBSIDIARY' (UNIQUE
) 7 5 TABLE ACCESS (BY INDEX ROWID) OF 'SRM_RDRECORD' (C
ost=2 Card=1 Bytes=168) 8 7 INDEX (RANGE SCAN) OF 'RD_SUB_FK' (NON-UNIQUE) (
Cost=1 Card=13726) 9 2 INDEX (UNIQUE SCAN) OF 'PK_SRM_VENDOR' (UNIQUE)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
69867 consistent gets
38749 physical reads
0 redo size
377 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed这是我在并发10-15个session以后的执行情况:
SQL> SELECT count(*)
2 FROM (select *
3 from v_rd_record t
4 where 1 = 1
5 AND (t.VEN_CODE like '%A1093%')
6 AND (t.ORG_ID = '2')) totalTable;已用时间: 00: 01: 11.01Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=42 Card=1 Bytes=188)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=42 Card=210 Bytes=39480)
3 2 HASH JOIN (Cost=42 Card=343 Bytes=62083)
4 3 TABLE ACCESS (FULL) OF 'SRM_VENDOR_ORG' (Cost=38 Car
d=567 Bytes=5670) 5 3 NESTED LOOPS (Cost=3 Card=343 Bytes=58653)
6 5 INDEX (UNIQUE SCAN) OF 'PK_SRM_SUBSIDIARY' (UNIQUE
) 7 5 TABLE ACCESS (BY INDEX ROWID) OF 'SRM_RDRECORD' (C
ost=2 Card=1 Bytes=168) 8 7 INDEX (RANGE SCAN) OF 'RD_SUB_FK' (NON-UNIQUE) (
Cost=1 Card=13726) 9 2 INDEX (UNIQUE SCAN) OF 'PK_SRM_VENDOR' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
69865 consistent gets
38820 physical reads
0 redo size
377 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed从执行情况我看不出来哪有什么问题啊,请各位达人帮帮我,急死了。
另外执行计划中出现了table full这样的现象正确吗?
解决方案 »
- oracle有时候调皮 任何人都不认识
- 匿名语句块和存储过程从远程db下载数据的性能疑惑
- ORA-00933: SQL 命令未正确结束
- 想做oracle开发,推荐几本书吧
- 有用过 oracle sqldevloper 工具的吗
- oracle 分组累计问题 一点头绪没有 求大牛指点啊
- 初学ORACLE,大家帮忙看看这样一个问题。
- 怎样终止进程?急,在线等。。。
- UPDATE(DELETE) THE RECORD(S) DEPEND ON THE RECORD(S) OF OTHER TABLE.
- (300分)在ORACLE服务器端,定期把一文件夹下的不同文本文件(格式相同)导入表内,该如何做???
- 求一个存储过程
- oracle 查询替换问题
select *
from v_rd_record t
where t.VEN_CODE like '%A1093%'
AND t.ORG_ID = '2'你那個為啥還要套一層呢? 這樣也能滿足吧