Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to parse Cursor and bind variables, the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

The following is a summary of the content of this speech, which also contains emails that I answered some of my friends' questions:

There are two kinds of cursor in Oracle: one is shared cursor, the other is session cursor.

The so-called shared cursor refers to a kind of library cache object cached in library cache. To put it bluntly, it refers to sql and anonymous pl/sql cached in library cache. They are one of dozens of library cache object that oracle caches in library cache, and the namespace to which it belongs is CRSR (that is, the abbreviation for cursor). The parent cursor and child cursor mentioned in your letter are both shared cursor. They are both stored in library cache in the form of library cache object handle. When a sql is executed for the first time, the address of its child cursor will be stored in the heap 0 of the library cache object handle of parent cursor and child cursor,parent cursor at the same time. The execution plan of this sql will be stored in heap 6 of the library cache object handle of the above child cursor. The process of performing the parent cursor and child cursor generated by the above sql for the first time is what the so-called "hard parsing" mainly does. If the above sql is executed again, Oracle only needs to scan the corresponding library cache object handle, find the child cursor generated after the last hard parsing, and use the parse tree and execution plan directly. This is the so-called "soft parsing".

The second kind of cursor in Oracle is session cursor,session cursor, which is divided into three types: implicit cursor,explicit cursor and ref cursor. The so-called session cursor actually refers to a memory area (or memory structure) in the PGA of the server process corresponding to this session (or memory structure), which is designed to process and process only one sql statement at a time (here, an implicit cursor handles only one sql,explicit cursor at a time and the number of ref cursor processing sql is under your control).

A session cursor can only correspond to one shared cursor, while a shared cursor may correspond to multiple session cursor at the same time.

When a session cursor is associated with its corresponding shared cursor, if you set cursor_space_for_time to true, when a session cursor has finished processing a sql, it will not be cache by destroy,Oracle (we call it soft closed session cursor). The purpose of doing so is obvious, because the soft closed lost session cursor has been linked to the shared cursor that contains its execution plan and parse tree. Then when the same sql is executed in this session again, the Oracle no longer needs to scan the library cache, just take the session cursor that has just been soft closed off and use it. This is the so-called "soft parsing".

Finally, I would like to talk about the meaning of several parameters about cursor in Oracle that are particularly confusing:

1 、 open_cursors

Open_cursors refers to the maximum number of session cursor that can exist in the open state simultaneously in a single session

2 、 session_cached_cursors

Session_cached_cursors refers to the maximum number of soft closed session cursor in a single session that can cache at the same time.

3 、 cursor_space_for_time

There are three points to note about cursor_space_for_time: 1) it has been invalidated in 10.2.0.5 and 11.1.0.7; 2) if binding variables are also used after adjusting its value to true, it may lead to logical data corruption;3 due to Bug 6696453) after setting its value to true, all child cursor will still hold library cache pin after execution until its parent cursor is turned off

First, let's take a look at the definition of namespace to which library cache object belongs:

1. Library cache objects are grouped in namespaces according to their type.

2. Each object can only be of one type.

3. All the objects of the same type are in the same namespace.

4. A namespace may be used by more than one type.

5. The most important namespace is called cursor (CRSR) and houses the shared SQL cursors.

The explanation of namespace that you see in obj$ is certainly incomplete, because library cache object like shared cursor is not in obj$ at all.

So you can actually understand it this way: namespace is for library cache object cached in library cache, so why is there a definition of namespace in obj$? -because part of the source of library cache object comes from the metadata of solidified object that already exists in the database.

Let's take a look at the details of the namespace to which library cache object belongs:

Currently there are 64 different object types but this number may grow at any time with the introduction of new features. Examples of types are: cursor, table, synonym, sequence, index, LOB, Java source, outline, dimension, and so on. Not every type corresponds to a namespace. Actually, there are only 32 namespaces which, of course, are also subject to increase at any time.

What is a certainty is that all the objects of the same type will always be stored in the same namespace. An object can only be of one type, hence the search for an object in the library cache is reduced to a search for this object in the corresponding namespace.

Some namespaces contain objects of two or three different types. These are some of the most commonly used namespaces:

CRSR: Stores library objects of type cursor (shared SQL statements)

TABL/PRCD/TYPE: Stores tables, views, sequences, synonyms, procedure specifications, function specifications, package specifications, libraries, and type specifications

BODY/TYBD: Stores procedure, function, package, and type bodies

TRGR: Stores library objects of type trigger

INDX: Stores library objects of type index

CLST: Stores library objects of type cluster

The exact number and name of namespaces in use depends on the server features that are used by the application. For example, if the application uses Java, namespaces like JVSC (java source) and JVRE (Java resource) may be used, otherwise they will not be used.

Note: These namespaces do not store tables, clusters, or indexes as such, only the metadata is stored.

The final conclusion is: I can't see the content of KQD.H, so I can't know the exact namespace id of all the namespace in Oracle, but you can guess it from the list of all namespace in library cache dump, because the display is sorted by namespace id.

You can know all the namespace under a version of Oracle through library cache dump, as shown in 9.2.0.6:

LIBRARY CACHE STATISTICS:

Namespace gets hit ratio pins hit ratio reloads invalids

CRSR 1078 0.860 4989 0.935 17 0

TABL/PRCD/TYPE 596 0.636 780 0.624 0 0

BODY/TYBD 1 0.000 0 0.000 00

TRGR 1 0.000 1 0.000 00

INDX 76 0.474 45 0.111 0 0

CLST 148 0.953 203 0.961 0 0

OBJE 0 0.000 0 0.000 00

PIPE 0 0.000 0 0.000 00

LOB 0 0.000 0 0.000 00

DIR 0 0.000 0 0.000 00

QUEU 30 0.700 30 0.700 00

OBJG 0 0.000 0 0.000 00

PROP 0 0.000 0 0.000 00

JVSC 0 0.000 0 0.000 00

JVRE 0 0.000 0 0.000 00

ROBJ 0 0.000 0 0.000 00

REIP 0 0.000 0 0.000 00

CPOB 0 0.000 0 0.000 00

EVNT 1 0.000 1 0.000 00

SUMM 0 0.000 0 0.000 00

DIMN 0 0.000 0 0.000 00

CTX 0 0.000 0 0.000 00

OUTL 0 0.000 0 0.000 00

RULS 0 0.000 0 0.000 00

RMGR 0 0.000 0 0.000 00

IFSD 1 0.000 0 0.000 00

PPLN 0 0.000 0 0.000 00

PCLS 0 0.000 0 0.000 00

SUBS 0 0.000 0 0.000 00

LOCS 0 0.000 0 0.000 00

RMOB 0 0.000 0 0.000 00

RSMD 0 0.000 0 0.000 00

JVSD 0 0.000 0 0.000 00

ENPR 0 0.000 0 0.000 00

RELC 0 0.000 0 0.000 00

STREAM 0 0.000 0 0.000 00

APPLY 0 0.000 0 0.000 00

APPLY SOURCE 0 0.000 0 0.000 00

APPLY DESTN 0 0.000 0 0.000 00

TEST 0 0.000 0 0.000 00

CUMULATIVE 1932 0.778 6049 0.888 17 0

From the results, 9.2.0.6 is a total of 40 namespace.

On how to parse Cursor and bind variables to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.