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功能介绍已关闭评论

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已关闭评论

AIX基本命令topas简介

由于最近工作需要涉及到AIX主机、存储层面,就对这方面的内容做个简要的笔记,以供后续参考。

topas命令利用System Performance Measurement Interface(SPMI) API获得有关信息,使系统开销保持最小程度,topas命令用于监控各种系统资源,比如cpu使用情况、内存和换页空间、磁盘性能、网络性能以及NFS统计信息等

Topas Monitor for host:    pgrid1               EVENTS/QUEUES    FILE/TTY
Thu Oct 15 21:13:28 2015   Interval:  2         Cswitch    1196  Readch     3694
                                                Syscall 2144.2K  Writech 1292.5K
CPU  User%  Kern%  Wait%  Idle%                 Reads        14  Rawin         0
ALL   19.7    6.0   20.9   53.4                 Writes      139  Ttyout      296
                                                Forks         0  Igets         0
Network  KBPS   I-Pack  O-Pack   KB-In  KB-Out  Execs         0  Namei        25
Total     9.0     52.0    50.5     4.4     4.6  Runqueue    2.0  Dirblk        0
                                                Waitqueue   5.5
Disk    Busy%     KBPS     TPS KB-Read KB-Writ                   MEMORY
Total   100.0   2646.2   352.0     0.0  2646.2  PAGING           Real,MB   61438
                                                Faults       27  % Comp     52
FileSystem        KBPS     TPS KB-Read KB-Writ  Steals      129  % Noncomp  19
Total              1.3K  145.5    3.6    1.3K   PgspIn        0  % Client   19
                                                PgspOut       0
Name            PID  CPU%  PgSp Owner           PageIn        0  PAGING SPACE
topasrec   26017938  13.3   4.2 root            PageOut     325  Size,MB   65536
emcp_mon    5767372  12.8   3.7 root            Sios        326  % Used      0
topas      43712720   0.0   8.7 oracle                           % Free    100
oracle     59048168   0.0  14.5 oracle          NFS (calls/sec)
agent      14155828   0.0  27.9 toptea          SerV2         0  WPAR Activ    0
oracle     11403504   0.0  17.9 oracle          CliV2         0  WPAR Total    0
xmtopas    58523722   0.0   4.3 root            SerV3         0  Press: "h"-help
oracle     44826690   0.0  14.5 oracle          CliV3         0         "q"-quit
oracle     45613158   0.0  16.5 oracle
aioserve   36110368   0.0   0.4 oracle
aioserve   15990858   0.0   0.4 oracle
aioserve   14024870   0.0   0.4 oracle
aioserve   18808886   0.0   0.4 oracle
aioserve   20316332   0.0   0.4 oracle
aioserve   55247090   0.0   0.4 oracle
aioserve   14811342   0.0   0.4 oracle
aioserve   18612470   0.0   0.4 oracle
aioserve   47120470   0.0   0.4 oracle
aioserve   10092550   0.0   0.4 oracle
aioserve   56361084   0.0   0.4 oracle

接下来对上面的topas命令做一个简要的说明:

CPU部分:
CPU  User%  Kern%  Wait%  Idle%
ALL   19.7    6.0   20.9   53.4

其中的user%显示的是用户进程占用的cpu时间比例,kern%显示的操作系统内核占用的cpu时间比例,wait%显示用于等待IO资源上占用cpu的时间比例(当这个值比较高,表示IO已经存在瓶颈),idle%显示的cpu空闲时间比例

如果按两次c键则会显示CPU列表,按一次c键又会关闭这个列表
CPU  User%  Kern%  Wait%  Idle%
14    76.1   17.5    0.0    6.4
0     68.4   14.2    0.0   17.3
12    21.3   16.7    0.0   62.0
4      9.4    2.3    0.0   88.4
8      7.7    6.0    0.0   86.3
6      7.4    2.2    0.0   90.4
2      0.2    0.4    0.0   99.4
10     0.0    0.1    0.0   99.9

网络接口:
Network  KBPS   I-Pack  O-Pack   KB-In  KB-Out
Total     9.0     52.0    50.5     4.4     4.6

