oracle常见问题与解答

1.对于sql，有几种方法查看执行计划，每种方法有什么区别，对于一条正在执行的sql，如何查看真实的，正在使用的执行计划？

答：第一种方法：explain plan for…,生成指定sql语句的执行计划，并把执行计划保存到用户指定的表中，供用户查询，但不作为真正运行时的执行计划，因为并没有保存在library cache中，得到的执行计划也不只library cache中的，所以不是真正的执行计划。

(1)SQL>explain plan for select * from emp

(2)SQL>select * from table(dbms_xplan.display);


第二种方法：SQL trace 和10046事件。查看trace文件，跟踪某个session的活动情况，跟踪的结果包括生成SQL语句的执行计划，可以用tkprof工具查看跟踪文件。执行计划有parse,bind,exec,fetch4个子过程，三个指标比较重要：disk(物理磁盘读取次数)，query(一致性读),current(当前读)这3列，调整的目标也是降低这3个统计量。跟踪会话信息：

(1)exec dbms_support.start_trace_in_session(sid=>,serial=>,waits=>true,binds=>true),其中的sid和serial可以从v$session中获取。(2)从v$process中获取os的pid和oracle的pid.然后SQL>oradebug setospid …; SQL>oradebug setorapid…;


第三种方法：从library cache中获取,就是真正的执行计划。其实就是综合利用了v$sql_plan,v$sql_plan_statics(statics_level参数设为all),v$sql_workarea,v$sql_plan_statics_all这几张视图。可以对以上几张视图写查询，来获得特定sql语句真正的执行计划，也可以按照下面的步骤得到真正的执行计划。

(1) select /*zhangxm*/ count(*) from table….

(2) Select sql_id from v$sql where sql_text like ‘% zhangxm %’; --得到这个语句的sql_id

(3) Select * from table(dbms_xplan.display_cursor(‘sql_id’,null,’basic’)); --以basic方式查

(4) Select * from table(dbms_xplan.display_cursor(‘sql_id’,null,’typical’));--以typical方式

(5) Select * from table(dbms_xplan.display_cursor(‘sql_id’,null,’all)); --以all方式查看

看其中的一些列，比如isstats,memstats,allstats:实际花费。A_time:语句执行时真正的花费。

还有一种：select * from table(dbms_xplan.display_cursor(‘sql_id’));--调用display_awr,传入sql_id参数。


2.常见oracle 错误，如 ora-01555 ， ora-4031等，说说起因，及常见的规避方法。

答：引起ora-01555错误原因:回滚段中的数据被新事务覆盖使得一个耗时很长的查询读不到需要读取的数据快。措施：undo_management 应该设置为auto.加大undotablespace参数值。加大undo_retention参数值。但也不能设的太大，太大会引起系统的性能下降。

引起ora-04031错误原因:在共享池中试图分配内存失败的时候，Oracle 首先刷新池中当前没使用的所有对象,使空闲内存块合并。如果仍然没有足够大单个的大块内存满足请求,就会产生ORA-04031 错误。措施：加大shared_pool_size参数的值。但如果当前该值已经比较大了,那么ORA-4031错误发生的原因很可能是因为oracle数据库的BUG,或是少了某个修补，或是应用程序未能很好的使用绑定变量(bind variable)而引起。这个时候应该给数据库打最新的修补或调整应用程序，使应用程序尽可能地使用绑定变量，单纯增加shared_pool的大小一般无法解决问题。

还有一种ORA-4031错可能是因为shared_pool中的碎片太多所致。查询v$shared_pool_reserved视图，看其中的一个字段request_failures,如果该值不为0那么就是由于碎片太多引起。措施：可以用刷新shared_pool的方法暂时解决ora-4031的故障，但可能会引起数据库暂时的性能下降。

其他的一些基本的错误，比如表空间没有足够的空间可分配而引发。比如ora-1652、ora-1653、ora-1654和ora-1655,针对这些错误，一般解决的方法有：(1)增加数据文件或调整数据文件的大小。(2) 设置autoextend on .(3) 收缩对象。 (4) 降低undo_retention (5) 检查长时间运行的查询sql,试着优化sql语句。

另外如ora-600错:这是oracle内核产生的一种错误，会伴随发生其他ora的错误。措施：可以通过借助metalink寻找相关的案例以及解决的方法。


3.rac环境下的cache fusion概念如何理解，如何设计才能使RAC系统的性能优良并持续稳定并体现出RAC对于单机的优越性，主要有哪些方面?

答：Cache fusion:缓冲区融合.在rac环境下,db cache在每个实例中都有一份.这些db cache之间需要融合才能保证数据访问的一致性.由于多个实例中多个db cache的存在,导致了db cache需要在多个实例之间进行传递,有几种模式:读读共享,读写共享,写写共享。

Cache fusion最主要的思路是分割了全局锁的模式和授予这个锁的持有者的角色。通过IPC(inter process communication)机制在缓冲之间传递db cache,并且在系统中保持同一个数据块的多个PI block(数据快影像)。

具体优化的措施有：

(1) 应用隔离:在rac不同节点跑不同应用,最大幅度减少数据共享。

多个应用共享非关键数据,核心业务数据的关联度较小,确保rac间共享的数据较少,访问冲突限制在一个可控的范围内。

(2) 通过表分区来限制某个分区被某个实例使用减少热块争用。通过表分区,使数据分散在数个segment中,其高水位推进,数据访问等也被分散。

(3) 增加db cache的命中率

Db cache命中率提高,不仅减少I/O,而且可以大幅度减少global cache cr request等待。如果可以直接找到db cache,就不需要访问其它实例了;如果没有找到,就要到其他实例中去查找,如果还没找到,才会从硬盘上去读取,开销就比单实例环境大很多。命中率最好在95%以上。

(4) 增加共享池的命中率

保持比较充足的共享池资源,使用好的编程习惯,合理使用绑定变量等都有助于提高rac的性能.

(5) 加大sequence 的cache

在rac环境下,sequence变为全局性,不同节点要生成序列号,就会产生对sequence资源的争用。sequence一般作为主键发生器,使用频率高。在rac环境下，需要设置较大的sequence cache,避免发生严重的争用,从而影响到业务。

(6) 使用只读表空间.

在cache fusion下,如果某个表空间是只读的,那么这个表空间中数据的访问只需要本地操作就行了,不需要rac互相的协同.实际上,数据在某个时间段里都会有大量的只读数据,而如果这些数据访问十分频繁的话,就会对ges和gcs产生严重的影响。

(7) 减少大表的全表扫描.

全表扫描对db cache的影响十分大,会占用大量的db cache,这样就会把很多数据快从db cache中挤出去。而且也会带来大量的物理读,开销远大于单机环境。

(8) 限制并行查询在实例范围内,不要在rac实例之间做并行查询

跨实例做并行查询主要是为了解决单机cup能力不足的问题,但是跨实例并行查询会引起gcs方面的问题.其实如果单机cup能力比较强的话,使用跨实例并行查询往往会得不偿失。在rac下,通过设置instance_groups和parallel_instance_group参数就可以实现将并行查询限制在本机上。

(9) 数据的横向隔离.

是在应用隔离的基础上进一步的措施.如果某个应用非常庞大,一个单节点无法承担,那么数据的横向隔离就可以派上用处。以我们sg186电力营销业务为例,其核心业务数据是按照各个供电单位进行范围分区,那么可以设置部分本地的应用连接到实例1上,部分本地网的应用连接到实例2上.通过表分区来确保核心业务数据之间的冲突降低到最小,从而避免由于rac中数据块争用带来的问题。


4.AIX系统中，主机参数maxperm,minperm，对数据库有何影响，该如何设置此参数。或者你了解HP-UX中，有什么参数与该参数类似?

答：AIX操作系统中的内存可以认为分为2个部分，1个部分为计算内存（Computational），用于应用程序运行使用。另1个部分为文件缓存（Non-Comp）,用于文件缓存。AIX操作系统通过 minperm%，maxperm%, maxclient%等参数控制系统的内存使用。

对于如何设置maxperm,minperm,我想最为重要的是考虑应用，看业务是计算型内存使用多，还是文件型内存使用多。如果服务器是ftp服务器，或者nim服务器，nfs服务器，那么非计算内存就不要设置太小。而如果是数据库，那么非计算内存设置小一些，计算型内存尽量给大一些。

比如典型的aix系统，内存16GB 64位操作系统，oracle可以给非计算内存5%-10%。给oracle的内存为aix系统物理内存的40%到60%。一般的数据库服务器，文件型内存可以使用较少的空间，因为文件型内存并不主动释放，可能造成内存资源的短缺及Paging Space使用率过高，所以数据库服务器上maxclient、maxperm、minperm的值不宜过大，一般可以这样设置：maxperm% = 12, minperm% = 5。