View Cursor from 10046 Trace RAW File

Cursor sharing Cursor is a key and difficulty in Oracle learning. Oracle's Cursor is based on the optimization of SQL parsing Parse consumption. Based on different memory cache structures, we often hear terms such as Hard parsing Hard Parse, Soft parsing Soft Parse, and Soft parsing.

Strictly speaking, the basic concept of cursor sharing is the cursor. In Oracle, the Cursor can be divided into two types: Shared Cursor and Private Cursor. To understand the condition of a cursor, you must differentiate the two cursor types.

Adaptive cursor sharing in Oracle -- adaptive cursor sharing

Oracle 11g new SQL Trace 10046 Method

1. Shared and Private Cursor

Shared Cursor is a lot of talk about Cache objects residing in the Library Cache, which stores the previously resolved execution plan. When an SQL statement appears in the system for the first time, Oracle does not find the corresponding "ready-to-use" execution plan in the Library Cache, the Hard parsing parse process is started, generate a Shared Cursor in the Library Cache. Note: This SQL Cursor can be shared by other sessions that "Meet the Cursor sharing condition. It can be shared before it is out of age or flushed out of memory. If the same SQL statement is issued for the second time, Shared Cursor is Shared, which is called Soft Parse.

Private Cursor corresponds to Shared Cursor. Private Cursor is resident in the PGA space of the Server Process. When we issue an SQL statement or manually create a Cursor, a Private Cursor object is created in the PGA space corresponding to the Server Process.

As the name suggests, Private Cursor means that it can only be used by the current Session and cannot be shared between sessions. However, if the same Session is executed multiple times, do you need to create a Private Cursor multiple times? This process involves the sharing of Private Cursor.

We can see some obfuscation concepts in some documents. An SQL statement can be shared only when it is executed three times. If we conduct a simple experiment, we can find that this argument is not true in shared cursor. Once the SQL statement is executed once, shared cursor is generated in the Library Cache. How can this problem be solved? It should be said that the premise of this argument is Private Cursor sharing.

2. Soft parsing and parameter configuration

We are familiar with the concepts of Hard Parse and Soft Parse ". The difference between the two lies in whether an execution plan is generated in the Library Cache. If we take the Private Cursor into account, a new parsing Type "soft resolution" will be created ".

Even Soft Parse, every time we execute SQL in PGA, there will be a Private Cursor creation process. According to the Cursor life cycle, when the Cursor execution ends, a Close action will invalidate the Private Cursor. Oracle can try to cache Private Cursor. That is to say, the Close action does not actually Close or disappear, but supports sharing Private Cursor.

If Private Cursor can be reused in PGA, we can eliminate part of the cost of creating Cursor in PGA. Implement soft parsing.

From the early stage of Oracle, we had a contact with the open_cursor parameter. Initially, this parameter serves two levels: one is to control the maximum number of Cursor that a session can open at the same time, and the other is to control the maximum number of Private Cursor caches that can be shared in the PGA.

Some changes have occurred in Oracle settings. The new parameter session_cached_cursors is introduced to restrict the cache size separately. In the current experiment version, this parameter is 50.

SQL> show parameter cached

NAME TYPE VALUE

--------------------------------------------------------------

Session_cached_cursors integer 50

For Private Cursor sharing in PGA, Oracle records the number of times generated. When the execution is performed three times, the structure of the PGA internal cache is established.

In this article, we use 10046 to verify the mechanism mentioned above.

3. Prepare the environment

First, we need to find a Trace file of 10046 as the experimental object. Select 11.2.0.3 as the lab object.

SQL> select value from v $ diag_info where name = 'default Trace file ';

VALUE

--------------------------------------------------------------------

/U01/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_6964.trc

Clear the shared pool and buffer cache and execute the same SQL statement 10 times.

SQL> alter system flush shared_pool;

The system has been changed.

SQL> alter system flush buffer_cache;

The system has been changed.

SQL> alter session set events '10046 trace name context forever, level 12 ';

The session has been changed.
 
 
SQL> select count (*) from t;
 
COUNT (*)
 
----------
 
0
 
SQL> select count (*) from t;
 
COUNT (*)
 
----------
 
0
 
(The remaining execution times are slightly ......)
 
SQL> alter session set events '10046 trace name context off ';
 
The session has been changed.
 
SQL>
 
After the trace file is generated, we will analyze the details of the file in detail below.

For more details, please continue to read the highlights on the next page:

1 2 Next Page