物化视图刷新，请yangtingkun等大侠帮忙

top100 · 发表于 2005-3-8 12:58

基于以上的,我大致总结一下,请看看是否正确:
我查看了一下帮助
CONSIDER FRESH
This clause lets you manage the staleness state of a materialized after changes have been made to its master tables. CONSIDER FRESH directs Oracle to consider the materialized view fresh and therefore eligible for query rewrite in the TRUSTED or STALE_TOLERATED modes. Because Oracle cannot guarantee the freshness of the materialized view, query rewrite in ENFORCED mode is not supported. This clause also sets the staleness state of the materialized view to UNKNOWN. The staleness state is displayed in the STALENESS column of the ALL_MVIEWS, DBA_MVIEWS, and USER_MVIEWS data dictionary views.

--------------------------------------------------------------------------------
Note:
A materialized view is stale if changes have been made to the contents of any of its master tables. This clause directs Oracle to assume that the materialized view is fresh and that no such changes have been made. Therefore, actual updates to those tables pending refresh are purged with respect to the materialized view.

我下面要做的:
1.将QUERY_REWRITE_INTEGERITY的值设置为STALE_TOLERATED,可以解决我的问题本身,但是如果数据库中其他的物化视图不是采用这种策略,则是会有影响的(是否是其他的不是这种策略的视图,也会被认为是更新及时,而导致基表/视图结果其实是不一致的);
2.只是这张表采用这种策略，可以考虑

alter materialized view js_mv_tlw_sett_f3_200503 consider fresh;
但是要在session里指定;

也就是说,如果想查询js_mv_tlw_sett_f3_200503 这种策略的视图的结果(实际是针对基表查询,使用查询重写)需要session级指定consider fresh;请问此时的QUERY_REWRITE_INTEGRITY 设置为什么是否已经没有关系?当前的我们是 enforce;

yangtingkun · 发表于 2005-3-8 13:21

alter system是系统级，修改以后系统中所有物化视图不管是否和基表一致都可以被用来进行查询重写。

alter session是session级，修改以后当前session利用物化视图进行查询重写时，被认为可以允许和基表不同步。

consider fresh是对象级，这个对象如果指定了consider fresh，则Oracle在重写物化视图时认为是同步的。不过这时候QUERY_REWRITE_INTEGRITY 不能设置为ENFORCE。

top100 · 发表于 2005-3-8 13:22

顺便问一句,怎么变成授权会员阿
以前有50贴的限制,现在还有吗?

yangtingkun · 发表于 2005-3-8 13:22

基于你的情况，我建议你在系统级设置QUERY_REWRITE_INTEGRITY 为trusted。
并将js_mv_tlw_sett_f3_200503 设置为consider refresh。

yangtingkun · 发表于 2005-3-8 13:24

哦，对了，如果你的物化视图执行了完全刷新，那么刷新后必须重新设置CONSIDER REFRESH

yangtingkun · 发表于 2005-3-8 13:26

最初由 top100 发布
[B]顺便问一句,怎么变成授权会员阿
以前有50贴的限制,现在还有吗? [/B]

哦，好像现在是系统自动判断。

你可以去授权用户区看看。
如果还进不去，可能还得等一两天。

top100 · 发表于 2005-3-8 13:58

好的,谢谢
下面的就是测试了

top100 · 发表于 2005-3-8 19:02

测试结果1：
目前的问题基本得到解决
zhjs01$[/jsdata02/tempdata/daijf]sqlplus user/passwd@db

SQL*Plus: Release 9.2.0.4.0 - Production on 星期二 3月 8 16:55:54 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL>
SQL>
SQL> conn / as sysdba
已连接。
SQL> alter system set QUERY_REWRITE_INTEGRITY=trusted scope=both;

系统已更改。

SQL> conn user/passwd@db
已连接。
SQL> set time on
17:02:07 SQL> set timing on
17:02:10 SQL> set autotrace traceonly explain;
17:02:26 SQL> select sum(num_cdr) from tlw_sett_f3_200503;
已用时间:  00: 00: 00.00

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=18828 Card=1 Bytes=3
      )

1 0 SORT (AGGREGATE)
2 1    TABLE ACCESS (FULL) OF 'TLW_SETT_F3_200503' (Cost=18828
      Card=24283160 Bytes=72849480)

17:02:29 SQL> ALTER MATERIALIZED VIEW js_mv_tlw_sett_f3_200503 CONSIDER FRESH;

实体化视图已更改。

已用时间:  00: 07: 32.61
17:11:49 SQL> select sum(num_cdr) from tlw_sett_f3_200503;
已用时间:  00: 00: 00.01

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1    SORT* (AGGREGATE)                                     :Q910000
                                                                     0

3 2    TABLE ACCESS* (FULL) OF 'JS_MV_TLW_SETT_F3_200503' (Co :Q910000
      st=34 Card=169444 Bytes=508332)                            0

2 PARALLEL_TO_SERIAL          SELECT /*+ PIV_SSF */ SYS_OP_MSR(SUM(A1.C0))
                                 FROM (SELECT /*+ NO_EXPAND ROWID(A2

3 PARALLEL_COMBINED_WITH_PARENT

需要测试结果2:对其他类型的视图的影响？
待续

top100 · 发表于 2005-3-14 13:43

问题再次出现如下：
zhjs01$[/jsdata02/tempdata/os]sqlplus /noloog

SQL*Plus: Release 9.2.0.4.0 - Production on 星期一 3月 14 12:30:27 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SP2-0306: 无效选项。
用法: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
其中 <logon>  : : = <username>[/<password>][@<connect_string>] | /
请输入用户名:
ERROR:
ORA-01017: 无效的用户名/口令；拒绝登录

请输入用户名:
ERROR:
ORA-01017: 无效的用户名/口令；拒绝登录

SP2-0157: 在3次尝试之后无法 CONNECT 到 ORACLE, 退出 SQL*Plus
zhjs01$[/jsdata02/tempdata/os]sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on 星期一 3月 14 12:30:33 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn user/passwd@db
已连接。
SQL> set time on
12:30:47 SQL> set timing on
12:30:51 SQL> set autotrace traceonly explain
12:31:09 SQL> select /*+rewrite*/sum(num_cdr) from tlw_sett_f3_200503;
已用时间:  00: 00: 00.01

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=18828 Card=1 Bytes=3
      )

1 0 SORT (AGGREGATE)
2 1    TABLE ACCESS (FULL) OF 'TLW_SETT_F3_200503' (Cost=18828
      Card=24283160 Bytes=72849480)

12:31:51 SQL> select sum(num_cdr) from tlw_sett_f3_200503;
已用时间:  00: 00: 00.00

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=18828 Card=1 Bytes=3
      )

1 0 SORT (AGGREGATE)
2 1    TABLE ACCESS (FULL) OF 'TLW_SETT_F3_200503' (Cost=18828
      Card=24283160 Bytes=72849480)

12:32:12 SQL> show parameter query
12:32:26 SQL> show parameter QUERY
12:33:21 SQL> create pfile='/jsdata02/tempdata/os/dbbak/initdb_0314.ora' from spfile;
create pfile='/jsdata02/tempdata/os/dbbak/initdb_0314.ora' from spfile
*
ERROR 位于第 1 行:
ORA-01031: 权限不足

已用时间:  00: 00: 00.01
12:34:55 SQL> conn / as sysdba
已连接。
12:35:01 SQL> show parameter query

NAME                               TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
query_rewrite_enabled             string
TRUE
query_rewrite_integrity             string
TRUSTED
12:35:20 SQL> create pfile='/jsdata02/tempdata/os/dbbak/initdb_0314.ora' from spfile;

文件已创建。

已用时间:  00: 00: 00.02
12:35:49 SQL> grant dba to user;

授权成功。

已用时间:  00: 00: 00.08
12:36:53 SQL> conn user/passwd
已连接。
12:37:05 SQL> show parameter query

NAME                               TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
query_rewrite_enabled             string
TRUE
query_rewrite_integrity             string
TRUSTED
12:37:11 SQL>  select sum(num_cdr) from tlw_sett_f3_200503;
select sum(num_cdr) from tlw_sett_f3_200503
                        *
