【Oracle-OCP】第五次课

金小金～

于 2019-06-15 21:41:16 发布

阅读量243

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/weixin_42161670/article/details/90760935

版权

oracle 同时被 2 个专栏收录

73 篇文章 0 订阅

订阅专栏

oracle_note

52 篇文章 0 订阅

订阅专栏

知识点复习一

数据库架构师Rigit Demand

DB HA
2.1 OS级别高可用
2.2 DB级别高可用
2.3 块级别高可用
了解主流数据库
3.1 关系型数据库 RDBMS（Oracle，Mysql）
3.2 非关系型数据库 DBMS（Manggo）
NoSQL/NEWSQL（Tidb）
了解主流平台 Platform : OS（linux）
Lang：Python/shell ，EN

c. 块级别高可用 – DRBD（active/standby结构）：跟随kernel版本升级，包：kernel-devel，脑裂现象比较严重

高可用方案

HA两种主从复制方式 & Voting投票机制

Primary/Slave
又叫Master/Slave，Slave节点是只读的，不能改
Active/standby
Standby有呼吸，但不活跃
写的时候往Active上写，不往standby上写
前者死了，standby就变Active了
Voting投票机制
三个节点（两个production节点，一个voting节点）互ping，得分高的production节点（ping到的越多、得分越高）会成为高可用集群的主节点
尽量用“向指定节点文件写入内容”来代替ping操作，因为ping可能会被防火墙禁止。
Oracle：
RAC：内存高可用
DG：数据库文件高可用（可用存储方案替代）

DRBD 方式就是 Active/standby方式，操作系统级别的复制
DRBD是随着Kernel的版本升级的
查看kernel版本命令：Uname -r

查找己安装软件包的命令
rpm -q samba 列出samba的安装信息
rpm -qa|grep samba 是列出包含samba字段的软件的信息。
你可以理解为一个完全匹配软件名，一个不完全匹配软件名

脑裂

什么是脑裂

Vip传数据给Active，然后发生20s网断，Standby认为Active死了，就要替代Active，后来前者恢复了，这时就有两个主，中间的复制链条会断掉，没办法决定他俩谁应该被kill掉，因为他俩上边都有数据，但是传过来的数据，有的会给前者传，有的会给后者传，两者管理各自的数据，没法互相复制，这种就叫脑裂，恢复起来也很麻烦
在这里插入图片描述

如何避免脑裂

没法避免，但是有另一种机制，叫做投票机制（Voting）
Voting disk ：作表决的，决定谁成为主，这种投票机制在mysql里叫Node
在这里插入图片描述

恢复数据的方式

Redo log有两种记录方式：
Oracle是改变向量的方式，MySQL可以从以下这两种方式进行选择

Insert t1(1) (直接记录SQL语句的详细操作)
Block 238 : 1 (记录数据库对Block的修改信息,改变向量)

keepalived

keepalived 主从搭建 - vip floating

搭建环境
http://www.evernote.com/l/AB5cboQImCVK-LPbhc-kYjKZXyqIRrx4iks/
解释
https://www.cnblogs.com/losbyday/p/5841830.html

Quiz

我有一个脚本要求7*24小时不间断，这个脚本只在一台服务器上，然后给你三台机器，怎么弄？

Keepalived Tool
通过这个工具选主
If [[ isn’t keepalive master]] ; then
Exit 0;
或者自己写一个算法，让机器互相向对方的机器写磁盘，看谁成功的多，然后选
不能机器之间相互ping，因为端口问题可能把机器干崩

删除一张表时，用truncate还是delete？

宁可truncate不Delete ，因为Delete慢
https://www.cnblogs.com/zhizhao/p/7825469.html

知识点复习二

一条SQL经历了什么？

在这里插入图片描述

概念

data file里4这个块儿会不会进到buffer cache里？
看你一次IO读(db-multi-block)参数设定的块一次抓多少？可以设定，进不进去都有可能。因为索引扫描时一次IO读一个块，全表扫描时一次IO读(db-multi-block)参数设定的块
Buffer cache里存的是结果集吗？
不是结果集，缓存的是数据块儿
因为结果集会变化（先select，再delete，再select，结果就变了，所以不能存结果集）
结果集会缓存到哪里？
会缓存到PGA里

