ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 优化由直方图信息导致的sql性能问题

优化由直方图信息导致的sql性能问题

原创 Linux操作系统 作者:myownstars 时间:2011-01-19 18:07:02 0 删除 编辑
下午在生产库上捕获一条sql,查询了系统的几个主要大表,cost有34776,并且在某列上使用了绑定变量,需要对其进行优化
sql大致内容如下
select si.id1,si.id2,si.id13
  from (select jusint_it.id1,jusint_it.id2,jusint_it.id3
          from justin, jusint_it
         where justin.id = jusint_it.order_id
           and justin.jusint_id  in (:1)
        union all
        select jusitin_it_hist.id1,jusitin_it_hist.id2,jusitin_it_hist.id3
          from justin_hist, jusitin_it_hist
         where justin_hist.order_id = jusitin_it_hist.order_id
           and justin_hist.jusint_id in (:2)) si
执行计划
| SELECT STATEMENT                  |                              |       |      |  34776 |       |       |
| VIEW                              |                              |   163K|   90M|  34776 |       |       |
|  UNION-ALL                        |                              |       |      |        |       |       |
|   HASH JOIN                       |                              |   163K|   10M|  34723 |       |       |
|   TABLE ACCESS BY INDEX ROWID     |justin_it                     |     6 |  366 |      3 |       |       |
|    NESTED LOOPS                   |                              |   125 |    8K|     54 |       |       |
|    TABLE ACCESS BY INDEX ROWID    |justin_hist                   |    23K|  209K|   2053 |       |       |
|    TABLE ACCESS FULL              |justin_it_hist                |     5M|  306M|  32607 |       |       |
|     INDEX RANGE SCAN              |idx_justin_order              |     6 |      |      2 |       |       |
|     INDEX RANGE SCAN              |idx_hist_justin_id            |    23K|      |    102 |       |       |
|     TABLE ACCESS BY INDEX ROWID   |justin                        |    21 |  189 |      7 |       |       |
|      INDEX RANGE SCAN             |idx_so_justin_id              |    21 |      |      3 |       |       |
------------------------------------------------------------------------------------------------------------

经查看,绑定变量列有直方图,信息如下
SQL> select table_name,num_distinct,density,num_nulls,num_buckets,last_analyzed from user_tab_col_statistics t where t.table_name in ('justin','justin_hist') and t.column_name='justin_id';

TABLE_NAME                     NUM_DISTINCT    DENSITY  NUM_NULLS NUM_BUCKETS LAST_ANALYZED
------------------------------ ------------ ---------- ---------- ----------- -------------
justin                                  9570 7.90613223      27923         254 2011-1-19 上午
justin_hist                             114  1.83563894      86491         114 2011-1-14 上午
注意density列,当没有直方图的时候,density=1/num_distinct;其值越接近0,表示该列的选择性越高,而这里两个值都超过了1

查看绑定变量justin_id在表justin和justin_hist中的分布状况
SQL> select count(*),count(distinct justin_id) from justin_hist;

  COUNT(*) COUNT(DISTINCTjustin_id)
---------- ---------------------------
   2887588                       32146

SQL> select justin_id,count(*) from justin_hist group by justin_id having count(*) >1000;

       justin_id   COUNT(*)
------------------- ----------
                       99300
                  0    2729587

SQL> select count(*),count(distinct justin_id) from justin;

  COUNT(*) COUNT(DISTINCT justin_id)
---------- ---------------------------
239715                        9733

SQL> select justin_id,count(*) from justin group by justin_id having count(*) >1000;

       justin_id   COUNT(*)
------------------- ----------
                    27934
              0            198284
              
可以看到justin_id的分布情况及其不均匀,其中justin_id=0占了全部值的80%还要多

SQL> select 2729587/2887588,198284/239715 from dual;

2729587/2887588 198284/239715
--------------- -------------
0.9452827065356 0.82716559247


