Hi guys,

My application access a Oracle database. So, on every report WebFOCUS convert my FOCUS commands (like TABLE) on SQL code to send to Oracle.
I would like to configure WebFOCUS to use Oracle bind variables on the generated SQL. So, instead of sending the code with the filters already filled just like

quote:

SELECT "BRANCH_NAME",
SUM(T1."QUANTITY_REQUIREMENTS")
FROM REQUIREMENTS T1,
BRANCHES T4
WHERE (T4."ID_BRANCH" = T1."ID_BRANCH")
AND (T4."BRANCH_TYPE" IN(4, 16, 0, 22, 26, 27, 31, 35, 36))
GROUP BY T4."BRANCH_NAME"
ORDER BY T4."BRANCH_NAME";

I need to send the parameters separated like

quote:

SELECT "BRANCH_NAME",
SUM(T1."QUANTITY_REQUIREMENTS")
FROM REQUIREMENTS T1,
BRANCHES T4
WHERE (T4."ID_BRANCH" = T1."ID_BRANCH")
AND (T4."BRANCH_TYPE" IN(?, ?, ?, ?, ?, ?, ?, ?, ?))
GROUP BY T4."BRANCH_NAME"
ORDER BY T4."BRANCH_NAME";

Parameters: (4, 16, 0, 22, 26, 27, 31, 35, 36)

My intention is to get better performance as described in this article: http://www.oracle.com/technetw...o26frame-100826.html

Is it possible? Can I configure WebFOCUS to do this?

This message has been edited. Last edited by: flavio.menezes,

Our DBA's have always asked if we could do this with Webfocus.

I would too like to know if this is possible.

Hi flavio and MattC,

You can use Oracle bind variables in a query. Please reference the following:

How to use Oracle bind variables in a query

However, you can't use them in a WHERE clause:

How to use an Oracle Bind Variable in a WHERE clause

Regards,

Kathryn

Thanks very much for the information Kathryn. I may find some interesting usage for this technique.

However it seems to be supported only when using SQL passthru where the developer has full control of the SQL statement and the bind variables to be used, and this is not quite the same Flavio seems to need. If my understanding is correct, he is looking for some sort of setting that would cause the use of bind variables automatically when TABLE FILE statements are being translated to SQL by the iWay Adapter, and this I don't think exists.

Flavio, in this case you could try to force cursor sharing in your particular Oracle session. I haven't personally done it in my environment because of the adhoc nature of the queries submitted to our database. SQL statements vary significantly one from another so the use of bind variables would create multiple "unique" cached cursors anyway, pretty much the same that happens with "hard-coded" filter conditions. In addition, common reports used by our users use mostly the same constant filters (the most recent business date for instance) so we are indirectly re-using the same cached statement even when no bind variables are being used.

In any case, you'll have to carefully test this setting as it may not necessarily make the impact your DBA is expecting. I am a big advocate of using bind variables in OLTP applications where you tend to execute very similar SQL statements thousands of times and the only difference among them is the constant values being used on each execution. Avoiding excessive parsing and reusing execution plans is a must for a scalable system like that.

In DSS/data warehouse environments however I have noticed that using bind variables (or not) seems to have less of an impact especially when adhoc queries tend to be rather different from one another and therefore parsing cannot really be avoided.

Look for instance your sample statement:

SELECT "BRANCH_NAME",
SUM(T1."QUANTITY_REQUIREMENTS")
FROM REQUIREMENTS T1,
BRANCHES T4
WHERE (T4."ID_BRANCH" = T1."ID_BRANCH")
AND (T4."BRANCH_TYPE" IN(4, 16, 0, 22, 26, 27, 31, 35, 36))
GROUP BY T4."BRANCH_NAME"
ORDER BY T4."BRANCH_NAME";

The only place where you'd use bind variables would be in the filter on BRANCH_TYPE. If you could use bind variables you would turn it into something like this:

...
AND "T4.BRANCH_TYPE" IN (:CAT1, :CAT2, :CAT3 :CAT4, :CAT5, :CAT6, :CAT7, :CAT8, :CAT9, :CAT10)
...

Though at a first glance it looks like a "good thing" because it's using bind variables, keep in mind that Oracle will only attempt to reuse the same cursor and plan when another SQL statement exactly as the one above is issued (that is, same columns, aggregations, group by, sorts, etc) and the only variable pieces are those 10 values for the category. If the new SQL statement however had more than 10 values in the IN clause (or less), or if the statement had an extra AND expression or an extra column, or one less sort field, etc. Oracle wouldn't be able to reuse the cursor and will have to hard parse the new statement anyway so those bind variables wouldn't give you that much of an advantage in this case.

That behaviour is rather common in data warehouse environments and I am not too sure that forcing cursor sharing would make a big positive impact. In any case, only you and your DBA know your environment better than anyone outside your organization and can determine what kind of queries (and their volume) the database handles on a daily basis so make sure to test, test and test some more before switching that setting on a more permanent basis.

This message has been edited. Last edited by: njsden,

Kathryn,

Thank you! Maybe we can use this approach in some foreseeable reports for now on.

njsden,

You really understood my point!!! In fact our company has a solid environment for OLTP applications but we are less experienced in BI. So we usually tend to use the same approach for OLTP and BI. But the concerns that you pointed are really important. We need to analyze each situation and study where make sense to use the technique showed by Kathryn. In general our application has a adhoc nature, as you mentioned. I think using bind variables will be positive in very specific cases and only testing we'll able to find where.

Great post, njsden. I'm very grateful! Thank you.

njsden is an Oracle super genius, it's scary!

I'm not sure how often people actually use bind variables (and prepared statements) for performance reasons, although that is certainly an important part of what they were designed for.

In my experience people often use them for security reasons, as it automatically sanitizes database input and thus helps prevent SQL injection. The web comic Xkcd has a nice cartoon about that: http://bobby-tables.com/

Do people actually sanitize input in reporting though? Reporting environments aren't generally open to the public internet, so if someone tries to damage data on purpose they are inside the company and are fairly easily found (and prosecuted). Of course that doesn't stop some people...
I can't say I have given it much thought since I started working with WebFOCUS.