Search Results for: cost

ora-00600[kkocxj:pjpCtx]内部错误一例

一套HP-UX上的10.2.0.4系统在运行某条 select查询语句时出现ORA-00600[kkocxj:pjpCtx]内部错误,TRACE文件信息如下:


FILE VERSION
------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/app/oracle/product/10.2
System name:    HP-UX
Node name:      crmdb1
Release:        B.11.31
Version:        U
Machine:        ia64
Instance name: cbssnm
Redo thread mounted by this instance: 1

TRACE FILE
---------------
Filename = cbssnm_ora_29061.trc

*** ACTION NAME:(SQL 窗口 - 新建) 2010-07-02 15:59:46.238
*** MODULE NAME:(PL/SQL Developer) 2010-07-02 15:59:46.238
*** SERVICE NAME:(SYS$USERS) 2010-07-02 15:59:46.238
*** SESSION ID:(770.4341) 2010-07-02 15:59:46.237
*** 2010-07-02 15:59:46.237
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kkocxj : pjpCtx], [], [], [], [], [], [], []
Current SQL statement for this session:
select p.access_number, aa.name
 from crm.product p,
      (select aa.prod_id, os.name, os.staff_number
         from (select *
                 from (select prod_id,
                              party_id,
                              row_number() over(partition by prod_id order by start_dt desc) num
                         from crm.party_2_prod
                        where end_dt > sysdate
                          and party_product_rela_role_cd = 3)
                where num = 1) aa,
              crm.our_staff os
        where aa.party_id = os.staff_id) aa
where p.prod_id = aa.prod_id(+)
  and p.access_number = '15335581126'
----- Call Stack Trace -----
    ksedst <- ksedmp <- ksfdmp <- kgerinv <- kgeasnmierr        
<- $cold_kkocxj <- kkoiqb <- kkooqb <- kkoqbc <- apakkoqb         
<- apaqbdDescendents <- apaqbd <- kkqctCostTransfQB <- kkqctdrvJP 
<- kkqjpdttr          <- kkqctdrvTD <- kkqjpddrv <- kkqdrv <- kkqctdrvIT 
<- apadrv           <- opitca <- kksFullTypeCheck <- rpiswu2 <- kksLoadChild 
<- kxsGetRuntimeLock            <- kksfbc <- kkspsc0 <- kksParseCursor 
<- opiosq0 <- kpooprx             <- kpoal8 <- opiodr <- ttcpip <- opitsk 
<- opiino              <- opiodr <- opidrv <- sou2o <- opimai_real <- main               
<- main_opd_entry

根据错误代码和stack trace可以在metalink上匹配到如下Bug:

Bug:7014646
Abstract: ORA-600: INTERNAL ERROR CODE, ARGUMENTS: [KKOCXJ : PJPCTX], [], [], [], [], []
Affects:
    Product (Component)	Oracle Server (Rdbms)
    Range of versions believed to be affected	Versions < 11.2
    Versions confirmed as being affected	
        * 10.2.0.4
        * 11.1.0.6 
    Platforms affected	Generic (all / most platforms affected)
Fixed:
    This issue is fixed in	
        * 10.2.0.4 Patch 7 on Windows Platforms
        * 10.2.0.5 (Server Patch Set)
        * 11.1.0.7 (Server Patch Set)
        * 11.2 (Future Release) 
Symptoms:
    * Internal Error May Occur (ORA-600)
    * ORA-600 [kkocxj : pjpCtx] 
Related To:
    * Optimizer
    * _OPTIMIZER_PUSH_PRED_COST_BASED 
Description
    A complex query can fail during parse with 
    ORA-600 [kkocxj : pjpCtx]
    Workaround
     Set  "_optimizer_push_pred_cost_based"=false

该bug可以通过实施one off Patch 7014646修复,也可以尝试通过修改隐式参数_optimizer_push_pred_cost_based禁用基于成本的谓词前置特性(WORKAROUND: disable cost based push predicate)来规避该[KKOCXJ:PJPCTX]内部错误发生,具体的修改方法:

SQL> conn / as sysdba
SQL> alter system set "_optimizer_push_pred_cost_based"=false;
SQL> exit
/* 设置该隐式参数无需重启实例 */

Oracle GCS更推荐通过应用补丁7014646的方法来解决问题,而修改以上隐式参数则不一定百分之百能解决问题。

Patch your 10g Oracle database to PSU 10.2.0.4.5

有人会问不是10.2.0.5已经release了吗?为什么还要装10.2.0.4版本的PSU(Patch set Update)呢?做出这样的决定很大程度上是处于无奈的,毕竟Oracle的Bug有那么多,百年大计安全第一;10.2.0.4的稳定性在10g的范畴内可说是有目共睹的,而多达5个版本的PSU也进一步保证了能在10.2.0.4基础上不做大的变更,只修复合并一些小的bug。继10.2.0.4.4后(10.2.0.4.4有CRS和DB 2个版本)在July 13.2010推出了10.2.0.4.5 PSU,该PSU只有1.3MB大小,合并了:9714832, 9713537, 9678695, 9655017, 9173248, 8309642, 9678697, 9678690,9573054, 9654991等多个patch;因为10.2.0.4.5 PSU需要在10.2.0.4.4的基础上才能apply,所以我们需要首先应用10.2.0.4.4 PSU补丁。

PSU 10.2.0.4.5 的安装步骤:

1.如果你是全新的fresh安装的话,需要安装数据库Server软件10.2.0.1,之后升级到10.2.0.4;此外因为是fresh installation,还不存在数据库,所以只要是以custom database形式创建的新库都可以skip Post installation的步骤(也就是说不用执行脚本),而以DBCA中General, Data Warehouse, Transaction Processing形式创建新库需要执行catbundle.sql升级脚本(Loading Modified SQL Files into the Database)。
2.安装Oracle Opatch软件版本11.2.0.0,对应的patch号为6880880,使用10.2.0.4默认的Opatch软件在prereq和apply阶段会遇到Bug,并出现以下问题:

Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" not executed
Unable to create Patch Object.
Exception occured : Patch ID is null.

Invoking prereq "checkconflictagainstohwithdetail"
The location ".//README.html" is not a directory or a valid patch zip file.
Prereq "checkConflictAgainstOHWithDetail" not executed
PrereqSession failed: Invalid patch location.

可以从MOS下载到p6880880_112000_$PLATFORM.zip的介质,解压后将OPatch部分的内容覆盖到原$ORACLE_HOME/OPatch目录:
[Read more...]

How to trouble shooting Library cache lock/pin

1.Troubleshooting Library Cache: Lock, Pin and Load Lock (Doc ID 444560.1)
What is "Library cache lock" ?

This event controls the concurrency between clients of the library cache. It acquires a lock on
the object handle so that either:

* One client can prevent other clients from accessing the same object.

* The client can maintain a dependency for a long time (for example, so that no other client can change the object).

This lock is also obtained to locate an object in the library cache.
Library cache lock will be obtained on database objects referenced during parsing or compiling of
SQL or PL/SQL statements (table, view, procedure, function, package, package body, trigger, index, cluster, synonym).
The lock will be released at the end of the parse or compilation.

Cursors (SQL and PL/SQL areas), pipes and any other transient objects do not use this lock.
Library cache lock is not deadlock sensitive and the operation is synchronous.

Parameters:

* handle address
Address of the object being loaded.

* lock address
Address of the load lock being used. This is not the same thing as a latch or an enqueue, it is a State Object.

* Mode
Indicates the data pieces of the object which need to be loaded.

* Namespace
The name of the object namespace as it is displayed in V$DB_OBJECT_CACHE view

How can Library cache lock be reduced?

In general , high contention on library cache lock is usually a result of an under-sized shared pool or
non-sharing of sql. Some ways of reducing the contention are:

* Reduce the reloads by increasing the shared pool size as the locks may take a long time if the pool is undersized.

* Increase sharing by setting the cursor_sharing to similar or force.
Be aware this may change the execution plan; so setting the parameter should be thoroughly tested.

