Oracle instance properties script (ORACLE_HOME, STATE, VERSION ...)

This script will give some important properties of your instance

SELECT status,
       startup_time,
       instance_name,
       version,
       host_name,
       (SELECT SUBSTR (file_spec, 1, INSTR (file_spec, 'lib') - 2)
          FROM dba_libraries
         WHERE library_name = 'DBMS_SUMADV_LIB')
          ORACLE_HOME
  FROM v$instance;

Oracle Flashback monitoring scripts

PROMPT How Far Back Can We Flashback To (Time)?
PROMPT
select to_char(oldest_flashback_time,'dd-mon-yyyy hh24:mi:ss') "Oldest Flashback Time"
 from v$flashback_database_log;

PROMPT
PROMPT How Far Back Can We Flashback To (SCN)?
PROMPT
col oldest_flashback_scn format 99999999999999999999999999
select oldest_flashback_scn from v$flashback_database_log;

PROMPT
PROMPT Flashback Area Usage
SELECT * FROM   V$FLASH_RECOVERY_AREA_USAGE;

PROMPT
col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)" format 999999
col round(space_used/1048576) heading "Space Used (MB)" format 99999
col name Heading "Flashback Location" format a40

select name, round(space_limit/1048576),round(space_used/1048576)
 from  v$RECOVERY_FILE_DEST;

What is a log file sync ?

Log file sync is a client wait event.  It is the wait event your clients wait on when 
they say "commit".  It is the wait for LGWR to actually write their redo to disk and 
return back to them.  You can "tune" this by making lgwr faster (no raid 5 for example) 
and committing less frequently and generating less redo (BULK operations generate less 
redo than row by row do)

The other one is a background wait. LGWR is waiting for forgrounds to finish a current 
copy affecting the data LGWR is about to process.

HOWEVER, that said, tuning either of these will have no noticable affect on your systems 
performance whatsoever!  It certainly looks like "enqueue" is your wait and that is all 
about application design - those are heavyweight locks induced by the application logic 
itself.  You would be best served by looking at the *application* not at the "system" at 
this point. 

More infos here

Strategies and techniques to resolve 'log file sync' waits

Commit is not complete until LGWR writes log buffers including commit redo recods to log files. In a
nutshell, after posting LGWR to write, user or background processes waits for LGWR to signal back
with 1 sec timeout. User process charges this wait time as 'log file sync' event.

Root causes of 'log file sync', essentially boils down to few scenarios :

• Disk I/O performance to log files is not good enough. Even though LGWR can use
  asynchronous I/O, redo log files are opened with DSYNC flag and buffers must be
  flushed to the disk (or at least, written to disk array cache in the case of SAN) before
  LGWR can mark commit as complete.
• LGWR is starving for CPU resource. If the server is very busy, then LGWR can starve
  for CPU too. This will lead to slower response from LGWR, increasing 'log file sync'
  waits. After all, these system calls and I/O calls must use CPU. In this case, 'log file
  sync' is a secondary symptom and resolving root cause for high CPU usage will reduce
  'log file sync' waits.
• Due to memory starvation issues, LGWR can be paged out. This can lead to slower
   response from LGWR too
• LGWR is unable to complete writes fast enough due to file system or unix buffer
  cache limitations.
• LGWR is unable to post the processes fast enough, due to excessive commits. It is quite
  possible that there is no starvation for cpu or memory and I/O performance is decent enough. Still, if
  there are excessive commits,  then LGWR has to perform many writes/semctl calls and this can
  increase 'log file sync' waits. This can also result in sharp increase in 'redo wastage' statistics'
• With Private strands, a process can generate few Megabytes of redo
  before committing. LGWR must write generated redo so far and processes must wait for 'log file sync'
  waits, even if redo generated from other processes is small enough
• LGWR is suffering from other database contention such as enqueue waits or latch contention.
  For example, we have seen LGWR freeze due to CF enqueue contention.

Finding and understanding root cause is essential to resolve a performance issue. 

• If I/O bandwith is an issue, then doing anything other than improving I/O bandwidth is not
  useful. Switching to file systems providing better write throughput is one option. RAW devices are
  another option. Reducing # of log file members in a group is another option as it reduces # of write
  calls. But, this option comes with a cost.
