Forum Stats

  • 3,836,931 Users
  • 2,262,204 Discussions
  • 7,900,144 Comments

Discussions

V$SQL_SHARED_MEMORY don't return rows

Pavel
Pavel Member Posts: 474
edited Feb 27, 2012 1:55PM in General Database Discussions
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

Answers

  • Aman....
    Aman.... Member Posts: 22,919 Gold Crown
    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....
  • Pavel
    Pavel Member Posts: 474
    Hi Aman !

    Is it dependent on statistic lavel ?

    Best regards,
    Paul
  • Aman....
    Aman.... Member Posts: 22,919 Gold Crown
    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....
  • Pavel
    Pavel Member Posts: 474
    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
  • Pavel
    Pavel Member Posts: 474
    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 ?
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    [email protected]:~$ 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
  • Pavel
    Pavel Member Posts: 474
    Hello !

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

    Regards,
    Paul
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    Paul wrote:
    Hello !

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

    Regards,
    Paul
    so what have you learned?
  • Pavel
    Pavel Member Posts: 474
    Sorry, I haven't understood your question, Sir

    Regards,
    Pual
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    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?
This discussion has been closed.