oracle 12c的cdb、pdb简介

oracle 12c引入了多租户环境,允许一个数据库容器CDB承载多个可插拔数据库PDB,CDB全称为Container Database翻译为数据库容器,PDB全称为Pluggable Database翻译为可插拔数据库,在oracle 12c之前实例与数据库是一对一或者多对一的关系,而在oracle 12c之后实例与数据库可以实现一对多的关系,也就是一个CDB,多个PDB,这个其实如果大家有接触过mysql、sqlserver这类数据库,对于这个概念就很好理解了,摘要下官方文档关于CDB和PDB的关系图:

cdb、pdb

CDB组成:
root组件:也就是CDB$ROOT,存储着ORACLE提供的元数据库和common user,元数据比如oracle提供的pl/sql包的源代码,common user是指每个容器中都存在的用户
SEED组件:Seed又叫做PDB$SEED,这个是创建PDBS数据库的模板,不能在Seed中添加或者修改对应,这个基本类似于sql server的module数据库
PDBS组件:具体的PDB数据库

ROOT、Seed、PDB关系可以这么简单描述:ROOT(根)是一个容器,Seed(种子)是一个容器,每个PDB是一个容器。每个容器在CDB中都有一个独一无二的的ID和名称。

查看数据库是否为CDB
SQL> select name,cdb from v$database;

NAME      CDB
--------- ---
ORA12C    YES

YES表示该数据库是CDB,NO表示是NO_CDB(普通数据库)

查看对应的PDB名称:
SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL> select sys_context('userenv','con_name') "Container DB" from dual;

Container DB
--------------------------------------------------------------------------------
CDB$ROOT

SQL> select con_id,dbid,name,open_mode from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 3962890709 PDB$SEED                       READ ONLY
         3 2723517908 PDB1                           MOUNTED
         4  369175303 PDB2                           MOUNTED
启动和关闭对应的PDBS
SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> select con_id,dbid,name,open_mode from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 3962890709 PDB$SEED                       READ ONLY
         3 2723517908 PDB1                           READ WRITE
         4  369175303 PDB2                           MOUNTED

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> select con_id,dbid,name,open_mode from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 3962890709 PDB$SEED                       READ ONLY
         3 2723517908 PDB1                           MOUNTED
         4  369175303 PDB2                           MOUNTED

切换到对应的PDBS
SQL> alter session set container=pdb1;

Session altered.

SQL> alter database open;

Database altered.

SQL> select con_id,dbid,name,open_mode from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         3 2723517908 PDB1                           READ WRITE
查看cdb、pdb模式下数据文件、日志文件、undo tablespace、控制文件等变化:
切换到cdb$root下:
SQL>conn / as sysdba
SQL> select con_id,name from v$datafile order by con_id,name;

    CON_ID NAME
---------- ----------------------------------------------------------------------------------------------------
         1 /oracle12c/app/oracle/oradata/ora12c/sysaux01.dbf
         1 /oracle12c/app/oracle/oradata/ora12c/system01.dbf
         1 /oracle12c/app/oracle/oradata/ora12c/undotbs01.dbf
         1 /oracle12c/app/oracle/oradata/ora12c/users01.dbf
         2 /oracle12c/app/oracle/oradata/ora12c/pdbseed/sysaux01.dbf
         2 /oracle12c/app/oracle/oradata/ora12c/pdbseed/system01.dbf
         3 /oracle12c/app/oracle/oradata/ora12c/pdb1/pdb1_users01.dbf
         3 /oracle12c/app/oracle/oradata/ora12c/pdb1/sysaux01.dbf
         3 /oracle12c/app/oracle/oradata/ora12c/pdb1/system01.dbf
         4 /oracle12c/app/oracle/oradata/ora12c/pdb2/pdb2_users01.dbf
         4 /oracle12c/app/oracle/oradata/ora12c/pdb2/sysaux01.dbf
         4 /oracle12c/app/oracle/oradata/ora12c/pdb2/system01.dbf

12 rows selected.

SQL> select con_id,name from v$controlfile order by con_id,name;

    CON_ID NAME
---------- ----------------------------------------------------------------------------------------------------
         0 /oracle12c/app/oracle/fast_recovery_area/ora12c/control02.ctl
         0 /oracle12c/app/oracle/oradata/ora12c/control01.ctl
SQL> select con_id,member from v$logfile;

    CON_ID MEMBER
---------- ----------------------------------------------------------------------------------------------------
         0 /oracle12c/app/oracle/oradata/ora12c/redo03.log
         0 /oracle12c/app/oracle/oradata/ora12c/redo02.log
         0 /oracle12c/app/oracle/oradata/ora12c/redo01.log

SQL> select con_id,name from v$tempfile;

    CON_ID NAME
---------- ----------------------------------------------------------------------------------------------------
         1 /oracle12c/app/oracle/oradata/ora12c/temp01.dbf
         2 /oracle12c/app/oracle/oradata/ora12c/pdbseed/pdbseed_temp012016-07-17_09-08-17-AM.dbf
         3 /oracle12c/app/oracle/oradata/ora12c/pdb1/temp012016-07-17_09-08-17-AM.dbf
         4 /oracle12c/app/oracle/oradata/ora12c/pdb2/temp012016-07-17_09-08-17-AM.dbf

这里可以看出CDB、PDB模式下,每个PDB都有自己的数据文件、临时文件,所有的PDB共享相同的日志文件、控制文件
[oracle@oracle12c ~]$ ls -l /oracle12c/app/oracle/product/12.1.0/dbhome_1/dbs  
total 17576
-rw-rw----. 1 oracle dba     1544 Jul 20 03:36 hc_ora12c.dat
-rw-r--r--. 1 oracle dba     2992 Feb  3  2012 init.ora
-rw-r-----. 1 oracle dba       24 Jun 25 13:12 lkORA12C
-rw-r-----. 1 oracle dba     7680 Jul 17 09:12 orapwora12c
-rw-r-----. 1 oracle dba 17973248 Jul 17 09:26 snapcf_ora12c.f
-rw-r-----. 1 oracle dba     3584 Jul 20 08:54 spfileora12c.ora

[oracle@oracle12c ~]$ ls -l /oracle12c/app/oracle/product/12.1.0/dbhome_1/network/admin/
total 20
-rw-r-----. 1 oracle dba  343 Jul  7 03:00 listener.ora
drwxr-xr-x. 2 oracle dba 4096 Jun 25 11:56 samples
-rw-r--r--. 1 oracle dba  373 Oct 31  2013 shrept.lst
-rw-r-----. 1 oracle dba  208 Jul  7 03:00 sqlnet.ora
-rw-r-----. 1 oracle dba  420 Jul 17 09:12 tnsnames.ora

