This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,106 Users
  • 2,269,775 Discussions
  • 7,916,823 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.... Database Consultant, Author, Trainer World Wide over the WebMember 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.... Database Consultant, Author, Trainer World Wide over the WebMember 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.