Skip to Main Content
  • Questions

  • Cursors : When exactly cursor run the Query?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Anubhav.

Asked: September 04, 2008 - 1:18 pm UTC

Last updated: September 05, 2008 - 1:43 pm UTC

Version: 10.2.0

Viewed 1000+ times

You Asked

Hi,

I need to know as to when exactly cursor runs it's query? As per my understanding Cursor runs query once that too when it is called but if I check v$sqlArea query is getting executed again and again?

I recently optimize a process and created an index over the cursor query. It worked fine but was amazed as I use to think that cursor only runs the query once.

Please help.

Thanks and Regards,
Anubhav

and Tom said...

A cursor is a programming device used to "access the database". It is a lot like a file handle, so you can read and write a file.


A cursor may be used to execute one or more queries - only one query at a time is associated with a cursor area however.


Do not confuse what you see in v$sql (the shared pool, where a cached execution plan is stored) with a "cursor". In v$sql you see a shared data structure - an execution plan - for a query. In an application - you could open up as many cursors as you like - all pointing to that same query, or a single cursor could execute that query over and over and over. Additionally - hundreds/thousands of applications could open a cursor (each having their own cursor) and point to the same entry in v$sql.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Example for Cursors : When exactly cursor run the Query?

Anubhav Kumar, September 05, 2008 - 1:12 pm UTC

Thanks for your response, I want to take the example If you don't mind :

I want to run Below block:

Declare
Cursor Cur_Emp
Select * from Emp
where EmpEntryDate = '01-Jan-2008'
and EmpExitdate = '30-Jan-2008'
and EmpMarried = 'Y';
begin
For Each_Rec In Cur_Emp
Loop
/*
Processing Here with Cursor
*/
End loop;
End;

Let the above process is taking time as below:

1) In Starts [For fecthing Records in cursor] : 1 min
2) In For Loop : 30 min

So, I tried to create a Index on Emp table with EmpEntryDate ,EmpExitdate ,EmpMarried column [same column as in where condition] Then the timings are:

1) In Starts [For fecthing Records in cursor] : 1/2 min
2) In For Loop : 10 min

The performance improvement in Cursor query is understandable but

Why there is a improvement in FOR Loop?

Is Cursor still using the index on the fetched records in PGA?

When Cursor fetch the records in PGA,Do it creates the table in PGA exactly same as Emp Table [with all the indexes and constraints] hence it uses that index now for every fetch from PGA while looping?

Thanks and Regards,
Anubhav Kumar

Tom Kyte
September 05, 2008 - 1:43 pm UTC 

where EmpEntryDate = '01-Jan-2008'
  and EmpExitdate = '30-Jan-2008'


unlearn that behavior, never compare a date to a string, compare dates to dates - use to_date and use a FORMAT
where EmpEntryDate = to_date('01-Jan-2008','dd-mon-yyyy')
  and EmpExitdate = to_date('30-Jan-2008','dd-mon-yyyy')


always - always avoid the implicit conversions, you'll never be sorry you did - you will be sorry if you don't.

I do not understand what you mean by "it starts [....] : 1 min" and 1/2 min. I don't know what you mean by that - what are you timing there???



do you have access to Effective Oracle by Design - in that book I spend a good 10 or say pages explaining from start to finish how statements are processed.


(short answer: NO, the entire table/result set is not built and copied into your pga)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.