[oracle@oracle12c ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 20-JUL-2016 08:59:47

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                20-JUL-2016 08:59:34
Uptime                    0 days 0 hr. 0 min. 12 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle12c/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /oracle12c/app/oracle/diag/tnslsnr/oracle12c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c)(PORT=5500))(Security=(my_wallet_directory=/oracle12c/app/oracle/product/12.1.0/dbhome_1/admin/ora12c/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ora12c" has 1 instance(s).
  Instance "ora12c", status READY, has 1 handler(s) for this service...
Service "ora12cXDB" has 1 instance(s).
  Instance "ora12c", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "ora12c", status READY, has 1 handler(s) for this service...
Service "pdb2" has 1 instance(s).
  Instance "ora12c", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> select name,con_Id from v$active_services order by 2;

NAME                                                                 CON_ID
---------------------------------------------------------------- ----------
ora12cXDB                                                                 1
SYS$BACKGROUND                                                            1
SYS$USERS                                                                 1
ora12c                                                                    1
pdb1                                                                      3
pdb2                                                                      4

6 rows selected.

同样也只有一个spfile文件、控制文件快照、密码文件,监听程序会将所有的PDB对应的services注册到监听程序中,可以连接到cdb$root,也可以连接到指定的pdb
可以用os认证、ezconnect方式连接到cdb$root环境下:
SQL> conn / as sysdba
Connected.
SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL> conn sys/oracle@192.168.212.139:1521/ora12c as sysdba
Connected.
SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

用ezconnect连接PDBS
SQL> conn sys/oracle@192.168.212.139:1521/pdb1 as sysdba
Connected.
SQL> show con_name;
CON_NAME
------------------------------
PDB1
SQL> conn sys/oracle@192.168.212.139:1521/pdb2 as sysdba
Connected.
SQL> show con_name;

CON_NAME
------------------------------
PDB2
创建pdb
SQL>conn / as sysdba
SQL> create pluggable database pdb3 admin user pdb_admin identified by password roles=(dba) file_name_convert=('/oracle12c/app/oracle/oradata/ora12c/pdbseed','/oracle12c/app/oracle/oradata/ora12c/pdb3');

Pluggable database created.
SQL> select * from cdb_pdbs;

    PDB_ID PDB_NAME         DBID    CON_UID GUID                             STATUS    CREATION_SCN        VSN LOGGING   FOR FOR     CON_ID
---------- ---------- ---------- ---------- -------------------------------- --------- ------------ ---------- --------- --- --- ----------
         3 PDB1       2723517908 2723517908 37CB98E04474679CE0538BD4A8C07711 NORMAL         1741469  202375680 LOGGING   NO  NO           3
         2 PDB$SEED   3962890709 3962890709 37CB7CA2D31D63D9E0538BD4A8C03C51 NORMAL         1594409  202375680 LOGGING   NO  NO           2
         4 PDB2        369175303  369175303 37CB997769736856E0538BD4A8C08647 NORMAL         1742072  202375680 LOGGING   NO  NO           4
         5 PDB3       2895601090 2895601090 3807F68447B4320DE0538BD4A8C05719 NEW            2000447  202375680 LOGGING   NO  NO           5

SQL> !lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 20-JUL-2016 09:20:58

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                20-JUL-2016 08:59:34
Uptime                    0 days 0 hr. 21 min. 24 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle12c/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /oracle12c/app/oracle/diag/tnslsnr/oracle12c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c)(PORT=5500))(Security=(my_wallet_directory=/oracle12c/app/oracle/product/12.1.0/dbhome_1/admin/ora12c/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ora12c" has 1 instance(s).
  Instance "ora12c", status READY, has 1 handler(s) for this service...
Service "ora12cXDB" has 1 instance(s).
  Instance "ora12c", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "ora12c", status READY, has 1 handler(s) for this service...
Service "pdb2" has 1 instance(s).
  Instance "ora12c", status READY, has 1 handler(s) for this service...
Service "pdb3" has 1 instance(s).
  Instance "ora12c", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> select name,con_id from v$active_services;

NAME                                                                 CON_ID
---------------------------------------------------------------- ----------
pdb3                                                                      5
pdb2                                                                      4
pdb1                                                                      3
ora12cXDB                                                                 1
ora12c                                                                    1
SYS$BACKGROUND                                                            1
SYS$USERS                                                                 1

7 rows selected.

创建新的pdb后,监听程序也会自动将这个pdb的services注册到监听程序中
需要注意的是在数据库重新启动后PDB默认是mounted状态,需要手动将PDB open,也可以直接写database级别的trigger,在数据库每次启动后将所有的pdb open
SQL> conn / as sysdba
Connected.
SQL> startup force;
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size                  2925024 bytes
Variable Size            1040191008 bytes
Database Buffers          587202560 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.
SQL> select open_mode from v$pdbs;

OPEN_MODE
----------
READ ONLY
MOUNTED
MOUNTED
MOUNTED
SQL> alter pluggable database all open;

Pluggable database altered.
修改pdb的名称
SQL> conn sys/oracle@192.168.212.139:1521/pdb3 as sysdba
Connected.
SQL> alter pluggable database pdb3 rename global_name to pdb_his;
alter pluggable database pdb3 rename global_name to pdb_his
                                                    *
ERROR at line 1:
ORA-65045: pluggable database not in a restricted mode

这里修改pdb的名称需要将对应的pdb open restricted模式下
SQL> alter pluggable database pdb3 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb3 open restricted;

Pluggable database altered.

SQL> alter pluggable database pdb3 rename global_name to pdb_his;

Pluggable database altered.

SQL> alter pluggable database close immediate;

Pluggable database altered.

SQL> alter pluggable database open;

Pluggable database altered.
创建用户:
SQL> conn / as sysdba
Connected.
SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE
PDB_HIS                        READ WRITE

在root中创建common user,用户必须以c##开头,在root中创建的用户会级联复制到所有的pdbs中,除了seed pdb
SQL> create user c##xiaoyu identified by xiaoyu container=all;

User created.

SQL> select username,common,con_id from cdb_users where username='C##XIAOYU';

USERNAME                                                                                                                         COM     CON_ID
-------------------------------------------------------------------------------------------------------------------------------- --- ----------
C##XIAOYU                                                                                                                        YES          1
C##XIAOYU                                                                                                                        YES          5
C##XIAOYU                                                                                                                        YES          3
C##XIAOYU                                                                                                                        YES          4

common user登陆到cdb$root和pdbs中
SQL> conn c##xiaoyu/xiaoyu;
ERROR:
ORA-01045: user C##XIAOYU lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn c##xiaoyu/xiaoyu@192.168.212.139:1521/pdb1
ERROR:
ORA-01045: user C##XIAOYU lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

登陆cdb$root给common user授权
SQL> conn / as sysdba
Connected.
SQL> grant dba to c##xiaoyu;

Grant succeeded.

SQL> conn c##xiaoyu/xiaoyu
Connected.

common user在cdb$root中权限默认并不级联到pdb中,需要在pdb中重新授权
SQL> conn c##xiaoyu/xiaoyu@192.168.212.139:1521/pdb1
ERROR:
ORA-01045: user C##XIAOYU lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.

SQL> conn sys/oracle@192.168.212.139:1521/pdb1 as sysdba
Connected.
SQL> grant dba to c##xiaoyu;

Grant succeeded.
SQL> conn c##xiaoyu/xiaoyu@192.168.212.139:1521/pdb1
Connected.

当然还是跟授权的SQL语句有关,比如在授权时添加container=all,该全选则会级联到所有的pdbs中
SQL> conn / as sysdba
Connected.
SQL> grant dba to c##xiaoyu container=all;

Grant succeeded.

SQL> conn c##xiaoyu/xiaoyu@192.168.212.139:1521/pdb2
Connected.
删除pdb
SQL> drop pluggable database pdb_his including datafiles;
drop pluggable database pdb_his including datafiles
*
ERROR at line 1:
ORA-65025: Pluggable database PDB_HIS is not closed on all instances.


SQL> alter pluggable database pdb_his close immediate;

Pluggable database altered.

SQL> drop pluggable database pdb_his including datafiles;

Pluggable database dropped.


这里简单先介绍下cdb、pdb的概念和常规管理,后续会陆续推出一些cdb、pdb的简单应用的文章!
Posted in oracle | oracle 12c的cdb、pdb简介已关闭评论

绑定变量知识—绑定变量的分级和捕获

绑定变量分级是oracle在pl/sql中会根据文本类型的绑定变量的定义长度而将这些文本型绑定变量分为四个等级:

1 定义长度在32字节(bytes)以内的文本型绑定变量被分在第一个等级,oracle为其分配32字节的内存空间
2 定义长度在33-128字节之间的被分为第二个等级,oracle为其分配128字节的内存空间
3 定义长度在129-2000字节之间的文本型被分为在第三个等级,oracle为其分配2000字节的内存空间
4 定义长度在2000字节以上的被分为在第四个等级,取决于对应文本类型的绑定变量锁传入的实际绑定变量的大小,如果实际传入的绑定变量的值小于或者等于2000,oracle为其分配2000字节的内存空间,如果实际传入的绑定变量大于2000字节,则oracle为其分配4000字节的内存空间

需要注意的是这里的绑定变量分级仅仅适用于文本类型的绑定变量,对于number类型的绑定变量oracle统一为其分配22字节的内存空间。

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL_TEXT1:
declare
n number(10);
v varchar2(32);
begin
n:=1;
v:='xiaoyu';
execute immediate 'insert into t values(:n,:v)' using n,v;
commit;
end;
/

SQL_TEXT2:
declare
n number(10);
v varchar2(80);
begin
n:=1;
v:='xiaoyu';
execute immediate 'insert into t values(:n,:v)' using n,v;
commit;
end;
/

SQL_TEXT3:
declare
n number(10);
v varchar2(2000);
begin
n:=1;
v:='xiaoyu';
execute immediate 'insert into t values(:n,:v)' using n,v;
commit;
end;
/

SQL_TEXT4:
declare
n number(10);
v varchar2(4000);
begin
n:=1;
v:='xiaoyu';
execute immediate 'insert into t values(:n,:v)' using n,v;
commit;
end;
/

SQL_TEXT5:
declare
n number(10);
v varchar2(4000);
begin
n:=1;
v:=rpad('xiaoyu',3000);
execute immediate 'insert into t values(:n,:v)' using n,v;
commit;
end;
/

SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'insert into t%';

SQL_TEXT                                 SQL_ID        VERSION_COUNT EXECUTIONS
---------------------------------------- ------------- ------------- ----------
insert into t values(:n,:v)              21mycdpm39kzv             3          5

SQL> select sql_id,child_number,child_address,executions from v$sql where sql_id='21mycdpm39kzv';

SQL_ID        CHILD_NUMBER CHILD_ADDRESS    EXECUTIONS
------------- ------------ ---------------- ----------
21mycdpm39kzv            0 000000008A758A48          1
21mycdpm39kzv            1 000000008A755890          3
21mycdpm39kzv            2 000000008A753240          1

SQL> select address,bind_name,position,datatype,max_length from v$sql_bind_metadata where address in ('000000008A758A48','000000008A755890','000000008A753240') order by address,position;

ADDRESS          BIND_NAME                        POSITION   DATATYPE MAX_LENGTH
---------------- ------------------------------ ---------- ---------- ----------
000000008A753240 N                                       1          2         22
000000008A753240 V                                       2          1       4000
000000008A755890 N                                       1          2         22
000000008A755890 V                                       2          1        128
000000008A758A48 N                                       1          2         22
000000008A758A48 V                                       2          1         32

6 rows selected.

自己在11.2.0.4环境中对于SQL语句测试绑定变量分级,发现并不绝对遵守上面的规则来生成child cursor,当然还是建议SQL语句或者PL/SQL中对于相同的绑定变量,申明的数据类型和长度必须统一,避免因为绑定变量分级而产生过多的child cursor。其原因是因为child cursor不仅仅存储了解析树和执行计划,还会存储该SQL所使用的绑定变量类型和长度,如果该SQL的绑定变量类型和长度发生变化,则oracle将无法重用该child cursor,必须为该SQL再生成一个child cursor。

绑定变量何时被捕获:
1 含有绑定变量的sql语句被硬解析时
2 当含有绑定变量的sql语句以软解析或者软软解析方式重复执行时,该sql语句中的绑定变量的具体输入值也可能被oracle捕获,只不过默认情况下这种捕获操作oracle需要间隔15分钟才会做一次

需要注意的是oracle只会捕获那些位于目标sql语句的where条件中的绑定变量的具体输入值,而对于那些使用了绑定变量的insert语句,不管insert语句是否以硬解析方式执行,oracle始终不会捕获其value子句中对应的绑定变量的具体输入值。

SQL> variable vnum number;
SQL> exec :vnum:=100;

PL/SQL procedure successfully completed.

SQL> insert into t100 values(:vnum,'acb');

1 row created.

SQL> select sql_id,sql_text from v$sql where sql_text like 'insert into t100%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
5cyjjwy6y74t3
insert into t100 values(:vnum,'acb')

SQL> select sql_id,name,position,datatype_string,last_captured,value_string from v$sql_bind_capture where sql_id='5cyjjwy6y74t3';

SQL_ID        NAME                   POSITION DATATYPE_STRING                                              LAST_CAPTURED       VALUE_STRING
------------- -------------------- ---------- ------------------------------------------------------------ ------------------- ------------------------------
5cyjjwy6y74t3 :VNUM                         1 NUMBER

这里看出来即使该sql语句是硬解析,由于绑定变量不在sql语句的where条件中,并且是insert into values的语句,oracle并不会捕获values后面绑定变量的值。

而如果我们换成下面这种形式,不以insert into values的形式,则oracle是可以抓取到where后面的绑定变量的具体值的:
SQL> insert into t100 select id,name from t100 where id=:vnum;

2 rows created.

SQL> select sql_id,sql_text from v$sql where sql_text like 'insert into t100 select id,name from t100 where id=:vnum';

SQL_ID
-------------
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
a9v1314t50dwg
insert into t100 select id,name from t100 where id=:vnum


SQL> select sql_id,name,policesition,datatype_string,last_captured,value_string from v$sql_bind_capture where sql_id='a9v1314t50dwg';

SQL_ID        NAME                   POSITION DATATYPE_STRING                                              LAST_CAPTURED       VALUE_STRING
------------- -------------------- ---------- ------------------------------------------------------------ ------------------- ------------------------------
a9v1314t50dwg :VNUM                         1 NUMBER                                                       2015-07-01 01:37:50 100

我们重新设置下绑定变量的具体值,再次执行上述sql,这里oracle并没有马上捕获sql语句中新的绑定变量的具体值,这个是因为软解析和如软软解析时,oracle为了考虑性能等问题需要间隔15分钟后才能再次捕获绑定变量的具体值:

SQL> exec :vnum:=1;

PL/SQL procedure successfully completed.

SQL> insert into t100 select id,name from t100 where id=:vnum;

1 row created.

SQL> select sql_id,name,position,datatype_string,last_captured,value_string from v$sql_bind_capture where sql_id='a9v1314t50dwg';

SQL_ID        NAME                   POSITION DATATYPE_STRING                                              LAST_CAPTURED       VALUE_STRING
------------- -------------------- ---------- ------------------------------------------------------------ ------------------- ------------------------------
a9v1314t50dwg :VNUM                         1 NUMBER                                                       2015-07-01 01:37:50 100

过了15分钟后再次执行sql,此时这个sql虽然是软解析,oracle还是会再次捕获这个绑定变量的传入值:
SQL> exec :vnum:=1;

PL/SQL procedure successfully completed.

SQL> insert into t100 select id,name from t100 where id=:vnum;

2 rows created.

SQL> select sql_id,name,position,datatype_string,last_captured,value_string from v$sql_bind_capture where sql_id='a9v1314t50dwg';

SQL_ID        NAME                   POSITION DATATYPE_STRING                                              LAST_CAPTURED       VALUE_STRING
------------- -------------------- ---------- ------------------------------------------------------------ ------------------- ------------------------------
a9v1314t50dwg :VNUM                         1 NUMBER                                                       2015-07-01 01:53:05 1

这里提到关于绑定变量的问题,这里再提一下何种谓词适合用绑定变量:一般而言对于主键、id、流水号等基本唯一的值,并且对应的SQL执行频率特别高,oracle建议使用绑定变量,而对于那些状态值(例如能够枚举的,不同值只有几个或者数十个)不建议使用绑定变量,因为这类SQL即使不使用绑定变量,oracle的解析消耗也是微乎其微,而如果使用绑定变量,会导致在某些特定场景下SQL执行计划并不是最合理的,甚至是非常糟糕的,所以也不要将系统中所有的SQL都使用绑定变量,这里简单做个测试以供大家参考:

SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SQL> create table t01 as select object_id,object_name,object_type from dba_objects;

Table created.
SQL> update t01 set object_type='INDEX' where object_id<90000;

86872 rows updated.

SQL> commit;

Commit complete.

这里模拟出object_type列数据分布存在倾斜性

SQL> select object_type,count(*) from t01 group by object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
INDEX PARTITION             21
TABLE PARTITION             21
DATABASE LINK                2
LOB                          1
TRIGGER                      1
INDEX                    86894
TABLE                       34
VIEW                         2

8 rows selected.
SQL> create index ind_t01_objtype on t01(object_type);

Index created.
SQL> execute dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T01',method_opt=>'FOR ALL COLUMNS SIZE 254');

PL/SQL procedure successfully completed.
SQL> alter session set "_optim_peek_user_binds"=false;

Session altered.

这里关闭掉绑定变量窥视,其实xiaoyu个人觉得在oracle 11g之前都应该关闭掉绑定变量窥视,而在oracle 11g之后推出了自适应游标,部分系统可以考虑开启窥视和自适应游标,但是xiaoyu维护的很多大型在线系统对于这两个特性都是选择禁掉,自适应游标有较多的bug,具体可以参考mos上的部分文章。
SQL> variable type varchar2(128);
SQL> exec :type:='INDEX';

PL/SQL procedure successfully completed.
SQL> select * from t01 where object_type=:type;

86894 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2537206454

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 | 10872 |   382K|    93   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T01             | 10872 |   382K|    93   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | IND_T01_OBJTYPE | 10872 |       |    26   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_TYPE"=:TYPE)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      12259  consistent gets
          0  physical reads
          0  redo size
    4607541  bytes sent via SQL*Net to client
      64235  bytes received via SQL*Net from client
       5794  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      86894  rows processed

SQL> select * from t01 where object_type='INDEX';

86894 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3295674804

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 86928 |  3056K|   144   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T01  | 86928 |  3056K|   144   (1)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_TYPE"='INDEX')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       6277  consistent gets
          0  physical reads
          0  redo size
    3342833  bytes sent via SQL*Net to client
      64235  bytes received via SQL*Net from client
       5794  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      86894  rows processed

对于object_type=’INDEX’优化器更应该选择全表扫描,而不应该选择索引范围扫描,但是因为使用的绑定变量,然后又关闭了绑定变量窥视,则优化器计算选择率时只能是1/num_distinct,进而导致rows评估也存在较大的误差,从而选择了资源消耗更多的索引范围扫描;如果开启了窥视,又没有自适应游标等特性支撑,会导致后续的SQL都使用之前的执行计划,又是一个性能隐患。

使用绑定变量字段类型:
xiaoyu个人是不太建议状态字段、时间范围字段等使用绑定变量的,这类SQL如果执行频率又不高,不存在较大的并发,即使不写成绑定变量,也只有几十个sql_id在shared pool中,这点解析消耗微乎其微,而如果走错一个不合理的执行计划,则消耗的资源将远远超过这点解析消耗。

可以参考之前写的关于sql代码中哪些列适合使用绑定变量 http://www.dbaxiaoyu.com/archives/2534

Posted in oracle | 绑定变量知识—绑定变量的分级和捕获已关闭评论

merge语法简介和等价改写优化案例分析(一)

oracle merge详细介绍
之前就看过sql中merge的使用,merge是oracle9I新增的语法,主要是用于简化更新的执行效率,后面有具体改写的案例分析。

具体语法如下:
merge into tab01 a
using tab02 b
on (a.id=b.id)
when matched then
update set a.name=b.name
when not matched then
insert values(b.id,d,name)

用tab02表来对tab01更新,tab01的所有记录行中如果有符合的数据行就执行update操作a.id=b.id的就对tab01的name更新为tab02的name,如果没有满足a.id=b.id的数据行就对tab01表执行insert操作,注意此时matched只能update,not matched也只能insert或者不操作。

需要注意的是update是不能对关联条件update的:

merge into tab01 a
using tab02 b
on (a.id=b.id)
when matched then
update set a.id=b.id,a.name=b.name
when not matched then
insert values(b.id,d,name)

此时oracle无法对连接条件的column进行update set

上面提到简化更新的执行效率,到底是如何简化的,何种情况下可以改写为merge来简化,下面xiaoyu参考oracle的改写调优案例分享中的例子来做简单的分享:

SQL>drop table t_objects;
SQL>drop table t_tables;
SQL>create table t_objects as select * from dba_objects;
SQL>create table t_tables as select * from dba_tables;
SQL>alter table t_objects add tablespace_name varchar2(32);


现在需要把t_tables的tablespace_name同步到t_objects的tablespace_name中
update t_objects a
set a.tablespace_name=(select b.tablespace_name from t_tables b where
b.table_name=a.object_name and b.owner=a.owner)
where exists(select 1 from  t_tables b where
b.table_name=a.object_name and b.owner=a.owner)

update t_objects a
set a.tablespace_name=(select b.tablespace_name from t_tables b where
b.table_name=a.object_name and b.owner=a.owner)

注意很多开发人员喜欢用第二种SQL去更新,这样oracle会把t_objects表中的所有tablespace_name都更新掉
这个一定要特别注意,如下例子:
SQL> select * from t01;

        ID NAME
---------- --------------------------------
         1 awk
         2 awk
         3 afg

SQL> select * from t02;

        ID EADDR
---------- ----------
         1 beijing
         2 shanghai

SQL> update t01 set name=(select eaddr from t02 where t01.id=t02.id);

3 rows updated.

SQL> select * from t01;

        ID NAME
---------- --------------------------------
         1 beijing
         2 shanghai
         3

SQL> rollback;

rollback complete.

SQL> update t01 set name=(select eaddr from t02 where t01.id=t02.id)
     where exists(select 1 from t02 where t01.id=t02.id);

2 rows updated.

SQL> select * from t01;

        ID NAME
---------- --------------------------------
         1 beijing
         2 shanghai
         3 afg

回到原SQL案例中认真分析下,原SQL执行计划如下:

SQL> UPDATE t_objects a
SET a.tablespace_name=
  (SELECT b.tablespace_name
  FROM t_tables b
  WHERE b.table_name=a.object_name
  AND b.owner       =a.owner
  )
WHERE EXISTS
  (SELECT 1 FROM t_tables b WHERE b.table_name=a.object_name AND b.owner=a.owner
  );

3174 rows updated.

Plan hash value: 2130220419

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |           |      1 |        |      0 |00:00:03.64 |     327K|       |       |          |
|   1 |  UPDATE               | T_OBJECTS |      1 |        |      0 |00:00:03.64 |     327K|       |       |          |
|*  2 |   HASH JOIN RIGHT SEMI|           |      1 |  76604 |   3174 |00:00:00.22 |    1352 |  1229K|  1229K| 1335K (0)|
|   3 |    VIEW               | VW_SQ_1   |      1 |   2989 |   2959 |00:00:00.01 |     107 |       |       |          |
|   4 |     TABLE ACCESS FULL | T_TABLES  |      1 |   2989 |   2959 |00:00:00.01 |     107 |       |       |          |
|   5 |    TABLE ACCESS FULL  | T_OBJECTS |      1 |  76604 |  86958 |00:00:00.01 |    1245 |       |       |          |
|*  6 |   TABLE ACCESS FULL   | T_TABLES  |   3015 |      1 |   3015 |00:00:03.02 |     322K|       |       |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ITEM_1"="A"."OBJECT_NAME" AND "ITEM_2"="A"."OWNER")
   6 - filter(("B"."TABLE_NAME"=:B1 AND "B"."OWNER"=:B2)

Statistics
----------------------------------------------------------
          0  recursive calls
       3238  db block gets
     323957  consistent gets
          0  physical reads
     754468  redo size
        841  bytes sent via SQL*Net to client
       1014  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       3174  rows processed

这个SQL的执行计划在于需要对表T_OBJECTS扫描两次,并且还有标量子查询部分(可以参考上面的T_TABLES表的循环查询了3015次)

而对于这类SQL最好改写为merge来优化:

merge into t_objects a
using t_tables b on(a.owner=b.owner and a.object_name=b.table_name)
when matched then update
set a.tablespace_name=b.tablespace_name;

Plan hash value: 1970127410

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |           |      1 |        |      0 |00:00:00.51 |    4590 |       |       |          |
|   1 |  MERGE               | T_OBJECTS |      1 |        |      0 |00:00:00.51 |    4590 |       |       |          |
|   2 |   VIEW               |           |      1 |        |   3174 |00:00:00.03 |    1352 |       |       |          |
|*  3 |    HASH JOIN         |           |      1 |   3915 |   3174 |00:00:00.03 |    1352 |  1598K|   984K| 2440K (0)|
|   4 |     TABLE ACCESS FULL| T_TABLES  |      1 |   2989 |   2959 |00:00:00.01 |     107 |       |       |          |
|   5 |     TABLE ACCESS FULL| T_OBJECTS |      1 |  76604 |  86958 |00:00:00.02 |    1245 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"."OWNER"="B"."OWNER" AND "A"."OBJECT_NAME"="B"."TABLE_NAME")

Statistics
----------------------------------------------------------
         66  recursive calls
       3238  db block gets
       1714  consistent gets
          0  physical reads
     754388  redo size
        844  bytes sent via SQL*Net to client
        947  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
       3174  rows processed

从执行计划来看merge改写的SQL已经没有标量子查询部分,逻辑读降低到了1714多
Posted in oracle, sql tuning and troubleshooting | merge语法简介和等价改写优化案例分析(一)已关闭评论

AWR基线和AWR Compare Period Report功能介绍

性能优化时大体有如下两种评估方式:如果希望知道性能度量值指示服务器接近容量限制,则应设置绝对值;但是如果希望知道今天的性能与上周(或者上个月)同一时间的性能之间的差异,则当前性能必须与基线进行比较,基线是某个时段内生成的一组快照,按照统计学对这些快照进行了分组,以便获得一组随时间变化的基线值,可以以任何时段快照采样来做基线,只不过一般情况下我们大多会选择系统正常时段的快照来做基线。

基线在oracle 10g中就出现了,而在oracle database 11g进一步增强了自动工作量资料档案库基线:
1 即用型移动窗口基线,可通过该基线指定自适应阀值
2 使用基线模板安排基线的创建操作
3 重命名基线
4 设置基线的到期日期

AWR baseline是指一个特定时间段内的性能数据,保留这些数据是为了在性能问题产生时与其他类似的工作负载时间段进行比较:

fixed baseline:fixed baseline表示的是您制定的一个固定的、连续的时间段
moving window baseline:表示的是war保留期内存在的所有war数据
Baseline template:您可以使用baseline template创建将来某个连续时间段的baseline,oracle中有两种baseline模板分别是single和repeating

创建fixed baseline:

SQL> begin
  2  dbms_workload_repository.create_baseline(
  3  start_snap_id=>1510,
  4  end_snap_id=>1511,
  5  baseline_name=>'test_baseline');
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select dbid,baseline_name,start_snap_id,end_snap_id,MOVING_WINDOW_SIZE,EXPIRATION from dba_hist_baseline;

      DBID BASELINE_NAME                                                    START_SNAP_ID END_SNAP_ID MOVING_WINDOW_SIZE EXPIRATION
---------- ---------------------------------------------------------------- ------------- ----------- ------------------ ----------
4257181262 test_baseline                                                             1510        1511
4257181262 SYSTEM_MOVING_WINDOW                                                      1515        1591                  8

创建baseline的procedure和function

根据snap_id创建:
PROCEDURE CREATE_BASELINE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 START_SNAP_ID                  NUMBER                  IN
 END_SNAP_ID                    NUMBER                  IN
 BASELINE_NAME                  VARCHAR2                IN
 DBID                           NUMBER                  IN     DEFAULT
 EXPIRATION                     NUMBER                  IN     DEFAULT
FUNCTION CREATE_BASELINE RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 START_SNAP_ID                  NUMBER                  IN
 END_SNAP_ID                    NUMBER                  IN
 BASELINE_NAME                  VARCHAR2                IN
 DBID                           NUMBER                  IN     DEFAULT
 EXPIRATION                     NUMBER                  IN     DEFAULT

根据时间创建:
PROCEDURE CREATE_BASELINE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 START_TIME                     DATE                    IN
 END_TIME                       DATE                    IN
 BASELINE_NAME                  VARCHAR2                IN
 DBID                           NUMBER                  IN     DEFAULT
 EXPIRATION                     NUMBER                  IN     DEFAULT
FUNCTION CREATE_BASELINE RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 START_TIME                     DATE                    IN
 END_TIME                       DATE                    IN
 BASELINE_NAME                  VARCHAR2                IN
 DBID                           NUMBER                  IN     DEFAULT
 EXPIRATION                     NUMBER                  IN     DEFAULT

还可以删除和重命名baseline:

SQL> begin
  2  dbms_workload_repository.drop_baseline(
  3  baseline_name=>'test_baseline',
  4  cascade=>true);
  5  end;
  6  /

PL/SQL procedure successfully completed.

在oracle 11g中系统已经默认创建moving window baseline:

SQL> select dbid,baseline_name,start_snap_id,end_snap_id,MOVING_WINDOW_SIZE,EXPIRATION from dba_hist_baseline;

      DBID BASELINE_NAME                                                    START_SNAP_ID END_SNAP_ID MOVING_WINDOW_SIZE EXPIRATION
---------- ---------------------------------------------------------------- ------------- ----------- ------------------ ----------
4257181262 SYSTEM_MOVING_WINDOW                                                      1515        1591                  8

drop baseline时如果指定cascade=>true,将把baseline对应的snap也级联删除了

oracle database会自动维护系统定义的moving window baseline,系统定义的moving window baseline的默认窗口大小就是当前的AWR保留期,如果打算使用自适应阀值,请考虑使用更长的移动窗口,比如30天,以便精确的计算阀值。移动窗口的大小调整为小于或等于AWR的保留天数,因为要增加移动窗口的大小,必须要先增加相应的AWR保留期限。

调整awr的保留周期:
SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>43200);

PL/SQL procedure successfully completed.

SQL> select retention from dba_hist_wr_control;

RETENTION
---------------------------------------------------------------------------
+00030 00:00:00.0

SQL> select baseline_id,baseline_name,moving_window_size from dba_hist_baseline;

BASELINE_ID BASELINE_NAME                                                    MOVING_WINDOW_SIZE
----------- ---------------------------------------------------------------- ------------------
          0 SYSTEM_MOVING_WINDOW                                                              8

SQL> exec dbms_workload_repository.modify_baseline_window_size(window_size=>30);

PL/SQL procedure successfully completed.

SQL> select baseline_id,baseline_name,moving_window_size from dba_hist_baseline;

BASELINE_ID BASELINE_NAME                                                    MOVING_WINDOW_SIZE
----------- ---------------------------------------------------------------- ------------------
          0 SYSTEM_MOVING_WINDOW                                                             30

基线模板:
基线模板允许定义可能在将来要捕捉的基线,create_baseline_template存储过程定义单一基线或重复基线的捕捉,创建单一基线模板与创建基于时间的基线类似,除了将来的时间外。

single基线模板:
PROCEDURE CREATE_BASELINE_TEMPLATE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 START_TIME                     DATE                    IN
 END_TIME                       DATE                    IN
 BASELINE_NAME                  VARCHAR2                IN
 TEMPLATE_NAME                  VARCHAR2                IN
 EXPIRATION                     NUMBER                  IN     DEFAULT
 DBID                           NUMBER                  IN     DEFAULT

SQL> exec DBMS_WORKLOAD_REPOSITORY.create_baseline_template(start_time=>TO_DATE('09-MAR-2016 00:00', 'DD-MON-YYYY HH24:MI'),end_time=>TO_DATE('16-MAR-2016 05:00', 'DD-MON-YYYY HH24:MI'),baseline_name=>'09_11_14_BS1',template_name =>'09_11_14_TP1',expiration=> 10);

PL/SQL procedure successfully completed.

SQL> select dbid,template_name,template_type,baseline_name_prefix,start_time,end_time,duration,expiration from dba_hist_baseline_template;

      DBID TEMPLATE_NAME                  TEMPLATE_ BASELINE_NAME_PREFIX           START_TIME          END_TIME              DURATION EXPIRATION
---------- ------------------------------ --------- ------------------------------ ------------------- ------------------- ---------- ----------
4257181262 09_11_14_TP1                   SINGLE    09_11_14_BS1                   2016-03-09 00:00:00 2016-03-16 05:00:00                    10

重复基线模板稍微有点不同,因为它需要调度信息,start_time和end_time参数分别在模板激活和释放时定义,day_of_week、hour_in_day和duration定义产生基线的日期、时间和持续时间,因为模板会产生多个基线,基线名是以baseline_name_prefix开始的。

repeat基线模板:

PROCEDURE CREATE_BASELINE_TEMPLATE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DAY_OF_WEEK                    VARCHAR2                IN
 HOUR_IN_DAY                    NUMBER                  IN
 DURATION                       NUMBER                  IN
 START_TIME                     DATE                    IN
 END_TIME                       DATE                    IN
 BASELINE_NAME_PREFIX           VARCHAR2                IN
 TEMPLATE_NAME                  VARCHAR2                IN
 EXPIRATION                     NUMBER                  IN     DEFAULT
 DBID                           NUMBER                  IN     DEFAULT

exec DBMS_WORKLOAD_REPOSITORY.create_baseline_template(day_of_week=>'MONDAY',hour_in_day=>0,duration=>5,start_time=>SYSDATE,end_time=>ADD_MONTHS(SYSDATE, 6),baseline_name_prefix =>'monday_morning_bl',template_name=>'monday_morning_tp',expiration=> NULL);

SQL> select dbid,TEMPLATE_NAME,TEMPLATE_TYPE,BASELINE_NAME_PREFIX,START_TIME,END_TIME,DAY_OF_WEEK,HOUR_IN_DAY,DURATION,EXPIRATION,REPEAT_INTERVAL from dba_hist_baseline_template;

      DBID TEMPLATE_NAME                  TEMPLATE_ BASELINE_NAME_PREFIX           START_TIME          END_TIME            DAY_OF_WE HOUR_IN_DAY   DURATION EXPIRATION
---------- ------------------------------ --------- ------------------------------ ------------------- ------------------- --------- ----------- ---------- ----------
REPEAT_INTERVAL
--------------------------------------------------------------------------------
4257181262 09_11_14_TP1                   SINGLE    09_11_14_BS1                   2016-03-09 00:00:00 2016-03-16 05:00:00                                          10

4257181262 monday_morning_tp              REPEATING monday_morning_bl              2016-03-15 02:54:55 2016-09-15 02:54:55 MONDAY              0          5
FREQ=WEEKLY;INTERVAL=1;BYDAY=MON;BYHOUR=0;BYMINUTE=0;BYSECOND=0

上面介绍了基线,那么如何来使用基线和指定时段的快照做awr数据对比了,其实Oracle为我们提供出AWR Compare Period Report来方便实现指定时段的awr数据对比。

由于基线又由于可以一直保存在awr快照中,DBA做性能分析时则可以利用基线和故障时段的awr做对比,更进一步的分析数据库的性能趋势变化,下面简单的来做一个AWR数据时段对比:

SQL> select dbid,min(snap_id),max(snap_id) from dba_hist_snapshot group by dbid;

      DBID MIN(SNAP_ID) MAX(SNAP_ID)
---------- ------------ ------------
4257181262         1508         1669

这里创建snap_id 1656到1657的基线
SQL> begin
  2      dbms_workload_repository.create_baseline(
  3      start_snap_id=>1656,
  4      end_snap_id=>1657,
  5      baseline_name=>'test_baseline');
  6      end;
  7      /

PL/SQL procedure successfully completed.

删除大部分snapshot
SQL> exec dbms_workload_repository.drop_snapshot_range(1508, 1667, 4257181262);

PL/SQL procedure successfully completed.

SQL> select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot where dbid=4257181262;

   SNAP_ID BEGIN_INTERVAL_TIME                                                         END_INTERVAL_TIME
---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
      1669 15-MAR-16 10.23.42.387 PM                                                   15-MAR-16 11.10.41.380 PM
      1656 11-MAR-16 10.00.33.158 PM                                                   14-MAR-16 08.40.27.499 PM
      1657 14-MAR-16 08.40.27.499 PM                                                   14-MAR-16 10.00.35.439 PM
      1668 15-MAR-16 08.41.44.009 PM                                                   15-MAR-16 10.23.42.387 PM

这里也验证了之前awr baseline的部分对应的snapshot并不会被awr保留策略或者手动删除awr而删除掉。

接下来使用awrddrpt.sql脚本来生成两个awr的对比数据:

SQL> @awrddrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id       DB Id    DB Name      Inst Num Inst Num Instance
----------- ----------- ------------ -------- -------- ------------
 4257181262  4257181262 ORA11G              1        1 ora11g

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html

Type Specified:  html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
  2350429211        1 TLINK        tlink        ylqz_s
* 4257181262        1 ORA11G       ora11g       redhat-ora

Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 4257181262 for Database Id for the first pair of snapshots
Using          1 for Instance Number for the first pair of snapshots

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.

Enter value for num_days: 3

Listing the last 3 days of Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
ora11g       ORA11G            1656 14 Mar 2016 20:40      1
                               1657 14 Mar 2016 22:00      1
                               1668 15 Mar 2016 22:23      1
                               1669 15 Mar 2016 23:10      1

Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1656
First Begin Snapshot Id specified: 1656

Enter value for end_snap: 1657
First End   Snapshot Id specified: 1657

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
  2350429211        1 TLINK        tlink        ylqz_s
* 4257181262        1 ORA11G       ora11g       redhat-ora

Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Using 4257181262 for Database Id for the second pair of snapshots
Using          1 for Instance Number for the second pair of snapshots

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.

Enter value for num_days2: 3

Listing the last 3 days of Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
ora11g       ORA11G            1656 14 Mar 2016 20:40      1
                               1657 14 Mar 2016 22:00      1
                               1668 15 Mar 2016 22:23      1
                               1669 15 Mar 2016 23:10      1

Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2: 1668
Second Begin Snapshot Id specified: 1668

Enter value for end_snap2: 1669
Second End   Snapshot Id specified: 1669

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_1656_1_1668.html  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: /home/oracle/awr0316.hmtl

生成的AWR部分信息:
1

2

Posted in oracle | AWR基线和AWR Compare Period Report功能介绍已关闭评论

写给自己的2015

2015年已经过去,现在的自己好像更懒了,可能也是走得更慢了,往年总会迫不及待的在近一年将去年的生活感悟写下,而2015年的托了好久,一直到这个周末才想着为自己的去年生活写下点什么。

2015年6月大学毕业五年,回想这五年来,百感交集,为什么更多说到的五年则是因为在很多的鸡汤书籍中大多以五年这个分水岭来衡量变化。变化这个词其实是相对的,而自己觉得找到自己的生活方式,享受当前的生活方式就是一种快乐,这种快乐往往比成功学中的变化更加重要,毕竟人生冲冲几十年,懂得享受生活可能更加重要。

去年经历了疯狂的A股,盈亏无所谓,倒是练就了好的心态,风险与机遇共存,这句话只有自己经历过才知道,好在并不影响自己的生活,过去了就过去了,一段经历也是一笔财富。

很遗憾去年在感情中依然没有什么值得惊喜的事情发生,平平淡淡又过完了一年,好在这一年父母、家人和朋友身体健康,而只有倒霉的自己又涨了几斤彪,请原谅从小家里饭菜好吃造就了一个吃货的自己。当初在自己只有110时代,觉得自己可以一辈子这么苗条下去,现在看看还是当时太年轻了;而自己又是一个臭美的人,特喜欢那些很显形体的衬衫啥子的,再想想还没有拍婚纱照,没有理由让自己胖下去。大肚腩,今年我想要跟你说byebye!

新的一年,自己并无特别的要求,减肥依然需要进行并且坚持,而感情希望今年也有收获,工作希望能够顺顺利利。当然也希望家人、朋友和一些善良的人们快乐常在,顺顺利利的度过这一年!

Posted in 成长脚步 | 写给自己的2015已关闭评论

oracle SPA实施简介

oracle 11g推出了新特性SPA(SQL performance Analyze)现在已经被广泛应用到升级和迁移的场景中,当然比如一些其他的场景也可以考虑使用,比如(优化器参数修改、IO子系统变更等),这种功能可向DBA 提供有关SQL语句性能的详细信息,例如,执行前后的统计信息,提高或降低性能的语句,主要用于衡量环境变化后SQL执行性能是否出现衰变等。

SPA的主要实施步骤如下:
1 在源环境捕捉SQL负载,生成SQLSET

exec dbms_sqltune.create_sqlset(‘SPA_SQLSET’);

从cursor cache收集SQLSET:

cat  sts_add.sh
date
sqlplus -s spa/spa_jzdb3 <<EOF1
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT value(P)
    FROM TABLE(dbms_sqltune.select_cursor_cache('parsing_schema_name not in (''SYS'') and  module not in (''PL/SQL Developer'') and force_matching_signature not in (select force_matching_signature from DBA_SQLSET_STATEMENTS ) ',NULL,NULL,NULL,NULL,1,NULL,'ALL')) p;
    dbms_sqltune.load_sqlset('SPA_SQLSET', cur, load_option=>'MERGE');
  CLOSE cur;
END;
/

从awr snapshot收集SQLSET:

declare
    cur sys_refcursor;
    begin
    open cur for select value(P) from table(dbms_sqltune.select_workload_repository(77589,78343)) p;
    dbms_sqltune.load_sqlset(sqlset_name=>'SPA_SQLSET',populate_cursor=>cur,load_option=>'MERGE',update_option=>'ACCUMULATE');
    close cur;
    end;
    /

从awr baseline收集SQLSET
从another sql set收集SQLSET
从10046 trace file

2 将SQLSET导入到中转表

源端创建stage table
exec DBMS_SQLTUNE.create_stgtab_sqlset(table_name => 'SQLSET11_TAB', schema_name => 'SPA', tablespace_name => 'USERS');

将sqlset打包到stage table:
exec DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'SPA_SQLSET', sqlset_owner => 'SPA', staging_table_name => 'SQLSET11_TAB', staging_schema_owner => 'SPA');

3 将中专表导入到新库环境中,解压舞台表数据到SQLSET中

impdp spa/spa_jzdb3 directory=back dumpfile=sqlset11_tab.dmp logfile=sqlset11_tab.log table_exists_action=replace

EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (SQLSET_NAME=>'SPA_SQLSET', SQLSET_OWNER=> 'SPA', REPLACE=>TRUE, STAGING_TABLE_NAME=>'SQLSET11_TAB', STAGING_SCHEMA_OWNER=>'SPA'); 解压舞台表sqlset11_tab到sqlset中

4 创建SPA任务,先生成10g的trail,然后在11g中在生成11g的trail

新建SPA任务:
var tname varchar2(30);
var sname varchar2(30);
exec :sname := 'SPA_SQLSET';
exec :tname := 'SPA_TASK';
exec :tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => :sname, task_name => :tname);