• If CPU starvation is an issue, then reducing CPU starvation is the correct step to resolve it.
  Increasing priority of LGWR is a work around
• If commit rate is higher, then decreasing commits is correct step but, in few case, if that is not
  possible, increasing priority of LGWR (using nice) or increasing priority class of LGWR to RT might
  provide some relief.
• Solid State Disk devices also can be used if redo size is extreme. In that case, it is also
  preferable to decrease redo size.
• If excessive redo size is root cause, redo size can be reduced using various techniques

vmstat relevant columns with descriptions

Column	Description
kthr	Kernel thread state changes per second over the sampling interval.
r	Number of kernel threads placed in run queue.
b	Number of kernel threads placed in the Virtual Memory Manager (VMM) wait queue (awaiting resource, awaiting input/output).
p	The number of threads waiting on raw I/Os (bypassing journaled file system (JFS)) to complete.
fi/fo	Number of file pages paged in/out per second.
cpu	Breakdown of percentage usage of CPU time. For multiprocessor systems, CPU values are global averages among all processors. Also, the I/O wait state is defined system-wide and not per processor.
us	Average percentage of CPU time executing in the user mode.
sy	Average percentage of CPU time executing in the system mode.
id	Average percentage of time that CPUs were idle and the system did not have an outstanding disk I/O request.
wa	CPU idle time during which the system had outstanding disk/NFS I/O request(s). If there is at least one outstanding I/O to a disk when wait is running, the time is classified as waiting for I/O. Unless asynchronous I/O is being used by the process, an I/O request to disk causes the calling process to block (or sleep) until the request has been completed. Once an I/O request for a process completes, it is placed on the run queue. If the I/Os were completing faster, more CPU time could be used.
pc	Number of physical processors consumed. Displayed only if the partition is running with shared processor.
ec	The percentage of entitled capacity consumed. Displayed only if the partition is running with the shared processor.

Aix CPU/Kernel monitoring and tuning tools

Tool	Function
topas	Reports selected local and remote system statistics
vmstat	Reports virtual memory statistics
sar	Collects, reports, or saves system activity information
wlmstat	Shows Workload Manager (WLM) per class resource utilization statistics
wlmmon	The wlmmon and wlmperf commands provide graphical views of Workload Manager (WLM) resource activities by class
xmperf
tprof	Reports processor usage
pprof	Reports CPU usage of all kernel threads over a period of time
nmon	Displays local system statistics in interactive mode and records system statistics in recording mode
schedo	Manages processor scheduler tunable parameters

Aix Disk I/O monitoring and tuning tools

Tool	Function
filemon	Monitors the performance of the file system, and reports the I/O activity on behalf of logical files, virtual memory segments, logical volumes, and physical volumes
fileplace	Displays the placement of file blocks within logical or physical volumes
lvmstat	Reports input/output statistics for logical partitions, logical volumes and volume groups. Also reports pbuf and blocked I/O statistics and allows pbuf allocation changes to volume groups
iostat	Reports Central Processing Unit (CPU) statistics, asynchronous input/output (AIO) and input/output statistics for the entire system, adapters, TTY devices, disks CD-ROMs, tapes and file systems
wlmstat	Shows Workload Manager (WLM) per class resource utilization statistics
wlmmon	The wlmmon and wlmperf commands provide graphical views of Workload Manager (WLM) resource activities by class
ioo	Manages Input/Output tunable parameters

IBM Aix tools you should know when running Oracle on Aix

Tool	Function
lsattr	Displays the attributes of devices
lslv	Displays information about a logical volume or the logical volume allocations of a physical volume
netstat	Displays the contents of network-related data structures
nfsstat	Displays statistics about Network File System and Remote Procedure Call activity
nice	Changes the initial priority of a process
no	Displays or sets network options
ps	Displays the status of one or more processes
reorgvg	Reorganizes the physical-partition allocation within a volume group
time	Displays the elapsed execution, user CPU processing, and system CPU processing time
trace	Records and reports selected system events
vmo	Manages Virtual Memory Manager tunable parameters

How to Reduce file system cache size on Aix to resolve paging issues

By default, AIX is tuned for a mixed workload, and will grow its VMM file cache up to 80% of physical RAM, it is very poor for running any application which does its own cache management.

