[20120417]select生产redo.txt

select生成redo主要有几个原因,常见的主要是修改表记录太多,在commit后,由于记录已经不在数据缓存,在下次select时,再修改相关信息,称为快速提交.

做一个测试:

1.快速提交产生的: 

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> show sga
Total System Global Area 1068937216 bytes
Fixed Size                  2220200 bytes
Variable Size             834670424 bytes
Database Buffers          226492416 bytes
Redo Buffers                5554176 bytes


SQL> select .25* 226492416 /8192 from dual ;


.25*226492416/8192


------------------


              6912




SQL> create table t2 as select rownum id ,'a' name from dual connect by level


Table created.


SQL> ALTER TABLE t2 MINIMIZE RECORDS_PER_BLOCK;


Table altered.


--这样每个数据块的记录仅仅2条.


SQL> insert into t2  select rownum id ,'a' name from dual connect by level


14000 rows created.


SQL> commit ;


Commit complete.


SQL> set autot traceonly ;


SQL> select count(*) from t2 ;


Execution Plan


----------------------------------------------------------


Plan hash value: 3321871023


-------------------------------------------------------------------


| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |


-------------------------------------------------------------------


|   0 | SELECT STATEMENT   |      |     1 |  1913   (1)| 00:00:23 |


|   1 |  SORT AGGREGATE    |      |     1 |            |          |


|   2 |   TABLE ACCESS FULL| T2   | 14774 |  1913   (1)| 00:00:23 |


-------------------------------------------------------------------


Note


-----


   - dynamic sampling used for this statement (level=2)




Statistics


----------------------------------------------------------


         29  recursive calls


          1  db block gets


      11564  consistent gets


          0  physical reads


     311216  redo size


        528  bytes sent via SQL*Net to client


        520  bytes received via SQL*Net from client


          2  SQL*Net roundtrips to/from client


          0  sorts (memory)


          0  sorts (disk)


          1  rows processed


--可以发现产生大量redo.第2次执行,redo变为0


SQL> select count(*) from t2 ;


Execution Plan


----------------------------------------------------------


Plan hash value: 3321871023


-------------------------------------------------------------------


| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |


-------------------------------------------------------------------


|   0 | SELECT STATEMENT   |      |     1 |  1913   (1)| 00:00:23 |


|   1 |  SORT AGGREGATE    |      |     1 |            |          |


|   2 |   TABLE ACCESS FULL| T2   | 14774 |  1913   (1)| 00:00:23 |


-------------------------------------------------------------------


Note


-----


   - dynamic sampling used for this statement (level=2)


Statistics


----------------------------------------------------------


          0  recursive calls


          0  db block gets


       7058  consistent gets


          0  physical reads


          0  redo size


        528  bytes sent via SQL*Net to client


        520  bytes received via SQL*Net from client


          2  SQL*Net roundtrips to/from client


          0  sorts (memory)


          0  sorts (disk)


          1  rows processed


2.select的中的字段带有sequence号,顺序号使用完要更新sys.seq$,会产生redo信心.


SQL> create sequence test_seq nocache;


SQL> set autot traceonly ;


SQL> select t2.* from t2 where rownum


10 rows selected.


Execution Plan


----------------------------------------------------------


Plan hash value: 1154646200


---------------------------------------------------------------------------


| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |


---------------------------------------------------------------------------


|   0 | SELECT STATEMENT   |      |    10 |   160 |     3   (0)| 00:00:01 |


|*  1 |  COUNT STOPKEY     |      |       |       |            |          |


|   2 |   TABLE ACCESS FULL| T2   | 14774 |   230K|     3   (0)| 00:00:01 |


---------------------------------------------------------------------------


Predicate Information (identified by operation id):