ERROR 位于第 1 行:
ORA-01013: 用户请求取消当前的操作

已用时间:  00: 00: 04.27

12:37:33 SQL> set autotrace traceonly explain;
12:37:50 SQL>  select sum(num_cdr) from tlw_sett_f3_200503;
已用时间:  00: 00: 00.00

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=18828 Card=1 Bytes=3
      )

1 0 SORT (AGGREGATE)
2 1    TABLE ACCESS (FULL) OF 'TLW_SETT_F3_200503' (Cost=18828
      Card=24283160 Bytes=72849480)

12:37:58 SQL> select /*+rewrite*/sum(num_cdr) from tlw_sett_f3_200503;
已用时间:  00: 00: 00.01

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=18828 Card=1 Bytes=3
      )

1 0 SORT (AGGREGATE)
2 1    TABLE ACCESS (FULL) OF 'TLW_SETT_F3_200503' (Cost=18828
      Card=24283160 Bytes=72849480)

12:38:21 SQL> revoke dba from user;

撤销成功。

已用时间:  00: 00: 00.04
12:38:50 SQL> show user
USER 为"user"
12:38:56 SQL> show parameter query
12:39:06 SQL> conn / as sysdba
已连接。
12:39:19 SQL> grant select any table to user;

授权成功。

已用时间:  00: 00: 00.05
12:39:30 SQL> show user
USER 为"SYS"
12:39:37 SQL> conn user/passwd
已连接。
12:39:44 SQL> show parameter query

NAME                               TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
query_rewrite_enabled             string
TRUE
query_rewrite_integrity             string
TRUSTED
12:39:49 SQL> select /*+rewrite*/sum(num_cdr) from tlw_sett_f3_200503;
select /*+rewrite*/sum(num_cdr) from tlw_sett_f3_200503
                                 *
ERROR 位于第 1 行:
ORA-01013: 用户请求取消当前的操作

已用时间:  00: 00: 04.51

12:40:18 SQL> set autotrace traceonly explain;
12:40:34 SQL> select /*+rewrite*/sum(num_cdr) from tlw_sett_f3_200503;
已用时间:  00: 00: 00.00

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=18828 Card=1 Bytes=3
      )

1 0 SORT (AGGREGATE)
2 1    TABLE ACCESS (FULL) OF 'TLW_SETT_F3_200503' (Cost=18828
      Card=24283160 Bytes=72849480)

top100 · 发表于 2005-3-14 13:44

以下附最新的参数列表initdb_0314.ora：
*.background_dump_dest='/oraclelog/admin/db/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/dev/vgjs01/rjsctrl1','/dev/vgjs02/rjsctrl2','/dev/vgjs03/rjsctrl3'
*.core_dump_dest='/oraclelog/admin/db/cdump'
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_cache_size=6442450944
*.db_domain='linkage'
*.db_file_multiblock_read_count=64
*.db_files=2048
*.db_name='db'
*.event='4031 trace name errorstack level 3'
*.fast_start_mttr_target=300
*.fast_start_parallel_rollback='HIGH'
*.hash_join_enabled=TRUE
*.instance_name='db'
*.java_pool_size=0
*.job_queue_processes=6
*.large_pool_size=134217728
*.log_archive_dest='/jsdata02/archlog'
*.log_archive_format='db%s.arc'
*.log_archive_start=true
*.log_buffer=2097152
*.open_cursors=300
*.optimizer_mode='CHOOSE'
*.parallel_automatic_tuning=true
*.parallel_threads_per_cpu=4
*.pga_aggregate_target=209715200
*.processes=150
*.query_rewrite_enabled='TRUE'
*.query_rewrite_integrity='TRUSTED'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_reserved_size=69206016
*.shared_pool_size=268435456
*.sort_area_size=16777216
*.star_transformation_enabled='FALSE'
*.statistics_level='BASIC'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=12000
*.undo_tablespace='UNDOTBS01'
*.user_dump_dest='/oraclelog/admin/db/udump'
*.utl_file_dir='/oraclelog/batch'
*.workarea_size_policy='AUTO'