RE: Unshared cursors redux

From: Yasin Baskan <yasin.baskan_at_yapikredi.com.tr>
Date: Thu, 22 Feb 2007 09:20:54 +0200
Message-ID: <083667B535F3464CA0DD0D1DAFA4E3760C969D24@camexc1.kfs.local>

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||

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 Received on Thu Feb 22 2007 - 01:20:54 CST