一个简单select的执行计划，谁帮我看看

显示全部楼层 · 2013-2-25 14:51:24

15:40:28 SQL> select table_name from dba_tables;

17765 rows selected.
Elapsed: 00:00:30.08
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=2175 Card=173 Bytes=16781)
10 HASH JOIN (Cost=2175 Card=173 Bytes=16781)
21 TABLE ACCESS (FULL) OF 'TS$' (Cost=60 Card=384 Bytes=1536)
31 HASH JOIN (Cost=2114 Card=173 Bytes=16089)
43 TABLE ACCESS (FULL) OF 'USER$' (Cost=4 Card=234 Bytes=936)
53 HASH JOIN (OUTER) (Cost=2109 Card=173 Bytes=15397)
65 NESTED LOOPS (OUTER) (Cost=2104 Card=173 Bytes=14705)
76 NESTED LOOPS (OUTER) (Cost=1931 Card=173 Bytes=12456)
87
NESTED LOOPS (OUTER) (Cost=1758 Card=173 Bytes=10899)
98
NESTED LOOPS (Cost=1758 Card=173 Bytes=10034)
109
TABLE ACCESS (FULL) OF 'TAB$' (Cost=1585 Card=173 Bytes=4671)
119
TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (Cost=1 Card=1 Bytes=31)
12 11
INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
138
INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
147
TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (Cost=1 Card=1 Bytes=9)
15 14
INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
166 TABLE ACCESS (CLUSTER) OF 'SEG$' (Cost=1 Card=1 Bytes=13)
17 16
INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE)
185 TABLE ACCESS (FULL) OF 'USER$' (Cost=4 Card=234 Bytes=936)
Statistics
----------------------------------------------------------

7recursive calls

0db block gets
116595consistent gets
11127physical reads

0redo size
601845bytes sent via SQL*Net to client
13679bytes received via SQL*Net from client
1186SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)
17765rows processed

竟然要30多秒

千问 · 2013-2-25 14:51:24

看看能不能对'TAB$' 走索引，不行的话用HINT吧

千问 · 2013-2-25 14:51:24

你的系统表空间不干净，碎片肯定很多
要不怎么会
116595 consistent gets
11127 physical reads

千问 · 2013-2-25 14:51:24

什么系统？Oracle什么版本？

千问 · 2013-2-25 14:51:24

我们oracle8.1.6的执行计划如下：
Operation
Object Name
Rows
Bytes
Cost
Object Node
In/Out
PStart
PStop
SELECT STATEMENT Optimizer Mode=CHOOSE

NESTED LOOPS

NESTED LOOPS

NESTED LOOPS OUTER

NESTED LOOPS OUTER

NESTED LOOPS

TABLE ACCESS FULL
SYS.OBJ$

TABLE ACCESS CLUSTER
SYS.TAB$

INDEX UNIQUE SCAN
SYS.I_OBJ#

INDEX UNIQUE SCAN
SYS.I_OBJ1

TABLE ACCESS CLUSTER
SYS.SEG$

INDEX UNIQUE SCAN
SYS.I_FILE#_BLOCK#

TABLE ACCESS CLUSTER
SYS.TS$

INDEX UNIQUE SCAN
SYS.I_TS#

TABLE ACCESS CLUSTER
SYS.USER$

INDEX UNIQUE SCAN
SYS.I_USER#

你们是不是对系统表有做分析了？

千问 · 2013-2-25 14:51:24

竟然要30多秒包括显示花的时间吗？

千问 · 2013-2-25 14:51:24

SQL> select table_name from dba_tables;
1387 rows selected.
Elapsed: 00:00:00.85
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=60 Card=980 Bytes=11

5640)
10 NESTED LOOPS (OUTER) (Cost=60 Card=980 Bytes=115640)
21 NESTED LOOPS (OUTER) (Cost=60 Card=980 Bytes=77420)
32 HASH JOIN (OUTER) (Cost=60 Card=980 Bytes=74480)
43 NESTED LOOPS (Cost=43 Card=980 Bytes=68600)
54 HASH JOIN (Cost=43 Card=980 Bytes=65660)
65
NESTED LOOPS (OUTER) (Cost=26 Card=980 Bytes=372

40)
76
NESTED LOOPS (Cost=26 Card=980 Bytes=33320)
87
TABLE ACCESS (FULL) OF 'TAB$' (Cost=26 Card=

12 Bytes=252)
97
TABLE ACCESS (CLUSTER) OF 'TS$'
109
INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE

)
116
INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
125
TABLE ACCESS (FULL) OF 'OBJ$' (Cost=16 Card=2126

4 Bytes=616656)
134 TABLE ACCESS (CLUSTER) OF 'USER$'
14 13
INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
153 TABLE ACCESS (FULL) OF 'OBJ$' (Cost=16 Card=21264 By

tes=127584)
162 TABLE ACCESS (CLUSTER) OF 'USER$'
17 16 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
181 TABLE ACCESS (CLUSTER) OF 'SEG$'
19 18 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE)

Statistics
----------------------------------------------------------

7recursive calls
18db block gets
9793consistent gets
978physical reads

0redo size
42840bytes sent via SQL*Net to client
10638bytes received via SQL*Net from client
94SQL*Net roundtrips to/from client

8sorts (memory)

0sorts (disk)
1387rows processed

千问 · 2013-2-25 14:51:24

ERP，数据库是9.2.0.4的

千问 · 2013-2-25 14:51:24

最初由 mychary 发布
[B]你的系统表空间不干净，碎片肯定很多
要不怎么会
116595 consistent gets
11127 physical reads [/B]

select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name

TABLESPACE_NAME
COUNT(TABLESPACE_NAME)
------------------------------ ----------------------
SYSTEM
11
有没可能是db_buffer 的问题，我重复执行一条命令，
physical reads 的数量几乎没有大的减少，理想状况应该是零才对

千问 · 2013-2-25 14:51:24

select count(tablespace_name) from dba_free_space
where tablespace_name = 'SYSTEM'
执行结果是多少？