生成oracle 10g的trail
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TASK',
execution_type => 'CONVERT SQLSET',
execution_name => 'CONVERT_10G');
end;
/

生成在目标库的trail,由于需要在目标库执行SQL执行时间可能比较长
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TASK',
execution_type => 'TEST EXECUTE',
execution_name => 'EXEC_11G');
end;
/

5 执行比较任务,生成SPA报告

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TASK',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_elapsed_time',
execution_params => dbms_advisor.arglist('execution_name1', 'CONVERT_10G', 'execution_name2', 'EXEC_11G', 'comparison_metric', 'elapsed_time') );
end;
/

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TASK',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_CPU_time',
execution_params => dbms_advisor.arglist('execution_name1', 'CONVERT_10G', 'execution_name2', 'EXEC_11G', 'comparison_metric', 'CPU_TIME') );
end;
/

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TASK',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_BUFFER_GETS_time',
execution_params => dbms_advisor.arglist('execution_name1', 'CONVERT_10G', 'execution_name2', 'EXEC_11G', 'comparison_metric', 'BUFFER_GETS') );
end;
/

生成SPA报告:

set trimspool on
set trim on
set pages 0
set long 999999999
set linesize 1000
ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED

spool spa_report_elapsed_time.html
SELECT dbms_sqlpa.report_analysis_task('SPA_TASK', 'HTML', 'ALL','ALL', top_sql=>300,execution_name=>'Compare_elapsed_time') FROM dual;
spool off;

