首页 » ORACLE » SQL join problem caused by Low cardinality with Histogram(柱状图产生的过低Card.导致表join性能问题)(一)

SQL join problem caused by Low cardinality with Histogram(柱状图产生的过低Card.导致表join性能问题)(一)

最近一直在优化一些问题SQL, 今天有时间整理一个前几天遇到的问题, 开发的同事问我一个运行很好的SQL中的一个小表上加了一个条件查询突然慢了好几倍,数据库是11.2.0.3, 下面是原SQL

  SELECT COUNT (1),
         o2.org_name,
         em_exam.name,
         '结业考试' AS exam_type_name
    FROM em_exam,
         em_examinee,
         em_user_rct,
         zyy_org o1,
         zyy_org o2
   WHERE     o2.id IN ('100039411')
         AND o1.ORG_CODE LIKE o2.ORG_CODE || '%'
         AND SUBSTR (em_user_rct.org_id, 4, LENGTH (em_user_rct.org_id)) =
                o1.id
         AND em_exam.id = 444
         AND em_exam.id = em_examinee.exam_id
         AND em_examinee.em_user_id = em_user_rct.id
         AND em_examinee.join_course_count != 0
--and em_examinee.is_course_qual = 2               --# 不带这个条件
GROUP BY o2.org_name, em_exam.name                          
ORDER BY o2.org_name, em_exam.name;

NOTE:
在没有加em_examinee.is_course_qual条件情况下这条运行影响时间还可以,我们看一下正常时的执行计划

Execution Plan
----------------------------------------------------------
Plan hash value: 1925057754

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                          |     1 |   139 |   106   (2)| 00:00:02 |
|   1 |  HASH GROUP BY                        |                          |     1 |   139 |   106   (2)| 00:00:02 |
|   2 |   NESTED LOOPS                        |                          |       |       |            |          |
|   3 |    NESTED LOOPS                       |                          |     1 |   139 |   106   (2)| 00:00:02 |
|   4 |     NESTED LOOPS                      |                          |     1 |   119 |   105   (2)| 00:00:02 |
|*  5 |      HASH JOIN OUTER                  |                          |     1 |   110 |   105   (2)| 00:00:02 |
|   6 |       NESTED LOOPS                    |                          |       |       |            |          |
|   7 |        NESTED LOOPS                   |                          |     1 |   100 |    84   (2)| 00:00:02 |
|*  8 |         HASH JOIN                     |                          |     1 |    91 |    83   (2)| 00:00:02 |
|   9 |          NESTED LOOPS                 |                          |     1 |    67 |    11   (0)| 00:00:01 |
|  10 |           NESTED LOOPS                |                          |     1 |    51 |     3   (0)| 00:00:01 |
|  11 |            TABLE ACCESS BY INDEX ROWID| ZYY_ORG                  |     1 |    34 |     2   (0)| 00:00:01 |
|* 12 |             INDEX UNIQUE SCAN         | PK_ZYY_ORG               |     1 |       |     1   (0)| 00:00:01 |
|  13 |            TABLE ACCESS BY INDEX ROWID| EM_EXAM                  |     1 |    17 |     1   (0)| 00:00:01 |
|* 14 |             INDEX UNIQUE SCAN         | PK_EM_EXAM               |     1 |       |     0   (0)| 00:00:01 |
|* 15 |           TABLE ACCESS BY INDEX ROWID | EM_EXAMINEE              |     1 |    16 |     8   (0)| 00:00:01 |
|* 16 |            INDEX RANGE SCAN           | EM_EXAMINEE_UK1          |    33 |       |     1   (0)| 00:00:01 |
|* 17 |          TABLE ACCESS FULL            | ZYY_USER                 | 38281 |   897K|    72   (2)| 00:00:01 |
|* 18 |         INDEX UNIQUE SCAN             | PK_ZYY_ORG               |     1 |       |     0   (0)| 00:00:01 |
|* 19 |        TABLE ACCESS BY INDEX ROWID    | ZYY_ORG                  |     1 |     9 |     1   (0)| 00:00:01 |
|  20 |       INDEX FAST FULL SCAN            | IDX_ZYY_BASE_RESIDENCY   | 15307 |   149K|    20   (0)| 00:00:01 |
|* 21 |      INDEX UNIQUE SCAN                | IDX_ZYY_SECURITY_ACCOUNT |     1 |     9 |     0   (0)| 00:00:01 |
|* 22 |     INDEX UNIQUE SCAN                 | PK_ZYY_ORG               |     1 |       |     0   (0)| 00:00:01 |
|* 23 |    TABLE ACCESS BY INDEX ROWID        | ZYY_ORG                  |     1 |    20 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("ZBR"."RESIDENCY_ID"(+)="U"."ID")
   8 - access("EM_EXAMINEE"."EM_USER_ID"='rct'||TO_CHAR("U"."ID"))
  12 - access("O2"."ID"=100039411)
  14 - access("EM_EXAM"."ID"=444)
  15 - filter("EM_EXAMINEE"."JOIN_COURSE_COUNT"<>0)
  16 - access("EM_EXAMINEE"."EXAM_ID"=444)
  17 - filter("U"."ZYY_USER_STATUS"<>(-2))
  18 - access("U"."ZYY_USER_ORG_ID"="ORG"."ID")
  19 - filter("ORG"."STATUS"=1)
  21 - access("AC"."ACCOUNT_NAME"="U"."ACCOUNT_NAME")
  22 - access("O1"."ID"=TO_NUMBER(SUBSTR('rct'||TO_CHAR("U"."ZYY_USER_ORG_ID"),4,LENGTH('rct'||TO_CHAR("U"
              ."ZYY_USER_ORG_ID")))))
  23 - filter("O1"."ORG_CODE" LIKE "O2"."ORG_CODE"||'%')

Statistics
----------------------------------------------------------
          8  recursive calls
          1  db block gets
        483  consistent gets
          0  physical reads
          0  redo size
        548  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

NOTE:
虽然有FTS ,但总体CR 也就不到500块次还可以,下面我们加上那个where 条件后再看一下执行计划,此处把上面SQL 的注释已去掉

----------------------------------------------------------
Plan hash value: 3651727838
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                          |     1 |   142 |    61   (2)| 00:00:01 |
|   1 |  HASH GROUP BY                        |                          |     1 |   142 |    61   (2)| 00:00:01 |
|   2 |   NESTED LOOPS                        |                          |       |       |            |          |
|   3 |    NESTED LOOPS                       |                          |     1 |   142 |    61   (2)| 00:00:01 |
|*  4 |     HASH JOIN OUTER                   |                          |     1 |   122 |    60   (2)| 00:00:01 |
|   5 |      NESTED LOOPS                     |                          |       |       |            |          |
|   6 |       NESTED LOOPS                    |                          |     1 |   112 |    39   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                   |                          |     1 |   103 |    38   (0)| 00:00:01 |
|   8 |         MERGE JOIN CARTESIAN          |                          |     1 |    79 |    37   (0)| 00:00:01 |
|   9 |          NESTED LOOPS                 |                          |     1 |    70 |    11   (0)| 00:00:01 |
|  10 |           NESTED LOOPS                |                          |     1 |    51 |     3   (0)| 00:00:01 |
|  11 |            TABLE ACCESS BY INDEX ROWID| ZYY_ORG                  |     1 |    34 |     2   (0)| 00:00:01 |
|* 12 |             INDEX UNIQUE SCAN         | PK_ZYY_ORG               |     1 |       |     1   (0)| 00:00:01 |
|  13 |            TABLE ACCESS BY INDEX ROWID| EM_EXAM                  |     1 |    17 |     1   (0)| 00:00:01 |
|* 14 |             INDEX UNIQUE SCAN         | PK_EM_EXAM               |     1 |       |     0   (0)| 00:00:01 |
|* 15 |           TABLE ACCESS BY INDEX ROWID | EM_EXAMINEE              |     1 |    19 |     8   (0)| 00:00:01 |
|* 16 |            INDEX RANGE SCAN           | EM_EXAMINEE_UK1          |    33 |       |     1   (0)| 00:00:01 |
|  17 |          BUFFER SORT                  |                          | 40350 |   354K|    29   (0)| 00:00:01 |
|  18 |           INDEX FAST FULL SCAN        | IDX_ZYY_SECURITY_ACCOUNT | 40350 |   354K|    26   (0)| 00:00:01 |
|* 19 |         TABLE ACCESS BY INDEX ROWID   | ZYY_USER                 |     1 |    24 |     1   (0)| 00:00:01 |
|* 20 |          INDEX UNIQUE SCAN            | IDX_ZYY_USER2            |     1 |       |     0   (0)| 00:00:01 |
|* 21 |        INDEX UNIQUE SCAN              | PK_ZYY_ORG               |     1 |       |     0   (0)| 00:00:01 |
|* 22 |       TABLE ACCESS BY INDEX ROWID     | ZYY_ORG                  |     1 |     9 |     1   (0)| 00:00:01 |
|  23 |      INDEX FAST FULL SCAN             | IDX_ZYY_BASE_RESIDENCY   | 15307 |   149K|    20   (0)| 00:00:01 |
|* 24 |     INDEX UNIQUE SCAN                 | PK_ZYY_ORG               |     1 |       |     0   (0)| 00:00:01 |
|* 25 |    TABLE ACCESS BY INDEX ROWID        | ZYY_ORG                  |     1 |    20 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("ZBR"."RESIDENCY_ID"(+)="U"."ID")
  12 - access("O2"."ID"=100039411)
  14 - access("EM_EXAM"."ID"=444)
  15 - filter("EM_EXAMINEE"."IS_COURSE_QUAL"=2 AND "EM_EXAMINEE"."JOIN_COURSE_COUNT"<>0)
  16 - access("EM_EXAMINEE"."EXAM_ID"=444)
  19 - filter("U"."ZYY_USER_STATUS"<>(-2) AND "EM_EXAMINEE"."EM_USER_ID"='rct'||TO_CHAR("U"."ID"))
  20 - access("AC"."ACCOUNT_NAME"="U"."ACCOUNT_NAME")
  21 - access("U"."ZYY_USER_ORG_ID"="ORG"."ID")
  22 - filter("ORG"."STATUS"=1)
  24 - access("O1"."ID"=TO_NUMBER(SUBSTR('rct'||TO_CHAR("U"."ZYY_USER_ORG_ID"),4,LENGTH('rct'||TO_CHAR("U"
              ."ZYY_USER_ORG_ID")))))
  25 - filter("O1"."ORG_CODE" LIKE "O2"."ORG_CODE"||'%')

Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
     561997  consistent gets
         91  physical reads
          0  redo size
        548  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

