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

I. Vernier

We need to talk about the concept of cursors first.

From the Oracle database administrator's point of view, cursors are a general term for executable objects stored in the library cache. The SQL statement is stored in the library cache, which is a cursor. In addition to it, Oracle's stored procedure is also an executable object stored in the library cache, which is also a cursor from an Oracle DBA point of view. Oracle also counts it as a cursor, and some information about stored procedures will also be displayed in some views related to cursors. But from the developer's point of view, only SQL statements are cursors.

II. Views on cursors

Your application may be developed in languages such as Java, Pro*C, etc., or there may be middleware, and so on. For DBA, we don't have to care too much about this. Take a common three-tier application as an example, as shown in the following figure:

Similar diagrams can be seen in many places, assuming that this is a three-tier J2EE application. The Java application called by the client is stored in the middle application server layer, and the application server is responsible for the execution of the application.

As shown in the above Java application, its execution is the task of the application server. However, when the executeQuery ("select * from Test") statement is executed, the Java statement requires that the table Test be queried from the database server. Send the following picture:

The execution of this statement is the responsibility of the database server. The database server is only responsible for completing the "Select * from test" execution as quickly as possible. It is not responsible for anything else. As DBA, we just need to make sure that the SQL statement can be executed more quickly, and we are not responsible for the logic problems of the application. In other words, as DBA, we are not responsible for specific code problems, we are only responsible for the execution of SQL statements. Every SQL statement that is sent to Oracle for execution, whether it is manually typed in the SQL*Plus command window or sent to Oracle by the application server for execution, is passed to Oracle in the same way and executed by the server process. The execution of these SQL statements, specific execution plans and other data will be recorded in some views for DBA to track problems and tune the execution of SQL.

Next, let's take a look at these views of the implementation of the relevant SQL. Let's emphasize another noun that we call cursors for what is passed to the Oracle database server for execution from anywhere. It mainly includes SQL statements and PL/SQL program segments.

1. V$SQL

The text of the SQL_TEXT:SQL statement

The full text of the SQL_FULLTEXT:SQL statement

SQL_ID

SHARABLE_MEM: shared memory occupied by cursors

PERSISTENT_MEM: the Fixed (fixed) memory occupied by the cursor duration

RUNTIME_MEM: the Fixed (fixed) memory occupied by cursors at run time

SORTS: the number of times the cursor has been sorted

LOADED_VERSIONS: whether the memory heap occupied by the cursor in the library cache is loaded

OPEN_VERSIONS: whether the cursor is locked.

USERS_OPENING: the number of sessions that opened the cursor. That is, the number of sessions when the cursor is being cached into the PGA. After the cursor is executed three times, it is cached in PGA. This value is added by 1.

FETCHES: the number of times fetched

EXECUTIONS: number of execution

PX_SERVERS_EXECUTIONS: total number of times executed in parallel

END_OF_FETCH_COUNT: the number of times all rows were crawled

USERS_EXECUTING: the number of sessions currently executing this cursor

LOADS: the number of times the cursor has been loaded or reloaded into the library cache. The cursor is only reloaded, so it is possible that the cursor is invalid or the library cache is insufficient.

FIRST_LOAD_TIME: the time when the cursor was first loaded. That is, the time to generate the execution plan

INVALIDATIONS: the number of invalid cursors

PARSE_CALLS: the number of times the cursor is parsed, including hard parsing and soft parsing

DISK_READS: how many physical reads were performed by the cursor

DIRECT_WRITES: the number of times the cursor is written directly

BUFFER_GETS: the number of logical reads

APPLICATION_WAIT_TIME: the wait time of the application (in microseconds)

CONCURRENCY_WAIT_TIME: parallel wait time (in microseconds)

CLUSTER_WAIT_TIME:Cluster wait time

USER_IO_WAIT_TIME: the waiting time of the user Imax O

PLSQL_EXEC_TIME:PL/SQL execution time

JAVA_EXEC_TIME:Java execution time

ROWS_PROCESSED: how many lines have been crawled by the cursor. For the same row, this column increases each time it is fetched

