oracle tuning training 笔记

oracle Architecture
cube
partition
paralle
SQL tuning

instance= memory+process

database 狭义上，磁盘上的数据文件

database buffer cache, 数据文件中的数据块， 默认8KB，database buffer cache越大越好

避免全表扫描，

LRU 算法，最近用的最多的数据，

Redo Log Buffer -> redo log file, 目的是为了 数据库恢复用，

Shared Pool, SQL 语句，PLSQL 语句相关，捕获语句，把SQL语句转换成可执行代码，生成执行计划的地方，编译好的SQL语句，暂时缓存起来


SGA的最重要的三块内存区域：
data buffer cache
redo log buffer
shared pool



Large Pool 并行处理时需要，备份恢复数据库


Java Pool, 用java 开发的存储过程

Stream Pool Stream复制用的


SQL调优的目的，其中之一是减少对PGA的消耗，


Server Process： dedicated, shared

通过中间件来连接数据库，中间件上的连接池，

Server Process的独有内存，即私有内存，PGA

PGA里面的数据会跟 SGA 内存区域有交互，如会拿PGA里面的SQL语句生成 hash 值，拿hash 值去 Shared Pool 里面匹配是否有现有的。。。如果存在，那么SQL就不需要hard parse

SQL语句的编译是需要CPU资源的，应避免每次都对SQL进行 hard parse


还有就是PGA数据块里面的 rows, 对结果集的 order by等操作都在PGA里面进行的


另外还有join,group by, order by 等这些在数据仓库里面比较多，会消耗比较多的PGA，需要比较多的PGA内存空间，如果PGA内存空间不够，就会利用 TEMP 表空间，这样就慢了


如果 order by 的栏位刚好是索引，索引本身就是排序好的，那么就不会有在 PGA里面的 order by 操作


SQL调优非常重要的一点是 减少排序，排序是最消耗PGA的，


直接路径加载，

insert 的提示，insert append, 通知系统直接路径写，不由DBWR 进程来写。 直接路径加载，整个表会被锁，其他session 无法操作




data buffer cache,

select 并行的问题：

oracle 11g 里面， 并行在默认的情况下，使用的是PGA内存空间（不会发生在 data buffer cache, 缺点是数据没法共享，），读数据块

自动化并行，in memory paralle, 可以用到 data buffer cache,

早期的并行，只能在PGA里面，现在在 oracle 11g 里面，可以去到 SGA里面，这样有利于 共享


log miner, 日志挖掘，逆向操作恢复数据，类似 flash recovery



Shared Pool 核心区域：
Library Cache, SQL 文本，编译好的执行计划
data dictionary cache, 跟SQL有关的   数据块在磁盘的哪个位置，数据块的编号列表，这些信息就是 数据库字典信息 可以查询下面视图 dba_segment, dba_extent,
result cache, SQL 执行的结果集缓存下来，复用， 行的形式缓存起来，注意与 数据块缓存区域的区别，一个是 block, 一个是 row
类似 select count 的结果，当然如果count变了，那么这个 cache 里面的结果会失效

可以通过 set autotrace on 查看执行计划来验证 result cache 的功能

当一致性读 为0时， 代表物理和内存读都没有

一致性读=物理读+内存读

看 statistics: 信息


访问海量数据，但是得到的结果集很小，


还有一种场景是表分区时，一些固定的分区如果数据不会再变，那么对这些分区的select count则可以直接从 result cache里面得到结果


control structure, oracle的内存锁，不允许多个人同一时刻操作同一个内存区域


PGA里面最大的区，UGA


v$_session,
cursor status, SQL Area, contains: Sort area, hash join area, bitmap merge area, bitmap create area.


位图索引，0/1
and, or, not 等 distinct 值比较少的情况

位图合并


PGA里面的不同区域大小，基本上都是 数据库自动调整，


当遇到 小PGA，加上烂排序时性能会非常差，可以通过 create index 来解决 索引是排好序的， 但需要注意索引不记空值，设计数据库时能 not null, 一定要 not null


partition tables and indexes,

每个分区就是一个 segment, 它具有存储属性，可以单独的为每个分区来设置

single-level partition,

partition by Range, hash, list, interval(对于range 的替代)

列表分区，hash分区


my question: segment 到底是什么？


注意 空值是放在最大值的那个分区里面


分区表 value less than, 只支持 to_date 函数

partition 的 truncate, merge, split, move(move between tablespace, also 整理碎片，记得update index), exchange(ILM, information life management 段交换，没有真正的数据移动，只是数据字典的变化)

oracle partition exchange 类似 DB2 partition roll-in roll-out



Rolling Window

混合型分区：
父分区
子分区

subpartition

partition pruning 分区裁剪 避免全表扫描，根据 where 条件来判断，用到哪个分区

Equipartition

master-detail userful 尽量

分区对分区的并发 join


full partition join,
partial partition join

interval partition, 间隔分区 only in oracle 11g 它是range 分区的一种特例，可以配置自动的创建分区


system partitioning

partition by system 插入数据时必须指定 partition 名字


子分区模板的使用


partitino key: literal, 不能是表达式，唯一支持的函数就是 to_date


enable movement, 当 update 语句时，可能会发生这种情况


虚拟列， partitioning based on virtual columns


reference partition,


partition by reference 外键名字    外键的栏位必须是 not null



Partition Index,

Local, Global

二者的区别在于索引的分区方式 是否跟表一样

索引分区，可以基于某一列


索引分区的方式跟表的分区方式一样则是 Local index


Local的性能绝大部分比Global的好

可以指定Local index 存放在哪个表空间里面

Local Prefix Index 这样的性能最好，前缀型索引，顺序问题 性能高50%以上


有以下视图可以查询到相关信息：
DBA_TABLES
DBA_PART_TABLES 分区表信息
DBA_TAB_*PARTITIONS
DBA_*PART_KEY_COLUMNS


DBA_SEGMENTS
DBA_EXTENTS
DBA_OBJECTS





Parallel,

对于并行度的设置，尽量使用oracle 的自动调整设置，另外并行不能滥用

使用并行时，QC Server process（即协调进程）自己再派生几个 server process, 如果太多的用户都开并行，可能会导致系统瞬间会有很多server process


Parallel, 利用多CPU的好处

并行，仍然需要考虑IO瓶颈的问题，

原始的server process 会变成 QC, execution coordinator, 协调进程

master, remaster,



Pnnn 进程

并行度，Degree Of Paralle DOP

V$PX_PROCESS

内存里面动态分区，


set autotrace traceonly Y

PX parallel execute


show parameter CPU

show parameter parallel


alter session enable parallel 设置session 级别的并行

hint 作用能力最大，优先级最大


表DDL级别设置是否启用并行
alter table ... parallel n

alter table ... noparallel



alter session set parallel_degree_policy auto

会话级别全自动并行



group by rollup 小记，性能更快

group by cube, 排列组合的 group by   一般后面跟两个字段

grouping 函数，

grouping set

model 子句，电子表格能干的事情，它都能干

分析型SQL

LAG/LEAD 同比 环比

分析函数

RANK() OVER()

top-N, bottom-N

window 操作，rows between 3 preceding

pivot 旋转处理


因为统计信息不是很准，或者缺索引，或者 索引失效 会导致视图的性能较差，建议减少视图的使用，


子查询，计数器机制

批量join

hash join 批量运行

把子查询从where 条件中移出到 from 语句后面


使用索引时，遇到隐式转换时性能不太好

union 排序 去重
union all  不排序


AWR快照，

ASH：活动的会话
AWR：SQL, 前台，后台
ADDM：oracle 全自动分析当前数据库是否有什么问题


EM 里面 run ASH report  偏实时的工具

EM 里面的 ADDM finding, OEM找到的数据库问题，问题的解决方案等

Advisor Centeral
ADDM 可以手动触发，生成snapshot即可

SQL调优工具
tuning advisor
tuning set

SQL Access Advisor
SQL 访问，调结构， 如是否使用partition ，index, MV之类的

SQL Tuning Advisor
调SQL语句本身，SQL重写


SQL调整器
SQL Tuning Set


完全分析模式，真正去run SQL本身，然后来分析这条SQL，生产环境需要慎用


SQL Monitoring 实时监控SQL，oracle 11g 新功能
v$sql_monitor
去关联 v$_sql_plan 看该SQL的执行计划

索引策略，B-tree Bitmap

DB time=CPU time + Wait time


高 CPU time, 调SQL本身，
高 IO wait time，调 instance, PGA 等


data modeling:
OLTP 三范式
DW 逆三范式


星型结构，多维分析

show parameter star

star_transformation_enabled


hash join 全表扫描，

维表上有过滤条件，可以先对事实表进行数据过滤，然后再去跟维表进行join


雪花型，即星型结构中维表太大（维表还有自己的维表）的情况，


slowly changing dimensions  SCD问题，分析老的，分析新的事实表数据

代理键，使用代理键做主键，不要用自然键做主键，

拉链表，现在比较少用，


数据仓库系统，海量数据，一般建议分 partition


view 可能造成 suboptimal execution plans, 次优的执行计划，原因是 oracle 优化器对SQL query 和 View 进行的 query plan 评估工作方式不一样


dba_hist_sqltext

动态SQL
execute immediate ....


CBO cost based optimizer

RBO oracle 9i 有，后面就没有了， Rule Based Optimizer


优化器统计，表(对象上)的 statistics,


SQL transform:
例如view merging


selectivity 选择度

histograms 直方图统计


选择度 10% 以下，一般偏向选择索引，否则就全表扫描

DBA_TABLES
DBA_TABLES_STATISTICS


cardinality  基数 简写CARD

生成执行计划评估值



1 cost unit = 1 SRds

Single block random Read, 单块随机读

索引---单块读

表扫描 --- 多块读

show parameter
db_file_multiblock_read_count        默认128，128×8k


查看oracle执行计划时，遇到节点，从上往下读，否则 从下往上读


三种Join方式

Hash Join
Merge Join
Nest Loop


索引碎片太多，需要考虑重建索引，一般生产环境建议定期重建索引
optimizer_index_cost_adj  调整索引的权重值


执行计划

PLAN TABLE 预估执行计划

真实的 执行计划
v$_sql_plan

v$_sql_plan_monitor    -- 这两个是内存里面的，没有历史的数据


DBA_HIST_SQL_PLAN
AWR 里面的，包括历史的SQL 执行计划


set autotrace on/off  获得到的执行计划，看到的执行计划还是假的，并非一定等于真实的执行计划

set autotrace traceonly


recursive calls
递归调用，

缓存到内存


db block gets
数据库修改量


一致性读=物理读+内存读


SQL*Net roundtrips to/from client 往返次数， 默认15行，可以手动设置，


V$SQL 当前内存里面所有缓存的SQL
V$SQL_WORKAREA

one pass
optimal        直接在PGA里面
multipass  需要考虑是否增大PGA的内存


elapsed time=db time



当前的，历史的，预估的 执行计划都可以看到

谁是节点
Join 是节点，filter 看执行计划的每一行的 缩进是否对齐，对齐的话上面的就是节点


驱动表 的数据需要过滤的越多越好，Join时先访问的表就是驱动表



行源操作，row source operations


全表扫描 = 多块读 ，默认128 blocks 从末尾顺序读到HWM


filter, where 条件


对索引的访问 最大的问题是 单块读，不过某些情况下 索引也会有多块读操作


在使用并行时，一般偏向于 全表扫描，所以有时候 并行并不一定会快


样本表扫描，随机读 N blocks, 一般用在测试方面

TABLE ACCESS SAMPLE


索引，本质上的是 树型结构 --- 树高

索引的叶节点，上一级节点里面放 下面的页节点的最大值 或者 最小值


索引 需要定期重建，一般树高达到6层，就得重建索引


dba_indexes 视图里面可以查到 BLEVEL - 这个就是 B-tree 的树高

索引不适合建在 长度太长的 字段上，如 varchar(1000) 之类的，最好是 整型相关的字段上


索引不记空值，建索引的栏位最好得是 not null


如果字段是 unique 的，需要建成 unique index, 性能更好


Index full scan

select 后面的字段，都在 index 里面，复合索引


索引量比较大的时候，INDEX_FFS(... ...)   index fast full scan


index_ss(... ...)  index skip scan


空值处理，索引，JOIN，PARTITION，PARALLEL，


位图索引 在数据仓库里面性能是更好的

重复值特别多的 栏位，在数据仓库里面 特别建议使用 位图索引，但是致命弱点是 该栏位不适合经常做更新操作



组合索引，复合型索引，引导列


表的索引超过6个索引后，性能会下降明显


先访问小表，再访问大表


嵌套循环

在Join的栏位上建索引 的好处，



Sort Merge Join

两张表 先各自排序（排序发生在PGA里面，适合两张表都比较小，且有索引）， 属于嵌套循环的一种特例，  Join好后分页，前几行数据先出来，互联网行业有些情况适合这种



现实中，由于统计信息不更新 而导致大表使用了 sort merge join 因而执行非常慢的情况



Hash Join

超大表Join 更好的join方式

先扫描小表，两个表都全表扫描。。。

除数取余



使用哪种join方式，可以hint 来控制，如没有控制，则优化器很有可能会选择 hash join(当表比较大的情况下),