Home » RDBMS Server » Performance Tuning » Fetching Cursor Performance
Fetching Cursor Performance [message #288054] Fri, 14 December 2007 09:15 Go to next message
Agus211
Messages: 39
Registered: September 2007
Member
Hi, i'd like to know if you could help me with this problem I'm having:

I have a Perl script that call's a PL/SQL package function that returns a cursor containing about 500 rows(VARCHAR2(50),NUMBER,VARCHAR2(50),VARCHAR2(50)).

When the database returns that cursor, I fetch the cursor and print it in a webpage. The problem is that it's taking a few seconds(like 4 or 5) to retrieve the first row.

I checked if the queries cost is too high, but it's not.

Can you help me?.

Thank you.

[Updated on: Fri, 14 December 2007 09:17]

Report message to a moderator

Re: Fetching Cursor Performance [message #288063 is a reply to message #288054] Fri, 14 December 2007 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not with the information you gave.
Read OraFAQ Forum Guide to know what have to post and How to Identify Performance Problem and Bottleneck to know how to solve your problem.

Regards
Michel
Re: Fetching Cursor Performance [message #288089 is a reply to message #288054] Fri, 14 December 2007 14:30 Go to previous messageGo to next message
Agus211
Messages: 39
Registered: September 2007
Member
Hi, sorry for not giving enough data:

This is the function that my Perl script is calling:

   FUNCTION get_all (
      v_word    IN   VARCHAR2,
      v_catalog   IN   VARCHAR2,
   )
      RETURN TYPES.cursortype
   AS
      l_cursor     TYPES.cursortype;
      v_ouid       INT;
      d_ordenado   VARCHAR2 (10);
   BEGIN
      SELECT ID
        INTO v_ouid
        FROM catalogs
       WHERE catalog_name = v_catalogs;

      OPEN l_cursor FOR
        SELECT *
          FROM (SELECT /*+ FIRST_ROWS(50) INDEX(IDX_DATE_NEWS) */
                    score (1) AS RANK, ID,
                    TO_CHAR (date_news, 'dd/mm/yyyy'), CODE_NEWS
                FROM newsfiles
                WHERE ouid = 1
                AND (contains (txt_index, v_word, 1) > 0)
                ORDER BY date_news DESC)
             WHERE ROWNUM < 201;
  
      RETURN l_cursor;

      CLOSE l_cursor;
   END get_all;


This is the explain plan from the main query of this function:

SELECT STATEMENT,5,0
COUNT STOPKEY
VIEW ,5,0
SORT ORDER BY STOPKEY
FILTER
TABLE ACCESS BY INDEX ROWID,ARCHIVO,373,15722670
BITMAP CONVERSION TO ROWIDS
BITMAP AND 
BITMAP INDEX SINGLE VALUE,IDX_OUID
BITMAP CONVERSION FROM ROWIDS
SORT ORDER BY
DOMAIN INDEX ,IDXTXT,122,1

This is the Perl loop that prints the cursor:
    $sel="BEGIN :cursor := pkgfiles.get_all(:word,:catalog,:order); END;";
	$labelsearch = "resume = '".$qall."' or insertado = '".$qall."' or theme = '".$qall."'";
        $sth1 = $dbh->prepare($sel);
        $sth1->bind_param(":word",$qall); 
        $sth1->bind_param(":catalog",$current_catalog ); 
        $sth1->bind_param(":order",$orden);

if (length($sel) > 1) {
        my $sth2;
        $sth1->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );
        $sth1->execute;

	while (@selcolumns = $sth2->fetchrow_array) {

		$cantcol = @selcolumns;
		$coldate = $selcolumns[$cantcol - 2];
		$colid = $selcolumns[$cantcol - 3];
		$colcod = $selcolumns[$cantcol - 1];
		$colrank = 0;
		if ($cantcol -3 >=0){$colrank = $selcolumns[$cantcol - 3];}
		if ($cantcol -4 >=0){$colrank = $selcolumns[$cantcol - 4];}
		if ($cantcol -5 >=0){$colrank = $selcolumns[$cantcol - 5];}
		print "<tr>\n";
		print "<td class=\"estilo_chico2_bold\"><img src='".$repositorio."prod/general/barrah.png' width='".$colrank."' height='8' title='".$colrank."'>\n";
		print "<br>".$coldate." - ";
		print "<a href='ver.pl?ID=".$colid."&WORD=".$qall."&SESSIONID=".$sessionID."&WORDAV=".$qav."' target='NewsFiles' onmouseover=\"javascript:parent.FILESNEWS.location.href='ver.pl?ID=".$colid."&WORD=".$qall."&SESSIONID=".$sessionID."&WORDAV= ".$qav."'\">".$colcod."</a></td></tr>\n";
        
	}



Thanks in advance.
[formatted by moderator]

[Updated on: Sat, 15 December 2007 22:30] by Moderator

Report message to a moderator

Re: Fetching Cursor Performance [message #288090 is a reply to message #288054] Fri, 14 December 2007 14:38 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Hi, sorry for not giving enough data:
Again, still data is lacking; like Oracle version to 4 decimal places.

It appears you missed the part about using <code tags>.

V9 Oracle had MAJOR performance problems when it came to BIT MAPPED indexes in an OLTP environment.
The BM Indexes needed frequent rebuilding to maintain decent performance.

Are statistics current for all involved tables & indexes?

Re: Fetching Cursor Performance [message #288102 is a reply to message #288054] Fri, 14 December 2007 16:30 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
You will have to do some creative tuning to figure out where your problem lives.

1) are you fetching a large result set, ordering it and then taking only the first 200 rows?

2) is your context index behaving badly?

3) is it your application that is moving data poorly?

I suggest possibly the following course:

1) run the query without the context index predicate for a timing
2) run the query again with the context index predicate for a second timing
3) get row counts of the number of rows being returned before you apply the rownum filter
4) read Tom Kyte for information on how to combine context indexes and other data elements to create a sort of "super" index for a special need

I wager you just read #4 and figure that is where you should start your search. This of course would be a newbie mistake. You need first to understand better where your problem is before you look to find a solution for it.

Good luck, Kevin
Re: Fetching Cursor Performance [message #288462 is a reply to message #288102] Mon, 17 December 2007 11:31 Go to previous messageGo to next message
Agus211
Messages: 39
Registered: September 2007
Member
Hi, thanks for replying,

1) Are you fetching a large result set, ordering it and then taking only the first 200 rows?
Yes. Usually the result set has like 4000 rows and then I take only the first 200.
2) Is your context index behaving badly?
No it isn't.
3) Is it your application that is moving data poorly?
No it isn't.

I suggest possibly the following course:

1) run the query without the context index predicate for a timing
This is the time I've got(printing only 9 records):

BEGINS SETTING SESSION ----> TIME: 17/12/2007 12:48:32,375974000
FINISHES SETTING SESSION ----> TIME: 17/12/2007 12:48:33,065943000 PM -03:00
BEGINS QUERYING ----> TIME: 17/12/2007 12:48:33,066064000 PM -03:00
ENDS QUERYING ----> TIME: 17/12/2007 12:48:33,192250000 PM -03:00
STARTS FETCHING CURSOR ----> TIME: 17/12/2007 12:48:33,192304000 PM -03:00
RECORD NUMBER: 1 ----> TIME: 17/12/2007 12:48:47,647739000 PM -03:00
RECORD NUMBER: 2 ----> TIME: 17/12/2007 12:48:47,647890000 PM -03:00
RECORD NUMBER: 3 ----> TIME: 17/12/2007 12:48:47,647965000 PM -03:00
RECORD NUMBER: 4 ----> TIME: 17/12/2007 12:48:47,648030000 PM -03:00
RECORD NUMBER: 5 ----> TIME: 17/12/2007 12:48:47,648091000 PM -03:00
RECORD NUMBER: 6 ----> TIME: 17/12/2007 12:48:47,648153000 PM -03:00
RECORD NUMBER: 7 ----> TIME: 17/12/2007 12:48:47,648220000 PM -03:00
RECORD NUMBER: 8 ----> TIME: 17/12/2007 12:48:47,648285000 PM -03:00
RECORD NUMBER: 9 ----> TIME: 17/12/2007 12:48:47,648423000 PM -03:00
FINISHES FETCHING CURSOR ----> TIME: 17/12/2007 12:48:47,648494000 PM -03:00
PL/SQL procedure successfully completed.
Elapsed: 00:00:16:44


2) run the query again with the context index predicate for a second timing
This is the result(it took less time to start fetching):

