1.先贴报错
----敏感信息已经经过处理
Fri Sep 30 11:01:37 2016
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Fri Sep 30 11:01:50 2016
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x7FFF82C72FF8] [PC:0x9541BA4, kkorminl()+306] [flags: 0x0, count: 1]
Errors in file /home/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_j000_78729.trc (incident=48167):
ORA-07445: exception encountered: core dump [kkorminl()+306] [SIGSEGV] [ADDR:0x7FFF82C72FF8] [PC:0x9541BA4] [Address not mapped to object] []
Incident details in: /home/app/oracle/diag/rdbms/orcl11g/orcl11g/incident/incdir_48167/orcl11g_j000_78729_i48167.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Sep 30 11:01:54 2016
Dumping diagnostic data in directory=[cdmp_20160930110154], requested by (instance=1, osid=78729 (J000)), summary=[incident=48167].
Fri Sep 30 11:01:55 2016
Sweep [inc][48167]: completed
Sweep [inc2][48167]: completed
2.trc内容如下
*** 2016-09-30 11:01:50.441
*** SESSION ID:(618.281) 2016-09-30 11:01:50.441
*** CLIENT ID:() 2016-09-30 11:01:50.441
*** SERVICE NAME:(SYS$USERS) 2016-09-30 11:01:50.441
*** MODULE NAME:(DBMS_SCHEDULER) 2016-09-30 11:01:50.441
*** ACTION NAME:(ADV_SYS_AUTO_SQL_TUNING_TASK) 2016-09-30 11:01:50.441
Dump continued from file: /home/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_j000_78729.trc
ORA-07445: exception encountered: core dump [kkorminl()+306] [SIGSEGV] [ADDR:0x7FFF82C72FF8] [PC:0x9541BA4] [Addre
ss not mapped to object] []
*** 2016-09-30 11:01:50.444
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=3syf3779v9ry8) -----
/* SQL Analyze(618,1) */ SELECT * FROM (SELECT * FROM T_.......... ROWNUM < 11
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
3.分析报错日志
----分析报错日志，可能是sql_id=3syf3779v9ry8 的语句导致的报错
----通过sql_id找该语句，手动执行
select * from v$sql where sql_id='bdnfsta01tv1j';
----执行正常，并没有报错
----然后使用STA手动调优测试
set serveroutput on
set pagesize 200;
set linesize 300;
set long 10000;
set longchunksize 10000;
var l_tuning_task varchar2(100);
exec :l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => 'bdnfsta01tv1j');
exec dbms_sqltune.execute_tuning_task(:l_tuning_task);
select dbms_sqltune.report_tuning_task(:l_tuning_task) from dual;
----在执行到exec dbms_sqltune.execute_tuning_task(:l_tuning_task);这里的时候，当前会话直接中断了，会话报错如下：
SQL> exec dbms_sqltune.execute_tuning_task(:l_tuning_task);
ERROR:
ORA-03114: not connected to ORACLE
BEGIN dbms_sqltune.execute_tuning_task(:l_tuning_task); END;
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 78142
Session ID: 773 Serial number: 95
----此时查看alert告警日志，发现报错内容一样。
----sql语句本身没有问题，但是执行调优分析就报错，怀疑是bug。
4.处理方案
1.对出错的sql进行处理，修改。
2.停止sql自动优化任务。
----关闭STA自动调优
BEGIN
dbms_auto_task_admin.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
----开启STA自动调优
BEGIN
dbms_auto_task_admin.enable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
reference http://www.itpub.net/thread-1871140-1-1.html