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

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

 

打赏

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