Oracle performance optimization cursor and SQL

Oracle performance optimization cursor and SQL, these SQL statements are strictly unable to share the cursor (that is, share the execution plan), but the execution plans required for these statements are

Oracle performance optimization cursor and SQL, these SQL statements are strictly unable to share the cursor (that is, share the execution plan), but the execution plans required for these statements are

I. cursor

Let's talk about the concept of cursor first.

From the perspective of the Oracle database administrator, cursors are collectively referred to as executable objects stored in the database cache. An SQL statement is stored in the database cache and is a cursor. In addition, Oracle stored procedures are executable objects stored in the database cache. From the Oracle DBA perspective, Oracle stored procedures are also cursors. Oracle also counts it as a cursor. In some cursor-related views, it also displays some information about the stored procedure. But from the developer's point of view, only SQL statements are cursors.

Ii. cursor View

Your application may be developed in Java, Pro * C, or middleware. For DBA, we don't have to worry too much about it. Take a common layer-3 application as an example, for example:

Similar figures can be seen in many places, assuming this is a layer-3 J2EE application. The Java applications called by the client are stored on the intermediate application server layer. The execution of the applications is the responsibility of the application server.

For example, the execution of this Java application is the task of the application server. However, when executing the executeQuery ("select * from Test") statement, this Java statement requires that the table Test be queried from the database server. Hair:

The execution of this statement is the responsibility of the database server. The database server is only responsible for executing "Select * from test" at the fastest speed. Others are not responsible. As a DBA, we only need to ensure that SQL statements can be executed more quickly. We are not responsible for the logic of the application. That is to say, as a DBA, we do not have to be responsible for the specific code, but we are only responsible for the execution of SQL statements. Each SQL statement sent to Oracle for execution, whether it is manually typed in the SQL * Plus command window or sent by the application server to Oracle for execution, they are all transmitted 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 DBAs to track problems and optimize SQL Execution.

,