oracle中常用的优化函数和方法，以及在一体机exadata上的优化

oracle中常用的优化函数和方法，以及在一体机exadata上的优化

1.with as 被用到多次的 建议加上hint/+materialize/ --强制转换成临时表。inline是不转换成临时表，materialize是强制转换成临时表。
若某个表被访问多次，而且每次访问的限制条件都是一样的，就可以使用WITH as 来提高性能。with as部分被调用一次则数据不会放入一个临时表，
这时要不使用hint/+materialize/ ;
被调用多次则会把数据存入临时表中，这时候就不需要使用hint.
2.禁止使用in 大数据量子查询。
3.标量子查询改写成表关联，同一个表或者相同几个表的取不同的字段值的 如受益人信息等,可以把公共部分提取出来
4.取最值行的某个字段值建议使用 keep 函数
max(…)keep(dense_rank first order by prp.effect_date desc)
min(…)keep(dense_rank first order by prp.effect_date)
5.过长sql确定取值范围后关联公共字段，拆分
6.求最大值可以使用greatest,求最小值可以使用least
7.若求字段A值，负数则取0，否则取自身。可以使用greatest(A,0)；
8.时间戳类型TIMESTAMP转换为date类型,两中方法:
一是：先转字符串，再转日期类型：to_date(to_char(cwtq.end_time, ‘YYYY-MM-DD HH24:MI:SS’),‘YYYY-MM-DD HH24:MI:SS’)
二是：直接定义date类型：cast(cwtq.create_time as date)
9.行转列 pivot函数：select * from (temp)
pivot(max(DETAIL_ADDRESS)
for dr in(1 as “ADDRESS_1”, 2 as “ADDRESS_2”, 3 as “ADDRESS_3”))
10.列转行 UNPIVOT函数 ：unpivot include nulls(DETAIL_ADDRESS for dr in(1 as “ADDRESS_1”, 2 as “ADDRESS_2”, 3 as “ADDRESS_3”))
SELECT *
FROM (SELECT ‘罗飞’ STU_NAME,
‘2001-2002’ TERM,
‘90’ 微积分,
‘88’ 线性代数,
‘85’ 数据结构,
‘70’ 操作系统
FROM DUAL) UNPIVOT(COURSE_MARK FOR TYPE IN(微积分,
线性代数,
数据结构,
操作系统));

11.oracle中的内置函数：sqlcode和sqlerrm.程序中要进行错误处理，这时就要输出错误信息。
SQLCode：数据库操作的返回码，其中0表示成功；-1表示失败；100表示没有检索到数据；+1表示用户自定义异常。
sqlerrm函数 sqlerrm函数返回指定错误代码的错误信息
一个Oracle的错误消息最多只能包含512个字节的错误代码。如果没有异常被触发，则SQLCODE返回0，SQLERRM返回“ORA-0000：normal, successful completion”。

12.关于使用视图查询数据,注意点:
(1)如果在内层的查询有多少个字段,在外层就要显示多少个字段,否则使用视图查询不出数据
(2)可以看视图的执行计划,三大连接的使用。

13.时间戳类型TIMESTAMP作为值可以传输到以date类型作为参数的过程或者函数中。

14.报错过程pub_error_handle.exception_no_raise(nvl(v_log_id,‘task_no:’ || p_task_no),v_sqlcode,v_sqlerrm,v_error_message)
exception_no_raise(p_program_id IN APPDATA.program_error_log.program_id%TYPE,–报错的具体信息：id、
p_error_code IN VARCHAR2,–报错的标志：0表示成功,-1表示失败。
p_comments IN VARCHAR2,–错误代码的错误信息：如ora-xxx
p_message OUT VARCHAR2)–输出具体具体报错

15.查询低效率的sql
SELECT EXECUTIONS,DISK_READS,BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) HIT_RADIO,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS > 0 AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;

16.hash join 的使用：
① 确认小表是驱动表
② 确认涉及到的表和连接键分析过了
③ 如果在连接键上数据不均匀的话，建议做柱状图
④ 如果可以，调大hash_area_size的大小或pga_aggregate_target的值
⑤ Hash Join适合于小表与大表连接、返回大型结果集的连接

17.截取字段串的长度
to_char(dbms_lob.substr( ‘’,1000,1))

18.distinct 和 union 的使用：
两者都是对数据进行排序再去重，在脚本尽量少用。

19.多表连接的三种方式详解 hash join、merge join、 nested loop:
(1)使用情况取决于：优化器模式（CBO(all_rows)、RBO(rule)）;表的大小;连接列是否有索引;连接列是否排序。
(2)hash join是大数据集，小表列数据存在hash表扫描大表，此方式建议小表完全可以放于内存中。
至于小表的概念，对于 hash join 来说，能容纳在 pga 中的 hash table 都可以叫小表，通常比如：
pga_aggregate_target big integer 1073741824
hash area size 大体能使用到40多 M ，这样的话通常可能容纳 几十万的记录
hash area size缺省是2*sort_area_size,我们可以直接修改SORT_AREA_SIZE 的大小，HASH_AREA_SIZE也会跟着改变的
(3)nested loop:循环从一张表中读取数据(驱动表outer table)，然后访问另一张表（被查找表 inner table,通常有索引）。
被连接的数据子集较小（小于10000）而且inner表需要有有效的访问方法（Index），并且索引选择性较好的时候.

看你们的处理方式，总体原则:

1 如果是逐行处理，即大海捞针，考虑加索引，

2 如果是批量处理，像琼杰的sql处理时间跨度整一年，走全表更快, cbo 识别不了就hint引导。

Ods作为一个数据仓库，优先考虑批处理，充分利用exadata的存储端过滤特性，不加索引为上。

21.索引的说明：
1）索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。
2）索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率
3）索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表；
4）索引一旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时），oracle会自动管理索引，索引删除，不会对表产生影响
5）索引对用户是透明的，无论表上是否有索引，sql语句的用法不变
6）oracle创建主键时会自动在该列上创建索引

22.看你们的处理方式，总体原则:

1 如果是逐行处理，即大海捞针，考虑加索引，
2 如果是批量处理，像琼杰的sql处理时间跨度整一年，走全表更快, cbo 识别不了就hint引导。

Ods作为一个数据仓库，优先考虑批处理，充分利用exadata的存储端过滤特性，不加索引为上。

23.针对大数据量处理，使用游标fetch bulk collect into 批量处理
通过bulk collect减少loop处理的开销，使用Bulk Collect提高Oracle查询效率。
Collect批查询在某种程度上可以提高查询效率，它首先将所需数据读入内存，然后再统计分析，这样就可以提高查询效率。但是，如果Oracle数据库的内存较小，Shared
Pool Size不足以保存Bulk Collect批查询结果，那么该方法需要将Bulk Collect的集合结果保存在磁盘上，在这种情况下，Bulk
Collect方法的效率反而不如其他两种方法。除了Bulk Collect批查询外，我们还可以使用FORALL语句来实现批插入、删除和更新，这在大批量数据操作时可以显著提高执行效率。

forall 的 bulk dml 操作，它大大优于 for 集合后的操作

fetch all_contacts_curbulk collect into v_contacts;
forall i in 1 … v_contacts.count
–forall i in v_contacts.first … v_contacts.last　　
–forall i in indices of v_contacts --10g以上,可以是非连续的集合　　
insert intosr_contacts(sr_contact_id,contact_phone,remark)
　　　 values(v_contacts(i).sr_contact_id,v_contacts(i).contact_phone,v_contacts(i).remark);

–或者是单条的 delete/update 操作。
25. 简单地说，执行计划如果显示是access，就表示这个谓词条件的值将会影响数据的访问路径(表还是索引)，
而filter表示谓词条件的值并不会影响数据访问路径，只起到过滤的作用。
filter的操作是对外表的每一行，都要对内表执行一次全表扫描，所以很多时候提到filter都会感到可怕。他其实很像我们熟悉的neested loop，但它的独特之处在于会维护一个hash table。其实filter 的性能实际上跟列值distinct数有关，oracle在执行的时候实际上做了很大优化，最坏情况下才会出现对外表每一行执行一次filter操作，如果distinct值比较少，那执行效率还是非常高的。甚至有可能比nl更高。

26.执行计划中的谓词说明
Access :

通过某种方式定位了需要的数据，然后读取出这些结果集，叫做Access。
表示这个谓词条件的值将会影响数据的访问路劲（表还是索引）。

Filter：

把所有的数据都访问了，然后过滤掉不需要的数据，这种方式叫做filter 。
表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。

27.性能优化之表分区：
当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。
表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。
什么时候使用分区表：
1、表的大小超过2GB。
2、表中包含历史数据，新的数据被增加都新的分区中。

表分区有以下优点：
1、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
2、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
3、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
4、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

–查询某个分区的数据
select * from employee partition(PAR_15);

–查询表上有多少分区

SELECT * FROM user_TAB_PARTITIONS WHERE TABLE_NAME=‘EMPLOYEE’

28.oracle性能优化之——Oracle的分区修剪介绍：Partition Pruning
分区修剪技术能大大的减少从磁盘上读取的数据量，从而缩短运行时间，改善查询性能，减少资源浪费。