Check V$SQL_SHARED_CURSOR
On 6/22/06, ryan_gaffuri_at_comcast.net <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
Received on Thu Jun 22 2006 - 09:58:41 CDT