NOTE:
加上了那个条件后产生了一个有笛卡尔积的执行计划,并且CR升到了56万,响应时间也变慢许多,这种在并发执行的情况下会更加明显。这时我们尝试加上gather_plan_statistics hint分析一下估算与实际的差值,确认是否有错误的Card或统计信息,select /*+ gather_plan_statistics */完整SQL不再展示,
下面是执行计划

app_zyyjhj@DEVDB> select * from table(dbms_xplan.display_cursor('8k5sr8d8wpdzj',0,'MEMSTATS LAST'));

Plan hash value: 3651727838

---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                     | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                          |      1 |        |      0 |00:00:07.68 |       |       |          |
|   1 |  HASH GROUP BY                        |                          |      1 |      1 |      0 |00:00:07.68 |   737K|   737K|          |
|   2 |   NESTED LOOPS                        |                          |      1 |        |      0 |00:00:07.68 |       |       |          |
|   3 |    NESTED LOOPS                       |                          |      1 |      1 |     14 |00:00:07.68 |       |       |          |
|*  4 |     HASH JOIN OUTER                   |                          |      1 |      1 |     14 |00:00:07.68 |   814K|   814K| 1131K (0)|
|   5 |      NESTED LOOPS                     |                          |      1 |        |     14 |00:00:07.01 |       |       |          |
|   6 |       NESTED LOOPS                    |                          |      1 |      1 |     14 |00:00:07.01 |       |       |          |
|   7 |        NESTED LOOPS                   |                          |      1 |      1 |     14 |00:00:07.01 |       |       |          |
|   8 |         MERGE JOIN CARTESIAN          |                          |      1 |      1 |    564K|00:00:01.48 |       |       |          |
|   9 |          NESTED LOOPS                 |                          |      1 |      1 |     14 |00:00:00.01 |       |       |          |
|  10 |           NESTED LOOPS                |                          |      1 |      1 |      1 |00:00:00.01 |       |       |          |
|  11 |            TABLE ACCESS BY INDEX ROWID| ZYY_ORG                  |      1 |      1 |      1 |00:00:00.01 |       |       |          |
|* 12 |             INDEX UNIQUE SCAN         | PK_ZYY_ORG               |      1 |      1 |      1 |00:00:00.01 |       |       |          |
|  13 |            TABLE ACCESS BY INDEX ROWID| EM_EXAM                  |      1 |      1 |      1 |00:00:00.01 |       |       |          |
|* 14 |             INDEX UNIQUE SCAN         | PK_EM_EXAM               |      1 |      1 |      1 |00:00:00.01 |       |       |          |
|* 15 |           TABLE ACCESS BY INDEX ROWID | EM_EXAMINEE              |      1 |      1 |     14 |00:00:00.01 |       |       |          |
|* 16 |            INDEX RANGE SCAN           | EM_EXAMINEE_UK1          |      1 |     33 |     22 |00:00:00.01 |       |       |          |
|  17 |          BUFFER SORT                  |                          |     14 |  40350 |    564K|00:00:00.64 |  1753K|   641K| 1558K (0)|
|  18 |           INDEX FAST FULL SCAN        | IDX_ZYY_SECURITY_ACCOUNT |      1 |  40350 |  40350 |00:00:00.03 |       |       |          |
|* 19 |         TABLE ACCESS BY INDEX ROWID   | ZYY_USER                 |    564K|      1 |     14 |00:00:06.22 |       |       |          |
|* 20 |          INDEX UNIQUE SCAN            | IDX_ZYY_USER2            |    564K|      1 |    550K|00:00:02.07 |       |       |          |
|* 21 |        INDEX UNIQUE SCAN              | PK_ZYY_ORG               |     14 |      1 |     14 |00:00:00.01 |       |       |          |
|* 22 |       TABLE ACCESS BY INDEX ROWID     | ZYY_ORG                  |     14 |      1 |     14 |00:00:00.01 |       |       |          |
|  23 |      INDEX FAST FULL SCAN             | IDX_ZYY_BASE_RESIDENCY   |      1 |  15307 |  15512 |00:00:00.02 |       |       |          |
|* 24 |     INDEX UNIQUE SCAN                 | PK_ZYY_ORG               |     14 |      1 |     14 |00:00:00.01 |       |       |          |
|* 25 |    TABLE ACCESS BY INDEX ROWID        | ZYY_ORG                  |     14 |      1 |      0 |00:00:00.01 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("ZBR"."RESIDENCY_ID"="U"."ID")
  12 - access("O2"."ID"=100039411)
  14 - access("EM_EXAM"."ID"=444)
  15 - filter(("EM_EXAMINEE"."IS_COURSE_QUAL"=2 AND "EM_EXAMINEE"."JOIN_COURSE_COUNT"<>0))
  16 - access("EM_EXAMINEE"."EXAM_ID"=444)
  19 - filter(("U"."ZYY_USER_STATUS"<>(-2) AND "EM_EXAMINEE"."EM_USER_ID"='rct'||TO_CHAR("U"."ID")))
  20 - access("AC"."ACCOUNT_NAME"="U"."ACCOUNT_NAME")
  21 - access("U"."ZYY_USER_ORG_ID"="ORG"."ID")
  22 - filter("ORG"."STATUS"=1)
  24 - access("O1"."ID"=TO_NUMBER(SUBSTR('rct'||TO_CHAR("U"."ZYY_USER_ORG_ID"),4,LENGTH('rct'||TO_CHAR("U"."ZYY_USER_ORG_ID")))))
  25 - filter("O1"."ORG_CODE" LIKE "O2"."ORG_CODE"||'%')

