analyze后,查询速度没改变

Yong Huang

原帖由 棉花糖ONE 于 2008-7-20 08:55 发表

这个bug在10g依然存在

I tested it in 10.2.0.2 and 10.2.0.4. The problem does not occur. Can you show your sqlplus screen shot and Oracle version? If you know the bug number, also tell us.

Yong Huang

zergduan

http://yangtingkun.itpub.net/post/468/410427

他说10.2.0.3中也有

而且我在9.2.0.8中也碰到过，关闭数据库，别退出sqlplus,启动后直接用sys做trace，会出现。如果再conn 普通用户还是会出现

[ 本帖最后由 zergduan 于 2008-7-21 06:19 编辑 ]

lemonair

this issue is fixed.

it is a bug in oracle.

wher you query like these
select /*+ frist_row(1) */  * from table_name where rownum < 2;
will hitting this bug.

it is fixed in 9208

棉花糖ONE

原帖由 Yong Huang 于 2008-7-21 02:23 发表


I tested it in 10.2.0.2 and 10.2.0.4. The problem does not occur. Can you show your sqlplus screen shot and Oracle version? If you know the bug number, also tell us.

Yong Huang

我在10.2.0.1下遇到的次数不下于10次，尤其是一个机器上同时装了2个版本的oracle,遇到的次数太频繁了

晶晶小妹

偶，我以前在9i下也碰到过一次，当时没在意。原来是个BUG。

Yong Huang

原帖由 棉花糖ONE 于 2008-7-21 04:10 发表
我在10.2.0.1下遇到的次数不下于10次，尤其是一个机器上同时装了2个版本的oracle,遇到的次数太频繁了:(

Are we talking about the same problem? Here's a test in 10.2.0.1:

SQL> select * from v$version where rownum=1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

SQL> set autot on
SQL> select count(*) from ind$;

  COUNT(*)
----------
      7844


Execution Plan
----------------------------------------------------------
Plan hash value: 1307493412

------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |        |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| I_IND1 |  7844 |     7   (0)| 00:00:01 |
------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         32  consistent gets
         22  physical reads
          0  redo size
        412  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> create table t (x int);

Table created.

SQL> select count(*) from t;

  COUNT(*)
----------
         0


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |     1 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        410  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> show user
USER is "SYS"
SQL>

You see, the statistics are not shown as all 0. I logged in as sys.

If you anybody finds the bug on Metalink, please state the bug number.

Yong Huang

anlinew

rebuild table吧