VC' Blog
0%

Oracle 笔记汇总

Posted on Edited on In Valine:

安装

Oracle in Docker

docke search oracle，选择相应的 image 拉取

1
2
3
$ docker run -d --name oracle \
  --privileged -v $(pwd)/oradata:/u01/app/oracle \
  -p 8080:8080 -p 1521:1521 absolutapps/oracle-12c-ee

镜像说明文档 中 Additional options 的意思是自己基于它的基础包重修 build 的时候可以修改的参数。

PS：在给自用 Ubuntu 安装时非常顺利，在给测试平台的Centos7安装时诡异地出现了docker-entrypoint.sh中一条chown语句卡出运行不了，修改文件中相应的语句后成功安装。

Oracle in Centos

静默安装教程

图形化安装教程

vi /etc/sysctl.conf

这里面有很多参数可以优化

内核的 shmall 和 shmmax 参数

1
2
3
4
5
SHMMAX= 配置了最大的内存 segment 的大小 ------>这个设置的比 SGA_MAX_SIZE 大比较好。

SHMMIN= 最小的内存 segment 的大小 

SHMMNI= 整个系统的内存 segment 的总个数

使配置生效
/sbin/sysctl -p

swap 不存在

报错，提示swap空间不足

1
Checking swap space: 0 MB available, 150 MB required. Failed <<<<

创建 swap 文件

1
2
3
dd if=/dev/zero of=/swapfile bs=1024 count=512k
mkswap /swapfile
swapon /swapfile

参考

配置监听

1
netca /silent /responsefile /home/database/response/netca.rsp # 这句话一定要写绝对路径

listener.ora 范例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# listener.ora Network Configuration File:/u01/app/oracle/product/12/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/12/db_1)
#      (PROGRAM = extproc)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.5.16.198)(PORT = 1521))
#      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

参考：

listener/tnsname
listener parameter

host 填 localhost 可能出现其他机子连不到的情况

使用 静态监听 方式时，lsnrctl status会看到实例的状态为unknown为正常现象。

  • 动态监听：oracle 服务器默认会去绑定1521端口。
  • 静态监听：在监听器中指定数据库信息（路径），由监听器去搜索服务。

sqlplus 无法使用 backspace 和 history

Cenots 中使用 sqlplus 无法使用删除和方向键，安装 rlwrap 解决。

系统防火墙配置

登录 root 检查防火墙状态

1
2
3
[root@centos7 ~]# firewall-cmd --get-active-zones
public
  interfaces: eth0

打开相关的端口

1
2
[root@centos7 ~]# firewall-cmd --zone=public --add-port=1521/tcp --add-port=5500/tcp --add-port=5520/tcp --add-port=3938/tcp --permanent
success

重新加载生效

1
2
[root@centos7 ~]# firewall-cmd --reload
success

查看端口规则

1
2
[root@centos7 ~]# firewall-cmd --list-ports
1521/tcp 3938/tcp 5500/tcp 5520/tcp

开机自启动配置

https://www.cnblogs.com/meiling12/p/8443823.html

  1. 查看ORACLE_HOME是否设置
1
2
$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/dbhome_1
  1. 执行dbstart 数据库自带启动脚本
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[oracle@local ~]$ cd $ORACLE_HOME
[oracle@local dbhome_1]$ cd bin/
[oracle@local bin]$ dbstart
ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener Usage: /u01/app/oracle/product/11.2.0/db_1/bin/dbstart ORACLE_HOME
错误提示：ORACLE_HOME_LISTNER 没有设置

[oracle@local bin]$ ll | grep dbs
-rwxr-x---. 1 oracle oinstall 6088 1月 1 2000 dbshut
-rwxr-x---. 1 oracle oinstall 13892 12月 11 16:01 dbstart

# 编辑 dbstart，将ORACLE_HOME_LISTNER=$1修改成 ORACLE_HOME_LISTNER=$ORACLE_HOME 前提是$ORACLE_HOME环境设置正确

[oracle@local bin]$ vi dbstart 

