SQL join problem caused by Low cardinality with Histogram(柱状图产生的过低Card.导致表join性能问题)(二)
接上一篇 http://www.anbob.com/archives/2323.html
下面我们从统计信息入手
sys@DEVDB>select count(distinct join_course_count),count(distinct is_course_qual),count(*)
from app_zyyjhj.em_examinee;
COUNT(DISTINCTJOIN_COURSE_COUNT) COUNT(DISTINCTIS_COURSE_QUAL) COUNT(*)
-------------------------------- ----------------------------- --------------------
6 4 35765
#使用tabstat2 脚本收信表信息，我已截断输出
sys@DEVDB>@tabstat2 em_examinee app_zyyjhj
Table Number Empty Average Chain Average Global User Sample Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- -------------- -------- ------------ ------- -------- ------- ------ ------ ------- ----------
EM_EXAMINEE 35,756 510 60 1,022 0 74 YES NO ####### 06-12-2014
Column Column Distinct Number Number Global User Sample Date
Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ ------- ----------
JOIN_COURSE_COUNT NUMBER(10,0) 6 0 6 35,741 YES NO 15, 06-12-2014
IS_COURSE_QUAL NUMBER(10,0) 4 0 3 0 YES NO 5,446, 06-12-2014
**********************
Column HISTOGRAM Level
**********************
Table Column
Name Name HISTOGRAM
--------------- ------------------------------ ---------------
EM_EXAMINEE JOIN_COURSE_COUNT FREQUENCY
IS_COURSE_QUAL FREQUENCY
下面我们那该表条件SQL单独拿出来测试
app_zyyjhj@DEVDB>explain plan for select * from em_examinee where em_examinee.join_course_count != 0; Explained. app_zyyjhj@DEVDB>@xp PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Plan hash value: 709731904 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 13 | 962 | 114 (1)| 00:00:02 | |* 1 | TABLE ACCESS FULL| EM_EXAMINEE | 13 | 962 | 114 (1)| 00:00:02 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("EM_EXAMINEE"."JOIN_COURSE_COUNT"<>0) app_zyyjhj@DEVDB>explain plan for select * from em_examinee where em_examinee.join_course_count != 0 and em_examinee.is_course_qual = 2; app_zyyjhj@DEVDB>@xp PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------- Plan hash value: 709731904 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 74 | 114 (1)| 00:00:02 | |* 1 | TABLE ACCESS FULL| EM_EXAMINEE | 1 | 74 | 114 (1)| 00:00:02 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("EM_EXAMINEE"."IS_COURSE_QUAL"=2 AND "EM_EXAMINEE"."JOIN_COURSE_COUNT"<>0) app_zyyjhj@DEVDB>select count(*) from em_examinee 2 where em_examinee.join_course_count != 0 3 and em_examinee.is_course_qual = 2; COUNT(*) -------------------- 14
Note:
可以看到加上is_course_qual = 2就会产生错误的Card. 并且表列上有柱状图，会不会是histogram 产生了错误的Selectivity而错误的估算了Card呢？
清掉这一列上的histogram,
app_zyyjhj@DEVDB>execute dbms_stats.gather_table_stats(user,'em_examinee',METHOD_OPT =>'for columns is_course_qual size 1');
PL/SQL procedure successfully completed.
# run same sql without hint
Execution Plan
----------------------------------------------------------
Plan hash value: 1925057754
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 142 | 102 (2)| 00:00:02 |
| 1 | HASH GROUP BY | | 1 | 142 | 102 (2)| 00:00:02 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 142 | 102 (2)| 00:00:02 |
| 4 | NESTED LOOPS | | 1 | 122 | 101 (2)| 00:00:02 |
|* 5 | HASH JOIN OUTER | | 1 | 113 | 101 (2)| 00:00:02 |
| 6 | NESTED LOOPS | | | | | |
| 7 | NESTED LOOPS | | 1 | 103 | 80 (2)| 00:00:01 |
|* 8 | HASH JOIN | | 1 | 94 | 79 (2)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 70 | 7 (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 | 4 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | EM_EXAMINEE_UK1 | 13 | | 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"."IS_COURSE_QUAL"=2 AND "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
471 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:
注意到这次带着那个条件也使用了”正常“执行计划，从hash id也可以看出和未加此条件是同一计划，到这里优化暂时完成， 下面附上有无关柱状图是的10053trace
有Histogram
****************
QUERY BLOCK TEXT
****************
select * from em_examinee
where em_examinee.join_course_count != 0
and em_examinee.is_course_qual = 2
———————
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: EM_EXAMINEE Alias: EM_EXAMINEE
#Rows: 36977 #Blks: 510 AvgRowLen: 75.00 ChainCnt: 0.00
Index Stats::
Index: EM_EXAMINEE_UK1 Col#: 2 11
LVLS: 1 #LB: 193 #DK: 36977 LB/K: 1.00 DB/K: 1.00 CLUF: 7615.00
Index: PK_EX_EXAMINEE_INFO Col#: 1
LVLS: 1 #LB: 74 #DK: 36977 LB/K: 1.00 DB/K: 1.00 CLUF: 454.00
Access path analysis for EM_EXAMINEE
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for EM_EXAMINEE[EM_EXAMINEE]
Column (#24):
NewDensity:0.000090, OldDensity:0.000013 BktCnt:5578, PopBktCnt:5576, PopValCnt:1, NDV:4
Column (#24): IS_COURSE_QUAL(
AvgLen: 3 NDV: 4 Nulls: 0 Density: 0.000090 Min: 0 Max: 3
Histogram: Freq #Bkts: 3 UncompBkts: 5578 EndPtVals: 3
Column (#23):
NewDensity:0.045455, OldDensity:0.022727 BktCnt:22, PopBktCnt:22, PopValCnt:6, NDV:6
Column (#23): JOIN_COURSE_COUNT(
AvgLen: 2 NDV: 6 Nulls: 35743 Density: 0.045455 Min: 0 Max: 8
Histogram: HtBal #Bkts: 22 UncompBkts: 22 EndPtVals: 6
Table: EM_EXAMINEE Alias: EM_EXAMINEE
Card: Original: 36977.000000 Rounded: 1 Computed: 0.18 Non Adjusted: 0.18
Access Path: TableScan
Cost: 114.26 Resp: 114.26 Degree: 0
Cost_io: 113.00 Cost_cpu: 28037086
Resp_io: 113.00 Resp_cpu: 28037086
Best:: AccessPath: TableScan
Cost: 114.26 Degree: 1 Resp: 114.26 Card: 0.18 Bytes: 0
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: EM_EXAMINEE[EM_EXAMINEE]#0
***********************
Best so far: Table#: 0 cost: 114.2617 card: 0.1810 bytes: 75
***********************
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000
*********************************
Number of join permutations tried: 1
*********************************
Enumerating distribution method (advanced)
Trying or-Expansion on query block SEL$1 (#0)
Transfer Optimizer annotations for query block SEL$1 (#0)
id=0 frofand predicate=”EM_EXAMINEE”.”IS_COURSE_QUAL”=2 AND “EM_EXAMINEE”.”JOIN_COURSE_COUNT”<>0
Final cost for query block SEL$1 (#0) – All Rows Plan:
Best join order: 1
Cost: 114.2617 Degree: 1 Card: 1.0000 Bytes: 75
Resc: 114.2617 Resc_io: 113.0000 Resc_cpu: 28037086
Resp: 114.2617 Resp_io: 113.0000 Resc_cpu: 28037086
无HISTOGRAM
***********************
Table Stats::
Table: EM_EXAMINEE Alias: EM_EXAMINEE
#Rows: 36977 #Blks: 510 AvgRowLen: 75.00 ChainCnt: 0.00
Index Stats::
check parallelism for statement[<unnamed>]
kkfdtParallel: parallel is possible (no statement type restrictions)
kkfdPaForcePrm return FALSE
…
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for EM_EXAMINEE[EM_EXAMINEE]
Column (#24): IS_COURSE_QUAL(
AvgLen: 3 NDV: 4 Nulls: 0 Density: 0.250000 Min: 0 Max: 3
Column (#23):
NewDensity:0.045455, OldDensity:0.022727 BktCnt:22, PopBktCnt:22, PopValCnt:6, NDV:6
Column (#23): JOIN_COURSE_COUNT(
AvgLen: 2 NDV: 6 Nulls: 35743 Density: 0.045455 Min: 0 Max: 8
Histogram: HtBal #Bkts: 22 UncompBkts: 22 EndPtVals: 6
Table: EM_EXAMINEE Alias: EM_EXAMINEE
Card: Original: 36977.000000 Rounded: 252 Computed: 252.41 Non Adjusted: 252.41
Access Path: TableScan
Cost: 114.28 Resp: 114.28 Degree: 0
Cost_io: 113.00 Cost_cpu: 28498967
Resp_io: 113.00 Resp_cpu: 28498967
Best:: AccessPath: TableScan
Cost: 114.28 Degree: 1 Resp: 114.28 Card: 252.41 Bytes: 0
11G r2 生成10053的一种方法
1, Find sql_id for the query that you want to trace. sys@DEVDB>SELECT sql_id,child_number,substr(sql_text,1,30) sqlt FROM v$sql WHERE sql_text LIKE '%em_examinee.is_course_qual = 2%'; SQL_ID CHILD_NUMBER SQLT ------------- -------------------- ------------------------------------------------------------ 6gfgyndqxh8w1 0 select * from em_examinee 2. Execute DBMS_SQLDIAG.DUMP_TRACE with your sql_id execute DBMS_SQLDIAG.DUMP_TRACE(p_sql_id=>'6gfgyndqxh8w1', p_child_number=>0, p_component=>'Compiler',p_file_id=>'test10053'); 3. Tracefile with the postfix 'test10053' that I used here will be generated under diag trace directory: cd $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace [oracle@dev-db trace]$ ls *10053.trc devdb_ora_31505_test10053.trc
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。