1. diagnostic file(alertlog, tracefile, redolog), 监控数据库动作时间点 [troubleshooting]
alertlog : 确认checkpoint时间, 其他一些对数据库进行的大动作, ok 直接打开查看
tracefile: 和用户进程相关的一些监控, 稍微有点麻烦
首先要设置session级别, 不要设置成instance级别(会产生大量文件), 对特殊的session进行监控, 然后要用tkprof 读取tracefile.
现在应该也有可视化工具了.
execute dbms_system.set_sql_trace_in_session(sid,serial#，true);
redolog : 数据库实际运行时的动作, 所有的 statement 即时间点, 特殊软件打开查看, 例如 toad
注: 个人感觉, tracefile 用不是, 直接一个 autotrace 就可以了.
2. 常用数据字典
用toad之类的工具直接可以浏览对象, 用到时再说
v$session, v$sqltext, v$sql_plan, v$sql_plan_statistics, v$sql_plan_statistics_all
User_table_histograms, User_tab_statistics, User_tab_col_statistics, User_ind_statistics
3. SCN, select操作系统会分配一个SCN, 数据块也有一个SCN(也是在内存的cache中)
无论是same session 还是 different session, 只要记住一点,
你要 select 时，系统会分配一个SCN（SYSTEM CHANGE NUMBER），只能增加不能减少的一个NUMBER，
每读一个数据块( 数据块本身也有SCN), 当自己的SCN 大于数据块的SCN，意味着，这个数据块没有被修改。
反之，到 UNDO SEGMENT 里找到相应数据块，读取该信息。
4. index (结构, 优化)
这里只介绍B*树索引, 最常用的一种索引, 这种索引, 一个索引条目就是对应一行
B*树索引不存在非唯一条目, (索引也是一个存储结构, 在这个存储结构中不存在两行完全相同的数据),
具体参考, blog->大师->11index
5. buffer cache 工作原理
dirty buffer, free buffer, clean buffer(buffer内容与数据文件一致), pin buffern(当前正在更新的数据块)
对于clean buffer, free buffer 统称为可用buffer.
LRU 最近最少被使用的buffer链表, 先被使用的放在后边, 后被使用的放在前边, 如果Buffer被DML语句修改了,
则该buffer会从LRU链表上摘下来, LRU链表上的buffer都是可用的数据块.
当服务器进程无法找到空的buffer来存放新的数据请求时, 则需要把已经存放了数据的buffer拿来使用, 也就是用
新的数据块内容覆盖曾经使用过的buffer, 在查找应该覆盖哪个buffer时, oracle会在LRU链表的尾部开始扫描,
如果扫描到的buffer正在被使用, 则跳过该buffer, 继续往下找, 直到找到为止, 如果扫描了一定量的buffer以后
还没找到可用的buffer, 则说明脏数据块太多了, 于是触发DBWn进程, 将脏数据块刷新到数据文件里, 刷新完毕以后,
buffer的内容与数据文件里的内容一致, 于是这些脏数据块变成了clean buffer, 就可以被覆盖了, 这些干净的buffer
就会被挂在LRU链表尾部.
数据库崩溃时, 重做日志文件的起点, 是哪?
CKPT后台进程, 检查点进程, 这个进程与 DBWn合作, 从而确定这个重做的起点, 这个起点也有一个专门的名字, 叫做
检查点位置(checkpoint position)该检查点位置记录在控制文件里, oracle为了在检查点的算法上更加的具有可扩展性,
(也就是能够在巨大的buffer cache下依然有效工作), 引入了检查点队列(checkpoint queue), 该队列串起来的都是脏数据块
所对应的 buffer header, 而每次DBWn写脏数据块时, 也是从检查点队列上扫描脏数据块, 并将这些脏数据块实际写入数据文件.
当写完以后, DBWn 会将这些已经写入数据文件的脏数据块从检查点队列上摘下来, 这样即便在巨大的buffer cache下工作,
CKPT也能狗快速的确定哪些脏数据块已经被写入了数据文件, 而那些还没有写入数据文件, 显然, 只要在检查点队列上的数据块
都是还没有写入数据文件的脏数据块, 同时为了能够尽量减少实例崩溃后恢复的时间, oracle还引入了增量检查点, 从而增加了
检查点的启动次数.
6. Hints, Explain plan (autotrace, book: cost-based oracle fundamentals, )
写hint的目的, 手工指定SQL语句的执行计划, 以下是常用的控制方式:
1) 表的访问路径, 全表扫描, 索引扫描, 还是直接利用rowid
/*+FULL(tablename)*/ /*+ROWID(tablename)*/ /*+CLUSTER(tablename)*/ /*+INDEX(table [index_name])*/
/*+NO_INDEX(TABLE [index_name])*/
2) 表之间的连接类型
/*+USE_NL(TABLE)*/ -- 嵌套 /*+USE_MERGE(TABLE)*/ /*+USE_HASH(TABLE)*/
3) 表之间的连接顺序
/*+ ORDERED*/ 按照from子句中标的顺序从左到友的连接
/*+ STAR*/ 指优化器使用星型查询
7. 绑定变量窥探
绑定变量窥探的作用是在SQL语句硬分析的时候, 查看一下当前SQL谓词的值, 以便胜场最佳的执行计划.要注意的是,Bind Peeking
只发生在硬分析的时候，即SQL被第一次执行的时候，之后的变量将不会在做peeking, 所以, 对调优来说, 意义不大.
在10g 中绑定变量窥探有一个缺点，绑定变量窥探不管后续传入的绑定变量的具体输入值是什么，
它会一直沿用之前第一次硬解析时所产省的解析树和执行计划（这个缺点在oracle 11g中引入自适应游标共享后才得到了改善）。
因为它可能使CBO在某些情况下所选择的执行计划并不是目标SQL在当前情况下的最优执行计划，
而且它可能会带来目标SQL执行计划的突然改变，影响系统的性能。
注意: 绑定变量窥探表明, 有时候执行SQL时, 不使用绑定变量比使用绑定变量还好, 但是在11G以后, 由于绑定变量得到改善,
所以, 还是推荐使用.
8. 物化视图, 需要刷新来进行同步基本表, 在OLAP中使用较多
首先, 创建material_log, 创建时, 会自动创建一个table, table name 为 m$log_table_name, 这个log是用来
配合同步的, 对主表的任何变化, 会在material_log上备份一下
创建物化视图时, 找到对应脚本, 简单修改, 注意一下同步时间, 就可以了.
另外, 你创建 material_view 时, 会自动增加一列, rowid, 并为这列自动 添加索引, 主要原因是由于同步:
同步原理:
主表发生的变化, 会在material_log中保存一条记录, 该记录中的主要内容rowid及一些操作标记, 比如
当你向主表中插入一条记录时, 这里会记录下这一行的rowid和插入标记等, 同步时, 发现是插入操作, 会通过
rowid找到该行, 并更新到存储在物化视图里, 如果是删除操作, 也是通过rowid, 找到当前物化视图里的对应行,
也进行删除操作
9. 调优SQL基本原则
a. 书写格式要规范, 比如大小写
b. 使用绑定变量
c. 是否使用索引: 只有嵌套连接会使用到索引, 其他连接, 索引没有什么实际意义
d. 看连接的顺序是否能使用到索引
e. 数据字典或视图, 是否被分析过
f. 索引列是否函数参数, 注意, 函数对索引的影响
g. 是否存在潜在的数据类型转换, 从而导致索引跟函数有关, 例如字符型转换为数值型, 那么oracle会自动调用to_number()函数
h. 带通配符（%）的 like 语句如果通配符（%）在搜寻词首出现，oracle 系统不会使用索引。
在很多情况下可能无法避免这种情况，然而当通配符出现在字符串其他位置时，优化器就能利用索引。
在下面的查询中索引得到了使用：select * from user_m where loginid like 'r%';
i. 尽量避免使用 in, exists 等, 碰到需要的时候, 尽量使用表的连接.
j. 返回结果集大于1W行时, 不适合使用嵌套索引, 注意, 嵌套索引的优先级比较高, 所以, 这个时候往往需要你使用Hint来
改变连接类型.
k. 在建立组合索引时, 尽量把最常用的字段放在前边. 另外, 尽量把散列值(就是这列可以取到的所有值)少的放在前边, 这样做的目的是, 可以使oracle走跳跃索引, 避免全表扫描.
l. 对于大批量的删除操作, 应该避免使用delete, (delete 1条记录所产生的undo信息是最多的, 因为undo需要记录之前所有的内容, 准备insert), 可以考虑 truncate 或者 create table as select 等
10.查看oracle文档, 参考blog->oracle实验->如何查看oracle文档
个人现在使用11g文档, 觉得使用最多的还是在 Supporting Documentation 下:(这个标题下的都很有用)
Concepts, Administrator'Guide, Reference, SQL Language Reference, Performance Tuning Guide, Error Messages
11.oracle 在多表连接时, 一定会两个两个进行连接, 一共有4种连接方式, 嵌套连接, 笛卡儿积, 归并连接, hash连接
嵌套连接: 主要连接方式, 读取都会利用到索引, 首先读取主表的一个值, 在附表中根据该值读取索引,
通过索引读取附表中对应的行, 进行连接.
类似 for 循环, 主表是外层, 内层是附表
嵌套循环时, 为什么主张要将小表放在外层作为驱动表, 因为驱动表需要进行全扫描, 而内层表一般可以是用
index, 所以, 如果是外层表是大表, 全扫描相对来说就需要很多时间.
归并连接: 先将两个表分别排序, 然后再对已经排序的的两个表进行join(注意, 这个join不同于嵌套连接,
可以想数据结构中的归并排序)
通常, 归并连接在非相等连接操作中有用.
归并连接不被推荐, 因为把两个表分别排序本身就很费资源, 尤其是比较的表, 除非是之前两个表都已经分别排序完成, 那还可以.
散列连接: 全扫描一个小表, 然后对这个小表的值, 做散列(即hash函数), 然后再全扫描较大的表, 针对值进行映射
通常, 散列擅长一个大一个小的两个表之间的join.
笛卡儿积: 最普通的连接, 全连接
注意: 只有嵌套连接会使用到索引, 其他连接, 索引没有什么实际意义
在返回结果集大于 1 W 行时, 不建议使用嵌套循环连接, 应考虑使用hash连接或排序合并连接进行替换.
说明: 返回大量结果集时嵌套循环连接成本较高, 效率很低, 此时应考虑变换连接方式, 特别在使用RBO优化器时,
嵌套循环连接的优先级较高, 通常需要用 “提示” 来强制改变连接方式.
两个无序大表的连接, 在连接字段无索引时, 不建议使用合并排序连接
说明: 排序合并联结需要先对两个表按连接字段进行排序, 如果两个表都非常大, 其排序操作的代价会非常巨大.
在建立复合索引时, 尽量把最常用的字段放在最前面, 尽量把离散值较少的字段放在前面, 尽量保证复合索引中至少有一个字段具有非空约束.
最后总结:
排序 - - 合并连接（Sort Merge Join， SMJ）：
a） 对于非等值连接，这种连接方式的效率是比较高的。
b） 如果在关联的列上都有索引，效果更好。
c） 对于将2个较大的row source做连接，该连接方法比NL连接要好一些。
d） 但是如果sort merge返回的row source过大，则又会导致使用过多的rowid在表中查询数据时，数据库性能下降，因为过多的I/O.
嵌套循环（Nested Loops， NL）：
a） 如果driving row source（外部表）比较小，并且在inner row source（内部表）上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。
b） NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。
哈希连接（Hash Join， HJ）：
a） 这种方法是在oracle7后来引入的，使用了比较先进的连接理论，一般来说，其效率应该好于其它2种连接，但是这种连接只能用在CBO优化器中，而且需要设置合适的hash_area_size参数，才能取得较好的性能。
b） 在2个较大的row source之间连接时会取得相对较好的效率，在一个row source较小时则能取得更好的效率。
c） 只能用于等值连接中
在一个多表关联的执行计划中, 必须包括3个要素:
a) 表/对象/数据集的读取顺序 (join order)
b) 数据的读取方式 (access path)
c) 表/数据的关联方式 (join method)
执行计划中, filter 按照某个条件过滤数据, ACCESS 按照某个条件/关系获取数据
12.访问数据的方式
首先介绍访问数据的几种方法: 全表扫描, 通过rowid来访问(最快), 索引扫描, 下面主要介绍一下索引扫描
我们先通过index查找到数据对应的rowid(对非唯一索引可能返回多个rowid值), 然后通过rowid直接从表中得到具体数据,
一个rowid唯一的表示一行数据, 该行对应的数据块是通过一次I/O得到的, 在此情况下该次I/O只会读取一个数据库块.
B*树结构 : 该书中的最低级的块(成为叶子结点)包含了每个索引键值和一个指向正在索引的行的 ROWID(换句话说,
通过索引键值, 找到ROWID, 在通过ROWID去找真正的行), 有趣的是, 索引的叶子结点实际上是一个双向链表,
这样, 要执行区间扫描就很容易, 直接找这个结点的前边或者后边就可以了.
索引的I/O情况, 索引树一般的高度是2-3层, 所以至多放生n+1次I/O, 其中n为读取索引信息, 1为读取实际表的数据, 另外,
索引表很可能已经缓存在cache中,这样就又省了一些I/O.
索引扫描本身又分为: 索引唯一扫描(index unique scan), 索引范围扫描(index range scan),
索引全扫描(index full scan), 索引快速扫描(index fast full scan)
索引唯一扫描: 优化程序知道索引中索引列是唯一的, 从索引查找中至多返回一行. (比如存在约束UNIQUE, PRIMARY KEY)
索引范围扫描: 对于索引范围扫描, 优化器知道可能返回 0行, 1行或多行. 例如 select max(empno) from emp; 这个查询,
oracle 不是将全表扫描一遍然后查出最大的, 而是直接通过索引的后面开始读取最后(最高)的值.
(因为一般情况下索引是按照从低到高存储的).
使用一个索引存取多行数据, 在唯一索引上使用索引范围扫描的典型情况是在谓词(where限制条件)中使用了范围操作符
(<,>,>=,<=, between) 等, 在非唯一索引上, 例如 谓词=5 可能返回多行数据, 所以在非唯一索引上都使用了索引范围扫描.
a) 使用了range操作符(>, <, >=, <=, between)
b) 在组合索引上, 只使用了部分列进行查询, 导致查询出多行
c) 对非唯一索引列上进行的任何查询.
索引全扫描: 你想查询的数据, 全部都可以从索引中直接得到.
索引快速扫描: 类似索引全扫描, 但是它不对查询出的数据进行排序, 有时也会使用并行读入机制.
13.锁相关
问题:
有可能产生丢失更新, 解决的办法是, 只改变你确实想改变的列, 而不是将所有列都改变
锁的策略: 个人推荐乐观锁定, 并且是要求数据库所有的用户都使用乐观锁
可以通过增加一列timestamp来判断你更新的这行是否没有被别人更新过.
注意这里的条件, 包括了 timestamp, 所以如果在之前有人修改过这个table, 那么本地保存的timestamp数据
就是"旧数据", 而此时如果你保存的是旧数据的话, 那么更新将不能进行, 因为条件不满足, 可以通过将所有
的update语句封装在procedure中, 从而让procedure来判断每次更新, 这主要是为了防止某些程序员在更新时忘记
更新这个时间戳, 详情参考 blog->大师->06Locking and Latching
阻塞&死锁:
当你所要申请的资源被占用了, 那么你就被自锁了. 死锁: 互相申请的资源都被阻塞了, 就死锁了
死锁的处理办法, 一般是人为干预, "回滚"一个, 释放资源. 出现死锁的最可能原因是外键没加索引.
锁类型
TX锁,
事务发起第一个修改时会得到TX锁(事务锁), 而且会一直持有这个锁, 直至事务执行提交(commit)或回滚(rollback)
TX锁用作一种排队机制, 使其他回话可以等待这个事务执行.事务中修改或通过select for update 选择的每一行都会"指向"
该事务的一个相关TX锁, 听上去好像开销很大, 但实际并非如此, 要想知道为什么, 需要从概念上对锁"居住" 在哪里以及如何
管理有所认识, 在oracle中,闩为数据的一个属性, oracle并没有一个传统的锁管理器, 不会用锁管理器为系统中锁定的每一行
oracle锁定的过程:
1) 找到想锁定的那一行地址.
2) 到达那一行
3) 锁定这一行(如果这一行已经锁定, 则等待锁住它的事务结束, 除非使用了 nowait 选项)
这里体现了TX锁的排队机制
说明: 闩是数据的一个属性, 事务找到数据, 如果还没有被锁定, 则对其锁定, 在oracle对数据进行锁定时, 行指向事务ID的
一个副本, 事务ID存储在包含数据的块中, 释放锁时, 事务ID会保留下来, 这个事务ID是事务所独有的, 表示了撤销段号, 槽
和序列号, 事务ID是说明"这个事务ID 你"拥有这个数据(并非数据块上所有的数据都是你的, 只是你修改的那一行"归你"),
另一个session来到时, 它会看到锁ID, 由于锁ID表示一个事务, 所以可以很快的查看持有这个锁的事务是否还是活动的, 如果
锁不活动, 则准许会话访问这个数据. 如果锁还是活动的, 会话就会要求一旦释放锁就得到通知.
补充: 所有的闩latch是一种轻量级锁, 用来控制争用资源的, 有点类似令牌, 你想做什么时, 先得到令牌才能做, 做完以后,
归还令牌,别人才能做. 另外, 闩会自旋.
update操作过程:
假设 A 用户发出一条 update 语句: update employees set last_name = 'HanSijie' where employee_id = 100;
oracle 在对该SQL进行解析后, 找到 employee_id 为100的记录所在的数据块(假设为58号数据块), 并找到一个可用的undo 数据块,
将 last_name 列上被更新前的旧值放入该 undo 数据块, 然后在数据块头部分配一个 ITL 槽, 在该ITL槽里存放当前事务ID号, SCN号,
所使用的undo数据块的地址, 以及当前还未提交的标记等信息, 接下来, 在58号数据块中, 找到被更新的数据行, 在其头部设置一个锁定标记,
并在头部记录当前事务所使用的ITL槽的曹号. 做完这些工作后, 将控制权(也就是光标)返回给用户.
该锁定标记说明当前用户在被修改的数据行上已经添加了X锁.
如果这时, 另一个用户(假设为B)也对 employee_id 为 100的记录进行修改, 则其过程和上面描述的一样,
只不过B在对数据行的头部设置锁定标记时, 发现该数据行头部已经有一个锁定标记了, 说明记录已经被添加了X锁,
于是用户进程B必须等待, 等待该锁被释放.
对应存储结构中的 INITRANS 和 MAXTRANS
在创建table的storage语句时, 会有 INITRANS 和 MAXTRANS 两个参数, 这里对应上了, 就是说事务槽的个数, oracle10g后这个参数被废除,
但是, 最起码我们对大致的脉络有了一个清晰的认识.
以上都是说的 TX型锁, 下边说说其他类型的锁:
TM锁: 作用, 比如你修改了一个表, 就会得到这个表的TM锁, 这是时候如果有人想drop这个表, 需要先获得TM锁
DDL锁: DDL操作时, 会得到DDL锁, 目的是, 在你修改对象时
14. redo 与 undo 的合作
回滚时, undo 做的是逻辑上与redo相反的是, 比如, 原来是insert操作, 那么回滚时, 就是delete操作
比如一条insert语句, 那么, redo做的是让这个语句再次发生, 而undo做的是让这个insert语句消失
另外, redo 同样保护 undo, 就把undo想象成普通的表空间就可以了.
详细参考 blog->大师->09->redo和undo如何合作专题
15.autotrace 比较看重的几个点
recursive calls: 递归调用, 有可能是由硬解析造成的
所需要的数据库=dblocks gets(在data buffer中的读取) + cosistent gets(在undo buffer中读取)
phsical reads: I/O
redo size: 产生了多少重做日志
执行计划的读法: 对同一凹层, 先上后下执行, 对不同凹层, 先里后外执行.
可能没有机会使用到 autotrace, 但是肯定可以使用到 explain, 其实都差不多, 用这个也可以.
16. logswitch 触发的是 增量检查点, (只针对要覆盖的那个日志文件的内容来进行DBWn的操作), 从而可以推断,
在检查点队列上串起来的脏数据, 是根据数据库的改变顺序来的, 即日志文件的顺序.
17. 需要调优的SQL
运行时间长的SQL
逻辑读高的SQL
物理读高的SQL