Cursor too Slow ,Select is fast

[HimanB]

Dear All,
My team is facing a problem.
They have made a stored procedure which fetches data in to a Cursor and then writes it into a CSV file.

The query has been checked & it is fully optimized.
When executed on sql prompt it fetches data in milliseconds.
The query uses UNION ALL clause & has three such Unions in the cursor.

The ables which are being used have approx 20 Million rows each out of which only 700 are to fetched.

Now when they use the same query on SQL prompt or use it to insert data into a temp table it takes less than a second,but if they use Cursor then it takes 10 to 15 minutes.

As the database used by client is 8i , so bulk collect cannot be used.

Can sombody explin this behaviour of Cursor.

Cheers,
Himanshu

 

[sem]

It can not be explaind as they're about the same. The only difference may be in different execution environments, that may lead to different execution plans used for the same query. Another possible problem is using variables of wrong types: if types of a column and a variable to compare with are different, column value is converted to variable datatype thus index is not used. You may trace your session to get real execution plans.

Regards, Dima

http://seminihin.narod.ru

 

[HimanB]

Dima,
Thanks for the valuable input.
I have traced both the sql's & both of them have same execution plan.The only difference in case of PL/SQL you have context switching which is not very high.

Both the SQL's used on SQL prompt as well as in Cursor are same & there in no datatype difference.
In fact when the same sql when converted into dynamic SQL & executed using Insert into ... Select ... From clause works within seconds.

We have managed to tune the process by first inserting data into temp table and then generating report from it, but I am still left guessing what is causing the problem with cursor.

I personally feel that it has something to do with the SGa and the amount of Data which the query filters to display the output.

Santa, can you add something to it.

Cheers,
Himanshu

 

[sem]

Which technique to write CSV do you use? I mean sql*plus, utl_file, Reports or anything else?

Regards, Dima

http://seminihin.narod.ru

 

[HimanB]

Dima,
We have tried both TEXT_IO from forms & UTL_FILE from stored procedures.

I repeat once again that there is no problem with writing of CSV file.
Once the data is populated in the Temp table(as we are currently doing) it takes less than a second to write the file.
By using insert into temp table the insertion itself does not take more than 1 second.

The probelm arises when we use the Cursor(No temp table population).
Open cursor works in a split of second but the Fetch takes ages to complete.

Still I have no idea why?

Regards
Himanshu

 

[sbix]

What do you mean for a temp table?
Is it a table you allocate in a preferred tablespace, or in the default tablespace, with no logging feature enabled?
Perhaps you have some big contention on the TEMPORARY tablespace, probably you have also some sorting and all this I/Os are directed on the same container which "hosts" the selected data

 

[HimanB]

Dear Sbix,
There is no contetnion of temp tablespace.
Sorry for confusing post,by temp table I mean a Database table only which I named as temp.
Also please go through the post carefully,I am facing the problem when we use cursors.

Do let me know if you have any idea about it.

Regards
Himanshu

 

[sbix]

This could explain something.
When you use a "scratch table" you allocate this in a normal tablespace, the table behind the cursor (as result of opening the cursor) is temporary allocated in the TEMPORARY tablespace as like all the space needed for joining and so on.
The funny thing is you have problem in FETCHING from the opened cursor .....

 

[sem]

HimanB? did you launch both procedure and query from the same Oracle user? Does that user own that table?

Regards, Dima

http://seminihin.narod.ru

 

[HimanB]

Yes Sem,
Both were launched by same user, which also happens to be the owner of all the objects used in both Processes.

Yes Sbix, the this is funny & I have no answer to it,why this is happening.

I have given the workaround to my team and the process is running fine but I am keen to find out what is the problem with cursor.

Regards
Himanshu