Category Archives: Oracle

Oracle Varray变量

Posted on by

[oracle@ucjmh ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 7 09:01:40 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set serveroutput on

SQL> DECLARE

— declare fixed array

TYPE arry_num IS VARRAY(10) OF NUMBER;

arry_top arry_num;

v_n int;

BEGIN

— init array

arry_top := arry_num(1,2,3);

dbms_output.put_line(arry_top.LIMIT());

dbms_output.put_line(arry_top.count());

dbms_output.put_line(arry_top.FIRST());

–dbms_output.put_line(arry_top.NEXT());

— dbms_output.put_line(arry_top.PRIOR());

dbms_output.put_line(arry_top.LAST());

dbms_output.put_line(arry_top(2));

FOR I IN 1..arry_top.COUNT() LOOP

v_n:=arry_top(I);

dbms_output.put_line(v_n);

DBMS_OUTPUT.PUT_LINE(‘arry_top(‘||I||’)=’ || arry_top(I));

END LOOP;

END;

/

10

3

1

3

2

1

arry_top(1)=1

2

arry_top(2)=2

3

arry_top(3)=3

PL/SQL procedure successfully completed.

SQL>

声明和初始化VARRAY变量

你不可以直接声明一个VARRAY变量。必须先声明一个包含这个数组最大容量的类型：
1. TYPE MY_ARRAY_TYPE IS VARRAY(10) OF NUMBER;
然后，你就可以用这个类型声明VARRAY变量：
1. V MY_ARRAY_TYPE;
在你对这个数组进行操作以前，必须先初始化该数组。你既可以在声明它的时候对其初始化，也可以在声明后对其赋值。下面展示了在声明的同时对其进行初始化：
1. V MY_ARRAY_TYPE := MY_ARRAY_TYPE ();
从指定的条目处取值

把条目的数目作为下标。下标可以是返回整数值(该值小于或等于数组条目数)的任意表达式，如：
1. K := V(3); 2. I := 2; 3. L := V(I+1);
得到数组的容量

对VARRAY变量使用COUNT()方法。注意，你不需要指定圆括号，因为这个方法没有输入参数：
1. N_ENTRIES := V.COUNT();
这告诉你这个数组中正在使用的条目数。当VARRAY类型被声明的时候，其最大的容量也就被定义了。你可以用LIMIT()方法得到该容量：
1. ARRAY_CAPACITY := V.LIMIT();
遍历VARRAY中的值

你可以使用多种技术。最简单的是使用FOR循环：
1. FOR I IN 1..V.COUNT() LOOP 2. DBMS_OUTPUT.PUT_LINE(‘V(‘||I||’)=’ || V(I)); 3. END LOOP;
你也可以使用FIRST()和LAST()方法。FIRST()返回数组的第一个条目的下标(总是1)，LAST()返回数组的最后一个条目的下标(与COUNT方法相同)。
1. FOR I IN V.FIRST()..V.LAST() LOOP 2. DBMS_OUTPUT.PUT_LINE(‘V(‘||I||’)=’ || V(I)); 3. END LOOP;
你也可以使用PRIOR(n)和NEXT(n)方法，这两个方法分别返回给定条目的前一个和后一个条目的下标。例如，下面的代码用来向后遍历整个数组：
1. I := V.COUNT(); 2. WHILE I IS NOT NULL LOOP 3. DBMS_OUTPUT.PUT_LINE(‘V(‘||I||’)=’ || V(I)); 4. I := V.PRIOR(I); 5. END LOOP;
PRIOR(n)和n-1是一样的，NEXT(n)和n+1是一样的，但是PRIOR(1)和NEXT(V.COUNT())则返回NULL。
对VARRAY的扩展

使用EXTEND(k)方法。这个方法可以在VARRAY的最后追加k个新的条目。如果k没有被指定，只增加一个条目。新增的条目没有值(默认为NULL)，但是你可以对它们进行初始化。COUNT()和LAST()方法现在可以反映VARRAY新的容量。以下代码向数组中添加两个条目并对其进行初始化：
1. I := V.LAST(); 2. V.EXTEND(2); 3. V(I+1) := 5; 4. V(I+2) := 6;
注意，你对VARRAY的扩展不可以超过其最大容量(通过LIMIT()方法得到)，且在对VARRAY扩展前必须要对其进行初始化。以下代码是无效的：
1. VT MY_ARRAY_TYPE; 2. VT.EXTEND(5);
下面的代码是有效的：
1. VT MY_ARRAY_TYPE; 2. VT := MY_ARRAY_TYPE(); 3. VT.EXTEND(5);
对VARRAY的缩减

使用TRIM(k)方法。这个方法在VARRAY的尾部删除最后k个条目。当k没有被指定时，删除最后一个条目。已被删除的条目的值将丢失。COUNT()和LAST()方法反映了新的容量。下面从VARRAY中删除最后一个条目：
1. V.TRIM;
你也可以用以下方式删除数组中的所有条目：
1. V.TRIM(V.COUNT());
或者用具有效果相同的DELETE()方法。该方法删除数组中的所有条目，并把其容量设置为0(也就是说，V.COUNT()现在的返回值为0)。
1. V.DELETE()

使用SQL实现对邮政编码分类规则

Posted on by

早晨在大阪工作的学长发信息过来要想用SQL实现对日本邮政编码分类的规则.本来学长只需要做概要设计即可,详细设计和开发都是外包给越南人做,但是担心越南的童鞋方法不能够满意,索性就直接把关键的步骤都自己实现掉,想想也是为了世界和平做了不少贡献;

规则是这样的:
1.日本邮编是7位的;
2.最终的结果集只需要两列(前缀 个数);
3.第一优先级是尾数为[00]的邮政编码;
4.第二优先级是前5位数字相同的邮政编码,只展现出现次数大于10次的结果,如果不足10次的邮政编码进入下一优先级统计;
5.第三优先级是前4位数字相同的邮政编码,规则同上;
6.第四优先级是前3位数字相同的邮政编码,规则同上;
7.第五优先级是剩余其它的邮政编码;

— 测试的数据,生成20W个邮政编码;
DROP TABLE TBPOSTTEST;
CREATE TABLE TBPOSTTEST AS
SELECT LPAD(ROUND(DBMS_RANDOM.VALUE (1, 9999999)) , 7, 0) postcode FROM DUAL
CONNECT BY LEVEL <= 200000;

— 实现的sql语句;
SELECT PREFIX, COUNT FROM (
WITH
P0P0 AS (SELECT ‘____00′ AS “PREFIX”, COUNT(*) c0 FROM TBPOSTTEST WHERE SUBSTR(postcode, LENGTH(postcode)-1) = ’00’),
P1P5 AS (SELECT SUBSTR(postcode, 0, 5) cp5, COUNT(*) c5 FROM TBPOSTTEST WHERE SUBSTR(postcode, LENGTH(postcode)-1) <> ’00’ GROUP BY SUBSTR(postcode, 0, 5)),
P2P4 AS (SELECT SUBSTR(cp5, 0, 4) cp4, SUM(c5) c4 FROM P1P5 WHERE c5 < 10 GROUP BY SUBSTR(cp5, 0, 4)),
P3P3 AS (SELECT SUBSTR(cp4, 0, 3) cp3, SUM(c4) c3 FROM P2P4 WHERE c4 < 10 GROUP BY SUBSTR(cp4, 0, 3))
SELECT ‘0’ AS “PRIORITY”, PREFIX, c0 AS “COUNT” FROM P0P0
UNION ALL
SELECT ‘1’, cp5 , c5 FROM P1P5 WHERE c5 >= 10
UNION ALL
SELECT ‘2’, cp4 , c4 FROM P2P4 WHERE c4 >= 10
UNION ALL
SELECT ‘3’, cp3 , c3 FROM P3P3 WHERE c3 >= 10
UNION ALL
SELECT ‘4’, ‘OTHER’ , SUM(c3) c2 FROM P3P3 WHERE c3 < 10)
ORDER BY PRIORITY;

— 测试的DEMO, 把条件改为大于2条;
INSERT INTO TBPOSTTEST VALUES(‘123400’);
INSERT INTO TBPOSTTEST VALUES(‘123500’);
INSERT INTO TBPOSTTEST VALUES(‘123450’);
INSERT INTO TBPOSTTEST VALUES(‘123451’);
INSERT INTO TBPOSTTEST VALUES(‘123452’);
INSERT INTO TBPOSTTEST VALUES(‘123453’);
INSERT INTO TBPOSTTEST VALUES(‘123454’);
INSERT INTO TBPOSTTEST VALUES(‘123444’);
INSERT INTO TBPOSTTEST VALUES(‘123555’);
INSERT INTO TBPOSTTEST VALUES(‘123566’);
INSERT INTO TBPOSTTEST VALUES(‘124444’);
COMMIT;

SELECT PREFIX, COUNT FROM (
WITH
P0P0 AS (SELECT ‘____00′ AS “PREFIX”, COUNT(*) c0 FROM TBPOSTTEST WHERE SUBSTR(postcode, LENGTH(postcode)-1) = ’00’),
P1P5 AS (SELECT SUBSTR(postcode, 0, 5) cp5, COUNT(*) c5 FROM TBPOSTTEST WHERE SUBSTR(postcode, LENGTH(postcode)-1) <> ’00’ GROUP BY SUBSTR(postcode, 0, 5)),
P2P4 AS (SELECT SUBSTR(cp5, 0, 4) cp4, SUM(c5) c4 FROM P1P5 WHERE c5 < 2 GROUP BY SUBSTR(cp5, 0, 4)),
P3P3 AS (SELECT SUBSTR(cp4, 0, 3) cp3, SUM(c4) c3 FROM P2P4 WHERE c4 < 2 GROUP BY SUBSTR(cp4, 0, 3))
SELECT ‘0’ AS “PRIORITY”, PREFIX, c0 AS “COUNT” FROM P0P0
UNION ALL
SELECT ‘1’, cp5 , c5 FROM P1P5 WHERE c5 >= 2
UNION ALL
SELECT ‘2’, cp4 , c4 FROM P2P4 WHERE c4 >= 2
UNION ALL
SELECT ‘3’, cp3 , c3 FROM P3P3 WHERE c3 >= 2
UNION ALL
SELECT ‘4’, ‘OTHER’ , SUM(c3) c2 FROM P3P3 WHERE c3 < 2)
ORDER BY PRIORITY;

— 测试结果;
PREFIX        COUNT
—————— ———-
____00               2
12345                 5
1235                   2
OTHER                2

创建带参数的视图

Posted on by

PROMPT CREATE OR REPLACE PACKAGE pkg_session

CREATE OR REPLACE PACKAGE pkg_session IS
TYPE parm_type IS TABLE OF varchar2(1000) index BY VARCHAR2(30);
PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN VARCHAR2);
PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN NUMBER);
PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN DATE);
FUNCTION get_val(p_idx IN VARCHAR2) RETURN VARCHAR2 ;
FUNCTION get_val_number(p_idx IN VARCHAR2) RETURN NUMBER;
FUNCTION get_val_date(p_idx IN VARCHAR2) RETURN DATE;
PRAGMA RESTRICT_REFERENCES(get_val,WNDS,WNPS);
PRAGMA RESTRICT_REFERENCES(get_val_number,WNDS,WNPS);
PRAGMA RESTRICT_REFERENCES(get_val_date,WNDS,WNPS);
END pkg_session;
/

CREATE OR REPLACE PACKAGE BODY pkg_session IS
sv_parameters parm_type;
PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN VARCHAR2)
IS
BEGIN
sv_parameters(TRIM(UPPER(p_idx))) := SUBSTR(p_value,1,1000);
RETURN;
END set_val;

PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN NUMBER)
IS
BEGIN
set_val(p_idx,TO_CHAR(p_value));
RETURN;
END set_val;

PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN DATE)
IS
BEGIN
set_val(p_idx,TO_CHAR(p_value,’YYYYMMDDHH24MISS’));
RETURN;
END set_val;

FUNCTION get_val(p_idx IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN sv_parameters(TRIM(UPPER(p_idx)));
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END get_val;

FUNCTION get_val_number(p_idx IN VARCHAR2) RETURN NUMBER
IS
BEGIN
RETURN TO_NUMBER(get_val(p_idx));
END get_val_number;

FUNCTION get_val_date(p_idx IN VARCHAR2) RETURN DATE
IS
BEGIN
RETURN TO_DATE(get_val(p_idx),’YYYYMMDDHH24MISS’);
END get_val_date;

END pkg_session;
/

CREATE OR REPLACE VIEW VW_TEST
AS SELECT * FROM t WHERE ROWNUM<=pkg_session.get_val_number('P_ROWNUM');

— 未设参数时没有数据
SELECT * FROM VW_TEST;

— 设置参数：

EXEC pkg_session.set_val('P_ROWNUM',10);

— 以下会返回10行：
SELECT * FROM VW_TEST;

–两个条件 并且当不设置值的时候不启用
CREATE OR REPLACE VIEW VW_TEST2
AS SELECT * FROM t WHERE ROWNUM exec pkg_session.set_val(‘P_NAME’,’UCJMH’);

PL/SQL procedure successfully completed.

SQL> select * from vw_test2;

USERNAME
——————————
UCJMH
这个时候就只会有一行数据了

在给定时间间隔，求间隔内出现的记录数

Posted on by

在itpub里看到一个很有意思的sql

一个表中记录了各个地点的报警开始时间，结束时间。

现在想统计，各个地点报警次数和时长，时长=结束时间-开始时间。

要求，同一个地点，开始时间间隔超过10分钟的，这个地点要在结果中再出现一次，10分钟之内的假如有多条，统计条数和报警时长，时长=10分钟内各条时长之和。

create table T_DEMO
(
id VARCHAR2(32) not null,
place_id VARCHAR2(32),
s_time DATE,
e_time DATE
)；

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘1’, ‘1’, to_date(’21-08-2014 01:24:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 01:24:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘2’, ‘1’, to_date(’21-08-2014 01:25:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 01:25:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘3’, ‘1’, to_date(’21-08-2014 01:35:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 01:35:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘4’, ‘1’, to_date(’21-08-2014 01:39:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 01:39:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘5’, ‘1’, to_date(’21-08-2014 02:05:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:05:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘6’, ‘1’, to_date(’21-08-2014 02:06:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:06:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘7’, ‘1’, to_date(’21-08-2014 02:12:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:12:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘8’, ‘1’, to_date(’21-08-2014 02:14:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:14:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘9’, ‘1’, to_date(’21-08-2014 02:50:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:50:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’10’, ‘1’, to_date(’21-08-2014 02:52:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:52:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’11’, ‘1’, to_date(’21-08-2014 02:57:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:57:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’12’, ‘1’, to_date(’25-08-2014 09:13:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 09:13:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’13’, ‘1’, to_date(’25-08-2014 09:18:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 09:18:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’14’, ‘1’, to_date(’25-08-2014 09:30:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 09:30:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’15’, ‘1’, to_date(’25-08-2014 09:37:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 09:37:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’16’, ‘1’, to_date(’25-08-2014 09:47:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 09:47:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’17’, ‘2’, to_date(’25-08-2014 10:09:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 10:09:01′, ‘dd-mm-yyyy hh24:mi:ss’));

WITHt2AS

(SELECT  a.place_id,

a.s_time,

a.e_time,

a.id,

floor((a.s_time – to_date(‘2000-01-01′,’YYYY-MM-DD’)) * 24 * 60 / 10) last_cha

FROM t_demo a)

SELECT

place_id 地点,

SUM(e_time – s_time) * 24 * 60 * 60 “时长:秒”,

min(s_time)  开始时间，

COUNT(*) 次数

FROM t2

GROUP BY place_id, last_cha;

导入导出工具04–Oralce数据库中表数据导出为TXT格式

Posted on by

Oracle数据库中表数据导出为TXT格式

  1. 使用SPOOL命令把结果输出到文本中;
  2. 使用编写的C语言的脚本;
——————————- 使用SPOOL命令把结果输出到文本中 ——————————-
— 1.编写脚本为/tmp/orauldr.sql;
set echo on            — 是否显示执行的命令内容
set feedback off       — 是否显示提示符,即多少条记录打一个点
set heading off        — 是否显示字段的名称
set verify off         — 是否显示替代变量被替代前后的语句
set trimspool off      — 去字段空格
set pagesize 1000      — 页面大小
set linesize 50        — linesize设定尽量根据需要来设定，大了生成的文件也大
define fil= ‘/tmp/exp.txt’
prompt *** Spooling to &fil
spool &fil
select col1 || ‘,’ || username || ‘,’ || ‘,’|| password from tbtest; — 格式自己拼接出来,比较灵活,导入也不容易出错
spool off;
— 2.在SQL*PLUS中执行;
SQL>@ /tmp/orauldr.sql;
——————————- 使用SPOOL命令把结果输出到文本中 ——————————-
——————————- 使用编写的C语言的脚本 ——————————-
第三方使用C语言写的工具蛮多的，可以联系sonne.k.wang@gmailcom交流;
——————————- 使用编写的C语言的脚本 ——————————-

导入导出工具03–可传输表空间

Posted on by
可传输表空间
  1. 用途:
    1. 用于不同平台的数据迁移;
    2. 用于不同版本数据库之间的迁移;
  2. 限制:
    1. 不支持XMLSchema类型的数据;
    2. 只是把表空间上的数据传输到目标,用户的对象(序列,视图,包,过程,触发器)需要手动创建
  3. 实验目标:创建测试表空间TBS_TT1,并传输到目标服务器;
  4. 检查是否满足表空间传输的条件:
    1. 查看可传输表空间支持数据库平台和当前数据库的版本;                                              
    2. 查看要传输的表空间集是否是自包含的:
      1. 如果某个表空间集引用了其他表空间的对象,则这个表空间不是自包含的,否则就是自包含;
      2. 只有自包含的表空间集才可以用传输表空间技术;
      3. 使用dbms_tts.transport_set_check来验证表空间是否是自包含的,要把需要表空间传输的表空间列表都写上,中间用逗号分割;
      4. TRANSPORT_SET_VIOLATIONS表是一个临时表,必须在执行dbms_tts.transport_set_check的session中查看,如果有记录返回,则表示表空间集不是自包含的;
      5. 检查的结果;                                                  
    3. 查看数据库的字符集;                                                                                                             
  5. 在源数据库导出表空间;
    1. 创建目录对象:CREATE DIRECTORY dir_tt AS ‘/u01/tt’;                     
    2. 把对应的表空间设置为只读模式:ALTER TABLESPACE tbs_tt1 READ ONLY;                                
    3. 使用数据泵导出,只是导出表空间的元信息:expdp \’sys/oracle as sysdba\’ directory=dir_tt transport_tablespaces=tbs_tt1 dumpfile=tt_tbs_tt1.dmp logfile=tt.log job_name=’job_tt’;                                                      
  6. 转换数据文件格式:
    1. 如果源数据库和目标数据库所在的平台不同需要进行此步骤,把表空间的数据转化为目标平台格式;
    2. rman提供了此功能,可以在源数据库进行也可以在目标数据库进行:
      1. 在源数据库转换:convert tablespace ts1,ts2… to platform <destination platform> format ‘/path/%U’;
      2. 在目标数据库转换:convert datafile df1,df2… from platform <source platform> format ‘/path/%U’;
  7. 把导出的表空间的元信息和表空间的数据文件传到目标数据库服务器,数据文件放到数据库的目录下,元数据放到目录对象下;
  8. 修改源数据库表空间为读写模式:ALTER TABLESPACE tbs_tt1 READ WRITE;            
  9. 在目标服务器导入表空间:
    1. 创建目录对象:CREATE DIRECTORY dir_tt AS ‘/u01/tt’;                      
    2. 在目标数据库上创建表空间上的用户和并授予权限,否则会报错;                         
    3. 如果之前进行了平台的转换操作的话,此时可能需要使用rman对数据文件进行重命名了;
    4. 如果源数据库和目标数据库的块大小不同的话,需要在目标数据库添加源数据库块的cache_size:ALTER SYSTEM SET db_4k_cache_size = 5M;
    5. 把数据导入到目标数据库中:impdp \’sys/oracle as sysdba\’ directory=dir_tt dumpfile=tt_tbs_tt1.dmp transport_datafiles=’/u01/app/oracle/oradata/ORCL/tbs_tt1.dbf’ logfile= tt.log job_name=’job_tt’;                                  
    6. 修改表空间为读写模式:ALTER TABLESPACE tbs_tt1 READ WRITE;(SELECT tablespace_name, status FROM dba_tablespaces WHERE tablespace_name = ‘TBS_TT1’;)           
    7. 修改用户的默认表空间:ALTER USER u1 DEFAULT TABLESPACE tbs_tt1;(SELECT username, default_tablespace FROM dba_users WHERE username = ‘U1’;)          
  10. 验证数据;
— 测试数据;
— 创建表空间;
CREATE TABLESPACE TBS_TT1 DATAFILE ‘/u01/app/oracle/oradata/ORCL/tbs_tt1.dbf’ SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
— 创建用户并授权;
CREATE USER u1 IDENTIFIED BY u1 DEFAULT TABLESPACE tbs_tt1;
GRANT CONNECT, RESOURCE TO u1;
— 创建表,序列,并插入测试数据;
CREATE TABLE u1.t1(ID INTEGER, c_lob CLOB) TABLESPACE tbs_tt1;
CREATE SEQUENCE u1.seq_t1;
INSERT INTO u1.t1(ID, c_lob) VALUES (u1.seq_t1.NEXTVAL, to_clob(‘传输表空间-表1中BLOB字段’));
COMMIT;
— 创建视图;
CREATE VIEW v_t1(ID, NAME) AS SELECT ID, to_char(c_lob) FROM u1.t1;
SELECT * FROM v_t1;
— 创建过程;
CREATE OR REPLACE PROCEDURE pr_t1 AS
BEGIN
NULL;
END;
— 查看可传输表空间支持数据库平台和当前数据库的版本;
SELECT d.NAME, i.host_name, i.version, d.platform_name, endian_format
FROM v$transportable_platform tp, v$database d, v$instance i
WHERE tp.platform_name = d.platform_name AND d.db_unique_name = i.instance_name;
— 查看表空间是否是自包含的;
EXECUTE dbms_tts.transport_set_check(‘TBS_TT1’, TRUE, TRUE);
SELECT * FROM transport_set_violations;
— 查看数据库的字符集;
SELECT max(decode(parameter,’NLS_LANGUAGE’, VALUE, NULL)) || ‘_’ ||
max(decode(parameter,’NLS_TERRITORY’, VALUE, NULL)) || ‘.’ ||
max(decode(parameter,’NLS_CHARACTERSET’, VALUE, NULL)) AS “NLS_LANG”
FROM nls_database_parameters;
— 数据文件平台转换的脚本;
RMAN> run{
allocate channel c1 device type disk connect ‘sys/oracle@orcl_source’;
convert tablespace example,users to platform ‘Linux IA (32-bit)’ Format ‘/backup/dmp/%U’;
release channel c1;}

由于bitmap造成的insert相互阻塞

Posted on by

CREATE TABLE t
(
NAME VARCHAR2(20)
);

CREATE BITMAP INDEX idx_b_name ON t(NAME);

INSERT INTO t VALUES(‘U’);
INSERT INTO t VALUES(‘C’);

select sid from v$mystat where rownum<2;  –198

select addr, sid, type, id1,id2, lmode,request,block from v$lock where sid=198 or sid=200;
1 000007FFBDC55D50 198 AE 100 0 4 0 0
2 000000001E2E9600 198 TM 77149 0 3 0 0
3 000007FFB9CE8488 198 TX 65561 1133 6 0 0
–这个时候登录第二个session
select sid from v$mystat where rownum<2;
200

INSERT INTO t VALUES(‘U’);
select addr, sid, type, id1,id2, lmode,request,block from v$lock where sid=198 or sid=200;

1 000007FFBDC55D50 198 AE 100 0 4 0 0
2 000007FFBDC56588 200 AE 100 0 4 0 0
3 0000000026980C10 198 TM 77149 0 3 0 0
4 000007FFB9CE8488 198 TX 196617 1336 6 0 0

这个时候在SESSION2中
INSERT INTO t VALUES(‘E’);

select addr, sid, type, id1,id2, lmode,request,block from v$lock where sid=198 or sid=200;

1 000007FFBDC55D50 198 AE 100 0 4 0 0
2 000007FFBDC56588 200 AE 100 0 4 0 0
3 000000002697E370 200 TM 77149 0 3 0 0
4 000000002697E370 198 TM 77149 0 3 0 0
5 000007FFB9CC9678 200 TX 65568 1131 6 0 0
6 000007FFB9CE8488 198 TX 196617 1336 6 0 0
SEssion不阻塞

这个时候继续在session2 做插入

INSERT INTO t VALUES(‘U’);
1 000007FFBDC55D50 198 AE 100 0 4 0 0
2 000007FFBDC56588 200 AE 100 0 4 0 0
3 000007FFBDC56658 200 TX 196617 1336 0 4 0
4 0000000026980C10 200 TM 77149 0 3 0 0
5 0000000026980C10 198 TM 77149 0 3 0 0
6 000007FFB9CC9678 200 TX 65568 1131 6 0 0
7 000007FFB9CE8488 198 TX 196617 1336 6 0 1

这个时候Insert发生了阻塞 session2在等seesion1释放资源

在这个时候发生一个insert 阻碍了 INSERT 的操作  造成它的原因就是 BITMAP
SELECT OBJECT_ID FROM User_Objects WHERE object_name=’IDX_B_NAME’ ;   –77150

alter session set events ‘immediate trace name treedump level INDEX_OBJECT_ID’;

alter session set events ‘immediate trace name treedump level 52563’

Bitmap深入研究(一)

Posted on by

环境：
ORACLE:
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Prod
PL/SQL Release 10.2.0.2.0 – Production
CORE    10.2.0.2.0      Production
TNS for Linux: Version 10.2.0.2.0 – Production
NLSRTL Version 10.2.0.2.0 – Production

OS：

LSB Version:    :core-3.1-ia32:core-3.1-noarch:graphics-3.1-ia32:graphics-3.1-noarch
Distributor ID: EnterpriseEnterpriseServer
Description:    Enterprise Linux Enterprise Linux Server release 5.4 (Carthage)
Release:        5.4
Codename:       Carthage
研究Bitmap索引的存储和特性

SQL> conn scott/scott
Connected.
SQL> CREATE TABLE t –创建一张测试表
2  (
3  NAME VARCHAR2(20)
4  );
Table created.

SQL> CREATE BITMAP INDEX idx_b_name ON t(NAME); –创建一个测试的索引

Index created.
SQL> select sid from v$mystat where rownum<2;  –查看自己的sid

SID
———-
159

–查看trace的文件所在
select a.value||b.symbol||c.instance_name||’_ora_’||d.spid||’.trc’ trace_file from
(select value from v$parameter where name=’user_dump_dest’) a,
(select substr(value,-6,1) symbol from v$parameter where name=’user_dump_dest’) b,
(select instance_name from v$instance) c,
(select spid from v$session s,v$process p,v$mystat m where s.paddr=p.addr
and s.sid=m.sid and m.statistic#=0) d;

TRACE_FILE
——————————————————————————–
/u01/app/oracle/admin/orcl/udump/orcl_ora_9707.trc
查看文件内容

*** ACTION NAME:() 2015-03-03 08:25:59.728
*** MODULE NAME:(SQL*Plus) 2015-03-03 08:25:59.728
*** SERVICE NAME:(SYS$USERS) 2015-03-03 08:25:59.728
*** SESSION ID:(159.7) 2015-03-03 08:25:59.728
—– begin tree dump   这个是空的时候
leaf: 0x100018c 16777612 (0: nrow: 0 rrow: 0)
—– end tree dump
*** 2015-03-03 08:28:08.940
—– begin tree dump   insert into t values(‘U’); commit;
leaf: 0x100018c 16777612 (0: nrow: 1 rrow: 1)
—– end tree dump
*** 2015-03-03 08:29:20.359
—– begin tree dump insert into t values(‘c’); commit;
leaf: 0x100018c 16777612 (0: nrow: 2 rrow: 2)
—– end tree dump
*** 2015-03-03 08:30:09.053
—– begin tree dump 又insert into t values(‘U’); commit;
leaf: 0x100018c 16777612 (0: nrow: 2 rrow: 2)
—– end tree dump
*** 2015-03-03 08:38:07.763
—– begin tree dump  delete from t where name=’c’; commit;
leaf: 0x100018c 16777612 (0: nrow: 2 rrow: 1)
—– end tree dump
*** 2015-03-03 08:40:35.384
—– begin tree dump  –insert into t select OBJECT_TYPE from user_objects;
leaf: 0x100018c 16777612 (0: nrow: 3 rrow: 3)
—– end tree dump

—– begin tree dump   insert into t SELECT object_type FROM dba_objects; / commit;
branch: 0x100018c 16777612 (0: nrow: 6, level: 1)
leaf: 0x1000190 16777616 (-1: nrow: 11 rrow: 11)
leaf: 0x100018f 16777615 (0: nrow: 2 rrow: 2)
leaf: 0x100018d 16777613 (1: nrow: 17 rrow: 17)
leaf: 0x100028c 16777868 (2: nrow: 3 rrow: 3)
leaf: 0x100018e 16777614 (3: nrow: 2 rrow: 2)
leaf: 0x100028d 16777869 (4: nrow: 11 rrow: 11)
—– end tree dump 这个时候可以发现整个树的结构都发生了变化 不再是单纯只有一个叶子节点了

仔细观察最后一次dump出来的结构 我们可以得出以下结论
1.原来的叶子节点发生了分裂而不是迁移。（b-tree也是一样） 根节点永远不会变
2.叶子节点从-1开始的顺序不代表在数据文件上也是连续的块

观察倒数第二个的nrow=3 我们去验证是否是这样
SQL> select DISTINCT name from t;

NAME
——————–
U
INDEX
TABLE

第一列中的
1.branch表示分支节点（包括根节点），
2.leaf则表示叶子节点
第二列表示十六进制表示的节点的地址；
第三列表示十进制表示的节点的地址；
第四列表示相对于前一个节点的位置，根节点从0开始计算，其他分支节点和叶子节点从-1开始计算；
第五列的nrow表示当前节点中所含有的索引条目的数量。比如我们可以看到含有的nrow为2，表示节点中含有2个索引条目，
分别指向2个分支节点
第六列中的
1.rrow表示有效的索引条目（因为索引条目如果被删除，不会立即被清除出索引块中。
所以nrow减rrow的数量就表示已经被删除的索引条目数量）的数量，
2.level表示分支节点的层级，对于叶子节点来说level都是0

以种方式以树状形式转储整个索引。同时，我们可以转储一个索引节点来看看其中存放了些什么

从上面我们可以看到索引块（根）的地址为：16777612 .我们先将其转换为文件号以及数据块号

select dbms_utility.data_block_address_file(16777615) “file”,dbms_utility.data_block_address_block(16777615) “BLOCK” from dual;

FILE  BLOCK
—————
4     396

— 注： 反推的话可以使用  select dbms_utility.make_data_block_address(4,396) from dual;
我们首先看一下根节点 然后再看一下叶子节点

SQL> alter system dump datafile 4 block 396;

System altered.
–内容如下

*** 2015-03-03 09:03:48.908
Start dump data blocks tsn: 4 file#: 4 minblk 396 maxblk 396
buffer tsn: 4 rdba: 0x0100018c (4/396)
scn: 0x0000.000739b7 seq: 0x02 flg: 0x04 tail: 0x39b70602
frmt: 0x02 chkval: 0x3905 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0DECE400 to 0x0DED0400
DECE400 0000A206 0100018C 000739B7 04020000  [………9……]
DECE410 00003905 00000002 0000CD53 000739B7  [.9……S….9..]
DECE420 00000000 00320001 01000189 002C0006  [……2…….,.]
DECE430 00000102 00800B8F 00020136 00008000  [……..6…….]
DECE440 000739B7 00000000 00000000 04800001  [.9…………..]
DECE450 00000001 00260005 1F171F3D 01000190  [……&.=…….]
DECE460 00000004 00001F78 1F6C1F4B 1F5D1F44  [….x…K.l.D.].]
。。。
。。。
省略部分内容
。。。
。。。
Block header dump:  0x0100018c
Object id on Block? Y
seg/obj: 0xcd53  csc: 0x00.739b7  itc: 1  flg: E  typ: 2 – INDEX
brn: 0  bdba: 0x1000189 ver: 0x01 opc: 0
inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0006.02c.00000102  0x00800b8f.0136.02  C—    0  scn 0x0000.000739b7

Branch block dump
=================
header address 233628748=0xdece44c
kdxcolev 1
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 4
kdxcosdc 1
kdxconro 5
kdxcofbo 38=0x26
kdxcofeo 7997=0x1f3d
kdxcoavs 7959
kdxbrlmc 16777616=0x1000190
kdxbrsno 4
kdxbrbksz 8056
kdxbr2urrc 0
row#0[8011] dba: 16777615=0x100018f
col 0; len 10; (10):  4a 41 56 41 20 43 4c 41 53 53
col 1; len 1; (1):  01
col 2; TERM
row#1[8044] dba: 16777613=0x100018d
col 0; len 6; (6):  4a 41 56 41 20 44
col 1; TERM
row#2[8004] dba: 16777868=0x100028c
col 0; len 1; (1):  53
col 1; TERM
row#3[8029] dba: 16777614=0x100018e
col 0; len 7; (7):  53 59 4e 4f 4e 59 4d
col 1; len 1; (1):  01
col 2; TERM
row#4[7997] dba: 16777869=0x100028d
col 0; len 1; (1):  54
col 1; TERM
—– end of branch block dump —–
End dump data blocks tsn: 4 file#: 4 minblk 396 maxblk 396

–kdxcolev表示索引层级号，这里由于我们转储的是根节点所以其层级号为1（当非叶子节点时层级也有可能为2 3 4递增）对叶子节点来说该值为0
–kdxcolok表示该索引上是否正在发生修改块结构的事务；
–kdxcoopc表示内部操作代码；
–kdxconco表示索引条目中列的数量；
–kdxcosdc表示索引结构发生变化的数量，当你修改表里的某个索引键值时，该值增加；
–kdxconro表示当前索引节点中索引条目的数量，但是注意，不包括kdxbrlmc指针；
–kdxcofbo表示当前索引节点中可用空间的起始点相对当前块的位移量；
–kdxcofeo表示当前索引节点中可用空间的最尾端的相对当前块的位移量；
–kdxcoavs表示当前索引块中的可用空间总量，也就是用kdxcofeo减去kdxcofbo得到的。
–kdxbrlmc表示分支节点的地址，该分支节点存放了索引键值小于row#0（在转储文档后半部分显示）所含有的最小值的所有节点信息；
–kdxbrsno表示最后一个被修改的索引条目号，这里看到是0，表示该索引是新建的索引；
–kdxbrbksz表示可用数据块的空间大小。实际从这里已经可以看到，即便是PCTFREE设置为0，也不能用足8192字节。
接下去看到的是row#0的内容
这部分内容就是在根节点中所记录的索引条目，总共是5个[0-4]条目。再加上kdxbrlmc所指向的第一个分支节点，
我们知道该根节点中总共存放了6个分支节点的索引条目 也对应了树形结构里nrow的值

每个索引条目都指向一个分支节点。
其中col 1表示所链接的分支节点的地址，该值经过一定的转换以后实际就是row#所在行的dba的值。
如果根节点下没有其他的分支节点，则col 1为TERM 比如上面的dba: 16777613=0x100018d的col 1; TERM

col 0表示该分支节点所链接的最小键值。其转换方式非常复杂 是要从十六进制转换为十进制
我们可以做下试验

row#0[8011] dba: 16777615=0x100018f
col 0; len 10; (10):  4a 41 56 41 20 43 4c 41 53 53
SELECT to_number(‘4a’,’XX’) FROM dual; –74
SELECT to_number(’41’,’XX’) FROM dual;–65
SELECT to_number(’56’,’XX’) FROM dual;–86
SELECT to_number(’41’,’XX’) FROM dual;–65
SELECT to_number(’20’,’XX’) FROM dual;–32
SELECT to_number(’43’,’XX’) FROM dual;–67
SELECT to_number(‘4c’,’XX’) FROM dual;–76
SELECT to_number(’41’,’XX’) FROM dual;–65
SELECT to_number(’53’,’XX’) FROM dual;–83
SELECT to_number(’53’,’XX’) FROM dual;–83
索引键值是char类型的，所以对每个值都运用chr函数就可以得到被索引键值为:
SELECT CHR(74) FROM dual;  –J
SELECT CHR(65) FROM dual;  –A
SELECT CHR(86) FROM dual;  –V
SELECT CHR(65) FROM dual;  –A
SELECT CHR(32) FROM dual;  — (空格)
SELECT CHR(67) FROM dual;  –C
SELECT CHR(76) FROM dual;  –L
SELECT CHR(65) FROM dual;  –A
SELECT CHR(83) FROM dual;  –S
SELECT CHR(83) FROM dual;  –S
–我擦 竟然是JAVA CLASS ， JAVA CLASS就是dba为16777615的索引块所链接的最小键值

然后再dump一下看到结果就是我们要的10进制还没转成16进制的值
select dump(‘JAVA CLASS’) from dual;
Typ=96 Len=10: 74,65,86,65,32,67,76,65,83,83

那么我们上面dump出来了根节点 接下来继续dump叶子节点  我们就dump刚刚的16777615这个块吧。

select dbms_utility.data_block_address_file(16777615) “file”,dbms_utility.data_block_address_block(16777615) “BLOCK” from dual;
file      BLOCK
———- ———-
4        399

alter system dump datafile 4 block 399;
文件如下：
*** 2015-03-03 10:26:29.354
Start dump data blocks tsn: 4 file#: 4 minblk 399 maxblk 399
buffer tsn: 4 rdba: 0x0100018f (4/399)
scn: 0x0000.00073fd7 seq: 0x01 flg: 0x04 tail: 0x3fd70601
frmt: 0x02 chkval: 0x0703 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0DECE400 to 0x0DED0400
DECE400 0000A206 0100018F 00073FD7 04010000  [………?……]
DECE410 00000703 00000002 0000CD53 00073FD7  [……..S….?..]
DECE420 00000000 00320002 01000189 00080004  [……2………]
…..
Block header dump:  0x0100018f
Object id on Block? Y
seg/obj: 0xcd53  csc: 0x00.73fd7  itc: 2  flg: E  typ: 2 – INDEX
brn: 0  bdba: 0x1000189 ver: 0x01 opc: 0
inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0004.008.000000d0  0x00800041.009e.01  CB–    0  scn 0x0000.000739b2
0x02   0x0001.009.000000db  0x008002da.00be.09  C—    0  scn 0x0000.000739ba

Leaf block dump
===============
header address 233628772=0xdece464
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 4
kdxcosdc 1
kdxconro 2
kdxcofbo 40=0x28
kdxcofeo 2853=0xb25
kdxcoavs 3233
kdxlespl 0
kdxlende 0
kdxlenxt 16777613=0x100018d
kdxleprv 16777616=0x1000190
kdxledsz 0
kdxlebksz 8032
row#0[3667] flag: ——, lock: 0, len=3945
col 0; len 10; (10):  4a 41 56 41 20 43 4c 41 53 53
col 1; len 6; (6):  01 00 01 db 00 b0
….
row#1[2853] flag: ——, lock: 0, len=814
col 0; len 10; (10):  4a 41 56 41 20 43 4c 41 53 53
col 1; len 6; (6):  01 00 02 3e 01 00
col 2; len 6; (6):  01 00 02 80 01 4f
col 3; len 785; (785):
cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55
55 55 ce 55 55 55 55 55 55 05 ff 18 55 55 55 55 55 55 55 55 cf 55 55 55 55
55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55
55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 ce
55 55 55 55 55 55 05 ff 18 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55
55 cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55
55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 ce 55 55 55
55 55 55 05 ff 18 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55
55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55
c9 55 05 ff 2d 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55
55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf
55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 ce 55 55 55 55 55 55 05
ff 18 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55
55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55 55
55 55 55 55 55 cf 55 55 55 55 55 55 55 55 ce 55 55 55 55 55 55 05 ff 18 55
—– end of leaf block dump —–
End dump data blocks tsn: 4 file#: 4 minblk 399 maxblk 399

发现内容与根节点完全类似，只不过该索引块中所包含的索引条目（指向叶子节点）的数量只有两个

我们再拿其中一个叶子来分析一下
row#1[2853] flag: ——, lock: 0, len=814
col 0; len 10; (10):  4a 41 56 41 20 43 4c 41 53 53
col 1; len 6; (6):  01 00 02 3e 01 00
col 2; len 6; (6):  01 00 02 80 01 4f
col 3; len 785; (785):
cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55 55 55 cf 55 55 55 55 55 55

其中flag表示标记，比如删除标记等；
而lock表示锁定信息。col 0表示索引键值，其算法与我们在前面介绍分支节点时所说的算法一致。

继续转化进制后使用chr
4a 41 56 41 20 43 4c 41 53 53
74 65 86 65 32 67 76 65 83 83
J  A  V  A     C  L  A  S  S

col 1
在b-tree的时候表示rowid
在bitmap的时候表示rowid的起始位置
01 00 01 db 00 b0
010001db00b0

SELECT to_number(‘010001db00b0′,’xxxxxxxxxxxx’) FROM dual;
select dbms_utility.data_block_address_file(to_number(‘010001DB’,’xxxxxxxxxxxx’)) “file”,
dbms_utility.data_block_address_block(to_number(‘010001DB’,’xxxxxxxxxxxx’)) “BLOCK” from dual;

4        475
col 2
在b-tree的时候没有
在bitmap中表示rowid的结束位置
01000280014f

col3表示位图编码 在b-tree里一样也没有
select to_number(‘0180001c’,’xxxxxxxxxxxx’) from dual;

中间的ca和开头的cf，不是索引的bitmap，而是类似数据头之类的东西，具体数值代表什么我还不清楚，不过似乎每64位就会出现一个数据头
SELECT
sys.pkg_number_trans.f_hex_to_bin(’55’) as c2,
from dual;

1010101

其中不足8位的前面用0补齐
–连接起来全是二进制的数据  也就代表了是不是JAVA CLASS

SELECT SYS.pkg_number_trans.f_oct_to_hex(‘01000280014f’) FROM dual;

在数据很少的时候单一块的时候很容易验证 但是数据多了之后怎么在块与块之间进行

我们来做一个单一的吧
DROP TABLE t1 PURGE;
CREATE TABLE t1
(
ID NUMBER
);
INSERT INTO t1 VALUES(1);
INSERT INTO t1 VALUES(2);
INSERT INTO t1 VALUES(3);
INSERT INTO t1 VALUES(4);
INSERT INTO t1 VALUES(3);
INSERT INTO t1 VALUES(2);
INSERT INTO t1 VALUES(1);
COMMIT;
select dbms_rowid.rowid_relative_fno(rowid)file_id, dbms_rowid.rowid_block_number(rowid)block_id,
dbms_rowid.rowid_row_number(rowid) row# from t1;

FILE_ID   BLOCK_ID       ROW#
———- ———- ———-
4        664          0
4        664          1
4        664          2
4        664          3
4        664          4
4        664          5
4        664          6

在同一个数据块上
alter session set events ‘10608 trace name context forever, level 10608’;

CREATE BITMAP INDEX IDX_B_T1 ON T1(ID);

alter session set events ‘10608 trace name context off’;

10608事件用来跟踪创建bitmap索引的过程

内容如下：
qerbiARwo: bitmap size is 8168
qerbiIPI default pctfree=10
qerbiIPI length=0
qerbiAllocate pfree=127 space=8168
qerbiStart first start
qerbiRop: rid=01000298.0000, new=Y , key: (2):  c1 02   –表示这是一个新值 产生一个的新的位图表示
qerbiCmpSz notfound pctfree=10
qerbiCmpSz adjblksize=7351 length=0
qerbiRop keysize=4 maxbm=3529
kdibcoinit(b7ddb044): srid=01000298.0000
qerbiRop: rid=01000298.0001, new=Y , key: (2):  c1 03
kdibcoinit(b7ddafc8): srid=01000298.0001
qerbiRop: rid=01000298.0002, new=Y , key: (2):  c1 04
kdibcoinit(b7ddaf4c): srid=01000298.0002
qerbiRop: rid=01000298.0003, new=Y , key: (2):  c1 05
kdibcoinit(b7ddaed0): srid=01000298.0003
qerbiRop: rid=01000298.0004, new=N, key: (2):  c1 04  –不是新值
qerbiRop: rid=01000298.0005, new=N, key: (2):  c1 03
qerbiRop: rid=01000298.0006, new=N, key: (2):  c1 02
kdibcoend(b7ddb044): erid=01000298.0007status=3
qerbiCon: key: (2):  c1 02
srid=01000298.0 erid=01000298.7 bitmap: (2):  c8 41
kdibcoend(b7ddafc8): erid=01000298.0007status=3
qerbiCon: key: (2):  c1 03
srid=01000298.0 erid=01000298.7 bitmap: (2):  c8 22
kdibcoend(b7ddaf4c): erid=01000298.0007status=3
qerbiCon: key: (2):  c1 04
srid=01000298.0 erid=01000298.7 bitmap: (2):  c8 14
kdibcoend(b7ddaed0): erid=01000298.0007status=3
qerbiCon: key: (2):  c1 05
srid=01000298.0 erid=01000298.7 bitmap: (1):  03
qerbiFreeMemory: Work heap is used.

上面是创建bitmap索引的过程。我们先把被索引的列的值换算成十六进制：

select dump(4),dump(3),dump(2),dump(1) from dual;
Typ=96 Len=2: 85,99 Typ=96 Len=3: 74,109,104
1 Typ=2 Len=2: 193,5 Typ=2 Len=2: 193,4 Typ=2 Len=2: 193,3 Typ=2 Len=2: 193,2
5 4 3 2的16进制是05 04 03 02 同时对应了上面的生成key部分的值
qerbiCon: key: (2):  c1 02
srid=01000298.0 erid=01000298.7 bitmap: (2):  c8 41    从上面的推论我们得出02表示1
start rowid和end rowid的格式分两部分，中间用点隔开，
点左边的表示文件号（从左边第一个字节开始的4个字节表示）和数据块号（从左边第五个字节开始的4个字节表示），
点右边表示数据块里的行号。这里的显示可以看到，
这几条记录都位于相同的数据块里。这里的0100表示文件号
select dbms_utility.data_block_address_file(to_number(‘01000298′,’xxxxxxxxxxxx’)) “file”,
dbms_utility.data_block_address_block(to_number(‘01000298′,’xxxxxxxxxxxx’)) “BLOCK” from dual;

4 664
根据我们刚刚的插入按照预想的应该是：1000001

得到结果果然是：
SELECT
sys.pkg_number_trans.f_hex_to_bin(’41’) as c2
from dual;
1000001

Oracle SPA介绍

Posted on by

Sql Performance Analyzer (SPA)
这是oracle 11g的新特性 可对一部分sql结果进行性能分析 适用于数据升级 迁移做前后的比较
分析的结果会永远存在的数据库内部。和数据库重演不同的是spa只能分析sql语句且只有查询语句
步骤如下：
1.在生产库捕获具体语句
2.把捕获的语句打包传输到测试环境
3.创建SPA任务在测试环境
4.生成改变之前的执行信息
5.做改变（升级或者打补丁或者改参数或者等等。。）
6.生成改变之后的执行信息
7.对于两份信息（可生成报告文件）

CREATE USER ucjmh IDENTIFIED BY ucjmh;
GRANT DBA TO ucjmh;
conn ucjmh/ucjmh –创建一个用户
–建几张表
CREATE TABLE uc_objects AS SELECT * FROM dba_objects;
CREATE TABLE uc_tables AS SELECT * FROM dba_tables;
CREATE TABLE uc_users AS SELECT * FROM dba_users;
–做一些查询
select /*UCJMH*/ * from uc_objects;
select /*UCJMH*/ * from uc_tables;
select /*UCJMH*/ * from uc_users;
–生成一个sqlset
begin
sys.dbms_sqltune.create_sqlset(
sqlset_name => ‘uc_sts’,
sqlset_owner => ‘UCJMH’);
end;
/

PL/SQL procedure successfully completed.

–查看一下是否生成成功
SQL> select name from user_sqlset;

NAME
——————————
uc_sts

–把需要的sql放到这个sqlset里
declare
stscur dbms_sqltune.sqlset_cursor;
begin
open stscur for
select value(P) from table(
dbms_sqltune.select_cursor_cache(
‘sql_text like ”select /*UCJMH*/%”’,
null,null,null,null,null,null,’ALL’)) P;
dbms_sqltune.load_sqlset(
sqlset_name =>’uc_sts’, –这里是区分大小写的 如果你上面写的小写 不要想当然认为ORACLE会内部转换成大写
populate_cursor => stscur,
sqlset_owner =>’UCJMH’);
end;

PL/SQL procedure successfully completed

SQL> select sql_text from user_sqlset_statements where sqlset_name=’uc_sts’;

SQL_TEXT
——————————————————————————–
select /*UCJMH*/ * from uc_objects
select /*UCJMH*/ * from uc_users
select /*UCJMH*/ * from uc_tables

–或者
SQL> select sql_Text from table(dbms_sqltune.select_sqlset(‘uc_sts’));

SQL_TEXT
——————————————————————————–
select /*UCJMH*/ * from uc_objects
select /*UCJMH*/ * from uc_users
select /*UCJMH*/ * from uc_tables

创建暂存表保存STS：
exec DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(‘UC_STS_TAB’,’UCJMH’);

SQL> select count(*) from uc_sts_tab;

COUNT(*)
———-
0
–上面的步骤只是创建了一个用来暂存set的表 但是并没有真的已经把sql放进去
–把STS加载到暂存表里：
exec DBMS_SQLTUNE.PACK_STGTAB_SQLSET(‘uc_sts’,’UCJMH’,’UC_STS_TAB’,’UCJMH’);

PL/SQL procedure successfully completed.

SQL> select count(*) from uc_sts_tab;

COUNT(*)
———-
6

–不是应该是3条sql吗 问什么是6行记录呢？仔细看看可以发现每个sql_id占两行 一行是sql 一行是统计信息和执行记划等信息

把暂存表导出生产环境 导入测试环境（我这里测试环境没有东西 我直接把整个用户导过去 如果你们其它表已经存在了可以只导一张暂存表）

create or replace directory dmp as ‘/home/oracle/dmp’;
EXIT
expdp ucjmh/ucjmh directory=dmp dumpfile=uc.dmp
export ORACLE_SID=emrep
sqlplus / AS SYSDBA
create or replace directory dmp as ‘/home/oracle/dmp’;
EXIT
impdp system/oracle directory=dmp dumpfile=uc.dmp

把STS从暂存表里放入数据字典里：
exec DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(‘uc_sts’,’UCJMH’,TRUE,’UC_STS_TAB’,’UCJMH’); –true表示如果有的话是否替换

PL/SQL procedure successfully completed.

–创建一个spa任务
SQL> var tname varchar2(20);
SQL> exec :tname:= dbms_sqlpa.create_analysis_task(sqlset_name => ‘uc_sts’, task_name => ‘MYSPA’);

PL/SQL procedure successfully completed.

–执行任务从而构建before change data
SQL> exec dbms_sqlpa.execute_analysis_task(task_name => :tname, execution_type => ‘TEST EXECUTE’, execution_name => ‘before’);

PL/SQL procedure successfully completed.

–生成before change的报表
SQL> set long 99999
SQL> SELECT dbms_sqlpa.report_analysis_task(task_name => :tname, type=>’text’, section=>’summary’) FROM dual;

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : MYSPA
Tuning Task Owner : UCJMH
Workload Type : SQL Tuning Set
Execution Count : 1
Current Execution : before
Execution Type : TEST EXECUTE
Scope : COMPREHENSIVE
Completion Status : COMPLETED
Started at : 11/01/2014 19:27:01

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
Completed at : 11/01/2014 19:27:01
SQL Tuning Set (STS) Name : uc_sts
SQL Tuning Set Owner : UCJMH
Number of Statements in the STS : 3
Number of SQLs Analyzed : 3
Number of SQLs in the Report : 3

——————————————————————————-
SUMMARY SECTION
——————————————————————————-
SQL Statements Ordered by Elapsed Time

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
——————————————————————————-
Parse Elapsed CPU Buffer Optimizer
object ID SQL ID Time (s) Time (s) Time (s) Gets Cost
———- ————- ——– ——– ——– ——– ———
3 5d0v1n7gt8j2y .014787 .016058 .015664 1013 284
5 g9shuu1cxbqqz .006716 .001369 .001333 76 23
4 71tf6tnk513p5 .002394 .000084 .000111 3 3

——————————————————————————-

–做你要做的变化，比如修改参数、升级硬件、升级操作系统等等。
SQL> exec dbms_stats.gather_schema_stats(USER);

PL/SQL procedure successfully completed.
构建after change data
SQL> EXEC dbms_sqlpa.execute_analysis_task(task_name => :tname, execution_type => ‘TEST EXECUTE’, execution_name => ‘after’);

PL/SQL procedure successfully completed.

生成after change的报表
SQL> SELECT dbms_sqlpa.report_analysis_task(task_name => :tname,type=>’text’, section=>’summary’) FROM dual;

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : MYSPA
Tuning Task Owner : UCJMH
Workload Type : SQL Tuning Set
Execution Count : 2
Current Execution : after
Execution Type : TEST EXECUTE
Scope : COMPREHENSIVE
Completion Status : COMPLETED
Started at : 11/01/2014 19:30:29

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
Completed at : 11/01/2014 19:30:29
SQL Tuning Set (STS) Name : uc_sts
SQL Tuning Set Owner : UCJMH
Number of Statements in the STS : 3
Number of SQLs Analyzed : 3
Number of SQLs in the Report : 3

——————————————————————————-
SUMMARY SECTION
——————————————————————————-
SQL Statements Ordered by Elapsed Time

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
——————————————————————————-
Parse Elapsed CPU Buffer Optimizer
object ID SQL ID Time (s) Time (s) Time (s) Gets Cost
———- ————- ——– ——– ——– ——– ———
7 5d0v1n7gt8j2y .000147 .017201 .016886 1012 284
9 g9shuu1cxbqqz .000244 .001269 .001444 75 23
8 71tf6tnk513p5 .000423 .000064 0 2 3

——————————————————————————-

–比较before和after
EXEC dbms_sqlpa.execute_analysis_task(task_name => :tname, execution_type => ‘COMPARE PERFORMANCE’);

–生成分析报表
SQL> SELECT dbms_sqlpa.report_analysis_task(task_name => :tname, type=>’text’, section=>’summary’) FROM dual;

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : MYSPA
Tuning Task Owner : UCJMH
Workload Type : SQL Tuning Set
Execution Count : 2
Current Execution : after
Execution Type : TEST EXECUTE
Scope : COMPREHENSIVE
Completion Status : COMPLETED
Started at : 11/01/2014 19:30:29

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
Completed at : 11/01/2014 19:30:29
SQL Tuning Set (STS) Name : uc_sts
SQL Tuning Set Owner : UCJMH
Number of Statements in the STS : 3
Number of SQLs Analyzed : 3
Number of SQLs in the Report : 3

——————————————————————————-
SUMMARY SECTION
——————————————————————————-
SQL Statements Ordered by Elapsed Time

DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME=>:TNAME,TYPE=>’TEXT’,SECTION=>’SUMMARY’
——————————————————————————–
——————————————————————————-
Parse Elapsed CPU Buffer Optimizer
object ID SQL ID Time (s) Time (s) Time (s) Gets Cost
———- ————- ——– ——– ——– ——– ———
7 5d0v1n7gt8j2y .000147 .017201 .016886 1012 284
9 g9shuu1cxbqqz .000244 .001269 .001444 75 23
8 71tf6tnk513p5 .000423 .000064 0 2 3

——————————————————————————-

删除任务：
select * from DBA_SQLSET_REFERENCES where SQLSET_NAME=’uc_sts’;
exec dbms_sqltune.drop_tuning_task(‘MYSPA’);

删除暂存表和STS：
drop table hr.hsj_sts_tab purge;
exec dbms_sqltune.drop_sqlset(‘uc_sts’);

Oracle UTL_FILE

Posted on by

With the UTL_FILE package, PL/SQL programs can read and write operating system text files. UTL_FILE provides a restricted version of operating system stream file I/O.
FOPEN
描述：打开一个文件，基本上在我们对文件进行读写动作之前都需要先执行这个function来打开文件先。
语法：
UTL_FILE.FOPEN (  location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER DEFAULT 1024)  RETURN FILE_TYPE;
参数：location   略。
Filename  略。
open_mode  指明文件打开的模式。有如下几种：
■r –只读（文本）
■ w – 只写（本文）
■ a – 追加（文本）
■ rb – 只读（字节）
■ wb – 只写（字节）
■ ab – 追加（字节）
(注：当使用模式：a或者ab的时候，如果文件不存在，则会以write模式创建此文件)
max_linesize   指定文件文本每一行存放的最大字符数。
返回值：FOPEN返回一个接下来我们的程序将要使用到的文件的指针

FCLOSE
功能：关闭一个打开的文件。
语法：UTL_FILE.FCLOSE (file IN OUT FILE_TYPE);
参数：1. file->调用FOPEN或者FOPEN_NVCHAR返回的活动中的文件指针。
注意事项：当FCLOSE执行的时候，如果还有缓冲数据没有及时写入到文件中，
那么程序就会raise一个异常：WRITE_ERROR。可以在PUT_LINE的时候加上参数autoflush => TRUE；或者在每次PUT之后执行：FFLUSH。

FCLOSE_ALL
功能：此procedure将会关闭本次session所有打开的文件。它用来紧急情况的清理功能，例如当PL/SQL程序在EXCEPTION部分退出时。
语法：UTL_FILE.FCLOSE_ALL;
注意事项：FCLOSE_ALL不会修改所打开的文件的状态，也就是说执行了FCLOSE_ALL后，再用IS_OPEN去检测文件，结果还是打开状态，
但是之后，这些文件任然是不能去read或者write的。而FCLOSE执行后，相关的文件则完全关闭了

FCOPY
功能：此procedure复制一个文件的连续部分内容或者全部内容到一个新创建的文件。
如果参数start_line和end_line省略的话，默认地会复制整个文件。此操作会将源文件以read模式打开，将目标文件以write模式打开。
语法：
UTL_FILE.FCOPY ( src_location    IN VARCHAR2,
src_filename  IN VARCHAR2,
dest_location  IN VARCHAR2,
dest_filename IN VARCHAR2,
start_line         IN BINARY_INTEGER DEFAULT 1,
end_line          IN BINARY_INTEGER DEFAULT NULL);
参数：src_location来源文件的目录名。取值来源是视图ALL_DIRECTORIES的DIRECTORY_NAME；
src_filename  将要被复制的来源文件
dest_location 被创建的目标文件存放的目录名。
dest_filename 从来源文件创建的目标文件。
start_line  要复制的内容起始行号，默认为1，表示从第一行开始复制。
end_line 要复制的内容的终止行号，默认NULL，表示文件的末尾。
FFLUSH
描述：FFLUSH强制将缓冲的数据写入文件。因为通常待写入文件的数据都是都在缓冲存储位置。当有必要去read一个任然处于打开状态的文件时，
FFLUSH就起作用了，例如在调试程序中，可以将调试的消息及时冲到文件中，已便于我们马上就能read这些内容。
语法：
UTL_FILE.FFLUSH (file IN FILE_TYPE);

FGETATTR
描述：FGETATTR读取磁盘上的文件并返回文件的属性。
语法：UTL_FILE.FGETATTR( location IN VARCHAR2,
filename IN VARCHAR2,
fexists OUT BOOLEAN,
file_length OUT NUMBER,
block_size OUT BINARY_INTEGER);
参数：location 此处略去X个字。
filename此处略去X个字。
fexists 返回的属性1：文件是否存在
file_length 返回的属性2：文件字节长度，如果文件不存在，则返回NULL。
block_size  文件系统块的字节大小。

FGETPOS
描述：此函数返回一个文件中当前的偏移位置。
语法：
UTL_FILE.FGETPOS (file IN FILE_TYPE) RETURN PLS_INTEGER;
注意事项：如果file没有打开，则会抛出异常

FREMOVE
描述：此procedure在你有充足的权限之下，删除一个磁盘上的文件。
语法：
UTL_FILE.FREMOVE ( location IN VARCHAR2,
filename IN VARCHAR2);

FRENAME
描述：此procedure将一个存在的文件重命名，类似unix命令：mv
语法：
UTL_FILE.FRENAME ( src_location   IN VARCHAR2,
src_filename  IN VARCHAR2,
dest_location  IN VARCHAR2,
dest_filename IN VARCHAR2,
overwrite        IN BOOLEAN DEFAULT FALSE);

GET_LINE
描述：此procedure从一个打开的文件中读取一行文本，直到遇到换行符。
语法：
UTL_FILE.GET_LINE ( file     IN FILE_TYPE,
buffer OUT VARCHAR2,
len      IN PLS_INTEGER DEFAULT NULL);
参数：len 从文本中读取一次的长度，默认是null，oracle就取FOPEN时的max_linesieze。

IS_OPEN
描述：顾名思义。
语法：UTL_FILE.IS_OPEN (file IN FILE_TYPE)  RETURN BOOLEAN;
1.PUT
描述：PUT写入内容到文件中。（每写一次，不带换行符）
语法：UTL_FILE.PUT (file IN FILE_TYPE, buffer IN VARCHAR2);

PUT_LINE
描述：PUT_LINE写入内容到文件中。（每写一次，末尾都加一个换行符）
语法：
UTL_FILE.PUT_LINE ( file          IN FILE_TYPE,
buffer      IN VARCHAR2,
autoflush IN BOOLEAN DEFAULT FALSE);
PUTF
描述：    写入格式化的内容到文件中。好比C语言的printf()
语法：
UTL_FILE.PUTF ( file IN FILE_TYPE,
format IN VARCHAR2,
[arg1 IN VARCHAR2 DEFAULT NULL,
. . .
arg5 IN VARCHAR2 DEFAULT NULL]);
参数：format 包含格式化字符[\n，%s]的内容。
\n：代表一个换行符。
%s：用arg1~5的值去代替。

 

 

做一个例子如下：

CREATE OR REPLACE PROCEDURE SQL_TO_CSV
(
P_QUERY IN VARCHAR2,                        — PLSQL文
P_DIR IN VARCHAR2,                          — 导出的文件放置目录
P_FILENAME IN VARCHAR2                      — CSV名
)
IS
L_OUTPUT UTL_FILE.FILE_TYPE;
L_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
L_COLUMNVALUE VARCHAR2(4000);
L_STATUS INTEGER;
L_COLCNT NUMBER := 0;
L_SEPARATOR VARCHAR2(1);
L_DESCTBL DBMS_SQL.DESC_TAB;
P_MAX_LINESIZE NUMBER := 32000;
BEGIN
–OPEN FILE
L_OUTPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, ‘A’, P_MAX_LINESIZE);
–DEFINE DATE FORMAT
EXECUTE IMMEDIATE ‘ALTER SESSION SET NLS_DATE_FORMAT=”YYYY-MM-DD HH24:MI:SS”’;
–OPEN CURSOR
DBMS_SQL.PARSE( L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE );
DBMS_SQL.DESCRIBE_COLUMNS( L_THECURSOR, L_COLCNT, L_DESCTBL );
–DUMP TABLE COLUMN NAM
FOR I IN 1 .. L_COLCNT LOOP
UTL_FILE.PUT( L_OUTPUT, L_SEPARATOR || ‘”’ || L_DESCTBL(I).COL_NAME || ‘”’ );
DBMS_SQL.DEFINE_COLUMN( L_THECURSOR, I, L_COLUMNVALUE, 4000 );
L_SEPARATOR := ‘,’;
END LOOP;
UTL_FILE.NEW_LINE( L_OUTPUT );
–EXECUTE THE QUERY STATEMENT
L_STATUS := DBMS_SQL.EXECUTE(L_THECURSOR);
–DUMP TABLE COLUMN VALUE
WHILE ( DBMS_SQL.FETCH_ROWS(L_THECURSOR) > 0 ) LOOP
L_SEPARATOR := ”;
FOR I IN 1 .. L_COLCNT LOOP
DBMS_SQL.COLUMN_VALUE( L_THECURSOR, I, L_COLUMNVALUE );
UTL_FILE.PUT( L_OUTPUT, L_SEPARATOR || ‘”’ ||
TRIM(BOTH ‘ ‘ FROM REPLACE(L_COLUMNVALUE,’”’,’””’)) || ‘”’);
L_SEPARATOR := ‘,’;
END LOOP;
UTL_FILE.NEW_LINE( L_OUTPUT );
END LOOP;
–CLOSE CURSOR
DBMS_SQL.CLOSE_CURSOR(L_THECURSOR);
–CLOSE FILE
UTL_FILE.FCLOSE( L_OUTPUT );
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/

— 创建存放文件的目录
CREATE OR REPLACE DIRECTORY MYDIR AS ‘C:\’;

create table t as select * from dba_users;

— 执行块
begin
sql_to_csv(‘select * from T ‘,’MYDIR’,’EXAMPLE2.CSV’);
dbms_lock.sleep(10) ;
sql_to_csv(‘select * from T ‘,’MYDIR’,’EXAMPLE3.CSV’);
end;
/

Limit SQL*Plus operation

Posted on by

SQL*Plus Security
限制哪些用户在SQL*Plus里能够执行哪些命令以及不能执行哪些命令

oracle是用system用户下的PRODUCT_USER_PROFILE (PUP)表来进行的限制。提供了产品级别的安全,用户级安全（SQL GRANT和REVOKE命令和用户角色）相互补充。

select * from product_user_profile;
如果这张表不存在可以使用  @?/sqlplus/admin/pupbld
表结构如下：
SQL> desc product_user_profile
Name                     Type
——————— —————
PRODUCT                 VARCHAR2(30)                  –必须包含产品的名称(SQL * Plus)。不能输入通配符或NULL
USERID                  VARCHAR2(30)                  –必须包含用户的用户名(大写)来说,你想禁用命令。为多个用户禁用命令,使用SQL通配符(%)或多个条目 HR，HR1  HR%  %
ATTRIBUTE               VARCHAR2(240)                 –必须包含的名称(大写)SQL、SQL * Plus或者PL / SQL命令禁用(例如,RUN)   如果你禁用一个角色,他可以是这个角色的字符串
SCOPE                   VARCHAR2(240)                 –空就好了
NUMERIC_VALUE           NUMBER(15,2)                  –空就好了
CHAR_VALUE              VARCHAR2(240)                 –禁用一个SQL、SQL * Plus,或PL / SQL命令是”DISABLED”  如果你禁用一个角色,它必须包含角色的名字  不可以使用通配符(%)
DATE_VALUE              DATE                          –空就好了
LONG_VALUE              LONG                          –空就好了

SQL*Plus   HR      HOST                           DISABLED
SQL*Plus   %       INSERT                         DISABLED
SQL*Plus   %       UPDATE                         DISABLED
SQL*Plus   %       DELETE                         DISABLED

insert into product_user_profile(PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE )values(‘SQL*Plus’,’HR’,’HOST’,’DISABLED’);
insert into product_user_profile(PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE )values(‘SQL*Plus’,’%’,’INSERT’,’DISABLED’);
insert into product_user_profile(PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE )values(‘SQL*Plus’,’%’,’UPDATE’,’DISABLED’);
insert into product_user_profile(PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE )values(‘SQL*Plus’,’%’,’DELETE’,’DISABLED’);
COMMIT;

–Disabling HOST disables the operating system alias for HOST, such as $ on Windows, and ! on UNIX
–不推荐禁用退出/退出。如果禁用,终止命令行会话通过发送一个EOF字符如Ctrl + D在UNIX或Ctrl + Z在Windows。终止一个Windows的GUI和文件>退出会话。否则,终止会话终止的SQL * Plus流程。
–如果禁用,使用退出/退出终止当前运行的脚本iSQL *加上也禁用。如果禁用,退出操作只要OSERROR每当SQLERROR也禁用。
–当禁用了help的时候同时也禁用了？
–禁用SQL * Plus set 命令也禁用SET CONSTRAINTS, SET ROLE and SET TRANSACTION
–禁用SQL * Plus start也禁用@ / @@
–禁用BEGIN 和 DECLARE 并不妨碍使用SQL * Plus  EXECUTE  去 运行PL / SQL。EXECUTE必须单独禁用
SQL*PLUS 可以限制的命令有：
ACCEPT   ，DEFINE ，PASSWORD ，SHUTDOWN     ，APPEND
DEL      ，PAUSE  ，SPOOL    ，ARCHIVE LOG  ，DESCRIBE
PRINT    ，START (@, @@)
ATTRIBUTE       ，DISCONNECT         ，PROMPT                          ，STARTUP
BREAK           ，EDIT               ，RECOVER                         ，STORE
BTITLE          ，EXECUTE            ，REMARK                          ，TIMING
CHANGE          ，EXIT/QUIT          ，REPFOOTER                       ，TTITLE
CLEAR           ，GET                ，REPHEADER                       ，UNDEFINE
COLUM           ，NHELP (?)          ，RUN                             ，VARIABLE
COMPUTE         ，HOST
SAVE            ，WHENEVER OSERROR
CONNECT  ，INPUT           ，SET      ，WHENEVER SQLERROR
COPY     ，LIST (;)        ，SHOW     ，XQUERY

SQL命令可以限制的有：
ALTER    ，DELETE      ， MERGE   ，  SET CONSTRAINTS
ANALYZE  ，DISASSOCIATE， NOAUDIT ，  SET ROLE
ASSOCIATE，DROP        ， PURGE   ，  SET TRANSACTION
AUDIT    ，EXPLAIN     ， RENAME  ，  TRUNCATE
CALL     ，FLASHBACK   ， REVOKE  ，  UPDATE
COMMENT  ，GRANT       ， ROLLBACK，  VALIDATE
COMMIT   ，INSERT      ， SAVEPOINT， CREATE，LOCK， SELECT

pl/sql可以限制的有
BEGIN    DECLARE
我们做个例子：

1.登录到system
conn system/oracle
2.添加禁用
INSERT INTO PRODUCT_USER_PROFILE
VALUES (‘SQL*Plus’, ‘HR’, ‘SELECT’, NULL, NULL, ‘DISABLED’, NULL, NULL);
3.登录到hr并查询
SQL> conn hr/hr
Connected.
SQL> select * from dual;
SP2-0544: Command “select” disabled in Product User Profile
SQL> ho ls
SP2-0544: Command “host” disabled in Product User Profile
4.在SYSTEM里去掉刚刚的限制
DELETE FROM PRODUCT_USER_PROFILE WHERE USERID = ‘HR’;

但是在3的时候我们如果用PL/SQL DEVELOPER或者其它的IDE工具是没有任何问题的
当然 PUP也可以在角色上进行限制：
可以使用SQL命令来创建和控制角色为你提供安全访问数据库表。通过创建一个角色,然后控制谁有权访问它,确保只有特定的用户可以访问特定的数据库特权
创建两个role
–一个不带密码
create role ROLE1;
–一个带密码
create role role2 identified by oracle;

grant create session,create table,create view to ROLE1;

grant create session,create table,create view,create procedure to role2;

create user ucjmh identified by ucjmh;

grant ROLE1,ROLE2 to ucjmh;

–修改ucjmh用户的缺省角色，只让role1在登陆的时候生效

alter user ucjmh default role role1;
select * from product_user_profile
然后insert
insert into product_user_profile(PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE )values(‘SQL*Plus’,’%’,’ROLES’,’ROLE1′);
这个时候登录：
SP2-0557: Error in disabling roles in product user profile.
Connected.

这个时候 我们再insert role2
insert into product_user_profile(PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE )values(‘SQL*Plus’,’UCJMH’,’ROLES’,’ROLE2′);

SQL> conn ucjmh/ucjmh
Connected.
SQL> select * from session_roles;

no rows selected

然后可以看出来其实在登录的时候oracle内部做了：
SET ROLE ALL EXCEPT ROLE1, ROLE2;

DBMS_ERRLOG.CREATE_ERROR_LOG记录错误日志

Posted on by

DBMS_ERRLOG包提供了一个程序,使您可以创建一个错误日志表,
以便DML操作后可以继续遇到错误而不是终止和回滚。这使您能够节省时间和系统资源。

–首先创建一张测试表 A
CREATE TABLE a (ID NUMBER PRIMARY KEY);

BEGIN
— DBMS_ERRLOG.create_error_log(dml_table_name => ‘A’);
DBMS_ERRLOG.create_error_log(dml_table_name => ‘A’,err_log_table_name => ‘A_LOG’);
END;

–这个时候去查 可以发现多出来了表ERR$_A和A_LOG
SELECT * FROM tab

–两张表的表结构完全相同
SELECT * FROM ERR$_A;
SELECT * FROM A_LOG;

ORA_ERR_NUMBER$        NUMBER           –错误记录数
ORA_ERR_MESG$          VARCHAR2(2000)   –错误信息
ORA_ERR_ROWID$         ROWID            –错误记录的rowid(仅对update或delete)
ORA_ERR_OPTYP$         VARCHAR2(2)      –操作类型(I-INSERT,U-UPDATE,D-DELETE)
ORA_ERR_TAG$           VARCHAR2(2000)   –自定义标志说明
A_ID                    VARCHAR2(4000)   –原表字段

其中前五个字段是固定的,后边字段根据原表确定.
但是原表和对应error log表的字段类型是不完全匹配的,
其转换规则如下:
NUMBER          –>         VARCHAR2(4000)
CHAR/VARCHAR2(n)    –>     VARCHAR2(4000)
NCHAR/NVARCHAR2(n)   –>      NVARCHAR2(4000)
DATE/TIMESTAMP       –>          VARCHAR2(4000)
RAW                –>         RAW(2000)
ROWID              –>           UROWID
LONG/LOB     –>     LOB

DBMS_ERRLOG.CREATE_ERROR_LOG (
dml_table_name            IN VARCHAR2,–DML的名称表基础上的错误日志表
err_log_table_name        IN VARCHAR2 := NULL,–您将创建错误日志表的名称。默认的是（最多25字符）DML表前缀的名称’ERR$_‘。例子如下:dml_table_name: ‘EMP’, err_log_table_name: ‘ERR$_EMP’
err_log_table_owner       IN VARCHAR2 := NULL,–错误日志表的所有者的名称。您可以指定dml_table_name的所有者
err_log_table_space       IN VARCHAR2 := NULL,–错误日志表将被创建的表空间。如果没有指定,默认表空间为用户拥有将使用DML错误日志表
skip_unsupported          IN BOOLEAN := FALSE);–设置为TRUE时,不支持的列类型错误日志将被跳过,而不是添加到错误日志表。设置为FALSE时,一个不受支持的列类型将导致程序终止。

INSERT INTO a VALUES(1) LOG ERRORS
–INTO A_LOG  可以指定到自己的表里 也可以不写 默认就是ERR$_的
REJECT LIMIT UNLIMITED; –当insert into多行记录时 最多可以容忍多少错误。

–当然 直接这样也是可以的
INSERT INTO a VALUES(1) LOG ERRORS

Oracle包、函数、过程的加密和解密

Posted on by

首先我们来看一下oracle对包、函数、过程的加密

oracle内部提供了两种加密方式

我们先来看第一种：

在没有内置OCI之前，ORACLE只提供WRAP操作系统工具。

我们来简单的看一个例子：

SQL> ho

[oracle@ucjmh ~]$ vi function.sql

SQL> ho cat function.sql

create or replace function dl2ml(hy in varchar)

return varchar

as

x varchar(2);

begin

select

case

when hy >=’01’ and hy<= ’05’ then 1

when hy >=’06’ and hy<= ’11’ then 2

when hy >=’13’ and hy<= ’43’ then 3

when hy >=’44’ and hy<= ’46’ then 4

when hy >=’47’ and hy<= ’50’ then 5

when hy >=’51’ and hy<= ’59’ then 6

when hy >=’60’ and hy<= ’62’ then 7

when hy  =’63’ or  hy = ’65’ then 8

when hy >=’66’ and hy<= ’67’ then 9

when hy >=’68’ and hy<= ’71’ then 10

when hy >=’72’ and hy<= ’72’ then 11

when hy >=’73’ and hy<= ’74’ then 12

when hy >=’75’ and hy<= ’78’ then 13

when hy >=’79’ and hy<= ’81’ then 14

when hy >=’82’ and hy<= ’83’ then 15

when hy >=’84’ and hy<= ’84’ then 16

when hy >=’85’ and hy<= ’87’ then 17

when hy >=’88’ and hy<= ’92’ then 18

when hy >=’93’ and hy<= ’98’ then 19

else null

end

into x from dual;

return x;

end;

/

我们首先创始一个文件 里面是一个简单的函数

然后使用操作系统级别的命令进行加密

SQL> @function.sql

Function created.

SQL> ho

[oracle@ucjmh ~]$ wrap iname=function.sql oname=function.pld

PL/SQL Wrapper: Release 11.2.0.3.0- Production on Fri May 01 00:31:44 2015

Copyright (c) 1993, 2009, Oracle.  All rights reserved.

Processing function.sql to function.pld

[oracle@ucjmh ~]$ cat function.pld

create or replace function dl2ml wrapped

a000000

369

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

8

335 199

ndEQSpIvhV202FWf2jLyxpFnjQMwg1zrr0hGyo5EUOI6GMTHyUnF4S3rRsXrfy11DmGfmkPS

tcQ9Nue/OhW9vrvTuRMesVKf0vjY+UGcxijwSrxfuyzUD1jEHovrR0cVM1dx5fbGGc3GfbTg

SQVk2HhtAZooHn1TQ80riTTKhkALI+3MzBisPE7I1t7HakJ8XEgwkBPoy+j/Luup3tbe3YVo

42UE9/37WALL6NcuuovoO6z3+lA4SokDsMd7LmhMrwBLEtXWs3DDSJGKB5yt8ZQOBOOE69Oj

iS6iaQ2OqcOiUA13rQk+tok7yrcY0NRreURk6AkbTbo1G5T2BtMv0hpeRGlTQJ71g9yC2mKn

aN8uR0U38EiQ3rCYTyTetEd3lapLv8FC/psoXEq+ww==

/

[oracle@ucjmh ~]$ exit

exit

SQL> @funtion.pld

SP2-0310: unable to open file “funtion.pld”

SQL> @function.pld

Function created.

SQL> select dl2ml(’45’) from dual;

DL2ML(’45’)

——————————————————————————–

4

SQL> select text from user_source where name=’DL2ML’;

TEXT

——————————————————————————–

function dl2ml wrapped

a000000

369

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

8

335 199

ndEQSpIvhV202FWf2jLyxpFnjQMwg1zrr0hGyo5EUOI6GMTHyUnF4S3rRsXrfy11DmGfmkPS

tcQ9Nue/OhW9vrvTuRMesVKf0vjY+UGcxijwSrxfuyzUD1jEHovrR0cVM1dx5fbGGc3GfbTg

SQVk2HhtAZooHn1TQ80riTTKhkALI+3MzBisPE7I1t7HakJ8XEgwkBPoy+j/Luup3tbe3YVo

42UE9/37WALL6NcuuovoO6z3+lA4SokDsMd7LmhMrwBLEtXWs3DDSJGKB5yt8ZQOBOOE69Oj

iS6iaQ2OqcOiUA13rQk+tok7yrcY0NRreURk6AkbTbo1G5T2BtMv0hpeRGlTQJ71g9yC2mKn

aN8uR0U38EiQ3rCYTyTetEd3lapLv8FC/psoXEq+ww==

接下来我们再来看第二种加密方式：

在ORACLE 10.2之后，ORACLE提供了内置的OCI，为用户提供代码加密，其中，DBMS_DDL.WRAP和DBMS_DDL.CREATE_WRAPPED分别提供了加密、加密并且创建功能

还是来看个例子：

DBMS_DDL.WRAP使用示例

1：文本加密

select dbms_ddl.wrap(‘create or replace procedure TESTPRO is begin null end;’) from dual;

DBMS_DDL.WRAP(‘CREATEORREPLACEPROCEDURETESTPROISBEGINNULLEND;’)

————————————————————–

create or replace procedure TESTPRO wrapped

a000000

369

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

7

25 59

8g4YR51zp6Rm0t2Q5m7K9QrNB6wwg5nnm7+fMr2ywFznUrLLveeb6qV0K7jAMv7SXqWZgQjM

uIHHLcmmpmq0vfo=

2：已经创建过的代码对象加密

首先先创建一个过程

SQL> create or replace procedure TESTPRO1 is

V_str   varchar2(100);

begin

v_str:=’This is  string.’;

dbms_output.put_line(v_str);

end TESTPRO1;

/

Procedure created.

然后 从数据库获取源码 并且加密

SQL> DECLARE

l_source  sys.DBMS_SQL.VARCHAR2A;

l_wrap    sys.DBMS_SQL.VARCHAR2A;

BEGIN

SELECT case line

when 1 then ‘Create or replace ‘||Type|| ‘ ‘||text

else TEXT

end case  bulk collect into l_source from dba_source where name=’TESTPRO1’ ORDER BY LINE;

 

l_wrap := SYS.DBMS_DDL.WRAP(ddl => l_source,

lb  => 1,

ub  => l_source.count);

FOR i IN 1 .. l_wrap.count LOOP

DBMS_OUTPUT.put_line(l_wrap(i));

END LOOP;

END;

/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17

Create or replace PROCEDURE procedure wrapped

a000000

369

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

7

ab

c6

wET87k8GVIxXUUglEZEexfhS/xAwg5nnm7+fMr2ywFwWFpeu3C4+YvJc51Kyy73nm3jDpXSL

CabWS86PyMovzQRdiu4EkRo4EXAO3rL39cAy/tLW5YQJeQEKHPH3k9im

op6VbOlYQikj/DyF

l3Fuz9aGc4SE4eaE0qlXGcbrclwd2JTJwqwKT5lofzx0pkJtxUQ=

PL/SQL procedure successfully completed.

注：

dbms_sql.varchar2a提供最大为每行32k的支持

dbms_sql.varchar2s提供每行256字节的支持

ORACLE 10.2.0.1可能会出现22921 bug.

dbms_ddl.create_wrapped同上一样，只是形成加密之后，进行创建

这是oracle提供的两种加密的方式  但是o并没有提供解密的方式 o在rdbms目录下也有大量的pld的文件 可能是不想破解吧

但是聪明的使用者已经研究出来了怎么破解  我们来看一下：

首先我们要明白 oracle是怎么加密的

Oracle加密的原理就是先对源码进行lz压缩lzstr，然后对压缩数据进行SHA-1运算得到40位的加密串shstr，然后将加密串与压缩串拼接得到shstr+lzstr，然后对拼接后的字符串进行Oracle双字符转换（转换表）。最后将转换后的字符串进行base64编码，最终得到wrap的加密串。

既然我们通过ＳＱＬ可以这样对某过程做DBMS_DDL.WRAP加密可以得到密文

那么对这部份密文的正文部份进行ＢＡＳＥ６４解码的值与未加密正文直接进行ＬＺ压缩后的值必然是一一对应的，且两个串的长度也是相等的对密文

进行ＢＡＳＥ６４解码后，将对应的密文的正文部份按字节替换成替换表中预先算出来的字节，最后直接按ＬＺ算法进行解压，

替换表正确的情况下，明文就应该出来了。通过这种假设，肯定就能得到替换表，替换表是按字

节来计算的，所以应该有二个列，其中一列代表ＢＡＳＥ６４解码后的字节值（十六进制００到ＦＦ），另一列代表替换列（ＢＡＳＥ６４列不能出现重复值是一个前提）。
ＢＡＳＥ６４编码地球人都知道，在ＯＲＡＣＬＥ中有现存的工具包进行编码和解码，我们将用到ＢＡＳＥ６４的解码，具体包是：sys.utl_encode.base64_decode。用的时候还需要另一个过程来将字符串转换为ＲＡＷ格式：sys.utl_raw.cast_to_raw

ＬＺ压缩很常见，不过懂得内部算法的人很少，ＯＲＡＣＬＥ中也有现存的工具包，我这里用的是老外的一个ＪＡＶＡ包。在

使用这个ＬＺ工具包时，涉及到一个压缩级别参数，这个等级参数不一样，压缩得到的字符串完全一不样。有人可能要问，这样搞

岂不是没法得到替换表了吗？是的，但也不完全正确。因为可供选择的等级参数有限，俺们还能从０等级开始一个一个进行测试，

看到底哪个参数是ＯＲＡＣＬＥ系统用的来ＷＲＡＰ的。嘿嘿，ＯＲＡＣＬＥ用的是“９”等级。

创建过程或包时如果没有ＣＲＥＡＴＥ部份，ＯＲＡＣＬＥ肯定要报错；同样DBMS_DDL.WRAP也不能缺少这个“create”，

否则就要报错。但对于过程或包的ＳＯＵＲＣＥ，查阅系统视图DBA_SOURCE的ＴＥＸＴ列就知道了，肯定没有ＣＲＥＡＴＥ这一句。

说到密文的正文部份，首先要看下面的例子：SQL>select dbms_ddl.wrap(‘create procedure a’) from dual;

create procedure a wrapped

a000000

354

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

7

c 38

8BgMHdmA3Qg9IbJmntlZoZQoHwcwg5nnm7+fMr2ywFxakaamb40d1Q=

这里要解释一下，加密后的代码中３５４与ＤＢ的版本有关，abcd后的７与创建的对象类型有关，也就是７为存储过程，另外的c 38有其他意义，这里就不多说了。从8BgMH开始，ＢＡＳＥ６４解码后的前二十个字节是SHA1-HASH值，所以解码后的第二十一个字节开始就是正文了
为了下一节的实践活动，嘿嘿，我们先把ＪＡＶＡ包创建好，用以进行LZ压缩与解压，如下所示（全部用ＳＹＳ用户来做）：create or replace java source named UNWRAPPER

as

import java.io.*;

import java.util.zip.*;

public class UNWRAPPER

{

public static String Inflate( byte[] src )

{

try

{

ByteArrayInputStream bis = new ByteArrayInputStream( src );

InflaterInputStream iis = new InflaterInputStream( bis );

StringBuffer sb = new StringBuffer();

for( int c = iis.read(); c != -1; c = iis.read() )

{

sb.append( (char) c );

}

return sb.toString();

} catch ( Exception e )

{

}

return null;

}

public static byte[] Deflate( String src, int quality )

{

try

{

byte[] tmp = new byte[ src.length() + 100 ];

Deflater defl = new Deflater( quality );

defl.setInput( src.getBytes( “UTF-8” ) );

defl.finish();

int cnt = defl.deflate( tmp );

byte[] res = new byte[ cnt ];

for( int i = 0; i < cnt; i++ )

res = tmp;

String hello = new String(res.toString().getBytes(“iso8859-1”), “GBK”);

return hello;

return res;

} catch ( Exception e )

{

}

return null;

}

}

/

alter java source UNWRAPPER compile

/

然后用包把JAVA声明进来：

create or replace package amosunwrapper

is

function deflate( src in varchar2 )

return raw;

function deflate( src in varchar2, quality in number )

return raw;

function inflate( src in raw )

return varchar2;

end;

/

create or replace package body amosunwrapper

is

function deflate( src in varchar2 )

return raw

is

begin

return deflate( src, 6 );

end;

function deflate( src in varchar2, quality in number )

return raw

as language java

name ‘UNWRAPPER.Deflate( java.lang.String, int ) return byte[]’;

function inflate( src in raw )

return varchar2

as language java

name ‘UNWRAPPER.Inflate( byte[] ) return java.lang.String’;

end;

/
创建好了工具，我们先来看看下面的SQL：with src AS ( select ‘PACKAGE a’ txt  from dual   ),

wrap as   ( select src.txt , dbms_ddl.wrap( ‘create ‘ || src.txt ) wrap  from src  ),

subst as  (select substr( utl_encode.base64_decode( utl_raw.cast_to_raw(rtrim( substr( wrap.wrap, instr( wrap.wrap, chr( 10 ), 1, 20 ) + 1 ), chr(10) )  ) ), 41 ) x,

amosunwrapper.deflate( wrap.txt || chr(0), 9 ) d

from wrap  )

select substr( x, r * 2 – 1, 2 )  c_base64,

substr( d, r * 2 – 1, 2 )  c_translatecode

from subst  , ( select rownum r from dual connect by rownum <= ( select length( x ) / 2 from subst ) );

结果如下：

C_BASE64 C_TRANSLATECODE

30 78

83 DA

99 0B

B8 70

F5 74

33 F6

9F 76

F5 74

BF 77

5C 55

5A 48

91 64

A6 00

A6 00

CB 0E

C4 B7

E1 02

48 6E

通过对结果的排序，没有出现同一个ＢＡＳＥ６４编码对应不同的十六进制的情况，因此我们知道了可以用这个ＳＱＬ为基础，通过用不同的ＳＯＵＲＣＥ串来产生替换表的内容。
首先建一个表来存储替换表的内容,然后写一段PLSQL块来生成替换表的内容:
CREATE TABLE SYS.IDLTRANSLATE(

C_BASE64DECODE  VARCHAR2(2) NOT NULL,

C_LZDEFLATECODE VARCHAR2(2)     NULL

)

/

declare

nCnt integer;

nLoop integer;

nSLoop integer;

nCharmax integer;

nCharmin  integer;

vChar     Varchar2(3);

cursor getchar is

with src AS ( select ‘PACKAGE ‘||vChar txt  from dual   ),

wrap as   ( select src.txt , dbms_ddl.wrap( ‘create ‘ || src.txt ) wrap  from src  ),

subst as  (select substr( utl_encode.base64_decode( utl_raw.cast_to_raw(rtrim( substr( wrap.wrap, instr( wrap.wrap, chr( 10 ), 1, 20 ) + 1 ), chr(10) )  ) ), 41 ) x,

amosunwrapper.deflate( wrap.txt || chr(0), 9 ) d

from wrap  )

select  substr( x, r * 2 – 1, 2 )  xr ,

substr( d, r * 2 – 1, 2 )  dr

from subst  , ( select rownum r from dual connect by rownum <= ( select length( x ) / 2 from subst ) );

begin

nCharmax:=97;

nCharmin:=122;

For nLoop In 97..122 Loop

For nSloop In 0..99 Loop

vChar := chr(nLoop)||to_char(nSloop);

For abc In getchar Loop

Select Count(*) Into nCnt From sys.idltranslate WHERE c_base64decode = abc.xr;

If nCnt < 1 Then

Insert INTO sys.idltranslate VALUES (abc.xr,abc.dr);

Commit;

Else

Select Count(*) Into ncnt From sys.idltranslate WHERE c_base64decode = abc.xr AND c_lzdeflatecode=abc.dr;

If nCnt < 1 Then

DBMS_OUTPUT.PUT_LINE(‘wrong orginal char:’||vchar||’         hex base64:’||abc.xr);

End If;

End If;

End Loop;

End Loop;

End Loop;

end;

运行上面这段ＳＱＬ大概会产生１百多条记录，还未达到00-FF总共256条记录的要求，建议替换

select ‘PACKAGE ‘||vChar txt  from dual   中的PACKAGE关健字为procedure或者function类似的，继续运行直到替换表中有不重复的256条记录为止。

然后接下来看一个系统包

SQL> set serveroutput on;

Declare

vWrappedtext                Varchar2(32767);

vChar                                        Varchar2(2);

vRepchar                                Varchar2(2);

vLZinflatestr                        Varchar2(32767);

nLen                Integer;

nLoop        Integer;

nCnt                Integer;

Begin

select substr( utl_encode.base64_decode( utl_raw.cast_to_raw(rtrim( substr( TEXT, instr( TEXT, chr( 10 ), 1, 20 ) + 1 ), chr(10) )  ) ), 41 ) x

Into vWrappedtext

from DBA_SOURCE

Where owner=’SYS’

And Name = ‘DBMS_MONITOR’

And Type=’PACKAGE BODY’ ;

–DBMS_OUTPUT.PUT_LINE(vWrappedtext);

nLen := Length(vWrappedtext)/2 – 1;

vLZinflatestr :=”;

For nLoop In 0..nLen Loop

vChar := Substrb(vWrappedtext,nLoop*2+1,2);

SelectSQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   Count(*) Into nCnt From SYS.IDLTRANSLATE Where C_BASE64DECODE=vChar;

If nCnt <> 1 Then

DBMS_OUTPUT.PUT_LINE(‘SUBSTATION TABLE WARNING: Count not find following char–‘||vChar);

Return;

Else

Select C_LZDEFLATECODE Into vRepchar From SYS.IDLTRANSLATE Where C_BASE64DECODE=vChar;

End If;

vLZinflatestr := vLZinflatestr || vRepchar;

–DBMS_OUTPUT.PUT_LINE(vLZinflatestr);

End Loop;

–DBMS_OUTPUT.PUT_LINE(vLZinflatestr);

DBMS_OUTPUT.PUT_LINE(amosunwrapper.inflate(vLZinflatestr));

End;

23   24   25   26   27   28   29   30   31   32   33   34   35

36  /

PACKAGE BODY dbms_monitor IS

PROCEDURE CLIENT_ID_STAT_ENABLE(CLIENT_ID IN VARCHAR2) IS

EXTERNAL

NAME “kewe_3gl_1”

WITH CONTEXT

PARAMETERS (CONTEXT,

CLIENT_ID   OCISTRING,

CLIENT_ID   INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

PROCEDURE CLIENT_ID_STAT_DISABLE(CLIENT_ID IN VARCHAR2) IS

EXTERNAL

NAME “kewe_3gl_2”

WITH CONTEXT

PARAMETERS (CONTEXT,

CLIENT_ID     OCISTRING,

CLIENT_ID   INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

PROCEDURE

SERV_MOD_ACT_STAT_ENABLE(SERVICE_NAME IN VARCHAR2,

MODULE_NAME  IN VARCHAR2,

ACTION_NAME  IN VARCHAR2) IS

EXTERNAL

NAME “kewe_3gl_3”

WITH CONTEXT

PARAMETERS (CONTEXT,

SERVICE_NAME

OCISTRING,

SERVICE_NAME    INDICATOR SB4,

MODULE_NAME     OCISTRING,

MODULE_NAME

INDICATOR SB4,

ACTION_NAME     OCISTRING,

ACTION_NAME     INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

PROCEDURE SERV_MOD_ACT_STAT_DISABLE(SERVICE_NAME IN VARCHAR2,

MODULE_NAME  IN VARCHAR2,

ACTION_NAME  IN VARCHAR2) IS

EXTERNAL

NAME “kewe_3gl_4”

WITH CONTEXT

PARAMETERS (CONTEXT,

SERVICE_NAME

OCISTRING,

SERVICE_NAME    INDICATOR SB4,

MODULE_NAME     OCISTRING,

MODULE_NAME

INDICATOR SB4,

ACTION_NAME     OCISTRING,

ACTION_NAME     INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

PROCEDURE CLIENT_ID_TRACE_ENABLE(CLIENT_ID IN VARCHAR2,

WAITS IN BOOLEAN,

BINDS IN BOOLEAN,

PLAN_STAT IN VARCHAR2) IS

EXTERNAL

NAME “kewe_3gl_5”

WITH CONTEXT

PARAMETERS (CONTEXT,

CLIENT_ID   OCISTRING,

CLIENT_ID     INDICATOR SB4,

WAITS   UB2,

WAITS   INDICATOR SB4,

BINDS

UB2,

BINDS   INDICATOR SB4,

PLAN_STAT   OCISTRING,

PLAN_STAT   INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

PROCEDURE CLIENT_ID_TRACE_DISABLE(CLIENT_ID IN VARCHAR2) IS

EXTERNAL

NAME “kewe_3gl_6”

WITH CONTEXT

PARAMETERS (CONTEXT,

CLIENT_ID   OCISTRING,

CLIENT_ID   INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

PROCEDURE SERV_MOD_ACT_TRACE_ENABLE(SERVICE_NAME IN VARCHAR2,

MODULE_NAME  IN VARCHAR2,

ACTION_NAME  IN VARCHAR2,

WAITS IN BOOLEAN,

BINDS IN BOOLEAN,

INSTANCE_NAME

IN VARCHAR2,

PLAN_STAT IN VARCHAR2) IS

EXTERNAL

NAME “kewe_3gl_7”

WITH CONTEXT

PARAMETERS

(CONTEXT,

SERVICE_NAME    OCISTRING,

SERVICE_NAME    INDICATOR SB4,

MODULE_NAME

OCISTRING,

MODULE_NAME     INDICATOR SB4,

ACTION_NAME     OCISTRING,

ACTION_NAME

INDICATOR SB4,

WAITS     UB2,

WAITS     INDICATOR SB4,

BINDS     UB2,

BINDS INDICATOR SB4,

INSTANCE_NAME   OCISTRING,

INSTANCE_NAME   INDICATOR SB4,

PLAN_STAT

OCISTRING,

PLAN_STAT     INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

PROCEDURE

SERV_MOD_ACT_TRACE_DISABLE(SERVICE_NAME IN VARCHAR2,

MODULE_NAME  IN VARCHAR2,

ACTION_NAME  IN VARCHAR2,

INSTANCE_NAME IN VARCHAR2) IS

EXTERNAL

NAME “kewe_3gl_8”

WITH

CONTEXT

PARAMETERS (CONTEXT,

SERVICE_NAME    OCISTRING,

SERVICE_NAME    INDICATOR SB4,

MODULE_NAME OCISTRING,

MODULE_NAME     INDICATOR SB4,

ACTION_NAME     OCISTRING,

ACTION_NAME INDICATOR SB4,

INSTANCE_NAME   OCISTRING,

INSTANCE_NAME   INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

PROCEDURE SESSION_TRACE_ENABLE(SESSION_ID IN BINARY_INTEGER,

SERIAL_NUM IN BINARY_INTEGER,

WAITS IN BOOLEAN,

BINDS IN BOOLEAN,

PLAN_STAT IN VARCHAR2) IS

EXTERNAL

NAME “kewe_3gl_9”

WITH CONTEXT

PARAMETERS (CONTEXT,

SESSION_ID    SB4,

SESSION_ID    INDICATOR

SB4,

SERIAL_NUM    SB4,

SERIAL_NUM    INDICATOR SB4,

WAITS   UB2,

WAITS

INDICATOR SB4,

BINDS   UB2,

BINDS   INDICATOR SB4,

PLAN_STAT   OCISTRING,

PLAN_STAT     INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

PROCEDURE SESSION_TRACE_DISABLE(SESSION_ID IN BINARY_INTEGER,

SERIAL_NUM IN BINARY_INTEGER) IS

EXTERNAL

NAME “kewe_3gl_10”

WITH CONTEXT

PARAMETERS (CONTEXT,

SESSION_ID    SB4,

SESSION_ID    INDICATOR SB4,

SERIAL_NUM    SB4,

SERIAL_NUM    INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

PROCEDURE DATABASE_TRACE_ENABLE(WAITS IN BOOLEAN,

BINDS IN BOOLEAN,

INSTANCE_NAME IN

VARCHAR2,

PLAN_STAT IN VARCHAR2) IS

EXTERNAL

NAME “kewe_3gl_11”

WITH CONTEXT

PARAMETERS

(CONTEXT,

WAITS     UB2,

WAITS     INDICATOR SB4,

BINDS     UB2,

BINDS INDICATOR SB4,

INSTANCE_NAME   OCISTRING,

INSTANCE_NAME   INDICATOR SB4,

PLAN_STAT

OCISTRING,

PLAN_STAT     INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

PROCEDURE DATABASE_TRACE_DISABLE(INSTANCE_NAME

IN VARCHAR2) IS

EXTERNAL

NAME “kewe_3gl_12”

WITH CONTEXT

PARAMETERS (CONTEXT,

INSTANCE_NAME   OCISTRING,

INSTANCE_NAME INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

END;

PL/SQL procedure success

上面直接调用不能大于4000个字节  我们写一个过程来封装一下 完善一下

set serveroutput on;create or replace procedure unwrap(o in varchar,n in varchar, t in varchar)

as

vWrappedtext                Varchar2(32767);

vtrimtext                Varchar2(32767);

vChar                                        Varchar2(2);

vRepchar                                Varchar2(2);

vLZinflatestr                        Varchar2(32767);

nLen                Integer;

nLoop        Integer;

nCnt                Integer;

type vartab is table of varchar2(2) index by varchar2(2);

mytbl vartab;

cursor getchar is select C_BASE64DECODE xr,C_LZDEFLATECODE dr from sys.idltranslate;

Begin

for i in getchar loop –sys.idltranslate表内容存到字符数组

mytbl(i.xr):=i.dr;

end loop;

vtrimtext:=”;

select count(*) into ncnt                         from DBA_SOURCE

Where owner=o

And Name = n

And Type=t ;

if ncnt >0 and ncnt <5 then

for i in 1..ncnt loop

if i=1 then

select rtrim( substr( TEXT, instr( TEXT, chr( 10 ), 1, 20 ) + 1 ), chr(10) )   –保存去掉换行的BASE64码正文

into vLZinflatestr

from DBA_SOURCE

Where owner=o

And Name = n

And Type=t and line=i;

else

select text into vLZinflatestr

from DBA_SOURCE

Where owner=o

And Name = n

And Type=t and line=i;

end if;

vtrimtext:=vtrimtext||vLZinflatestr;

end loop;

end if;

vtrimtext:=replace(vtrimtext,chr(10),”);

nLen := Length(vtrimtext)/64 ;

vWrappedtext :=”;

for i in 0..nLen  loop

if i< nLen   then

vWrappedtext:=vWrappedtext||utl_encode.base64_decode( utl_raw.cast_to_raw(substrb(vtrimtext,64*i+1 , 64 ))) ;

else

vWrappedtext:=vWrappedtext||utl_encode.base64_decode( utl_raw.cast_to_raw(substrb(vtrimtext,64*i+1  ))) ;

end if;

–DBMS_OUTPUT.PUT_LINE(vWrappedtext);

End Loop;

–vWrappedtext:=substr(vWrappedtext,41);

nLen := Length(vWrappedtext)/2 – 1;

vLZinflatestr :=”;

For nLoop In 20..nLen Loop –从第41字节开始

vChar := Substrb(vWrappedtext,nLoop*2+1,2);

/*

Select Count(*) Into nCnt From SYS.IDLTRANSLATE Where C_BASE64DECODE=vChar;

If nCnt <> 1 Then

DBMS_OUTPUT.PUT_LINE(‘SUBSTATION TABLE WARNING: Count not find following char–‘||vChar);

Return;

Else

Select C_LZDEFLATECODE Into vRepchar From SYS.IDLTRANSLATE Where C_BASE64DECODE=vChar;

End If;

*/

vLZinflatestr := vLZinflatestr || mytbl(vChar); –从字符数组匹配

–DBMS_OUTPUT.PUT_LINE(vLZinflatestr);

End Loop;

–DBMS_OUTPUT.PUT_LINE(vLZinflatestr);

DBMS_OUTPUT.PUT_LINE(amosunwrapper.inflate(vLZinflatestr));

End;

/

最后我们来一个解密一下一开始的那个函数SQL> exec unwrap(‘SYS’,’DL2ML’,’FUNCTION’);

lag()和lead()

Posted on by

LAG()和LEAD()统计函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现，不过使用LAG和 LEAD有更高的效率。以下整理的LAG()和LEAD()例子：

LAG(EXPRESSION,<OFFSET>,<DEFAULT>)
SQL> select year,region,profit ,lag (profit,1) over (order by year)  as 51xit_exp from test;
YEAR REGION      PROFIT 51xit_exp
—- ——- ———- ————-
2003 West            88
2003 West            88            88
2003 Central        101            88
2003 Central        100           101
2003 East           102           100
2004 West            77           102
2004 East           103            77
2004 West            89           103

LEAD(EXPRESION,<OFFSET>,<DEFAULT>)
SQL> select year,region,profit ,lead (profit,1) over (order by year)  as next_year_exp from test;
YEAR REGION      PROFIT NEXT_YEAR_EXP
—- ——- ———- ————-
2003 West            88            88
2003 West            88           101
2003 Central        101           100
2003 Central        100           102
2003 East           102            77
2004 West            77           103
2004 East           103            89
2004 West            89

Lag函数为Lag(exp,N,defval)，defval是当该函数无值可用的情况下返回的值。Lead函数的用法类似。
Lead和Lag函数也可以使用分组，以下是使用region分组的例子：
SQL> select year,region,profit , lag (profit,1,0) over (PARTITION BY region order by year)    as 51xit_exp from test;
YEAR REGION      PROFIT 51xit_exp
—- ——- ———- ————-
2003 Central        101             0
2003 Central        100           101
2003 East           102             0
2004 East           103           102
2003 West            88             0
2003 West            88            88
2004 West            77            88
2004 West            89            77

一SQL问题解答：
问题：
CREATE   TABLE  ldy_temp_2
(
分局    VARCHAR(255),
派出所    VARCHAR(255) ,
证件类型    VARCHAR(255) ,
证件号码    VARCHAR(255) ,
姓名    VARCHAR(255) ,
性别    VARCHAR(255) ,
行政区划    VARCHAR(255) ,
旅馆名称    VARCHAR(255) ,
旅馆地址    VARCHAR(255) ,
房间号    VARCHAR(255) ,
入住时间    VARCHAR(255) ,
col012    VARCHAR(255)
);

INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES(‘1234′,’ZHANGTAO’,’A’,’20100506′);
INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES(‘1234′,’ZHANGTAO’,’A’,’20100507′);
INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES(‘1234′,’ZHANGTAO’,’B’,’20100508′)；

INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES(‘1234′,’ZHANGTAO’,’A’,’20100509′);
INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES(‘1235′,’ZZZZ’,’A’,’20100506′);
INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES(‘1235′,’ZZZZ’,’B’,’20100507′);
INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES(‘1235′,’ZZZZ’,’A’,’20100508′);
INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES(‘1235′,’ZZZZ’,’B’,’20100509′);

建表语句和测试数据已经给出  请问  如何查找相邻两次入住旅馆名称不同的人；也就是说 一个人的证件号码是123的话 那么这个人的信息按照入住时间排序后  相邻两条数据的旅馆名称不能一样 。

解答：
with temp_a as
(select
t.证件号码,
t.旅馆名称,
t.入住时间,
lag(t.旅馆名称) over (partition by t.证件号码 order by t.入住时间) as lagname
from ldy_temp_2 t)
select 证件号码,姓名,旅馆名称,入住时间
from ldy_temp_2 a
where a.证件号码 not in (select b.证件号码 from temp_a b where  b.旅馆名称=b.lagname)

性能调优工具05–SQL Profiles

Posted on by
SQL Profiles

  1. SQL Profiles介绍:
    1. SQL Profiles是在10g中被引进的新特性,主要是通过DBMS_SQLTUNE包或者EM来管理,它是AST(Automatic SQL Tuning)过程的一部分;也在10g版本中使用较多,11g中更多使用SPM;
    2. Automatic SQL Tuning:
      1. 因为缺少必要的信息,查询优化器对sql语句可能会产生不准确的评估(比如返回的记录数等),从而导致生成一个较差的执行计划;传统的处理方法是手动添加hints来改变执行计划,但是对于打包的应用程序,无法修改相应的sql语句,所以只能等应用程序提供商来解决这个问题;
      2. Automatic SQL Tuning使用SQL Profile的特性解决了这个问题;Automatic Tuning Optimizer会为每条sql语句创建一个包含辅助信息(主要通过动态采用和部分执行技术收集附加信息)的SQL Profile;查询优化器可以计算出更精确的cardinality/selectivity/cost的评估,从而生成更优的执行计划;
      3. 在产生SQL Profile期间,Automatic Tuning Optimizer也会为了与优化器参数匹配从而执行SQL语句的历史信息,比如把optimizer_mode参数从all_rows修改到first_rows;
    3. SQL Profile:
      1. SQL Profile是存储在数据字典中的一系列的信息(主要是包括在AST过程中针对较差执行计划的一些修正信息),它可以使查询优化器针对某条sql生成更优的执行计划;
      2. SQL Profile并不包含独立的执行计划,只是优化器在选择执行计划时除了参考数据库配置/绑定变量值/优化器统计信息/数据集等信息之外还会参考SQL Profile中的附加信息;
      3. 一段时间后,SQL Profile的内容可能会过期,需要运行AST重新生成SQL Profile;
      4. tips:SQL Profile并不会像Stored Outlines一样固定SQL语句的执行计划;即便拥有相同SQL Profile,由于表的增长或者索引的创建删除,执行计划也会改变;
    4. 如何控制SQL Profile的范围:
      1. SQL Profile的范围由category属性控制,它决定了哪些用户会话可以应用这些profile;
      2. 可以通过dba_sql_profiles字典来查看(SELECT NAME, category FROM dba_sql_profiles);
      3. 默认情况下,所有的profiles都属于DEFAULT分类,表示所有的初始化参数sqltune_category=DEFAULT(这也是默认值)的用户会话都可使用这些profile;
      4. 可以通过修改sqltune_category参数来控制profile使用的范围,从而可以在发布之前进行测试;
    5. 可以使用SQL Profile的语句:
      1. SELECT语句;
      2. UDPATE语句;
      3. INSERT INTO SELECT语句;
      4. DELETE语句;
      5. CTAS语句;
      6. MERGE语句(包含UPDATE或者INSERT操作);
    6. Sql Profiles是对OUTLINES的增强,并且具有自己的特点:
      1. SQL Profiles更容易生成,更改和控制;
      2. SQL Profiles对SQL语句的支持更广泛;
    7. 使用SQL Profiles的目的:
      1. 稳定或者”锁定”SQL的执行计划;
      2. 在不能修改应用程序中SQL的情况下,使SQL按照指定的执行计划运行;
  2. 管理SQL Profiles:
    1. 首先用户需要有CREATE ANY SQL_PROFILE, DROP ANY SQL_PROFILE, ALTER ANY SQL_PROFILE权限;
    2. 从STA优化结果中接受一个Profile:DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name,object_id,name,description,category,task_owner,replace,force_match,profile_type),例子<<如何使用SQL Tuning Advisor来生成SQL Profile,及SQL Profile原理>>;
    3. 修改Profile:DBMS_SQLTUNE.ALTER_SQL_PROFILE (name,attribute_name,value);
    4. 删除Profile:DBMS_SQLTUNE.DROP_SQL_PROFILE (name,ignore);
    5. SQL Profile的迁移,例子<<How to Move SQL Profiles from One Database to Another>>;
    6. 使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE过程手工创建SQL Profiles,这种方法创建的SQL Profiles和Outlines一样,能够稳定SQL的执行计划,例子;
      1. 稳定现有的SQL的执行计划;
      2. 在不能修改SQL的情况下改变并固定SQL的执行计划,即使原来的SQL使用了hints;
———————- 如何使用SQL Tuning Advisor来生成SQL Profile,及SQL Profile原理 ———————-
— 1.实验环境;
SQL> SELECT * FROM v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production

— 2.创建测试表;
conn / as sysdba
CREATE TABLE t1 AS SELECT object_id, object_name FROM dba_objects WHERE rownum <= 50000;
CREATE TABLE t2 AS SELECT * FROM dba_objects;
CREATE INDEX idx_t2_oid ON t2(object_id);

BEGIN
dbms_stats.gather_table_stats(USER, ‘T1’, cascade => TRUE, method_opt => ‘for all columns size 1’);
dbms_stats.gather_table_stats(USER, ‘T2’, cascade => TRUE, method_opt => ‘for all columns size 1’);
END;
/

— 3.查看SQL的执行计划和统计信息,并选择不同的访问路径;

1.查看SQL的执行计划;
     1.从执行结果发现:这条sql在两个表上都是全表扫描,在T1上因为有like条件,所以很正常;在T2表上应该有其它的访问路径;
     2.在执行计划ID=2那一行,CBO评估表T1 LIKE ‘%T1%’的返回结果为2500行,即T1表总记录的5%(类似于LIKE等谓词的默认选择率为5%);如果采用两个表采用INDEX RANGE SCAN + NESTED LOOPS连接的话,CBO估算的成本会高于FULL TABLE SCAN + HASH JOIN;

SQL> SET AUTOT ON;
SQL> SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id;

……
26 rows selected.

Execution Plan
———————————————————-
Plan hash value: 1838229974

—————————————————————————
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT   |      |  2500 |    97K|   212   (3)| 00:00:03 |
|*  1 |  HASH JOIN         |      |  2500 |    97K|   212   (3)| 00:00:03 |
|*  2 |   TABLE ACCESS FULL| T1   |  2500 | 72500 |    55   (4)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 50350 |   540K|   155   (2)| 00:00:02 |
—————————————————————————

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

1 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)
2 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)

Statistics
———————————————————-
304  recursive calls
0  db block gets
974  consistent gets
0  physical reads
0  redo size
1402  bytes sent via SQL*Net to client
396  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
5  sorts (memory)
0  sorts (disk)
26  rows processed

2.加hints改变sql的访问路径查看成本;
     1.从执行计划发现:CBO评估的成本为5061,远远高于原来的212;
     2.从统计信息发现:加了hint之后的逻辑读只有288,远远低于原来的974;
     3.说明由于ORACLE过高的估计了T1表返回的结果集行数,进而过高的估计了NEST LOOP的成本,导致没有选择最优的执行计划;
     4.tips:也可以使用EXPLAIN PLAN FOR然后SELECT * FROM TABLE(DBMS_XPLAN.display())的方法查看执行计划;
SQL> SET AUTO ON;
SQL> SELECT /*+ use_nl(t1 t2) index(t2) */ t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id;

26 rows selected.

Execution Plan
———————————————————-
Plan hash value: 1164754026

——————————————————————————–
———-

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |

——————————————————————————–
———-

|   0 | SELECT STATEMENT            |            |  2500 |    97K|  5061   (1)|
00:01:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T2         |     1 |    11 |     2   (0)|
00:00:01 |

|   2 |   NESTED LOOPS              |            |  2500 |    97K|  5061   (1)|
00:01:01 |

|*  3 |    TABLE ACCESS FULL        | T1         |  2500 | 72500 |    55   (4)|
00:00:01 |

|*  4 |    INDEX RANGE SCAN         | IDX_T2_OID |     1 |       |     1   (0)|
00:00:01 |

——————————————————————————–
———-

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

3 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)
4 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)

Statistics
———————————————————-
1  recursive calls
0  db block gets
288  consistent gets
0  physical reads
0  redo size
1402  bytes sent via SQL*Net to client
396  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
26  rows processed

— 4.使用STA调优这条sql;
1.查看这条sql的sql_id为’cy6x06rhrt54x’;
可以从v$sql或者dba_hist_sqltext视图中拿到sql_id;
2.可以使用工具包sqltrpt.sql来调优;
Tune the sql
~~~~~~~~~~~~

GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name                  : TASK_62
Tuning Task Owner                 : SYS
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1800
Completion Status                 : COMPLETED
Started at                        : 07/26/2014 10:45:03
Completed at                      : 07/26/2014 10:45:03
Number of SQL Profile Findings    : 1

——————————————————————————-
Schema Name: SYS

SQL ID     : cy6x06rhrt54x
SQL Text   : SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE
‘%T1%’ AND t1.object_id = t2.object_id

——————————————————————————-
FINDINGS SECTION (1 finding)
——————————————————————————-

1- SQL Profile Finding (see explain plans section below)
——————————————————–
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 49.32%)

——————————————
– Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => ‘TASK_62’, replace
=> TRUE);

——————————————————————————-
EXPLAIN PLANS SECTION
——————————————————————————-

1- Original With Adjusted Cost
——————————
Plan hash value: 1838229974

—————————————————————————
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT   |      |    26 |  1040 |   212   (3)| 00:00:03 |
|*  1 |  HASH JOIN         |      |    26 |  1040 |   212   (3)| 00:00:03 |
|*  2 |   TABLE ACCESS FULL| T1   |    26 |   754 |    55   (4)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 50350 |   540K|   155   (2)| 00:00:02 |
—————————————————————————

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

1 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)

2 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)

2- Using SQL Profile
——————–
Plan hash value: 1164754026

——————————————————————————–
———-
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |
——————————————————————————–
———-
|   0 | SELECT STATEMENT            |            |    26 |  1040 |   107   (2)|

00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2         |     1 |    11 |     2   (0)|
00:00:01 |
|   2 |   NESTED LOOPS              |            |    26 |  1040 |   107   (2)|
00:00:02 |
|*  3 |    TABLE ACCESS FULL        | T1         |    26 |   754 |    55   (4)|
00:00:01 |
|*  4 |    INDEX RANGE SCAN         | IDX_T2_OID |     1 |       |     1   (0)|
00:00:01 |
——————————————————————————–
———-

Predicate Information (identified by operation id):

—————————————————

3 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)
4 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)

3.从结果发现STA找到了最优的执行计划,T1表上的条件过滤后的结果集记录数为26,非常准确,所以可以选择接受这个SQL Profile;

SQL> EXECUTE dbms_sqltune.accept_sql_profile(task_name => ‘TASK_62’, REPLACE=> TRUE);

PL/SQL procedure successfully completed.

— 5.查看SQL Profile做了什么;
1.查看SQL Profile信息;
SQL>  SELECT NAME, CATEGORY, signature, sql_text FROM dba_sql_profiles;

NAME                           CATEGORY                        SIGNATURE
—————————— —————————— ———-
SQL_TEXT
——————————————————————————–
SYS_SQLPROF_0151dc24f6338000   DEFAULT                        8.9755E+18
SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object
_id = t2.object_id

2.在sys.sqlprof$attr数据字典(11g中为SQLOBJ$,SQLOBJ$AUXDATA,SQLOBJ$DATA字典)中,有signature和attr_val两列重要信息;
     1.在10g之后的v$sql视图中有两列exact_matching_signature,force_matching_signature信息,用来表示sql的特征,可以用于查找没有使用绑定变量的sql语句;
     2.对于dbms_sqltune.accept_sql_profile过程中,如果force_match=>true的话表示dba_sql_profile中的signature由sql的force_matching_signature而来,否则就是exact_matching_signature;
     3.这也验证了SQL Profile比outlines好的一点,因为它也支持没有使用绑定变量的SQL;
     4.attr_val实际上就是一些hints,和outline本质是一样的,只是SQL Profile中的hints没有指定sql使用的索引和表连接的方式及顺序;这里仅仅提供了一些辅助信息,即评估了表返回记录的行数,与原始的评估返回的行数的放大/缩小倍数;
     5.计算0.0104*2500=26,就告诉CBO,T1表经过谓词过滤返回行数应该为评估的0.0104倍;
     6.这里也验证了SQL Profile并不会锁定SQL的执行计划,只是提供更多,更准确的统计信息给CBO参考;
SQL> SELECT * FROM sys.sqlprof$attr;

SIGNATURE   CATEGORY      ATTR#   ATTR_VAL
———– ——– ———-  ——————————————————————————–

8.9755E+18  DEFAULT           1  OPT_ESTIMATE(@”SEL$1″, TABLE, “T1″@”SEL$1”, SCALE_ROWS=0.0104)

3.修改谓词条件?如果执行sql:SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T2%’ AND t1.object_id = t2.object_id;
1.在10.2.0.1版本中:则还是会走没有使用SQL Profile的执行计划;
2.也许在其它的版本中会使用SQL Profile,但是评估的T1表返回的记录数应该和LIKE ‘%T1%’一样,以为这个SQL Profile是为这个条件生成的;
4.修改表的统计信息,进一步验证;
     1.将T1表的统计信息修改为500W,则Oracle会评估返回500W*5%*0.0104=2600行记录;执行计划变为FULL TABLE SCAN + HASH JOIN;
     2.虽然SQL Profile起了所用,但是并没有锁定执行计划;
SQL> EXEC dbms_stats.set_table_stats(‘SYS’, ‘T1’, numrows=>5000000);
SQL> EXPLAIN PLAN FOR SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id;

Explained.

SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1838229974

—————————————————————————
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT   |      |  2600 |   101K|   401  (49)| 00:00:05 |
|*  1 |  HASH JOIN         |      |  2600 |   101K|   401  (49)| 00:00:05 |
|*  2 |   TABLE ACCESS FULL| T1   |  2600 | 75400 |   245  (79)| 00:00:03 |
|   3 |   TABLE ACCESS FULL| T2   | 50350 |   540K|   155   (2)| 00:00:02 |
—————————————————————————

PLAN_TABLE_OUTPUT
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————

1 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)
2 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)

Note
—–
– SQL profile “SYS_SQLPROF_0151dc24f6338000” used for this statement

———————- 如何使用SQL Tuning Advisor来生成SQL Profile,及SQL Profile原理 ———————-
———————- How to Move SQL Profiles from One Database to Another ———————-
— 1.实验环境;
1.源端数据库信息;
SQL> SELECT * FROM v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production

2.目标端数据库信息;
SQL> SELECT * FROM v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
PL/SQL Release 11.2.0.3.0 – Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production

— 2.准备测试数据,在源端和目标端分别创建测试表,结果都是走全表扫描;
SQL> conn / as sysdba
SQL> ALTER USER hr IDENTIFIED BY oracle ACCOUNT UNLOCK;
SQL> conn hr/oracle
SQL> CREATE TABLE emp AS SELECT * FROM employees;
SQL> ALTER TABLE emp ADD CONSTRAINT PK_EMP_ID PRIMARY KEY (EMPLOYEE_ID) USING INDEX;
SQL> SET AUTOT ON
SQL> SELECT /*+ NO_INDEX(e pk_emp_id) */ employee_id, first_name FROM emp e WHERE employee_id=198;

EMPLOYEE_ID FIRST_NAME
———– ——————–
198 Donald

Execution Plan
———————————————————-
Plan hash value: 3956160932

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |     4 |   100 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     4 |   100 |     3   (0)| 00:00:01 |
————————————————————————–

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

1 – filter(“EMPLOYEE_ID”=198)

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

— 3.在源端使用STA进行SQL调优,然后并使用给出的建议生成一个SQL Profile;
DECLARE
my_sta_name        VARCHAR2(30);
my_sqltext         CLOB;
BEGIN
my_sqltext  := ‘SELECT /*+ NO_INDEX(e pk_emp_id) */ employee_id, first_name FROM emp e WHERE employee_id=198’;
my_sta_name := dbms_sqltune.create_tuning_task(sql_text => my_sqltext,
user_name => ‘HR’,
scope => ‘COMPREHENSIVE’,
time_limit => 60,
task_name => ‘my_sql_tuning_task’);
dbms_sqltune.execute_tuning_task(task_name => my_sta_name);
dbms_sqltune.accept_sql_profile(task_name => my_sta_name, NAME => ‘my_sql_profile’);
END;
/
— 4.在源端查看相应的改变;
1.查看生成的profile信息;
SELECT * FROM dba_sql_profiles WHERE name = ‘my_sql_profile’;
2.查看sql的执行计划改变;
SQL> SELECT /*+ NO_INDEX(e pk_emp_id) */ employee_id, first_name FROM emp e WHERE employee_id=198;

EMPLOYEE_ID FIRST_NAME
———– ——————–
198 Donald

Execution Plan
———————————————————-
Plan hash value: 2864363232

——————————————————————————–
———

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
ime     |

——————————————————————————–
———

|   0 | SELECT STATEMENT            |           |     1 |     9 |     1   (0)| 0
0:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP       |     1 |     9 |     1   (0)| 0
0:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | PK_EMP_ID |     1 |       |     0   (0)| 0
0:00:01 |

——————————————————————————–
———

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

2 – access(“EMPLOYEE_ID”=198)

Note
—–
– SQL profile “my_sql_profile” used for this statement

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

— 5.在源端打包SQL Profiles并导出;
1.打包SQL Profiles(表名和schema名严格区分大小写);
BEGIN
dbms_sqltune.create_stgtab_sqlprof(table_name => ‘TB_SQLP_STAGE’, schema_name => ‘HR’);
dbms_sqltune.pack_stgtab_sqlprof(profile_name => ‘my_sql_profile’,
staging_table_name => ‘TB_SQLP_STAGE’,
staging_schema_owner => ‘HR’);
END;
/
2.导出对应的stage table;
[oracle@singleton10g ~]$ expdp hr/oracle directory=data_pump_dir dumpfile=sqlp.dmp tables=TB_SQLP_STAGE;

Export: Release 10.2.0.1.0 – Production on Friday, 25 July, 2014 11:49:29

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Starting “HR”.”SYS_EXPORT_TABLE_01″:  hr/******** directory=data_pump_dir dumpfile=sqlp.dmp tables=TB_SQLP_STAGE
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “HR”.”TB_SQLP_STAGE”                        9.257 KB       1 rows
Master table “HR”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/ORCL/dpdump/sqlp.dmp

Job “HR”.”SYS_EXPORT_TABLE_01″ successfully completed at 11:49:33
3.把dump文件放到目标端;
— 6.在目标端导入并解包SQL Profiles;
1.导入对应的stage table;
[oracle@singleton11g ~]$ impdp hr/oracle directory=data_pump_dir dumpfile=sqlp.dmp tables=TB_SQLP_STAGE

Import: Release 11.2.0.3.0 – Production on Fri Jul 25 11:52:37 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “HR”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “HR”.”SYS_IMPORT_TABLE_01″:  hr/******** directory=data_pump_dir dumpfile=sqlp.dmp tables=TB_SQLP_STAGE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “HR”.”TB_SQLP_STAGE”                        9.257 KB       1 rows
Job “HR”.”SYS_IMPORT_TABLE_01″ successfully completed at 11:53:05

2.解包SQL Profiles(对应的SQL Profile被添加到了数据字典中,可以通过dba_sql_profiles查看);
BEGIN
dbms_sqltune.unpack_stgtab_sqlprof(profile_name => ‘my_sql_profile’,
REPLACE => TRUE,
staging_table_name => ‘TB_SQLP_STAGE’,
staging_schema_owner => ‘HR’);
END;
/
— 7.在目标端查看对应的执行计划;
SQL> SELECT /*+ NO_INDEX(e pk_emp_id) */ employee_id, first_name FROM emp e WHERE employee_id=198;

EMPLOYEE_ID FIRST_NAME
———– ——————–
198 Donald

Execution Plan
———————————————————-
Plan hash value: 2864363232

——————————————————————————–
———

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
ime     |

——————————————————————————–
———

|   0 | SELECT STATEMENT            |           |     1 |     9 |     1   (0)| 0
0:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP       |     1 |     9 |     1   (0)| 0
0:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | PK_EMP_ID |     1 |       |     0   (0)| 0
0:00:01 |

——————————————————————————–
———

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

2 – access(“EMPLOYEE_ID”=198)

Note
—–
– SQL profile “my_sql_profile” used for this statement

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

———————- How to Move SQL Profiles from One Database to Another ———————-
———————- 使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE过程手工创建SQL Profiles ———————-
— 1.实验环境;
SQL> SELECT * FROM v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production

— 2.相关介绍;
1.存储过程:在帮助文档中并没有介绍,但是可以通过查看包体的信息;
SQL> desc dbms_sqltune
PROCEDURE IMPORT_SQL_PROFILE
Argument Name                  Type                    In/Out Default?
—————————— ———————– —— ——–
SQL_TEXT                       CLOB                    IN
PROFILE                        SQLPROF_ATTR            IN
NAME                           VARCHAR2                IN     DEFAULT
DESCRIPTION                    VARCHAR2                IN     DEFAULT
CATEGORY                       VARCHAR2                IN     DEFAULT
VALIDATE                       BOOLEAN                 IN     DEFAULT
REPLACE                        BOOLEAN                 IN     DEFAULT
FORCE_MATCH                    BOOLEAN                 IN     DEFAULT
2.SYS.SQLPROF_ATTR是一个VARCHAR2的集合类型;
SELECT * FROM dba_source WHERE owner = ‘SYS’ AND NAME = ‘SQLPROF_ATTR’;
TYPE sqlprof_attr AS VARRAY(2000) of VARCHAR2(500)
— 3.还原环境;
BEGIN
dbms_sqltune.drop_sql_profile(‘SYS_SQLPROF_0151dc24f6338000’);
END;
/
BEGIN
dbms_stats.set_table_stats(‘SYS’, ‘T1’, numrows => 5000);
END;
/
— 4.稳定现有的SQL的执行计划;
1.查看sql的信息;
SQL>SELECT sql_id, plan_hash_value, sql_text FROM v$sql;

SQL_ID        PLAN_HASH_VALUE
————- —————
SQL_TEXT
——————————————————————————–
cy6x06rhrt54x      1838229974
SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object
_id = t2.object_id

2.10g开始,v$sql_plan中包括了SQL语句的outlines数据,即稳定执行计划的hints;
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(‘cy6x06rhrt54x’,NULL,’outline’));

PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID  cy6x06rhrt54x, child number 0
————————————-
SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND
t1.object_id = t2.object_id

Plan hash value: 1838229974

—————————————————————————
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT   |      |       |       |   212 (100)|          |

PLAN_TABLE_OUTPUT
——————————————————————————–
|*  1 |  HASH JOIN         |      |  2500 |    97K|   212   (3)| 00:00:03 |
|*  2 |   TABLE ACCESS FULL| T1   |  2500 | 72500 |    55   (4)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 50350 |   540K|   155   (2)| 00:00:02 |
—————————————————————————

Outline Data
————-

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘10.2.0.1’)
ALL_ROWS
OUTLINE_LEAF(@”SEL$1″)
FULL(@”SEL$1″ “T1″@”SEL$1″)
FULL(@”SEL$1” “T2″@”SEL$1″)
LEADING(@”SEL$1” “T1″@”SEL$1” “T2″@”SEL$1″)
USE_HASH(@”SEL$1” “T2″@”SEL$1”)
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):

1 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)
2 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)

3.SQLT工具中的coe_xfr_sql_profile.sql脚本:这个脚本可以用于从shared pool,awr中提取指定的SQL ID的Outline Data并创建SQL Profile;
     1.脚本需要输入sql的sql_id和plan_hash_value,然后会生成一个coe_xfr_sql_profile_${sql_id}_${plan_hash_value}.sql的脚本;
     2.脚本中的force_match参数默认是FALSE,可以修改为TRUE,也可以修改脚本其它的内容;
SQL>@/home/oracle/coe_xfr_sql_profile.sql cy6x06rhrt54x 1838229974

Parameter 1:
SQL_ID (required)

PLAN_HASH_VALUE AVG_ET_SECS
————— ———–
1838229974        .087
1164754026

Parameter 2:
PLAN_HASH_VALUE (required)

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : “cy6x06rhrt54x”
PLAN_HASH_VALUE: “1838229974”

SQL>BEGIN
2    IF :sql_text IS NULL THEN
3      RAISE_APPLICATION_ERROR(-20100, ‘SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).’);
4    END IF;
5  END;
6  /
SQL>SET TERM OFF;
SQL>BEGIN
2    IF :other_xml IS NULL THEN
3      RAISE_APPLICATION_ERROR(-20101, ‘PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).’);
4    END IF;
5  END;
6  /
SQL>SET TERM OFF;

Execute coe_xfr_sql_profile_cy6x06rhrt54x_1838229974.sql
on TARGET system in order to create a custom SQL Profile
with plan 1838229974 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

SQL>@coe_xfr_sql_profile_cy6x06rhrt54x_1838229974.sql
SQL>DECLARE
2  sql_txt CLOB;
3  h       SYS.SQLPROF_ATTR;
4  PROCEDURE wa (p_line IN VARCHAR2) IS
5  BEGIN
6  DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
7  END wa;
8  BEGIN
9  DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
10  DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
11  — SQL Text pieces below do not have to be of same length.
12  — So if you edit SQL Text (i.e. removing temporary Hints),
13  — there is no need to edit or re-align unmodified pieces.
14  wa(q'[SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1]’);
15  wa(q'[%’ AND t1.object_id = t2.object_id
16   ]’);
17  DBMS_LOB.CLOSE(sql_txt);
18  h := SYS.SQLPROF_ATTR(
19  q'[BEGIN_OUTLINE_DATA]’,
20  q'[IGNORE_OPTIM_EMBEDDED_HINTS]’,
21  q'[OPTIMIZER_FEATURES_ENABLE(‘10.2.0.1′)]’,
22  q'[ALL_ROWS]’,
23  q'[OUTLINE_LEAF(@”SEL$1″)]’,
24  q'[FULL(@”SEL$1″ “T1″@”SEL$1″)]’,
25  q'[FULL(@”SEL$1” “T2″@”SEL$1″)]’,
26  q'[LEADING(@”SEL$1” “T1″@”SEL$1” “T2″@”SEL$1″)]’,
27  q'[USE_HASH(@”SEL$1” “T2″@”SEL$1″)]’,
28  q'[END_OUTLINE_DATA]’);
29  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
30  :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
31  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
32  sql_text    => sql_txt,
33  profile     => h,
34  name        => ‘coe_cy6x06rhrt54x_1838229974’,
35  description => ‘coe cy6x06rhrt54x 1838229974 ‘||:signature||’ ‘||:signaturef||”,
36  category    => ‘DEFAULT’,
37  validate    => TRUE,
38  replace     => TRUE,
39  force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
40  DBMS_LOB.FREETEMPORARY(sql_txt);
41  END;
42  /

PL/SQL procedure successfully completed.

SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;

SIGNATURE
———————
8975541025552400288

SIGNATUREF
———————
3960696072677096522

… manual custom SQL Profile has been created

COE_XFR_SQL_PROFILE_cy6x06rhrt54x_1838229974 completed

— 5.在不能修改SQL的情况下改变并固定SQL的执行计划,即使原来的SQL使用了hints;
1.一般遇到sql不能修改,或者sql使用了错误hints的情况下有三种办法修改sql的执行计划;
     1.调整统计信息:不可靠,使用比较复杂,而且统计信息会自动收集;
     2.使用outline:比较复杂;
     3.使用SQL Profiles:本质和OUTLINE是一样的;
2.一般的工作流;
     1.取得原始的SQL文本,包括sql_id;
     2.构造一个与原始SQL在逻辑上,结构上完全相同的SQL(逻辑和结构相同即可,即sql解析的用户,引用对象的schema,甚至是谓词条件可以不同);
     3.执行我们构造的SQL,生成Outline Data;
     4.使用原始SQL的文本和构造的SQL的Outline Data创建SQL Profile;
3.构建SQL;
     1.FULL TABLE SCAN + HASH JOIN的方式:SELECT /*+ sql_profile full(t1) full(t2) use_hash(t1 t2) */ t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id;
2.INDEX RANGE SCAN + NESTED LOOP的方式:SELECT /*+ sql_profile index(t1) use_nl(t1 t2) */  t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id;
     3.查看sql_id和plan_hash_value;
SQL>SELECT sql_id, plan_hash_value, sql_text FROM v$sql WHERE sql_text LIKE ‘%sql_profile%’ AND sql_id IN (‘8cukn1u7dxssh’,’6csfwp8k7qu49′)
2  ;

SQL_ID        PLAN_HASH_VALUE
————- —————
SQL_TEXT
——————————————————————————–
8cukn1u7dxssh      1838229974
SELECT /*+ sql_profile full(t1) full(t2) use_hash(t1 t2) */ t1.*, t2.owner FROM
t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id

6csfwp8k7qu49      1164754026
SELECT /*+ sql_profile index(t1) use_nl(t1 t2) */  t1.*, t2.owner FROM t1, t2 WH
ERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id

4.分别使用coe_xfr_sql_profile.sql脚本生产两个sql的sql_profile创建脚本;
SQL>@/home/oracle/coe_xfr_sql_profile.sql 8cukn1u7dxssh 1838229974 — coe_xfr_sql_profile_8cukn1u7dxssh_1838229974.sql
SQL>@/home/oracle/coe_xfr_sql_profile.sql 6csfwp8k7qu49 1164754026 — coe_xfr_sql_profile_6csfwp8k7qu49_1164754026.sql
5.然后把coe_xfr_sql_profile_6csfwp8k7qu49_1164754026.sql中构建SYS.SQLPROF_ATTR的部分和force_match => TRUE地方替换掉coe_xfr_sql_profile_8cukn1u7dxssh_1838229974.sql脚本中相应内容;
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]’,
q'[IGNORE_OPTIM_EMBEDDED_HINTS]’,
q'[OPTIMIZER_FEATURES_ENABLE(‘10.2.0.1′)]’,
q'[ALL_ROWS]’,
q'[OUTLINE_LEAF(@”SEL$1″)]’,
q'[FULL(@”SEL$1″ “T1″@”SEL$1″)]’,
q'[INDEX(@”SEL$1” “T2″@”SEL$1” (“T2″.”OBJECT_ID”))]’,
q'[LEADING(@”SEL$1″ “T1″@”SEL$1” “T2″@”SEL$1″)]’,
q'[USE_NL(@”SEL$1” “T2″@”SEL$1”)]’,
q'[END_OUTLINE_DATA]’);
6.然后执行,并查看执行计划;
SQL>EXPLAIN PLAN FOR SELECT /*+ sql_profile full(t1) full(t2) use_hash(t1 t2) */ t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id;

Explained.

SQL>SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1164754026

——————————————————————————–
———-

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |

——————————————————————————–
———-

PLAN_TABLE_OUTPUT
——————————————————————————–
|   0 | SELECT STATEMENT            |            |   250 | 10000 |   554   (1)|
00:00:07 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T2         |     1 |    11 |     2   (0)|
00:00:01 |

|   2 |   NESTED LOOPS              |            |   250 | 10000 |   554   (1)|
00:00:07 |

|*  3 |    TABLE ACCESS FULL        | T1         |   250 |  7250 |    53   (0)|
00:00:01 |

PLAN_TABLE_OUTPUT
——————————————————————————–

|*  4 |    INDEX RANGE SCAN         | IDX_T2_OID |     1 |       |     1   (0)|
00:00:01 |

——————————————————————————–
———-

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

PLAN_TABLE_OUTPUT
——————————————————————————–
3 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)
4 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)

Note
—–
– SQL profile “coe_8cukn1u7dxssh_1838229974” used for this statement

— 7.改变条件,再次查看执行计划,发现SQL的执行计划被固定了;
SQL>EXPLAIN PLAN FOR SELECT /*+ sql_profile full(t1) full(t2) use_hash(t1 t2) */ t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T2%‘ AND t1.object_id = t2.object_id;

Explained.

SQL>SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1164754026

——————————————————————————–
———-

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |

——————————————————————————–
———-

PLAN_TABLE_OUTPUT
——————————————————————————–
|   0 | SELECT STATEMENT            |            |   250 | 10000 |   554   (1)|
00:00:07 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T2         |     1 |    11 |     2   (0)|
00:00:01 |

|   2 |   NESTED LOOPS              |            |   250 | 10000 |   554   (1)|
00:00:07 |

|*  3 |    TABLE ACCESS FULL        | T1         |   250 |  7250 |    53   (0)|
00:00:01 |

PLAN_TABLE_OUTPUT
——————————————————————————–

|*  4 |    INDEX RANGE SCAN         | IDX_T2_OID |     1 |       |     1   (0)|
00:00:01 |

——————————————————————————–
———-

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

PLAN_TABLE_OUTPUT
——————————————————————————–
3 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T2%’)
4 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)

Note
—–
– SQL profile “coe_8cukn1u7dxssh_1838229974” used for this statement

———————- 使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE过程手工创建SQL Profiles ———————-

性能调优工具04–SQLT Diagnostic Tool

Posted on by
SQLT Diagnostic Tool
1.SQLT介绍:SQLTXPLAIN, also known as SQLT, is a tool provided by Oracle Server Technologies Center of Expertise – ST CoE. SQLT inputs one SQL statement and outputs a set of diagnostics files. These files are commonly used to diagnose SQL statements performing poorly. SQLT connects to the database and collects execution plans, Cost-based Optimizer CBO statistics, schema objects metadata, performance statistics, configuration parameters, and similar elements that influence the performance of the SQL being analyzed.
2.安全模式:SQLT在安装的过程中会创建两个用户和一个角色,这些用户和角色的名字都是固定的;
     1.SQLTXPLAIN用户:用来管理SQLT repository,SQLT的使用者每次使用SQLT提供的主要方法时都要提供SQLTXPLAIN的密码;SQLTXPLAIN用户被赋予了CREATE SESSION,CREATE TABLE系统权限;
2.SQLTXADMIN用户:用来管理SQLT包含的PL/SQL程序包以及视图,SQLTXADMIN用户处于锁定状态并且由一个随机产生的密码保护;SQLTXADMIN用户被赋予了ADMINISTER SQL MANAGEMENT OBJECT,ADMINISTER SQL TUNING SET,ADVISOR,ALTER SESSION,ANALYZE ANY,SELECT ANY DICTIONARY,SELECT_CATALOG_ROLE系统权限;
3.SQLT_USER_ROLE角色:所有SQLT的使用者在使用SQLT提供的主要方法之前必须被赋予这个角色;SQLT_USER_ROLE角色被赋予了ADVISOR,SELECT_CATALOG_ROLE系统权限;
     4.TIPS:12c在缺省情况下SYS用户不能作为SQLT的用户,因为PL/SQL安全模型改变的原因,需要单独对SQLTADMIN授权(GRANT INHERIT PRIVILEGES ON USER SYS TO SQLTXADMIN);
3.安装:
     1.下载并解压到相应的目录;(可以查看目录下的sqlt_instructions.html安装文档)
     2.安装:conn / as sysdba; SQL> @/tools/sqlt/install/sqcreate.sql;需要提供以下信息:
          1.连接标识符(可选,当安装在一个PDB上时是必须的):一般不输入直接回车;

2.SQLTXPLAIN密码;
3.SQLTXPLAIN默认表空间:它必须具有50MB以上的可用空间;
4.SQLTXPLAIN临时表空间;
5.应用程序用户(可选):指定发出要分析SQL语句的用户(EBS中为APPS,Siebel中为SIEBEL,PeopleSoft中为SYSADM),也可以安装之后再添加用户,必须授予其SQLT_USER_ROLE角色;
6.授权的Oracle Pack(T,D,N):T表示Oracle Tuning,D表示Oracle Diagnostic,或N表示None;如果选择T或D,SQLT可以在它生成的诊断文件中包含授权的内容;默认值为T,如果选择N,SQLT将只安装限定的功能;

     3.卸载:conn / as sysdba; SQL> @/path/sqlt/install/sqdrop.sql;
     4.升级:只需要执行安装的过程即可,如果升级失败,则需要执行卸载然后安装;
     5.tips:
          1.如果安装时没有设置连接标示符,那么可以之后自己设置:EXEC sqltxadmin.sqlt$a.set_sess_param(‘connect_identifier’, ‘@network_name’);
          2.如果安装时没有指定添加用户,那么可以CREATE USER sqlt;GRANT sqlt_user_role TO sqlt;
4.主要方法:
     1.XTRACT方法:
          1.如果知道待分析SQL的SQL_ID或HASH_VALUE,可以使用此方法,否则使用XECUTE;可以在AWR report中找到SQL_ID,在SQL trace中找到HASH_VALUE(在SQL文本上面,通过”hv=”标记进行标识);
2.如果对SQL进行硬分析时将参数STATISTICS_LEVEL设置为ALL,将可以得到重要的性能统计信息(如每步操作的实际行数);也可以通过在SQL中包括以下CBO提示来生成同样有用的性能统计信息:/*+ GATHER_PLAN_STATISTICS */;在11g中,您可以在SQL中包含以下CBO提示以获得增强的诊断信息:/*+ GATHER_PLAN_STATISTICS MONITOR */;
3.使用方法:SQL> @/path/sqlt/run/sqltxtract.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password];
     2.XECUTE方法:
          1.与XTRACT方法相比,此方法提供的信息更为详细;正如名称XECUTE所指示的,它将执行正在分析的SQL,然后生成一组诊断文件;
          2.它的主要缺点是如果正在分析的SQL需要很长时间来执行,那么该方法也要花费很长的时间;根据经验法则,仅当SQL执行少于1小时的情况下,才考虑使用此方法,否则请使用XTRACT;
3.使用此XECUTE方法之前,必须创建一个包含SQL文本的文本文件;如果SQL包括绑定变量,则您的文件必须包含绑定变量声明和赋值;以sqlt/input/sample/script1.sql为例;您的SQL应该包含标记/* ^^unique_id */;
          4.使用方法:SQL> @/path/sqlt/run/sqltxecute.sql /path/scriptname [sqltxplain_password];
     3.XTRXEC方法:
          1.该方法合并了XTRACT和XECUTE的功能;实际上,XTRXEC连续执行了这两种方法;针对所请求SQL语句找到的开销较大的计划,XTRACT阶段将生成一个包含提取的SQL以及绑定声明和赋值的脚本;然后,XTRXEC使用第一阶段创建的脚本执行XECUTE阶段;
2.SQLT根据在内存中生成开销最大的执行计划时窥视到的值,创建脚本的绑定变量的以供XTRACT使用;判断计划的开销大小的标准是基于这个计划的平均执行时间;
3.如果XTRXEC仅执行了第一个阶段(XTRACT)后就输出错误,您可能需要检查在第二阶段(XECUTE)使用的脚本并相应调整绑定变量;使用不常用数据类型时尤其需要进行调整;
          4.使用方法:SQL> @/path/sqlt/run/sqltxtrxec.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password];
     4.XTRSBY方法:
          1.如果需要分析在DataGuard或备用只读数据库上执行的SQL,请使用该方法;您需要知道要分析的SQL的SQL_ID或HASH_VALUE;
2.在主库上创建一个到备库的database link,连接到的用户需要有访问数据字典的权限,通常都是使用有DBA权限的用户;
3.如果对只读数据库中的SQL进行硬分析时将参数STATISTICS_LEVEL设置为ALL,将可以得到重要的性能统计信息(如每个执行计划操作的实际行数);您也可以通过在SQL中包括以下CBO提示来生成同样有用的性能统计信息:/*+ GATHER_PLAN_STATISTICS */;在11g中,您可以在SQL中包含以下CBO提示以获得改进的诊断信息:/*+ GATHER_PLAN_STATISTICS MONITOR */;
          4.使用方法:SQL> @/path/sqlt/run/sqltxtrsby.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password] [DB_LINK];
     5.XPLAIN方法:
          1.该方法是基于EXPLAIN PLAN FOR命令执行的,因此它将无视您的SQL语句引用的绑定变量;仅当无法使用XTRACT或XECUTE时才使用该方法;
