StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POReading CLOB column is slow
text
Body
copied!<p>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!!</p> <p>I've managed to recreate this in a sample, so here is the DB structure:</p> <pre><code>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; / </code></pre> <p>Extra info:</p> <p>Schema owner is TEST_SCHEMA</p> <p>User is CARL</p> <p>CARL has the TEST_ROLE role</p> <p>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).</p> <p>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).</p> <p>OK, to the problem....</p> <p>Time from beginning until the grid is populated is ~35-40s, ouch.</p> <p>However, if I do the following:</p> <pre><code>GRANT SELECT ON MyTable TO TEST_ROLE / </code></pre> <p>and then perform the query again, it takes ~5-6s.</p> <p>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??</p> <p>Just to throw something else into the pot, if I change the procedure to </p> <pre><code>SELECT ixMyTable, TO_CLOB(TO_NCLOB(clobData)) FROM MyTable; </code></pre> <p>Then the time is ~5-6s, with or without the extra SELECT privilege.</p> <p>Any pointers or straight out solutions would be much appreciated!</p> <p>Edit:</p> <p>OS is Vista x86 Business</p> <p>Oracle Server is 10.2.0.1</p> <p>Oracle Client is 10.2.0.3</p> <p>Edit:</p> <p>As suggested, I've tried changing from the MS OracleClient to the ODP.NET and this does speed up as required. </p> <p>Unfortunately, the C# app that was being affected was just an internal app that is used for viewing tables / running SPROCS etc. </p> <p>Our main deliverable is the C++ app that uses OTL (<a href="http://otl.sourceforge.net/otl3_intro.htm" rel="nofollow noreferrer">http://otl.sourceforge.net/otl3_intro.htm</a>) 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.</p> <p>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.</p> <p>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> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload