前几天看技术通信的时候，看到一篇《一次悬挂事务处理的全过程》，觉得分析的很好，和大家来分享下。其中具体的过程就不细说了，主要介绍下思路。 问题是这样的，某数据库新建了一个undotbs2，准备将系统默认的undotbs1（已经使用了2年了）切到undotbs2中。但是很长时间undotbs1中仍然有 […]
hash join 读书笔记
hash join必须使用cbo（因此必须表分析）
|
1 2 3 |
NLJ： 根据连接键，把小表的每一行，和大笔的每一行做对比。 一般情况下会对大表连接键上建index。 成本计算：读小表的行+（小表的每一行×读取大表的行） |
|
1 2 3 4 5 6 7 8 9 |
SMJ: 读取小表和大表读的行，根据连接键排序，然后根据排序后的数据集（小表的和大表的）合进行连接。 理想状态：2个表的排序操作都能在内存进行 常规情况：2阶段进行： 1.sort run阶段：数据读取到内存，排序，写出到临时表空间。直到所有的row sourse完成排序。 2.merge阶段：之前每次写到临时表空间的数据（即sort run）被重新读入到内存，进行merge。 成本计算：读取小表的行+写小表的run sort到temp表空间+ 读取大表的行+写大表的run sort到temp表空间+ cpu对小表和大表的排序消耗 |
|
1 2 |
join连接中的并行机制： 能在NLJ和SMJ中使用。并发查询的执行计划是一个树形结构（DFO），每个树上的DFO节点是一个sql操作过程，并且能把该操作过程能指派到一个query slave进程中。 |
[crayon-662fccb02abe59704 […]
小议compress表
总体说来，compress table的原理和compress index原理大致相同，compress table是压缩某表的同一字段下的相同的行，compress index是压缩复合索引中同一字段（一般是第一、二个字段）的相同行。 compress表可以通过user_tables.COMPRE […]
10g的新进程以及not documented进程
昨天在群里面有人问到，10g的rac中 ora_pz99是啥进程，一开始也没查到资料，经朋友指点后，在metalink上找到了答案，原来这是属于not document的10g后台进程。在这里，干脆把10g新增的进程整理一下吧。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production SQL> !ps -ef |grep ora_ oracle 6296 1 0 Jul23 ? 00:00:07 ora_pmon_devdb1 oracle 6304 1 0 Jul23 ? 00:00:18 ora_diag_devdb1 oracle 6310 1 0 Jul23 ? 00:00:02 ora_psp0_devdb1 oracle 6315 1 0 Jul23 ? 00:01:26 ora_lmon_devdb1 oracle 6317 1 0 Jul23 ? 00:01:41 ora_lmd0_devdb1 oracle 6319 1 1 Jul23 ? 00:03:28 ora_lms0_devdb1 oracle 6329 1 0 Jul23 ? 00:00:01 ora_mman_devdb1 oracle 6331 1 0 Jul23 ? 00:00:07 ora_dbw0_devdb1 oracle 6333 1 0 Jul23 ? 00:00:15 ora_lgwr_devdb1 oracle 6337 1 0 Jul23 ? 00:00:15 ora_ckpt_devdb1 oracle 6341 1 0 Jul23 ? 00:00:13 ora_smon_devdb1 oracle 6351 1 0 Jul23 ? 00:00:00 ora_reco_devdb1 oracle 6359 1 0 Jul23 ? 00:00:33 ora_cjq0_devdb1 oracle 6365 1 0 Jul23 ? 00:00:18 ora_mmon_devdb1 oracle 6370 1 0 Jul23 ? 00:00:11 ora_mmnl_devdb1 oracle 6372 1 0 Jul23 ? 00:00:00 ora_d000_devdb1 oracle 6374 1 0 Jul23 ? 00:00:00 ora_s000_devdb1 oracle 6394 1 0 Jul23 ? 00:00:29 ora_lck0_devdb1 oracle 6422 1 0 Jul23 ? 00:00:01 ora_asmb_devdb1 oracle 6437 1 0 Jul23 ? 00:00:01 ora_rbal_devdb1 oracle 6840 1 0 Jul23 ? 00:00:01 ora_arc0_devdb1 oracle 6842 1 0 Jul23 ? 00:00:03 ora_arc1_devdb1 oracle 6895 1 0 Jul23 ? 00:00:01 ora_qmnc_devdb1 oracle 6916 1 0 Jul23 ? 00:00:00 ora_o003_devdb1 oracle 7065 1 0 Jul23 ? 00:00:00 ora_q001_devdb1 oracle 5327 1 0 Jul23 ? 00:00:30 ora_pz99_devdb1 oracle 16365 1 1 Jul23 ? 00:01:19 ora_j000_devdb1 oracle 23522 1 0 00:58 ? 00:00:00 ora_q002_devdb1 oracle 25680 1 0 01:00 ? 00:00:00 ora_q000_devdb1 oracle 26024 25845 0 01:00 pts/2 00:00:00 /bin/ksh -c ps -ef |grep ora_ oracle 26026 26024 0 01:00 pts/2 00:00:00 grep ora_ SQL> |
10 […]
dbms_metadata的进一步学习
一开始，只是认为dbms_metadata.get_ddl仅仅能用来获取表结构的语句，其实这个包的功能还是挺多的，差不多所有你希望的对象都能获取： 1.显示设置：
|
1 2 3 4 5 6 7 8 9 10 11 |
/*创建DBMS_METADATA： @?/rdbms/admin/catmeta.sql */ SET SERVEROUTPUT ON SET LINESIZE 1000 SET FEEDBACK OFF set long 999999 SET PAGESIZE 1000 /*若希望不显示storage参数： EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false); */ |
2.9i R2所支持的45个OBJECT TYPE： [crayon- […]
关于rac的external table
今天有朋友在blog中问了这么一个问题，RAC环境中应用外部表（rac1上），如果把外部表设置并行度，在查询该外部表时是否会报错？，一开始就觉得如果是不是在共享存储，由于另一个节点访问不了directory设置的路径，因此会报错。测试结果也是如此： 在rac1的非共享存储上上创建一个用于测试的平面文 […]
调优笔记
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 |
1.内存调优 注：SGA中的shared pool中，系统自动优先缓存datadictionary cache，对系统性能影响较大的是library cache。 1.1 library cache 调优（v$librarycache/v$sql/v$sqlarea/v$sqltext/v$db_object_cache） 判断：要不要调整library cache？判断条件：v$librarycache.pinhitratio|gethitratio尽量接近100% 1.1.1 存储对象所消耗的内存预估： select sum(SHARABLE_MEM) from v$db_object_cache; 1.1.2 经常执行的sql消耗的内存预估： select sum(SHARABLE_MEM) from v$sqlarea where EXECUTIONS>100; 1.1.3 每个用户打开游标，需要250个字节： select sum(250*USERS_OPENING) from v$sqlarea; 1.1.4 keep在shared pool中： 先创建dbms_shared_pool： @?/rdbms/admin/dbmspool.sql 创建后： execute dbms_shared_pool.keep('package_name');----------------------将package keep execute dbms_shared_pool.keep('address,hash_value');----------------将匿名的块 keep 1.2 data dictionary cache 调优（v$rowcache） 判断：要不要调整data dictionary cache？ 判断标准：v$rowcache.(gets-getmisses)/gets尽量接近100% 1.2.1 由于没有单独调整数据字典高速缓冲的参数，但是系统又优先缓存data dictionary cache，只能调整shared pool的总体大小。 1.3 UGA 1.3.1 使用共享服务器模式的时候，如没有配置large pool，用户会话数据（排序等等）和游标状态（共享sql）会在SGA。 因此如果发现一个报表系统shared pool特别大，有没有配置large pool，请检查是否使用了共享服务器模式。 1.3.2 UGA预估： SELECT 'current session uga memory',SUM(VALUE)/1024 size_kb FROM v$mystat a,v$statname b WHERE a.STATISTIC#=b.STATISTIC# AND b.NAME IN ('session uga memory') GROUP BY NAME UNION ALL SELECT decode(name,'session uga memory','all session uga memory',name),SUM(VALUE)/1024 size_kb FROM v$sesstat a,v$statname b WHERE a.STATISTIC#=b.STATISTIC# AND b.NAME IN ('session uga memory','session uga memory max') GROUP BY NAME; 1.4 db buffer cache（V$sysstat/v$buffer_pool_statistics/v$buffer_pool/v$sysstat/v$sess_io/v$wait_stat） 1.4.1 db buffer caceh和datafile之间的交互： ------DBWn--------> buffer cache datafile <---服务器进程----- 1.4.2 db buffer cache和LRU列表： 传入的块从LRU的冷端复制到缓冲区中，然后该缓冲区的数据会放到LRU列表的中部，在根据情况上下浮动。 1.4.3 DB_CACHE_ADVICE off-ready-on 1.4.4 dbwr什么时候将dirty buffer写出：A.checkpoint 队列到达阈值；B.LRU中显示没有空余的buffer；C.logwr指示已经发出checkpoint信号；D.表空间变化；E.Drop object;F.clean shutdown 判断：要不要调整db cache size：命中率小于90% 判断标准：SQL> SELECT 1 - (phy.value – lob.value – dir.value) / ses.value "CACHE HIT RATIO" 2 FROM v$sysstat ses, v$sysstat lob, 3 v$sysstat dir, v$sysstat phy 3 WHERE ses.name = 'session logical reads' 4 AND dir.name = ‘physical reads direct' 5 AND lob.name = 'physical reads direct (lob)' 6 AND phy.name = 'physical reads'; 注：因为直接读和lob的读取不经过buffer pool缓存，因此整个physical read要减去这2项。 启动开始有较多的物理读，会影响buffer caceh命中率的统计 1.4.5 影响buffer cache的因素：全表扫描、程序、命中率分布不均、可随机访问的大表（其实也是全表扫描） 1.4.6 keep一般为default的10%，recycle一般是default的2倍 1.4.7 空闲列表 ：A.单cpu增加空闲列表不会增加性能 B. 1.4.8 相关视图： v$sess_io:预估recycle 1.5 log buffer 1.5.1 诊断标准：A.v$sysstat中的redo buffer allocation retries/redo entries<10% B.$session_wait中不应出现log buffer space 1.5.2 对策：加大log buffer，或者将relog转移到性能好的磁盘上 1.5.3 深入分析，lgwr变慢的原因：lgwr之前和之后的工作是在logbuffer和redolog中，分析这2者的情况： 1.5.3.1 redolog太小，而logbuffer较大，因此需要频繁的切换redolog，当本次切换还未完成时，又轮到下次切换，出现等待（表现为system_event中的log file switch completion%过多） 1.5.3.2 redolog太大或者redolog组不够，当上次的logbuffer中的内容还没完全写入到redolog中，需要用到这块的logbuffer了，出现等待，即redolog为高active状态(非current和inactive，未完成checkpoint)，需要加多redolog组或者调整调整FAST_START_MTTR_TARGET，加大checkpoint频率（表现为system_event中'log file switch (check%';检查点未完成过多） 1.5.3.3 redolog切换不了，可能是写archlog过慢，或者archlog空间爆满。（表现为system_event中log file switch (arch%';archlog未完成过多） 1.6 java pool 的优化 判断：要不要调整java pool：v$sgastat中java pool 的free memory 和memory in use的比例 2 IO优化 2.1 v$filestat 2.2 条带化：A.raid B.数据文件条带化 C.alter table allocate extent 2.3 增加db_file_multiblock_read_count，减少IO次数 2.4 减少v$sysstat中的long tables（大于4 blocks的表） 2.5 关于checkpoint： 2.5.1 增量chkpt： checkpoint队列：1.dirty buffer：第一次数据块被修改时就写入chkpt队列中。2.RBA：redolog中第一次修改数据块的RBA也被记录在chkpt队列 每隔3秒做一次增量chkpt，不强制写数据文件，只是将chkpt队列中的第一个（即时间至今最长的一个）RBA写入到控制文件，实例crash的恢复，即redolog中该RBA之后的内容。 增量chkpt发生条件：1.每隔3秒做增量chkpt，更新控制文件；2.switch logfile，更新控制文件和数据文件头 2.5.2 全chkpt： 完整检查点：1.dirty buffer写入到datafile ；2.clean shutdown；3.alter system checkpoint 表空间检查点：表空间变化时（backup、offline） 3 排序优化 3.1 sort_area_size和sort_area_retained_size 3.2 CREATE_BITMAP_AREA_SIZE和BITMAP_MERGE_AREA_SIZE:创建索引速度有关 3.3 需要排序的操作：1.建检索 2.维护索引 3.order by/group by/distinct/union/minus/intersect 4.Sort Merge Join 5.analyze 3.4 避免排序的操作：1.create index nosort（要求事先已经asc排序） 2.union all 3.analyze for column 4.analyze estimate 3.5 判断：要不要调整排序区：(v$sysstat.name='sorts (disk)'/v$sysstat.name='sorts (memory)')<5% 注意：dss中该比例不适用。 3.6 对策：增加sort_area_size和PGA_AGGREGATE_TARGET 4 latch优化 4.1 重要的latch free 4.1.1 shared pool和library cache的：sql没有绑定变量(检查v$sqlarea.parse_call,v$sqlarea.EXECUTIONS)或者存在热块或者为共享服务器模式但是没有配large pool 4.1.2 db buffer cache的lru：基于db buffer cache大量的排序或者大量的index full scan和full table scan 5 undo优化 5.1 undo使用：（1）读一致性 （2）dml的事务回退 （3）事务故障恢复，打开数据库时rollback未commit的事务 5.2 何时需要加回退段（手工管理）： SELECT sum(waits)* 100 /sum(gets) "Ratio", sum(waits) "Waits", sum(gets) "Gets" FROM v$rollstat; SELECT event, total_waits, total_timeouts FROM v$system_event WHERE event LIKE 'undo segment tx slot'; SELECT class, count FROM v$waitstat WHERE class LIKE '%undo%'; 5.3 大事务指定回滚段（提交后结束）： sys@ORA11G(192.168.0.11)> set transaction use rollback segment "_SYSSMU1_1193229161$"; 事务处理集。 5.4 shared server 题外话：判断是否shared server： (1).show parameter shared_server 大于0为共享服务器模式 (2).select server from v$session 看到DEDICATED为专用；看到SHARED即为共享，且shared_server_process正在对其其他服务；看到NONE为也为共享，且shared_server_process尚未对其其他服务。 5.4.1 监控视图：V$SHARED_SERVER_MONITOR V$DISPATCHER繁忙率=busy/(busy+idle) 等待时间=wait/totalq V$DISPATCHER_RATE 5.4.2 调整参数：SHARED_SERVERS--共享服务器进程的数量 DISPATCHERS-----调度进程的数量 5.5 sql调优 5.5.1 optimizer_mode:choose(默认，如果涉及的任一表有统计信息，就用CBO的all_rows，否则使用RBO) 5.5.2 执行计划中的信息：行访问方法，连接顺序，连接方法，分布式事务访问（不包含远程节点），子查询 5.5.3 创建存储概要：参数文件 CREATE_STORED_OUTLINES------------------------------------------指定创建存储概要的名称（可取名为OLTP、DSS，或者TRUE、FALSE）。 参数文件 USE_STORED_OUTLINES---------------------------------------------指定需要使用的存储概要名称（可取名为OLTP、DSS，或者TRUE、FALSE，在session级或system级指定后select。 参数文件 USE_PRIVATE_OUTLINES--------------------------------------------专用概要，仅用于当前会话，如不存在，不使用共用的概要。 create or replace outline XXX for catalog OLTP on select ……--------------创建存储概要的名称。 5.5.4 trace: 5.5.4.1 @?/rdbms/admin/utlxplan.sql explain plan for @?/rdbms/admin/utlxpls.sql or @?/rdbms/admin/utlxplp.sql or select * from table(dbms_xplan.display); 5.5.4.2 alter session set sql_trace=true; tkprof 5.5.4.3 exec dbms_session.set_sql_trace=true; 5.5.4.4 exec dbms_system.set_sql_trace_in_session(sid,serial#,true); 5.5.4.5 set autotrace on 5.5.5 表的统计信息包含：NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,ROW_MOVEMENT,AVG_ROW_LEN,LAST_ANALYZED (dba_tables) 5.5.6 索引的统计信息包含：DEGREE,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,NUM_ROWS,CLUSTERING_FACTOR (dba_indexes) 5.5.7 列统计信息包含：NUM_DISTINCT,LOW_VALUE,HIGH_VALUE(DBA_TAB_COL_STATISTICS) 5.5.8 直方图：EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'EMPLOYEES', METHOD_OPT => 'FOR COLUMNS salary SIZE 10'); 5.5.9 dbms_stats.gather_system_stats:收集信息 dbms_stats.get_system_stats:验证统计信息 dbms_stats.set_system_stats:明确设置系统统计信息 5.6 块调优 5.6.1 将extent大小设置为5*DB_FILE_MULTIBLOCK_READ_COUNT的倍数。（原因：一个extent=5×DB_FILE_MULTIBLOCK_READ_COUNT），注：对非全表扫描，该优化无效。 5.6.2 HWM:以5 block递增， 5.6.3 减少行迁移：提高pctfree 5.7 索引调优 5.7.1 重建浪费大于20%的索引: analyze index index_name validate structure; SELECT name, (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS wastage FROM index_stats; 5.7.2 找出未使用的索引 alter index index_name monitoring usage; alter index index_name nomonitoring usage; select index_name,used from v$object_usage; 5.7.3 注函数索引必须在CBO下使用，对RBO无效 6 应用调优 6.1 数据访问类型： 6.1.1 聚簇：使用同一个物理地址存放两个或者多个表。先建簇，再建簇表，再向簇表insert数据。分两类：hash 和 index 6.1.2 b-tree索引：叶块填满后，将分成两半，50%在旧叶块，50%到新叶块；分支类推，一直到根 6.1.3 位图索引：lock位图段 6.1.4 反向索引：为了避免降低索引高度。 6.1.5 IOT表：注：IOT表上可使用位图索引，但是需要建立map。 6.1.6 分区表：若range 分区中可能含有null，需要设置maxvalue 若hash，为了数据分布均匀，分区个数需设置为2的n次方。提高单键查找的效率，不提高range scan的效率。 若list，需提前了解有多少种类 若复合，支持range-hash和range-list 6.1.7 分区索引：local index：与表分区一一对应关系;可以是list、hash、range、compsite任一种类;bitmap索引必须是local index global index：与表分区可以是多对一的关系;只能是range且需设置maxvlaue;表分区变化后需重建。 另外的分法： prefix index：分区的key为组合索引的最左端字段;可以是唯一索引也可以是非唯一索引 non-prefix index：分区的key不是组合索引的最左端字段;可以是唯一索引也可以是非唯一索引;唯一的non-prefix index条件：分区键是组合索引的子集。 注：non-prefix的global index不存在。 6.1.8 使用物化视图 |
表的学习笔记
表的学习笔记本（除了列出的表之外，还有分区表和外部表，在此不一一列出）： 1.heap table： 1.1 堆表。最常用，插入数据时，到HWM以下的空块。对于mssm，大于pctfree就从freelist中去掉，小于pctused时加到freelist [crayon-662fccb02f3a6 […]
BUG：9204中cast不能返回正确的值
问题是这样引起的：测试建一个single table hash cluster，建了好几次，也查了不少资料，一般报错 ORA-01753 是因为设置的字段类型不一致，但是经检查字段类型确实是一致了，且number也被cast定成了整形，应该没问题呀，但是总是报错： [crayon-662fccb03 […]
基于数据文件的rman建DG
前段时间，itpub上有朋友问“小弟仔细想了想发现一个问题：dataguard的空间不足以容下一份rman备份和数据文件．这样就只能停机做了吗？？”。在主机空间不够的情况下，我们可以用rman一个数据文件一个数据文件的做dataguard。今天把基于数据文件的rman建datagurard操作了一下 […]
Dataguard启动的各个阶段
主库启动instance和lsnr，在备库启动lsnr后，在dg启动的各个阶段，dg都做了什么呢：启动了那些进程？什么时候会传归档？什么时候开始应用归档？ 1.备库关闭时。 1.1 检查备库进程：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
[oracle@standby1 arch]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.4.0 - Production on Fri Jan 11 23:52:25 2008 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. > > !ps -ef |grep ora_ oracle 3917 3915 0 23:52 pts/1 00:00:00 /bin/bash -c ps -ef |grep ora_ oracle 3919 3917 0 23:52 pts/1 00:00:00 grep ora_ |
1.2 备库arch目录： [cra […]
ASM的管理：asmcmd命令介绍
oracle 10R2推出了asmcmd这个工具，可以用来管理asm存储上的文件。 1.使用asmcmd命令必须先启动asm实例，不然会有以下报错：
|
1 2 3 4 |
rac2-> asmcmd -p ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux Error: 2: No such file or directory (DBD ERROR: OCISessionBegin) |
2.使用asmcmd必须先指定ORACLE_HOME和ORACLE_SID，注意此处 […]