Oracle程序员面试分类模拟21

Oracle程序员面试分类模拟21简答题1.

如何获取trace文件的路径?正确答案：跟踪文件(TraceFile)一般位于“user_dump_dest”参数所指定的目录中，具体路径可以通过以（江南博哥）下几种方式查询获得。

(1)通过user_dump_dest查询运行如下SQL语句来创建视图：

创建公共同义词的命令如下：

查询同义词或视图都可以得到当前会话的trace文件名。

(2)Oracle11g可以通过查询V$DIAG_INFO获取

(3)oradebugtracefile_name获取

(4)通过参数直接查询

2.

SQL_TRACE是什么?诊断事件是什么?常用的10046及10053诊断事件的区别是什么?正确答案：(1)SQL_TRACE

SQL_TRACE是Oracle提供的用于进行SQL跟踪的手段，是强有力的辅助诊断工具。在日常的数据库问题诊断和解决中，SQL_TRACE是非常常用的方法。可以设置SQL_TRACE为TRUE，但是一般不推荐在全局指定为TRUE，只建议在会话级别指定。若在全局设定则对性能会造成较大影响。

(2)诊断事件

Oracle为数据库提供了多种的诊断工具，诊断事件(Event)是其中一种非常实用的方法，它能使DBA可以方便地转储数据库各种结构及跟踪特定事件的发生过程。通过如下的脚本可以获取所有的诊断事件列表：

在Linux系统下诊断事件的消息文件放在目录：$ORACLE_HOME/rdbms/mesg/oraus.rnsg。oraus.msg文件属于文本文件，可以直接打开查看。诊断事件可以像普通的ORA错误一样采用oerr命令来查询，如下：

设置诊断事件有两种方法，一种是在pfile参数文件中设置事件，这样数据库在OPEN后，将影响到所有的会话。设置格式如下：

通过英文冒号(:)符号，可以连续设置多个事件，也可以通过连续使用event来设置多个事件。如：

或者分开写，如：

另一种方法是在会话过程中使用ALTERSESSIONSETEVENTS命令，只对当前会话有影响。设置格式如下：

通过英文冒号(:)符号，可以连续设置多个事件，也可以通过连续使用ALTERSESSIONSETEVENTS来设置多个事件，如：

或：

格式说明：

1)eventnumber是指触发dump的事件号，事件号可以是Oracle错误号(出现相应错误时跟踪指定的事件)或Oralce内部事件号，内部事件号在10000～10999，不能与immediate关键字同用。

2)immediate关键字表示命令发出后，立即将指定的结构dump到跟踪文件中，这个关键字只用在ALTERSESSION语句中，并且不能与eventnumber、forever关键字同用。

3)tracename是关键词，tracename位于第二、三项，除它们外的其他限定词是供Oracle内部开发组用的。

4)eventname是指事件名称，即要进行dump的实际结构名。若eventname为context，则是指根据内部事件号进行跟踪。

5)forever关键字表示事件在实例或会话的周期内保持有效状态，不能与immediate同用。

6)level为事件级别关键字。但在dump错误栈(errorstack)时不存在级别。level通常位于1到10(10046有时用到12)，10意味着转储事件所有的信息。例如当转储控制文件时，level1表示转储控制文件头，而level10表明转储控制文件全部内容。

7)levelnumber表示事件级别号，一般从1～10，1表示只dump结构头部信息，10表示dump结构的所有信息。

8)转储所生成的trace文件在user_dump_dest初始化参数指定的位置。

9)移除所有的诊断事件(Event)：

如果设置了诊断事件(Event)，那么如何知道在系统中设置了哪些诊断事件(Event)呢?如果事件是在pfile文件中设置的，那么可以用“SHOWPARAMETEREVENT”来查看。如果是通过“ALTERSYSTEM|SESSION”来设置的，那么可以使用如下的SQL来查看：

但是，10046和10053事件不能通过这种方式查询，只能通过oradebug来查询，如下：

设置诊断事件需要注意的是，既可以使用“ALTERSYSTEMEVENTS”，也可以使用“ALTERSYSTEMEVENT”，它们的区别在如下两点：

1)EVENTS可以动态修改，可以使用“ALTERSESSION”或“ALTERSYSTEM”设置，只影响内存不影响参数文件。

2)EVENT不能动态修改，只能使用“ALTERSYSTEM”或在参数文件里设置，必须重启库方可生效。

另外，“ALTERSYSTEM”会记录到告警日志中，“ALTERSESSION”不会记录在告警日志中。

(3)10046和10053

