Oracle 學習之性能優化（二）游標

  游標是cursor的中文翻譯，那么到底什么是cursor呢？Oracle Concept中是這樣描述的：

  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.

如果上述很多的專業術語把你搞暈了，那你可以簡單的理解成，一個sql語句就會對應到一個游標。

游標分類：

• session cursor 其實就是指跟這個session相對應的server process的PGA里（準確的說是UGA）的一塊內存區域（或者說內存結構），它的目的是為了處理且一次只處理一條sql語句。

• shared cursor 緩存在librarycache（SGA下的Shared Pool）里的一種library cache object，說白了就是指緩存在library cache里的sql和匿名pl/sql。

如下圖描述

還記得我們在http://lqding.blog.51cto.com/9123978/1685341這篇文章中描述的硬解析、軟解析嗎？

硬解析，上圖中的第4種情況，需要重新構造一個游標。

軟解析，上圖中的第3種情況，可以在共享池中查詢到可以被重用的游標信息。

軟軟解析：如上圖，不單Shared pool中有cursor的詳細信息，UGA中也會記錄cursor的狀態。當一個sql   發出后，如果能在uga中找到已經打開的相同游標，那么直接共用該游標。無需再進行Shared pool檢   查。如果uga中的cursor已經關閉，那么直接打開游標即可。也可以直接共用游標。這兩種情況都無   需進行Shared pool的檢查，這種解析稱之為軟軟解析。

Session Cursor

  使用v$open_cursor視圖查詢，一個會話最多可以打開的游標數由參數OPEN_CURSORS定義。

  session cursor又分為三種：分別是implicit cursor，explicit cursor和ref cursor。 

共享游標分類：

• 父游標

  文本相同的子游標的代表。所有文本相同的SQL都共享父游標。

  父游標沒有執行計劃，只有一些管理性的信息，包含了SQL TEXT和相關的hash value等。

  v$sqlarea中的每一行代表了一個parent cursor， address字段表示其內存地址。

• 子游標

  SQL文本相同，但是因執行環境等不同，會生成多個執行計劃。

  包含了SQL的metadata，即使得這個SQL可以執行的所有相關信息，如OBJECT和權限，優化器設置，執行計劃等。v$sql中的每一行表示了一個child cursor，根據hash value和address與parent cursor 關聯。child cursor有自己的address，即V$SQL.CHILD_ADDRESS。

案例：

1. 創建一個用戶

SQL> conn / as sysdba
Connected.
SQL> create user ding identified by ding;

User created.

SQL> grant resource,connect to ding;

Grant succeeded.

2. 生成數據

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

Table created.

將數據庫重啟

3. 分別登錄scott和ding用戶，執行如下查詢

SQL> select * from emp;

4. 查看父游標

SQL> COL SQL_TEXT FOR A30
SQL> COL SQL_ID FOR A20
SQL> SET LINESIZE 200
SQL> 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

兩次查詢共享了一個父游標，該父游標被load 2次，表示被硬解析了兩次。VERSION_COUNT表示子游標的個數。

5. 查看子游標

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	      1
a2dk8bdn0ujx7	     select * from emp			     1		  1	      1

6. scott用戶下的語句再執行一遍，再次查看父子游標

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變成了3. 

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 DING
a2dk8bdn0ujx7	     select * from emp			     1		  1	      2 SCOTT

只有文本完全相同，才能共享父游標。哪怕是語句的語義、環境等有完全相同，sql文本稍微不同都不行。例如如下sql

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

那么當父游標相同，有多個子游標時，我們如何知道是何原因導致不共享子游標呢？

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

  這個表中會有什么*MISMATCH的字段，如果該值為Y，就表示是因為這個字段指示的內容不一致導致不能共享子游標。

游標的生命周期：

（1）打開游標（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.

  系統會在UGA中分配相關的內存結構,就是獲得游標句柄的過程，這時的游標還未和sql語句有關聯；

（2）解析游標(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.

  有一條sql與游標相關聯，并將執行解析過后的執行計劃放在library cache（SGA的shared pool下）中，UGA中生成指向這個共享游標的指針；即session cursor 指向shared cursor。 一個session cursor 只能指向一個shared cursor，而一個shared cursor 可以指向多個session cursor。
（3）定義輸出變量（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.

  如果sql語句返回數據，必須定義接收數據的變量，對delete，update，insert來說是returning；

（4）綁定輸入變量（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.

   綁定過程是不做檢查的；
（5）執行游標(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.

  這步數據庫引擎其實不做什么重要事情，而對大多數sql語句來說，真正處理過程是到fetch獲取數據階段；
（6）獲取游標(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.

   真正的處理過程，有返回數據的話，必須提供輸出變量(dbms_sql.column_value)；
（7）關閉游標(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.

   釋放UGA中相關資源，庫緩存中共享游標不會被清除。

   當游標被關閉后，還可以繼續緩存在內存中，參數SESSION_CACHED_CURSORS定義當前Session已經關閉并被緩存的游標的最大數量,即單個session中同時能cache住的soft closed session cursor的最大數量。