oracle 12c新功能之inmemory option

oracle 12c inmemory功能,不仅仅是将数据放入内存,而且采用了列存储和特殊的查询方式(SIMD vector instructions),可以在短时间内快速进行大量数据的是扫描,因此对于大表的全表扫描速度得到了很大的提升;对于开发者内存数据的访问、更新完全是透明,不需要再应用程序做任何处理的,只需要在数据库中配置哪些表启用inmemory功能。

1、修改数据库参数,以启用数据库的inmemory功能
inmemory使用的是SGA的内存空间,本次测试中inmemory占用内存设置为1G,因此将SGA_MAX_SIZE、SGA_TARGET也扩大了1G

SQL> alter system set sga_max_size = 7g scope=spfile;

System altered.

SQL> alter system set sga_target = 5g scope=spfile;

System altered.

SQL> alter system set inmemory_size=1g scope=spfile;

System altered.

2、重启数据库

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 7516192768 bytes
Fixed Size                  6087600 bytes
Variable Size            3774876752 bytes
Database Buffers         2650800128 bytes
Redo Buffers               10686464 bytes
In-Memory Area           1073741824 bytes
Database mounted.
Database opened.

3、查看inmemory的参数是否生效

SQL> show parameter inmemory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     3
inmemory_query                       string      ENABLE
inmemory_size                        big integer 1G>
inmemory_trickle_repopulate_servers_ integer     1
percent
optimizer_inmemory_aware             boolean     TRUE

4、测试表的行数和占用空间(表记录340万,占用2.3G空间)

CREATE TABLE mara AS SELECT * FROM mara@dl_ods_dev;

SQL> SELECT COUNT(*) FROM mara;
  COUNT(*)
----------
   3419561
SQL> select bytes/1024/1024 as size_mb from user_segments where segment_name = 'MARA';

   SIZE_MB
----------
      2304

5、测试从磁盘读取和内存读取的逻辑读差异
5.1 未使用inmemory选项的时候,全表扫描的逻辑读为292417

SQL> set autotrace traceonly;

SQL> select count(*) from mara;
Execution Plan
----------------------------------------------------------
Plan hash value: 781138304
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 | 79487   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MARA |  3419K| 79487   (1)| 00:00:04 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     292417  consistent gets
     292410  physical reads
          0  redo size
        545  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

5.2 修改表定义,启用inmemory功能

SQL> alter table mara inmemory priority high;
Table altered.

5.3 查看表的inmemory特性的状态,(正在向内存中同步数据,状态为STARTED)

SQL> col owner format a30
SQL> col segment_name format a30
SQL> select owner, segment_name, populate_status status from v$im_segments;

OWNER                          SEGMENT_NAME                   STATUS
------------------------------ ------------------------------ ---------
ADAM                           MARA                           STARTED

5.4 内存同步完成后,重新查看表inmemory特性的状态

SQL> select owner, segment_name, t.inmemory_size/1024/1024 memory_size, t.bytes/1024/1024 table_size, populate_status status from v$im_segments t;

OWNER           SEGMENT_NAME    MEMORY_SIZE TABLE_SIZE STATUS
--------------- --------------- ----------- ---------- ---------
ADAM            MARA               183.6875       2304 COMPLETED

5.5 重新运行查询语句,记录逻辑读(23)

SQL> select count(*) from mara;
Execution Plan
----------------------------------------------------------
Plan hash value: 781138304
----------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |  3253   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE             |      |     1 |            |          |
|   2 |   TABLE ACCESS INMEMORY FULL| MARA |  3419K|  3253   (4)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
         24  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
        545  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

6. 总结
原表记录数3419561,占用空间2304M,全表扫描一次逻辑读次数292417,花费时间1.83秒
使用inmemory特性后,占用空间184M,占原表大小约8%,内存中全表扫描一次逻辑读次数23,是直接扫描表方式的万分之一,花费时间0.11秒
根据inmemory的特点,比较适合用于从大量数据进行筛选、过滤,返回结果较少场景,可以节省因创建大量不同查询条件索引占用的空间,提升查询效率和响应时间

参考资料:
Ask Tom:On Oracle Database In-Memory
How do i configure Oracle database 12c In-memory Option
Getting started with Oracle Database In-Memory Part I – Installing & Enabling
Getting started with Oracle Database In-Memory Part II – In-Memory Population
Getting started with Oracle Database In-Memory Part III – Querying The IM Column Store
Getting started with Oracle Database In-Memory Part IV – Joins In The IM Column Store
Getting started with Oracle Database In-Memory Part V – Controlling Access

使用sqlplus导出文本文件的脚本

--不显示执行的sql语句
set echo off
--不显示XX rows selected的行
set feedback off
--不显示表头
set heading off
--不显示脚本运行结果
set termout off
--去掉脚本输出行尾部的空格
set trimspool on
--设置pagesize为零,不让oracle进行分页处理
set pagesize 0
--设置每行最大列数
set linesize 10000
--设置列之间的分隔符
set colsep '    '
--CLOB导出的设置
set long 20000
set longchunksize 20000

spool /home/oracle/tj30t.txt
SELECT * from bi_ods.tj30t;
spool off

在Oracle中调用操作系统命令

1、准备工作
修改当前用户的权限,可以创建JOB、创建外部命令的JOB

GRANT CREATE ANY JOB TO user_name;
GRANT CREATE EXTERNAL JOB TO user_name;

设置执行操作系统命令的用户

vi $ORACLE_HOME/rdbms/admin/externaljob.ora

将run_user和run_group设置为oracle所在的用户和组

参考资料:
Guide to External Jobs on 10g with dbms_scheduler

2、创建JOB

  v_job_name := 'JOB_'
    || to_char(SYSDATE, 'YYYYMMDD_HH24MISS');
  dbms_scheduler.create_job(
    job_name  => v_job_name,
    job_type  => 'EXECUTABLE',
    number_of_arguments => 2,
    job_action => '/home/oracle/bin/run_etl_session.sh',
    auto_drop  => TRUE
  );

  dbms_scheduler.set_attribute(
    name     => v_job_name,
    attribute => 'MAX_RUNS',
    value    => 1
  );

注意:JOB_NAME不能有特殊字符,建议为字母、数字或下划线,实际测试中不允许名字中包含横线。

3、JOB设置参数

  dbms_scheduler.set_job_argument_value(
    job_name  => v_job_name,
    argument_position => 1,
    argument_value => in_session_name
  );

  dbms_scheduler.set_job_argument_value(
    job_name  => v_job_name,
    argument_position => 2,
    argument_value => in_workflow_name
  );

4、运行JOB

  dbms_scheduler.run_job(
    job_name => v_job_name
  );

5、如何执行另外一台服务器上的命令
首先需要设置可以通过证书直接登录目标服务器:

ssh-keygen -t rsa
ssh-copy-id -i ~/.ssh/id_rsa.pub remote-host

通过SSH执行远程服务器上的命令

ssh ${ETL_SERVER} "${command} ${param...}"

注意:远程登录后用户的参数文件未加载,相关的环境变量不正确;为保证调用脚本可以正常运行,建议在目标服务器上将执行的命令封装为脚本,在脚本中将bash_profile中相关的环境变量在脚本中重新设置。

6、查询JOB的运行情况

SELECT * FROM user_scheduler_jobs;
SELECT * FROM user_scheduler_job_run_details;

Oracle中日期运算实例

下面的例子列出常用的按照年、月、周进行计算的函数,假设当天为2016-02-03

SELECT
  today,                                        --当天
  add_months(today, -2) AS today_of_2month_ago, --上2个月的当天,跨年
  trunc(today, 'mm') AS first_day_month,        -- 2月的第一天
  last_day(today) AS last_day_month,            -- 2月的最后一天
  trunc(today, 'y') AS first_day_year,          -- 当年的第一天
  TRUNC(today, 'd') AS first_day_week,          -- 当天所在周的第一天
  next_day(today, 6) AS next_friday             -- 当天之后的第一个周五,从周日到周六按1-表示
FROM (
  SELECT to_date('2016-02-03', 'yyyy-mm-dd') AS today
  FROM dual
);

运行结果:

TODAY TODAY_OF_2MONTH_AGO FIRST_DAY_MONTH LAST_DAY_MONTH FIRST_DAY_YEAR FIRST_DAY_WEEK NEXT_FRIDAY
2016/2/3 2015/12/3 2016/2/1 2016/2/29 2016/1/1 2016/1/31 2016/2/5

 

PL/SQL函数缓存测试

一、创建测试的表,并初始化测试数据

