对表和索引进行analyze后，查询性能大大降低

显示全部楼层 · 2005-10-30 17:05:33

查询一视图：v_customer_product ，该视图的ddl语句是：
CREATE OR REPLACE VIEW V_CUSTOMER_PRODUCT AS
SELECT CP_PROD_ID,CP_CUS_ID,CUS_NAME,CP_PROD_CODE,

CP_PROD_BUY_DATE,CP_PROD_PRICE,CP_PROD_MACHINE_CODE,
CP_PROD_MACHINE_CODE2,CP_PROD_BUY_IVOICE,
CP_OWNER_ID,CP_OWNER_COM,CUS_REGION_CODE,CUS_HOME_PHONE,CUS_OFFICE_PHONE,CUS_MOBILE_PHONE,CUS_PERSON_PHONE
,EB_CUSTOMER.CUS_ID
FROM EB_CUSTOMER_PRODUCT INNER JOIN EB_CUSTOMER ON
EB_CUSTOMER_PRODUCT.CP_CUS_ID = EB_CUSTOMER.CUS_ID
分析前的执行计划：
SQL> explain plan for select * from v_customer_product;

------------------------------------------------------------------------------------
| Id| Operation
|Name
| Rows| Bytes | Cost|
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
| | | |
| 1 |TABLE ACCESS BY INDEX ROWID| EB_CUSTOMER_PRODUCT| | | |
| 2 | NESTED LOOPS
|
| | | |
| 3 |TABLE ACCESS FULL| EB_CUSTOMER
| | | |
|*4 |INDEX RANGE SCAN | IDX_CP_CUS_ID| | | |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("EB_CUSTOMER_PRODUCT"."CP_CUS_ID"="EB_CUSTOMER"."CUS_ID&quot

Note: rule based optimization
17 rows selected

对表eb_customer, eb_customer_product及其索引进行分析：
sql> execute dbms_stats.gather_table_stats(ownname=>'CALL',tabname=>'eb_customer',cascade=>TRUE)
sql> execute dbms_stats.gather_table_stats(ownname=>'CALL',tabname=>'eb_customer_product',cascade=>TRUE)
分析后的执行计划：
-------------------------------------------------------------------------------------
| Id| Operation
|Name
| Rows| Bytes |TempSpc| Cost|
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 930K|71M| |4807 |
|*1 |HASH JOIN |
| 930K|71M|29M|4807 |
| 2 | TABLE ACCESS FULL| EB_CUSTOMER
| 755K|20M| |1218 |
| 3 | TABLE ACCESS FULL| EB_CUSTOMER_PRODUCT| 930K|46M| |1022 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("EB_CUSTOMER_PRODUCT"."CP_CUS_ID"="EB_CUSTOMER"."CUS_ID&quot

Note: cpu costing is off
16 rows selected.

千问 · 2005-10-30 17:05:33

SQL> select index_name, table_name, column_name from user_ind_columns where table_name in ('EB_CUSTOMER',
'EB_CUSTOMER_PRODUCT');
INDEX_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
PK_EB_CUSTOMER_1_1 EB_CUSTOMER CUS_ID
IDX_CUS_ADDRESS EB_CUSTOMER CUS_ADDRESS
IDX_CUS_BP_PHONE EB_CUSTOMER CUS_BP_PHONE
IDX_CUS_HOME_PHONE EB_CUSTOMER CUS_HOME_PHONE
IDX_CUS_MOBILE_PHONE EB_CUSTOMER CUS_MOBILE_PHONE
IDX_CUS_OFFICE_PHONE EB_CUSTOMER CUS_OFFICE_PHONE
IDX_CUS_NAME EB_CUSTOMER CUS_NAME
IDX_CUS_OLD_ID EB_CUSTOMER CUS_OLD_ID
PK_EB_CUSTOMER_PRODUCT_1_1 EB_CUSTOMER_PRODUCT CP_PROD_ID
IDX_CP_PROD_MACHINE_CODE EB_CUSTOMER_PRODUCT CP_PROD_MACHINE_CODE
IDX_CP_CUS_ID EB_CUSTOMER_PRODUCT CP_CUS_ID
INDEX_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
IDX_CP_PROD_CODE EB_CUSTOMER_PRODUCT CP_PROD_CODE
IDX_CP_PROD_BUY_DATE EB_CUSTOMER_PRODUCT CP_PROD_BUY_DATE
IDX_PROD_BUY_IVOICE EB_CUSTOMER_PRODUCT CP_PROD_BUY_IVOICE

千问 · 2005-10-30 17:05:33

把分析删了，再看看执行计划恢复没有

千问 · 2005-10-30 17:05:33

execute dbms_stats.gather_table_stats使用method_opt 获得索引的histogram
再看看

千问 · 2005-10-30 17:05:33

加hint /*+rule*/的执行计划是什么?

千问 · 2005-10-30 17:05:33

看你的执行计划，在分析之前，sql语句的执行计划是RBO,2个表之间的连接方式是nested loop连接，其中一个表使用了index
但是你将表和索引analyze之后，sql语句的执行计划是CBO,2个表之间的连接方式变成了hash join ,2个表都使用了全表扫描
这也许就是问题的所在，不要以为使用了CBO就可以增加表连接查询的速度，要根据连接表数据的分布来选择合适的优化方式，从而来选择合适的执行计划
加上/*+RULE */提示来恢复原来的执行计划

千问 · 2005-10-30 17:05:33

分析只是个cbo提供资料，快不快是不一定的！

千问 · 2005-10-30 17:05:33

你是指返回第一条记录快呢,还是要返回所有的记录?
你是如何测试快与慢的?

千问 · 2005-10-30 17:05:33

to wrui：删除analyze后，执行计划恢复了
to grassbell：获得索引的histogram？
sql> execute dbms_stats.gather_table_stats(ownname=>'CALL',tabname=>'eb_customer',method_opt=>'FOR ALL INDEXES SIZE n',cascade=>TRUE)
但是不知道n该指定多少？
to husthxd ：加/*+rule*/后执行计划恢复原样
to d.c.b.a：分析后，从应用程序上查看客户产品资料，速度极慢，删除分析后就好多了

千问 · 2005-10-30 17:05:33

n 从0-254，一般72就可以啦