查看: 2282|回复: 6

以下语句,还能进一步优化吗?

[复制链接]
认证徽章
论坛徽章:
50
2014年世界杯参赛球队: 荷兰
日期:2014-07-11 07:56:59蛋疼蛋
日期:2012-03-06 07:22:542012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-01-04 11:53:29蛋疼蛋
日期:2011-11-11 15:47:00ITPUB十周年纪念徽章
日期:2011-11-01 16:23:26
发表于 2010-3-2 14:33 | 显示全部楼层 |阅读模式
语句如下,调优前语句如下(环境情况见

SQL> select  
  2                     a.ne_id,  
  3                     c.ne_name||'|'||d.attri_value||'|'||e.attri_value  
  4                     from net_element a, net_element b, net_element c, ne_property d, ne_property e  
  5                             where a.ne_id in (  
  6                     SELECT DISTINCT ne_id from ne_task_config
  7                             where agent_ID = 1
)
  8                             and a.ne_type_id like '1001__'  
  9                             and a.ne_id=b.parent_ne_id and b.ne_type_id like '1001__06' and b.state='0SA'  
10                             and b.ne_id=c.parent_ne_id and c.state='0SA'  
11                             and d.ne_id(+)=c.ne_id and d.ne_type_attr_id(+) like '1001__35'  
12                             and e.ne_id(+)=c.ne_id and e.ne_type_attr_id(+) like '1001__34'  
13                           order by a.ne_id ;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2249170592

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                          |     1 |   104 |    19  (16)| 00:00:01 |
|   1 |  SORT ORDER BY                     |                          |     1 |   104 |    19  (16)| 00:00:01 |
|*  2 |   HASH JOIN OUTER                  |                          |     1 |   104 |    18  (12)| 00:00:01 |
|*  3 |    HASH JOIN OUTER                 |                          |     1 |    84 |    15  (14)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID    | NET_ELEMENT              |     1 |    26 |     3   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                  |                          |     1 |    64 |    11  (10)| 00:00:01 |
|   6 |       NESTED LOOPS                 |                          |     1 |    38 |     8  (13)| 00:00:01 |
|   7 |        NESTED LOOPS                |                          |     1 |    20 |     5  (20)| 00:00:01 |
|   8 |         SORT UNIQUE                |                          |     1 |    10 |     3   (0)| 00:00:01 |
|*  9 |          TABLE ACCESS FULL         | NE_TASK_CONFIG           |     1 |    10 |     3   (0)| 00:00:01 |
|* 10 |         TABLE ACCESS BY INDEX ROWID| NET_ELEMENT              |     1 |    10 |     1   (0)| 00:00:01 |
|* 11 |          INDEX UNIQUE SCAN         | PK_NET_ELEMENT           |     1 |       |     0   (0)| 00:00:01 |
|* 12 |        TABLE ACCESS BY INDEX ROWID | NET_ELEMENT              |     1 |    18 |     3   (0)| 00:00:01 |
|* 13 |         INDEX RANGE SCAN           | IDX_NE_ELEM_PARENT_NE_ID |     7 |       |     1   (0)| 00:00:01 |
|* 14 |       INDEX RANGE SCAN             | IDX_NE_ELEM_PARENT_NE_ID |     7 |       |     1   (0)| 00:00:01 |
|* 15 |     TABLE ACCESS FULL              | NE_PROPERTY              |    62 |  1240 |     3   (0)| 00:00:01 |
|* 16 |    TABLE ACCESS FULL               | NE_PROPERTY              |    62 |  1240 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

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

   2 - access("D"."NE_ID"(+)="C"."NE_ID")
   3 - access("E"."NE_ID"(+)="C"."NE_ID")
   4 - filter("C"."STATE"='0SA')
   9 - filter("AGENT_ID"=1)
  10 - filter(TO_CHAR("A"."NE_TYPE_ID") LIKE '1001__')
  11 - access("A"."NE_ID"="NE_ID")
  12 - filter(TO_CHAR("B"."NE_TYPE_ID") LIKE '1001__06' AND "B"."STATE"='0SA')
  13 - access("A"."NE_ID"="B"."PARENT_NE_ID")
  14 - access("B"."NE_ID"="C"."PARENT_NE_ID")
  15 - filter(TO_CHAR("E"."NE_TYPE_ATTR_ID"(+)) LIKE '1001__34')
  16 - filter(TO_CHAR("D"."NE_TYPE_ATTR_ID"(+)) LIKE '1001__35')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        373  bytes sent via SQL*Net to client
        389  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>


做了如下修改
1、确认排序无意义,去掉ORDER BY
2、把SELECT DISTINCT ne_id from ne_task_config     where agent_ID = 1
  修改为 select ne_id from ne_task_config where agent_id=1 and rownum=1  希望能避免排序,不过原本就没排序,只是确实单独跑会快一点点

SQL> SELECT DISTINCT ne_id from ne_task_config     where agent_ID = 1;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 2068196749

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |    10 |     4  (25)| 00:00:01 |
|   1 |  HASH UNIQUE       |                |     1 |    10 |     4  (25)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| NE_TASK_CONFIG |     1 |    10 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - filter("AGENT_ID"=1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        274  bytes sent via SQL*Net to client
        389  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> select ne_id from ne_task_config where agent_id=1 and rownum=1;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 2847239454

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |    10 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |                |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| NE_TASK_CONFIG |     1 |    10 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   2 - filter("AGENT_ID"=1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        274  bytes sent via SQL*Net to client
        389  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed





修改完后如下,是有一些收效大,大家看看,还有提升性能的空间吗?(个人感觉表自连接写法,这么多次扫描表,要是能改写SQL,降低到扫描表次数,比如分析函数啥的,能否等价替代,那才是正道)
SQL> select  
  2                     a.ne_id,  
  3                     c.ne_name||'|'||d.attri_value||'|'||e.attri_value  
  4                     from net_element a, net_element b, net_element c, ne_property d, ne_property e  
  5                             where a.ne_id in (  
  6                     SELECT  ne_id from ne_task_config
  7                             where agent_ID = 1 AND ROWNUM=1
)
  8                             and a.ne_type_id like '1001__'  
  9                             and a.ne_id=b.parent_ne_id and b.ne_type_id like '1001__06' and b.state='0SA'  
10                             and b.ne_id=c.parent_ne_id and c.state='0SA'  
11                             and d.ne_id(+)=c.ne_id and d.ne_type_attr_id(+) like '1001__35'  
12                             and e.ne_id(+)=c.ne_id and e.ne_type_attr_id(+) like '1001__34'  ;
13                       --- order by a.ne_id --注释掉

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2666028570

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                          |     1 |   107 |    18  (12)| 00:00:01 |
|*  1 |  HASH JOIN OUTER                  |                          |     1 |   107 |    18  (12)| 00:00:01 |
|*  2 |   HASH JOIN OUTER                 |                          |     1 |    87 |    15  (14)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID    | NET_ELEMENT              |     1 |    26 |     3   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                  |                          |     1 |    67 |    11  (10)| 00:00:01 |
|   5 |      NESTED LOOPS                 |                          |     1 |    41 |     8  (13)| 00:00:01 |
|   6 |       NESTED LOOPS                |                          |     1 |    23 |     5  (20)| 00:00:01 |
|   7 |        VIEW                       | VW_NSO_1                 |     1 |    13 |     3   (0)| 00:00:01 |
|   8 |         HASH UNIQUE               |                          |     1 |    10 |            |          |
|*  9 |          COUNT STOPKEY            |                          |       |       |            |          |
|* 10 |           TABLE ACCESS FULL       | NE_TASK_CONFIG           |     1 |    10 |     3   (0)| 00:00:01 |
|* 11 |        TABLE ACCESS BY INDEX ROWID| NET_ELEMENT              |     1 |    10 |     1   (0)| 00:00:01 |
|* 12 |         INDEX UNIQUE SCAN         | PK_NET_ELEMENT           |     1 |       |     0   (0)| 00:00:01 |
|* 13 |       TABLE ACCESS BY INDEX ROWID | NET_ELEMENT              |     1 |    18 |     3   (0)| 00:00:01 |
|* 14 |        INDEX RANGE SCAN           | IDX_NE_ELEM_PARENT_NE_ID |     7 |       |     1   (0)| 00:00:01 |
|* 15 |      INDEX RANGE SCAN             | IDX_NE_ELEM_PARENT_NE_ID |     7 |       |     1   (0)| 00:00:01 |
|* 16 |    TABLE ACCESS FULL              | NE_PROPERTY              |    62 |  1240 |     3   (0)| 00:00:01 |
|* 17 |   TABLE ACCESS FULL               | NE_PROPERTY              |    62 |  1240 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

   1 - access("D"."NE_ID"(+)="C"."NE_ID")
   2 - access("E"."NE_ID"(+)="C"."NE_ID")
   3 - filter("C"."STATE"='0SA')
   9 - filter(ROWNUM=1)
  10 - filter("AGENT_ID"=1)
  11 - filter(TO_CHAR("A"."NE_TYPE_ID") LIKE '1001__')
  12 - access("A"."NE_ID"="$nso_col_1")
  13 - filter(TO_CHAR("B"."NE_TYPE_ID") LIKE '1001__06' AND "B"."STATE"='0SA')
  14 - access("A"."NE_ID"="B"."PARENT_NE_ID")
  15 - access("B"."NE_ID"="C"."PARENT_NE_ID")
  16 - filter(TO_CHAR("E"."NE_TYPE_ATTR_ID"(+)) LIKE '1001__34')
  17 - filter(TO_CHAR("D"."NE_TYPE_ATTR_ID"(+)) LIKE '1001__35')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        373  bytes sent via SQL*Net to client
        389  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed



附:

1 、环境是10G

2、索引情况
SQL> SELECT index_name,table_name,column_position FROM user_ind_columns WHERE lower(table_name) IN('ne_task_config','net_element','ne_property');

INDEX_NAME                                TABLE_NAME                     COLUMN_POSITION
------------------------------ ------------------------------ ---------------
PK_NET_ELEMENT                          NET_ELEMENT                                  1
IDX_NE_ELEM_PARENT_NE_ID       NET_ELEMENT                                  1
IDX_NE_PATH                                 NET_ELEMENT                                  1
PK_NE_TASK_CONFIG                     NE_TASK_CONFIG                            1

3、记录情况:

SQL> SELECT COUNT(*) FROM ne_task_config;

  COUNT(*)
----------
        65

SQL> SELECT COUNT(*) FROM net_element;

  COUNT(*)
----------
      1150

SQL> SELECT count(*) FROM ne_property;

  COUNT(*)
----------
      1249

4、统计信息直方图都已收集

[ 本帖最后由 wabjtam123 于 2010-3-2 14:40 编辑 ]
招聘 : Java研发
认证徽章
论坛徽章:
71
马上加薪
日期:2014-02-19 11:55:14蜘蛛蛋
日期:2012-12-26 18:16:01茶鸡蛋
日期:2012-11-16 08:12:48ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:07奥运会纪念徽章:网球
日期:2012-08-23 14:58:08奥运会纪念徽章:沙滩排球
日期:2012-07-19 17:28:14版主2段
日期:2012-07-07 02:21:02咸鸭蛋
日期:2012-03-23 18:17:482012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:51
发表于 2010-3-2 15:11 | 显示全部楼层
实际环境也是没数据返回?
这里
SELECT DISTINCT ne_id from ne_task_config     where agent_ID = 1
返回0行
可能会跟实际情况有很大的偏差吧

使用道具 举报

回复
认证徽章
论坛徽章:
131
2006年度最佳技术回答
日期:2007-01-24 12:58:48福特
日期:2013-10-24 13:57:422014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:142013年新春福章
日期:2013-02-25 14:51:24
发表于 2010-3-2 15:16 | 显示全部楼层
ne_property创建(ne_id,ne_type_attr_id)的index
ne_task_config  创建(agent_id)的index
不过,table都很小,估计怎么跑都是毫秒级的。

使用道具 举报

回复
认证徽章
论坛徽章:
50
2014年世界杯参赛球队: 荷兰
日期:2014-07-11 07:56:59蛋疼蛋
日期:2012-03-06 07:22:542012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-01-04 11:53:29蛋疼蛋
日期:2011-11-11 15:47:00ITPUB十周年纪念徽章
日期:2011-11-01 16:23:26
发表于 2010-3-2 17:24 | 显示全部楼层
原帖由 rollingpig 于 2010-3-2 15:16 发表
ne_property创建(ne_id,ne_type_attr_id)的index
ne_task_config  创建(agent_id)的index
不过,table都很小,估计怎么跑都是毫秒级的。


ROLLINGPIG大师,建了如下索引后,进一步优化了,看来还是多建一下索引让ORACLE去选择用否。

    CREATE INDEX idx_ne_id_and_type_attr ON ne_property (ne_id,ne_type_attr_id);
      CREATE INDEX idx_agent_id ON ne_task_config(AGENT_ID);

SQL>  select  
  2                         a.ne_id,  
  3                         c.ne_name||'|'||d.attri_value||'|'||e.attri_value  
  4                         from net_element a, net_element b, net_element c, ne_property d, ne_property e  
  5                                 where a.ne_id in (  
  6                         SELECT  ne_id from ne_task_config
  7                                 where agent_ID = 1 AND ROWNUM=1)
  8                                 and a.ne_type_id like '1001__'  
  9                                 and a.ne_id=b.parent_ne_id and b.ne_type_id like '1001__06' and b.state='0SA'  
10                                and b.ne_id=c.parent_ne_id and c.state='0SA'  
11                                and d.ne_id(+)=c.ne_id and d.ne_type_attr_id(+) like '1001__35'  
12                               and e.ne_id(+)=c.ne_id and e.ne_type_attr_id(+) like '1001__34'  ;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 224950889

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                          |     1 |   107 |    13   (8)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER                 |                          |     1 |   107 |    13   (8)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER                |                          |     1 |    87 |    12   (9)| 00:00:01 |
|   3 |    NESTED LOOPS                     |                          |     1 |    67 |    10  (10)| 00:00:01 |
|   4 |     NESTED LOOPS                    |                          |     1 |    41 |     7  (15)| 00:00:01 |
|   5 |      NESTED LOOPS                   |                          |     1 |    23 |     4  (25)| 00:00:01 |
|   6 |       VIEW                          | VW_NSO_1                 |     1 |    13 |     2   (0)| 00:00:01 |
|   7 |        HASH UNIQUE                  |                          |     1 |    10 |            |          |
|*  8 |         COUNT STOPKEY               |                          |       |       |            |          |
|   9 |          TABLE ACCESS BY INDEX ROWID| NE_TASK_CONFIG           |     1 |    10 |     2   (0)| 00:00:01 |
|* 10 |           INDEX RANGE SCAN          | IDX_AGENT_ID             |     1 |       |     1   (0)| 00:00:01 |
|* 11 |       TABLE ACCESS BY INDEX ROWID   | NET_ELEMENT              |     1 |    10 |     1   (0)| 00:00:01 |
|* 12 |        INDEX UNIQUE SCAN            | PK_NET_ELEMENT           |     1 |       |     0   (0)| 00:00:01 |
|* 13 |      TABLE ACCESS BY INDEX ROWID    | NET_ELEMENT              |     1 |    18 |     3   (0)| 00:00:01 |
|* 14 |       INDEX RANGE SCAN              | IDX_NE_ELEM_PARENT_NE_ID |     7 |       |     1   (0)| 00:00:01 |
|* 15 |     TABLE ACCESS BY INDEX ROWID     | NET_ELEMENT              |     1 |    26 |     3   (0)| 00:00:01 |
|* 16 |      INDEX RANGE SCAN               | IDX_NE_ELEM_PARENT_NE_ID |     7 |       |     1   (0)| 00:00:01 |
|  17 |    TABLE ACCESS BY INDEX ROWID      | NE_PROPERTY              |     1 |    20 |     2   (0)| 00:00:01 |
|* 18 |     INDEX RANGE SCAN                | IDX_NE_ID_AND_TYPE_ATTR  |     1 |       |     1   (0)| 00:00:01 |
|  19 |   TABLE ACCESS BY INDEX ROWID       | NE_PROPERTY              |     1 |    20 |     1   (0)| 00:00:01 |
|* 20 |    INDEX RANGE SCAN                 | IDX_NE_ID_AND_TYPE_ATTR  |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

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

   8 - filter(ROWNUM=1)
  10 - access("AGENT_ID"=1)
  11 - filter(TO_CHAR("A"."NE_TYPE_ID") LIKE '1001__')
  12 - access("A"."NE_ID"="$nso_col_1")
  13 - filter(TO_CHAR("B"."NE_TYPE_ID") LIKE '1001__06' AND "B"."STATE"='0SA')
  14 - access("A"."NE_ID"="B"."PARENT_NE_ID")
  15 - filter("C"."STATE"='0SA')
  16 - access("B"."NE_ID"="C"."PARENT_NE_ID")
  18 - access("E"."NE_ID"(+)="C"."NE_ID")
       filter(TO_CHAR("E"."NE_TYPE_ATTR_ID"(+)) LIKE '1001__34')
  20 - access("D"."NE_ID"(+)="C"."NE_ID")
       filter(TO_CHAR("D"."NE_TYPE_ATTR_ID"(+)) LIKE '1001__35')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        373  bytes sent via SQL*Net to client
        389  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


不过我郁闷的是,其实不管怎么优化,其实效果都是进步不会太明显,只是改善,不过听说系统有的时候这个语句慢的的要命(1分钟算慢的要命),大多时候不会慢

我现在正在用AWR观察上周慢的那天,看看那天的快照点产生的报表有啥异常。。。。。

[ 本帖最后由 wabjtam123 于 2010-3-2 17:34 编辑 ]

使用道具 举报

回复
发表于 2010-3-2 17:26 | 显示全部楼层
路过看看

使用道具 举报

回复
论坛徽章:
2
2010新春纪念徽章
日期:2010-03-01 11:04:572010年世界杯参赛球队:科特迪瓦
日期:2010-03-07 14:39:46
发表于 2010-3-2 19:16 | 显示全部楼层
关联表太多建议在程序中拆成多个语句

使用道具 举报

回复
论坛徽章:
3
2010新春纪念徽章
日期:2010-03-01 11:20:072011新春纪念徽章
日期:2011-02-18 11:42:49ITPUB十周年纪念徽章
日期:2011-11-01 16:24:04
发表于 2010-3-5 10:31 | 显示全部楼层
可以试试connect by ,组建另外一张表。

使用道具 举报

回复

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

本版积分规则 发表回复

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