CREATE TABLE tmp_sales_orders(sales_year INTEGER, amount NUMBER(10,2));

INSERT INTO tmp_sales_orders VALUES('2010', 500);
INSERT INTO tmp_sales_orders VALUES('2013', 1500);
INSERT INTO tmp_sales_orders VALUES('2014', 1000);
INSERT INTO tmp_sales_orders VALUES('2015', 2000);
INSERT INTO tmp_sales_orders VALUES('2016', 3000);

COMMIT;

二、创建带缓存的函数

create or replace function fn_get_sales_amount_with_cache(in_sales_year varchar2)
return number
result_cache
is
  Result number;
begin
  SELECT amount
  INTO Result
  FROM tmp_sales_orders
  WHERE sales_year = in_sales_year;

  return(Result);
end fn_get_sales_amount_with_cache;

三、执行结果和统计信息

select fn_get_sales_amount_with_cache('2014') from dual;

第一次:

Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        372  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

第二次查询:

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        372  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

三、表新增或更新数据,缓存将失效

update tmp_sales_orders set amount = 5000 where sales_year = '2014';
commit;

再次运行同样的语句,发现consistent gets不为零,说明缓存失效,oracle重新从表读取了数据

select fn_get_sales_amount_with_cache('2014') from dual;
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        372  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQLSERVER的CTE递归查询

SQLSERVERCTE递归查询,虽然语法上比Oracle的麻烦一些,但本身比较简单,下面这个例子主要是取出顶层节点、层级数,额外增加了一点代码

创建测试表:

CREATE TABLE tmp_t1 (id VARCHAR(10), name VARCHAR(30), pid VARCHAR(10));

插入测试数据

INSERT INTO tmp_t1 VALUES('A', 'XX集团', NULL);
INSERT INTO tmp_t1 VALUES('A1', '公司1', 'A');
INSERT INTO tmp_t1 VALUES('A11', '公司1-部门1', 'A1');
INSERT INTO tmp_t1 VALUES('A111', '公司1-部门1-模块1', 'A11');
INSERT INTO tmp_t1 VALUES('A12', '体系1-部门2', 'A1');
INSERT INTO tmp_t1 VALUES('A2', '公司2', 'A');
INSERT INTO tmp_t1 VALUES('A21', '公司2-部门1', 'A2');
INSERT INTO tmp_t1 VALUES('A22', '公司2-部门2', 'A2');

查询语句:

WITH org AS (
 SELECT id AS start_id, id, name, pid, 1 AS level
 FROM tmp_t1
 WHERE pid = 'A'
 UNION ALL
 SELECT t2.start_id, t1.id, t1.name, t1.pid, t2.level + 1 AS level
 FROM tmp_t1 t1 INNER JOIN org t2
   ON t1.pid = t2.id
)
SELECT
*
FROM org
where level <=2

运行结果:

start_id id name pid level
A1 A1 公司1 A 1
A2 A2 公司2 A 1
A2 A21 公司2-部门1 A2 2
A2 A22 公司2-部门2 A2 2
A1 A11 公司1-部门1 A1 2

 

PL/SQL的常用设置

一、显示所有打开的窗口,并永久生效
菜单:Tools -> Window List
菜单:Window -> Save Layout

二、默认显示My Objects,提高数据库对象的展开速度
菜单:Tools -> Browser Filters,将My Objects设置为默认

三、调整数据库对象的显示顺序,最常用的放在前面
菜单:Tools -> Browser Folders
建议显示顺序为:Recent Objects | Tables | Sequences | Views | Tablespaces | Fucntions | Procedures | Tiggers | Database Links | Jobs | Users | Recycle bin,其他顺序不变,可以根据自己的习惯调整。

四、编辑器设置:关键字自动转化为大写
菜单:Tools -> Preferences -> User Interface -> Editor -> Keyword case

五、编辑器设置:用缩写完成常用的语句
菜单:Tools -> Preferences -> User Interface -> Editor -> AutoReplace

个人配置为:
i=INSERT INTO
s=SELECT
u=UPDATE
d=DELETE
sf = SELECT * FROM
sc = SELECT COUNT(*) FROM
cnt = COUNT(*)

六、为常用操作设置快捷键
菜单:Tools -> Preferences -> User Interface -> Key Congfiguration

个人配置为:
新建 Sql Windows Shfit + Ctrl + s
新建 Command Windows Shfit + Ctrl + w
关闭当前窗口 Ctrl+ w

