ITPub博客

首页 > 数据库 > Oracle > 调优的学习笔记

调优的学习笔记

Oracle 作者:abin1703 时间:2015-08-03 11:20:05 0 删除 编辑

写在前面的话

近期,笔者利用业余时间,观看了cuug讲师的oracle调优视频。笔者一边听讲,一边记录了视频里面讲师演示的所有实验以及讲师说过的所有话。

这篇笔记知识点颇多,内容丰富,里面的讲解也通俗易懂,运行大量的生活常识来诠释oracle的优化原理,非常适合那些对学习oracle优化一筹莫展和迷茫的朋友。因为这篇笔记内容一点都不枯燥,似乎是在讲我们身边发生的一个个小故事。

oracle优化确实是一件不容易掌握的技术,因为这需要从业者对oracle体系结构和SQLPL/SQL有着深刻的理解和认识,并需要有很丰富的工作经验,才能对oracle优化运用的如鱼得水。

也正因如此,如果能把oracle的优化学的好的话,薪水是很可观的;优化是oracle面试必问的问题,有时也能决定公司是否录用你的一个占用比例比较大的评判标准。

笔者用了近四个月的时间,整理并修正了此笔记的内容,可以说,此笔记的完成,也饱含了笔者的大量心血。

希望大家多多交流,把每个人的知识分享他人,正所谓“赠人玫瑰,手有余香”,让我们一起来感受获得知识的快乐吧。

                                                                                                                             --by czx

                                                                                                                    2015于北京

 

 

 


 

目录

写在前面的话... 1

cuug调优笔记... 8

设置share pool 8

library cache sql语句的解析... 14

查看硬解析... 14

创建三个存储过程对比动态sql和绑定变量的sql以及普通的变量... 15

动态pl/sql改成绑定变量的方式... 19

statspack 发现硬解析... 21

pga. 25

调整参数... 27

statspack调整Pga. 30

父子游标... 31

相关的cursor参数... 35

dictionary cache 调优目标... 39

调整参数... 39

statspack 调整dictionary cache 的尺寸... 40

library cache调优目标... 41

调整参数... 42

statspack调整share pool的尺寸... 42

避免共享语句无效... 45

我们的目标是没有蛀牙... 47

减少硬解析... 47

减少语句老化... 49

减少共享池碎片、缓存常用语句... 51

设置数据缓存区... 58

把表缓存到db buffer cachekeep... 61

有三种检查点:... 63

数据块大小的设置... 65

数据缓存区大小建议值... 65

busy buffer wait-等待事件... 68

找到热块的位置和类型... 71

free buffer-等待时间... 72

数据库缓冲区命中率的计算... 72

计算方法一:... 73

计算方法二... 73

数据缓冲区的调优技巧... 74

热块举例实验... 78

使用ASSM解决并发insert 冲突... 84

把表存放在段空间自动管理的表空间中实验... 84

我们的目标是没有蛀牙... 88

实例恢复和介质恢复的本质区别:就看文件换没换... 90

redo log buffer的调整... 91

查看日志的产生量... 96

监控redo log buffer—几个等待事件... 98

log buffer space. 98

log file sync. 99

log file parallel write. 101

log file switch. 101

log file single write. 102

日志缓冲区大下的计算... 102

减少redo日志量... 103

怎么样提高expdpimpdp效率... 106

直接加载和SQL*LORDER. 106

用临时表优化的一个例子... 107

java pool 107

larger pool 110

stream pool 111

dbwr写的方式... 111

我们的目标是没有蛀牙优化总结... 114

数据库配置与IO问题... 116

I/O调整的原则... 117

减少物理i/o. 118

b-tree索引... 119

位图索引... 121

一个位图索引和b-tree索引比较的实验:... 121

函数索引... 124

反向索引... 128

hash索引... 129

索引也是可以保存在keep pool 130

复合索引... 131

rebuild online. 131

raid的选择... 133

raid 5. 134

raid 01raid 10的比较... 136

