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: Execution Plan
Oracle SQL: Optimizing a WHERE predicate inequality with a Function Based Index
Intro Recently, on Oracle 11.2.0.3, I saw a join of three tables, using three times TABLE ACCESS FULL and HASH JOIN, without an obvious reason. During separating and understanding its components, the following scenario turned out to be the culprit. select * from CARDTEST where OBJECT_ID<DATA_OBJECT_ID; The optimizer is unable to calculate the cardinality of […]
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 […]
DOAG Konferenz 2009: Impressions Day 2
As promised, today my impressions of yesterday (confusing, I know). Clarification: This is about Wednesday, November 18th. I’m still at DOAG Conference 2009 in Nuremberg. Wednesday was a silent day for my interests, especially at noon there have been less talks being of interest, but maybe they have done something for my general knowledge… So […]
Oracle: Recreate PLAN_TABLE
drop table plan_table; @?/rdbms/admin/utlxplan.sql […]
Oracle: Tracing of another session
I simply love 10046 level 12 traces. I described session tracing based on a logon trigger quite earlier, but some situations in real DBA life need this trace event switched on for a session other than my own AND for a short peroid in time (with no need for the connected user to log out). […]