Re: Anyone know when the predicate columns of DBA_HIST_SQL_PLAN are planne

From: Yong Huang <>
Date: Fri, 2 May 2008 10:34:43 -0700 (PDT)
Message-ID: <>

I looked at the SQL that populates wrh$_sql_plan, the underlined table. It is:

SQL> select sql_text from v$sqltext where sql_id = 'fz1wxs0nw77cg' order by piece;


INSERT INTO wrh$_sql_plan sp (... ...
s, filter_predicates, projection, time, qblock_name, re marks, timestamp, other_xml) SELECT /*+ ordered use_nl(p) ...
..., NUL
L access_predicates,           NULL filter_predicates,
 NULL projection,           p.time,           p.qblock_name,

So it always skips these two columns. I think if there was a way to enable it, it must be done by dbms_workload_repository.modify_snapshot_settings and record it in dba_hist_wr_control. But currently there's no such knob to turn.

At least in, querying access_predicate and filter_predicates columns of v$sql_plan during very high load period will cause ORA-600 [504]. But I think it should be fixed in 10g. Maybe the AWR team was not notified?

Yong Huang

On 4/30/08, Allen, Brandon <> wrote:
> Good day, list,
> With all the great work going on with the optimizer and features in 11g, I
> am very curious when the predicate columns in the AWR will be populated.
> Obviously, someone had the foresight to think about them, but it would be
> nice to get a sneak peek with the "powers that be" on the plans for these
> potential features. I looked in,, and,
> and in all cases the predicate columns (ACCESS_PREDICATES,
> FILTER_PREDICATES) of DBA_HIST_SQL_PLAN are all null. Perhaps it is just me.
> --
> Charles Schultz

Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now.;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
Received on Fri May 02 2008 - 12:34:43 CDT

Original text of this message