查询视图v$sql_shared_memory没有数据返回
SYS@db1>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SYS@db1>select count(*) from v$sql_shared_memory;
COUNT(*)
----------
0
SYS@db1>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID g6xwfn7dmbcsg, child number 0
-------------------------------------
select count(*) from v$sql_shared_memory
Plan hash value: 1454219499
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | SORT AGGREGATE | | 1 | 37 | |
| 2 | NESTED LOOPS | | 1 | 37 | 0 (0)|
| 3 | FIXED TABLE FULL| X$KSMHP | 1 | 6 | |
|* 4 | FIXED TABLE FULL| X$KGLCURSOR | 1 | 31 | 0 (0)|
-----------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5C160134
3 - SEL$5C160134 / H@SEL$3
4 - SEL$5C160134 / C@SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5C160134")
MERGE(@"SEL$335DD26A")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$335DD26A")
MERGE(@"SEL$3")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
FULL(@"SEL$5C160134" "H"@"SEL$3")
FULL(@"SEL$5C160134" "C"@"SEL$3")
LEADING(@"SEL$5C160134" "H"@"SEL$3" "C"@"SEL$3")
USE_NL(@"SEL$5C160134" "C"@"SEL$3")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(("C"."INST_ID"=USERENV('INSTANCE') AND
"KGLHDADR"<>"KGLHDPAR" AND "KSMCHDS"="KGLOBHD6"))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
3 - "KSMCHDS"[RAW,8]
4 - "C"."INST_ID"[NUMBER,22], "KGLHDADR"[RAW,8], "KGLHDPAR"[RAW,8],
"KGLOBHD6"[RAW,8]
X$KSMHP 包含cursor的subheap信息, 但是无条件的查询没有数据返回
SYS@db1>select count(*) from X$KSMHP;
COUNT(*)
----------
0
想查出数据来必须要提供某个subheap的地址
SYS@db1>select KGLOBHD6 from X$KGLCURSOR where kglnaobj = 'select /*+ bwd */ * from dual' and "KGLHDADR"<>"KGLHDPAR";
KGLOBHD6
----------------
000000007EADC490
SYS@db1>select count(*) from X$KSMHP where KSMCHDS = hextoraw('000000007EADC490');
COUNT(*)
----------
57
这个执行计划首先扫描X$KSMHP, 所以没有数据返回
看看X$KSMHP,$KGLCURSOR的统计信息
SYS@db1>select table_name,num_rows,last_analyzed from dba_tab_statistics where table_name in ('X$KSMHP','X$KGLCURSOR');
TABLE_NAME NUM_ROWS LAST_ANAL
------------------------------ ---------- ---------
X$KSMHP 0 18-OCT-16
X$KSMHP的num_rows当然是零, X$KGLCURSOR不能收集统计信息
SYS@db1>exec dbms_stats.gather_table_stats(user,'X$KGLCURSOR');
BEGIN dbms_stats.gather_table_stats(user,'X$KGLCURSOR'); END;
*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "SYS"."X$KGLCURSOR", insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 24270
ORA-06512: at "SYS.DBMS_STATS", line 24332
ORA-06512: at line 1
删除X$KSMHP的统计信息看一下,
SYS@db1>exec dbms_stats.delete_table_stats(user,'X$KSMHP',no_invalidate=>false);
PL/SQL procedure successfully completed.
SYS@db1>select table_name,num_rows,last_analyzed from dba_tab_statistics where table_name in ('X$KSMHP','X$KGLCURSOR');
TABLE_NAME NUM_ROWS LAST_ANAL
------------------------------ ---------- ---------
X$KSMHP
SYS@db1>select count(*) from v$sql_shared_memory;
COUNT(*)
----------
226178
SYS@db1>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID g6xwfn7dmbcsg, child number 0
-------------------------------------
select count(*) from v$sql_shared_memory
Plan hash value: 3966365769
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | SORT AGGREGATE | | 1 | 37 | |
| 2 | NESTED LOOPS | | 1 | 37 | 0 (0)|
|* 3 | FIXED TABLE FULL | X$KGLCURSOR | 1 | 31 | 0 (0)|
|* 4 | FIXED TABLE FIXED INDEX| X$KSMHP (ind:1) | 1 | 6 | 0 (0)|
----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5C160134
3 - SEL$5C160134 / C@SEL$3
4 - SEL$5C160134 / H@SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5C160134")
MERGE(@"SEL$335DD26A")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$335DD26A")
MERGE(@"SEL$3")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
FULL(@"SEL$5C160134" "C"@"SEL$3")
FULL(@"SEL$5C160134" "H"@"SEL$3")
LEADING(@"SEL$5C160134" "C"@"SEL$3" "H"@"SEL$3")
USE_NL(@"SEL$5C160134" "H"@"SEL$3")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("C"."INST_ID"=USERENV('INSTANCE') AND
"KGLHDADR"<>"KGLHDPAR"))
4 - filter("KSMCHDS"="KGLOBHD6")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
3 - "C"."INST_ID"[NUMBER,22], "KGLHDADR"[RAW,8], "KGLHDPAR"[RAW,8],
"KGLOBHD6"[RAW,8]
4 - "KSMCHDS"[RAW,8]
先扫描X$KGLCURSOR就可以了
另一个解决方法:用hint
SYS@db1>select /*+ LEADING(@"SEL$5C160134" "C"@"SEL$3" "H"@"SEL$3" ) USE_NL(@"SEL$5C160134" "H"@"SEL$3") */
count(*) from v$sql_shared_memory;
COUNT(*)
----------
227773
SYS@db1>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID ahda5kmtzusvu, child number 0
-------------------------------------
select /*+ LEADING(@"SEL$5C160134" "C"@"SEL$3" "H"@"SEL$3" )
USE_NL(@"SEL$5C160134" "H"@"SEL$3") */ count(*) from v$sql_shared_memory
Plan hash value: 3966365769
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | SORT AGGREGATE | | 1 | 37 | |
| 2 | NESTED LOOPS | | 1 | 37 | 0 (0)|
|* 3 | FIXED TABLE FULL | X$KGLCURSOR | 1 | 31 | 0 (0)|
|* 4 | FIXED TABLE FIXED INDEX| X$KSMHP (ind:1) | 1 | 6 | 0 (0)|
----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5C160134
3 - SEL$5C160134 / C@SEL$3
4 - SEL$5C160134 / H@SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5C160134")
MERGE(@"SEL$335DD26A")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$335DD26A")
MERGE(@"SEL$3")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
FULL(@"SEL$5C160134" "C"@"SEL$3")
FULL(@"SEL$5C160134" "H"@"SEL$3")
LEADING(@"SEL$5C160134" "C"@"SEL$3" "H"@"SEL$3")
USE_NL(@"SEL$5C160134" "H"@"SEL$3")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("C"."INST_ID"=USERENV('INSTANCE') AND
"KGLHDADR"<>"KGLHDPAR"))
4 - filter("KSMCHDS"="KGLOBHD6")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
3 - "C"."INST_ID"[NUMBER,22], "KGLHDADR"[RAW,8], "KGLHDPAR"[RAW,8],
"KGLOBHD6"[RAW,8]
4 - "KSMCHDS"[RAW,8]
SYS@db1>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SYS@db1>select count(*) from v$sql_shared_memory;
COUNT(*)
----------
0
SYS@db1>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID g6xwfn7dmbcsg, child number 0
-------------------------------------
select count(*) from v$sql_shared_memory
Plan hash value: 1454219499
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | SORT AGGREGATE | | 1 | 37 | |
| 2 | NESTED LOOPS | | 1 | 37 | 0 (0)|
| 3 | FIXED TABLE FULL| X$KSMHP | 1 | 6 | |
|* 4 | FIXED TABLE FULL| X$KGLCURSOR | 1 | 31 | 0 (0)|
-----------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5C160134
3 - SEL$5C160134 / H@SEL$3
4 - SEL$5C160134 / C@SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5C160134")
MERGE(@"SEL$335DD26A")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$335DD26A")
MERGE(@"SEL$3")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
FULL(@"SEL$5C160134" "H"@"SEL$3")
FULL(@"SEL$5C160134" "C"@"SEL$3")
LEADING(@"SEL$5C160134" "H"@"SEL$3" "C"@"SEL$3")
USE_NL(@"SEL$5C160134" "C"@"SEL$3")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(("C"."INST_ID"=USERENV('INSTANCE') AND
"KGLHDADR"<>"KGLHDPAR" AND "KSMCHDS"="KGLOBHD6"))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
3 - "KSMCHDS"[RAW,8]
4 - "C"."INST_ID"[NUMBER,22], "KGLHDADR"[RAW,8], "KGLHDPAR"[RAW,8],
"KGLOBHD6"[RAW,8]
X$KSMHP 包含cursor的subheap信息, 但是无条件的查询没有数据返回
SYS@db1>select count(*) from X$KSMHP;
COUNT(*)
----------
0
想查出数据来必须要提供某个subheap的地址
SYS@db1>select KGLOBHD6 from X$KGLCURSOR where kglnaobj = 'select /*+ bwd */ * from dual' and "KGLHDADR"<>"KGLHDPAR";
KGLOBHD6
----------------
000000007EADC490
SYS@db1>select count(*) from X$KSMHP where KSMCHDS = hextoraw('000000007EADC490');
COUNT(*)
----------
57
这个执行计划首先扫描X$KSMHP, 所以没有数据返回
看看X$KSMHP,$KGLCURSOR的统计信息
SYS@db1>select table_name,num_rows,last_analyzed from dba_tab_statistics where table_name in ('X$KSMHP','X$KGLCURSOR');
TABLE_NAME NUM_ROWS LAST_ANAL
------------------------------ ---------- ---------
X$KSMHP 0 18-OCT-16
X$KSMHP的num_rows当然是零, X$KGLCURSOR不能收集统计信息
SYS@db1>exec dbms_stats.gather_table_stats(user,'X$KGLCURSOR');
BEGIN dbms_stats.gather_table_stats(user,'X$KGLCURSOR'); END;
*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "SYS"."X$KGLCURSOR", insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 24270
ORA-06512: at "SYS.DBMS_STATS", line 24332
ORA-06512: at line 1
删除X$KSMHP的统计信息看一下,
SYS@db1>exec dbms_stats.delete_table_stats(user,'X$KSMHP',no_invalidate=>false);
PL/SQL procedure successfully completed.
SYS@db1>select table_name,num_rows,last_analyzed from dba_tab_statistics where table_name in ('X$KSMHP','X$KGLCURSOR');
TABLE_NAME NUM_ROWS LAST_ANAL
------------------------------ ---------- ---------
X$KSMHP
SYS@db1>select count(*) from v$sql_shared_memory;
COUNT(*)
----------
226178
SYS@db1>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID g6xwfn7dmbcsg, child number 0
-------------------------------------
select count(*) from v$sql_shared_memory
Plan hash value: 3966365769
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | SORT AGGREGATE | | 1 | 37 | |
| 2 | NESTED LOOPS | | 1 | 37 | 0 (0)|
|* 3 | FIXED TABLE FULL | X$KGLCURSOR | 1 | 31 | 0 (0)|
|* 4 | FIXED TABLE FIXED INDEX| X$KSMHP (ind:1) | 1 | 6 | 0 (0)|
----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5C160134
3 - SEL$5C160134 / C@SEL$3
4 - SEL$5C160134 / H@SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5C160134")
MERGE(@"SEL$335DD26A")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$335DD26A")
MERGE(@"SEL$3")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
FULL(@"SEL$5C160134" "C"@"SEL$3")
FULL(@"SEL$5C160134" "H"@"SEL$3")
LEADING(@"SEL$5C160134" "C"@"SEL$3" "H"@"SEL$3")
USE_NL(@"SEL$5C160134" "H"@"SEL$3")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("C"."INST_ID"=USERENV('INSTANCE') AND
"KGLHDADR"<>"KGLHDPAR"))
4 - filter("KSMCHDS"="KGLOBHD6")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
3 - "C"."INST_ID"[NUMBER,22], "KGLHDADR"[RAW,8], "KGLHDPAR"[RAW,8],
"KGLOBHD6"[RAW,8]
4 - "KSMCHDS"[RAW,8]
先扫描X$KGLCURSOR就可以了
另一个解决方法:用hint
SYS@db1>select /*+ LEADING(@"SEL$5C160134" "C"@"SEL$3" "H"@"SEL$3" ) USE_NL(@"SEL$5C160134" "H"@"SEL$3") */
count(*) from v$sql_shared_memory;
COUNT(*)
----------
227773
SYS@db1>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID ahda5kmtzusvu, child number 0
-------------------------------------
select /*+ LEADING(@"SEL$5C160134" "C"@"SEL$3" "H"@"SEL$3" )
USE_NL(@"SEL$5C160134" "H"@"SEL$3") */ count(*) from v$sql_shared_memory
Plan hash value: 3966365769
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | SORT AGGREGATE | | 1 | 37 | |
| 2 | NESTED LOOPS | | 1 | 37 | 0 (0)|
|* 3 | FIXED TABLE FULL | X$KGLCURSOR | 1 | 31 | 0 (0)|
|* 4 | FIXED TABLE FIXED INDEX| X$KSMHP (ind:1) | 1 | 6 | 0 (0)|
----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5C160134
3 - SEL$5C160134 / C@SEL$3
4 - SEL$5C160134 / H@SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5C160134")
MERGE(@"SEL$335DD26A")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$335DD26A")
MERGE(@"SEL$3")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
FULL(@"SEL$5C160134" "C"@"SEL$3")
FULL(@"SEL$5C160134" "H"@"SEL$3")
LEADING(@"SEL$5C160134" "C"@"SEL$3" "H"@"SEL$3")
USE_NL(@"SEL$5C160134" "H"@"SEL$3")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("C"."INST_ID"=USERENV('INSTANCE') AND
"KGLHDADR"<>"KGLHDPAR"))
4 - filter("KSMCHDS"="KGLOBHD6")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
3 - "C"."INST_ID"[NUMBER,22], "KGLHDADR"[RAW,8], "KGLHDPAR"[RAW,8],
"KGLOBHD6"[RAW,8]
4 - "KSMCHDS"[RAW,8]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/37279/viewspace-2126742/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/37279/viewspace-2126742/