For most database systems, the ideal solution is to use raw logical volumes. If this is not acceptable, then direct I/O and concurrent I/O should be used. If for some reason this is not possible, then the last solution is to tune the AIX file caches to be less aggressive. 

The four main parameters that should be tuned are the three controlling the size of the persistent file cache (minperm% and maxperm%) used for JFS filesystems, and the client file cache (maxclient%) used by NFS, CDRFS and JFS2 filesystems, and also the lru_file_repage parameter, which influences what pages the VMM page stealing algorithm will steal (present in AIX 5.2 ML4+ and AIX 5.3 ML1+).

numperm% 

Defines the current size of the persistent file cache.

minperm% 

Defines the minimum amount of RAM the persistent file cache may occupy. If numperm% is less than or equal to minperm%, file pages will not be stolen when RAM is required.

maxperm% 

Defines the maximum amount of RAM the persistent file cache may occupy before it is used as the sole source of new pages by the page stealing algorithm. By default, numperm% may exceed maxperm% if there is free memory available. The setting strict_maxperm may be set to one to change maxperm% into a hard limit, guaranteeing numperm% will never exceed maxperm%.

strict_maxperm 

As above, if set to 1, changes maxperm% into a hard limit.

numclient% 

Defines the current size of the client file cache.

maxclient% 

Defines the hard maximum size of the client file cache.

strict_maxclient 

Introduced in 5.2 ML4, allows the changing of maxclient% into a soft limit, similar to strict_maxperm.

lru_file_repage 

Introduced in AIX 5.2 ML4 and AIX 5.3 ML1, this influences the VMM page stealing algorithm. If set to 0, the algorithm will strongly prefer stealing file pages to satisfy memory requests.

Note that maxclient% may never exceed maxperm%. In later versions of vmtune, this is enforced by changing both parameters if necessary.

How to enable or disable user tracing

User tracing is enabled or disabled at the session or instance level by using the following commands and
parameter:

• Session level using the ALTER SESSION command: ALTER SESSION SET SQL_TRACE = TRUE
• Session level by executing DBMS procedure: dbms_system.SET_SQL_TRACE_IN_SESSION
• Instance level by setting the initialization parameter: SQL_TRACE = TRUE

Ways to reduce the shared pool latch

The shared pool latch is used to protect critical operations when allocating and freeing memory in the shared pool. If an application makes use of literal (unshared) SQL then this can severely limit scalability and throughput. The cost of parsing a new SQL statement is expensive both in terms of CPU requirements and the number of times the library cache and shared pool latches may need to be acquired and released. Before Oracle9, there use to be just one such latch to the entire database to protects the allocation of memory in the library cache. In Oracle9 multiple childs were introduced to relieve contention on this resource


To reduce the shared pool latch :

•  Avoid hard parses when possible, parse once, execute many 
•  Eliminate literal SQL is also useful to avoid the shared pool latch 
•  The size of the shared_pool and use of MTS (shared server option) also greatly influences the shared pool latch

How to minimize log file sync waits

When a user session commits (or rolls back), the session's redo information must be flushed to the redo logfile by LGWR. The server process performing the COMMIT or ROLLBACK waits under this event for the write to the redo log to complete. If this event's waits constitute a significant wait on the system or a significant amount of time waited by a user experiencing response time issues or on a system, then examine the average time waited. If the average time waited is low, but the number of waits are high, then the application might be committing after every INSERT, rather than batching COMMITs. Applications can reduce the wait by committing after 50 rows, rather than every row. If the average time waited is high, then examine the session waits for the log writer and see what it is spending most of its time doing and waiting for. If the waits are because of slow I/O, then try the following:

- Reduce other I/O activity on the disks containing the redo logs, or use dedicated disks
- Alternate redo logs on different disks to minimize the effect of the archiver on the log writer
- Move the redo logs to faster disks or a faster I/O subsystem
- Consider using raw devices to speed up the writes
- Depending on the type of application, it might be possible to batch COMMITs by committing every N rows, rather than every row, so that fewer log file syncs are needed

Buffer Busy Waits : Common Causes, Diagnosis, and Actions