spool spa_report_CPU_time.html
SELECT dbms_sqlpa.report_analysis_task('SPA_TASK', 'HTML', 'ALL','ALL', top_sql=>300,execution_name=>'Compare_CPU_time') FROM dual;
spool off;

spool spa_report_buffer_time.html
SELECT dbms_sqlpa.report_analysis_task('SPA_TASK','HTML','ALL','ALL',top_sql=>300,execution_name=>'Compare_BUFFER_GETS_time') FROM dual;
spool off;

spool changed_plans.html
SELECT dbms_sqlpa.report_analysis_task('SPA_TASK','HTML','CHANGED_PLANS','ALL',top_sql=>300) FROM dual;
spool off;

spool spa_report_errors.html
SELECT dbms_sqlpa.report_analysis_task('SPA_TASK', 'HTML', 'errors','summary') FROM dual;
spool off;

spool spa_report_unsupport.html
SELECT dbms_sqlpa.report_analysis_task('SPA_TASK', 'HTML', 'unsupported','all') FROM dual;
spool off;
/

6 分析性能退化的SQL语句

常用的SPA Script:

-- 检查运行SPA的进程的运行状态
SELECT SID, TASK_ID, SOFAR, TOTALWORK, START_TIME, START_TIME + (SYSDATE - START_TIME)/SOFAR * TOTALWORK EST_END_TIME
  FROM V$ADVISOR_PROGRESS
 WHERE SOFAR <> TOTALWORK
   AND SOFAR <> 0
 ORDER BY 3;

