oraclesql并发写法,Sql优化（三）关于oracle的并发
Oracle的并发技术可以将⼀个⼤任务分解为多个⼩任务由多个进程共同完成。合理地使⽤并发可以充分利⽤系统资源，提⾼效率。
⼀、 并发的种类
Parallel query
Parallel DML(PDML)
oracle游标的使用Parallel DDL
Parallel recovery
[@more@]
⼆、 适⽤场合
适⽤parallel的两个条件
1)⼤的任务，如全表扫描⼤表
这和⽇常⽣活中的经验是⼀样的，⼩任务⾃⼰完成都⽐派发任务省事
2)系统有⾜够的资源(cpu/io)
换句话说，并发是在系统资源充⾜、⽤户少的系统上，为了充分利⽤系统资源以提⾼任务处理速度⽽设计的⼀种技术。以下是⼏种场景：
1)OLTP系统 有⼤量⽤户和session，如果每个session使⽤并发查询将导致系统崩溃。但也有例外例如计费系统⽉底或下班后没有或⽤户很少访问，运⾏批处理程序，此时可使⽤并发提⾼速度
2)数据仓库系统 通常可使⽤并发查询、PDML等并发，注意有些数据仓库系统也提供给⼤量⽤户访问，这种系统有某些OLTP特性，应慎⽤并发
3)⽆论是OLTP还是数据仓库，维护期间使⽤parallel ddl和PDML对管理员来说是⾮常有⽤的
三、 Parallel query
使⽤并发查询的⽅法：
1)修改表属性
Alter table big_table parallel 4;
Alter
table big_table parallel
；由oracle根据系统资源情况决定。这是推荐的.Oracle根据cpu数⽬乘以parallel threads per
cpu参数(default 2)，例如4cpu的机器，oracle决定parallel数⽬为8
2)使⽤hint , select * /*+ PARALLEL(emp,12) */ …
四、 PDML
例⼦：
ALTER TABLE emp PARALLEL (10);
ALTER SESSION ENABLE PARALLEL DML;
INSERT INTO emp
SELECT * FROM t_emp;
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ PARALLEL(emp,12) */ INTO emp
SELECT /*+ PARALLEL(t_emp,12) */ * FROM t_emp;
COMMIT;
注意：使⽤parallel后，insert select * 语句⾃动就使⽤direct-load了，此时不再需要使⽤append hint( /*+APPEND */)
PDML的限制：
不⽀持有trigger的表，在上⾯做PDML，能成功，但忽略了并发性
不⽀持某些约束，例如self-referential integrity。原因是PDML分为多个独⽴的session去修改数据，⽆法保证某些完整性；容易引起死锁已经其他锁问题
⼀个session使⽤了PDML，在commit/rollback之前，另⼀个session⽆法再使⽤PDML
Advanced replication不⽀持(因为使⽤了trigger)
Deferred constraints(约束的deferred模式指修改操作在提交时才去验证是否满⾜约束条件)不⽀持
分布式事务不⽀持
Clustered tables不⽀持
当违反这些限制，PDML要么报错，要么忽略并⾏度
五、 并发与空间浪费
Parallel DDL以及某些PDML依赖于direct path load，即绕过databuffer直接写数据⽂件。
例如，create table as select ，insert /*+APPEND */,
这
会形成空间浪费，例如倒⼊1010M数据，每个extent 100m，direct path load会新分配100m
的extent来存放数据(如果有⼩于100m的extent，常规insert可以⽤这些空间)。假设10个并发，每个并发倒⼊101M数据，会创建2
个extent，则总共会创建20个extent，则形成990m空间浪费。⼀⽅⾯浪费了空间(如果表创建之后有常规insert，则能使⽤这些空间)，
另⼀⽅⾯全表扫描时会搜索这些空的extent，这也降低了全表扫描的速度。
表空间的extent管理有两种⽅式，unform size，则每个extent⼤⼩相同，autoallocate是oracle根据内部机制决定extent⼤⼩，更灵活
Uniform ⽅式不⽀持extent trimming，⽽autoallocate在parallel ddl中⽤到extent trimming，减少了空间浪费。
因此在频繁使⽤parallel DDL操作的表空间上，要么减少uniform size每个extent的⼤⼩，要么使⽤autoallocate ，以减少空间浪费。
六、 并发DIY-存储过程的并发
以下是⼀个常见任务：扫描全表，修改数据，再写⼊新的表
如果⼀个进程处理太慢，我们通常会⾃⼰将数据划分，然后开多个进程调⽤。
使⽤11gr2 内置的并发包：DBMS_PARALLLEL_EXECUTE，⼤⼤简化了这⼀过程
(11gr2之前，没有内置的并发程序包，需要⼿⼯按照rowid或主键划分⼤表，然后通过dbms_job或dbms_schedule并发调⽤。)
我们以前两天***的⼀个程序为例，看看如何使⽤这⼀并发技术(本例较简单，不见得需要使⽤这样技术，仅仅作为例⼦来说明)
程序的⽬的是删除bmf中orig_bill_ref_no like ‘18%‘的记录，本来⼀句sql可以完成，由于数据量太⼤，系统回滚段不⾜。因此开发⼈员准备分多个进程运⾏
cursor c1
is select orig_bill_ref_no from bmf where orig_bill_ref_no like ‘18%‘
and mod(account_no, 5) = 0; (将数据分为5段)
begin
for r1 in c1 loop
delete from bmf where orig_bill_ref_no = r1.orig_bill_ref_no;
commit;
end loop;
commit;
end;
/
这
样的写法会有什么问题呢，很快就遇到snapshot too
old错误了。原因是select打开bmf游标，同时修改bmf并commit数据，由于查询⼀致性要求，打开的游标要看到的是bmf修改之前的情况，
这是从undo去读的，因此⼀旦时间超出undo_retention，undo信息过期，就报snapshot too old了。
使⽤ora11g提供的并发包的写法：
1) 创建过程serial过程，⽤来被多个并发线程调⽤
create or replace
procedure serial( p_lo_rid in rowid, p_hi_rid in rowid )
is
begin
delete from bmf
where rowid between p_lo_rid and p_hi_rid and orig_bill_ref_no like ‘15%‘;
end;
/
2) 按照rowid将表划分为多个chunk，供线程调⽤
begin
dbms_ate_task(‘PROCESS BIG TABLE‘);
dbms_ate_chunks_by_rowid
( task_name => ‘PROCESS BIG TABLE‘,
table_owner => ‘LUW‘,
table_name => ‘BMF‘,
by_row => false, --不按⾏记录数⽽按block数
chunk_size => 2000 );
end;
/
select *
from (
select chunk_id, status, start_rowid, end_rowid
from dba_parallel_execute_chunks
where task_name = ‘PROCESS BIG TABLE‘
order by chunk_id
)
where rownum <= 5
/
3) 发起并发任务，按照第2步对表的划分来分配并运⾏任务begin
dbms_parallel_execute.run_task
( task_name => ‘PROCESS BIG TABLE‘,
sql_stmt => ‘begin serial( :start_id, :end_id ); end;‘, language_flag => DBMS_SQL.NATIVE,
parallel_level => 4 );
end;
/
4) 删除并发作业
begin
dbms_parallel_execute.drop_task(‘process big table‘ ); end;
/
那么使⽤并发和简单的delete相⽐，速度怎样呢
使⽤并发：
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.07
直接delete:
delete from bmf where orig_bill_ref_no like ‘15%‘; 403525 rows deleted.
Elapsed: 00:00:08.12
这说明使⽤并发提⾼了速度，更别说对回滚段的空间要求也少了。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。