一、ORACLE数据库的数据操作优化(批量绑定Bulk Binding)
绑定变量包扩下面两个部分:
1) 输入集合(collections),使用FORALL语句,一般用来改善DML(INSERT、UPDATE和DELETE) 操作的性能;
2) 输出集合(collections),使用BULK COLLECT子句;一般用来提高查询(SELECT)的性能。
(1)数据查询的优化(BULK COLLECT)
Create Or Replace Procedure SELECT_BULK_TEST AS
TYPE curtime IS TABLE OF Strainformation.Curtime%TYPE;
TYPE position IS TABLE OF Strainformation.Position%TYPE;
TYPE Strain IS TABLE OF Strainformation.Strain%TYPE;
ctime curtime; -- no need to initialize
pos position;
str strain;
BEGIN
SELECT Curtime,Position,Strain BULK COLLECT INTO ctime,pos,str FROM Strainformation;
FOR i in 1..pos.count LOOP
DBMS_OUTPUT.PUT_LINE(enums(i) || ' ' ||names(i));
END LOOP;
END;
/
(2)删除优化
DELETE /*+ RULE */ from Test_Table;
(3)插入优化输入集合(FORALL)
顾名思义,插入数据时使用批量绑定,就是要通过存储过程,自定义数组的方式,一次绑定一个完整的集合。如:
create or replace PROCEDURE insert_test AS
TYPE curtime ISTABLE OF Strainformation.Curtime%TYPE INDEXBY BINARY_INTEGER;
TYPE position ISTABLE OF Strainformation.Position%TYPE INDEX BY BINARY_INTEGER;
TYPE Strain ISTABLE OF Strainformation.Strain%TYPE INDEXBY BINARY_INTEGER;
i Integer;
pos Position;
str Strain;
ctime curtime;
-- 该过程为通过使用批量绑定实现的数据插入,插入速度相当快
BEGIN
--首先使用批量绑定查询,把信息表中的数据绑定到设定的数组变量中
Select Curtime,Position,Strain bulk collect into ctime,pos,str from Strainformation;
FORALL i IN 1..pos.count -- use FORALL statement
insert /*+ append */ into STRAININFO_ALERT(ctime(i),pos(i),s(i),str(i));
commit;
exception
when others then
--出现错误,提示失败
dbms_output.put_line('数据插入失败!');
rollback;
END insert_test;
二、ORACLE性能优化
1、buffer_cache大小的设置及依据
(1)buffercache大小的查询和修改
buffercache的设置和sharedpool的设置很相似,一种情况我们单独的去设置,如buffercache设置1个g或2个g,另外可以把buffercache的设置放在SGA target里面去,oracle自动去设置buffercache大小,在实际的生产中我们还是经常的去手工设buffercache大小,下面这句是查一下当前我们sga里面的各个组件到底是多大。
SELECT component,current_size,min_size FROM v$sga_dynamic_components;
buffer设置大小
alter system set db_cache_size=20M scope=memory;(也可以scope=both,设置buffercache,大小这里设的是20M)
一般的有一个原则:DB_CACHE_SIZE = SGA_MAX_SIZE/2~ SGA_MAX_SIZE*2/3,dbcache设的大小一般是SGA_MAX_SIZE的二分之一到三分之二,也就是整个的SGA的一半到三分之二的空间。
(2)查看sga状态
SELECT component,current_size,min_size FROM v$sga_dynamic_components;
其中DEFAULTbuffer cache 是buffercache的大小
(3)仔细查一下设置的值: show parameter sga;
(4)show parameter db_cache_size;
如果db_cache_size的设置的值为默认值0,说明没有设置大小,用的全是由SGA自动调整
如何改呢,如下:altersystem set db_cache_size=200M scope=both;
(5)查看spfile文件的位置:show parameter spfile;
(6)当前数据库设置的块的大小:show parameter db_block_size
2、CLOB性能调优
1)Alter Table test_table_info Modify (position_clob Clob store as securefile(cache));
//强制将所有LOB 创建为SecureFileLOB
Alter system set db_securefile = 'FORCE';
2)Alter Table test_table_info Add (
position_clob clob lob(position_clob) store as securefile(cache);
strain_clob clob lob(strain_clob) store as securefile(cache));
3)使用DB_SECUREFILE 初始化参数,数据库管理员(DBA) 可确定SecureFiles 的使用情况,其中有效值为:
(1) ALWAYS:尝试将ASSM表空间上的所有LOB 创建为SecureFile LOB,但是仅可将自动段空间管理(ASSM) 表空间外的任何LOB 创建为BasicFile LOB;
(2) FORCE:强制将所有LOB 创建为SecureFileLOB;
(3) PERMITTED:允许创建SecureFiles(默认值);
(4) NEVER:禁止创建SecureFiles;
(5) IGNORE:禁止创建SecureFiles,并忽略使用SecureFiles 选项强制创建BasicFiles 而导致的任何错误
注:内容非完全原创,是由我个人在学习的过程中整理和练习而作。