network表示的是网路接口的名称,KBPS表示的在监视时间内每秒钟总吞吐量,I-Pack显示的是监视时间内每秒钟收到的数据包数目,O-Pack显示的是监视时间内每秒钟发送数据库数目,KB-In显示的是每秒钟接受的KB数目,KB-Out表示的是每秒钟发送的KB数目

同样按n键后可以以列表形式显示
Network  KBPS   I-Pack  O-Pack   KB-In  KB-Out
lo0       8.4     50.0    50.0     4.2     4.2
en0       1.0      4.0     4.0     0.2     0.8
en4       0.2      1.0     1.0     0.1     0.1
磁盘部分:
Disk    Busy%     KBPS     TPS KB-Read KB-Writ
Total   100.0   2646.2   352.0     0.0  2646.2

Disk表示物理磁盘的名称;busy%表示物理磁盘繁忙的百分比;即磁盘能满足的最大IOPS(每秒IO操作数)和当前IO数量的比率;KBPS表示的是监视时间内每秒钟读和写的KB数目;TPS表示的是每秒钟发送到物理磁盘的传输数据,传输是对物理磁盘的IO请求,多个逻辑请求可组合成对磁盘的单个IO请求;KB_read和KB_write则表示的物理磁盘每秒钟读取和写入BK的数目。

同样按d键后可以详细的显示各个物理磁盘的信息:
Disk    Busy%     KBPS     TPS KB-Read KB-Writ
power601  0.0     8.0      0.0    0.0     8.0
hdisk0    0.0     0.0      0.0    0.0     0.0
hdisk1    0.0     0.0      0.0    0.0     0.0
hdisk95   0.0     0.0      0.0    0.0     0.0
hdisk103  0.0     0.0      0.0    0.0     0.0
hdisk99   0.0     0.0      0.0    0.0     0.0
hdisk100  0.0     0.0      0.0    0.0     0.0
hdisk110  0.0     0.0      0.0    0.0     0.0
hdisk102  0.0     0.0      0.0    0.0     0.0
hdisk104  0.0     0.0      0.0    0.0     0.0
hdisk105  0.0     0.0      0.0    0.0     0.0
hdisk101  0.0     0.0      0.0    0.0     0.0
hdisk96   0.0     0.0      0.0    0.0     0.0
进程部分:
Name            PID  CPU%  PgSp Owner
topasrec   26017938  13.3   4.2 root
emcp_mon    5767372  12.8   3.7 root
topas      43712720   0.0   8.7 oracle
oracle     59048168   0.0  14.5 oracle
agent      14155828   0.0  27.9 toptea
oracle     11403504   0.0  17.9 oracle
xmtopas    58523722   0.0   4.3 root
oracle     44826690   0.0  14.5 oracle
oracle     45613158   0.0  16.5 oracle
aioserve   36110368   0.0   0.4 oracle
aioserve   15990858   0.0   0.4 oracle
aioserve   14024870   0.0   0.4 oracle
aioserve   18808886   0.0   0.4 oracle
aioserve   20316332   0.0   0.4 oracle
aioserve   55247090   0.0   0.4 oracle
aioserve   14811342   0.0   0.4 oracle
aioserve   18612470   0.0   0.4 oracle
aioserve   47120470   0.0   0.4 oracle
aioserve   10092550   0.0   0.4 oracle
aioserve   56361084   0.0   0.4 oracle

在进程部分topas默认列出最消耗cpu的几个进程
其中name表示的可执行程序名称,PID表示的进程id,%cpu表示该进程占用cpu的资源,PgSp表示分配给此进程的调页空间大小,即进程占用的pagingspace的空间大小,ower表示进程所有者

事件/队列部分:

EVENTS/QUEUES    FILE/TTY
Cswitch    1196  Readch     3694
Syscall 2144.2K  Writech 1292.5K
Reads        14  Rawin         0
Writes      139  Ttyout      296
Forks         0  Igets         0
Execs         0  Namei        25
Runqueue    2.0  Dirblk        0
Waitqueue   5.5

显示的是所选系统全局事件的每秒频率、线程运行和等待队列平均大小

Cswitch:在监视时间内每秒上下文切换的数量
Syscalls:在监视时间内每秒执行的系统调用数量
read:在监视时间内每秒执行的read系统调用数量
write:在监视时间内每秒执行的write系统调用数量
forks:在监视时间内每秒执行的fork系统调用数量
execs:在监视时间内每秒执行的exec系统调用数量
runqueue:准备运行但需要等待处理器可用的平均线程数目
waitrunqueue:正在等待页面调度完成的平均线程数目

