Re: Anyone know when the predicate columns of DBA_HIST_SQL_PLAN are planne
Date: Fri, 2 May 2008 10:34:43 -0700 (PDT)
Message-ID: <672091.73836.qm@web80604.mail.mud.yahoo.com>
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;
SQL_TEXT
INSERT INTO wrh$_sql_plan sp (... ...
...access_predicate
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 9.2.0.5, 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 <Brandon.Allen_at_oneneck.com> 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 10.2.0.2, 10.2.0.3, 10.2.0.4 and 11.1.0.6,
> 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. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
-- http://www.freelists.org/webpage/oracle-lReceived on Fri May 02 2008 - 12:34:43 CDT