COMMAND_TYPE: command typ

OPTIMIZER_MODE: optimizer mode

OPTIMIZER_COST: the cost of executing the plan

OPTIMIZER_ENV: environment at execution time

OPTIMIZER_ENV_HASH_VALUE: HASH value of the environment

PARSING_USER_ID: the ID of the user who first parsed this cursor

PARSING_SCHEMA_ID: the scheme ID that parses this cursor first

PARSING_SCHEMA_NAME: the scheme ID that parses this cursor first

KEPT_VERSIONS: whether to use the DBMS_SHARED_POOL package to Pin the cursor to the library cache

ADDRESS: the address of the parent cursor handle

TYPE_CHK_HEAP:

HASH_VALUE: the HASH value of the cursor

OLD_HASH_VALUE: old HASH value

PLAN_HASH_VALUE: HASH value of the execution plan. (the above three HASH values are not the same)

CHILD_NUMBER: number of child cursors

SERVICE:

SERVICE_HASH

MODULE: the name of the application that parsed the cursor for the first time. You can set it in your application by calling DBMS_APPLICATION_INFO.SET_MODULE.

MODULE_HASH: the HASH value of the application name

ACTION: the name of the action when it was first parsed. You can set it in your application by calling DBMS_APPLICATION_INFO.SET_ACTION.

ACTION_HASH: the HASh value of the action name

SERIALIZABLE_ABORTS: the number of times an ORA-08177 errors error is generated per cursor (transaction serialization is invalid).

OUTLINE_CATEGORY: outline typ

CPU_TIME: the CPU time spent parsing, executing, and fetching cursors. The unit is microseconds.

ELAPSED_TIME: the total time spent parsing, executing, and fetching cursors. The unit is microseconds.

OUTLINE_SID: the SID of the outline session

CHILD_ADDRESS: the address of the cursor itself

SQLTYPE: the version of the SQL language used by the cursor

REMOTE: whether the cursor is a remote image

OBJECT_STATUS: object statu

LITERAL_HASH_VALUE: the HASH value of the cursor text

LAST_LOAD_TIME: the last time the execution plan was loaded into the library cache.

IS_OBSOLETE: whether this child cursor is abandoned when there are too many child cursors.

CHILD_LATCH: the child latch number of the protection cursor

Profile for SQL_PROFILE:SQL

PROGRAM_ID: process ID

PROGRAM_LINE#

EXACT_MATCHING_SIGNATURE

FORCE_MATCHING_SIGNATURE

LAST_ACTIVE_TIME: the last time the execution plan was used.

BIND_DATA: information about binding variables

In this view, the four columns DISK_READS, BUFFER_GETS, CPU_TIME, and ELAPSED_TIME are the most important when tuning SQL statements. When the speed of the database system is not very satisfactory, if you have determined that it is not because of anything else, it is the performance of SQL statements, but you are just not sure which or which statements are slowing down the overall speed. Then at this time to choose to tune physical reading, logical reading the most, or the most CPU-consuming SQL statements to adjust, can often achieve satisfactory performance growth.

We can also tune the SQL statement with the most EXECUTIONS (execution times). In addition, PARSE_CALLS is the number of parses, so for the SQL statement with the most values in this column, we can see if we can reduce the number of parsing of the statement.

With regard to SQL tuning, it is the same as program tuning. If we have ever worked on code optimization, we know that the way to tune for a large application is to start with the part of the code that executes the most, or the code that consumes the most resources.

Another problem is that there is a concept often mentioned in the document about this view: child cursors and parent cursors. If the text of the two cursors is exactly the same, but because the environment is different, for example, the table operated on by the cursor is a table with the same name under different users, the two cursors cannot share the execution plan. They all have their own execution plans stored in the library cache. These two cursors are child cursors, and Oracle also creates a parent cursor. There is no execution plan in the parent cursor, but it represents all cursors with the same text but different execution plans.

In fact, in the library cache, Oracle creates a parent cursor for each cursor, even if there are no child cursors with the same text. Because parent cursors represent child cursors with the same text, all cursors with the same text share the same parent cursor.