实验一

select * from t where id=100
select * from t where id=200
在这里插入图片描述
Sql1 生成hash1 生成execution plan id ，生成execution plan（在CPU生成）
Sql2 生成hash2 生成execution plan id ，生成execution plan（在CPU生成）
但存储时只存储一个execution plan到library cache

实验二（数据分布）

Select * from test3 where id=100;
Select * from test3 where id=0;
它俩的执行计划一定是一样的吗？
答案是不一定的，数据的分布有可能导致执行计划不一样

Select rownum from dual connect by level <6
Rownum+10
Rownum+sysdates
Select chr(66) from dual;
Chr(rownum+64)

Create table test3
插入如下数据
Id=0 1个
Id=100 9999个

Select * from test3 where id=100;
Id=100时，返回的数量是9999条，执行计划走的是table_full scan

Select * from test3 where id=0;
Id=0时，返回的数量是1条，走的是index scan

实验三（硬解析，软解析）

Scott用户下创建表t1
scott@ORCL>create table t1 as select * from dba_objects;
alter system flush shared_pool;
sys@ORCL>alter system flush shared_pool;
System altered.
运行SQL
select * from t1 where object_id=20; 运行1次
select * from t1 where object_id=30; 运行1次
select * from t1 where object_id=40; 运行2次
select * from t1 where object_id=50; 运行4次
查看执行计划
scott@ORCL>column sql_text format a50
scott@ORCL>set linesize 999
select
sql_text,
s.parse_calls,
loads,
executions
from v$sql s
where sql_text like ‘select * from t1 where object_id%’
order by 1, 2, 3, 4;
执行SQL
begin
for i in 1 … 4 loop
execute immediate ‘select * from t1 where object_id=10’;
end loop;
end;
/
再查看执行计划
scott@ORCL>column sql_text format a50
scott@ORCL>set linesize 999
select
sql_text,
s.parse_calls,
loads,
executions
from v$sql s
where sql_text like ‘select * from t1 where object_id%’
order by 1, 2, 3, 4;

结论
执行和解析次数都是1 –>硬解析 sql第一次执行以后一定是一个硬解析
第二次执行时，library cache里有执行计划，省略掉execution plan，代表exec count>=2 exectution不等于1就说明是软解析

如果使用的是library cache 是正常解析，执行多少次，解析多少次 SGA
如果SQL被多次执行，在PL/SQL block里面，SQL被执行了多次，那就缓存到PGA里面（Pragram Global Area）

如果一个SQL execution执行超过3次，并且放到PL/SQL block，放到PGA里，叫软软解析
在这里插入图片描述

Show parameter session
里有一个参数叫session_cached_cursors
PGA里能缓存多少语句是由session_cached_cursors决定的

实验四（变量）

Select * from t where id = var
Var = 10;
Var = 20;
当一条SQL语句，带变量的情况下，无论变量是几，因为这是一条SQL语句，所以执行后产生的hash值是一样的
具体如下

前提
scott@ORCL>connect sys/oracle as sysdba
Connected.
sys@ORCL>alter system flush shared_pool;
System altered.
sys@ORCL>
sys@ORCL>connect scott/tiger
Connected.
声明变量并赋值
scott@ORCL>var oid number
scott@ORCL>exec :oid :=20
执行SQL
scott@ORCL>select * from t1 where object_id=:oid; #####执行带变量的语句
执行SQL
scott@ORCL>select * from t1 where object_id=20;
继续赋值
scott@ORCL>exec :oid :=30
执行SQL
scott@ORCL>select * from t1 where object_id=:oid;
继续赋值
scott@ORCL>exec :oid :=40
执行SQL
scott@ORCL>select * from t1 where object_id=:oid;
scott@ORCL>select * from t1 where object_id=:oid;
scott@ORCL>select * from t1 where object_id=40;
scott@ORCL>select * from t1 where object_id=40;
查看执行结果
TIP
Oracle查看当前变量的值是多少（Oracle的print函数）
scott@ORCL>exec dbms_output.put_line(:oid)
30
PL/SQL procedure successfully completed.

