This discussion is archived
7 Replies Latest reply: Feb 3, 2013 1:47 PM by Justin_Mungal RSS

Dynamic Performance View question

Justin_Mungal Journeyer
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    Well played, sir.
  • 3. Re: Dynamic Performance View question
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points