为什么再次select还会产生redo？

yxyup · 发表于 2007-2-28 10:06

最初由 eygle 发布
[B]数据库什么版本?

重新做一个测试，给出完整的步骤，看能否再现问题。 [/B]

[php]

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0    Production
TNS for HPUX: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

SQL>

[/php]

eygle大师，测试步骤如下,很简单的

[php]

SQL> select * from v$sesstat where sid =(select distinct sid from v$mystat) and statistic#=(select statistic# from v$statname where name='redo size');

   SID STATISTIC#    VALUE
---------- ---------- ----------
      18       115    1448

SQL> select count(*) from temp_trial_3006;

  COUNT(*)
----------
9651547

SQL> select * from v$sesstat where sid =(select distinct sid from v$mystat) and statistic#=(select statistic# from v$statname where name='redo size');

   SID STATISTIC#    VALUE
---------- ---------- ----------
      18       115    2384

SQL>

[/php]

yxyup · 发表于 2007-2-28 10:08

并且如下
[php]
create table aaa as select * from temp_trial_3006;

SQL> r
  1* select * from v$sesstat where sid =(select distinct sid from v$mystat) and statistic#=(select statistic# from v$statname where name='redo size')

   SID STATISTIC#    VALUE
---------- ---------- ----------
      20       115    7160

SQL> select count(*) from aaa;

  COUNT(*)
----------
9651547

SQL> select * from v$sesstat where sid =(select distinct sid from v$mystat) and statistic#=(select statistic# from v$statname where name='redo size');

   SID STATISTIC#    VALUE
---------- ---------- ----------
      20       115    7160

_________________________________________________
[/php]

eygle · 发表于 2007-2-28 10:20

你后面的两个测试，只有一次select，没能说明问题。

给一个完整的测试。

yxyup · 发表于 2007-2-28 10:26

最初由 eygle 发布
[B]你后面的两个测试，只有一次select，没能说明问题。

给一个完整的测试。 [/B]

谢谢eygle指点

[php]

SQL> select * from v$sesstat where sid =(select distinct sid from v$mystat) and statistic#=(select statistic# from v$statname where name='redo size');

   SID STATISTIC#    VALUE
---------- ---------- ----------
      18       115    2384

SQL>  select count(*) from temp_trial_3006;

  COUNT(*)
----------
9651547

SQL> select * from v$sesstat where sid =(select distinct sid from v$mystat) and statistic#=(select statistic# from v$statname where name='redo size');

   SID STATISTIC#    VALUE
---------- ---------- ----------
      18       115    3320

SQL> select count(*) from temp_trial_3006;

  COUNT(*)
----------
9651547

SQL> select * from v$sesstat where sid =(select distinct sid from v$mystat) and statistic#=(select statistic# from v$statname where name='redo size');

   SID STATISTIC#    VALUE
---------- ---------- ----------
      18       115    4256

SQL>  select count(*) from temp_trial_3006;

  COUNT(*)
----------
9651547

SQL> select * from v$sesstat where sid =(select distinct sid from v$mystat) and statistic#=(select statistic# from v$statname where name='redo size');

   SID STATISTIC#    VALUE
---------- ---------- ----------
      18       115    5192

SQL>
SQL>
SQL> set autotrace trace exp stat
SQL>  select count(*) from temp_trial_3006;

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1    TABLE ACCESS (FULL) OF 'TEMP_TRIAL_3006'

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

SQL>

[/php]

eygle · 发表于 2007-2-28 10:30

create table aaa as select * from temp_trial_3006;

你重新建个表来测试。不要用temp这个表，怀疑你这个表有问题的，是不是有触发器之类?或者数据库建有全局出发器。

你也可以用sql_trace/10046跟踪一下select操作。

yxyup · 发表于 2007-2-28 10:31

最初由 eygle 发布
[B]create table aaa as select * from temp_trial_3006;

你重新建个表来测试。不要用temp这个表，怀疑你这个表有问题的，是不是有触发器之类?或者数据库建有全局出发器。

你也可以用sql_trace/10046跟踪一下select操作。 [/B]

下面是10046跟踪的内容

[php]

oramdb_ora_28058_yxy.trc" 61 lines, 2955 characters
/oracle/admin/oramdb/udump/oramdb_ora_28058_yxy.trc

*** TRACE DUMP CONTINUED FROM FILE  ***
/oracle/admin/oramdb/udump/oramdb_ora_28058_yxy.trc

*** TRACE DUMP CONTINUED FROM FILE  ***

Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production
ORACLE_HOME = /oracle/product/9.2.0
System name: HP-UX
Node name:    restore
Release:       B.11.11
Version:       U
Machine:       9000/800
Instance name: oramdb
Redo thread mounted by this instance: 1
Oracle process number: 18
Unix process pid: 28058, image: oracle@restore (TNS V1-V3)

*** 2007-02-27 17:24:25.318
*** SESSION ID

