RE: oracle recompiling sql the same sql repeatedly?

Ahh yes. 9i has bind peeking. I bet that columns BIND_MISMATCH and/or INCL_LTRL_MISMATCH are 'Y' in that view (join the CHILD_ADDRESS column in V$SQL to KGLHDPAR in V$SQL_SHARED_CURSOR).  

Ryan, like I said, we're using CS=S, but we still have four statements that match your V$SQL query, probably due to bind peeking in our 9.2.0.5.0 DB.  

Rich

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of goran bogdanovic Sent: Thursday, June 22, 2006 9:59 AM
To: ryan_gaffuri_at_comcast.net
Cc: oracle-l_at_freelists.org
Subject: Re: oracle recompiling sql the same sql repeatedly?

Check V$SQL_SHARED_CURSOR

On 6/22/06, ryan_gaffuri_at_comcast.net < ryan_gaffuri_at_comcast.net <mailto:ryan_gaffuri_at_comcast.net> > wrote:

        I have a test box. I am testing some things out. I noticed excessive row cache waits, so I ran this generic query:

        select substr(sql_text,1,100),count(*)

        from v$sql

        group by substr(sql_text,1,100)

        having count(*) > 500

        order by 2

        I found that some of my test sql was recompiling repeatedly.

1. I am using bind variables and I am not using dynamic sql.
2. I was not able to do a select count(distinct sql_fulltext) from v$sql because its a clob, so I copied the sql to a new table with a varchar2(4000) column.
3. I did a select count(distinct sql_fulltext) from mydifftable and I got 1 row back.
4. Copied to rows from v$sql with this sql to files locally and ran an open source diff utility on them and got the exact same sql.

        Why would oracle recompile the same sql? It is run out of the same schema? Oracle support recommended that I use cursor_sharing=similiar; before I do that I want to understand why this is happening.

        Anyone see this before? I am on 10g release 2. Maybe I missed something in the docs?

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