七、记录历史登录的口令,这样就不用每次都输入口令了
菜单:Tools -> Preferences -> Oracle -> Logon History

[转]TCP的三次握手和四次分手

原文地址:简析TCP的三次握手与四次分手

下面是最核心的一张图:

三次握手过程

  1. 第一次握手:建立连接。客户端发送连接请求报文段,将SYN位置为1,Sequence Number为x;然后,客户端进入SYN_SEND状态,等待服务器的确认;
  2. 第二次握手:服务器收到SYN报文段。服务器收到客户端的SYN报文段,需要对这个SYN报文段进行确认,设置Acknowledgment Number为x+1(Sequence Number+1);同时,自己自己还要发送SYN请求信息,将SYN位置为1,Sequence Number为y;服务器端将上述所有信息放到一个报文段(即SYN+ACK报文段)中,一并发送给客户端,此时服务器进入SYN_RECV状态;
  3. 第三次握手:客户端收到服务器的SYN+ACK报文段。然后将Acknowledgment Number设置为y+1,向服务器发送ACK报文段,这个报文段发送完毕以后,客户端和服务器端都进入ESTABLISHED状态,完成TCP三次握手。

四次分手过程

当客户端和服务器通过三次握手建立了TCP连接以后,当数据传送完毕,肯定是要断开TCP连接的啊。那对于TCP的断开连接,这里就有了神秘的“四次分手”。

  1. 第一次分手:主机1(可以使客户端,也可以是服务器端),设置Sequence Number和Acknowledgment Number,向主机2发送一个FIN报文段;此时,主机1进入FIN_WAIT_1状态;这表示主机1没有数据要发送给主机2了;
  2. 第二次分手:主机2收到了主机1发送的FIN报文段,向主机1回一个ACK报文段,Acknowledgment Number为Sequence Number加1;主机1进入FIN_WAIT_2状态;主机2告诉主机1,我也没有数据要发送了,可以进行关闭连接了;
  3. 第三次分手:主机2向主机1发送FIN报文段,请求关闭连接,同时主机2进入CLOSE_WAIT状态;
  4. 第四次分手:主机1收到主机2发送的FIN报文段,向主机2发送ACK报文段,然后主机1进入TIME_WAIT状态;主机2收到主机1的ACK报文段以后,就关闭连接;此时,主机1等待2MSL后依然没有收到回复,则证明Server端已正常关闭,那好,主机1也可以关闭连接了。

按照SQL的方式操作PLSQL中的集合变量

直接上程序代码,唯一感觉不爽的地方就是集合类型要在数据库级别定义。根据ORACLE PL/SQL Programming书上的介绍,在Oracle 12c中,table语句支持在package中定义的集合变量,因无12c的环境尚未实际验证。

定义数据库级别的对象和集合类型

CREATE OR REPLACE TYPE obj_tj30t AS OBJECT(
  estat VARCHAR2(15),
  txt04 VARCHAR2(12),
  txt30 VARCHAR2(90)
);
CREATE OR REPLACE TYPE list_of_tj30t IS TABLE OF obj_tj30t;

使用table语句,让oracle将集合在系统内容转换为虚拟的数据库表,然后可以用sql的方式操作集合变量

DECLARE
  vt_tj30t list_of_tj30t := list_of_tj30t();
BEGIN
  vt_tj30t.extend(3);
  vt_tj30t(1) := obj_tj30t('E0001', 'STATUS01', '状态01');
  vt_tj30t(2) := obj_tj30t('E0003', 'STATUS03', '状态03');
  vt_tj30t(3) := obj_tj30t('E0005', 'STATUS05', '状态05');

