RE: Unshared cursors redux
- From: "Yasin Baskan" <yasin.baskan@xxxxxxxxxxxxxxxx>
- To: <rjoralist@xxxxxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 22 Feb 2007 09:20:54 +0200
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Rich Jesse
Sent: Wednesday, February 21, 2007 6:22 PM
To: oracle-l@xxxxxxxxxxxxx
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
--
//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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