---------------------------------------------------


   1 - filter(ROWNUM


Note


-----


   - dynamic sampling used for this statement (level=2)




Statistics


----------------------------------------------------------


          0  recursive calls


          0  db block gets


          9  consistent gets


          0  physical reads


          0  redo size


        732  bytes sent via SQL*Net to client


        520  bytes received via SQL*Net from client


          2  SQL*Net roundtrips to/from client


          0  sorts (memory)


          0  sorts (disk)


         10  rows processed


--没有redo生成!


SQL> select test_seq.nextval,t2.* from t2 where rownum


10 rows selected.


Execution Plan


----------------------------------------------------------


Plan hash value: 2371786635


--------------------------------------------------------------------------------


| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |


--------------------------------------------------------------------------------


|   0 | SELECT STATEMENT    |          |    10 |   160 |     3   (0)| 00:00:01 |


|   1 |  SEQUENCE           | TEST_SEQ |       |       |            |          |


|*  2 |   COUNT STOPKEY     |          |       |       |            |          |


|   3 |    TABLE ACCESS FULL| T2       | 14774 |   230K|     3   (0)| 00:00:01 |


--------------------------------------------------------------------------------


Predicate Information (identified by operation id):


---------------------------------------------------


   2 - filter(ROWNUM


Note


-----


   - dynamic sampling used for this statement (level=2)


Statistics


----------------------------------------------------------


        162  recursive calls


         30  db block gets


        175  consistent gets


          0  physical reads


       6524  redo size


        807  bytes sent via SQL*Net to client


        520  bytes received via SQL*Net from client


          2  SQL*Net roundtrips to/from client


          0  sorts (memory)


          0  sorts (disk)


         10  rows processed


--由于sequence没有cache,导致每次都更新sys.seq$,导致日志产生。


3.select如果要建立临时表空间，会产生redo。


SQL> set autot traceonly ;


SQL> with a as ( select /*+   materialize */ * from emp ) select * from a;


14 rows selected.


Execution Plan


----------------------------------------------------------


Plan hash value: 2922916991


--------------------------------------------------------------------------------------------------------


| Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |


--------------------------------------------------------------------------------------------------------


|   0 | SELECT STATEMENT           |                           |    15 |  1305 |     5   (0)| 00:00:01 |


|   1 |  TEMP TABLE TRANSFORMATION |                           |       |       |            |          |


|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D660C_8B1198 |       |       |            |          |


|   3 |    TABLE ACCESS FULL       | EMP                       |    15 |   540 |     3   (0)| 00:00:01 |


|   4 |   VIEW                     |                           |    15 |  1305 |     2   (0)| 00:00:01 |


|   5 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D660C_8B1198 |    15 |   540 |     2   (0)| 00:00:01 |


--------------------------------------------------------------------------------------------------------


Statistics


----------------------------------------------------------


          2  recursive calls


          8  db block gets


         13  consistent gets


          1  physical reads


        600  redo size


       1571  bytes sent via SQL*Net to client


        520  bytes received via SQL*Net from client


          2  SQL*Net roundtrips to/from client


          0  sorts (memory)


          0  sorts (disk)


         14  rows processed


SQL> set autot off


SQL> with a as ( select /*+   materialize */ * from emp ) select * from a;


     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO


---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------


      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20


      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30


      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30


      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20


      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30


      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30


      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10


      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20


      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10


      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30


      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20


      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30


      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20


      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10


14 rows selected.


SQL> @dpc


PLAN_TABLE_OUTPUT


-------------------------------------


SQL_ID  3f5gc86jaqhct, child number 0


-------------------------------------


with a as ( select /*+   materialize */ * from emp ) select * from a


Plan hash value: 236646685


-----------------------------------------------------------------------------------------------------------------


| Id  | Operation                  | Name                      | E-Rows | Cost (%CPU)|  OMem |  1Mem | Used-Mem |


-----------------------------------------------------------------------------------------------------------------


|   0 | SELECT STATEMENT           |                           |        |     5 (100)|       |       |          |


|   1 |  TEMP TABLE TRANSFORMATION |                           |        |            |       |       |          |


|   2 |   LOAD AS SELECT           |                           |        |            |   269K|   269K|  269K (0)|


|   3 |    TABLE ACCESS FULL       | EMP                       |     15 |     3   (0)|       |       |          |


|   4 |   VIEW                     |                           |     15 |     2   (0)|       |       |          |


|   5 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D660A_8B1198 |     15 |     2   (0)|       |       |          |


-----------------------------------------------------------------------------------------------------------------


Note


-----


   - Warning: basic plan statistics not available. These are only collected when:


       * hint 'gather_plan_statistics' is used for the statement or


       * parameter 'statistics_level' is set to 'ALL', at session or system level




23 rows selected.


--删除materialize提示：


SQL> set autot traceonly ;


SQL> with a as ( select /*+   111materialize */ * from emp ) select * from a;


14 rows selected.


Execution Plan


----------------------------------------------------------


Plan hash value: 3956160932


--------------------------------------------------------------------------


| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |


--------------------------------------------------------------------------


|   0 | SELECT STATEMENT  |      |    15 |   540 |     3   (0)| 00:00:01 |


|   1 |  TABLE ACCESS FULL| EMP  |    15 |   540 |     3   (0)| 00:00:01 |


--------------------------------------------------------------------------




Statistics


----------------------------------------------------------


          1  recursive calls


          0  db block gets


          8  consistent gets


          0  physical reads


          0  redo size


       1630  bytes sent via SQL*Net to client


        520  bytes received via SQL*Net from client


          2  SQL*Net roundtrips to/from client


          0  sorts (memory)


          0  sorts (disk)


         14  rows processed


--可以发现没有redo。
				


				
时间： 2024-10-04 21:36:43

		
		


		


	

	
	

		


		
[20120417]select生产redo.txt的相关文章


								

		

			

                [20161003]触发器与redo.txt
			

		

		

									

				[20161003]触发器与redo.txt --对于触发器,我个人认为对于dba是最讨厌的东西,它使得维护变得困难,不小心就陷入陷阱里面. --我曾经跟开发讲过建立一个触发器相当于给表建立一个索引.除非万不得以不要建立触发器. --昨天看了一个例子,重复作者的测试来说明问题: http://orasql.org/2016/09/22/how-even-empty-trigger-increases-redo-generation/ 1.环境: SCOTT@test01p> @ ver1 POR			

		

	

				

		

			

                [20130312]undo与select for update.txt
			

		

		

									

				[20130312]undo与select for update.txt 闲着没事,研究一下undo与select for update. DML操作在UNDO中的信息 通过Dump UNDO Block观察到DML操作记录在UNDO中的信息,主要为以下内容:1.对于Insert操作,需要在UNDO中记录插入行的ROWID.2.对于Update操作,需要在UNDO中记录被更新列的前镜像的值,同时也会记录被更新行的ROWID.3.对于Delete操作,需要在UNDO中记录被删除行所有列的值(前镜像			

		

	

				

		

			

                为什么忘记commit也会造成select查询的性能问题（SELECT产生Redo的情形）
			

		

		

									

				为什么忘记commit也会造成select查询的性能问题(SELECT产生Redo的情形) 1.延迟库块清除 2.recursive calls中有产生REDO的情况   Oracle什么情况下select会产生redo ?   1`)快速块清除或者叫commit cleanout.事务提交的时候,oracle针对内存里的块 1)把数据块ITL  ENTRY里flag的标记为U 2)设置commit scn在Scn/Fsc列.有了两个标记就可以告诉全世界这个事务已经提交.但ITL ENTRY 的			

		

	

				

		

			

                [20170215]再次理解flush redo.txt
			

		

		

									

				[20170215]再次理解flush redo.txt --链接: http://blog.itpub.net/267265/viewspace-1992583/ http://blog.itpub.net/267265/viewspace-1992840/ 在Oracle 11g里,Data Guard 切换多了一个新的功能:flush redo. flush redo就是出现问题时,Flush可以把没有发送的redo从主库传送到standby数据库.而只要主库能启动到mount状态,那么F			

		

	

				

		

			

                [20150309]热备份与redo.txt
			

		

		

									

				[20150309]热备份与redo.txt -- 最近一段时间看关于备份的书籍,提到热备份期间,如果对某块做dml操作,redo 日志里面是包含整个数据库,防止出现块分裂. -- http://blog.itpub.net/267265/viewspace-1441552/ --实际上仅仅第1次会做,后续的dml就不会做这样的操作,做一个测试例子: 1.测试环境: SCOTT@test> @ver1 PORT_STRING                    VERSION        B			

		

	

				

		

			

                【知识点整理】NOLOGGING、APPEND、ARCHIVE和PARALLEL下，REDO、UNDO和执行速度的比较
			

		

		

									

				[知识点整理]Oracle中NOLOGGING.APPEND.ARCHIVE和PARALLEL下,REDO.UNDO和执行速度的比较 1  BLOG文档结构图     2  前言部分 2.1  导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~: ① 系统和会话级别的REDO和UNDO量的查询 ② NOLOGGING.APPEND.ARCHIVE和PARALLEL下,REDO.UNDO和执行速度的比较(重点)   Tips:			

		

	

				

		

			

                archive log文件大小与redo log文件大小关系探究
			

		

		

									

				     首先我们来看下什么是archive log file,oracle 11g 的concept中是这样定义的:When you enable archiving of the online redo logs, Oracle Database copies the online redo log files to another location before they are overwritten. These copied files are referred to as arch			

		

	

				

		

			

                小麦苗的常用代码(仅限自己使用)
			

		

		

									

				点击(此处)折叠或打开 ? ● ? ◆ ※ ⊙ ------GBK: =E6=B5=B7=E6=BB=A8 (=E5=8F ---> LHR (=E5=8F ------3DUTF-8: =E6=B5=B7=E6=BB=A8 (=E5=8F ---> =E5=B0=8F=E9=BA=A6=E8=8B=97 (=E5=8F ---小麦苗 3DUTF-8:=E5=B0=8F=E9=BA=A6=E8=8B=97 3DGBK: =D0=A1=C2=F3=C3=E7 ----- editplus 替换空			

		

	

				

		

			

                Common table expression
			

		

		

									

				common table expression Common table expression简称CTE,由SQL:1999标准引入, 目前支持CTE的数据库有Teradata, DB2, Firebird, Microsoft SQL Server, Oracle (with recursion since 11g release 2), PostgreSQL (since 8.4), MariaDB (since 10.2), SQLite (since 3.8.3), HyperSQL a