20.302) 2007-02-27 17:24:25.318
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
=====================
PARSING IN CURSOR #3 len=68 dep=0 uid=30 oct=42 lid=30 tim=1702212540675 hv=1346161232 ad='198bdd08'
alter session set events '10046 trace name context forever,level 12'
END OF STMT
EXEC #3:c=0,e=85,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1702212540398
WAIT #3: nam='SQL*Net message to client' ela= 1 p1=1650815232 p2=1 p3=0
WAIT #3: nam='SQL*Net message from client' ela= 4410783 p1=1650815232 p2=1 p3=0
=====================
PARSING IN CURSOR #3 len=36 dep=0 uid=30 oct=3 lid=30 tim=1702216952107 hv=3078240771 ad='19d68130'
select count(*) from temp_trial_3006
END OF STMT
PARSE #3:c=0,e=413,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1702216952096
BINDS #3:
EXEC #3:c=0,e=128,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1702216952342
WAIT #3: nam='SQL*Net message to client' ela= 2 p1=1650815232 p2=1 p3=0
FETCH #3:c=3260000,e=3182635,p=0,cr=24110,cu=0,mis=0,r=1,dep=0,og=4,tim=1702220135050
WAIT #3: nam='SQL*Net message from client' ela= 478 p1=1650815232 p2=1 p3=0
FETCH #3:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1702220135729
WAIT #3: nam='SQL*Net message to client' ela= 2 p1=1650815232 p2=1 p3=0
WAIT #3: nam='SQL*Net message from client' ela= 8990867 p1=1650815232 p2=1 p3=0
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE '
=====================
PARSING IN CURSOR #4 len=116 dep=1 uid=0 oct=3 lid=0 tim=1702229127534 hv=431456802 ad='18eb6030'
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
END OF STMT
PARSE #4:c=0,e=661,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1702229127523
BINDS #4:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=c000000000000001 size=24 offset=0
bfp=800003fb80069788 bln=22 avl=04 flg=05
value=20078
EXEC #4:c=0,e=3125,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1702229130856
FETCH #4:c=0,e=51,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=1702229130950
STAT #3 id=2 cnt=9651547 pid=1 pos=1 obj=20078 op='TABLE ACCESS FULL TEMP_TRIAL_3006 '
=====================
PARSING IN CURSOR #3 len=56 dep=0 uid=30 oct=42 lid=30 tim=1702229131261 hv=1799830823 ad='198b49c0'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #3:c=0,e=148,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1702229131252
BINDS #3:
EXEC #3:c=0,e=95,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1702229131451

_____________________________________

[/php]

yxyup · 发表于 2007-2-28 10:34

最初由 eygle 发布
[B]create table aaa as select * from temp_trial_3006;

你重新建个表来测试。不要用temp这个表，怀疑你这个表有问题的，是不是有触发器之类?或者数据库建有全局出发器。

你也可以用sql_trace/10046跟踪一下select操作。 [/B]

eygle你好，保证没有触发器的

重建其他表，再做这样的查询是没有产生redo的。

有可能是这temp_trial_3006表的问题，但是是什么问题？还请指点!

eygle · 发表于 2007-2-28 10:37

最初由 yxyup 发布
[B]

eygle你好，保证没有触发器的

重建其他表，再做这样的查询是没有产生redo的。

有可能是这temp_trial_3006表的问题，但是是什么问题？还请指点!

[/B]

是不是有大事务操作，数据库在回滚啊?

v$rollstat看看

piner · 发表于 2007-2-28 10:40

针对你这个问题，肯定是有其它东西在做怪，我举一个例子，但是跟你一样不一样，我不能确定

[php]
10:39:09 SQL> BEGIN
10:39:09 2  dbms_fga.add_policy( object_schema => 'CHEN',
10:39:09 3                      object_name => 'A',
10:39:09 4                      policy_name => 'chk_test',
10:39:09 5                      enable => TRUE );
10:39:09 6  END;
10:39:10 7  /

PL/SQL procedure successfully completed.

10:39:11 SQL> set autot trace
10:39:17 SQL> select count(*) from a;

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=1)
1 0 SORT (AGGREGATE)
2 1    FILTER
3 2    TABLE ACCESS (FULL) OF 'A' (Cost=16 Card=100002)

Statistics
----------------------------------------------------------
      17  recursive calls
      24  db block gets
      158  consistent gets
      1  physical reads
      736  redo size
      408  bytes sent via SQL*Net to client
      504  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

10:39:24 SQL> /

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=1)
1 0 SORT (AGGREGATE)
2 1    FILTER
3 2    TABLE ACCESS (FULL) OF 'A' (Cost=16 Card=100002)

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

10:39:27 SQL> /

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=1)
1 0 SORT (AGGREGATE)
2 1    FILTER
3 2    TABLE ACCESS (FULL) OF 'A' (Cost=16 Card=100002)

Statistics
----------------------------------------------------------
      17  recursive calls
      14  db block gets
      158  consistent gets
      1  physical reads
      712  redo size
      408  bytes sent via SQL*Net to client
      504  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

10:39:32 SQL>
.
[/php]

piner · 发表于 2007-2-28 10:42

可以看到，我增加了一个审计策略之后，每次查询也是有redo的。

[笔记] 为什么再次select还会产生redo？