查看: 5518|回复: 15

反向索引的奇怪问题

[复制链接]
招聘 : 数据库管理员
论坛徽章:
9
生肖徽章2007版:牛
日期:2009-03-10 21:26:492010新春纪念徽章
日期:2010-01-04 08:33:082010年世界杯参赛球队:葡萄牙
日期:2010-02-22 14:35:242010新春纪念徽章
日期:2010-03-01 11:19:092010广州亚运会纪念徽章:射击
日期:2010-09-08 23:42:12ITPUB9周年纪念徽章
日期:2010-10-08 09:31:212010广州亚运会纪念徽章:拳击
日期:2010-10-30 00:46:582011新春纪念徽章
日期:2011-02-18 11:43:322011新春纪念徽章
日期:2011-03-01 08:49:39
发表于 2010-8-19 17:24 | 显示全部楼层 |阅读模式

有条Top SQL :
select * from nutch_visitedurl where url=:1

表结构:
SQL> desc nutch_visitedurl
Name       Null?    Type
---------- -------- ---------------
ID         NOT NULL NUMBER
URL                 VARCHAR2(200)
HASHCODE            NUMBER(12)  

执行计划如下:
--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |       |       |    84 (100)|          |
|   1 |  TABLE ACCESS FULL| NUTCH_VISITEDURL |  4211 |   378K|    84   (2)| 00:00:02 |
--------------------------------------------------------------------------------------

大小:
select segment_name,bytes/1048576 from user_segments where segment_name=upper('nutch_visitedurl');

SEGMENT_NAME           BYTES/1048576
---------------------- -------------
NUTCH_VISITEDURL                   8

很简单的查询,很简单的表,表也不大才8M,理论上优化这个查询没有多少“肉”可以吃。


但是从AWR发现这条语句在1小时内拼命执行了 218,135 多次,Gets per Exec 373.64 不大,毕竟是很小的一张表,但是因为次数多 gets 占总gets的 63.28%,CPU Time 563.88 秒

Top 30 Hot Block 有一半是这个表:
sys$ora8i@4.20 SQL> l
  1  select tch , file#, dbablk ,
  2         case when obj = 4294967295
  3         then 'rbs/compat segment'
  4         else (select max('('||object_type||') '||owner||'.'||object_name)||
  5               decode(count(*),1,'',' maybe!')
  6               from dba_objects
  7               where data_object_id = X.OBJ)
  8          end what
  9          from (
10          select tch , file# ,dbablk,obj from x$bh where state <>0
11          order by tch desc) x
12*         where rownum <=&top_num

       TCH      FILE#     DBABLK WHAT
---------- ---------- ---------- ------------------------------------------------------------
     19306          5       2420 (INDEX) xxxxxx.PK_CATALOG
     19305          5       2422 (INDEX) xxxxxx.PK_CATALOG
     19198          5      48896 (TABLE) xxxxxx.NUTCH_VISITEDURL
     19198          5      44372 (TABLE) xxxxxx.NUTCH_VISITEDURL
     19198          5      48846 (TABLE) xxxxxx.NUTCH_VISITEDURL
     19198          5      48833 (TABLE) xxxxxx.NUTCH_VISITEDURL
     19198          5      40196 (TABLE) xxxxxx.NUTCH_VISITEDURL
     19198          5      48864 (TABLE) xxxxxx.NUTCH_VISITEDURL
     19198          5      40180 (TABLE) xxxxxx.NUTCH_VISITEDURL
     19198          5      44405 (TABLE) xxxxxx.NUTCH_VISITEDURL
     19198          5      41931 (TABLE) xxxxxx.NUTCH_VISITEDURL
     19198          5      44307 (TABLE) xxxxxx.NUTCH_VISITEDURL
     19198          5      44349 (TABLE) xxxxxx.NUTCH_VISITEDURL
     19198          5      44404 (TABLE) xxxxxx.NUTCH_VISITEDURL
     19198          5      48886 (TABLE) xxxxxx.NUTCH_VISITEDURL
     19198          5      44394 (TABLE) xxxxxx.NUTCH_VISITEDURL
     19198          5      40195 (TABLE) xxxxxx.NUTCH_VISITEDURL
     19198          5      48837 (TABLE) xxxxxx.NUTCH_VISITEDURL
     19198          5      44376 (TABLE) xxxxxx.NUTCH_VISITEDURL
     19198          5      44316 (TABLE) xxxxxx.NUTCH_VISITEDURL
     19096          5       2416 (TABLE) xxxxxx.CATALOG
     15092          5       7732 (INDEX) xxxxxx.PK_RESOURCES_RELATE
     15079          5       5804 (TABLE) xxxxxx.NUTCH_CATALOG
     15079          5       5808 (TABLE) xxxxxx.NUTCH_CATALOG
     15079          5       5803 (TABLE) xxxxxx.NUTCH_CATALOG
     15079          5       5807 (TABLE) xxxxxx.NUTCH_CATALOG
     15079          5       5805 (TABLE) xxxxxx.NUTCH_CATALOG
     15079          5       5806 (TABLE) xxxxxx.NUTCH_CATALOG
     15077          5      40110 (TABLE) xxxxxx.NUTCH_VISITEDURL
     15077          5      40107 (TABLE) xxxxxx.NUTCH_VISITEDURL