将绑定变量替换成常量,重新观察执行计划
1.        Null
----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |   754K|   417M| 93401   (2)| 00:18:41 |
|   1 |  VIEW                |                 |   754K|   417M| 93401   (2)| 00:18:41 |
|   2 |   UNION-ALL          |                 |       |       |            |          |
|*  3 |    HASH JOIN         |                 |   162K|    10M|  9149   (1)| 00:01:50 |
|*  4 |     TABLE ACCESS FULL| justin          | 27923 |   245K|  6767   (1)| 00:01:22 |
|   5 |     TABLE ACCESS FULL| justin_it       |   295K|    17M|  2378   (2)| 00:00:29 |
|*  6 |    HASH JOIN         |                 |   592K|    38M| 84251   (2)| 00:16:52 |
|*  7 |     TABLE ACCESS FULL| justin_hist     | 86491 |   760K| 51580   (1)| 00:10:19 |
|   8 |     TABLE ACCESS FULL| justin_it_hist  |  5354K|   306M| 32607   (2)| 00:06:32 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("justin"."ID"="justin_it"."ORDER_ID")
   4 - filter("justin"."justin_id" IS NULL)
   6 - access("justin_hist"."ORDER_ID"="justin_it_hist"."ORDER_ID")
   7 - filter("justin_hist"."justin_id" IS NULL)

23 rows selected.

2.        0
------------------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |  5649K|  3124M|       |   115K  (2)| 00:23:08 |
|   1 |  VIEW                |                 |  5649K|  3124M|       |   115K  (2)| 00:23:08 |
|   2 |   UNION-ALL          |                 |       |       |       |            |          |
|*  3 |    HASH JOIN         |                 |   295K|    19M|  3936K| 10368   (1)| 00:02:05 |
|*  4 |     TABLE ACCESS FULL| justin          |   191K|  1686K|       |  6768   (1)| 00:01:22 |
|   5 |     TABLE ACCESS FULL| justin_it       |   295K|    17M|       |  2378   (2)| 00:00:29 |
|*  6 |    HASH JOIN         |                 |  5354K|   352M|    53M|   105K  (2)| 00:21:04 |
|*  7 |     TABLE ACCESS FULL| justin_hist     |  2671K|    22M|       | 51590   (1)| 00:10:20 |
|   8 |     TABLE ACCESS FULL| justin_it_hist  |  5354K|   306M|       | 32607   (2)| 00:06:32 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("justin"."ID"="justin_it"."ORDER_ID")
   4 - filter("justin"."justin_id"=0)
   6 - access("justin_hist"."ORDER_ID"="justin_it_hist"."ORDER_ID")
   7 - filter("justin_hist"."justin_id"=0)

23 rows selected.

3.        除了null和0外的任意一个值
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                             |  1408 |   797K|   492   (0)| 00:00:06 |
|   1 |  VIEW                           |                             |  1408 |   797K|   492   (0)| 00:00:06 |
|   2 |   UNION-ALL                     |                             |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID  | justin_it                   |     6 |   366 |     3   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                |                             |     8 |   560 |     7   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| justin                      |     1 |     9 |     4   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | idx_so_justin_id            |     1 |       |     3   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN           | idx_justin_order            |     6 |       |     2   (0)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID  | justin_it_hist              |     7 |   420 |     3   (0)| 00:00:01 |
|   9 |     NESTED LOOPS                |                             |  1400 | 96600 |   485   (0)| 00:00:06 |
|  10 |      TABLE ACCESS BY INDEX ROWID| justin_hist                 |   204 |  1836 |    20   (0)| 00:00:01 |
|* 11 |       INDEX RANGE SCAN          | idx_hist_justin_id          |   204 |       |     3   (0)| 00:00:01 |
|* 12 |      INDEX RANGE SCAN           | idx_hist_justin_order_id    |     7 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

   6 - access("justin"."justin_id"=864477)
   7 - access("justin"."ID"="justin_it"."ORDER_ID")
  11 - access("justin_hist"."justin_id"=864477)
  12 - access("justin_hist"."ORDER_ID"="justin_it_hist"."ORDER_ID")

27 rows selected.

解决方法:
去掉该列上的直方图信息,使其采用稳定的执行计划
因为这两个表比较大,在主库收集统计信息会造成一定的压力,所以考虑在逻辑备库收集,然后将统计信息导入主库.
逻辑备库上上重新收集这两个表,去掉所有列的直方图,在11g,可以通过调用dbms_stats.delete_column_stats直接删除,同时收集完成后lock其统计信息,以避免再出现直方图统计