証跡

scott@ORCL>connect sys/oracle as sysdba
Connected.
sys@ORCL>alter system flush shared_pool;

System altered.

sys@ORCL>
sys@ORCL>connect scott/tiger
Connected.
scott@ORCL>var oid number    #####声明一个变量
scott@ORCL>exec :oid :=20     #####给变量赋值为20

PL/SQL procedure successfully completed.

scott@ORCL>
scott@ORCL>select * from t1 where object_id=:oid;  #####执行带变量的语句

OWNER                          OBJECT_NAME                                                          SUBOBJECT_NAME
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------
 OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
---------- -------------- ------------------- --------- --------- ------------------- ------- - - - ---------- ------------------------------
SYS                            ICOL$
        20              2 TABLE               15-AUG-09 15-AUG-09 2009-08-15:00:16:51 VALID   N N N      1

scott@ORCL>select * from t1 where object_id=20;

OWNER                          OBJECT_NAME                                                          SUBOBJECT_NAME
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------
 OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
---------- -------------- ------------------- --------- --------- ------------------- ------- - - - ---------- ------------------------------
SYS                            ICOL$
        20              2 TABLE               15-AUG-09 15-AUG-09 2009-08-15:00:16:51 VALID   N N N      1


scott@ORCL>
scott@ORCL>
scott@ORCL>exec :oid :=30     #####给变量赋值为30

PL/SQL procedure successfully completed.

scott@ORCL>
scott@ORCL>select * from t1 where object_id=:oid;   #####执行带变量的语句


OWNER                          OBJECT_NAME                                                          SUBOBJECT_NAME
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------
 OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
---------- -------------- ------------------- --------- --------- ------------------- ------- - - - ---------- ------------------------------
SYS                            I_COBJ#
        30             30 INDEX               15-AUG-09 15-AUG-09 2009-08-
15:00:16:51 VALID   N N N      4
scott@ORCL>
scott@ORCL>
scott@ORCL>exec :oid :=40     #####给变量赋值为40

PL/SQL procedure successfully completed.

scott@ORCL>select * from t1 where object_id=:oid;    #####执行带变量的语句

OWNER                          OBJECT_NAME                                                          SUBOBJECT_NAME
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------
 OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
---------- -------------- ------------------- --------- --------- ------------------- ------- - - - ---------- ------------------------------
SYS                            I_OBJ5
        40             40 INDEX               15-AUG-09 15-AUG-09 2009-08-15:00:16:51 VALID   N N N      4


scott@ORCL>
scott@ORCL>
scott@ORCL>
scott@ORCL>select * from t1 where object_id=:oid;   #####执行带变量的语句

OWNER                          OBJECT_NAME                                                          SUBOBJECT_NAME
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------
 OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
---------- -------------- ------------------- --------- --------- ------------------- ------- - - - ---------- ------------------------------
SYS                            I_OBJ5
        40             40 INDEX               15-AUG-09 15-AUG-09 2009-08-15:00:16:51 VALID   N N N      4


scott@ORCL>

scott@ORCL>

scott@ORCL>select * from t1 where object_id=40;  ###执行该sql语句第一次

OWNER                          OBJECT_NAME                                                          SUBOBJECT_NAME
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------
 OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
---------- -------------- ------------------- --------- --------- ------------------- ------- - - - ---------- ------------------------------
SYS                            I_OBJ5
        40             40 INDEX               15-AUG-09 15-AUG-09 2009-08-15:00:16:51 VALID   N N N      4


scott@ORCL>select * from t1 where object_id=40; ###执行该sql语句第二次


OWNER                          OBJECT_NAME                                                          SUBOBJECT_NAME
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------
 OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
---------- -------------- ------------------- --------- --------- ------------------- ------- - - - ---------- ------------------------------
SYS                            I_OBJ5
        40             40 INDEX               15-AUG-09 15-AUG-09 2009-08-15:00:16:51 VALID   N N N      4


scott@ORCL>

实验五（绑定变量窥探）

回顾实验二，执行以下两条SQL

Select * from t2 where id= 100 (数据库有9999条数据)
Select * from t2 where id= 0 (数据库只有1条数据)

Select * from t2 where id= 100 走的是Full table scan
Select * from t2 where id= 0 走的是index scan
这两条SQL生成两个不一样的执行计划，一个是全表扫描，一个是索引扫描

那么问题来了
如果id是变量，那么sql就是这样的
Select * from t2 where id= var
这种情况下会生成一条执行计划，但是生成的执行计划是全表扫描呢还是索引扫描呢？
在这里插入图片描述

创建表
scott@ORCL>drop table t2 purge;
scott@ORCL>create table t2(id number,name varchar2(10));
scott@ORCL>insert into t2 select 100,‘bbb’ from dual connect by level <10000;
scott@ORCL>insert into t2 values(0,‘aaa’);
创建索引
create index ind_t2_id on t2(id);
定义变量并赋值
var oid number
exec :oid :=0;
查看结果
scott@ORCL>set autotrace traceonly
scott@ORCL>select * from t2 where id= :oid;
第二次赋值
scott@ORCL>exec :oid :=100;
确认结果
结果还是索引扫描
scott@ORCL>set autotrace traceonly
scott@ORCL>select * from t2 where id= :oid;
绑定变量再执行后
又变成全表扫描
scott@ORCL>select * from t2 where id=100;

结论：绑定变量窥探
第一遍 :oid=0 -> index scan，执行计划存起来了
第二遍：赋值100后，再执行 :oid=100 时遵行了上次的执行计划，因此沿用了index scan(即使不是最优计划)

反之亦然，第一次进行全表扫描，第二次执行因为沿用第一次缓存的执行计划，也会是全表扫描，
绑定变量窥探：也叫数据倾斜

QUIZ

判断系统里是否存在像下面这样的大量的硬解析
Select ~ from~ where id=1;
Select ~ from~ where id=2;
…
Select ~ from~ where id=n;
这样的硬解析出现多少次算有问题了：count数出现1万次以上时
做法：
SUBSTR(v $sql,1,50) 截取sql_text字符串 INSTR(v$ sql.sql_text,’where’) 查看sql_text所在位置

知识点复习三

索引扫描

索引是一个什么样的数据结构？
B-Tree (B是Balance的意思)的结构
根节点到叶子节点，高度是一样的
一层代表一次访问，也就是一次IO，

假设你想找384这个数，因为在这个树里找了4层，所以进行了4次访问，也就是进行了4次IO，但是找到了384的rowid后，要再进行一次全表扫描，在全表里找这个row id，一共进行5次扫描

假设你想找 100,200,300,400,500,600,700,800,98的时候，索引扫描进行几次？
与上述例子一个道理，一共要进行四十几次扫描
在这里插入图片描述

全表扫描

一次IO读多少个块？
由db_file_multiblock_read_count变量决定，有的电脑是128块，有的电脑是40来块儿

全表扫描时，一个块能存5行数据，一个表里一共有2000个块，一次IO扫描100个块的话，那么一共要进行20次IO(2000/100)

db_file_multiblock_read_count参数怎么看？
scott@ORCL>show parameter multi
在这里插入图片描述

金小金～

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【Oracle-OCP】第五次课

Category知识点复习一数据库架构师Rigit Demand高可用方案脑裂什么是脑裂如何避免脑裂恢复数据的方式Quizkeepalived知识点复习二一条SQL经历了什么？概念实验一实验二实验三硬解析，软解析结论实验四变量的实验証跡知识点复习一数据库架构师Rigit DemandDB HA2.1 OS级别高可用2.2 DB级别高可用2.3 块级别高可用了解主流数据库3....
复制链接

扫一扫