1 2 Previous Next 16 Replies Latest reply: Feb 27, 2012 12:55 PM by Jonathan Lewis RSS

    V$SQL_SHARED_MEMORY don't return rows

    Pavel
      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....
          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
            Hi Aman !

            Is it dependent on statistic lavel ?

            Best regards,
            Paul
            • 3. Re: V$SQL_SHARED_MEMORY don't return rows
              Aman....
              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
                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
                  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
                    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
                      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
                        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
                          Sorry, I haven't understood your question, Sir

                          Regards,
                          Pual
                          • 10. Re: V$SQL_SHARED_MEMORY don't return rows
                            sb92075
                            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
                              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
                                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
                                  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
                                    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