Home » SQL & PL/SQL » SQL & PL/SQL » Question on RefCursor result count (Oracle 10g)
Question on RefCursor result count [message #342249] Thu, 21 August 2008 15:28 Go to next message
complan_mango
Messages: 2
Registered: August 2008
Junior Member
In a .NET environment, user submits a search page. Based on the input parameters, a stored procedure is called to return a Ref Cursor. In that procedure, we are doing the following:

(1) Run the query to check whether the total record count is > 300. If yes, the return to the search page and ask the user to add more search conditions to restrict the query results count to < 300.
(2) If the query results count is < 300, then return the Ref Cursor with the results set.

In this process, the whole query (processing 1+ million records) is run 2 times to process, which is causing Performance Issue.

Is there any way in the Ref Cursor, to check the COUNT so that we can restrict the query running 2 times. Ref Cursor is opened in the .NET and not in the stored procedure.
Re: Question on RefCursor result count [message #342253 is a reply to message #342249] Thu, 21 August 2008 15:53 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
There is no way to count the rows before they are fetched. (Or an additional count is run first like you do now)

However, maybe you could just fetch the first 300 rows in the .net program (and perhaps add an "and rownum < 301" to the query itself ) and then print the "More than 300 results returned" info when row 300 is returned.

Re: Question on RefCursor result count [message #342500 is a reply to message #342249] Fri, 22 August 2008 09:42 Go to previous messageGo to next message
complan_mango
Messages: 2
Registered: August 2008
Junior Member
Thank You for your quick response.

I was also thinking of this approach only.
Re: Question on RefCursor result count [message #342506 is a reply to message #342500] Fri, 22 August 2008 09:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Keep in mind though that adding a "where rownum < 301" has consequences for your resultset.
e.g. don't expect to get the top 300, just for free. Chances are that you might have to do some extra work for that. That in return will cost performance...
Not as straightforward as it may seem.
Re: Question on RefCursor result count [message #342510 is a reply to message #342506] Fri, 22 August 2008 09:59 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi Sir Frank,
Just want to ask what do you mean by:

e.g. don't expect to get the top 300, just for free. Chances are that you might have to do some extra work for that. That in return will cost performance...

If you're not busy, could you give a sample? or just a flow? Thanks!
Re: Question on RefCursor result count [message #342513 is a reply to message #342510] Fri, 22 August 2008 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It means that
select ... from ... where .... and rownum < 301
gives you the first 300 rows but not the top 300 ones.

To get the top 300 ones you have to add a bit:
select * from (select ... from ... where ... order by whatyouwant) where rownum < 301

regards
Michel
Re: Question on RefCursor result count [message #342517 is a reply to message #342513] Fri, 22 August 2008 10:55 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi Michel, sorry if I didn't understand it already, I'm a bit confused on the "it will cost performance" part by just encapsulating it with an outer select?
Re: Question on RefCursor result count [message #342519 is a reply to message #342517] Fri, 22 August 2008 11:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What will cost is the "order by" as you have to sort the whole result set and will not get the answer before it is sorted.

Of course, if you already sorted your result set, this will not cost more.

Regards
Michel

[Updated on: Fri, 22 August 2008 11:02]

Report message to a moderator

Re: Question on RefCursor result count [message #342520 is a reply to message #342519] Fri, 22 August 2008 11:07 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thank you very much! Is that the part where it puts the resultset on the temp tablespace and sort it there? If it is will the "sorted" resultset be coming from the temp tablespace then to the application? Or if not, then will it put the sorted resultset from temp space to "memory" then from there pass it to the application? (sorry for too many questions)
Re: Question on RefCursor result count [message #342525 is a reply to message #342520] Fri, 22 August 2008 11:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is that the part where it puts the resultset on the temp tablespace and sort it there?

Yes, it is this part.

Quote:
If it is will the "sorted" resultset be coming from the temp tablespace then to the application?

No, this part costs less than the same one when the result set is not sorted (not stored in temp tablespace). Reading from the temp tablespace bypasses buffer cache (direct reads) and so is faster.

Regards
Michel
Re: Question on RefCursor result count [message #342531 is a reply to message #342525] Fri, 22 August 2008 12:27 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Quote:
No, this part costs less than the same one when the result set is not sorted (not stored in temp tablespace).


Didn't quite get that. What happens to the sorted resultsets in the temp tablespace? Thanks!
Re: Question on RefCursor result count [message #342533 is a reply to message #342531] Fri, 22 August 2008 12:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When the result set is sorted for a query it is stored in a private temp segment ready to be fetched.
When the result set is not sorted (at any step of the query execution) it is only retrieved from original file/cache blocks or rebuilt from rollback/undo information if it has been modified at fetch time. This takes longer and costs more than just retrieving rows from temp segment.

Regards
Michel
Re: Question on RefCursor result count [message #342537 is a reply to message #342533] Fri, 22 August 2008 12:45 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks again for answering! But now I'm a bit confused again.

Quote:
What will cost is the "order by" as you have to sort the whole result set and will not get the answer before it is sorted


The "ordering" part results in sorting which results to added "cost"?


Quote:
When the result set is not sorted (at any step of the query execution) it is only retrieved from original file/cache blocks or rebuilt from rollback/undo information if it has been modified at fetch time. This takes longer and costs more than just retrieving rows from temp segment.


No sorting also is "costly". Does that mean either way it will have bad effect?
Re: Question on RefCursor result count [message #342540 is a reply to message #342537] Fri, 22 August 2008 13:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorting does what no sorting does plus sort.

In short (a real short cut), when you don't sort you have:
a1) query execution
a2) fetch by the client from files (through cache)
When you sort you have:
b1) query execution
b2) internal fetch from file (through cache) to temp segment
b3) sort in temp segment
b4) fetch by the client from temp segment (bypass cache)

b4 is faster than a2 but to get b4 you also have to execute b2 and b3. (b2 is about the same thing than a2 but without roundtrips with client.)

Regards
Michel

Re: Question on RefCursor result count [message #342548 is a reply to message #342540] Fri, 22 August 2008 14:02 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thank you very much again! just to review =)

sorting is less cost than no sort in the part of finally returning the result sets to the client (a2 & b4).

Does the passing of the "actual data" meaning the actual rows from temp segment (sorted) or "cache" (not sorted) to the "end-user or application" also involves many mini-steps? Or is it already of no importance to know how oracle passes the rows to the application, and just always concentrate on the things being done on the "explain plan" part?

hope I've explained it clearly. Thanks again =)
Re: Question on RefCursor result count [message #342549 is a reply to message #342548] Fri, 22 August 2008 14:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know if I understand you but passing data as much as possible (in reasonable limit) at each step is better.
I mean fetching rows one hundred by one hundred is far far better than one row per one row. client/server roundtrips are very expensive and in the "nosort" case may lead to reconstruct the block each time you have to fetch a new row.

Here's a test with a table of 10000 rows (I removed non useful lines in autotrace):
SQL> set arraysize 2
SQL> select * from test;

Elapsed: 00:00:07.06

Statistics
----------------------------------------------------------
       5021  consistent gets
     670082  bytes sent via SQL*Net to client
      55368  bytes received via SQL*Net from client
       5001  SQL*Net roundtrips to/from client
      10000  rows processed

SQL> set arraysize 10
SQL> select * from test;

Elapsed: 00:00:01.06

Statistics
----------------------------------------------------------
       1036  consistent gets
     174141  bytes sent via SQL*Net to client
      11488  bytes received via SQL*Net from client
       1001  SQL*Net roundtrips to/from client
      10000  rows processed

SQL> set arraysize 100
SQL> select * from test;

Elapsed: 00:00:00.04

Statistics
----------------------------------------------------------
        136  consistent gets
      62901  bytes sent via SQL*Net to client
       1588  bytes received via SQL*Net from client
        101  SQL*Net roundtrips to/from client
      10000  rows processed

SQL> set arraysize 1000
SQL> select * from test;

Elapsed: 00:00:00.03

Statistics
----------------------------------------------------------
         46  consistent gets
      55341  bytes sent via SQL*Net to client
        598  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
      10000  rows processed

SQL> set arraysize 5000
SQL> select * from test;

Elapsed: 00:00:00.02

Statistics
----------------------------------------------------------
         38  consistent gets
      70349  bytes sent via SQL*Net to client
        510  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
      10000  rows processed

You can see the evolution of cost (consistent gets) and time (elapsed) when I increase the number of rows returned at each fetch.

Regards
Michel
Re: Question on RefCursor result count [message #342552 is a reply to message #342549] Fri, 22 August 2008 14:55 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thank you very much! It's much clearer now. On SQLPlus we affect on how many "rows at a time" should be pass to it by the arraysize variable right? On other applications not on SQLPlus, what should we consider that might affect the consistent gets/time elapsed like the arraysize? Is this the "rownum filter" part? Thanks again for the sample.

[Updated on: Fri, 22 August 2008 14:56]

Report message to a moderator

Re: Question on RefCursor result count [message #342597 is a reply to message #342552] Sat, 23 August 2008 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Each language has its procedure to set "array size".

For instance:

* in JDBC:
((OracleConnection)conn).setDefaultRowPrefetch (arraySize);
((OracleStatement)stmt).setRowPrefetch (arraySize);

* In PL/SQL:
fetch query bulk collect into valueTab limit arraysize;

* In Pro*C:
EXEC SQL ARRAYLEN host_array (arraysize) [EXECUTE];

And so on.

Regards
Michel
Re: Question on RefCursor result count [message #342710 is a reply to message #342597] Sun, 24 August 2008 09:43 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Quote:
* In PL/SQL:
fetch query bulk collect into valueTab limit arraysize;


Thank you very much! So that was they say the cost in the normal fetching of one row at a time, and that BULK COLLECT would improve it. The costly part is the client/server roundtrips. Does this also include the "context switch between sql and pl/sql"?
Re: Question on RefCursor result count [message #342714 is a reply to message #342710] Sun, 24 August 2008 10:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In the case of PL/SQL client/server roundtrips are indeed replaced by context switches between the 2 engines.

Regards
Michel
Re: Question on RefCursor result count [message #342744 is a reply to message #342249] Sun, 24 August 2008 17:22 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Adding my two cents:

In my experience, this limiting of fetched rows to a predetermined maximum is a common practice. How many users are really willing to wade through 3000 rows? 30 is more like it. I see people (and have in the past myself) land on one of four options:

1) select ... from ... where ... and rownum <= 301;

2) select count(*) from (your query here and hope oracle skips table accesses where possible)

3) select * from data_of_type_1 into collection variable
   if collection_variable.count < 300 then
      select * from data_of_type_2 into another collection_variable
      if ...

4) precompute or estimate query counts

The idea of #1 is what has been discussed here. As pointed out by other posters, this works best when it is possible to stop fetching rows after you have fetched the limit. It works less well when you have some kind of "SORT" operation (ORDER BY, GROUP BY, ANALYTIC, "ALL ROWS" STYLE QUERY PLAN OPTIMIZATION, ETC.) that essentially requires you to fetch all rows anyway.

The idea of #2 works on the principle that Oracle is smart and when it see the count(*) knows you do not actually want any of the columns you are fetching. In response Oracle can often construct a query plan that will visit only indexes, thus not requiring the ever so common TABLE ACCESS BY ROWID step. The net effect is often a very fast count. However, this is not a given. There are many reasons that this might not happen.

The idea of #3 is that the "ORDERING" of rows is often based on some kind of preference for groups such that one large query may actually be the combination of several smaller queries such that each next query is less important than the previous one. For example, assume the data can be got using 10 independent queries. Query 1 returns 150 rows, Query 2 returns 100 rows, query 3 returns 51 rows. You are now past the 300 limit so don't bother with queryies (4-10).

The idea of #4 is that it is somtimes possible to invent a COUNTING SCHEME where in one can either precount rowcounts for known query sets, and/or estimate the number of rows to be returned by a query. IBM was big on this in the day with its communications theory formulae. Most business systems do not do this, but some systems can. Think search engines for example.

Quote:
Results 1 - 10 of about 321,000,000 for mom

Wonder how they know 321 million (more or less)?

Maybe there are other good ideas, but I can't think of them at the moment. The last big project I was controling did a combination of #1,#2,#3.

Good luck, Kevin
Previous Topic: problem when passing values to Execute immediate
Next Topic: SQL%ROWCOUNT don't give the true value !!!
Goto Forum:
  


Current Time: Fri Dec 09 04:01:16 CST 2016

Total time taken to generate the page: 0.22456 seconds