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.

What is the difference between nomount,mount and open database in Oracle?

http://www.saptechies.com/difference-between-oracle-mount-no-mount-and-open-stage/

The differences in the 3 startup stages for oracle db are as below.
NoMount Stage:
When DB is in the nomount stage, oracle first opens and reads the initialization parameter file "init.ora" for the DB configuration which includes various parameters required for initialization. Based on the parameters, the memory areas in database instance are allocated and db background processes are started. The instance then starts.
Mount Stage:
When DB is in this stage, it opens and reads the control file which contains the physical structure of db  like the database datafiles etc.. It determines the location of the datafiles and the database is ready to be opened.
Open Stage
When DB is in this stage, it opens the database, it tries to access all of the datafiles associated with the database. Once it has accessed the database datafiles, it makes sure that all of the database datafiles are consistent.

After that DB is into normal operations state.


http://technado.wordpress.com/2012/10/10/what-is-the-difference-between-nomountmount-and-open-database-in-oracle/

Nomount – The database instance has been started (processes and memory structures have been allocated, but control file is not yet accessed).
Mount – Instance has accessed the control file, but has not yet validated its entry or accessed the datafiles.
Open – Instance has validated the entries in the control files and is accessing the datafiles – it is now open for business.
The startup process is Nomount -> Mount -> Open.

Automatic Workload Repository (AWR)

Automatic Workload Repository (AWR)

 http://www.oracle-base.com/articles/10g/automatic-workload-repository-10g.php

http://docs.oracle.com/cd/E11882_01/server.112/e16638/autostat.htm



AWR is a separately licensed feature of the Oracle 11gR2 Diagnostic Pack

While Statspack reports will certainly provide sufficient details for I/O performance tuning, AWR reports offer significantly more detail, are easier to navigate, and are more efficient to execute because they access performance metric data that’s already been captured within AWR snapshots … and those snapshots were gathered in real time using direct memory access.)


http://docs.oracle.com/cd/E11882_01/server.112/e16638/autostat.htm#i27008

The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is both in memory and stored in the database. The gathered data can be displayed in both reports and views.
The statistics collected and processed by AWR include:

• Object statistics that determine both access and usage statistics of database segments
  
• Time model statistics based on time usage for activities, displayed in the V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views
  
• Some of the system and session statistics collected in the V$SYSSTAT and V$SESSTAT views
  
• SQL statements that are producing the highest load on the system, based on criteria such as elapsed time and CPU time
  
• ASH statistics, representing the history of recent sessions activity
  

Automatic Performance Statistics

http://docs.oracle.com/cd/E11882_01/server.112/e16638/autostat.htm

• When analyzing a performance problem in any of these scopes, you typically look at the change in statistics (delta value) over the period you are interested in. Specifically, you look at the difference between the cumulative value of a statistic at the start of the period and the cumulative value at the end.
• Cumulative values for statistics are generally available through dynamic performance views, such as the V$SESSTAT and V$SYSSTAT views
• Note that the cumulative values in dynamic views are reset when the database instance is shutdown
• The Automatic Workload Repository (AWR) automatically persists the cumulative and delta values for most of the statistics at all levels except the session level. 
• This process is repeated on a regular time period and the result is called an AWR snapshot. The delta values captured by the snapshot represent the changes for each statistic over the time period

NOT FOUND

Best practices in maintaining production support system including things not to do on a production system

pre-release and post release activities

Day to day customer support, query execution best practices, customer sensitive data

Oracle explain plan

http://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm

http://stackoverflow.com/questions/10572619/difference-between-explain-plan-and-execution-plan

http://www.dba-oracle.com/t_explain_plan.htm

Oracle indexing

http://docs.oracle.com/cd/B19306_01/server.102/b14231/indexes.htm

http://www.dba-oracle.com/art_9i_indexing.htm

Oracle Instance tuning

http://docs.oracle.com/cd/E11882_01/server.112/e16638/instance_tune.htm

These are the main steps in the Oracle performance method for instance tuning:

1. Define the Problem
   Get candid feedback from users about the scope of the performance problem.
   
2. Examine the Host System and Examine the Oracle Database Statistics
   
   • After obtaining a full set of operating system, database, and application statistics, examine the data for any evidence of performance problems.
     
   • Consider the list of common performance errors to see whether the data gathered suggests that they are contributing to the problem.
     
   • Build a conceptual model of what is happening on the system using the performance data gathered.
     
3. Implement and Measure Change
   Propose changes to be made and the expected result of implementing the changes. Then, implement the changes and measure application performance.
   
