[20121026]11g下訪問v$sql_shared_memory.txt

[20121026]11g下訪問v$sql_shared_memory.txt

參考連結：

select * from v$version where rownum<=1;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> select * from v$sql_shared_memory;

no rows selected

SQL> column view_definition format a100

SQL> select * from V$FIXED_VIEW_DEFINITION where view_name='GV$SQL_SHARED_MEMORY'

VIEW_NAME                      VIEW_DEFINITION

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

GV$SQL_SHARED_MEMORY           select /*+use_nl(h,c)*/ c.inst_id,kglnaobj,kglfnobj, kglnahsh, kglobt03, kglobhd6, rtrim(substr(ksmc

                               hcom, 1, instr(ksmchcom, ':', 1, 1) - 1)), ltrim(substr(ksmchcom,              -(length(ksmchcom) -

                               (instr(ksmchcom, ':', 1, 1))),               (length(ksmchcom) - (instr(ksmchcom, ':', 1, 1)) + 1)))

                               , ksmchcom, ksmchptr, ksmchsiz, ksmchcls, ksmchtyp, ksmchpar from x$kglcursor c, x$ksmhp h where ksm

                               chds = kglobhd6 and kglhdadr != kglhdpar

檢視執行計劃:

SQL> select * from v$sql_shared_memory;

no rows selected

SQL> @dpc 

PLAN_TABLE_OUTPUT

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

SQL_ID  7q80kq23v3nd1, child number 0

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

select * from v$sql_shared_memory

Plan hash value: 2632394999

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

| Id  | Operation         | Name        | E-Rows | Cost (%CPU)|

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

|   0 | SELECT STATEMENT  |             |        |     1 (100)|

|   1 |  NESTED LOOPS     |             |      1 |     0   (0)|

|   2 |   FIXED TABLE FULL| X$KSMHP     |      1 |            |

|*  3 |   FIXED TABLE FULL| X$KGLCURSOR |      1 |     0   (0)|

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

Predicate Information (identified by operation id):

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

   3 - filter(("C"."INST_ID"=USERENV('INSTANCE') AND

              "KGLHDADR"<>"KGLHDPAR" AND "KSMCHDS"="KGLOBHD6"))

--而11g下，先掃描X$KSMHP ，然後在掃描X$KGLCURSOR ,然後選擇nested loop.

執行如下:

SQL> select * from X$KSMHP;

no rows selected

--自然先訪問X$KSMHP會沒有結果.加入提示leading(c,h),這樣先掃描x$kglcursor.如下:

SELECT /*+ leading(c,h) use_nl(c,h)*/

       c.inst_id, kglnaobj, kglfnobj, kglnahsh, kglobt03, kglobhd6,

       RTRIM (SUBSTR (ksmchcom, 1, INSTR (ksmchcom, ':', 1, 1) - 1)),

       LTRIM (SUBSTR (ksmchcom,

                      - (LENGTH (ksmchcom) - (INSTR (ksmchcom, ':', 1, 1))),

                      (LENGTH (ksmchcom) - (INSTR (ksmchcom, ':', 1, 1)) + 1

                      )

                     )

             ),

       ksmchcom, ksmchptr, ksmchsiz, ksmchcls, ksmchtyp, ksmchpar

  FROM x$kglcursor c, x$ksmhp h

 WHERE ksmchds = kglobhd6 AND kglhdadr != kglhdpar;

--有結果.看看執行計劃:

SQL> @ dpc 378frj3u3phsc

PLAN_TABLE_OUTPUT

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

SQL_ID  378frj3u3phsc, child number 0

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

SELECT /*+ leading(c,h) use_nl(c,h)*/        c.inst_id, kglnaobj,

kglfnobj, kglnahsh, kglobt03, kglobhd6,        RTRIM (SUBSTR (ksmchcom,

1, INSTR (ksmchcom, ':', 1, 1) - 1)),        LTRIM (SUBSTR (ksmchcom,

                    - (LENGTH (ksmchcom) - (INSTR (ksmchcom, ':', 1,

1))),                       (LENGTH (ksmchcom) - (INSTR (ksmchcom, ':',

1, 1)) + 1                       )                      )

),        ksmchcom, ksmchptr, ksmchsiz, ksmchcls, ksmchtyp, ksmchpar

FROM x$kglcursor c, x$ksmhp h  WHERE ksmchds = kglobhd6 AND kglhdadr !=

kglhdpar

Plan hash value: 1141239260

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

| Id  | Operation                | Name            | E-Rows | Cost (%CPU)|

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

|   0 | SELECT STATEMENT         |                 |        |     1 (100)|

|   1 |  NESTED LOOPS            |                 |      1 |     0   (0)|

|*  2 |   FIXED TABLE FULL       | X$KGLCURSOR     |     99 |     0   (0)|

|*  3 |   FIXED TABLE FIXED INDEX| X$KSMHP (ind:1) |      1 |     0   (0)|

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

Predicate Information (identified by operation id):

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

   2 - filter("KGLHDADR"<>"KGLHDPAR")

   3 - filter("KSMCHDS"="KGLOBHD6")

--可以發現改變執行計劃,訪問X$KSMHP的操作是FIXED TABLE FIXED INDEX,與上面的不同.

--知道這些,要11g下有結果輸出,可以加入提示:

1.建立自己檢視,加入需要的提示.

2.執行時加入提示,顯示計劃時加入advanced 或者outline.

select * from v$sql_shared_memory;

select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALLSTATS LAST PEEKED_BINDS cost advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID  7q80kq23v3nd1, child number 0

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

select * from v$sql_shared_memory

Plan hash value: 2632394999

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

| Id  | Operation         | Name        | E-Rows | Cost (%CPU)|

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

|   0 | SELECT STATEMENT  |             |        |     1 (100)|

|   1 |  NESTED LOOPS     |             |      1 |     0   (0)|

|   2 |   FIXED TABLE FULL| X$KSMHP     |      1 |            |

|*  3 |   FIXED TABLE FULL| X$KGLCURSOR |      1 |     0   (0)|

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

Outline Data

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

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

      DB_VERSION('11.2.0.1')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$88122447")

      MERGE(@"SEL$641071AC")

      OUTLINE(@"SEL$1")

      OUTLINE(@"SEL$641071AC")

      MERGE(@"SEL$07BDC5B4")

      OUTLINE(@"SEL$2")

      OUTLINE(@"SEL$07BDC5B4")

      MERGE(@"SEL$4")

      OUTLINE(@"SEL$3")

      OUTLINE(@"SEL$4")

      FULL(@"SEL$88122447" "H"@"SEL$4")

      FULL(@"SEL$88122447" "C"@"SEL$4")

      LEADING(@"SEL$88122447" "H"@"SEL$4" "C"@"SEL$4")

      USE_NL(@"SEL$88122447" "C"@"SEL$4")

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

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

   3 - filter(("C"."INST_ID"=USERENV('INSTANCE') AND

              "KGLHDADR"<>"KGLHDPAR" AND "KSMCHDS"="KGLOBHD6"))

Note

--從outline date中取出如下

      LEADING(@"SEL$88122447" "H"@"SEL$4" "C"@"SEL$4")

      USE_NL(@"SEL$88122447" "C"@"SEL$4")

修改執行語句如下:

select  /*+      LEADING(@"SEL$88122447"  "C"@"SEL$4" "H"@"SEL$4")  USE_NL(@"SEL$88122447" "H"@"SEL$4" */ * from v$sql_shared_memory;

--這樣就有輸出了,另外我是以scott使用者執行,如果sys使用者執行.@"SEL$88122447"要修改@sel$5c160134. 方法一樣,檢視執行計劃加入outline或者advanced.

select  /*+      LEADING(@sel$5c160134  "C"@"SEL$3" "H"@"SEL$3")  USE_NL(@sel$5c160134 "H"@"SEL$3" */ * from v$sql_shared_memory;