help! 查询DBA_SEGMENTS突然变得很慢

dblife

一个建议，重新建立数据字典，会不会改善？CATALOG.SQL等
BACKUP IT FIRST
顺便友情UP

cgh_chen

不要糊涂了一个概念。oracle重建数据库字典，只是alter 字典的表结构，个人这样认为。
否则的话，字典信息，它从何而来。

隔岸观火   帮不了忙啊

dblife

嗯，万一影响了执行计划呢

acdsee

确实有趣，学习。。。

richto

The following SQL alternative is generated by DB Expert.  You can try to see which on is fit for your enviroment.

Generated at (Date/Time)



/*****************************************************************/

/*----------------------------------------*/
/* SQL NAME : Source SQL */

/*----------------------------------------*/
select count(*)

  from dba_segments

/*****************************************************************/

/*----------------------------------------*/
/* SQL NAME : Alt1 */

/*----------------------------------------*/
select /*+ ALL_ROWS */ count(*)

  from dba_segments

/*****************************************************************/

/*----------------------------------------*/
/* SQL NAME : Alt2 */

/*----------------------------------------*/
select /*+ FIRST_ROWS */ count(*)

  from dba_segments

/*****************************************************************/

/*----------------------------------------*/
/* SQL NAME : Alt3 */

/*----------------------------------------*/
select /*+ FULL(DBA_SEGMENTS) */ count(*)

  from dba_segments

/*****************************************************************/

/*----------------------------------------*/
/* SQL NAME : Alt4 */

/*----------------------------------------*/
select /*+ ROWID(DBA_SEGMENTS) */ count(*)

  from dba_segments

/*****************************************************************/

/*----------------------------------------*/
/* SQL NAME : Alt5 */

/*----------------------------------------*/
select /*+ INDEX(DBA_SEGMENTS) */ count(*)

  from dba_segments

/*****************************************************************/

/*----------------------------------------*/
/* SQL NAME : Alt6 */

/*----------------------------------------*/
select /*+ INDEX_ASC(DBA_SEGMENTS) */ count(*)

  from dba_segments

/*****************************************************************/

/*----------------------------------------*/
/* SQL NAME : Alt7 */

/*----------------------------------------*/
select /*+ NO_INDEX(DBA_SEGMENTS) */ count(*)

  from dba_segments

/*****************************************************************/

/*----------------------------------------*/
/* SQL NAME : Alt8 */

/*----------------------------------------*/
select /*+ NO_MERGE(DBA_SEGMENTS) */ count(*)

  from dba_segments

/*****************************************************************/

/*----------------------------------------*/
/* SQL NAME : Alt9 */

/*----------------------------------------*/
select /*+ RULE */ count(*)

  from dba_segments

/*****************************************************************/

/*----------------------------------------*/
/* SQL NAME : Alt10 */

/*----------------------------------------*/
select /*+ CLUSTER */ count(*)

  from dba_segments

acdsee

哦，几乎所有的hints都在这了，反正我测试时，就choose和rule最优

richto

Yes, use computer to do this kind of tedious job is much better than human.  SQL Expert try every possible hints and rewrites, only those hints can affects the SQL to generate execution plan.  So, you can save your time to do only 10 SQL testing out of hunderds possibilities.

feng_xin

SQL> set timing on
SQL>  select /*+ ALL_ROWS */ count(*)
  2  from  dba_segments;

COUNT(*)
---------
    19123

real: 79920
SQL> select /*+ FIRST_ROWS */ count(*)
  2  from dba_segments;

COUNT(*)
---------
    19123

real: 84420
SQL> select /*+ FULL(DBA_SEGMENTS) */ count(*)
  2  
SQL> select /*+ FULL(DBA_SEGMENTS) */ count(*)
  2  from dba_segments;

COUNT(*)
---------
    19123

real: 80570
SQL> select /*+ ROWID(DBA_SEGMENTS) */ count(*)
  2  from dba_segments;

COUNT(*)
---------
    19123

real: 75640
SQL> select /*+ INDEX(DBA_SEGMENTS) */ count(*)
  2  from dba_segments;

COUNT(*)
---------
    19123

real: 83440
SQL> select /*+ INDEX_ASC(DBA_SEGMENTS) */ count(*)
  2  
SQL>  select /*+ INDEX_ASC(DBA_SEGMENTS) */ count(*)
  2  from dba_segments;

COUNT(*)
---------
    19123

real: 81070

feng_xin

SQL> select /*+ NO_INDEX(DBA_SEGMENTS) */ count(*)
  2  from dba_segments;

COUNT(*)
---------
    19123

real: 71130

SQL> select /*+ NO_MERGE(DBA_SEGMENTS) */ count(*)
  2   from dba_segments;

COUNT(*)
---------
    19123

real: 81350

SQL> select /*+ RULE */ count(*)
  2   from dba_segments;

COUNT(*)
---------
    19123

real: 82990

SQL> select /*+ CLUSTER */ count(*)
  2   from dba_segments;

COUNT(*)
---------
    19123

real: 75800
SQL>

feng_xin

SQL> select /*+ NO_INDEX(DBA_SEGMENTS) */ count(*)
2 from dba_segments;

COUNT(*)
---------
19123

real: 71130

SQL> select /*+ ROWID(DBA_SEGMENTS) */ count(*)
2 from dba_segments;

COUNT(*)
---------
19123

real: 75640

应该是最优的了，不过速度还是不理想，不知根本问题在哪儿？