1 AND THE RESULTS OF THE STARTUP AND SHUTDOWNS.1...

Exercise 3-1 and the results of the startup and shutdowns.

Use Data Dictionary and

PA R T I

Dynamic Performance Views

An Oracle database is defined by its data dictionary. The data dictionary is not very

comprehensible. For this reason, Oracle provides a set of views onto the data dictionary

that are much easier to understand. These views provide the DBA with a tool for

understanding what is happening in the database. The instance also has a set of tables

(which are in fact C data structures) that are not easily understandable. These are

externalized as the dynamic performance views that are key to understanding what

is happening within the instance.

The Data Dictionary Views

The data dictionary contains metadata: that is, data about data. It describes the

database, both physically and logically, and its contents. User definitions, security

information, integrity constraints, and (from release 10g onward) performance

monitoring information are all part of the data dictionary. It is stored as a set of

segments in the SYSTEM and SYSAUX tablespaces.

In many ways, the segments that make up the data dictionary are like other regular

table and index segments. The critical difference is that the data dictionary tables are

generated at database creation time, and you are not allowed to access them directly.

There is nothing to stop an inquisitive DBA from investigating the data dictionary

directly, but if you do any updates to it you may cause irreparable damage to your

database, and certainly Oracle Corporation will not support you. Creating a data

dictionary is part of the database creation process. It is maintained subsequently by

Data Definition Language (DDL) commands. When you issue the CREATE TABLE

command, you are not only creating a data segment to store your data in its rows,

your DDL command has the side effect of inserting rows into many data dictionary

tables that keep track of segment-related information including tablespace, extent,

column and ownership related properties.

To query the dictionary, Oracle provides a set of views which come in three forms,

prefixed with: DBA_, ALL_, or USER_. Most of the views come in all three forms. Any

view prefixed USER_ will be populated with rows describing objects owned by the

user querying the view. So no two users will see the same contents. When user JOHN

queries USER_TABLES, he will see information about only his tables; if you query

USER_TABLES, you will see information about only your tables. Any view prefixed

ALL_ will be populated with rows describing objects to which you have access. So

ALL_TABLES will contain rows describing your own tables, plus rows describing

tables belonging to anyone else that you have been given permission to see. Any view

prefixed DBA_ will have rows for every object in the database, so DBA_TABLES will

have one row for every table in the database, no matter who created it. Figure 3-10

describes the underlying concept represented by the three forms of dictionary views.

The USER_ views sit in the middle of the concentric squares and only describe an

individual user’s objects. The ALL_ views in the middle display all the contents of the

USER_ views, and in addition describe objects that belong to other schemas but to

which your user has been granted access. The DBA_ views describe all objects in the

database. Needless to say, a user must have DBA privileges to access the DBA_ views.

Figure 3-10 The overlapping structure of the three forms of the dictionary views

These views are created as part of the database creation process, along with a large

number of PL/SQL packages that are provided by Oracle to assist database administrators

in managing the database and programmers in developing applications.

TIP Which view will show you ALL the tables in the database? DBA_TABLES,

not ALL_TABLES.

There are hundreds of data dictionary views. Some of those commonly used by

DBAs are

• DBA_OBJECTS A row for every object in the database

• DBA_DATA_FILES A row describing every datafile

• DBA_USERS A row describing each user

• DBA_TABLES A row describing each table

• DBA_ALERT_HISTORY Rows describing past alert conditions

There are many more than these, some of which will be used in later chapters.

Along with the views, there are public synonyms onto the views. A query such as this,

select object_name,owner, object_type from dba_objectswhere object_name='DBA_OBJECTS';

shows that there is, in fact, a view called DBA_OBJECTS owned by SYS, and a public

synonym with the same name.

The Dynamic Performance Views

There are more than three hundred dynamic performance views. You will often hear

them referred to as the “Vee dollar” views, because their names are prefixed with “V$”.

In fact, the “Vee dollar” views are not views at all—they are synonyms to views that

are prefixed with “V_$”, as shown in Figure 3-11.

Figure 3-11 A V_$ view and its V$ synonym

The figure shows V$SQL, which has one row for every SQL statement currently

stored in the shared pool, with information such as how often the statement has been

executed.

The dynamic performance views give access to a phenomenal amount of

information about the instance, and (to a certain extent) about the database. The

majority of the views are populated with information from the instance; while the

remaining views are populated from the controlfile. All of them provide real-time

information. Dynamic performance views that are populated from the instance, such

as V$INSTANCE or V$SYSSTAT, are available at all times, even when the instance is in

NOMOUNT mode. Dynamic performance views that are populated from the

controlfile, such as V$DATABASE or V$DATAFILE, cannot be queried unless the

database has been mounted, which is when the controlfile is read. By contrast, the

data dictionary views (prefixed DBA, ALL, or USER) can only be queried after the

database—including the data dictionary—has been opened.

EXAM TIP Dynamic performance views are populated from the instance or

the controlfile; DBA_, ALL_, and USER_ views are populated from the data

dictionary. This difference determines which views can be queried at the

various startup stages.

The dynamic performance views are created at startup, updated continuously

during the lifetime of the instance, and dropped at shutdown. This means they will

accumulate values since startup time; if your database has been open for six months

nonstop, they will have data built up over that period. After a shutdown/startup, they

will be initialized. While the totals may be interesting, they do not directly tell you

anything about what happened during certain defined periods, when there may have

been performance issues. For this reason, it is generally true that the dynamic

performance views give you statistics, not metrics. The conversion of these statistics

into metrics is a skillful and sometimes time-consuming task, made much easier by

the self-tuning and monitoring capabilities of the database.