最为常用的诊断事件是10046和10053事件。10046事件是Oracle提供的内部事件，是对SQL_TRACE的增强。Oracle的10046事件可以跟踪应用程序所执行的SQL语句，并且得到其解析次数、执行次数、CPU使用时间等信息。这对DBA来分析、定位数据库性能问题是非常有用的。

10046事件可分成不同的级别(LEVEL)，分别追踪记录不同程度的有用信息。对于这些不同的级别，应当注意的是向下兼容的，即高一级的TRACE信息包含低于此级的所有信息。

10046事件可以设置以下级别：

1)level0：禁用SQL_TRACE，等价于SQL_TRACE=FALSE。

2)level1：启用标准的SQL_TRACE功能跟踪SQL语句，包括解析、执行、提取、提交、回滚、响应时间、服务时间、处理的行数，物理读和写的数目、执行计划以及其他一些额外信息，等价于SQL_TRACE=TRUE。

3)level4：Level1+包括绑定变量(BindValues)的详细信息。

4)level8：Level1+包括等待事件。

5)level12：包括绑定变量与等待事件，包含Level1+Level4+Level8。

10053事件是最常用的Oracle优化器跟踪Trace。10053事件解析优化器为什么选择某个执行计划但并不记录这个执行计划到底运行地如何。10046事件并不解释优化器的工作，但它记录了在SQL解析阶段所遇到的等待事件和所消耗的CPU等资源，以及执行阶段的各项指标。需要注意的是，如果一个SQL语句已经被解析过，那么就不会生成10053的trace文件，但10046的trace文件可以重复生成。

简而言之，10046事件记录SQL如何运行，而10053记录优化器为什么为这个SQL选择某个执行计划。

(4)在Oracle中跟踪会话执行语句的几种方法

因为TRACE的目标范围不同，所以导致必须使用不同的方法。若作用于数据库全局的，则改初始化参数。若只作用于当前会话的，则用ALTERSESSION命令，若作用于其他会话的，则用DBMSSYSTEM包。

1)SQL_TRACE参数设置：非常传统的方法。SQLTRACE可以作为初始化参数在全局启用，也可以通过命令行方式在具体会话启用。在参数文件(PFILE/SPFILE)中指定：SQL_TRACE=TRUE或ALTERSYSTEMSETSQL_TRACE=TRUE;。

通过在全局启用SQL_TRACE可以跟踪到所有后台进程及所有用户进程的活动，通过跟踪文件的实时变化，可以清晰地看到各个进程之间的紧密协调。需要注意的是，在全局启用SQL_TRACE会产生大量trace文件，很容易耗尽磁盘空间，这通常会导致比较严重的性能问题，所以在生产环境中要谨慎使用，并且及时关闭。

在大多数时候使用SQL_TRACE跟踪的都是当前会话的进程。通过跟踪当前进程可以发现当前操作的后台数据库递归活动，这在研究数据库新特性时尤其有效，在研究SQL执行，发现后台错误等方面也非常有用。

在会话级启用和停止SQL_TRACE的方式如下：

启用当前SESSION的跟踪：

在很多时候需要跟踪其他用户的进程，而不是当前用户，这可以通过Oracle提供的系统包DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION来完成。

通过V$SESSION可以获得SID、SERIAL#等信息，获得进程信息，选择需要跟踪的进程：

设置跟踪SID为26的会话：

可以等候片刻，等待SID为26的会话执行SQL，完成后，停止跟踪：

2)使用10046事件。全局设定：参数文件中指定：EVENT="10046TRACENAMECONTEXTFOREVER,LEVEL12"

或者：

当前会话设定：

在Oracle11g中还可以对单个SQL打开10046事件跟踪，命令如下：

关闭单个SQL的跟踪命令如下：

若要针对每个新连接的会话生成10046跟踪文件，则可以使用登录触发器。如下的代码是跟踪LHR用户的信息，在用户LHR登录数据库系统时会启动10046跟踪。

3)DBMS_SESSION包：只能跟踪当前会话，不能指定会话。跟踪当前会话：

DBMS_SESSION.SET_SQL_TRACE相当于ALTERSESSIONSETSQL_TRACE，从生成的TRACE文件可以明确地看ALTERSESSIONSETSQL_TRACE语句。使用DBMS_SESSION.SESSION_TRACE_ENABLE过程，不仅可以看到等待事件信息还可以看到绑定变量信息，相当于“ALTERSESSIONSETEVENTS'10046TRACENAMECONTEXTFOREVER,LEVEL12';”语句：

4)DBMS_SYSTEM包。使用DBMS_SYSTEM.SET_EV设置10046事件：

5)DBMS_MONITOR包。该包是从Oracle10g开始提供的，功能非常强大。可在模块级别、动作级别、客户端级别、数据库级别和会话级别进行跟踪。