NOTE:
当CBO产生的Cardinality过低,尤其是在表之间关连时,往往会差之毫厘差之千里,甚至影响表之间的join方式,注意下MERGE JOIN CARTESIAN部分,这里使用了一种NL style的MJ,这点大师JL在他的blog中这样描述”Despite this optimisation, the arithmetic for the cost still seems to be the traditional nested loop calculation: cost of 1st rowsource + (cardinality of 1st rowsource * cost of 2nd rowsource).”从上面的执行计划我们可以验证(11+1*26)=37 Card, 另外注意有”buffer sort”, 这是在9i引入 Cartesian join,但它不是一个真正的Sort.
现在我们回来再说执行计划,注意到加条件的表EM_EXAMINEE 实际是14,而CBO是估算1,因为是1所以才选择了MERGE JOIN CARTESIAN 这是问题的根源,找到了问题,解决起来就容易了,先尝试用SELECT /*+cardinality(em_examinee 14)*/Hint, 下面是产生了新的执行计划

 

Execution Plan
----------------------------------------------------------
Plan hash value: 2180356062

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                          |     1 |   142 |   113   (3)| 00:00:02 |
|   1 |  HASH GROUP BY                       |                          |     1 |   142 |   113   (3)| 00:00:02 |
|   2 |   NESTED LOOPS                       |                          |     1 |   142 |   113   (3)| 00:00:02 |
|*  3 |    HASH JOIN OUTER                   |                          |     1 |   133 |   113   (3)| 00:00:02 |
|   4 |     NESTED LOOPS                     |                          |       |       |            |          |
|   5 |      NESTED LOOPS                    |                          |     1 |   123 |    92   (3)| 00:00:02 |
|*  6 |       HASH JOIN                      |                          |     1 |   114 |    91   (3)| 00:00:02 |
|*  7 |        HASH JOIN                     |                          |    39 |  3666 |    83   (2)| 00:00:02 |
|   8 |         NESTED LOOPS                 |                          |    14 |   980 |    11   (0)| 00:00:01 |
|   9 |          NESTED LOOPS                |                          |     1 |    51 |     3   (0)| 00:00:01 |
|  10 |           TABLE ACCESS BY INDEX ROWID| ZYY_ORG                  |     1 |    34 |     2   (0)| 00:00:01 |
|* 11 |            INDEX UNIQUE SCAN         | PK_ZYY_ORG               |     1 |       |     1   (0)| 00:00:01 |
|  12 |           TABLE ACCESS BY INDEX ROWID| EM_EXAM                  |     1 |    17 |     1   (0)| 00:00:01 |
|* 13 |            INDEX UNIQUE SCAN         | PK_EM_EXAM               |     1 |       |     0   (0)| 00:00:01 |
|* 14 |          TABLE ACCESS BY INDEX ROWID | EM_EXAMINEE              |    14 |   266 |     8   (0)| 00:00:01 |
|* 15 |           INDEX RANGE SCAN           | EM_EXAMINEE_UK1          |    33 |       |     1   (0)| 00:00:01 |
|* 16 |         TABLE ACCESS FULL            | ZYY_USER                 | 38281 |   897K|    72   (2)| 00:00:01 |
|  17 |        TABLE ACCESS FULL             | ZYY_ORG                  |   932 | 18640 |     7   (0)| 00:00:01 |
|* 18 |       INDEX UNIQUE SCAN              | PK_ZYY_ORG               |     1 |       |     0   (0)| 00:00:01 |
|* 19 |      TABLE ACCESS BY INDEX ROWID     | ZYY_ORG                  |     1 |     9 |     1   (0)| 00:00:01 |
|  20 |     INDEX FAST FULL SCAN             | IDX_ZYY_BASE_RESIDENCY   | 15307 |   149K|    20   (0)| 00:00:01 |
|* 21 |    INDEX UNIQUE SCAN                 | IDX_ZYY_SECURITY_ACCOUNT |     1 |     9 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ZBR"."RESIDENCY_ID"(+)="U"."ID")
   6 - access("O1"."ID"=TO_NUMBER(SUBSTR('rct'||TO_CHAR("U"."ZYY_USER_ORG_ID"),4,LENGTH('rct'||TO_CHAR("U
              "."ZYY_USER_ORG_ID")))))
       filter("O1"."ORG_CODE" LIKE "O2"."ORG_CODE"||'%')
   7 - access("EM_EXAMINEE"."EM_USER_ID"='rct'||TO_CHAR("U"."ID"))
  11 - access("O2"."ID"=100039411)
  13 - access("EM_EXAM"."ID"=444)
  14 - filter("EM_EXAMINEE"."IS_COURSE_QUAL"=2 AND "EM_EXAMINEE"."JOIN_COURSE_COUNT"<>0)
  15 - access("EM_EXAMINEE"."EXAM_ID"=444)
  16 - filter("U"."ZYY_USER_STATUS"<>(-2))
  18 - access("U"."ZYY_USER_ORG_ID"="ORG"."ID")
  19 - filter("ORG"."STATUS"=1)
  21 - access("AC"."ACCOUNT_NAME"="U"."ACCOUNT_NAME")

Statistics
----------------------------------------------------------
          8  recursive calls
          1  db block gets
        363  consistent gets
          0  physical reads
          0  redo size
        548  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Note:
貌似效果良好,但是我可能修改应用,更不可能在where条件变动的情况下hint的Card永远最合适,所以再接着使用其它方法优化。如果我们在系统中禁用MERGE JOIN CARTESIAN会怎么样呢? 尝试使用隐藏参数在会话级修改。

tip:
“Disabling Cartesian joins in Oracle 10g r1 only

Oracle 10g saw some shops suffering from unnecessary Cartesian joins. The hidden parameter _optimizer_mjc_enabled stands for “merge join cartesian” and _optimizer_mjc_enabled was first introduced in Oracle 10g to disable cartesian join operations within SQL execution plans, with an alter system or alter session statement, like this:

alter session set “_optimizer_mjc_enabled”=false ;

Disabling Cartesian joins in Oracle 10g r2 and beyond

Oracle 10g release 2 also saw the introduction of the _optimizer_cartesian_enabled parameter. Internally, setting _optimizer_cartesian_enabled=false will cause the optimizer to disallow cartesian joins and produce a nested loops or hash join in lieu of a merge join cartesian (MJC) execution plan.

alter session set “_optimizer_mjc_enabled”=false ;
alter session set “_optimizer_cartesian_enabled “=false ;

 

app_zyyjhj@DEVDB>alter session set "_optimizer_mjc_enabled"=false;
Session altered.

app_zyyjhj@DEVDB>alter session set "_optimizer_cartesian_enabled"=false ;
Session altered.

# run  same sql

Execution Plan
----------------------------------------------------------
Plan hash value: 4199682508

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                          |     1 |   142 |    61   (2)| 00:00:01 |
|   1 |  HASH GROUP BY                        |                          |     1 |   142 |    61   (2)| 00:00:01 |
|   2 |   NESTED LOOPS                        |                          |       |       |            |          |
|   3 |    NESTED LOOPS                       |                          |     1 |   142 |    61   (2)| 00:00:01 |
|*  4 |     HASH JOIN OUTER                   |                          |     1 |   122 |    60   (2)| 00:00:01 |
|   5 |      NESTED LOOPS                     |                          |       |       |            |          |
|   6 |       NESTED LOOPS                    |                          |     1 |   112 |    39   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                   |                          |     1 |   103 |    38   (0)| 00:00:01 |
|   8 |         NESTED LOOPS                  |                          |     1 |    79 |    37   (0)| 00:00:01 |
|   9 |          NESTED LOOPS                 |                          |     1 |    70 |    11   (0)| 00:00:01 |
|  10 |           NESTED LOOPS                |                          |     1 |    51 |     3   (0)| 00:00:01 |
|  11 |            TABLE ACCESS BY INDEX ROWID| ZYY_ORG                  |     1 |    34 |     2   (0)| 00:00:01 |
|* 12 |             INDEX UNIQUE SCAN         | PK_ZYY_ORG               |     1 |       |     1   (0)| 00:00:01 |
|  13 |            TABLE ACCESS BY INDEX ROWID| EM_EXAM                  |     1 |    17 |     1   (0)| 00:00:01 |
|* 14 |             INDEX UNIQUE SCAN         | PK_EM_EXAM               |     1 |       |     0   (0)| 00:00:01 |
|* 15 |           TABLE ACCESS BY INDEX ROWID | EM_EXAMINEE              |     1 |    19 |     8   (0)| 00:00:01 |
|* 16 |            INDEX RANGE SCAN           | EM_EXAMINEE_UK1          |    33 |       |     1   (0)| 00:00:01 |
|  17 |          INDEX FAST FULL SCAN         | IDX_ZYY_SECURITY_ACCOUNT | 40350 |   354K|    26   (0)| 00:00:01 |
|* 18 |         TABLE ACCESS BY INDEX ROWID   | ZYY_USER                 |     1 |    24 |     1   (0)| 00:00:01 |
|* 19 |          INDEX UNIQUE SCAN            | IDX_ZYY_USER2            |     1 |       |     0   (0)| 00:00:01 |
|* 20 |        INDEX UNIQUE SCAN              | PK_ZYY_ORG               |     1 |       |     0   (0)| 00:00:01 |
|* 21 |       TABLE ACCESS BY INDEX ROWID     | ZYY_ORG                  |     1 |     9 |     1   (0)| 00:00:01 |
|  22 |      INDEX FAST FULL SCAN             | IDX_ZYY_BASE_RESIDENCY   | 15307 |   149K|    20   (0)| 00:00:01 |
|* 23 |     INDEX UNIQUE SCAN                 | PK_ZYY_ORG               |     1 |       |     0   (0)| 00:00:01 |
|* 24 |    TABLE ACCESS BY INDEX ROWID        | ZYY_ORG                  |     1 |    20 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("ZBR"."RESIDENCY_ID"(+)="U"."ID")
  12 - access("O2"."ID"=100039411)
  14 - access("EM_EXAM"."ID"=444)
  15 - filter("EM_EXAMINEE"."IS_COURSE_QUAL"=2 AND "EM_EXAMINEE"."JOIN_COURSE_COUNT"<>0)
  16 - access("EM_EXAMINEE"."EXAM_ID"=444)
  18 - filter("U"."ZYY_USER_STATUS"<>(-2) AND "EM_EXAMINEE"."EM_USER_ID"='rct'||TO_CHAR("U"."ID"))
  19 - access("AC"."ACCOUNT_NAME"="U"."ACCOUNT_NAME")
  20 - access("U"."ZYY_USER_ORG_ID"="ORG"."ID")
  21 - filter("ORG"."STATUS"=1)
  23 - access("O1"."ID"=TO_NUMBER(SUBSTR('rct'||TO_CHAR("U"."ZYY_USER_ORG_ID"),4,LENGTH('rct'||TO_CHAR("U"
              ."ZYY_USER_ORG_ID")))))
  24 - filter("O1"."ORG_CODE" LIKE "O2"."ORG_CODE"||'%')


Statistics
----------------------------------------------------------
         10  recursive calls
          0  db block gets
     563511  consistent gets
          0  physical reads
          0  redo size
        548  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

NOTE:
产生新的执行计划非常糟糕, 不可取, 在哪些情况下会产生笛卡尔积呢?

– Missing table join condition to WHERE clause – As for the cartesian merge join, first, carefully check your WHERE clause to ensure that you have provided the proper join conditions (e.g. where a.primary_key= b.foreign_key).
– Optimizer bugs (see _optimizer_transitivity_retain
– Missing join indexes
– Bad/stale schema statistics (reanalyze with dbms_stats)

现在我们可以尝试使用ordered Hint看下效果,这个hint的作用不太本篇讨论范围,下面是执行计划

Execution Plan
----------------------------------------------------------
Plan hash value: 3055956805

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                          |     1 |   142 |   105   (2)| 00:00:02 |
|   1 |  HASH GROUP BY                        |                          |     1 |   142 |   105   (2)| 00:00:02 |
|   2 |   NESTED LOOPS                        |                          |       |       |            |          |
|   3 |    NESTED LOOPS                       |                          |     1 |   142 |   105   (2)| 00:00:02 |
|   4 |     NESTED LOOPS                      |                          |     1 |   108 |   104   (2)| 00:00:02 |
|*  5 |      HASH JOIN OUTER                  |                          |     1 |    88 |   103   (2)| 00:00:02 |
|   6 |       NESTED LOOPS                    |                          |       |       |            |          |
|   7 |        NESTED LOOPS                   |                          |     1 |    78 |    82   (2)| 00:00:01 |
|   8 |         NESTED LOOPS                  |                          |     1 |    69 |    81   (2)| 00:00:01 |
|*  9 |          HASH JOIN                    |                          |     1 |    60 |    81   (2)| 00:00:01 |
|  10 |           NESTED LOOPS                |                          |     1 |    36 |     9   (0)| 00:00:01 |
|  11 |            TABLE ACCESS BY INDEX ROWID| EM_EXAM                  |     1 |    17 |     1   (0)| 00:00:01 |
|* 12 |             INDEX UNIQUE SCAN         | PK_EM_EXAM               |     1 |       |     0   (0)| 00:00:01 |
|* 13 |            TABLE ACCESS BY INDEX ROWID| EM_EXAMINEE              |     1 |    19 |     8   (0)| 00:00:01 |
|* 14 |             INDEX RANGE SCAN          | EM_EXAMINEE_UK1          |    33 |       |     1   (0)| 00:00:01 |
|* 15 |           TABLE ACCESS FULL           | ZYY_USER                 | 38281 |   897K|    72   (2)| 00:00:01 |
|* 16 |          INDEX UNIQUE SCAN            | IDX_ZYY_SECURITY_ACCOUNT |     1 |     9 |     0   (0)| 00:00:01 |
|* 17 |         INDEX UNIQUE SCAN             | PK_ZYY_ORG               |     1 |       |     0   (0)| 00:00:01 |
|* 18 |        TABLE ACCESS BY INDEX ROWID    | ZYY_ORG                  |     1 |     9 |     1   (0)| 00:00:01 |
|  19 |       INDEX FAST FULL SCAN            | IDX_ZYY_BASE_RESIDENCY   | 15307 |   149K|    20   (0)| 00:00:01 |
|  20 |      TABLE ACCESS BY INDEX ROWID      | ZYY_ORG                  |     1 |    20 |     1   (0)| 00:00:01 |
|* 21 |       INDEX UNIQUE SCAN               | PK_ZYY_ORG               |     1 |       |     0   (0)| 00:00:01 |
|* 22 |     INDEX UNIQUE SCAN                 | PK_ZYY_ORG               |     1 |       |     0   (0)| 00:00:01 |
|* 23 |    TABLE ACCESS BY INDEX ROWID        | ZYY_ORG                  |     1 |    34 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("ZBR"."RESIDENCY_ID"(+)="U"."ID")
   9 - access("EM_EXAMINEE"."EM_USER_ID"='rct'||TO_CHAR("U"."ID"))
  12 - access("EM_EXAM"."ID"=444)
  13 - filter("EM_EXAMINEE"."IS_COURSE_QUAL"=2 AND "EM_EXAMINEE"."JOIN_COURSE_COUNT"<>0)
  14 - access("EM_EXAMINEE"."EXAM_ID"=444)
  15 - filter("U"."ZYY_USER_STATUS"<>(-2))
  16 - access("AC"."ACCOUNT_NAME"="U"."ACCOUNT_NAME")
  17 - access("U"."ZYY_USER_ORG_ID"="ORG"."ID")
  18 - filter("ORG"."STATUS"=1)
  21 - access("O1"."ID"=TO_NUMBER(SUBSTR('rct'||TO_CHAR("U"."ZYY_USER_ORG_ID"),4,LENGTH('rct'||TO_CHAR("U"
              ."ZYY_USER_ORG_ID")))))
  22 - access("O2"."ID"=100039411)
  23 - filter("O1"."ORG_CODE" LIKE "O2"."ORG_CODE"||'%')

Statistics
----------------------------------------------------------
          8  recursive calls
          1  db block gets
        491  consistent gets
          0  physical reads
          0  redo size
        548  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Note:
效果也是可以的,哪有没有其它方法不使用hint ,就可以使用像第一次一样的执行计划呢? 可以的,出于篇幅太大,转到下一篇

打赏

对不起,这篇文章暂时关闭评论。