1
刚才看了下DATA GUARD的描述,不太明白究竟什么时候用PHYSICAL STANDBY什么时候用LOGICAL STANDBY呢?
PHYSICAL STANDBY是通过APPLY LOG来保持数据同步,同时它的物理结构和PRIMARY DATABASE是一样的。
而LOGICAL STANDBY是通过日志重新生成SQL语句来保持数据同步,可是只是逻辑上的一致,物理结果是不一样的。还有一点不明白,说PHYSICAL STANDBY一打开的话ROWID就会改变(OTHER THAN FOR READ ONLY ACCESS),这是为什么呢?
那它总不能就一直工作在只读状态吧?如果PRIMARY DATABASE坏了,而且需要对数据进行操作的话,那必须得打开PHYSICAL STANDBY了吧?2
我现在想做一个实验,需要知道我每执行一条语句会产生多少REDO和UNDO,该如何查询呢?
3
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 29 10:19:20 2009Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> conn scott/tiger
Connected.
SQL> set autot on
SQL> select * from dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1126 recursive calls
7 db block gets
213 consistent gets
20 physical reads
1100 redo size
647 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
22 sorts (memory)
0 sorts (disk)
4 rows processedSQL> select * from dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
6 recursive calls
4 db block gets
10 consistent gets
0 physical reads
784 redo size
647 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processedSQL>
这里为什么会有REDO呢?同一个查询执行了两次都有。谢谢各位啦~~
刚才看了下DATA GUARD的描述,不太明白究竟什么时候用PHYSICAL STANDBY什么时候用LOGICAL STANDBY呢?
PHYSICAL STANDBY是通过APPLY LOG来保持数据同步,同时它的物理结构和PRIMARY DATABASE是一样的。
而LOGICAL STANDBY是通过日志重新生成SQL语句来保持数据同步,可是只是逻辑上的一致,物理结果是不一样的。还有一点不明白,说PHYSICAL STANDBY一打开的话ROWID就会改变(OTHER THAN FOR READ ONLY ACCESS),这是为什么呢?
那它总不能就一直工作在只读状态吧?如果PRIMARY DATABASE坏了,而且需要对数据进行操作的话,那必须得打开PHYSICAL STANDBY了吧?2
我现在想做一个实验,需要知道我每执行一条语句会产生多少REDO和UNDO,该如何查询呢?
3
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 29 10:19:20 2009Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> conn scott/tiger
Connected.
SQL> set autot on
SQL> select * from dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1126 recursive calls
7 db block gets
213 consistent gets
20 physical reads
1100 redo size
647 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
22 sorts (memory)
0 sorts (disk)
4 rows processedSQL> select * from dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
6 recursive calls
4 db block gets
10 consistent gets
0 physical reads
784 redo size
647 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processedSQL>
这里为什么会有REDO呢?同一个查询执行了两次都有。谢谢各位啦~~
解决方案 »
- 安装了oracle,管理工具-服务 里面却没有OracleServiceDB服务,我的数据库名为DB,
- 【求助】SQL问题,急~~~~~~~~~~~~
- oracle包与包体的区别,以及如何查看包内的存储过程实现
- oracle hibernate主键自增策略的问题!
- 更改spfileSID.ora后,不能启动数据库了
- 请问一个sql的显示问题,select语句中如何显示三位一撇的数字
- 局域网oracle的连接问题,感谢指教!!
- 高分求教:CLOB 和 long 类型选择问题
- 请问substr('专家门诊',1,2) 等于 ?
- 初学WebLogic,出现一个问题,求大神解答
- 两道SQL面试题
- 使用sqlplus连接数据库出现问题
先赞一个, wh兄弟,现在的问题越来越深了哟,思考的方面也越来越广了,看得出了进步很明显了哟。oracle的学习就是如此,有一个学的多,问的也越多的阶段,这个阶段不要逃避问题。 这些问题正是你前面学习的结果,学习的东西多了,思考的模式和方面也就多了,所以这个时候如果回避自己的问题,那么你将在这个阶段止步不前。 wh,继续加油,突破一个一个的问题,在csdn里逐渐成长起来。看到一个真实的成长过程,很为wh开心哟。
----
我爱SQL> select substr('我爱CSDN',1,2) from dual;SUB
---
我
我觉得select substr('我爱CSDN',1,2) from dual;这句话的输出结果就应该已经是'我爱'两个字了呀
这是为什么呢?
个人觉得概念性的东西看英文的要好一些 理解的会比较准确
至于像SQL PL/SQL之类应用性比较强的内容看中文就可以了
我刚给你发短消息了 有我QQ号
oracle里面的substr是从1开始的,这个表示,从起始位1开始,截取2个字符串,结果应该是‘我爱’,我的plsql里面执行是:SQL> select substr('我爱CSDN',1,2)from dual;
SUBSTR('我爱CSDN',1,2)
----------------------
我爱
SQL>
SUBSTR('我爱CSDN',1,3)
----------------------
我爱C
SQL>
难道是我的SQL*PLUS里什么参数设置的不对??
刚才看了下DATA GUARD的描述,不太明白究竟什么时候用PHYSICAL STANDBY什么时候用LOGICAL STANDBY呢?
PHYSICAL STANDBY是通过APPLY LOG来保持数据同步,同时它的物理结构和PRIMARY DATABASE是一样的。
而LOGICAL STANDBY是通过日志重新生成SQL语句来保持数据同步,可是只是逻辑上的一致,物理结果是不一样的。 还有一点不明白,说PHYSICAL STANDBY一打开的话ROWID就会改变(OTHER THAN FOR READ ONLY ACCESS),这是为什么呢?
那它总不能就一直工作在只读状态吧?如果PRIMARY DATABASE坏了,而且需要对数据进行操作的话,那必须得打开PHYSICAL STANDBY了吧?
所谓 standby ,就是当 primary 挂掉了,standby再来接管的意思.
在10g里,dataguard 无论逻辑的还是物理的,都是不支持和主库并行读写的了.(11g里支持了)
个人感觉逻辑standby配置相对麻烦一些,而且效果不见得比physical standby好,
逻辑的standby原理上和 stream 复制很类似了.那它总不能就一直工作在只读状态吧?如果PRIMARY DATABASE坏了,而且需要对数据进行操作的话,那必须得打开PHYSICAL STANDBY了吧?
是的,这个时候就要做一个切换的动作,把主库切到standby来.这个时候原来的standby就变成主库,
当然也可以写了.
就这个问题,刚才我咨询了一下老工程师,
他们告诉我,逻辑standyby平时也是处于打开状态的,所以平时逻辑备库可以读写,除可以用它来做灾备外和可以分担一定的应用负荷; 逻辑备库可以读写,除可以用它来做灾备外和可以分担一定的应用负荷;那就是说,在10g里,逻辑的standby 可以在备库写操作,但一般只针对那些在主库不存在的用户.
查看redo 和执行计划,都可以在session级别开启参数:
set autotrace traceonly;
这样就可以看执行产生的redo情况了
至于undo,LZ可以学习这个视图:v$rollstat,看这两个列:
USN NUMBER Rollback segment number
WRITES NUMBER Number of bytes written to the rollback segment 具体的操作就自己动手吧,第三个问题:
select 为什么会产生redo呢?
LZ可以关注下延迟块清除的问题.
简单的说啊
如果一次修改的块,没有超过了缓冲区缓存大小的10%,并且这些块在内存中,则commit时,会清除块上的事务信息,否则,就不会理会它,直到下次访问这些块时,再清除块中的事务信息,这就是延迟块清除.
因为这个Select修改了块的事务信息,所以就会产生Redo.
文档上说一般PHYSICAL STANDY不能打开是因为一旦OPEN了,那么它的ROWID就会改变。
这是什么意思?我不太理解,是不是就是说一旦OPEN了,然后其他用户来进行了增删改操作,这就导致了PHYSICAL STANDY 和PRIMARY DATABASE的ROWID不一致。(这是我个人猜测 因为文档上说可以以READ ONLY方式打开,那么也就是说只要不进行增删改就可以仍然保持和PRIMARY DATABASE物理结构上一致)2
我知道相关延迟块的概念,所以我刚才在一个事务中进行了多次很多次SELECT,可总不能每一次都产生DELAY CLEANOUT吧?你看我刚才那段代码,两次SELECT都有REDO产生。 难道是一次SELECT清除不完?应该不会这样吧,因为我实际上进行了5次同样的SELECT,都产生差不多数量REDO了。难道我对延迟块清除理解错了?
我的理解就是下一个TRANSACTION去清除上一个TRANSACTION没有清除的数据块头事务信息,可我这多个SELECT是在同一个事务中呀,该不会是每天语句都会去清除前一条语句在数据块头中遗留的事务信息吧?
block-for-block using the physical rowid. The database schema, including indexes,
must be the same, and the database cannot be opened (other than for read-only
access). If opened, the physical standby database will have different rowids, making
continued recovery impossible.
http://blog.csdn.net/wh62592855/archive/2009/09/29/4615689.aspx
2 where a.taddr=b.addr;USERNAME USED_UBLK USED_UREC
------------------------------ ---------- ----------
SCOTT 1 6SQL>我用的这个视图v$transaction
里面有如下两个字段
USED_UBLK NUMBER Number of undo blocks used
USED_UREC NUMBER Number of undo records used 应该也可以吧?
If opened, the physical standby database will have different rowids, making
continued recovery impossible
.
我理解是,你一旦打开了physical standby数据库,往里面插入数据之后,
物理standby 就无法和主库同步了.
为什么?因为物理的dataguard,主库和standby库之间的物理块都是一模一样的.
相当于硬同步.
(这个和逻辑的dataguard不一样,不要求两边的块rowid都一模一样.)rowid的概念是什么呢?
和rownum相似,oracle还提供了另外一个伪数列:rowid。不过rowid和rownum不同,一般说来每一行数据对应的rowid是固定而且唯一的,在这一行数据存入数据库的时候就确定了。可以利用rowid来查询记录,而且通过rowid查询记录是查询速度最快的查询方法
http://www.cnblogs.com/rootq/archive/2008/10/24/1319058.html
你PHYSICAL STANDY如果自己在这边插入数据的话
就和人家PRIMARY DATABASE不同步了那接下去那个问题呢?
http://www.hellodba.com/Doc/Oracle_transaction_on_block(2).htm你两次同样select 都可以产生redo,是不是因为数据量比较多的原因啊,
这个保留下,我查询下是什么情况吧
前面的也贴出来.我看看
USER is ""
SQL> conn scott/tiger
Connected.
SQL> show user
USER is "SCOTT"
SQL> set autot on
SQL> select * from dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1126 recursive calls
5 db block gets
213 consistent gets
20 physical reads
1000 redo size
647 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
22 sorts (memory)
0 sorts (disk)
4 rows processedSQL> select * from dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
6 recursive calls
4 db block gets
10 consistent gets
0 physical reads
792 redo size
647 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processedSQL> select * from dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
6 recursive calls
4 db block gets
10 consistent gets
0 physical reads
784 redo size
647 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processedSQL> select * from dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
6 recursive calls
4 db block gets
10 consistent gets
0 physical reads
740 redo size
647 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processedSQL> select * from dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
6 recursive calls
4 db block gets
10 consistent gets
0 physical reads
740 redo size
647 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processedSQL> select * from dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
6 recursive calls
4 db block gets
10 consistent gets
0 physical reads
784 redo size
647 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processedSQL>
我想重复下你的试验
剑飞霜提到的延迟块清除是一个方面还有一个是你疏忽掉的就是
6 recursive calls你的实验排除了延迟块清除的影响,但是却疏忽了recursive calls的影响,不知道你这里dept的表上做了其他的什么实验没有,不过这里却出现了recursive calls,是不是在dept建立了trigger或者是其他的什么处理,你可以做一个sql trace,看看select * from dept里是做了什么其他的处理。
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed这是我机器上的statstics
奇怪的是我以SYS和SCOTT分别对DEPT表进行查询的时候结果却是不一样的
下面我把实验内容贴一下
Connected.
SQL> alter session set sql_trace=true;
alter session set sql_trace=true
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn / as sysdba
Connected.
SQL> grant alter session to scott
2 ;Grant succeeded.SQL> conn scott/tiger
Connected.
SQL> alter session set sql_trace=true;Session altered.SQL> select * from dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTONSQL> alter session set sql_trace=false;Session altered.接下来使用tkprof
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.C:\Documents and Settings\Admin>d:D:\>cd oracleD:\oracle>cd db_1D:\oracle\db_1>cd binD:\oracle\db_1\BIN>tkprof d:\oracle\admin\ORCL\udump\orcl_ora_5544.trc output.tx
tTKPROF: Release 10.2.0.1.0 - Production on Thu Oct 8 11:54:48 2009Copyright (c) 1982, 2005, Oracle. All rights reserved.D:\oracle\db_1\BIN>output.txt内容如下 很多很多Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.C:\Documents and Settings\Admin>d:D:\>cd oracleD:\oracle>cd db_1D:\oracle\db_1>cd binD:\oracle\db_1\BIN>tkprof d:\oracle\admin\ORCL\udump\orcl_ora_5544.trc output.tx
tTKPROF: Release 10.2.0.1.0 - Production on Thu Oct 8 11:54:48 2009Copyright (c) 1982, 2005, Oracle. All rights reserved.D:\oracle\db_1\BIN>
我重新做一个TRC文件
现在把output2.txt内容重新贴一下SQL> alter session set sql_trace=true;Session altered.SQL> select * from dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTONSQL> alter session set sql_trace=false;Session altered.SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining optionsD:\oracle\db_1\BIN>tkprof d:\oracle\admin\ORCL\udump\orcl_ora_1792.trc output2.t
xtTKPROF: Release 10.2.0.1.0 - Production on Thu Oct 8 12:15:32 2009Copyright (c) 1982, 2005, Oracle. All rights reserved.D:\oracle\db_1\BIN>output2.txt内容如下TKPROF: Release 10.2.0.1.0 - Production on Thu Oct 8 12:15:32 2009Copyright (c) 1982, 2005, Oracle. All rights reserved.Trace file: d:\oracle\admin\ORCL\udump\orcl_ora_1792.trc
Sort options: default********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************alter session set sql_trace=true
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
********************************************************************************select *
from
dept
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 1 0
Fetch 2 0.00 0.00 0 8 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.00 0 8 1 4Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 Rows Row Source Operation
------- ---------------------------------------------------
4 TABLE ACCESS FULL DEPT (cr=8 pr=0 pw=0 time=93 us)********************************************************************************update sys.aud$ set ses$actions=merge$actions(ses$actions,:3), spare2=
nvl(spare2,:4)
where
sessionid=:1 and ses$tid=:2 and action#=103 and (priv$used=:5 or priv$used
is null and :5 is null)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.04 1 2 5 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.04 1 2 5 1Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************alter session set sql_trace=false
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 ********************************************************************************OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTScall count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 1 0
Fetch 2 0.00 0.00 0 8 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.01 0.01 0 8 1 4Misses in library cache during parse: 2
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.04 1 2 5 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.04 1 2 5 1Misses in library cache during parse: 0 3 user SQL statements in session.
1 internal SQL statements in session.
4 SQL statements in session.
********************************************************************************
Trace file: d:\oracle\admin\ORCL\udump\orcl_ora_1792.trc
Trace file compatibility: 10.01.00
Sort options: default 1 session in tracefile.
3 user SQL statements in trace file.
1 internal SQL statements in trace file.
4 SQL statements in trace file.
4 unique SQL statements in trace file.
50 lines in trace file.
11 elapsed seconds in trace file.
[code=SQL]SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> set autot on
SQL> select * from scott.dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
647 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
下面以SCOTT用户登录就会产生很多REDO
SQL> conn scott/tiger
Connected.
SQL> set autot on
SQL> select * from dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
6 recursive calls
4 db block gets
10 consistent gets
0 physical reads
792 redo size
647 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed另外,我查了一下好像DEPT表上没触发器SQL> select trigger_name,table_name,table_owner from all_triggers
2 where table_name='DEPT';no rows selectedSQL>
还有csucxcc和inthirties高手
Connected.
SQL> show parameter auditNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string D:\ORACLE\ADMIN\ORCL\ADUMP
audit_sys_operations boolean FALSE
audit_trail string DB
SQL> select distinct username from dba_audit_trail;USERNAME
------------------------------
SCOTT
SYSTEM
V485ASQL>果然如此 利害呀!! 你就是我的目标 呵呵
连我自己都忘了什么时候做了AUDIT的实验了最近比较忙 明天去参加一个公司的宣讲会 说不定会有笔试 自己给自己加个油 UP~
我对你充满好奇哦 呵呵 我好奇心可重了 对什么都好奇你好像也不用上班不用干什么 每天都有大段的时间在网上发帖回帖还有 你举办的那个网上学习计划不错 等有时间我去注册一个号报个名
破学校马上就熄灯了 我得抓紧时间找点笔试题看看
Connected.
SQL> set autotrace traceonly;
SQL> select * from hr.employees;107 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1342275408-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7276 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7276 | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
592 recursive calls
5 db block gets
198 consistent gets
1 physical reads
1064 redo size
10306 bytes sent via SQL*Net to client
569 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
107 rows processedSQL> l
1* select * from hr.employees
SQL> /107 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1342275408-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7276 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7276 | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
20 recursive calls
6 db block gets
16 consistent gets
0 physical reads
1120 redo size
10306 bytes sent via SQL*Net to client
569 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processedSQL> /107 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1342275408-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7276 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7276 | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
20 recursive calls
5 db block gets
15 consistent gets
0 physical reads
1072 redo size
10306 bytes sent via SQL*Net to client
569 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processedSQL>