# 添加
ORACLE_HOME_LISTNER=/u01/app/oracle/product/11.2.0/dbhome_1
  1. 编辑 /etc/oratab 文件

dbca建库时都会自动创建/etc/oratab文件

将 orcl:/u01/app/oracle/product/11.2.0/dbhome_1:N
修改成orcl:/u01/app/oracle/product/11.2.0/dbhome_1:Y

  1. 编辑 /etc/rc.d/rc.local 启动文件，添加数据库启动脚本 dbstart
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[root@local ~]# vi /etc/rc.d/rc.local
#!/bin/bash
# THIS FILE IS ADDED FOR COMPATIBILITY PURPOSES
#
# It is highly advisable to create own systemd services or udev rules
# to run scripts during boot instead of using this file.
#
# In contrast to previous versions due to parallel execution during boot
# this script will NOT be run after all other services.
#
# Please note that you must run 'chmod +x /etc/rc.d/rc.local' to ensure
# that this script will be executed during boot.

su oracle -lc "/u01/app/oracle/product/11.2.0/dbhome_1/bin/lsnrctl start"
su oracle -lc /u01/app/oracle/product/11.2.0/dbhome_1/bin/dbstart

管理

用户管理

创建的新用户是没有任何权限的，甚至连登陆的数据库的权限都没有，需要为其指定相应的权限。给一个用户赋权限使用命令grant，回收权限使用命令revoke。

权限分为系统权限和对象权限。

系统权限

用户对数据库的相关权限，connect、resource、dba 等系统权限，如建库、建表、建索引、建存储过程、登陆数据库、修改密码等。

1
2
3
4
5
6
7
8
9
10
11
12
SQL> conn xiaoming/oracle
ERROR:
ORA-01045: user XIAOMING lacks CREATE SESSION privilege; logon denied
警告：您不再连接到 ORACLE。
SQL> show user
USER 为 ""
SQL> conn system/oracle
已连接。
SQL> grant connect to xiaoming;
授权成功。
SQL> conn xiaoming/oracle
已连接。

注意：准确地说grant connect to xiaoming;中，connect 不是权限，而是角色

对象权限

用户对其他用户的数据对象操作的权限，insert、delete、update、select、all 等对象权限，数据对象有很多，比如表，索引，视图，触发器、存储过程、包等。

执行SELECT * FROM Dba_Object_Size;语句可得到 oracle 数据库对象。

希望 xiaoming 用户可以去查询 scott 的 emp 表

1
grant select on scott.emp to xiaoming

希望 xiaoming 用户可以去修改 scott 的 emp 表

1
grant update on scott.emp to xiaoming

希望 xiaoming 用户可以去修改/删除，查询，添加 scott 的 emp 表

1
grant all on scott.emp to xiaoming

scott 希望收回 xiaoming 对 emp 表的查询权限

1
revoke select on scott.emp from xiaoming

权限的传递

希望 xiaoming 用户可以去查询 scott 的 emp 表，还希望 xiaoming 可以把这个权限传递给别人。

如果是对象权限，就加入 with grant option

1
grant select on emp to xiaoming with grant option

如果 scott 把 xiaoming 对 emp 表的查询权限回收，那么 xiaohong 的权限被回收。

1
2
3
4
5
6
7
8
9
10
11
SQL> conn scott/oracle;
已连接。
SQL> revoke select on emp from xiaoming;
撤销成功。
SQL> conn xiaohong/oracle;
已连接。
SQL> select * from scott.emp;
select * from scott.emp
*
第 1 行出现错误：
ORA-00942: 表或视图不存在

结果显示：小红受到诛连了。

  • 如果是系统权限，就加入 with admin option
1
grant connect to xiaoming with admin option

收回这个用户的系统权限时，这个用户已经授予其他用户或角色的此系统权限不会因传播无效

代理用户

proxy users, allowing you to access a schema via a different username/password combination.

1
2
3
4
5
6
7
8
9
CONN / AS SYSDBA
CREATE USER test_user IDENTIFIED BY test_user;

