oracle 优化——来自网络

最新推荐文章于 2024-02-05 22:10:25 发布

cixieku3433

最新推荐文章于 2024-02-05 22:10:25 发布

阅读量251

点赞数

oracle 优化收藏

转：ray‘s

1 前言 2
2 总纲 2
3 降龙十八掌 3
第一掌避免对列的操作 3
第二掌避免不必要的类型转换 4
第三掌增加查询的范围限制 4
第四掌尽量去掉”IN”、”OR” 4
第五掌尽量去掉 “<>” 5
第六掌去掉Where子句中的IS NULL和IS NOT NULL 5
第七掌索引提高数据分布不均匀时查询效率 5
第八掌利用HINT强制指定索引 6
第九掌屏蔽无用索引 6
第十掌分解复杂查询，用常量代替变量 7
第十一掌 like子句尽量前端匹配 7
第十二掌用Case语句合并多重扫描 7
第十三掌使用nls_date_format 8
第十四掌使用基于函数的索引 8
第十五掌基于函数的索引要求等式匹配 9
第十六掌使用分区索引 9
第十七掌使用位图索引 9
第十八掌决定使用全表扫描还是使用索引 9
4 总结 10

1 前言
客服业务受到SQL语句的影响非常大，在规模比较大的局点，往往因为一个小的SQL语句不够优化，导致数据库性能急剧下降，小型机idle所剩无几，应用服务器断连、超时，严重影响业务的正常运行。因此，称低效的SQL语句为客服业务的‘恶龙’并不过分。数据库的优化方法有很多种，在应用层来说，主要是基于索引的优化。本次秘笈根据实际的工作经验，在研发原来已有的方法的基础上，进行了一些扩充，总结了基于索引的SQL语句优化的降龙十八掌，希望有一天你能用其中一掌来驯服客服业务中横行的‘恶龙’。
2 总纲
l 建立必要的索引
这次传授的降龙十八掌，总纲只有一句话：建立必要的索引，这就是后面降龙十八掌的内功基础。这一点看似容易实际却很难。难就难在如何判断哪些索引是必要的，哪些又是不必要的。判断的最终标准是看这些索引是否对我们的数据库性能有所帮助。具体到方法上，就必须熟悉数据库应用程序中的所有SQL语句，从中统计出常用的可能对性能有影响的部分SQL，分析、归纳出作为Where条件子句的字段及其组合方式；在这一基础上可以初步判断出哪些表的哪些字段应该建立索引。其次，必须熟悉应用程序。必须了解哪些表是数据操作频繁的表；哪些表经常与其他表进行连接；哪些表中的数据量可能很大；对于数据量大的表，其中各个字段的数据分布情况如何；等等。对于满足以上条件的这些表，必须重点关注，因为在这些表上的索引，将对SQL语句的性能产生举足轻重的影响。不过下面还是总结了一下降龙十八掌内功的入门基础，建立索引常用的规则如下：
1、表的主键、外键必须有索引；
2、数据量超过300的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5、索引应该建在选择性高的字段上；
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
A、正确选择复合索引中的主列字段，一般是选择性较好的字段；
B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
8、频繁进行数据操作的表，不要建立太多的索引；
9、删除无用的索引，避免对执行计划造成负面影响；
以上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。
3 降龙十八掌

第一掌避免对列的操作
任何对列的操作都可能导致全表扫描，这里所谓的操作包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等式的右边，甚至去掉函数。
例1：下列SQL条件语句中的列都建有恰当的索引，但30万行数据情况下执行速度却非常慢：
select * from record where substrb(CardNo,1,4)=’5378′(13秒)
select * from record where amount/30< 1000（11秒）
select * from record where to_char(ActionTime,’yyyymmdd’)=’19991201′（10秒）
由于where子句中对列的任何操作结果都是在SQL运行时逐行计算得到的，因此它不得不进行表扫描，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表扫描，因此将SQL重写如下：
select * from record where CardNo like ‘5378%’（< 1秒）
select * from record where amount < 1000*30（< 1秒）
select * from record where ActionTime= to_date (’19991201′ ,’yyyymmdd’)（< 1秒）
差别是很明显的！

第二掌避免不必要的类型转换
需要注意的是，尽量避免潜在的数据类型转换。如将字符型数据与数值型数据比较，ORACLE会自动将字符型用to_number()函数进行转换，从而导致全表扫描。
例2：表tab1中的列col1是字符型（char)，则以下语句存在类型转换：
select col1,col2 from tab1 where col1>10，
应该写为： select col1,col2 from tab1 where col1>’10′。

第三掌增加查询的范围限制
增加查询的范围限制，避免全范围的搜索。
例3：以下查询表record 中时间ActionTime小于2001年3月1日的数据：
select * from record where ActionTime < to_date (’20010301′ ,’yyyymm’)
查询计划表明，上面的查询对表进行全表扫描，如果我们知道表中的最早的数据为2001年1月1日，那么，可以增加一个最小时间，使查询在一个完整的范围之内。修改如下： select * from record where
ActionTime < to_date (’20010301′ ,’yyyymm’)
and ActionTime > to_date (’20010101′ ,’yyyymm’)
后一种SQL语句将利用上ActionTime字段上的索引，从而提高查询效率。把’20010301′换成一个变量，根据取值的机率，可以有一半以上的机会提高效率。同理，对于大于某个值的查询，如果知道当前可能的最大值，也可以在Where子句中加上 “AND 列名< MAX(最大值)”。

第四掌尽量去掉”IN”、”OR”
含有”IN”、”OR”的Where子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。
例4： select count(*) from stuff where id_no in(’0′,’1′)（23秒）
可以考虑将or子句分开：
select count(*) from stuff where id_no=’0′
select count(*) from stuff where id_no=’1′
然后再做一个简单的加法，与原来的SQL语句相比，查询速度更快。

第五掌尽量去掉 “<>”
尽量去掉 “<>”，避免全表扫描，如果数据是枚举值，且取值范围固定，则修改为”OR”方式。
例5：
UPDATE SERVICEINFO SET STATE=0 WHERE STATE<>0;
以上语句由于其中包含了”<>”，执行计划中用了全表扫描（TABLE ACCESS FULL），没有用到state字段上的索引。实际应用中，由于业务逻辑的限制，字段state为枚举值，只能等于0，1或2，而且，值等于=1，2的很少，因此可以去掉”<>”，利用索引来提高效率。
修改为：UPDATE SERVICEINFO SET STATE=0 WHERE STATE = 1 OR STATE = 2 。进一步的修改可以参考第4种方法。
第六掌去掉Where子句中的IS NULL和IS NOT NULL
Where字句中的IS NULL和IS NOT NULL将不会使用索引而是进行全表搜索，因此需要通过改变查询方式，分情况讨论等方法，去掉Where子句中的IS NULL和IS NOT NULL。

