SQL性能问题

XueChao

Oracle version： 10.2.0.4

现象：SQL执行慢 需要 8秒

执行SQL：
  
select /*+ dynamic_sampling(kd 10) dynamic_sampling_est_cdn(kd) */  all count(kd.r_object_id) "cnt"
  from km_document_sp kd
where (((kd.approve_state = 1) or (kd.approve_state = 4)) and
       (kd.r_object_type = 'km_document'))
   and (kd.i_has_folder = 1 and kd.i_is_deleted = 0)
   and ((kd.owner_name in ('fjdlkm_admin',
                           'kmg90054746',
                           'kmg99001011',
                           'kmg90054760',
                           'dm_world',
                           'km_120f1f0e8020ac54',
                           'km_group',
                           'kmg90055413')) or
       (exists
        (select /*+ dynamic_sampling(ACL_S0 10) dynamic_sampling_est_cdn(ACL_S0) dynamic_sampling(ACL_R 10) dynamic_sampling_est_cdn(ACL_R) */ 1
            from dm_acl_s ACL_S0, dm_acl_r ACL_R
           where ACL_S0.r_object_id = ACL_R.r_object_id
             and kd.acl_domain = ACL_S0.owner_name
             and kd.acl_name = ACL_S0.object_name
             and ((ACL_R.r_accessor_name in ('fjdlkm_admin', 'dm_world') or
                 (ACL_R.r_is_group = 1 and
                 (ACL_R.r_accessor_name in
                 ('fjdlkm_admin',
                      'kmg90054746',
                      'kmg99001011',
                      'kmg90054760',
                      'dm_world',
                      'km_120f1f0e8020ac54',
                      'km_group',
                      'kmg90055413')))) and
                 ((ACL_R.r_permit_type = 0 or ACL_R.r_permit_type is null) and
                 (((ACL_R.r_accessor_permit >= 2))))))))

执行计划：

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3781511774

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

| Id  | Operation                       | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                    |     1 |    97 |     |  4332   (1)| 00:00:52 |
|   1 |  SORT AGGREGATE                 |                    |     1 |    97 |     |            |          |
|*  2 |   FILTER                        |                    |       |       |     |            |          |
|*  3 |    HASH JOIN                    |                    |   326K|    30M|  10M|  4332   (1)| 00:00:52 |
|   4 |     INLIST ITERATOR             |                    |       |       |     |            |          |
|   5 |      TABLE ACCESS BY INDEX ROWID| KM_DOCUMENT_S      |   332K|  6490K|     |   427   (1)| 00:00:06 |
|*  6 |       INDEX RANGE SCAN          | D_1F0F1F0E80002508 |   332K|       |     |     3  (34)| 00:00:01 |
|*  7 |     TABLE ACCESS BY INDEX ROWID | DM_SYSOBJECT_S     |   324K|    23M|     |  2023   (1)| 00:00:25 |
|*  8 |      INDEX RANGE SCAN           | D_1F0F1F0E80002902 |   324K|       |     |    64   (0)| 00:00:01 |
|*  9 |    TABLE ACCESS BY INDEX ROWID  | DM_ACL_R           |     1 |    29 |     |     1   (0)| 00:00:01 |
|  10 |     NESTED LOOPS                |                    |     1 |    76 |     |     2   (0)| 00:00:01 |
|* 11 |      INDEX RANGE SCAN           | D_1F0F1F0E80000104 |     1 |    47 |     |     1   (0)| 00:00:01 |
|* 12 |      INDEX RANGE SCAN           | D_1F0F1F0E80000102 |     4 |       |     |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------

   2 - filter("XB_"."OWNER_NAME"='dm_world' OR "XB_"."OWNER_NAME"='fjdlkm_admin' OR
              "XB_"."OWNER_NAME"='km_120f1f0e8020ac54' OR "XB_"."OWNER_NAME"='km_group' OR
              "XB_"."OWNER_NAME"='kmg90054746' OR "XB_"."OWNER_NAME"='kmg90054760' OR
              "XB_"."OWNER_NAME"='kmg90055413' OR "XB_"."OWNER_NAME"='kmg99001011' OR  EXISTS (SELECT /*+ */ 0 FROM
              "DM_ACL_R" "ACL_R","DM_ACL_S" "ACL_S0" WHERE "ACL_S0"."OBJECT_NAME"=:B1 AND "ACL_S0"."OWNER_NAME"=:B2
              AND "ACL_S0"."R_OBJECT_ID"="ACL_R"."R_OBJECT_ID" AND "ACL_R"."R_ACCESSOR_PERMIT">=2 AND
              (("ACL_R"."R_ACCESSOR_NAME"='dm_world' OR "ACL_R"."R_ACCESSOR_NAME"='fjdlkm_admin') OR
              "ACL_R"."R_IS_GROUP"=1 AND ("ACL_R"."R_ACCESSOR_NAME"='dm_world' OR
              "ACL_R"."R_ACCESSOR_NAME"='fjdlkm_admin' OR "ACL_R"."R_ACCESSOR_NAME"='km_120f1f0e8020ac54' OR
              "ACL_R"."R_ACCESSOR_NAME"='km_group' OR "ACL_R"."R_ACCESSOR_NAME"='kmg90054746' OR
              "ACL_R"."R_ACCESSOR_NAME"='kmg90054760' OR "ACL_R"."R_ACCESSOR_NAME"='kmg90055413' OR
              "ACL_R"."R_ACCESSOR_NAME"='kmg99001011')) AND ("ACL_R"."R_PERMIT_TYPE"=0 OR "ACL_R"."R_PERMIT_TYPE" IS NULL)))
   3 - access("XB_"."R_OBJECT_ID"="EOB_"."R_OBJECT_ID")
   6 - access("EOB_"."APPROVE_STATE"=1 OR "EOB_"."APPROVE_STATE"=4)
   7 - filter("XB_"."I_HAS_FOLDER"=1 AND "XB_"."I_IS_DELETED"=0)
   8 - access("XB_"."R_OBJECT_TYPE"='km_document')
   9 - filter("ACL_R"."R_ACCESSOR_PERMIT">=2 AND (("ACL_R"."R_ACCESSOR_NAME"='dm
              "ACL_R"."R_ACCESSOR_NAME"='fjdlkm_admin') OR "ACL_R"."R_IS_GROUP"=1 AND
              ("ACL_R"."R_ACCESSOR_NAME"='dm_world' OR "ACL_R"."R_ACCESSOR_NAME"='fjdlkm_admin' OR
              "ACL_R"."R_ACCESSOR_NAME"='km_120f1f0e8020ac54' OR "ACL_R"."R_ACCESSOR_NAME"='km_group' OR
              "ACL_R"."R_ACCESSOR_NAME"='kmg90054746' OR "ACL_R"."R_ACCESSOR_NAME"='kmg90054760' OR
              "ACL_R"."R_ACCESSOR_NAME"='kmg90055413' OR "ACL_R"."R_ACCESSOR_NAME"='kmg99001011')) AND
              ("ACL_R"."R_PERMIT_TYPE"=0 OR "ACL_R"."R_PERMIT_TYPE" IS NULL))
  11 - access("ACL_S0"."OWNER_NAME"=:B1 AND "ACL_S0"."OBJECT_NAME"=:B2)
  12 - access("ACL_S0"."R_OBJECT_ID"="ACL_R"."R_OBJECT_ID")

