首页 » ORACLE 9i-23ai » 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 ，就可以使用像第一次一样的执行计划呢？ 可以的，出于篇幅太大，转到下一篇
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。