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.

Index

http://www.codeproject.com/Articles/9990/SQL-Tuning-Tutorial-Understanding-a-Database-Execu

• An index is a collection of pairs of key and location. The key is the word by which we are looking. In the case of a book, the location is the page number. In the case of a database, it is the physical row identifier. Looking for a record in a table by physical row identifier has constant complexity, that is, it does not depend on the number of rows in the table.
• Keys are sorted, so we don't have to read all keys to find the right one. Indeed, searching in an index has logarithmic complexity. If looking for a record in an index of 1000 records takes 100 ms, it may take 200 ms in an index of million of rows and 300 ms in an index of billion of rows. (Here I'm talking about B-Tree indexes. There are other types of indexes, but they are less relevant for application development). 

If we are looking for customers by name, we can perform the following physical operations:

• Seek the first entry in IX_CUSTOMERS_LAST_NAME where LAST_NAME=@LastName. This operation is named an index seek.
• Read the index from this entry to the last where the LAST_NAME=@LastName is still true. This will cost to read PAGES(IX_CUSTOMERS_LAST_NAME)*Prob[LAST_NAME=@LastName] pages from disk. This operation (always coupled with an index seek) is called an index range scan.
• Each index entry found by the previous steps gives us the physical location of the a record in the CUSTOMERS table. We still have to fetch this record from the table. This will imply RECORDS(CUSTOMERS)*Prob[LAST_NAME=@LastName] page fetches. This operation is called a table seek.

Execution plan

http://www.codeproject.com/Articles/9990/SQL-Tuning-Tutorial-Understanding-a-Database-Execu

You may for instance ask the server to retrieve all customers living in the region of Prague. The server chooses whether it is better to read completely the table of customers, or whether using an index would be faster. It compares the cost of all possible approaches. The way that a statement can be physically executed is called an execution plan or a query plan.

The component of the database server that is responsible for computing the optimal execution plan is called the optimizer. The optimizer bases its decision on its knowledge of the database content.


What are we optimizing

Application developers usually have to minimize processor use and sometimes memory use. However, when developing database applications, the bottleneck is elsewhere. The main concern is to minimize disk access.

The main disk allocation unit of database engines is called a page. The size of a page is typically some kilobytes. A page usually contains between dozens and hundreds of records. This is important to remember: sometimes you may think a query is optimal from the point of view of the record accesses, while it is not if you look at page accesses.