4. Determine whether the performance objective defined in step 1 has been met. If not, then repeat steps 2 and 3 until the performance goals are met.
   
   Define the Problem
   
   Gather the following data:
   
   
   1. Identify the performance objective - What is the measure of acceptable performance? How many transactions an hour, or seconds, response time will meet the required performance level?
   2. Identify the scope of the problem - What is affected by the slowdown? For example, is the whole instance slow? Is it a particular application, program, specific operation, or a single user?
      
   3. Identify the time frame when the problem occurs - Is the problem only evident during peak hours? Does performance deteriorate over the course of the day? Was the slowdown gradual (over the space of months or weeks) or sudden?
      
   4. Quantify the slowdown - This helps identify the extent of the problem and also acts as a measure for comparison when deciding whether changes implemented to fix the problem have actually made an improvement. Find a consistently reproducible measure of the response time or job run time. How much worse are the timings than when the program was running well?
      
   5. Identify any changes - Identify what has changed since performance was acceptable. This may narrow the potential cause quickly. For example, has the operating system software, hardware, application software, or Oracle Database release been upgraded? Has more data been loaded into the system, or has the data volume or user population grown?
      
   
   

Software deployment

http://en.wikipedia.org/wiki/Software_deployment

Software deployment is all of the activities that make a software system available for use.

Deployment Activities

Release 

The release activity follows from the completed development process. It includes all the operations to prepare a system for assembly and transfer to the customer site. 

 

Install and activate 

Activation is the activity of starting up the executable component of software. For simple system, it involves establishing some form of command for execution. For complex systems, it should make all the supporting systems ready to use.

In larger software deployments, the working copy of the software might be installed on a production server in a production environment. Other versions of the deployed software may be installed in a test environment, development environment and disaster recovery environment.

 

Deactivate 

Deactivation is the inverse of activation, and refers to shutting down any executing components of a system. Deactivation is often required to perform other deployment activities, e.g., a software system may need to be deactivated before an update can be performed. The practice of removing infrequently used or obsolete systems from service is often referred to as application retirement or application decommissioning.

 

Adapt 

The adaptation activity is also a process to modify a software system that has been previously installed. It differs from updating in that adaptations are initiated by local events such as changing the environment of customer site, while updating is mostly started from remote software producer.

 

Update 

The update process replaces an earlier version of all or part of a software system with a newer release.

 

Built-In 

Mechanisms for installing updates are built into some software systems. Automation of these update processes ranges from fully automatic to user initiated and controlled. Norton Internet Security is an example of a system with a semi-automatic method for retrieving and installing updates to both the antivirus definitions and other components of the system. Other software products provide query mechanisms for determining when updates are available.

 

Version tracking 

Version tracking systems help the user find and install updates to software systems installed on PCs and local networks.

• Web based version tracking systems notify the user when updates are available for software systems installed on a local system. 
• Local version tracking system notifies the user when updates are available for software systems installed on a local system. 
• Browser based version tracking systems notify the user when updates are available for software packages installed on a local system.

 

Uninstall 

Uninstallation is the inverse of installation. It is the removal of a system that is no longer required. It also involves some reconfiguration of other software systems in order to remove the uninstalled system’s files and dependencies.

 

Retire 

Ultimately, a software system is marked as obsolete and support by the producers is withdrawn. It is the end of the life cycle of a software product.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Maintenance window

http://en.wikipedia.org/wiki/Maintenance_window

In information technology and systems management, a maintenance window is a period of time designated in advance by the technical staff, during which preventive maintenance that could cause disruption of service may be performed.

The primary goal of maintenance is to avoid or mitigate the consequences of failure of equipment. This may be by preventing the failure before it actually occurs which Planned Maintenance and Condition Based Maintenance help to achieve

http://msdn.microsoft.com/en-us/library/cc144068.aspx

Within Microsoft System Center Configuration Manager 2007, maintenance windows (sometimes called service windows) provide administrators with a way to define a period of time that limits when changes can be made on the systems that are members of a collection.

Database Health monitoring - Oracle

http://docs.oracle.com/cd/B28359_01/server.111/b28310/diag007.htm

Beginning with Release 11g, Oracle Database includes a framework called Health Monitor for running diagnostic checks on the database.

Health Monitor checks (also known as checkers, health checks, or checks) examine various layers and components of the database. Health checks detect file corruptions, physical and logical block corruptions, undo and redo corruptions, data dictionary corruptions, and more. The health checks generate reports of their findings and, in many cases, recommendations for resolving problems. Health checks can be run in two ways:

• Reactive—The fault diagnosability infrastructure can run health checks automatically in response to a critical error.
  
