RE: Stored outline not being used all the times...
- From: "Jesse, Rich" <Rich.Jesse@xxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 7 Dec 2006 16:22:25 -0600
The real fun begins when rows in there have multiple entries for the
same address, but all the columns are "N".
Oh, wait, that's the fun I'm having right now...
Good timing on the thread!
Rich
p.s. I used this quick'n'dirty query in 10.2.0.2.0, which was easy to
whip out via Toad:
select *
from
(
SELECT address,
unbound_cursor|| sql_type_mismatch||
optimizer_mismatch|| outline_mismatch|| stats_row_mismatch||
literal_mismatch|| sec_depth_mismatch|| explain_plan_cursor||
buffered_dml_mismatch|| pdml_env_mismatch|| inst_drtld_mismatch||
slave_qc_mismatch|| typecheck_mismatch|| auth_check_mismatch||
bind_mismatch|| describe_mismatch|| language_mismatch||
translation_mismatch|| row_level_sec_mismatch|| insuff_privs||
insuff_privs_rem|| remote_trans_mismatch||
logminer_session_mismatch||
incomp_ltrl_mismatch|| overlap_time_mismatch||
sql_redirect_mismatch||
mv_query_gen_mismatch|| user_bind_peek_mismatch||
typchk_dep_mismatch||
no_trigger_mismatch|| flashback_cursor|| anydata_transformation||
incomplete_cursor|| top_level_rpi_cursor|| different_long_length||
logical_standby_apply|| diff_call_durn|| bind_uacs_diff||
plsql_cmp_switchs_diff|| cursor_parts_mismatch||
stb_object_mismatch||
row_ship_mismatch|| pq_slave_mismatch|| top_level_ddl_mismatch||
multi_px_mismatch|| bind_peeked_pq_mismatch|| mv_rewrite_mismatch||
roll_invalid_mismatch|| optimizer_mode_mismatch|| px_mismatch||
mv_staleobj_mismatch|| flashback_table_mismatch||
litrep_comp_mismatch "FLAGS",
count(*)
FROM v$sql_shared_cursor
group by
address,
unbound_cursor|| sql_type_mismatch||
optimizer_mismatch|| outline_mismatch|| stats_row_mismatch||
literal_mismatch|| sec_depth_mismatch|| explain_plan_cursor||
buffered_dml_mismatch|| pdml_env_mismatch|| inst_drtld_mismatch||
slave_qc_mismatch|| typecheck_mismatch|| auth_check_mismatch||
bind_mismatch|| describe_mismatch|| language_mismatch||
translation_mismatch|| row_level_sec_mismatch|| insuff_privs||
insuff_privs_rem|| remote_trans_mismatch||
logminer_session_mismatch||
incomp_ltrl_mismatch|| overlap_time_mismatch||
sql_redirect_mismatch||
mv_query_gen_mismatch|| user_bind_peek_mismatch||
typchk_dep_mismatch||
no_trigger_mismatch|| flashback_cursor|| anydata_transformation||
incomplete_cursor|| top_level_rpi_cursor|| different_long_length||
logical_standby_apply|| diff_call_durn|| bind_uacs_diff||
plsql_cmp_switchs_diff|| cursor_parts_mismatch||
stb_object_mismatch||
row_ship_mismatch|| pq_slave_mismatch|| top_level_ddl_mismatch||
multi_px_mismatch|| bind_peeked_pq_mismatch|| mv_rewrite_mismatch||
roll_invalid_mismatch|| optimizer_mode_mismatch|| px_mismatch||
mv_staleobj_mismatch|| flashback_table_mismatch||
litrep_comp_mismatch
having count(*) > 1
order by count(*) desc
)
where flags = 'NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN';
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Charles Schultz
Sent: Thursday, December 07, 2006 1:05 PM
To: binhpham15@xxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Stored outline not being used all the times...
What does v$SQL_SHARED_CURSOR show? Also remember that outlines will
follow cursor sharing rules; if you expect SIMILAR cursors to be shared,
the outline must be created with cursor_sharing = SIMILAR. If the
queries are exactly the same and still not being shared, hopefully the
view will help identify the reason.
On 12/6/06, Binh Pham <binhpham15@xxxxxxxxxxx> wrote:
I have a stored outline that has been indicated as being used (
in v$sql, column outline_category = PROD). However, in V$SQL_PLAN it
shows a different plan than in the stored outline and also when I do
explain of the SQL.
We have the "alter system set use_stored_outlines = PROD" in our
database startup trigger. We've also verified that other outlines are
being used.
Why was this one not using the stored ouline?
I've noticed that in V$SQLAREA, this particular SQL has 5
invalidations and 6 loads. Even with the multiple invalidations and
loads, Oracle should have used the outline.
Any idea?
Thanks.
Charles Schultz
Other related posts:
