Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> How Cursor works Internally
Vidhu
post Dec 28 2016, 01:10 PM
Post #1


Advanced Member
***

Group: Members
Posts: 42
Joined: 11-March 15
From: India
Member No.: 50,402



Hi,

I have read many articles on how Cursor works Internally. However the articles mentions different versions to the answer. Just curious to know about these points:

1. When we declare a Cursor what really happens in the back end.
2. When we open a cursor will the query get executed, if yes where would the result get stored in memory.
3. What happens when we fetch the data from cursor. Is that fetching from the result set or does the actual execution takes place during cursor fetch.


--------------------
Regards,

Vidhu V S
Oracle PL SQL Developer
Go to the top of the page
 
+Quote Post
Vidhu
post Jan 2 2017, 07:08 AM
Post #2


Advanced Member
***

Group: Members
Posts: 42
Joined: 11-March 15
From: India
Member No.: 50,402



QUOTE (Vidhu @ Dec 28 2016, 11:40 PM) *
Hi,

I have read many articles on how Cursor works Internally. However the articles mentions different versions to the answer. Just curious to know about these points:

1. When we declare a Cursor what really happens in the back end.
2. When we open a cursor will the query get executed, if yes where would the result get stored in memory.
3. What happens when we fetch the data from cursor. Is that fetching from the result set or does the actual execution takes place during cursor fetch.


Hi All,

does anybody have any idea on this.


--------------------
Regards,

Vidhu V S
Oracle PL SQL Developer
Go to the top of the page
 
+Quote Post
burleson
post Jan 2 2017, 12:36 PM
Post #3


Advanced Member
***

Group: Members
Posts: 13,295
Joined: 26-January 04
Member No.: 13



Hi Vidhu,

The internals of Oracle cursor management are a closely-guarded secret, but you can get some idea how Oracle manages cursors by looking at the undocumented parms for cursors.

Also, be aware that it changes between releases, and the new mutex method is different:

http://dba-oracle.com/googlesearchsite_pro...=2621j1281359j7

Sorry, but that's about all of the documented information on cursor internals.




--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
boobal_ganesan
post Jan 3 2017, 05:21 PM
Post #4


Advanced Member
***

Group: Members
Posts: 57
Joined: 6-June 16
From: India
Member No.: 51,370



Hello Vidhu,

We have little descriptive information on the cursor working in the documentation. They are explained below,

1. When we declare a Cursor what really happens in the back end.

The Oracle server allocates a chunk of private temporary workspace in the SGA called the context area for processing every single SQL statement encountered inside a PL/SQL block. This memory area holds the SQL query, its parsing information and the result set returned by the SQL query. The result set can then be processed either row by row or in bulk. As the context area is managed internally by the Oracle server, we do not have any control over it. The cursor can hold n number of rows, but can process only one row at a time. The record set held by the cursor is called as the active set.

2. When we open a cursor will the query get executed, if yes where would the result get stored in memory.

OPEN

At this stage, the cursor is initialized and allocated with a portion of the private memory of the server process in the Process Global Area associated with the session for processing the SQL statement. During this stage, the cursor points to the allocated context area. This stage internally follows the below stages in the below-mentioned sequence.

1) PARSE: This is the primary step for processing the cursor associated SQL statement by undergoing the syntax and semantic checking. The syntax check verifies for any misspelled keywords. The semantic check mainly verifies for the object permission and for its validity. After this step, the binding process takes place, which is mentioned in the below step.

2) BIND: This step replaces all the bind variables present in the SQL statement with their actual value and gets it ready for the execution. If the bind value passed is not valid, no check is performed during the binding process, but a runtime error is thrown during the program execution.

3) EXECUTE: This phase executes the resulting SQL query from the above step, fetches the corresponding data from the database and loads them up in the pre-allocated context area. This process additionally sets up the record pointer to the first row of the query’s result set.


3. What happens when we fetch the data from cursor. Is that fetching from the result set or does the actual execution takes place during cursor fetch.

FETCH
This stage pulls the data from the query result set stored in the context area in the above step. The cursor pointer is incremented by one row and points the next row in the result set sequence for every single row fetch. This stage also fetches rows in bulk using the BULK COLLECT clause and the number of rows to be fetched can be limited using the LIMIT clause. Once the cursor pointer reaches the last row of the result set, the pointer halts, and any further fetch statement returns only the last row from the result set.

Reference: http://www.rampant-books.com/book_1701_pls...initive_ref.htm

Thank you,
Boobal Ganesan
Go to the top of the page
 
+Quote Post
Vidhu
post Jan 4 2017, 05:35 AM
Post #5


Advanced Member
***

Group: Members
Posts: 42
Joined: 11-March 15
From: India
Member No.: 50,402



QUOTE (boobal_ganesan @ Jan 4 2017, 03:51 AM) *
Hello Vidhu,

We have little descriptive information on the cursor working in the documentation. They are explained below,

1. When we declare a Cursor what really happens in the back end.

The Oracle server allocates a chunk of private temporary workspace in the SGA called the context area for processing every single SQL statement encountered inside a PL/SQL block. This memory area holds the SQL query, its parsing information and the result set returned by the SQL query. The result set can then be processed either row by row or in bulk. As the context area is managed internally by the Oracle server, we do not have any control over it. The cursor can hold n number of rows, but can process only one row at a time. The record set held by the cursor is called as the active set.

2. When we open a cursor will the query get executed, if yes where would the result get stored in memory.

OPEN

At this stage, the cursor is initialized and allocated with a portion of the private memory of the server process in the Process Global Area associated with the session for processing the SQL statement. During this stage, the cursor points to the allocated context area. This stage internally follows the below stages in the below-mentioned sequence.

1) PARSE: This is the primary step for processing the cursor associated SQL statement by undergoing the syntax and semantic checking. The syntax check verifies for any misspelled keywords. The semantic check mainly verifies for the object permission and for its validity. After this step, the binding process takes place, which is mentioned in the below step.

2) BIND: This step replaces all the bind variables present in the SQL statement with their actual value and gets it ready for the execution. If the bind value passed is not valid, no check is performed during the binding process, but a runtime error is thrown during the program execution.

3) EXECUTE: This phase executes the resulting SQL query from the above step, fetches the corresponding data from the database and loads them up in the pre-allocated context area. This process additionally sets up the record pointer to the first row of the query’s result set.


3. What happens when we fetch the data from cursor. Is that fetching from the result set or does the actual execution takes place during cursor fetch.

FETCH
This stage pulls the data from the query result set stored in the context area in the above step. The cursor pointer is incremented by one row and points the next row in the result set sequence for every single row fetch. This stage also fetches rows in bulk using the BULK COLLECT clause and the number of rows to be fetched can be limited using the LIMIT clause. Once the cursor pointer reaches the last row of the result set, the pointer halts, and any further fetch statement returns only the last row from the result set.

Reference: http://www.rampant-books.com/book_1701_pls...initive_ref.htm

Thank you,
Boobal Ganesan



Hi Boobal,

Thanks for your valuable information. However I have some questions on this. For example please have a look at the code for a simple cursor below:

set serveroutput on;
set timing on;

declare

cursor cur_indv_records is
select ind.* from test.individual, test.individual ind; -- 1000000 * 1000000 records

v_indv_records cur_indv_records%rowtype;

begin

open cur_indv_records;



close cur_indv_records;

end;

anonymous block completed
Elapsed: 00:00:00.234

If you see the time, it's very less. As you said if the memory stores the result set during declaring or opening of the cursor, then it should take more time than this to store 1000000 * 1000000 rows. Can you please explain why. Or please correct me if am wrong.


--------------------
Regards,

Vidhu V S
Oracle PL SQL Developer
Go to the top of the page
 
+Quote Post
boobal_ganesan
post Jan 4 2017, 04:04 PM
Post #6


Advanced Member
***

Group: Members
Posts: 57
Joined: 6-June 16
From: India
Member No.: 51,370



Hello Vidhu,

Very good observation.

Oracle database does not take time while executing a query, but only during its retrieval.

OPEN operation executes the query but does not retrieve them, meanwhile the FETCH operation retrieves them to us. Thus, FETCH operation consumes time rather OPEN.

I can demonstrate this to you with a simple example shown below,

In the first image - I have created a table with one column and have populated it with 999999 records. When I execute it in the SQL developer tool, it takes just 0.028 seconds to retrieve the first 50 rows. That is, EXECUTION + 50 rows retrieval took very less time.

In the second image - I have clicked inside the retrieved result and have clicked CTRL+A to retrieve all the results from the query.

In the third image - I have retrieved all the 999999 records from the executed query and it took 66.781 seconds. That is EXECUTION + 999999 rows retrieval took more time.

This shows that the execution always takes very little time, whereas the time consumption is observed only in the retrieval part.

Hope this answers your question.


Thank you,
Boobal Ganesan
Attached File(s)
Attached File  Image_1.jpg ( 184.05K ) Number of downloads: 4
Attached File  Image2.jpg ( 178.7K ) Number of downloads: 4
Attached File  Image3.jpg ( 188.19K ) Number of downloads: 3
 
Go to the top of the page
 
+Quote Post
Vidhu
post Jan 5 2017, 02:54 AM
Post #7


Advanced Member
***

Group: Members
Posts: 42
Joined: 11-March 15
From: India
Member No.: 50,402



Hi Boobal,

Thank you very much for clarifying :-)


--------------------
Regards,

Vidhu V S
Oracle PL SQL Developer
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd January 2017 - 03:02 AM