zyo_320

0 积分	1 好友	10 主题

发消息

[RAC性能调优] sql优化

1^#

发表于 2013-3-27 14:31:42 | 查看: 4320| 回复: 4

explain plan for
select agenId, sum(appWeight),sum(appPriceSum),sum(turnover),sum(sTurnover) from
(  select  nvl(b.agenId,a.agenId) agenId,round(nvl(b.weight,0),2) appWeight,round(nvl(b.priceSum,0),2)
appPriceSum,nvl(b.turnover,0) turnover,nvl(a.sTurnover,0) sTurnover from(  select based.agenId agenId,sum(based.SaleAmount)
weight,sum(based.NSaleSum) priceSum,count(*) turnover from T_FM_SALEINFO based,T_sys_code_agen agen where  agen.usetag > 0
and agen.id=based.agenId and based.SaleDate >= '20130321' and based.SaleDate <= '20130327' and agen.raId=24 group by based.agenId) b
full join ( select based.agenId agenId,count(*) sTurnover from T_FM_SALEINFO based,T_sys_code_agen agen where  agen.usetag > 0 and
agen.id=based.agenId and based.SaleDate >= to_char(to_date('20130321','yyyymmdd')-6,'yyyymmdd') and
  based.SaleDate <= to_char(to_date('20130327','yyyymmdd')-6,'yyyymmdd') and agen.raId=24 group by based.agenId)a
on b.agenId=a.agenId  )group by agenId order by agenId asc

执行计划：

PLAN_TABLE_OUTPUT
Plan hash value: 3896613971

---------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name             | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                |                   | 226 | 17628 | 21616 (3)| 00:04:20 |
| 1 |  SORT GROUP BY                   |                   | 226 | 17628 | 21616 (3)| 00:04:20 |
| 2 | VIEW                            |                   | 226 | 17628 | 21615 (3)| 00:04:20 |
| 3 | UNION-ALL                      |                   |    |    |          |       |
|*  4 |    HASH JOIN OUTER             |                   | 191 | 19482 |  9306 (3)| 00:01:52 |
| 5 |    VIEW                         |                   | 35 |  2520 |  3878 (3)| 00:00:47 |
| 6 |    HASH GROUP BY             |                   | 35 |  1120 |  3878 (3)| 00:00:47 |
|*  7 |       HASH JOIN                |                   | 808K| 24M|  3811 (1)| 00:00:46 |
|*  8 |       TABLE ACCESS FULL       | T_SYS_CODE_AGEN | 30 | 330 |    5 (0)| 00:00:01 |
| 9 |       TABLE ACCESS BY INDEX ROWID| T_FM_SALEINFO    | 948K| 18M|  3797 (1)| 00:00:46 |
|* 10 |       INDEX RANGE SCAN       | INDEX_SALEDATE    | 948K|    | 430 (1)| 00:00:06 |
|  11 |    VIEW                         |                   | 35 |  1050 |  5428 (3)| 00:01:06 |
|  12 |    HASH GROUP BY             |                   | 35 | 840 |  5428 (3)| 00:01:06 |
|* 13 |       HASH JOIN                |                   |  1131K| 25M|  5333 (1)| 00:01:04 |
|* 14 |       TABLE ACCESS FULL       | T_SYS_CODE_AGEN | 30 | 330 |    5 (0)| 00:00:01 |
|  15 |       TABLE ACCESS BY INDEX ROWID| T_FM_SALEINFO    |  1328K| 16M|  5315 (1)| 00:01:04 |
|* 16 |       INDEX RANGE SCAN       | INDEX_SALEDATE    |  1328K|    | 602 (1)| 00:00:08 |
|  17 |    HASH GROUP BY                |                   | 35 | 840 | 12309 (2)| 00:02:28 |
|* 18 |    FILTER                      |                   |    |    |          |       |
|* 19 |    HASH JOIN                   |                   |  1131K| 25M|  5333 (1)| 00:01:04 |
|* 20 |       TABLE ACCESS FULL          | T_SYS_CODE_AGEN | 30 | 330 |    5 (0)| 00:00:01 |
|  21 |       TABLE ACCESS BY INDEX ROWID | T_FM_SALEINFO    |  1328K| 16M|  5315 (1)| 00:01:04 |
|* 22 |       INDEX RANGE SCAN          | INDEX_SALEDATE    |  1328K|    | 602 (1)| 00:00:08 |
|  23 |    SORT GROUP BY NOSORT       |                   |    1 | 24 | 197 (3)| 00:00:03 |
|  24 |       NESTED LOOPS             |                   | 774 | 18576 | 197 (3)| 00:00:03 |
|* 25 |       TABLE ACCESS BY INDEX ROWID| T_SYS_CODE_AGEN |    1 | 11 |    1 (0)| 00:00:01 |
|* 26 |       INDEX UNIQUE SCAN       | PK_T_SYS_CODE_AGEN |    1 |    |    1 (0)| 00:00:01 |
|* 27 |       TABLE ACCESS BY INDEX ROWID| T_FM_SALEINFO    | 774 | 10062 | 196 (3)| 00:00:03 |
|* 28 |       INDEX RANGE SCAN       | SALEAGENID       | 40095 |    |    4  (75)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

