« 由compute statistics选项引起的性能问题 »
DBA » http://www.anysql.net/dba/index_compute_statistics.html 2007-05-14在数据库中有一个表, 在其上面有一个索引, 现在的情况是没有分析数据的. 如下所示:
SQL> SELECT TABLE_NAME, NUM_ROWS, LAST_ANALYZED
2 FROM USER_TABLES WHERE TABLE_NAME=’POS_SELL’;TABLE_NAME NUM_ROWS LAST_ANAL
—————————— ———- ———
POS_SELLSQL> SELECT INDEX_NAME, NUM_ROWS, LAST_ANALYZED
2 FROM USER_INDEXES WHERE INDEX_NAME=’POS_SELL_IX1′;INDEX_NAME NUM_ROWS LAST_ANAL
—————————— ———- ———
POS_SELL_IX1
接着需要再创建一个索引, 在创建时, 我加上了计算统计信息的选项, 如下所示:
SQL> CREATE INDEX POS_SELL_IX2 ON POS_SELL (USERCODE)
2 COMPUTE STATISTICS;Index created.
现在我们再来查一下, 表及索引的分析情况, 发现在10g中, 只有新创建的索引有统计数据:
SQL> SELECT TABLE_NAME, NUM_ROWS, LAST_ANALYZED
2 FROM USER_TABLES WHERE TABLE_NAME=’POS_SELL’;TABLE_NAME NUM_ROWS LAST_ANAL
—————————— ———- ———
POS_SELLSQL> SELECT INDEX_NAME, NUM_ROWS, LAST_ANALYZED
2 FROM USER_INDEXES WHERE INDEX_NAME=’POS_SELL_IX1′;INDEX_NAME NUM_ROWS LAST_ANAL
—————————— ———- ———
POS_SELL_IX1SQL> SELECT INDEX_NAME, NUM_ROWS, LAST_ANALYZED
2 FROM USER_INDEXES WHERE INDEX_NAME=’POS_SELL_IX2′;INDEX_NAME NUM_ROWS LAST_ANAL
—————————— ———- ———
POS_SELL_IX2 24398 14-MAY-07
但今天在9i中创建索引时, 发现表被分析了, 但另一个索引则没有分析. 由于表的统计信息变了, 引起了一些SQL选择了错误的执行计划, 导致了服务器的性能问题.
肯能使9i的bug
SQL> show user
USER is “SYS”
SQL> conn ixora10g/ixora10g@ixora10g
Connected.
SQL> desc user_objects;
Name Null? Type
—————————————– ——– —————————-
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
1* select object_name from user_objects
SQL> /
no rows selected
SQL> create table pos_sell as select * from dba_objects;
Table created.
SQL> desc pos_sell;
Name Null? Type
—————————————– ——– —————————-
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> select table_name,num_rows,last_analyzed from user_tables;
TABLE_NAME NUM_ROWS LAST_ANALYZE
—————————— ———- ————
POS_SELL
SQL> create index pos_sell_ix1 on pos_sell(object_id);
Index created.
SQL> select table_name,num_rows,last_analyzed from user_tables;
TABLE_NAME NUM_ROWS LAST_ANALYZE
—————————— ———- ————
POS_SELL
SQL> select index_name,num_rows,last_analyzed from user_indexes;
INDEX_NAME NUM_ROWS LAST_ANALYZE
—————————— ———- ————
POS_SELL_IX1 49745 17-MAY-07
1* create index pos_sell_ix2 on pos_sell(object_name) compute statistics
SQL> /
Index created.
SQL> select index_name,num_rows,last_analyzed from user_indexes;
INDEX_NAME NUM_ROWS LAST_ANALYZE
—————————— ———- ————
POS_SELL_IX2 49745 17-MAY-07
POS_SELL_IX1 49745 17-MAY-07
SQL> select * from v$version;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production
SQL>
我建立的第一个index也被分析了
G:\>ruochen
G:\>sqlplus /nolog @ruochen.sql
SQL*Plus: Release 9.2.0.1.0 – Production on Thu May 17 14:16:09 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected.
SQL> select * from v$version;
BANNER
—————————————————————-
Oracle9i Enterprise Edition Release 9.2.0.1.0 – Production
PL/SQL Release 9.2.0.1.0 – Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 – Production
NLSRTL Version 9.2.0.1.0 – Production
SQL> conn ruochen@ruochen
Enter password:
Connected.
SQL> create table pos_sell as select * from dba_objects;
Table created.
SQL> select table_name,num_rows,last_analyzed from user_tables;
TABLE_NAME NUM_ROWS LAST_ANAL
—————————— ———- ———
BONUS
DEPT
DUMMY
EMP
POS_SELL
SALGRADE
6 rows selected.
SQL> select index_name,num_rows,last_analyzed from user_indexes;
no rows selected
SQL> create index pos_sell_ix1 on pos_sell(object_id);
Index created.
SQL> select index_name,num_rows,last_analyzed from user_indexes;
INDEX_NAME NUM_ROWS LAST_ANAL
—————————— ———- ———
POS_SELL_IX1
SQL>