数据文件条带化(分布I/O... 136

操作系统层... 137

手工条带化... 137

分区表... 140

各文件的管理方式... 140

表空间调优... 143

表空间本地管理和建立索引表空间... 144

区管理和段管理的区别... 145

数据块的管理方式... 146

手工管理... 147

自动管理... 147

assm的局限性... 149

调整全表扫描... 149

I/O问题监控... 151

statspackadvisory. 153

db file scattered read. 154

direct path write. 155

log file switch. 156

检查点的调优... 156

联机日志组合归档日志的调整... 159

我们的目标是没有蛀牙... 160

检测锁冲突... 162

中层领导CKPT. 162

pmonsmon. 164

检测锁冲突... 168

锁类型... 168

排他锁占座(该锁只有一份)... 169

共享锁:... 170

dml锁(第一种锁)... 171

ddl锁(第二种锁)... 176

查看锁的状态... 176

锁冲突的原因... 179

死锁... 179

未提交... 182

杀操作系统进程... 183

dump. 185

我们的目标是没有蛀牙... 187

调整共享服务器... 187

共享服务器... 188

专有连接和共享连接使用场景... 193

shared server(共享连接)的配置... 193

服务端... 193

客户端... 197

监控共享server. 198

实验... 200

杀进程... 204

优化排序操作... 204

一个操作可能导致使用多个排序区... 210

尽量避免排序... 213

创建索引... 213

distinct. 214

unionunion all 214

表连接... 217

analyze. 217

监控排序... 218

临时表空间... 218

设置虚拟机交互区... 225

设置tmpfs文件... 225

我们的目标是没有蛀牙... 228

调整回滚段... 229

回滚段的功能... 230

开启闪回... 232

undoredo的区别... 232

闪回区的作用... 234

块的真相... 238

回滚段的状态... 239

手工管理和自动管理的优缺点... 241

回滚段的自动扩展... 242

手工管理回滚段的实验... 244

回滚段调优工具... 253

动态性能视图... 253

statspack busy waits. 256

如何产生较少的undo数据... 260

我们的目标是没有蛀牙... 271

手工建库... 272

应用调整... 275

选择合适的存储结构... 276

分区表... 276

创建分区表... 278

创建索引... 284

一般索引:... 286

分区索引... 286

truncate partiton. 289

小知识:分区索引使用场景... 289

索引组织表(IOT表)... 297

创建IOT... 301

Mapping表的视图... 305

簇表... 305

Index cluster. 310

hash... 315

rowid. 328

复合索引和压缩索引... 331

物化视图... 331

单表物化视图... 335

两个表的物化视图... 340

创建跨库并可更新的物化视图... 344

几种扩展... 347

oltpolap的对比... 348

我们的目标是没有蛀牙... 348

有效使用数据块... 353

监控段的使用情况... 353

扩展数据文件的脚本... 359

大区的好处... 361

监控区的使用情况... 364

高水位线... 367

moveshrink的区别... 368

减少数据块的访问数量... 372

大小块的对比... 372

使用大的数据块... 373

记录存放的更紧密... 374

减少行迁移... 375

检测行迁移和行链接... 386

消除行迁移... 386

索引的重建... 390

索引的监控... 390

对索引碎片的整理... 392

我们的目标是没有蛀牙... 393

体系结构... 394

PGA. 394

数据库启动三阶段... 395

a、数据库的scnv$database.checkpoint_change#: 397

b、控制文件scn(v$datafile.checkpoint_change#) 398

c、数据文件scnv$datafile_header.checkpoint_changer#... 398

SQL应用调整... 399

优化器模式optimizer. 399

稳定执行计划... 402

解锁用户、赋予权限... 403

创建outline,并固定执行计划... 404

sql分析工具... 407

statspackadvisory. 407

如何读懂执行计划... 408

rebuild rebuild online的区别... 410

表连接和索引的优化... 411

nest loop. 411

hash join. 415

sort merge join. 416

没有索引表连接的实验... 419

有索引表连接的实验... 432

全表扫描... 444

通过rowid 的表存取(table acess by rowid或者rowid lookup... 451

索引扫描... 455

搜集统计信息... 460

数据库间的拷贝统计信息... 460

柱状图(直方图)... 466

列的选择性和聚簇因子... 474

hint. 483

性能调优总览... 487

调优的三大问题... 489

性能和安全的权衡... 504

诊断和调优工具... 509

告警日志(相当于听诊器)... 511

用户进程trace文件... 512

跟踪当前session. 513

跟踪其他session. 516

搜集统计信息... 518

动态信息视图(相当于听诊器)... 520

系统统计信息(v$sysstat... 528

查看sga里面的统计信息(v$sgastatv$sgainfo... 529

查看等待事件的名称(v$event_name):... 530

查看等待事件的值(system_event) 530

会话的统计信息(v$session... 531

explan(相当于x光)... 533

utlbstat.sqlutlestat.sql 536

v$sql或者v$sql_plan(相当于医院的x光)... 536

配置auto trace. 538

statspack 安装产生报告... 539

生成awr addm ash报告... 572

手工生成快照步骤... 578

oem生成awr addm ash报告的步骤... 585

我们的目标是没有蛀牙... 586

诊断闩锁冲突... 586

latch free. 588

buffer busy wait. 588

闩锁统计信息... 589

cache buffer chain. 590

cache buffer lru chain. 590

library cache latch and share pool 591

library cache pin. 591

redo copy. 592

redo allocation. 592

row cache objects. 592

enqueue. 593

附录... 594

根据操作系统进程号查看对应的SQL语句内容... 594

查看Oracle性能差的SQL. 595

1.查看总消耗时间最多的前10SQL语句... 595

2.查看CPU消耗时间最多的前10SQL语句... 596

3.查看消耗磁盘读取最多的前10SQL语句... 596

4、查看sql语句在等什么... 596

查看数据库top 5等待事件... 597

数据库hang住了怎么办... 605

1 通过v$lock检查数据库是否有锁表的问题:... 605

2、通过hanganalyze来检查数据库hang住的原因... 607

各种日志... 608

oracle监听日志存放位置... 608

执行计划的查看经典版... 608

执行计划的查看经典版... 608

执行计划查看的规则:... 610

执行计划的案例分析... 610

查看执行计划的步骤:... 612

综上图解:... 615

综上所述图解:... 615

hash value. 616

 

 

 

 

 

 

 

 

 

 

 

 

cuug调优笔记

 

 

设置share pool

 

 

 

我们可以把实例形象的理解成为一个快递公司。

进程分为用户进程、服务进程、后台进程。

用户进程:就是客户端的进程,和实例没有关系,比如我们用sqlplus连接数据库,那么用户进程就是sqlplus的进程;如果我们用pl/sql连接,那么用户进程就是pl/sql进程。

用户进程就相当于一个公司的客服一样。

 

服务进程:可以理解为送快递的师傅。其作用有四:

1、  服务进程负责通信。

2、  服务进程调用cpu资源让优化器(优化器是数据库的大脑)产生执行计划

 

执行计划是由数据库的大脑(优化器)产生的

解析的本质就是匹配(匹配就是查找)。

 

软软解析也叫不解析:就是把执行计划的位置信息(不是执行计划的本身信息)记录在uga里面。不进行执行计划的解析。有一个不太恰当的比喻,有一个人想从cuug去天安门,那么这个人可能需要进行硬解析(也就是自己亲自选择路线)或者软解析(直接用别人去过天安门的路线)后选择路线去天安门照相。然而我知道他要去天安门照相,我就和那个人说,你不用去天安门了,我这有天安门的照片你先用着,这就是软软解析(又名不解析)。

 

 

 

查看share pool的大小:

 

share pool相当于公告栏。

libarary cache里面存放的是sql语句、pl/sql和执行计划。

data dictionary cache缓存的是数据库的对象的定义,用于语法语义的校验。

 

 

 

 

 

library cache 怎么去你们家;

data dictionary cache校验你们家的地址对不对。

 

share pool:

 

 

 

 

注:上面从第一步开始就是对语法语义和权限的校验。然后生成hash值,再将这个hash值到share pool里面去匹配。

 

 

 

 

library cache sql语句的解析

session_cached_cursors=0表示uga里面缓存0sql

查看硬解析

scott执行:

 

 

sys执行:

 

批注:

SQL> select sql_text,parse_calls,sql_id from v$sqlarea where sql_text like 'select * from emp where empno%';

 

 

v$sqlarea这个视图查到的每条sql语句都表示是硬解析,即上面的两条sql语句都是发生的硬解析。

set timing off将返回时间关闭,否则会影响查询结果。

 

创建三个存储过程对比动态sql和绑定变量的sql以及普通的变量

 

 

 

 

select sql_text,parse_calls,sql_id from v$sqlarea where sql_text like 'select ename from emp where empno%';

 

大家看到,动态pl/sql的方式执行后每次都是硬解析。如果这个语句执行成千上万次,数据库性能一定会给脱的很低。所以DBA一定要提醒开发不要写这样的语句。

 

 

 

p_2过程用的是绑定变量的方式:

 

 

执行完两次p_2后,通过v$sqlarea查询后只有一条记录,说明绑定变量的方法硬解析只有一次。剩下的都走软解析或软软解析。

绑定变量加个小眼睛(:)。

 

 

 

下面的存储过程p_3用的就是一般的pl/sql变量的写法:

我们在p_3里面加了个’is 2033’是为了能捕捉到这条sql语句,不加的话v$sqlarea捕捉不到

 

 

大家看到在未执行p_3之前,通过v$sqlarea查到一条记录,这条记录我们不用管。

 

 

 

 

大家看到执行完exec p_37788)和exec p_3(7900)后,再通过v$sqlarea只有一条’is 2033’的记录,说明只发生一次硬解析。也就是说普通变量的pl/sql就是用的绑定变量。

 

结论:

1、  动态pl/sql(即把sql语句拼起来)不好,这种方式每次都会发生硬解析。

2、  第二种用的是绑定变量方式,这样专门的绑定绑定变量的方式每次都只有一次硬解析;

3、  在生产中,我们用第三种普通变量的pl/sql就行,因为普通变量的pl/sql用的就是绑定变量,所以我们没有必要刻意的用第二种方式进行变量绑定(虽然绑定变量是好的)

 

 

 

动态pl/sql改成绑定变量的方式

 

问题:请把下面的动态pl/sql语句改写成绑定变量的形式。

 

 

declare 
v_sql varchar2(30);
begin
for i in 1..100000loop
v_sql:='inster into test values('||i||',"'||'zhangsan'||i||"')'
execute immediate v_sql;
if mod(i,1000)=0then
commit;
end if;
end loop;
commit;
end;
/ 

 

注:所谓动态pl/sql,即把sql语句拼起来

 

 

 

 

下面开始改写。

方法一:
declare 
v_sql varchar2(3000);
begin
for i in 1..10000 loop
execute immediate 'insert into test values(:a, :b)' using i, 'zhangsan' || i;
if mod(i,1000)=0 then
commit;
end if;
end loop;
commit;
end;
/


方法二:
declare 
v_sql varchar2(3000);
begin
for i in 1..10000 loop
v_sql:='insert into test values(:x,''zhangsan''||:x)';
execute immediate v_sql USING i,i;
if mod(i,1000)=0 then
commit;
end if;
end loop;
commit;
end;
/

 

statspack 发现硬解析

 

 

上图中的hard parses超过一百就不好了,通过这里就可以发现硬解析。另外,如果看到cpu time多,也说明硬解析太多。另外,出现latch free-latch也说明没有使用绑定变量,说明有很多的硬解析。此外,段空间使用手工管理,也容易出现硬解析多的情况。

软解析可以通过parses减去hard parses算出。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

关于oracle内存自动管理机制,在10g中是sga_target11gmemory_target(包含pgasga_target)。

 

调整内存参数(oltp系统),在10g中需要调整pgasga两个参数,但是在11g只需要调整memory_target一个就行了。

 

sgapga4:1sga+pga建议设置为os物理内存的0.75

 

oltp

 

olap

 

 

上图中的tmpfs是共享内存文件或者叫共享临时文件,这个文件是在内存中的,这个文件的效率很高。sga_target或者memory_target不能大于这个值,否则会报错,如果没报错是走运。

 

实验:

tmpfs这个值的大小是可以调整的,方法如下:

vi /etc/fstab

更改tmpfs大小为800m

 

重新挂载一下使其生效。

 

 

 

 

pga

pga可以比喻成oracle进程的招待所里的单间,单间里面存放进程的信息。每个后台进程和服务进程都有一个pga(单间)

pga总大小的查询:show parameter pga

 

 

 

UGAU代表用户:UGA里面存放的是用户session的信息、排序区、cursor的状态信息。

软软解析:如果一条语句执行三次以上,那么这条语句的地址信息就会存在UGA里面(注意存放的不是sql语句本身,而是把这个sql语句的执行计划的地址存放在旅店单间的uga里面,然后根据这个地址到share pool里面直接就能定位到执行计划,不需要在share pool里面耗费资源去找执行计划),这就是软软解析(也叫不解析)。软软解析只做了hash运算,别的啥也没干,到share pool里面就把执行计划找到了(注意软解析还有一个找执行计划的过程)。

 

 

数据库system 默认密码是manager

 

 

 

在专有模式下,UGAPGA里面;在共享模式下,UGA不在PGA里面,如果有大池,uga在大池里面,如果没有大池,UGAshare pool里面。

 

CGA一定在PGA里面。

 

 

 

调整参数

show parameter pga

 

 

select trunc(pga_target_for_estimate/1048576) pga_target,

pga_target_factor target_factor,

trunc(bytes_processed/1048576) bytes_pro,

estd_extra_bytes_rw estd_rw,

estd_pga_cache_hit_percentage hit_percent,

estd_overalloc_count over_count

from v$pga_target_advice;

 

 

 

select sql_text,sum(onepass_executions) onepass_cnt,sum(multipasses_executions) mpass_cnt

from v$sql s ,v$sql_workarea wa

where s.address=wa.address group by sql_text having sum(onepass_executions+multipasses_executions)>0;

 

 

过载的含义就是oracle系统担心pga空间不够用,就给pga分配内存,但分配给Pga的内存就不能供其他进程是用了(也算是内存泄露)。为了消除过载,可以给pga设置一个合适的值,让oracle不用担心pga空间不够用。

statspack调整Pga

 

上图的estd extra w/a read/written to disk就是过载的意思。为0 时即消除过载。

 

 

父子游标

有时候两条sql语句完全一样(大小写也一样),但是执行计划也可能会不一样。

父游标是用来管理子游标,它没有执行计划。

子游标里有执行计划、绑定变量、对象和权限、优化器设置等。

子游标会随时被踢出内存(library cache),但是父游标不会踢出内存。

 

如果子游标被踢出内存,可以根据父游标重构子游标。

父游标视图v$sqlarea里的version_count表示一个父游标里面有几个子游标,即一个同样的sql语句有几个版本(版本表示的是可能是不同用户下的表,只不过是表名相同,查询的sql语句也一样的情况下)。

 

上面访问的是两个用户下的emp表,所以这两个emp表不是同一张表。因此即使sql语句一样,也不能共用一个执行计划。

 

上面通过子游标视图v$sql查询有两条记录,说明文本相同的sql语句执行计划是不相同的,有两个执行计划(子游标里面才有执行计划)。

 

 

父游标:

SQL> select sql_text,sql_id,hash_value,executions exec,loads,version_count,invalidations invalid from v$sqlarea where sql_text like '&text%';

Enter value for text: select * from emp where empno=7788

SQL_TEXT       SQL_ID        HASH_VALUE       EXEC      LOADS VERSION_COUNT    INVALID

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

select * from  2cv6qqj01b9wu 1075160986          5          3             2          0

emp where empn

o=7788

VERSION_COUNT=2表示父游标下面有两个子游标

子游标:

                                                                                                                                                                      SQL> select sql_id,child_number,sql_text,optimizer_mode,plan_hash_value,child_address,executions,loads from v$sql where sql_id='2cv6qqj01b9wu';

 

SQL_ID        CHILD_NUMBER SQL_TEXT       OPTIMIZER_ PLAN_HASH_VALUE CHILD_AD EXECUTIONS      LOADS

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

2cv6qqj01b9wu            0 select * from  ALL_ROWS        2949544139 30984C98          3          1

                           emp where empn

                           o=7788

 

2cv6qqj01b9wu            1 select * from  ALL_ROWS        3956160932 308D7460          2          2

                           emp where empn

                           o=7788

 

 

下面演示子游标被踢出内存:

执行analyze table scott.emp compute statistics 就会将子游标踢出内存(即踢出library cache

然后再在scotttom用户下执行select * from emp where empno=7788后再查看v$sqlv$sqlarea。会看到v$sql.invalidaton的值比上面的数字少1,表示有一个子游标失效,即被踢出内存;v$sqlarea.reloads的值和上面的比加1,说明子游标被重构(即又发生了一次硬解析),也说明了有一个子游标被踢出library cache

上面这个实验也说明了,当执行analyze table后,原来的执行计划就失效了,也就是说分析之后的语句要走一次硬解析。

 

 

 

使用forcesimilar是个歪着,因为使用这两个参数相当于把数据库灌醉,这时候数据库看什么语句都挺象,就都走同一个执行计划(数据库在exact时是清醒状态)。

 

 

 

相关的cursor参数

 

以前我们讲的硬解析,软解析,软软解析是这样的:

         我们执行的sql语句,首先要到library cachedata dictionary cache里面去找执行计划,如果里面没有,就重新解析一下(硬解析);如果有执行计划,就在share pool里面花费大量时间去找这个执行计划(软解析);如果把执行计划的地址缓存到宾馆uga里面(因为这条语句经常用,这条sql语句执行3次以上,就会把这个sql语句执行计划在share pool里面的地址给记到uga里面),然后根据地址到share pool一下子就把执行计划找到了(软软解析和软解析相比,软软解析不用花大把的时间到share pool里面去找执行计划,而是到share pool直接就找到执行计划了;软解析的话,还需要到share pool里面花时间找执行计划)。

 

下面我们从cursor的角度重新理解硬解析和软解析以及软软解析。

 

 

其实硬解析是构造一个新的子游标的过程(子游标里面有执行计划)。

软解析表示打开已经存在的子游标。

如果一条sql语句重复执行三次以上,就会把这条sql语句的执行计划在share pool的地址缓存到uga里面。

 

注:子游标关闭过,表明这个子游标的sql语句执行过。

 

 

 

 

 

open_cursor:能打开游标的数量越多,那么走软软解析的几率也就会越多(因为如果一条sql语句重复执行3次以上,就会把执行计划的地址缓存到uga里面,变成软软解析)。

 

session_cached_cursors:这个参数表示uga里面缓存关闭过3次的子游标的数量(子游标关闭多少次,就表示sql语句执行过多少次)。session_cached_cursors这个值设置的越大,表示uga里面缓存的子游标(这些子游标都关闭过三次以上)就越多,那么走软软解析的几率就越高。

当然,如果open_cursorsession_cached_cursor设置的越大,那么就需要更多的内存,所以也不是越大越好。

 

 

 

SQL> select name,value from v$sysstat where name like '%cursor%';

 

SQL> select name,value from v$sysstat where name like '%parse%';

 

也就是说,增大session_cached_cursors的值,那么session cursor cache hitsparse count(total)的比值也增大。

 

 

 

上图也可以看到软解析

 

 

如果软解析高,软软解析低,这种情况也不好。正常应该是软解析和软软解析同步变化。

 

 

dictionary cache 调优目标

调整参数

data dictionary cache用来进行语法语义的校验。

library cache用来缓存执行计划。

 

 

对于常用对象:

SQL> select parameter,gets,getmisses,round((getmisses/gets*100),2) "Misses%" from v$rowcache where parameter like '%objects%';

 

对于整体:

SQL> select sum(gets),sum(getmisses),round((sum(getmisses)/sum(gets)*100),2) "Misses%" from v$rowcache;

 

 

我们一般不会单独调整data dictionary cachelibrary cache的大小,只调整share pool的大小就行了。

statspack 调整dictionary cache 的尺寸

 

 

 

上图中,我们主要看pct miss列的值,如果有大于2%的值,就说明有些东西没有缓存起来,如上图的dc_objects(常用对象)和dc_histgram_des就没有缓存进来。这时我们可以增加share pool的大小(注意,千万不要被工具所左右,有时报告前后说的是矛盾的,需要对整体做出判断再做调整)

 

 

 

library cache调优目标

调整library cache DBA可以增大share pool;另外一半责任都是开发的问题,我们应该给开发做个代码的规范。DBA不要轻易动人家的代码,因为动了是要负责的,但DBA可以把问题给开发反映。所以要有良好的沟通技巧,和开发的人员沟通。

调整参数

select pool,name,trunc(bytes/1048576,2) free_space from v$sgastat where pool='shared pool' and name like '%free memory%';

statspack调整share pool的尺寸

如果pct miss高于1,说明硬解析很多。

 

 

 

 

执行下面的sql

 

select shared_pool_size_for_estimate "SP",estd_lc_size "EL" ,

estd_lc_memory_objects "ELM",estd_lc_time_saved "ELT",

estd_lc_time_saved_factor "ELTS",estd_lc_memory_object_hits "ELMO"

from v$shared_pool_advice;

 

 

 

 

避免共享语句无效

gets是解析阶段(语法分析)pin是执行阶段。

 

select namespace,gethitratio,pinhitratio,reloads,invalidations from v$librarycache;

对表进行分析后,有些sql语句的执行计划会失效,需要重新就行硬解析。

我们的目标是没有蛀牙

analyze 表太频繁,会导致语句老化。

减少硬解析

 

 

 

similarforce应急时再用,一般不要用。

 

 

减少语句老化

 

select sum(pins) "Exucutions",sum(reloads) "Cache Miss",

trunc(sum(reloads)/sum(pins),3)*100||'%' percent from v$librarycache;

 

select namespace,gethitratio,pinhitratio,reloads,invalidations from v$librarycache;

 

 

下面开始做实验:

 

declare

 sql_text varchar2(100);

begin

  for i in 1..100000

   loop

     sql_text:='insert into test values('||i||','''||'zhangsan'||i|| ''')';

      execute immediate sql_text;

   if mod(i,1000)=0 then

      commit;

    end if;

    end loop;

    commit;

end;

/

上面语句表示向表插入10万行数据。这个匿名块没有使用绑定变量,是一个烂代码。这个代码执行完后,读者可以做个awr报告,然后转到此书的awr部分,来详细解读,在awr报告中抓出该语句。

get是解析的次数,pin是执行的次数。

 

 

上面这个实验表示硬解析太多,会使命中率降下来。

减少共享池碎片、缓存常用语句

 

上面这两张图可以解释碎片的产生。

对于区(extent)来讲,分为uniform(区每次增长的大小都是一样的)和自动增长(区的大小是以递增的方式增长的)两种方式扩展区的大小。uniform会较少碎片的产生,你知道为什么吗?

我们用反正法来证明。

假设区是自动增长的,并假设区的起始大小是两个数据块,然后陆续又分配了48个数据块,我们在这些新分配的数据块里面插入数据后又删除,就会造成在一些连续的数据块里,有的数据块有数据,有的数据块没数据。从而造成碎片。如果我们在新块里插入的数据少,可能这些块还能再继续插入数据。但是如果我想再插入很多的数据,假设需要连续的10个数据块,那么这些产生碎片的数据块的空间就不够用,于是还得分配新的更大的区来满足要求。而uniform方式分配的区大小一直都是固定的,减少了碎片的产生。

一句话,碎片产生的原因:我一个数据很大,我在空闲块里面找不到足够多的连续的块,那么这些块就使用不了,需要再分配更多新的连续块。那些使用不了的数据块就产生了碎片。

 

索引表空间要建成uniform形式的,因为索引怕碎片。

1、  为大对象分配所需要的保留空间

library cache里面缓存了sql,pl/sql、执行计划,以及一些大对象、包。

 

reserved是保留、预留的意思。

 

select free_space,request_misses,request_failures from v$shared_pool_reserved;                     

 

 

 

select (request_misses/(requests+0.0001))*100 "REQUEST MISSES RATIO",

(request_failures/(requests+0.0001))*100 "REQUEST FAILURES RATIO"

from v$shared_pool_reserved;

 

查看share pool的总大小:

 

查看share pool里面的保留池大小:show parameter share_pool_reserved_size

保留池的大小一般预留share pool总大小的10%~50%

2、将经常调用的大对象保留在共享池中

上图显示的包没有在share pool里面。

 

select name,sharable_mem,type from v$db_object_cache

where sharable_mem>50000

and type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE','TRIGGER')

and kept='NO';

 

 

 

 

3、  移除大的匿名块

 

4、  oracle共享服务连接设置大池

大池分配100m就行了。

大池只是用来倒腾东西的一个空间。

数据库就是一座城市,数据块里面的数据就是卖家。

备份恢复的时候转储的是文件。server process读数据,读完数据放在buffer cache里。buffer cache里面放的是比较少的东西。如果要搬运一座城市,往buffer cache里面放就不太好了,而是应该建立一个物流中心,用来来回倒腾大件东西。这个大池(large pool)就是物流中心。大池不用特别大,只要能倒腾开就行了。

 

 

 

 

 

 

 

 

 


 

 

 

设置数据缓存区

 

 

 

 

 

server process把数据放在数据缓冲区(data buffer cache),然后dbwr进程把脏块写入磁盘。

 

我们可以把实例形象的看成是一个快递公司,把各种进程看成是员工。

user process接收客户请求,比如user process可以接收客户的快递;

server process是负责客户端和服务端通信的,相当于快递员。server process到共享池找执行计划,然后server process再到数据块里面找数据,放到buffer cache

 

lru算法 db buffer cache空间快满时,会把db buffer cache里面最近最少使用的块放在磁盘上,如果下次再用的时在从磁盘取回来,以这样的方式来释放db buffer cache的空间,这就是lru算法。

 

drwr:把脏块写入到数据文件。

 

数据缓冲区(db buffer cache)分三个池:

1default池:数据读进来默认就放进default池,由lru算法负责管理空间;

2keep池:想让经常用的对象不被排除db buffer cache,那就把这样的对象放在keep 池里面。只要数据库不重启,数据就一直会在keep池里面;默认keep池空间是0,即没有空间。keep池满了,会自动把数据放到default池,所以不用担心keep池满。注意,keep池不遵守lru算法。

3recycle池:假设有一个很小的表,可能就用一次,用完就不用了,那么就把这样的表放在recycle池里面。recycle池也不遵循lru算法。

 

 

看到上面这三个池大小是0,表示的是自动管理。

用下面的语句查看数据缓冲区db buffer cache的总大小:为276M

把表缓存到db buffer cachekeep

select table_name,tablespace_name,blocks,buffer_pool from dba_tables where owner='SCOTT';

上图看到一般的表都是放在default池里面的。

 

默认是没给keep池分配空间的。当然,在keep池没有空间的情况下,我们可以直接把数据保存到keep池,这时数据库会自动给keep池分配空间。但是最好我们先给keep池分配一下空间:

 

 

上图,我们看到我们设置的keep 池大小为10m,但是查出来的为什么是12M:这是因为粒度的原因:sga_target小于1G,粒度就会4M 4M的方式增长,sga_target大于1G,就会以16M 16M的方式增长。

 

 

 

 

上图看到把表放在了keep池了,下次在读这个表的数据时,就不会从磁盘来读了,而是从这个keep池来读,所以keep池可以提高性能。如果keep池空间满了,会放在default

 

总结:

1、对于小表(不超过数据库缓存区大小10%的表),如果想长时间驻留在数据库缓冲区,就放在keep池。

2、但如果对一个小表,打算访问一次就不再访问它了,完全可以把这个表的数据放在recycle池。因为recycle池里面的数据只用一次就被覆盖了。

 

oracle里面有两种送快递的人,一种是和客户交互的快递员即server possess,另外一个是公司的有名员工dbwrdbwr会把脏块(发生变化的块)放到数据文件(相当于卖家)。

 

数据缓冲区里面分三个池,是由队列来管理哪些数据块脏了(即数据块发生了改变)。我们只了解两个队列就可以了。

1、一个队列是检查点队列(或叫脏队列),只要数据块(或者叫脏块)有变化,就会把这个脏块的信息放在检查点队列里面(不是把脏块放在检查点队列里面,而是把脏块的信息放在检查点队列里面),当发生触发dbwr写的条件时,就把这些脏块写入磁盘;

2、还有一个是lru队列(注意不是lru算法,lru队列和lru算法这两个概念不一样),lru队列是server process申请数据缓冲区(db buffer cache)空闲空间时用的。server process会通过lru队列来找db buffer cache的空闲空间,当server process扫面lru队列的40/%时,还是没有找到数据缓存区空闲的空间时,就会让dbwr赶紧写脏块,以释放db buffer cache的空间。

 

小知识:当日志切换时,dbwr不会将脏块写到磁盘中,这点要注意。也就是说,当日志切换时,dbwr进程是在那傻站着的,因为dbwr是个大力士。只有满足dbwr写条件时,才会触发dbwr进程把大量的脏块写盘。

 

DBWn:数据写进程。

         定期将脏数据同步到磁盘。有20个写进程  dbwn 09 a-l

         什么时候工作,共分9种情况:

                   1。检查点(后台事件)。

                   2。脏数据缓存区的大小达到阀值。当脏块达到数据缓冲区1/4时,会触发dbwr来写。

                   3。扫描整个buffer cache. 没有空闲空间就需要写脏数据,写完之后就可以释放。

                   4timeout。默认3s触发一次。后台的一个计数器。

                   5。集群环境中使用。RCping Request。集群环境中有多个实例连接数据库,后台发出ping请求使数据达到一致。

                   6truncate table drop table

                   7。修改表空间。

                   8。表空间的脱机离线操作。offline关闭表空间,关闭之前先存盘。

                   9。热备份命令。beging backup

         脏数据的写和提交没有关系,由DBWn控制。

 

小知识:log切日志时用的是增量检查点。

 

有三种检查点:

1、完全检查点:发生完全检查点时,会把全部脏块写到磁盘(dbwr)。

2、局部检查点(部分检查点):表空间只读热备份时会发生局部检测点,因为这个表空间要脱机了,所以要赶紧写脏块了。

3、增量检查点:其实是把脏块放到检查点队列(也叫脏队列)里面

 

 

小知识:

完全检查点在8i之后只有在下列两种情况下才会发生:
DBA
手工执行alter system checkpoint的命令;
数据库正常shutdown (immediate,transcational,normal)

 

下面做个完全查点的实验(会看到,发生完全检查点时,scn号全变):

 

 

查看高水位线:

 

select table_name,tablespace_name,blocks,empty_blocks from dba_tables where owner='SCOTT';

上图中的5表示是在高水位线下面的块,3是高水位线上面的块。

高水位下面的块是使用过的块,高水位线上面的块是没有使用过的块。

 

 

因为内存和磁盘速度差1000倍。所以我们要关注data buffer cache的命中率,合理设置db buffer cache的大小,以便提高命中率。

 

数据块大小的设置

 

 

上图代表数据块的大小。

表空间相当于一个住宅小区,一个段相当于一个楼,一个区相当于一层,一个块是一个房间,有的房间是16K的,有的房间是8K的等。但是不同大小的数据块中,数据是不可以互相放的,比如8k的数据不能放在16k的表空间数据块中。

 

 

数据缓存区大小建议值

 

默认是on的,当然on会耗费性能的。

 

SQL> select name,100*(1-(physical_reads/(db_block_gets+consistent_gets))) hit_ratio from v$buffer_pool_statistics;

 

 

keep池不用关注,因为我们手工放的表才进keep池。

我们要关注的default池的值,该池命中率低于90%就需要加大db buffer cache大小了或者增加dbwr进程个数。

 

 

SQL> select name,size_for_estimate s_estimate,size_factor s_factor,buffers_for_estimate b_estimate,estd_physical_read_factor physical_factor,estd_physical_reads e_physical from v$db_cache_advice where estd_physical_read_factor is not null;

 

 

 

上图圈住的部分,接近1就行了,也不一定非得是1

上图中等1时的尺寸,和通过select * from v$sgainfo; 得到的buffer_cache_size的大小是有点误差的,不过这个误差也并不大,可以忽略。

busy buffer wait-等待事件

上图中,如果发生段头等待,表示空闲列表少,都在等待获取空闲列表的时间上,所以增加空闲列表的个数。或者降低pct_used的值。

 

通过v$waitstat视图,看哪个地方在发生等待:段头?回滚段头?回滚数据块?普通数据块?

 

 

 

 

freelist 是用来管理高水位线下面的空块的。

asmm:可以记为美妹 ,就是targetasmm指的是sga(sga_target或者memory_target)的自动管理。

assm:段空间自动管理

 

 

 

scott emp 一个块可以存放170行数据。

 

 

 

 

 

 

 

 

 

 

 

 

找到热块的位置和类型

 

 

上面这条语句可以找到热块的位置和类型

 

关于buffer busy wait你也可以参照本书的:buffer busy wait

 

col class for a20

col segment_name for a15

col segment_type for a15

col partitioned_name for a15

select 'Segment Header' class,a.segment_type,a.segment_name,a.partition_name from dba_segments a,v$session_wait b
where a.header_file=b.p1 and a.header_block=b.p2 and b.event='buffer busy waits'
union
select 'Freelist Groups' class,a.segment_type,a.segment_name,a.partition_name from dba_segments a,v$session_wait b
where a.header_file=b.p1 and b.p2 between a.header_block+1 and (a.header_block+a.freelist_groups) and a.freelist_groups>1 and b.event='buffer busy waits'
union
select a.segment_type||' block' class,a.segment_type,a.segment_name,a.partition_name from dba_extents a,v$session_wait b
where a.file_id=b.p1 and b.p2 between a.block_id and a.block_id+a.blocks-1 and b.event='buffer busy waits' and not exists(select 1 from dba_segments where
header_file=b.p1 and header_block= b.p2);

free buffer-等待时间

 

 

 

数据库缓冲区命中率的计算

db buffer cache里面的数据就是逻辑读,读磁盘里面的数据就是物理读。

 

低于90%就需要增大db buffer cache的大小或者增加dbwr进程的个数(命中率低,可能是dbwr写脏块慢,导致的db buffer cache空间紧张。)

注意:dbwr的个数上限是20个,其个数不能超过核数。一个dbwr对应一个核。

 

计算方法一:

SQL> select name,100*(1-(physical_reads/(db_block_gets+consistent_gets))) hit_ratio from v$buffer_pool_statistics;

 

 

keep池不用关注,因为DBA执行alter table emp storage(buffer_pool keep)命令后才进keep池。我们要关注default的值,低于90%就需要加大db buffer cache大小。

 

 

 

 

计算方法二

SELECT 1 - (phy.value - lob.value - dir.value)/ ses.value  "CACHE HIT RATIO"
FROM   v$sysstat ses, v$sysstat lob,v$sysstat dir, v$sysstat phy
    WHERE  ses.name = 'session logical reads'
    AND    dir.name = 'physical reads direct'
    AND    lob.name = 'physical reads direct (lob)'
    AND    phy.name = 'physical reads';

 

上图中为什么会是1-(phy.value-lob.value-dir.value)呢,因为lob.valuedir.value操作系统和oracle各统计了一次

 

 

 

以上两种算法都行,有一点差距。

 

 

数据缓冲区的调优技巧

 

 

物理读(即磁盘读)比逻辑读(即块在缓冲区中的读)慢1000倍。

 

 

 

 

 

 

 

select name,1-(physical_reads/(db_block_gets+consistent_gets)) "HIT_RATIO"

from v$buffer_pool_statistics where db_block_gets+consistent_gets>0;

select name,value from v$sysstat where name='free buffer inspected';

free buffer inspected 的值如果变化不大到没什么,但是我们要关注free buffer inspected值是不是在不断的增大,如果该值越来越多,我们就需要增加db buffer cache的大小或者sga_taget的大小或者增加dbwr

 

select event,total_waits from v$system_event where event in ('free buffer waits','buffer busy waits');

 

total_waits 等待的次数。

 

inspect是检索的意思。

热块举例实验

 

上面我们创建了一个mssm手工管理的表空间

 

 

 

vi dml.sh

#!/bin/bash

while true

do

sqlplus cuug/cuug <

begin

         for i in 1..10000

         loop

                   insert into emp values (i,'cuug','train',2233,sysdate,2000,1000,20);

         end loop;

         rollback;

end;

/

quit;

EOF

done

 

 

 

vi test.sh

./dml.sh &

./dml.sh &

./dml.sh &

./dml.sh &

 

 

执行上图中的脚本,对一个表插入数据,然后再rollback,会反复修改freelist,从而引起段头的热块产生。

 

 

 

小知识:杀进程的命令:

 

 

ps –elf |grep ora_查看进程

 

通过下面这个语句可以找到热块产生在什么位置

col class for a20

col segment_name for a15

col segment_type for a15

col partitioned_name for a15

select 'Segment Header' class,a.segment_type,a.segment_name,a.partition_name from dba_segments a,v$session_wait b
where a.header_file=b.p1 and a.header_block=b.p2 and b.event='buffer busy waits'
union
select 'Freelist Groups' class,a.segment_type,a.segment_name,a.partition_name from dba_segments a,v$session_wait b
where a.header_file=b.p1 and b.p2 between a.header_block+1 and (a.header_block+a.freelist_groups) and a.freelist_groups>1 and b.event='buffer busy waits'
union
select a.segment_type||' block' class,a.segment_type,a.segment_name,a.partition_name from dba_extents a,v$session_wait b
where a.file_id=b.p1 and b.p2 between a.block_id and a.block_id+a.blocks-1 and b.event='buffer busy waits' and not exists(select 1 from dba_segments where
header_file=b.p1 and header_block= b.p2);

 


 

 

 

 

上图的sql是查看热块的位置和类型,这条sql语句会用就行了(自己记不住怎么写也就算了)。看到热块在emp表上。

上面的方法可以总体定位热快信息

用下面方法也能看热块,可以精确定位

SQL> select * from v$waitstat;用这个视图可以看到等待发生的位置

 

CLASS                   COUNT       TIME

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

data block               9963     192686

sort block                  0          0

save undo block             0          0

segment header            379        906

save undo header            0          0

free list                   0          0

extent map                  0          0

1st level bmb               0          0

2nd level bmb               0          0

3rd level bmb               0          0

bitmap block                0          0

 

CLASS                   COUNT       TIME

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

bitmap index block          0          0

file header block           0          0

unused                      0          0

system undo header          0          0

system undo block           0          0

undo header               309      24953

undo block                  1         15

 

 

select se.sid,se.p1 "file",se.p2 "block#",se.p3 "id"

from v$session_wait se,v$event_name ev

where se.event=ev.name

and ev.name like 'buffer busy waits';

 

上述看到在段头块的第九个块是热快。

 

 

使用ASSM解决并发insert 冲突

解题思路:

 

把表存放在段空间自动管理的表空间中实验

下面我们做个试验,就是把段为手工管理的new_tbs表空间里面的表和索引移动到段为自动管理的new_tbs表空间:

 

create tablespace new_tbs datafile '/data/new_tbs01.dbf' size 50m segment space management manual;

 

SQL> create table ha.new_fust tablespace new_tbs as select * from scott.emp;

 

Table created.

 

                                    *

 

 

 

SQL> create index i_fust_id on ha.new_fust(empno) tablespace new_tbs;

 

Index created.

 

select tablespace_name,contents,status,extent_management,segment_space_management from dba_tablespaces;

 

上图中看到表空间的new_tbs段管理方式手工管理(freelist),区管理方式是本地管理。

 

select owner,segment_name,segment_type from dba_segments where tablespace_name='NEW_TBS';

 

生产中rebuild索引时最好加online关键字。

 

迁移完后,看到表空间new_tbs里面已经没有了表和索引

datafiles,表示删除表空间里面的文件;

contents表示删除后不放在回收站里面,而是直接从回收站删除。

 

小知识:获取建库建表的sql语句。

SQL> select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;

 

 

 

然后我们再把user表空间的表和索引移回new_tbs表空间里面。

 

 

再次验证,发现已经将表和索引迁回到了以ASSM管理的表空间new_tbs里:

 


 

看到现在的表空间管理方式是自动管理了。

select index_name,index_type,table_name,tablespace_name from dba_indexes where table_name='NEW_FUST';

 

查看索引在哪个列上:

select index_name,table_name,column_name from dba_ind_columns where table_name='NEW_FUST';

 

我们的目标是没有蛀牙

总结一下:

 

 

数据库缓冲区第一个目标是命中率。然后注意等待事件。一个回滚段一个事务比较好。

 

索引出现热块,可以采用反向索引。

 

 

 

实例恢复和介质恢复的本质区别:就看文件换没换

 

redo log buffer的调整

 

Lgwr写条件:

 

 

 

redo log buffer大小几兆就行。

 

 

 

归档的坏处:占用空间,会带来性能的降低10%。不开归档只能恢复到上一次备份的时刻。

归档的好处:开归档可以恢复到最后一次提交。

 

 

 

 

lgwr写日志是的进度严重影响着数据库的性能。把日志文件放在io最快的盘上。

 

 

 

 

 

 

 

查看日志的产生量

 

 

 

select a.name,b.value from v$statname a,v$mystat b where

b.statistic#=a.statistic# and a.name like '%redo size%';

用这条语句查看生产的日志量,这个语句看的是当前session的。

监控redo log buffer—几个等待事件

log buffer space

 

select sid,event,seconds_in_wait,state from v$session_wait where event='log buffer space';

 

log buffer space是个等待事件。

 

select name,value from v$sysstat where name in ('redo buffer allocation retries','redo entries');

一般要求,重试次数是0,如果是非零,那么两者相比的比率小于1.

 

log file sync

 

select sid,event,total_waits wait_class from v$session_event where event

in ('log file sync','log file parallel write');

 

 

一般是100行或者1000行一提交,不要频繁提交,也不要总是不提交。

 

log buffer 空间几兆就够了。影响数据库性能主要是lgwr写入速度,可以用读写速度快的盘来提高lgwr的写入速度。

我们不推荐调整log buffer空间的大小。

生产中日志组一般是500M-800m。也有2T的,这种大的日志组切换时可能有问题。建议15分钟切换一次日志。

 

如果一行一提交,那么数据库每次都要等待log buffer写入redo log,这个等待就是Log file sync

 

log file parallel write

 

log file switch

 

 

log file single write

 

 

日志缓冲区大小的计算

select a.value as trancount from v$sysstat a,v$statname b

where a.statistic#=b.statistic# and b.name='user commits';

 

select (sysdate-startup_time)*24*60*60 seconds from v$instance;

 

select value as redoblocks from v$sysstat where name='redo blocks written';

 

减少redo日志量

注意:在导入导出时把表或者表空间设置为nologging虽然能提高导入导出的效率,但是是有风险的,所以不建议设置为nologing。因为你把表或者表空间设置为nologing时,万一数据库出现问题,这时的日志就没有了。所以我们完全可以从导入导出的参数上提高效率,而不是不记录日志。

 

 

可以设置表空间或者表为nologing

 

 

小提示:在生产中,我们一建索引就要加nologgingonline

nologging不是不记日志,而是少记日志。

使用nologging的情况:

1、对于索引来说,是不需要恢复的,因为索引坏了可以再重建,所以建索引可以设置为nologging

2、临时表空间也可以设置为nologing,因为他是放临时数据的。

 

 

怎么样提高expdpimpdp效率

 

 

 

直接加载和SQL*LORDER

 

sqlldr里的direct=true表示的是绕过数据库缓冲区,这样日志量就减少了。以前我们学过的直接导入是指的是从高水位线上插入数据。这两个直接加载概念还不一样。

如果我们把数据插入到高水位线下面的数据块,那么需要通过空闲列表或者位图来找空闲块,这样就慢,如果我们直接把数据插入到高水位线上面的块,那么就不用再耗费资源去找哪些是空闲块了,因为都是空闲块,直接把数据插入到数据块里面就行了,所以直接加载数据就快。

用临时表优化的一个例子

临时表是放在临时表空间中的,用户一提交或者一退出,临时表里的数据就完全消失了。临时表是一个内存表,插入临时表的数据都会放到内存中,所以效率很高。在写存储过程中,可以把中间结果放在临时表。

关于一个用临时表优化的例子:

java页面可以通过pl/sql的游标open cursor c_emp for select * from emp….来调用pl/sql的程序,但是如果这个pl/sql写的足够复杂,会出现java调用pl/sql程序非常慢的情况,这时可以用临时表来做优化。如下:

 

上图意思是把数据插入到临时表中,相当于插入的数据放在内存中了,然后java从临时表取数据。这样做后,javapl/sql的数据的效率非常高。这曾是一个真实的优化案例。

 

java pool

 oracle 8i是用c语言写的。9i就用java语言写的,少量的c,c++

 pl/sql有部分包是java的。

java pool 一般20m50m就可以了

 

 

上图中,一个说java pool0,这个0表示的是java pool是自动管理的。通过v$sgainfo可以看到java pool的大小是4M

设置java pool的大小:

 

查看java pool的剩余空间:

 

large pool

大池一般100m就行了。uga在大池里面,备份转储、异步Io,批处理是需要用大池。共享模式用大池,专用模式用pga连。

 

stream pool

 

dbwr写的方式

 

 

大池可以支持异步I/o

同步i/o:我们知道,server process往数据库缓冲区里面写数据,dbwr进程将数据库缓冲区里面的数据往数据文件里面写。同步i/o就是指server process往数据库缓冲区里面写数据的过程和dbwr进程将数据库缓冲区里面的数据往数据文件里面写的过程是不能同时发生的,只有一个过程完成了,才能进行另一个写的过程。这种效率低。

异步i/o:就是上面两个过程可以同时运行。这样效率高。

 

一般我们的数据库都是支持异步i/o的。

 

上述的async表示的是异步io,看到其值为true

 

 

 

如果不支持异步I/O,有两种方法可以实现:

1、  可以把dbwr_io_slaves 这个参数加大,即把从属进程增多些。

2、  也可以把db_writer_process个数调大,即增加dbwr进程个数(最多20个),但是注意,dbwr进程的个数不能超过cpu的核数(show parameter cpu查看到的核数);同时,dbwr的个数也不能超过_db_block_lru_latches参数的值(这个隐含参数的值可以自己改)。

 

注意:

 

 

我们的目标是没有蛀牙优化总结

 

 

 

 

数据库配置与IO问题

 

内存和外存差1000倍,原因是硬盘磁头移动的时间长导致的外存速度慢。

 

I/O调整的原则

 

减少i/o最主要的手段就是索引。

本地管理就是指用以区的方式管理空间;字典管理是指用数据字典的方式(uet$fet$)来管理空间。

 

上图中,et代表区的意思,u代表usedf带表free。修改数据字典是串行的,会造成等待,同时修改数据字典还会产生undo,基于这两种原因,用数据字典来管理表空间会耗费性能。

 

减少物理i/o

 

 

索引表空间要建成uniform的形式,因为uniform和自动方式相比,uniform会减少碎片的产生。索引是怕碎片产生的。

 

为什么要建索引表空间呢?

         使用索引,会产生两次i/o:第一次i/o是访问索引块的键值以及rowid,第二次i/o是通过rowid到磁盘找到真实数据。

 

         如果索引和表放在一个表空间或者一个磁盘上,这样i/o通道就会发生竞争了。

所以表和索引最好放在不同的表空间上或者磁盘上。

 

 

b-tree索引

 

 

走索引是减少i/o的最好方式。

b-tree索引:基数比较大(重复值比较少)时用b-tree索引;增删改(dml)多时也用b-tree索引;

位图索引:基数比较小(重复值比较多时)时用位图索引。位图索引非常怕update,因为位图索引块里面存放的是表rowid的范围,在做update的时候,会把这个索引块rowid范围内对应的表数据全部锁住,从而影响了性能。

 

 

 

 

位图索引

位图索引,其实就是一个矩阵的形式。

 

b-tree索引:基数比较大(重复值比较少)时用b-tree索引;增删改(dml)多时也用b-tree索引;

位图索引:基数比较小(重复值比较多时)时用位图索引;位图索引非常怕update,因为位图索引块里面存放的是表rowid的范围,在做update的时候,会把这个索引块rowid范围内的表数据全部锁住,从而影响了性能。

在生产库的oltp系统中,比如有性别一列,虽然这样的列重复值比较多,但也不要使用位图索引,因为做update会很耗费性能。所以,请记住一句话,在oltp系统一般不要位图索引,位图索引多用在数据仓库上。

 

 

一个位图索引和b-tree索引比较的实验:

 

 

 

 

declare

         v_num number(2);

begin

         for i in 1..20000 loop

                   v_num:=round(dbms_random.value(0,1),0);

         if v_num>0 then

                   insert into lxtb values (i,'M','male'||i);

         else

                   insert into lxtb values (i,'F','female'||i);

         end if;

         if mod(i,1000)=0 then

                   commit;

         end if;

         end loop;

         commit;

end;

/

 

 

 

 

 

 

分析索引:

 

select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks from dba_indexes where table_name='LXTB';

 

上图中的1代表索引是两层,即根和叶两层;有37个叶级块。

 

我们删除b-tree索引后,再建立一个位图索引:

可以看到位图索引只有1层,即根,只用了1个块(blocks)。说明了位图索引占空间很少。因为位图索引里面存的是矩阵01

 

select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks from dba_indexes where table_name='LXTB';

函数索引

 

 

看到走索引了。

 

 

但是下面这种情况就没走索引:


 

怎么让它走索引呢?

建立函数索引就走了:

 

 

看到建立函数索引后,再查询就走索引了。

但是要注意:b-tree索引的costcost值要相加)是3,全表扫面的代价是40,走函数索引的代价是35.

所以函数索引是次要选择,不是最好的选择。

 

一般来说,一个表上建立的索引尽量不要超过5个,一般不要超过10个。

 

对于函数索引,要把函数放在where =号的右边,如下:

 

执行set autot off后,虽然还是sys用户,但是权限会降低,这时候我们可以再重新登录一下sys用户。

 

第一个PL/SQL

declare

         v_ename varchar2(30);

begin

         v_ename:='scott';

         dbms_output.put_line(v_ename);

end;

/

 

 

第二个PL/SQL

declare

         v_ename varchar2(30);

begin

         v_ename:='scott';

         dbms_output.put_line(v_ename);

         v_ename:=upper('scott');

         dbms_output.put_line(v_ename);

end;

/

 

存储过程可以传参。如果你一开始不进行这种upper的转换,那么你再查询where语句中你就会写upper,用来转换字段值为大写。这样就会造成我们上面看到的创建函数索引后,cost成本高的结果。但是如果你把这个‘scott‘值传进来的时候就upper一下,那就没必要在查询的sql语句中的where子句里upper转换了。上面第二个pl/sql就是这么做的。

 

反向索引

反向索引最大的缺陷就是做范围查询时代价高,所以不适合范围查询。

所以做为范围查询时要用一般索引。

反向索引可以把键值打散,hash索引也可以把键值打散。

建立反向索引:

 

SQL> select index_name,index_type,table_name from dba_indexes where table_name='LXTB';

hash索引

建立hash 索引:

 

 

 

索引也是可以保存在keep pool

 

复合索引

关于联合索引你也可以调整到本书的复合索引和压缩索引

rebuild online

 

索引在存储时是块间有序而块内是无序的。

 

 

raid的选择

 

 

 

 

raid 5的速度慢于raid 0,但是比raid 1速度快。

 

raid 10 raid 01速度和安全都差不多,不过他两速度都慢于raid 0.

其实raid 10好于 raid 01  10 >01

有钱你就raid 10,没钱你就raid 5 记忆方法:10 > 5

raid 5的速度慢于raid 10raid 01:同时raid 5的安全性也不如raid 10raid 01

 

 

raid 3专有一块盘来存放校验信息的,而raid 5是把校验信息分别存放在不同的盘上。

raid 3专门用一块盘存放校验信息,会造成这块盘的i/o瓶颈;

raid 5从速度和安全性上来讲,都还是不错的。

 

 

上图是raid 5的示意图,a+ a-是互补的,只要有一块盘坏了,就可以通过另外两块盘拼出换的盘的信息。

 

raid 5

 

 

raid 01raid 10的比较

 

 

raid 10是先镜像,再条带;

raid 01是先条带,再镜像。

 

raid 10好于raid 01.

raid 01只能坏一块盘;raid 10可以坏两块盘;raid 5只能坏一块盘。

 

数据文件条带化(分布I/O

条带化:把一段数据分成多条,然后放在不同的磁盘,这样就可以加快写入速度。

 

操作系统层

手工条带化

操作系统层做raid进行条带是最好的。但是如果公司穷,买不起阵列,也可以通过手工的方式实现条带。

 

上面说的我可以预先分配空间,指的就是可以手工分配空间。

 

 

 

 

手工条带的实验:

select table_name,tablespace_name,blocks,empty_blocks from dba_tables

  where owner='SCOTT';

 

 

上图中的3表示有3个空块在高水位线上面。高水线上的块是从来都没有用过。

5表示高水位下面有5个块,这5个块存过数据了,在高水线下面。

即使把块里面的数据删了,高水位线还是在那个位置。

 

 

 

上面报错的原因是emp表在users表空间里面,但users表空间不能向system表空间要空间,而应该向users的数据文件要空间。

如下:

搜索标记:allocate

 

alter table scott.emp allocate extent(size 20m ‘/u01…’); 写在这是用来搜索的,读者可以忽略这个。

 

 

看到empty_blocks原来是3个块,现在是2563个块,现在有足够的空间供其插入数据了。

empty_blocks是高水位线上的块。

生产中,一个表空间里面是有很多的数据文件,假设我有很多不同的盘,不同的数据文件在不同的盘上,我们可以找一个闲的盘,用这个闲的盘来分配空间,这样就把一个表分散开来。让数据库I/O的时候,不要老I/O一个盘。

 

怎么回收空间呢?

用如下方法回收空间:deallocate

 

 

 

分区表

分区表的好处:

1、可以缩小全表扫描的代价。

2、分区表的每个分区可以放在不同的盘上,这样也可以分散i/o,但是这样比较死板,一般不用,我们知道就行。

 

具体内容可以参考本文档其他部分分区表的详细内容:分区表

 

各文件的管理方式

 

其实裸设备性能只提高10%,说提高40%的都是吹。

 

文件系统读数据块其实是两次i/o,一次是操作系统读一次,另一次i/ooracle自己读一下块,这样性能不是特别高的,不过管理起来方便。

裸设备是未格式化,操作系统不认识这块裸设备,但是Oracle认识,所以就只有oracle来读取这个裸设备,不经过操作系统了,所以快,但是管理起来不方便。

 

 

 

asm

 

前面我们讲的文件系统管理方便,但是i/o性能不好;裸设备i/o性能好,但是管理起来不方便。那有没有一种即好管理,i/o性能也高的方法呢。答案是有,这就是asmasm兼具有裸设备和文件系统的优点,他即具有很好管理,,i/o性能也很好。

 

 

asm也有个实例,用来管理asm磁盘。

asm可以实现镜像和条带,它和raid 1相比,asm可以实现动态的条带。比如多插入一块盘,asm会自动把数据同步到新盘。raid不会自动同步。

什么时候用asm呢?asm一般用于数据文件数量多(好几百上千)的情况。asm可以创建逻辑卷,dba管理逻辑卷就行了,dba就不用再直接管理数据文件了,而是让asm来管理数量巨多的数据文件。

asm是个软件,它用来管理数据文件raid需要硬件支持。

 

如果数据文件比较少,那就最好不要用asmasm用于数据文件较多的情况。

因为asmdba管理数据文件,dba就不好直接操作数据文件了。

 

 

raid 5写操作性能欠佳,适用于读操作更多的数据仓库系统。

asm是软件级别的,他实现也是基于raid的。

 

 

表空间调优

system是万能表空间,可以充当undo表空间,临时表空间,永久表空间。

oem就存放在辅助表空间。

 

 

执行上述语句,表示新建的用户对象默认放在user表空间。否则会放在system表空间。

表空间本地管理和建立索引表空间

区的位图管理方式是在数据文件头部;而段的位图管理方式是在段头。

 

 

 

 

区管理和段管理的区别

区的管理方式是表示即将分配但还没分配给你的空间;

段的管理方式,表示空间已经给你了,高水位下面的块已经存过数据。高水线下面的数据块会成为空块,但是高水位线还是在那不动。这样,高水位线下面的块有的可能有数据,有的没有数据,这样我们就需要一种手段来管理高水位线下面的空块。当有新的数据插入时,我们需要优先用高水位线下面的空块来存数据,这就是段的管理方式。段的方式管理的是块,管理的是已有的空间,它有freelist 和位图两种,其信息位置在段头;而区的管理方式的位图信息在数据文件头。

 

数据块的管理方式

 

手工管理

自动管理

 

freelist很难支持并发操作,如果有并发修改一个块,会造成busy buffer wait 等待事件。

而位图管理就不会造成buffer busy wait。为什么呢?看下图:

 

段的位图管理方式代表的是块,如果这个块是空闲的,它是0,我就锁定这个位置就行了,锁定以后,其他用户再来申请一个块,一看这个位置被锁定了,那它就锁定这个位置旁边的空闲块。再来一个用户再锁定旁边的位置,所以,这样的方式就可以支持好几个用户的并发了。

从而也就减少了buffer busy wait的事件。

 

assm的局限性

 

select tablespace_name,contents,status,extent_management,segment_space_management from dba_Tablespaces;

 

临时表空间只能用手工管理,不能用自动管理。为什么,我也不知道。

assm提高了dml的效率,但是对超高容量的DML性能很差

调整全表扫描

 

 

 

上面说的使用size更大的块表示可以把块设置成16k,32k的等,一般索引用大的块好,但是块大容易产生热块。所以说什么都是一把双刃剑!!!

 

调整db_file_multiblock_read_count这个参数后,就打破了索引和全表扫描代价的平衡。

 

db_file_multiblock_read_count代表一次i/o读的块数。如果把该值调大,那么每次读的数据块就多了,从而可以提高全表扫描的效率。但是,这样做的缺点是,oracle数据库认为全表扫描的代价低了,所以放弃某些索引而走全表扫描。所以,一般我们最好不要调整这个参数。

 

optimizer_index_cost_adjadj是调整的意思,这个参数是靠经验来调的。其值是全表扫描的代价和索引扫描代价之比。

当我们把ndb_file_multiblock_read_count的值)设置为10,表示即使索引的代价提高10倍,他还会走索引。

n=100代表的是1

 

I/O问题监控

col tablespace for a10;

col file_name for a10;

select d.tablespace_name tablespace,d.file_name,f.phyrds,f.phyblkrd,f.readtim,

f.phywrts,f.phyblkwrt,f.writetim from v$filestat f,dba_data_files d where f.file#=d.file_id order by tablespace_name,file_name;

 

主要是关注上述的phyrds读的次数和phywrts写的次数。上图中的u02盘比较闲。

 

另外:iostat也可以看出哪些磁盘空闲,哪些磁盘繁忙。如果下面%idle小于70%,说明这块盘还是比较繁忙的。如果%util值比较高,如百分之八九十了,那就说明这块盘比较忙,见下:

 

statspackadvisory

 

需要关注上面av rd(ms)列的值,如果比较大,说明其对应的表空间存在严重的i/o问题,可以通过手工条带化或者阵列等方式解决表空间i/o瓶颈。

 

db file scattered read

scatter是分散的意思

direct path write

 

log file switch

 

检查点的调优

 

 

fast_start_mttr_target表示实例奔溃后恢复所需要的时间。

 

 

fast_start_mttr_target=1时,表示估计实例恢复时间的下限是多少,看到最快15秒(我想让数据库1秒就进行实例恢复,但是数据库发现最快15秒才能恢复)。

如果想最快恢复实例,就需要产生很高的I/O频率了,这样也不好。所以fast_start_mttr_target设置一个中间值较好。

 

estimate_mttr表示当前系统根据内存中写脏块等信息,认为12秒可以实例恢复。

 

 

fast_start_mttr_target=3600,表示估计实例恢复时间的上限是多少,看到最多需要29秒实例恢复(我设置最慢3600秒恢复,但是数据库计算后发现最慢29秒就能恢复了)。

 

 

实例恢复先用redo log做前滚,前滚之后,发现有东西没有提交,再用undo做回滚。

回滚到一致时才开始打开数据库。

应用日志越少实例恢复的越快;

需要恢复的块数越少,实例恢复就越快。

 

 

联机日志组和归档日志的调整

 

安全和性能是矛盾的。

一般两个成员就行了,放在不同的磁盘上。

 

 

 

我们的目标是没有蛀牙

 

如果你的数据文件比较多,用asm比较好,反之,数据文件少,就没必要用asm

 

 

上面最大归档进程是2,有可能表示的是3个进程,分别是012,其中有一个是父进程的。

ps –elf |grep ora_    这样就直观的看到归档进程了

检测锁冲突

dbwr写的条件:

1、  发生全局检测点,部分检查点(表空间脱机、droptruncate,这几种情况会发生部分检查点)时,dbwr会写脏块;

2、  数据缓冲区有两个队列:

a、  脏队列  数据块脏了以后,脏块的信息就往脏队列里面写。脏队列达到1/4时,dbwr开始写脏块

b、  lru队列  server process寻找可用空间时,使用lru队列。当server process 读取lru队列的40%时还没有找到data buffer cache里的空闲空间时,dbwr就会写脏块

3、  超时

 

 

这个女领导就是checkpoint

 

中层领导CKPT

 

当检测点ckpt发生的时候,就会通知dbwr(最能干的员工)写脏块,dbwr同时更新数据文件头和控制文件头的scn号。此时lgwr被动写日志(lgwr工作条件是commitdbwr写脏块之前写日志)。

dbwr写脏块的进度记录在控制文件里面(每3秒一次)

 

 

日志切换时发生的是增量检查点;

数据文件脱机时发生的是部分检查点;

数据库关库的时候发生的是全局检测点。

 

pmonsmon

mon代表monitor

p代表process

smons代表system

所以说,pmon是监控进程的,smon是监控系统的。

 

 

smon是公司的保安和施工人员。

如果一个公司被砸了,负责公司的重建。

smon的功能,就记住一句话:拆别人家(回收临时段和不用的段),盖自己家(实例崩溃了进行恢复;也就是公司被砸了)

 

pmon

pmon应该不只是清洁工,还应是个医生。

pmon就像上图这个医生一样,拿个听诊器来监听人的情况。

进程挂掉后,由pmon来重启进程。

 

 

 

 

pmon理念就是救死扶伤(进程挂了,pmon来重启进程),开锁劝架(比如两个server process同时抢一个东西,就会吵架了,这时候就是死锁,由pmon来解锁;还有,进程挂了后,是由pmon来释放资源的)

 

server process就是送快的师傅,他把数据从硬盘读取放进缓冲区。

 

对比:

         smon释放的是那些临时段和不使用的段;pmon释放的是进程的资源。

 

 

检测锁冲突

锁类型

 

 

排他锁占座(该锁只有一份)

 

共享锁:

 

update操作,需要加两种锁,一个是表级的共享锁,一个是行级的排他锁:

在行上要加排他锁(该锁只有一份);

在表上加共享锁(该锁可以有多份),表示我要对这张表进行update,在我update的时候别人千万别把这张表drop了或者alter(修改表结构)。

 

 

 

dml锁(第一种锁)

网上把锁分的种类很多,其实我们只把锁分成ddl锁和dml锁两类就行。

 

上面的12是排他锁的锁位,每个锁位各锁一行。

 

 

 

 

行共享锁(RS),也叫悲观锁,其语句就是for update,其目的有二个:    

         目的1:我希望在我查完数据以后,在进行下一个动作之前,别人不要动这个数据;

         目的2:我先查一下这条数据,查完以后我要对他做update操作,但是我怕我查完以后,别人比我手快,别人先update了,所以我加个for update,这样别人就不能做update操作了,直等到我释放该锁为止。

 

实验:

   for update表示我查询时就加锁了,只要我不释放这个锁,别人就做不了操作。

会话1

 

再开个新的会话,看到新的会话执行delete操作是会挂起。

会话2

会话1commit

会话2:这时候,会话2加上了排他锁

 

 

然后再在会话1执行for update语句,看到会话1也挂起等待了:


 

在会话1可以加nowait表示不等待:

 

小知识:还有一种锁,叫乐观锁,就是不加锁。

 

 

 

ddl锁(第二种锁)

 

我们一般不关心ddl锁,因为ddl是隐式提交,自动就会把锁释放。

ddl锁中,create加的就是ddl共享锁,droptruncatealter加的就是ddl排他锁。

 

 

查看锁的状态

下面说的DBA用户指的是sys用户:

 

col object_name for  a10

col oracle_username for a10

select

a.oracle_username,a.session_id,b.type,b.id1,b.id2,b.lmode,b.request,b.block,

c.object_name from v$locked_object a,v$lock b,dba_objects c where a.session_id=b.sid

and a.object_id=c.object_id;

 

 

lmode的数字表示的是锁的类型编号:

 

 

 

锁冲突的原因

 

 

 

跑完@?/rdbms/admin/catblock脚本后,才会产生dba_waitersdba_blocks两个数据字典。

dba_waiters 查询等待的人:

dba_blockes:查询阻塞会话的人。

死锁

死锁:就是都想要对方的东西,但是自己还持有对方的东西。说白了,也就是相互持有对方的一部分资源,等待对方的另外一部分资源,谁也不让谁,这就是死锁。死锁就是一个闭塞环路。

 

pmon是救死扶伤,解锁劝架。所以死锁是系统自动解除的,不需要dba解除。

 

ora 60错误在第一个会话上面报,说明会话1的死锁已经解除了,把会话1的最后一个操作的事务回滚。然后我们再看session 2,发现session 2仍然在发生等待。

 

此时,我们查看一下现在的锁情况:

col object_name for  a10

col oracle_username for a10

select

a.oracle_username,a.session_id,b.type,b.id1,b.id2,b.lmode,b.request,b.block,

c.object_name from v$locked_object a,v$lock b,dba_objects c where a.session_id=b.sid

and a.object_id=c.object_id;

 

 

未提交

紧接着上面的死锁来看未提交。

执行完@?/rdbms/admin/utllockt.sql后,可以看到lock_typenone,说明他不需要向别人发送请求,也就是283在阻塞别人。289在等待。

 

 

 

找到阻塞别人的会话后,dba可以手工把该session杀死。

 

 

小知识:

当然我们也可以通过dba_blockersdba_waiters来看是在等待,谁阻塞了别人。

上面可以看到tom阻塞了别人,scott在等待。

 

 

 

杀操作系统进程

 

select p.spid,s.machine,s.username,s.sid,s.serial#,s.status

from v$session s,v$process p

where s.sid in (select sid from v$session where username is not null) and s.paddr=p.addr;

 

spid 是操作系统的进程号

windows 的杀进程方法:orakill ORACLE_SID spid

 

dump

select extent_id,segment_name,bytes/1024,blocks,file_id,block_id from

dba_extents where segment_name='EMP' and owner='SCOTT';

 

 

 

udump里看

 

我们的目标是没有蛀牙

 

调整共享服务器

 

 

共享服务器

 

监听连接方式分为共享连接和专有连接,如下:

 

 

server process是负责通信的。

server processuser process的对应关系分两种:

         专有连接:一对一的对应;

         共享连接:一对多(一个server process服务对应好多个user porcess)或者多对多

 

 

 

 

上图中的dispatcher是分发器(相当于酒店的迎宾,分发器不止一个),分发器把请求放到sga的请求队列(请求队列只有一个)里面,然后server process来处理这个请求,处理完后再把处理结果放到sga响应队列(响应队列有多个)里面,然后再把请求返回给先前那个dispatcher分发器(这个分发器还是同一个)。

 

 

 

请求队列只有一个。响应队列不止一个。有几个dispatcher 分发器,就有几个响应队列。

 

Dispatcher(即分发器)相当于酒店的迎宾,server process相当于酒店的服务员。

 

 

专有连接和共享连接使用场景

必须使用专有连接的情况:

1、  dba连接数据库管理的时候;

2、  备份和恢复;

3、  批处理或者批量加载

4、  数据仓库

以上四种必须用专有连接方式。

 

使用共享连接的情况:

 

shared server(共享连接)的配置

服务端

SQL> select username,server from v$session where username is not null;

        

 

 

注意:share_server_session(一个共享连接打开session的数量)和shared_servers两个参数必须有值,否则就不能以共享方式连接数据库的。

 

小知识:

SHARED_SERVERS: Specifies the initial number of shared servers to start and the minimum number of shared servers to keep. This is the only required parameter for using shared servers.

MAX_SHARED_SERVERS: Specifies the maximum number of shared servers that can run simultaneously.

SHARED_SERVER_SESSIONS: Specifies the total number of shared server user sessions that can run simultaneously(同时). 

 

 

下面走的就是共享的连接:

cat tnsnames.ora

然后通过如下语句可以看出是走的什么连接方式:

SQL> select username,server from v$session where username is not null;

显示none或者shared都表示是共享连接

 

 

 

 

客户端

 

 

 

 

 

 

监控共享server

 

col name for a10

col network for a50

col status for a5

select name,network,paddr,status,accept,idle,busy from v$dispatcher;

 

 

 

select name,trunc((busy/(busy+idle)*100),4)||'%' "busy rate" from v$dispatcher;

 

上图看到我设置的是3个分发器

 

select name,status,idle,busy,trunc(busy*100/(busy+idle),1)||'%' rate,requests from v$shared_server;

 

上图有10条记录,其实是由shared_servers参数控制的,如下:

SQL> show parameter shared_servers; 

 

小知识:

SHARED_SERVERS: Specifies the initial number of shared servers to start and the minimum number of shared servers to keep. This is the only required parameter for using shared servers.

 

select

 decode(totalq,0,'No requests') "wait time",

 round(wait/decode(totalq,0,1,totalq),3)||'centisecond' "Average wait time perrequest"

from v$queue where type='COMMON';

 

centisecond是百分之一秒的意思

实验

 

 

如果不加#!/bin/bash,那么杀这个脚本的进程时会杀不掉。

 

插随机数的原因是为了每次生成的都是硬解析。

 

#!/bin/bash

while true

do

sqlplus scott/tiger@czx4 <

insert into e01 values

(round(dbms_random.value(0,8000)),'cuug','train',2233,sysdate,2000,1000,20);

rollback;

quit

EOF

done

 

 

 

vi test.sh

 

 

我认为shared_servers的值是在设置服务进程的数量。

 

col name for a10

col network for a70

col status for a5

select name,network,paddr,status,accept,idle,busy from v$dispatcher;

 

 

select name,trunc((busy/(busy+idle)*100),4)||'%' "busy rate" from v$dispatcher;

 

 

select

 decode(totalq,0,'No requests') "wait time",

 round(wait/decode(totalq,0,1,totalq),3)||'centisecond' "Average wait time perrequest"

from v$queue where type='COMMON';

 

 

select name,status,idle,busy,trunc(busy*100/(busy+idle),1)||'%' rate,requests from v$shared_server;

 

 

 

上面是有80个进程同时再跑,这80个进程是以共享方式(通过脚本里面的sqlplus scott/tiger@prod可以看出)连接数据库的。

忙率:就看dispatchershare server的繁忙程度。

如果用户很多,dispatcher很少,那么dispatcher就是瓶颈,如果dispatcher的忙率超过50%,就可以考虑增加分发器数量;同理,如果share server忙率超过50%,那么就可以考虑增加share server process进程的数量。

 

所以,此部分就看忙率。

 

那么怎么增加dispatchershare server 的数量呢?

 

 

上面设置的shared_servers10,但是如果忙率很高的话,数据库会自动调节为最大值30个。

 

杀进程

 

 

 

优化排序操作

 

 

 

 

快速排序

 

 

排序操作存放位置:pga和临时表空间。

 

uga:在专有连接模式里面,ugapga里面;但是在共享连接模式里面,uga在大池或者共享池里面。

 

查看排序区的大小:

show parameter sort_area_size

 

可以看到排序区是64k,如果在pga里面排序时空间不够,就会把中间结果放在临时表空间里面。

 

 

如果我创建一个索引,突然报临时表空间不足,原因是为什么呢?

原因是因为索引需要排序,如果临时表空间不足,就会报临时表空间不足。

 

排序区(pga)小,效率就低。因为如果排序区小,每次排序放不下多少行,就会分多次排序,从而效率低。

 

 

上图中的estd extra w/a mb read/written to disk是过载,表示让数据库感觉不到内存排序的压力。

小知识:过载的概念:就是系统担心pga的空间不够用,而给PGA分配内存,这种现象就叫过载(其实就是内存的泄露)。如果给Pga分配了内存,那么其他内存的空间就不够用了。

我们需要给pga设置一个合适大小,让系统自己不用担心pga的大小。

 

 

 

 

 

select sql_text,sum(onepass_executions) onepass_cnt,sum(multipasses_executions) mpass_cnt

from v$sql s,v$sql_workarea wa

where s.address=wa.address group by sql_text

having sum(onepass_executions+multipasses_executions)>0;

 

如果pga和临时表空间经常有往返的操作,就可以通过上述语句,看出是由于sql语句写的不好引起的还是排序比较大引引起的。

 

另外,查看磁盘排序和pga排序的比例:

select disk.value "DISK",mem.value "Mem",

          (disk.value/mem.value)*100||'%' "Ratio"

from v$sysstat mem,v$sysstat disk

where mem.name='sorts (memory)'

and disk.name='sorts (disk)';

 

 

排序调优的方法:        

         可以调整sort_area_size的大小;不过在调整sort_area_size大小之前,先改workarea_size_polycymanual,因为默认是自动管理sort_area_size大小的。

根据需要,不用动系统级别的(即不用alter system),而是用session级别的。

 

默认情况下,sort_area_retained_size=0,表示排序区排完序后,排序区的空间就释放了。

sort_area_retained_size=sort_area_size:表示排完序后空间不立即释放,而是等到把排序的结果返回给用户的时候再释放。

sort_area_retained_size=其他值适用场景:假如有一条非常复杂的sql语句,用了自连接,一个表名起了两个别名AB,一个字段在表别名A中排序了,但可能这个字段在表别名B中也要排序。如果在A中排完序后空间释放了,那么在B中还要排次序,就耗费性能。所以为了解决这种情况,可以把sort_area_retained_size= sort_area_size, 表示排完序后空间不立即释放,而是等到把排序的结果返回给用户的时候再释放。

 

那么如果设置sort_area_retained_size= sort_area_size/2是什么意思呢?表示的是我排完序以后,立即释放一半的空间,还留一半空间。

一般pga空间不是太紧张时,可以考虑设置sort_area_retained_size= sort_area_size

 

 

 

一个操作可能导致使用多个排序区

select d.dname,sum(e.sal) from scott.emp e,scott.dept d where e.deptno=d.deptno group by d.dname;

 

group by 不排序,加个order by就会排序

select d.dname,sum(e.sal) from scott.emp e,scott.dept d where e.deptno=d.deptno group by d.dname order by d.dname,sum(sal);


如果要排序,可以建个索引,因为索引本身就是有序的,从而减少了表的排序。

另外,如果一个表里索引已经很多了,那么就不要在建立索引了,可以调节sort_area_size的大小来进行优化。

 

下面这条语句就会出现一个操作使用了多个排序区:

parallel并不一定好,因为它消耗cpu资源,并且parallel语句会使用好几个排序区。你并发写几,他就用几个排序区,如上述的parallel(e3),就会使用3个排序区。

如果是rac,会使用两倍的排序区。

 

一般系统比较空闲时,用parallel语句还是不错的。

 

尽量避免排序

 

 

创建索引

 

create index i_sort on t_sort(empno) nosort;

 

 

select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks

from dba_indexes where index_name='I_SORT';

 

上图中说的表事先已经asc排序(即事先已经升序),创建索引时可以指定nosort不排序。不过不建议使用nosort,因为索引是有序的,但是你用了nosort,你必须严格保证表的数据必须有序。

 

distinct

去重的两种方法:一个是disctinct,一个是group by

 

 

distinct去重是需要排序的,而group by去重是不需要排序的,所以大家在生产中需要去重的话,就用group by吧。

 

unionunion all

集合的并交差三种方式

 

 

下面把上面两个结果写在一块:

 

小知识:

默认的当一条sql发出的时候,oracle会给一个反馈,比如说创建表的时候,如果成功,命令行会返回类似:Table created的反馈,又比如执行一个pl/sql成功的时候,命令行会返回:PL/SQL procedure successfully completed 

 

 

上图看到union是有排序的动作的。

 

再加个all来看一下:

 

 

看到union all没有排序了。

union是有排序的。

 

上面两条语句不足以看出结果集的区别,现在我们再来看个例子:

 

加个all

 

表连接

 

表连接有先后执行的问题,即有时间差。

merger join是需要排序的,可以在连接字段上创建索引

一般来说,有索引以后,就走netst loop;没有索引,一般走hash了;如果你发现有一个字段能很好的筛选数据,那么我们可以在这个字段上创建索引,建完索引以后,就有可能走nest loop了。

analyze

大表就用estimate,小表就用compute

监控排序

 

select disk.value "DISK",mem.value "Mem",

          (disk.value/mem.value)*100||'%' "Ratio"

from v$sysstat mem,v$sysstat disk

where mem.name='sorts (memory)'

and disk.name='sorts (disk)';

 

 

另外,查看pga和磁盘表空间往返的SQL语句:

select sql_text,sum(onepass_executions) onepass_cnt,sum(multipasses_executions) mpass_cnt

from v$sql s,v$sql_workarea wa

where s.address=wa.address group by sql_text

having sum(onepass_executions+multipasses_executions)>0;

 

 

 

临时表空间

 

 

临时表空间相当于一个公司的会议室。

 

group by 不排序,但是group by分组时会用到临时表空间的。

 

 


 

 

查看临时文件:


 

 

 

 

 

下面我们把上面两个表空间创建一个临时表空间组:

 

查看临时表空间和查看字符集都用如下语句:

 

下面把临时表空间组设置成默认表空间:

 

下面看临时表空间的users

 

 

下面让一个用户使用指定的一个临时表空间:


 

 

 

临时表空间的优化:

多创建几个临时表空间,让不同的用户或者并发的程序使用不同的临时表空间:

设置虚拟机交互区

设置tmpfs文件

 

 

上图中的tmpfs叫共享内存文件,这个文件在内存中,所以其效率很高。

tmpfs文件存放的是临时东西(功能类似于oracle的临时表空间),就是放一下临时的东西,而且临时的东西是放在内存中的。tmpfs默认大小是内存的一半。要想改该文件的大小,改/etc/fstab里面的tmpfs的值,然后mount –o remount /dev/shm使其生效。

 

 

 

 

2、交换区

 

 

创建交换区:

 

/etc/fstab里面再加一行

 

我们的目标是没有蛀牙

 

 

调整回滚段

回滚段中记录的不是整个块,而是你修改哪行,哪行数据就放在回滚段里面。这样减少回滚段的空间。

 

回滚段的功能

回滚段的功能有四个:

 

 

 

 

 

 

 

 

 

倒叙查询就是我们所说的闪回查询。

 

开闪回的前提是要先开归档。

 

开启闪回

闪回区里面存放闪回日志

 

 

undoredo的区别

 

 

大家注意,没有undo日志,只有undo数据。

 

 

 

 

 

 

 

redo 就像照相机,重放过去的事务:

 

 

 

闪回区的作用

 

 

select name,bytes/1048576 m from v$sgastat where pool='shared pool' and name like '%flash%'

 

 

 

为什么把回滚段画成圆的?因为回滚段是由很多区组成,用完第一个区,就用第二个区……循环的。

 

 

 

 

闪回区里面存如下东西:

 

我们一般不把归档放在闪回区里面,因为闪回区有大小的限制,所以我们一般把归档单独放在一个目录下(用log_archive_dest指定);

查看闪回区的位置用show parameter recovery来看。

 

 

小知识:

         闪回的分类:

1、  闪回drop,利用回收站实现;

2、  闪回查询闪回表,依赖的是回滚段(记录dml语句,不记录ddl语句,因为ddl是隐式提交,回滚段被覆盖)里面的值:

参数undo_retention,表示希望在这个时间内undo数据不要被覆盖。但是如果undo空间非常紧张时,没有达到undo_retention时间的undo数据也会被覆盖。如果你想在undo_retention时间内的undo一定不要被覆盖,需要设置:alter tablespace undotbs retention guarantee

guarantee记忆方法:寡人一个人(n)替人担保。

3、  闪回数据库,依赖闪回日志(闪回日志记录的是ddl操作);

4、  闪回归档

 

 

 

 

出现01555错误的原因:系统非常繁忙,进行着大量dml操作,undo段非常紧张,假设有个查询语句非常复杂,他在查询某个块时,发现这个数据块正在被修改且没有被提交,那么这条超复杂的查询语句就会去回滚段查找数据,如果这时候那个修改的事务提交了,因为undo空间紧张,所以可能这时那个被修改的块在undo对应的数据就被清除了,这时候那条超复杂的查询语句到回滚段一看要查询的数据不在了,就会抛出01555错误。

 

块的真相

一个数据块就好比一个水杯,由块头,装数据的位置和空闲位置组成。

块头里面有事务槽。

事务槽 :就是一种排队机制。假设块头有10个事务槽,每个事务一个槽位,第十一个事务就得排队了。事务槽记录了事务的id,事务scn号,uba(事务修改的数据在回滚段的位置)、lck(影响事务的行数)、flag

 

如果一个事务没有提交,那么块头的scn号(就是这个块的scn号)是比较稳定的、状态不变,如果提交了,那么块头的scn号就更新了。

 

事务的scn号和块头的scn号比较:

         如果事务的scn号比块头的scn号大,表示这个块被修改过了,server process就找这个块回滚段的位置,从而读回滚段的数据;反之,表示这个块没有被修改过,server process就直接读这个块了。

 

 

 

 

回滚段的状态

 

上述是回滚段产生热块的原因,解决方法就是一个回滚段让少量的事务来修改。

 

 

oracle系统默认分配10个回滚段(system的除外,因为他是给系统用的),最多有30个回滚段。但是有时候我们会看到系统有大于30个回滚段的现象,这就是由于不正确的管理方式造成的,此时可能是数据文件可以自动增长导致的。

 

 

 

如果一个块被修改,该块会在回滚段中记录为active状态,如果事务结束,回滚段的区就变成inactive

 

 

*如果一个事务很大,那么可能这个事务会跨越好几个回滚段。

 

 

手工管理和自动管理的优缺点

 

SQL> select tablespace_name,sum(bytes)/1024/1024 m from dba_free_space group by tablespace_name;

 

结论:回滚段的管理最关键的不是创建多大的回滚段,而是要及时的提交。

 

回滚段的自动扩展

 

 

 

一般我们用自动管理回滚段就行。但设置自动管理不一定好。

回滚段调优方向:

         1、不应该有等待事件

                   a、假设我有10个回滚段,当第十一个事务来时,我需要新创建一个新的回滚段,那么在创建新的回滚段时会产生等待;

                   b、当回滚段达到30个的时候,就不能再增加回滚段的个数,那么很多事务就会共用回滚段。但是回滚段空间不够大时,就需要扩展回滚段的空间,这时也会发生等待;

         2、不能存在耗尽回滚段的事务。

                  

减少手工管理时回滚段扩展的概率:

1、  可以给每个回滚段多分点extent,也就是让每个回滚段很大。这样就会减少回滚段扩展的概率。.

2、  另外,可以在手工扩展回滚段时,让新增加的回滚段里面的extent的个数多点;

3、及时提交---这是降低回滚段扩展概率的关键。

大家看下图,表示的是回滚段区的个数和回滚段扩展概率的一条曲线。可以看到,随着回滚段区的个数增加,回滚段扩展概率也在降低,但是当达到一个数值,再增加区的数量,回滚段扩展概率降低的幅度很小了。所以,回滚段里面的区的个数设置一个合适的值就行了。如下图的20。如果再增加区的数量,对性能的提高意义不大。

 

 

 

 

手工管理回滚段的实验

回滚段自动管理:表示当我们创建一个回滚表空间的时候,他自动创建里面的回滚段,不需要dba管理。


 

 

现在我们把它变成回滚段手工管理的方式:

alter system set undo_management=manual scope=spfile--这是个静态参数,需要重启数据库生效。

 

shutdown immediate;

startup

 

现在我们已经改成手工管理回滚段的方式了。

现在我们做一个dml操作(做dml操作时需要用到回滚段):


 

看到报错了,原因是手工管理回滚段的方式是没有undo表空间的概念的。手工管理回滚段的方式是可以在任何表空间创建回滚段的。

下面我们就专门用一个表空间(其实这个表空间是任意的)来存放回滚段。

查看表空间默认区的大小:

select tablespace_name,contents,status,initial_extent/1024 init,next_extent/1024 next,pct_increase from dba_tablespaces;

 

上图看到,我们表空间rbs初始区大小是64K

下面我们创建一个初始有20个区,下一次扩展也是20个区的回滚段:

 

create rollback segment r01 tablespace rbs storage(initial 1280k next 1280k);

 

注:1280k=64k*20

 

上面还是报错了,原因是我们需要先创建system表空间的回滚段后,才能再建其他表空间创建回滚段:

 

 

 

 

 

declare

  v_sql varchar2(200);

begin

  for i in 1..20

  loop

   v_sql:='create rollback segment seg'||lpad(i,2,'0')||' tablespace rbs storage(initial 1280k next 1280k)'; //tablespace 前面有个空格

   execute immediate v_sql;

   v_sql:='alter rollback segment seg'||lpad(i,2,'0')||' online';   //online前面有个空格

   execute immediate v_sql;

  end loop;

end;

/

 

 

 

再次执行dml语句就成功了:

 

select segment_name,tablespace_name,owner,file_id,status,initial_extent/1024 init,next_extent/1024 next from dba_rollback_segs;

现在我们看到我们手工创建了大的、数量也多的回滚段。这就是黑社会吃自助餐(一上来就要大盘)。

 

我们可以指定哪个事务使用哪个回滚段:

 

 

select a.username,a.sid,a.serial#,b.xidusn,b.xidslot,b.status,c.usn,c.name,

d.extents,d.xacts

from v$session a,v$transaction b,v$rollname c,v$rollstat d

where a.saddr=b.ses_addr and b.xidusn=c.usn and c.usn=d.usn;

 

 

上图看到,事务使用的是seg13回滚段。事务使用哪个回滚段是随机的。

xacts=1表示事务是活跃的,事务正在进行中.

 

注意,如果想下次还用这些回滚段,要在初始化参数文件中添加:

 

 

实验结论:上述我们进行了一个手工管理回滚段的实验。在生产中,迫不得已(就是我想人工干预大的事务使用大的回滚段,小的事务使用小的回滚段。但是我们要记住关键点是及时提交就不会产生回滚段性能问题),我们不要用这种手工管理的方式。我们用自动管理就行。

 

吃自助餐不是你用大的盘子,用多少个盘子,而是要及时吃。

 

好了,我们做完了手工管理回滚段的实验,下面我们再设置回原来的自动管理回滚段的状态下吧:

alter system set undo_management=auto scope=spfile

shutdown

startup

 

 

 

大家看到,只要是自动管理,就默认是有10个回滚段。最大可以自动扩展到30个。

 

 

回滚段调优工具

动态性能视图

select class,count from v$waitstat where class like '%undo%';

 

 

 

select sum(value) from v$sysstat where name in ('db block gets','consistent gets');

 

 

 

select trunc(sum(waits)*100/sum(gets),3)||'%' "Ratio",sum(waits) "Waits",sum(gets) "Gets" from v$rollstat;

 


 

 

 

statspack busy waits

buffer busy wait

 

 

 

实例负载归档信息

 

 

 

select * from v$sysstat where name in ('user commits','user rollbacks','transaction rollbacks');

 

 

回滚段统计信息

 

shrinks的值为0,说明从来没有收缩过。

 

 

如果extents扩展的次数比较多,就会产生等待事件。解决办法就是可以通过手工管理回滚段的方式,给回滚段的区分的更大一些;其实自动管理,就已经管理的很好,所以我们用自动管理就行。

 

关心pct waits的值

如何产生较少的undo数据

经常提交:尽量一百行或者一千行一提交。

 

 

 

consistent=y,这个参数是为了解决在导出表时有dml操作时的情况,exp命令会把它所涉及的表全部放到回滚段,然后从回滚段中再导出,如果表很大,将占用很多的回滚段。

consistent=n,在导出数据的时候,不把表放到回滚段后再导出,而是直接导出。这时候如果在导出表的过程中,有dml操作,就会出现数据不一致的情况。所以,设置成n也需要慎重。

 

import时最好设置commit=y,批量提交(设置成一百行或者一千行一提交)。

 

如何正确插入数据

1、一百行一提交或者一千行一提交

 

写程序时最好一百行一提交或者一千行已提交

 

 

 

 

begin

         for i in 1..10000

         loop

                   insert into test values (i,'zhangsan'||i);

         if mod(i,1000)=0 then

                   commit;

         end if;

        

         end loop;

         commit;

end;

/

 

 

2、一天一提交

一天一提交适用于一天内不会产生占用特别多回滚段的情况,如果一天一提交会占用太多的回滚段,那就用分页插入。

 

 

 

 

 

declare

         cursor cu_emp is

                   select * from emp_cu for update;

         v_date date;

         k number;

begin

         k:=1;

         v_date:=to_date('2015-03-09 20:40:00','yyyy-mm-dd hh24:mi:ss');

         for i in cu_emp

         loop

                   update emp_cu set hiredate=v_date,empno=k where current of cu_emp;

                   v_date:=v_date+1;

                   k:=k+1;

         end loop;

         commit;

end;

/

 

 

小知识:current of 表示把游标指针fetch的当前行进行修改,只修改当前行。

Select …from For updatecurrent of经常连用,也就是我for update锁住一些行后,来修改current of后面的当前内容。

 

 

 

 

declare

         cursor cu_emp is

                   select * from emp_cu for update;

         v_date date;

         k number;

begin

 

         v_date:=to_date('2015-03-09 20:40:00','yyyy-mm-dd hh24:mi:ss');

         for i in cu_emp

         loop

                   update emp_cu set hiredate=v_date  where empno=i.empno;

                   v_date:=v_date+1;

                  

         end loop;

         commit;

end;

/

上图中的存储过程是把201511日前的A表的数据插入B表。

 

create or replace procedure proc_insert_data_t

as

         current_time date;

begin

         current_time:=to_date('2015-03-09','yyyy-mm-dd');

         for count in 1..1000000

         loop

                   insert into emp_cu2

                   select t.* from emp_cu t

                   where t.hiredate>=current_time

                   and t.hiredate

         current_time:=current_time+1;

         commit;

         if current_time>=to_date('2015-01-01','yyyy-mm-dd') then

         exit;

         end if;

         end loop;

 

end;

/

 

 

 

 

 

3、分页插入

 

 

上述匿名块的关键点是v_pagesize,表示页有多少条(上述页数是1000,表示一千行一千行的查)和起始位置。

 

上面标红字的v_startindexv_endindex表示页的起始位置和页的终止位置。

 

聚簇因子(重排):表示相同的数据放在一块,这样走索引的时候,rowid就不会大幅度跳动乱指,从而更容易走索引。这种情况就会有重排的过程,排序代价很高。所以想order by的时候,先建立索引。因为索引本身就是有序的,所以有了索引就不用再加order by了,这样代价就降低了。

 

4、重排数据并插入

 

 

其实,上面这个脚本是一天一提交和分页插入的合体,用于数据量超大,比较变态的系统,即先按天循环,在按页插入。

 

我们的目标是没有蛀牙

 

我们可以通过回滚段手工管理,一下子把回滚段的区分的大点,这样可以减少回滚段不够的情况;对于自动管理回滚段,DBA只要保证表空间的空间足够大就行了,其余的让数据库自己管理吧。

 

 

结论:只要你及时提交,空间足够,自动管理是不错的选择;在某些场合,如果你有想让某些事务使用大的回滚段的需求等,就可以使用手工管理回滚段。

 

 

 

 

手工建库

 

 

 

手工建库三步:

 创建目录,赋予权限;

 

   su - oracle

         mkidir $ORACLE_BASE/oradata/sid

chown –R oracle.oinstall /u01/app/oracle/oradata/sid

cd $ORACLE_BASE/oradata/sid/

chmod –R  755 sid

mkdir disk1 disk2 disk3 disk4 disk5 bdump cdump udump

要注意权限

 

vi $ORACLE_HOME/dbs/initsid.ora

 

参数文件有个口诀:

         山地车,又又又被吃(sdcuuubc)---10g

11g写参数文件就两步:

 

 

应用调整

 

选择合适的存储结构

 

分区表

你还可以参考本文档其他部分分区表的内容:分区表

 

全表扫描的终点是高水位线。

分区表可以降低全表扫描的范围(在不使用索引的情况下)

 

分区表逻辑上是一张表,但物理上是由很多小表组成。

分区表可以降低全表扫描的范围。

对于范围分区,可以以年分区,以月分区,以天分区,也有以小时分区的(电信)。以小时分区不好。因为分区表逻辑上是一张表,如果以小时来分区,就会产生很多的小表,这么多的小表,维护起来开销大,因为数量太多了。所以总体来说以天分区就可以了。

 

list分区,就是列举,比如河南、河北等这样列举;

 

hash分区,随机数

 

如果是sql语句是范围查询,那么用范围分区比较好。

 

一般来说,一张表大于2G,就需要建立分区表。一个分区表建议存放数据不超过100万行数据(也不是绝对的,超过100万行也是可以的,可能性能有些低)。

                                                                                                           

把表数据放在不同的分区,放在不同的磁盘,提高性能。

分区表可以降低全表扫描的代价。分区表是根据键值,把数据放在一个分区(段)中。如果你想把相同键值的数据往一个数据块(也不一定是只一个数据块里面存放,还可能是多个块,但是不要超过三四个数据块,否则簇表的性能就会走下坡了)里面塞儿,那就用簇表。如果是多表查询、范围查询就用索引簇,如果是等值查询就用hash簇。对于簇表来说,索引簇和hash簇都不适合update操作,但是如果有少量的update,使用索引簇比hash簇更合适。

什么时候不适合使用簇呢?

簇键值经常变、空间紧张等就不适合建簇表。

 

 

创建分区表

在生产库中建立分区表,为安全起见,我们可以先建立一个和原表一样的表,然后再对新的表进行分区,最后再把新表rename成原表,原表renamexx_old

 

 

入口:administrator—>partitioned

 

 

 

请把1999放在第一个分区….

 

 

create table sales_history

                   (id number(8),name varchar2(30),sales_date date)

         partition by range (sales_date)

         (partition p1 values less than (to_date('01-JAN-1999','DD-MON-YYYY')) tablespace users,

         partition p2 values less than (to_date('01-JAN-2000','DD-MON-YYYY')) tablespace users,

         partition p3 values less than (to_date('01-JAN-2001','DD-MON-YYYY')) tablespace users,

         partition p4 values less than (to_date('01-JAN-2002','DD-MON-YYYY')) tablespace users,

         partition p5 values less than (maxvalue) tablespace users

);

 

 

 

 

insert into sales_history values (1,'PCT1',to_date('17-FEB-2000','DD-MON-YYYY'));

insert into sales_history values (2,'PCT2',to_date('17-FEB-2001','DD-MON-YYYY'));

insert into sales_history values (3,'PCT3',to_date('17-FEB-2002','DD-MON-YYYY'));

insert into sales_history values (4,'PCT4',to_date('17-FEB-1999','DD-MON-YYYY'));

insert into sales_history values (5,'PCT5',to_date('17-FEB-2000','DD-MON-YYYY'));

insert into sales_history values (6,'PCT6',to_date('17-FEB-2001','DD-MON-YYYY'));

insert into sales_history values (7,'PCT7',to_date('17-FEB-1998','DD-MON-YYYY'));

insert into sales_history values (8,'PCT8',to_date('17-FEB-1998','DD-MON-YYYY'));

insert into sales_history values (8,'PCT8',to_date('17-FEB-2002','DD-MON-YYYY'));

insert into sales_history values (7,'PCT7',to_date('17-FEB-2013','DD-MON-YYYY'));

commmit;

 

 

 

请创建一个唯一global index

 

 

create unique index i_sales_id on sales_history(id)

                   global partition by hash (id)

         (partition pi1 tablespace users,

          partition pi2 tablespace users,

          partition pi3 tablespace users,

          partition pi4 tablespace users

) parallel 4;      

 

上述看到报错了,原因是我们要创建唯一索引,但是表里面有重复行。

下面我们去除重复行。

去除重复行

先查看一些去除重复行之前的状态:

 

开始去重:

上图去重思路:

上图以a表和b表以id字段来关联。

相同rowid的行就是重复行。

对于不是重复的行,其rowid既是最大值也是最小值,因为没人和它比。

对于重复的行,其rowid就有多个,也就有最大和最小rowid之分。

 

所以,对于重复的行去重的思路就是:

如果是单行,那么单行本身就是最大或者最小rowid,所以单行中最小的或者最大的rowid行显示出来,其实就是单行自己都显示出来(加not就表示不显示这个最大或者最小的rowid行);

如果是重复的行,因为有多个rowid,我只显示rowid最小的行,其他rowid的我不显示

 

显示重复记录:

 

 

删掉重复记录:

 

create unique index i_sales_id on sales_history(id)

                   global partition by hash (id)

         (partition pi1 tablespace users,

          partition pi2 tablespace users,

          partition pi3 tablespace users,

          partition pi4 tablespace users

) parallel 4;      

 

 

 

下面再建立两个索引:

 

select index_name,index_type,table_name,tablespace_name,partitioned from dba_indexes where table_name='SALES_HISTORY';

 

 

 

 

创建索引

 

索引分为一般索引(不分区)和分区索引。

分区索引又分为全局索引和本地索引。

olap适用本地索引

 


 

 

一般索引:

下面这个就是一般索引:

下面这张图就是一般索引:

一般索引指的是这个索引没有分区,并且索引里面的rowid指向表时是乱指的(上图画的有点规矩了)。

 

分区索引

http://www.linuxidc.com/Linux/2013-01/78218.htm 更加详细的可以看这个文档。或参考我的为知笔记

上图中的yes就表示的是分区索引。

 

全局索引

 

全局索引示意图:

 

全局分区索引,rowid也是乱指的,其优点是全局有序,这样检索的效率会高。

 

本地索引

 

下面这个索引就是本地索引:


 

本地分区索引示意图:

local 索引,索引的分区和表的分区是一一对应的,并且其rowid不是乱指的,而是一一对应的。

本地分区索引是局部有序,全局无序,也就是相当于给每个分区表创建一个小的索引。

所以本地索引的检索效率就没有global索引的高。

local索引也有好处:我们的分区表如果经常做truncate操作,如果是global类型的索引,那么truncate分区后,这个global索引就不能用了,因为global索引是全局有序的;如果索引是local索引,我truncate一个分区时,只是这个表分区对应的索引分区坏了,其他分区的索引不受影响。

 

数据仓库经常做truncate操作,要用本地索引;

oltp系统,global索引的效率就比较高了。所以oltp系统用global索引。

 

 

本地分区索引是对单个分区的,每个分区索引只指向一个表分区,全局索引则不然,一个分区索引能指向n个表分区,同时,一个表分区,也可能指向n个索引分区。

truncate partition

 

索引重建

 

Alter index idx_name rebuild partition index_partition_name [online nologging]

需要对每个分区索引做rebuild,重建的时候可以选择online(不会锁定表),或者nologging建立索引的时候不生成日志,加快速度。

Alter index rebuild idx_name [online nologging]

对非分区索引,只能整个index重建

                                                                                                    

 

所以,我们在工作中创建分区表时,一般不指定maxvalue,如果指定maxvalue,就不能再增加分区了。因为增加的新分区边界必须大于最后一个分区,你用了maxvalue,那么就没法再大于它了,所以也就不能再增加分区了。所以工作中不指定maxvalue

  小知识:

 如果指定了maxvalue,想再增加分区的个数,需要用split增加分区的个数:

SQL> alter table test split partition p3 at (30000) into (partition p3, partition p4);

原分区表:

create table test (id number,data varchar2(100))
partition by RANGE (id)
(
partition p1 values less than (10000) ,
partition p2 values less than (20000) ,
partition p3 values less than (maxvalue) 
);

                                                                                               

老师经验:分区表用完了,程序会报错,所以要注意表的分区不要用完

 

小知识:分区索引使用场景

 

参考文档:http://www.linuxidc.com/Linux/2013-01/78218.htm

 

分区索引总结:

一,分区索引分为2类:

1global,它必定是Prefix的。不存在non-prefix

2local,它又分成2类:

  2.1prefix:索引的第一个列等于表的分区列。

  2.2non-prefix:索引的第一个列不等于表的分区列。

 

 

LOCAL的索引只能是表的分区方式,不能自己写分区方式。他们是EQUI-Partition的。GLOBAL索引可以不分区,这个时候就是普通的一个索引。同一个列只能只有一个索引,这个列可以是GLOBAL或者是LOCAL的索引。如果唯一索引所在的列不是表的分区列,只能建立GLOBAL索引。

 

 

备注:

 

局部索引local index

 

 

 

1.        局部索引一定是分区索引,分区键等同于表的分区键,分区数等同于表的分区说,一句话,局部索引的分区机制和表的分区机制一样。

 

2.        如果局部索引的索引列以分区键开头,则称为前缀局部索引。

 

3.        如果局部索引的列不是以分区键开头,或者不包含分区键列,则称为非前缀索引。

 

4.        前缀和非前缀索引都可以支持索引分区消除,前提是查询的条件中包含索引分区键。

 

5.        局部索引只支持分区内的唯一性,无法支持表上的唯一性,因此如果要用局部索引去给表做唯一性约束,则约束中必须要包括分区键列。

 

6.        局部分区索引是对单个分区的,每个分区索引只指向一个表分区,全局索引则不然,一个分区索引能指向n个表分区,同时,一个表分区,也可能指向n个索引分区,

 

          对分区表中的某个分区做truncate或者moveshrink等,可能会影响到n个全局索引分区,正因为这点,局部分区索引具有更高的可用性。

 

7.        位图索引只能为局部分区索引。

 

8.        局部索引多应用于数据仓库环境中。

 

 

 

全局索引global index

 

 

 

1.        全局索引的分区键和分区数和表的分区键和分区数可能都不相同,表和全局索引的分区机制不一样。

 

2.        全局索引可以分区,也可以是不分区索引,全局索引必须是前缀索引,即全局索引的索引列必须是以索引分区键作为其前几列。

 

3.        全局分区索引的索引条目可能指向若干个分区,因此,对于全局分区索引,即使只动,截断一个分区中的数据,都需要rebulid若干个分区甚

 

          至是整个索引。

 

4.        全局索引多应用于oltp系统中。

 

5.        全局分区索引只按范围或者散列hash分区,hash分区是10g以后才支持。

 

6.        oracle9i以后对分区表做move或者truncate的时可以用update global indexes语句来同步更新全局分区索引,用消耗一定资源来换取高度的可用性。

 

7.        表用a列作分区,索引用b做局部分区索引,若where条件中用b来查询,那么oracle会扫描所有的表和索引的分区,成本会比分区更高,此时可以考虑用b做全局分区索引

 

 

分区索引字典

 

DBA_PART_INDEXES 分区索引的概要统计信息,可以得知每个表上有哪些分区索引,分区索引的类新(local/global,)

Dba_ind_partitions每个分区索引的分区级统计信息

Dba_indexesminusdba_part_indexes,可以得到每个表上有哪些非分区索引

 

索引重建

 

Alter index idx_name rebuild partition index_partition_name [online nologging]

需要对每个分区索引做rebuild,重建的时候可以选择online(不会锁定表),或者nologging建立索引的时候不生成日志,加快速度。

Alter index rebuild idx_name [online nologging]

对非分区索引,只能整个index重建

 

 

 

例如:分区表

create table test (id number,data varchar2(100))

partition by RANGE (id)

(

partition p1 values less than (10000) ,

partition p2 values less than (20000) ,

partition p3 values less than (maxvalue)

);

 

ID列上创建一个LOCAL的索引

create index id_local on test(id) local;

 

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='ID_LOCAL';

 

INDEX_NAME                    PARTITION_NAME                HIGH_VALUE          STATUS

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

ID_LOCAL                      P1                            10000                USABLE

ID_LOCAL                      P2                            20000                USABLE

ID_LOCAL                      P3                            MAXVALUE            USABLE

从上面可以看出索引的分区和表一样,即是EQUI-PARTITION

 

如果我在表上增加个分区,则Oracle会自动维护分区的索引,注意此时加分区必须是用split,直接加会出错的。例如:

SQL> alter table test add partition p4 values less than (30000);

alter table test add partition p4 values less than (30000)

                              *

ERROR at line 1:

ORA-14074: partition bound must collate higher than that of the last partition

 

--笔者批注:我想上面报错的原因就是因为在创建分区表时指定了maxvlue字段造成的,所以在工作中不指定maxvalue

SQL> alter table test split partition p3 at (30000) into (partition p3, partition p4);

 

Table altered.

 

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='ID_LOCAL';

 

INDEX_NAME                    PARTITION_NAME                HIGH_VALUE          STATUS

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

ID_LOCAL                      P1                            10000                USABLE

ID_LOCAL                      P2                            20000                USABLE

ID_LOCAL                      P3                            30000                USABLE

ID_LOCAL                      P4                            MAXVALUE            USABLE

 

 

 

SQL> select INDEX_NAME,INDEX_TYPE,TABLE_NAME from dba_indexes where index_name='ID_LOCAL';

 

INDEX_NAME                    INDEX_TYPE                  TABLE_NAME

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

ID_LOCAL                      NORMAL                      TEST

 

 

删除id_local索引

drop index id_local;

 

重新在ID列上创建一个GLOBAL的索引

create index id_global on test(id) global;

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='ID_GLOBAL';

 

no rows selected

 

SQL> select INDEX_NAME,INDEX_TYPE,TABLE_NAME from dba_indexes where index_name='ID_GLOBAL';

 

INDEX_NAME                    INDEX_TYPE                  TABLE_NAME

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

ID_GLOBAL                      NORMAL                      TEST

从上面可以看出,它此时是个普通索引。dba_ind_partitions里根本就没有记录。

 

SQL>create index i_id_global on test(data) global

  partition by range(id)

  ( partition p1 values less than (10000) ,

    partition p2 values less than (MAXVALUE)

  );

  partition by range(id)

                        *

ERROR at line 2:

ORA-14038: GLOBAL partitioned index must be prefixed

此错误表示GLOBAL的索引必须是prefixed,即索引分区的列,必须是其基表的分区列。

 

 

SQL>create index id_global on test(id) global

  partition by range(id)

  ( partition p1 values less than (10000) ,

    partition p2 values less than (MAXVALUE)

  );

 

Index created.

 

 

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='ID_GLOBAL';

 

INDEX_NAME                    PARTITION_NAME                HIGH_VALUE          STATUS

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

ID_GLOBAL                      P1                            10000                USABLE

ID_GLOBAL                      P2                            MAXVALUE            USABLE

 

SQL> select INDEX_NAME,INDEX_TYPE,TABLE_NAME from dba_indexes where index_name='ID_GLOBAL';

 

INDEX_NAME                    INDEX_TYPE                  TABLE_NAME

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

ID_GLOBAL                      NORMAL                      TEST

 

从上面可以看出,它此时是个GLOBAL的索引了。dba_ind_partitions里有记录。请和上面的做个比较,加深印象。

 

 

 

 

二,到底如何判断建立怎样的分区索引(GLOBAL 还是LOCAL)

我将用下面的例子来分析到底需要创建什么类型索引好。

 

create table TT(id number,createdate date)

partition by range(createdate)

(

  partition Q1 VALUES LESS THAN (TO_DATE('2012-03-30','YYYY-MM-DD')),

  partition Q2 VALUES LESS THAN (TO_DATE('2012-06-30','YYYY-MM-DD')),

  partition Q3 VALUES LESS THAN (TO_DATE('2012-09-30','YYYY-MM-DD')),

  partition Q4 VALUES LESS THAN (TO_DATE('2012-12-31','YYYY-MM-DD')),

  partition Q_OTHERS VALUES LESS THAN (MAXVALUE)

);

 

注意:只能是to_date,其他的任何函数都不行,maxvalue必须在最后,他可以包括NULL值。

 

 

第一种情况:

如果查询的语句的条件是where createdate='2012-10-19' and id>100,则此时查询的是4号分区,假设他有10万条记录。在扫描这10万条记录的时候,

可以使用id列上的索引。这个时候可以在ID列上建立个local nonprofiex索引

create index index_tt1_local on TT(id) local

( partition p1,

  partition p2,

  partition p3,

  partition p4,

  partition p5

);

注意:索引分区的数量和其基本的分区数量要一样。

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='INDEX_TT1_LOCAL';

 

INDEX_NAME                    PARTITION_NAME                HIGH_VALUE          STATUS

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

INDEX_TT1_LOCAL                P1                            TO_DATE(' 2012-03-30 USABLE

                                                              00:00:00', 'SYYYY-M

                                                              M-DD HH24:MI:SS', 'N

                                                              LS_CALENDAR=GREGORIA

 

INDEX_TT1_LOCAL                P2                            TO_DATE(' 2012-06-30 USABLE

                                                              00:00:00', 'SYYYY-M

                                                              M-DD HH24:MI:SS', 'N

                                                              LS_CALENDAR=GREGORIA

 

INDEX_TT1_LOCAL                P3                            TO_DATE(' 2012-09-30 USABLE

 

INDEX_NAME                    PARTITION_NAME                HIGH_VALUE          STATUS

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

                                                              00:00:00', 'SYYYY-M

                                                              M-DD HH24:MI:SS', 'N

                                                              LS_CALENDAR=GREGORIA

 

INDEX_TT1_LOCAL                P4                            TO_DATE(' 2012-12-31 USABLE

                                                              00:00:00', 'SYYYY-M

                                                              M-DD HH24:MI:SS', 'N

                                                              LS_CALENDAR=GREGORIA

 

INDEX_TT1_LOCAL                P5                            MAXVALUE            USABLE

 

 

 

 

第二种情况:

如果查询的语句条件只有一个createdate,where createdate='2010-10-19',则这种情况就在createdate上建立一个local profiex索引

SQL> create index index_TT2_local on TT(createdate) local;

 

Index created.

 

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='INDEX_TT2_LOCAL';

 

INDEX_NAME                    PARTITION_NAME                HIGH_VALUE          STATUS

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

INDEX_TT2_LOCAL                Q1                            TO_DATE(' 2012-03-30 USABLE

                                                              00:00:00', 'SYYYY-M

                                                              M-DD HH24:MI:SS', 'N

                                                              LS_CALENDAR=GREGORIA

 

INDEX_TT2_LOCAL                Q2                            TO_DATE(' 2012-06-30 USABLE

                                                              00:00:00', 'SYYYY-M

                                                              M-DD HH24:MI:SS', 'N

                                                              LS_CALENDAR=GREGORIA

 

INDEX_TT2_LOCAL                Q3                            TO_DATE(' 2012-09-30 USABLE

 

INDEX_NAME                    PARTITION_NAME                HIGH_VALUE          STATUS

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

                                                              00:00:00', 'SYYYY-M

                                                              M-DD HH24:MI:SS', 'N

                                                              LS_CALENDAR=GREGORIA

 

INDEX_TT2_LOCAL                Q4                            TO_DATE(' 2012-12-31 USABLE

                                                              00:00:00', 'SYYYY-M

                                                              M-DD HH24:MI:SS', 'N

                                                              LS_CALENDAR=GREGORIA

 

INDEX_TT2_LOCAL                Q_OTHERS                      MAXVALUE            USABLE

 

从上面查询可以看出他和表是equi-partitioned.

 

 

 

第三种情况:

如果查询根本就没有createdate,而是有像where id>100的条件,则就只能在ID列上建立GLOBAL索引了

create index index_tt3_global on TT(id)

global partition by range(id)

(

 partition p1 values less than (100000),

 partition p2 values less than (200000),

 partition p3 values less than (MAXVALUE)

);

 

从上面可以看出,GLOBAL的索引的分区数和其基本是没有关系的。他甚至可以想下面一个建立索引,即一个普通索引。但是LOCAL的必须和其基本分区数一致。

SQL> create index index_tt3_global on TT(id) global;

 

Index created.

 

 

总之,一般建议建立LOCAL的索引,因为GLOBAL的容易所有的都失效,而LOCAL的最多只在某个分区上失效。索引失效必须一个分区的一个分区的REBUILD

 

 

 

 

索引组织表(IOT表)

 

堆表,即表上的数据是无序的。比如我想在一堆糖中找某块糖,那我需要把所有的糖都翻一遍。

索引相当于目录。索引是通过键值和rowid,到全表里面找数据。所以走索引时需要两次I/O.

 

 

因为走索引是两次I/O,为了解决这个问题,用IOT

IOT表(索引组织表)是一次I/O

索引组织表就是相当于把糖豆(即数据)直接粘在目录(索引)上,直接在目录(索引)就找到糖豆(数据)了。

 

 

IOT表的本质就一句话:消除索引的回表访问。

消除回表访问有两种手段:

         第一种:如果列在三个以内,可以考虑复合索引;

         第二种:可以使用iot,但是iotupdate,iot表的索引没有物理的rowid,只有逻辑的rowid因此iot表以主键为条件的查询很适合iot表查询条件不能换,如果换条件性能一下子就降下来了。但是想在iot表上其他列建索引,可以用mapping表映射一下,即逻辑rowid映射到物理rowid就行了。

 

索引分两种:

第一种b-tree

 

叶级块:里面含有键值和rowid

复合索引不能超过3个字段。

 

IOT表就记住一句话:消除回表访问。

回表访问就是根据键值访问完索引后,再根据rowid去表里面访问。

 

IOT表的叶级块里面存放了键值和那行真实的表数据。这样就会出叶级块放不下的情况。放不下怎么办呢?IOT表会把一些最关键的列(比如有20%关键的列)和索引列放在叶级块,而剩下的列放在溢出表空间(访问溢出表空间也会有I/O。我本来用IOT表就是想消除回表访问,消除I/O,但是用了IOT表虽然消除了回表访问,但是增加了溢出表空间的访问,这种利弊就需要DBA来权衡了)。

 

IOT表必须以主键为条件查询,并且查询时不能换条件(不能换列),否则性能非常差,不如全部扫描。注意:索引是不存空值的IOT表也不能update

 

为什么IOT表查询时要以主键为条件查询时,其一次I/O就够了,但是如果查询时换字段效率会低呢?因为iot表的叶级块记录的是键值和表的数据,没有rowid,所以,如果换条件,没有其他字段rowid。所以要想在IOT表上除了主键外的字段建立索引,就需要加mapping table 关键字(即逻辑rowid映射到物理rowid)。具体见创建IOT表部分。

 

 

IOT表其实是一个歪着(不好的办法)。我们说访问索引是两次I/O,这只是简单的说法,其实访问索引也是有很多次I/O的。索引访问IOTI/O会很多,跳来跳去的。

 

IOT表只应付ocp考试,工作中慎用。

 

创建IOT

首先创建两张普通表:学生表和课程表。

上面分别创建了一张学生表和一张课程表。这两张表是多对多的关系。

 

题目要求:将上述两张表创建一个对应关系表。

我们先到官方文档里面看看索引组织表的语法:

 

上图中的pctthreashold 20表示20%的表中数据放在索引的叶级块里,其余的放在溢出表空间admin_tbs2

Overflow tablespace就是溢出表空间的意思。

下面创建IOT表:

需要加主键约束和organization index关键字段:

外键可以不加。

为什么IOT表查询时要以主键为条件查询时,其一次I/O就够了;但是如果查询时换字段效率会低呢?因为iot表的叶级块记录的是键值和表的数据,没有rowid,所以,如果换条件,没有其他字段rowid。所以要想在IOT表上除了主键外的字段建立索引,要加mapping table 关键字(逻辑rowid映射到物理rowid的意思)。

 

 

Mapping是映射的意思。Mapping 表是没有物理rowid,但是它有逻辑rowid。它可以把物理rowid和逻辑rowid映射一下。

 

总体来说,iot表慎用。

 

Mapping表的视图

 

簇表

簇:就是一家人住在一个屋檐下。

 

 

簇可以分为索引簇和hash簇。

 

 

 

看到这两张表都要进行一次I/O,所以合起来至少要两次I/O才能得到所要的结果集。

 

我们创建一个索引簇表,就把两张表的数据根据deptno放在一个数据块中,这样读取数据时一次I/O就可以得到所要结果集。

 

 

 

什么时候使用簇表呢?一般是把同一个键值的数据往一个块里面放,就可以使用簇表。

这样两个表的数据在一个块中,一次I/O就可一得到结果集。

 

 

 

一般多表连接用索引簇,范围查询也用索引簇,单表用索引簇和hash簇都行。

如果是类似于deptno=10这样簇列,用hash簇比较好。

对于经常update键值,就不要使用任何簇表。但是如果有少量的update,使用索引簇更好。

 

 

 

 

 

 

Index cluster

 

 

两张表经常做连接,并且连接字段不经常做update,或者范围查询,就可以建立索引簇。这样两张表相同的数据存放在一个数据块上,一次I/O就够了(以前是需要两次I/O)。

等值连接时(比如deptno=10)就用hash簇。

 

 

小知识:

官网上创建索引簇的语法:

下面开始建立索引簇表:

1、  首先建立簇键(即连接字段):

上图中size 600表示簇键的大小是600,这个值有两种理解方式:第一种理解就是600表示一行信息的长度,按照这个观点,假设一个块是8K,那么一个块可以放8k*1024/600行数据,其实这种说法不正确;第二种理解方式建hash簇的时候,size 600代表一个数据块用多少字节来存放数据。假如一个数据块是8k,平均行长是40,那么600字节可以存放600/40行数据。

####################################################

小知识:

上面的size 600是怎么算出来的呢?

 

簇表中一个数据块存两张表的相同数据,在一行中,前半部分存放的是emp表的数据,后半部分存放的是dept表的数据。

我们知道,emp表的平均行长是40字节:

 

 

dept表的平均行长是23字节

 

40+23=63,每个部门大概有10个人,所以size设置成600就行了。

注意:size大小必须设置,否则创建的簇表一个数据块只存放一行数据。

 

#################################### ##################################

 

2

 

上面看到报错了。解决方法:dept表列的信息一定要和簇键的信息要一致。

 

更改后:

 

2、   

4、最后再建立簇索引

 

下面我们插入数据:

现在我们对于索引簇表和原来表执行计划的区别:

 

原来表的执行计划:

大家看到原来表的访问成本是7+7+3+3=20

 

下面测试使用簇表的执行计划:

 

上图看到走的是簇扫描和簇索引。代价比以前的低了。这里可能是数据量少,所以看不出这两个成本的差别。

 

下面我们用rowid来访问对比一下:

 

 

rowid中,前十五位相同的就是同一个数据块。

上图看到,从w前面的都相同,说明是同一个数据块。表示empdept表的数据存放在了同一个数据块里面。这样再做关联查询的时候,在一个数据块里面、一次I/O就把数据给查出来了,从而提高了性能。但是使用簇表也是有限制的,比如一个部门的人特别多,存放数据可能会超过三个数据块了,那么我们就不应该再考虑簇表了。

 

 

 

hash

 

 

实验:

 

 

 

select table_name,tablespace_name,blocks,pct_free,pct_used,avg_space,avg_row_len from dba_tables where table_name='EMP_HASH';

 

大家看到,这个表的行平均长度是40字节。

下面我们先建立hash簇键:

 

上图,以deptno做为簇键,表示把同一个部门的人放在一个数据块。hashkeys 10表示我估计将来最多会有几个部门,

 

下面我们再建立簇表:

 

下面我们来看10号部门的rowid

 

我们知道,如果rowid15位相同,那么就是表示在一个数据块里面。如上图看B前面是否相同。

上面我们看到10号部分在同一个数据块里面。

 

簇表就是在一个数据块里面,一行值存了两个表的数据,在这一行中,前半部分存放emp表的数据,后半部分存放的就是dept表的数据。

 

下面我们再看20号部门是否在同一个数据块里面:

 

 

我们会看到20号部门有很多行不在同一个数据块(实验观察20部门的数据放在了3个数据块中)。

 

下面我们再看30号部门是否在同一个数据块里面:

 

 

 

结果我们看到30号部门的数据也放在了三个数据块里面。

那么下面我们来分析为什么会放在三个数据块里面呢?

 

下面我们来看各部门人是怎么分布的:


 

 

上面几张图看到了每个部门的人数。

 

我们知道,scott.emp表一个数据块可以存放170行数据。

所以,上面的96160是可以存放在一个数据块里面的;192肯定不能存放在一个数据块里面。

 

好了,现在我们已经分析到这里。

下面我们把簇表删除,重新再做一遍。

 

 

重新建表:

 

 

下面建立簇键:

现在我们把前面的size 4000改成size 1000,其他不变。

 

下面再建立一次簇表:

 

 

再看10部门数据存在几个数据块:

 

发现10号部分存在了三个数据块里面。

 

下面再看20号部们存在几个数据块里面:

 

 

发现20号部门也放在了3个数据块里面。

 

下面再看30号部门的情况:

 

 

看到30号部门的数据也存放在了3个数据块里面。

小知识:rowid15位相同,表示的是同一个数据块。

 

好了,下面我们删除后再做一次实验,不要嫌麻烦,这是你进步的阶梯:

 

 

 

size变成8192

 

再建立簇表:

 

测试:

发现10号部门的数据在一个数据块里面。

 

发现20号也在一个数据块里面

 

 

发现30号部门放在两个数据块里面(这个是肯定的,因为30号部门的数据行数超过170行了)。

注:一个数据块可以放170行数据(因为平均行长是40)。

 

 

总结:我们通过上面的实验看到,当size 设置为4000时,10号部门在一个数据块里面,2030号部门的数据在多个块;当size 设置为1000时,102030号部门的数据都不在一个数据块里面;当size=8192时,1020号部门都在一个数据块,而30号部门不在一个数据块。

分析:

先看每个部门的人数:

 

 

正常来说,1020号部门的数据可以放在一个数据块里面,而30号部门的数据需要放在两个数据块里面。

size 设置为1000时,表示一个8k的数据块里面只能用1000字节的空间来存数据,1000/40(平均行长)=25,表示一个数据块里面尽量放25行(我们知道一个数据块里面能存170行数据),这样一来,系统就把10号部门的96行数据放在了三个数据块里面。

 

size=8192时,表示一个块能放多少数据就放多少数据。1020号部门都在一个数据块,而30号部门不在一个数据块(因为30号部门数据超过170行了)。

 

size 设置为4000时,10号部门在一个数据块里面,2030号部门的数据在多个块.这是因为4000表示一个8k的数据块拿出4000字节来存放数据,假设平均行长是40字节,那么一个数据块就只能存放4000/40=100行数据,所以10号部门可以放100行,其他两个部门的数据一个块放不下。

 

 

一般键值不是设置越大越好,比如把size 设置成8192,那么一个数据块存放数据会很慢,这样也会有问题,有维护方面的问题。所以,我们尽量将一个块里面不要存放太多行的数据,尽量分散开。比如我估计每个部门大概有100人,那我设置size=100*40=4000就行。

 

rowid

 

 

其实一个rowid就是身份证号,身份证号是18位,rowid也是18位(6363表示,6是对象号,3是文件号,6是块号,3是行号)。

 

下面两个查询分别看对象号,文件号,块号:

 

 

下面我们详细解读rowid

select rowid from scott.emp;

 

 

 

 

上图中,一个区是8个块所以有9+8=17,17+8=25,25+8=33,33+8=41,41+8=49

所以块号是31的数据块在数据块起始block_id25(块起始id)的emp表中。这句话有点绕,希望你能明白。

 

行号是7

 

 

 

 

 

 

 

 

 

 

 

 

 

复合索引和压缩索引

关于联合索引的部分,你也可以跳转到本文的复合索引进行阅读。

上图的compress 2表示是对job列的索引进行压缩,因为job列是有重复值的,对其索引进行压缩以节省空间。但是解压的时间会长些。

 

 

直接写compress表示对两个字段都压缩。

 

小知识:一般来说,压缩可以节省空间,但是解压时会稍耗一些时间。综合考虑来说,节省空间利益更大一些,解压时间长可以忽略。

 

物化视图

 

 

ocp中簇表不考,但ocp会考物化视图、iot表、分区表。

 

再说物化视图前,先想一下什么是视图?

视图就是一张虚表,它不占用存储空间,是数据字典中接入的一条查询语句。视图就是一条查询语句,使用视图时实际上在执行那条sql语句。

物化视图是把查询结果真正的存储起来。而视图只是一条查询语句,下次用的时候还得再执行视图所对应的查询语句,所以视图查询耗费性能。但是我们把结果放在一个表里面(就是物化视图),这时候就可以提高性能。物化视图占用存储空间。

 

物化视图分两种情况:

1、物化视图可以是远程数据的本地副本。我们知道跨库查询走网络,性能会低。如果我把远程数据在本地存一个副本(这就是物化视图),我们查询本地的数据(物化视图)就比直接查询远程的数据快,并且本地的副本和远程数据时时刻保持同步的。

         2、物化视图也是把一个查询结果(比如汇总)保存在一个表里面,并保持和基表的同步。

 

dblink非常简单,但是又非常有用,它可以跨库查询。

 

查询重写:当在执行SQL语句的时候,当你有表,也有物化视图,优化器就会判断是查表快,还是查物化视图快。如果优化器觉得物化视图快,它就会把这个查询语句重写一下(以前SQL语句写的是查某张表from table,开启查询重写后就改写SQL为查询物化视图from mv…),不是去查表,而是查物化视图。

 

 

 

 

on demand:不靠谱,数据库觉得有必要(什么是觉得有必要?)才去更新基表和物化视图,所以一般不用on demand,而是用on commit

 

fast刷新需要用物化视图记录数据的变化,每次是刷新同步变化的数据。

不要迷信物化视图。

 

物化视图也有个物化视图日志。物理视图其实挺好,但是也不要大量使用,也有可能会有bug.

 

 

 

我们来看一下物化视图在官网的哪个部分?

物化视图是属于数据仓库的内容:

 

 

 

单表物化视图

下面我们做一个物化视图的实验:

 

 

using 后面是tnshr是用户名

 

count(*)是为了加分组group by去重。

 

 

查询系统权限:

 

下面建立单表的完全刷新的(即更新时先truncate物化视图再insert,不需要日志)物化视图:

查看一下我们建立的物化视图:

 

下面我们更新一下基表:

 

 

再次查看物化视图是否有变化:


 

 


 

 

 

上面我们演示的物化视图的完全自动刷新。

 

下面我们用包来手工刷新:

c是完全刷新complete的意思,f是快速刷新fast

 

上面我们讲的是完全刷新的例子(即刷新时先truncate物化视图,再insert

 

两个表的物化视图

下面我们再举一个快速刷新的例子,同时也是两个表的物化视图。

 

 

上述的查询语句要注意,只要有某个字段的聚合函数(如sum),就要有这个字段的count

 

 

下面我们准备把下面这个sql语句的查询建立一个物化视图。

 

select e.empno,d.deptno,sum(e.sal),count(e.sal),count(*) from scott.emp e,scott.emp d

where e.deptno=d.deptno group by e.empno,d.deptno;

 

把上面的sql语句建成物化视图:

 

物化视图需要根据物化视图日志来看基本的变化。

 

单表物化视图需要建立一个物化视图日志,两张表需要建立两个物化视图日志。

 

先建立两个表的物化视图日志:

 

下面开始建立两张表的物化视图:

 

上述的enable query rewrite可以省略不写。

 

我们查看一下我们建立的物化视图:

 

我们更新一下基本:

 

 

测试发现物化视图信息变化了:

 

物化视图快速刷新模式,被修改的行总是会放在物化视图的最后。

快速刷新只刷新变化的数据,它实现的方法是先把那行变化的行delete掉,然后再insert进去,所以会在最后。

 

下面我们再用完全刷新一次,来和快速刷新做对比。

 

c就是完全刷新的意思。f是快速刷新。

下面我们验证一下:

 

看到完全刷新,变化的行跑到物化视图的其他位置了。为什么呢?

完全刷新是先把表truncate,然后再insertinsert时是按照顺序来放的

 

下面我们更新一下物化视图,发现不可更新:

上面的物化视图是只读的。下面讲可更新的物化视图。

创建跨库并可更新的物化视图

物化视图也有双向更新的,即更新基表,物化视图更新;当更新物化视图,基表也更新。这样的物化视图配置起来很复杂,有兴趣的可以到网上看看。

 

好了,废话不多说了,我们开始做一个跨库且可更新的物化视图。这样就是本地有两个表做关联,本地有两个物化视图,远程数据库也建立一个物化视图。

我们先做一个如下查询:

将上面的表做一个可更新的物化视图:

先建立物化视图日志:

 

 

物化视图更新时很怕更新重复,所以上述加主键约束避免这种情况;另外,加了主键约束后,就不用再一一把全部列写上了,因为写了主键约束后,数据库就知道是要写表的全部的列。

 

在第二个数据库建立如下物化视图:

在第二个数据库对物化视图进行update

 

看到现在的物化视图mv_tm3可更新。

 

 

但是我们在第二个数据库查看基表信息是没有变化的:

 

我们刷一下基表和物化视图就会同步了。

 

 

看到物化视图又变回salary1000了。

 

 几种扩展

 

 

 

 


 

 

oltpolap的对比

 

 

oltp:每天的事情很多,但都是小事。oltp要求快速响应。

olap:每天事不是很多,但是每个事的事务量很大。数据仓库不一定要求快速响应(但也别慢的太离谱)。

oltp sql重用率很高,我们要降低硬解析,所以非常适合绑定变量;oltp dml语句多。

olap系统sql重用率很低,很少做update操作。一般都是把数据insertoltp系统,然后做大量的select查询。最后再把表truncate了。

 

 

我们的目标是没有蛀牙

 

 

 

注意:索引是一把双刃剑,它可以提高查询的速度,但是会降低dml操作的速度。

 

 

 

 

上面第五条说的小的事务使用小的回滚段,大的事务使用大的回滚段,想实现这样,回滚段一定是使用的手工管理。

 

 

 

 

 

 

注意:对于oltp系统,一张表不要创建太多的索引,最多5个,最好不要超过10个。因为索引会降低dml操作的速度。

 

 

 

OLAP系统中,对于全表扫描,可以调整db_file_mutiblock_read_count(一次读取的块数),一次读的数据块越多,读的数据也就越多,从而全表扫描的代价就会减少。但是,如果你把全表扫描的代价减少了,那么就会把全表扫描和走索引的平衡打破了,所以还要调一个just的参数,这个参数是靠经验来调整的。

OLAP系统中,如果数据块设置的大些,全表扫描的代价也会降低,因为每次读的数据就会多一些。

 

OLAP系统,cursor_sharing设置为exact合适。

 

OLAP系统中很少做update操作,大多数都是在做truncateinsertselect,所以对于olap系统可以建立多个索引。

 

其他:如果我的数据库既是OLTP系统也是OLAP系统(混合型数据库),那我们就按照oltp来调就行了。

 

 

 

 

有效使用数据块

 

监控段的使用情况

 

为了有效使用数据块,我们要经常监控段、区、块的状态。

 

 

 

 

select

dbf.tablespace_name,

dbf.totalspace "总量(M)",

dbf.totalblocks as "总块数",

dfs.freespace "剩余总量(M)",

dfs.freeblocks "剩余块数",

round((dfs.freespace/dbf.totalspace)*100,2) "空闲比例"

from

   (select t.tablespace_name,

       sum(t.bytes)/1024/1024 totalspace,

       sum(t.blocks) totalblocks

       from dba_data_files t

       group by t.tablespace_name) dbf,

   (select tt.tablespace_name,

       sum(tt.bytes)/1024/1024 freespace,

       sum(tt.blocks) freeblocks

       from dba_free_space tt

       group by tt.tablespace_name) dfs

       where trim(dbf.tablespace_name)=trim(dfs.tablespace_name);

 

 

select owner,table_name,blocks,empty_blocks

from dba_tables

where empty_blocks/(blocks+empty_blocks) < .1;

 

上图以$结尾的是数据字典的基表,看到数据字典的基表空闲块也少于10%,这个不用管。

我们只管我们人建立的表空闲块少于10%就行了。

 

我们在undo调优中说过,为了减少等待事件,我们尽量减少临时创建或者扩展undo段,因为创建或者扩展undo段会造成等待事件。

那么数据段也是这样,尽量减少段的扩展,因为段的扩展,在申请空间需要时间的等待。

所以我们要监控空间少于10%的段,如有,我们可以手工分配段的空间。

 

对于区的管理,我们尽量使用本地管理,原因是区的本地管理可以消除数据字典的更改,而造成的串行化过程;另外本地管理也产生undo数据、redo增加。

 

 

 

实验:

 

 

上图中我们看到emp表有5个数据块,空块是3个。

5+3=8。也就是系统给这个emp表分了一个区,这个区里面用了5个数据块,还有3个数据块从来没用过,这3个块存在高水位以上。存过数据的数据块哪怕它现在没数据,现在都会在高水位线下面。像在这样的情况下,如果你发现这张表的空块比例非常少,少于10%,系统会可能给它分配空间,也就是增加新的区,扩展段,这样会消耗性能。为了消除这个性能,我们可以手工分配空间。

下面我们就来手工给分配空间:

 

 

报错的原因是薅羊毛不能在别人身上薅(因为emp表在users上,不在system上)。

 

 

这样,我们就会看到emp表有足够的空间了。

 

当然,如果我们想要回收空间,如下:


 

 

看到空间被回收了。

 

如果你想保持高水位线上面空块的个数,不动态扩展空间,可以写脚本。

 

扩展数据文件的脚本

 

 

 

 

 

 

vi /home/oracle/prod_bak/extend.sql

 

set echo off trimspool off heading off feedbackoff verify off time off

set pagesize 0 linesize 200

define bakcp='/home/oracle/prod_bak/extend_cmd.sql'

set serveroutput on

spool &bakcp

declare

         cursor cu_tablespace is

                   select tablespace_name from dba_tablespaces

                   where contents not like 'TEMP%' and status='ONLINE';

         cursor cu_datafile(name varchar2) is

                   select file_id from dba_data_files

                   where tablespace_name=name;

begin

         for i in cu_tablespace

         loop

                 for j in cu_datafile(i.tablespace_name)

                   loop

                            dbms_output.put_line('alter database datafile '||j.file_id||' autoextend on next 10m maxsize unlimited;');

                   end loop;

         end loop;

 

end;

/

spool off

@&bakcp

 

 

执行后,会得到类似如下的脚本:

 

 

 

大区的好处

 

 

 

 

 

select segment_name,segment_type,tablespace_name,extents,max_extents from user_segments;

 

 

上面说,大区可以消除操作系统对文件大小的限制,这句话纯属瞎说。

max_extents:每个段都有最大区个数的限制,这个值就是max_extents所示。通过上图可以看到这个数字的值已经非常大了。

不过,“大区可以消除操作系统对文件大小的限制”这句话可以这么理解,区大了,存的数据就会多一些,从而减少了文件大小的限制。

 

 

另外,如果区大一些,那么区的数量就少一些,那么我们管理区的代价就小一些。

 

 


 

上面说的server指的是server process

 

如果区很大,那么碎片就会有很多。

 

FAT32格式:最大单个文件大小是4G

 

 

监控区的使用情况

 

 

select table_name,tablespace_name,blocks,empty_blocks,num_rows,avg_space,

avg_row_len,chain_cnt from dba_tables where owner='SCOTT' and table_name='EMP';

 

 

大家知道为什么我们要回收高水位线下面的空间吗?因为高水位线是全表扫描的终点。所以,我们为了降低全表扫描的代价,就把高水位线往下降。

 

大家注意一下上面说的第一点,千万不要把表导出,然后把表干掉在导入,这样做很危险,而应该是把表导出,再对原表进行rename,然后再导入。切记。

 

 

另外,上面回收高水位的方法有三种:但最好用第三种,为什么呢?

我们先看第一种,我们把表导出和导入的过程中,可能会有人在访问这张表,这个时间可能会很长的,所以会有问题的;

第二种情况,表在移动到其他表空间,这个表会被加锁,影响使用。

而第三种情况,比较好。

 

其实上述三种情况用哪个都行。

 


 

 

 

 

 

 

高水位线

 

 

 

 

moveshrink的区别

 

注意,以下内容老师没讲,有兴趣可以自己到网上查看相关资料。即moveshrink

 

 

 

 

1. move时产生的日志比shrink时少.参看http://blog.csdn.net/huang_xw/article/details/7016365

 

2. shrink在移动行数据时,也一起维护了index上相应行的数据rowid的信息,当然shrink过程中用来维护index的成本也会比较高。而表moveindex的状态是UNUSABLE,需要进行rebuild。参见http://blog.csdn.net/huang_xw/article/details/7016415

 

3. oracle是从后向前移动行数据,那么,shrink的操作就不会像move一样,shrink不需要使用额外的空闲空间。

4.shrink需要开启行迁移

5. 当执行了shrink操作后,可以发现shrink操作与move不太一样。在move操作的时候,所有行的rowid都发生了变化,table所位于的block的区域也发生了变化,但是所有行物理存储的顺序都没有发生变化,所以我们得到的结论是,oracleblock为单位,进行了block间的数据copy。而在shrink后,部分行数据的rowid发生了变化,同时,部分行数据的物理存储的顺序也发生了变化,而table所位于的block的区域却没有变化(ID156rowid没有发生变化,ID910两行数据,原来在AAAAEnAAAAEo上都移到AAAAEk上)。以上说明,shrink只移动了table其中一部分的行数据,来完成释放空间,而且,这个过程是在table当前所使用的block中完成的。

 

 

 

SQL> analyze table emp2 compute  statistics;

 

Table analyzed.

 

SQL> select table_name,tablespace_name,blocks,empty_blocks from dba_tables where table_name='EMP2';

 

TABLE_NAME                     TABLESPACE_NAME                    BLOCKS EMPTY_BLOCKS

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

EMP2                           USERS                                  53            3

 

 

要使用assm上的shink,首先我们需要使该表支持行移动,可以用这样的命令来完成:

SQL> alter table emp2 enable row movement;

Table altered

现在,就可以来降低my_objectsHWM,回收空间了,使用命令:

SQL> alter table emp2 shrink space;

Table altered

 

 

SQL> analyze table emp2 compute  statistics;

 

Table analyzed.

 

SQL> select table_name,tablespace_name,blocks,empty_blocks from dba_tables where table_name='EMP2';

 

TABLE_NAME                     TABLESPACE_NAME                    BLOCKS EMPTY_BLOCKS

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

EMP2                           USERS                                  35            5

 

看到高水位线从53降到了35个数据块

 

 

 

 

索引进行rebuild

 

 

减少数据块的访问数量

大小块的对比

 

 

使用大的数据块

索引适用使用大的数据块,因为大的块一次性读取数据会多一些,从而效率也会高。

 

分配大块需要分配大的缓冲区。

 

记录存放的更紧密

鱼和熊掌不可兼得。

 

放的密,数据就能多放一些,但是会导致行链接和行迁移。

至于什么是行连接、什么是行链接,见减少行迁移部分的“行迁移和行链接”。

 

减少行迁移

数据块

 

一个数据块分为三个部分,块头、空闲部分、数据部分。

 

 

 

 

pctused and pctfree

 

 

 

 

上述第三种,数据在40%80%之间时,是不能重新插入数据的,因为数据空间大于pct_used,在空闲列表中找不到该块,也就是说现在该块还不是空块。

 

行迁移和行链接

 

 

 

 

数据发生行迁移后,rowid不变。

 

 

 

 

pct _free是从上往下;pct_used是从下往上。

所以说,假如pct_free 设置为10%,就是从上往下预留百分之10的空间;pct_used 40%就是指从下到上40%的空间。

 

pct_free预留的小,越容易引起行迁移;

 

刚才讲完了行迁移,那么什么是行链接呢?

 

行链接

 

一行数据要插入到一个数据块(注意,这行数据原来不在这个数据块里面),如果这行数据在这个数据块里面放不下,那么这行数据就会放在很多的数据块里面,但是为了表示这是一行数据,我们用铁链子(chain)把它给拴起来就行了,这就是行链接。

 

那么,什么时候会发生行链接呢?

pct_used 很高的时候,如果我此时数据略低于pct_used,那么这个数据块是在空闲列表中的,表明此块可以插入数据。不过这个数据块插数据的话,只能放一点点,如果这行数据很大,那么这行数据就会发生行链接(也就是说这行数据需要放在多个数据块中)。

一行数据放在好几个块中,rowid就会变。也就是说发生行链接,rowid会变。

 

总结:pct_used 是为insert 预留的;pct_free是为update预留的。

           如果pct_used设置的太高,就会引起行链接;如果pct_free设置的太小,就会引起行迁移。

           发生行迁移时,rowid不变;发生行链接时,rowid会变。

 

ITS(事务槽)

 

select table_name,tablespace_name,blocks,ini_trans,max_trans from dba_tables where table_name like 'EMP%';

 

 

 

select index_name,index_type,table_name,tablespace_name,ini_trans,max_trans  from dba_indexes where owner='PINPAIADM';

 

 

pct_free pct_used 的计算

 

 

 

select table_name,tablespace_name,blocks,pct_free,pct_used,avg_space,avg_row_len from dba_tables where table_name like 'EMP%';

 

 

 

 

上述介绍了pct_freepct_used的计算方法,但其实没必要算,因为建库时有默认的pct_freepct_used,如下:

 

select num_rows,pct_free,pct_used,avg_space,chain_cnt,avg_row_len from dba_tables where table_name='EMP';

 

大家注意:段空间手工管理的时候,用的是freelist,这时候pctusedpctfree都有。

但是段空间自动管理(assm)时只有pctfree,没有pctused(其实这个pctused值被四个域值取代:25%25%~50%50%~75%、大于75%),所以,在assm中,我们只调pctfree就行了。

 

实验(行链接和行迁移)

 

 

select table_name,tablespace_name,num_rows,avg_row_len,chain_cnt from dba_tables where table_name='EMP1';

 

上面的chain_cntcnt就是count的简称。chain_cnt表示行迁移和行链接的和值(也就是这个值包括行迁移的个数,也包含行链接的个数)。

 

 

看到发生了行迁移(chain_cnt值为701)。

 

检测行迁移和行链接

 

 

消除行迁移

方法有四:1、导入导出;2、移动到其他表空间;3、拷贝行迁移的记录;4、创建一张空表,把生产表的数据插入这张空表,然后再把生产表renameold,新建的表rename成生产表(这种方式不错,推荐使用)

 

 

上面跑完utlchain脚本后,会创建一张chained_rows表,该表用来存放发生行链接和行迁移的值。

 

 

 

 

chaint_cnt0 ,表示没有行迁移。

 

 

知识点:上面我们用拷贝行迁移记录的方式来消除行迁移,这种方式适合大表。但是这种方式缺点是中间有个delete生产表的操作,如果在delete时有用户在select数据就会有问题。

 

 

索引的重建

 

 

索引的监控

索引监控的目的:我们前面曾经说过,一个表索引最好不要超过5个,至多不要超过10个。所以,我们就可以监控哪些索引从来都没有用过,没用过的索引我们就可以删除,从而减少索引的数量。

 

 

select * from v$object_usage where index_name='I_EMP1';

 

打开索引的监控:

lter index i_emp1 monitoring usage;

 

注意上面的use字段,如果为yes表示表示走索引,no表示没有走索引。

 

use字段还是no,看来我们的select查询没有走索引。你可以使用hints让查询强制走索引看一下,会发现上面的use字段变为yes了。

 

下面我们来关掉索引的监控:

 

 

看到产生了关于索引监控的时间了。

 

 

对索引碎片的整理

 

 

coalesce[[?k????les]]:联合,合并

 

对索引update或者delete多了以后,有的索引块就不满了,可以通过整理索引碎片的方式来把不满的索引块整理成一个数据块。

 

对于coalesce这个单词的记忆方法:扣下a片,乐死色狼。

 

我们的目标是没有蛀牙

 

下面是回收高水位下面空间的方法:

如何回收高水位线上面的空间:alter table unused…..

 

 

 

 

 

体系结构

PGA

 

数据库启动三阶段

 

 

 

数据库启动分为三个阶段,这三个阶段共干了五件事:

1、  nomout阶段:初始化参数文件,生成实例(即分配sga内存和启动后台进程);

2、  mount阶段:读取控制文件。控制文件相当于工作卡(记录我是谁,我有什么财产)。

 

控制文件记录了数据库的物理状态(日志文件和数据文件的位置、检查点、备份信息)和维护了数据库的一致性。控制文件本质是数据库的记忆。

 

3、 open

检查数据库的一致性:即检查数据库如下三个scn号要一致:

 

a、数据库的scnv$database.checkpoint_change#:

在全局检测点发生之前,数据库的scn号(v$database.checkpoint_change#)是不变的,但是数据块的scn(即v$database.current_scn,不要被其名字所骗,它并不表示数据库当前的scn号,而是表示数据库中当前数据块中最大的scn号)是在变化的,所以数据块的scn号要比数据库的scn号大。当发生全局检测点时,数据库的scn号就会变成current_scn号(即数据块scn号)。

 

 

 

 

 

 

b、控制文件scn(v$datafile.checkpoint_change#)

 

 

 

c、数据文件scnv$datafile_header.checkpoint_changer#

 

 

 

 

 

 

SQL应用调整

 

调优就是给数据库看病。

 

很多人给sql调优,看了半天执行计划,也没抓住要点。

SQL调优占调优的1/4

优化器模式optimizer

优化器optimizer_mode是数据库的大脑,它才会思考。执行计划就是由优化器产生的。

 

执行计划就是一个路径图一样,即先干什么,后干什么,先走哪,后走哪。

 

 

 

 

 

first_rowsall_rows都是基于cbo的。

first_rows是取表的前n行数据做统计信息,这个统计信息并不一定代表整张表数据的统计学,所以不准;

all_rows是取表的所有数据来做统计分析,这个比较精确些。

 

 

稳定执行计划

 

 

解锁用户、赋予权限

 

 

 

创建outline,并固定执行计划

 

 

 

 

现在我们固定执行计划:

 

outline用户的状态必须是打开的,否则用hint是固定不了执行计划的。

 

outline就是大纲的意思。

下面语句意思是:创建一个目录(category [?k?t?g?ri])叫cuug,把当前的执行计划用大纲记录下来。

 

下面语句含义是我当前session确定使用这个cuug的执行计划。

 

小知识:如果上面不写for category cuug这三个单词,表示的大纲使用默认的目录;这时create_stored_outlines=true

 

 

 

 

注意:查询要在同一个session里面看。

看到现在即使创建了索引也不走索引了。

 

 

 

sql分析工具

 

statspackadvisory

 

上面表示有一个匿名块,消耗了很多的资源。

 

如何读懂执行计划

 

 

 

 

 

 

rebuild rebuild online的区别

有这么一个场景,有一个表没有建立索引,你发现这条SQL语句执行消耗资源很高。于是,你要在白天,通过parallelrebuid建立索引,那这么做会产生什么后果呢?

答:白天用parallelcpu资源会瞬间耗尽,所以千万别在白天用parallel,而是在晚上空闲时间再用parallel;而且也不要在白天建立索引,应该是自己写个job在晚上建立索引;另外建立索引千万不要写rebuild,而是要写rebuild online

 

rebuildrebuild online的区别:两个区别是非常大的,可以说是天上和地下,现分别阐述之。

rebuid:只扫描索引,不扫描原来的表数据,所以rebuild的优点就是建索引快,但是由于索引和表要保持一致,所以在用rebuild建索引的过程中,会把表的数据锁定,因为锁定表数据以后才能保持表和索引的一致性。如果表的数据被锁住,那么这个时候其他人就不能对表做dml操作了,因此,rebuid这个命令一敲下去,所有人的业务dml操作就全部失败。

rebuild online:扫描的是表数据(全表扫描),所以rebuild online的缺点是建索引慢。但是它的优点就是不锁定表数据,在建立索引的过程中别人可以做dml操作。我们知道,我们在建索引的过程中,可能会有人对表数据做dml操作,那么rebuild online是怎么保持索引和表一致性的呢?其实rebuild online的运行原理是这样的:1、先根据全表扫描建立索引(在根据全表扫描建立索引的过程中,会有一个日志记录在建立索引过程中对数据做的dml操作);2、当根据全表扫描建立索引完成后,再根据刚才的日志调整一下索引。

所以建索引要用rebuild online,不要只用rebuild

 

表连接和索引的优化

 

nest loop

大家注意,不是说有表连接就需要优化,而是当我们跑完报告,找到最耗性能的语句进行优化。

 

 

 

 

 

找对象以什么为关联字段,最好以同省的进行连接。

 

如果是100个男匹配10个女,那么就需要匹配100次,即需要100I/O

如果是10个女(驱动表)匹配100个男(被驱动表),那么只需要匹配10次就行了,即10I/O

综上所述,驱动表和被驱动表的顺序,对性能有很大的影响。

 

 

实验:

 

 

 

 

 

cost求和来看。

 

recursive calls:递归调用,次数越少越好。

consisten gets:逻辑读,即读缓冲区的数据,逻辑读越少越好;

physical reads:物理读,即磁盘读,物理读越少越好。

 

sorts(memory):排序区不一定在pga中,也可能会在uga

sorts(disk):磁盘排序

 

在开发的过程中调优是最好的,不要等到出了问题再解决。

本文是以开发的角度来进行SQL调优。

 

 

 

 

 

 

 

 

 

hash join

 

hash的含义:通过hash运算,生成一个值。例如15取模是1,65取模是1。这样就使数据通过hash运算以后就相对有序,但是数据本身并未排序。这样两张表也各访问一次。

 

sort merge join

 

 

 

排序有时候还是可以提高性能的,比如上图的sort mergeT1表的A列范围是17T2A列是19,这样范围都比较小的情况下,都各自排序然后进行等值匹配,其性能代价是低的。如果用nest loop,那么就会T1表和T2表用A列各匹配一次,要匹配很多次,其代价显然比sort merge高。

 

 

 

 

小知识:

一:连接方式原理

合并排序连接(meger join):

以目标sql指定的谓语条件去访问两表,然后按照连接列将两个结果集进行排序,然后将排序后的两个结果集合并操作。

理想状态:2个表的排序操作都能在内存进行

常规情况:2阶段进行:

          1.sort run阶段:数据读取到内存,排序,写出到临时表空间。直到所有的row sourse完成排序。

          2.merge阶段:之前每次写到临时表空间的数据(即sort run)被重新读入到内存,进行merge

 

嵌套循环连接(nest loop)

首先,优化器决定驱动表(外层循环)t1和被驱动表(内层循环)t2以指定谓语条件访问驱动表t1,得到驱动结果集s1然后遍历s1并同时遍历t2,即先取出s1的第一条记录,接着遍历t2表并按照连接条件去判断t2是否匹配,然后再取出s1中第二条记录,同样的连接条件再去遍历t2并判断t2中是否还存在匹配的记录,直到遍历完s1中所有记录为止。

 

嵌套循环实现机制(伪代码)

          For r1 in (select rows from table_1 where colx={value})

          loop

                for r2 in (select rows from table_2 that match current row from table_1)

               loop

                  output values from current row of table_1 and current row of table_2;

               end loop;

         End loop;

在嵌套循环连接中,外部表又称为驱动表(driver table)

伪代码中:table_1为驱动表(where条件较小结果集的表),table_2为内表(被驱动表)

从伪代码中可以看出该连接过程就是一个2层嵌套循环,所以外层循环的次数越少越好,这也就是我们为什么将小表或返回较小结果集的表作为驱动表的原因。

 

HASH连接:

1 一张小表(结果集)被hash在内存中。因为数据量小,所以这张小表的大多数数据已经驻入在内存中,剩下的少量数据被放置在临时表空间中;

2  每读取大表的一条记录,就和小表中内存中的数据进行比较,如果符合,则立即输出数据(也就是说没有读取临时表空间中的小表的数据)。而如果大表的数据与小表中临时表空间的数据相符合,则不直接输出,而是也被存储临时表空间中。

3 当大表的所有数据都读取完毕,将临时表空间中的数据一起输出。如果小表的数据量足够小(小于hash area size),那所有数据就都在内存中了,可以避免对临时表空间的读写。

 

二:优缺点及使用场景

1))嵌套循环(nest loop):

          对于被连接的数据子集较小的情况,嵌套循环连接是较好的选择。在嵌套循环中,外表驱动内表,外表返回的每一行都要在内表中检索找到它匹配的行,因此整个查询返回的结果集不能太大(大于10000不合适),要把返回子集较小的表作为外表(驱动表),而且在内表的连接字段上一定要有索引。嵌套循环是行源连接方式,只适合小量数据连接。嵌套循环连接可以先返回已经连接的行,适用于快速返回结果的语句,而不必等待所有的连接操作处理完才返回数据。而其它两种连接方式则不行。

2)哈希连接(hash join):

哈希连接是大数据集连接时常用的方式,优化器使用两个表中较小的表,利用连接键在内存中建立散列表,然后扫描较大的表并探测散列表,找出与散列表匹配的行。

这种方式适用于较小的表完全可以放入内存的情况,这样成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存,这时优化器将它分割成若干不同的分区,不能放入内存的部分就把该分区写入磁盘的临时段哈希连接只能应用于等值连接(WHERE A.COL3 = B.COL4)。 哈希连接和排序合并连接是集合连接方式,适合大量数据连接。

3)排序合并连接(Sort Merge Join

通常情况下哈希连接的效果都比排序合并连接要好,因为排序对于OLTP系统来说是非常昂贵的。然而如果行源已经被排过序,在执行排序合并连接时不需要再排序了,这时排序归并连接的性能会优于哈希连接。

在等值连接方式下,返回少量记录(<10000)且内部表在连接列上存在索引,适合嵌套循环连接。若返回大量记录则适合哈希连接。

在等值连接方式下,两个行源集合都很大,若连接列是高基数列,则适合哈希连接,否则适合排序合并连接。除了等值连接,排序合并连接还可以应用于非等值连接(<>,<=,!=等)

说明: 捕获.PNG

 

 

没有索引表连接的实验

 

 

在执行计划里面,通过“最右最上”原则,可以区分哪个是驱动表(第一张表),哪个是被驱动表(第二张表)。即最右最上的就是驱动表。

 

nest loop的优化实验

 

下图的dept就是驱动表:


 

 

 

 

 

 

set pagesize 0表示不分页

set linesize 200 表示不换行,即一行足够长。

 

hints中,leading表示指定驱动表

 

 

 

display来看执行计划:

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

 

display看执行计划,最主要看buffers(求buffer的和),其相当于set autot off中的cost

display语句看执行计划时,SQL语句没有真正执行。

E-Rows代表估计的行数;

A-Rows代表实际的行数;

starts代表这张表被匹配了多少次。

上面执行计划解释:DEPT表是驱动表(最右最上原则)。dept表被匹配了一次,即一次性从dept中取出了4行数据,根据nest loop的原理,dept表将被循环四次;emp被匹配了四次。其代价buffers17+4+13=34

 

 

下面我们按另外一种方式执行(和上面的区别就是驱动表换一下):

 

display生成执行计划:

 

 

看到现在的代价buffers的和值为94。看到这条语句比上述语句性能差。

上图中,驱动表是empemp表被匹配了一次(starts表示),匹配一次后返回了14行数据,按照nest loop的原理,另外一张表将被匹配14次。这样大家能看出为什么这条语句比前面的语句性能差了吧(因为前面的语句驱动表和被驱动表匹配了4次,而现在这条语句两个表匹配了14次)。

 

 

现在我们似乎的得出一个结论:就是nest loop小表当驱动表比较好。是不是这样呢?我们再通过下面的实验来验证。

 

接下来我们再加个ename=’JAMES’的条件来看一下:

 

 

 

 

 

 

大家看到下面这个代价才16,性能更好。

为什么会这样呢?

 

前面我们得出一个结论,说是小表做驱动表性能会好,这句话是正确的。注意,这里说的小表,并不是指这张表的数据量少,而是说根据条件返回的数据量少的表。

 

 

 

 

 

hash 的优化实验

hash就是分堆的思想。比如我有一亿条数据,但是我分堆以后,可能就有一千组的数据。这样数据就是一堆一堆的,然后我再用相同的hash值去匹配,这样匹配的次数就会少一些。而且这些数据都比较集中了。

 

如果想看hash详细内容,就需要看算法的书了。

 

 

 

 

大家看到hash的执行计划里面多了三列:omemimemused-mem,这三列代表他使用的内存。因为hash完数据有一个中间的结果,所以需要使用内存缓存机制把中间结果保存起来。如果用的内存越多,代价就会越多。所以我们在看hash的执行计划时,先看buffers的和,如果buffers的和值相等,然后再看omemimemused-mem这三列内存的值。内存值越大,性能越差。

 

上图中,dept表是驱动表,它被访问了一次(starts),返回了四行数据;

被驱动表emp被访问一次(starts),返回了一行数据。为什么empdept两张表都是被访问一次呢?因为hash的原理是对各表各做一次hash,然后再根据相同的hash值连接就行了。

 

结论:hash适用于两张表都没有索引的情况。因为这样会更省事。

 

大家记住上面的used_mem用了656k

 

然后我们换一下驱动表的顺序来执行一下:


 

 

 

 

 

上图中,emp是驱动表,emp表被访问了一次(starts),emp表被返回了一行数据;

dept表也被访问了一次(starts),返回四行数据。现在的used-mem295k,代价更小。

 

大家看到,上面两个语句的总体代价都差不多(buffers相等),但是小表(emp)当驱动表性能会更低。

 

所以,对于hash,也是小表做驱动表比较好

 

注意:这里说的小表不是指表的数据量少,而是说被访问后返回的数据量少的表。

 

 

 

 

 

merge join的优化实验

 

 

 

 

看到merger join也会用到omem1memused-mem三列。因为排序会产生中间结果,这中间结果需要放到内存中。

上图中,buffers总和为18。大家看到有一个sort join,表示做了一个排序的操作。deptemp各访问了一次(start均为1,表示两张表各做了一次排序的操作,即两张表各被访问了一次)。对于merger join我们一般不区分驱动表和被驱动表。

大家看到,上图中dept表返回了四条数据,sort join了四次。

 

下面我们把这句话反过来,再执行一遍。

 

 

 

 

 

大家看到,代价buffers还是18,和上一个语句代价是一样的。

上图的empdept两张表中,emp表先被返回来数据的,返回了14行数据, emp表就sort join 14次。

 

 

 

 

下面我们把ename=’JAMES’条件加上再来看:

 

 

 

上图中dept表返回了4行数据,就sort join了四次。

 

 

 

上图中emp返回了1行数据,所以sort join1次。

 

通过上面对merge join的实验,我们得出结论,meger join不区分驱动表和被驱动表。不管两张表的顺序如何,他们的buffersmem都一样。但是两张表的顺序还是有一个非常小的差别,那就是sort join的次数不一样。所以,如果非要比较merge join两表顺序的优劣,那么merge join也是小表(小表不是数据量少的表,而是根据条件后,返回数据量少的表)做为驱动表比较好。

 

有索引表连接的实验

有索引的nest loop

 

 


下面我们不加hints,看oracle通过大脑(即优化器)选择哪种执行计划:

 

 

上面这条语句忘记加ename=’JAMES’的条件,大家在自己做实验时加上ename=’JAMES’

看到oracle自己选择hash做为表连接的方式。这两张表各被访问一次,访问代价是15.

 

结论:

1、  hash适用于没有索引的情况;

2、  有索引oracle经常走nest loop

3、  在表连接的条件中,靠近分号的先执行(为什么?因为Oracle不是开源的,所以不知道为什么)。所以,如果一列数据的筛选能力很强,那么我们最好把这个列的条件往分号附近放,并且在该列上建立索引,让它优先把数据筛选出来。

 

 


 

上面这句话强制使用d表做驱动表,其执行计划使用了索引。

 

下面我们去掉hints,在有索引的情况下,看oracle是如何选择执行计划的:

 

 

 

 

看到在有索引的情况下Oracle自己选择nest loop了(前面在没有索引的情况下,oracle自己选择走hash join)。所以验证了在没有索引的情况下一般走hash join;有索引的情况下走nest loop

 

 

 

数据库在数据量少的时候,比如所有数据都在一个数据块中,那么就可能不走索引。

但是在数据量很大的时候会走索引。

 

连接时效率的高低取决于连接字段,我们通过给连接字段加索引进行优化。先给连接字段deptno加一个索引:

 

 

 

上面这个加索引后执行计划没有啥变化。其驱动表是dept

 

我们让oracle自己选择执行计划:

 

 

 

我们看到此时自然的执行计划和上一句加hints时的执行计划没啥区别,也就是说还是dept是驱动表。

 

我们在另外一张表上加索引:

 

上图看到在deptdeptno上建立索引,再让oracle自己选择性执行计划后,emp表是驱动表了。驱动表empdept表上的的索引i_deptno进行nest loops循环匹配。

 

结论:对于nest loops优化的方法,不用两个表的连接字段deptno都加索引,只在被驱动表的连接字段上加索引就行了。

 

也就是说我们通过display看执行计划,发现两个表走的是nest loops连接方式,那么我们很痛快的在被驱动表上建立索引就行了。

小表(不一定是数据量少的表,而是根据连接字段返回行数的少的表)做驱动表,被驱动表上建立索引,这样的nest loop连接方式效率最优。

 

 

有索引的hash join

 

 

hash join是对数据进行hash,建立索引也没用,因为会不走索引。

 

 

 

 

pga_arregate_target代表pga大小;

sort_area_retatined_size代表排序区的大小;

hash_area_size代表hash区的大小。

大家注意,hash区和sort排序区都在uga里面。

关于uga,在专有连接模式下,ugapga里面;在共享连接模式下,如果有大池,那么uga在大池里面,如果没有大池,那么uga就在share pool里面。

 

所以说,在专有连接模式下,可以通过调pga的大小来调hash区的大小;在共享连接模式下可以直接调hash_area_size的大小。

 

 

hash join的优化方法:不用在连接字段上建立索引,而是调整hash区的大小就可以了。

 

有索引的merger  join

 

 

 

 

上图看到,在没有索引的情况下,merge joinbuffers18,驱动表是deptsort join4行。deptemp都是全表扫描。

 

下面我们开始添加索引:

 

 

 

 

看到我们在emp表的连接字段deptno建立索引后,执行计划和没有索引的执行计划比没有变化。

 

我们下面在dept表的连接字段deptno上建立索引。

 

 

 

通过最右最上原则,index full scan是最右最上,所以驱动表是该索引对应的dept表。

 

结论:对于merge join的优化,可以在两张表的连接字段上都建立索引。为什么呢?

因为merge join的原理是先对两张表都排序,然后再等值匹配。那么我们知道一句话,能不排序就不排序,因为索引是有序的,所以可以通过建立索引,来减少对两张表的排序,或者根本就不排序。

 

其实,对于merge join是不区分驱动表和被驱动表的。

 

 

 

总结:

1、  小表当驱动表;

2、  对于nest loop上,可以在被驱动表上的连接字段建立索引来提高两张表的匹配效率;

3、  对于hash join,加索引没有用,因为从hash的原理上来看跟索引就没关系。所以,对于hash join可以通过增加hash 区的大小来提高hash join的效率。

4、  对于merge join,可以在两张表的连接字段都建立索引。因为索引是有序的,对于表来说,能不排序就不排序,那就利用索引的有序当做排序了吧。

 

 

全表扫描

 

聚合函数避免全表扫描

 

 

 

declare

         v_num number(2);

begin

         for i in 1..10000

         loop

                   v_num:=round(dbms_random.value(1,4));

                   if v_num=1 then

                            insert into lxtb values(i,'zhangsan'||i,10);

                   elsif v_num=2 then

                            insert into lxtb values(i,'zhangsan'||i,20);

                   elsif v_num=3 then

                            insert into lxtb values(i,'zhangsan'||i,30);

                   else

                               insert into lxtb values (i,'zhangsan'||i,40);

                   end if;

                   if mod (i,1000)=0 then

                            commit;

                   end if;

         end loop;

                   commit;

end;

/

 

 

 

聚合函数:min max avg count sum 5个聚合函数,如果写的不好,这5个聚合函数也会耗掉很多性能的。

 

 

 

 

count  avg  sum的特点

 

 

我们看到count语句走的是全表扫描,其实count语句的代价是很高的。

 

那我们怎么样使count语句的性能降低呢?可以通过创建索引来提高性能。

 

 

大家注意我是在不空的列上建立的索引,不要在空的列上建立索引。

 

我们看到上述语句没有走索引。

执行下面的语句,count就会走索引:

 

 

 

 

看到上面加where name is not null就走索引了。

 

oracle优化器就像人的大脑,他胆子小,就怕count(*)里面有空值。所以优化器让count不走索引。

 

 

 

上面通过countcomm)可以看出聚合函数count不计算空值。

 

 

 

 

max min 的特点

上图中把name列上的非空约束去掉:alter table lxtb modify …

 

 

 

 

通过上面的实验可以看出,minmax不管所查询的列上有没有非空约束,只要所查询的列上有索引,minmax就会走索引。

 

上图中的index full scan (min/max):这是最高的效率方式,即只扫描索引块的头和尾,就能找到数据的最大值和最小值。因为索引是有序的,通过扫描索引的头和尾,就能找到最大值和最小值,这种效率最高。

 

 

 

 

上图中的sql select min(name),max(name) from lxtb(既有max也有min),就没走Index full scan (min/max)这种效率最高的方法,而是走的index fast full scan(即把索引块全部扫描了一遍)。

 

 

通过rowid 的表存取(table acess by rowid或者rowid lookup

 

 

索引回表的优化

 

 

上图通过索引进行回表访问的时候,可以通过索引列name在表中找到name的值,但是不能找到id,deptno的内容。

一般走索引都是两次i/o,一次是通过索引找到rowid,然后通过rowid到表中找到数据(回表访问)

那么我们可以让索引走一次i/o?

 

 

 

 

上图看到只访问了一次索引(index range scan),没有回表访问(table access by rowid),这时候只有一次i/o

 

 

 

下面我们建立复合索引:

 

 

 

 

上面看到,我们在idname,deptno上面的三个字段都建立复合索引后,在进行select id,name deptno查询时只有索引访问,没有回表访问了(table acess by rowid)

 

复合索引字段最多不要超过三个,如果超过三个,尽量放弃复合索引的方法。因为索引也是需要维护的,如果复合索引超过三个维护代价是非常大的。

 

什么时候用复合索引呢?

如果某些列筛选数据的能力不强,但如果这些列组合起来有很强的筛选数据的能力,我们就可以建复合索引了。但复合索引的列不要超过三个,因为列多了,维护索引的代价是很高的。

 

复合索引有两条,一个叫先导性,一个叫选择性。

1、复合索引中的第一列叫先导列,在查询的语句中,复合索引第一列出现的时候,才会走这个复合索引,否则不走这个复合索引。(我实验结果怎么不是索引的第一列也走索引呢)

2、另外,复合索引的先导列筛选数据的能力要强,比如nameid这样的列筛选数据的能力就比较强。

 

上面我们看到通过建立复合索引消除了回表访问,但是不一定非得要消除回表,其实走索引就很好了。另外如果复合索引的列超过三个, 那么我们就彻底不要使用复合索引了,因为那样代价会很高。

索引扫描

 

索引唯一扫描(index unique scan)

 

 

 

 

 

 

上述的index unique scan是走的是索引唯一扫描,一般是字段上有主键或者唯一约束的时候,会走索引唯一扫描。

 

注意:主键约束和唯一约束会自动创建一个索引,这个索引是用来保持唯一性的。

 

索引范围扫描(index range scan

 

 

看到上述用到了索引范围扫描index range scan

 

那么什么时候用索引范围扫描呢?

1、              当你查询的where子句里面有范围条件(如上述的deptno>10