文件/TTY部分:
FILE/TTY
Readch 3694
Writech 1292.5K
Rawin 0
Ttyout 296
Igets 0
Namei 25
Dirblk 0

显示的所选文件与tty统计信息的每秒频率

readch:在监视时间内read系统调用每秒读的字节数
writech:在监视时间内write系统调用每秒写的字节数
rawin:在监视时间内从TTY中每秒读取的原始字节数
Ttyout:在监视时间内每秒写入的TTY中的字节数
Igets:在监视时间内每秒调用信息节点查找历程的数量
Namei:在监视时间内每秒调用路径名查找历程的数量
Dirblk:在监视时间内被目录搜索历程每秒扫描的目录快数目

页面调度:
PAGING
Faults 27
Steals 129
PgspIn 0
PgspOut 0
PageIn 0
PageOut 325
Sios 326

换页空间即磁盘磁盘上的空间,在AIX操作系统中用来做内存空间使用,磁盘空间的速度相对于内存慢很多,换页空间只是内存页面的一个暂时存放地,存放的是那些长期不怎么用到的内存页面,如果paging大量出现,这个时候就说明了内存不够用了.页面调度主要要关注Pagein和Pageout部分,如果这两个值长期较大,在技术上叫做内存颠婆,即不停的把内存页面换到磁盘空间上,由于磁盘空间把内存页面读进来,系统的内存使用效率变的极差,系统响应性能也会变慢。

faults:在监视时间内每秒页面出错的数量,这个主要是应用程序指无法申请到内存
steals:在监视时间内每秒钟有物理内存4K帧被虚拟内管管理器占用
PgspIn:在监视时间内每秒钟从调页空间读取的4K页面的数量
PgspOut:在监视时间内每秒钟从把4K页面写入调页空间额数量
PageIn:在监视时间内每秒钟读取4K页面的数量。这包括于从文件系统读取有关的页面调度活动,从这个值减去PgspIn就可以得到在监视时间内每秒钟从文件系统读取的4K页面的数量
PageOut:在监视时间内每秒钟写4K页面的数量。从这个值中减去PgspOut就可以得到在监视时间内每秒钟写入文件系统4k页面的数量
Sios:在监视监视内虚拟内存管理器每秒钟发出的IO请求数目

内存部分:
MEMORY
Real,MB 61438
% Comp 52
% Noncomp 19
% Client 19

显示实际内存大小与内存使用的分布情况:

Real,MB:实际的内存大小
% Comp :当前分配给计算页面帧的实际内存的百分数,计算页面帧通常是那些被调页空间支持的帧
% Noncomp:当前分配给非计算页面帧的实际内存百分数,非计算页面帧通常是那些被文件空间支持的帧
% Client :当前被分配用来高速缓存远程安装的文件的实际内存的百分数

计算型内存就是进程实际使用的内存,例如程序中用到了堆栈,进程中变量熟知都需要在内存中保存,这部分内存就是计算型内存,而操作系统进行的文件读写,需要的IO缓冲区,或者写程序时打开文件、读写文件、均在文件件缓冲区进行,这部分内存就是非计算型内存。

调页空间:
PAGING SPACE
Size,MB 65536
% Used 0
% Free 100

%used:当前在使用的调页空间的百分比
%free:当前未使用的调页空间的百分比

如果调页空格键的使用率长期增长,就说明系统内存已经严重不足,已经开始使用磁盘空间来缓冲内存了(一般达到50%就需要警惕了)

NFS部分:
NFS (calls/sec)
SerV2 0 WPAR Activ 0
CliV2 0 WPAR Total 0
SerV3 0 Press: “h”-help
CliV3 0 “q”-quit

oracle@pgrid1:/home/oracle$ uname -a
AIX pgrid1 1 6 00C30C964C00
分别表示的是操作系统名、节点名称、操作系统的发型版本号、操作系统版本硬件运行系统的机器ID号