收集统计信息
begin
  dbms_stats.gather_table_stats(ownname => 'JUSTIN',tabname => 'justin_hist',method_opt => 'for all columns size 1',estimate_percent => 10);
end;

begin
  dbms_stats.gather_table_stats(ownname => 'JUSTIN',tabname => 'justin',method_opt => 'for all columns size 1',estimate_percent => 10);
end;

将统计信息导出至数据库表
begin
  dbms_stats.create_stat_table(ownname => 'JUSTIN',stattab => 'temp_justin_stats');
end;

begin
  dbms_stats.export_table_stats(ownname => 'JUSTIN',tabname => 'JUSTIN',stattab => 'temp_justin_stats');
end;

begin
  dbms_stats.create_stat_table(ownname => 'JUSTIN',stattab => 'temp_justin_hist_stats');
end;

begin
  dbms_stats.export_table_stats(ownname => 'JUSTIN',tabname => 'JUSTIN_HIST',stattab => 'temp_justin_hist_stats');
end;

采用exp导出并传输至主库

[oracle@std ~]$ exp  JUSTIN/xxxxx tables=temp_justin_stats file=temp_justin_stats.dmp

[oracle@std ~]$ scp temp_justin_stats.dmp 192.168.132.21:/home/oracle

[oracle@racdg1 ~]$ imp JUSTIN/xxxxx fromuser=JUSTIN touser=JUSTIN file=temp_justin_stats.dmp

在主库上将统计信息导入
begin
  dbms_stats.import_table_stats(ownname => 'JUSTIN',tabname => 'justin',stattab => 'temp_justin_stats');
end;

在主库上查看其直方图信息,bucket为1,已经删掉了直方图信息
SQL> select table_name,num_distinct,density,num_nulls,num_buckets,last_analyzed from user_tab_col_statistics t where t.table_name in ('justin','justin_hist') and t.column_name='justin_id';

TABLE_NAME                     NUM_DISTINCT    DENSITY  NUM_NULLS NUM_BUCKETS LAST_ANALYZED
------------------------------ ------------ ---------- ---------- ----------- -------------
justin                                 1321 0.00075700      27650          1 2011-1-19 下午
justin_hist                            5441 0.00018378     100300           1 2011-1-19 下午

然后锁住统计信息
SQL> begin
  2    dbms_stats.lock_table_stats(ownname => 'JUSTIN',tabname => 'justin');
  3  end;
  4  /

PL/SQL procedure successfully completed

SQL> begin
  2    dbms_stats.lock_table_stats(ownname => 'JUSTIN',tabname => 'justin_hist');
  3  end;
  4  /

PL/SQL procedure successfully completed

检验
SQL> select table_name,u.STATTYPE_LOCKED from user_tab_statistics u where u.STATTYPE_LOCKED is not null;

TABLE_NAME                     STATTYPE_LOCKED
------------------------------ ---------------
justin                          ALL
justin_hist                     ALL


此时查看该sql执行计划,
| Id  | Operation                       | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                             |  4445 |  2517K|  1596   (1)| 00:00:20 |
|   1 |  VIEW                           |                             |  4445 |  2517K|  1596   (1)| 00:00:20 |
|   2 |   UNION-ALL                     |                             |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID  | JUSTIN_IT                   |     6 |   366 |     3   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                |                             |   945 | 66150 |   387   (0)| 00:00:05 |
|   5 |      TABLE ACCESS BY INDEX ROWID| JUSTIN                      |   162 |  1458 |    29   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | IDX_JUSTIN_HIST_ID          |   162 |       |     3   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN           | IDX_JUSTIN_ORDER            |     6 |       |     2   (0)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID  | JUSTIN_IT_HIST              |     7 |   420 |     3   (0)| 00:00:01 |
|   9 |     NESTED LOOPS                |                             |  3500 |   235K|  1209   (1)| 00:00:15 |
|  10 |      TABLE ACCESS BY INDEX ROWID| JUSTIN_HIST                 |   511 |  4599 |    47   (0)| 00:00:01 |
|* 11 |       INDEX RANGE SCAN          | IDX_JUSTIN_HIST_ID          |   511 |       |     5   (0)| 00:00:01 |
|* 12 |      INDEX RANGE SCAN           | IDX_JUSTIN_ORDER            |     7 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-688363/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3112808