Oracle多一个条件慢了5分钟

AND PO.DOMS_STATUS IN ('IP,'PP')这个当然很慢了，你可以分别用AND PO.DOMS_STA＝‘IP’
和AND PO.DOMS_STA＝‘PP’
用union all 把两次结果合并

我加上这条AND PO.DOMS_STA＝‘IP’
也一样的变慢了很多,差不多也是5分多钟才能出来...

很简单。加了这个条件后oracle认为需要全表扫描了。
建议对表OFSPP.PROD_ORDER 进行一次表分析。这样不全表扫描的概率会大些。另外希望提供
show parameter opt
所有参数

因为只是只读的权限,所以没有办法创建索引
<ExplainPlan>
  <PlanElement id="0" operation="SELECT STATEMENT" optimizer="ALL_ROWS" cost="14,621" cardinality="632" bytes="75,840" cpu_cost="118,556,931" io_cost="14,614" time="176">
    <PlanElements>
      <PlanElement id="1" operation="NESTED LOOPS">
        <PlanElements>
          <PlanElement id="2" operation="NESTED LOOPS" cost="14,621" cardinality="632" bytes="75,840" cpu_cost="118,556,931" io_cost="14,614" time="176">
            <PlanElements>
              <PlanElement id="3" operation="NESTED LOOPS" cost="10,959" cardinality="1,830" bytes="102,480" cpu_cost="87,069,544" io_cost="10,954" time="132">
                <PlanElements>
                  <PlanElement id="4" operation="NESTED LOOPS" cost="7,307" cardinality="1,825" bytes="69,350" cpu_cost="56,933,538" io_cost="7,304" time="88">
                    <PlanElements>
                      <PlanElement object_ID="0" id="5" operation="TABLE ACCESS" option="BY GLOBAL INDEX ROWID" optimizer="ANALYZED" object_owner="OFSPP" object_name="PART" object_type="TABLE" object_instance="3" cost="1,830" cardinality="1,825" bytes="29,200" partition_id="5" partition_start="ROW LOCATION" partition_stop="ROW LOCATION" cpu_cost="13,983,404" io_cost="1,829" time="22">
                        <PlanElements>
                          <PlanElement object_ID="1" id="6" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="OFSPP" object_name="PART_IDX2" object_type="INDEX" search_columns="1" cost="22" cardinality="1,825" cpu_cost="532,472" io_cost="22" access_predicates=""PA"."PART_NUM"='3DJRJ'" time="1"/>
                        </PlanElements>
                      </PlanElement>
                      <PlanElement object_ID="2" id="7" operation="TABLE ACCESS" option="BY GLOBAL INDEX ROWID" optimizer="ANALYZED" object_owner="OFSPP" object_name="LINE_SKU" object_type="TABLE" object_instance="4" cost="3" cardinality="1" bytes="22" partition_id="7" partition_start="ROW LOCATION" partition_stop="ROW LOCATION" cpu_cost="23,534" io_cost="3" time="1">
                        <PlanElements>
                          <PlanElement object_ID="3" id="8" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="OFSPP" object_name="LINE_SKU_PK" object_type="INDEX (UNIQUE)" search_columns="1" cost="2" cardinality="1" cpu_cost="16,143" io_cost="2" access_predicates=""PA"."LINE_SKU_SEQ"="LS"."LINE_SKU_SEQ"" time="1"/>
                        </PlanElements>
                      </PlanElement>
                    </PlanElements>
                  </PlanElement>
                  <PlanElement object_ID="4" id="9" operation="TABLE ACCESS" option="BY GLOBAL INDEX ROWID" optimizer="ANALYZED" object_owner="OFSPP" object_name="PROD_ORDER_LINE" object_type="TABLE" object_instance="2" cost="2" cardinality="1" bytes="18" partition_id="9" partition_start="ROW LOCATION" partition_stop="ROW LOCATION" cpu_cost="16,513" io_cost="2" time="1">
                    <PlanElements>
                      <PlanElement object_ID="5" id="10" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="OFSPP" object_name="PROD_ORDER_LINE_PK" object_type="INDEX (UNIQUE)" search_columns="2" cost="1" cardinality="1" cpu_cost="9,021" io_cost="1" access_predicates=""LS"."PROD_ORDER_NUM"="POL"."PROD_ORDER_NUM" AND "LS"."LINE_NUM"="POL"."LINE_NUM"" time="1"/>
                    </PlanElements>
                  </PlanElement>
                </PlanElements>
              </PlanElement>
              <PlanElement object_ID="6" id="11" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="OFSPP" object_name="PROD_ORDER_PK" object_type="INDEX (UNIQUE)" search_columns="1" cost="1" cardinality="1" cpu_cost="9,021" io_cost="1" access_predicates=""POL"."PROD_ORDER_NUM"="PO"."PROD_ORDER_NUM"" time="1"/>
            </PlanElements>
          </PlanElement>
          <PlanElement object_ID="7" id="12" operation="TABLE ACCESS" option="BY GLOBAL INDEX ROWID" optimizer="ANALYZED" object_owner="OFSPP" object_name="PROD_ORDER" object_type="TABLE" object_instance="1" cost="2" cardinality="1" bytes="64" partition_id="12" partition_start="ROW LOCATION" partition_stop="ROW LOCATION" cpu_cost="17,206" io_cost="2" filter_predicates=""PO"."FACILITY"='CCC2' OR "PO"."FACILITY"='CCC4'" time="1"/>
        </PlanElements>
      </PlanElement>
    </PlanElements>
  </PlanElement>
</ExplainPlan>

如果你实在是没权限你就加hint吧

感谢lookat800的建议,但我是刚接触ORACLE的,还不知道该如何加HINT,希望指点一下,非常感谢!

给你个链接地址，自己去下载吧
http://wenku.baidu.com/view/02f73d09f78a6529647d530b.html

SQL上优化空间不是很大，主要需要优化数据库：1、一些表字段建立索引2、相关表进行表分析，让SQL能够走正确的执行计划如果这些都做不了，一个最简单的方法是加rule hint，让sql走基于规则的优化器，不走基于代价的优化器。oracle基于代价的优化器在没有分析表的时候经常出问题。但这种方法不能保证一定有效。SELECT /*+rule*/TO_CHAR(PO.CREATE_DATE,'YYYY-MM-DD') AS ORD_DATE ..........

1.如果这个SQL经常用到你可以添加索引
2.可以添加hint试试

oracle 中，如果用了in，not in 会导致索引失效，你把in换成exists

能否把
OFSPP.PROD_ORDER_LINE POL,
OFSPP.LINE_SKU LS,
OFSPP.PART PA,
OFSPP.PROD_ORDER PO
的表结构和索引贴上来？
各表的记录数各是多少？

建立索引，用exists代替in，这样能避免全表扫描

Plan
SELECT STATEMENT  ALL_ROWSCost: 14,325  Bytes: 90,810  Cardinality: 1,009
12 NESTED LOOPS
10 NESTED LOOPS  Cost: 14,325  Bytes: 90,810  Cardinality: 1,009
8 NESTED LOOPS  Cost: 10,743  Bytes: 100,240  Cardinality: 1,790
5 NESTED LOOPS  Cost: 7,161  Bytes: 68,020  Cardinality: 1,790
2 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE OFSPP.PART Cost: 1,789  Bytes: 28,640  Cardinality: 1,790  Partition #: 5  Partition access computed by row location
1 INDEX RANGE SCAN INDEX OFSPP.PART_IDX2 Cost: 22  Cardinality: 1,790
4 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE OFSPP.LINE_SKU Cost: 3  Bytes: 22  Cardinality: 1  Partition #: 7  Partition access computed by row location
3 INDEX UNIQUE SCAN INDEX (UNIQUE) OFSPP.LINE_SKU_PK Cost: 2  Cardinality: 1
7 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE OFSPP.PROD_ORDER_LINE Cost: 2  Bytes: 18  Cardinality: 1  Partition #: 9  Partition access computed by row location
6 INDEX UNIQUE SCAN INDEX (UNIQUE) OFSPP.PROD_ORDER_LINE_PK Cost: 1  Cardinality: 1
9 INDEX UNIQUE SCAN INDEX (UNIQUE) OFSPP.PROD_ORDER_PK Cost: 1  Cardinality: 1
11 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE OFSPP.PROD_ORDER Cost: 2  Bytes: 34  Cardinality: 1  Partition #: 12  Partition access computed by row location

OFSPP.PROD_ORDER_LINE POL, 80000条记录
OFSPP.LINE_SKU LS,         650000条记录
OFSPP.PART PA,            1650000条记录
OFSPP.PROD_ORDER PO        50000条记录

在表OFSPP.PROD_ORDER PO中查到有索引,索引名:PROD_ORDER_IDX3 对应的DOMS_STATUS数据
我该如何利用索引来查询这个条件 AND PO.DOMS_STATUS IN ('IP,'PP')

INFO INDEX_NAME OWNER UNIQUE COLUMN_NAME ORDER POSITION LOGGING DEGREE
PLAIN PROD_ORDER_FK2_IDX OFSPP N STATUS_CODE Asc 1 YES 1
PLAIN PROD_ORDER_FK3_IDX OFSPP N SALES_ORDER_ID Asc 1 YES 1
PLAIN PROD_ORDER_FK3_IDX OFSPP N BUID Asc 2 YES 1
PLAIN PROD_ORDER_FK4_IDX OFSPP N CHANNEL_CODE Asc 1 YES 1
PLAIN PROD_ORDER_IDX1 OFSPP N CREATE_DATE Asc 1 YES 1
PLAIN PROD_ORDER_IDX2 OFSPP N MODIFY_DATE Asc 1 YES 1
PLAIN PROD_ORDER_IDX3 OFSPP N DOMS_STATUS Asc 1 YES 1
PLAIN PROD_ORDER_IDX4 OFSPP N CHANNEL_HOLD Asc 1 YES 1
PLAIN PROD_ORDER_IDX4 OFSPP N HELD_CODE Asc 2 YES 1
PLAIN PROD_ORDER_PK OFSPP Y PROD_ORDER_NUM Asc 1 YES 1
PLAIN PROD_ORDER_UNIQUE_CHANNEL OFSPP Y SALES_ORDER_ID Asc 1 YES 1
PLAIN PROD_ORDER_UNIQUE_CHANNEL OFSPP Y BUID Asc 2 YES 1
PLAIN PROD_ORDER_UNIQUE_CHANNEL OFSPP Y CHANNEL_CODE Asc 3 YES 1

建议你在写where 后面的条件时，先把排除大量数据的条件放在前面，还有最好不要用AND PO.DOMS_STATUS IN ('IP,'PP')，这样会对全表进行查询，你可以在查询的列建个索引，还有用PO.DOMS_STATUS='IP' or PO.DOMS_STATUS='pp',并把这个放在 where 的后面，这样可能会快点，你试试

select /*+index(tablename indexname)*/ * from table

是不是这样写的? 好像查询速度没有改善...
select /*+index(doms_status PROD_ORDER_IDX3)*/ * from ofspp.PROD_ORDER

我杂感觉你HINT是不是写错了？

index()括号中间是表明空格索引名你的doms_status是什么东西啊
还有如果这样也不走索引估计oracle就认为不应该走索引那就只能找dba优化了

doms_status是表ofspp.PROD_ORDER列名

调试易

Oracle多一个条件慢了5分钟

解决方案 »