跟蹤oracle中sql語句執行過程及相關知識拓展，oraclesql

跟蹤oracle中sql語句執行過程及相關知識拓展，oraclesql

select * from v$sqlarea;

select * from v$sqlarea where first_load_time>'2010-11-27/09:30:00';

        這個方法查詢結果每條記錄顯示一條查詢語句，且只能查詢sql_text小於1000字元的，多餘的會被截斷。
        改進一下：

select * from v$sqlarea where first_load_time>'2010-11-27/09:30:00'  and sql_text like 'insert%';  //可以進一步篩選

         如果要顯示多餘1000的用fulltext.

知識拓展：

       1、V$SQLTEXT

        本視圖包括 Shared pool中 SQL語句的完整文本，一條 SQL語句可能分成多個塊被儲存於多個記錄內。
　　註： V$SQLAREA只包括頭 1000個字元。

V$SQLTEXT中的常用列
l         HASH_VALUE： SQL語句的 Hash值
l         ADDRESS： sql語句在 SGA中的地址
l         SQL_TEXT： SQL文本。
l         PIECE： SQL語句塊的序號

V$SQLTEXT中的串連列：

Column                                          View                                        Joined Column(s)

HASH_VALUE, ADDRESS         V$SQL, V$SESSION            HASH_VALUE, ADDRESS
HASH_VALUE. ADDRESS         V$SESSION                          SQL_HASH_VALUE, SQL_ADDRESS

        樣本：已知 hash_value:3111103299，查詢 sql語句：

select * from v$sqltextwhere hashvalue= '3111103299'order by piece

        2、V$SQLAREA

       本視圖持續跟蹤所有 shared pool中的共用 cursor，在 shared pool中的每一條 SQL語句都對應一列。本視圖在分析 SQL語句資源使用方面非常重要。

V$SQLAREA中的資訊列

l         HASH_VALUE： SQL語句的 Hash值。
l         ADDRESS： SQL語句在 SGA中的地址。
這兩列被用於鑒別 SQL語句，有時，兩條不同的語句可能 hash值相同。這時候，必須連同 ADDRESS一同使用來確認 SQL語句。
l         PARSING_USER_ID：為語句解析第一條 CURSOR的使用者
l         VERSION_COUNT：語句 cursor的數量
l         KEPT_VERSIONS：
l         SHARABLE_MEM： cursor使用的共用記憶體總數
l         PERSISTENT_MEM： cursor使用的常駐記憶體總數
l         RUNTIME_MEM： cursor使用的運行時記憶體總數。
l         SQL_TEXT： SQL語句的文本（最大隻能儲存該語句的前 1000個字元）。
l         MODULE,ACTION：使用了 DBMS_APPLICATION_INFO時 session解析第一條 cursor時的資訊
V$SQLAREA中的其它常用列
l         SORTS: 語句的排序數
l         CPU_TIME: 語句被解析和執行的 CPU時間
l         ELAPSED_TIME: 語句被解析和執行的共用時間
l         PARSE_CALLS: 語句的解析調用 (軟、硬 )次數
l         EXECUTIONS: 語句的執行次數
l         INVALIDATIONS: 語句的 cursor失效次數
l         LOADS: 語句載入 (載出 )數量
l         ROWS_PROCESSED: 語句返回的列總數

V$SQLAREA中的串連列
Column                                      View                                                                      Joined Column(s)
HASH_VALUE, ADDRESS     V$SESSION                                                         SQL_HASH_VALUE, SQL_ADDRESS
HASH_VALUE, ADDRESS     V$SQLTEXT, V$SQL, V$OPEN_CURSOR     HASH_VALUE, ADDRESS
SQL_TEXT                                V$DB_OBJECT_CACHE                                    NAME

樣本：
        A.查看消耗資源最多的 SQL：

SELECT hash_value, executions, buffer_gets, disk_reads, parse_callsFROM V$SQLAREAWHERE buffer_gets > 10000000 OR disk_reads > 1000000ORDER BY buffer_gets + 100 * disk_reads DESC ;

       B、查看某條 SQL 陳述式的資源消耗：

SELECT hash_value, buffer_gets, disk_reads, executions, parse_callsFROM V$SQLAREAWHERE hash_Value = 228801498 AND address = hextoraw( 'CBD8E4B0' );

      C、尋找前10條效能差的sql語句：

SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea ;  SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea order BY disk_reads DESC )where ROWNUM<10 ;

        EXECUTIONS表示同一條SQL語句一共執行了多少次，SORTS表示排序的次數，DISK_READS表示物理讀的數量。

        3、v$sql

        一條語句可以映射多個 cursor,因為對象所指的 cursor可以有不同使用者 (如例 1)。如果有多個 cursor(子遊標 )存在，在 V$SQLAREA為所有 cursor提供集合資訊。

       例 1：這裡介紹以下 child cursor

user A: select * from tbluser B: select * from tbl

       大家認為這兩條語句是不是一樣的啊，可能會有很多人會說是一樣的，但我告訴你不一定，那為什麼呢？
       這個 tblA看起來是一樣的，但是不一定哦，一個是 A使用者的 , 一個是 B使用者的，這時他們的執行計畫分析代碼差別可能就大了哦，改下寫法大家就明白了 :

select * from A.tblselect * from B.tbl

        在個別 cursor上， v$sql可被使用。該視圖包含 cursor層級資料。當試圖定位 session或使用者以分析 cursor時被使用。
　　PLAN_HASH_VALUE列儲存的是數值表示的 cursor執行計畫。可被用來對比執行計畫。 PLAN_HASH_VALUE讓你不必一行一行對比即可輕鬆鑒別兩條執行計畫是否相同。

V$SQL中的列說明：
l         SQL_TEXT： SQL文本的前 1000個字元
l         SHARABLE_MEM：佔用的共用記憶體大小 (單位： byte)
l         PERSISTENT_MEM：生命期內的固定記憶體大小 (單位： byte)
l         RUNTIME_MEM：執行期內的固定記憶體大小
l         SORTS：完成的排序數
l         LOADED_VERSIONS：顯示上下文堆是否載入， 1是 0否
l         OPEN_VERSIONS：顯示子遊標是否被鎖， 1是 0否
l         USERS_OPENING：執行語句的使用者數
l         FETCHES： SQL語句的 fetch數。
l         EXECUTIONS：自它被載入緩衝庫後的執行次數
l         USERS_EXECUTING：執行語句的使用者數
l         LOADS：對象被載入過的次數
l         FIRST_LOAD_TIME：初次載入時間
l         INVALIDATIONS：無效的次數
l         PARSE_CALLS：解析調用次數
l         DISK_READS：讀磁碟次數
l         BUFFER_GETS：讀緩衝區次數
l         ROWS_PROCESSED：解析 SQL語句返回的總列數
l         COMMAND_TYPE：命令類型代號
l         OPTIMIZER_MODE： SQL語句的最佳化器模型
l         OPTIMIZER_COST：最佳化器給出的本次查詢成本
l         PARSING_USER_ID：第一個解析的使用者識別碼
l         PARSING_SCHEMA_ID：第一個解析的計劃 ID
l         KEPT_VERSIONS：指出是否當前子遊標被使用 DBMS_SHARED_POOL包標記為常駐記憶體
l         ADDRESS：當前遊標父控制代碼地址
l         TYPE_CHK_HEAP：當前堆類型檢查說明
l         HASH_VALUE：緩衝庫中父語句的 Hash值
l         PLAN_HASH_VALUE：數值表示的執行計畫。
l         CHILD_NUMBER：子遊標數量
l         MODULE：在第一次解析這條語句是通過調用 DBMS_APPLICATION_INFO.SET_MODULE設定的模組名稱。
l         ACTION：在第一次解析這條語句是通過調用 DBMS_APPLICATION_INFO.SET_ACTION設定的動作名稱。
l         SERIALIZABLE_ABORTS：事務未能序列化次數
l         OUTLINE_CATEGORY：如果 outline在解釋 cursor期間被應用，那麼本列將顯示出 outline各類，否則本列為空白
l         CPU_TIME：解析 /執行 /取得等 CPU使用時間 (單位，毫秒 )
l         ELAPSED_TIME：解析 /執行 /取得等消耗時間 (單位，毫秒 )
l         OUTLINE_SID： outline session標識
l         CHILD_ADDRESS：子遊標地址
l         SQLTYPE：指出當前語句使用的 SQL語言版本
l         REMOTE：指出是否遊標是一個遠程映象 (Y/N)
l         OBJECT_STATUS：對象狀態 (VALID or INVALID)
l         IS_OBSOLETE：當子遊標的數量太多的時候，指出遊標是否被廢棄 (Y/N)