2.使用此XPLAIN方法之前,必须创建一个包含SQL文本的文本文件;如果SQL包括绑定变量,您有两个选择:保持SQL文本”不变”,或谨慎使用相同数据类型的字面值替换该绑定;以sqlt/input/sample/sql1.sql为例;
          3.使用方法:SQL> @/path/sqlt/run/sqltxplain.sql /path/scriptname [sqltxplain_password];
     6.XPREXT方法:
          1.假如您想使用XTRACT同时希望禁用一些SQLT的特性使之执行更快,请使用这个方法;脚本sqlt/run/sqltcommon11.sql显示了哪些特性被禁用;
          2.使用方法:SQL> @/path/sqlt/run/sqltxprext.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password];
     7.XPREXC方法:
          1.假如您想使用XECUTE同时希望禁用一些SQLT的特性使之执行更快,请使用这个方法;脚本sqlt/run/sqltcommon11.sql显示了哪些特性被禁用;
          2.使用方法:SQL> @/path/sqlt/run/sqltxprexc.sql /path/scriptname [sqltxplain_password];
     8.TIPS:
          1.XTRACT,XECUTE,XTRXEC,XTRSBY,XPREXT和XPREXC处理绑定变量和会做bind peeking(绑定变量窥视),但是XPLAIN不会;这是因为XPLAIN是基于EXPLAIN PLAN FOR命令执行的,该命令不做bind peeking;因此,如果可能请避免使用XPLAIN;
          2.除了XPLAIN的bind peeking限制外,所有这7种主要方法都可以提供足够的诊断详细信息,对性能较差或产生错误结果集的SQL进行初步评估;如果该SQL仍位于内存中或者AWR中,请使用XTRACT或XTRXEC,其他情况请使用XECUTE;
          3.对于DataGuard或备用只读数据库,请使用XTRSBY;
          4.仅当其他方法都不可行时,再考虑使用XPLAIN;
          5.XPREXT和XPREXC是类似于XTRACT和XECUTE,但为了提高SQLT的性能它们禁了一些SQLT的特性;
