| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: oracle recompiling sql the same sql repeatedly?
The recompile happens even with 'similiar'. Looks like oracle is forced to recompile when its doing partition pruning. I looked closer at the tkprof. So I guess there is a trade off, benefits of partition prune vs. the increased latch connection of recompiling a certain number of queries. When I tried to use 'force' the LIOs were horrible though the plan was same. My best guess is that oracle is using a partition pruning plan, but it doesn't know which one to take, so we have extra LIOs. LIOs are about 5 times higher with force.
Hey Ryan,
I would challenge Support to defend using CS=S for you. This is really not a situation where one should use it. For us, it was either recode all of our programs to use bind variables (when we started with Oracle, we didn't know how important they were), use CS=S, or have nightmares about the shared pool. So I chose the lesser of all evils, CS=S. And it hasn't been without it's problems. There have been bugs where some queries would return incorrect data. And a recent ORA-6500 bout may be affected by CS=S (although less likely).
Don't take a decision to use CS=S lightly!
HTH! GL! Rich
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ryan_gaffuri_at_comcast.net
Sent: Thursday, June 22, 2006 9:37 AM
To: oracle-l_at_freelists.org
Subject: oracle recompiling sql the same sql repeatedly?
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?
 |
 |