Home » RDBMS Server » Performance Tuning » Fetching Cursor Performance
Fetching Cursor Performance [message #288054] |
Fri, 14 December 2007 09:15 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #288089 is a reply to message #288054] |
Fri, 14 December 2007 14:30 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #288462 is a reply to message #288102] |
Mon, 17 December 2007 11:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #288467 is a reply to message #288462] |
Mon, 17 December 2007 12:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
Kevin Meade
Messages: 2103 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #288506 is a reply to message #288492] |
Mon, 17 December 2007 20:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 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 #288746 is a reply to message #288507] |
Tue, 18 December 2007 09:29 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
Kevin Meade
Messages: 2103 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
|
|
|
Goto Forum:
Current Time: Fri Sep 06 09:28:42 CDT 2024
|