查看: 2172|回复: 2

请大家帮忙看下这个语句的优化

[复制链接]
论坛徽章:
9
发表于 2011-2-14 09:46 | 显示全部楼层 |阅读模式
这几天数据库在运行一个PKG时出现下面的错误:
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
AWRRPT报告显示下面语句有问题:
SELECT distinct a.organ_id,
                c.company_name as organ_name,
                a.commision_type_id,
                d.commision_name,
                b.policy_code,
                b.apply_code as send_code,
                e.Internal_Id AS product_code,
                round(a.money, 2) AS fee_amount,
                to_char(a.finish_time, 'yyyy-mm-dd') As finish_time,
                to_char(i.accounting_date, 'yyyy-mm-dd') As accounting_date,
                F.DR_SEG1,
                F.DR_SEG2,
                F.DR_SEG3,
                F.DR_SEG4,
                F.DR_SEG5,
                F.DR_SEG6,
                f.dr_seg7,
                f.dr_seg8,
                f.dr_seg9,
                f.dr_seg10,
                f.cr_seg1,
                f.cr_seg2,
                f.cr_seg3,
                f.cr_seg4,
                f.cr_seg5,
                f.cr_seg6,
                f.cr_seg7,
                f.cr_seg8,
                f.cr_seg9,
                f.cr_seg10,
                f.je_posting_id as cred_id,
                a.fee_id,
                null as attribute8 /*, dp.dept_name*/
  FROM t_commission_fee a,
       t_contract_master b,
       t_company_organ c,
       t_commision_type d, --t_dept dp, --t_agent ag,
       t_product_life e,
       (select * from T_BIZ_ACCOUNTING_INFO where DATA_TABLE = '6') F,
       t_gl_biz_interface i
WHERE a.policy_id = b.policy_id(+)
and a.fee_id>779999--780000

  AND a.organ_id = c.Organ_Id --and a.agent_id = ag.agent_id --and dp.dept_id = ag.dept_id
  AND a.commision_type_id = d.commision_type_id AND a.product_id = e.product_id(+) AND a.cred_id = i.posting_id(+) AND A.FEE_ID = F.FEE_ID(+) and f.je_posting_id = i.posting_id and a.organ_id in (select organ_id from t_company_organ start with organ_id = 1001 connect by parent_id = prior organ_id) and a.posted = 'Y' and i.accounting_date >= to_date('2011-01-01', 'yyyy-MM-dd') and i.accounting_date < to_date('2011-01-31', 'yyyy-MM-dd') + 1

查找发现该语句在存储过程PLAN_TABLE_OUTPUT中,下面是运行时候的输出报告:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3959216560

