------------------如何查询日志切换频率 --通过以下SQL检查日志切换频率 --Check_logfile_switch set lin 300; col "00:" for a3; col "01:" for a3; col "02:" for a3; col "03:" for a3; col "04:" for a3; col "05:" for a3; col "06:" for a3; col "07:" for a3; col "08:" for a3; col "09:" for a3; col "10:" for a3; col "11:" for a3; col "12:" for a3; col "13:" for a3; col "14:" for a3; col "15:" for a3; col "16:" for a3; col "17:" for a3; col "18:" for a3; col "19:" for a3; col "20:" for a3; col "21:" for a3; col "22:" for a3; col "23:" for a3; SELECT * FROM ( SELECT * FROM ( SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0)), '99') "00:" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0)), '99') "01:" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0)), '99') "02:" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0)), '99') "03:" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0)), '99') "04:" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0)), '99') "05:" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0)), '99') "06:" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0)), '99') "07:" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0)), '99') "08:" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0)), '99') "09:" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0)), '99') "10:" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0)), '99') "11:" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0)), '99') "12:" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0)), '99') "13:" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0)), '99') "14:" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0)), '99') "15:" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0)), '99') "16:" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0)), '99') "17:" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0)), '99') "18:" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0)), '99') "19:" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0)), '99') "20:" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0)), '99') "21:" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0)), '99') "22:" , to_char(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0)), '99') "23:" FROM V$LOG_HISTORY WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate) GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM') )ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC) WHERE ROWNUM < 8 --(rownum确定记录的数量,30天够了) / 二、Reduce Redo(减少Redo)------------------------------------------------ 介绍: *.Amount of redo generated by both INSERT and UPDATE can be variable due to -->Insert和Update会产生大量日志,取决于 1).Undo segment management --undo 段的管理 2).Recursive DDL statements e.g. extent allocation --递归的DDL调用解析 3).Block cleanouts --更新插入过程中产生Block cleanout Conclusion(总结) 1)减少更新的列可以减少redo --Eliminating unnecessary columns from update statements reduced update redo generation by 882640 bytes 2)减少更新大列可以明显减少redo --Would be significantly more if unchanged columns included long fields e.g. CHAR, or VARCHAR2 3)减少'Select for update'可以明显减少redo --Eliminating SELECT FOR UPDATE statement reduced update redo generation by 3109276 bytes (for update 在redo中多加锁,因此多了锁的信息) 4)减少不必要的Update语句可以减少大量的redo --Eliminating unnecessary update statements reduced update redo generation by 6405168 bytes 5)减少Commit次数可以减少大量的redo insert into t(c1) values(1) --产生 648 redo size SQL> begin 2 for i in 1..10000 loop 3 insert into t(c1) values(i); 4 commit; 5 end loop; 6 end; PL/SQL procedure successfully completed DBA监视当前session redo 1. SQL> select sid from v$session where audsid=15994; SID ---------- 1075 查询一: SID CLASS NAME SID VALUE ---------- ---------- ---------------------------------------------------------------- ---------- ---------- 1075 2 redo entries 1075 70519 70519 1075 2 redo size 1075 19667524 查询二:执行后 SID CLASS NAME SID VALUE ---------- ---------- ---------------------------------------------------------------- ---------- ---------- 1075 2 redo entries 1075 101184 1075 2 redo size 1075 28936504 -->产生redo差即该Bock块产生的redo:9268980(28936504-19667524) 2. ------t_t和t的结构完全一样 SQL> begin 2 for i in 1..10000 loop 3 insert into t_t(c1) values(i); 4 end loop; 5 commit; 6 end; 查询一: SID CLASS NAME SID VALUE ---------- ---------- ---------------------------------------------------------------- ---------- ---------- 1075 2 redo entries 1075 101184 1075 2 redo size 1075 28936504 查询二: SID CLASS NAME SID VALUE --------- ---------- ---------------------------------------------------------------- ---------- ---------- 1075 2 redo entries 1075 121592 1075 2 redo size 1075 34178100 -->产生redo:5241596(34178100-28936504) 结论:以10000条记录中1.2两中方式产生的reduce redo=4027384(9268980-5241596)也就是第2中方式的commit一次比第一种commit10000次减少了将近一半的Redo 6)加大批量(batch size)Insert 可以减少redo --reduced insert redo generation by 7320636 bytes 1.批量提交20条记录 SQL> begin 2 for i in 1..20 loop 3 insert into t(c1) values(i); 4 end loop; 5 end; 6 / SID CLASS NAME SID VALUE --------- ---------- ---------------------------------------------------------------- ---------- ---------- 1075 2 redo entries 1075 121592 1075 2 redo size 1075 34178100 运行后: SID CLASS NAME SID VALUE ---------- ---------- ---------------------------------------------------------------- ---------- ---------- 1075 2 redo entries 1075 121632 1075 2 redo size 1075 34188124 -->产生redo:10024 2.insert into t(c1) values(1..20) 运行前: SID CLASS NAME SID VALUE ---------- ---------- ---------------------------------------------------------------- ---------- ---------- 1075 2 redo entries 1075 121632 1075 2 redo size 1075 34188124 运行后: SID CLASS NAME SID VALUE ---------- ---------- ---------------------------------------------------------------- ---------- ---------- 1075 2 redo entries 1075 121678 1075 2 redo size 1075 34200596 -->产生redo:12472 结论:以20条插为记录,批处理的话比非批处理减少:2448 redo 因此批处理比单条插会减少更多的redo 7)全局临时表可以减少大量的redo ---------tmp_结构和t一样1000条记录为样本,t只有c1 number列------------ SQL> begin 2 for i in 1..10000 loop 3 insert into t(c1) values(i); 4 end loop; 5 commit; 6 end; 1.非global temporary table 查询一: SID CLASS NAME SID VALUE ---------- ---------- ---------------------------------------------------------------- ---------- ---------- 1075 2 redo entries 1075 121678 1075 2 redo size 1075 34200596 运行后: SID CLASS NAME SID VALUE ---------- ---------- ---------------------------------------------------------------- ---------- ---------- 1075 2 redo entries 1075 142232 1075 2 redo size 1075 39679896 -->产生redo:5479300 2.Gloable temporary table tmp_t SQL> begin 2 for i in 1..10000 loop 3 insert into tmp_t(c1) values(i); 4 end loop; 5 commit; 6 end; 查询一: create global temporary table tmp_t as select * from t where 1=2; SID CLASS NAME SID VALUE ---------- ---------- ---------------------------------------------------------------- ---------- ---------- 1075 2 redo entries 1075 142232 1075 2 redo size 1075 39679896 --注意创建临时表在当前session级别不会产生redo 查询二:运行后 SID CLASS NAME SID VALUE ---------- ---------- ---------------------------------------------------------------- ---------- ---------- 1075 2 redo entries 1075 152235 1075 2 redo size 1075 41287164 -->产生redo:1607268 结论:用Global temporary table 产生redo: 1607268 非Global temporary table 产生redo: 5479300 用Global temporary table 减少redo:5479300-1607268=3872032 用Global temporary tanle可以减少大量的Redo 8)外部表Extenrnal table 减少Redo Extenrnal不能进程日常操作,感觉就是直接想外部文件以对应的格式放在OS目录进行查询,因此不会产生什么redo -----------10000条记录插入,ext_t外部表,t_t为结构和t相同的,两个表都没记录------- SQL> begin 2 for i in 1..10000 loop 3 insert into t_t(c1) values(i); 4 end loop; 5 end; 6 / PL/SQL procedure successfully completed. 1.常规表 测试前:新的session SID CLASS NAME SID VALUE ---------- ---------- ---------------------------------------------------------------- ---------- ---------- 1075 2 redo entries 1075 0 1075 2 redo size 1075 0 执行后: SID CLASS NAME SID VALUE ---------- ---------- ---------------------------------------------------------------- ---------- ---------- 1075 2 redo entries 1075 10110 1075 2 redo size 1075 2659600 -->产生redo:2659272 (前面可能是由于t表存在记录数导致Redo比较多) 2.创建外部表 CREATE TABLE T_EXT ( c1 number ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY USER_DIR ACCESS PARAMETERS ( fields terminated by ',' ) LOCATION (USER_DIR:'t_ext.dat') --t_ext.dat包含1到10000条记录 ) REJECT LIMIT 0 NOPARALLEL NOMONITORING; 插数据前： SID CLASS NAME SID VALUE ---------- ---------- ---------------------------------------------------------------- ---------- ---------- 1075 2 redo entries 1075 10110 1075 2 redo size 1075 2659600