5.PROFILE方法:
     1.当性能较差的SQL语句正好有已知的更好的执行计划时,在10g使用该PROFILE方法可以提供一个快速修复;
     2.这个更好的执行计划可以位于相同或不同系统中的内存中,或者位于相同或不同系统的AWR中;换句话说,如果有更好的计划,该方法允许使用自定义SQL Profile “固定”该计划;使用该方法之前,必须对您要为其提取和固定此计划的SQL使用主要方法中的任何一个;
     3.在11g或更高的版本你可以使用SQL Plan Management(SPM)来代替这个方法;
6.可以查看生成文件中的html报告;
7.相关文档:SQLT Diagnostic Tool (Doc ID 215187.1),SQLT 使用指南 (Doc ID 1677588.1(CHN),Doc ID 1614107.1(EN));

性能调优工具03–OUTLINE技术暂时锁定SQL的执行计划

Posted on by
OUTLINE技术暂时锁定SQL的执行计划

  1. Oracle的outline技术和hint技术可以在特殊情况下保证执行计划的稳定,使用outline技术锁定执行计划的场景:
    1. 短时间内无法完成sql的优化任务;
    2. 在CBO模式下,当统计信息出现问题时,导致执行计划出现变化;
    3. 由于数据库的bug导致sql的执行计划出现异常;
    4. 使用第三方的系统,sql语句无法直接修改时;
  2. OUTLINE相关的参数:
    1. CREATE_STORED_OUTLINES:会话中执行的sql语句是否自动创建并存储为OUTLINE,初始化参数;
      1. true:开启自动创建outline的功能,系统会自动指定名称并存储在DEFAULT类别下,如果在DEFAULT类别下已经存在的话不会重复创建;
      2. false:默认值,不开启自动创建功能,推荐不要修改此参数;
      3. category_name:跟true功能一样,只是默认存在category_name类别下;
    2. USE_STORED_OUTLINES:使用公有OUTLINE产生执行计划,不是初始化参数;
      1. true:使优化器使用DEFAULT类别的OUTLINE产生执行计划;
      2. false:默认值,不使用outline;
      3. category_name:是优化器使用category_name类别的outline产生执行计划;
    3. USE_PRIVATE_OUTLINES:使用私有OUTLINE产生执行计划,不是初始化参数;
      1. 参数的含义与USE_STORED_OUTLINES一致;
      2. 限制:只有USE_STORED_OULINES关闭的时候才能打开此参数;
    4. 都可以使用ALTER SYSTEM/ALTER SESSION语法来修改;
  3. 创建OUTLINE的语法解析:
    1. 创建语法:CREATE [OR REPLACE] [PUBLIC|PRIVATE] OUTLINE outln_name [FROM PUBLIC|PRIVATE source_outline] [FOR CATEGORY category_name] [ON statement];
    2. PUBLIC:默认值,创建一个公有的OUTLINE;
    3. PRIVATE:在当前的session创建一个私有的OUTLINE,存在当前的SCHEMA下;要创建私有的OUTLINE,必须要在当前SCHEMA下使用DMBS_OUTLN_EDIT.CREATE_EDIT_TABLES过程创建一个表来存储信息;
    4. FROM子句:从一个现有的outline中创建一个新的outine,如果指定FROM子句就不用指定ON子句了;
    5. FOR CATEGORY子句:指定OUTLINE所属的分类,如果不指定就默认属于DEFAULT分类,如果指定的分类不存在,系统自动创建该分类;
    6. ON子句:要创建OUTLINE的sql语句;
      1. 当有FROM子句时不用指定;
      2. 不能是多路插入语句;
      3. 不能是远程服务器的DML操作;
    7. 修改OUTLINE:ALTER OUTLINE outln_name;
    8. 删除OUTLINE:DROP OUTLINE outln_name;
  4. 相关的包:
    1. DBMS_OUTLN:
      1. CLEAR_USED:清除outline的USED标识;
      2. CREATE_OUTLINE:使用shared pool中的sql生成一个outline;
      3. DROP_BY_CAT:删除某一个分类下的所有outline;
      4. DROP_UNUSED:删除从来没有被使用过的outline;
      5. UPDATE_BY_CAT:修改一个outline的分类;
      6. UPDATE_SIGNATURES:修改outline的signature到当前的版本;
    2. DBMS_OUTLN_EDIT:
      1. CHANGE_JOIN_POS:修改执行计划中步骤的顺序;
      2. CREATE_EDIT_TABLES:在当前schema下创建一个表用来保存私有的outline;
      3. DROP_EDIT_TABLES:删除表;
  5. 相关的视图:
    1. DBA_OUTLINE:所有的outline信息,其中USED表示此outline是否被使用过,SIGNATURE是sql语句的唯一标示符;
    2. DBA_OUTLINE_HINTS:所有outline的执行计划;
    3. 与OUTLINE相关的数据都保存了OUTLN用户下的对象中了:
      1. ALTER USER outln IDENTIFIED BY outln ACCOUNT UNLOCK;
      2. outln.ol$:outline的信息;
      3. outln.ol$hints:保存执行计划;
      4. outln.ol$nodes:OUTLINE节点信息;
  6. 创建outline;
  7. 使用outline;
  8. 清除outline:
    1. 可以使用DMBS_OUTLN包来实现删除某个分类的outline;
    2. 使用drop outline语法删除单个个outline;
  9. 把一个环境中的outline应用于其它环境:使用expdp导出当前环境中outln schema,然后impdp导入到其它环境即可;
  10. 如果想要创建一个使用变量的outline,可以在创建outline时使用绑定变量,例子;
  11. 关于HINTS:
    1. 最后才使用的方法;
    2. hints是硬编码;
    3. Hints apply to only the statement block in which they appear:只能用在第一个sql上,如果有子查询,要单独使用hints;
    4. 建议使用表的别名;
    5. HINTS写错的话,会当成注释;
————————– 创建OUTLINE ————————–
— 1.在hr用户下创建测试表;
CREATE TABLE tbobjects AS SELECT * FROM dba_objects;
— 2.创建一个outline,并属于CATE分类;
CREATE OUTLINE ol_objects FOR CATEGORY cate
ON SELECT * FROM tbobjects WHERE object_id = 100;
— 3.查看outln.ol$hints中保存的执行计划;
SELECT hint_text FROM outln.ol$hints
WHERE ol_name = ‘OL_OBJECTS’
ORDER BY HINT#;
— 4.查看真正的执行计划;
————————– 创建OUTLINE ————————–
————————– 使用OUTLINE ————————–
— 1.在表上创建索引,来改变执行计划;
CREATE INDEX IDX_TBOBJECTS_ID ON TBOBJECTS (object_id);
— 2.查看当前实际的执行计划;
— 3.查看outline是否被使用过;
SELECT NAME, category, used, sql_text FROM user_outlines WHERE NAME = ‘OL_OBJECTS’;
— 4.强制会话使用outline中保存的执行计划,要首先设置当前会话的CATEGORY为CATE:ALTER SESSION SET USE_STORED_OUTLINES = CATE;
— 5.查看outline是否被使用;
SELECT NAME, category, used, sql_text FROM user_outlines WHERE NAME = ‘OL_OBJECTS’;
— 6..消除outline对sql执行计划的影响;
方法1.ALTER SESSION SET USE_STORED_OUTLINE = FALSE;
方法2.ALTER OUTLINE ol_objects DISABLE;
————————– 使用OUTLINE ————————–
————————– 创建OUTLINE时使用绑定变量 ————————–
— 1.创建一个outline

CREATE OUTLINE ol_objects FOR CATEGORY cate
ON SELECT * FROM tbobjects WHERE object_id = :object_id;

— 2.设置当前会话使用此outline
ALTER SESSION SET USE_STORED_OUTLINES = CATE;
— 3.使用绑定变量调用;
variable object_id NUMBER;
exec :object_id := 100;
SELECT * FROM tbobjects WHERE object_id = :object_id;
— 4.查看outline是否被使用;
SELECT NAME, category, used, sql_text FROM user_outlines WHERE NAME = ‘OL_OBJECTS’;
————————– 创建OUTLINE时使用绑定变量 ————————–

调优实践04–利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQL语句

Posted on by
利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQL语句
1.10g之后的v$sql视图增加了两列:
1.EXACT_MATCHING_SIGNATURE:Signature calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings.
2.FORCE_MATCHING_SIGNATURE:Signature used when the CURSOR_SHARING parameter is set to FORCE,也就是SQL语句在CURSOR_SHARING=FORCE模式下运行的签名值;
2.控制共享游标的参数:CURSOR_SHARING
1.FORCE:在不改变语义的情况下都使用游标共享,可能使用的并不是最优的执行计划,强制绑定变量,并在左边的列进行to_char转换;
2.EXACT:默认值,只有在SQL语句完全一致的情况下才共享游标;
3.SIMILAR:在不改变语义或者不降低性能的情况下,尽量采用游标共享,一般用于第三方的系统,无法修改源码的情况;
3.执行计划相关内容:
1.v$sql_plan:在Shared Pool中的Library Cache中保存的实际使用的执行计划;
2.查看实际的执行计划:SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(‘sql_id’));
4.在CURSOR_SHARING=EXACT模式下查看使用字面值的sql语句;
4.1创建测试表;
CREATE TABLE tb_sqltuning AS SELECT * FROM dba_objects;
ALTER TABLE TB_SQLTUNING ADD CONSTRAINT PK_sqltuning_sqlid PRIMARY KEY (OBJECT_ID);
4.2清除shared pool内容;
ALTER SYSTEM FLUSH SHARED_POOL;
4.3执行测试sql;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 1;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 2;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 3;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 4;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 5;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 6;
4.4查看sql的信息;
SELECT sql_text,
sql_id,
address,
hash_value,
plan_hash_value,
is_shareable,
executions,
parse_calls,
child_address,
child_number,
force_matching_signature,
exact_matching_signature
FROM v$sql
WHERE sql_text LIKE ‘%SQL_TUNING_LITERAL%’ AND
sql_text NOT LIKE ‘%LIKE%’;
4.5结论;
每一条sql都会进行硬结析并产生不同的执行计划,它们的exact_matching_signature不同,但是force_matching_signature相同;(plan_hash_value相同更说明了,不同的sql产生的执行计划是相同的,需要优化)
5.在CURSOR_SHARING=EXACT模式下查看使用绑定变量的sql语句;
5.1清除shared pool内容;
ALTER SYSTEM FLUSH SHARED_POOL;
5.2执行测试sql;
VARIABLE id NUMBER;
EXEC :id := 1;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
EXEC :id := 2;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
EXEC :id := 3;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
EXEC :id := 4;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
EXEC :id := 5;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
EXEC :id := 6;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
5.3查看sql信息;
SELECT sql_text,
sql_id,
address,
hash_value,
plan_hash_value,
is_shareable,
executions,
parse_calls,
child_address,
child_number,
force_matching_signature,
exact_matching_signature
FROM v$sql
WHERE sql_text LIKE ‘%SQL_TUNING_BIND%’ AND
sql_text NOT LIKE ‘%LIKE%’;
5.4结论;
只进行一次硬结析,之后的语句使用相同的执行计划,它们的exact_matching_signature和force_matching_signature都相同;
硬结析了三次是应为11g里面SPM(Sql Plan Management)的特性,它可以自动学习并选择最优的执行计划,最大的优点是通过避免执行计划的退化,从而保证系统性能的稳定;10g的话第一次产生执行计划就固定了,很可能不是最优的执行计划;
1.第一次执行SQL的时候跟原来一样,什么也不做,只是把执行计划保存在Library Cache中;
2.第二次执行SQL的时候会产生Plan History,而且Plan Baseline为空,把这次的执行计划放到Plan Baseline中,并且固定执行计划;
3.第三次执行SQL的时候,会把产生的执行计划放入到Plan History中,但是不会进入Plan Baseline;
6.修改参数,在CURSOR_SHARING=SIMILAR模式下查看使用字面值的sql语句;
6.1修改参数;
ALTER SYSTEM SET cursor_sharing = SIMILAR
6.2清除shared pool内容;
ALTER SYSTEM FLUSH SHARED_POOL;
6.3执行测试sql;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 1;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 2;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 3;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 4;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 5;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 6;
6.4查看sql的信息;
SELECT sql_text,
sql_id,
address,
hash_value,
plan_hash_value,
is_shareable,
executions,
parse_calls,
child_address,
child_number,
force_matching_signature,
exact_matching_signature
FROM v$sql
WHERE sql_text LIKE ‘%SQL_TUNING_LITERAL%’ AND
sql_text NOT LIKE ‘%LIKE%’;
6.5结论;
跟在EXACT模式下使用绑定变量的结果一致:每一条sql都会进行硬结析并产生不同的执行计划,它们的exact_matching_signature不同,但是force_matching_signature相同;
7.How to Find Literal SQL in Shared Pool;
DECLARE
b_myadr   VARCHAR2(20);
b_myadr1  VARCHAR2(20);
qstring   VARCHAR2(100);
b_anybind NUMBER;

