Re: Unshared cursors redux

Sorry for jumping in...I didn't have time to read the article of JL, but the case that you mentioned Yasin, shouldn't that be indicated as "BIND_MISMATCH" in v$sql_shared_cursor?

Thanks,
Goran
*
*
On 2/22/07, Yasin Baskan <yasin.baskan_at_yapikredi.com.tr> wrote:
>
>
>
> We have several sql statements having tens of versions and
> v$sql_shared_cursor does not show any difference between versions.
>
> This can be related to the sizes of the values for your bind variables.
> For example, if you have a varchar2(128) column and you provide a 10
> character input for it and then you run the same sql with a 100
> character input you get two versions of the same statement. Support says
> the solution for this is to first run the sql with the largest input
> values possible.
>
> Jonathan Lewis has a post about this,
> http://jonathanlewis.wordpress.com/2007/01/05/bind-variables
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rich Jesse
> Sent: Wednesday, February 21, 2007 6:22 PM
> To: oracle-l_at_freelists.org
> Subject: Unshared cursors redux
>
> Hi all,
>
> While investigating a hot table in 10.1.0.5, I see that one of the
> SELECTs
> hitting it has multiple children. No big deal, I'll just use my buddy
> V$SQL_SHARED_CURSOR to see the issue, right? Wrong. Once again, all
> explanation columns for every occurance of the cursor is "N". Here's
> the
> SQL I used to check for more of them (note: this is specific to 10gR1!):
>
> select sc.address, sc.total, vsa.sql_text
> 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|| litrep_comp_mismatch
> "FLAGS",
> count(*) "TOTAL"
> 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|| litrep_comp_mismatch
> having count(*) > 1
> ) "SC", v$sqlarea vsa
> where flags = 'NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN'
> and sc.address = vsa.address
> order by total desc;
>
> <groan> Here we go again! The last time I saw this problem was on
> 10gR2,
> but after extensive work on an SR, that issue could possibly be blamed
> on
> nightly shared pool flushing (which I still think is a BUG, but I'm no
> longer able to access that particular SR). According to the alert log
> for
> this database, there has been no SP flushing.
>
> Could it be that the 10gR1 fixed view is just not "mature" enough and
> that
> the reason columns explaining the multiple cursors was added in 10gR2?
>
> I *really* don't have the time to deal with an SR on this... Thoughts
> anyone
> ???
>
> TIA!
> Rich
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
> Bu mesaj ve onunla iletilen tum ekler gonderildigi kisi ya da kuruma ozel
> ve Bankalar Kanunu geregince, gizlilik yukumlulugu tasiyor olabilir. Bu
> mesaj, hicbir sekilde, herhangi bir amac icin cogaltilamaz, yayinlanamaz ve
> para karsiligi satilamaz; mesajin yetkili alicisi veya alicisina iletmekten
> sorumlu kisi degilseniz, mesaj icerigini ya da eklerini kopyalamayiniz,
> yayinlamayiniz, baska kisilere yonlendirmeyiniz ve mesaji gonderen kisiyi
> derhal uyararak bu mesaji siliniz. Bu mesajin iceriginde ya da eklerinde yer
> alan bilgilerin dogrulugu, butunlugu ve guncelligi Bankamiz tarafindan
> garanti edilmemektedir ve bilinen viruslere karsi kontrolleri yapilmis
> olarak yollanan mesajin sisteminizde yaratabilecegi zararlardan Bankamiz
> sorumlu tutulamaz.
>
> This message and the files attached to it are under the privacy liability
> in accordance with the Banking Law and confidential to the use of the
> individual or entity to whom they are addressed. This message cannot be
> copied, disclosed or sold monetary consideration for any purpose. If you are
> not the intended recipient of this message, you should not copy, distribute,
> disclose or forward the information that exists in the content and in the
> attachments of this message; please notify the sender immediately and delete
> all copies of this message. Our Bank does not warrant the accuracy,
> integrity and currency of the information transmitted with this message.
> This message has been detected for all known computer viruses thence our
> Bank is not liable for the occurrence of any system corruption caused by
> this message
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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