Varying IN lists – last bit

So, just a final say on this series of blog entries.

The first is that often you start down one workaround and then you find something that doesn’t quite work so you workaround that and before you know it you’re along way from where you’d like to be.

What started as a simple desire to reduce the impact of dynamically generated varying IN lists (some not using bind variables) from a couple of applications was severely complicated by ODP.NET’s lack of support / interoperability with Oracle UDTs and the OCI limit of 4000 characters in a VARCHAR2 bind.

As a result, the current choice is between the original situation – lots of similar SQL – and inserting the values into a GLOBAL TEMPORARY TABLE and then using a query which has a WHERE … IN subquery selecting from that GTT. More on that down below…

When I wrote previously, also under consideration was using a global packaged variable. However this was eliminated as a possibility due to at least three frustrating issues. First up was an idea to use a function that would convert an associative array (supported by ODP.NET) to a similar UDT (just using a FOR LOOP that puts the values from one into the other). Using a bit of PL/SQL to demonstrate (8i hence the lack of SYS_REFCURSOR and the need to declare a REF CURSOR in a package):


CREATE OR REPLACE TYPE tt_number AS TABLE OF NUMBER;
/


CREATE OR REPLACE PACKAGE pkg_types
AS
--
TYPE refcursor IS REF CURSOR;
TYPE aa_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
--
END pkg_types;
/


CREATE OR REPLACE FUNCTION f_tt_convert_error_demo (
i_associative_array IN pkg_types.aa_number
)
RETURN tt_number
AS
--
vt_number tt_number := tt_number();
--
BEGIN
--
FOR i IN i_associative_array.FIRST .. i_associative_array.LAST
LOOP
--
vt_number.EXTEND();
vt_number(i) := i;
--
END LOOP;
--
RETURN vt_number;
--
END;
/


declare
v_number number;
v_cursor pkg_types.refcursor;
va_number pkg_types.aa_number;
begin
for i in 1 .. 10
loop
va_number(i) := i;
end loop;
open v_cursor for
select *
from table(cast(f_tt_convert_error_demo(va_number) as tt_number));
loop
fetch v_cursor into v_number;
exit when v_cursor%NOTFOUND;
dbms_output.put_line(v_number);
end loop;
close v_cursor;
end;

I should have known, but this cannot work due to an error “PLS-00425: in SQL, function argument and return types must be SQL type”.

Incidentally, if you try dynamic SQL, you’ll get a “PLS-00457: expressions have to be of SQL types” instead.

Secondly, just because you can do something in SQL doesn’t mean that the same statement will work in a PL/SQL routine (and vice versa from memory – it tends to be features that are relative new). Following on from SQL above:


CREATE OR REPLACE FUNCTION f_tt_error_demo
RETURN tt_number
AS
--
vt_number tt_number := tt_number();
--
BEGIN
--
FOR i IN 1 .. 10
LOOP
--
vt_number.EXTEND();
vt_number(i) := i;
--
END LOOP;
--
RETURN vt_number;
--
END;
/


SQL> select value(t)
2 from table (cast (f_tt_error_demo as tt_number)) t
3 /
VALUE(T)
----------
1
2
3
4
5
6
7
8
9
10


SQL> declare
2 begin
3 for a in (select VALUE(t) num
4 from table (cast (f_tt_error_demo as tt_number)) t)
5 loop
6 dbms_output.put_line(a.num);
7 end loop;
8 end;
9 /
declare
*
ERROR at line 1:
ORA-06550: line 0, column 0:
PLS-00382: expression is of wrong type
ORA-06550: line 3, column 13:
PL/SQL: SQL Statement ignored
ORA-06550: line 6, column 28:
PLS-00364: loop index variable 'A' use is invalid
ORA-06550: line 6, column 7:
PL/SQL: Statement ignored

but, with dynamic sql:


SQL> declare
2 v_number number;
3 v_cursor pkg_types.refcursor;
4 begin
5 open v_cursor for
6 ' select value(t) '||
7 ' from table (cast (f_tt_error_demo as tt_number)) t ';
8 loop
9 fetch v_cursor into v_number;
10 exit when v_cursor%NOTFOUND;
11 dbms_output.put_line(v_number);
12 end loop;
13 close v_cursor;
14 end;
15 /
1
2
3
4
5
6
7
8
9
10
PL/SQL procedure successfully completed.

