oracle 常用函数，Parallel并行查询--工作备忘2016/1/18

最新推荐文章于 2024-08-11 07:23:05 发布

richieruan

最新推荐文章于 2024-08-11 07:23:05 发布

阅读量1.9k

点赞数

分类专栏：个人经验总结技术总结文章标签： oracle数据库数据库

个人经验总结同时被 2 个专栏收录

38 篇文章 0 订阅

订阅专栏

技术总结

15 篇文章 0 订阅

订阅专栏

1、oracle中的sql%rowcount,sql%found、sql%notfound、sql%rowcount和sql%isopen

在执行DML（insert，update，delete）语句时，可以用到以下三个隐式游标（游标是维护查询结果的内存中的一个区域，运行DML时打开，完成时关闭，用sql%isopen检查是否打开）：

sql%rowcount用于记录修改的条数，就如你在sqlplus下执行delete from之后提示已删除xx行一样，这个参数必须要在一个修改语句和commit之间放置，否则你就得不到正确的修改行数。

sql%found （布尔类型，默认值为null）

sql%notfound（布尔类型,默认值为null）

sql%rowcount(数值类型默认值为0)

sql%isopen(布尔类型)

当执行一条DML语句后，DML语句的结果保存在四个游标属性中，这些属性用于控制程序流程或者了解程序的状态。当运行DML语句时，PL/SQL打开一个内建游标并处理结果，游标是维护查询结果的内存中的一个区域，游标在运行DML语句时打开，完成后关闭。隐式游标只使用SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT三个属性.SQL%FOUND,SQL%NOTFOUND是布尔值，SQL%ROWCOUNT是整数值。

SQL%FOUND和SQL%NOTFOUND在执行任何DML语句前SQL%FOUND和SQL%NOTFOUND的值都是NULL,

在执行DML语句后，SQL%FOUND的属性值将是：

　　. TRUE :INSERT

　　. TRUE :DELETE和UPDATE，至少有一行被DELETE或UPDATE.

　　. TRUE :SELECT INTO至少返回一行

　　当SQL%FOUND为TRUE时,SQL%NOTFOUND为FALSE。

　　SQL%ROWCOUNT

　　在执行任何DML语句之前，SQL%ROWCOUNT的值都是NULL,对于SELECT INTO语句，如果执行成功，SQL%ROWCOUNT的值为1,如果没有成功或者没有操作(如update、insert、delete为0条），SQL%ROWCOUNT的值为0.

　　SQL%ISOPEN

　　SQL%ISOPEN是一个布尔值，如果游标打开，则为TRUE, 如果游标关闭，则为FALSE.对于隐式游标而言SQL%ISOPEN总是FALSE，这是因为隐式游标在DML语句执行时打开，结束时就立即关闭。

no_data_found 与sql%notfound 以及sql%rowcount 的区别：

NO_DATA_FOUND：该异常可以在两种不同的情况下出现：第一种：当SELECT。。。。INTO语的 WHERE子句没匹配任何数据行时；第二种：试图引用尚未赋值的PL/SQL index-by表元素时。

SQL%NOTFOUND：是隐匿游标的属性，当没有可检索的数据时，该属性为：TRUE；常作为检索循环退出的条件。若某UPDATE或DELETE语句的WHERE子句不匹配任何数据行，该属性为：TRUE，但不并不出现NO_DATA_FOUND异常.

SQL%ROWCOUNT：该数字属性返回了到目前为止，游标所检索数据库行的个数。

2、substr(SQLERRM, 1, 70) ：SQLERRM为错误代码

3、异常

EXCEPTION
  when others then
    rollback;
    dbms_output.put_line('code:' || sqlcode);
    dbms_output.put_line('errm:' || sqlerrm);

    raise;
when others then和raise：

异常分很多种类，如NO_FOUND。others处本应该写异常名称，如果不想把异常分得那麼细，可以笼统一点用others来捕获，即所有异常均用others来捕获。
when others then表示是其它异常。
raise表示抛出异常，让User可以看到。

4、并行查询

Parallel分类
l 并行查询parallel query
l 并行dml parallel dml pdml
l 并行ddl parallel ddl pddl

一、并行查询
并行查询允许将一个sql select语句划分为多个较小的查询，每个部分的查询并发地运行，然后将各个部分的结果组合起来，提供最终的结果，多用于全表扫描，索引全扫描等，大表的扫描和连接、创建大的索引、分区索引扫描、大批量插入更新和删除

1. 启用并行查询
SQL> ALTER TABLE T1 PARALLEL;
告知oracle，对T1启用parallel查询，但并行度要参照系统的资源负载状况来确定。
利用hints提示，启用并行，同时也可以告知明确的并行度，否则oracle自行决定启用的并行度，这些提示只对该sql语句有效。
SQL> select /*+ parallel(t1 8) */ count(*)from t1;

SQL> select degree from user_tables where table_name='T1';
DEGREE
--------------------
DEFAULT

并行度为Default，其值由下面2个参数决定
SQL> show parameter cpu

NAME TYPE VALUE
----------------------------------------------- ------------------------------
cpu_count integer 2
parallel_threads_per_cpu integer 2

cpu_count表示cpu数
parallel_threads_per_cpu表示每个cpu允许的并行进程数
default情况下，并行数为cpu_count*parallel_threads_per_cpu

2. 取消并行设置
SQL> alter table t1 noparallel;
SQL> select degree from user_tables wheretable_name='T1';

DEGREE
----------------------------------------
1

3. 数据字典视图
v$px_session
sid：各个并行会话的sid
qcsid：query coordinator sid,查询协调器sid

二、并行dml
并行dml包括insert，update，delete，merge，在pdml期间，oracle可以使用多个并行执行服务器来执行insert，update，delete，merge，多个会话同时执行，同时每个会话(并发进程)都有自己的undo段，都是独立的一个事务，这些事务要么由pdml协调器进程提交，要么都rollback。
在一个有充足I/o带宽的多cpu主机中，对于大规模的dml，速度可能会有很大的提升，尤其是在大型的数据仓库环境中。
并行dml需要显示的启用
SQL> alter session enable parallel dml;

Disable并行dml
SQL> alter session disable parallel dml;

三、并行ddl
并行ddl提供了dba使用全部机器资源的能力，常用的pddl有
create table as select ……
create index
alter index rebuild
alter table move
alter table split
在这些sql语句后面加上parallel子句
例
SQL> alter table t1 move parallel;
Table altered
SQL> create index T1_IDX on T1 (OWNER,OBJECT_TYPE)
2 tablespace SYSTEM
3 parallel;
4 ；

1．用途

强行启用并行度来执行当前SQL。这个在Oracle 9i之后的版本可以使用，之前的版本现在没有环境进行测试。也就是说，加上这个说明，可以强行启用Oracle的多线程处理功能。举例的话，就像电脑装了多核的CPU，但大多情况下都不会完全多核同时启用（2核以上的比较明显），使用parallel说明，就会多核同时工作，来提高效率。

但本身启动这个功能，也是要消耗资源与性能的。所有，一般都会在返回记录数大于100万时使用，效果也会比较明显。

2．语法

/*+parallel(table_short_name,cash_number)*/

这个可以加到insert、delete、update、select的后面来使用（和rule的用法差不多，有机会再分享rule的用法）

开启parallel功能的语句是：

alter session enable parallel dml;

这个语句是DML语句哦，如果在程序中用，用execute的方法打开。

3．实例说明

用ERP中的transaction来说明下吧。这个table记录了所有的transaction，而且每天数据量也算相对比较大的（根据企业自身业务量而定）。假设我们现在要查看对比去年一年当中每月的进、销情况，所以，一般都会写成：

select to_char(transaction_date,'yyyymm') txn_month,

sum(

decode(

sign(transaction_quantity),1,transaction_quantity,0
)

) in_qty,

sum(

decode(

sign(transaction_quantity),-1,transaction_quantity,0
)

) out_qty

from mtl_material_transactions mmt

where transaction_date >= add_months(

to_date(

to_char(sysdate,'yyyy')||'0101','yyyymmdd'),

-12)

and transaction_date <= add_months(

to_date(

to_char(sysdate,'yyyy')||'1231','yyyymmdd'),

-12)

group by to_char(transaction_date,'yyyymm')

这个SQL执行起来，如果transaction_date上面有加index的话，效率还算过的去；但如果没有加index的话，估计就会半个小时内都执行不出来。这是就可以在select 后面加上parallel说明。例如：
select /*+parallel(mmt,10)*/
to_char(transaction_date,'yyyymm') txn_month,

...

这样的话，会大大提高执行效率。如果要将检索出来的结果insert到另一个表tmp_count_tab的话，也可以写成：
insert /*+parallel(t,10)*/
into tmp_count_tab

(

txn_month,

in_qty,

out_qty

)

select /*+parallel(mmt,10)*/
to_char(transaction_date,'yyyymm') txn_month,

...

插入的机制和检索机制差不多，所以，在insert后面加parallel也会加速的。关于insert机制，这里暂不说了。
Parallel后面的数字，越大，执行效率越高。不过，貌似跟server的配置还有oracle的配置有关，增大到一定值，效果就不明显了。所以，一般用8,10,12,16的比较常见。我试过用30，发现和16的效果一样。不过，数值越大，占用的资源也会相对增大的。如果是在一些package、function or procedure中写的话，还是不要写那么大，免得占用太多资源被DBA开K。

4． Parallel也可以用于多表

多表的话，就是在第一后面，加入其他的就可以了。具体写法如下：

/*+parallel(t,10) (b,10)*/

5．小结

关于执行效率，建议还是多按照index的方法来提高效果。Oracle有自带的explan road的方法，在执行之前，先看下执行计划路线，对写好的SQL tuned之后再执行。实在没办法了，再用parallel方法。Parallel比较邪恶，对开发者而言，不是好东西，会养成不好习惯，导致很多bad SQL不会暴漏，SQL Tuning的能力得不到提升。我有见过某些人create table后，从不create index或primary key，认为写SQL时加parallel就可以了。

引用文章：http://blog.csdn.net/jojo52013145/article/details/7460121

5、

ORDERED提示强制Oracle按照From子句中表出现的顺序进行表连接。

通过ORDERED提示，可以避免CBO SQL解析过程中的表连接评估，从而避免Oracle产生错误的执行计划，或者强制Oracle按照我们指定的方式执行。在很多时候，当我们清楚地了解数据结构和数据分布之后，就可以通过ORDERED提示来提高SQL性能。

1 SQL> SELECT /*+ ordered */ COUNT (*)
2   2  FROM t_middle, t_small, t_max
3   3  WHERE t_small.object_id = t_middle.object_id
4   4  AND t_middle.object_id = t_max.object_id;