I think you are right, but it is not likely more than 1000 or so of these
statements are executed each day (I hope). So a few statements with literals
should not be so bad.
Believe me, I have had trouble enough with the shared pool and a conversion
program that fired many thousands of identical statements accept for the
literals values in a few hours. This was on V8.0.5 so we could not use the
V8.1 parameter CURSOR_SHARING.
When the OP really has a problem this parameter could be of any help but not
without knowing all its impact.
Bricklen <bricklen_at_shaw.ca> schreef in berichtnieuws
3DA0BA23.108EB898_at_shaw.ca...
| I would think that what the OP is trying do is use bind variables so
| that his shared pool is not getting filled by the
| "SELECT /*+NESTED_TABLE_GET_REFS+*/ "me"."the_table".* FROM > |
| "me"."the_table" where thisid > 15379950" sql. Seems like a reasonable
| question, where you don't want the same sql aging everything else out
| (when bind variables are an obvious answer).
|
| Anton Buijs wrote:
| >
| > Ofcourse not. The value of $max_thatid is replaced by it's value first
and
| > then exp is started. That's how ksh and bash variable evaluation works.
| > But does it matter? Why do you really want to see a bind variable?
| > I don't see any method getting a bind variable used in Oracle when a
query
| > is passed as exp parameter.
| >
| > RR <rickraster_at_hotmail.com> schreef in berichtnieuws
| > 97b4acf3.0210061306.1b3db0c3_at_posting.google.com...
| > | Hi all. This is probably a pretty basic question, but I'm stumped...
| > | Oracle 8.1.7
| > | RH 7.2, using bash
| > |
| > | I have a shell script, run from the command line, that accesses a
| > | remote database and returns a numeric value to a shell variable. That
| > | part works fine, but I am using that value as part of the QUERY
| > | keyword in EXP. Everything works, but I find when I check the shared
| > | pool, the shell variable is not a bind variable (obviously my
| > | assumption was incorrect).
| > |
| > | the shell variable is assigned:
| > | max_thatid=`sqlplus -s xxxxxxxx/xxxxxxxx_at_remote_db
| > | @$ORACLE_HOME/bin/get_id.sql`
| > |
| > | the get_id.sql script looks like this:
| > | set timing off
| > | set feedback off
| > | set verify off
| > | set heading off
| > | set echo off
| > | select max(thatid) from the_table;
| > | set timing on
| > | exit
| > |
| > |
| > | the export is run like so:
| > | EXP ... query=\"where thisid \> $max_thatid\"
| > |
| > |
| > | the shared pool then shows this:
| > | ...
| > | SELECT /*+NESTED_TABLE_GET_REFS+*/ "me"."the_table".* FROM
| > | "me"."the_table" where thisid > 15379950
| > | ...
| > |
| > |
| > | No doubt it's something simple and obvious, but I am certainly missing
| > | it!
| > |
| > | Thanks!!!
| > |
| > | Cheers,
| > |
| > | Rick
Received on Mon Oct 07 2002 - 03:40:16 CDT
