2015年8月23日 星期日
[ORACLE]轉載：Version Count數目過多診斷的方法
[ORACLE][11G][ISSUE][CURSOR][SQL]
Version Count數目過多診斷的方法
Oracle SQL調優是我們進行應用系統調優的一個重要方面。一個SQL的書寫方式、執行計畫和執行時間會顯著的影響到系統的投產性能。本篇我們主要聊一下SQL version count數目過多，子游標過多的問題診斷。
1、關於version count
首先我們一起來回顧一下Oracle的“父子游標”（child cursor and parent cursor）概念。在Oracle中，任何一個執行的SQL語句，都會以游標cursor的形式進行處理。具體來說，一個SQL要涉及到兩個游標物件，共用游標shared cursor和私有游標private cursor。
Private cursor是駐留在Oracle Server Process PGA內部，只能被一個Server Process對應的會話使用。而shared cursor駐留在SGA的shared pool中，具體位於shared pool中的library cache。
對shared cursor而言，主要緩存的目的在於執行計畫的共用。一個SQL經過validate和parse過程，會形成父游標和子游標的配對組合。SQL文本完全相同的語句，會共用父游標。而環境資訊、物件資訊相同的SQL語句，才可能共用子游標。當找不到對應的子游標或者父游標時，也就意味著找不到可共用的執行計畫。這樣對應的SQL就需要發生hard parse，重新生成執行計畫。
一個父游標下對應的子游標個數，我們成為version count。每一個子游標對應一個執行計畫物件。下面通過示例來演示，依然選擇Oracle 11g環境進行試驗。
SQL> select * from v$version;
BANNER
------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
構建資料表T。
SQL> create table t as select * from dba_objects;
Table created
SQL> create index idx_t_owner on t(owner);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
我們使用實驗SQL，通過視圖v$sqlarea和v$sql可以觀察到library cache中的父子游標情況。v$sqlarea中保存父游標資訊，而v$sql保存子游標資訊。
SQL> select /*+version_count */count(*) from t where wner='SCOTT';
COUNT(*)
----------
14
SQL> select sql_id, version_count from v$sqlarea where sql_text like 'select /*+version_count */count(*)%';
SQL_ID VERSION_COUNT
------------- -------------
54fuganxkyky6 1
SQL> select sql_id, child_number from v$sql where sql_id='54fuganxkyky6';
SQL_ID CHILD_NUMBER
------------- ------------
54fuganxkyky6 0
SQL語句（sql_id=54fuganxkyky6）對應一個父游標和一個子游標。
version count就表示當前父游標下對應子游標的個數。如果一個父游標對應的子游標version count過多，也就是對應了很多的子游標物件。這樣，當server process檢查可共用的游標時，就需要長時間的檢索子游標清單。
最有名的version count過多問題是由於設置cursor_sharing參數為similar後，引發的version count錯誤。
此外，version count過多也是我們需要診斷SQL為什麼不會共用的一個出發點。比較常用的有兩種，本篇中進行詳細介紹：
2、v$sql_shared_cursor視圖
SQL執行計畫的生成，是受到很多因素影響的。相同父游標只是表示輸入SQL的字面值相同。子游標對應的因素，如優化器類型、optimizer_mode、對應物件使用權限等的差異，都會影響到子游標的共用。
在Oracle 11g中，一個新的視圖被提供出來，用於?明診斷子游標不共用問題的原因，就是v$sql_shared_cursor。
SQL> desc v$sql_shared_cursor;
Name Type Nullable Default Comments
----------------------------- ------------ -------- ------- --------
SQL_ID VARCHAR2(13) Y
ADDRESS RAW(4) Y
CHILD_ADDRESS RAW(4) Y
CHILD_NUMBER NUMBER Y
UNBOUND_CURSOR VARCHAR2(1) Y
SQL_TYPE_MISMATCH VARCHAR2(1) Y
OPTIMIZER_MISMATCH VARCHAR2(1) Y
OUTLINE_MISMATCH VARCHAR2(1) Y
（篇幅原因，省略……）
PDML_ENV_MISMATCH VARCHAR2(1) Y
INST_DRTLD_MISMATCH VARCHAR2(1) Y
BIND_LENGTH_UPGRADEABLE VARCHAR2(1) Y
該視圖是一個寬列視圖，通過sql_id和child_number就可以定義某個特定子游標的資訊。其他大部分列都是以varchar2(1)的Y/N取值，每列的含義都是一個不能共用的理由。注意：這個理由N表示的是不能共用第一個子游標child_number=0的理由。下面通過簡單的示例演示，繼續上面的實驗。
SQL> select sql_id, child_number from v$sql where sql_id='54fuganxkyky6';
SQL_ID CHILD_NUMBER
------------- ------------
54fuganxkyky6 0
通過變換環境資訊來生成新的子游標。
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
--工作階段層面取值變化；
SQL> alter session set optimizer_mode=first_rows;
Session altered
SQL> select value from v$parameter where name='optimizer_mode';
VALUE
-----------------------------------------------------------------
FIRST_ROWS
重新執行SQL，檢查執行計畫。
SQL> select /*+version_count */count(*) from t where wner='SCOTT';
COUNT(*)
----------
14
SQL> select sql_id, version_count from v$sqlarea where sql_text like 'select /*+version_count */count(*)%';
SQL_ID VERSION_COUNT
------------- -------------
54fuganxkyky6 2
SQL> select sql_id, child_number, OPTIMIZER_MODE from v$sql where sql_id='54fuganxkyky6';
SQL_ID CHILD_NUMBER OPTIMIZER_MODE
------------- ------------ --------------
54fuganxkyky6 0 ALL_ROWS
54fuganxkyky6 1 FIRST_ROWS
生成了兩個執行計畫。此時，如果我們檢查v$sql_shared_cursor，就可以發現非共用的原因。
SQL> select sql_id, child_number, OPTIMIZER_MODE_MISMATCH from v$sql_shared_cursor where sql_id='54fuganxkyky6';
SQL_ID CHILD_NUMBER OPTIMIZER_MODE_MISMATCH
------------- ------------ -----------------------
54fuganxkyky6 0 N
54fuganxkyky6 1 Y
在實際中，使用v$sql_shared_cursor就可以確定非共用SQL子游標的原因。
3、version-rpt腳本
在MOS438755.1中，Oracle提供了一個專門的腳本程式，用於協助診斷high SQL version count問題。
首先，我們需要從MOS上下載到腳本version_rpt.sql，目前筆者的版本為3.1.2。使用sys用戶登錄之後，調用該腳本創建相應的資料庫物件。
SQL> @version_rpt3_12.sql
視圖已創建。
函數已創建。
注意，在筆者的實驗中，直接從MOS上下載的檔是不能正確編譯通過的。要將下面片段進行修改：
--原有結構
SELECT COLUMN_NAME,0
from cols
where table_name='SQL_SHARED_CURSOR'
and CHAR_LENGTH=1
order by column_id;
--修改之後
SELECT COLUMN_NAME,0 bulk collect into v_colname,v_Ycnt
from dba_tab_cols
where table_name='SQL_SHARED_CURSOR'
and CHAR_LENGTH=1
order by column_id;
借助創建的函數version_rpt，可以詳細分析version count問題。具體使用的方法有三個場景：
u 列出version count大於某個閾值的報告，以sql_id方式顯示
SQL> select b.* from v$sqlarea a ,table(version_rpt(a.sql_id)) b where loaded_versions >=4;
COLUMN_VALUE
----------------------------------------------------------------------------
Version Count Report Version 3.1.2 -- Today's Date 12-5月 -12 15:20
RDBMS Version :11.2.0.1.0 Host: oracle11g Instance 1 : wilson
==================================================================
Addr: 385D72E0 Hash_Value: 3393782897 SQL_ID 9p6bq1v54k13j
Sharable_Mem: 51266 bytes Parses: 5
Stmt:
0 select value$ from sys.props$ where name = :1
Versions Summary
----------------
SQL_TYPE_MISMATCH :3
BIND_MISMATCH :1
Total Versions:3
~
Plan Hash Value Summary
-----------------------
Plan Hash Value Count
=============== =====
415205717 4
COLUMN_VALUE
-----------------------------------------------------------------------
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for SQL_TYPE_MISMATCH :
No details available
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for BIND_MISMATCH :
Consolidated details for :
BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF and
BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)
from v$sql_bind_capture
COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE (PRECISION,SCALE)
======== ======== =============== =============== ======== ================
4 1 32 32 1 (,)
SUM(DECODE(column,Y, 1, 0) FROM V$SQL
IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
=========== ================= ============= ============
0 0 0 4
####
COLUMN_VALUE
--------------------------------------------------------------------------------
To further debug Ask Oracle Support for the appropiate level LLL.
alter session set events
'immediate trace name cursortrace address 3393782897, level LLL';
To turn it off do use address 1, level 2147483648
================================================================
46 rows selected
u 列出version count大於某個閾值的報告，以sql_hash方式顯示
SQL> select b.* from v$sqlarea a ,table(version_rpt(null,a.hash_value)) b where loaded_versions>=4;
COLUMN_VALUE
--------------------------------------------------------------------------------
Version Count Report Version 3.1.2 -- Today's Date 12-5月 -12 15:22
RDBMS Version :11.2.0.1.0 Host: oracle11g Instance 1 : wilson
==================================================================
Addr: 385D72E0 Hash_Value: 3393782897 SQL_ID 9p6bq1v54k13j
Sharable_Mem: 51266 bytes Parses: 5
Stmt:
0 select value$ from sys.props$ where name = :1
Versions Summary
----------------
SQL_TYPE_MISMATCH :3
BIND_MISMATCH :1
Total Versions:3
~
Plan Hash Value Summary
-----------------------
Plan Hash Value Count
=============== =====
415205717 4
（篇幅原因，省略……）
u 列出某個特定sql_id的version count
SQL> select * from table(version_rpt('9p6bq1v54k13j'));
COLUMN_VALUE
--------------------------------------------------------------------------------
Version Count Report Version 3.1.2 -- Today's Date 12-5月 -12 15:23
RDBMS Version :11.2.0.1.0 Host: oracle11g Instance 1 : wilson
==================================================================
Addr: 385D72E0 Hash_Value: 3393782897 SQL_ID 9p6bq1v54k13j
Sharable_Mem: 51266 bytes Parses: 5
Stmt:
0 select value$ from sys.props$ where name = :1
Versions Summary
----------------
SQL_TYPE_MISMATCH :3
BIND_MISMATCH :1
Total Versions:3
三種方法，都可以很直觀的?明我們定位問題和錯誤。
4、結論
version count問題是Oracle SQL共用的一個方面。借助適當的工具手段，我們可以準確的定位問題，解決問題。
轉自ITPUB
http://blog.itpub.net/17203031/viewspace-729872/
訂閱：
張貼留言 (Atom)
沒有留言:
張貼留言