Unshared cursors (WAS: Stored outline not being used...)

From: Jesse, Rich <Rich.Jesse_at_qg.com>
Date: Tue, 2 Jan 2007 09:48:50 -0600
Message-ID: <FB5D3CCFCECC2948B5DCF4CABDBE6697A52818@QTEX1.qg.com>

Hey all,  

I opened an SR on this. While I don't have a repeatable process yet (and therefore no BUG apparently as per a previous thread here), this problem seems to be pointing to flushing the shared pool. The kicker is that I don't think I'll be able to look at this problem any longer other than to put in our SOPs not to flush the shared pool.  

Has anyone else seen this in 10gR2, specifically with flushing the shared pool? From past experience and advice, I flush our hybrid production 9.2.0.5.0 nightly with no known side effects.  

Rich

---

From: Jesse, Rich
Sent: Thursday, December 07, 2006 7:40 PM To: oracle-l_at_freelists.org
Subject: RE: Stored outline not being used all the times...

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_at_yahoo.com] Sent: Thursday, December 07, 2006 5:48 PM To: binhpham15_at_hotmail.com; Jesse, Rich
Cc: oracle-l_at_freelists.org
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_at_hotmail.com> wrote:

        I've seen those before, tried to understand why but no clue. Any clue?                                   

---

		From: "Jesse, Rich" <Rich.Jesse_at_qg.com>
		Reply-To: Rich.Jesse_at_qg.com
		To: <oracle-l_at_freelists.org>
		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';

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 02 2007 - 09:48:50 CST