发布新日志

  • oracle-merge（合并语句）用法详解

    2011-05-10 00:38:24

    Oracle9i引入了MERGE命令,你能够在一个SQL语句中对一个表同时执行inserts和updates操作. MERGE命令从一个或多个数据源中选择行来updating或inserting到一个或多个表.在Oracle 10g中MERGE有如下一些改进：

    1、UPDATE或INSERT子句是可选的

    2、UPDATE和INSERT子句可以加WHERE子句

    3、在ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表

    4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行

    首先创建示例表： 

    create table PRODUCTS
    (
    PRODUCT_ID INTEGER,
    PRODUCT_NAME VARCHAR2(60),
    CATEGORY VARCHAR2(60)
    );

    insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');
    insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');
    insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');
    insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');
    insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');
    commit;

    create table NEWPRODUCTS
    (
    PRODUCT_ID INTEGER,
    PRODUCT_NAME VARCHAR2(60),
    CATEGORY VARCHAR2(60)
    );

    insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');
    insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');
    insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');
    insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');
    commit;

    1、可省略的UPDATE或INSERT子句

    在Oracle 9i, MERGE语句要求你必须同时指定INSERT和UPDATE子句.而在Oracle 10g, 你可以省略UPDATE或INSERT子句中的一个. 下面的例子根据表NEWPRODUCTS的PRODUCT_ID字段是否匹配来updates表PRODUCTS的信息:

    SQL> MERGE INTO products p
    2 USING newproducts np
    3 ON (p.product_id = np.product_id)
    4 WHEN MATCHED THEN
    5 UPDATE
    6 SET p.product_name = np.product_name,
    7 p.category = np.category;

    3 rows merged.

    SQL> SELECT * FROM products;

    PRODUCT_ID PRODUCT_NAME CATEGORY
    ---------- -------------------- ----------
    1501 VIVITAR 35MM ELECTRNCS
    1502 OLYMPUS CAMERA ELECTRNCS
    1600 PLAY GYM TOYS
    1601 LAMAZE TOYS
    1666 HARRY POTTER TOYS
    SQL>
    SQL> ROLLBACK;
    Rollback complete.
    SQL>

    在上面例子中, MERGE语句影响到是产品id为1502, 1601和1666的行. 它们的产品名字和种 类被更新为表newproducts中的值. 下面例子省略UPDATE子句, 把表NEWPRODUCTS中新的PRODUCT_ID插入到表PRODUCTS中, 对于在两个表中能够匹配上PRODUCT_ID的数据不作任何处理. 从这个例子你能看到PRODUCT_ID=1700的行被插入到表PRODUCTS中. 

    SQL> MERGE INTO products p
    2 USING newproducts np
    3 ON (p.product_id = np.product_id)
    4 WHEN NOT MATCHED THEN
    5 INSERT
    6 VALUES (np.product_id, np.product_name,
    7 np.category);

    1 row merged.

    SQL> SELECT * FROM products;

    PRODUCT_ID PRODUCT_NAME CATEGORY
    ---------- -------------------- ----------
    1501 VIVITAR 35MM ELECTRNCS
    1502 OLYMPUS IS50 ELECTRNCS
    1600 PLAY GYM TOYS
    1601 LAMAZE TOYS
    1666 HARRY POTTER DVD
    1700 WAIT INTERFACE BOOKS

    2、带条件的Updates和Inserts子句

    你能够添加WHERE子句到UPDATE或INSERT子句中去, 来跳过update或insert操作对某些行的处理. 下面例子根据表NEWPRODUCTS来更新表PRODUCTS数据, 但必须字段CATEGORY也得同时匹配上: 

    SQL> MERGE INTO products p
    2 USING newproducts np
    3 ON (p.product_id = np.product_id)
    4 WHEN MATCHED THEN
    5 UPDATE
    6 SET p.product_name = np.product_name
    7 WHERE p.category = np.category;

    2 rows merged.

    SQL> SELECT * FROM products;

    PRODUCT_ID PRODUCT_NAME CATEGORY
    ---------- -------------------- ----------
    1501 VIVITAR 35MM ELECTRNCS
    1502 OLYMPUS CAMERA ELECTRNCS
    1600 PLAY GYM TOYS
    1601 LAMAZE TOYS
    1666 HARRY POTTER DVD
    SQL>
    SQL> rollback;

    在这个例子中, 产品ID为1502,1601和1666匹配ON条件但是1666的category不匹配. 因此MERGE命令只更新两行数据. 下面例子展示了在Updates和Inserts子句都使用WHERE子句: 

    SQL> MERGE INTO products p
    2 USING newproducts np
    3 ON (p.product_id = np.product_id)
    4 WHEN MATCHED THEN
    5 UPDATE
    6 SET p.product_name = np.product_name,
    7 p.category = np.category
    8 WHERE p.category = 'DVD'
    9 WHEN NOT MATCHED THEN
    10 INSERT
    11 VALUES (np.product_id, np.product_name, np.category)
    12 WHERE np.category != 'BOOKS'
    SQL> /

    1 row merged.

    SQL> SELECT * FROM products;

    PRODUCT_ID PRODUCT_NAME CATEGORY
    ---------- -------------------- ----------
    1501 VIVITAR 35MM ELECTRNCS
    1502 OLYMPUS IS50 ELECTRNCS
    1600 PLAY GYM TOYS
    1601 LAMAZE TOYS
    1666 HARRY POTTER TOYS

    SQL>

    注意由于有WHERE子句INSERT没有插入所有不匹配ON条件的行到表PRODUCTS.

    3、无条件的Inserts

    你能够不用连接源表和目标表就把源表的数据插入到目标表中. 这对于你想插入所有行到目标表时是非常有用的. Oracle 10g现在支持在ON条件中使用常量过滤谓词. 举个常量过滤谓词例子ON (1=0). 下面例子从源表插入行到表PRODUCTS, 不检查这些行是否在表PRODUCTS中存在:






    SQL> MERGE INTO products p
    2 USING newproducts np
    3 ON (1=0)
    4 WHEN NOT MATCHED THEN
    5 INSERT
    6 VALUES (np.product_id, np.product_name, np.category)
    7 WHERE np.category = 'BOOKS'
    SQL> /

    1 row merged.

    SQL> SELECT * FROM products;

    PRODUCT_ID PRODUCT_NAME CATEGORY
    ---------- -------------------- ----------
    1501 VIVITAR 35MM ELECTRNCS
    1502 OLYMPUS IS50 ELECTRNCS
    1600 PLAY GYM TOYS
    1601 LAMAZE TOYS
    1666 HARRY POTTER DVD
    1700 WAIT INTERFACE BOOKS
    6 rows selected.
    SQL>

    4、新增加的DELETE子句

    Oracle 10g中的MERGE提供了在执行数据操作时清除行的选项. 你能够在WHEN MATCHED THEN UPDATE子句中包含DELETE子句. DELETE子句必须有一个WHERE条件来删除匹配某些条件的行.匹配DELETE WHERE条件但不匹配ON条件的行不会被从表中删除.

    下面例子验证DELETE子句. 我们从表NEWPRODUCTS中合并行到表PRODUCTS中, 但删除category为ELECTRNCS的行. 

    SQL> MERGE INTO products p
    2 USING newproducts np
    3 ON (p.product_id = np.product_id)
    4 WHEN MATCHED THEN
    5 UPDATE
    6 SET p.product_name = np.product_name,
    7 p.category = np.category
    8 DELETE WHERE (p.category = 'ELECTRNCS')
    9 WHEN NOT MATCHED THEN
    10 INSERT
    11 VALUES (np.product_id, np.product_name, np.category)
    SQL> /

    4 rows merged.

    SQL> SELECT * FROM products;

    PRODUCT_ID PRODUCT_NAME CATEGORY
    ---------- -------------------- ----------
    1501 VIVITAR 35MM ELECTRNCS
    1600 PLAY GYM TOYS
    1601 LAMAZE TOYS
    1666 HARRY POTTER TOYS
    1700 WAIT INTERFACE BOOKS
    SQL>

    产品ID为1502的行从表PRODUCTS中被删除, 因为它同时匹配ON条件和DELETE WHERE条件. 产品ID为1501的行匹配DELETE WHERE条件但不匹配ON条件, 所以它没有被删除. 产品ID为1700 的行不匹配ON条件, 所以被插入表PRODUCTS. 产品ID为1601和1666的行匹配ON条件但不匹配DELETE WHERE条件, 所以被更新为表NEWPRODUCTS中的值.

     

    转自：http://blogold.chinaunix.net/u/12457/showart_346642.html

    查看(813) 评论(0) 收藏 分享 管理

  • insert WITH CHECK OPTION的用法

    2011-05-10 00:17:58

    insert into (<select clause> WITH CHECK OPTION) values (...)

    例如：

    SQL> insert into (select object_id,object_name,object_type from xxx where object_id<1000  WITH CHECK OPTION)
      2  values(999,'testbyhao','testtype');

    这样的语法看起来很特殊，其实是insert进subquery里的这张表里，只不过如果不满足subquery里的where条件的话，就不允许插入。

    如果插入的列有不在subquery作为检查的where条件里，那么也会不允许插入。

    如果不加WITH CHECK OPTION则在插入时不会检查。

    这里注意，subquery其实是不会实际执行的。

     

    例如：

    SQL> insert into (select object_id,object_name,object_type from xxx where object_id<1000)
      2  values(1001,'testbyhao','testtype');

    1 row created.


    SQL> insert into (select object_id,object_name,object_type from xxx where object_id<1000with check option)
      2  values(1001,'testbyhao','testtype');
    insert into (select object_id,object_name,object_type from xxx where object_id<1000 with check option)
                                                               *
    ERROR at line 1:
    ORA-01402: view WITH CHECK OPTION where-clause violation

    这里插入的列中没有object_id，也是不允许插入的：

    SQL> insert into (select object_name,object_type from xxx where object_id<1000 with check option)
      2  values('testbyhao','testtype');
    insert into (select object_name,object_type from xxx where object_id<1000 with check option)
                                                     *
    ERROR at line 1:
    ORA-01402: view WITH CHECK OPTION where-clause violation

    为什么说subquery没有实际执行呢？看统计信息吧：


    SQL> set autotrace trace exp stat
    SQL> select object_id,object_name,object_type from xxx where object_id<1000;

    955 rows selected.


            197  consistent gets

    SQL> insert into (select object_id,object_name,object_type from xxx where object_id<1000)
      2  values(999,'testbyhao','testtype');

    1 row created.


              1  consistent gets

     

     

    转自：http://space.itpub.net/15415488/viewspace-673594

     

    查看(506) 评论(0) 收藏 分享 管理

  • Oracle内连接、左外连接、右外连接、全外连接小总结

    2011-05-09 17:24:06

    数据库版本：Oracle 9i
    表TESTA,TESTB,TESTC,各有A, B两列
    *******TESTA*******
    A      B 
    001   10A 
    002   20A
     
    *********TESTB******

    A      B 
    001   10B
    003   30B
    ********TESTC*******
    A      B 
    001   10C 
    004   40C
     

    连接分为两种：内连接与外连接。

    A．内连接
    内连接，即最常见的等值连接，例：

    SELECT *
    FROM TESTA,TESTB
    WHERE TESTA.A=TESTB.A
    结果
    A      B      A      B
     
    001   10A    001    10B
     

    B.外连接
    （1）使用外连接可以查询不满足连接条件的数据
    （2）外连接的符号是（+）

    外连接分为左外连接，右外连接和全外连接。
    1．  左外连接 left outer join 或者 left join
    左外连接就是在等值连接的基础上加上主表中的未匹配数据，例：

    SELECT *
    FROM TESTA
    LEFT OUTER JOIN TESTB
    ON TESTA.A=TESTB.A
     
    Oracle 支持另一种写法
     
    SELECT *
    FROM TESTA,TESTB
    WHERE TESTA.A=TESTB.A(+)
    结果：
    A      B      A      B
     
    001   10A    001    10B
     
    002   10B
      
     

    三个表做左外连接
    SELECT *
    FROM TESTA
    LEFT OUTER JOIN TESTB
    ON TESTA.A=TESTB.A
    LEFT OUTER JOIN TESTC
    ON TESTA.A=TESTC.A
     
    Oracle 支持的另外一种写法
     
    SELECT *
    FROM TESTA,TESTB,TESTC
    WHERE TESTA.A=TESTB.A(+)
    AND TESTA.A=TESTC.A(+)
     
    结果：
    A     B    A    B     A     B
    001  10A  001  10B   001   10C
    002  20A 
      
      
      
     

    2． 右外连接
    right outer join 或者 right join
    右外连接是在等值连接的基础上加上被连接表的不匹配数据
     
    SELECT *
    FROM TESTA
    RIGHT OUTER JOIN TESTB
    ON TESTA.A=TESTB.A

    Oracle支持的另一种写法
     
    SELECT *
    FROM TESTA,TESTB
    WHERE TESTA.A(+)=TESTB.A
     
    结果：
    A    B    A    B
    001 10A  001  10B
     
             003  30B
     

    3.全外连接
    full outer join 或者 full join
    全外连接是在等值连接的基础上将左表和右表的未匹配数据都加上
     
    SELECT *
    FROM TESTA
    FULL OUTER JOIN TESTB
    ON TESTA.A=TESTB.A
     
    全外连接的等价写法，对同一表先做左连接，然后右连接
     
    SELECT  TESTA.*,TESTB.*
    FROM TESTA
    LEFT OUTER JOIN TESTB
    ON TESTA.A=TESTB.A
    UNION
    SELECT TESTA.*,TESTB.*
    FROM TESTB
    LEFT OUTER JOIN TESTA
    ON TESTA.A=TESTB.A
     
    结果：
    A     B    A    B
     
    001  10A  001  10B
     
    002  20A
      
              003  30B
     

    查看(1394) 评论(1) 收藏 分享 管理

  • 创建表空间

    2010-04-23 13:40:47

    查看(7) 评论(0) 收藏 分享 管理

  • 【转】Oracle日志文件使用小结

    2010-04-22 09:56:42

    查看(4) 评论(0) 收藏 分享 管理

  • 【转】Oracle 重要文件系统解释

    2010-04-22 09:53:02

    查看(4) 评论(0) 收藏 分享 管理

  • 【转】通过v$sqlarea,v$sql查询最占用资源的查询

    2010-04-21 14:52:40

    查看(5) 评论(0) 收藏 分享 管理

  • 【转】Oracle 10g 常见问题处理

    2010-04-21 14:50:09

    查看(5) 评论(0) 收藏 分享 管理

  • 【转】ORACLE的索引和约束详解

    2010-04-20 22:35:58

    Oracle的约束

    * 如果某个约束只作用于单独的字段，即可以在字段级定义约束，也可以在表级定义约束，但如果某个约束作用于多个字段，
    必须在表级定义约束
    * 在定义约束时可以通过CONSTRAINT关键字为约束命名，如果没有指定，ORACLE将自动为约束建立默认的名称

    定义primary key约束(单个字段）
    create table employees (empno number(5) primary key,...)

    指定约束名
    create table employees (empno number(5) constraint emp_pk primary key,...)

    定义primary key约束(多个字段,在表级定义约束）
    create table employees
    (empno number(5),
    deptno number(3) not null,
    constraint emp_pk primary key(empno,deptno)
    using index tablespace indx
    storage (initial 64K
    next 64K
    )
    )

    ORACLE自动会为具有PRIMARY KEY约束的字段(主码字段)建立一个唯一索引和一个NOT NULL约束,定义PRIMARY KEY约束时可以为它的索引
    指定存储位置和存储参数

    alter table employees add primary key (empno)
    alter table employees add constraint emp_pk primary key (empno)
    alter table employees add constraint emp_pk primary key (empno,deptno)

    not null约束(只能在字段级定义NOT NULL约束,在同一个表中可以定义多个NOT NULL约束)
    alter table employees modify deptno not null/null

    unique约束
    create table employees
    ( empno number(5),
    ename varchar2(15),
    phone varchar2(15),
    email varchar2(30) unique,
    deptno number(3) not null,
    constraint emp_ename_phone_uk unique (ename,phone)
    )

    alter table employees
    add constraint emp_uk unique(ename,phone)
    using index tablespace indx

    定义了UNIQUE约束的字段中不能包含重复值，可以为一个或多个字段定义UNIQUE约束,因此，UNIQUE即可以在字段级也可以在表级定义，
    在UNIQUED约束的字段上可以包含空值.

    foreign key约束

    * 定义为FOREIGN KEY约束的字段中只能包含相应的其它表中的引用码字段的值或者NULL值
    * 可以为一个或者多个字段的组合定义FOREIGN KEY约束
    * 定义了FOREIGN KEY约束的外部码字段和相应的引用码字段可以存在于同一个表中，这种情况称为"自引用"
    * 对同一个字段可以同时定义FOREIGN KEY约束和NOT NULL约束

    定义了FOREIGN KEY约束的字段称为"外部码字段",被FORGIEN KEY约束引用的字段称为"引用码字段",引用码必须是主码或唯一码,包含外部码的表称为子表，
    包含引用码的表称为父表.

    A:
    create table employees
    (.....,
    deptno number(3) NOT NULL,
    constraint emp_deptno_fk foreign key (deptno)
    references dept (deptno)
    )

    如果子表中的外部码与主表中的引用码具有相同的名称，可以写成:
    B:
    create table employees
    (.....,
    deptno number(3) NOT NULL
    constraint emp_deptno_fk references dept
    )

    注意：
    上面的例子(B)中not null后面没有加逗号,因为这一句的contraint是跟在那一列deptno后面的，属于列定义，所以都无需指明列。而A例中的是表定义，需要指明那一列，所以要加逗号，不能在列后面定义，还可以写成：

    create table employees
    (empno char(4),
    deptno char(2) not null constraint emp_deptno_fk references dept,
    ename varchar2(10)
    )
    表定义contraint的只能写在最后，再看两个例子：

    create table employees
    (empno number(5),
    ename varchar2(10),
    deptno char(2) not null constraint emp_deptno_fk references dept,
    constraint emp_pk primary key(empno,ename)
    )

    create table employees
    ( empno number(5),
    ename varchar2(15),
    phone varchar2(15),
    email varchar2(30) unique,
    deptno number(3) not null,
    constraint emp_pk primary key(empno,ename),
    constraint emp_phone_uk unique (phone)
    )

    添加foreign key约束(多字段／表级）
    alter table employees
    add constraint emp_jobs_fk foreign key (job,deptno)
    references jobs (jobid,deptno)
    on delete cascade

    更改foreign key约束定义的引用行为(delete cascade/delete set null/delete no action),默认是delete on action

    引用行为(当主表中一条记录被删除时，确定如何处理字表中的外部码字段）：
    delete cascade : 删除子表中所有的相关记录
    delete set null :　将所有相关记录的外部码字段值设置为NULL
    delete no action: 不做任何操作

    先删除原来的外键约束,再添加约束
    ALTER TABLE employees DROP CONSTRAINT emp_deptno_fk;
    ALTER TABLE employees ADD CONSTRAINT emp_deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno) ON DELETE CASCADE;

    check约束
    * 在CHECK约束的表达式中必须引用到表中的一个或多个字段，并且表达式的计算结果必须是一个布尔值
    * 可以在表级或字段级定义
    * 对同一个字段可以定义多个CHECK约束，同时也可以定义NOT NULL约束
    　
    create table employees
    (sal number(7,2)
    constraint emp_sal_ck1 check (sal > 0)
    )

    alter table employees
    add constraint emp_sal_ck2 check (sal < 20000)

    删除约束

    alter table dept drop unique (dname,loc) --指定约束的定义内容
    alter table dept drop constraint dept_dname_loc_uk --指定约束名

    删除约束时，默认将同时删除约束所对应的索引，如果要保留索引，用KEEP INDEX关键字
    alter table employees drop primary key keep index

    如果要删除的约束正在被其它约束引用，通过ALTER TABLE..DROP语句中指定CASCADE关键字能够同时删除引用它的约束

    利用下面的语句在删除DEPT表中的PRIMARY KEY约束时，同时将删除其它表中引用这个约束的FOREIGN KEY约束:
    alter table dept drop primary key cascade

    禁用/激活约束(禁用/激活约束会引起删除和重建索引的操作)
    alter table employees disable/enable unique email
    alter table employees disable/enable constraint emp_ename_pk
    alter tabel employees modify constraint emp_pk disable/enable
    alter tabel employees modify constraint emp_ename_phone_uk disable/enable

    如果有FOREIGN KEY约束正在引用UNIQUE或PRIMARY KEY约束，则无法禁用这些UNIQUE或PRIMARY KEY约束，
    这时可以先禁用FOREIGN KEY约束，然后再禁用UNIQUE或PRIMARY KEY约束；或者可以在ALTER TABLE...DISABLE
    语句中指定CASCADE关键字，这样将在禁用UNIQUE或PRIMARY KEY约束的同时禁用那些引用它们的FOREIGN KEY约束，如：
    alter table employees disable primary key cascade

    约束数据字典
    all_constraints/dba_constraints/user_constraints 约束的基本信息，包括约束的名称，类型，状态
    (约束类型：C(CHECK约束),P(主码约束),R(外部码约束),U(唯一码约束))
    all_cons_columns/dba/user 约束对应的字段信息
    Oracle的索引
        索引和对应的表应该位于不同的表空间中,oracle能够并行读取位于不同硬盘上的数据，可以避免产生I/O冲突
    B树索引：在B树的叶节点中存储索引字段的值与ROWID。
    唯一索引和不唯一索引都只是针对B树索引而言.
    Oracle最多允许包含32个字段的复合索引

    索引创建策略
    1.导入数据后再创建索引
    2.不需要为很小的表创建索引
    3.对于取值范围很小的字段（比如性别字段）应当建立位图索引
    4.限制表中的索引的数目
    5.为索引设置合适的PCTFREE值
    6.存储索引的表空间最好单独设定

    创建不唯一索引
    create index emp_ename on employees(ename)
    tablespace users
    storage(......)
    pctfree 0;

    创建唯一索引
    create unique index emp_email on employees(email)
    tablespace users;

    创建位图索引
    create bitmap index emp_sex on employees(sex)
    tablespace users;

    创建反序索引
    create unique index order_reinx on orders(order_num,order_date)
    tablespace users
    reverse;

    创建函数索引(函数索引即可以是普通的B树索引，也可以是位图索引)
    create index emp_substr_empno
    on employees(substr(empno,1,2))
    tablespace users;

    修改索引存储参数(与表类似，INITIAL和MINEXTENTS参数在索引建立以后不能再改变)
    alter index emp_ename storage(pctincrease 50);

    由于定义约束时由oracle自动建立的索引通常是不知道名称的，对这类索引的修改经常是利用alter table ..using index语句进行的,而不是alter index语句

    利用下面的语句将employees表中primary key约束对应的索引的PCTFREE参数修改为5
    alter table employees enable primary key using index pctfree 5;

    清理索引碎片
    1.合并索引(只是简单的将B树叶结点中的存储碎片合并在一起，并不会改变索引的物理组织结构）
    alter index emp_pk coalesce;

    2.重建索引(不仅能够消除存储碎片,还可以改变索引的全部存储参数设置，并且可以将索引移动到其它的表空间中,重建索引
    实际上就是再指定的表空间中重新建立一个新的索引,然后删除原来的索引)
    alter index emp_pk rebuild;

    删除索引
    drop index emp_ename;

    如果索引中包含损坏的数据块，或者包含过多的存储碎片，需要首先删除这个索引，然后再重建它.
    如果索引是在创建约束时由oracle自动产生的,可以通过禁用约束或删除约束的方法来删除对应的索引.
    在删除一个表时,oracle会自动删除所有与该表相关的索引.

    索引数据字典
    all_indexes/dba_indexes/user_indexes 索引的基本信息
    all_ind_columns/dba_ind_columns/user_ind_columns 索引对应的字段信息

    查看(328) 评论(0) 收藏 分享 管理

  • 【转】循序渐进学习Oracle

    2010-04-20 15:41:26

    查看(4) 评论(0) 收藏 分享 管理

  • 【转】释放临时表空间实例

    2010-04-16 16:38:05

    转自：

    http://blog.csdn.net/47522341/archive/2009/02/05/3864171.aspx

    临时表空间作用
    Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。

    重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长。直到耗尽硬盘空间。

    网上有人猜测在磁盘空间的分配上，oracle使用的是贪心算法，如果上次磁盘空间消耗达到1GB，那么临时表空间就是1GB。也就是说当前临时表空间文件的大小是历史上使用临时表空间最大的大小。

    临时表空间的主要作用：

    索引create或rebuild

    Order by 或 group by

    Distinct 操作

    Union 或 intersect 或 minus

    Sort-merge joins

    analyze

    查看临时表空间大小
    查看临时表文件大小和已使用空间

    select t1."Tablespace" "Tablespace",
    t1."Total (G)" "Total (G)",
    nvl(t2."Used (G)", 0) "Used(G)",
    t1."Total (G)" - nvl(t2."Used (G)", 0) "Free (G)"
    from
    (
    select tablespace_name "Tablespace", to_char((sum(bytes/1024/1024/1024)),'99,999,990.900') "Total (G)"
    from dba_temp_files
    group by tablespace_name
    union
    select tablespace_name "Tablespace", to_char((sum(bytes/1024/1024/1024)),'99,999,990.900') "Total (G)"
    from dba_data_files
    where tablespace_name like 'TEMP%'
    group by tablespace_name
    ) t1,
    (
    select tablespace, round(sum(blocks)*8/1024) "Used (G)" from v$sort_usage
    group by tablespace
    ) t2
    where t1."Tablespace"=t2.tablespace(+)

     

    查看当前临死表使用空间大小与正在占用临时表空间的sql语句

    select sess.SID,  segtype, blocks*8/1000 "MB" ,sql_text
    from v$sort_usage sort, v$session sess,v$sql sql
     where sort.SESSION_ADDR = sess.SADDR
     and sql.ADDRESS = sess.SQL_ADDRESS
      order by blocks desc;

     

    select 'the ' || name || ' temp tablespaces ' || tablespace_name ||
           ' idle ' ||
           round(100 - (s.tot_used_blocks / s.total_blocks) * 100, 3) ||
           '% at ' || to_char(sysdate, 'yyyymmddhh24miss')
      from (select d.tablespace_name tablespace_name,
                   nvl(sum(used_blocks), 0) tot_used_blocks,
                   sum(blocks) total_blocks
              from v$sort_segment v, dba_temp_files d
             where d.tablespace_name = v.tablespace_name(+)
             group by d.tablespace_name) s,
           v$database;

    修改临时文件大小
    select 'ALTER database TEMPFILE  ' || file_name || ' resize 100M ;'
      from dba_temp_files
     where tablespace_name = 'ONLYDWTEMP';

     

    ALTER database TEMPFILE  '/oradata/ONLYDWTEMP06.dbf' resize 100M ;

    ALTER database TEMPFILE  '/oradata/ONLYDWTEMP07.dbf' resize 100M ;

    ALTER database TEMPFILE  '/oradata/ONLYDWTEMP08.dbf' resize 100M ;

    ALTER database TEMPFILE  '/oradata/ONLYDWTEMP09.dbf' resize 100M ;

    ALTER database TEMPFILE  '/oradata/ONLYDWTEMP10.dbf' resize 100M ;

    ALTER database TEMPFILE  '/oradata/ONLYDWTEMP01.dbf' resize 100M ;

    ALTER database TEMPFILE  '/oradata/ONLYDWTEMP02.dbf' resize 100M ;

    ALTER database TEMPFILE  '/oradata/ONLYDWTEMP03.dbf' resize 100M ;

    ALTER database TEMPFILE  '/oradata/ONLYDWTEMP04.dbf' resize 100M ;

    ALTER database TEMPFILE  '/oradata/ONLYDWTEMP05.dbf' resize 100M ;

     

    SQL> ALTER database TEMPFILE  '/oradata/ONLYDWTEMP09.dbf' resize 100M ;

     

    ALTER database TEMPFILE  '/oradata/ONLYDWTEMP09.dbf' resize 100M

     

    ORA-03297: file contains used data beyond requested RESIZE value

    创建新的临时表空间
    SQL> create temporary tablespace TEMP1 TEMPFILE '/oradata/TEMP1_01.dbf' size 100M;

     

    Tablespace created

     

    SQL> create temporary tablespace TEMP2 TEMPFILE '/oradata/TEMP2_01.dbf' size 100M;

     

    Tablespace created

    将当前临时表空间指定为新的临时表空间
    SQL> alter database default temporary tablespace TEMP1;

     

    Database altered

    删除旧的临时表空间
    SQL> drop tablespace ONLYDWTEMP including contents and datafiles;

     

    Tablespace dropped

    检查当前用户的临时表空间
    9i之前，如果一个数据库用户没有被指定默认临时表空间，那么oracle就会使用system表空间作为该用户的临时表空间，这是很危险的。在9i里边，database可以被指定一个默认临时表空间。这样如果数据库用户没有被明确指定临时表空间，oracle9i就会自动指定database的默认临时表空间作为该用户的临时表空间。

    我们可以通过下面的语句来查询数据库的默认临时表空间。

    select * from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';

    如下语句可以查询数据库用户的临时表空间。

    select username,default_tablespace,temporary_tablespace from dba_users

    如果删除了用户的临时表空间，而这个临时表空间又不是默认临时表空间（如果是数据库的默认临时表空间是删不掉的），用户的临时表空间不会自动转换到数据库的默认临时表空间上。这时如果使用该用户登陆，并执行查询用到临时空间，就会报错。解决方式就是将用户的临时表空间修改过来。

     

    SQL> alter user CTL temporary tablespace TEMP1;

     

    User altered

     

    SQL> alter user ODS temporary tablespace TEMP2;

     

    User altered

     

    select username,default_tablespace,temporary_tablespace from dba_users

    user
     tablespace
     temporary tablespace
     
    SCOTT
     SYSTEM
     ONLYDWTEMP
     
    CTL
     CTL
     TEMP1
     
    ODS
     ODSD
     TEMP2
     

     

    SQL> drop tablespace TEMP2 including contents and datafiles;

     

    Tablespace dropped

     

    select username,default_tablespace,temporary_tablespace from dba_users

    user
     tablespace
     temporary tablespace
     
    SCOTT
     SYSTEM
     ONLYDWTEMP
     
    CTL
     CTL
     TEMP1
     
    ODS
     ODSD
     TEMP2
     

     

    SQL> conn ods/ods@test_BI_DW

    Connected to Oracle9i Enterprise Edition Release 9.2.0.8.0

    Connected as ods

     

    SQL> select  do.object_name from dba_objects do, dba_objects db order by 1;

    Tablespace TEMP2 not found

     

     

    SQL> alter user ODS temporary tablespace TEMP1;

     

    User altered

     

    以上的方法只是暂时释放了临时表空间的磁盘，是治标但不是治本的方法。真正的治本方法是找出数据库中消耗资源比较大的sql语句，然后对其进行优化处理。

    Select se.username,
           se.sid,
           su.extents,
           su.blocks * to_number(rtrim(p.value)) as Space,
           tablespace,
           segtype,
           sql_text
      from v$sort_usage su, v$parameter p, v$session se, v$sql s
     where p.name = 'db_block_size'
       and su.session_addr = se.saddr
       and s.hash_value = su.sqlhash
       and s.address = su.sqladdr
     order by se.username, se.sid

     

    查看(569) 评论(0) 收藏 分享 管理

  • 【转】ORA-01653:表空间扩展失败的问题

    2010-04-13 10:20:44

    ----查询表空间使用情况---
    使用DBA权限登陆
    SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
    D.TOT_GROOTTE_MB "表空间大小(M)",
    D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
    TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比",
    F.TOTAL_BYTES "空闲空间(M)",
    F.MAX_BYTES "最大块(M)"
    FROM (SELECT TABLESPACE_NAME,
    ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
    ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
    FROM SYS.DBA_FREE_SPACE
    GROUP BY TABLESPACE_NAME) F,
    (SELECT DD.TABLESPACE_NAME,
    ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
    FROM SYS.DBA_DATA_FILES DD
    GROUP BY DD.TABLESPACE_NAME) D
    WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
    ORDER BY 4 DESC;
    表空间名                       表空间大小(M) 已使用空间(M) 使用比 空闲空间(M) 最大块(M)
    ------------------------------ ------------- ------------- ------- ----------- ----------
    ...
    CCEN                                      10           8.5   85.00         1.5        .94
    ...

    发现表空间只有1.5M的空闲,猜测可能是表空间自动扩展失败的问题(表空间的增长量太高，ORACLE默认是50%),修改表空间文件扩展方式:


    SQL>ALTER DATABASE
        DATAFILE '/u01/oracle/oradata/orcl/ccen01.dbf' AUTOEXTEND
        ON NEXT 50M MAXSIZE UNLIMITED
    问题解决.


    查看表空间是否具有自动扩展的能力
    SELECT T.TABLESPACE_NAME,D.FILE_NAME,
    D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
    FROM DBA_TABLESPACES T,DBA_DATA_FILES D
    WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
    ORDER BY TABLESPACE_NAME,FILE_NAME;


    本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/flyingstarwb/archive/2008/05/03/2369134.aspx

    查看(187) 评论(0) 收藏 分享 管理

  • 【转】临时表空间相关知识点总结

    2010-04-13 10:07:12

    临时表空间的作用：

    　　临时表空间主要用途是在数据库进行排序运算[如创建索引、order by及group by、distinct、union/intersect/minus/、sort-merge及join、analyze命令]、管理索引[如创建索引、IMP进行数据导入]、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。

    　　当临时表空间不足时，表现为运算速度异常的慢，并且临时表空间迅速增长到最大空间（扩展的极限），并且一般不会自动清理了。

    　　如果临时表空间没有设置为自动扩展，则临时表空间不够时事务执行将会报ora-01652无法扩展临时段的错误，当然解决方法也很简单:1、设置临时数据文件自动扩展，或者2、增大临时表空间。

    　　临时表空间的相关操作：

    　　查询默认临时表空间：

    　　SQL> select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

    　　查询临时表空间状态:

    　　SQL> select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;

    扩展临时表空间：

    　　方法一、增大临时文件大小：

    　　SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ resize 100m;

    　　方法二、将临时数据文件设为自动扩展：

    　　SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ autoextend on next 5m maxsize unlimited;

    　　方法三、向临时表空间中添加数据文件：

    　　SQL> alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ size 100m;

    　　删除临时表空间的一个数据文件：

    　　SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' drop;

    　　删除临时表空间(彻底删除)：

    　　SQL> drop tablespace temp1 including contents and datafiles cascade constraints;

    　　创建临时表空间：

    　　SQL> create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/orcl/temp11.dbf' size 10M;

    　　更改系统的默认临时表空间（所有用户的默认临时表空间都将切换为新的临时表空间）：

    　　SQL> alter database default temporary tablespace temp1;

    更改某一用户的临时表空间：

    　　SQL> alter user scott temporary tablespace temp;

    查看各用户所对应的临时表空间：
       SQL> select username,temporary_tablespace from dba_users;

    查看(452) 评论(0) 收藏 分享 管理

  • 常用查询SQL--表空间相关

    2010-04-12 15:41:22

    ----查询表空间使用情况---
    使用DBA权限登陆
    SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
    D.TOT_GROOTTE_MB "表空间大小(M)",
    D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
    TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比",
    F.TOTAL_BYTES "空闲空间(M)",
    F.MAX_BYTES "最大块(M)"
    FROM (SELECT TABLESPACE_NAME,
    ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
    ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
    FROM SYS.DBA_FREE_SPACE
    GROUP BY TABLESPACE_NAME) F,
    (SELECT DD.TABLESPACE_NAME,
    ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
    FROM SYS.DBA_DATA_FILES DD
    GROUP BY DD.TABLESPACE_NAME) D
    WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
    ORDER BY 4 DESC;


    ----查看表空间是否具有自动扩展的能力----
    SELECT T.TABLESPACE_NAME,D.FILE_NAME,
    D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
    FROM DBA_TABLESPACES T,DBA_DATA_FILES D
    WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
    ORDER BY TABLESPACE_NAME,FILE_NAME;

    ----查询某个表空间所有的数据文件----

    select name from v$datafile where ts# in (select ts# from v$tablespace where name='USERS');
     

    ----查询某个表空间被使用的具体情况（被哪些用户所使用）----

     Select owner,decode(partition_name, null, segment_name, segment_name || ':' || partition_name) objectname
           ,segment_type objecttype
           ,nvl(bytes / 1048576, 0) SIZEMB
           ,nvl(initial_extent, 0) INITIALEXT
           ,nvl(next_extent, 0) NEXTEXT
           ,nvl(extents, 0) NUMEXTENTS
           ,nvl(max_extents, 0) "MAXEXTENTS"
    from   dba_segments
    where  tablespace_name = 'TOPO'
    order by sizemb desc

    ----查询默认临时表空间----

    SELECT H.TABLESPACE_NAME,
           (H.BYTES_FREE + H.BYTES_USED)/1024/1024 ALL_MB,
           H.BYTES_FREE/1024/1024 FREE_MB,'' LOC_TYPE
    FROM V$TEMP_SPACE_HEADER H order by TABLESPACE_NAME;

    ----查询用户的默认表空间和默认临时表空间----

    SELECT USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS;

    ----临时表空间查询相关----

    select * from dba_tablespaces; 
    select * from dba_temp_files; 
    select * from v$tempfile;

    查看(312) 评论(0) 收藏 分享 管理

  • 【sql】总结sql数据库性能优化相关的注意事项

    2010-01-22 10:08:55

    近期因工作需要，希望比较全面的总结下SQL SERVER数据库性能优化相关的注意事项，在网上搜索了一下,发现很多文章,有的都列出了上百条,但是仔细看发现，有很多似是而非或者过时(可能对SQL SERVER6.5以前的版本或者ORACLE是适用的)的信息，只好自己根据以前的经验和测试结果进行总结了。

    我始终认为，一个系统的性能的提高，不单单是试运行或者维护阶段的性能调优的任务，也不单单是开发阶段的事情，而是在整个软件生命周期都需要注意，进行有效工作才能达到的。所以我希望按照软件生命周期的不同阶段来总结数据库性能优化相关的注意事项。

    一、分析阶段

    一般来说，在系统分析阶段往往有太多需要关注的地方，系统各种功能性、可用性、可靠性、安全性需求往往吸引了我们大部分的注意力，但是，我们必须注意，性能是很重要的非功能性需求，必须根据系统的特点确定其实时性需求、响应时间的需求、硬件的配置等。最好能有各种需求的量化的指标。

    另一方面，在分析阶段应该根据各种需求区分出系统的类型，大的方面，区分是OLTP（联机事务处理系统）和OLAP（联机分析处理系统）。

    二、设计阶段

    设计阶段可以说是以后系统性能的关键阶段，在这个阶段，有一个关系到以后几乎所有性能调优的过程—数据库设计。

    在数据库设计完成后，可以进行初步的索引设计，好的索引设计可以指导编码阶段写出高效率的代码，为整个系统的性能打下良好的基础。

    以下是性能要求设计阶段需要注意的：

    1、 数据库逻辑设计的规范化

    数据库逻辑设计的规范化就是我们一般所说的范式，我们可以这样来简单理解范式：

    第1规范：没有重复的组或多值的列，这是数据库设计的最低要求。

    第2规范: 每个非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字的某些组成部分。消除部分依赖，大部分情况下，数据库设计都应该达到第二范式。

    第3规范: 一个非关键字段不能依赖于另一个非关键字段。消除传递依赖，达到第三范式应该是系统中大部分表的要求，除非一些特殊作用的表。

    更高的范式要求这里就不再作介绍了，个人认为，如果全部达到第二范式，大部分达到第三范式，系统会产生较少的列和较多的表，因而减少了数据冗余，也利于性能的提高。

    2、 合理的冗余

    完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。

    冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。

    冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。

    3、 主键的设计

    主键是必要的，SQL SERVER的主键同时是一个唯一索引，而且在实际应用中，我们往往选择最小的键组合作为主键，所以主键往往适合作为表的聚集索引。聚集索引对查询的影响是比较大的，这个在下面索引的叙述。

    在有多个键的表，主键的选择也比较重要，一般选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。

    主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。

    4、 外键的设计

    外键作为数据库对象，很多人认为麻烦而不用，实际上，外键在大部分情况下是很有用的，理由是：

    外键是最高效的一致性维护方法，数据库的一致性要求，依次可以用外键、CHECK约束、规则约束、触发器、客户端程序，一般认为，离数据越近的方法效率越高。

    谨慎使用级联删除和级联更新，级联删除和级联更新作为SQL SERVER 2000当年的新功能，在2005作了保留，应该有其可用之处。我这里说的谨慎，是因为级联删除和级联更新有些突破了传统的关于外键的定义，功能有点太过强大，使用前必须确定自己已经把握好其功能范围，否则，级联删除和级联更新可能让你的数据莫名其妙的被修改或者丢失。从性能看级联删除和级联更新是比其他方法更高效的方法。

    5、 字段的设计

    字段是数据库最基本的单位，其设计对性能的影响是很大的。需要注意如下：

    A、数据类型尽量用数字型，数字型的比较比字符型的快很多。

    B、 数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。

    C、 尽量不要允许NULL，除非必要，可以用NOT NULL+DEFAULT代替。

    D、少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。

    E、 自增字段要慎用，不利于数据迁移。

     

    6、 数据库物理存储和环境的设计

    在设计阶段，可以对数据库的物理存储、操作系统环境、网络环境进行必要的设计，使得我们的系统在将来能适应比较多的用户并发和比较大的数据量。

    这里需要注意文件组的作用，适用文件组可以有效把I/O操作分散到不同的物理硬盘，提高并发能力。

    7、 系统设计

    整个系统的设计特别是系统结构设计对性能是有很大影响的，对于一般的OLTP系统，可以选择C/S结构、三层的C/S结构等，不同的系统结构其性能的关键也有所不同。

    系统设计阶段应该归纳一些业务逻辑放在数据库编程实现，数据库编程包括数据库存储过程、触发器和函数。用数据库编程实现业务逻辑的好处是减少网络流量并可更充分利用数据库的预编译和缓存功能。

    8、 索引的设计

    在设计阶段，可以根据功能和性能的需求进行初步的索引设计，这里需要根据预计的数据量和查询来设计索引，可能与将来实际使用的时候会有所区别。

    关于索引的选择，应改主意：

    A、根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。

    B、根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。

    C、把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。

    D、一个表不要加太多索引，因为索引影响插入和更新的速度。

     

    三、编码阶段

    编码阶段是本文的重点，因为在设计确定的情况下，编码的质量几乎决定了整个系统的质量。

    编码阶段首先是需要所有程序员有性能意识，也就是在实现功能同时有考虑性能的思想，数据库是能进行集合运算的工具，我们应该尽量的利用这个工具，所谓集合运算实际是批量运算，就是尽量减少在客户端进行大数据量的循环操作，而用SQL语句或者存储过程代替。关于思想和意识，很难说得很清楚，需要在编程过程中来体会。

    下面罗列一些编程阶段需要注意的事项：

    1、 只返回需要的数据

    返回数据到客户端至少需要数据库提取数据、网络传输数据、客户端接收数据以及客户端处理数据等环节，如果返回不需要的数据，就会增加服务器、网络和客户端的无效劳动，其害处是显而易见的，避免这类事件需要注意：

    A、横向来看，不要写SELECT *的语句，而是选择你需要的字段。

    B、纵向来看，合理写WHERE子句，不要写没有WHERE的SQL语句。

    C、 注意SELECT INTO后的WHERE子句，因为SELECT INTO把数据插入到临时表，这个过程会锁定一些系统表，如果这个WHERE子句返回的数据过多或者速度太慢，会造成系统表长期锁定，诸塞其他进程。

    D、对于聚合查询，可以用HAVING子句进一步限定返回的行。

    2、尽量少做重复的工作

    这一点和上一点的目的是一样的，就是尽量减少无效工作，但是这一点的侧重点在客户端程序，需要注意的如下：

    A、控制同一语句的多次执行，特别是一些基础数据的多次执行是很多程序员很少注意的。

    B、减少多次的数据转换，也许需要数据转换是设计的问题，但是减少次数是程序员可以做到的。

    C、杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销。

    D、合并对同一表同一条件的多次UPDATE，比如

    1. UPDATE EMPLOYEE SET FNAME=’HAIWER’ WHERE EMP_ID=’ VPA30890F’

    2. UPDATE EMPLOYEE SET LNAME=’YANG’ WHERE EMP_ID=’ VPA30890F’        

    这两个语句应该合并成以下一个语句

    1.     UPDATE EMPLOYEE SET FNAME=’HAIWER’,LNAME=’YANG’ 

    2.     WHERE EMP_ID=’ VPA30890F’

    E、 UPDATE操作不要拆成DELETE操作+INSERT操作的形式，虽然功能相同，但是性能差别是很大的。

    F、 不要写一些没有意义的查询，比如

        SELECT * FROM EMPLOYEE WHERE 1=2

    3、 注意事务和锁

    事务是数据库应用中和重要的工具，它有原子性、一致性、隔离性、持久性这四个属性，很多操作我们都需要利用事务来保证数据的正确性。在使用事务中我们需要做到尽量避免死锁、尽量减少阻塞。具体以下方面需要特别注意：

    A、事务操作过程要尽量小，能拆分的事务要拆分开来。

    B、 事务操作过程不应该有交互，因为交互等待的时候，事务并未结束，可能锁定了很多资源。

    C、 事务操作过程要按同一顺序访问对象。

    D、提高事务中每个语句的效率，利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间。

    E、 尽量不要指定锁类型和索引，SQL SERVER允许我们自己指定语句使用的锁类型和索引，但是一般情况下，SQL SERVER优化器选择的锁类型和索引是在当前数据量和查询条件下是最优的，我们指定的可能只是在目前情况下更有，但是数据量和数据分布在将来是会变化的。

    F、 查询时可以用较低的隔离级别，特别是报表查询的时候，可以选择最低的隔离级别（未提交读）。

    4、 注意临时表和表变量的用法

    在复杂系统中，临时表和表变量很难避免，关于临时表和表变量的用法，需要注意：

    A、如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成。

    B、 如果需要多次用到一个大表的同一部分数据，考虑用临时表和表变量暂存这部分数据。

    C、 如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据。

    D、其他情况下，应该控制临时表和表变量的使用。

    E、 关于临时表和表变量的选择，很多说法是表变量在内存，速度快，应该首选表变量，但是在实际使用中发现，这个选择主要考虑需要放在临时表的数据量，在数据量较多的情况下，临时表的速度反而更快。

    F、 关于临时表产生使用SELECT INTO和CREATE TABLE + INSERT INTO的选择，我们做过测试，一般情况下，SELECT INTO会比CREATE TABLE + INSERT INTO的方法快很多，但是SELECT INTO会锁定TEMPDB的系统表SYSOBJECTS、SYSINDEXES、SYSCOLUMNS，在多用户并发环境下，容易阻塞其他进程，所以我的建议是，在并发系统中，尽量使用CREATE TABLE + INSERT INTO，而大数据量的单个语句使用中，使用SELECT INTO。

    G、  注意排序规则，用CREATE TABLE建立的临时表，如果不指定字段的排序规则，会选择TEMPDB的默认排序规则，而不是当前数据库的排序规则。如果当前数据库的排序规则和TEMPDB的排序规则不同，连接的时候就会出现排序规则的冲突错误。一般可以在CREATE TABLE建立临时表时指定字段的排序规则为DATABASE_DEFAULT来避免上述问题。

    5、 子查询的用法

    子查询是一个 SELECT 查询，它嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询。

    子查询可以使我们的编程灵活多样，可以用来实现一些特殊的功能。但是在性能上，往往一个不合适的子查询用法会形成一个性能瓶颈。

    如果子查询的条件中使用了其外层的表的字段，这种子查询就叫作相关子查询。相关子查询可以用IN、NOT IN、EXISTS、NOT EXISTS引入。

    关于相关子查询，应该注意：

    A、NOT IN、NOT EXISTS的相关子查询可以改用LEFT JOIN代替写法。比如：

     

    1.     SELECT PUB_NAME

    2.     FROM PUBLISHERS

    3.     WHERE PUB_ID NOT IN

    4.        (SELECT PUB_ID

    5.        FROM TITLES

    6.        WHERE TYPE = 'BUSINESS')

                可以改写成：

    1.    SELECT A.PUB_NAME

    2.    FROM PUBLISHERS A LEFT JOIN TITLES B

    3.    ON        B.TYPE = 'BUSINESS' AND

    4.              A.PUB_ID=B. PUB_ID

    5.    WHERE B.PUB_ID IS NULL

     

    1.     SELECT TITLE

    2.     FROM TITLES

    3.     WHERE NOT EXISTS

    4.        (SELECT TITLE_ID

    5.        FROM SALES

    6.        WHERE TITLE_ID = TITLES.TITLE_ID)

    可以改写成：

    1.     SELECT TITLE

    2.     FROM TITLES LEFT JOIN SALES

    3.     ON SALES.TITLE_ID = TITLES.TITLE_ID

    4.     WHERE SALES.TITLE_ID IS NULL

    B、 如果保证子查询没有重复 ，IN、EXISTS的相关子查询可以用INNER JOIN 代替。比如：

    1.    SELECT PUB_NAME

    2.    FROM PUBLISHERS

    3.    WHERE PUB_ID IN

    4.       (SELECT PUB_ID

    5.       FROM TITLES

    6.       WHERE TYPE = 'BUSINESS')

    可以改写成：

    1.    SELECT DISTINCT A.PUB_NAME

    2.    FROM PUBLISHERS A INNER JOIN TITLES B

    3.    ON        B.TYPE = 'BUSINESS' AND

    4.              A.PUB_ID=B. PUB_ID

     

    C、 IN的相关子查询用EXISTS代替，比如

    1.     SELECT PUB_NAME

    2.     FROM PUBLISHERS

    3.     WHERE PUB_ID IN

    4.        (SELECT PUB_ID

    5.        FROM TITLES

    6.        WHERE TYPE = 'BUSINESS')

    可以用下面语句代替：

    1.     SELECT PUB_NAME

    2.     FROM PUBLISHERS

    3.     WHERE EXISTS

    4.        (SELECT 1

    5.        FROM TITLES

    6.        WHERE TYPE = 'BUSINESS' AND

    7.        PUB_ID= PUBLISHERS.PUB_ID)

    D、不要用COUNT(*)的子查询判断是否存在记录，最好用LEFT JOIN或者EXISTS，比如有人写这样的语句：

    1.     SELECT JOB_DESC FROM JOBS

    2.     WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)=0

    应该改成：

    1.     SELECT JOBS.JOB_DESC FROM JOBS LEFT JOIN EMPLOYEE 

    2.     ON EMPLOYEE.JOB_ID=JOBS.JOB_ID

    3.     WHERE EMPLOYEE.EMP_ID IS NULL

     

    1.     SELECT JOB_DESC FROM JOBS

    2.     WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)<>0

    应该改成：

    1.     SELECT JOB_DESC FROM JOBS

    2.     WHERE EXISTS (SELECT 1 FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)

    6、 慎用游标

    数据库一般的操作是集合操作，也就是对由WHERE子句和选择列确定的结果集作集合操作，游标是提供的一个非集合操作的途径。一般情况下，游标实现的功能往往相当于客户端的一个循环实现的功能，所以，大部分情况下，我们把游标功能搬到客户端。

    游标是把结果集放在服务器内存，并通过循环一条一条处理记录，对数据库资源（特别是内存和锁资源）的消耗是非常大的，所以，我们应该只有在没有其他方法的情况下才使用游标。

    另外，我们可以用SQL SERVER的一些特性来代替游标，达到提高速度的目的。

    A、字符串连接的例子

    这是论坛经常有的例子，就是把一个表符合条件的记录的某个字符串字段连接成一个变量。比如需要把JOB_ID=10的EMPLOYEE的FNAME连接在一起，用逗号连接，可能最容易想到的是用游标：

          

    1.     DECLARE@NAMEVARCHAR(20)

    2.     DECLARE@NAMEVARCHAR(1000)

    3.     DECLARENAME_CURSORCURSORFOR

    4.     SELECTFNAMEFROMEMPLOYEEWHEREJOB_ID=10ORDERBYEMP_ID

    5.     OPENNAME_CURSOR

    6.     FETCHNEXTFROMRNAME_CURSORINTO@NAME

    7.     WHILE@@FETCH_STATUS=0

    8.     BEGIN

    9.     SET@NAMES=ISNULL(@NAMES+’,’,’’)+@NAME

    10.  FETCHNEXTFROMNAME_CURSORINTO@NAME

    11.  END

    12.  CLOSENAME_CURSOR

    13.  DEALLOCATENAME_CURSOR

    可以如下修改，功能相同：

    1.        DECLARE @NAME VARCHAR(1000)

    2.        SELECT @NAMES = ISNULL(@NAMES+’,’,’’)+FNAME

    3.           FROM EMPLOYEE WHERE JOB_ID=10 ORDER BY EMP_ID

    B、 用CASE WHEN 实现转换的例子

    很多使用游标的原因是因为有些处理需要根据记录的各种情况需要作不同的处理，实际上这种情况，我们可以用CASE WHEN语句进行必要的判断处理，而且CASE WHEN是可以嵌套的。比如:

    表结构:

    1.     CREATE TABLE 料件表(

    2.     料号           VARCHAR(30),

    3.     名称           VARCHAR(100),

    4.     主单位         VARCHAR(20),

    5.     单位1         VARCHAR(20),

    6.     单位1参数      NUMERIC(18,4),

    7.     单位2         VARCHAR(20),

    8.     单位2参数      NUMERIC(18,4)

    9.     )

    10.  

    11.  GO

    12.  

    13.  CREATE TABLE 入库表(

    14.  时间               DATETIME,

    15.  料号               VARCHAR(30),

    16.  单位               INT,

    17.  入库数量           NUMERIC(18,4),

    18.  损坏数量           NUMERIC(18,4)

    19.  )

    20.  

    21.  GO

    其中，单位字段可以是0，1，2，分别代表主单位、单位1、单位2，很多计算需要统一单位，统一单位可以用游标实现：

    1.     DECLARE @料号     VARCHAR(30),

    2.             @单位   INT,

    3.             @参数      NUMERIC(18,4),

    4.     

    5.     DECLARE CUR CURSOR FOR

    6.             SELECT 料号,单位 FROM 入库表 WHERE 单位 <>0

    7.     OPEN CUR

    8.     FETCH NEXT FROM CUR INTO @料号,@单位

    9.     WHILE @@FETCH_STATUS<>-1

    10.  BEGIN

    11.    IF @单位=1

    12.    BEGIN

    13.      SET @参数=(SELECT 单位1参数 FROM 料件表 WHERE 料号 =@料号)

    14.      UPDATE 入库表 SET 数量=数量*@参数,损坏数量=损坏数量*@参数,单位=1 WHERE CURRENT OF CUR

    15.    END

    16.    IF @单位=2

    17.    BEGIN

    18.      SET @参数=(SELECT 单位1参数 FROM 料件表 WHERE 料号 =@料号)

    19.      UPDATE 入库表 SET 数量=数量*@参数,损坏数量=损坏数量*@参数,单位=1 WHERE CURRENT OF CUR

    20.    END

    21.    FETCH NEXT FROM CUR INTO @料号,@单位

    22.  END

    23.  CLOSE CUR

    24.  DEALLOCATE CUR

               可以改写成：

    1.     UPDATE A SET 

    2.     数量=CASE A.单位 WHEN 1 THEN      A.数量*B. 单位1参数

    3.                        WHEN 2 THEN         A.数量*B. 单位2参数

    4.                        ELSE A.数量

    5.     END,                  

    6.     损坏数量= CASE A.单位 WHEN 1 THEN    A. 损坏数量*B. 单位1参数

    7.                        WHEN 2 THEN         A. 损坏数量*B. 单位2参数

    8.                        ELSE A. 损坏数量

    9.     END,

    10.  单位=1 

    11.  FROM入库表 A, 料件表 B

    12.  WHERE    A.单位<>1      AND

    13.           A.料号=B.料号

    C、 变量参与的UPDATE语句的例子

    SQL ERVER的语句比较灵活，变量参与的UPDATE语句可以实现一些游标一样的功能，比如：

    在

    1.     SELECT A,B,C,CAST(NULL AS INT) AS 序号

    2.     INTO #T

    3.     FROM 表

    4.     ORDER BY A ,NEWID()

    产生临时表后，已经按照A字段排序，但是在A相同的情况下是乱序的，这时如果需要更改序号字段为按照A字段分组的记录序号，就只有游标和变量参与的UPDATE语句可以实现了，这个变量参与的UPDATE语句如下：

    1.     DECLARE @A INT

    2.     DECLARE @序号 INT

    3.     UPDATE #T SET

    4.        @序号=CASE WHEN A=@A THEN @序号+1 ELSE 1 END,

    5.        @A=A,

    6.        序号=@序号

    D、如果必须使用游标，注意选择游标的类型，如果只是循环取数据，那就应该用只进游标（选项FAST_FORWARD），一般只需要静态游标（选项STATIC）。

    E、 注意动态游标的不确定性，动态游标查询的记录集数据如果被修改，会自动刷新游标，这样使得动态游标有了不确定性，因为在多用户环境下，如果其他进程或者本身更改了纪录，就可能刷新游标的记录集。

    7、 尽量使用索引

    建立索引后，并不是每个查询都会使用索引，在使用索引的情况下，索引的使用效率也会有很大的差别。只要我们在查询语句中没有强制指定索引，索引的选择和使用方法是SQLSERVER的优化器自动作的选择，而它选择的根据是查询语句的条件以及相关表的统计信息，这就要求我们在写SQL语句的时候尽量使得优化器可以使用索引。

    为了使得优化器能高效使用索引，写语句的时候应该注意：

    A、不要对索引字段进行运算，而要想办法做变换，比如

    SELECT ID FROM T WHERE NUM/2=100

    应改为:

    SELECT ID FROM T WHERE NUM=100*2

     

    SELECT ID FROM T WHERE NUM/2=NUM1

    如果NUM有索引应改为:

    SELECT ID FROM T WHERE NUM=NUM1*2

    如果NUM1有索引则不应该改。

     

    发现过这样的语句：

    1.     SELECT 年,月,金额 FROM 结余表

    2.     WHERE 100*年+月=2007*100+10

    应该改为：

    1.     SELECT 年,月,金额 FROM 结余表

    2.     WHERE 年=2007 AND

    3.           月=10

     

    B、 不要对索引字段进行格式转换

    日期字段的例子：

    WHERE CONVERT(VARCHAR(10), 日期字段,120)=’2008-08-15’

    应该改为

    WHERE日期字段〉=’2008-08-15’      AND   日期字段<’2008-08-16’

     

    ISNULL转换的例子：

    WHERE ISNULL(字段,’’)<>’’应改为:WHERE字段<>’’

    WHERE ISNULL(字段,’’)=’’不应修改

    WHERE ISNULL(字段,’F’) =’T’应改为: WHERE字段=’T’

    WHERE ISNULL(字段,’F’)<>’T’不应修改

    C、 不要对索引字段使用函数

    WHERE LEFT(NAME, 3)='ABC' 或者WHERE SUBSTRING(NAME,1, 3)='ABC'

    应改为:

    WHERE NAME LIKE 'ABC%'

     

    日期查询的例子：

    WHERE DATEDIFF(DAY, 日期,'2005-11-30')=0应改为:WHERE 日期 >='2005-11-30' AND 日期 <'2005-12-1‘

    WHERE DATEDIFF(DAY, 日期,'2005-11-30')>0应改为:WHERE 日期 <'2005-11-30‘

    WHERE DATEDIFF(DAY, 日期,'2005-11-30')>=0应改为:WHERE 日期 <'2005-12-01‘

    WHERE DATEDIFF(DAY, 日期,'2005-11-30')<0应改为:WHERE 日期>='2005-12-01‘

    WHERE DATEDIFF(DAY, 日期,'2005-11-30')<=0应改为:WHERE 日期>='2005-11-30‘

    D、不要对索引字段进行多字段连接

    比如：

    WHERE FAME+ ’.’+LNAME=‘HAIWEI.YANG’

    应改为:

    WHERE FNAME=‘HAIWEI’ AND LNAME=‘YANG’

    8、 注意连接条件的写法

    多表连接的连接条件对索引的选择有着重要的意义，所以我们在写连接条件条件的时候需要特别的注意。

    A、多表连接的时候，连接条件必须写全，宁可重复，不要缺漏。

    B、 连接条件尽量使用聚集索引

    C、 注意ON部分条件和WHERE部分条件的区别

     

    9、 其他需要注意的地方

    经验表明，问题发现的越早解决的成本越低，很多性能问题可以在编码阶段就发现，为了提早发现性能问题，需要注意：

    A、程序员注意、关心各表的数据量。

    B、 编码过程和单元测试过程尽量用数据量较大的数据库测试，最好能用实际数据测试。

    C、 每个SQL语句尽量简单

    D、不要频繁更新有触发器的表的数据

    E、 注意数据库函数的限制以及其性能

    10、学会分辩SQL语句的优劣

    自己分辨SQL语句的优劣非常重要，只有自己能分辨优劣才能写出高效的语句。

    A、 查看SQL语句的执行计划，可以在查询分析其使用CTRL+L图形化的显示执行计划，一般应该注意百分比最大的几个图形的属性，把鼠标移动到其上面会显示这个图形的属性，需要注意预计成本的数据，也要注意其标题，一般都是CLUSTERED INDEX SEEK 、INDEX SEEK 、CLUSTERED INDEX SCAN 、INDEX SCAN 、TABLE SCAN等，其中出现SCAN说明语句有油画的余地。也可以用语句

    SET SHOWPLAN_ALL ON

    要执行的语句

    SET SHOWPLAN_ALL OFF

    查看执行计划的文本详细信息。

    B、用事件探查器跟踪系统的运行，可疑跟踪到执行的语句，以及所用的时间，CPU用量以及I/O数据，从而分析语句的效率。

    C、可以用WINDOWS的系统性能检测器，关注CPU、I/O参数

    四、测试、试运行、维护阶段

    测试的主要任务是发现并修改系统的问题，其中性能问题也是一个重要的方面。重点应该放在发现有性能问题的地方，并进行必要的优化。主要进行语句优化、索引优化等。

    试运行和维护阶段是在实际的环境下运行系统，发现的问题范围更广，可能涉及操作系统、网络以及多用户并发环境出现的问题，其优化也扩展到操作系统、网络以及数据库物理存储的优化。

    这个阶段的优花方法在这里不再展开，只说明下索引维护的方法：

    A、              可以用DBCC DBREINDEX语句或者SQL SERVER维护计划设定定时进行索引重建，索引重建的目的是提高索引的效能。

    B、               可以用语句UPDATE STATISTICS或者SQL SERVER维护计划设定定时进行索引统计信息的更新，其目的是使得统计信息更能反映实际情况，从而使得优化器选择更合适的索引。

    C、               可以用DBCC CHECKDB或者DBCC CHECKTABLE语句检查数据库表和索引是否有问题，这两个语句也能修复一般的问题。

    D、             

    五、             网上资料中一些说法的个人理解

    1、 “应尽量避免在 WHERE 子句中对字段进行 NULL 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

    SELECT ID FROM T WHERE NUM IS NULL

    可以在NUM上设置默认值0，确保表中NUM列没有NULL值，然后这样查询：

    SELECT ID FROM T WHERE NUM=0”

    个人意见：经过测试，IS NULL也是可以用INDEX SEEK查找的，0和NULL是不同概念的，以上说法的两个查询的意义和记录数是不同的。

    2、 “应尽量避免在 WHERE 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。”

    个人意见：经过测试，<>也是可以用INDEX SEEK查找的。

    3、 “应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

    SELECT ID FROM T WHERE NUM=10 OR NUM=20

    可以这样查询：

    SELECT ID FROM T WHERE NUM=10

    UNION ALL

    SELECT ID FROM T WHERE NUM=20”

    个人意见：主要对全表扫描的说法不赞同。

    4、 “IN 和 NOT IN 也要慎用，否则会导致全表扫描，如：

    SELECT ID FROM T WHERE NUM IN(1,2,3)

    对于连续的数值，能用 BETWEEN 就不要用 IN 了：

    SELECT ID FROM T WHERE NUM BETWEEN 1 AND 3”

    个人意见：主要对全表扫描的说法不赞同。

    5、 “如果在 WHERE 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

    SELECT ID FROM T WHERE NUM=@NUM

    可以改为强制查询使用索引：

    SELECT ID FROM T WITH(INDEX(索引名)) WHERE NUM=@NUM”

    个人意见：关于局部变量的解释比较奇怪，使用参数如果会影响性能，那存储过程就该校除了，我坚持我上面对于强制索引的看法。

    6、 “尽可能的使用 VARCHAR/NVARCHAR 代替 CHAR/NCHAR ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。”

    个人意见：“在一个相对较小的字段内搜索效率显然要高些”显然是对的，但是字段的长短似乎不是由变不变长决定，而是业务本身决定。在SQLSERVER6.5或者之前版本，不定长字符串字段的比较速度比定长的字符串字段的比较速度慢很多，所以对于那些版本，我们都是推荐使用定长字段存储一些关键字段。而在2000版本，修改了不定长字符串字段的比较方法，与定长字段的比较速度差别不大了，这样为了方便，我们大量使用不定长字段。

    7、 关于连接表的顺序或者条件的顺序的说法，经过测试，在SQL SERVER，这些顺序都是不影响性能的，这些说法可能是对ORACLE有效。

     出处忘了：作者见谅

     

     

    查看(198) 评论(0) 收藏 分享 管理

  • ORACLE-LOB字段

    2010-01-21 13:48:52

       在ORACLE数据库中，LOB（Large Objects—大对象）是用来存储大量的二进制和文本数据的一种数据类型（一个LOB字段可存储可多达4GB的数据）。目前，它又分为两种类型：内部LOB和外部LOB。内部LOB将数据以字节流的形式存储在数据库的内部。因而，内部LOB的许多操作都可以参与事务，也可以像处理普通数据一样对其进行备份和恢复操作。Oracle8i支持三种类型的内部LOB：BLOB（二进制数据）、CLOB（单字节字符数据）、NCLOB（多字节国家字符数据）。其中CLOB和NCLOB类型适用于存储超长的文本数据，BLOB字段适用于存储大量的二进制数据，如图像、视频、音频等。

       可通过以下语句可查看clob字段的信息：select   dbms_lob.substr(col_lob)   from   table_name

    查看(711) 评论(0) 收藏 分享 管理

  • 【转】ORACLE初始化参数文件详细文档

    2010-01-11 10:24:06

    1.简介

    在9i之前，参数文件只有一种，它是文本格式的，称为pfile，在9i及以后的版本中，新增了服务器参数文件,称为spfile,它是二进制格式的。这两种参数文件都是用来存储参 数配置以供oracle读取的，但也有不同点，注意以下几点：
    1)pfile是文本文件，spfile是二进制文件；
    2)对于参数的配置，pfile可以直接以文本编辑器打开手工配置，而spfile不行，必须在数据库启动后，通过sql命令进行在线修改，当然spfile也可用文本编辑器打开，把里面文本的内容复制出来放在pfile中，但决对不可以直接修改，直接修改后会使文件无法读取。
    3)pfile配置改变后，要使用其生效，必须重新启动数据库，spfile的配置生效时限和作用域可以由修改参数的sql命令指定，可以立即生效，也可以不立即生效。当然有些参数的修改必须重启数据库才能生效；
    4)可以用sql命令由pfile创建spfile,也可以由spfile创建pfile；
    5)如果是手动创建数据库而不是通过DBCA，则开始创建数据库时，你只能定义pfile 。因为它是文本格式的；

    现在一般普通生产库都采用spfile启动数据库，pfile确实没有用的理由了，感觉只有以下情况才会使用pfile：
    1)入门学习时，为了理解初始化参数
    2)创建数据库时，因为这时还没spfile
    3)恢复数据库时
    4)采用RAC的生产库中如果要共用spfile，可以建一个初始的pfile,然后在pfile指定spfile为共享设备文件

    2.创建spfile和pfile语法
    创建SPFILE(只能建在数据库所在的机器上)
    CREATE SPFILE [= 'spfile_name'] FROM PFILE [= 'pfile_name'];
    如果命令中未指定spfile的路径则会在缺省路径创建spfile文件（%ORACLE_HOME%\database\spfile<SID>.ora）
    这个与spfile参数无关(rac中共享spfile时要注意)
    创建PFILE
    CREATE PFILE [= 'pfile_name'] FROM SPFILE [= 'spfile_name'];
    如果命令中未指定spfile的路径则会从缺省路径（%ORACLE_HOME%\database\spfile<SID>.ora）创建pfile文件

    注：语法中创建和指定的spfile和pfile都是服务器路径
    注：不能创建已经由数据库实例启动的spfile(在nomount状态下也不行)

    3.数据库启动时spfile和pfile的加载顺序
    初始化参数是在数据库实例启动时(startup nomount)加载。
    如果startup命令中未指定pfile参数，在windows系统中数据库启动时会按如下顺序查找启动参数文件:
    %ORACLE_HOME%\database\SPFILE<SID>.ora
    %ORACLE_HOME%\database\SPFILE.ora
    %ORACLE_HOME%\database\init<SID>.ora
    在unix或linux系统中数据库启动时会按如下顺序查找启动参数文件:
    %ORACLE_HOME%\dbs\SPFILE<SID>.ora
    %ORACLE_HOME%\dbs\SPFILE.ora
    %ORACLE_HOME%\dbs\init<SID>.ora

    也就是说先找spfile<sid>.ora文件，如果spfile<sid>.ora没有则找spfile.ora文件，最后才会去找init<sid>.ora的pfile文件.

    使用指定pfile启动数据库则数据库会使用指定的pfile来加载数据库参数：
    命令如下：
    startup pfile='<pfile>'

    注：以pfile启动的实例,修改初始化参数时不能使用scope=spfile的语法
    如果参数文件(不管是pfile还是spfile)中指定了spfile的参数,则还会从指定的spfile中再读取配置的参数，这样数据库的参数就包括了两个文件的内容.
    4.数据库启动参数的加载顺序
    加载顺序按文件中顺序读取
    如果文件中有两个相同的参数名则以最后出现的为准，以下例子说明了这个问题。
    如有一个spfile文件(myspfile.ora)，包括两个参数:
    sessions=150
    processes=200
    另外有一个pfile文件(mypfile.ora)，里面包括三个参数，其中指定了spfile为前面的spfile文件
    processes=100
    sessions=100
    spfile='myspfile.ora'
    processes=180
    当使用mypfile.ora启动数据库时，会按如下顺序读取参数
    1)processes=100
    2) sessions=100
    3) spfile='myspfile.ora'
     3.1) sessions=150(重新加载)
     3.2) processes=200(重新加载)
    4)processes=180(再次重新加载)

    最后加载的数据库参数为
    sessions=150
    spfile='myspfile.ora'
    processes=180
    5.关于show parameter spfile时显示的内容
    如果是采用pfile指定了spfile参数，则显示指定的spfile
    如果是采用缺省的spfile启动，则显示缺省的spfile文件名，不管spfile还有没有指定其它的spfile文件。
    6.关于spfile递归调用
    如果出现spfile递归调用，则ORACLE启动时会报错：
    SQL> startup nomount;
    ORA-03113: 通信通道的文件结束
    SQL>
    spfile递归调用就是指
    spfile1.spfile='spfile2'
    spfile2.spfile='spfile1'
    这样的参数配置

    7.初始化参数的修改方法
    　　分为手动修改和在线修改。
    　　手动修改用于修改pfile，直接用文本编辑打开pfile修改。要使用修改生效，须重启数据库。
    　　在线修改是在数据库运行时，用alter system命令进行修改，命令如下(详细的命令 语句请参考oracle官方参考文档)：
    alter system set parameter_name = parameter_value [, parameter_value ]...
      [ COMMENT 'text' ]
      [ DEFERRED ]
      [ SCOPE = { MEMORY | SPFILE | BOTH } ]
      [ SID = { 'sid' | * } ]
    注：
    [ COMMENT 'text' ] :表示可以加注释，如 ALTER SYSTEM SET JOB_QUEUE_PROCESSES=50 SCOPE=BOTH DEFERRED COMMENT="  2008-05-19"
    [ DEFERRED ]:表示所作修改只适用于将来的会话，对当前已有的会话不影响，这个参数只对少数参数有用，具体可使用的参数可以查看v$parameter视图
    select * from v$parameter where issys_modifiable='DEFERRED'
    [ SCOPE = { MEMORY | SPFILE | BOTH } ]:
    SPFILE:修改只对SPFILE有效，不影响当前实例，需要重启数据库才能生效；
    MEMORY:修改只对内存有效，即只对当前实例有效，且立即生效，但不会保存到SPFILE, 数据库重启后此配置丢失；
    BOTH:顾名思义，包含以上两种，立即生效，且永久生效。
    所有的参数都可以使用spfile,部份参数可以使用memory，可以使用如下SQL查询不能使用memory方式修改的参数
    select * from v$parameter where issys_modifiable='FALSE'
    　　对于ALTER SYSTEM的参数修改命令，请注意以下几点：
    1)如果当前实例使用的是pfile而非spfile,则scope=spfile或scope=both会产生错误；
    2)如果实例以pfile启动，则scope的默认值为MEMORY,若以spfile启动，则默认值为BOTH；
    [ SID = { 'sid' | * } ]
    指定修改参数的作用实例号，如果为*则表示所有实例，这个选项用于RAC数据库。
    8.删除初始化参数：
    alter system reset parameter_name
      [ SCOPE = { MEMORY | SPFILE | BOTH } ]
      [ SID = 'sid' ]
    对于有些参数也可以使用 ALTER SYSTEM SET PARAMETER=''达到相同的效果;
    9.初始化参数的相关系统视图
    9.1v$parameter
    9.2v$parameter2
    9.3v$system_parameter
    9.4v$system_parameter2
    9.5gv$parameter
    9.6gv$parameter2
    9.7gv$system_parameter
    9.8gv$system_parameter2

    SQL> desc v$parameter;
    Name             Type          Nullable Default Comments
    ---------------- ------------- -------- ------- --------
    NUM              NUMBER        Y        编号                
    NAME             VARCHAR2(64)  Y        参数名称                
    TYPE             NUMBER        Y        参数类型
         (
         1 - Boolean
         2 - String
         3 - Integer
         4 - Parameter file
         5 - Reserved
         6 - Big integer
         )
    VALUE            VARCHAR2(512) Y        参数值                
    ISDEFAULT        VARCHAR2(9)   Y        是否为缺省值                
    ISSES_MODIFIABLE VARCHAR2(5)   Y        是否允许会话级动态调整(alter session set...)              
    ISSYS_MODIFIABLE VARCHAR2(9)   Y        是否允许使用动态调整(alter system set...)
         (
         IMMEDIATE:可以调整且修改会立即生效;
         DEFERRED:可以调整，但是在新建的会话中才生效;
         FALSE:不可调整;
         )                  
    ISMODIFIED       VARCHAR2(10)  Y        参数在实例启动后是否被修改过              
    ISADJUSTED       VARCHAR2(5)   Y        参数是否被调整
         (对于有一些参数，如规范要求是素数，但是参数里设置了合数，则ORACLE会自动调整成下一个素数)      
    DESCRIPTION      VARCHAR2(64)  Y        参数功能描述                
    UPDATE_COMMENT   VARCHAR2(255) Y        最近的参数更新备注                
     
    以下是ORACLE官方文档关于V$PARAMETER的解释
    V$PARAMETER displays information about the initialization parameters that are currently in effect for the session. A new session inherits parameter values from the instance-wide values displayed by the V$SYSTEM_PARAMETER view.
    以下是字段说明：
    NUM
     NUMBER
     Parameter number
     
    NAME
     VARCHAR2(64)
     Name of the parameter
     
    TYPE
     NUMBER
     Parameter type:
    1 - Boolean
    2 - String
    3 - Integer
    4 - Parameter file
    5 - Reserved
    6 - Big integer
     
    VALUE
     VARCHAR2(512)
     Parameter value for the session (if modified within the session); otherwise, the instance-wide parameter value
     
    ISDEFAULT
     VARCHAR2(9)
     Indicates whether the parameter is set to the default value (TRUE) or the parameter value was specified in the parameter file (FALSE)
     
    ISSES_MODIFIABLE
     VARCHAR2(5)
     Indicates whether the parameter can be changed with ALTER SESSION (TRUE) or not (FALSE)
     
    ISSYS_MODIFIABLE
     VARCHAR2(9)
     Indicates whether the parameter can be changed with ALTER SYSTEM and when the change takes effect:
    IMMEDIATE - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect immediately.
    DEFERRED - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect in subsequent sessions.
    FALSE - Parameter cannot be changed with ALTER SYSTEM unless a server parameter file was used to start the instance. The change takes effect in subsequent instances.
     
    ISMODIFIED
     VARCHAR2(10)
     Indicates whether the parameter has been modified after instance startup:
    MODIFIED - Parameter has been modified with ALTER SESSION
    SYSTEM_MOD - Parameter has been modified with ALTER SYSTEM (which causes all the currently logged in sessions' values to be modified)
    FALSE - Parameter has not been modified after instance startup
     
    ISADJUSTED
     VARCHAR2(5)
     Indicates whether Oracle adjusted the input value to a more suitable value (for example, the parameter value should be prime, but the user input a non-prime number, so Oracle adjusted the value to the next prime number)
     
    DESCRIPTION
     VARCHAR2(64)
     Description of the parameter
     
    UPDATE_COMMENT
     VARCHAR2(255)
     Comments associated with the most recent update
     
    V$PARAMETER2与V$PARAMETER的区别是当某个参数有多个值时，会显示多行，最典型的就是参数control_files，一般的数据库都会指定多个控制文件，在V$PARAMETER中只显示一行，但在V$PARAMETER2会显示多行，如下所示：
    SQL> select name,value from V$PARAMETER where name='control_files';
     
    NAME                                                             VALUE
    ----------------- --------------------------------------------------------------------------------
    control_files     H:\oracle\oradata\ydgl\control01.ctl, H:\oracle\oradata\ydgl\control02.ctl, H:\oracle\oradata\ydgl\control03.ctl

    SQL> select name,value,ORDINAL from V$PARAMETER2 where name='control_files';
     
    NAME                      VALUE                                                 ORDINAL
    ------------------- ----------------------------------------------------------- ----------
    control_files    H:\oracle\oradata\ydgl\control01.ctl                              1
    control_files    H:\oracle\oradata\ydgl\control02.ctl                              2
    control_files    H:\oracle\oradata\ydgl\control03.ctl'                             3
     
    V$PARAMETER2多了一个ORDINAL字段，表示顺序。
    SQL> desc v$parameter2;
    Name             Type          Nullable Default Comments
    ---------------- ------------- -------- ------- --------
    NUM              NUMBER        Y                        
    NAME             VARCHAR2(64)  Y                        
    TYPE             NUMBER        Y                        
    VALUE            VARCHAR2(512) Y                        
    ISDEFAULT        VARCHAR2(6)   Y                        
    ISSES_MODIFIABLE VARCHAR2(5)   Y                        
    ISSYS_MODIFIABLE VARCHAR2(9)   Y                        
    ISMODIFIED       VARCHAR2(10)  Y                        
    ISADJUSTED       VARCHAR2(5)   Y                        
    DESCRIPTION      VARCHAR2(64)  Y                        
    ORDINAL          NUMBER        Y                        
    UPDATE_COMMENT   VARCHAR2(255) Y                        

    V$SYSTEM_PARAMETER 和V$PARAMETER的区别是，V$PARAMETER显示的是当前系统的初始化参数，V$SYSTEM_PARAMETER显示的是新会话将使用的初始化参数。
    例如下例中，使用deferred参数修改了sore_area_size，在V$PARAMETER显示的老值，而V$SYSTEM_PARAMETER显示的是新值：
    SQL> show parameter sort_area_size;
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    sort_area_size                       integer     524288
     
    SQL> alter system set sort_area_size=603213 deferred;
     
    System altered
     
    SQL> select name,value from v$parameter where name='sort_area_size';
     
    NAME                                                             VALUE
    -------------------------------- --------------------------------------------------------------------------------
    sort_area_size                                                   524288
     
    SQL> select name,value from v$system_parameter where name='sort_area_size';
     
    NAME                                                             VALUE
    -------------------------------- --------------------------------------------------------------------------------
    sort_area_size                                                   603213
     
    SQL>
    SQL> desc v$system_parameter;
    Name             Type          Nullable Default Comments
    ---------------- ------------- -------- ------- --------
    NUM              NUMBER        Y                        
    NAME             VARCHAR2(64)  Y                        
    TYPE             NUMBER        Y                        
    VALUE            VARCHAR2(512) Y                        
    ISDEFAULT        VARCHAR2(9)   Y                        
    ISSES_MODIFIABLE VARCHAR2(5)   Y                        
    ISSYS_MODIFIABLE VARCHAR2(9)   Y                        
    ISMODIFIED       VARCHAR2(8)   Y                        
    ISADJUSTED       VARCHAR2(5)   Y                        
    DESCRIPTION      VARCHAR2(64)  Y                        
    UPDATE_COMMENT   VARCHAR2(255) Y 

    gv$parameter和v$parameter的区别的是gv$parameter多一个INST_ID字段(实例号)，这个用于RAC数据库系统中，INST_ID用于标识是哪个实例的信息，其实所有以gv$和以v$开始的系统视图的区别都是这样的。
    SQL> desc gv$parameter;
    Name             Type          Nullable Default Comments
    ---------------- ------------- -------- ------- --------
    INST_ID          NUMBER        Y                        
    NUM              NUMBER        Y                        
    NAME             VARCHAR2(64)  Y                        
    TYPE             NUMBER        Y                        
    VALUE            VARCHAR2(512) Y                        
    ISDEFAULT        VARCHAR2(9)   Y                        
    ISSES_MODIFIABLE VARCHAR2(5)   Y                        
    ISSYS_MODIFIABLE VARCHAR2(9)   Y                        
    ISMODIFIED       VARCHAR2(10)  Y                        
    ISADJUSTED       VARCHAR2(5)   Y                        
    DESCRIPTION      VARCHAR2(64)  Y                        
    UPDATE_COMMENT   VARCHAR2(255) Y           
    10.废弃参数
    Oracle数据库中，系统提供了几个视图可以查看系统参数的情况。视图V$OBSOLETE_PARAMETER中含有所有的过时(obsolete)和强调(underscored)参数。这里首先说明一下什么是Oracle的过时(obsolote)和强调(underscored)参数，过时参数，顾名思义就是在Oracle以前的版本中存在，但在新版本中已经淘汰了的参数，已经不再使用；而强调参数，是指那些在新版本中保留了下来，但是除非特殊需要不希望用户使用的那些参数。在视图V$OBSOLETE_PARAMETER中，包含这些参数的名称和一个标志字ISSPECIFIED，该标志字用来指出这个参数是否在init.ora文件中已实际设置。
    下面的SQL脚本列出了当前系统中所有的过时参数名称以及它们是否在当前系统中设定。

    SQL> SELECT name, isspecified FROM v$obsolete_parameter;
    上面谈到，Oracle系统并没有将V$OBSOLETE_PARAMETER视图中的所有参数均丢弃，而是将其中的一部分转换为强调参数，下面就来讨论如何查看这些参数是已被丢弃还是被转换。这可以通过系统视图X$KSPPO来查看，该视图中包含一个名为KSPPOFLAG的字段，用来指明该参数在当前版本中是被丢弃还是被强调，如果该值为1，则表示该参数已被丢弃，该值为2，则表明该参数现为强调参数。
    SELECT kspponm,
    DECODE(ksppoflg, 1,'Obsolete', 2, 'Underscored')
    FROM x$ksppo
    ORDER BY kspponm;
    注：该视图只在sys用户下可以看到。
    11.隐藏参数
    数据库初始化总共有近千个，可见的只有两三百个，其它的都是隐藏参数。
    隐藏参数的参数名都是以下划线开头，比较少用，一般用于进行ORACLE故障诊断或恢复数据库，或者是进行一些不成熟功能的测试。
    使用如下SQL可以查询到所有的初始化参数。
    --用户需要有访问内核表的权限，一般为sys用户登录
    select a.indx,
           a.inst_id,
           a.ksppinm name,
           a.ksppity type,
           a.ksppdesc DESCRIPTION,
           a.ksppiflg FLAG,
           b.ksppstvl value,
           b.ksppstdf ISDEFAULT,
           b.ksppstvf ORDINAL,
           b.ksppstcmnt UPDATE_COMMENT
      from sys.x$ksppi a, sys.x$ksppcv b
     where a.indx = b.indx
    12.自动SGA内存管理的参数
    ORACLE从10g开始可以自动管理SGA了.
    当初始化参数中设置了SGA_TARGET后,ORACLE会自动根据总的SGA大小动态调整SGA中几个区域的大小。当使用数据库关闭时，会将最后调整的大小记录到SPFILE中，以供下次系统启动时直接应用。
    自动调整后的分类参数在SPFILE中类似如下：
    以下是11g数据库SPFILE的部分内容，这是一个使用RAW设备两节点的RAC数据库：
    rawrac1.__db_cache_size=138412032
    rawrac2.__db_cache_size=134217728
    rawrac2.__java_pool_size=4194304
    rawrac2.__large_pool_size=4194304
    rawrac2.__oracle_base='C:\oracle'#ORACLE_BASE set from environment
    rawrac1.__oracle_base='C:\oracle'#ORACLE_BASE set from environment
    rawrac2.__pga_aggregate_target=155189248
    rawrac2.__sga_target=230686720
    rawrac2.__shared_io_pool_size=0
    rawrac1.__shared_pool_size=83886080
    rawrac2.__shared_pool_size=83886080
    rawrac2.__streams_pool_size=0
    *.memory_target=384827392
    参考：
    oralce官方参考文档

    查看(225) 评论(0) 收藏 分享 管理

  • 【转】REDO LOG 与 UNDO LOG这两个概念的区别

    2010-01-11 10:15:44

    重做日志：每当有操作执行前，将数据真正更改时，先前相关操作写入重做日志。这样当断电，或者一些意外，导致后续任务无法完成时，系统恢复后，可以继续完成这些更改

    撤消日志：当一些更改在执行一半时，发生意外，而无法完成，则可以根据撤消日志恢复到更改之前的壮态

    网上找到一些解说：以便以后自己参考
    有两个概念：前滚与回退
    比如某一时刻数据库DOWN机了，有两个事务，一个事务已经提交，另一个事务正在处理
    数据库重启的时候就要根据日志进行前滚及回退，把已提交事务的更改写到数据文件，未提交事务的更改恢复到事务开始前的状态。

    redo--> undo-->datafile
    insert一条记录时, 表跟undo的信息都会放进 redo 中, 在commit 或之前, redo 的信息会放进硬盘上. 故障时, redo 便可恢复那些已经commit 了的数据.
    redo->每次操作都先记录到redo日志中，当出现实例故障（像断电），导致数据未能更新到数据文件，则数据库重启时须redo，重新把数据更新到数据文件
    undo->记录更改前的一份copy，但你系统rollback时，把这份copy重新覆盖到原来的数据
    redo->记录所有操作，用于恢复（redo records all the database transaction used for recovery）
    undo->记录所有的前印象，用于回滚（undo is used to store uncommited data infor used for rollback）
    redo->已递交的事务,实例恢复时要写到数据文件去的
    undo->未递交的事务.
    redo的原因是：每次commit时，将数据的修改立即写到online redo中，但是并不一定同时将该数据的修改写到数据文件中。因为该数据已经提交，但是只存在联机日志文件中，所以在恢复时需要将数据从联机日志文件中找出来，重新应用一下，使已经更改数据在数据文件中也改过来！
    undo的原因是：在oracle正常运行时，为了提高效率，加入用户还没有commit,但是空闲内存不多时，会由DBWR进程将脏块写入到数据文件中，以便腾出宝贵的内存供其它进程使用。这就是需要UNDO的原因。因为还没有发出commit语句，但是oracle的dbwr进程已经将没有提交的数据写到数据文件中去了。
    undo 也是也是datafile， 可能dirty buffer 没有写回到磁盘里面去。
    只有先redo apply 成功了，才能保证undo datafile 里面的东西都是正确的，然后才能rollback
    做undo的目的是使系统恢复到系统崩溃前(关机前)的状态,再进行redo是保证系统的一致性.
    不做undo,系统就不会知道之前的状态,redo就无从谈起
    所以instance crash recovery 的时候总是先rollforward， 再rollback
    undo
    回退段中的数据是以“回退条目”方式存储。
    回退条目=块信息(在事务中发生改动的块的编号)+在事务提交前存储在块中的数据
    在每一个回退段中oracle都为其维护一张“事务表”
    在事务表中记录着与该回退段中所有回退条目相关的事务编号（事务SCN&回退条目）
    redo
    重做记录由一组“变更向量”组成。
    每个变更变量中记录了事务对数据库中某个块所做的修改。
    当用户提交一条commit语句时，LGWR进程会立刻将一条提交记录写入到重做日志文件中，然后再开始写入与该事务相关的重做信息。
    #事务提交成功后，Oracle将为该事备生成一个系统变更码（SCN）。事务的SCN将同时记录在它的提交记录和重做记录中。
    commit
    提交事务前完成的工作：
    ·在SGA区的回退缓存中生成该事务的回退条目。在回退条目中保存有该事务所修改的数据的原始版本。
    ·在SGA区的重做日志缓存中生成该事务的重做记录。重做记录中记载了该事务对数据块所进行的修改，并且还记载了对回退段中的数据块所进行的修改。缓存中的重做记录有可能在事务提交之前就写入硬盘中。
    ·在SGA区的数据库缓丰中记录了事务对数据库所进行的修改。这些修改也有可能在事务提交之前就写入硬盘中。
    提交事务时完成的工作：
    ·在为该事务指定的回退段中的内部事务表内记录下这个事务已经被提交，并且生成一个惟一的SCN记录在内部事务表中，用于惟一标识这个事务。
    ·LGWR后进进程将SGA区重做日志缓存中的重做记录写入联机重做日志文件。在写入重做日志的同时还将写入该事务的SCN。
    ·Oracle服务进程释放事务所使用的所有记录锁与表锁。
    ·Oracle通知用户事务提交完成。
    ·Oracle将该事务标记为已完成。
    rollback
    回退事务完成的工作：
    ·Oracle通过使用回退段中的回退条目，撤销事务中所有SQL语句对数据库所做的修改。
    ·Oracle服务进程释放事务所使用的所有锁
    ·Oracle通知事务回退成功。
    ·Oracle将该事务标记为已完成
    举个例子：
    insert into a(id) values(1);(redo)
    这条记录是需要回滚的。
    回滚的语句是delete from a where id = 1;(undo)
    试想想看。如果没有做insert into a(id) values(1);(redo)
    那么delete from a where id = 1;(undo)这句话就没有意义了。
    现在看下正确的恢复:
    先insert into a(id) values(1);(redo)
    然后delete from a where id = 1;(undo)
    系统就回到了原先的状态，没有这条记录了。

    查看(505) 评论(0) 收藏 分享 管理

  • 【转】Oracle数据库参数优化

    2010-01-11 10:13:26

    许多开发维护人员并没有真正理解Oracle数据库相关参数在系统运行中的作用，导致数据库系统的执行效率低下。因此，如何设计合理的Oracle数据库初始化参数就显得非常重要。

    为了访问数据库中的数据，Oracle数据库为所有用户提供一组后台进程，并且，有一些存储结构专门用来存储最近的有关对数据库访问的数据。这些存储区域可以通过减少对数据库文件的I/O次数来改善数据库性能。

    数据库实例就是用来访问一个数据库文件集的一个存储结构以及后台进程的集合。它使一个单独的数据库可以被多个实例访问（这是Oracle的并行服务器方式 ）。决定实例的组成以及大小的参数存储在文件init.ora中。这个文件在实例启动时候需要装载，也可以在运行中被DBA装载 。instance的init.ora文件通常包含instance 的名字，比如一个instance叫ORA9，其init.ora将被起名为initORA9.ora。本文结合笔者实际工作中的Oracle数据库使用经验讨论其相关参数的调整策略。

     
    通常需要设置的参数:
    ● DB_BLOCK_BUFFERS

    该参数决定了数据库缓冲区的大小，这部分内存的作用主要是在内存中缓存从数据库中读取的数据块，数据库缓冲区越大，为用户已经在内存里的共享数据提供的内存就越大，这样可以减少所需要的磁盘物理读写次数。在Oracle 9i以前版本数据库中，缓冲是由db_block_buffers*db_block_size来决定，db_block_sizBe参数是在创建数据库时设置的，它决定数据库里每个块的大小，9i以前不能改变已经存在的数据库的块大小，因此应该在最初创建数据库时就确保设置适当的数据库块大小。在创建数据库时设置为8k，db_block_buffers*db_block_size的乘积占物理内存的20%～30%，物理内存在1G以内，可以占20%，1G～2G之间可占25%，物理内存在2G以上，可以占30%，甚至更高。
    说明：Oracle 8i以后的数据缓冲区(data buffer)实际可由三部分组成，分别是默认池、保留池(buffer keep)和回收池(buffer recycle)。保留池可用于存放需要极快的访问的小表，回收池可用于存放需要全表扫描的大表。保留池和回收池的大小分别由参数buffer_pool_keep和buffer_pool_recycle决定。这两个参数通常在做性能调整时才设置，因此不建议设置这两个参数。

    ● shared_pool_size
    Shared pool由三部分组成，分别是Dictionary cache(包括数据字典的定义，如表结构、权限等)、Library cache（包括共享的sql游标、sql原代码以及执行计划、存储过程和会话信息）和Control structure。它的大小由初始化参数shared_pool_size控制，它的作用是缓存已经被解析过的SQL，使其能被重用，不用再解析。SQL的解析非常消耗CPU的资源，如果一条SQL在Shared pool中已经存在，则进行的仅是软解析（在Shared pool中寻找相同SQL），这将大大提高数据库的运行效率。当然，这部分内存也并非越大越好，太大的Shared pool，Oracle数据库为了维护共享结构，将付出更大的管理开销。这个参数的设置建议在150M～500M之间。如果系统内存为1G，该值可设为150M～200M;如果为2G，该值设为250M～300M；每增加1G内存，该值增加100M；但该值最大不应超过500M。（Shared pool不足，Oracle将报4031错）。

    ● shared_pool_reserved_size
    它的作用是在shared pool中保留一块区域以放置一些大对象（如大型软件包），如不设置此参数，系统缺省保留5%的shared pool空间用于放置大对象，不建议设置该参数，让系统自动分配即可。

    ● Sort_area_size

    该参数是当查询需要排序的时候，Oracle将使用这部分内存做排序，当内存不足时，使用临时表空间做排序。这个参数是针对会话(session)设置的，不是针对整个数据库。即如果应用有170个数据库连接（session），假设这些session都做排序操作，则Oracle会分配8×170等于1360M内存做排序，而这些内存是在Oracle的SGA区之外分配的，即如果SGA区分配了1.6G内存，Oracle还需要额外的1.3G内存做排序。建议该值设置不超过3M，当物理内存为1G时，该值宜设为1M或更低（如512K）；2G时可设为2M;但不论物理内存多大，该值也不应超过3M。

    ● sort_area_retained_size

    这个参数的含义是当排序完成后至少为session继续保留的排序内存的最小值，该值最大可设为等于Sort_area_size。这样设置的好处是可以提高系统性能，因为下次再做排序操作时不需要再临时申请内存，缺点是如果Sort_ara_size设得过大并且session数很多时，将导致系统内存不足。建议该值设为Sort_area_size的10%～20%左右，或者不设置（缺省为0）。

    ● Log_buffer

    Log_buffer是重做日志缓冲区，对数据库的任何修改都按顺序被记录在该缓冲，然后由LGWR进程将它写入磁盘。LGWR的写入条件是：用户提交、有1/3重做日志缓冲区未被写入磁盘、有大于1M重做日志缓冲区未被写入磁盘、超时、DBWR需要写入的数据的SCN 号大于LGWR记录的SCN 号，DBWR 触发LGWR写入。从中可以看出，大于1M的log buffer值意义并不大，建议不论物理内存多大，该值统一设为1M。

    ● Large_pool_size

    Oracle的large_pool用于MTS、并行查询和RMAN。如果使用了MTS或RMAN，large_pool特别有用，它可以降低用户对shared_pool的争用，这个参数不推荐设置。（或设为1M）。

    ● Java_pool_size

    Oracle 8i以后版本中，Oracle数据库内置了对Java的支持，短消息系统并未使用这些功能，因此java_pool_size的值可以减少到1M。但如果数据库安装时选择了JServer组件，则这个值可以设置为20M～30M之间。（可以查询v$option动态视图，如果java那一项值为true，则表示安装了JServer，为false，表示未安装）。

    ● SESSION_CACHED_CURSOR

    该参数指定要高速缓存的会话游标的数量。对同一SQL语句进行多次语法分析后，它的会话游标将被移到该会话的游标高速缓存中。这样可以缩短语法分析的时间，因为游标被高速缓存，无需被重新打开。设置该参数有助于提高系统的运行效率，建议无论在任何平台都应被设为50。

    ● re_page_sga

    该参数表示将把所有SGA装载到内存中，以便使该实例迅速达到最佳性能状态。这将增加例程启动和用户登录的时间，但在内存充足的系统上能减少缺页故障的出现。建议在2G以上（含2G）内存的系统都将该值设为true。

    ● ML_LOCKS

    该参数表示所有用户获取的表锁的最大数量。对每个表执行 DML 操作均需要一个 DML锁。例如，如果3个用户修改 2 个表，就要求该值为 6。该值过小可能会引起死锁问题。这个参数同transaction参数有关系，缺省为4倍的transaction大小。建议该参数不应该低于600，可以检查系统的当前值，若比600小，则将transaction参数改为150以上，dml_locks参数会自动变为transaction的4倍。

    ● OB_QUEUE_PROCESS

    该参数指定每个例程的SNP作业队列进程的数量。当使用job或复制时，一定要设置该参数，推荐设为3或4。并行查询中需要设置的参数并行查询仅当在多CPU处于空闲状态且数据分布在不同磁盘时才会对某些查询（有全表扫描操作）的性能产生有益的影响，而且即使在这种情况下，并行查询服务器的最大数量最好也不要超过CPU的数量。这里面应该修改的参数如下。

    ● DB_FILE_MULTIBLOCK_READ_COUNT

    该参数主要同全表扫描有关。当Oracle在请求大量连续数据块的时候，该参数控制块的读入速率。DB_FILE_MULTIBLOCK_READ_COUNT参数能对系统性能产生较大的影响，它和DB_BLOCK_SIZE参数之间有重要关系。因为在Unix物理层上，Oracle总是以最小64K的数据库块进行读入，因此应该使这2个参数的乘积为64K。即如果DB_BLOCK_SIZE为8192，则DB_FILE_MULTIBLOCK_READ_COUNT应设为8。

    ● DB_BLOCK_LRU_LATCHES

    在多CPU机器上通过初始化参数DB_BLOCK_LRU_LATCHES允许多个LRU锁存器。当DBWR和服务器进程扫描数据块缓冲Cache时，它们需要获取LRU锁存器。这种锁存器对于避免缓冲区变脏以及避免被其他进程改变都是必要的，这也避免了扫描时返回不一致的结果。如果没有使用锁存器，某个扫描自由缓冲区的进程可能会发现一个自由缓冲区，但是立刻就可能被其他进程使用。每一个LRU 锁存器至少保护50个数据块缓冲区。所有缓冲区都被Oracle基于Hash算法分配到特定的LRU锁存器之上。如果该值太小，在数据库活动量很大时就会潜在地导致竞争。在多CPU机器之上这种竞争会高一些，这是因为多个服务器进程可能排队以获取一个LRU Cache。该参数默认值为CPU个数的一半，在多CPU系统中，推荐设为等于CPU数目或CPU数目的2倍～3倍（在Oracle 9i中，该参数已经变为一个隐含参数）。

    ● OPEN_CURSORS

    指定一个会话一次可以打开的游标 (环境区域) 的最大数量，并且限制PL/SQL使用的PL/SQL游标高速缓存的大小，以避免用户再次执行语句时重新进行语法分析。请将该值设置得足够高，这样才能防止应用程序耗尽打开的游标。此值建议设置为250～300。

    ● LOG_CHECKPOINT_INTERVAL

    该参数同检查点有关，检查点由ckpt 进程执行，检查点发生时Oracle会同步数据文件、控制文件和redo文件。该参数指定当写入重做日志文件中的OS 块 (而不是数据库块) 的数量达到设定值时，强制执行一次检查点。该值较低可以缩短例程恢复所需的时间，但可能导致磁盘操作过量。在Oracle 8i中该值缺省为100000。当值为0时，表示此参数不起作用。该参数的设定需要考虑的因素较多，建议使用缺省值。

    ● LOG_CHECKPOINT_TIMEOUT

    该参数仍然同检查点有关。它指定距下一个检查点出现的最大时间间隔(秒数)。将该时间值指定为0，将禁用以时间为基础的检查点。较低的值可以缩短例程恢复的时间，但可能导致磁盘操作过量。在8i中该值缺省为1800。如果想强制某一时间段后执行检查点，则用此选项。不建议修改该值。

    Oracle 9i中主要参数设置

    ● undo_management

    Oracle 9i中新引入了undo tablespace，它可以自动管理Oracle的回滚，不再需要手工建立回滚段，但9i也支持继续使用手工建立的回滚段。由Undo_management参数决定是使用undo tablespace还是使用手工建立的回滚段。如果该参数为auto，则表示使用undo tablespace。为manual，表示使用手工管理回滚段，这时同8i一样，也需要建立回滚表空间，且要建立回滚段。在9i R2（9i R2指Oracle 9.2.X.X，下同）中，该参数缺省值为auto。建议继续使用该缺省值，即使用undo tablespace，也不用手工建立回滚段。

    ● undo_retention

    该参数的单位为秒，当undo_management参数为auto时，undo_retention表示在undo_tablespace中保留多长时间的回滚信息，Oracle 9i的flash_back功能同该值有关。该值设为多大同undo_tablespace的大小有关，在9i R2中缺省值为10800，一般情况下已经够了，不建议修改该参数。

    ● sga_max_size

    Oracle 9i中该值决定了SGA区的最大值。只要SGA区不大于该值，就可以动态调整数据库缓冲区和share pool的大小。建议该值取物理内存的50%。

    ● db_cache_size

    在Oracle 9i中，数据库缓冲区的大小由db_cache_size决定，Oracle 8i中的db_block_buffers被取消。db_cache_size的单位是字节，它直接决定了数据库缓冲区的大小，而不再是块的数量。对该值的建议值可以参照Oracle 8i中对数据库缓冲区的大小建议来设置。

    ● db_nk_cache_size

    这是Oracle 9i中引入的新参数。Oracle 9i允许以不同的数据库块大（db_block_size）建立表空间。比如标准db_block_size(即建立数据库时定义的大小)为8k，Oracle 9i还允许以4k、16k、32k等不同的db_block_size值建立表空间。与此相对应，需要建立不
    同块尺寸的数据库缓冲区，这就需要定义db_nk_cache_size，如db_4k_cache_size等。考虑到我们系统的实际情况，不建议配置该参数。

    ● workarea_size_policy

    这也是Oracle 9i新引入的参数，如果该参数设为auto，Oracle会为所有的session在SGA区之外分配一块大的内存区域做为UGA，该内存由pga_aggregate_target设置。每个session都在这块大内存区中做排序等操作，从而不用再设置其他的“*_area_size” 参数。该值在Oracle 9i中缺省设为auto，建议继续使用缺省值。

    ● pga_aggregate_target

    该参数指定连接到例程的所有服务器进程的目标 PGA 总内存。建议对该值设置为每1G内存增加100M，但最大不要超过500M。设置了workarea_size_policy和pga_aggregate_target参数后，不用再设置sort_area_size等参数。
     
    参数调整方法 
    一、确定我们的系统是使用spfile启动的（9i以上）：

    对于Oracle 9i以上的版本，首先要查看一下系统当前使用的配置是否是spfile；

    首先，以sysdba权限登录到系统，查看当前使用的配置是spfile还是pfile:

    SQL> conn / as sysdba
    Connected.
    SQL> show parameter spfile;

    NAME                                 TYPE        VALUE
    ------------------------------------ -----------
    spfile                               string          ?/dbs/spfile@.ora

    注意蓝色部分，如果与此非常相像，则说明是spfile，否则是pfile。

    如果是spfile，则首先创建一个pfile来备份，以免修改错误不能启动：

    SQL> create pfile from spfile;

    File created.

    这时pfile被创建。

    如果是pfile，我们可以改用spfile启动，使用下面的方法创建spfile, 并重新启动：

    SQL> create spfile from pfile;

    File created.

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

    SQL> startup
    ORACLE instance started.

    Total System Global Area  630501376 bytes
    Fixed Size                  2147032 bytes
    Variable Size             452988200 bytes
    Database Buffers          167772160 bytes
    Redo Buffers                7593984 bytes
    Database mounted.
    Database opened.

    二、开始调整：
    我们仅以流行的4GB内存，32位系统为例：

    增大系统全局区：
    SQL> alter system set sga_max_size=1200m scope=spfile;

    增大数据缓存区：
    SQL> alter system set db_cache_size=700m scope=spfile;

    增大共享内存区：
    SQL> alter system set shared_pool_size=320m scope=spfile;

    增大程序全局区：
    SQL> alter system set pga_aggregate_target=500m scope=spfile;

    增大排序区：
    SQL> alter system set sort_area_size=30000000 scope=spfile;

    增加连接数量：
    SQL> alter system set processes=600 scope=spfile;

    三、查看参数:
    show parameters 查看所有参数
    show parameters db 查看所有名称带db的参数
    show parameters log 查看所有名称带log的参数
    (依此类推)
     
    四、注意事项：
        在32位的系统上，ORACLE的SGA＋PGA区的大小是不能超过1.7GB的，需要特别的调整，但除非必要一般不推荐这么做。但是，根据以往的使用经验，SGA区在WINDOWS下开到1.5G以上就有可能不稳。
        shared_pool_size 与 db_cache 都在SGA内，所以这两项的大小加在一起一定要小于SGA

    查看(363) 评论(0) 收藏 分享 管理

  • 【转】 oracle的归档模式

    2009-10-28 12:15:33

    转自：

    http://www.cnblogs.com/jacktu/archive/2008/02/27/1083214.html

    一。查看oracle数据库是否为归档模式：
    1.select name,log_mode from v$database;
    NAME               LOG_MODE
    ------------------ ------------------------
    QUERY           NOARCHIVELOG
    2.使用ARCHIVE LOG LIST 命令
    Database log mode              No Archive Mode
    Automatic archival             Disabled
    Archive destination            /data/oracle/product/10.2.0/db_1//dbs/arch
    Oldest online log sequence     739
    Current log sequence               741
    二。什么是Oracle归档模式？
    　　Oracle数据库有联机重做日志，这个日志是记录对数据库所做的修改，比如插入，删除，更新数据等，对这些操作都会记录在联机重做日志里。一般数据库至少要有2个联机重做日志组。当一个联机重做日志组被写满的时候，就会发生日志切换，这时联机重做日志组2成为当前使用的日志，当联机重做日志组2写满的时候，又会发生日志切换，去写联机重做日志组1，就这样反复进行。
    　　如果数据库处于非归档模式,联机日志在切换时就会丢弃. 而在归档模式下，当发生日志切换的时候，被切换的日志会进行归档。比如，当前在使用联机重做日志1，当1写满的时候，发生日志切换，开始写联机重做日志2，这时联机重做日志1的内容会被拷贝到另外一个指定的目录下。这个目录叫做归档目录，拷贝的文件叫归档重做日志。
    　　数据库使用归档方式运行时才可以进行灾难性恢复。
    1.归档日志模式和非归档日志模式的区别
    非归档模式只能做冷备份,并且恢复时只能做完全备份.最近一次完全备份到系统出错期间的数据不能恢复.
    归档模式可以做热备份,并且可以做增量备份,可以做部分恢复.
    用ARCHIVE LOG LIST 可以查看期模式状态时归档模式还是非归档模式.

    三。配置数据库的归档模式

    改变非归档模式到归档模式:
    1)SQL>SHUTDOWN NORMAL/IMMEDIATE;
    2)SQL>STARTUP MOUNT;
    3)SQL>ALTER DATABASE ARCHIVELOG;
    4)SQL>ALTER DATABASE OPEN;

    5)SQL>做一次完全备份,因为非归档日志模式下产生的备份日志对于归档模式已经不可用了.这一步非非常重要!

    改变归档模式到非归档模式:
    1)SQL>SHUTDOWN NORMAL/IMMEDIATE;
    2)SQL>START MOUNT;
    3)SQL>ALTER DATABASE NOARCHIVELOG;
    4)SQL>ALTER DATABASE OPEN;

    3.启用自动归档: LOG_ARCHIVE_START=TRUE
    归档模式下,日志文件组不允许被覆盖(重写),当日志文件写满之后,如果没有进行手动归档,那么系统将挂起,知道归档完成为止.
    这时只能读而不能写.
    运行过程中关闭和重启归档日志进程
    SQL>ARCHIVE LOG STOP
    SQL>ARCHIVE LOG START

    4.手动归档: LOG_ARCHIVE_START=FALSE
    归档当前日志文件
    SQL>ALTER SYSTEM ARCHIVE LOG CURRENT;
    归档序号为052的日志文件
    SQL>ALTER SYSTEM ARCHIVE LOG SEQUENCE 052;
    归档所有日志文件
    SQL>ALTER SYSTEM ARCHIVE LOG ALL;
    改变归档日志目标
    SQL>ALTER SYSTEM ARCHIVE LOG CURRENT TO '&PATH';

    5.归档模式和非归档模式的转换

    第4步的逆过程.

    6.配置多个归档进程
    Q:什么时候需要使用多个归档进程?
    A:如果归档过程会消耗大量的时间,那么可以启动多个归档进程,这是个动态参数,可以用ALTER SYSTEM动态修改.
    SQL>ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=10;
    Oracle9i中最多可以指定10个归档进程
    与归档进程有关的动态性能视图
    v$bgprocess,v$archive_processes


    7.配置归档目标,多归档目标,远程归档目标,归档日志格式
    归档目标 LOG_ARCHIVE_DEST_n
    本地归档目标:
    SQL>LOG_ARCHIVE_DEST_1 = "LOCATION=D:ORACLEARCHIVEDLOG";
    远程归档目标:
    SQL>LOG_ARCHIVE_DEST_2 = "SERVICE=STANDBY_DB1";
    强制的归档目标,如果出错,600秒后重试:
    SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_4 = "LOCATION=E:ORACLEARCHIVEDLOG MANDATORY REOPEN=600";
    可选的归档目标,如果出错,放弃归档:
    SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_3 = "LOCATION=E:ORACLEARCHIVEDLOG OPTIONAL";

    归档目标状态:关闭归档目标和打开归档目标
    关闭归档目标1
    SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1 = DEFER
    打开归档目标2
    SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE

    归档日志格式
    LOG_ARCHIVE_FORMAT

    8.获取归档日志信息
    V$ARCHVIED_LOG
    V$ARCHVIE_DEST
    V$LOG_HISTORY
    V$DATABASE
    V$ARCHIVE_PROCESSES

    ARCHIVE LOG LIST;

    查看(447) 评论(0) 收藏 分享 管理

621/41234>
Open Toolbar