• Manual—As a DBA, you can manually run health checks using either the DBMS_HM PL/SQL package or the Enterprise Manager interface. You can run checkers on a regular basis if desired, or Oracle Support may ask you to run a checker while working with you on a service request.
  

Health Monitor checks store findings, recommendations, and other information in the Automatic Diagnostic Repository (ADR).
Health checks can run in two modes:

• DB-online mode means the check can be run while the database is open (that is, in OPEN mode or MOUNT mode).
  
• DB-offline mode means the check can be run when the instance is available but the database itself is closed (that is, in NOMOUNT mode).
  

All the health checks can be run in DB-online mode. Only the Redo Integrity Check and the DB Structure Integrity Check can be used in DB-offline mode.

Health monitor runs the following checks:

• DB Structure Integrity Check—This check verifies the integrity of database files and reports failures if these files are inaccessible, corrupt or inconsistent. If the database is in mount or open mode, this check examines the log files and data files listed in the control file. If the database is in NOMOUNT mode, only the control file is checked.
  
• Data Block Integrity Check—This check detects disk image block corruptions such as checksum failures, head/tail mismatch, and logical inconsistencies within the block. Most corruptions can be repaired using Block Media Recovery. Corrupted block information is also captured in the V$DATABASE_BLOCK_CORRUPTION view. This check does not detect inter-block or inter-segment corruption.
  
• Redo Integrity Check—This check scans the contents of the redo log for accessibility and corruption, as well as the archive logs, if available. The Redo Integrity Check reports failures such as archive log or redo corruption.
  
• Undo Segment Integrity Check—This check finds logical undo corruptions. After locating an undo corruption, this check uses PMON and SMON to try to recover the corrupted transaction. If this recovery fails, then Health Monitor stores information about the corruption in V$CORRUPT_XID_LIST. Most undo corruptions can be resolved by forcing a commit.
  
• Transaction Integrity Check—This check is identical to the Undo Segment Integrity Check except that it checks only one specific transaction.
  
• Dictionary Integrity Check—This check examines the integrity of core dictionary objects, such as tab$ and col$. It performs the following operations:
  
  • Verifies the contents of dictionary entries for each dictionary object.
    
  • Performs a cross-row level check, which verifies that logical constraints on rows in the dictionary are enforced.
    
  • Performs an object relationship check, which verifies that parent-child relationships between dictionary objects are enforced.
    

http://www.dba-oracle.com/oracle_database_health_check.htm

How to identify performance bottlenecks - Oracle

http://www.dba-oracle.com/oracle10g_tuning/t_bottlenecks_identification.htm

The best way to isolate the bottlenecks in Oracle is to analyze the top five wait events for the database and look for any external waits that might be associated with disk, CPU and network

The best way to see system-level wait summaries is to run the awrrpt.sql script from the $ORACLE_HOME/rdbms/admin directory.  

This will yield the top 5 timed events for the specific interval between AWR snapshots

Top wait events can also be quickly identified by using the Top Wait Events report in Ion to see the overall system bottleneck

External Bottlenecks

Oracle does not run in a vacuum, and any tool must be able to detect external bottlenecks from any area of the server environment:

§         Disk:  Both scattered reads, such as full-table scans, and sequential reads, such as index probes, are monitored.

§         Network:  The SQL*Net metrics are monitored and can easily spot when an application is network-bound.

§         CPU:  The display tracks processor consumption.

Internal Bottlenecks

Internally, the Oracle database has many shared resources and potential bottlenecks, and Ion OEM and DBFlash visualize the following metrics:

§         latch and lock waits: These waits result from serialized access latches.

§         control file waits: Oracle touches the control file very frequently.

§         buffer busy waits: Segment header contention with the data buffers can cause bottlenecks.

§         enqueue waits: This can indicate internal latch and lock contention.

§         log buffer waits: Events such as redo log space requests can cripple Oracle performance.

§         undo segment waits: Undo segments can cause serialization waits.

§         buffer deadlocks:  Internal locking and latching can cause contention.

 
 

http://www.databasejournal.com/features/oracle/article.php/3917631/Identifying-and-Evaluating-IO-Bottlenecks-in-Oracle-Database.htm

Automatic Database Diagnostic Monitor (ADDM)

Automatic Database Diagnostic Monitor (ADDM) and Automatic Workload Repository (AWR)

Automatic Database Diagnostic Monitor (ADDM)
http://docs.oracle.com/cd/B28359_01/server.111/b28275/tdppt_auto.htm

• Aautomatically detects and reports performance problems with the database