数据分布:

select substr(url,1,20),count(*)
from nutch_visitedurl
group by substr(url,1,20);

SUBSTR(URL,1,20)                           COUNT(*)
---------------------------------------- ----------
http://dadao.net/php                          12870
/usr/jakarta-tomcat-                          12870

select substr(Reverse(url),1,20),count(*)
from nutch_visitedurl
group by substr(Reverse(url),1,20)

SUBSTR(REVERSE(URL),1,20)                  COUNT(*)
---------------------------------------- ----------
08327=DIelcitrA?php.                              1
29203=DIelcitrA?php.                              1
92268=DIelcitrA?php.                              1
.....
....
.....
lmth.02153=DIelcitrA                              1
24205=DIelcitrA?php.                              1  

select
  count(distinct(SUBSTR(URL,1,6))) count_start ,
  count(distinct(SUBSTR(REVERSE(URL),1,6))) count_end ,
  count(*) count_all
from nutch_visitedurl;

COUNT_START  COUNT_END  COUNT_ALL
----------- ---------- ----------
          2      12604      25740
         
从这个分布来说,url 字段的头几乎是一样,屁股几乎都不一样,
如果建一个普通的索引,那么要查找匹配很多次才能定位到一个索引条目,
如果建立反向索引,“尾巴”变成“头”,很快就可以定位索引条目,

在测试服务器上建立两个表和索引
SQL> create table table01 as select * from nutch_visitedurl ;
SQL> create table table02 as select * from nutch_visitedurl ;
SQL> create index  idx_table01_url on table01(url);
SQL> create index ridx_table02_url on table02(url) reverse ;

SQL> select * from table01
  2  where url='/usr/jakarta-tomcat-5.0.28/webapps/e3/output/dadao.net/php/prtime/temp_news.php!ArticleID=90145.html'
  3  /
------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     4 |   512 |    80   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE02         |     4 |   512 |    80   (0)|
|   2 |   INDEX RANGE SCAN          | IDX_TABLE02_URL |    85 |       |     1   (0)|
------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          5  consistent gets

SQL> select * from table02
  2  where url='/usr/jakarta-tomcat-5.0.28/webapps/e3/output/dadao.net/php/prtime/temp_news.php!ArticleID=90145.html'
  3  /
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     4 |   512 |    80   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE02          |     4 |   512 |    80   (0)|
|   2 |   INDEX RANGE SCAN          | RIDX_TABLE02_URL |    85 |       |     1   (0)|
-------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          4  consistent gets

反向索引的确表现好一点。         
         
