【效能調整】系統檢視（二）

V$SQL, V$SQLTEXT，V$SQLAREA，V$SQL_PLAN

V$SQL_BIND_DATA，V$SQLSTATS，V$SQL_CURSOR，V$SQL_SHARED_CURSOR

V$SQL_WORKAREA_ACTIVEV$SQL_WORKAREA，V$SQL_WORKAREA_HISTOGRAM

一系列sql相關檢視，sql調優的重要檢視。

SQL> select a.object_name from dba_objects a

  2  where a.object_name like 'V$SQL%' order by object_name;

 

OBJECT_NAME

--------------------------------------------------------------------------------

V$SQL

V$SQLAREA

V$SQLAREA_PLAN_HASH

V$SQLSTATS

V$SQLTEXT

V$SQLTEXT_WITH_NEWLINES

V$SQL_BIND_CAPTURE

V$SQL_BIND_DATA

V$SQL_BIND_METADATA

V$SQL_CURSOR

V$SQL_JOIN_FILTER

V$SQL_OPTIMIZER_ENV

V$SQL_PLAN

V$SQL_PLAN_STATISTICS

V$SQL_PLAN_STATISTICS_ALL

V$SQL_REDIRECTION

V$SQL_SHARED_CURSOR

V$SQL_SHARED_MEMORY

V$SQL_WORKAREA

V$SQL_WORKAREA_ACTIVE

V$SQL_WORKAREA_HISTOGRAM

 

21 rows selected

 

SQL>

 

Sql相關的檢視，幾個欄位很重要，標示sql的id，sql的地址，sql的hash value，還有plan的hash value。

通過與session裡頭的sql相關欄位關聯，可以查詢sql的session和system狀態或其他資訊。

 

通常有人喜歡問v$sql,v$sqlarea,v$sqltext這三個檢視的區別，一般還真很少注意這個(這個很重要麼？)，看看就曉得了：

SELECT view_definition FROM v$fixed_view_definition        WHERE view_name='GV$SQL';

SELECT view_definition FROM v$fixed_view_definition        WHERE view_name='GV$SQLAREA';

SELECT view_definition FROM v$fixed_view_definition        WHERE view_name='GV$SQLTEXT';

這裡可以看到每個檢視的定義，可以發現者三個檢視分別基於下面三個表：

x$kglcursor_child

x$kglcursor_child_sqlid

x$kglna

這麼來看，前面兩個貌似差不多，看看區別：

SQL> select count(distinct a.kglobt03),count(*) from x$kglcursor_child a ;

 

COUNT(DISTINCTA.KGLOBT03)   COUNT(*)

------------------------- ----------

                      722        789

SQL> select count(distinct a.kglobt03) sqlid,count(*) from x$kglcursor_child_sqlid a ;

 

     SQLID   COUNT(*)

---------- ----------

       723        723

SQL> select count(distinct a.kglnasqlid),count(*) from x$kglna a where kgloboct != 0 ;

 

COUNT(DISTINCTA.KGLNASQLID)   COUNT(*)

--------------------------- ----------

                        820       4011

 

從上面可以看出v$sql和v$sqlarea還是很相近的，x$kglcursor_child_sqlid相當於按照sqlid等欄位對x$kglcursor_child做了一個彙總後的結果：

SQL> select b.SQL_ID,count(*) from v$sqlarea b

  2  group by b.SQL_ID having count(*) > 1

  3  ;

 

SQL_ID          COUNT(*)

------------- ----------

SQL> select count(*) from (

  2  select b.SQL_ID,count(*) from v$sql b

  3  group by b.SQL_ID having count(*) > 1)

  4  ;

 

  COUNT(*)

----------

        50

 

再來看v$sqltext

select d.SQL_TEXT, d.SQL_FULLTEXT, a.PIECE, a.SQL_TEXT

  from v$sqltext a,

       (select sid from v$mystat b where rownum = 1) b,

       v$session c,

       v$sql d

 where c.Sid = b.sid

   and c.SQL_ADDRESS = d.ADDRESS

   and c.SQL_HASH_VALUE = d.HASH_VALUE

   and c.SQL_ID = d.SQL_ID

   and d.ADDRESS = a.ADDRESS

   and d.HASH_VALUE = a.HASH_VALUE

   and d.SQL_ID = a.SQL_ID

 order by a.ADDRESS, a.HASH_VALUE, a.SQL_ID, a.PIECE;

 

SELECT osuser, username,piece, sql_text

  from v$session a, v$sqltext b

 where a.sql_address = b.address

 order by address, piece;

顯然v$sqltext是分片了的sql。如果是9i，v$sql裡面找不到完整的sql的時候，則需要從v$sqlarea中找或者拼v$sqltext的sql片。

 

V$sql_plan

對於sql調優來說非常重要的一個檢視，不過一般都喜歡從plan_table中看，也可以通過靈活的sql來獲得更多資訊。

select c.PROGRAM,

       c.PROCESS,

       c.USERNAME,

       a.OPERATION,

       a.OPTIONS,

       a.OBJECT_NAME,

       a.OBJECT_TYPE,

       a.OPTIMIZER,

       a.ID,

       a.PARENT_ID,

       a.DEPTH,

       a.COST,

       a.CARDINALITY,

       a.BYTES,

       a.CPU_COST,

       a.IO_COST,

       a.ACCESS_PREDICATES,

       a.FILTER_PREDICATES,

       a.PROJECTION

  from v$sql_plan a,

       (select sid from v$mystat where rownum = 1) b,

       v$session c,

       v$sql d

 where b.sid = c.SID

   and c.SQL_ADDRESS = d.ADDRESS

   and c.sql_id = d.sql_id

   and c.SQL_HASH_VALUE = d.HASH_VALUE

   and d.PLAN_HASH_VALUE = a.PLAN_HASH_VALUE

   and a.ADDRESS = d.ADDRESS

   and a.sql_id = d.sql_id

   and a.HASH_VALUE = d.HASH_VALUE;

其中這裡的PLAN_HASH_VALUE就是autotrace後資訊中的Plan hash value: xxxxxxxx

 

V$sql_Shared_Memory

可以檢視在shared pool裡頭的sql以及分配的記憶體狀況，例如：

select a.CHUNK_SIZE,

       a.CHUNK_TYPE,

       a.CHUNK_PTR,

       a.ALLOC_CLASS,

       a.HEAP_DESC,

       a.SQL_TEXT

  from V$sql_Shared_Memory a,

       (select sid from v$mystat where rownum = 1) b,

       v$sql c,

       v$session d

 where d.SID = b.sid

   and d.SQL_ADDRESS = c.ADDRESS

   and d.SQL_HASH_VALUE = c.HASH_VALUE

   and d.SQL_ID = c.SQL_ID

   and c.HASH_VALUE = a.HASH_VALUE

   and c.SQL_ID = a.SQL_ID

;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-674363/，如需轉載，請註明出處，否則將追究法律責任。