Thirdly, the above are distilled code examples of further errors that I came across while trying to put together a bit of example code for this blog entry to show the real problem that I had.

I actually wanted to use the function in a subquery like this:


create table error_demo
as
select rownum col1
from all_objects
where rownum < 11
1 select *
2 from error_demo
3 where col1 IN (select value(t) num
4* from table (cast (f_tt_error_demo as tt_number)) t)
SQL> /
COL1
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.


SQL> declare
2 v_number number;
3 v_cursor pkg_types.refcursor;
4 begin
5 open v_cursor for
6 select col1
7 from error_demo
8 where col1 IN (select *
9 from table (cast (f_tt_error_demo as tt_number)) t);
10 loop
11 fetch v_cursor into v_number;
12 exit when v_cursor%NOTFOUND;
13 dbms_output.put_line(v_number);
14 end loop;
15 close v_cursor;
16 end;
17 /
from table (cast (f_tt_error_demo as tt_number)) t);
*
ERROR at line 9:
ORA-06550: line 9, column 37:
PLS-00220: simple name required in this context
ORA-06550: line 6, column 4:
PL/SQL: SQL Statement ignored

“PLS-00220: simple name required in this context” – that’s a new one for me, first time I’ve had that error, I think.

But again, with dynamic sql:


SQL> ed
Wrote file afiedt.buf
1 declare
2 v_number number;
3 v_cursor pkg_types.refcursor;
4 begin
5 open v_cursor for
6 ' select col1 '||
7 ' from error_demo '||
8 ' where col1 IN (select * '||
9 ' from table (cast (f_tt_error_demo as tt_number)) t)';
10 loop
11 fetch v_cursor into v_number;
12 exit when v_cursor%NOTFOUND;
13 dbms_output.put_line(v_number);
14 end loop;
15 close v_cursor;
16* end;
SQL> /
1
2
3
4
5
6
7
8
9
10
PL/SQL procedure successfully completed.

So, as I mentioned way up above, these issues really have reduced it down to a choice between inserting the values in a GTT and then using that GTT in an IN subquery SELECT, or just to revert back to the IN lists as they were.

On the GTT front, we can bulk insert the values for the IN list from the application into the GTT using a simple procedure which accepts an associative array (like pkg_types.aa_number above).
One important note is that the GTT as to be created as “ON COMMIT PRESERVE ROWS”, otherwise the application will raise error “ORA-08103: object no longer exists”. This rings a bell from previous experiences using a JDBC app.

However this then requires another sort of workaround to delete the rows in the GTT as the first step in the procedure which bulk inserts the rows….

So, this leaves it all in a funny place where you’ve got to weigh up the evils of all the IN lists with the inefficiency, phaffing around and the general “bad smell” of the GTT approach.

It also leaves two open questions. Firstly, should we have another look at a higher level at the application design to reconsider why these IN lists are being constructed / are required in the first place? And secondly, when will ODP.NET provide the same support for Oracle UDTs as it does for JDBC?

Varying IN lists – part III

… continued …

OK, so the varying in list solution is not working for binds where the length > 4000 characters and ODP.NET does not seem to support Oracle collections to the same extent as JDBC (although some results on a Google search indicate that this may have been present in a Beta release but withdrawn). What are the alternatives?

We can return to the previous situation with many similar SQL statments with different length IN lists stuffing up the shared pool.

We could use a routine just before the select in the application to insert the previous IN list argument values into a GLOBAL TEMPORARY TABLE and then change the SQL so that the IN list subquery selects from that. It’s not nice, it’s not the ideal solution but it’s a possibility. An alternative is to the GTT is to use a global variable in a package. Not dissimilar to the GTT approach. Both have a nasty “smell” about them.

In both cases, the general approach is that the application would first have to insert values into the GLOBAL TEMPORARY TABLE or into the global packaged variable (either individual values or in the latter, using the ODP.NET support for associative arrays). Then we would write a query that would have a subquery to either select from the GTT or call a function which would change the associative array to a SQL type.

It sounds a bit daft whichever the method.

(Incidentally I lose track of what I’m meant to call things. Associative arrays used to be called index-by tables which used to be called PLSQL tables. These are part of the Oracle collections framework which includes SQL User Defined Types (UDTs)? That latter are what we need to use in SQL using the TABLE and CAST operators and those are what ODP.NET does not support.)

An initial runstats examination shows that the packaged variable approach is more scalable. This is mainly because a packaged variable is a memory structure whereas inserts into a GTT generate some redo and so runstats picks up on that. In terms of clock speed, the packaged variable approach seems slightly faster.

Hopefully, I will expand this a bit later with proper example code.

Varying IN lists – part II

I have blogged before about a solution to varying in lists being issued by an application.

The problem related to hard parsing – when these varying in lists used literals rather than binds (resolved by the cursor_sharing paramter) – and to shared pool usage – with every distinct number of binds in an in list requiring a shared pool entry (or parse if absent therefrom).

Having gently guided the application developers down the path of this type of approach, I was feeling pretty pleased with progress. However, two problems have subsequently presented themselves which underline why the initial approach was taken and why the standard solutions approach is not a silver bullet in all circumstances.

One of the applications concerned uses ODP (Oracle Data Provider for .NET). This is one of those situations where my understanding (or lack thereof) leaves me feeling very vulnerable.
ODP sits on top of OCI in a way that is, at this time, beyond my comprehension in terms of driver internals.

The varying in list “design pattern” uses a single delimited string as a single bind variable. As a result, no matter the length of the single bind, there is only a single statement per x binds for that base bit of SQL.

However, OCI has a limt of 4000 for a VARCHAR2 argument. Therefore, depending on the length of each argument in the delimited string, we are severely limited in the number of arguments that can be passed in.

This brings me back to an old bug raised against the current application. In the old varying IN list approach, the application was limited to something like 1000 arguments in the IN list. There are obvious question marks against the application design of any application which is dynamically building such IN lists – where are the arguments in the IN lists coming from? What is the application object to relational mapping such that this is happening? Can we present alternative structures / entities to avoid these scenarios? etc.

However, by pushing the varying IN list solution, the limit of 1000 arguments is now considered a “nice-to-have”. Because at 8 digits, the maximum number of arguments is now closer to 250!

As the length of the single bind variable reaches the 4000 limit, the error raised is “ORA-01460: unimplemented or unreasonable conversion requested”. We have tried a LONG and CLOB alternative and both are met with the same result (although an erroneous implementation of a workaround can never be ruled out).

One of the frustrations here is that the application developers would love to use a more appropriate interface. The natural choice, IMHO, is to provide some sort of array of the arguments concerned. If we were using JDBC, then using proper Oracle collections (CREATE TYPE…. AS TABLE OF) and the appropriate JDBC structures would be fantastic.

However, for some reason, ODP does not yet support the same functionality. According to the documentation, ODP.NET supports associative arrays/index-by tables/plsql tables but not SQL types. Which raises the question, why has this functionality been absent from ODP.NET for so long?

… more …

Cursor_sharing, mdsaora, ora-00937 part II

Previously, I mentioned an issue with the setting of the CURSOR_SHARING parameter to FORCE when combined with an application using a version of the MSDAORA driver.

This had resulted in an ORA-00937 error – “not a single-group group function”. The reason being that the driver was adding a ROWID column into the column list of a SELECT statement which did a GROUP BY. Not a nice side effect.

I have noted from my blog stats that there is a consistent trickle of people arriving at my blog that, given their search engine terms used to find me, seem to be having the same problem.

With this in mind, I can confirm that a recent change in the application to use the Oracle OLEDB driver has resolved this issue. However, I don’t unfortunately have visibility of the detailed changes made in the application to give any insight into this.

Driver palavers – Missing data bind variable: Java -> Web Objects -> JDBC -> Oracle 8i

Another day, another interesting investigation.

Today, we had to investigate a strange hanging of the application on executing a certain bit of SQL.

On initial inspection, there didn’t seem much to go wrong in this application-generated SQL:

SELECT t0.languageID, t0.CREATED, t0.DOB, t0.PUB, t0.STORYSOID, t0.sectionID, t0.ID, t0.lastModified, t0.CURRENTOWNERID, t0.lifeSpan, t0.OrigPubTime, t0.newsID, t0.STATUS, t0.pubTime, t0.ORIGINATORID, t0.origid, t0.EDITSOID, t0.dobuserid, t0.description
FROM News t0
WHERE (t0.lastModified > :1
AND t0.STATUS = :2)
ORDER BY t0.lastModified DESC

To me, the first step that occurred was to do a quick explain plan (version 8i) substituting in some sensible variables:

1 SELECT t0.languageID……
2 FROM News t0
3 WHERE (t0.lastModified > TO_DATE(’01/01/2007′,’DD/MM/YYYY’)
4 AND t0.STATUS = 3)
5* ORDER BY t0.lastModified DESC
SQL> /

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=1236)
1 0 TABLE ACCESS (BY INDEX ROWID) OF ‘NEWS’ (Cost=3 Card=1 Bytes=1236)
2 1 INDEX (RANGE SCAN DESCENDING) OF ‘NEWSLASTMODIFIEDINDEX’ (NON-UNIQUE) (Cost=2 Card=1)

Incidentally, I did not realise that you couldn’t set up a SQL*Plus variable with a date datatype.

Anyway, the above plan did not seem unreasonable.

Next step was to have a quick look at the SGA to see if that could reveal much. So, the shared pool was flushed and some example SQL run via the application and via SQL*Plus . Sure enough – there was the statement. However, the strange thing was the high level of consistent gets.

A light bulb moment – sure the application might look like it was hanging if it was doing a lot of IO. However, the value being passed in for the date was such that no rows would be found. And we had a quick discussion about the mapping of Oracle dates, Java dates, Oracle Timestamps and Java Timestamps and recent version changes.

Curious. Initial thoughts revolved around date conversion. Maybe something was wrong with the date format and the value passed in such that we were matching too many rows.

A quick step back. This application is Java with WebObjects and JDBC Thin driver – a recent 10g version, I believe.

So, I decided to cut to the chase and do an extended trace on event 10046 to see if that could reveal more.

First step was to create a LOGON trigger to capture the binds and waits:


CREATE OR REPLACE TRIGGER trig_ald_set_session_trace
AFTER LOGON ON DATABASE
DECLARE
BEGIN
--
IF USER='USER1'
THEN
--
EXECUTE IMMEDIATE 'ALTER SESSION SET events ''10046 trace name context forever, level 12''';
--
END IF;
--
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

Eventually, after unsuccessfully testing this a couple of times, the constant potential danger of the EXCEPTION WHEN OTHERS THEN NULL clause (not one that I normally use, honest guv) was revealed as the DBA type user that I was using did not have direct ALTER SESSION privilege.

So, once rectified, the trigger was enabled and we were able to run the application to see what would happen:

PARSING IN CURSOR #1 len=314 dep=0 uid=48 oct=3 lid=48 tim=1008224631 hv=3758786868 ad='90be9478'
SELECT t0.languageID, t0.CREATED, t0.DOB, t0.PUB, t0.STORYSOID, t0.sectionID, t0.ID, t0.lastModified, t0.CURRENTOWNERID, t0.lifeSpan, t0.OrigPubTime, t0.newsID, t0.STATUS, t0.pubTime, t0.ORIGINATORID, t0.origid, t0.EDITSOID, t0.dobuserid, t0.description FROM News t0 WHERE (t0.lastModified = :1 AND t0.STATUS = :2)
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=1008224631
BINDS #1:
bind 0: dty=180 mxl=11(11) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0 size=36 offset=0
bfp=01a6fac8 bln=11 avl=07 flg=05
value=
Dump of memory from 0x1A6FAC8 to 0x1A6FACF
1A6FAC0 786B0119 0B290900 [xk...)..]
bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0 size=0 offset=12
bfp=01a6fad4 bln=22 avl=02 flg=01
value=11
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1008224632
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1952673792 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 3 p1=1952673792 p2=1 p3=0
WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=5343 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=4 p2=17552 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=85979 p3=1
...... lots more db file sequential reads .......

I thought this bit was a little strange:

BINDS #1:
bind 0: ……
value=

My initial thoughts were that this should raise an ORA-1008: Not all variables bound. However, clearly we were past that stage.

So, at the moment my best reading between the lines revolves around the optimizer doing some sort of predicate pruning given that bind variable :1 has been lost – hence the full table scan on the table concerned and an awful lot of rows being fetched.

The application developer rebuilt the app using the 8i driversand sure enough, no lost bind variable. Also interestingly, the trace of the 8i driver session revealed something that was absent from the previous traced session – the checking of NLS settings.