如果在执行过程中cancel掉,再次对sqlset操作会爆出如下错误:
SQL> EXEC DBMS_SQLTUNE.DELETE_SQLSET(sqlset_name   => 'SPA_SQLSET', basic_filter  => 'executions<3');
BEGIN DBMS_SQLTUNE.DELETE_SQLSET(sqlset_name   => 'SPA_SQLSET', basic_filter  => 'executions<1'); END;

*
ERROR at line 1:
ORA-13757: "SQL Tuning Set" "SPA_SQLSET" owned by user "SPA" is active.
ORA-06512: at "SYS.DBMS_SQLTUNE", line 5712
ORA-06512: at line 1

找到task然后删除掉
SQL> SELECT TASK_ID, OWNER, TASK_NAME FROM DBA_ADVISOR_TASKS WHERE TASK_NAME LIKE 'SPA%' ORDER BY 1;

   TASK_ID OWNER                                              TASK_NAME
---------- -------------------------------------------------- --------------------------------------------------
      1235 SPA                                                SPA_TASK

SQL> EXEC DBMS_SQLPA.DROP_ANALYSIS_TASK('SPA_TASK');

PL/SQL procedure successfully completed.

删除sqlset
EXEC DBMS_SQLTUNE.DROP_SQLSET('SPA_SQLSET', 'SPA');

如果SQL信息太多,为了便于我们尽快的分析,我们需要抓取最需要分析的SQL,比如执行次数、该SQL的执行用户、执行module,绑定变量做筛选:

删除未使用绑定变量的sql
CREATE INDEX IDX_SQLSET11_TAB_F_S ON SQLSET11_TAB(FORCE_MATCHING_SIGNATURE, SQL_ID) PARALLEL 8;
BEGIN
FOR X IN(SELECT FORCE_MATCHING_SIGNATURE, MIN(SQL_ID) SQL_ID FROM SQLSET11_TAB
          GROUP BY FORCE_MATCHING_SIGNATURE
          HAVING COUNT(*) > 1)
LOOP
  DELETE FROM SQLSET11_TAB WHERE FORCE_MATCHING_SIGNATURE = X.FORCE_MATCHING_SIGNATURE AND SQL_ID <> X.SQL_ID;
  COMMIT;
END LOOP;
END;
/

删除执行次数小于10次的SQL
exec dbms_sqltune.delete_sqlset(sqlset_name=>'SPA_SQLSET',basic_filter=>'executions<10',sqlset_owner=>'SPA');

删除非指定用户的SQL
delete from spa.sqlset11_tab where PARSING_SCHEMA_NAME not in ('ACCOUNTING','SPS');

删除指定module比如PL/SQL Developer的SQL
delete from spa.sqlset11_tab where MODULE='PL/SQL Developer';
Posted in install upgrade migrate, oracle | oracle SPA实施简介已关闭评论

降低SQLSET相关表WRI$_SQLSET_PLAN_LINES高水位线,释放SYSAUX表空间

客户提出SYSAUX空间太大,已经占据了20多G的空间,登陆系统发觉SYSAUX表空间中占据TOP SEGMENT的主要就是WRI$_SQLSET_PLAN_LINES表

SQL> select *
  2    from (select bytes / 1024 / 1024 / 1024, segment_name, owner, segment_type
  3            from dba_segments
  4           where tablespace_name = 'SYSAUX'
  5           order by bytes desc)
  6   where rownum < 20
  7  ;

BYTES/1024/1024/1024 SEGMENT_NAME                                                                     OWNER                          SEGMENT_TYPE
-------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------
        3.9814453125 WRI$_SQLSET_PLAN_LINES                                                           SYS                            TABLE
        2.8681640625 WRH$_SQLTEXT                                                                     SYS                            TABLE
        2.3955078125 SYS_LOB0000009295C00038$$                                                        SYS                            LOBSEGMENT
        1.0615234375 WRH$_SYSMETRIC_HISTORY                                                           SYS                            TABLE
        0.9951171875 WRH$_SYSMETRIC_HISTORY_INDEX                                                     SYS                            INDEX
        0.8427734375 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST                                                   SYS                            INDEX
        0.7587890625 SYS_LOB0000009002C00038$$                                                        SYS                            LOBSEGMENT
         0.751953125 WRH$_ACTIVE_SESSION_HISTORY                                                      SYS                            TABLE PARTITION
         0.673828125 WRH$_SQL_PLAN                                                                    SYS                            TABLE
         0.646484375 WRH$_ACTIVE_SESSION_HISTORY                                                      SYS                            TABLE PARTITION
         0.599609375 WRI$_OPTSTAT_HISTGRM_HISTORY                                                     SYS                            TABLE
         0.599609375 WRH$_ACTIVE_SESSION_HISTORY                                                      SYS                            TABLE PARTITION
        0.5849609375 WRH$_ACTIVE_SESSION_HISTORY                                                      SYS                            TABLE PARTITION
          0.55078125 WRH$_ACTIVE_SESSION_HISTORY                                                      SYS                            TABLE PARTITION
        0.5244140625 WRH$_ACTIVE_SESSION_HISTORY                                                      SYS                            TABLE PARTITION
        0.4052734375 I_WRI$_OPTSTAT_H_ST                                                              SYS                            INDEX
         0.380859375 WRH$_SYSMETRIC_SUMMARY                                                           SYS                            TABLE
        0.3115234375 WRI$_SQLSET_PLAN_LINES_PK                                                        SYS                            INDEX
        0.2900390625 WRI$_SQLSET_PLANS                                                                SYS                            TABLE

19 rows selected

对于WRI$_SQLSET_PLAN_LINES表查看其中数据只有0条,但是segment分配已经达到了4G多,这个优先考虑的就是在系统闲暇时间进行move或者shrink space操作:

SQL> alter table WRI$_SQLSET_PLAN_LINES shrink space;
alter table WRI$_SQLSET_PLAN_LINES shrink space
*
ERROR at line 1:
ORA-10662: Segment has long columns

SQL> !oerr ora 10662
10662, 00000, "Segment has long columns"
// *Cause:  Shrink was issued on a segment with long columns. This is not
//          supported.
// *Action:

SQL> alter table _SQLSET_PLAN_LINES
  2
SQL> alter table WRI$_SQLSET_PLAN_LINES move;
alter table WRI$_SQLSET_PLAN_LINES move
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

而对于包含LONG的数据表无法进行move或者shrink space,oracle提供了一种方法,就是首先exp或者expdp将这部分数据导出,然后truncate该表,最后将数据导入到该表中。这里对于WRI$_SQLSET_PLAN_LINES表仅仅只是存储的SQL SET的信息,而这部分SQL SET已经被删除掉,这里直接truncate该表就可以释放出该表的空间,当然如果表中有数据则可以参考上面这种处理方式。

MOS中有一篇关于如何缩减SYSAUX表空间的文章 How to Reduce SYSAUX Tablespace Occupancy Due to Fragmented TABLEs and INDEXes (文档 ID 1563921.1)

Posted in oracle | 降低SQLSET相关表WRI$_SQLSET_PLAN_LINES高水位线,释放SYSAUX表空间已关闭评论

关于主表.列=子表.列这种写法隐患——子查询无法展开、返回多行报错

碰到一个SQL语句执行计划不合理,就详细分析了一下,结果发现自己对于子查询理解还存在一定的盲区,整理如下:

select wm_concat(r.recdefid)
  from t_test r
 where r.servnumber = (select t.servnumber
                         from t_test t
                        where t.oid = :1
                          and t.region = r.region)
   and r.recdate > sysdate - 1 / 144
 order by r.recdate asc

Plan hash value: 3667943477

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        |       |       | 25480 (100)|          |       |       |
|   1 |  SORT AGGREGATE                      |                        |     1 |    37 |            |          |       |       |
|*  2 |   FILTER                             |                        |       |       |            |          |       |       |
|   3 |    PARTITION RANGE MULTI-COLUMN      |                        |    19 |   703 | 25270   (1)| 00:05:04 |KEY(MC)|KEY(MC)|
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| T_TEST                 |    19 |   703 | 25270   (1)| 00:05:04 |KEY(MC)|KEY(MC)|
|*  5 |      INDEX SKIP SCAN                 | IDX_T_TEST_RECORGID    |    20 |       | 25255   (1)| 00:05:04 |KEY(MC)|KEY(MC)|
|   6 |    PARTITION RANGE ITERATOR          |                        |     1 |    24 |   209   (0)| 00:00:03 |   KEY |   KEY |
|   7 |     TABLE ACCESS BY LOCAL INDEX ROWID| T_TEST                 |     1 |    24 |   209   (0)| 00:00:03 |   KEY |   KEY |
|*  8 |      INDEX RANGE SCAN                | PK_CS_REC_T_TEST       |     1 |       |   208   (0)| 00:00:03 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("R"."SERVNUMBER"=)
   5 - access("R"."RECDATE">SYSDATE@!-.006944444444444444444444444444444444444444)
       filter("R"."RECDATE">SYSDATE@!-.006944444444444444444444444444444444444444)
   8 - access("T"."OID"=TO_NUMBER(:1) AND "T"."REGION"=:B1)
       filter("T"."REGION"=:B1)

该SQL执行计划确实是有问题的,合理的执行计划是先对子查询的t_test t表经过oid的唯一索引,找到一条servernumber,然后将这条数据传递给t_test r表,然后对表t_test r走servernumber上的索引,但是优化器这里先选择了外部表t_test r为驱动表,走r.recdate > sysdate – 1 / 144字段的跳跃索引,然后去filter(类似于nested loops)子查询表t.oid = :1

那么优化器为什么在原SQL是=时候不选择用子查询结果集做驱动表了,具体原因是跟cost计算、还是子查询无法展开表连接有关?

排除cost成本问题:
这里加上hint leadind指定子查询的T@SEL$2表做驱动表,也就是t_test t表,cbo忽略掉了这个hint,还是走原来的执行计划,一般而言只要hint的执行计划是可行的并且不违背sql的返回结果,优化器就会走hint的执行计划,加上hint的SQL跟cost成本计算无关:

explain plan for
select /*+leading(T@SEL$2)*/wm_concat(r.recdefid)
  from t_test r
 where r.servnumber =(select t.servnumber
                         from t_test t
                        where t.oid = :1
                          and t.region = r.region)
   and r.recdate > sysdate - 1 / 144
 order by r.recdate asc

select * from table(dbms_xplan.display(null,null,'advanced'))