• The results are displayed as ADDM findings on the Database Home page in Oracle Enterprise Manager (Enterprise Manager)
• Reviewing the ADDM findings enables you to quickly identify the performance problems that require your attention.
• Each ADDM finding provides a list of recommendations for reducing the impact of the performance problem
• Reviewing ADDM findings and implementing the recommendations are tasks that you should perform daily as part of the regular database maintenance
• Even when the database is operating at an optimal performance level, you should continue to use ADDM to monitor database performance on an ongoing basis 

• ADDM examines and analyzes data captured in the Automatic Workload Repository (AWR) to determine possible performance problems in Oracle Database
• ADDM then locates the root causes of the performance problems, provides recommendations for correcting them, and quantifies the expected benefits. ADDM also identifies areas where no action is necessary
• The ADDM analysis is performed from the top down, first identifying symptoms and then refining the analysis to reach the root causes of performance problems. ADDM uses the DB time statistic to identify performance problems
• DB time is the cumulative time spent by the database in processing user requests, including both the wait time and CPU time of all user sessions that are not idle.

Oracle SQL tuning

http://www.dba-oracle.com/art_sql_tune.htm
http://docs.oracle.com/cd/E23824_01/html/821-1451/docinfo.html#scrolltoc


Server Tuning
   iostat  -  Use the iostat command to report statistics about disk input and output, and to produce measures of throughput, utilization, queue lengths, transaction rates, and service time. For a detailed description of this command, refer to the iostat(1M) man page.

http://docs.oracle.com/cd/E23824_01/html/821-1451/spmonitor-4.html
  
   vmstat - You can use the vmstat command to report virtual memory statistics and information about system events such as CPU load, paging, number of context switches, device interrupts, and system calls. The vmstat command can also display statistics on swapping, cache flushing, and interrupts.

http://docs.oracle.com/cd/E23824_01/html/821-1451/spmonitor-22.html

   top - Provides an ongoing look at processor activity in real time. It displays a listing of the most CPU-intensive tasks on the system, and can provide an interactive interface for manipulating processes. It can sort the tasks by CPU usage, memory usage and runtime.

http://linux.about.com/od/commands/l/blcmdl1_top.htm

• Optimize the server kernel - You must always tune your disk and network I/O subsystem (RAID, DASD bandwidth, network) to optimize the I/O time, network packet size and dispatching frequency.
   
• Adjusting your optimizer statistics - You must always collect and store optimizer statistics to allow the optimizer to learn more about the distribution of your data to take more intelligent execution plans.  Also, histograms can hypercharge SQL in cases of determining optimal table join order, and when making access decisions on skewed WHERE clause predicates.
   
• Adjust optimizer parameters - Optimizer optimizer_mode, optimizer_index_caching, optimizer_index_cost_adj.
   
• Optimize your instance - Your choice of db_block_size, db_cache_size, and OS parameters (db_file_multiblock_read_count, cpu_count, &c), can influence SQL performance.
   
• Tune your SQL Access workload with physical indexes and materialized views - Just as the 10g SQLAccess advisor recommends missing indexes and missing materialized views, you should always optimize your SQL workload with indexes, especially function-based indexes, a Godsend for SQL tuning. 

Once the environment, instance, and objects have been tuned, the Oracle administrator can focus on what is probably the single most important aspect of tuning an Oracle database: tuning the individual SQL statements.

Oracle SQL tuning goals

The goals of SQL tuning focus on improving the execution plan to fetch the rows with the smallest number of database "touches" (LIO buffer gets and PIO physical reads).  

• Remove unnecessary large-table full-table scans—Unnecessary full-table scans cause a huge amount of unnecessary I/O and can drag-down an entire database. The tuning expert first evaluates the SQL based on the number of rows returned by the query. The most common tuning remedy for unnecessary full-table scans is adding indexes. Standard b-tree indexes can be added to tables, and bitmapped and function-based indexes can also eliminate full-table scans. In some cases, an unnecessary full-table scan can be forced to use an index by adding an index hint to the SQL statement.
   
• Cache small-table full-table scans—In cases where a full-table scan is the fastest access method, the administrator should ensure that a dedicated data buffer is available for the rows.  In Oracle8 and beyond, a small table can be cached by forcing it into the KEEP pool.
   
• Verify optimal index usage—Oracle sometimes has a choice of indexes, and the tuning professional must examine each index and ensure that Oracle is using the proper index.
   
• Materialize your aggregations and summaries for static tables - One features of the Oracle 10g SQLAccess advisor is recommendations for new indexes and suggestions for materialized views.  Materialized views pre-join tables and pre-summarize data, a real silver bullet for data mart reporting databases where the data is only updated daily. Again, see the book "Oracle Tuning: The Definitive Reference", for complete details on SQL tuning with materialized views.