Oracle Performance Views - V$ and GV$

V$ Views

The actual dynamic performance views are identified by the prefix V_$. Public synonyms for these views have the prefix V$. Database administrators and other users should access only the V$ objects, not the V_$ objects. The dynamic performance views are used by Oracle Enterprise Manager, which is the primary interface for accessing information about system performance. After an instance is started, the V$ views that read from memory are accessible. Views that read data from disk require that the database be mounted, and some require that the database be open.

GV$ Views

For almost every V$ view described in this chapter, Oracle has a corresponding GV$ (global V$) view. In Real Application Clusters, querying a GV$ view retrieves the V$ view information from all qualified instances. In addition to the V$ information, each GV$ view contains an extra column named INST_ID of datatype NUMBER. The INST_ID column displays the instance number from which the associated V$ view information was obtained. The INST_ID column can be used as a filter to retrieve V$ information from a subset of available instances. For example, the following query retrieves the information from the V$LOCK view on instances 2 and 5:

SQL> SELECT * FROM GV$LOCK WHERE INST_ID = 2 OR INST_ID = 5;

V$ACCESS

V$ACCESS displays information about locks that are currently imposed on library cache objects. The locks are imposed to ensure that they are not aged out of the library cache while they are required for SQL execution.

V$ACTIVE_INSTANCES

V$ACTIVE_INSTANCES displays the mapping between instance names and instance numbers for all instances that have the database currently mounted.

V$ACTIVE_SERVICES

V$ACTIVE_SERVICES displays information about the active services in the database.

V$ACTIVE_SESSION_HISTORY

V$ACTIVE_SESSION_HISTORY displays sampled session activity in the database. It contains snapshots of active database sessions taken once a second. A database session is considered active if it was on the CPU or was waiting for an event that didn't belong to the Idle wait class. Refer to the V$EVENT_NAME view for more information on wait classes. This view contains one row for each active session per sample and returns the latest session sample rows first. A majority of the columns describing the session in the active session history are present in the V$SESSION view.

Oracle - Sql Developer - Installation

Windows Systems with JDK1.6.11 or Later

To install and start SQL Developer on a Windows system on which the Java J2SE JDK 1.6.11 or later (but do not use 1.7.n) is installed, follow these steps:

1. Unzip the SQL Developer kit into a folder (directory) of your choice (for example, C:\Program Files). This folder will be referred to as <sqldeveloper_install>.

Unzipping the SQL Developer kit causes a folder named sqldeveloper to be
created under the <sqldeveloper_install> folder (for example, C:\Program
Files\sqldeveloper). It also causes many files and folders to be placed in and
under that directory.

2. To start SQL Developer, go to <sqldeveloper_install>\sqldeveloper, and
double-click sqldeveloper.exe.

If you are asked to enter the full pathname for java.exe, click Browse and find java.exe. For example, the path might have a name similar to C:\Program
Files\Java\jdk1.6.0_25\bin\java.exe.

After SQL Developer starts, you can connect to any database by right-clicking the Connections node in the Connections Navigator and selecting New Connection. Alternatively, if you have any exported connections (see Section 1.4 or Section 1.10), you can import these connections and use them.

You can learn about SQL Developer by clicking Help, then Table of Contents, and reading the help topics under SQL Developer Concepts and Usage.















Linux and Mac OS X Systems, and Windows Systems without JDK 1.6.11 or Later




SQL Developer requires that the Java JDK 1.6.0_11 or later (but not 1.7.n) be installed on the system, or that you install a SQL Developer for Windows kit that includes a JDK. If you need to install a JDK, go to
http://www.oracle.com/technetwork/java/javase/downloads/.

----------------------------------------------------------------------------------------------------------------------------------
NOTE: On Macintosh systems, a native Macintosh application in the
form sqldeveloperxxx.tar.gz is provided. When it is expanded, it
appears as a Macintosh application that can be put into the
applications folder. If you choose to expand this file, it will replace
any older sqldeveloper applications in that folder.

----------------------------------------------------------------------------------------------------------------------------------

To install and start SQL Developer, follow these steps:

1. Unzip the SQL Developer kit into a directory (folder) of your choice. This
directory location will be referred to as <sqldeveloper_install>.

Unzipping the SQL Developer kit causes a directory named sqldeveloper to be
created under the <sqldeveloper_install> directory. It also causes many files and
folders to be placed in and under that directory.

2. To start SQL Developer, go to the sqldeveloper directory under the
<sqldeveloper_install> directory, and do one of the following, as appropriate
for your operating system:

On Linux and Mac OS X systems, run sh sqldeveloper.sh.

On Windows systems, double-click sqldeveloper.exe.

After SQL Developer starts, you can connect to any database by right-clicking the Connections node in the Connections Navigator and selecting New Connection.

You can learn about SQL Developer by clicking Help, then Table of Contents, and reading the help topics under SQL Developer Concepts and Usage.

DB Performance

(a) Denormalization of the schema to reduce the number of joins in ad hoc queries and reports;

(b) Creation of new indices or modification of existing indices to force indexed search of tables instead of full table scans, where appropriate;

(c) Integration of views into queries to avoid long-running SELECT statements involving views;

(d) Partitioning of very large tables to take advantage of parallel full table scans or to access only some partitions instead of accessing the entire tables;

(e) Data replication for copying the same conceptual level at multiple locations to reduce query processing time and to avoid failed updates when communication links tosome of the remote sites are down. Each of the above items involves a change in theconceptual level, which, therefore, must be updated with each change. If ERwin or any other similar CASE tool is used, such changes are automatically migrated to the relevant scripts for schema creation.

Approach for database tuning

Approach for database tuning


(a) Localize the problem and trace it to its root.
(b) Fix the root cause and test to ensure that the problem does not reappear.
(c) Look for any adverse side effects caused by the fix.

Database Maintenance

Database Maintenance

Unlike the first four phases which are one-time developmental tasks, database maintenance is a set of ongoing tasks that are performed by the DBAs regularly and routinely to ensure data integrity, security, proper backup and recovery, and overall database tuning and performance.

(a) Monitor the performance at the internal level by checking tablespace fragmentation, chaining of rows in tables, proper sizing of tables and indices, proper allocation of extents to them, sizing of the system global area (SGA) and its components, and computation of various hit ratios. In addition, periodically run the Oracle diagnostic tools UTILBSTAT and UTLESTAT and examine the generated report REPORT.TXT to track overall database performance. To allow running the utilities UTILBSTAT and UTLESTAT, you need to set the initialization parameter TIMED_STATISTICS=TRUE either at the system level via the initSID.ora file, or at the session level via the command
ALTER SESSION SET TIMED_STATISTICS=TRUE;

(b) Monitor the performance at the external level by tracking and improving query performance through EXPLAIN PLAN and other utilities, and rebuilding indices as appropriate.

(c) Monitor the performance at the conceptual level by validating the database structure after any changes to it caused by denormalization, partitioning of very large tables, data replication, etc. This process ensures that the database has not suffered any loss of information content.

(d) Set the initialization parameter AUDIT_TRAIL=TRUE in the initSID.ora file so that the AUDIT option can be turned on for selected tables to monitor user transactions against them.