Oracle Support – Grrr

Is this an appropriate response?

Others have raised this before – in fact I think I’ve raised the question before – but my initial search on Oracle Support did not turn up an answer so I thought I would raise a question again.

Problem Summary
Question only: Null predicates in DBA_HIST_SQL_PLAN

Problem Description
Everyone knows that the FILTER_PREDICATES and ACCESS_PREDICATES columns in DBA_HIST_SQL_PLAN are not populated.
If I remember correctly, this was originally a bug in 9i and statspack and has been carried forward into AWR.
As a result, any execution plan fetched using DBMS_XPLAN.DISPLAY_AWR has no predicate information
The statement which populates the underlying data has hardcoded NULLS.
Why do these continue to be null through all versions?
Is there any plan to address this?

I even provided the AWR statement with the hardcoded NULLS:

INSERT INTO wrh$_sql_plan sp (snap_id, dbid, sql_id, plan_hash_value, id, operation, options, object_node, object#, object_owner, object_name, object_alias, object_type, optimizer, parent_id, depth, position, search_columns, cost, cardinality, bytes, other_tag, partition_start, partition_stop, partition_id, other, distribution, cpu_cost, io_cost, temp_space, access_predicates, filter_predicates, projection, time, qblock_name, remarks, timestamp, other_xml) SELECT /*+ ordered use_nl(p) PARAM(‘_flush_plan_in_awr_sql’, 1) PARAM(‘_cursor_plan_unparse_enabled’, ‘FALSE’) */ :lah_snap_id lsnid, :dbid dbid, p.sql_id, p.plan_hash_value,, p.operation, p.options, p.object_node, p.object#, p.object_owner, p.object_name, p.object_alias, p.object_type, p.optimizer, p.parent_id, p.depth, p.position, p.search_columns, p.cost, p.cardinality, p.bytes, p.other_tag, p.partition_start, p.partition_stop, p.partition_id, p.other, p.distribution, p.cpu_cost, p.io_cost, p.temp_space, NULL access_predicates, NULL filter_predicates, NULL projection, p.time, p.qblock_name, p.remarks, p.timestamp, p.other_xml FROM x$kewrattrnew atn, x$kewrtsqlplan spn, v$sql_plan p WHERE atn.str1_kewrattr = spn.sqlid_kewrspe AND atn.num1_kewrattr = spn.planhash_kewrspe AND spn.sqlid_kewrspe = p.sql_id AND spn.planhash_kewrspe = p.plan_hash_value AND spn.childaddr_kewrspe = p.child_address


Please provide 10046 showing the issue

blah, blah, blah
instructions for doing a trace
blah, blah, blah

Might as well have been an automated response or “did you try turning it off and on again?”


7 Responses to Oracle Support – Grrr

  1. the usual, i wouldn’t have expected something else from them.
    i always keep insisting in those cases, it could need up to two or three more rounds until you get a reasonable answer, i would estimate for the kind of question

  2. Perhaps replying to the SR with “WTF! Can you read?” might help, 🙂

  3. I also talked about this bug a couples times before. We can see that Bug 7493519 : “ACCESS_PREDICATES AND FILTER_PREDICATES MISSING IN DBA_SQL_PLAN_HIST” is still open(since 2008) with severity=2 and latest update was 20-Feb-2014 (Also we can find that Bug 5217053 clased as “Not a bug”)
    So it seems that the bug will not be fixed soon.

  4. daryl says:

    Nice grr.. I have plenty of those moments. Currently I am running a little test.. i created an SR 3 months ago and the tech responded back (automated?) .. thanks we are researching it. Its been 3 months now with no response.. Thats some awesome research!

    • Dom Brooks says:

      I think we’re all used to a certain level but to me this was a new level of complete irrelevance.

      Still, at least the ticket servicing metrics will show that an initial response to customer opening SR was completed within one hour…

  5. savvinov says:

    Hi Dom,

    I feel your pain. I have my own collection of witty responses from Oracle support. My personal favorite was the one to an ORA-1400 bug SR (Oracle complained about NULL being inserted in a non-nullable column, but in fact it wasn’t a NULL). After several weeks of exchanges, and diagnostics, trace files, error stack traces and what not: “don’t insert NULL into a NON NULL column or make it nullable”. Eventually (after escalating that SR and a few nasty conversations with them) they did find a bug — it was in their bug databas all along!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: