【Oracle-OCP】第五次课

2022-11-26 07:51:51

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)截取sqltext字符串INSTR(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排查问题思路