Skip to main content
Question

SQL Creator and CURSOR_SHARING in Oracle 11g

  • July 10, 2013
  • 4 replies
  • 20 views
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
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

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
Celebrity
  • david_rCelebrity
  • 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

Community Stats

32,181
Posts
122,172
Replies
39,855
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.