That is, as long as you execute the SQL statement, Oracle will save one parent, one son and two cursors in the library cache. If you execute SQL statements with the same text but different environments and therefore cannot share the execution plan, then a parent cursor may correspond to multiple child cursors.

The parent cursor has no execution plan, it only has information management data, and Oracle added it to manage cursors with the same text. One view is specific to the parent cursor, which is V$sqlarea. Let's talk about this view.

2. V$SQLAREA

The columns of V$SQLAREA and V$SQL are almost identical. The data of sub-games are summarized in V$SQLAREA. If you have two statements, statement An and statement B, the text is exactly the same, but there is no shared execution plan due to different environments, but each has its own execution plan. That is, statement An and statement B are child cursors under the same parent cursor. In the V$SQL view, because it displays subcursors, statement An and statement B each have one line, assuming that the DISK_READS (physical read) of statement An is 100 and the physical read of statement B is 3000. V$SQLAREA displays parent cursor information, and statement An and statement B correspond to the same parent cursor and occupy a line in V$SQLAREA because the text is the same. In V$SQLAREA, the DISK_READS in the parent cursor line of statement An and statement B is 3100, which is the sum of statement An and statement B. The same is true of other columns in V$SQLAREA, which are the sum of corresponding child cursors in V$SQL.

One column that is not in V$SQL is VERSION_COUNT, which is the number of child cursors corresponding to the same parent cursor. If this number is too high, it may mean that cursors that could have shared the execution plan were not shared for some reason.

3.V$open_cursor and Open_cursor parameters

This view and parameters involve the opening of cursors. What is the opening of cursors, that is, in the library cache, when users parse cursors soft or hard, they will add a lock on the handle of the cursor object, that is, Library cache lock. After the cursor is parsed and executed, the lock is not removed immediately, but remains until the user issues the Close command to close the cursor. When we issue the command in the SQL*Plus command window, after fetching all the lines, SQL*Plus will automatically issue the Close command for us to close the cursor.

When the cursor is opened, the Library cache lock is maintained so that even if the library cache is out of memory and needs to age objects, they do not age objects that are still being locked. Therefore, if the user keeps asking the database server to open the cursor and execute the SQL, but forgets to close the cursor, it is easy to exhaust the memory of the shared pool. For this reason, Oracle prepares a parameter, Open_cursor, whose default value is 50 under 9i and 300 in 10g, which means that at 10g, a maximum of 300 cursors can be opened at the same time per session. With this limit, you don't have to be afraid to run out of shared pool memory by constantly opening the cursor without closing it.

If the number of cursors that the session opens at the same time exceeds the limit of the Open_cursor parameter, Oracle prevents the session from opening new cursors. An error was also reported: ORA-01000: the maximum number of cursors opened was exceeded.

These cursors opened by the session are automatically closed after the user disconnects the session.

The V$open_cursor view is dedicated to view cursor information opened by the current session. It can only view cursors open in the current session.

4.CURSOR_SHARING parameter

If there are many SQL statements in your application that look like this:

Select * from a table where id=1

Select * from a table where id=2

Select * from a table where id=50

Wait, these SQL statements cannot strictly share cursors (that is, shared execution plans), but the execution plans required by these statements are all the same. Whether you query a row with an ID of 1 in a table or a row with an ID of 100, the execution should be the same. If you want such statements to share cursors, you can change the value of the Cursor_sharing parameter.

This parameter has three values:

Default EXACT: this value is the default value. Cursors are not shared unless the cursor text is exactly the same.

Cursor SIMILAR: this is the smartest, if the cursor differs only in terms of data values, and the execution plan of the original cursor in the library cache is optimal for the newly executed SQL statement, it will no longer create a new cursor for the SQL statement, but will let it share the original cursor in the library cache.

Optimized FORCE: the cursor is shared without comparing whether the execution plan is optimal, as long as all parts of the cursor are the same except for the different data values in the condition.

This parameter can be modified at the session level, that is, its value can be modified using Alter session, which affects only one session and not others.

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.