1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 | $ sqlplus sh/sh@pdb2 SQL> drop table tab1 purge; SQL> create table tab1( 2 id number, 3 gender varchar2(1), 4 has_y_chromosome varchar2(1), 5 constraint tab1_pk primary key(id), 6 constraint tab1_gender_chk check (gender in ('M','F')), 7 constraint tab1_has_y_chromosome_chk check (has_y_chromosome in ('Y','N')) 8 ); Table created. SQL> insert /*+ append */ into tab1 2 select level,'M','Y' 3 from dual 4 connect by level <= 10; 10 rows created. SQL> commit; SQL> insert /*+ append */ into tab1 2 select 10+level,'F','N' 3 from dual 4 connect by level<=90; 90 rows created. SQL> commit; SQL> create index tab1_gender_idx on tab1(gender); SQL> create index tab1_has_y_chromosome_idx on tab1(has_y_chromosome); SQL> exec dbms_stats.gather_table_stats(USER,'TAB1'); #此时没有任何直方图信息 SQL> select column_id,column_name,histogram 2 from user_tab_columns 3 where table_name='TAB1' 4 order by column_id; COLUMN_ID COLUMN_NAME HISTOGRAM ---------- -------------------- --------------- 1 ID NONE 2 GENDER NONE 3 HAS_Y_CHROMOSOME NONE SQL> 实际数据中,所有males都有Y标志,但是所有females都没有。不过优化器并不知道这点。 优化器会评估谓词的selectivity,假设数据是均衡分布的,两个列相互独立,认为25行数据既含male列又含有Y标记。 SQL> select /*+ gather_plan_statistics */ * 2 from tab1 3 where gender='M' 4 and has_y_chromosome='Y'; ID G H ---------- - - 1 M Y 2 M Y 3 M Y 4 M Y 5 M Y 6 M Y 7 M Y 8 M Y 9 M Y 10 M Y 10 rows selected. SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------- SQL_ID dnpgrp1fvkp7t, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ * from tab1 where gender='M' and has_y_chromosome='Y' Plan hash value: 1552452781 ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 4 | |* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 25 | 10 |00:00:00.01 | 4 | |* 2 | INDEX RANGE SCAN | TAB1_GENDER_IDX | 1 | 50 | 10 |00:00:00.01 | 2 | ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("HAS_Y_CHROMOSOME"='Y') 2 - access("GENDER"='M') 21 rows selected. SQL> 如上结果所示,发生的cardinality 的估算错误。此时如果有直方图信息、或者扩展统计信息,优化器会评估出更准确的cardinality。 查看v$sql,确认该sql计划是否还可以优化。IS_REOPTIMIZABLE=Y表示优化器已经意识到cardinality估算不准,也表示SQL plan directives已经被创建: SQL> select sql_text,is_reoptimizable 2 from v$sql 3 where sql_id='dnpgrp1fvkp7t'; SQL_TEXT IS_REOPTIMIZABLE ---------------------------------------- ---------------- select /*+ gather_plan_statistics */ * Y from tab1 where gender='M' and has_y _chromosome='Y' SQL> 查看sql plan directives 直线以下sql查看,如果查不到结果,说明sql plan directive还没有刷新到磁盘: SQL> select to_char(d.directive_id) dir_id,o.owner,o.object_name,o.subobject_name col_name,o.object_type,d.type,d.state,d.reason 2 from dba_sql_plan_directives d,dba_sql_plan_dir_objects o 3 where d.directive_id=o.directive_id 4 and o.owner='SH' 5 order by 1,2,3,4,5; no rows selected SQL> 手动刷新 SQL> exec dbms_spd.flush_sql_plan_directive; SQL> select to_char(d.directive_id) dir_id,o.owner,o.object_name,o.subobject_name col_name,o.object_type,d.type,d.state,d.reason 2 from dba_sql_plan_directives d,dba_sql_plan_dir_objects o 3 where d.directive_id=o.directive_id 4 and o.owner='SH' 5 order by 1,2,3,4,5; DIR_ID OWNER OBJECT_NAM COL_NAME OBJECT TYPE STATE REASON -------------------- ---------- ---------- ---------- ------ ---------------- ---------- ------------------------------------ 17805875575772415323 SH TAB1 GENDER COLUMN DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE 17805875575772415323 SH TAB1 TABLE DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE SQL> 再次执行查询,这次查询会使用到上面创建的sql plan directive_id SQL> select /*+ gather_plan_statistics */ * 2 from tab1 3 where gender='M' 4 and has_y_chromosome='Y'; ID G H ---------- - - 1 M Y 2 M Y 3 M Y 4 M Y 5 M Y 6 M Y 7 M Y 8 M Y 9 M Y 10 M Y 10 rows selected. SQL> SET LINESIZE 200 PAGESIZE 100 SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------- SQL_ID gj6qavway0k06, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ * from tab1 where gender='M' and has_y_chromosome='Y' Plan hash value: 1552452781 ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 4 | |* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 10 | 10 |00:00:00.01 | 4 | |* 2 | INDEX RANGE SCAN | TAB1_GENDER_IDX | 1 | 10 | 10 |00:00:00.01 | 2 | ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("HAS_Y_CHROMOSOME"='Y') 2 - access("GENDER"='M') Note ----- - dynamic statistics used: dynamic sampling (level=2) - 1 Sql Plan Directive used for this statement 26 rows selected. SQL> |
欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |