에너쓰오라클

DBA_DATA_FILES, V$DATAFILE

V$DATAFILE_HEADER

V$BACKUP

v$logfile, v$log

v$archived_log, V$LOG_HISTORY

v$controlfile

v$tablespaces

V$RECOVERY_LOG

V$RECOVER_FILE

V$RECOVERY_FILE_STATUS

V$RECOVERY_PROGRESS

V$RECOVERY_STATUS

V$% views return data from memory structures. Data is lost when the instance is restarted.
DBA_% tables return data from the database's data dictionary (SYSTEM tablespace). Data persists across instance restarts.

V$ & DBA_

Static Data Dictionary Views and Dynamic Performance Views are the core of database administration. They are of crucial importance. Whoever wants to learn Oracle database administration has to get familiar with these views.

The Data Dictionary tables contain meta data on objects, privileges, roles, users etc.
Whenever you issue DML statements Oracle consults its data dictionary to make sure a table is there, it has the right columns and you have privileges on it. And whenever you issue DDL statements Oracle updates its data dictionary.
All information needed by an instance on its database is kept in the data dictionary. Except some storage related information which is in the datafile headers and some information in controlfile and spfile which is needed at instance startup.

At runtime the oracle instance stores information about itself, about its CPU, memory or I/O performance, wait statistics and everything else which is going on at the instance in dynamic performance tables. Dynamic performance tables are non persistent. You can’t see past instance startup. After shutdown or instance crash all information contained in dynamic performance tables is lost. *1)

Through static dictionary views users can get at information kept in data dictionary tables.
And through dynamic performance views users can look at non persistent runtime information contained in dynamic performance tables.

But why are those views that important, you might ask? All information necessary for database administration can be found in those static dictionary views. And basically all information necessary for instance diagnostic and tuning can be obtained through those dynamic performance views at runtime.

Of course, GUI tools like Enterprise Manager or SQL Developer offer the same information
in a more user friendly manner. But all those GUI tools rely on static dictionary views and dynamic performance views.

As a DBA you will likely get into situations where there is no GUI tools available. At times you will be alone with your SQL*Plus skills. Your mastery of data dictionary and performance views will make all the difference!

SQL> desc dbms_xplan
FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TABLE_NAME                     VARCHAR2                IN     DEFAULT
 STATEMENT_ID                   VARCHAR2                IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT

FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN
 PLAN_HASH_VALUE                NUMBER(38)              IN     DEFAULT
 DB_ID                          NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT

FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN     DEFAULT
 CURSOR_CHILD_NO                NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT

FUNCTION PREPARE_RECORDS RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 PAR1                           VARCHAR2                IN
 PAR2                           VARCHAR2                IN

<내용>
set pagesize 250
col sid for 9999
col waiting_on for a90
col serial# for 999999
col prg for a19
col OraPid for 9999
col command for a9
col aa Heading "DB-User" for a8
col bb Heading "OS-Pid" for a7

set pau off
select to_char(sysdate,'MM/DD HH24:MI:SS') dat,s.sid SID,s.serial#,sql_hash_value,
decode(s.command,
        '0','NO',
        '1','Cr Tab',
        '2','Insert',
        '3','Select',
        '6','Update',
        '7','Delete',
        '9','Create Idx',
        '10','Drop Idx',
        '15','Alter Tbl',
        '24','Create Proc',
        '32','Create Link',
        '33','Drop Link',
        '36','Create RBS',
        '37','Alter RBS',
        '38','Drop RBS',
        '40','Alter TBS',
        '41','Drop TBS',
        '42','Alter Sess',
        '45','Rollback',
        '47','PL/SQL Exe',
        '62','Anal Table',
        '63','Anal Index',
        '85','Truncate') Command,
substr(s.machine,1,8)||'['|| substr(s.program, 1, 9)||']' prg,
round(q.buffer_gets/q.executions,2) getperexec,
substr(
rtrim(w.event) || ': ' ||
rtrim(p1text,' ') || ' ' || to_char(p1) || ',' ||
rtrim(p2text,' ') || ' ' || to_char(p2) || ',' ||
rtrim(p3text,' ') || ' ' || to_char(p3),1,75) ||',waiting:'||wait_time  as waiting_on
from  v$session s, v$session_wait w,v$sqlarea q
where w.wait_time = 0
and w.sid = s.sid
and s.sql_hash_value = q.hash_value
and event not like '%pmon timer%'
and event not like '%smon timer%'
and event not like '%rdbms ipc message%'
and event not like '%SQL*Net message%'
and event not like '%lock manager wait for%'
and event not like '%slave wait%'
and event not like '%io done%'
and event not like '%pipe get%'
and event not like '%wakeup time manager%'
and event not like '%queue messages%'
order by getperexec
/