root@pgrid1:[/]# lsdev -C|grep proc
proc0          Available 00-00       Processor
proc2          Available 00-02       Processor
proc4          Available 00-04       Processor
proc6          Available 00-06       Processor
proc8          Available 00-08       Processor
proc10         Available 00-10       Processor
proc12         Available 00-12       Processor
proc14         Available 00-14       Processor

lsdev查看cpu数量

root@pgrid1:[/]# bootinfo -r
62914560
bootinfo -r查看系统的内存

root@pgrid1:[/]# bootinfo -y
64
bootinfo查看系统的位数

root@pgrid1:[/]# errpt
IDENTIFIER TIMESTAMP  T C RESOURCE_NAME  DESCRIPTION
A924A5FC   1105163415 P S SYSPROC        SOFTWARE PROGRAM ABNORMALLY TERMINATED
A924A5FC   1101041415 P S SYSPROC        SOFTWARE PROGRAM ABNORMALLY TERMINATED
A924A5FC   1101022115 P S SYSPROC        SOFTWARE PROGRAM ABNORMALLY TERMINATED
9D30B78E   1031101315 T S tty0           RECEIVER OVER-RUN ON INPUT
4DA8FE60   1030115015 T S DR_KER_MEM     Memory related DR operation failed
4DA8FE60   1030061515 T S DR_KER_MEM     Memory related DR operation failed
A924A5FC   1028005315 P S SYSPROC        SOFTWARE PROGRAM ABNORMALLY TERMINATED
A924A5FC   1020190815 P S SYSPROC        SOFTWARE PROGRAM ABNORMALLY TERMINATED
A924A5FC   1015174515 P S SYSPROC        SOFTWARE PROGRAM ABNORMALLY TERMINATED
F7FA22C9   1014164115 I O SYSJ2          UNABLE TO ALLOCATE SPACE IN FILE SYSTEM
A6D1BD62   1014002515 I H unspecified    Firmware Event

root@pgrid1:[/]# errpt -aj A924A5FC
---------------------------------------------------------------------------
LABEL:          CORE_DUMP
IDENTIFIER:     A924A5FC

Date/Time:       Thu Nov  5 16:34:11 GMT+08:00 2015
Sequence Number: 3679
Machine Id:      00C30C964C00
Node Id:         pgrid1
Class:           S
Type:            PERM
WPAR:            Global
Resource Name:   SYSPROC

Description
SOFTWARE PROGRAM ABNORMALLY TERMINATED

Probable Causes
SOFTWARE PROGRAM

User Causes
USER GENERATED SIGNAL

        Recommended Actions
        CORRECT THEN RETRY

Failure Causes
SOFTWARE PROGRAM

        Recommended Actions
        RERUN THE APPLICATION PROGRAM
        IF PROBLEM PERSISTS THEN DO THE FOLLOWING
        CONTACT APPROPRIATE SERVICE REPRESENTATIVE

。。。。。。

erupt可以查看系统的报错信息

root@pgrid1:[/]# oslevel -r
6100-06
查看详细的操作系统版本

root@pgrid1:[/]# lsps -a
Page Space      Physical Volume   Volume Group Size %Used Active  Auto  Type Chksum
lvswap01        hdisk1            rootvg       32768MB     1   yes   yes    lv     0
hd6             hdisk0            rootvg       32768MB     1   yes   yes    lv     0

lsps查看交换空间的大小及其使用率
root@pgrid1:[/]# swap -l
device              maj,min        total       free
/dev/lvswap01         10, 17     32768MB     32708MB
/dev/hd6              10,  2     32768MB     32717MB

swap也是查看交换空间使用信息
root@pgrid1:[/]# vmstat 3

System configuration: lcpu=16 mem=61438MB

kthr    memory              page              faults        cpu    
----- ----------- ------------------------ ------------ -----------
 r  b   avm   fre  re  pi  po  fr   sr  cy  in   sy  cs us sy id wa
 2  2 11105499 1268208   0   0   0 2030 2031   0 622 2219481 1898 20  7 58 15
 2  3 11105501 1268084   0   0   0 1470 1471   0 525 2192650 1630 21  6 60 13
 3  3 11105502 1268204   0   0   0 4594 4595   0 1868 2186276 4102 22  8 57 13
vmstat和linux中的vmstat类似,可以查看内存、进程、cpu、调页空间等使用状况。
Posted in linux、unix、windows | AIX基本命令topas简介已关闭评论