RE: Stored outline not being used all the times...
- From: "Jesse, Rich" <Rich.Jesse@xxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 7 Dec 2006 19:40:06 -0600
Hi Fairle,
The key word here is "should". I agree, but if you look at my query,
you'll see that it's returning multiple rows with all "N"s for the same
address. At least it is for me on 10.2.0.2.0.
My 1 year old kept me up until 2:00 AM last night, so I could be wrong.
I'll try and hammer away at it some more tomorrow.
Thanks!
Rich
________________________________
From: fairlie rego [mailto:fairlie_r@xxxxxxxxx]
Sent: Thursday, December 07, 2006 5:48 PM
To: binhpham15@xxxxxxxxxxx; Jesse, Rich
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Stored outline not being used all the times...
The row with all 'N' should be the first child as mentioned here
<http://el-caro.blogspot.com/2006/11/diagnosing-unshared-sql-in-10g.html
>
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! <http://g.msn.com/8HMBENUS/2752??PS=47575> --
//www.freelists.org/webpage/oracle-l
Fairlie Rego
Senior Oracle Consultant
http://www.linkedin.com/in/fairlierego
<http://www.optus.com.au/>
http://el-caro.blogspot.com/
M: +61 402 792 405
________________________________
Any questions? Get answers on any topic at Yahoo! Answers
<http://answers.yahoo.com/;_ylc=X3oDMTFvbGNhMGE3BF9TAzM5NjU0NTEwOARfcwMz
OTY1NDUxMDMEc2VjA21haWxfdGFnbGluZQRzbGsDbWFpbF90YWcx> . Try it now.
Other related posts:
