3個視圖（v$sql、v$sqltext、V$SQLAREA）的區別

3個視圖（v$sql、v$sqltext、V$SQLAREA）的區別

準備從下面幾個方面去查看

a.        從官方文檔找到視圖的定義描述及視圖的結構和字段信息

b.       根據v$fixed_view_definition視圖找出題目原始的定義區別

c.        根據上面2點的推測，做測試驗證

 

第一：從官方文檔入手：

V$SQLTEXT

This view contains the text of SQL statements belonging to shared SQL cursors in the SGA.

Column	Datatype	Description
ADDRESS	RAW(4 | 8)	Used with HASH_VALUE to uniquely identify a cached cursor
HASH_VALUE	NUMBER	Used with ADDRESS to uniquely identify a cached cursor
SQL_ID	VARCHAR2(13)	SQL identifier of a cached cursor
COMMAND_TYPE	NUMBER	Code for the type of SQL statement (SELECT, INSERT, and so on)
PIECE	NUMBER	Number used to order the pieces of SQL text
SQL_TEXT	VARCHAR2(64)	A column containing one piece of the SQL text

根據piece和sql_text的長度推測，視圖v$sqltext中記錄的是一個sql切片后的信息。該視圖僅僅記錄sql的基本信息（sql的地址及內容），沒有記錄統計信息等。

V$SQLAREA和V$SQL

這2個視圖比較相似，所以對比分析。

V$SQLAREAlists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.

V$SQLlists statistics on shared SQL area without theGROUP BYclause and contains one row for each child of the original SQL text entered. Statistics displayed inV$SQLare normally updated at the end of query execution. However, for long running queries, they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are still in progress.

從文檔的定義中去看，v$sql應該是V$SQLAREA沒有group by的結果，反過來說，V$SQLAREA是v$sql group by后的結果。具體是根據什麽來group by的呢？等下看能不能從視圖的定義中得到答案。

 

 

以下為整理出來的2個視圖中有差異的欄位。根據紅色部份所示，V$SQLAREA應該是parent cursor的信息，v$sql應該是child curosr的信息。

		Column	Datatype	Description
1組	V$SQLAREA	VERSION_COUNT	NUMBER	Number of child cursors that are present in the cache under this parent
	V$SQL	無	無	無
2組	V$SQLAREA	無	無	無
	V$SQL	TYPE_CHK_HEAP	RAW(4)	Descriptor of the type check heap for this child cursor
3組	V$SQLAREA	無	無	無
	V$SQL	CHILD_NUMBER	NUMBER	Number of this child cursor
4組	V$SQLAREA	無	無	無
	V$SQL	SERVICE	VARCHAR2(64)	Service name
5組	V$SQLAREA	無	無	無
	V$SQL	SERVICE_HASH	NUMBER	Hash value for the name listed in SERVICE
6組	V$SQLAREA	無	無	無
	V$SQL	CHILD_ADDRESS	RAW(4 | 8)	Address of the child cursor
7組	V$SQLAREA	LAST_ACTIVE_CHILD_ADDRESS	RAW(4)	Address (identifier) of the child cursor that was the last to be active in the group (that is, the child cursor on behalf of which statistics in V$SQL were updated)
	V$SQL	無	無	無
8組	V$SQLAREA	無	無	無
	V$SQL	SQLTYPE	NUMBER	Denotes the version of the SQL language used for this statement

 

 

 

第二：根據v$fixed_view_definition查定義

先不看具體的欄位，先看看數據的來源

V$SQLAREA 

select   欄位 from GV$SQLAREA where inst_id = USERENV('Instance')

V$SQL 

select  欄位 from GV$SQL where inst_id = USERENV('Instance')

再根據v$fixed_view_definition朝下看：

GV$SQLAREA

select 欄位  from  x$kglcursor_child_sqlid  where kglobt02 != 0

GV$SQL

Select 欄位  from x$kglcursor_child

兩個內部表該怎麼去理解呢？目前還不清楚，暫停留在這裡。

附上原始帖子：http://www.itpub.net/thread-181450-1-1.html

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25609824/viewspace-1148137/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25609824/viewspace-1148137/