PARSING IN CURSOR #1 len=43 dep=0 uid=48 oct=42 lid=48 tim=1008340170 hv=1567373371 ad='904ffb90'
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN'
END OF STMT
....
PARSING IN CURSOR #1 len=43 dep=0 uid=48 oct=42 lid=48 tim=1008340170 hv=3073757550 ad='904f8ef8'
ALTER SESSION SET NLS_TERRITORY = 'AMERICA'
END OF STMT
....
PARSING IN CURSOR #1 len=76 dep=0 uid=48 oct=3 lid=48 tim=1008340170 hv=3617023014 ad='904f7e04'
SELECT VALUE FROM NLS_INSTANCE_PARAMETERS WHERE PARAMETER ='NLS_DATE_FORMAT'
END OF STMT
....
PARSING IN CURSOR #1 len=314 dep=0 uid=48 oct=3 lid=48 tim=1008340180 hv=3758786868 ad='90be9478'
SELECT t0.languageID, t0.CREATED, t0.DOB, t0.PUB, t0.STORYSOID, t0.sectionID, t0.ID, t0.lastModified, t0.CURRENTOWNERID, t0.lifeSpan, t0.OrigPubTime, t0.newsID, t0.STATUS, t0.pubTime, t0.ORIGINATORID, t0.origid, t0.EDITSOID, t0.dobuserid, t0.description FROM News t0 WHERE (t0.lastModified = :1 AND t0.STATUS = :2)
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1008340180
BINDS #1:
bind 0: dty=12 mxl=07(49) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size=32 offset=0
bfp=01a59f40 bln=07 avl=07 flg=05
value="1/25/2007 10:59:23"
bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size=0 offset=8
bfp=01a59f48 bln=22 avl=02 flg=01
value=11
EXEC #1:c=2,e=1,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1008340181
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1952673792 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1952673792 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1952673792 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1952673792 p2=1 p3=0
FETCH #1:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=0,dep=0,og=4,tim=1008340182
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1952673792 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1952673792 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1952673792 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 1 p1=1952673792 p2=1 p3=0
XCTEND rlbk=0, rd_only=1
WAIT #0: nam='SQL*Net message to client' ela= 0 p1=1952673792 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 0 p1=1952673792 p2=1 p3=0
STAT #1 id=1 cnt=0 pid=0 pos=0 obj=2498 op='TABLE ACCESS BY INDEX ROWID NEWS '
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=2737 op='INDEX RANGE SCAN '
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1952673792 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 3 p1=1952673792 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1952673792 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1952673792 p2=1 p3=0

So, there could be something environmental that’s not correct – for example local settings, driver config, etc – or I suppose it could be nothing but different behaviour between the drivers. Gut feeling is that I wouldn’t rule out the former.

So, for me, a number of searches to do starting at Metalink, then Jonathan Lewis’s sites and a quick flick through his book, then Google.

Although this is now no longer a pressing problem, this is something we will need to look into again some day soon en route to 10g upgrade. For example, is this an incompatibility between the application and the 10g drivers, or an issue between the 10g drivers and an 8i database etc. Plus, of course, it could be to do with the behaviour of dates and timestamps between Java and Oracle and the compatibility between, and changes to, aforementioned datatypes between different versions of said technologies.

Cursor_sharing, mdsaora, ora-00937

On top of the Oracle database, most of my application experience has revolved around either Vignette or Java.

Therefore, when we had a problem the other day (in a test system, of course…) it was good to gain a little insight into some different drivers.

What did we do before Google? I can hardly remember.

Anyway, in order to try to boost the performance of part of the database, I thought I would sneak in an after logon trigger for a particular application that does not use bind variables. The trigger would fire for a particular connecting user and do an “ALTER SESSION” to set the “CURSOR_SHARING” parameter to “FORCE”.

Most of the application was fine. However, a small legacy bit of code was raising an Oracle error from within the VB layer:

“ORA-00937: not a single-group group function.”

From metalink (although it wasn’t an immediately obvious search to find it there) and from a single entry on google groups, it turns out that there is an issue with the microsoft mdsadora driver. The metalink article talked also talked about OCI 7 and OCI 8.

Apparently and allegedly, if the cursor_sharing parameter is set to anything other than “EXACT”, then the driver appends a column into the select clause such that something like “SELECT count(*) FROM table” becomes “SELECT count(*), ROWID from table” … and the inevitable error arises. So, no CURSOR_SHARING setting then. Damn….

Follow

Get every new post delivered to your Inbox.

Join 70 other followers