Get a copy of Tom's book ....
"srivenu" <srivenu_at_hotmail.com> wrote in message
news:1a68177.0307092113.619f62a0_at_posting.google.com...
> I really appreciate everyone who has answered and especially yong.
> I think, yong, you are right.
> The high version count is due to Bind Mismatch.
> I gave the following query and got the answer for my question.
>
> sys_at_OMPROD> l
> 1 select
substr(sql_text,1,25),child_number,first_load_time,parsing_user_id,
> 2 decode(b.unbound_cursor,'Y','-Child Cursor not fully
> built-','')||
> 3 decode(b.sql_type_mismatch,'Y','-Sql Type Mismatch-','')||
> 4 decode(b.optimizer_mismatch,'Y','-Optimizer Mode
> Mismatch-','')||
> 5 decode(b.outline_mismatch,'Y','-Outline Mismatch-','')||
> 6 decode(b.stats_row_mismatch,'Y','-Statistics Mismatch-','')||
> 7 decode(b.literal_mismatch,'Y','-Non-Data Literal
> Mismatch-','')||
> 8 decode(b.sec_depth_mismatch,'Y','-Security Mismatch-','')||
> 9 decode(b.explain_plan_cursor,'Y','-Explain Plan Cursor Hence
> Not Shared-','')||
> 10 decode(b.buffered_dml_mismatch,'Y','-Buffered DML
> Mismatch-','')||
> 11 decode(b.pdml_env_mismatch,'Y','-PDML Mismatch-','')||
> 12 decode(b.inst_drtld_mismatch,'Y','-Insert Direct Load
> Mismatch-','')||
> 13 decode(b.slave_qc_mismatch,'Y','-Slave Cursor-','')||
> 14 decode(b.typecheck_mismatch,'Y','-Child Not Fully
> Optimized-','')||
> 15 decode(b.auth_check_mismatch,'Y','-Child Authorization
> Failure-','')||
> 16 decode(b.bind_mismatch,'Y','-Bind Metadata Mismatch-','')||
> 17 decode(b.describe_mismatch,'Y','-Type Check Heap Not Present
> During Child Describe-','')||
> 18 decode(b.language_mismatch,'Y','-Language Mismatch-','')||
> 19 decode(b.translation_mismatch,'Y','-Child Cursor Base Objects
> Mismatch-','')||
> 20 decode(b.insuff_privs,'Y','-Insufficient Privileges On Objects
> Referenced In Child-','')||
> 21 decode(b.insuff_privs_rem,'Y','-Insufficient Privileges On
> Remote Objects-','')||
> 22 decode(b.remote_trans_mismatch,'Y','-Remote Base Objects Of The
> Child Dont Match-','') reason,
> 23 loaded_versions,open_versions,
> 24 kept_versions,users_opening,users_executing
> 25 from v$sql a,v$sql_shared_cursor b
> 26 where a.address=b.kglhdpar
> 27 and sql_text like '%DATE_ENTERED,NOTES_ID%'
> 28* order by 2
> sys_at_OMPROD> /
>
> Par Loa
> First sing Reason For ded
> Open Kept Users Users
> Child Load User Cursor Not Vers
> Vers Vers Ope Execu
> SUBSTR(SQL_TEXT,1,25) Number Time id Being Shared ions
> ions ions ning ting
> ------------------------- ------ ---------- ----- --------------- ----
> ---- ---- ----- -----
> INSERT INTO ASAP.NOTES ( 0 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 1 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 2 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 3 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 4 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 5 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 6 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 7 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 8 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 9 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 10 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 11 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 12 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 13 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 14 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 15 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 16 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 17 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 18 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 19 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 20 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 21 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 22 2003-06-21 72 -Bind Metadata 1
> 1 0 32 32
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 23 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 24 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 25 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 26 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 27 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 28 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 29 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 30 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
> /02:00:34 Mismatch-
>
> INSERT INTO ASAP.NOTES ( 31 2003-06-21 0 -Bind Metadata 0
> 0 0 0 0
>
> Par Loa
> First sing Reason For ded
> Open Kept Users Users
> Child Load User Cursor Not Vers
> Vers Vers Ope Execu
> SUBSTR(SQL_TEXT,1,25) Number Time id Being Shared ions
> ions ions ning ting
> ------------------------- ------ ---------- ----- --------------- ----
> ---- ---- ----- -----
> /02:00:34 Mismatch-
>
>
> 32 rows selected.
>
> Now these statements are coming from an application.
> Can someone tell me how i can rectify this ?
> Thanks in advance
>
> regards
> Srivenu
Received on Thu Jul 10 2003 - 03:44:23 CDT