Plan hash value: 3667943477

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        |     1 |    37 | 25480   (1)| 00:05:06 |       |       |
|   1 |  SORT AGGREGATE                      |                        |     1 |    37 |            |          |       |       |
|*  2 |   FILTER                             |                        |       |       |            |          |       |       |
|   3 |    PARTITION RANGE MULTI-COLUMN      |                        |    19 |   703 | 25270   (1)| 00:05:04 |KEY(MC)|KEY(MC)|
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| T_TEST                 |    19 |   703 | 25270   (1)| 00:05:04 |KEY(MC)|KEY(MC)|
|*  5 |      INDEX SKIP SCAN                 | IDX_T_TEST_RECORGID    |    20 |       | 25255   (1)| 00:05:04 |KEY(MC)|KEY(MC)|
|   6 |    PARTITION RANGE ITERATOR          |                        |     1 |    24 |   209   (0)| 00:00:03 |   KEY |   KEY |
|   7 |     TABLE ACCESS BY LOCAL INDEX ROWID| T_TEST                 |     1 |    24 |   209   (0)| 00:00:03 |   KEY |   KEY |
|*  8 |      INDEX RANGE SCAN                | PK_CS_REC_T_TEST       |     1 |       |   208   (0)| 00:00:03 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   4 - SEL$1 / R@SEL$1
   5 - SEL$1 / R@SEL$1
   6 - SEL$2
   7 - SEL$2 / T@SEL$2
   8 - SEL$2 / T@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("R"."SERVNUMBER"= (SELECT "T"."SERVNUMBER" FROM "TBCS"."T_TEST" "T" WHERE "T"."OID"=TO_NUMBER(:1) AND
              "T"."REGION"=:B1))
   5 - access("R"."RECDATE">SYSDATE@!-.006944444444444444444444444444444444444444)
       filter("R"."RECDATE">SYSDATE@!-.006944444444444444444444444444444444444444)
   8 - access("T"."OID"=TO_NUMBER(:1) AND "T"."REGION"=:B1)
       filter("T"."REGION"=:B1)

而如果用in的方式,优化器默认是选择t_test t做驱动表:

explain plan for
select wm_concat(r.recdefid)
  from t_test r
 where r.servnumber  in (select t.servnumber
                         from t_test t
                        where t.oid = :1
                          and t.region = r.region)
   and r.recdate > sysdate - 1 / 144
 order by r.recdate asc

Plan hash value: 1325607285

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                       |     1 |    61 |   360   (1)| 00:00:05 |       |       |
|   1 |  SORT AGGREGATE                        |                       |     1 |    61 |            |          |       |       |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID    | T_TEST                |     1 |    37 |   147   (0)| 00:00:02 |       |       |
|   3 |    NESTED LOOPS                        |                       |     8 |    61 |   360   (1)| 00:00:05 |       |       |
|   4 |     SORT UNIQUE                        |                       |     1 |    24 |   212   (0)| 00:00:03 |       |       |
|   5 |      PARTITION RANGE ALL               |                       |     1 |    24 |   212   (0)| 00:00:03 |     1 |    70 |
|   6 |       TABLE ACCESS BY LOCAL INDEX ROWID| T_TEST                |     1 |    24 |   212   (0)| 00:00:03 |     1 |    70 |
|*  7 |        INDEX RANGE SCAN                | PK_CS_REC_T_TEST      |     1 |       |   211   (0)| 00:00:03 |     1 |    70 |
|   8 |     PARTITION RANGE ITERATOR           |                       |     8 |       |   140   (0)| 00:00:02 |   KEY |   KEY |
|*  9 |      INDEX RANGE SCAN                  | IDX_T_TEST_SERVNUM    |     8 |       |   140   (0)| 00:00:02 |   KEY |   KEY |
--------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / R@SEL$1
   6 - SEL$5DA710D3 / T@SEL$2
   7 - SEL$5DA710D3 / T@SEL$2
   9 - SEL$5DA710D3 / R@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("R"."RECDATE">SYSDATE@!-.006944444444444444444444444444444444444444 AND "T"."REGION"="R"."REGION")
   7 - access("T"."OID"=TO_NUMBER(:1))
   9 - access("R"."SERVNUMBER"="T"."SERVNUMBER")

in的书写方式oracle可以将其展开为表连接,从而优化器能够自行选择返回数据更少的t_test t表做驱动表。

很显然原SQL语句没办法将子查询展开表连接,这个我们在原SQL中强制添加hint unnest的方式,执行计划还是没有变化

select wm_concat(r.recdefid)
  from t_test r
 where r.servnumber =(select /*+unnest*/t.servnumber
                         from t_test t
                        where t.oid = :1
                          and t.region = r.region)
   and r.recdate > sysdate - 1 / 144
 order by r.recdate asc

也不是所有的=子查询就会导致无法展开表连接的,比如这里我们将原SQL的t.region = r.region去掉

explain plan for
select wm_concat(r.recdefid)
  from t_test r
 where r.servnumber =(select t.servnumber
                         from t_test t
                        where t.oid = :1)
   and r.recdate > sysdate - 1 / 144
 order by r.recdate asc

select * from table(dbms_xplan.display)

Plan hash value: 1804346429

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                       |     1 |    34 |   147   (0)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE                        |                       |     1 |    34 |            |          |       |       |
|   2 |   PARTITION RANGE MULTI-COLUMN         |                       |     1 |    34 |   147   (0)| 00:00:02 |KEY(MC)|KEY(MC)|
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID   | T_TEST                |     1 |    34 |   147   (0)| 00:00:02 |KEY(MC)|KEY(MC)|
|*  4 |     INDEX RANGE SCAN                   | IDX_T_TEST_SERVNUM    |     7 |       |   141   (0)| 00:00:02 |KEY(MC)|KEY(MC)|
|   5 |      PARTITION RANGE ALL               |                       |     1 |    21 |   212   (0)| 00:00:03 |     1 |    70 |
|   6 |       TABLE ACCESS BY LOCAL INDEX ROWID| T_TEST                |     1 |    21 |   212   (0)| 00:00:03 |     1 |    70 |
|*  7 |        INDEX RANGE SCAN                | PK_CS_REC_T_TEST      |     1 |       |   211   (0)| 00:00:03 |     1 |    70 |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("R"."RECDATE">SYSDATE@!-.006944444444444444444444444444444444444444)
   4 - access("R"."SERVNUMBER"= (SELECT "T"."SERVNUMBER" FROM "TBCS"."T_TEST" "T" WHERE "T"."OID"=TO_NUMBER(:1)))
   7 - access("T"."OID"=TO_NUMBER(:1))

可见在这个SQL中造成执行计划错误是因为cbo没有办法将子查询展开为表连接,而造成没有办法展开为表连接的原因则是因为该sql书写方式是=的子查询书写方式,并且子查询中还嵌套了主查询的比较列,优化器对于子查询能够展开必须要和原SQL业务逻辑一致,这里优化器并不能智能的将该子查询展开。

关于子查询的写法:
尽量写成in、not in、exists和not exists方式,在oracle 11g(oracle 11g推出了null-aware anti join的算法,不过现网系统中对于该特性的隐含参数被置为了false)之前not in的方式如果比较列没有not null约束还是会导致子查询无法展开,写成=的方式不仅仅有隐患(如果子查询中还有主查询的列去做比较,优化器就没办法将其展开为表连接,这个在oracle 12c中都是如此,也没有办法展开为表连接),而且如果子查询返回多行数据,该SQL执行时候还会报错,如下:

SQL> select count(*) from t_test02 where object_id=100;

  COUNT(*)
----------
         3

SQL> select a.object_id from t_test01 a where a.data_object_id=(select b.data_object_id from t_test02 b where b.object_id=100);
select a.object_id from t_test01 a where a.data_object_id=(select b.data_object_id from t_test02 b where b.object_id=100)
                                                           *
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
Posted in oracle, sql tuning and troubleshooting | 关于主表.列=子表.列这种写法隐患——子查询无法展开、返回多行报错已关闭评论

oracle 12c R1新特性-在线迁移数据文件、在线迁移表分区或者子分区

在线重定义数据文件:
在oracle 12c R1之前的版本中,如果在线移动数据文件需要将表空间或者数据文件离线,然后操作系统mv,recover后online数据文件或者表空间,在oracle 12c R1后可以直接在线重定义数据文件,这个过程用户可以进行查询、DML以及DDL的任务,另外数据文件也可以直接在存储设备间迁移,比如ASM到文件系统的相互迁移。

SQL> select name from v$datafile;

NAME
-----------------------------------------------------------------------------------------------------------------------------------------
/oracle12c/app/oracle12/oradata/oracle12c/system01.dbf
/oracle12c/app/oracle12/oradata/oracle12c/sysaux01.dbf
/oracle12c/app/oracle12/oradata/oracle12c/undotbs01.dbf
/oracle12c/app/oracle12/oradata/oracle12c/undotbs02.dbf
/oracle12c/app/oracle12/oradata/oracle12c/users01.dbf

在线移动数据文件

SQL> alter database move datafile '/oracle12c/app/oracle12/oradata/oracle12c/users01.dbf' to '/oracle12c/app/oracle12/oradata/oracle12c/users02.dbf';

Database altered.

SQL> !ls -l /oracle12c/app/oracle12/oradata/oracle12c/
total 2258588
-rw-r-----. 1 oracle12 oinstall   10141696 Dec 16 23:31 control01.ctl
-rw-r-----. 1 oracle12 oinstall   52429312 Dec 16 23:30 redo01.log
-rw-r-----. 1 oracle12 oinstall   52429312 Dec 16 22:00 redo02.log
-rw-r-----. 1 oracle12 oinstall   52429312 Dec 16 23:13 redo03.log
-rw-r-----. 1 oracle12 oinstall  880812032 Dec 16 23:28 sysaux01.dbf
-rw-r-----. 1 oracle12 oinstall 1006641152 Dec 16 23:30 system01.dbf
-rw-r-----. 1 oracle12 oinstall   62922752 Dec 16 23:23 temp01.dbf
-rw-r-----. 1 oracle12 oinstall   68165632 Dec 16 23:28 undotbs01.dbf
-rw-r-----. 1 oracle12 oinstall    1056768 Dec 16 23:18 undotbs02.dbf
-rw-r-----. 1 oracle12 oinstall  149430272 Dec 16 23:22 users02.dbf

在线移动数据文件并且保留之前的数据文件位置
SQL> alter database move datafile '/oracle12c/app/oracle12/oradata/oracle12c/users02.dbf' to '/oracle12c/app/oracle12/oradata/oracle12c/users01.dbf' keep;

Database altered.

SQL> !ls -l /oracle12c/app/oracle12/oradata/oracle12c/
total 3185324
-rw-r-----. 1 oracle12 oinstall   10141696 Dec 16 23:40 control01.ctl
-rw-r-----. 1 oracle12 oinstall   52429312 Dec 16 23:40 redo01.log
-rw-r-----. 1 oracle12 oinstall   52429312 Dec 16 23:40 redo02.log
-rw-r-----. 1 oracle12 oinstall   52429312 Dec 16 23:40 redo03.log
-rw-r-----. 1 oracle12 oinstall  880812032 Dec 16 23:40 sysaux01.dbf
-rw-r-----. 1 oracle12 oinstall 1006641152 Dec 16 23:40 system01.dbf
-rw-r-----. 1 oracle12 oinstall   62922752 Dec 16 23:23 temp01.dbf
-rw-r-----. 1 oracle12 oinstall   68165632 Dec 16 23:40 undotbs01.dbf
-rw-r-----. 1 oracle12 oinstall    1056768 Dec 16 23:40 undotbs02.dbf
-rw-r-----. 1 oracle12 oinstall  549199872 Dec 16 23:40 users01.dbf
-rw-r-----. 1 oracle12 oinstall  549199872 Dec 16 23:40 users02.dbf

在线迁移表分区或者子分区:

SQL> CREATE TABLE t_part
  (
    id   NUMBER,
    name VARCHAR2(100)
  )
  partition BY range
  (
    id
  )
  (
    partition p1 VALUES less than(1000) TABLESPACE users,
    partition pmax VALUES less than(maxvalue) TABLESPACE test
  )

SQL> show user;
USER is "SYS"
SQL>  alter table t_part move partition p1 tablespace test;

Table altered.
SQL> alter table t_part move partition p1 tablespace test online;
alter table t_part move partition p1 tablespace test online
            *
ERROR at line 1:
ORA-14809: ONLINE MOVE PARTITION not supported for this schem

这里需要注意在线迁移表分区或者子分区的特性只对非SYS用户使用,SYS用户不支持这个特性
SQL> conn xiaoyu/xiaoyu
Connected.
SQL>  alter table t_part move partition p1 tablespace test  update indexes online;

Table altered.

这里的在线迁移并不会影响该表的DML操作,为了满足在线迁移的特性,oracle需要引入锁机制,这个会导致下降并且产生大量的redo,影响程度取决于表分区或者子分区的大小,当然oracle推出的新特性可能也会存在一定的隐患,这个需要dba多去关注mos上的文章。

Posted in oracle | oracle 12c R1新特性-在线迁移数据文件、在线迁移表分区或者子分区已关闭评论

oracle 12c R1执行计划新特性-table access by index rowid batched和INMOMEORY OPTION

oracle 12c R1执行计划在索引回表阶段oracle推出了batched特性,类似于oracle 11g中在nested loop中被驱动表回表时的向量IO,也是为了有效的解决表中数据无序性(索引的聚簇因子),下面看实际测试用例:

数据库版本:12.1.0.2版本

sys@CRMDB2>
explain plan for SELECT offering_inst_id,
       offering_id,
       owner_party_role_type,
       owner_party_role_id,
       purchase_seq,
       brand,
       primary_flag,
       rel_pri_offering_inst_id,
       bundle_flag,
       p_offering_inst_id,
       apply_obj_type,
       apply_obj_id,
       status,
       status_detail,
       status_date,
       eff_date,
       。。。。。。
  FROM transdata.bk_INF_OFFERING_INST t
 WHERE be_id = 18
   AND modify_time <= to_date(20151104 || 235959, 'yyyymmddhh24miss')
   AND modify_time >= to_date(20151104 || 0, 'yyyymmddhh24miss')
   AND SUBS_ID >= 1842201100000000
   AND SUBS_ID < 1842211100000000;

sys@CRMDB2>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3059718575

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                       |     1 |   165 |  5118   (1)| 00:00:01 |
|*  1 |  FILTER                              |                       |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| BK_INF_OFFERING_INST  |     1 |   165 |  5118   (1)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IDX_INF_OFFERING_CQL1 | 28466 |       |    87   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE('201511040','yyyymmddhh24miss')<=TO_DATE(' 2015-11-04 23:59:59', 'syyyy-mm-dd
              hh24:mi:ss'))
   2 - filter("MODIFY_TIME">=TO_DATE('201511040','yyyymmddhh24miss') AND "MODIFY_TIME"<=TO_DATE('
              2015-11-04 23:59:59', 'syyyy-mm-dd hh24:mi:ss') AND "BE_ID"=18)
   3 - access("SUBS_ID">=1842201100000000 AND "SUBS_ID"<1842211100000000)

19 rows selected.

执行计划中的table access by index rowid batched,这个是12.1中的新特性,主要是在通过rowid访问数据块时,对于一个数据块中的多个rowid,通过批量访问减少访问块的次数,该特性由隐含参数_optimizer_batch_table_access_by_rowid来控制,该参数默认为true,也就是开启这个特性。

sys@CRMDB2>select a.ksppinm, a.ksppdesc,b.ksppstvl,a.inst_id
  2    from x$ksppi a, x$ksppcv b
  3   where a.inst_id = 1
  4     and a.ksppinm = '&param'
  5     and a.indx = b.indx;
Enter value for param: _optimizer_batch_table_access_by_rowid
old   4:    and a.ksppinm = '&param'
new   4:    and a.ksppinm = '_optimizer_batch_table_access_by_rowid'

KSPPINM                        KSPPDESC                       KSPPSTVL                          INST_ID
------------------------------ ------------------------------ ------------------------------ ----------
_optimizer_batch_table_access_ enable table access by ROWID I TRUE                                    1
by_rowid                       O batching

12.1.0.2版本中,oracle正式发布了内存和列式计算的In-Memory Option,In-Meomory option列存与压缩,数据在内存的独立区域中按照列式存储,数据是被压缩存放的,内存与列式存储可以极大提升查询的性能

SQL> show sga;
Total System Global Area  599785472 bytes
Fixed Size                  2927192 bytes
Variable Size             335545768 bytes
Database Buffers          150994944 bytes
Redo Buffers                5459968 bytes
In-Memory Area            104857600 bytes

IMO的特性相关的参数:
SQL> show parameter inmemory;

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

启动IMO特性,需要设置inmemory_size参数,指定可以作为内存中列存的内存区域,该参数为静态参数,需要重启数据库才能使之生效,sga中会分配一部分内存来作为IMO部分存储,Inmemory_max_populate_servers参数用于将数据加载到内存的后台进程数量。

SQL> select * from v$inmemory_area;

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS                CON_ID
-------------------------- ----------- ---------- -------------------------- ----------
1MB POOL                      82837504    4194304 DONE                                0
64KB POOL                      4194304     131072 DONE                                0

SQL> select * from t_inmemory;

728520 rows selected.

Elapsed: 00:00:09.99

Execution Plan
----------------------------------------------------------
Plan hash value: 2996090576

-----------------------------------------------------------------------------------------
| Id  | Operation                  | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |            | 91065 |     9M|    20  (20)| 00:00:01 |
|   1 |  TABLE ACCESS FULL         | T_INMEMORY | 91065 |     9M|    20  (20)| 00:00:01 |
-----------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
      60188  consistent gets
          0  physical reads
          0  redo size
   40118128  bytes sent via SQL*Net to client
     534788  bytes received via SQL*Net from client
      48569  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
     728520  rows processed

在没有将表T_INMEMORY加载到INMEMOYR中之前,消耗逻辑读要60188,INMEMORY_AREA空间没有使用

SQL> alter table t_inmemory inmemory;

Table altered.

SQL> select * from v$inmemory_area;

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS                CON_ID
-------------------------- ----------- ---------- -------------------------- ----------
1MB POOL                      82837504    4194304 DONE                                0
64KB POOL                      4194304     131072 DONE                                0
将表T_INMEMORY加载到INMEMORY POOL中,此时在没有对表进行查询之前,INMEMORY_AREA pool的空间是没有被使用的

表加载到INMEMORY_AREA pool中后,第一次查询T_INMEMORY表:

SQL> select * from t_inmemory;

728520 rows selected.

Elapsed: 00:00:09.99

Execution Plan
----------------------------------------------------------
Plan hash value: 2996090576

-----------------------------------------------------------------------------------------
| Id  | Operation                  | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |            | 91065 |     9M|    20  (20)| 00:00:01 |
|   1 |  TABLE ACCESS INMEMORY FULL| T_INMEMORY | 91065 |     9M|    20  (20)| 00:00:01 |
-----------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
      60188  consistent gets
          0  physical reads
          0  redo size
   40118128  bytes sent via SQL*Net to client
     534788  bytes received via SQL*Net from client
      48569  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
     728520  rows processed
执行计划出现了变化,新增加了INMEMORY选项,此时逻辑读依然是60188,但是INMEMORY_POOL出现了变化,USED_BYTES增加

SQL> select * from v$inmemory_area;

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS                CON_ID
-------------------------- ----------- ---------- -------------------------- ----------
1MB POOL                      82837504   18874368 DONE                                0
64KB POOL                      4194304     327680 DONE                                0
再次查询,发现逻辑读已经大幅度降低,查询时间也降低到了3秒82

SQL> select * from t_inmemory;

728520 rows selected.

Elapsed: 00:00:03.82

Execution Plan
----------------------------------------------------------
Plan hash value: 2996090576

-----------------------------------------------------------------------------------------
| Id  | Operation                  | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |            | 91065 |     9M|    20  (20)| 00:00:01 |
|   1 |  TABLE ACCESS INMEMORY FULL| T_INMEMORY | 91065 |     9M|    20  (20)| 00:00:01 |
-----------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
   40118128  bytes sent via SQL*Net to client
     534788  bytes received via SQL*Net from client
      48569  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     728520  rows processed
此时我们查询别的相关的SQL语句,逻辑读大幅度降低
SQL> select count(*) from t_inmemory;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2112900194

----------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    16   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |            |     1 |            |          |
|   2 |   TABLE ACCESS INMEMORY FULL| T_INMEMORY | 91065 |    16   (0)| 00:00:01 |
----------------------------------------------------------------------------------


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

而如果我们将表T_INMEMORY移出IN_MEMORY pool中,逻辑读将回归到之前的数据

SQL> select * from v$inmemory_area;

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS                CON_ID
-------------------------- ----------- ---------- -------------------------- ----------
1MB POOL                      82837504    4194304 DONE                                0
64KB POOL                      4194304     131072 DONE                                0

SQL> select * from t_inmemory;

728520 rows selected.

Elapsed: 00:00:04.10

Execution Plan
----------------------------------------------------------
Plan hash value: 2996090576

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            | 91065 |     9M|   426   (1)| 00:00:01 |oracle 12c R1 R1
|   1 |  TABLE ACCESS FULL| T_INMEMORY | 91065 |     9M|   426   (1)| 00:00:01 |
--------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
      60182  consistent gets
          0  physical reads
          0  redo size
   97202838  bytes sent via SQL*Net to client
     534788  bytes received via SQL*Net from client
      48569  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
     728520  rows processed
Posted in oracle, sql tuning and troubleshooting | oracle 12c R1执行计划新特性-table access by index rowid batched和INMOMEORY OPTION已关闭评论