跟踪当前会话：

跟踪其他会话：

6)oradebug。这是SQL*Plus的工具，需要提供OSPID或者OraclePID。

跟踪当前会话：

跟踪其他会话：

或者：

使用oradebughelp可以查看oradebug的命令帮助。

3.

对于一个比较复杂的存储过程来说，如何快速定位到是什么语句什么原因导致的失败解析呢?正确答案：在解析SQL的过程中，若碰到SQL语法错误、访问的对象不存在或没有权限，则会导致目标SQL语句解析失败，所以，解析失败的SQL语句是不会生成执行计划的。可以通过如下几种方式找到解析失败的SQL：

1)通过关联X$KGLCURSOR和X$KGLCURSOR_CHILD_SQLID视图。

2)通过使用10035事件。

3)通过Oraclesystemdump。

Oracle提供了一系列的跟踪事件来帮助定位各种问题，通过10035事件可以诊断解析失败的情况，如下：

首先通过“altersystemsetevents'10035tracenamecontextforever,leve5';”开启监控，然后使用tail观察告警日志，找到问题SQL之后，使用命令“altersystemsetevents'10035tracenamecontextoff';”关闭10035事件。

4.

errorstack的作用是什么?正确答案：在使用Oracle数据库的过程中，可能会遇到各种各样的错误或异常，而且这些错误或异常的提示并不具体，那么这个时候就可以使用Oracle提供的一种对于错误堆栈进行跟踪的方法即errorstack。通过设置跟踪可以将一些错误的后台信息详尽地转储出来，写入跟踪文件，对于错误的研究与诊断非常有效。

errorstack主要有4个级别：

1)0仅转储错误堆栈(0级已经被逐渐废弃)。

2)1转储错误堆栈和函数调用堆栈。

3)2Level1+ProcessState。

4)3Level2+Contextarea(显示所有游标，着重显示当前游标)。

errorstack可以在实例级或会话级别设置，也可以在参数文件中设置，这个设置仅当某个特定的错误出现时才被触发，如设置ORA-01438事件的跟踪：

接下来在告警日志中找到相关的跟踪文件，对该文件进行分析即可。

5.

如何收集systemdump?如何收集hanganalyze?正确答案：若sysdba可登录，则使用命令“sqlplus"/assysdba"”登录系统。若不能登录，则使用“sqlplus-prelim"/assysdba"”登录系统，然后使用oradebug收集systemdump，如下：

若要获得某个进程的状态，则：

如果系统暂停运行的时候，则systemstate基本等同于hanganalyze，可以用于诊断SYSTEMHANG住的问题。Oracle数据库可能因为HANG住而产生严重的性能问题，通过hanganalyze功能产生的日志可以帮助DBA快速地诊断出数据库的问题，如下：

对于单实例而言，如下设置：

对于RAC环境，如下设置：

6.

什么是分布式事务处理?正确答案：分布式事务是指一个事务在本地和远程执行，本地需要等待确认远程的事务结束后，进行下一步本地的操作。如通过DBLINK更新远程数据库的一行记录，如果在执行过程中网络异常或者其他事件导致本地数据库无法得知远程数据库的执行情况，那么此时就会发生IN-DOUBT的报错。此时需要DBA介入，且需要分多种情况进行处理。

Oracle会自动处理分布式事务，保证分布式事务的一致性，所有站点全部提交或全部回滚。一般情况下，处理过程在很短的时间内完成，根本无法察觉到。但是，如果在COMMIT或ROLLBACK的时候，出现了连接中断或某个数据库站点Crash的情况，那么提交操作可能会无法继续，此时DBA_2PC_PENDING和DBA_2PC_NEIGHBORS中会包含尚未解决的分布事务。对于绝大多数情况，当恢复连接或Crash的数据库重新启动后，会自动解决分布式事务，不需要人工干预。只有分布事务锁住的对象急需被访问，锁住的回滚段阻止了其他事务的使用，网络故障或Crash的数据库的恢复需要很长的时间等情况出现时，才使用人工操作的方式来维护分布式事务。手工强制提交或回滚将失去二层提交的特性，Oracle无法继续保证事务的一致性，事务的一致性应由手工操作者保证。

使用“ALTERSYSTEMDISABLEDISTRIBUTEDRECOVERY;”可以使Oracle不再自动解决分布事务，即使网络恢复连接或者CRASH的数据库重新启动。使用“ALTERSYSTEMENABLEDISTRIBUTEDRECOVERY;”恢复自动解决分布事务。

有关分布式事务有两个重要的视图，分别是DBA_2PC_PENDING和DBA_2PC_NEIGHBO