原文地址：
http://hi.baidu.com/clebean/item/73297be5da8cba0e8d3ea8e7
http://blog.sina.com.cn/s/blog_8019d3c10100rte3.html

oracle怎跟蹤一條sql語句的執行過程？

需要建立一張表來記錄
explain plan SET statement_id='name' FOR (這裡是你要調試的語句 )

SELECT
A.OPERATION,
OPTIONS,
OBJECT_NAME,
OBJECT_TYPE,
ID,
PARENT_ID
FROM
PLAN_TABLE A
WHERE
STATEMENT_ID='name'
ORDER BY
Id;

ID 'name'是一個標識，你可以自己取，欄位有很多個，以下是各個欄位的解釋（可能格式不對，你可以複製後看）：

欄位名 欄位類型 含義
STATEMENT_ID VARCHAR2(30) explain PLAN 語句中所指定的最優STATEMENT_ID 參數值, 如果在EXPLAN PLAN語句中沒有使用SET STATEMENT_ID，那麼此值會被設為NULL。

REMARKS VARCHAR2(80) 與被解釋規劃的各步驟相關聯的注釋最長可達80 位元組

OPERATION VARCHAR2(30) 各步驟所執行內部操作的名稱在某條語句所產生的第一行中該列的可能取值如下DELETE STATEMENT INSERT STATEMENT SELECT STATEMENT UPDATE STATEMENT

OPTIONS VARCHAR2(30) 對OPERATION 列中所描述操作的變種

OBJECT_NODE VARCHAR2(128) 用於訪問對象的資料庫連結database link 的名稱對於使用並存執行的本地查詢該列能夠描述操作中輸出的次序

OBJECT_OWNER VARCHAR2(30) 對於包含有表或索引的架構schema 給出其所有者的名稱

OBJECT_NAME VARCHAR2(30) 表或索引的名稱

OBJECT_INSTANCE INTEGER 根據對象出現在原始original 語句中的次序所給出的相應次序編號就原始的語句文本而論其處理順序為自左至右自外向內景象擴張view

OBJECT_TYPE VARCHAR2(30) 用於提供對象描述性資訊的修飾符例如索引的NON-UNIQUE

OPTIMIZER VARCHAR2(255) 當前最佳化程式的模式

ID INTEGER 分配給執行規劃各步驟的編號

PARENT_ID INTEGER 對ID 步驟的輸出進行操作的下一個執行步驟的ID

POSITION INTEGER 對於具有相同PARENT_ID 的步驟其相應的處理次序

COST INTEGER 根據最佳化程式的基於開銷的方法所估計出的操作開銷值對於使用基於規則方法的語句該列為空白該列值沒有特定的測量單位它只是一個用於比較執行規劃開銷大小的權重值

CARDINALITY INTEGER ......餘下全文>>
 
Oracle怎可以像SQLSERVER那樣很方便的跟蹤到執行的SQL語句？

v$session配合 v$sql,請查詢這2個視圖

SELECT * FROM v$session a,v$sql b
WHERE a.SQL_ID = b.SQL_ID(+)