Hi all,
Hopefully someone can shed a little light on an issue that I'm currently having with an Oracle DB - I'm sure it's something simple!!
I've managed to recreate this in a sample, so here is the DB structure:
CREATE TABLE MyTable(
ixMyTable NUMBER,
clobData CLOB
)
/
CREATE OR REPLACE PACKAGE PKGTEST
AS
PROCEDURE DoSomething(
cur_OUT OUT SYS_REFCURSOR
);
END PKGTEST;
/
CREATE OR REPLACE PACKAGE BODY PKGTEST
AS
PROCEDURE DoSomething(
cur_OUT OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN cur_OUT FOR
SELECT ixMyTable, clobData
FROM MyTable;
END;
END PKGTEST;
/
GRANT EXECUTE ON PKGTEST TO TEST_ROLE
/
BEGIN
FOR i IN 1 .. 7000 LOOP
insert into mytable values (i, TO_CLOB('123456'));
END LOOP;
END;
/
Extra info:
Schema owner is TEST_SCHEMA
User is CARL
CARL has the TEST_ROLE role
Given the database setup as above, I have a C# test app that uses the standard System.Data.OracleClient.OracleCommand etc. in order to execute PKGTEST.DoSomething and throw the results into a datagrid (DevExpress).
Pretty sure that the grid is irrelevant here, as we experience the same issue through c++ using the open source OTL (not my department, fortunately).
OK, to the problem....
Time from beginning until the grid is populated is ~35-40s, ouch.
However, if I do the following:
GRANT SELECT ON MyTable TO TEST_ROLE
/
and then perform the query again, it takes ~5-6s.
It seems to me that this has something to do with privileges etc., but I'm not quite sure why it still actually works both ways??
Just to throw something else into the pot, if I change the procedure to
SELECT ixMyTable, TO_CLOB(TO_NCLOB(clobData))
FROM MyTable;
Then the time is ~5-6s, with or without the extra SELECT privilege.
Any pointers or straight out solutions would be much appreciated!
Edit:
OS is Vista x86 Business
Oracle Server is 10.2.0.1
Oracle Client is 10.2.0.3
Edit:
As suggested, I've tried changing from the MS OracleClient to the ODP.NET and this does speed up as required.
Unfortunately, the C# app that was being affected was just an internal app that is used for viewing tables / running SPROCS etc.
Our main deliverable is the C++ app that uses OTL (http://otl.sourceforge.net/otl3_intro.htm) for database access. This is not really something that can be changed at this time, and so I would really like to understand the reasons for the difference, without having to throw gratuitous GRANT SELECTs around willy-nilly.
If lack of SELECT privilege caused a complete failure then I could probably live with this, but lack of SELECT appears to be causing some slower route for accessing the CLOB data.
I've marked up the 3 answers - thanks for those - but I could really do with a reason, so I'll add a bounty to this.
P.S. We really wanted to go with OCCI at the outset for our C++, but as Oracle are always supporting a version of the IDE before current we couldn't get it to play nicely with our Visual Studio 2008.