【Oracle-OCP】第五次课

Category

知识点复习一

数据库架构师Rigit Demand

  1. DB HA
    2.1 OS级别高可用
    2.2 DB级别高可用
    2.3 块级别高可用

  2. 了解主流数据库
    3.1 关系型数据库 RDBMS(Oracle,Mysql)
    3.2 非关系型数据库 DBMS(Manggo)

  3. NoSQL/NEWSQL(Tidb)

  4. 了解主流平台 Platform : OS(linux)

  5. Lang:Python/shell ,EN

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

高可用方案

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

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

DRBD 方式就是 Active/standby方式,操作系统级别的复制
DRBD是随着Kernel的版本升级的
查看kernel版本命令:Uname -r
【Oracle-OCP】第五次课
查找己安装软件包的命令
rpm -q samba 列出samba的安装信息
rpm -qa|grep samba 是列出包含samba字段的软件的信息。
你可以理解为一个完全匹配软件名,一个不完全匹配软件名
【Oracle-OCP】第五次课

脑裂

什么是脑裂

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

如何避免脑裂

没法避免,但是有另一种机制,叫做投票机制(Voting)
Voting disk :作表决的,决定谁成为主,这种投票机制在mysql里叫Node
【Oracle-OCP】第五次课

恢复数据的方式

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

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

keepalived

keepalived 主从搭建 - vip floating

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

Quiz

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

  1. Keepalived Tool
    通过这个工具选主
    If [[ isn’t keepalive master]] ; then
    Exit 0;

  2. 或者自己写一个算法,让机器互相向对方的机器写磁盘,看谁成功的多,然后选
    不能机器之间相互ping,因为端口问题可能把机器干崩
    【Oracle-OCP】第五次课

删除一张表时,用truncate还是delete?

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

知识点复习二

一条SQL经历了什么?

【Oracle-OCP】第五次课

概念

  1. data file里4这个块儿会不会进到buffer cache里?
    看你一次IO读(db-multi-block)参数设定的块一次抓多少?可以设定,进不进去都有可能。因为 索引扫描时 一次IO读一个块,全表扫描时 一次IO读(db-multi-block)参数设定的块

  2. Buffer cache里存的是结果集吗?
    不是结果集,缓存的是数据块儿
    因为结果集会变化(先select,再delete,再select,结果就变了,所以不能存结果集)

  3. 结果集会缓存到哪里?
    会缓存到PGA里

实验一

select * from t where id=100
select * from t where id=200
【Oracle-OCP】第五次课
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

实验三 (硬解析,软解析)

  1. Scott用户下创建表t1
    scott@ORCL>create table t1 as select * from dba_objects;

  2. alter system flush shared_pool;
    sys@ORCL>alter system flush shared_pool;
    System altered.

  3. 运行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次

  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;
    【Oracle-OCP】第五次课

  5. 执行SQL
    begin
    for i in 1 … 4 loop
    execute immediate ‘select * from t1 where object_id=10’;
    end loop;
    end;
    /

  6. 再查看执行计划
    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;
    【Oracle-OCP】第五次课

结论
执行和解析次数都是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里,叫软软解析
【Oracle-OCP】第五次课

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

实验四(变量)

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

  1. 前提
    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.

  2. 声明变量并赋值
    scott@ORCL>var oid number
    scott@ORCL>exec :oid :=20

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

  4. 执行SQL
    scott@ORCL>select * from t1 where object_id=20;

  5. 继续赋值
    scott@ORCL>exec :oid :=30

  6. 执行SQL
    scott@ORCL>select * from t1 where object_id=:oid;

  7. 继续赋值
    scott@ORCL>exec :oid :=40

  8. 执行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;

  9. 查看执行结果
    【Oracle-OCP】第五次课

  10. 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
这种情况下会生成一条执行计划,但是生成的执行计划是全表扫描呢还是索引扫描呢?
【Oracle-OCP】第五次课

  1. 创建表
    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’);

  2. 创建索引
    create index ind_t2_id on t2(id);

  3. 定义变量并赋值
    var oid number
    exec :oid :=0;

  4. 查看结果
    scott@ORCL>set autotrace traceonly
    scott@ORCL>select * from t2 where id= :oid;
    【Oracle-OCP】第五次课

  5. 第二次赋值
    scott@ORCL>exec :oid :=100;

  6. 确认结果
    结果还是索引扫描
    scott@ORCL>set autotrace traceonly
    scott@ORCL>select * from t2 where id= :oid;
    【Oracle-OCP】第五次课

  7. 绑定变量再执行后
    又变成全表扫描
    scott@ORCL>select * from t2 where id=100;
    【Oracle-OCP】第五次课

结论:绑定变量窥探
第一遍 :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(vsql,1,50)sqltextINSTR(vsql,1,50) 截取sql_text字符串 INSTR(vsql,1,50)截取sqlt​ext字符串INSTR(vsql.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的时候,索引扫描进行几次?
与上述例子一个道理,一共要进行四十几次扫描
【Oracle-OCP】第五次课
【Oracle-OCP】第五次课

全表扫描

一次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
【Oracle-OCP】第五次课

上一篇:【MyBatis】Spring集成MyBatis示例


下一篇:Oracle排查问题思路