A session that reads or modifies a buffer in the SGA must first acquire the cache buffers chains latch and traverse the buffer chain until it finds the necessary buffer header. Then it must acquire a buffer lock or a pin on the buffer header in shared or exclusive mode, depending on the operation it intends to perform. Once the buffer header is pinned, the session releases the cache buffers chains latch and performs the intended operation on the buffer itself. If a pin cannot be obtained, the session waits on the buffer busy waits wait event. This wait event does not apply to read or write operations that are performed in sessions’ private PGAs.

Keep the following key thoughts in mind when dealing with the buffer busy waits event.

• The Oracle session is waiting to pin a buffer. A buffer must be pinned before it can be read or modified. Only one process can pin a buffer at any one time.
• buffer busy waits indicate read/read, read/write, or write/write contention.
• The appropriate action to take depends on the reason encoded in the P3 parameter.
  
  
  The following pieces of information are essential for diagnosing the buffer busy waits problem when it is the leading bottleneck that slows a process down.
  
  
• The primary reason code that represents why a process fails to get a buffer pin.
• The class of block that the buffer busy waits wait event is for.
• The SQL statement that is associated with the buffer busy waits event.
• The segment that the buffer belongs to.

A session may fail to get a pin on a buffer header for various reasons. The reasons are represented by a set of codes. Up to Oracle9i Database, a process that waits on the buffer busy waits event publishes the reason code in the P3 parameter of the wait event.