并收集统计信息  
SQL> execute dbms_stats.gather_table_stats(ownname => 'e3nanhai',tabname => 'table01',cascade => true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.25
SQL> execute dbms_stats.gather_table_stats(ownname => 'e3nanhai',tabname => 'table02',cascade => true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.27

再来:
SQL> select * from table01
  2  where url='/usr/jakarta-tomcat-5.0.28/webapps/e3/output/dadao.net/php/prtime/temp_news.php!ArticleID=90145.html'
  3  /
  
------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         | 12491 |  1110K|    82   (2)|
|   1 |  TABLE ACCESS FULL| TABLE01 | 12491 |  1110K|    82   (2)|
------------------------------------------------------------------
Statistics
----------------------------------------------------------
        353  consistent gets
        
SQL> select * from table02
  2  where url='/usr/jakarta-tomcat-5.0.28/webapps/e3/output/dadao.net/php/prtime/temp_news.php!ArticleID=90145.html'
  3  /        
------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         | 12903 |  1159K|    82   (2)|
|   1 |  TABLE ACCESS FULL| TABLE02 | 12903 |  1159K|    82   (2)|
------------------------------------------------------------------

Statistics
----------------------------------------------------------
        353  consistent gets  
        
全都跑去走全表扫描了。        
看看索引的情况:
SQL> l
  1  select index_name,table_name,index_type,blevel,leaf_blocks,clustering_factor,num_rows
  2  from user_indexes
  3* where table_name like 'TABLE0%'
SQL> /

INDEX_NAME           TABLE_NAME INDEX_TYPE          BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR   NUM_ROWS
-------------------- ---------- --------------- ---------- ----------- ----------------- ----------
IDX_TABLE01_URL      TABLE01    NORMAL                   2         325             24147      25740
RIDX_TABLE02_URL     TABLE02    NORMAL/REV               1         325             25643      25740

CLUSTERING_FACTOR 非常高,估计这就是不是用索引的原因,使用 hint 强制走索引是可以的,也不贴了。
再来。

建一个 url 是反转的table03:
SQL> create table table03 as select id,reverse(url) url,hashcode from nutch_visitedurl ;

Table created.

Elapsed: 00:00:00.32
SQL> create index idx_table03_url on table03(url) ;

Index created.

Elapsed: 00:00:00.43
SQL> execute dbms_stats.gather_table_stats(ownname => 'e3nanhai',tabname => 'table03',cascade => true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.81

语句的where已经改成了:url= reverse(.......
SQL> select * from table03
  2  where url= reverse('/usr/jakarta-tomcat-5.0.28/webapps/e3/output/dadao.net/php/prtime/temp_news.php!ArticleID=90145.html')
  3  /
  
------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |    92 |     2   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE03         |     1 |    92 |     2   (0)|
|   2 |   INDEX RANGE SCAN          | IDX_TABLE03_URL |     1 |       |     1   (0)|
------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          4  consistent gets
         
看看索引的情况:
SQL> l
  1  select index_name,table_name,index_type,blevel,leaf_blocks,clustering_factor,num_rows
  2  from user_indexes
  3* where table_name like 'TABLE0%'
SQL> /

INDEX_NAME           TABLE_NAME INDEX_TYPE          BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR   NUM_ROWS
-------------------- ---------- --------------- ---------- ----------- ----------------- ----------
IDX_TABLE01_URL      TABLE01    NORMAL                   2         325             24147      25740
RIDX_TABLE02_URL     TABLE02    NORMAL/REV               1         325             25643      25740
IDX_TABLE03_URL      TABLE03    NORMAL                   1         325             25643      25740

CLUSTERING_FACTOR 也很高。

奇怪的地方是 table02 是反向索引,table03 是普通索引,但是索引的条目应该是一样的,其他不说,索引的几个重要属性也是一样的,为什么 table03 会走索引,而 table02 不走索引呢 ?
如果 CLUSTERING_FACTOR 高这个原因可能说不过,毕竟 table02 和 table03 的 CLUSTERING_FACTOR 一样高。




附,数据库版本和操作系统版本 :
SQL> select * from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

SQL> ! uname -a
Linux test-server 2.6.9-78.ELsmp #1 SMP Thu Jul 24 23:54:48 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux

SQL> ! cat /etc/redhat-release
CentOS release 4.7 (Final)







[ 本帖最后由 killkill_shadow 于 2010-8-19 18:20 编辑 ]
招聘 : 数据库管理员
论坛徽章:
16
2009新春纪念徽章
日期:2009-01-04 14:52:282014年新春福章
日期:2014-02-18 16:43:092013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:19奥运会纪念徽章:艺术体操
日期:2012-07-24 12:01:27奥运会纪念徽章:乒乓球
日期:2012-07-23 18:08:47奥运会纪念徽章:铁人三项
日期:2012-06-20 13:06:10ITPUB十周年纪念徽章
日期:2011-11-01 16:24:512011新春纪念徽章
日期:2011-02-18 11:43:332010广州亚运会纪念徽章:游泳
日期:2010-10-19 11:09:06
发表于 2010-8-19 17:51 | 显示全部楼层
对于table03 的测试写错了, 你把02 改成03看看。这里主要取决于oracle 计算的selectivity.

使用道具 举报

回复
招聘 : 数据库管理员
论坛徽章:
16
2009新春纪念徽章
日期:2009-01-04 14:52:282014年新春福章
日期:2014-02-18 16:43:092013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:19奥运会纪念徽章:艺术体操
日期:2012-07-24 12:01:27奥运会纪念徽章:乒乓球
日期:2012-07-23 18:08:47奥运会纪念徽章:铁人三项
日期:2012-06-20 13:06:10ITPUB十周年纪念徽章
日期:2011-11-01 16:24:512011新春纪念徽章
日期:2011-02-18 11:43:332010广州亚运会纪念徽章:游泳
日期:2010-10-19 11:09:06
发表于 2010-8-19 18:06 | 显示全部楼层
另外,执行下面的2条语句

select table_name, column_name, num_buckets,density from user_tab_columns where table_name in ('TABLE01','TABLE02','TABLE03') and column_name='URL';


select table_name, column_name,enpoint_number, endpoint_value from user_tab_histograms where table_name in ('TABLE01','TABLE02','TABLE03') and column_name='URL';

[ 本帖最后由 Meets 于 2010-8-19 18:09 编辑 ]

使用道具 举报

回复
招聘 : 数据库管理员
论坛徽章:
9
生肖徽章2007版:牛
日期:2009-03-10 21:26:492010新春纪念徽章
日期:2010-01-04 08:33:082010年世界杯参赛球队:葡萄牙
日期:2010-02-22 14:35:242010新春纪念徽章
日期:2010-03-01 11:19:092010广州亚运会纪念徽章:射击
日期:2010-09-08 23:42:12ITPUB9周年纪念徽章
日期:2010-10-08 09:31:212010广州亚运会纪念徽章:拳击
日期:2010-10-30 00:46:582011新春纪念徽章
日期:2011-02-18 11:43:322011新春纪念徽章
日期:2011-03-01 08:49:39
 楼主| 发表于 2010-8-19 18:28 | 显示全部楼层
原帖由 Meets 于 2010-8-19 18:06 发表
另外,执行下面的2条语句

select table_name, column_name, num_buckets,density from user_tab_columns where table_name in ('TABLE01','TABLE02','TABLE03') and column_name='URL';


select table_name, column_name,enpoint_number, endpoint_value from user_tab_histograms where table_name in ('TABLE01','TABLE02','TABLE03') and column_name='URL';


感谢回复,按照要求执行了以上两个语句,结果如下;


SQL> l
  1  select table_name, column_name, num_buckets,density
  2  from user_tab_columns
  3  where table_name in ('TABLE01','TABLE02','TABLE03')
  4* and column_name='URL'
SQL> /

TABLE_NAME                     COLUMN_NAME          NUM_BUCKETS    DENSITY
------------------------------ -------------------- ----------- ----------
TABLE01                        URL                            4 .000019803
TABLE02                        URL                            8 .000019365
TABLE03                        URL                            1 .000038682



SQL> l
  1  select table_name, column_name,endpoint_number, endpoint_value
  2  from user_tab_histograms
  3  where table_name in ('TABLE01','TABLE02','TABLE03')
  4* and column_name='URL'
SQL> /

TABLE_NAME                     COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ -------------------- --------------- --------------
TABLE01                        URL                             2669     2.4642E+35
TABLE01                        URL                             2672     5.4236E+35
TABLE01                        URL                             3073     5.4236E+35
TABLE01                        URL                             5395     5.4236E+35
TABLE02                        URL                             2757     2.4642E+35
TABLE02                        URL                             2758     5.4236E+35
TABLE02                        URL                             2759     5.4236E+35
TABLE02                        URL                             2760     5.4236E+35
TABLE02                        URL                             2762     5.4236E+35
TABLE02                        URL                             3172     5.4236E+35
TABLE02                        URL                             3173     5.4236E+35
TABLE02                        URL                             5517     5.4236E+35
TABLE03                        URL                                0     2.5021E+35
TABLE03                        URL                                1     1.0271E+36



另外,对 table03 的确写错了,但的确是走 index range scan

[ 本帖最后由 killkill_shadow 于 2010-8-19 18:29 编辑 ]

使用道具 举报

回复
论坛徽章:
86
2015中国数据库技术大会纪念徽章
日期:2015-04-24 16:04:24马上有车
日期:2014-02-19 11:55:14马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11优秀写手
日期:2013-12-18 09:29:11日产
日期:2013-10-17 08:44:39马自达
日期:2013-08-26 16:28:022013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-23 16:55:51马上有房
日期:2014-02-19 11:55:14
发表于 2010-8-19 20:36 | 显示全部楼层
删掉直方图再试

使用道具 举报

回复
招聘 : 数据库管理员
论坛徽章:
9
生肖徽章2007版:牛
日期:2009-03-10 21:26:492010新春纪念徽章
日期:2010-01-04 08:33:082010年世界杯参赛球队:葡萄牙
日期:2010-02-22 14:35:242010新春纪念徽章
日期:2010-03-01 11:19:092010广州亚运会纪念徽章:射击
日期:2010-09-08 23:42:12ITPUB9周年纪念徽章
日期:2010-10-08 09:31:212010广州亚运会纪念徽章:拳击
日期:2010-10-30 00:46:582011新春纪念徽章
日期:2011-02-18 11:43:322011新春纪念徽章
日期:2011-03-01 08:49:39
 楼主| 发表于 2010-8-19 21:35 | 显示全部楼层
原帖由 sundog315 于 2010-8-19 20:36 发表
删掉直方图再试



感谢回复。

清除直方图:
SQL> begin
dbms_stats.gather_table_stats(ownname => 'e3nanhai',
                              tabname => 'table01' ,
                              estimate_percent => null ,
                              method_opt => 'for columns SIZE 1 url' ,
                              cascade => true);
end;
/
begin                              
dbms_stats.gather_table_stats(ownname => 'e3nanhai',
                              tabname => 'table02' ,
                              estimate_percent => null ,
                              method_opt => 'for columns SIZE 1 url' ,
                              cascade => true);
end;
/
begin                              
dbms_stats.gather_table_stats(ownname => 'e3nanhai',
                              tabname => 'table03' ,
                              estimate_percent => null ,
                              method_opt => 'for columns SIZE 1 url' ,
                              cascade => true);              
end;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.29
SQL>   2    3    4    5    6    7    8  
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.27
SQL>   2    3    4    5    6    7    8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.19

看看效果:
SQL>
SQL>
select table_name, column_name, num_buckets,density
  2  from user_tab_columns
where table_name in ('TABLE01','TABLE02','TABLE03')
  4  and column_name='URL';

TABLE_NAME                     COLUMN_NAME          NUM_BUCKETS    DENSITY
------------------------------ -------------------- ----------- ----------
TABLE01                        URL                            1 .000038974
TABLE02                        URL                            1 .000038974
TABLE03                        URL                            1 .000038974

Elapsed: 00:00:00.11
SQL>
select table_name, column_name,endpoint_number, endpoint_value
from user_tab_histograms
where table_name in ('TABLE01','TABLE02','TABLE03')
  4  and column_name='URL';

TABLE_NAME                     COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ -------------------- --------------- --------------
TABLE01                        URL                                0     2.4642E+35
TABLE02                        URL                                0     2.4642E+35
TABLE03                        URL                                0     2.5021E+35
TABLE01                        URL                                1     5.4236E+35
TABLE02                        URL                                1     5.4236E+35
TABLE03                        URL                                1     1.0271E+36

6 rows selected.

Elapsed: 00:00:00.04

再看一下执行计划
SQL>
SQL> set autotrace traceonly ;
SQL> l
  1  select * from table01
  2* where url= '/usr/jakarta-tomcat-5.0.28/webapps/e3/output/dadao.net/php/prtime/temp_news.php!ArticleID=90145.html'
SQL> /

------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |    92 |     4   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE01         |     1 |    92 |     4   (0)|
|   2 |   INDEX RANGE SCAN          | IDX_TABLE01_URL |     1 |       |     3   (0)|
------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          5  consistent gets

SQL> l
  1  select * from table02
  2* where url= '/usr/jakarta-tomcat-5.0.28/webapps/e3/output/dadao.net/php/prtime/temp_news.php!ArticleID=90145.html'
SQL> /

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    92 |     2   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE02          |     1 |    92 |     2   (0)|
|   2 |   INDEX RANGE SCAN          | RIDX_TABLE02_URL |     1 |       |     1   (0)|
-------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          4  consistent gets

SQL> l
  1  select * from table03
  2* where url= reverse('/usr/jakarta-tomcat-5.0.28/webapps/e3/output/dadao.net/php/prtime/temp_news.php!ArticleID=90145.html')
SQL> /

------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |    92 |     2   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE03         |     1 |    92 |     2   (0)|
|   2 |   INDEX RANGE SCAN          | IDX_TABLE03_URL |     1 |       |     1   (0)|
------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          4  consistent gets
         
的确是我想要的那种执行计划,看来问题是出在直方图上,能解析一下吗?
关键是 table02 和 table03 的现象让我很费解。

使用道具 举报

回复
论坛徽章:
27
数据库板块每日发贴之星
日期:2010-06-17 01:01:07迷宫蛋
日期:2011-07-07 15:25:46紫蛋头
日期:2011-08-10 10:31:56ITPUB十周年纪念徽章
日期:2011-09-27 16:33:28ITPUB十周年纪念徽章
日期:2011-11-01 16:25:222012新春纪念徽章
日期:2012-02-07 09:59:35ITPUB知识分享者
日期:2012-02-20 17:49:25铁扇公主
日期:2012-02-21 15:02:40ITPUB年度最佳BLOG写作奖
日期:2012-03-13 17:09:53ITPUB 11周年纪念徽章
日期:2012-10-09 18:14:48
发表于 2010-8-19 22:47 | 显示全部楼层
直方图反应的某个列上值的分布情况,如果搜集了直方图而不使用索引,那么几乎非常可能是你的数据分布不均匀,
某些值上,占据了多个桶。
很可能是你的语句在第一次解析时(此时有bind peeking),碰到了占据列多数的那些个特殊值,此时结合直方图来判断的话,cbo 自然觉得该列上的索引扫描不如全表来的快,因此走了全表扫描。在后面的软解析时,执行计划也会使用第一次的执行计划,走的全表扫描,而不再管绑定变量带入的值是否是那些特殊值。
                                                                          ---来自CSDN

使用道具 举报

回复
招聘 : 数据库管理员
论坛徽章:
9
生肖徽章2007版:牛
日期:2009-03-10 21:26:492010新春纪念徽章
日期:2010-01-04 08:33:082010年世界杯参赛球队:葡萄牙
日期:2010-02-22 14:35:242010新春纪念徽章
日期:2010-03-01 11:19:092010广州亚运会纪念徽章:射击
日期:2010-09-08 23:42:12ITPUB9周年纪念徽章
日期:2010-10-08 09:31:212010广州亚运会纪念徽章:拳击
日期:2010-10-30 00:46:582011新春纪念徽章
日期:2011-02-18 11:43:322011新春纪念徽章
日期:2011-03-01 08:49:39
 楼主| 发表于 2010-8-19 23:17 | 显示全部楼层
原帖由 杨奇龙 于 2010-8-19 22:47 发表
直方图反应的某个列上值的分布情况,如果搜集了直方图而不使用索引,那么几乎非常可能是你的数据分布不均匀,
某些值上,占据了多个桶。
很可能是你的语句在第一次解析时(此时有bind peeking),碰到了占据列多数的那些个特殊值,此时结合直方图来判断的话,cbo 自然觉得该列上的索引扫描不如全表来的快,因此走了全表扫描。在后面的软解析时,执行计划也会使用第一次的执行计划,走的全表扫描,而不再管绑定变量带入的值是否是那些特殊值。
                                                                          ---来自CSDN


这个貌似是 bind peeking 的经典问题,最开始的时候这个表上面没有索引,想走索引都难,测试的那几条语句都没有使用绑定变量,所以应该不存在 bind peeking 的问题。

使用道具 举报

回复
论坛徽章:
86
2015中国数据库技术大会纪念徽章
日期:2015-04-24 16:04:24马上有车
日期:2014-02-19 11:55:14马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11优秀写手
日期:2013-12-18 09:29:11日产
日期:2013-10-17 08:44:39马自达
日期:2013-08-26 16:28:022013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-23 16:55:51马上有房
日期:2014-02-19 11:55:14
发表于 2010-8-20 08:46 | 显示全部楼层
有时间的话可以看看JL的Cost Based Oracle Fundamentals CHAPTER 6 ■  SELECTIVITY ISSUES 117

使用道具 举报

回复
招聘 : 数据库管理员
论坛徽章:
9
生肖徽章2007版:牛
日期:2009-03-10 21:26:492010新春纪念徽章
日期:2010-01-04 08:33:082010年世界杯参赛球队:葡萄牙
日期:2010-02-22 14:35:242010新春纪念徽章
日期:2010-03-01 11:19:092010广州亚运会纪念徽章:射击
日期:2010-09-08 23:42:12ITPUB9周年纪念徽章
日期:2010-10-08 09:31:212010广州亚运会纪念徽章:拳击
日期:2010-10-30 00:46:582011新春纪念徽章
日期:2011-02-18 11:43:322011新春纪念徽章
日期:2011-03-01 08:49:39
 楼主| 发表于 2010-8-20 09:42 | 显示全部楼层
原帖由 sundog315 于 2010-8-20 08:46 发表
有时间的话可以看看JL的Cost Based Oracle Fundamentals CHAPTER 6 ■  SELECTIVITY ISSUES 117


谢谢您的建议,我看冯大辉翻译的那本 Throbleshooting Oracle Performance 里面提到了统计信息和直方图,
其中对于 varchar/char 类型收集统计信息时是看开始的6个字节,正如我SQL的统计有这么一项:

select
  count(distinct(SUBSTR(URL,1,6))) count_start ,
  count(distinct(SUBSTR(REVERSE(URL),1,6))) count_end ,
  count(*) count_all
from nutch_visitedurl;

COUNT_START  COUNT_END  COUNT_ALL
----------- ---------- ----------
          2      12604      25740

从这个SQL的结果来看,开头的6个字节几乎是一样的,而reverse后,“开头”的六个字节几乎都不一样,如果收集统计的信息的话,估计会令选择率降低很多,接近主键的水平,这一点在 table03(对url reverse)的实验中印证,优化器义无反顾地走Index Range Scan,哪怕Clustering_Factor异常的高;同样对于table01 (url没有reverse)的实验中可以看到收集统计信息之后,优化器认为记录很多都是重复(可恶的头6个字节),所以走全表扫描。
而 table02 使用反向索引的话,我觉得行为应该类似 table03 ,但事实却是走全表扫描,和table01的行为一样,想不通...

感谢您的回复。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表