ORA-00600: 內部錯誤程式碼, 引數: [19004]

ORA-00600: 內部錯誤程式碼, 引數: [19004]

資料庫版本：Oracle 11.2.0.1.0

資料庫伺服器作業系統：Windows server 2008

問題現象：業務人員在前臺執行某些查詢操作時，報錯ORA-00600: 內部錯誤程式碼, 引數: [19004]；

解決方案：檢視alert警告日誌，查詢對應的trace檔案，找到觸發此bug的sql語句如下：

----- Current SQL Statement for this session (sql_id=.......) -----

SELECT *

FROM TAB_1

WHERE COL_1 in (select id from TAB_2)

and COL_2 in (:1, :2)

and COL_3 in

('null', 'null', 'null', 'null', 'null', 'null', 'null')

order by COL_4,COL_5;

根據MOS檢視，此錯誤匹配多個BUG，都是由於表錯誤的統計資訊和列的統計直方圖觸發此BUG，可以刪除SQL中對應表的統計資訊和統計直方圖臨時解決此問題，或者直接升級資料庫跳過此BUG；

刪除統計資訊方法如下：

---ANALYZE TABLE TAB_1 DELETE STATISTICS;

---ANALYZE TABLE TAB_2 DELETE STATISTICS;

SQL> execute dbms_stats.delete_table_stats(ownname=>'USER_NAME',tabname=>'TAB_1');

SQL> execute dbms_stats.delete_table_stats(ownname=>'USER_NAME',tabname=>'TAB_2');

SQL> execute dbms_stats.lock_table_stats(ownname=>'USER_NAME',tabname=>'TAB_1');

SQL> execute dbms_stats.lock_table_stats(ownname=>'USER_NAME',tabname=>'TAB_2');

---SQL> execute dbms_stats.unlock_table_stats(ownname=>'USER_NAME',tabname=>'TAB_1');

---SQL> execute dbms_stats.unlock_table_stats(ownname=>'USER_NAME',tabname=>'TAB_2');

刪除表統計資訊可能會生成錯誤的執行計劃，影響SQL執行效率；

可以適當調大動態取樣級別；

SQL> show parameter optimizer_dynamic_sampling

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

optimizer_dynamic_sampling integer 4

其中MOS中查詢有關ORA-00600[19004]問題相關資訊；

ORA-600/ORA-7445/ORA-700 Error Look-up Tool (文件 ID 153788.1)

ORA-600 [19004] (文件 ID 138652.1)

Note: For additional ORA-600 related information please read Note:146580.1

PURPOSE:

This article represents a partially published OERI note.

It has been published because the ORA-600 error has been

reported in at least one confirmed bug.

Therefore, the SUGGESTIONS section of this article may help

in terms of identifying the cause of the error.

This specific ORA-600 error may be considered for full publication

at a later date. If/when fully published, additional information

will be available here on the nature of this error.

SUGGESTIONS:

If the Known Issues section below does not help in terms of identifying

a solution, please submit the trace files and alert.log to Oracle

Support Services for further analysis.

Known Issues:

You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:

The list below is restricted to show only bugs believed to affect version 11.2.0.1.
Other bugs may affect this version but have not been confirmed as being relevant yet.

There are 6 bugs listed.

NB	Prob	Bug	Fixed	Description
	II	17314888	12.1.0.2, 12.2.0.0	ORA-600 [19004] when stats have been gathered with NLS_SORT / NLS_COMP not default BINARY
	II	13607472	11.2.0.4, 12.1.0.1	Manually setting column stats cannot exceed 4Gb max size
	III	16698971	12.1.0.2, 12.2.0.0	ORA-600 [19004] or inaccuracies on join of histogrammed columns
	III	10627631	11.2.0.3, 12.1.0.1	Allow optimizer to bypass corrupt histogram data
	III	9022470	11.2.0.2, 12.1.0.1	Query optimization fails with OERI[19004]
E	II	7284269	11.2.0.2, 12.1.0.1	Enh: allow extended and adaptive cursor sharing to support LIKE predicates

· '*' indicates that an alert exists for that issue.

· '+' indicates a particularly notable issue / bug.

· See Note:1944526.1 for details of other symbols used

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2104830/，如需轉載，請註明出處，否則將追究法律責任。

ORA-00600: 內部錯誤程式碼, 引數: [19004]

相關文章