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.