Skip to main content
  • EN
I am creating features with SQLCreator on Oracle 11g.   I have some complex joins and filters ... and a LOT of them to issue.

 

 

So many that Oracle CPU performance goes to hell.   I'm told because it can't do all that hard parsing, because there are no "bind variables"

 

 

I am using FME Server 2012.   There are 16 engines, each submit 2 queries per job to get the features it needs to process.  At any given time there are 32 queries executing at once and by the end of an hour 250+ jobs will have to run.   That is over 500 queries needing a hard parse.  With CURSOR_SHARING set to EXACT, the CPU thrashes itself to death.

 

 

How can I get SQLCreator to use bind variables?   Or should I use a reader and parameterize all the SQL for the Reader?   Do readers force bind variables?

 

 

Brad
Addendum:

 

 

Can I send a block of Oracle PL/SQL statements to SQLCreator

 

 

DECLARE

 

myparm NUMBER(10);

 

BEGIN

 

  select myattr, mygeom from myfeature where myid=myparm;

 

END;

 

 

How do I get it to return the data to FME?

 

 

--Brad
Hi,

 

 

it is unfortunate, but FME does not currently support bind variables in the SQLCreator / SQLExecutor transformers. Also, I do not know if there are any plans to support this, or how difficult it is to implement given the FME architecture.

 

 

I do believe that the Oracle readers / writers use bind variables, though, although I've never verified it.

 

 

As for the performance, perhaps a rethink is in order. Would it be possible to define your select statements in a view and then access this view rather than the joined tables with FME?

 

 

David
Bind variables definately used when writing because I recently had a problem updating using Oracle Writer when using a Sequenced column, now fixed in 2014 Beta!
Readers don't appear to use bind variables when you supply an SQL parameter.

 

 

Still investigating query hints.   Since I know there is no optimization possible, maybe I can prevent it wasting CPU trying to optimize it.

 

 

Brad

Reply


Community Stats

30,159
Posts
114,480
Replies
37,806
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.