* Reduce the invalidations by running the batch jobs to collect statistics or any other maintenance jobs
separately from OLTP.

Note 122793.1 How to Find which Session is Holding a Particular Library Cache Lock

Known Bugs:

Note:10018789.8Spin in kgllock / DB hang with high library cache lock waits
Note:7423411.8Process may hang waiting for "library cache load lock" with no holder
Note:7706138.8Process may hang waiting for "library cache load lock" with no holder
Note:9675816.8Bug 9675816 - Self deadlock with 'library cache lock' waits

2.How to Find which Session is Holding a Particular Library Cache Lock (Doc ID 122793.1)

Method 1: Systemstate Analysis

For older versions you can use the following syntax that is also possible in higher versions.The level 266 is
not available before 9.2.0.6

alter session set max_dump_file_size=unlimited;
alter session set events 'immediate trace name systemstate level 10'
Oracle will create a systemstate tracefile in your USER_DUMP_DEST directory.

METHOD 2: EXAMINE THE X$KGLLK TABLE
select sid,saddr from v$session where event= 'library cache lock';

SID SADDR
---------- --------
16 572ed244

select kgllkhdl Handle,kgllkreq Request, kglnaobj Object
from x$kgllk where kgllkses = '572ed244'
and kgllkreq > 0;

HANDLE REQUEST OBJECT
-------- ---------- ------------------------------------------------------------
62d064dc 2 EMPLOYEES

A:It's possible that one library cache lock can block all the other sessions if this table need to be
queried by other sessions.

The problem is why the library cache lock was held for so long and wasn't released.
Usually, library cache lock only cost a few milliseconds.

It could be these Known Bugs:

Note:10018789.8 Spin in kgllock / DB hang with high library cache lock waits
Note:7423411.8 Process may hang waiting for "library cache load lock" with no holder
Note:7706138.8 Process may hang waiting for "library cache load lock" with no holder
Note:9675816.8 Bug 9675816 - Self deadlock with 'library cache lock' waits

For more information, you can refer note 444560.1 and 122793.1.

To understand the root cause, we need more information to analyze.
You may take systemstate and hanganalyze next time and we'll help you to analyze them.

1. Please generate systemstate dump as sysdba:
SQL>conn / as sysdba;
SQL>alter session set max_dump_file_size = unlimited;
SQL>alter session set events 'immediate trace name systemstate level 10';
Wait for some some seconds
SQL>alter session set events 'immediate trace name systemstate level 10';
Wait for some some seconds
SQL>alter session set events 'immediate trace name systemstate level 10';

2. Open another session as sysdba:
SQL>conn / as sysdba;
SQL>oradebug setmypid
SQL>oradebug unlimit;
SQL>oradebug dump hanganalyze 3
Wait for some seconds
SQL>oradebug dump hanganalyze 3
Wait for some seconds
SQL>oradebug dump hanganalyze 3

3. The generated files will be under udump. Please upload these files.
$cd udump
$ls –ltr

No way to disable datapump estimate?

昨天晚上去客户现场配合一个导入导出作业;这是一套Solaris 10上的10.2.0.1系统,导出采用expdp数据泵工具,需要导出的数据源是一张大小在120G左右的单表,该表存放了最近一年的数据,实际导出时只需要最近三个月的数据,所以使用了QUERY参数,并指定了并行度为2。
该导出作业之前未经测试过,语句也是临时写的,实际执行导出工作时发现在评估阶段(estimate phase)耗费了大约十多分钟的时间,estimate默认使用blocks模式,即通过计算对象段占用的数据库块来预估dumpfile的大小;此外还有statistics模式通过对象的统计信息来推算导出文件的大小:

The block method of estimate is the least accurate. We investigate how many blocks were allocated for the storage segments for the tables specified. If the table had been recently analyzed, the estimate=statistics would be the most accurate. So remember, it is important to generate statistics before calculating the estimate using estimate=statistics method. Additionally, ESTIMATE=BLOCKS method could be inaccurate when:

a) The table was created with a much bigger initial extent size than was needed for the actual table data

b) Many rows have been deleted from the table, or a very small percentage of each block is used.

# When statistics have been explicitly set via the DBMS_STATS package in order to force certain optimizer plans, instead of being directly collected from the data, ESTIMATE=STATISTICS may not report correct results.

但实际上这2中方式在使用QUERY的情况下都无法正确预估导出文件的大小:

[maclean@rh2 udump]$ expdp maclean/maclean directory=dump  tables=estimate_me query=estimate_me:'"where t1<2000"'  estimate=statistics

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 02 November, 2010 21:28:26

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** directory=dump tables=estimate_me query=estimate_me:"where t1<2000" estimate=statistics
Estimate in progress using STATISTICS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "MACLEAN"."ESTIMATE_ME"                     104.7 MB
Total estimation using STATISTICS method: 104.7 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MACLEAN"."ESTIMATE_ME"                     32.42 KB    1999 rows
Master table "MACLEAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_01 is:
  /s01/dump/expdat.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_01" successfully completed at 21:28:29

/* statistics模式使用统计来判断导出文件大小,比较容易控制 */

begin
 dbms_stats.set_table_stats(ownname => 'MACLEAN',tabname => 'ESTIMATE_ME',numrows => 999999999999999,numblks => 99999999999999999999);
  end;
/

PL/SQL procedure successfully completed.

Estimate in progress using STATISTICS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "MACLEAN"."ESTIMATE_ME"                     10244 GB
Total estimation using STATISTICS method: 10244 GB

/* 再次导出时数据泵采用了"作假的"统计信息*/

[maclean@rh2 udump]$ expdp maclean/maclean directory=dump  tables=estimate_me query=estimate_me:'"where t1<2000"'  estimate=blocks

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 02 November, 2010 21:31:58

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** directory=dump tables=estimate_me query=estimate_me:"where t1<2000" estimate=blocks
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "MACLEAN"."ESTIMATE_ME"                       192 MB
Total estimation using BLOCKS method: 192 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MACLEAN"."ESTIMATE_ME"                     32.42 KB    1999 rows
Master table "MACLEAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_01 is:
  /s01/dump/expdat.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_01" successfully completed at 21:32:01

/* 可以看到以上BLOCKS method预估的dumpfile大小为192MB,而STATISTICS方式预估为104.7MB;
    然而实际的使用QUERY参数导出的1999行数据只占用32k的空间!
    estimate在这里摆了一个大乌龙
*/

查了一下metalink发现10.2.0.1上存在expdp在estimate评估阶段耗时很久的bug:

EXPDP Slow and Estimate Takes Long Time [ID 822283.1]
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.3
This problem can occur on any platform.
Symptoms
Data pump Export is very slow and long time is spent in Estimating the data size.
EXPDP is stuck for long at :

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** parfile=dpexp.par
Estimate in progress using BLOCKS method...

AWR report taken when EXPDP is running slow show the below query took long time to complete .

SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$),
XMLFORMAT.createFormat2('TABLE_DATA_T', '7')), 0 ,KU$.BASE_OBJ.NAME
,KU$.BASE_OBJ.OWNER_NAME ,'TABLE' ,to_char(KU$.BYTES_ALLOC) ,
to_char(KU$.ET_PARALLEL) ,KU$.FGAC ,KU$.NONSCOPED_REF ,KU$.XMLSCHEMACOLS
,KU$.NAME , KU$.NAME ,'TABLE_DATA' ,KU$.PART_NAME ,KU$.SCHEMA_OBJ.OWNER_NAME
,KU$.TS_NAME , KU$.TRIGFLAG
,decode(KU$.SCHEMA_OBJ.TYPE_NUM,2,decode(bitand(KU$.PROPERTY,8192),8192,
'NESTED TABLE','T'),19,'PARTITION',20,'PARTITION','SUBPARTITION')
,to_char(KU$.UNLOAD_METHOD)
FROM SYS.KU$_TABLE_DATA_VIEW KU$
WHERE NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0 AND NOT (BITAND
(KU$.BASE_OBJ.FLAGS,16)=16) AND KU$.BASE_OBJ.NAME IN
('PA_DRAFT_INVOICES_ALL') AND KU$.BASE_OBJ.OWNER_NAME IN ('PA') AND NOT
EXISTS (SELECT 1 FROM SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='TABLE' AND
A.NAME=KU$.BASE_OBJ.NAME AND A.SCHEMA=KU$.BASE_OBJ.OWNER_NAME) AND NOT EXISTS
(SELECT 1 FROM SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='SCHEMA' AND
A.NAME=KU$.BASE_OBJ.OWNER_NAME);