Note

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic sampling used for this statement

补充：

kd   50万条左右
ACL_S0 50万条左右
ACL_R 50万条左右

Maclean Liu(刘相兵

1. 代码请用code模式，否则格式会乱点
2. 你执行计划都贴了，为啥statistics却不贴？

XueChao

Maclean Liu(刘相兵 发表于 2013-1-13 14:31
1. 代码请用code模式，否则格式会乱点
2. 你执行计划都贴了，为啥statistics却不贴？ ...

1. Execution Plan
   
2. ----------------------------------------------------------
   
3.    0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=4332 Card=1 Bytes=97)
   
4. 
   
5.    1    0   SORT (AGGREGATE)
   
6.    2    1     FILTER
   
7.    3    2       HASH JOIN (Cost=4332 Card=326821 Bytes=31701637)
   
8.    4    3         INLIST ITERATOR
   
9.    5    4           TABLE ACCESS (BY INDEX ROWID) OF 'KM_DOCUMENT_S' (TABLE) (Cost=427 Card=332307 Bytes=6646140)
   
10.    6    5             INDEX (RANGE SCAN) OF 'D_1F0F1F0E80002508' (INDEX) (Cost=3 Card=332307)
    
11.    7    3         TABLE ACCESS (BY INDEX ROWID) OF 'DM_SYSOBJECT_S' (TABLE) (Cost=2023 Card=324500 Bytes=24986500)
    
12.    8    7           INDEX (RANGE SCAN) OF 'D_1F0F1F0E80002902' (INDEX) (Cost=64 Card=324877)
    
13.    9    2       TABLE ACCESS (BY INDEX ROWID) OF 'DM_ACL_R' (TABLE) (Cost=1 Card=1 Bytes=29)
    
14.   10    9         NESTED LOOPS (Cost=2 Card=1 Bytes=76)
    
15.   11   10           INDEX (RANGE SCAN) OF 'D_1F0F1F0E80000104' (INDEX(UNIQUE)) (Cost=1 Card=1 Bytes=47)
    
16.   12   10           INDEX (RANGE SCAN) OF 'D_1F0F1F0E80000102' (INDEX(UNIQUE)) (Cost=1 Card=4)
    
17. 
    
18. 
    
19. 
    
20. 
    
21. 
    
22. Statistics
    
23. ----------------------------------------------------------
    
24.           0  recursive calls
    
25.           0  db block gets
    
26.     1030143  consistent gets
    
27.           0  physical reads
    
28.           0  redo size
    
29.         227  bytes sent via SQL*Net to client
    
30.         278  bytes received via SQL*Net from client
    
31.           2  SQL*Net roundtrips to/from client
    
32.           0  sorts (memory)
    
33.           0  sorts (disk)
    
34.           1  rows processed

复制代码

Maclean Liu(刘相兵

2次贴的执行计划不一样， 也没有任何文字说明。

不知道你想做什么， 如果真心求助何必如此不负责任。