1 Reply Latest reply on Nov 5, 2015 1:24 PM by thatJeffSmith-Oracle

    list of some bugs related to RAC Awareness in Oracle SQL Developer

    Dimitri Avrutin

      as a followup to this post:

      Bugs with Oracle RAC Awareness

       

      I made more Traces while using SQL Developer , I seen duplicate and some misleading data results.

       

      my environment contains 2 RAC nodes with 11.2.0.3 versions,

      the Oracle SQL Developer is Version 4.1.2.20 Build MAIN-20.64

       

      at DBA Console\ <Database_Connection>\ Database Status\ Instance Viewer - this is excellent  Viewer! and you made amazing work to give us such feature, but sadly if someone is using this in Oracle RAC Environment he would get dangerous mixture of results, some of the results are RAC query Aware  results while other gives only the local connected instance results.

       

      1. first most noticeable thing, there are no indication to which RAC Node we are connected to. my recommendation is doing such viewer per Instance as the current viewer gives mostly the results from the connected instance.

       

      2. double press on the "Redo Log" Field would open a result window with duplicate results. this is because you are doing a query from GV$log instead of V$log.

      V$log is a query that already gives info for all the available instances, i guess this is because this query suppose to give you info about the Redo Log file Groups which are seen by all Nodes.

      another thing,it should show the "Thread" column, as this is the actual way to identify between Instances.

      my recommendation is to do something similar to this query instead of the existing one:

      Instead:

      SELECT

                              'SQLDEV:LINK:'||USER||':REDOLOGGROUP:#'||group#||':oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink' as "Group",

                              NLS_INITCAP(status) as "Status",

                              members as "# of Members",

                              NLS_INITCAP(archived) as "Archived",

                              TO_CHAR((bytes / 1024),'99999990') as "Size (KB)",

                              sequence# as "Sequence",

                              first_change# as "First Changed #"

                          FROM

                              gv$log

                          ORDER BY 1

       

      SELECT

                              'SQLDEV:LINK:'||USER||':REDOLOGGROUP:#'||group#||':oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink' as "Group",

                              thread as "Thread",

                              NLS_INITCAP(status) as "Status",

                              members as "# of Members",

                              NLS_INITCAP(archived) as "Archived",

                              TO_CHAR((bytes / 1024),'99999990') as "Size (KB)",

                              sequence# as "Sequence",

                              first_change# as "First Changed #"

                          FROM

                              v$log

                          ORDER BY 1

       

      3. pressing on a desired Group number in the "Redo Logs" result window brings the specific Redo log Files results. which are also duplicated because the usage of gv$log  and gv$logfile. all you need to do to fix it is changing it to v$log and v$logfile.

       

      SELECT

                              member as "File",

                              type as "Type",

                              is_recovery_dest_file as "Created in Flash Recovery Area"

                          from

                             gv$logfile 

                           v$logfile

                          where

                              ('#' || group#) = :NAME

                          order by 1

       

       

      SELECT

                                GROUPNO,

                                'REDOLOGGROUP' type

                              from

                                (

                                  SELECT

                                    '#' || group# as GROUPNO,

                                    'REDOLOGGROUP' type

                                  from

                                  gv$log

                                    v$log

                                )

                             WHERE GROUPNO IN :NAME

       

      4. Creating a New Redo Log group using the action button doesn't allows you to choose for which thread, which is critical in RAC Environment. same thing when you get the DDL by going to SQL tab that gives you the command for creating this particular Redo Log Group, it gives you the required SQL to make it but doesn't indicate the Thread parameter.

       

      5. Memory Filed\ SGA Statistics - in this result windows you use the following query:

      select pool as "SGA Pool", name as "Statistic", bytes as "Bytes" from V$SGASTAT

       

      so you use the current connected instance without indication which instance we are connected to,

      but also here is actually a place where you can benefit from GV$SGASTAT by also adding the INST_ID column:

      select INST_ID,pool as "SGA Pool", name as "Statistic", bytes as "Bytes" from GV$SGASTAT;

       

      6. pressing on the DB CPU RATIO field gives results like the above which could easily show results from all instances, instead of querying from just V$SESS_TIME_MODEL you could query from GV$SESS_TIME_MODEL and adding the INST_ID column in the results. of course you gonna have to change the query because doing only :

      select distinct sid from V$SESS_TIME_MODEL

      would give wrong results for example.

       

      7. "Version Banner" field gives also duplicate results:

      select banner "Your_Database_Settings"

                                    from desc gv$version

      you should just make it v$version, as its only gives the same results for all Instances.

       

      8. not a big deal but there are some strange queries such as this:

           a. select   GROUP# , THREAD# , SEQUENCE# , BYTES , BLOCKSIZE, MEMBERS , ARCHIVED , STATUS , FIRST_CHANGE# , FIRST_TIME, NEXT_CHANGE#, NEXT_TIME from GV$LOG where inst_id = USERENV('Instance')

           b. select  POOL, NAME , BYTES from GV$SGASTAT where inst_id = USERENV('Instance')

           c. select SID, STAT_ID, STAT_NAME, VALUE from GV$SESS_TIME_MODEL  where inst_id = USERENV('Instance')

       

      its seems like some strange workaround to get the current Instance, but you don't actually query the current INST_ID columns from the queries, so i don't see the point in querying the GV$ and than limiting it by current connected instance:

      where inst_id = USERENV('Instance')

       

      why not just query the regular V$ ?

       

       

      9. looking at Tools\Monitor SQL\ - its really nice thing that you integrated the V$SQL_MONITOR. the problems are similar to the one i mentioned above.

      most importantly no INST_ID indication.

       

       

      this is what i noticed, there might be more areas where there is a problematic RAC awareness. 

      anyway thank you,

      this is great tool and i hope you fix this issues regarding RAC.