CURSOR my_statement IS
SELECT address FROM v$sql GROUP BY address;
CURSOR getsqlcode IS
SELECT substr(sql_text, 1, 60) FROM v$sql WHERE address = b_myadr;
CURSOR kglcur IS
SELECT kglhdadr
FROM x$kglcursor
WHERE kglhdpar = b_myadr AND
kglhdpar != kglhdadr AND
kglobt09 = 0;
CURSOR isthisliteral IS
SELECT kkscbndt FROM x$kksbv WHERE kglhdadr = b_myadr1;
BEGIN
dbms_output.enable(10000000);
OPEN my_statement;
LOOP
FETCH my_statement
INTO b_myadr;
OPEN kglcur;
FETCH kglcur
INTO b_myadr1;
IF kglcur%FOUND THEN
OPEN isthisliteral;
FETCH isthisliteral
INTO b_anybind;
IF isthisliteral%NOTFOUND THEN
OPEN getsqlcode;
FETCH getsqlcode
INTO qstring;
dbms_output.put_line(‘Literal:’ || qstring || ‘ address: ‘ || b_myadr);
CLOSE getsqlcode;
END IF;
CLOSE isthisliteral;
END IF;
CLOSE kglcur;
EXIT WHEN my_statement%NOTFOUND;
END LOOP;
CLOSE my_statement;
END;

调优实践03–使用SQL_TRACE和10046事件跟踪SQL的执行

Posted on by
使用SQL_TRACE和10046事件跟踪SQL的执行

  1. 当在数据库中启动SQL_TRACE或者设置10046事件之后,Oracle将会启动内核跟踪程序,持续记录会话的相关信息,并写入到相应的trace文件中;跟踪记录的内容包括SQL的解析过程,执行计划,绑定变量的使用和会话中发生的等待事件等;
  2. SQL_TRACE和10046事件介绍:
    1. SQL_TRACE:
      1. 是一个静态的初始化参数,可以设置为TRUE/FALSE,用于开启/关闭SQL TRACE工具默认为FALSE;
      2. 设置SQL_TRACE=TRUE的话可以收集信息用于性能优化(DBMS_SYSTEM包可以实现相同的功能),但是对数据库会产生严重的性能问题,生产环境一定不要打开此参数,如果一定要在全局打开,要做到以下几点:
        1. 保证25%的CPU idle;
        2. 为USER_DUMP_DEST分配足够的空间:ALTER SYSTEM SET max_dump_file_size=UNLIMITED;
        3. 条带化磁盘,减轻IO负担;
        4. 设置timed_statistics打开:ALTER SYSTEM SET timed_statistics=TRUE;
      3. 打开SQL_TRACE功能:
        1. 全局打开:ALTER SYSTEM SET SQL_TRACE=TRUE SCOPE=SPFILE;重启数据库服务,会跟踪所有进程的活动,包括用户进程和后台进程;可以通过跟踪文件的实时变化来分析各个进程之间的协作关系;
        2. Session级别打开:ALTER SESSION SET SQL_TRACE=TRUE/FALSE;通过跟踪当前进程,来发现后台数据库的递归活动,用于研究SQL执行和发现后台错误等;
        3. 如果要跟踪其它用户的进程,可以通过DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION来完成,所需要的sid和serial#参数可以通过v$session视图查看得到;
        4. 如果要针对其它用户的参数进行设置,可以通过DBMS_SYSTEM.SET_INI_PARAM_IN_SESSION过程或者DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION过程来完成;
      4. 其它方式:EXEC DBMS_SESSION.SET_SQL_TRACE(sql_trace=>TRUE/FALSE);
    2. 10046事件:
      1. 是Oracle提供的内部事件,是对SQL_TRACE的增强;
      2. 10046分为四个级别:
        1. level 1:启用标准的sql_trace功能,等于sql_trace(包含了SQL语句,响应事件,服务时间,处理的行数,物理读和写的数目,执行计划以及其它一些额外信息);
        2. level 4:level 1加上绑定值;
        3. level 8:level 1加上等待事件;
        4. level 12:level 1 + level 4 + level 8;
      3. 设置10046事件:
        1. 全局开启:在spfile中添加events=”10046 trace name context forever, level 12″:ALTER SYSTEM SET EVENTS ‘10046 trace name context forever, level 12’;
        2. 全局关闭:ALTER SYSTEM SET EVENTS ‘10046 trace name context off’;
        3. 针对当前session的开启:ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 12’;
        4. 针对当前session的关闭:ALTER SESSION SET EVENTS ‘10046 trace name context off’;
        5. 针对其它用户session的开启:使用DBMS_SYSTEM.SET_EV(si in integer, se in integer, ev in integer, le in integer, nm in varchar):EXEC DBMS_SYSTEM.SET_EV(38,25,10046,12,’HR’);
          1. si:sid;
          2. se:serial#;
          3. ev:event number;
          4. le:level;
          5. nm:username;
          6. 可以通过v$session视图查询:SELECT sid, serial#, username FROM v$session WHERE username IS NOT NULL;
        6. 针对其它用户session的关闭:EXEC DBMS_SYSTEM.SET_EV(38,25,10046,0,’HR’);
    3. 也可以使用oradebug工具或者DBMS_MONITOR包;
    4. 获取跟踪文件的脚本;
    5. 获取当前session设置的参数的脚本;
  3. 实例分析步骤:
    1. 隐式转换与索引失效:
      1. 问题描述:反应前端程序某个功能非常慢;
      2. 首先检查并跟踪数据库进程:SELECT sid, serial#, username FROM v$session WHERE username IS NOT NULL AND username <> ‘SYS’;
      3. 然后对这几个进程开启sql trace:EXEC dbms_system.set_sql_trace_in_session(sid, serial#, TRUE);等待一段时间后关闭:EXEC dbms_system.set_sql_trace_in_session(sid, serial#, FALSE);
      4. 针对产生的trace文件使用tkprof工具进行格式化,然后查看内容;
      5. 一般发生索引失效或者是隐式转换的话就会发现返回少量的数据但是却产生特别多的物理读(也有可能是汇总操作);
      6. 可以查看关键列的索引和索引列的类型:SELECT index_name, table_name, column_name FROM user_ind_columns WHERE table_name = upper(‘tb_name’);DESC tb_name;
      7. 然后可以针对发现的问题做相应的处理,比如创建索引,手动处理隐式转换等问题;
    2. 对数据库进行操作时(如drop table/user)发生错误:
      1. 问题描述:对数据库操作后发生了ORA错误;
      2. 首先在当前的会话开启一个标识符:ALTER SESSION SET TRACEFILE_IDENTIFIER='<TRACEFILE_IDENTIFIER>’;
      3. 然后打开sql trace功能:ALTER SESSION SET SQL_TRACE=TRUE;
      4. 重现错误,即再执行当前操作;
      5. 关闭sql trace功能;
      6. 然后查看trace文件,找到具体的错误并解决;
  4. 10046查看等待事件的例子;
  5. db_file_multiblock_read_count:
    1. 表示全表扫描时,ORACLE一次I/O可以读取的数据库的数据块数,Oracle的一次I/O操作不能跨extent;
    2. 最大值为((max OS I/O size)/db_block_size),一般操作系统一次I/O最大读取1M,db_block_size=8k,所以这个参数最大为128;
    3. 这个参数也会受到SSTIOMAX的参数影响,这是一个内核参数,不能被修改,同时db_file_multiblock_read_count也不能超过db_block_buffer/4;
    4. 在OLTP系统中一般设置为4-16,在DSS系统中可以根据需要设置更大的值;
    5. 增大db_file_multiblock_read_count参数会使全表扫面的成本降低,但是在CBO下,Oracle会更倾向于使用全表扫面而不是索引扫描,db_file_multiblock_read_count与执行计划选择的例子;
  6. 为什么要使用10046事件:
    1. 10046事件可以帮助我们解析SQL语句的运行状态(包括Parse/Fetch/Execute三个阶段中遇到的等待事件,消耗的物理和逻辑读,CPU时间,执行计划等等);
    2. 即10046为我们揭示了SQL语句的运行情况,对于以点入手的SQL调优是很好的辅助工具,特别是在10g之前没有ASH的情况下;但整体系统调优不是10046所擅长的,需要用到AWR;
    3. 10046还能帮助我们分析一些DDL维护命令的内部工作原理,比如RMAN,expdp/impdp等工具的缓慢问题等;
  7. 10046事件和10053事件的区别:
    1. 10053事件是最常用的Oracle优化器optimizer跟踪trace,10053可以作为我们解释优化器为什么选择某个执行计划,但并不告诉我们这个执行计划到底运行地如何;
    2. 10046并不解释optimizer优化器的工作,但它同样说明了在SQL解析parse阶段所遇到的等待事件和所消耗的CPU等资源,以及Execute执行和Fetch阶段的各项指标;
    3. 简而言之10046告诉我们SQL(执行计划)运行地如何,10053告诉我们优化器为什么为这个SQL选择某个执行计划;
  8. 10046跟踪文件的阅读;
———————————– 获取跟踪文件的脚本 ———————————–
— 1.解析阶段;
PARSING IN CURSOR #11328540 len=56 dep=0 uid=84 oct=3 lid=84 tim=1396508012619901 hv=3963517665 ad=’4464298c’ sqlid=’25vmrurq3wyr1′
SELECT COUNT(*) FROM HR.EMPLOYEES WHERE DEPARTMENT_ID=20
END OF STMT
PARSE #11328540:c=6999,e=6974,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2271004725,tim=1396508012619893
PARSING
1.#11328540:表示游标号,非常重要,后面的PARSE,EXEC,WAIT,FETCH,STAT,CLOSE阶段都需要使用这个游标号和前面的sql关联起来;
2.这个过程之后可能会产生很多的递归sql,一般是用来查询一些数据字典的,所消耗的资源和时间都非常的少;
3.len=56:表示sql的长度;
4.dep=0:表示Recursive Depth,即sql递归的深度;如果为0表示不是递归sql,如果大于0表示递归sql;
5.uid=84:表示解析这个游标的用户的UID,如果是0表示是sys;通过dba_users/user$查看;
6.oct=3:表示Oracle Command Type,即Oracle中的命令类型,与v$sql中的command_type列对应,可以通过查询v$sqlcommand视图查看具体的定义;
7.lid=84:表示Privilege User Id,即权限用户ID;
8.tim:表示timestamp时间戳,9i之后单位是ms,用来判断trace中两个点的时间差;来自v$timer视图,一个Oracle的内部时钟;
9.hv:表示sql的hash value,10g之前没有sqlid就使用hash value来定位一个sql;
10.ad:表示sqltext的地址,来自于v$sql的address列;
11.sqlid:表示对应的sql id;
12.err:如果有错误的话,代表错误代码,可以通过oerr ora xxx;
PARSE:是sql运行的第一个阶段,解析SQL语句;
1.c:表示cpu time,即消耗cpu的时间,9i之后单位是ms;
2.e:表示elapsed time,即消耗的自然时间,9i之后单位是ms;
3.p:表示physcial read,即物理的数目;
4.cr:表示consist read,即一致性读引起的buffer get数目;
5.cu:表示current read,即当前读取引起的buffer get数目;
6.mis:表示读取library cache的miss的数目,如果=0的话表示使用软解析或者更好的方式;如果大于0表示发生了硬结析;
7.r:表示rows,即处理的行数;
8.dep:表示Recursive Depth,即sql递归的深度;如果为0表示不是递归sql,如果大于0表示递归sql;
9.og:表示optimizer_mode,对应关系为:0-游标不可见/优化器环境未合理创建;1-ALL_ROWS;2-FIRST_ROWS;3-RULE;4-CHOOSE;
EXEC:sql运行的第二个阶段,执行sql语句;
FETCH:从游标中fetch数据行;
UNMAP:当游标使用临时表时,若游标关闭则使用UNMAP释放临时表相关的资源;
— 2.执行阶段;
EXEC #11328540:c=0,e=102,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2271004725,tim=1396508012620426
— 3.相关的等待;

WAIT #11328540: nam=’db file sequential read’ ela= 25707 file#=5 block#=243 blocks=1 obj#=76349 tim=1396508012647378
1.Nam:等待针对的事件名字,它的P1/P2/P3可以参考视图V$EVENT_NAME,也可以从V$SESSION/ASH中观察到等待事件;
2.ela:本操作的耗时,单位是ms;
3.p1,p2,p3:针对该事件的三个描述参数,见V$EVENT_NAME;
4.obj#:相关的对象id;
— 4.获取数据;

FETCH #11328540:c=4000,e=27513,p=1,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=2271004725,tim=1396508012648208
— 5.相关的统计;
STAT #11328540 id=1 cnt=1 pid=0 pos=1 obj=0 op=’SORT AGGREGATE (cr=1 pr=1 pw=0 time=27520 us)’
STAT #11328540 id=2 cnt=2 pid=1 pos=1 obj=76349 op=’INDEX RANGE SCAN EMP_DEPARTMENT_IX (cr=1 pr=1 pw=0 time=27485 us cost=1 size=6 car
d=2)’
STAT:相关行反应解释执行计划的统计信息
1.id:执行计划的行数,从1开始;
2.cnt:该数据源的行数;
3.pid:该数据源的父ID;
4.pos:在执行计划中的位置;
5.obj:对应数据源的object id;
6.op:数据源的访问操作,例如FULL TABLE SCAN;
7.cr:代表一致性读的数量
8.pr:代表物理读的数量
9.pw:代表物理写的数量
10.time:单位为ms,本步骤的耗时间;
11.cost:本操作的优化器成本;
12.size:评估的数据源大小,单位为字节;
13.card:评估的优化器基数Cardinality;
— 6.关闭游标;

CLOSE #11328540:c=0,e=21,dep=0,type=0,tim=1396508016737280

CLOSE:关闭游标;
type:关闭游标的操作类型;
0-该游标从未被缓存且执行次数小于3次,也叫hard close;
1-该游标从未被缓存但执行次数至少3次,若在session cached cursor中有free slot,则将该游标放入session cached cursor;
2-该游标从未被缓存但执行次数至少3次,该游标置入session cached cursor的条件是讲老的缓存age out掉;
3-该游标已经在缓存里;
— 7.其它,如果有绑定变量的话;
BINDS:
1.kkscoacd:是绑定变量相关的描述符;
2.Bind#0:说明是第0个变量;
3.oacdty:data type,96是ANSI fixed char;
4.oacflg:代表绑定选项的特殊标志位;
5.size:为该内存chunk分配的内存大小;
6.mxl:绑定变量的最大长度;
7.pre:precision;
8.scl:Scale;
9.kxsbbbfp:buffer point;
10.bln:bind buffer length;
11.avl:实际的值的长度;
12.flg:代表绑定状态;
13.value:实际的绑定值;
———————————– 获取跟踪文件的脚本 ———————————–
———————————– 获取跟踪文件的脚本 ———————————–
SELECT a.VALUE || b.symbol || c.instance_name || ‘_ora_’ || d.spid || ‘.trc’ trace_file
FROM (SELECT VALUE FROM v$parameter WHERE NAME = ‘user_dump_dest’) a,
(SELECT substr(VALUE, -6, 1) symbol FROM v$parameter WHERE NAME = ‘user_dump_dest’) b,
(SELECT instance_name FROM v$instance) c,
(SELECT spid
FROM v$session s, v$process p, v$mystat m
WHERE s.paddr = p.addr AND
s.sid = m.sid AND
m.statistic# = 0) d;
11g later:SELECT VALUE FROM v$diag_info WHERE NAME = ‘Default Trace File’;

———————————– 获取跟踪文件的脚本 ———————————–
———————————– 获取当前session设置的参数的脚本 ———————————–

DECLARE
event_level NUMBER;
BEGIN
FOR event_number IN 10000 .. 10999 LOOP
sys.dbms_system.read_ev(event_number, event_level);
IF (event_level > 0) THEN
sys.dbms_output.put_line(‘Event ‘ || to_char(event_number) || ‘ is set at level ‘ || to_char(event_level));
END IF;
END LOOP;
END;

———————————– 获取当前session设置的参数的脚本 ———————————–
———————————– 10046查看等待事件的例子 ———————————–
— 1.查看系统参数;
show parameter db_block_size; — 8192;
show parameter db_file_multiblock_read_count; — 128;
SELECT tablespace_name, block_size, initial_extent / block_size, next_extent / block_size
FROM dba_tablespaces
WHERE tablespace_name IN (‘SYSTEM’, ‘TBS32B’);
SYSTEM     8192     8
TBS32B     8192     32     32
— 2.创建一个测试表;
sqlplus / as sysdba
CREATE TABLE TB10046 AS SELECT * FROM dba_objects;
SELECT file_id, block_id, blocks FROM dba_extents WHERE segment_name = ‘TB10046’;
   FILE_ID   BLOCK_ID     BLOCKS
———- ———- ———-
1      93784          8
1      93792          8
1      93800          8
1      93808          8
1      93816          8
1     104832          8
1     104840          8
1     104848          8
1     104856          8
1     104864          8
1     104872          8

FILE_ID   BLOCK_ID     BLOCKS
———- ———- ———-
1     104880          8
1     104888          8
1     104896          8
1     104904          8
1     104912          8
1     104960        128
1     105088        128
1     105216        128
1     105344        128
1     105472        128
1     105600        128

FILE_ID   BLOCK_ID     BLOCKS
———- ———- ———-
1     105728        128
1     105856        128

24 rows selected.

— 3.生成trace文件;
ALTER SESSION SET TRACEFILE_IDENTIFIER=’t1′;
ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 12’;
SELECT event, total_waits FROM V$system_EVENT WHERE EVENT = ‘db file scattered read’; — 146913315
SELECT COUNT(*) FROM TB10046; — 发生全表扫描;
SELECT event, total_waits FROM V$system_EVENT WHERE EVENT = ‘db file scattered read’; — 146913412
— 4.查看trace文件,发现一次读取8个blocks,因为扫描不能跨extent,此时一个extent中是8个块;(trace中的file#,block#和blocks是与等待事件中参数一一对应的:SELECT NAME, parameter1, parameter2, parameter3 FROM v$event_name WHERE NAME = ‘db file scattered read’;)
WAIT #1: nam=’db file scattered read’ ela= 82 file#=1 block#=104961 blocks=8 obj#=95923 tim=1389859838564653
WAIT #1: nam=’db file scattered read’ ela= 66 file#=1 block#=104986 blocks=8 obj#=95923 tim=1389859838565112
WAIT #1: nam=’db file scattered read’ ela= 64 file#=1 block#=105009 blocks=8 obj#=95923 tim=1389859838565556
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105017 blocks=8 obj#=95923 tim=1389859838565951
WAIT #1: nam=’db file scattered read’ ela= 66 file#=1 block#=105059 blocks=8 obj#=95923 tim=1389859838566301
WAIT #1: nam=’db file scattered read’ ela= 77 file#=1 block#=105070 blocks=8 obj#=95923 tim=1389859838566646
WAIT #1: nam=’db file scattered read’ ela= 62 file#=1 block#=105106 blocks=8 obj#=95923 tim=1389859838567210
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105118 blocks=8 obj#=95923 tim=1389859838567565
WAIT #1: nam=’db file scattered read’ ela= 71 file#=1 block#=105163 blocks=8 obj#=95923 tim=1389859838567961
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105185 blocks=8 obj#=95923 tim=1389859838568283
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105200 blocks=8 obj#=95923 tim=1389859838568635
WAIT #1: nam=’db file scattered read’ ela= 68 file#=1 block#=105226 blocks=8 obj#=95923 tim=1389859838568997
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105236 blocks=8 obj#=95923 tim=1389859838569331
WAIT #1: nam=’db file scattered read’ ela= 67 file#=1 block#=105264 blocks=8 obj#=95923 tim=1389859838569659
WAIT #1: nam=’db file scattered read’ ela= 67 file#=1 block#=105291 blocks=8 obj#=95923 tim=1389859838569972
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105303 blocks=8 obj#=95923 tim=1389859838570278
WAIT #1: nam=’db file scattered read’ ela= 79 file#=1 block#=105325 blocks=8 obj#=95923 tim=1389859838570640
WAIT #1: nam=’db file scattered read’ ela= 63 file#=1 block#=105371 blocks=8 obj#=95923 tim=1389859838571135
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105425 blocks=8 obj#=95923 tim=1389859838571486
WAIT #1: nam=’db file scattered read’ ela= 69 file#=1 block#=105474 blocks=8 obj#=95923 tim=1389859838571855
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105516 blocks=8 obj#=95923 tim=1389859838572204
WAIT #1: nam=’db file scattered read’ ela= 64 file#=1 block#=105528 blocks=8 obj#=95923 tim=1389859838572530
WAIT #1: nam=’db file scattered read’ ela= 66 file#=1 block#=105601 blocks=8 obj#=95923 tim=1389859838572887
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105617 blocks=8 obj#=95923 tim=1389859838573215
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105644 blocks=8 obj#=95923 tim=1389859838573561
WAIT #1: nam=’db file scattered read’ ela= 64 file#=1 block#=105676 blocks=8 obj#=95923 tim=1389859838573917
WAIT #1: nam=’db file scattered read’ ela= 66 file#=1 block#=105700 blocks=8 obj#=95923 tim=1389859838574251
WAIT #1: nam=’db file scattered read’ ela= 66 file#=1 block#=105712 blocks=8 obj#=95923 tim=1389859838574650
WAIT #1: nam=’db file scattered read’ ela= 64 file#=1 block#=105759 blocks=8 obj#=95923 tim=1389859838575214
WAIT #1: nam=’db file scattered read’ ela= 66 file#=1 block#=105779 blocks=8 obj#=95923 tim=1389859838575545
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105795 blocks=8 obj#=95923 tim=1389859838575886
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105820 blocks=8 obj#=95923 tim=1389859838576234
WAIT #1: nam=’db file scattered read’ ela= 63 file#=1 block#=105828 blocks=8 obj#=95923 tim=1389859838576565
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105842 blocks=8 obj#=95923 tim=1389859838576902
WAIT #1: nam=’db file scattered read’ ela= 64 file#=1 block#=105871 blocks=8 obj#=95923 tim=1389859838577386
WAIT #1: nam=’db file scattered read’ ela= 66 file#=1 block#=105879 blocks=8 obj#=95923 tim=1389859838577723
— 5.创建一个256k大小extent的表空间,即一个extent可以存放32个块;
CREATE TABLESPACE TBS32B DATAFILE ‘/u01/app/oracle/oradata/ORCL/tbs32b.dbf’ SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;
ALTER TABLE TB10046 MOVE TABLESPACE TBS32B;
SELECT file_id, block_id, blocks FROM dba_extents WHERE segment_name = ‘TB10046’;
   FILE_ID   BLOCK_ID     BLOCKS
———- ———- ———-
8        128         32
8        160         32
8        192         32
8        224         32
8        256         32
8        288         32
8        320         32
8        352         32
8        384         32
8        416         32
8        448         32

FILE_ID   BLOCK_ID     BLOCKS
———- ———- ———-
8        480         32
8        512         32
8        544         32
8        576         32
8        608         32
8        640         32
8        672         32
8        704         32
8        736         32
8        768         32
8        800         32

FILE_ID   BLOCK_ID     BLOCKS
———- ———- ———-
8        832         32
8        864         32
8        896         32
8        928         32
8        960         32
8        992         32
8       1024         32
8       1056         32
8       1088         32
8       1120         32
8       1152         32

FILE_ID   BLOCK_ID     BLOCKS
———- ———- ———-
8       1184         32
8       1216         32

35 rows selected.

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SESSION SET TRACEFILE_IDENTIFIER=’t2′;
ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 12’;
SELECT COUNT(*) FROM TB10046; — 发生全表扫描;
— 6.查看trace文件,发现一次读取32个blocks;
WAIT #1: nam=’db file scattered read’ ela= 114 file#=8 block#=132 blocks=28 obj#=95923 tim=1389860294615193
WAIT #1: nam=’db file scattered read’ ela= 118 file#=8 block#=162 blocks=30 obj#=95923 tim=1389860294616423
WAIT #1: nam=’db file scattered read’ ela= 109 file#=8 block#=194 blocks=30 obj#=95923 tim=1389860294617528
WAIT #1: nam=’db file scattered read’ ela= 111 file#=8 block#=225 blocks=31 obj#=95923 tim=1389860294618635
WAIT #1: nam=’db file scattered read’ ela= 117 file#=8 block#=256 blocks=32 obj#=95923 tim=1389860294619776
WAIT #1: nam=’db file scattered read’ ela= 133 file#=8 block#=289 blocks=31 obj#=95923 tim=1389860294620942
WAIT #1: nam=’db file scattered read’ ela= 142 file#=8 block#=320 blocks=32 obj#=95923 tim=1389860294622095
WAIT #1: nam=’db file scattered read’ ela= 115 file#=8 block#=353 blocks=31 obj#=95923 tim=1389860294623268
WAIT #1: nam=’db file scattered read’ ela= 117 file#=8 block#=384 blocks=32 obj#=95923 tim=1389860294624399
WAIT #1: nam=’db file scattered read’ ela= 113 file#=8 block#=417 blocks=31 obj#=95923 tim=1389860294625493
WAIT #1: nam=’db file scattered read’ ela= 114 file#=8 block#=448 blocks=32 obj#=95923 tim=1389860294626569
WAIT #1: nam=’db file scattered read’ ela= 112 file#=8 block#=481 blocks=31 obj#=95923 tim=1389860294627654
WAIT #1: nam=’db file scattered read’ ela= 117 file#=8 block#=512 blocks=32 obj#=95923 tim=1389860294628730
WAIT #1: nam=’db file scattered read’ ela= 113 file#=8 block#=545 blocks=31 obj#=95923 tim=1389860294629788
WAIT #1: nam=’db file scattered read’ ela= 139 file#=8 block#=576 blocks=32 obj#=95923 tim=1389860294631207
WAIT #1: nam=’db file scattered read’ ela= 123 file#=8 block#=609 blocks=31 obj#=95923 tim=1389860294632340
WAIT #1: nam=’db file scattered read’ ela= 191 file#=8 block#=640 blocks=32 obj#=95923 tim=1389860294633470
WAIT #1: nam=’db file scattered read’ ela= 193 file#=8 block#=673 blocks=31 obj#=95923 tim=1389860294634671
WAIT #1: nam=’db file scattered read’ ela= 131 file#=8 block#=704 blocks=32 obj#=95923 tim=1389860294635781
WAIT #1: nam=’db file scattered read’ ela= 119 file#=8 block#=737 blocks=31 obj#=95923 tim=1389860294636852
WAIT #1: nam=’db file scattered read’ ela= 125 file#=8 block#=768 blocks=32 obj#=95923 tim=1389860294637924
WAIT #1: nam=’db file scattered read’ ela= 126 file#=8 block#=801 blocks=31 obj#=95923 tim=1389860294638974
WAIT #1: nam=’db file scattered read’ ela= 119 file#=8 block#=832 blocks=32 obj#=95923 tim=1389860294640007
WAIT #1: nam=’db file scattered read’ ela= 123 file#=8 block#=865 blocks=31 obj#=95923 tim=1389860294641040
WAIT #1: nam=’db file scattered read’ ela= 116 file#=8 block#=896 blocks=32 obj#=95923 tim=1389860294642112
WAIT #1: nam=’db file scattered read’ ela= 114 file#=8 block#=929 blocks=31 obj#=95923 tim=1389860294643170
WAIT #1: nam=’db file scattered read’ ela= 135 file#=8 block#=960 blocks=32 obj#=95923 tim=1389860294644287
WAIT #1: nam=’db file scattered read’ ela= 183 file#=8 block#=993 blocks=31 obj#=95923 tim=1389860294645459
WAIT #1: nam=’db file scattered read’ ela= 131 file#=8 block#=1024 blocks=32 obj#=95923 tim=1389860294646588
WAIT #1: nam=’db file scattered read’ ela= 116 file#=8 block#=1057 blocks=31 obj#=95923 tim=1389860294647669
WAIT #1: nam=’db file scattered read’ ela= 116 file#=8 block#=1088 blocks=32 obj#=95923 tim=1389860294648735
WAIT #1: nam=’db file scattered read’ ela= 114 file#=8 block#=1121 blocks=31 obj#=95923 tim=1389860294649806
WAIT #1: nam=’db file scattered read’ ela= 119 file#=8 block#=1152 blocks=32 obj#=95923 tim=1389860294650864
WAIT #1: nam=’db file scattered read’ ela= 113 file#=8 block#=1185 blocks=31 obj#=95923 tim=1389860294651919
———————————– 10046查看等待事件的例子 ———————————–
———————————– db_file_multiblock_read_count与执行计划选择的例子 ———————————–
— 1.准备工作;
SELECT owner, count(*) FROM TB10046 GROUP BY owner ORDER BY 2;

OWNER                            COUNT(*)
—————————— ———-
OWBSYS                                  2
APPQOSSYS                               3
SCOTT                                   6
ORACLE_OCM                              8
SI_INFORMTN_SCHEMA                      8
BI                                      8
OUTLN                                   9
ORDPLUGINS                             10
OWBSYS_AUDIT                           12
FLOWS_FILES                            12
PM                                     27

OWNER                            COUNT(*)
—————————— ———-
HR                                     34
IX                                     55
DBSNMP                                 65
OE                                    127
ORDDATA                               248
SH                                    306
EXFSYS                                310
WMSYS                                 316
CTXSYS                                366
SYSTEM                                529
OLAPSYS                               719

OWNER                            COUNT(*)
—————————— ———-
XDB                                   844
MDSYS                                1509
PV                                   2161
APEX_030200                          2406
ORDSYS                               2532
SYSMAN                               3491
PUBLIC                              27702
SYS                                 31132

30 rows selected.

CREATE INDEX IDX_TB10046_HR ON TB10046(owner);

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘TB10046’, cascade=>TRUE);
— 2.设置db_file_multiblock_read_count参数为8,查看表的执行计划;
ALTER SYSTEM SET db_file_multiblock_read_count=8;
SET AUTOT TRACE EXP;
SQL> SELECT * FROM tb10046 WHERE OWNER=’SYSMAN’;
Execution Plan
———————————————————-
Plan hash value: 3379381082

——————————————————————————–
————–

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CP
U)| Time     |

——————————————————————————–
————–

|   0 | SELECT STATEMENT            |                |  3268 |   315K|    95   (
0)| 00:00:02 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TB10046        |  3268 |   315K|    95   (
0)| 00:00:02 |

|*  2 |   INDEX RANGE SCAN          | IDX_TB10046_HR |  3268 |       |     8   (
0)| 00:00:01 |

——————————————————————————–
————–

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

2 – access(“OWNER”=’SYSMAN’)

— 3.设置db_file_multiblock_read_count参数为128,查看表的执行计划;
ALTER SYSTEM SET db_file_multiblock_read_count=;128
SQL> SELECT * FROM tb10046 WHERE OWNER=’SYSMAN’;
Execution Plan
———————————————————-
Plan hash value: 3237706262

—————————————————————————–
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————–
|   0 | SELECT STATEMENT  |         | 3268  |   315K|    99   (1)| 00:00:03 |

|*  1 |  TABLE ACCESS FULL| TB10046 | 3268  |   315K|    99   (1)| 00:00:03 |
—————————————————————————–

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

1 – filter(“OWNER”=’SYSMAN’)

———————————– db_file_multiblock_read_count与执行计划选择的例子 ———————————–

性能调优工具02–Sql Access Advisor工具的使用

Posted on by

SAA(Sql Access Advisor)工具的使用

  1. SAA可以做什么:
    1. 在给定的负载上推荐一些物化视图,物化视图日志和索引;
    2. 推荐的索引包括Bitmap索引,基于函数的索引和B-Tree索引;
    3. 推荐如何创建物化视图,以至于可以实现快速刷新和查询重写;
  2. SAA的使用模型:
    1. SQL的来源:
      1. SQL Cache:即当前v$sql视图中记录的sql语句;
      2. User-defined:自定义一个表,记录执行的sql语句和执行sql语句的用户,然后把这个表传给SAA;
      3. STS:使用Sql Tuning Set,主要是从负载中获得;
    2. 给出的建议：
      1. Simultaneously considers index solutions, materialized view solutions, or combinations of both;
      2. Considers storage for creation and maintenance costs;
      3. Does not generate drop recommendations for partial workloads;
      4. Optimizes materialized views for maximum query rewrite usage and fast refresh;
      5. Recommends materialized view logs for fast refresh;
      6. Combines similar indexes into a single index;
      7. Generates recommendations that support multiple workload queries;
    3. 架构图;                                                                                          
    4. 推荐的两种方式:Comprehensive和Limited;
      1. Add new index on table or materialized view;
      2. Drop an unused index;
      3. Modify an existing index by changing the index type;
      4. Modify an existing index by adding columns at the end;
      5. Add a new materialized view;
      6. Drop an unused materialized view;
      7. Add a new materialized view log;
      8. Modify an existing materialized view log to add new columns or clauses;
      9. Comprehensive可以分析1-8所有的推荐;
      10. Limited只能分析1,4,5,7,8推荐;
  3. 使用SAA需要的权限:
    1. ADVISOR的系统权限;
    2. 目标表的SELECT权限,而且这个权限不能从一个角色中获得;
  4. 使用SAA工具:
    1. 执行负载;
    2. 使用EM中图形化界面;
    3. 使用脚本调用DMBS_ADVISOR包;
  5. 使用DMBS_ADVISOR包:
    1. 步骤:
      1. 创建一个任务,并定义参数;
      2. 定义负载;
      3. 生成一些建议;
      4. 查看并应用建议;
    2. SAA的工作流;
    3. 创建一个任务:DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, :task_id, :task_name);
    4. 设置SAA的参数:
      1. 设置任务的参数:DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, parameter, value);
      2. 设置负载的参数:DBMS_ADVISOR.SET_SQLWKLD_PARAMETER(:workload_name, parameter, value);
    5. 创建模板:DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, :task_id, :task_name, template=>:template_name, is_template=>’TRUE’);
    6. 创建一个负载:DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name);
      1. SQL Tuning Sets:DBMS_ADVISOR.IMPORT_SQLWKLD_STS();
      2. 用户自定义负载:DBMS_ADVISOR.IMPORT_SQLWKLD_USER();
      3. SQL Cache:DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE();
      4. 单条的SQL语句:DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT();
      5. 某个用户下的SQL语句:DBMS_ADVISOR.IMPORT_SQLWKLD_SCHEMA();
    7. 删除负载:DBMS_ADVISOR.DELETE_SQLWKLD(:workload_name);
    8. 任务与负载关联:DBMS_ADVISOR.ADD_SQLWKLD_REF(:task_name, :workload_name);
    9. 生成建议并把建议保存在SAA的资源库:DBMS_ADVISOR.EXECUTE_TASK(:task_name);
      1. 相应的视图为:dba/user_advisor_recommendations;
      2. 或者使用dbms_advisor.get_task_script生成相应的脚本;
    10. SAA的主要建议有:
      1. 创建/删除物化视图;
      2. 创建/删除物化视图日志;
      3. 创建/删除索引;
      4. 收集统计信息;
    11. 生成SQL脚本:
      1. 创建DIRECTORY;
      2. 授权给用户;
      3. 生成脚本;
  6. 如果只是想调优一条SQL语句的话,可以执行快速调优,使用DBMS_ADVISOR.QUICK_TUNE()过程;
  7. 对于物化视图的优化:
    1. DBMS_MVIEW.EXPLAIN_MVIEW:查看使用/不使用物化视图的原因;
    2. DBMS_MVIEW.EXPLAIN_REWRITE:为没有么有使用查询重写,如果使用了,使用的哪个物化视图;
    3. DBMS_ADVISOR.TUNE_MVIEW:生成快速刷新物化视图和生成查询重写的建议;
——————————– 使用SAA工具 ——————————–
— 1.执行负载;
CONNECT / AS SYSDBA;
ALTER USER sh IDENTIFIED BY oracle ACCOUNT UNLOCK;
GRANT DBA TO sh;
ALTER SYSTEM FLUSH shared_pool;
ALTER SYSTEM FLUSH buffer_cache;
CONNECT sh/oracle;
SET autotrace traceonly stat;

SELECT c.cust_last_name, SUM(s.amount_sold) AS dollars, SUM(s.quantity_sold) AS quantity
FROM sales s, customers c, products p
WHERE c.cust_id = s.cust_id AND
s.prod_id = p.prod_id AND
c.cust_state_province IN (‘Dublin’, ‘Galway’)
GROUP BY c.cust_last_name;

SELECT c.cust_id, SUM(amount_sold) AS dollar_sales
FROM sales s, customers c
WHERE s.cust_id = c.cust_id
GROUP BY c.cust_id;

SELECT SUM(unit_cost) FROM costs GROUP BY prod_id;
SELECT * FROM customers WHERE cust_postal_code = ‘83786’;

— 2.使用EM中的SAA工具;
1.Databaes Instance->Advisor Central->SQL Access Advisor;
2.选择SQL语句的来源:选择从内存中的SQL语句;
3.选择推荐的选项:生成索引和物化视图,并使用综合模式;
4.系统创建了一个作业,指定立即执行;
5.查看作业已完成;
6.然后查看和采用相应的建议;
— 3.使用脚本调用DBMS_ADVISOR包;
1.定义变量;
VARIABLE v_task_id NUMBER;
VARIABLE v_task_name VARCHAR2(255);
VARIABLE v_wkld_name VARCHAR2(255);
2.指定任务的名称和负载的名称;
EXECUTE :v_task_name := ‘my_task’;
EXECUTE :v_wkld_name := ‘my_sql_wkld’;
3.删除之前的任务和负载;
BEGIN
dbms_advisor.delete_sqlwkld_ref(:v_task_name, :v_wkld_name);
dbms_advisor.delete_sqlwkld(:v_wkld_name);
dbms_advisor.delete_task(:v_task_name);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
4.创建一个任务;
EXECUTE DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, :v_task_id, :v_task_name);

5.查看当天前的优化任务;
SELECT * FROM user_advisor_tasks;

6.设置任务的参数,生成所有建议,并使用综合模式;
EXECUTE dbms_advisor.set_task_parameter(:v_task_name, ‘EXECUTION_TYPE’, ‘FULL’);
EXECUTE dbms_advisor.set_task_parameter(:v_task_name, ‘MODE’, ‘COMPREHENSIVE’);

7.创建一个负载;
EXECUTE dbms_advisor.create_sqlwkld(:v_wkld_name);
8.把任务与负载关联;
EXECUTE dbms_advisor.add_sqlwkld_ref(:v_task_name, :v_wkld_name);

9.查看任务与负载的映射关系;
SELECT * FROM user_advisor_sqla_wk_map;

10.导入负载,使用SQL Cache的模式;
VARIABLE v_saved_stmts NUMBER;
VARIABLE v_failed_stmts NUMBER;
EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE(:v_wkld_name, ‘APPEND’, 2, :v_saved_stmts, :v_failed_stmts);

11.查看负载中的SQL语句;
SELECT * FROM user_advisor_sqlw_stmts;
12.执行任务,生成优化建议;
EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:v_task_name);
13.查看优化建议;
SELECT * FROM user_advisor_recommendations;
14.生成优化建议的脚本;
直接查看:SELECT DBMS_ADVISOR.GET_TASK_SCRIPT(:v_task_name) FROM dual;
或者创建目录,生成建议的脚本;
CONNECT / AS SYSDBA;
DROP DIRECTORY ADVISOR_RESULTS;
CREATE OR REPLACE DIRECTORY ADVISOR_RESULTS AS ‘/home/oracle’;
GRANT READ, WRITE ON DIRECTORY ADVISOR_RESULTS TO sh;
CONNECT SH/ORACLE
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:v_task_name), ‘ADVISOR_RESULTS’, ‘advscript.sql’);
——————————– 使用SAA工具 ——————————–
——————————– SAA的快速调优 ——————————–
1.定义变量;
CONNECT sh/oracle;
VARIABLE v_task_name VARCHAR2(30);
EXECUTE :v_task_name := ‘quick_task’;
2.执行快速调优,只能执行一条SQL语句,默认是在当前用户下执行;
EXECUTE dbms_advisor.quick_tune(dbms_advisor.sqlaccess_advisor, :v_task_name, ‘SELECT AVG(amount_sold) FROM sh.sales WHERE promo_id=10’);
3.查看优化建议结果;
SELECT DBMS_ADVISOR.GET_TASK_SCRIPT(:v_task_name) FROM dual;
——————————– SAA的快速调优 ——————————–