Wednesday, September 28, 2011

Interesting Error

We just upgraded one of our 10.2.0.3 dbs to 11.2.0.2 plus some patches.  Today an interesting ORA-07445 was thrown to the alert.log:

ORA-07445: exception encountered: core dump [__intel_new_memcpy()+382] [SIGILL] [ADDR:0x3FE5CAE] [PC:0x3FE5CAE] [Illegal operand] []

Searching metalink didn't get us anywhere, so we opened a TAR (or iTar, or SR, or ServReq, or whatever the heck they are calling them these days).  While Oracle Support was looking at the issue, I looked at the query generating the error:

SELECT nvl(t.value, null) FROM table t WHERE t.id = 12345;

Huh?  If the value is null, substitute a null?  WTF?

Presto-changeo...

SELECT t.value FROM table t WHERE t.id = 12345;

...and the error goes away.

Is it a bug on Oracle's side? Sure.

Is it a stupid thing to do on our side? You betcha.

Thursday, September 15, 2011

Common Sense

Developer:
I am running this query:
DELETE FROM tab;
It's been running since 2PM yesterday.  Can you make it go faster?

DBA:
You shouldn't delete 80M rows in a single transaction.  You should have used TRUNCATE instead of DELETE.  I have killed your session and it will be a while before it completes.

Developer (5 minutes later):
I tried TRUNCATE but it says my table is locked.

DBA:
I know, the table will be locked up for a while while the transaction rolls back.

Developer:
Can you make it go faster?

DBA:
I have turned on _transmogrify_delete_queries on so that is the best I can do.

Developer:
Thanks!! That should definitely help!!!