Skip to main content
Question

SQL Creator and CURSOR_SHARING in Oracle 11g

B
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

4 replies

B
  • blekros
  • Author
  • July 10, 2013
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
david_r
Evangelist
  • david_rEvangelist
  • July 11, 2013
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
M
Forum|alt.badge.img+2
  • mark_f
  • July 11, 2013
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!
B
  • blekros
  • Author
  • July 15, 2013
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


Helpful Members This Week

Community Stats

30,942
Posts
117,252
Replies
38,742
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.