第七掌索引提高数据分布不均匀时查询效率
索引的选择性低，但数据的值分布差异很大时，仍然可以利用索引提高效率。A、数据分布不均匀的特殊情况下，选择性不高的索引也要创建。
表 ServiceInfo中数据量很大，假设有一百万行，其中有一个字段DisposalCourseFlag，取值范围为枚举值：[0，1，2，3， 4，5，6，7]。按照前面说的索引建立的规则，“选择性不高的字段不应该建立索引，该字段只有8种取值，索引值的重复率很高，索引选择性明显很低，因此不建索引。然而，由于该字段上数据值的分布情况非常特殊，具体如下表：
取值范围 1~5 6 7
占总数据量的百分比 1% 98% 1%
而且，常用的查询中，查询DisposalCourseFlag<6 的情况既多又频繁，毫无疑问，如果能够建立索引，并且被应用，那么将大大提高这种情况的查询效率。因此，我们需要在该字段上建立索引。

第八掌利用HINT强制指定索引
在ORACLE优化器无法用上合理索引的情况下，利用HINT强制指定索引。
继续上面7的例子，ORACLE缺省认定，表中列的值是在所有数据行中均匀分布的，也就是说，在一百万数据量下，每种 DisposalCourseFlag值各有12.5万数据行与之对应。假设SQL搜索条件DisposalCourseFlag=2，利用 DisposalCourseFlag列上的索引进行数据搜索效率，往往不比全表扫描的高，ORACLE因此对索引“视而不见”，从而在查询路径的选择中，用其他字段上的索引甚至全表扫描。根据我们上面的分析，数据值的分布很特殊，严重的不均匀。为了利用索引提高效率，此时，一方面可以单独对该字段或该表用analyze语句进行分析，对该列搜集足够的统计数据，使ORACLE在查询选择性较高的值时能用上索引；另一方面，可以利用HINT提示，在 SELECT关键字后面，加上“/*+ INDEX(表名称，索引名称）*/”的方式，强制ORACLE优化器用上该索引。
比如： select * from serviceinfo where DisposalCourseFlag=1 ;
上面的语句，实际执行中ORACLE用了全表扫描，加上蓝色提示部分后，用到索引查询。如下：
select /*+ INDEX(SERVICEINFO,IX_S_DISPOSALCOURSEFLAG) */ *
from serviceinfo where DisposalCourseFlag=1;
请注意，这种方法会加大代码维护的难度，而且该字段上索引的名称被改变之后，必须要同步所有指定索引的HINT代码，否则HINT提示将被ORACLE忽略掉。

第九掌屏蔽无用索引
继续上面8的例子，由于实际查询中，还有涉及到DisposalCourseFlag=6的查询，而此时如果用上该字段上的索引，将是非常不明智的，效率也极低。因此这种情况下，我们需要用特殊的方法屏蔽该索引，以便ORACLE选择其他字段上的索引。比如，如果字段为数值型的就在表达式的字段名后，添加 “+ 0”，为字符型的就并上空串：“||”"”
如： select * from serviceinfo where DisposalCourseFlag+ 0 = 6 and workNo = ‘36′ 。
不过，不要把该用的索引屏蔽掉了，否则同样会产生低效率的全表扫描。

第十掌分解复杂查询，用常量代替变量
对于复杂的Where条件组合，Where中含有多个带索引的字段，考虑用IF语句分情况进行讨论；同时，去掉不必要的外来参数条件，减低复杂度，以便在不同情况下用不同字段上的索引。
继续上面9的例子，对于包含
Where (DisposalCourseFlag < v_DisPosalCourseFlag) or (v_DisPosalCourseFlag is null) and ….的查询，(这里v_DisPosalCourseFlag为一个输入变量，取值范围可能为[NULL，0，1，2，3，4，5，6，7])，可以考虑分情况用IF语句进行讨论，类似：
IF v_DisPosalCourseFlag =1 THEN
Where DisposalCourseFlag = 1 and ….
ELSIF v_DisPosalCourseFlag =2 THEN
Where DisposalCourseFlag = 2 and ….
。。。。。。

第十一掌 like子句尽量前端匹配
因为like参数使用的非常频繁，因此如果能够对like子句使用索引，将很高的提高查询的效率。
例6：select * from city where name like ‘%S%’
以上查询的执行计划用了全表扫描（TABLE ACCESS FULL），如果能够修改为：
select * from city where name like ‘S%’
那么查询的执行计划将会变成（INDEX RANGE SCAN），成功的利用了name字段的索引。这意味着Oracle SQL优化器会识别出用于索引的like子句，只要该查询的匹配端是具体值。因此我们在做like查询时，应该尽量使查询的匹配端是具体值，即使用 like ‘S%’。

第十二掌用Case语句合并多重扫描
我们常常必须基于多组数据表计算不同的聚集。例如下例通过三个独立查询：
例8：1）select count(*) from emp where sal<1000;
2）select count(*) from emp where sal between 1000 and 5000;
3）select count(*) from emp where sal>5000;
这样我们需要进行三次全表查询，但是如果我们使用case语句：
select
count (sale when sal <1000
then 1 else null end) count_poor,
count (sale when between 1000 and 5000
then 1 else null end) count_blue_collar,
count (sale when sal >5000
then 1 else null end) count_poor
from emp;
这样查询的结果一样，但是执行计划只进行了一次全表查询。

第十三掌使用nls_date_format
例9：
select * from record where to_char(ActionTime,’mm’)=’12′
这个查询的执行计划将是全表查询，如果我们改变nls_date_format，
SQL>alert session set nls_date_formate=’MM’;
现在重新修改上面的查询：
select * from record where ActionTime=’12′
这样就能使用actiontime上的索引了，它的执行计划将是（INDEX RANGE SCAN）。

第十四掌使用基于函数的索引
前面谈到任何对列的操作都可能导致全表扫描，例如：
select * from emp where substr(ename,1,2)=’SM’;
但是这种查询在客服系统又经常使用，我们可以创建一个带有substr函数的基于函数的索引，
create index emp_ename_substr on eemp ( substr(ename,1,2) );

这样在执行上面的查询语句时，这个基于函数的索引将排上用场，执行计划将是（INDEX RANGE SCAN）。

第十五掌基于函数的索引要求等式匹配
上面的例子中，我们创建了基于函数的索引，但是如果执行下面的查询：
select * from emp where substr(ename,1,1)=’S’
得到的执行计划将还是（TABLE ACCESS FULL），因为只有当数据列能够等式匹配时，基于函数的索引才能生效，这样对于这种索引的计划和维护的要求都很高。请注意，向表中添加索引是非常危险的操作，因为这将导致许多查询执行计划的变更。然而，如果我们使用基于函数的索引就不会产生这样的问题，因为Oracle只有在查询使用了匹配的内置函数时才会使用这种类型的索引。

第十六掌使用分区索引
在用分析命令对分区索引进行分析时，每一个分区的数据值的范围信息会放入Oracle的数据字典中。Oracle可以利用这个信息来提取出那些只与SQL查询相关的数据分区。
例如，假设你已经定义了一个分区索引，并且某个SQL语句需要在一个索引分区中进行一次索引扫描。Oracle会仅仅访问这个索引分区，而且会在这个分区上调用一个此索引范围的快速全扫描。因为不需要访问整个索引，所以提高了查询的速度。

第十七掌使用位图索引
位图索引可以从本质上提高使用了小于1000个唯一数据值的数据列的查询速度，因为在位图索引中进行的检索是在RAM中完成的，而且也总是比传统的B树索引的速度要快。对于那些少于1000个唯一数据值的数据列建立位图索引，可以使执行效率更快。

第十八掌决定使用全表扫描还是使用索引
和所有的秘笈一样，最后一招都会又回到起点，最后我们来讨论一下是否需要建立索引，也许进行全表扫描更快。在大多数情况下，全表扫描可能会导致更多的物理磁盘输入输出，但是全表扫描有时又可能会因为高度并行化的存在而执行的更快。如果查询的表完全没有顺序，那么一个要返回记录数小于10％的查询可能会读取表中大部分的数据块，这样使用索引会使查询效率提高很多。但是如果表非常有顺序，那么如果查询的记录数大于40％时，可能使用全表扫描更快。因此，有一个索引范围扫描的总体原则是：
1）对于原始排序的表仅读取少于表记录数40％的查询应该使用索引范围扫描。反之，读取记录数目多于表记录数的40％的查询应该使用全表扫描。
2）对于未排序的表仅读取少于表记录数7％的查询应该使用索引范围扫描。反之，读取记录数目多于表记录数的7％的查询应该使用全表扫描。

4 总结
以上的招式，是完全可以相互结合同时运用的。而且各种方法之间相互影响，紧密联系。这种联系既存在一致性，也可能带来冲突，当冲突发生时，需要根据实际情况进行选择，没有固定的模式。最后决定SQL优化功力的因素就是对ORACLE内功的掌握程度了。
另外，值得注意的是：随着时间的推移和数据的累计与变化，ORACLE对SQL语句的执行计划也会改变，比如：基于代价的优化方法，随着数据量的增大，优化器可能错误的不选择索引而采用全表扫描。这种情况可能是因为统计信息已经过时，在数据量变化很大后没有及时分析表；但如果对表进行分析之后，仍然没有用上合理的索引，那么就有必要对SQL语句用HINT提示，强制用合理的索引。但这种HINT提示也不能滥用，因为这种方法过于复杂，缺乏通用性和应变能力，同时也增加了维护上的代价；相对来说，基于函数右移、去掉“IN ，OR ，<> ，IS NOT NULL ”、分解复杂的SQL语句等等方法，却是“放之四海皆准”的，可以放心大胆的使用。
同时，优化也不是“一劳永逸”的，必须随着情况的改变进行相应的调整。当数据库设计发生变化，包括更改表结构：字段和索引的增加、删除或改名等；业务逻辑发生变化：如查询方式、取值范围发生改变等等。在这种情况下，也必须对原有的优化进行调整，以适应效率上的需求。

常用sql语句

1、查看表空间的名称及大小

select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size

from dba_tablespaces t, dba_data_files d

where t.tablespace_name = d.tablespace_name

group by t.tablespace_name;

2、查看表空间物理文件的名称及大小

select tablespace_name, file_id, file_name,

round(bytes/(1024*1024),0) total_space

from dba_data_files

order by tablespace_name;

3、查看回滚段名称及大小

select segment_name, tablespace_name, r.status,

(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,

max_extents, v.curext CurExtent

From dba_rollback_segs r, v$rollstat v

Where r.segment_id = v.usn(+)

order by segment_name ;

4、查看控制文件

select name from v$controlfile;

5、查看日志文件

select member from v$logfile;

6、查看表空间的使用情况

select sum(bytes)/(1024*1024) as free_space,tablespace_name

from dba_free_space

group by tablespace_name;

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,

(B.BYTES*100)/A.BYTES “% USED”,(C.BYTES*100)/A.BYTES “% FREE”

FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C

WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

7、查看数据库库对象

select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;

8、查看数据库的版本

Select version FROM Product_component_version

Where SUBSTR(PRODUCT,1,6)=’Oracle’;
9、查看数据库的创建日期和归档方式

Select Created, Log_Mode, Log_Mode From V$Database;

10、捕捉运行很久的SQL

column username format a12

column opname format a16

column progress format a8

select username,sid,opname,

round(sofar*100 / totalwork,0) 　　 ‘%’ as progress,

time_remaining,sql_text

from v$session_longops , v$sql

where time_remaining <> 0

and sql_address = address

and sql_hash_value = hash_value

11、查看数据表的参数信息

SELECT partition_name, high_value, high_value_length, tablespace_name,

pct_free, pct_used, ini_trans, max_trans, initial_extent,

next_extent, min_extent, max_extent, pct_increase, FREELISTS,

freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,

empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,

last_analyzed

FROM dba_tab_partitions

–WHERE table_name = :tname AND table_owner = :towner

ORDER BY partition_position

12、查看还没提交的事务

select * from v$locked_object;

select * from v$transaction;

13、查找object为哪些进程所用

select

p.spid,

s.sid,

s.serial# serial_num,

s.username user_name,

a.type object_type,

s.osuser os_user_name,

a.owner,

a.object object_name,

decode(sign(48 - command),

to_char(command), ‘Action Code #’ 　　 to_char(command) ) action,

p.program oracle_process,

s.terminal terminal,

s.program program,

s.status session_status

from v$session s, v$access a, v$process p

where s.paddr = p.addr and

s.type = ‘USER’ and

a.sid = s.sid and

a.object=’SUBSCRIBER_ATTR’

order by s.username, s.osuser

14、回滚段查看

select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents

Extents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs,

v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes,

sys.dba_rollback_segs.status status from v$rollstat, sys.dba_rollback_segs,

v$rollname where v$rollname.name(+) = sys.dba_rollback_segs.segment_name and

v$rollstat.usn (+) = v$rollname.usn order by rownum

15、耗资源的进程（top session）

select s.schemaname schema_name, decode(sign(48 - command), 1,

to_char(command), ‘Action Code #’ 　　 to_char(command) ) action, status

session_status, s.osuser os_user_name, s.sid, p.spid , s.serial# serial_num,

nvl(s.username, ‘[Oracle process]’) user_name, s.terminal terminal,

s.program program, st.value criteria_value from v$sesstat st, v$session s , v$processp

where st.sid = s.sid and st.statistic# = to_number(’38′) and (’ALL’ = ‘ALL’

or s.status = ‘ALL’) and p.addr = s.paddr order by st.value desc, p.spid asc, s.username asc, s.osuser asc

library cache pin原理

library cache pin

=================
原理
~~~~~
An Oracle instance has a library cache that contains the description of
different types of objects e.g. cursors, indexes, tables, views, procedures,
… Those objects cannot be changed when they are used. They are locked by a
mechanism based on library locks and pins. A session that need to use an object
will first acquire a library lock in a certain mode (null, shared or exclusive)
on the object, in order to prevent other sessions from accessing the same
object (e.g. exclusive lock when recompiling a package or view) or to maintain
the object definition for a long time. Locking an object is sometimes referred
as the job to locate it in the library cache and lock it in a certain mode.
If the session wants to modify or examine the object, it must acquire after
the lock also a pin in a certain mode (again null, shared or exclusive).

Each SQL statement that want to use/modify objects that are locked or pinned
and whose lock/pin mode is incompatible with the requested mode, will wait
on events like ‘library cache pin’ or ‘library cache lock’ until a timeout
occurs. The timeout normally occurs after 5 minutes and the SQL statement
then ends with an ORA-4021. If a deadlock is detected, an ORA-4020 is given
back.

Dealing with slow downs related to “mysterious” library cache pins
and load locks we should look for the reason of the database object
invalidations. They are likely to be triggered by actions causing
changes to “LAST_DDL” attribute of database objects that have other
dependent ones. Typically they are the object maintenance operations -
ALTER, GRANT, REVOKE, replacing views, etc. This behavior. is described
in Oracle Server Application Developer’s Guide as object dependency
maintenance.

After object invalidation, Oracle tries to recompile the object at the
time of the first access to it. It may be a problem in case when other
sessions have pinned the object to the library cache. It is obvious that
it is more likely to occur with more active users and with more complex
dependencies (eg. many cross-dependent packages or package bodies).
In some cases waiting for object recompilation may even take hours
blocking all the sessions trying to access it.

ORA-04021 timeout occurred while waiting to lock object %s%s%s%s%s”.
Cause: While trying to lock a library object, a time-out occurred.
Action: Retry the operation later.

ORA-04020 deadlock detected while trying to lock object %s%s%s%s%s
Cause: While trying to lock a library object, a deadlock is detected.
Action: Retry the operation later.
(see )

2. Which views can be used to detect library locking problems?
—————————————————————-

Different views can be used to detect pin/locks:

DBA_KGLLOCK : one row for each lock or pin of the instance
-KGLLKUSE  session address
-KGLLKHDL  Pin/lock handle
-KGLLKMOD/KGLLKREQ  Holding/requested mode
0          no lock/pin held
1          null mode
2          share mode
3          exclusive mode
-KGLLKTYPE Pin/lock
(created via the $ORACLE_HOME/rdbms/admin/catblock.sql)

V$ACCESS : one row for each object locked by any user
-SID    session sid
-OWNER    username
-OBJECT object name
-TYPE    object type

V$DB_OBJECT_CACHE : one row for each object in the library cache
-OWNER       object owner
-NAME       object name or cursor text
-TYPE       object type
-LOCKS       number of locks on this object
-PINS       number of pins on this object

DBA_DDL_LOCKS : one row for each object that is locked (exception made of the cursors)
-SESSION_ID
-OWNER
-NAME
-TYPE
-MODE_HELD
-MODE_REQUESTED

V$SESSION_WAIT : each session waiting on a library cache pin or lock is blocked by some other session
-p1 = object address
-p2 = lock/pin address

3. How to find out why an ORA-4021 occurs?
——————————————–

When you execute the statement that generates the ORA-4021, it is possible
during the delay of 5 minutes to detect the reason for the blocking situation.
Following query can be used to find the blocking and waiting sessions:

FYI: You need to run the script. called “catblock.sql” first.
=== This script. can be found in: $ORACLE_HOME/rdbms/admin/catblock.sql

select /*+ ordered */ w1.sid  waiting_session,
h1.sid  holding_session,
w.kgllktype lock_or_pin,
w.kgllkhdl address,
decode(h.kgllkmod,  0, ‘None’, 1, ‘Null’, 2, ‘Share’, 3, ‘Exclusive’,
‘Unknown’) mode_held,
decode(w.kgllkreq,  0, ‘None’, 1, ‘Null’, 2, ‘Share’, 3, ‘Exclusive’,
‘Unknown’) mode_requested
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and  w.kgllktype       =  h.kgllktype
and  w.kgllkhdl       =  h.kgllkhdl
and  w.kgllkuse    = w1.saddr
and  h.kgllkuse    = h1.saddr
/

The result looks like:

WAITING_SESSION HOLDING_SESSION lock ADDRESS MODE_HELD MODE_REQU
————— ————— —- ——– ——— ———
16 12 Pin 03FA2270 Share Exclusive

The object that is locked can be found with v$object_dependency and
should be the same as the one mentioned in the ORA-4021 error message.
e.g.
select to_name from v$object_dependency where to_address = ‘03FA2270′;
should give:

TO_NAME
————-
DBMS_PIPE

You can find which library objects are used by each session via following
queries, e.g.
a. for the blocked session:

select distinct kglnaobj from x$kgllk where
kgllkuse in (select saddr from v$session where sid = 16);

b. for the blocking session

select distinct kglnaobj from x$kgllk where
kgllkuse in (select saddr from v$session where sid = 12);

One of those objects can be the cursor or statement that each session is
executing/trying to execute.

You can also use the $ORACLE_HOME/rdbms/admin/utldtree.sql utility to find out
how the dependency tree looks like and which objects are dependent on e.g.
DBMS_PIPE. One of those objects will be the sql statement of the holding
session. A variant script. on utldtree.sql stands in [NOTE:139594.1] and
gives which objects an object depends on.

Library cache pins are used to manage library cache concurrency.
Pinning an object causes the heaps to be loaded into memory (if not already loaded).
PINS can be acquired in NULL, SHARE or EXCLUSIVE modes and can be considered like a special form. of lock.
A wait for a “library cache pin” implies some other session holds that PIN in an incompatible mode.

P1 = Handle address
P2 = Pin address
P3 = Encoded Mode & Namespace

·Handle address
~~~~~~~~~~~~~~~~
Use P1RAW rather than P1
This is the handle of the library cache object which the waiting session wants to acquire a pin on.

查找library cache对象
~~~~~~~~~~~~~~
The actual object being waited on can be found using
SELECT kglnaown “Owner”, kglnaobj “Object”
FROM x$kglob
WHERE kglhdadr=’&P1RAW’
;
·Pin address
~~~~~~~~~~~~~
Use P2RAW rather than P2
This is the address of the PIN itself.
·Encoded Mode & Namespace
~~~~~~~~~~~~~~~~~~~~~~~~~
In Oracle 7.0 - 8.1.7 inclusive the value is 10 * Mode + Namespace.
In Oracle 9.0 - 9.2 inclusive the value is 100 * Mode + Namespace.

Where:

Mode is the mode in which the pin is wanted. This is a number thus:
o 2 - Share mode
o 3 - Exclusive mode

Namespace is just the namespace number of the namespace in the library cache in which the required object lives:
o       0 SQL Area
o       1 Table / Procedure / Function / Package Header
o       2 Package Body
o       3 Trigger
o       4 Index
o       5 Cluster
o       6 Object
o       7 Pipe
o       13 Java Source
o       14 Java Resource
o       32 Java Data

对于’Consistent Gets’,'’Physical Reads’和’DB Block Gets’的理解和解释

在Oracle的文档中有这样的解释：
db block gets：Number of times a CURRENT block was requested.
consistent gets：Number of times a consistent read was requested for a block.
physical reads：Total number of data blocks read from disk. This number equals the value of “physical reads direct” plus all reads into buffer cache.
———————————————
针对以上3个概念进行的说明解释及关系如下：
1、DB Block Gets（当前请求的块数目）
当前模式块意思就是在操作中正好提取的块数目，而不是在一致性读的情况下而产生的块数。正常的情况下，一个查询提取的块是在查询开始的那个时间点上存在的数据块，当前块是在这个时刻存在的数据块，而不是在这个时间点之前或者之后的数据块数目。

2、Consistent Gets（数据请求总数在回滚段Buffer中的数据一致性读所需要的数据块）
这里的概念是在处理你这个操作的时候需要在一致性读状态上处理多少个块，这些块产生的主要原因是因为由于在你查询的过程中，由于其他会话对数据块进行操作，而对所要查询的块有了修改，但是由于我们的查询是在这些修改之前调用的，所以需要对回滚段中的数据块的前映像进行查询，以保证数据的一致性。这样就产生了一致性读。

3、Physical Reads（物理读）
就是从磁盘上读取数据块的数量，其产生的主要原因是：
1、在数据库高速缓存中不存在这些块
2、全表扫描
3、磁盘排序

它们三者之间的关系大致可概括为：
逻辑读指的是Oracle从内存读到的数据块数量。一般来说是’consistent gets’ + ‘db block gets’。当在内存中找不到所需的数据块的话就需要从磁盘中获取，于是就产生了’phsical reads’。

How Do I get Started with Oracle?

November 22, 2006 at 18:23 · Filed under This is actually a fairly common question and not one that is often addressed adequately. The following is the, slightly edited text of a reply sent by Howard Rogers www.dizwell.com to someone who asked this exact question. The post is so good, that with Howard’s permission I reproduce it here. The ‘here’ referred to below is the usenet group comp.databases.oracle.server which can be reached via the link on the left or via your favourite newsreader.

Be patient. You’re starting from scratch, so there’s a lot to learn, and much of it may seem very strange to begin with. Visit The online documentation set to get access to the latest Oracle documentation (though your CD set will probably include it already). Read the official Concepts Guide. Then visit sites such as that maintained by Jonathan Lewis, and Ask Tom. Visit Google and hunt around for advice. Take nothing on face value: test it yourself to destruction. Lurk here for a month or two, and try and pick up on the sort of questions being asked, and ask yourself how *you* would answer them. Then see what answers actually come through, and compare. When you’re feeling brave, post some of your answers and see how they are taken by people.

Buy books. Anything with the names Jonathan Lewis, or Thomas Kyte on the cover are *extremely* good bets for accurate advice. O’Reilly are good. As are Apress (used to be Wrox) Avoid anything that mentions OCP (Oracle Certified Professional). The qualification is not worth a damn, and leads you straight into myth territory.

If you can afford an Oracle training course (most people can’t), go. Go to DBA Fundamantals I if you can… the architecture stuff they cover on that is extremely good (but you play Russian Roulette with the instructor you get. If the guy starts reading from the course notes in the first hour, then leave and ask to be scheduled with someone else at a later date). DBA Fundamentals II is quite good, but is light on networking, and the Backup and Recovery stuff is not difficult. Don’t bother with Performance Tuning, because it’s a badly-written course that addresses none of the real issues. I think the general thing is to get involved and to get communicating, and you’ve already started that by posting here. Just don’t stop now.

The other general thing is to test and test and test, and verify for yourself. Experiment, and stuff the consequences. The other thing I would suggest, given your background, is that you learn Oracle at the command line, and learn *Oracle*. Worry about putting a graphical front-end app. on top of Oracle later. When you’ve got command-line Oracle sorted, the graphical front-ends will come as second nature, and will be better for the understanding you have of the back-end architecture and inner-workings.

And finally, enjoy yourself. Oracle is quite a majestic system. It’s got an internal logic that is beautiful, and the sense of power that you can get from controlling those internal workings can be quite exhilerating. So have fun.

PL/SQL单行函数和组函数详解

函数是一种有零个或多个参数并且有一个返回值的程序。在SQL中Oracle内建了一系列函数，这些函数都可被称为SQL或PL/SQL语句，函数主要分为两大类：

单行函数

组函数

本文将讨论如何利用单行函数以及使用规则。

SQL中的单行函数

SQL和PL/SQL中自带很多类型的函数，有字符、数字、日期、转换、和混合型等多种函数用于处理单行数据，因此这些都可被统称为单行函数。这些函数均可用于SELECT,WHERE、ORDER BY等子句中，例如下面的例子中就包含了TO_CHAR,UPPER,SOUNDEX等单行函数。
SELECT ename,TO_CHAR(hiredate,’day,DD-Mon-YYYY’)
FROM emp
Where UPPER(ename) Like ‘AL%’
ORDER BY SOUNDEX(ename)

单行函数也可以在其他语句中使用，如update的SET子句，INSERT的VALUES子句，DELET的WHERE子句,认证考试特别注意在SELECT语句中使用这些函数，所以我们的注意力也集中在SELECT语句中。

NULL和单行函数

在如何理解NULL上开始是很困难的，就算是一个很有经验的人依然对此感到困惑。NULL值表示一个未知数据或者一个空值，算术操作符的任何一个操作数为NULL值，结果均为提个NULL值,这个规则也适合很多函数，只有CONCAT,DECODE,DUMP,NVL,REPLACE在调用了NULL 参数时能够返回非NULL值。在这些中NVL函数时最重要的，因为他能直接处理NULL值，NVL有两个参数：NVL(x1,x2),x1和x2都式表达式，当x1为null时返回X2,否则返回x1。

下面我们看看emp数据表它包含了薪水、奖金两项，需要计算总的补偿
column name emp_id salary bonus

key type pk
nulls/unique nn,u nn
fk table
datatype number number number
length 11.2 11.2

不是简单的将薪水和奖金加起来就可以了，如果某一行是null值那么结果就将是null，比如下面的例子：
update emp
set salary=(salary+bonus)*1.1

这个语句中，雇员的工资和奖金都将更新为一个新的值，但是如果没有奖金，即 salary + null,那么就会得出错误的结论，这个时候就要使用nvl函数来排除null值的影响。
所以正确的语句是：
update emp
set salary=(salary+nvl(bonus,0)*1.1

单行字符串函数

单行字符串函数用于操作字符串数据，他们大多数有一个或多个参数，其中绝大多数返回字符串

ASCII()
c1是一字符串，返回c1第一个字母的ASCII码，他的逆函数是CHR()
SELECT ASCII(’A') BIG_A,ASCII(’z') BIG_z FROM emp

BIG_A BIG_z
65 122

CHR(＜i＞)[NCHAR_CS]
i是一个数字，函数返回十进制表示的字符
select CHR(65),CHR(122),CHR(223) FROM emp

CHR65 CHR122 CHR223
A z B

CONCAT(,)
c1,c2均为字符串，函数将c2连接到c1的后面，如果c1为null,将返回c2.如果c2为null,则返回c1，如果c1、c2都为null，则返回null。他和操作符||返回的结果相同
select concat(’slobo ‘,’Svoboda’) username from dual

username

slobo Syoboda

INITCAP()
c1为一字符串。函数将每个单词的第一个字母大写其它字母小写返回。单词由空格，控制字符，标点符号限制。
select INITCAP(’veni,vedi,vici’) Ceasar from dual

Ceasar

Veni,Vedi,Vici

INSTR(,[,＜i＞[,]])
c1,c2均为字符串，i,j为整数。函数返回c2在c1中第j次出现的位置，搜索从c1的第i个字符开始。当没有发现需要的字符时返回0,如果i为负数，那么搜索将从右到左进行，但是位置的计算还是从左到右，i和j的缺省值为1.
select INSTR(’Mississippi’,'i’,3,3) from dual

INSTR(’MISSISSIPPI’,'I’,3,3)

select INSTR(’Mississippi’,'i’,-2,3) from dual

INSTR(’MISSISSIPPI’,'I’,3,3)

INSTRB(,[,i[,j])
与INSTR（）函数一样，只是他返回的是字节，对于单字节INSTRB()等于INSTR()

LENGTH()
c1为字符串，返回c1的长度，如果c1为null，那么将返回null值。
select LENGTH(’Ipso Facto’) ergo from dual

ergo

LENGTHb()
与LENGTH()一样，返回字节。

lower()
返回c的小写字符，经常出现在where子串中
select LOWER(colorname) from itemdetail WHERE LOWER(colorname) LIKE ‘%white%’

COLORNAME

Winterwhite

LPAD(,＜i＞[,])
c1,c2均为字符串，i为整数。在c1的左侧用c2字符串补足致长度i,可多次重复，如果i小于c1的长度，那么只返回i那么长的c1字符，其他的将被截去。c2的缺省值为单空格，参见RPAD。
select LPAD(answer,7,'’) padded,answer unpadded from question;

PADDED UNPADDED

Yes Yes
NO NO
Maybe maybe

LTRIM(,)
把c1中最左边的字符去掉，使其第一个字符不在c2中，如果没有c2，那么c1就不会改变。
select LTRIM(’Mississippi’,'Mis’) from dual

LTR

ppi

RPAD(,＜i＞[,])
在c1的右侧用c2字符串补足致长度i,可多次重复，如果i小于c1的长度，那么只返回i那么长的c1字符，其他的将被截去。c2的缺省值为单空格,其他与LPAD相似

RTRIM(,)
把c1中最右边的字符去掉，使其第后一个字符不在c2中，如果没有c2，那么c1就不会改变。

REPLACE(,[,])
c1,c2,c3都是字符串，函数用c3代替出现在c1中的c2后返回。
select REPLACE(’uptown’,'up’,'down’) from dual

REPLACE

downtown

STBSTR(,＜i＞[,])
c1为一字符串，i,j为整数，从c1的第i位开始返回长度为j的子字符串，如果j为空，则直到串的尾部。
select SUBSTR(’Message’,1,4) from dual

SUBS

Mess

SUBSTRB(,＜i＞[,])
与SUBSTR大致相同，只是I,J是以字节计算。

SOUNDEX()
返回与c1发音相似的词
select SOUNDEX(’dawes’) Dawes SOUNDEX(’daws’) Daws, SOUNDEX(’dawson’) from dual

Dawes Daws Dawson

D200 D200 D250

TRANSLATE(,,)
将c1中与c2相同的字符以c3代替
select TRANSLATE(’fumble’,'uf’,'ar’) test from dual

TEXT

ramble

TRIM([[]] from c3)
将c3串中的第一个，最后一个，或者都删除。
select TRIM(’ space padded ‘) trim from dual

TRIM

space padded

UPPER()
返回c1的大写，常出现where子串中
select name from dual where UPPER(name) LIKE ‘KI%’

NAME

KING
单行数字函数

单行数字函数操作数字数据，执行数学和算术运算。所有函数都有数字参数并返回数字值。所有三角函数的操作数和值都是弧度而不是角度，oracle没有提供内建的弧度和角度的转换函数。

ABS()
返回n的绝对值

ACOS()
反余玄函数，返回-1到1之间的数。n表示弧度
select ACOS(-1) pi,ACOS(1) ZERO FROM dual

PI ZERO

3.14159265 0

ASIN()
反正玄函数，返回-1到1，n表示弧度

ATAN()
反正切函数，返回n的反正切值，n表示弧度。

CEIL()
返回大于或等于n的最小整数。

COS()
返回n的余玄值，n为弧度

COSH()
返回n的双曲余玄值，n 为数字。
select COSH(<1.4>) FROM dual

COSH(1.4)

2.15089847

EXP()
返回e的n次幂，e=2.71828183.

FLOOR()
返回小于等于N的最大整数。

LN()
返回N的自然对数，N必须大于0

LOG(,)
返回以n1为底n2的对数

MOD()
返回n1除以n2的余数，

POWER(,)
返回n1的n2次方

ROUND(,)
返回舍入小数点右边n2位的n1的值，n2的缺省值为0，这回将小数点最接近的整数，如果n2为负数就舍入到小数点左边相应的位上，n2必须是整数。
select ROUND(12345,-2),ROUND(12345.54321,2) FROM dual

ROUND(12345,-2) ROUND(12345.54321,2)

12300 12345.54

SIGN()
如果n为负数，返回-1,如果n为正数，返回1，如果n=0返回0.

SIN（)
返回n的正玄值,n为弧度。

SINH()
返回n的双曲正玄值,n为弧度。

SQRT()
返回n的平方根,n为弧度

TAN（)
返回n的正切值,n为弧度

TANH()
返回n的双曲正切值,n为弧度

TRUNC(,)
返回截尾到n2位小数的n1的值，n2缺省设置为0，当n2为缺省设置时会将n1截尾为整数，如果n2为负值，就截尾在小数点左边相应的位上。

单行日期函数

单行日期函数操作DATA数据类型，绝大多数都有DATA数据类型的参数，绝大多数返回的也是DATA数据类型的值。

ADD_MONTHS(,＜i＞)
返回日期d加上i个月后的结果。i可以使任意整数。如果i是一个小数，那么数据库将隐式的他转换成整数，将会截去小数点后面的部分。

LAST_DAY()
函数返回包含日期d的月份的最后一天

MONTHS_BETWEEN(,)
返回d1和d2之间月的数目,如果d1和d2的日的日期都相同，或者都使该月的最后一天，那么将返回一个整数，否则会返回的结果将包含一个分数。

NEW_TIME(,,)
d1是一个日期数据类型，当时区tz1中的日期和时间是d时，返回时区tz2中的日期和时间。tz1和tz2时字符串。

NEXT_DAY(,)
返回日期d后由dow给出的条件的第一天，dow使用当前会话中给出的语言指定了一周中的某一天，返回的时间分量与d的时间分量相同。
select NEXT_DAY(’01-Jan-2000′,’Monday’) “1st Monday”,NEXT_DAY(’01-Nov-2004′,’Tuesday’)+7 “2nd Tuesday”) from dual;

1st Monday 2nd Tuesday

03-Jan-2000 09-Nov-2004

ROUND([,])
将日期d按照fmt指定的格式舍入，fmt为字符串。

SYADATE
函数没有参数，返回当前日期和时间。

TRUNC([,])
返回由fmt指定的单位的日期d.

单行转换函数

单行转换函数用于操作多数据类型，在数据类型之间进行转换。

CHARTORWID()
c 使一个字符串，函数将c转换为RWID数据类型。
SELECT test_id from test_case where rowid=CHARTORWID(’AAAA0SAACAAAALiAAA’)

CONVERT(,[,])
c尾字符串，dset、sset是两个字符集，函数将字符串c由sset字符集转换为dset字符集，sset的缺省设置为数据库的字符集。

HEXTORAW()
x为16进制的字符串，函数将16进制的x转换为RAW数据类型。

RAWTOHEX()
x是RAW数据类型字符串，函数将RAW数据类转换为16进制的数据类型。

ROWIDTOCHAR()
函数将ROWID数据类型转换为CHAR数据类型。

TO_CHAR([[,)
x是一个data或number数据类型，函数将x转换成fmt指定格式的char数据类型，如果x为日期 nlsparm= NLS_DATE_LANGUAGE 控制返回的月份和日份所使用的语言。如果x为数字nlsparm=NLS_NUMERIC_CHARACTERS 用来指定小数位和千分位的分隔符，以及货币符号。
NLS_NUMERIC_CHARACTERS =”dg”, NLS_CURRENCY=”string”

TO_DATE([,[,）
c表示字符串，fmt表示一种特殊格式的字符串。返回按照fmt格式显示的c,nlsparm表示使用的语言。函数将字符串c转换成date数据类型。

TO_MULTI_BYTE()
c表示一个字符串，函数将c的担子截字符转换成多字节字符。

TO_NUMBER([,[,)
c表示字符串，fmt表示一个特殊格式的字符串，函数返回值按照fmt指定的格式显示。nlsparm表示语言，函数将返回c代表的数字。

TO_SINGLE_BYTE()
将字符串c中得多字节字符转化成等价的单字节字符。该函数仅当数据库字符集同时包含单字节和多字节字符时才使用

其它单行函数

BFILENAME(
,)
dir是一个directory类型的对象，file为一文件名。函数返回一个空的BFILE位置值指示符，函数用于初始化BFILE变量或者是BFILE列。

DECODE(,,[,,,[])
x是一个表达式，m1是一个匹配表达式，x与m1比较，如果m1等于x，那么返回r1,否则,x与m2比较，依次类推m3,m4,m5….直到有返回结果。

DUMP(,[,[,[,]]])
x是一个表达式或字符，fmt表示8进制、10进制、16进制、或则单字符。函数返回包含了有关x的内部表示信息的VARCHAR2类型的值。如果指定了n1,n2那么从n1开始的长度为n2的字节将被返回。

EMPTY_BLOB()
该函数没有参数，函数返回一个空的BLOB位置指示符。函数用于初始化一个BLOB变量或BLOB列。

EMPTY_CLOB()
该函数没有参数，函数返回一个空的CLOB位置指示符。函数用于初始化一个CLOB变量或CLOB列。

GREATEST()
exp_list是一列表达式，返回其中最大的表达式，每个表达式都被隐含的转换第一个表达式的数据类型，如果第一个表达式是字符串数据类型中的任何一个，那么返回的结果是varchar2数据类型，同时使用的比较是非填充空格类型的比较。

LEAST()
exp_list是一列表达式，返回其中最小的表达式，每个表达式都被隐含的转换第一个表达式的数据类型，如果第一个表达式是字符串数据类型中的任何一个，将返回的结果是varchar2数据类型，同时使用的比较是非填充空格类型的比较。

UID
该函数没有参数，返回唯一标示当前数据库用户的整数。

USER
返回当前用户的用户名

USERENV()
基于opt返回包含当前会话信息。opt的可选值为：

ISDBA　　　会话中SYSDBA脚色响应，返回TRUE
SESSIONID 　返回审计会话标示符
ENTRYID 　　返回可用的审计项标示符
INSTANCE　　在会话连接后，返回实例标示符。该值只用于运行Parallel 服务器并且有多个实例的情况下使用。
LANGUAGE　　返回语言、地域、数据库设置的字符集。
LANG　　　　返回语言名称的ISO缩写。
TERMINAL　　为当前会话使用的终端或计算机返回操作系统的标示符。

VSIZE()
x是一个表达式。返回x内部表示的字节数。
SQL中的组函数

组函数也叫集合函数，返回基于多个行的单一结果，行的准确数量无法确定，除非查询被执行并且所有的结果都被包含在内。与单行函数不同的是，在解析时所有的行都是已知的。由于这种差别使组函数与单行函数有在要求和行为上有微小的差异.

组（多行）函数

与单行函数相比，oracle提供了丰富的基于组的，多行的函数。这些函数可以在select或select的having子句中使用，当用于select子串时常常都和GROUP BY一起使用。

AVG([{DISYINCT|ALL}])
返回数值的平均值。缺省设置为ALL.
SELECT AVG(sal),AVG(ALL sal),AVG(DISTINCT sal) FROM scott.emp

AVG(SAL) AVG(ALL SAL) AVG(DISTINCT SAL)

1877.94118 1877.94118 1916.071413

COUNT({*|DISTINCT|ALL} )
返回查询中行的数目，缺省设置是ALL,*表示返回所有的行。

MAX([{DISTINCT|ALL}])
返回选择列表项目的最大值，如果x是字符串数据类型，他返回一个VARCHAR2数据类型，如果X是一个DATA数据类型，返回一个日期，如果X是numeric数据类型，返回一个数字。注意distinct和all不起作用，应为最大值与这两种设置是相同的。

MIN([{DISTINCT|ALL}])
返回选择列表项目的最小值。

STDDEV([{DISTINCT|ALL}])
返回选者的列表项目的标准差，所谓标准差是方差的平方根。

SUM([{DISTINCT|ALL}])
返回选择列表项目的数值的总和。

VARIANCE([{DISTINCT|ALL}])
返回选择列表项目的统计方差。

用GROUP BY给数据分组

正如题目暗示的那样组函数就是操作那些已经分好组的数据，我们告诉数据库用GROUP BY怎样给数据分组或者分类，当我们在SELECT语句的SELECT子句中使用组函数时，我们必须把为分组或非常数列放置在GROUP BY子句中，如果没有用group by进行专门处理，那么缺省的分类是将整个结果设为一类。
select stat,counter(*) zip_count from zip_codes GROUP BY state;

ST ZIP_COUNT
– ———
AK 360
AL 1212
AR 1309
AZ 768
CA 3982

在这个例子中，我们用state字段分类；如果我们要将结果按照zip_codes排序,可以用ORDER BY语句，ORDER BY子句可以使用列或组函数。
select stat,counter(*) zip_count from zip_codes GROUP BY state ORDER BY COUNT(*) DESC;

ST COUNT(*)
– ——–
NY 4312
PA 4297
TX 4123
CA 3982

用HAVING子句限制分组数据

现在你已经知道了在查询的SELECT语句和ORDER BY子句中使用主函数，组函数只能用于两个子串中，组函数不能用于WHERE子串中，例如下面的查询是错误的：
错误
SELECT sales_clerk,SUN(sale_amount) FROM gross_sales WHERE sales_dept=’OUTSIDE’ AND SUM(sale_amount)>10000 GROUP BY sales_clerk

这个语句中数据库不知道SUM()是什么，当我们需要指示数据库对行分组，然后限制分组后的行的输出时，正确的方法是使用HAVING语句：
SELECT sales_clerk,SUN(sale_amount)
FROM gross_sales
WHERE sales_dept=’OUTSIDE’
GROUP BY sales_clerk
HAVING SUM(sale_amount)>10000;

嵌套函数

函数可以嵌套。一个函数的输出可以是另一个函数的输入。操作数有一个可继承的执行过程。但函数的优先权只是基于位置，函数遵循由内到外，由左到右的原则。嵌套技术一般用于象DECODE这样的能被用于逻辑判断语句IF….THEN…ELSE的函数。

嵌套函数可以包括在组函数中嵌套单行函数，或者组函数嵌套入单行函数或组函数中。比如下面的例子：
SELECT deptno, GREATEST(COUNT(DISTINCT job),COUNT(DISTINCT mgr) cnt,
COUNT(DISTINCT job) jobs,
COUNT(DISTINCT mgr) mgrs
FROM emp
GROUP BY deptno;

DEPTNO CNT JOBS MGRS
—— — —- —-
10 4 4 2
20 4 3 4
30 3 3 2

Oracle 11g 500种新功能着重商业智能及内容管理

11g将增加近五百种的新功能，其中涵盖了商业智能及内容管理，目前已在测试当中，但尚未有明确的问世时间。数据库大厂甲骨文（Oracle）执行副总裁Chuck Rozwat在周一（10/23）举行的Oracle OpenWorld会议中，发表并展示甲骨文新一代的数据库软件─Database 11g，此新版本距上一个版本已有三年的时间。甲骨文指出，11g将增加近五百种的新功能，其中涵盖了商业智能及内容管理。Chuck Rozwat表示，Database 11g目前已在测试当中，但尚未有明确的问世时间，此外，他指出Database 11g为一重要版本，涵盖数百种新功能，以让新数据库有更高的可用性、效能、延展性及管理功能等，该新版本能够符合拥有庞大数据库使用者的需求，同时，也具备了商业智能与内容管理功能。在该会议中展示了Database 11g的执行效能，并指出它的速度已快到超越专门的档案系统，与档案系统相较，Database 11g移转1G的数据仅耗费了9秒钟，而档案系统则需要12秒。Chuck Rozwat认为，这样的高效能执行速度对需要实时取得数据的客户而言是非常重要的。甲骨文新版数据库支持网格系统，Chuck Rozwat指出这将有助于推广企业采用网格运算。此外，它所具备的新压缩技术最多可减少2/3的数据储存空间。甲骨文亦将近年来并购的技术纳入新版数据库软件中，例如今年10月买下Sunopsis所拥有的数据转换整合技术，就可让Database 11g具备更容易建置数据仓储的特色，因为它可整合来自不同数据库的数据，包括IBM的DB2及微软的SQL Server等。甲骨文数据库近年来面临开放源码产品的竞争，这使得甲骨文逐渐向开源码靠拢，例如买下Sleepycat软件公司，取得该公司的 Berkeley DB开放源码数据库，以及去年买下的Innobase Oy所开发的InnoDB数据库引擎可将数据储存在MySQL数据库中，该引擎可透过MySQL数据库开源码通用授权取得。有趣的是，采用甲骨文数据库的用户很少很快就升级到最新版本，这些企业通常赖着旧版本，除非他们有新的企业数据库应用，否则鲜少进行升级。是在2004年2月发表，根据Chuck Rozwat在今年6月的估计，仅有约一半的客户升级到10g。

Chuck Rozwat

Database 10g

Oracle的全文检索技术

Oracle 一直致力于全文检索技术的研究，当Oracle9i Rlease2发布之时，Oracle数据库的全文检索技术已经非常完美，Oracle Text使Oracle9i具备了强大的文本检索能力和智能化的文本管理能力。Oracle Text是Oracle9i采用的新名称，在Oracle8/8i中它被称作Oracle interMedia Text。使用Oracle Text，可以方便而有效地利用标准的SQL工具来构建基于文本的新的开发工具或对现有应用程序进行扩展。应用程序开发人员可以在任何使用文本的 Oracle数据库应用程序中充分利用Oracle Text搜索，应用范围可以是现有应用程序中可搜索的注释字段，也可是实现涉及多种文档格式和复杂搜索标准的大型文档管理系统。Oracle Text支持Oracle数据库所支持的大多数语言的基本全文搜索功能。

虽然大多数大型数据库都支持全文检索，但Oracle在这方面无疑是最出色的。 Oracle能搜索多种格式的文档，如Word,Execl,PowerPoint,Html,PDF等等。但在使用中也发现有遗憾的地方，Oracle Text无论使用何种过滤器（INSO_FILTER或NULL_FILTER）及何种词法分析器（BASIC_LEXER, CHINESE_VGRAM_LEXER还是CHINESE_LEXER）都不能检索出中文内容的文本文档（TXT,RTF）。