This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Feb 27, 2012 10:55 AM by Jonathan Lewis RSS

V$SQL_SHARED_MEMORY don't return rows

Pavel Explorer
Currently Being Moderated
Hello!

* 10.2.0.5*

query to V$SQL_SHARED_MEMORY don't return rows
please explain why ?

Regards,
Paul

Edited by: Paul on 22.02.2012 0:22

Edited by: Paul on 22.02.2012 0:23
  • 1. Re: V$SQL_SHARED_MEMORY don't return rows
    Aman.... Oracle ACE
    Currently Being Moderated
    Paul wrote:
    Hello!

    * 10.2.0.5*

    query to V$SQL_SHARED_MEMORY don't return rows
    please explain why ?
    On a two node RAC , it does for me,
    SQL> select * from V$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
    PL/SQL Release 11.1.0.6.0 - Production
    CORE    11.1.0.6.0      Production
    TNS for Linux: Version 11.1.0.6.0 - Production
    NLSRTL Version 11.1.0.6.0 - Production
    
    SQL> select count(*) from V$sql_shared_memory;
    
      COUNT(*)
    ----------
         41979
    
    SQL>
    Aman....
  • 2. Re: V$SQL_SHARED_MEMORY don't return rows
    Pavel Explorer
    Currently Being Moderated
    Hi Aman !

    Is it dependent on statistic lavel ?

    Best regards,
    Paul
  • 3. Re: V$SQL_SHARED_MEMORY don't return rows
    Aman.... Oracle ACE
    Currently Being Moderated
    In my db, the value is set to default (TYPICAL) . Are you having anything else set? I don't think that its based on the parameter but I am not sure.

    Aman....
  • 4. Re: V$SQL_SHARED_MEMORY don't return rows
    Pavel Explorer
    Currently Being Moderated
    Thank You Sir!


    Whether there is a sense to check MMON, MMNL or MMAN ?
    Or not these processes fill the fixed tables for V$SQL_SHARED_MEMORY ?

    Best regards,
    Paul
  • 5. Re: V$SQL_SHARED_MEMORY don't return rows
    Pavel Explorer
    Currently Being Moderated
    Hello !!

    I see the next picture :

    select owner,object_name,object_type,status
    from dba_objects
    where object_name like upper('G%SQL_SHARED_MEMORY%');
    1 SYS GV_$SQL_SHARED_MEMORY VIEW VALID
    2 PUBLIC GV$SQL_SHARED_MEMORY SYNONYM VALID

    here I'm using definition from query You provid:
    select /*+use_nl(h,c)*/ count(*)
    from x$kglcursor c, x$ksmhp h where ksmchds = kglobhd6 and kglhdadr != kglhdpar;
    COUNT(*)
    0

    SELECT * FROM
    (select /*use_nl(h,c)*/ 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)
    WHERE ROWNUM < 3;

    no rows ... WHY?

    Is it Bug 3252000 ?
  • 6. Re: V$SQL_SHARED_MEMORY don't return rows
    sb92075 Guru
    Currently Being Moderated
    bcm@bcm-laptop:~$ sqlplus
    
    SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 23 20:36:31 2012
    
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
    Enter user-name: / as sysdba
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> 
    SQL> 
    SQL> select count(*) from V$sql_shared_memory;
    
      COUNT(*)
    ----------
          0
  • 7. Re: V$SQL_SHARED_MEMORY don't return rows
    Pavel Explorer
    Currently Being Moderated
    Hello !

    Thank You Sir,
    And at the same time I using 10.2.0.5

    Regards,
    Paul
  • 8. Re: V$SQL_SHARED_MEMORY don't return rows
    sb92075 Guru
    Currently Being Moderated
    Paul wrote:
    Hello !

    Thank You Sir,
    And at the same time I using 10.2.0.5

    Regards,
    Paul
    so what have you learned?
  • 9. Re: V$SQL_SHARED_MEMORY don't return rows
    Pavel Explorer
    Currently Being Moderated
    Sorry, I haven't understood your question, Sir

    Regards,
    Pual
  • 10. Re: V$SQL_SHARED_MEMORY don't return rows
    sb92075 Guru
    Currently Being Moderated
    Paul wrote:
    Sorry, I haven't understood your question, Sir
    my DB returned no rows & I don't see any problem.
    What SQL & error code needs to be resolved for you?
  • 11. Re: V$SQL_SHARED_MEMORY don't return rows
    Pavel Explorer
    Currently Being Moderated
    1) recently Recently I have seen in alert,log
    ORA-00600: internal error code, arguments: [kglhdda-bad-free], [], [], [], [], [], [], []

    2) in corresponding trace-file it is visible Unequal Memory Utilization of Subpols

    3) There are many identical statements , but gv$sql_shared_memory "Is silent" (It is interesting to me, why ?)

    all it will lead to ora-0431 needs to be resolved it

    regards,
    Paul
  • 12. Re: V$SQL_SHARED_MEMORY don't return rows
    sb92075 Guru
    Currently Being Moderated
    Paul wrote:
    1) recently Recently I have seen in alert,log
    ORA-00600: internal error code, arguments: [kglhdda-bad-free], [], [], [], [], [], [], []
    ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink and/or call Oracle support
  • 13. Re: V$SQL_SHARED_MEMORY don't return rows
    Pavel Explorer
    Currently Being Moderated
    Hello


    Yes , and at the same timeThe same picture and at absence of ora-00600

    regards,
    Paul
  • 14. Re: V$SQL_SHARED_MEMORY don't return rows
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Paul wrote:

    select owner,object_name,object_type,status
    from dba_objects
    where object_name like upper('G%SQL_SHARED_MEMORY%');
    1 SYS GV_$SQL_SHARED_MEMORY VIEW VALID
    2 PUBLIC GV$SQL_SHARED_MEMORY SYNONYM VALID

    here I'm using definition from query You provid:
    select /*+use_nl(h,c)*/ count(*)
    from x$kglcursor c, x$ksmhp h where ksmchds = kglobhd6 and kglhdadr != kglhdpar;
    COUNT(*)
    0

    SELECT * FROM
    (select /*use_nl(h,c)*/ 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)
    WHERE ROWNUM < 3;

    no rows ... WHY?
    The developer who wrote that view definitin didn't know how to use hints.
    The code HAS to access x$kglcursor before it joins to x$ksmhp otherwise the routine to generate the "rows" for x$ksmhp doesn't have a memory address to access.
    At present, the following hinting is probably going to give you what you want if you simply query v$sql_shared_memory. Be VERY careful that you get the query block name reference right.
    select
         /*+  
              leading(@sel$5c160134 c@sel$3 h@sel$3 )     
              use_nl(@sel$5c160134 h@sel$3)
         */
         *
    from 
         v$sql_shared_memory
    ;
    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    Author: <b><em>Oracle Core</em></b>
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points