ALTER USER scott GRANT CONNECT THROUGH test_user;

SQL> CONN test_user[scott]/test_user
SQL> SHOW USER
USER is "SCOTT"
SQL>

特定 schema 的权限

想要将user1下所有表的查询权限付给user2用户，ORACLE 没有提供这个层级的语句。

方法 1：在 all_tables 中查出所有表后，拷贝输出结果执行

1
2
select 'Grant all on '||table_name||'to user2 ;' from all_tables 
where owner = upper(user1);

方法 2：存储过程

1
2
3
4
5
6
7
8
9
10
11
create or replace procedure grant_selectAll_sql(v_from in varchar2, v_to in varchar2) is
  v_sql varchar2(1000);
  cursor v_cur is
    select t.* from dba_tables t where t.OWNER = v_from;

begin
  for v_row in v_cur loop
    v_sql := 'grant select on ' || v_from || '.' || v_row.table_name || ' to ' || v_to;
    execute immediate v_sql;
  end loop;
end test;

表空间管理

创建表空间

oracle 默认最大数据文件为 32G, 想要创建超过这个大小可以使用。一个 表空间不能混用 datafile和 bigfile。

1
create bigfile tablespace ...

为用户赋予权限，否则会报 ORA-01950。

1
ALTER USER userName quota [unlimited/100M] on tablespaceName;

NAS 数据存储

oracle 对 NAS 的挂载方式有要求，ORACLE 不支持 cifs

NFS 挂载命令

1
mount -o rw,bg,hard,nointr,rsize=32768,wsize=32768,vers=3,tcp,actimeo=0,timeo=600 192.168.1.2:/ETL /mnt/data

字符集

server 字符集

查询 server 字符集

1
2
3
SELECT * FROM V$NLS_PARAMETERS;

SELECT userenv('language') FROm dual;

client 端字符集

在 windows 平台下，就是注册表里面相应 OracleHome 的 NLS_LANG。还可以在 dos 窗口里面自己设置，这样就只影响这个窗口里面的环境变量，比如：

1
set nls_lang=AMERICAN_AMERICA.ZHS16GBK

在 unix 平台下，就是环境变量 NLS_LANG。

1
2
echo $NLS_LANG
AMERICAN_AMERICA.ZHS16GBK

如果检查的结果发现 server 端与 client 端字符集不一致，请统一修改为同 server 端相同的字符集

dump 文件字符集

用 oracle 的 exp 工具导出的 dmp 文件也包含了字符集信息，dmp 文件的第 2 和第 3 个字节记录了 dmp 文件的字符集。