This is typical in applications that spawn multiple concurrent threads or sessions, and each one executes the same query that goes after the same data set. In this case, you can check the sessions’ logon time in the V$SESSION view, and chances are you will find them only a few seconds apart. When multiple sessions request the same data block that is not in the buffer cache, Oracle is smart enough to prevent every session from making the same operating system I/O call. Otherwise, this can severely increase the number of system I/Os. Instead, Oracle allows only one of the sessions to perform the actual I/O, while others wait for the block to be brought into the buffer cache. The other sessions wait for the block on the buffer busy waits event and the session that performs the I/O waits on the db file sequential read (or the db file scattered read) wait event. You will notice that the buffer busy waits and the db file sequential read events share the same P1 (file#) and P2 (block#) values. 

Reason code 220 indicates there are multiple sessions trying to concurrently modify different rows within the same block that is in the buffer cache. This symptom is typical in applications with high DML concurrency. Unfortunately, a block can be pinned by only one process at any one time. The other concurrent processes must wait on the buffer busy waits wait event until the first change is complete. This is a good thing; otherwise the block will be corrupted.

If the majority of the buffer busy waits wait events are centered on data blocks (class #1) and the reason code is 130, this shows the application runs multiple sessions that query the same data set at the same time. (You will only know this if you query the V$SESSION_WAIT view repeatedly or trace the session with the 10046 event or use the data sampling methods) This is an application issue. There are three things you can do to minimize this problem:

• Reduce the level of concurrency or change the way the work is partitioned between the parallel threads.
• Optimize the SQL statement to reduce the number of physical and logical reads.
• Increase the number of FREELISTS and FREELIST GROUPS.

From our experience, it is very difficult to get the application to reduce the level of concurrency. It may not be a good idea because it limits scalability. However, there are differences between scalability and a blind attempt by the application to improve performance by spawning multiple sessions. So far, SQL tuning has worked wonderfully to reduce the occurrences of buffer busy waits. Check the SQL execution plan and optimize the SQL statement to use the most effective join method and access paths that reduce the number of physical and logical reads.

If the majority of the buffer busy waits wait events are centered on data blocks and the reason code is 220, this indicates there are multiple sessions performing DML on the same object at the same time. In addition, if the database block size is large (for example, 16K and above), it can only intensify this symptom as larger blocks generally contain more rows per block. There are three things you can do to minimize this problem:

• Reduce the level of concurrency or change the portioning method.
• Reduce the number of rows in the block.
• Rebuild the object in another tablespace with a smaller block size (Oracle9i Database and above).

Again, as mentioned earlier, it may not be practical to limit scalability by reducing the level of concurrency.

If the data blocks belong to tables or indexes, then consider rebuilding the objects to reduce the number of rows per block and spread the data over a larger number of blocks. For example, you can rebuild a table or an index with a higher PCTFREE. In some cases, we have rebuilt indexes with PCTFREE as high as 50 percent. The downside to this is that index range scans and index fast full scans will be slower. You can also alter the table to minimize the number of rows per block with the ALTER TABLE table_name MINIMIZE RECORDS_PER_BLOCK command. Starting in Oracle9i Database, you can move or rebuild the object in another tablespace with a smaller block size. While these actions can minimize the buffer busy waits problem, they will definitely increase full table scans time and disk space utilization. As the saying goes, there is no such thing as a free lunch.

If the majority of the buffer busy waits wait events are centered on data segment headers (that is, the table or index segment header and not the undo segment header), this usually means some tables or indexes in the database have high segment header activities. Processes visit segment headers for two main reasons—to get or modify the process FREELISTS information and to extend the high watermark (HWM). There are three things you can do to minimize this problem:

• Increase the number of process FREELISTS and FREELIST GROUPS of the identified object.
• Make sure the gap between PCTFREE and PCTUSED is not too small.
• Make sure the next extent size is not too small.

The first step is to identify the segment name and type. They can be identified from the P1 and P2 parameters of the buffer busy waits wait event. Then you can alter the object to increase the number of process FREELISTS. If time and opportunity permit, you should also increase the number of FREELIST GROUPS by recreating the object. You should create all data segments with a minimum of two FREELIST GROUPS, by default—even in a single-instance database.
If you do not want to mess with FREELISTS and FREELIST GROUPS, you can rely on the Automatic Segment Space Management (ASSM) feature to scatter the incoming data from the insert statements.

Finally, you should check the next extent size of the identified segment. A high insert rate combined with a small next extent size can cause frequent insertion of new entries into the extent map located in the segment header. Consider altering or rebuilding the object with a larger next extent size. If the object resides in a locally managed tablespace, consider moving the object into a reasonable uniform-size locally managed tablespace.

If the majority of the buffer busy waits wait events are centered on undo segment headers, this indicates there are either too few rollback segments in the database or their extent sizes are too small, causing frequent updates to the segment headers. If you use the system-managed undo introduced in Oracle9i Database, you shouldn’t have to deal with this problem as Oracle will create additional undo segments according to demand.

If the majority of the buffer busy waits wait events are centered on undo blocks, this usually means there are multiple concurrent sessions querying data that is being updated at the same time. Essentially the query sessions are fighting for the read consistent images of the data blocks. This is an application issue and there is nothing amiss in the database. The problem should go away when the application can run the query and DML at different times.

Oracle maintains a number of instance-level statistics on buffer busy waits. These statistics can give you a rough idea of what you are dealing with, but the information may not be specific enough for you to formulate a corrective action. They are mentioned here for the sake of completeness.

The view X$KCBWAIT (kernel cache buffer wait) is the base view for the V$WAITSTAT view, which keeps track of buffer busy waits contentions by block class. The class with the highest count deserves your attention, but unless you are also monitoring the buffer busy waits symptom at a lower level, you don’t have a clear direction to proceed. Let’s say the data block class has the highest count of all. Which segment was affected most, and why were the sessions unable to pin the buffers? Did they fail to get the pins while attempting to read or change the blocks? Unfortunately, Oracle does not keep track of buffer busy waits by block class and reason code. We hope someday Oracle will provide a matrix of buffer busy waits by SQL statement, segment name, and block class.

Ways to optimize sorting in Oracle

A sort area in memory is used to sort records before they are written out to disk. Increasing the size of this memory by increasing the value of the initialization parameters SORT_AREA_SIZE or PGA_AGGREGATE_TARGERT, lets you sort more efficiently. SORT_AREA_SIZE is a dynamic parameter that can be set at either the session level using an ALTER SESSION command or system wide using an ALTER SYSTEM command. PGA_AGGREGATE_TARGET is an initialization parameter that defines the total size of all working areas, including the sort area, for all sessions. This parameter is available in Oracle 9i and 10g.


Sorting is performed by the following statements:

• CREATE INDEX
• GROUP BY
• ORDER BY
• INTERSECT
• MINUS
• UNION
• DISTINCT
• Unindexed table joins
• Some correlated subquerie

If the data being sorted does not fit in memory, Oracle must sort it in small runs. As each run is completed, Oracle stores the data in temporary segments on disk. After all of the runs have completed, Oracle merges the data to produce the sorted data. This is less efficient than doing the entire sort in memory.

In general, try to allocate as much space in memory as possible for SORT_AREA_SIZE (assuming that your entire sort can fit into memory). When defined, this parameter will cause memory to be allocated from the Process Global Area (PGA) unless the database is using Oracle’s Shared Server architecture (called Multi Threaded Sever (MTS) prior to Oracle9i). If the Oracle Shared Server architecture is being used, then the sort memory will be allocated from the large pool so long as the large pool is allocated. The sort memory will be allocated alternatively from the User global area (UGA) in the shared_pool if the large pool is not available. 

Consider using a two megabyte SORT_AREA_SIZE when your sorted data exceeds 100 megabytes in size. Because SORT_AREA_SIZE is allocated per user, increasing this parameter can exhaust memory very quickly if a large number of users are logged on. You also need to make sure that the temporary segments to which the sort operation will write its output (if it runs out of memory) are large enough, with appropriately sized INITIAL and NEXT extents. Again, the PGA_AGGREGATE_TARGET method of allocating working areas helps with this problem.


To find out whether sorting is affecting performance in your system, monitor the sorting disk activity in your system and then adjust accordingly. One good way to do this is to define a separate tablespace for temporary tables. By watching the I/O rate on the temporary tablespaces, you can detect how frequently the sort process failed to perform the entire sort in memory. You can monitor the V$SYSSTAT table to observe memory and disk activity.
Below is an example of querying the V$SYSSTAT table:

SQL> select name, value from v$sysstat where name like 'sort%';

      NAME                                                         VALUE
---------------------------------------------------------------- ----------
sorts (memory)                                                         1658
sorts (disk)                                                              2
sorts (rows)                                                        2232346

The "sorts(memory)" statistic shows the total number of sort operations that could be performed completely within the sort buffer in memory, without using the temporary tablespace segments. The "sorts(disk)" statistic shows the number of sort operations that could not be performed in memory. (Note that this number does not represent the total number of times a temporary table extent was written by a sort process.) Out of a total of 1293 sorts, only two required disk usage.


Using dedicated temporary tablespaces (created via the create temporary tablespace command) can provide some performance improvements with regards to sorting. This is because the temporary sort segments, once used, will not be removed but will instead be reused by the next process that needs to do a sort to disk. This eliminates the overhead of having to remove and recreate sort segments. This also means that your temporary tablespace may appear to contain segments in it most of the time. This is perfectly natural.
Also, consider in Oracle 9i and later, assigning a default temporary tablespace to the database. This has the benefit of allowing you to assign users to a temporary tablespace other than SYSTEM automatically when the user is created. Sorts occurring in the SYSTEM tablespace can badly fragment the tablespace and can cause other performance related problems.


Your goal should be to always reduce or eliminate sorts completely. Again, this starts at the application layer. Here is a list of suggested things you can look for in application code, which might be causing unneeded sort operations. While these different operations may well be needed, it’s a good idea to review your code and make sure.

1. Avoid using the DISTINCT clause unless necessary.
2. Use the UNION ALL clause in place of the UNION clause unless duplicates need to be eliminated.
3. Try to use HASH JOINS instead of SORT MERGE JOINS. The use of hints will cause the optimizer to choose this join.
4. Use appropriate index hints to avoid sorts.
5. The cost based optimizer will try to avoid a sort operation when the FIRST_ROWS hint is used.
6. Make sure that your SQL query is taking advantage of the best available indexing options.
7. Review operational SQL code for unneeded sort operations, such as order by clauses.

Follow these suggestions if your monitoring shows that you have a sorting problem:

1. Check if the sort is really necessary. Has an index been inadvertently overlooked? Can a SQL statement be structured more efficiently?
    
2. Increase the value of the SORT_AREA_SIZE parameter. Because this increase applies to all user processes, this is likely to consume a lot of memory. Make sure you don't increase the value of SORT_AREA_SIZE to the point where you have little free memory. The maximum allowable value is system-dependent. If you are using the PGA_AGGREGATE_TARGET method of work area allocation, then check the V$PGASTAT view to ensure that this parameter is set correctly.
    
3. Verify your temporary tablespace extents are large enough. Make sure that you specify large enough table extents (in the INITIAL and NEXT parameters on the CREATE statement) to allow SORT_AREA_SIZE of memory to be written to disk without having to throw multiple extents. Make your temporary segments a minimum of SORT_AREA_SIZE + 1 block. It is a very good idea to use Locally Managed Tablespaces with the UNIFORM extent allocation method for your temporary tablespaces. This way, you ensure that the extent sizes are the same for every sort segment.
    
4. Let users who require larger sorts use a temporary tablespace with larger INITIAL and NEXT default tablespace storage parameters. This is a less likely, but possible, alternative which will help reduce the degree of dynamic extension. For example, during daily online transaction processing hours, set your default settings to:
   

   ALTER TABLESPACE temp_tspace DEFAULT STORAGE
   (INITIAL 260K  NEXT 260K  PCTINCREASE 0);

   
   For overnight processing, you might set the default storage to:
   

   ALTER TABLESPACE temp_tspace DEFAULT STORAGE
   (INITIAL 5M  NEXT 5M  PCTINCREASE 0);

   
   If you are using Locally Managed Tablespaces, then you will not be able to modify the default storage clause as shown above. Instead, create two Locally Managed temporary tablespaces, one with smaller extents and the other with larger extents. Instead of altering the default storage clause information above, you would modify the default temporary tablespace for the entire database (in Oracle 9i and 10g). For overnight processing, you would issue a command Similar to the following:
   

   ALTER DATABASE DEFAULT TEMPORARY TABLESPACE big_temp;

5. Set your INITIAL and NEXT extent sizes to one block plus a multiple of the sort area size. This will achieve minor improvements in response times. Assuming that you have a DB_BLOCK_SIZE of 4K and a SORT_AREA_SIZE of 64K, you may consider any of the following sizes or a higher size, depending on your requirements:
   

   4K  +  (1 * 64K)  =   68K
   4K  +  (2 * 64K)  =  130K
   4K  +  (3*  64K)  =  196K
   4K  +  (4 * 64K)  =  260K

   If you are using Locally Managed Tablespaces, set the uniform extent size to one of the above values.
   
6. Make sure to use the SORT_AREA_RETAINED_SIZE. Oracle will restore the sort area available to user processes to the size specified in this parameter if it believes that the sort area data will not be referenced in the near future. This will save memory. If memory is tight, we highly recommend that you take advantage of this feature by setting your SORT_AREA_RETAINED_SIZE to half the SORT_AREA_SIZE. For example, you might set:
   

   SORT_AREA_SIZE           = 131072   (128K)
   SORT_AREA_RETAINED_SIZE  = 65536     (64K)

7. Use the TEMPORARY type when creating tablespaces. This tablespace type is tuned especially for sorting and will boost your performance. We recommend that you use it as your users' TEMPORARY tablespace.

   

ORACLE PARAMETERS TO IMPROVE CURSOR PROCESSING PERFORMANCE

For applications where literals are being used instead of bind variables, leading to unnecessary hard parsing, the parameter cursor_sharing can be used.  The performance of such applications is primarily influenced by the hard parse necessary.  By default, Oracle will behave doing a hard parse for each new SQL statement.  If, however, these SQL statements could be shared had the literal(s) been replaced by a bind variable, setting the parameter to one of the non-default values will change this behavior.  Doing this will bring
some of the benefit of coding applications with bind variables instead of literals.

The cursor_sharing parameter can be set to one of three values:

• exact  SQL statements are only shared if they are exactly identical. This is the default

• force  If SQL statements only differ in literal values, they will be shared as if the literals had been bind variables.  This will be done unconditionally.

• similar  Causes cursor sharing to take place when this is known not to have any impact on optimization.

The parameter can be set at the system level using the alter system command or at the session level using the alter session command.

It is highly recommended not to write applications dependent on this parameter, and to use the parameter only when needed for existing applications incorrectly using literals. 

Applications identified by repeated (soft) parse of identical SQL statements.  On the Oracle server side, this has the implication that the same values repeatedly are assigned to the server side information about cursors.  This behavior can be modified by allowing the server to keep information available for frequently parsed SQL statement, at the expense of the need to lookup such SQL statements. 
The parameter session_cached_cursors can be used to do exactly that:  If set to an integer value, the Oracle server engine will attempt to keep that many cursors in each session parsed and ready for execution.  Appropriate values for the session_cached_cursors parameter depend on the Oracle release. 
In Oracle9i Release 2 and later, values as high as several hundreds can be used, in earlier releases, values above 10 to 20 are not likely to be useful as more CPU usage has a tendency to negate the effect of increased scalability using the parameter. 

Generate monitoring scripts for all indexes

select 'alter index '||index_name||' monitoring usage' from user_indexes where
      index_type=’NORMAL’;

Script to identify queries having full table scan

SELECT   sql_text
    FROM   v$sql
   WHERE   (address, hash_value, child_number) IN
                 (SELECT   address, hash_value, child_number
                    FROM   v$sql_plan
                   WHERE   operation = 'TABLE ACCESS' AND options = 'FULL')
ORDER BY   sql_text

Fourthelephant Insider for Oracle ! the best alternative for Quest Spotlight on Oracle

Insider is a real-time performance tuning solution for Oracle. It allows you to monitor all aspects of Oracle internal workings and presents this in a revolutionary and sleek user interface.

Oracle Instance View
Oracle Instance view is a detailed real-time snapshot of an Oracle database showing performance and other critical information for a single Oracle instance

Oracle Alert Log
Whenever an error is discovered in the Oracle alert log an alert is shown on instance view and it can be opened in the separate window.

System Configuration
Configuration subview shows other important Oracle database and instance configuration data as well as licensing, options and NLS parameters.

 System Statistics Chart
System statistics charts subview allows to select any number of Oracle statistics and add them to a drill-down chart. A month worth of statistics history can be analyzed.

Enterprise View
Enterprise view is the starting point where you add, remove and arrange the items you would like to monitor, view some basic information about them, enable and disable items, view and configure alerts.

Oracle Context Menu
From Oracle Context Menu you can start and stop Oracle instance, start SQL*Plus and perform some other DBA functions.

Availability Report
You can generate an availability report for any server Insider is monitoring.

External Tools
You can easily extend Insider functionality by configuring external tools such as SQL*Plus, ssh or SQL Developer right on an Enterprise View item.

Tools Configuration
External tools can be configured with various variables, such as host name, oracle username etc.

100+ Configurable Alerts
You can disable alerts for all databases, you can disable them for a particular database and you can change alerts thresholds.

Alerts MiniHelp
 Alerts come with a small description and/or recomendation on how to fix a problem

Operating System View
Operating system overview is a detailed real-time snapshot of an OS server showing performance and other critical information for a single OS.

Processes
Processes view shows a list of operating system processes currently running.

Storage View
Operating system storage view shows the drives (mounts) and space usage statistics.

Sessions
Operating system sessions view shows all currently connected operating system users and the details of their connection such as login time, idle time and IP address.

Secure Shell
 Shell view is a special type of view in that it allows you to login to you operating system host using your login and password you entered when adding an Enterprise view item and execute any command that your user account is configured to access.

script to identify inefficient sql

This script reports on the apparent efficiency of SQL statements whose information presently
resides in the shared pool

SELECT   hash_value stmtid,
           SUM (disk_reads) disk_reads,
           SUM (buffer_gets) buffer_gets,
           SUM (rows_processed) rows_processed,
           SUM (buffer_gets) / GREATEST (SUM (rows_processed), 1) rpr,
           SUM (executions) executions,
           SUM (buffer_gets) / GREATEST (SUM (executions), 1) rpe,
           SQL_TEXT
    FROM   v$sql
   WHERE   command_type IN (2, 3, 6, 7)
GROUP BY   hash_value, SQL_TEXT
ORDER BY   5 DESC

How to interpretate wait events to troubleshoot production problems

• Wait events touch all areas of Oracle—from I/O to latches to parallelism to network traffic
• Wait event data can be remarkably detailed. “Waited 0.05 seconds to read 15 blocks from file 15 starting at block 25008.”
• Analyzing wait event data will yield a path toward a solution for almost any problem
• The wait event interface gives you access to a detailed accounting of how Oracle processes spend their time.
• Wait events touch all aspects of the Oracle database server.
• The wait event interface will not always give you the answer to every performance problem, but it will just about always give you insights that guide you down the proper path to problem resolution.