7 Replies Latest reply: Feb 3, 2013 3:47 PM by Justin_Mungal RSS

    Dynamic Performance View question

    Justin_Mungal
      So, Oracle maintains dynamic performance tables. Views are created off of them, and then public synonyms are created off of those views and they begin with V$. Very good. But something doesn't make sense to me.

      V$DATAFILE is a synonym that references a view named V_$DATAFILE:
      SQL> select dbms_metadata.get_ddl('SYNONYM', 'V$DATAFILE', 'PUBLIC') from dual;

      DBMS_METADATA.GET_DDL('SYNONYM','V$DATAFILE','PUBLIC')
      --------------------------------------------------------------------------------

      CREATE OR REPLACE PUBLIC SYNONYM "V$DATAFILE" FOR "SYS"."V_$DATAFILE"

      SQL> select object_type from dba_objects where object_name='V_$DATAFILE';

      OBJECT_TYPE
      -------------------
      VIEW

      But the definition of the view references the.... synonym itself?
      SQL> select dbms_metadata.get_ddl('VIEW', 'V_$DATAFILE', 'SYS') from dual;

      DBMS_METADATA.GET_DDL('VIEW','V_$DATAFILE','SYS')
      --------------------------------------------------------------------------------

      CREATE OR REPLACE FORCE VIEW "SYS"."V_$DATAFILE" ("FILE#", "CREATION_CHANGE#",
      "CREATION_TIME", "TS#", "RFILE#", "STATUS", "ENABLED", "CHECKPOINT_CHANGE#", "C
      HECKPOINT_TIME", "UNRECOVERABLE_CHANGE#", "UNRECOVERABLE_TIME", "LAST_CHANGE#",
      "LAST_TIME", "OFFLINE_CHANGE#", "ONLINE_CHANGE#", "ONLINE_TIME", "BYTES", "BLOCK
      S", "CREATE_BYTES", "BLOCK_SIZE", "NAME", "PLUGGED_IN", "BLOCK1_OFFSET", "AUX_NA
      ME", "FIRST_NONLOGGED_SCN", "FIRST_NONLOGGED_TIME", "FOREIGN_DBID", "FOREIGN_CRE
      ATION_CHANGE#", "FOREIGN_CREATION_TIME", "PLUGGED_READONLY", "PLUGIN_CHANGE#", "
      PLUGIN_RESETLOGS_CHANGE#", "PLUGIN_RESETLOGS_TIME") AS
      select "FILE#","CREATION_CHANGE#","CREATION_TIME","TS#","RFILE#","STATUS","ENA
      BLED","CHECKPOINT_CHANGE#","CHECKPOINT_TIME","UNRECOVERABLE_CHANGE#","UNRECOVERA
      BLE_TIME","LAST_CHANGE#","LAST_TIME","OFFLINE_CHANGE#","ONLINE_CHANGE#","ONLINE_
      TIME","BYTES","BLOCKS","CREATE_BYTES","BLOCK_SIZE","NAME","PLUGGED_IN","BLOCK1_O
      FFSET","AUX_NAME","FIRST_NONLOGGED_SCN","FIRST_NONLOGGED_TIME","FOREIGN_DBID","F
      OREIGN_CREATION_CHANGE#","FOREIGN_CREATION_TIME","PLUGGED_READONLY","PLUGIN_CHAN
      GE#","PLUGIN_RESETLOGS_CHANGE#","PLUGIN_RESETLOGS_TIME" from v$datafile

      So the view definition references a synonym, and the synonym references the same view... seems circular. Is this Oracle Corp's way of telling me to bugger off? I just wanted to look up the name of the dynamic performance table out of curiosity.
        • 1. Re: Dynamic Performance View question
          JohnWatson
          You need to look at v$fixed_view_definition. In the case of V$DATAFILE, you will see that it comes from GV$DATAFILE, which in turn comes from x$kcvdf.
          --
          John Watson
          http://www.skillbuilders.com

          Edited by: JohnWatson on Feb 3, 2013 11:02 AM
          • 2. Re: Dynamic Performance View question
            Justin_Mungal
            Well played, sir.
            • 3. Re: Dynamic Performance View question
              Jonathan Lewis
              DBjanitor_old wrote:
              So, Oracle maintains dynamic performance tables. Views are created off of them, and then public synonyms are created off of those views and they begin with V$. Very good. But something doesn't make sense to me.
              The synonyms is owned by PUBLIC, not by SYS - so when SYS references v$XXX they access the underlying object, when anyone else references v$XXX they access the synomym, which points to the view owned by SYS, and the view accesses the underlying object owned by SYS.

              The point of this is that SYS is the only schema allowed to use the code that interprets the dynamic performance views properly, so any end user that wants to access them has to go through an object owned by sys, which then accesses them with the privileges of sys (i.e. the equivalent of authid owner). The apparent loop in the chain simply means that SYS, SYSTEM, DBSNMP etc. can all use the same queries to access the dynamic performance views, but only one of them is accessing the special code directly.

              See what happens if you try to grant access to the raw objects to non-SYS schemas (logged on as SYS)
              SQL> grant select on v$session to test_user;
              grant select on v$session to test_user
                              *
              ERROR at line 1:
              ORA-02030: can only select from fixed tables/views
              Reagrds
              Jonathan Lewis
              • 4. Re: Dynamic Performance View question
                Justin_Mungal
                Thanks Jonathan. That makes sense; we wouldn't want just anyone directly accessing x$ structures. By the way, are the x$ structures simply arrays built from Oracle memory structures? We can't accurately call them views or tables could we?
                • 5. Re: Dynamic Performance View question
                  Jonathan Lewis
                  DBjanitor_old wrote:
                  Thanks Jonathan. That makes sense; we wouldn't want just anyone directly accessing x$ structures.
                  By the way, are the x$ structures simply arrays built from Oracle memory structures? We can't accurately call them views or tables could we?
                  Some are fixed arrays, some are segmented arrays, some are linked lists, some turn into simple calls to the operating system.
                  The X$ are definitely not tables, and the V$ are not really like views - but Oracle Corp. has gone to some trouble to make them look like tables and views.

                  Regards
                  Jonathan Lewis
                  • 6. Re: Dynamic Performance View question
                    JohnWatson
                    Jonathan Lewis wrote:
                    Some are fixed arrays, some are segmented arrays, some are linked lists, some turn into simple calls to the operating system.
                    Oh my gosh. If I remember correctly, a segmented array is a linked list of fixed arrays. Or something. Isn't C a wonderful language? The way I think of the X$ tables is that they are an externalization of the C data structures that make up the Oracle instance. If I'm right, when one queries an X$ table, or a view on one, you are in fact using pointers to read values from memory locations. Hence the impossibility of read consistency: any such action would bypass those mechanisms.
                    I suspect that ASH data is gathered in this manner, using pointers. Which would be why it can be gathered per second with no appreciable cost. It doesn't take many instructions to read a value from one location and write it to another.
                    --
                    John Watson
                    http://www.skillbuilders.com
                    • 7. Re: Dynamic Performance View question
                      Justin_Mungal
                      I'm sure you're right. Here's something from Oracle themselves regarding that:

                      "The Oracle database server maintains a dynamic set of data about the operation and
                      performance of the instance. These dynamic performance views are based on virtual tables that
                      are built from memory structures inside the database server. That is, they are not conventional
                      tables that reside in a database. V$views externalize metadata contained in memory structures
                      of an Oracle instance. Some V$views can show data before a database is mounted or open.
                      The V$FIXED_TABLE view lists all the dynamic views.

                      Dynamic performance views include the raw information used by AWR and Statspack and
                      detail information about but not limited to:
                      • Sessions
                      • Wait events
                      • Locks
                      • Backup status
                      • Memory usage and allocation
                      • System and session parameters
                      • SQL execution
                      • Statistics and metrics"

                      The externalized metadata would be the X$ structures. It makes sense for AWR and ASH to use the V$ views and possibly the X$ structures, although I imagine it would be less work to simply use the V$ views when possible.