Oracle Data Dictionary Conclusion
focused on using the Oracle data dictionary to monitor performance. We looked closely at using the alert monitor against the V$ views, and creating a script that can be used to alert the DBA to pending performance problems.
Now you should be able to:
- Identify the main V$ views that monitor database performance
- Identify the main DBA views that can be interrogated for the status of the database
- Know the primary table used to monitor disk sorts, memory usage and SGA events
- Identify the critical events that warrant attention, and the queries to alert the condition
- Describe a method to notify the DBA about a pending performance problem
About Static Data Dictionary Views
Data dictionary tables are not directly accessible, but you can access information in them through data dictionary views. To list the data dictionary views available to you, query the view DICTIONARY. Many data dictionary tables have three corresponding views:
- An ALL_ view displays all the information accessible to the current user, including information from the current user's schema as well as information from objects in other schemas, if the current user has access to those objects by way of grants of privileges or roles.
- A DBA_ view displays all relevant information in the entire database. DBA_ views are intended only for administrators. They can be accessed only by users with the SELECT ANY TABLE privilege. This privilege is assigned to the DBA role when the system is initially installed.
- A USER_ view displays all the information from the schema of the current user. No special privileges are required to query these views.
The columns of the ALL_, DBA_, and USER_ views corresponding to a single data dictionary table are usually nearly identical. Therefore, these views are described in full only once in this chapter, at their first occurrence alphabetically, and are listed
without full descriptions at their other occurrences.
Terms introduced in this module
Here are some terms used in this module that you may choose to review:
- crontab: A crontab in Unix is a file that contains a list of commands that are to be executed periodically. The crontab file is managed by the cron daemon, which is a program that runs in the background and checks the crontab file for new or changed entries.
- V$ views: V$ views, or dynamic performance views, are a set of views in Oracle that provide information about the performance of the database instance. They are prefixed with the letter "V$".
- X$views: X$ views, or dynamic system views, are a set of views in Oracle that provide information about the internal structures of the database. They are prefixed with the letter "X$".
The following sections describe how to create, edit, display, and remove crontab files, as well as how to control access to them.
Inside a crontab File
The cron daemon schedules system tasks according to commands found within each crontab file. A crontab file consists of commands, one command per line, that will be executed at regular intervals. The beginning of each line contains date and time information that tells the cron daemon when to execute the command. For example, a crontab file named root is supplied during SunOS software installation. The file's contents include these command lines:
10 3 * * * /usr/sbin/logadm (1)
15 3 * * 0 /usr/lib/fs/nfs/nfsfind (2)
1 2 * * * [ -x /usr/sbin/rtc ] && /usr/sbin/rtc -c > /dev/null 2>&1 (3)
30 3 * * * [ -x /usr/lib/gss/gsscred_clean ] && /usr/lib/gss/gsscred_clean (4)
The following describes the output for each of these command lines:
- The first line runs the logadm command at 3:10 a.m. every day.
- The second line executes the nfsfind script every Sunday at 3:15 a.m.
- The third line runs a script that checks for daylight savings time (and make corrections, if necessary) at 2:10 a.m. daily.
If there is no RTC time zone, nor an /etc/rtc_config file, this entry does nothing.
DBA Performance Tools - Quiz