4 - access("B"."AGENID"="A"."AGENID"(+))
7 - access("AGEN"."ID"="BASED"."AGENID")
8 - filter("AGEN"."RAID"=24 AND "AGEN"."USETAG">0)
  10 - access("BASED"."SALEDATE">='20130321' AND "BASED"."SALEDATE"<='20130327')
  13 - access("AGEN"."ID"="BASED"."AGENID")
  14 - filter("AGEN"."RAID"=24 AND "AGEN"."USETAG">0)
  16 - access("BASED"."SALEDATE">='20130315' AND "BASED"."SALEDATE"<='20130321')
  18 - filter( NOT EXISTS (SELECT /*+ UNNEST */ 0 FROM "T_SYS_CODE_AGEN" "AGEN","T_FM_SALEINFO"
            "BASED" WHERE "AGEN"."ID"="BASED"."AGENID" AND "BASED"."SALEDATE">='20130321' AND
            "BASED"."SALEDATE"<='20130327' AND "BASED"."AGENID"=:B1 AND "AGEN"."ID"=:B2 AND "AGEN"."RAID"=24
            AND "AGEN"."USETAG">0 GROUP BY "BASED"."AGENID"))
  19 - access("AGEN"."ID"="BASED"."AGENID")
  20 - filter("AGEN"."RAID"=24 AND "AGEN"."USETAG">0)
  22 - access("BASED"."SALEDATE">='20130315' AND "BASED"."SALEDATE"<='20130321')
  25 - filter("AGEN"."RAID"=24 AND "AGEN"."USETAG">0)
  26 - access("AGEN"."ID"=:B1)
  27 - filter("BASED"."SALEDATE">='20130321' AND "BASED"."SALEDATE"<='20130327')
  28 - access("AGEN"."ID"="BASED"."AGENID")
   filter("BASED"."AGENID"=:B1)
@?/rdbms/admin/sqltrpt：报告部分信息如下：
。。。。。。。。。。
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)

--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit<=10%)
  ---------------------------------------
  - Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_31076',
         replace => TRUE);

-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- The optimizer could not merge the view at line ID 122 of the execution

  plan. A view on the right side of an outer join can be merged only if it
  contains a single table with a simple "WHERE" clause.
- The optimizer could not merge the view at line ID 113 of the execution
  plan. The optimizer cannot merge a view that contains a set operator.
- The optimizer could not merge the view at line ID 2 of the execution plan.
  The optimizer cannot merge a view that contains a set operator.
。。。。。。。。
详细见附件。
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_31076',
         replace => TRUE);    ---对这有些不明白？想请教刘大，谢谢！