如果 dmp 文件不大，比如只有几 M 或几十 M，可以用 UltraEdit 打开 (16 进制方式），看第 2 第 3 个字节的内容，如 0354，然后用以下 SQL 查出它对应的字符集：

1
2
SQL> select nls_charset_name(to_number('0354','xxxx')) from dual;
ZHS16GBK

参考

日期

Oracle 的日期格式

Oracle 数据缺省的时间格式数据的显示形式，与所使用的字符集有关。一般显示年月日，而不显示时分秒。

例如：

  • 使用 us7ascii 字符集（或者是其他的英语字符集）时，缺省的时间格式显示为：28-Jan-2003，

  • 使用 zhs16gbk 字符集（或其他中文字符集）时时间格式缺省显示为：2003-1 月-28。

向表中插入数据时，如果不使用转换函数，则时间字段的格式必须遵从会话环境的时间格式，否则不能插入。

查看当前会话的时间格式，可以使用以下的 SQL 语句：

1
SQL> select sysdate from dual;

修改日期格式的方法

  1. 在 sql*plus 中修改当前会话的日期格式
1
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

将当前会话的时间格式修改为这种格式： 2003-01-28 15:23:38，这种修改方法，只对当前会话有效。

注意，是对当前会话，而不是当前的 sql*plus 窗口。即如果你这样修改之后，又使用 connect 命令以其他用户连接到数据库或者是连接到其他的数据库，则这个日期格式就失效了，又恢复到缺省的日期格式。

  1. 修改注册表（只对 windows 系统）

在注册表/hkey_local_machine/software/oracle/home0 主键中增加一个字串 (8i 版本），字串名为 nls_date_format，字串的值为你希望定义的时间格式，如： yyyy-mm-dd hh24:mi:ss ，然后重新启动 sql*plus。

这种修改方法，对 sql*plus 窗口有效，即不论你打开多少个 sql*plus 窗口，缺省的都是这种时间格式。修改服务器端的注册表无效，只有修改客户端的注册表才有效。

  1. 修改环境变量（Linux）

oracle 用户编辑 .bash_profile 下 加入以下内容，重新登录即可生效

1
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
  1. 用 sysdba 登录：然后更新 props$这个表里的字段即可
1
update props$ set value = 'YYYY-MM-DD HH24:MI:SS' where parameter = 'NLS_DATE_FORMAT';
  1. sql developer 修改

工具->首选项->数据库->NLS->日期格式：DD-MON-RR 修改为：YYYY-MM-DD HH24:MI:SS

统计信息

sql developer 中看到的统计信息并不是实时的，只有使用analysis命令之后才会更新，比如：

1
ANALYZE TABLE tablename COMPUTE STATISTICS

数据库恢复与迁移

X医院使用的数据库为 10.2.0.5 in winsows，想要在Centos下恢复。

恢复步骤

  1. 安装数据库

  2. 修改环境变量 ORACLE_SID

  3. nomout 启动，恢复 pfile 文件，并修改 pfile 文件中的路径为本地路径

    1
    2
    3
    4
    5
    rman target /

    rman>startup nomount

    rman>restore spfile to pfile '$ORACLE_HOME/dbs/init_$ORACLE_SID.ora' from'/backup/rman/full_09l9esg4_1_1';

  4. sql使用 pfile 重启数据库至 nomount 状态，恢复 controlfile，启动到 mount 状态

    1
    2
    RMAN> restore controlfile from '...'
    RMAN> alter database mount;

  5. 检查备份，设置 datafi 路径，restore datebase

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    RMAN> catalog start with '/home/oracle/rmanbackup';

    RMAN> run{
    2> allocate channel c1 type disk;
    3> set newname for datafile 1 to '/u01/app/oracle/oradata/csdb/system01.dbf';
    4> set newname for datafile 2 to '/u01/app/oracle/oradata/csdb/sysaux01.dbf';
    5> set newname for datafile 3 to '/u01/app/oracle/oradata/csdb/undotbs1.dbf';
    6> set newname for datafile 4 to '/u01/app/oracle/oradata/csdb/users01.dbf';
    7> set newname for datafile 5 to '/u01/app/oracle/oradata/csdb/example01.dbf';
    8> set newname for datafile 6 to '/u01/app/oracle/oradata/csdb/undotbs2.dbf';
    9> set newname for datafile 7 to '/u01/app/oracle/oradata/csdb/testtbs01.dbf';
    10> restore database;
    11> switch datafile all;
    12> release channel c1;
    13> }

  6. recover datafilebase

尝试历程

备份恢复的时候尽量选择相同的数据库版本，即使相同大版本不同小版本（补丁）也有可能出现各种奇怪的错误无法恢复数据。

  • 12c 失败
  • 10.2.0.1 catalog start with 失败
  • 10.2.0.3 restore database 失败
  • 10.2.0.5 restore database 成功，recover database 失败

跨平台问题

The Cross-platform redo application is only supported for physical standby but not for general media recovery which is involved while duplicating database using RMAN.

Windows 与 Linux 平台的 Oracle 日志系统不同，无法平台进行恢复，因此想要跨平台恢复存在如下方式：

  1. 冷备份：即停机备份

  2. 热备份时备份 standby controlfile （参考）

    Let’s modify the RMAN script in windows to backup standby controlfile instead of a normal controlfile.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    run
    {
    allocate channel c1 device type disk;
    allocate channel c2 device type disk;
    backup as compressed backupset database filesperset 1 format 'c:\backup\datafile_%U';
    sql "alter database create standby controlfile as ''c:\backup\control_stby.bak''";
    release channel c1;
    release channel c2;
    }

    Make a few switch log in windows.

    1
    2
    3
    SQL> alter system switch logfile;

    System altered.

  3. 修改配置跳过一致性检查（本次恢复最终使用此方法）

    1
    alter system set “_allow_resetlogs_corruption”=true scope=spfile;

  4. BBED 可以直接修改文件头，骗过一致性检查 （参考）

SQL

in vs exists

in 和 exists 主要是造成了驱动顺序的改变（这是性能变化的关键），如果是 exists，那么以外层表为驱动表，先被访问，如果是 IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标。因此 in 适用于内表小，exists 适用于外表小。

not in vs not exists

对于 not in如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录。

性能对比

联表更新

写法 1

1
2
3
4
5
6
update test1 t1
    set t1.object_name = (select t2.object_name
        from test2 t2
    where t1.object_id = t2.object_id)
where exists (select 1 from test2 t3 
    where t3.object_id = t1.object_id);

写法 2 (inline view 更新法）

1
2
3
4
update (select t1.object_name, t2.object_name new_object_name
    from test1 t1, test2 t2
    where t1.object_id = t2.object_id)
set object_name = new_object_name;

写法 3 (merge 法）

1
2
3
4
5
merge into test1 t1
using test2 t2
on (t1.object_id = t2.object_id)
when matched then
update set t1.object_name = t2.object_name;

这种写法不能对on条件中用到的字段进行更新。

写法 2-3 更优，方法 1 会进行两次子查询

写法 4 （快速游标法）: 没用过

方案 建议
标准 update 语法 单表更新或较简单的语句采用使用此方案更优。
inline view 更新法 两表关联且被更新表通过关联表主键关联的，采用此方案更优。
merge 更新法 两表关联且被更新表不是通过关联表主键关联的，采用此方案更优。
快速游标更新法 多表关联且逻辑复杂的，采用此方案更优。

联表删除

创建表 A

key value
a 1
b 1

创建表 B

key value
a 1
c 1

从表 A 中删除，与 B 中 key，value 都相等的数据。

方法 1: 无条件

1
2
3
4
5
6
7
8
9
10
delete from A
where exists(
    select 1 from B where A.key = B.key 
        and A.value = B.value
)
-- 或
delete from A
where rowid in (
    select a.rowid from B where A.key = B.key 
        and A.value = B.value);

方法 2：要求其中一个表要有主键

1
2
3
4
delete from 
(select * from B,A 
where A.key = B.key
    and A.value = B.value);

经测试，删除结果为：当两个表都有主键时删除位置靠前的表的数据，当其中一个表没有主键时删除没有主键的表的数据。

优化

Oracle 数据库性能优化与运维最佳实践

指导思想

自上而下优化以下内容：

  1. 优化应用程序代码之前先优化设计
  2. 优化实例之前先优化代码
    对可以带来最大潜在好处的方面进行优化并确定：
  3. 最长的等待
  4. 最慢的 SQL
    达到目标时停止优化

优化过程

  1. 定义问题并陈述目标
  2. 收集当前性能统计信息
  3. 考虑一些常见的性能错误
  4. 制定适用解决方案
  5. 事实并度量更改
  6. 是否达到目标？完成：转到步骤 3

实践方法

Select Tuning

事实上简单查询时 CBO (Cost-Based Optimization) 已经能够完成优化工作，但在写复杂的嵌套查询是仍需要注意。

from 查询顺序 小表在后

ORACLE 的解析器按照从右到左的顺序处理 FROM 子句中的表名，因此 FROM 子句中写在最后的表（驱动表 driving table) 将被最先处理。在 FROM 子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当 ORACLE 处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表 (FROM 子句中最后的那个表）并对记录进行排序，然后扫描第二个表 (FROM 子句中最后第二个表）, 最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。

如：TAB1 有两千万条，TAB2 中有 1 条

1
2
select count(*) from TAB1, TAB2; --20s
select count(*) from TAB2, TAB1; --40s

如果有 3 个以上的表连接查询，那就需要选择交叉表 (intersection table) 作为基础表，交叉表是指那个被其他表所引用的表。

例如：EMP 表描述了 LOCATION 表和 CATEGORY 表的交集。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT *
FROM LOCATION L ,
       CATEGORY C,
       EMP E
WHERE E.EMP_NO BETWEEN 1000 AND 2000
AND E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
-- 将比下列 SQL 更有效率
SELECT *
FROM EMP E ,
LOCATION L ,
       CATEGORY C
WHERE   E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
AND E.EMP_NO BETWEEN 1000 AND 2000

where 查询顺序

ORACLE 采用自下而上的顺序解析 WHERE 子句，根据这个原理，

  • 表之间的连接必须写在其他 WHERE 条件之前，
  • 可以过滤掉最大数量记录的条件必须写在 WHERE 子句的末尾。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- （低效，执行时间 156.3 秒）
SELECT *
FROM EMP E
WHERE   SAL >50000
AND     JOB = ‘MANAGER’
AND     25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO);
-- （高效，执行时间 10.6 秒）
SELECT *
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
              WHERE MGR=E.EMPNO)
AND     SAL >50000
AND     JOB = ‘MANAGER’;

避免使用 *

ORACLE 在解析的过程中，会将’*’ 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。

在 OLAP 场景下作用不大 ，语句运行的时间远大于解析时间 。

减少访问次数

未实践

当执行每条 SQL 语句时，ORACLE 在内部执行了许多工作：解析 SQL 语句，估算索引的利用率，绑定变量 , 读数据块等等。由此可见，减少访问数据库的次数 , 就能实际上减少 ORACLE 的工作量。

在 SQL*Plus , SQL*Forms 和 Pro*C 中重新设置 ARRAYSIZE 参数，可以增加每次数据库访问的检索数据量 , 建议值为 200.

使用 DECODE 函数来减少处理时间

使用 DECODE 函数可以避免重复扫描相同记录或重复连接相同的表。

Insert Tuning

  • 停用/删除索引和约束，载入数据后重建

a - Disable/drop indexes and constraints - It’s far faster to rebuild indexes after the data load, all at-once. Also indexes will rebuild cleaner, and with less I/O if they reside in a tablespace with a large block size.

  • 管理并行插入时的段头争用

b - Manage segment header contention for parallel inserts - Make sure to define multiple freelist (or freelist groups) to remove contention for the table header. Multiple freelists add additional segment header blocks, removing the bottleneck. You can also use Automatic Segment Space Management (bitmap freelists) to support parallel DML, but ASSM has some limitations.

  • 使用 hint 并行加载

c - Parallelize the load - You can invoke parallel DML (i.e. using the PARALLEL and APPEND hint) to have multiple inserts into the same table. For this INSERT optimization, make sure to define multiple freelists and use the SQL “APPEND” option. Mark Bobak notes that if you submit parallel jobs to insert against the table at the same time, using the APPEND hint may cause serialization, removing the benefit of parallel jobstreams.

  • 使用 append 使表顺序写入硬盘

d - APPEND into tables - By using the APPEND hint, you ensure that Oracle always grabs “fresh” data blocks by raising the high-water-mark for the table. If you are doing parallel insert DML, the Append mode is the default and you don’t need to specify an APPEND hint. Mark Bobak notes “Also, if you’re going w/ APPEND, consider putting the table into NOLOGGING mode, which will allow Oracle to avoid almost all redo logging.”

insert /*+ append */ into customer values ('hello',';there');

  • 使用更大的 blocksize

e - Use a large blocksize - By defining large (i.e. 32k) blocksizes for the target table, you reduce I/O because more rows fit onto a block before a “block full” condition (as set by PCTFREE) unlinks the block from the freelist.

See benchmark test of blocksize and inserts here

See general benefits of multiple blocksizes here

  • 使用 NOLOGGING 参数

f - Use NOLOGGING

  • 使用告诉的硬盘

g- RAM disk - You can use high-speed solid-state disk (RAM-SAN) to make Oracle inserts run up to 300x faster than platter disk.

操作顺序

  • Mark indexes unuasble
  • Disable primary key
  • Alter table nologging
  • Do an insert /*+ append */ into table (select …)
  • Enable primary key
  • Rebuild indexes nologging

参考

Update Tuning

子查询 update 过慢问题

一下两条语句功能完全相同，但在执行效率有巨大差异

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
UPDATE scott.mytables a
SET
    a.tablespace_name = (
        SELECT
            b.tablespace_name
        FROM
            scott.all_tables b
        WHERE
            a.owner = b.owner
            AND a.table_name = b.table_name
    );--3.93s

MERGE INTO scott.mytables a 
USING scott.all_tables b 
ON ( a.owner = b.owner AND a.table_name = b.table_name )
WHEN MATCHED THEN 
UPDATE SET a.tablespace_name = b.tablespace_name;

适用 autotrace 跟踪实际运行 cost（用 10g 貌似跑不了，执行计划的估计有误），结果如下

使用子查询时以外表为条件，循环对内表进行查询（Nested Loop Join），join 的 cost 很高

使用 merg 语句时，使用了 hash join 进行了优化，join 几乎没有什么成本（这一点 oracle 优化得不错，mysql 都还没实现 hash join）

Nested Loop Join/Merge Join/Hash Join

数据库参数调优

sga/pga 分配

Use Subqueries to Count Distinct 50X Faster

https://www.periscopedata.com/blog/use-subqueries-to-count-distinct-50x-faster

IO 优化

通过将数据分布存储至多个硬盘，可以通过并行 IO 提升效率。在建立 datafile 时就要合理分配

Turning the Tables on Disk I/O

spread a table into multiple disk

错误处理

ORA-00257

ORA-00257: archiver error. Connect internal only, until freed.

ORACLE 默认的日志归档路径为闪回恢复区（$ORACLE_BASE/fast_recovery_area）。对于这个路径，Oracle 有一个限制，就是默认只有 4G 的空间，而且不只是归档日志的默认路径，也是备份文件和闪回日志的默认地址，这样的话归档日志锁使用的空间就达不到 4G，在没有设置好这个路径大小的情况下，很多系统都遇到过归档日志满而无法归档导致数据库夯住的问题。

分析

  1. 查看归档日志路径
1
2
3
4
5
6
7
SQL>  archive log list;
数据库日志模式            存档模式
自动存档             启用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     6827
下一个存档日志序列   6827
当前日志序列           6829
  1. 查看 DB_RECOVERY_FILE_DEST 路径
1
2
3
4
5
6
7
SQL> show parameter db_recovery;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      E:\oracle\product\10.2.0/flash
                                                 _recovery_area
db_recovery_file_dest_size           big integer 2G
  1. 查看闪回恢复区的使用情况
1
2
3
4
5
6
7
8
9
10
SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                   0                         0               0
ONLINELOG                     0                         0               0
ARCHIVELOG                98.38                         0              43
BACKUPPIECE                 .69                       .35               2
IMAGECOPY                     0                         0               0
FLASHBACKLOG                  0                         0               0

确定了确实是因为闪回恢复区的空间不足造成的问题

解决方法

  1. 手动扩容
1
alter system set db_recovery_file_dest_size=10G;
  1. 修改日志路径
1
2
3
4
--修改 log_archive_dest_1 值来重新制定路径
SQL> alter system set log_archive_dest_1='location=/u01/oracle/archive';
 
System altered.

重启

ORA-22858: 数据类型的变更无效

原因：Oracle 不允许将字段类型修改为：object、REF、nested table、varchar、clob、blob

解决方法：1. 修改该字段的名称。2. 新建一个正确的字段。3. 将数据同步的到新字段。4. 删除错误字段。