Shulou(Shulou.com)06/01 Report--

Cursor is the Chinese translation of cursor, so what exactly is cursor? It is described in Oracle Concept as follows:

When an application issues a SQL statement, the application makes a parse call to the database to prepare the statement for execution. The parse call opens or creates acursor, which is a handle for the session-specific private SQL area that holds a parsed SQL statement and other processing information. The cursor and private SQL area are in the PGA.

If many of the above technical terms confuse you, you can simply understand that a sql statement corresponds to a cursor.

Cursor classification:

Session cursor actually refers to a memory area (or memory structure) in the PGA (or memory structure) of the server process corresponding to this session, which is designed to process and process only one sql statement at a time.

Shared cursor cache is a kind of librarycache object in librarycache (Shared Pool under SGA). To put it bluntly, it refers to sql and anonymous pl/sql cached in librarycache.

The following figure is described

Remember the hard parsing and soft parsing we described in the http://lqding.blog.51cto.com/9123978/1685341 article?

Hard parsing, in the fourth case in the figure above, requires a cursor to be reconstructed.

Soft parsing, in the third case in the figure above, can query the cursor information that can be reused in the shared pool.

Soft resolution: as shown in the figure above, not only the details of cursor are found in Shared pool, but also the status of cursor is recorded in UGA. When a sql is issued, if you can find the same cursor that has been opened in the uga, then share the cursor directly. There is no need for Shared pool examination. If the cursor in uga is closed, you can simply open the cursor. You can also share cursors directly. In both cases, there is no need for Shared pool checking, which is called soft parsing.

Session Cursor

Using v$open_cursor view queries, the maximum number of cursors a session can open is defined by the parameter OPEN_CURSORS.

Session cursor is divided into three types: implicit cursor,explicit cursor and ref cursor.

Shared cursor classification:

Parent cursor

A representative of a subcursor with the same text. All SQL with the same text share the parent cursor.

The parent cursor has no execution plan, only some administrative information, including SQL TEXT and related hash value, and so on.

Each line in the v$sqlarea represents a parent cursor, and the address field represents its memory address.

Sub-cursor

The SQL text is the same, but multiple execution plans are generated depending on the execution environment, etc.

Contains the metadata of the SQL, even if you get all the relevant information that the SQL can execute, such as OBJECT and permissions, optimizer settings, execution plan, etc. Each line in v$sql represents a child cursor, which is associated with parent cursor based on hash value and address. Child cursor has its own address, or V$SQL.CHILD_ADDRESS.

Case study:

1. Create a user

SQL > conn / as sysdbaConnected.SQL > create user ding identified by ding;User created.SQL > grant resource,connect to ding;Grant succeeded.

two。 Generate data

SQL > create table ding.emp as select * from scott.emp;Table created.

Restart the database

3. Log in to scott and ding users respectively and execute the following query

SQL > select * from emp

4. View parent cursor

SQL > COL SQL_TEXT FOR A30SQL > COL SQL_ID FOR A20SQL > SET LINESIZE 200SQL > SELECT sql_text, sql_id, EXECUTIONS, LOADS, VERSION_COUNT FROM v$sqlarea WHERE sql_text LIKE'% emp' SQL_TEXT SQL_ID EXECUTIONS LOADS VERSION_COUNT -select * from emp a2dk8bdn0ujx7 2 2 2

The two queries share a parent cursor, which is load twice, indicating that it has been hard parsed twice. VERSION_COUNT represents the number of child cursors.

5. View child cursors

SQL > SELECT sql_id,sql_text,loads,child_number,parse_calls FROM v$sql WHERE sql_text LIKE'% emp' SQL_ID SQL_TEXT LOADS CHILD_NUMBER PARSE_CALLS A2dk8bdn0ujx7 select * from emp 1 0 1a2dk8bdn0ujx7 select * from emp 1 1 1

6. Execute the statement under the scott user again, and check the parent-son cursor again

SQL > SELECT sql_text, sql_id, EXECUTIONS, LOADS, VERSION_COUNT FROM v$sqlarea WHERE sql_text LIKE'% emp' 2 3 4 5 6 7 SQL_TEXT SQL_ID EXECUTIONS LOADS VERSION_COUNT -select * from emp a2dk8bdn0ujx7 3 2 2

EXECUTIONS becomes 3. 5%.

SQL > SELECT sql_id, sql_text, loads, child_number, parse_calls, parsing_schema_name FROM v$sql WHERE sql_text LIKE'% emp' 2 3 4 5 6 7 8 SQL_ID SQL_TEXT LOADS CHILD_NUMBER PARSE_CALLS PARSING_SC -a2dk8bdn0ujx7 select * from emp 1 0 1 DINGa2dk8bdn0ujx7 select * from emp 1 1 2 SCOTT

The parent cursor can be shared only if the text is identical. Even if the semantics, environment, and so on of the statement are exactly the same, the sql text cannot be slightly different. For example, sql

Select * from emp;select * from emp;select * from Emp

So how do we know why child cursors are not shared when the parent cursors are the same and there are multiple child cursors?

SELECT * FROM v$sql_shared_cursor WHERE sql_id = 'a2dk8bdn0ujx7'

What is the field of * MISMATCH in this table? if the value is Y, it means that subcursors cannot be shared because the content indicated by this field is inconsistent.

The life cycle of the cursor:

(1) Open the cursor (dbms_sql.open_cursor)

Open cursor: A memory structure for the cursor is allocated in the server-side private memory of the server process associated with the session, the user global area (UGA). Note that no SQL statement isassociated with the cursor yet.

The system allocates the relevant memory structure in the UGA, which is the process of getting the cursor handle, and the cursor is not associated with the sql statement.

(2) parse cursor (dbms_sql.parse)

Parse cursor: A SQL statement is associated with the cursor. Its parsed representation that includes the execution plan (which describes how the SQL engine will execute the SQL statement) is loaded in the shared pool, specifically, in the library cache. The structure in the UGA is updated to store a pointer to the location of the shareable cursor in the library cache. The next section will describe parsing in more detail.

There is a sql associated with the cursor, and the parsed execution plan is placed in library cache (under the shared pool of SGA), and a pointer to the shared cursor is generated in UGA; that is, session cursor points to shared cursor. A session cursor can point to only one shared cursor, while a shared cursor can point to multiple session cursor.

(3) define the output variable (dbms_sql.define_column)

Define output variables: If the SQL statement returns data, the variables receiving it must be defined. This is necessary not only for queries but also for DELETE, INSERT, and UPDATE statements that use the RETURNING clause.

If the sql statement returns data, you must define the variable that receives the data, which is returning for delete,update,insert

(4) bind input variables (dbms_sql.bind_variable/bind_array)

Bind input variables: If the SQL statement uses bind variables, their values must be provided. No check is performed during the binding. If invalid data is passed, a runtime error will be raised during the execution.

The binding process is not checked.

(5) execute cursor (dbms_sql.execute)

Execute cursor: The SQL statement is executed. But becareful, because the database engine doesn't always do anything significant during this phase. In fact, for many types of queries, the real processing isusually delayed to the fetch phase.

The database engine doesn't really do anything important at this step, but for most sql statements, the real process is to the fetch data acquisition phase.

(6) get cursor (dbms_sql.fetch_rows)

Fetch cursor: If the SQL statement returns data, this step retrieves it. Especially for queries, this step is where most of theprocessing is performed. In the case of queries, rows might be partiallyfetched. In other words, the cursor might be closed before fetching all therows.

The real process, if there is any returned data, must provide an output variable (dbms_sql.column_value)

(7) close the cursor (dbms_sql.close_cursor)

Close cursor: The resources associated with the cursorin the UGA are freed and consequently made available for other cursors. The shareable cursor in the library cache is not removed. It remains there in the hope of being reused in the future.

Release the related resources in UGA, and the shared cursors in the library cache will not be cleared.

When the cursor is closed, it can continue to be cached in memory, and the parameter SESSION_CACHED_CURSORS defines the maximum number of cursors the current Session has been closed and cached, that is, the maximum number of soft closed session cursor in a single session that can cache at the same time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.