环境：

sys@ORCL> select * from v$version where rownum=1;

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

sys@ORCL> !uname -r
2.6.18-308.el5xen

conn / as sysdba

grant advisor to sh;

grant create materialized view to sh;

conn sh/sh

drop table t purge；

create table t as select * from dba_objects

--未建立物化视图前的select代价开销
sh@ORCL> select owner,object_type,count(*) from t group by owner,object_type;

236 rows selected.

Execution Plan
----------------------
Plan hash value: 47235625

---------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------
|   0 | SELECT STATEMENT   |      |   483 |  7245 |   166   (6)| 00:00:02 |
|   1 |  HASH GROUP BY          |      |   483 |  7245 |   166   (6)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| T    | 50349 |   737K|   160   (2)| 00:00:02 |
---------------------------------------

Statistics
----------------------
          9  recursive calls
          0  db block gets
        712  consistent gets
          0  physical reads
          0  redo size
       7036  bytes sent via SQL*Net to client
        550  bytes received via SQL*Net from client
         17  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        236  rows processed

--为查询预建立物化视图         
declare
  v_task varchar2(2000);
begin
  v_task :='Task1';
  dbms_advisor.tune_mview(v_task,
     'create materialized view mv_t refresh fast with rowid as select owner,object_type,count(*)
                                                               from t
              &n