Home
All Oracle Error Codes
Oracle DBA Forum

Frequent Oracle Errors

TNS:could not resolve the connect identifier specified
Backtrace message unwound by exceptions
invalid identifier
PL/SQL compilation error
internal error
missing expression
table or view does not exist
end-of-file on communication channel
TNS:listener unknown in connect descriptor
insufficient privileges
PL/SQL: numeric or value error string
TNS:protocol adapter error
ORACLE not available
target host or object does not exist
invalid number
unable to allocate string bytes of shared memory
resource busy and acquire with NOWAIT specified
error occurred at recursive SQL level string
ORACLE initialization or shutdown in progress
archiver error. Connect internal only, until freed
snapshot too old
unable to extend temp segment by string in tablespace
Credential retrieval failed
missing or invalid option
invalid username/password; logon denied
unable to create INITIAL extent for segment
out of process memory when trying to allocate string bytes
shared memory realm does not exist
cannot insert NULL
TNS:unable to connect to destination
remote database not found ora-02019
exception encountered: core dump
inconsistent datatypes
no data found
TNS:operation timed out
PL/SQL: could not find program
existing state of packages has been discarded
maximum number of processes exceeded
error signaled in parallel query server
ORACLE instance terminated. Disconnection forced
TNS:packet writer failure
see ORA-12699
missing right parenthesis
name is already used by an existing object
cannot identify/lock data file
invalid file operation
quoted string not properly terminated

RE: Why we should use bind variables when we write code......

Mark W. Farnham

2006-09-20

Replies:

An archetypal example is “everyone fill out your on line labor distribution worksheet for last week” between 9 AM and 9:15 AM on Monday. And code that up with a literal for the employee id. Sure, they’ll age out eventually, but of course it should have been bind variables given that there is certainly a unique index or primary key on employee id. Except that the application was written before there was a shared sql area, and literals parsed and executed just a wee bit faster back then. So now that application just overwhelms the shared sql area pushing everything else out and those nearly identical sqls are the most recently used. Sure, eventually that application will be retired, but if you’ve got an application no longer being maintained by the vendor and you don’t have the source code you might be stuck with it for a while competing for budget and application selection. (Doesn’t everyone hate the incumbent applications except when a change to something new is proposed?)

 

I’m still not sure why there is no Unshared sql area and no session parameter to parse privately (skipping all the latches and the hash and search of shared sql). It is not as if that would be creating something new – that was all there used to be! True, that defeats the laudable purpose of the shared sql area, which scales much better if the application is written for bind variables, but it would handle antique applications that cannot be cost effectively changed. And the corresponding hint could be used for modern data warehouse queries designed for literals as well. Usually there is no point in keeping a copy of those parses either, but with a hint rather than a session parameter you could mix private and shared parsing as appropriate. And the session parameter solves the “I can’t change the application” problem, so you need both.

 

Sigh. The other improvement that would be easily added if there existed an unshared sql area is bailing out if the search in the shared area reaches either some limit of absolute time or some fraction of the average parse time. Of course that would help ameliorate latch storms, or any other pile up on the shared sql area. If it is being too expensive at the moment to use the shared area, parse me privately. Done. Thankyou very much. Next customer. Oh – and maybe optionally log the sql when you time out into a private parse and keep some stats so you know if you have a problem brewing. Because of course private parsing doesn’t ultimately scale as well as shared parsing. But it could be very effective in the treating the symptoms while the doctors try to figure out whether there is a cure for the disease. Then we get into the whole issue of the cost of parsing. The reason we tolerate the cost of permutations is that we’re expecting to get a big reduction in execution cost on average, and the scale is tipped in favor of spending time getting a good plan if you expect the shared sql to be re-used many times. If the sql in question is going to be pretty cheap to execute with pretty much any logical plan (cough, RBO, cough), then spending time on permutations quickly becomes a waste. (See nearly any of Cary’s publications for the citation to Amdahl’s Law (1967).) So *maybe* if the absolute cost underflows some epsilon it should stop trying to get better, and you’d tend to want epsilon to be higher if you bailed out to a private parse. That is way better than altering your session to a very low max permutations, because that affects expensive to execute queries as well.

 

I really miss having Oracle VLDB meetings. Just hit delete if this was uninteresting to you.

 

mwf

 


From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Wolfgang Breitling
Sent: Wednesday, September 20, 2006 1:14 AM
To: Mark.Bobak@il.proquest.com
Cc: oracle-l
Subject: Re: Why we should use bind variables when we write code......

 

In addition to my warning about "inappropriate" use of bind variables I am wondering how the 3,424 "identical except for literals" sql could cause the shared pool to become fragmented. They should be eligible to be aged out for new sql, including "identical except for literals" ones. Unless, of course, the application doesn't close the corresponding cursor. But that would be a different issue.
As some of you know I am working with Peoplesoft applications which are not particularly knownfamous for their use of bind variables. But I rarely encounter ora-04031 errors and when then they are caused by some other application/add-on, often 3rd party monitoring or administrative "utilities".

At 08:37 AM 9/19/2006, Bobak, Mark wrote:


So, last week, one of my instances starts getting ORA-4031s, and after a few minutes, comes crashing down when a background process (lmd0, I think it was) catches an ORA-4031.  So, with the instance down, it's a bit tough to see what happened.  So, we start things up again, and I start watching closely over the next few days.  Seems there's lots of code that doesn't bother with binds.  In some cases, there are a dozen non-sharable SQLs that are identical except for literals, in other cases, up to hundreds.  (Thanks to T.Kyte for the script that I'm using to identify non-sharable SQL.)  After a few days, I find the smoking gun.  One single SQL statement that has 3,424 copies that are identical except for literals.  (No, that's not a typo.)  This is taking up abour 75% of the 475M of shared pool that's dedicated to the sql area.  One single SQL statement, 75%.  Yikes!

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com