RE: Stored outline not being used all the times...
- From: fairlie rego <fairlie_r@xxxxxxxxx>
- To: binhpham15@xxxxxxxxxxx, Rich.Jesse@xxxxxx
- Date: Thu, 7 Dec 2006 15:47:45 -0800 (PST)
The row with all 'N' should be the first child as mentioned here
All subsequent children should have some mismatch.
-Fairlie
Binh Pham <binhpham15@xxxxxxxxxxx> wrote:
I've seen those before, tried to understand why but no clue. Any clue?
---------------------------------
From: "Jesse, Rich" <Rich.Jesse@xxxxxx>
Reply-To: Rich.Jesse@xxxxxx
To: <oracle-l@xxxxxxxxxxxxx>
Subject: RE: Stored outline not being used all the times...
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
---------------------------------
All-in-one security and maintenance for your PC. Get a free 90-day trial! --
//www.freelists.org/webpage/oracle-l
Fairlie Rego
Senior Oracle Consultant
http://www.linkedin.com/in/fairlierego
http://el-caro.blogspot.com/
M: +61 402 792 405
---------------------------------
Any questions? Get answers on any topic at Yahoo! Answers. Try it now.
Other related posts: