oracle 执行计划乱,执行计划为何不稳定呢？

执行计划为何不稳定呢？

各位老大，能否给予指点呢？谢谢！

Q1：

consistent gets

能否代表SQL的优越性呢？

下边这两句话a/b，consistent gets a< consistent gets b

但在pl/sql developer中消耗的时间 a>b！

Q2：

如何维护表执行计划的一致性呢？

a句在ORACLE服务重起之后，居然选择了全表扫描！

表都分析过了！

我曾经选择添加first_rows提示，但是consistent gets 有80多W，似乎不优！(当时添加了rule获得的下边的执行计划consistent gets 有50多W)

Q3：我写的这些提示会不会有互相抵消而不起作用的呢？

谢谢大家了！

Select   /*+ rule

and_equal(a,IX_ENTERPRISE_EMPLOYEE,IX_ENTERPRISE_EMPLOYEE3,PKENTERPRISE_EMPLOYEE)

no_index(d) */a.FIdentityCard

from enterprise_employee a

where

a.FPersonTypeId in (select /*+ no_index(d) */d.fid from Sys_PersonType d where

d.FName in  ('工程技术人员','持证上岗人员','经理','技术负责人'))

and a.FIsDeleted=0 --

and a.FValidEnd ='9999-12-31 23:59:59' --1381653

and a.fbaseinfoid in

(select /*+ index (b,ix_enterprisebaseinfo_full)*/b.fid from Enterprise_BaseInfo

b

where b.FManagedeptId in (select /*+ index(c,IX_GOV_MANAGEDETP_FULL) */c.fid

from Gov_Managedept c where  c.FNumber like '00.13%'

and b.FValidEnd ='9999-12-31 23:59:59'))

/

Execution Plan

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=HINT: RULE (Cost=346 Card=1 Bytes

=169)

1    0   HASH JOIN (Cost=346 Card=1 Bytes=169)

2    1     NESTED LOOPS (Cost=344 Card=3 Bytes=369)

3    2       VIEW OF 'VW_NSO_1' (Cost=42 Card=2 Bytes=40)

4    3         SORT (UNIQUE) (Cost=42 Card=2 Bytes=252)

5    4           NESTED LOOPS (Cost=32 Card=2 Bytes=252)

6    5             INDEX (FULL SCAN) OF 'IX_ENTERPRISEBASEINFO_FULL

' (NON-UNIQUE) (Cost=26 Card=3 Bytes=237)

7    5             INDEX (RANGE SCAN) OF 'IX_GOV_MANAGEDETP_FULL' (

NON-UNIQUE) (Cost=2 Card=2 Bytes=94)

8    2       TABLE ACCESS (BY INDEX ROWID) OF 'ENTERPRISE_EMPLOYEE'

(Cost=151 Card=8653 Bytes=891259)

9    8         INDEX (RANGE SCAN) OF 'IX_ENTERPRISE_EMPLOYEE' (NON-

UNIQUE) (Cost=2 Card=8653)

10    1     TABLE ACCESS (FULL) OF 'SYS_PERSONTYPE' (Cost=1 Card=4 B

ytes=184)

Statistics

----------------------------------------------------------

64  recursive calls

4  db block gets

550682  consistent gets

4633  physical reads

0  redo size

11387039  bytes sent via SQL*Net to client

1367057  bytes received via SQL*Net from client

12314  SQL*Net roundtrips to/from client

12  sorts (memory)

0  sorts (disk)

184681  rows processed

Select /*+ rule */

ed1.FIdentityCard

from enterprise_employee ed1 where

ed1.FIsDeleted=0

and ed1.FPersonTypeId in ( Select FID from Sys_PersonType where FName in

('工程技术人员','持证上岗人员','经理','技术负责人'))

and ed1.FValidEnd ='9999-12-31 23:59:59'

and ed1.FBaseInfoId in ( Select  distinct FId  from Enterprise_BaseInfo

where FValidEnd=ed1.FValidEnd and FManagedeptId in (

Select FId from Gov_Managedept where FNumber like '00.13%'  ))

Execution Plan

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=12672 Card=173 Bytes

=25777)

1    0   FILTER

2    1     HASH JOIN (Cost=12672 Card=173 Bytes=25777)

3    2       TABLE ACCESS (FULL) OF 'SYS_PERSONTYPE' (Cost=1 Card=4

Bytes=184)

4    2       TABLE ACCESS (FULL) OF 'ENTERPRISE_EMPLOYEE' (Cost=12670 Card=433

Bytes=44599)

5    1     NESTED LOOPS (Cost=3 Card=1 Bytes=126)

6    5       INDEX (RANGE SCAN) OF 'IX_ENTERPRISEBASEINFO_FULL' (NO

N-UNIQUE) (Cost=2 Card=1 Bytes=79)

7    5       TABLE ACCESS (BY INDEX ROWID) OF 'GOV_MANAGEDEPT' (Cos

t=1 Card=2 Bytes=94)

8    7         INDEX (UNIQUE SCAN) OF 'PKGOV_MANAGEDEPT' (UNIQUE)

Statistics

----------------------------------------------------------

0  recursive calls

113  db block gets

5493911  consistent gets

1532  physical reads

0  redo size

6193720  bytes sent via SQL*Net to client

1367057  bytes received via SQL*Net from client

12314  SQL*Net roundtrips to/from client

8  sorts (memory)

0  sorts (disk)

184681  rows processed

SQL> /

已选择184681行。