Re: 2 short questions (DBMS_SQL)

To expand on Mark's comment about bind variables. The reason they are more efficient is due to the fact that they allow the parsing of that SQL statement to stay around in memory for other sessions to reuse. If you just concatenate a value, Oracle will see that as an entirely different statement (unless the variable value being concatenated is exactly the same) and parse the statement all over again and store its parsing in memory for possible reuse. If you loop through enough times on this statement (or have lots of users running it multiple times) you will eventually fill up the Oracle SGA and then the optimizer has to go through the process of cleaning LRU queries out of the library cache until there is enough room to parse the query (meanwhile, if you have lots of users trying to run their concatenated queries, they will either spin cycles on the cpu or their oracle sessions will sleep until the optimizer can get to them and do the same thing). This added memory and cpu contention can really drag down a system (and it is one of the most common performance degrading processes out there--I've seen an application achieve an approximate 40% gain in performance just by replacing the concatenations with with bind variables). Just trying to give you an idea why it's more efficient...

Jeff S

Mark G <someone_at_hot> wrote in message news:378077b7.0_at_145.227.194.253...
> 1. Bind variables are more efficient.
>
> 2. Cant help you on the other one. Maybe someone is holding a lock on the
> table you want to drop?
>
> M
>
> Morten wrote in message ...
> >
> >1) When should one use BIND_VARIABLE instead of
> > string concatenation?
> > E.g.: v_Stuff := 'SELECT :m FROM table';
> > vs. v_Stuff := 'SELECT ' || stuff || ' FROM table';
> >
> >2) When I try to drop a procedure absolutely nothing
> > happens. SQL*PLUS just 'freezes' and stays there
> > until I get the following timeout
> >
> > SQL> drop procedure mapdocument;
> > drop procedure mapdocument
> >
> > ERROR at line 1:
> > ORA-04021: timeout occurred while waiting to lock object
> > BIIS.MAPDOCUMENT
> >
> > Can any of you help me decipher this?
> >
> >Thanks a ton
> >
> >Morten
> >
> >
> >
> >
>
>
Received on Tue Jul 06 1999 - 19:15:14 CDT