[20121026]11g下访问v$sql_shared_memory.txt
参考链接：
http://jonathanlewis.wordpress.com/2012/02/27/geek-stuff-3/
select * from v$version where rownum
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;