Sometimes, advanced features can bring advanced problems. Seeing a project with hundreds of child cursors with a different execution plan each, is never a pleasure, but there has to be a reason. In fact, we are talking about the “USE_FEEDBACK_STATS” reason in v$sql_shared_cursor. Its meaning is: “A hard parse is forced so that the optimizer can reoptimize […]
Tag: cost based optimizer
How NLS settings can affect Oracle’s join behavior – v$ example
Recently, I had a problem with accessing Active Session History being not fast enough on Oracle Database 11.2 and 12.1. Looking at the explain plan, wow, no doubt why: Accessing two fixed tables with TABLE ACCESS FULL and joining them with NESTED LOOP. This couldn’t have been “works as designed”, it would render gv$active_session_history nearly […]
Oracle: How to create a good execution plan
Sometimes you simple need to know whether a statement is fast or not, or why it is slow. (Sadly, it seems like virtually nobody wants to know why a statement is fast, but that’s another story…) Do not use EXPLAIN PLAN in such cases, it does not (can not) deal bind variables right. I’ve got […]