-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                    | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  HASH UNIQUE                         |                         |      1 |      4 |      0 |00:00:00.01 |       |       |  |
|   2 |   NESTED LOOPS OUTER                 |                         |      1 |      4 |      0 |00:00:00.01 |       |       |  |
|*  3 |    HASH JOIN                         |                         |      1 |      4 |      0 |00:00:00.01 |   703K|   703K|  |
|   4 |     NESTED LOOPS OUTER               |                         |      1 |      4 |   2524K|00:02:17.15 |       |       |  |
|   5 |      NESTED LOOPS                    |                         |      1 |      4 |   2524K|00:01:34.23 |       |       |  |
|*  6 |       HASH JOIN                      |                         |      1 |      4 |   2524K|00:00:53.84 |  2047M|    29M|   55M (1)|

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  7 |        HASH JOIN                     |                         |      1 |      4 |     11M|00:01:00.03 |  2797K|  1148K| 3144K (0)|
|*  8 |         TABLE ACCESS BY INDEX ROWID  | T_COMMISSION_FEE        |      1 |  25517 |  31948 |00:00:00.16 |       |       |  |
|*  9 |          INDEX RANGE SCAN            | PK_T_COMMISSION_FEE     |      1 |  25520 |  31948 |00:00:00.03 |       |       |  |
|* 10 |         HASH JOIN                    |                         |      1 |  33714 |     11M|00:00:12.24 |  1299K|  1299K| 1925K (0)|
|* 11 |          TABLE ACCESS FULL           | T_GL_BIZ_INTERFACE      |      1 |   7889 |  12414 |00:00:00.10 |       |       |  |
|* 12 |          TABLE ACCESS FULL           | T_BIZ_ACCOUNTING_INFO   |      1 |  32696 |  63896 |00:00:00.27 |       |       |  |
|  13 |        VIEW                          | VW_NSO_1                |      1 |      3 |      2 |00:00:00.01 |       |       |  |
|* 14 |         FILTER                       |                         |      1 |        |      2 |00:00:00.01 |       |       |  |
|* 15 |          CONNECT BY WITH FILTERING   |                         |      1 |        |      2 |00:00:00.01 |  9216 |  9216 | 8192  (0)|
|  16 |           TABLE ACCESS BY INDEX ROWID| T_COMPANY_ORGAN         |      1 |        |      1 |00:00:00.01 |       |       |  |
|* 17 |            INDEX FULL SCAN           | PK_T_COMPANY_ORGAN      |      1 |      1 |      1 |00:00:00.01 |       |       |  |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  18 |           NESTED LOOPS               |                         |      2 |        |      1 |00:00:00.01 |       |       |  |
|  19 |            BUFFER SORT               |                         |      2 |        |      2 |00:00:00.01 |  9216 |  9216 | 8192  (0)|
|  20 |             CONNECT BY PUMP          |                         |      2 |        |      2 |00:00:00.01 |       |       |  |
|* 21 |            INDEX RANGE SCAN          | T_COMPANY_ORGAN_IDX_002 |      2 |      3 |      1 |00:00:00.01 |       |       |  |
|  22 |           TABLE ACCESS FULL          | T_COMPANY_ORGAN         |      0 |      3 |      0 |00:00:00.01 |       |       |  |
|  23 |       TABLE ACCESS BY INDEX ROWID    | T_COMPANY_ORGAN         |   2524K|      1 |   2524K|00:00:30.72 |       |       |  |
|* 24 |        INDEX UNIQUE SCAN             | PK_T_COMPANY_ORGAN      |   2524K|      1 |   2524K|00:00:12.32 |       |       |  |
|  25 |      TABLE ACCESS BY INDEX ROWID     | T_PRODUCT_LIFE          |   2524K|      1 |   2524K|00:00:31.35 |       |       |  |
|* 26 |       INDEX UNIQUE SCAN              | PK_T_PRODUCT_LIFE       |   2524K|      1 |   2524K|00:00:12.00 |       |       |  |
|  27 |     TABLE ACCESS FULL                | T_COMMISION_TYPE        |      0 |     84 |      0 |00:00:00.01 |       |       |  |
|  28 |    TABLE ACCESS BY INDEX ROWID       | T_CONTRACT_MASTER       |      0 |      1 |      0 |00:00:00.01 |       |       |  |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 29 |     INDEX UNIQUE SCAN                | PK_T_CONTRACT_MASTER    |      0 |      1 |      0 |00:00:00.01 |       |       |  |
-------------------------------------------------------------------------------------------------------------------------------------------

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

   3 - access("A"."COMMISION_TYPE_ID"="D"."COMMISION_TYPE_ID")
   6 - access("A"."ORGAN_ID"="$nso_col_1")
   7 - access("A"."CRED_ID"="I"."POSTING_ID" AND "A"."FEE_ID"="T_BIZ_ACCOUNTING_INFO"."FEE_ID")
   8 - filter("A"."POSTED"='Y')
   9 - access("A"."FEE_ID">779999)

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  10 - access("T_BIZ_ACCOUNTING_INFO"."JE_POSTING_ID"="I"."POSTING_ID")
  11 - filter(("I"."ACCOUNTING_DATE">=TO_DATE('2011-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "I"."ACCOUNTING_DATE"<TO_DATE('2011-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')))
  12 - filter(("T_BIZ_ACCOUNTING_INFO"."FEE_ID">779999 AND "DATA_TABLE"=6))
  14 - filter((TO_DATE('2011-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')<TO_DATE('2011-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              TO_DATE('2011-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')>TO_DATE('2011-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')))
  15 - filter(TO_NUMBER("ORGAN_ID")=1001)
  17 - filter(TO_NUMBER("ORGAN_ID")=1001)
  21 - access("PARENT_ID"=NULL)
  24 - access("A"."ORGAN_ID"="C"."ORGAN_ID")
  26 - access("A"."PRODUCT_ID"="E"."PRODUCT_ID")

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  29 - access("A"."POLICY_ID"="B"."POLICY_ID")


68 rows selected.
论坛徽章:
9
 楼主| 发表于 2011-2-14 09:48 | 显示全部楼层
HASH JOIN                      |                         |      1 |      4 |   2524K|00:00:53.84 |  2047M|    29M
HASHJOIN也用到TEMP表空间,这个输出是到报错,并未全部输出完!

使用道具 举报

回复
论坛徽章:
9
ITPUB社区OCM联盟徽章
日期:2013-03-27 11:17:11奥运纪念徽章
日期:2013-06-18 09:13:52ITPUB社区千里马徽章
日期:2013-08-22 09:58:03大众
日期:2013-08-30 14:51:33路虎
日期:2013-12-01 18:25:42
发表于 2011-2-14 15:02 | 显示全部楼层
感觉应该想办法修改小表也就VIEW为构造输入数据集,大表作为探测输入数据集。
现在好像是反了,被探测数据在生成HASH数据集的时候过大。

[ 本帖最后由 gaopengtttt 于 2011-2-14 15:07 编辑 ]

使用道具 举报

回复

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

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

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