BEGINS SETTING SESSION ----> TIME: 17/12/2007 12:50:37,160609000 PM -03:00
FINISHES SETTING SESSION ----> TIME: 17/12/2007 12:50:37,316827000 PM -03:00
BEGINS QUERYING ----> TIME: 17/12/2007 12:50:37,316920000 PM -03:00
ENDS QUERYING ----> TIME: 17/12/2007 12:50:39,007746000 PM -03:00
STARTS FETCHING CURSOR ----> TIME: 17/12/2007 12:50:39,007810000 PM -03:00
RECORD NUMBER: 1 ----> TIME: 17/12/2007 12:50:41,625418000 PM -03:00
RECORD NUMBER: 2 ----> TIME: 17/12/2007 12:50:41,625521000 PM -03:00
RECORD NUMBER: 3 ----> TIME: 17/12/2007 12:50:41,625569000 PM -03:00
RECORD NUMBER: 4 ----> TIME: 17/12/2007 12:50:41,625625000 PM -03:00
RECORD NUMBER: 5 ----> TIME: 17/12/2007 12:50:41,625670000 PM -03:00
RECORD NUMBER: 6 ----> TIME: 17/12/2007 12:50:41,625722000 PM -03:00
RECORD NUMBER: 7 ----> TIME: 17/12/2007 12:50:41,625766000 PM -03:00
RECORD NUMBER: 8 ----> TIME: 17/12/2007 12:50:41,625811000 PM -03:00
RECORD NUMBER: 9 ----> TIME: 17/12/2007 12:50:41,625855000 PM -03:00
FINISHES FETCHING CURSOR ----> TIME: 17/12/2007 12:50:41,625901000 PM -03:00
PL/SQL procedure successfully completed.
Elapsed: 00:00:05:54

3) get row counts of the number of rows being returned before you apply the rownum filter
2160.

Do you have any ideas?.
Thank you.
Re: Fetching Cursor Performance [message #288464 is a reply to message #288054] Mon, 17 December 2007 11:36 Go to previous messageGo to next message
Agus211
Messages: 39
Registered: September 2007
Member
Hi, this is the database's information:

Product: Oracle Server - Enterprise Edition.
Product Version: 10.2.0.2.
Platform: Linux x86.
Re: Fetching Cursor Performance [message #288467 is a reply to message #288462] Mon, 17 December 2007 12:09 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
you need to run each test at least twice in order to filter out IO time for fecthing the same data off disk.

But, there is nothing unusual here. Your query needs to process all rows before it can return the first one. This is because you are doing a top-n query. Thus is it going to take longer to get the first row.

It is just as if you had done a create table as... and then did select * from table; after the table was created. The time to first row in this situation includes the time to create the table right? That is what is happening here. The nature of your query (the sort required for top-n) means you are in reality creating a partial result set either in memory or writing it to temp space, and the reading the result set in till you get 200 rows.

There is little that can be done to change your query timings unless you can work out some rewrite of the query that does not require the result set to be produced in its entirety or in part, before you can see the first row. Your best bet is to have a look at some kind of index that would do the sort desc on date_news and thus allow you to skip the sort operation and hence be able to find your 200 rows and then stop. The context index complicates this matter a bit, but maybe you can have a look at different indexes. Check out this page, it describes what I think is your problem. You can also do something similar with normal context indexes as you are using. Something like this might allow you to go straight to the 200 rows you want without doing an actual sort, especially if you include all columns needed by the query, in the index.

http://www.oracle.com/technology/products/text/htdocs/ctxcat_primer.html

In their example, they do this:

ctx_ddl.create_index_set('auction_set');
ctx_ddl.add_index ('auction_set', 'price');
ctx_ddl.add_index ('auction_set', 'start_time');

CREATE INDEX auction_index ON auction (item_desc)
INDEXTYPE IS CTXCAT
PARAMETERS ('INDEX SET auction_set');

WHERE CATSEARCH (item_desc, '(toy dog) | "live animal"', 'price < 100 order by start_time desc') > 0

I am betting you can do something similar with the hope that the index having your sort column, will let you skip doing the sort in which case you will have blindingly fast retrieval. But it is just a guess. You should see if you can add your sort column using add_index as "date_new desc" rather than "date_news" (not sure if this is valid). Even if you do have to do the sort, if you can include all columns in your index that the query needs, then you won't have to go to the table and that will reduce your time to first record as well, just not as much.

This is pretty advanced stuff, so do some research and lots of testing.

Good luck, Kevin
Re: Fetching Cursor Performance [message #288476 is a reply to message #288467] Mon, 17 December 2007 13:06 Go to previous messageGo to next message
Agus211
Messages: 39
Registered: September 2007
Member
Hi Kevin, I'd like to try adding subindexes, but I don't have a CTXCAT, I'm using a CONTEXT index and I think it doesn't allow me to use an INDEX SET as a parameter. Is there any similar parameter in CONTEXT indexes?.
Re: Fetching Cursor Performance [message #288488 is a reply to message #288476] Mon, 17 December 2007 14:26 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
nope CONTEXT indexes will not, you are right, says so here:

http://www.peacetech.com/flipper/oracle9i/901_doc/text.901/a90121/csql5.htm

So, I would say, see if you can create the CTXCAT index. Nobody said you can't use this type of index did they? Best thing you can do is try. I have limited experience with these types of indexes so I am as about blind as you here. You will have to do the work to find out. Can't be that bad though right? If you can create a context index, you should be able to create one of these types too. If you get errors, contact your DBA to get them to make it work.

Kevin
Re: Fetching Cursor Performance [message #288492 is a reply to message #288488] Mon, 17 December 2007 15:03 Go to previous messageGo to next message
Agus211
Messages: 39
Registered: September 2007
Member
Hi, I'll try and see what I can do. Thank's Kevin for your help!.
Re: Fetching Cursor Performance [message #288506 is a reply to message #288492] Mon, 17 December 2007 20:37 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The top-n component may not be the WHOLE problem.
WHERE ouid = 1
AND (contains (txt_index, v_word, 1) > 0)

What about the OUID. Are there lots of rows that match the CONTEXT index that do not have ouid = 1 ?

If so, then the context index could be returning thousands more rows that it is ultimately filtering out.

How many rows are returned when you remove the OUID clause?

Ross Leishman

Re: Fetching Cursor Performance [message #288507 is a reply to message #288054] Mon, 17 December 2007 20:43 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes. My hope is he can get the composite CTXCAT index working and thus make this a non issue.

I wonder too though if there is some mistake in his code. For example:

There is a select into the fetchs a variable called v_ouid.
But after fetching this variable it is not used, rather ouid = 1 is used instead.

Kevin
Re: Fetching Cursor Performance [message #288746 is a reply to message #288507] Tue, 18 December 2007 09:29 Go to previous messageGo to next message
Agus211
Messages: 39
Registered: September 2007
Member
Hi, thanks for replying. I'm sorry, but there's a mistake in the script I've copied.

SELECT ID
INTO v_ouid
FROM catalogs
WHERE catalog_name = v_catalogs;

OPEN l_cursor FOR
SELECT *
FROM (SELECT /*+ FIRST_ROWS(50) INDEX(IDX_DATE_NEWS) */
score (1) AS RANK, ID,
TO_CHAR (date_news, 'dd/mm/yyyy'), CODE_NEWS
FROM newsfiles
WHERE ouid = 1
AND (contains (txt_index, v_word, 1) > 0)
ORDER BY date_news DESC)
WHERE ROWNUM < 201;

Should be:

This select is used to "traduce" the Zone name that the function recieves as a parameter to the Zone's code.

SELECT ID
INTO v_ouid
FROM catalogs
WHERE catalog_name = v_catalogs;

OPEN l_cursor FOR
SELECT *
FROM (SELECT /*+ FIRST_ROWS(50) INDEX(IDX_DATE_NEWS) */
score (1) AS RANK, ID,
TO_CHAR (date_news, 'dd/mm/yyyy'), CODE_NEWS
FROM newsfiles
WHERE ouid = v_ouid
AND (contains (txt_index, v_word, 1) > 0)
ORDER BY date_news DESC)
WHERE ROWNUM < 201;

v_ouid it's used there. That variable is a zone code(there are 3 codes available). This table has like 400000 rows, and OUID = 1 is the one that appears most times(like 300000).

I'm thinking also that I should take out the INDEX(IDX_DATE_NEWS) hint, since the date_news column is only used to for the ORDER BY clause and not in the filter. Besides, it doesn't appear in the explain plan.
Re: Fetching Cursor Performance [message #288810 is a reply to message #288054] Tue, 18 December 2007 15:06 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
OK, Agus, thanks, but come on, how long does it take to create a CTXCAT index and fool around with it to see if you can get what you want out of it.

I am interested to see this work (or not).

Kevin
Previous Topic: ORA-03127 error?
Next Topic: Query taking long time to execute
Goto Forum:
  


Current Time: Thu Dec 08 08:40:58 CST 2016

Total time taken to generate the page: 0.09032 seconds