Optimizer generating wrong execution plans!!

Hi Data Lovers,

Recently I came across a weird situation which was caused by the Oracle optimizer’s wrong selection of execution plan.

To explain clearly,  I got a complain from application teams saying they’re noticing a database error in their log files. It’s 10.2.0.4 database and that’s a simple TEMP space exhaustion message.

Then I identified the SQL which is causing the issue and asked them to run again and monitored closely.

It was a simple SQL statement with 2-3 nested joins on 5 tables and none of those tables contains more than 400 rows. But it’s consuming 17GB of temp space; Yes it’s SEVENTEEN absolutely 🙂

Then I tried analyzing different things and in the process of investigation i generated the execution plan too.

If I see the execution plan, i found all weird predicates. Why i said weird? Because i’m seeing column and table names which are not being used in any part of the original sql statement.

Then i observed the cost, number of rows processed etc…they were in millions. then i realized that there’s something wrong.

So i flushed the shared pool and issued the statement again; Eureka, it was working perfectly as expected. My expected execution plan and Oracle’s execution plan were almost similar now.

The query execution is finished in 20-30 seconds instead of 30 minutes duration.

Hopefully, Oracle’s optimizer associated a wrong execution plan with this statement’s hash value.

There’s a problem in flushing the shared pool actually as it may lead to hard parses with every statement but in our case we were allowed to do that as it was a non-production environment and number of queries fired against the database are very limited.

If you want to purge a selected plan, go through this https://blogs.oracle.com/mandalika/entry/oracle_rdbms_flushing_a_single

The main intention here is, if your queries started behaving weirdly all of a sudden, try to consider the fact that Oracle’s optimizer will be in hangover mood sometimes 😉

p.s. the query i used to determine the TEMP usage are

– Temp segment usage per session.–

SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;

Feel free to comment on / provide your feedback.

 

Have a good day

CSM.