An example of ORA-07445 troubleshooting
The tester reported an error when running SQL statements on a test database (the specific SQL statement is not mentioned here. In short, it is a complicated SQL statement with more than rows .), The error is:
ORA-03113: end-of-file on communication channel
At the beginning, I suspected that SQL is too complicated and the database is overwhelmed. Our testing database machines are not very good, and many of them are virtual machines, in addition, it is the kind of physical machine that runs more than a dozen virtual machines... Therefore, there is no reason not to doubt the possibility of database disconnection.
Check the connection to the server. The database runs well and the listening is normal. No error is reported. Okay, check the alert log. An error is returned:
ORA-07445: exception encountered: core dump [kkqudhus () + 756] [SIGSEGV] [Address not mapped to object] [0x000000068] [] []
There are many causes for 7445 errors, such as bad blocks, memory errors, and Oracle bugs. And there is a high possibility of bugs. Oracle also provides ORA-600/ORA-7445/ORA-700 Error Look-up Tool, this is a good Tool, enter the first Error code, the system will help you find the relevant documentation:
Sure enough, the error is caused by Oracle BUG (BUG: 4664788). Oracle provides two solutions:
1. Set the hidden parameter _ optimizer_cbqt_no_size_restriction to false.
2. Apply patches.
Because the tester said it was a little urgent and only a Test Library, the first method to modify the hidden parameters was used here. View information about the currently hidden parameters:
SQL> select x. ksppinm name,
Y. ksppstvl value,
Y. ksppstdf isdefault,
Decode (bitand (y. ksppstvf, 7 ),
1,
\ 'Modified \',
4,
\ 'System _ MOD \',
\ 'False \ ') ismod,
Decode (bitand (y. ksppstvf, 2), 2, \ 'true \ ', \ 'false \') isadj
From sys. x $ ksppi x, sys. x $ ksppcv y
Where x. inst_id = userenv (\ 'instance \')
And y. inst_id = userenv (\ 'instance \')
And x. indx = y. indx
And x. ksppinm like \ '% _ optimizer_cbqt_no_size_restriction % \'
Order by translate (x. ksppinm ,\'_\',\'\');
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
ISDEFAULT ISMOD ISADJ
------------------------
_ Optimizer_cbqt_no_size_restriction
TRUE
TRUE FALSE
Modify hidden parameters:
SQL> alter system set \ "_ optimizer_cbqt_no_size_restriction \" = false scope = both;
System altered.
Let the tester re-run the SQL statement, and this time it is executed normally. Now the problem is solved.