如何在Oracle中查找伪游标PSEUDO_CURSOR

Maclean Liu(刘相兵

如何在Oracle中查找伪游标PSEUDO_CURSOR


从10.2.0.1 开始Oracle 会利用伪游标PSEUDO CURSOR
实现对SCHEMA OBJECT的直接访问，以避免 显示地写SELECT语句而造成的性能损耗。

Oracle官方介绍PSEUDO_CURSOR是RDBMS内部的机制 一般不会对USer用户造成问题。

伪游标PSEUDO CURSOR主要是用来访问数据字典表、LOBS、NCHAR、NVARCHAR2这类对象。


我们来具体看一个  伪游标 PSEUDO CURSOR。


创建一个 h$pseudo_cursor视图 以方便我们了解这些神奇的伪游标


SQL> create or replace view h$pseudo_cursor as
  2  select Pseudo_cursor, sql_id,obj_id hex_obj_id
  3       ,obj# object_id, u.name owner, o.name object_name
  4  from (select distinct
  5               KGLNAOBJ Pseudo_cursor,kglobt03 sql_id
  6          ,substr(KGLNAOBJ
  7                 ,instr(KGLNAOBJ,'_',1,3)+1
  8                 ,instr(KGLNAOBJ,'_',1,4)-instr(KGLNAOBJ,'_',1,3)-1) obj_id
  9         ,(case when
10           replace(translate(substr(upper(KGLNAOBJ)
11                                   ,instr(KGLNAOBJ,'_',1,3)+1
12                                   ,instr(KGLNAOBJ,'_',1,4)
13                                    -instr(KGLNAOBJ,'_',1,3)-1)
14                            ,'0123456789ABCDEF','................')
15                  ,'.') is null then 'Y' else 'N' end) is_safe_to_compare
            from x$kglob) k
   , obj$ o, user$ u
where obj#=decode(is_safe_to_compare,'Y',to_number(obj_id,'xxxxxxxxxx'),0)
   and o.owner#=u.user#; 16   17   18   19  



SQL> select * from h$pseudo_cursor ;

PSEUDO_CURSOR                  SQL_ID        HEX_OBJ_ID  OBJECT_ID OWNER                OBJECT_NAME
------------------------------ ------------- ---------- ---------- -------------------- ------------------------------
table_4_9_115f_0_0_0           386whntar002n 115f             4447 SYS                  RULE$
table_4_9_e6a_0_0_0            4mnh4ct2q02jd e6a              3690 SYS                  WRI$_ADV_OBJECTS
table_4_9_10f_0_0_0            8r7dm0qvp2259 10f               271 SYS                  INDPART$
SYS_PLSQL_4246_817_1                         817              2071 PUBLIC               GV$SGA_DYNAMIC_COMPONENTS
table_4_d_22e4_0_0_0           9hvcbtha8q7g8 22e4             8932 SYS                  WRH$_SQLTEXT
table_4_d_10f_0_0_0            bn3f98wzb3ffj 10f               271 SYS                  INDPART$
table_4_9_22e4_0_0_0           9c2044mpmzqhn 22e4             8932 SYS                  WRH$_SQLTEXT
SYS_PLSQL_4246_790_1                         790              1936 SYS                  GV_$SESSION_OBJECT_CACHE
table_4_d_10a_0_0_0            g3sv0f9vfq0p7 10a               266 SYS                  TABPART$
table_1_ff_14f_0_0_0           4gd6b1r53yt88 14f               335 SYS                  KOTTD$
table_1_ff_154_0_0_0           ady0t9s6h5x5n 154               340 SYS                  KOTTB$
table_1_ff_159_0_0_0           2hbdag3dfzj9u 159               345 SYS                  KOTAD$
SYS_PLSQL_4246_817_2                         817              2071 PUBLIC               GV$SGA_DYNAMIC_COMPONENTS
table_1_ff_163_0_0_0           531sc6y5xdd41 163               355 SYS                  KOTTBX$
table_e_a_e6a_7_0_0            0f3hrc506rrcg e6a              3690 SYS                  WRI$_ADV_OBJECTS
table_1_ff_15e_0_0_0           6vshqsdtq1w9z 15e               350 SYS                  KOTMD$
table_4_9_10a_0_0_0            fjk3cdx1tuqxb 10a               266 SYS                  TABPART$

因为是伪游标 所以SQL文本是系统生成的 如 table_4_9_115f_0_0_0 ，它的SQL_ID是386whntar002n ，除非这些 伪游标有真实的 SQL文本，否则这些伪游标一般无法从V$SQL中获得：

SQL> SELECT SQL_ID,SQL_TEXT FROM  v$SQL WHERE SQL_ID IN (SELECt SQL_ID FROM h$pseudo_cursor);

SQL_ID
-------------
SQL_TEXT
----------------------------------------------------------------------------------------------------
d471t1nr3yfzv
select * from v$sql where sql_id='table_e_a_e6a_7_0_0'




我们可以利用 h$pseudo_cursor视图的 OBJECT_NAME 了解这些伪游标产生的原因， 例如:

PSEUDO_CURSOR                  SQL_ID        HEX_OBJ_ID  OBJECT_ID OWNER                OBJECT_NAME
------------------------------ ------------- ---------- ---------- -------------------- ------------------------------
table_4_9_115f_0_0_0           386whntar002n 115f             4447 SYS                  RULE$

table_4_9_115f_0_0_0    这个伪游标是因为 OBJECT_ID=4447 OBJECT_NAME=RULE$ 这张字典基表产生的，了解一下这张表：



SQL> desc RULE$;
Name                                                                          Null?    Type
----------------------------------------------------------------------------- -------- ----------------------------------------------------
OBJ#                                                                          NOT NULL NUMBER
CONDITION                                                                              CLOB
ECTX#                                                                                  NUMBER
R_ACTION                                                                               RE$NV_LIST
PROPERTY                                                                      NOT NULL NUMBER
R_COMMENT                                                                              VARCHAR2(4000)
UACTX_CLIENT                                                                           VARCHAR2(30)


可以看到这张表上有一个 CONDITION        是CLOB类型，一般就是table_4_9_115f_0_0_0 这个伪游标生成的原因。

Maclean Liu(刘相兵

ODM Finding:

1. H$PSEUDO_CURSOR
   
2. 
   
3. Applies to:
   
4. Oracle Server - Enterprise Edition - Version: 10.2.0.1 and later   [Release: 10.2 and later ]
   
5. Information in this document applies to any platform.
   
6. Purpose
   
7. Pseudo cursors are structures to provide direct access to schema objects; avoiding the overhead of writing an explicit SELECT statement.
   
8. 
   
9. These pseudo cursors show in Enterprise Manager and AWR reports with the legend
   
10. "SQL Text Not Available"
    
11. 
    
12. They are used by internal mechanisms in the RDBMS and cannot be harnessed by the user.
    
13. Their most common use is to access data dictionary tables, LOBs , NCHAR, and NVARCHAR2 directly.
    
14. 
    
15. WARNING: Querying H$PSEUDO_CURSOR or the X$ views it references can cause severe library cache latch and mutex contention, so it is imperative that it is only used sparingly while diagnosing an issue where pseudo cursors are suspected.
    
16. 
    
17. 
    
18. Software Requirements/Prerequisites
    
19. Run from SQL*Plus.
    
20. Configuring the Script
    
21. The script needs to be run as the SYS user, to create the view.
    
22. Running the Script
    
23. column HEX_OBJ_ID  format a10
    
24. column PSEUDO_CURSOR format a30
    
25. column OWNER format a20
    
26. select * from h$pseudo_cursor where sql_id='b54441z8q9kk9';
    
27. Caution
    
28. This script is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.
    
29. 
    
30. Proofread this script before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected.
    
31. Script
    
32. 
    
33. 
    
34. create or replace view h$pseudo_cursor as
    
35. select Pseudo_cursor, sql_id,obj_id hex_obj_id
    
36.      ,obj# object_id, u.name owner, o.name object_name
    
37. from (select distinct
    
38.              KGLNAOBJ Pseudo_cursor,kglobt03 sql_id
    
39.         ,substr(KGLNAOBJ
    
40.                ,instr(KGLNAOBJ,'_',1,3)+1
    
41.                ,instr(KGLNAOBJ,'_',1,4)-instr(KGLNAOBJ,'_',1,3)-1) obj_id
    
42.        ,(case when
    
43.          replace(translate(substr(upper(KGLNAOBJ)
    
44.                                  ,instr(KGLNAOBJ,'_',1,3)+1
    
45.                                  ,instr(KGLNAOBJ,'_',1,4)
    
46.                                   -instr(KGLNAOBJ,'_',1,3)-1)
    
47.                           ,'0123456789ABCDEF','................')
    
48.                 ,'.') is null then 'Y' else 'N' end) is_safe_to_compare
    
49.             from x$kglob) k
    
50.    , obj$ o, user$ u
    
51. where obj#=decode(is_safe_to_compare,'Y',to_number(obj_id,'xxxxxxxxxx'),0)
    
52.    and o.owner#=u.user#;
    
53. 
    
54. 
    
55. WARNING: Querying H$PSEUDO_CURSOR or the X$ views it references can cause severe library cache latch and mutex contention, so it is imperative that it is only used sparingly while diagnosing an issue where pseudo cursors are suspected.
    
56. 
    
57. 
    
58. Script Output
    
59. SQL> column HEX_OBJ_ID  format a10
    
60. SQL> column PSEUDO_CURSOR format a30
    
61. SQL> column OWNER format a20
    
62. SQL> select * from h$pseudo_cursor where sql_id='b54441z8q9kk9';
    
63. 
    
64. PSEUDO_CURSOR                  SQL_ID        HEX_OBJ_ID  OBJECT_ID OWNER                OBJECT_NAME
    
65. ------------------------------ ------------- ---------- ---------- -------------------- ------------------------------
    
66. table_1_ff_22d_0_0_0           b54441z8q9kk9 22d               557 SYS                  KOTTBX$

复制代码

tohjb

厉害！！

[ 本帖最后由 tohjb 于 2012-3-15 21:29 编辑 ]