Cause
The issue here is with using RBO.
Data pump is not choosing Cost Based Optimizer.
This is because of the 'unpublished' bug 5929373 -DATA PUMP TAKES LONG TIME TO EXPORT DATA

This bug is closed as duplicate of another bug << 5464834 >>

Solution

   1. Upgrade to 10.2.0.4 . The bug is fixed in 10.2.0.4
      Or
   2. Apply the << patch 5464834 >> if available for your platform .

        * Login to metalink
        * Go to 'Patches and Updates'
        * Click on 'Simple Search'
        * Enter the patch number 5464834
        * Select your platform from the dropdown list .
        * Patch,if available , will be displayed .
        * Download and apply the patch .

该Bug可以通过升级版本解决,但远水解不了近火,最初想disable掉estimate功能;但查阅若干资料后发现目前似乎没有能够关掉estimate功能的办法,这个就是悲哀所在了!耐心等estimate阶段完成后,实际数据导出阶段倒也只花了十来分钟,就这一点看来datapump很有中国式的办事风格。

另外当指定的导出文件数小于指定的parallelism,且导出数据量较大时可能出现ORA-39095错误,会导致导出作业意外终止:

39095, 00000, "Dump file space has been exhausted: Unable to allocate %s bytes"
// *Cause:  The Export job ran out of dump file space before the job was
//          completed.
// *Action: Reattach to the job and add additional dump files to the job
//          restarting the job.

EXPDP generating ORA-39095 : " dump file space has been exhausted"
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.2 - Release: 10.2 to 11.2
Information in this document applies to any platform.
Symptoms
Checked for relevance on 10-22-2010

Errors while performing Datapump Export:

- when exporting a large number of tables then it completely stops
- the jobs may remain in the datapump export tables
- No trace generated at the time of the failure.
ERROR:
ORA - 39095 : " dump file space has been exhausted. Unable to allocate 4096 bytes".jobsystem.sys_export_full_02 stops due to fatal error.

Cause
Customer chose only limited number of files in "dumpfile" parameter with limited "filesize" , i.e.:

   full=Y
   directory=dpump_dir
   dumpfile=full_1.dmp,full_2.dmp,full_3.dmp,full_4.dmp
   filesize=3000000000

This means 4 dump files with filesize 3000000000 byte (2.79 G).

the dumpfile parameter is the issue here.

.
Solution
1- You can use the dynamic format (i.e dumpfile=full_%U.dmp) :

The 'wildcard' specification for the dump file can expand up to 99 files. If 99 files have been generated before the export has completed, it will again return the ORA-39095 error.

2- If this is yet not enough and more files are needed, a workaround would be to speficy a bigger 'filesize' parameter.

3- If this is inconvenient, another option is to use this syntax:

      dumpfile=fullexp%U.dmp, fullexp2_%U.dmp, fullexp3_%U.dmp

which can expand up to 3*99 files.

If encountering problems containing the dump in a single directory using this solution, you may prefer this syntax:

     dumpfile=dmpdir1:fullexp1_%U.dmp, dmpdir2:fullexp2_%U.dmp, dmpdir3:fullexp3_U.dmp

(assuming the 3 directory objects listed above had been already created first).

解决方法很简单就是去掉并行度,或者指定动态的导出文件名。
记以录之,回去补觉了!

  • The block method of estimate is the least accurate. We investigate how many blocks were allocated for the storage segments for the tables specified. If the table had been recently analyzed, the estimate=statistics would be the most accurate. So remember, it is important to generate statistics before calculating the estimate using estimate=statistics method. Additionally, ESTIMATE=BLOCKS method could be inaccurate when:a) The table was created with a much bigger initial extent size than was needed for the actual table data

    b) Many rows have been deleted from the table, or a very small percentage of each block is used.

  • When statistics have been explicitly set via the DBMS_STATS package in order to force certain optimizer plans, instead of being directly collected from the data, ESTIMATE=STATISTICS may not report correct results.

ORA-00600: internal error code, arguments: [15160]

一套windows上的11.1.0.7系统,用户在查询时出现ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], [], [], [], [], []错误如下:

Dump continued from file: c:\app\administrator\diag\rdbms\dbuat\dbuat\trace\dbuat_ora_544.trc
ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], [], [], [], [], []

========= Dump for incident 16131 (ORA 600 [15160]) ========

*** 2010-07-20 15:49:20.015
----- Current SQL Statement for this session (sql_id=3vx5wh2859qy4) -----
SELECT * FROM ACVW_ACDCBIRD
WHERE CUST_AC_NO = NVL(:B5 , CUST_AC_NO)
AND BRANCH_CODE = :B4
AND ACCOUNT_CLASS = NVL(:B3 , ACCOUNT_CLASS)
AND CUST_NO = NVL(:B2 , CUST_NO)
AND CCY = NVL(:B1 , CCY)

----- PL/SQL Call Stack -----
object line object
handle number name
4648BA6C 811 package body FCUAT.ACPKS_FCJ_ACDCBIRD
4648BA6C 895 package body FCUAT.ACPKS_FCJ_ACDCBIRD
4648BA6C 199 package body FCUAT.ACPKS_FCJ_ACDCBIRD
4648BA6C 122 package body FCUAT.ACPKS_FCJ_ACDCBIRD
4648E458 13 anonymous block
4648F520 161 package body FCUAT.STPKS_FIDPKG_WRAPPER
464953BC 1940 package body FCUAT.STPKS_FCMAINT_SERVICE
464B8DE0 164 package body FCUAT.GWPKS_SERVICE_ROUTER
464B8DE0 1549 package body FCUAT.GWPKS_SERVICE_ROUTER
464B8DE0 1718 package body FCUAT.GWPKS_SERVICE_ROUTER
464B96E0 1 anonymous block

----- Call Stack Trace -----

PGOSF60__ksfdmp dbgexPhaseII dbgexProcessError dbgeExecuteForError

该ORA-600[15160]内部错误已知可能由以下Bug引起:

1)
Bug:8295719 - ORA-600 [15160] running query with FULL OUTER join Fixed-Releases: B201 Tags: ANSI CBO OERI REDISCOVERY INFORMATION:
If a query with nested views and Full Outer Join fails during parsing in Oracle11g with ORA-600 [15160] and Join Predicate Push Down (JPPD) is taking place and one of the nested view query blocks in the 10053 trace has a very high value for cost in the chosen plan (in section CBQT Join Predicate Push Down Additional Phase) then you may have encountered this bug.
WORKAROUND:
_optimizer_push_pred_cost_based = false

2)
Bug 7370515 - ORA-600[15160]: INTERNAL ERROR CODE
REDISCOVERY INFORMATION:
If you encountered ORA-600[15160] error when UNION/UNION ALL view is on the right side of null aware anti join, it could be this bug.
WORKAROUND:
Set "_optimizer_null_aware_antijoin"=false

3)
Bug 9826143 - UNION QUERY WITH OUTER JOIN AND GROUP BY CLAUSE FAILS WITH ORA-600 [15160] (36)
>>>>>>>>>>
Bug 9213751 - SQL GETS ORA-600 [15160] WITH _OPTIMIZER_PUSH_PRED_COST_BASED=TRUE (11)
From trace: [_optimizer_push_pred_cost_based = true]

4)
Bug 8947490 - ORA-600[15160] OCCURS WHEN USING SQL TUNING ADVISOR
REDISCOVERY INFORMATION:
a query with full outer joined union view produced ora-[15160] most likely during sqltune /index advisor.

可以从bug描述中了解到这是一个优化器相关的bug,一般与优化器相关的bug都可以通过关闭优化器的部分特性来workaround。针对ORA-600[15160]内部错误,我们可以尝试设置以下2个隐藏参数以绕过问题:

1) SQl>
Alter session set "_optimizer_null_aware_antijoin"=false;

/* to disable null aware anti join */

2) SQl>
Alter session set "_OPTIMIZER_PUSH_PRED_COST_BASED" =false ;

/*WORKAROUND: disable cost based push predicate */

以上多个bug据Oracle官方说法均已在11.2.0.1版本中修复了。

11g Release 2 enhanced Tablespace Point In Time Recovery

11g release 2中引入了针对被dropped掉的表空间的表空间时间点恢复,这是一种十分有用的新特性。TSPITR(TablesSpace Point In Time Recovery)在10g中就能做到自动创建辅助实例以恢复表空间到某个时间点,但在10g中是无法恢复一个已经被drop掉的表空间的。如同10g中一样11g仍旧可以利用全自动的TSPITR恢复被drop的表空间;Oracle会自动创建并启动辅助实例,且仅仅还原那些恢复所需的控制文件,system,sysaux,undo表空间及目标表空间,这些工作都将在用户指定的辅助目的地’Auxiliary Destination’中完成;之后Oracle将进一步使用辅助实例recover目标表空间到指定的时间点,并将其中的数据以Data Pump传输表空间的形式倒回到原数据库当中。
接下来我们要具体测试这一新特性,我们会创建一个示例表空间并在该表空间上产生少量数据,之后我们将对数据库进行备份,drop目标示例表空间,并在RMAN中使用TSPITR的方式将已经被drop掉的表空间恢复回来。在正式drop表空间前我们当然需要留意时间点或者当时的scn号,以保证正常恢复,同时在测试时使用recovery catalog恢复目录,虽然我们同样可以不用。
[Read more...]

ORA-00600: [7005], [192]内部错误一例

一套AIX上的9.2.0.6系统,应用的某条查询语句执行时频繁报ORA-00600:[7005]错误,alert告警日志内容如下:

Errors in file /oracle/admin/BIDW/udump/bidw2_ora_3252288.trc:
ORA-00600: internal error code, arguments: [7005], [192], [], [], [], [], [], []
Mon Dec  7 15:20:27 2009
Errors in file /oracle/admin/BIDW/udump/bidw2_ora_3252288.trc:
ORA-00600: internal error code, arguments: [7005], [192], [], [], [], [], [], []
Mon Dec  7 15:20:27 2009
Trace dumping is performing id=[cdmp_20091207152027]
Mon Dec  7 15:20:28 2009
Thread 2 advanced to log sequence 909143
 Current log# 7 seq# 909143 mem# 0: /oradata2/bidw/BIDW/redo2_3
...............
Mon Dec  7 15:21:10 2009
Errors in file /oracle/admin/BIDW/udump/bidw2_ora_3600486.trc:
ORA-00600: internal error code, arguments: [7005], [192], [], [], [], [], [], []
Mon Dec  7 15:21:11 2009
Errors in file /oracle/admin/BIDW/udump/bidw2_ora_3600486.trc:
ORA-00600: internal error code, arguments: [7005], [192], [], [], [], [], [], []

[Read more...]

从视图查询表分区的相关信息

分区表的分区键和分区类型都可以通过获取创建该分区表的DDL定义语句来了解,那么可不可以直接查询字典视图来获取这些信息呢?常用的dba_tab_partitions视图并没有包含我们想要的分区属性信息;这里我们可以用到dba_part_key_columns(describes the partitioning key columns for all partitioned objects in the database. Its columns are the same as those in ALL_PART_KEY_COLUMNS)和dba_part_tables(displays the object-level partitioning information for all partitioned tables in the database. Its columns are the same as those in ALL_PART_TABLES)这2个视图:
[Read more...]