-- 人工插入一条重复记录,在后面使用set函数去重
  vt_tj30t.extend();
  vt_tj30t(4) := obj_tj30t('E0005', 'STATUS05', '状态05');

  FOR v_row IN (
    SELECT *
-- 使用set函数去重
    FROM TABLE(set(vt_tj30t))
    WHERE estat > 'E0002'
    ORDER BY txt30 DESC
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(v_row.txt30);
  END LOOP;
END;

Oracle存储中的集合类型测试

主要测试了Associative array和Nested Table两种类型。

create or replace procedure sp_test_collection
IS
    v_index PLS_INTEGER;
    v_string_index VARCHAR2(20);

--Associative array的作用,类似于Java中的Map对象
    TYPE ty_ht_name IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
    ht_name ty_ht_name;

--Associative array的下标,可以使字符串
    TYPE ty_ht_string IS TABLE OF VARCHAR2(20) INDEX BY VARCHAR2(10);
    ht_string ty_ht_string;

-- Nested Table定义方式和Associative array类似,只是缺少了INDEX BY部分的定义
    TYPE ty_lt_name IS TABLE OF VARCHAR2(20);
    lt_name ty_lt_name;

    TYPE ty_at_name IS VARRAY(5) OF VARCHAR2(20);
    at_name ty_at_name;
BEGIN
--Associative array的下标,可以使负值,且不连续
    ht_name(-1) := 'Unknown NAM';
    ht_name(1) := 'NAME1';
    ht_name(9) := 'NAME9';

    v_index := ht_name.FIRST;
--因下标不连续,不能使用FOR的遍历方式
    WHILE (v_index IS NOT NULL)
    LOOP
        DBMS_OUTPUT.put_line(v_index || '--' || ht_name(v_index));
        v_index := ht_name.NEXT(v_index);
    END LOOP;
    DBMS_OUTPUT.put_line('---------');

--访问字符串下标的Associative array
    ht_string('john') := 'John Kennedy';
    ht_string('obama') := 'Barack Obama ';

    DBMS_OUTPUT.put_line('记录数量:' || ht_string.COUNT);
    v_string_index := ht_string.FIRST;
    WHILE (v_string_index IS NOT NULL)
    LOOP
        DBMS_OUTPUT.put_line(v_string_index || '--' || ht_string(v_string_index));
        v_string_index := ht_string.NEXT(v_string_index);
    END LOOP;

--判断是否包含某个元素
    IF ht_string.EXISTS('john') THEN
       DBMS_OUTPUT.put_line('john is in ht_string');
    END IF;
    DBMS_OUTPUT.put_line('---------');

--Nested table, 必须初始化且Extend扩展后才能使用
    lt_name := ty_lt_name('NAME1', 'NAME2', 'NAME3');
    lt_name.extend(4);
    lt_name(4) := 'NAME4';
    lt_name(6) := 'NAME6';

    DBMS_OUTPUT.put_line('Nested table最大下标为:' || lt_name.LAST);
    DBMS_OUTPUT.put_line('Nested table当前个数为:' || lt_name.COUNT);

--Nested table extend后未赋值的数组元素,其值为NULL
    IF (lt_name(5) IS NULL) THEN
        DBMS_OUTPUT.put_line('第5个元素IS NULL');
    END IF;

    lt_name.DELETE(4);
--Nested table的某个下标的元素,赋值后再删除,不能再使用下标访问
    BEGIN
      FOR v_index1 IN lt_name.FIRST .. lt_name.LAST LOOP
          v_index := v_index1;
          DBMS_OUTPUT.put_line(v_index || '--' || lt_name(v_index));
      END LOOP;
    EXCEPTION
      WHEN OTHERS THEN
           DBMS_OUTPUT.put_line('访问第' || v_index || '个元素出错: ' || SQLERRM);
    END;
    DBMS_OUTPUT.put_line('---------');

    v_index := lt_name.FIRST;
    WHILE (v_index IS NOT NULL)
    LOOP
        DBMS_OUTPUT.put_line(v_index || '--' || lt_name(v_index));
        v_index := lt_name.NEXT(v_index);
    END LOOP;
    DBMS_OUTPUT.put_line('---------');

--varray, 必须初始化且Extend扩展后才能使用
    at_name := ty_at_name();
    at_name.EXTEND(4);
    at_name(1) := 'NAME1';
    at_name(3) := 'NAME3';

--varray extend后未赋值的数组元素,其值为NULL
    IF (at_name(2) IS NULL) THEN
        DBMS_OUTPUT.put_line('第2个元素IS NULL');
    END IF;

    DBMS_OUTPUT.put_line('varray最大个数为:' || at_name.LIMIT);
    DBMS_OUTPUT.put_line('varray当前个数为:' || at_name.COUNT);

--varray 有删除操作后,其索引仍然是连续的
    FOR v_index IN at_name.FIRST .. at_name.LAST LOOP
        DBMS_OUTPUT.put_line(v_index || '--' || at_name(v_index));
    END LOOP;
end sp_test_collection;