Following Steps to perform. SYSAUX tablespace


SELECT *  FROM dba_jobs WHERE SCHEMA_USER='SYSMAN';

SELECT *  FROM SYSMAN.mgmt_metrics_1hour;

SELECT *  FROM SYSMAN.MGMT_SEVERITY WHERE LOAD_TIMESTAMP<TRUNC(ADD_MONTHS(SYSDATE,-2));

DELETE FROM SYSMAN.MGMT_SEVERITY WHERE LOAD_TIMESTAMP<TRUNC(ADD_MONTHS(SYSDATE,-2));
COMMIT;

SELECT trigger_name FROM dba_triggers WHERE trigger_name LIKE '%SEV%DEL%';

spool c:\del_severity.sql
SELECT 'exec sysman.em_severity.delete_current_severity('''|| target_guid || ''','''|| metric_guid ||''','''|| key_value ||''');' FROM sysman.MGMT_SEVERITY;
spool off
@c:\del_severity.sql
commit;

-------------
EXEC em_severity.delete_current_severity('142E273EE3BDA54ECF9C42EF7CCB7616','6E65075DA52ACA744B4B8C3FCB018289','/billing01');

EXEC em_severity.delete_current_severity('85DE1181E5B760BBB8F70F97FFBD7070','10633143D11A8FCF6CB1A732CEE5352A','Sat Dec 31 20:03:38 2011/115843');
COMMIT;

EXEC em_severity.delete_current_severity('85DE1181E5B760BBB8F70F97FFBD7070','10633143D11A8FCF6CB1A732CEE5352A','Sat Dec 31 20:47:33 2011/134506');

EXEC em_severity.delete_current_severity('85DE1181E5B760BBB8F70F97FFBD7070','3E6F70DB22758B7B9756EF342180E7BB','SYSAUX');

EXEC em_severity.delete_current_severity('85DE1181E5B760BBB8F70F97FFBD7070','F95BA0D95585002889E1ABF92B2DA7C3','ARBORAD');

EXEC em_severity.delete_current_severity('85DE1181E5B760BBB8F70F97FFBD7070','F95BA0D95585002889E1ABF92B2DA7C3','SYSMAN');

COMMIT;
-----------------------------------
SELECT s.target_guid,
           s.metric_guid,
           s.key_value
       FROM mgmt_targets t JOIN mgmt_current_severity s
           ON s.target_guid != t.target_guid
      WHERE LOWER(t.target_name) LIKE '%myhost%';
     

SELECT DBMS_STATS.get_stats_history_retention FROM dual;
EXEC DBMS_STATS.alter_stats_history_retention(15);
EXEC DBMS_STATS.PURGE_STATS(SYSDATE-15);

col Mb FORM 9,999,999
col SEGMENT_NAME FORM a40
col SEGMENT_TYPE FORM a6
SET lines 120
SELECT SUM(bytes/1024/1024)Mb,segment_name,segment_type FROM dba_segments
 WHERE  tablespace_name='SYSAUX'
AND segment_name LIKE 'WRI$_OPTSTAT%'
AND segment_type='TABLE'
GROUP BY segment_name,segment_type ORDER BY 1 ASC;


spool c:\rebuild_index.sql
SELECT 'alter index '||segment_name||' rebuild online parallel (degree 14);' FROM dba_segments WHERE tablespace_name='SYSAUX'
AND segment_name LIKE '%OPT%' AND segment_type='INDEX';
spool off
@c:\rebuild_index.sql


SELECT *  FROM dba_indexes WHERE owner='SYS' AND status!='VALID';

Clear Table records after startup database

If user does not logout properly / kill task / services restarted then record of that user will present in tblrklogindetails table.

We can create trigger that will delete all records if database service will be restarted

create or replace trigger tg_clear_logindetails

    after startup

     on database

  declare

     j   integer;

    pragma autonomous_transaction;

  begin

    dbms_job.submit (j,  'begin execute immediate ''truncate table ldbo.tblrklogindetails''; end;');

   commit;

 end tg_clear_logindetails;

 /

If there is no records in the table then following Error message will popup.

Please change this message to some user friendly message

“Session is Disconnected from a server. Please Login again”

Or something else

In web application , client makes connection with server using IIS manager.

1)      User login into rk application and fire report

2)      Oracle service is restart in between. Oracle Not connected Error has started because connection is break between client and server.

3)      Error is coming until we have not logout and login again into application.

4)      Iisreset help to reconnect client server without logout application.

Resolution

When database is shutdown, application should automatically shutdown when showing oracle not connected .

Tblrklogindetails records should be clear.

Schedule Job for specific time interval

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'Test',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN sp_rkexecutejob(13,'' ''); END;',
    start_date      => '01-APR-12 09.00.00 AM ASIA/CALCUTTA',
    repeat_interval=> 'freq=minutely;bysecond=0;byminute=00,15,30,45;byhour=9,10,11,12,13,14,15,16;byday=MON,TUE,WED,THU,FRI',
    enabled         => TRUE,
    comments        => 'JOB for rk test');
END;
/

The repaet_interval paramter of the scheduler configuration should have a freq value of minutely interval value of 5 and byhourly value of 9-17.

BEGIN

  DBMS_SCHEDULER.create_job (

    job_name        => 'analyze_queue',

    job_type        => 'PLSQL_BLOCK',

    job_action      => 'BEGIN dbms_stats.gather_table_stats(''LDBO'',''TBLRKQUEUE'' ,force=>TRUE); END;',

    start_date      => '01-APR-12 10.00.00 AM ASIA/CALCUTTA',

    repeat_interval=> 'freq=minutely;interval=5;byhour=9,10,11,12,13,14,15,16,17;byday=MON,TUE,WED,THU,FRI',

    enabled         => TRUE,

    comments        => 'JOB to gather Queue Table statistics');

END;

/

IIS Performance Tuning

1)     Recycling Application Pool

Daily they have to restart IIS to clear the application pool.

An application pool is a set of one or more applications assigned to a set of one or more worker processes.

It is useful to recycle periodically because web apps might have a memory leaks or other conditions that get worse over time.

Schedule a recycle application pool once a day on off-peak hours. 

2)     Dedicated Application Pool for different applications

3)     Web Garden on IIS (Max Worker process) Allocate worker process to different CPU core

By default each Application Pool runs with a Single Worker Process (W3Wp.exe). We can assign multiple Worker Processes With a Single Application Pool. An Application Poll with multiple Worker process is called "Web Gardens". Many worker processes with the same Application Pool can sometimes provide better throughput performance and application response time. And each worker process should have their own Thread and Own Memory space.

Application Pool > Max Worker process

 IIS has a Maximum Worker Process setting per App Pool. For my AppPool, I increased this value to 16 on our 8 core (16 w/ hyperthreading) Win2008 Xeon server in hopes of getting more parallel processing of client requests for our IIS hosted WCF Service (net.tcp, .net 4.0). I am not sure that this is having the desired affect as I don't see 16 worker processes in task manager despite a heavy load from my multi-threaded test client.

Web gardens was designed for one single reason – Offering applications that are not CPU-bound but execute long running requests the ability to scale and not use up all threads available in the worker process.

The examples might be things like -

Applications that make long running database requests (e.g. high computational database transaction)

Applications that have threads occupied by long-running synchronous and network intensive transactions

Max Worker Process=10

Max Worker Process=no of CPU

4)     Limit the queue length for application pools

When running an IIS server in worker process isolation mode, limit the queue length for application pools to prevent large numbers of requests from queuing and overloading the IIS web server.
Adding a new request to the queue can result in exceeding the maximum queue length. In turn, the IIS server may reject the requests and send a 503 error to the client. Keep the queue length shorter to boost IIS performance.

5)     IIS HTTP compression

Enable HTTP compression on the Microsoft IIS server to use bandwidth more effectively. This results in faster response times between the IIS web server and compression-enabled browsers.

IIS can provide three compression options, including static files only, dynamic application responses, and both static and dynamic application responses.

Enable dynamic compression for the server when bandwidth is an issue as indicated by IIS server monitoring, but remember that compression consumes CPU time and memory resources significantly.

Sites > Compression>

6)      Use the Expires header in IIS7

Configure the HTTP Expires header to improve IIS server performance. Generally, continuous HTTP requests sent from new website visitors to display pages creates a massive load on the IIS server.

Configuring the Expires header allows caching pages (and page elements such as images, scripts, css, etc.) on the client side and helps avoid unnecessary HTTP requests.

In IIS 7, Expires headers can be enabled in the system.webServer section of the web.config file. Using the Expires header, you can determine how long files can be cached on the client side. This may be a certain number of hours or days in the future or a fixed date.

7)     Windows 2008 Network Settings

http://support.microsoft.com/kb/951037

8)     Change max concurrent requests per CPU setting

Max Concurent Request is calculated by following formula

(MaxWorkerThreads * no Of CPU) – (MinFreeThreads * no Of CPU)

This is 12 by default on a single-proc machine.

So performance does improve by adding more CORE/CPU(hardware) to your system

So If you have Two Processor with below configuration

maxWorkerThreads               100

minFreeThreads                      88

Than MaxConcurent Request would be

(100*2) - (88*2) =24 Concurent Request

If you add two more Core than

Than MaxConcurent Request would be

(100*4) - (88*4) =48 Concurent Request.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ASP.NET\2.0.50727\MaxConcurrentRequestsPerCPU

Set this to a value higher than 12 (12 is the default)

[DWORD]

Change the maxConcurrentRequestsPerCPU setting from 12 to 5000. This allowed IIS to process more requests concurrently.

Improve windows networking

Improve windows networking (especially if hosting webservers)

First of all, I forgive you for having to use windows to host your web server :)

Now that thats out of the way, here's how you speed up networking performance on windows (especially if you happen to host Apache, IBM HTTP Server, or any other web server on a windows machine)

Create a .reg file (eg: perf.reg) with the contents that follow:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]
"MaxUserPort"=dword:00008000
"TcpTimedWaitDelay"=dword:0000001e
"TcpMaxDataRetransmissions"=dword:00000005

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\Interfaces]
"TcpAckFrequency"=dword:00000001
"TcpDelAckTicks"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\AFD\Parameters]
"EnableDynamicBacklog"=dword:00000001
"MinimumDynamicBacklog"=dword:00000020
"MaximumDynamicBacklog"=dword:00001000
"DynamicBacklogGrowthDelta"=dword:00000010
"KeepAliveInterval"=dword:00000001

Double click on the file to merge its contents into the registry. Restart the machine.
You're done.

1)


HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

Value Name: MaxUserPort 

Value Type: REG_DWORD 

Valid Range: 5000-65534 (decimal) Default: 0×1388 (5000 decimal)

New Value: 65534 


Description: This parameter controls the maximum port number
used when an application requests any available user port from the system.
Normally, ephemeral (that is, short-lived) ports are allocated between the
values of 1024 and 5000 inclusive.



2)

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters


Value Name: TcpTimedWaitDelay


Value Type: REG_DWORD 


Valid Range: 30-300 (decimal)


Default: 0xF0 (240 decimal)


New Value: 30

Description: This parameter determines the time that must elapse
before TCP can release a closed connection and reuse its resources. This
interval between closure and release is known as the TIME_WAIT state or 2MSL
state. During this time, the connection can be reopened at much less cost to
the client and server than establishing a new connection.



Normally, TCP does not release closed connections until the
value of this entry expires. However, TCP can release connections before this
value expires if it is running out of TCP control blocks (TCBs). 



A lower TcpTimedWaitDelay setting means that the sockets wait in
the TIME_WAIT state for less time. A higher MaxUserPort setting means that you
can have more sockets in the TIME_WAIT state.

Login Oracle database using Windows User

Create Oracle External users in Windows

1. Create the Windows user:

start --> settings --> control panel --> administrative tools --> computer management --> user

2. Add the new user to the Oracle group:

start --> settings --> control panel --> administrative tools --> computer management --> groups

3. Add user to OS Database Administrator:

start --> programs --> oracle home --> configuration and migration tools --> administration for windows NT --> OS database administrator

4. Add user to OS Database Operators:

start --> programs --> oracle home --> configuration and migration tools --> administration for windows NT --> OS database operators

5. Add user to OS Administrator:

start --> programs --> oracle home --> configuration and migration tools --> administration for windows NT --> OS administrator

6. Add user to OS Operators:

start --> programs --> oracle home --> configuration and migration tools --> administration for windows NT --> OS operators

7. Add os_authent_prefix=OPS$ to your initialization parameters (pfile or spfile) and bounce Oracle database, if necessary.




remote_os_authent FALSE
os_authent_prefix OPS$


SQLNET.AUTHENTICATION_SERVICES in file sqlnet.ora contains NTS

CREATE USER "OPS$WIN-RGWOS2P8G1H\LDUSER" IDENTIFIED EXTERNALLY;

GRANT CONNECT TO "OPS$WIN-RGWOS2P8G1H\LDUSER";


connect to client machine with OS user

sqlplusw /@nbs1112srv

SQL Session Tracing

1. Start a sqlplus session to the database, and enable sql tracing with this command:

alter session set events '10046 trace name context forever, level 12';

The resulting trace file will be put in the USER_DUMP_DEST directory.
To find the location of this directory, as system, do:
SQL>show parameters user_dump_dest

2. Execute your code to reproduce the error/issue.

3. Turn off the trace using this command:

alter session set events '10046 trace name context off';

4. Please upload the trace file and the alert.log file


A 10046 trace creates an output dump file. Therefore, before you enable the 10046 trace event, you will need to set some database parameters that control the output of the dump file. These parameters include:

TIMED_STATISTICS – Must be set to TRUE to get timing information in your trace files.

MAX_DUMP_FILE_SIZE – Controls the maximum size of the trace file. For 10046 trace files, the default setting is generally too small.

USER_DUMP_DEST – This is the location the 10046 trace file(s) are written to.

STATISTICS_LEVEL – This should be set, at a minimum, to TYPICAL. When set to ALL, more information will be collected in the resulting trace files. All examples in this white paper are with STATISTICS_LEVEL set to TYPICAL.

Listener Tracing

We will need to trace a connection on both CLIENT ,SERVER and Listener endpoints to see what is happening.
Please collect traces for the good and the slow connections:

Please follow these steps:

1. Add the following parameters to the listener.ora:

TRACE_LEVEL_<listener_name>=16
TRACE_DIRECTORY_<listener_name>=<some_known_directory> (but not the root directory)
TRACE_FILE_<listener_name>=listener
TRACE_TIMESTAMP_<listener_name>=ON

Replace <listener_name> with the name of your listener, the default name is LISTENER.


2. Reload the listener (execute "lsnrctl reload <listener_name>").

3. Add the following parameters in the sqlnet.ora file on the CLIENT workstation:

TRACE_LEVEL_CLIENT=16
TRACE_DIRECTORY_CLIENT=<some_known_directory> (but not the root directory)
TRACE_FILE_CLIENT=client
TRACE_UNIQUE_CLIENT=ON
TRACE_TIMESTAMP_CLIENT=ON


4. Add the following parameters in the sqlnet.ora file on the SERVER:

TRACE_LEVEL_SERVER=16
TRACE_DIRECTORY_SERVER=<some_known_directory> (but not the root directory)
TRACE_FILE_SERVER=server
TRACE_TIMESTAMP_SERVER=ON


5. Attempt to reproduce the issue (connect from a client workstation) and verify if trace files were created.

>>> Please note that we need to group of traces, one when the connection is fine and one when there a delay.

6. Compress (in .zip or .tar.gz format) and send the trace files. Also please clarify in the name if it the good trace or the bad one.

7. Turn off tracing by setting all TRACE_LEVEL_<listener_name> = OFF and reload the listener.

8. Disable tracing by removing the TRACE lines from sqlnet.ora on the client and server

Database Link Creation Script

set pagesize 0
set linesize 30000
set long 500000
set longchunksize 500000
set trimspool on
set feed off


------------Drop Database link

Select 'DROP '||Decode(U.Name,'PUBLIC','public ')||'database link '||Chr(10)
||Decode(U.Name,'PUBLIC',Null, U.Name||'.')|| L.Name||Chr(10) ||';' TEXT
From Sys.Link$ L, Sys.User$ U
Where L.Owner# = U.User#;


------------Create Database link

SELECT 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||Decode(U.Name,'PUBLIC',Null, U.Name||'.')|| L.Name||Chr(10)
||' connect to ' || L.Userid || ' identified by values '''
||L.PASSWORDX||''' using ''' || L.host || ''''
||chr(10)||';' TEXT
From Sys.Link$ L, Sys.User$ U
WHERE L.OWNER# = U.USER#;


------------------------CHECK LINK

select 'SELECT INSTANCE_NAME,HOST_NAME FROM V$INSTANCE@' ||Db_Link || ';' from dba_db_links;

ORACLE 11G work with IPv6 addresses in addition to IPv4

ORACLE 11G work with IPv6 addresses in addition to IPv4

These are the simple steps that will make your DB 11g instance work with IPV6 environment.

 Configure Linux

To configure Linux with this specific IPv6 address, you modify the /etc/sysconfig/network-scripts/ifcfg-{device name} file.

In my case I modified the ifcfg-eth0 file. I had previously added an IPv6 address and will add a secondary IPv6 address for the IPv4 Mapped Address.

DEVICE=eth0

BOOTPROTO=none

ONBOOT=yes

HWADDR=00:50:43:ac:11:a2

TYPE=Ethernet

USERCTL=no

IPV6INIT=yes

PEERDNS=yes

NETMASK=255.255.0.0

IPADDR=192.168.110.77

GATEWAY=192.168.255.254

IPV6ADDR=2001:DB8::200C:417A/64

IPV6ADDR_SECONDARIES=::ffff:192.168.110.77/64

Configure the Database Listener

The next step is to configure the Listener for this IPv6 address.



LISTENER =

(DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

 (ADDRESS = (PROTOCOL = TCP)(HOST = 2001:db8::200c:417a)(PORT = 1521))

       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.110.77)(PORT = 1521))

      )

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

     )

   )

)



Configuring the Client

The last step is to create an appropriate TNS entry for your application

AVI =

(DESCRIPTION =

   (ADDRESS_LIST =

 (ADDRESS = (PROTOCOL = TCP)(HOST = 2001:db8::200c:417a)(PORT = 1521))

)

   (CONNECT_DATA =

     (SERVER = DEDICATED)

     (SERVICE_NAME = DB11g)

   )

)



AVI2 =

(DESCRIPTION =

   (ADDRESS_LIST =

  (ADDRESS =  (PROTOCOL = TCP)(HOST =192.168.110.77)(PORT = 1521))

)

   (CONNECT_DATA =

     (SERVER = DEDICATED)

     (SERVICE_NAME = DB11g)

   )

)

Test the Connection

use the utility TNSPING

database-server :/u/app/oracle/v11.2.0/network/admin > tnsping AVI


database-server :/u/app/oracle/v11.2.0/network/admin > tnsping AVI2


Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.110.77)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB11g)))

OK (10 msec)

database-server :/u/app/oracle/v11.2.0/network/admin

in 11g UTL_INADDR.GET_HOST_ADDRESS gives me IPv6, while I need IPv4

in 11g UTL_INADDR.GET_HOST_ADDRESS gives me IPv6, while I need IPv4

SELECT  UTL_INADDR.GET_HOST_ADDRESS  FROM DUAL;


disable ipv6 on OS level

c:\Windows\System32\drivers\etc\hosts

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

ORA_CLIENT_IP_ADDRESS Returns the IP address of the client in a LOGON event, when the underlying protocol is TCP/IP

select sys_context('userenv','ip_address') from dual;

ORA-12839: cannot modify an object in parallel after modifying it

ORA-12839: cannot modify an object in parallel after modifying it


Affects:

Product (Component)  Oracle Server (Rdbms)
Range of versions believed to be affected  Versions >= 11.1 but BELOW 12.1
Versions confirmed as being affected
11.2.0.2
11.1.0.7
Platforms affected  Generic (all / most platforms affected)
Fixed:

This issue is fixed in
12.1 (Future Release)
11.2.0.3
Symptoms:

Related To:

Error May Occur
Internal Error May Occur (ORA-600)
ORA-12839
ORA-600 [qerltcFetch_20]
Constraint Related
Parallel Query (PQO)
_disable_parallel_conventional_load
DML
Description

If you have a parent table and child table with a parent referential constraint
then running Parallel DML again this may fail with an unexpected ORA-12839
or even fail with an ORA-600.

Workaround
 Setting "_disable_parallel_conventional_load" = true can help avoid this.

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.
References

Bug:9831227 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article

Resolving common Oracle Wait Events

Resolving common Oracle Wait Events

Wait Event	Possible Causes	Actions	Remarks
db file sequential reads	Use of an unselective index Fragmented Indexes High I/O on a particular disk or mount point Bad application design Index reads performance can be affected by  slow I/O subsystem and/or poor database files layout, which result in a higher average  wait time	Check indexes on the table to ensure that the right index is being used Check the column order of the index with the WHERE clause of the Top SQL statements Rebuild indexes with a high clustering factor Use partitioning to reduce the amount of blocks being visited Make sure optimizer statistics are up to date Relocate ‘hot’ datafiles Consider the usage of multiple buffer pools and cache frequently used indexes/tables in the KEEP pool Inspect the execution plans of the SQL statements that access data through indexes Is it appropriate for the SQL statements to access data through index lookups? Is the application an online transaction  processing (OLTP) or decision support system (DSS)? Would full table scans be more efficient? Do the statements use the right driving  table? The optimization goal is to minimize  both the number of logical and physical I/Os.	The Oracle process wants a block that is currently not in the SGA, and it is waiting for the database block to be read into the SGA from disk. Significant db file sequential read wait time is most likely an application issue. If the DBA_INDEXES.CLUSTERING_FACTOR of the index approaches the number of blocks in the table, then most of the rows in the table are ordered. This is desirable.  However, if the clustering factor approaches the number of rows in the table, it means the rows in the table are randomly ordered and thus it requires more I/Os to complete the operation. You can improve the index’s clustering factor by rebuilding the table so that rows are ordered according to the index key and rebuilding the index thereafter. The OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING initialization parameters can influence the optimizer to favour the nested loops operation and choose an index access path over a full table scan. Tuning I/O related waits Note# 223117.1 db file sequential read Reference Note# 34559.1
db file scattered reads	The Oracle session has requested and is waiting for multiple contiguous database blocks (up to DB_FILE_MULTIBLOCK_READ_COUNT) to be  read into the SGA from disk. Full Table scans Fast Full Index Scans	Optimize multi-block I/O by setting the parameter DB_FILE_MULTIBLOCK_READ_COUNT Partition pruning to reduce number of blocks visited Consider the usage of multiple buffer pools and cache frequently used indexes/tables in the KEEP pool Optimize the SQL statement that initiated most of the waits. The goal is to minimize the number of physical and logical reads. Should the statement access the data by a full table scan or index FFS? Would an index range or unique scan  be more efficient? Does the query use the right driving table? Are the SQL predicates appropriate for hash or merge join?  If full scans are appropriate, can parallel query improve the response time? The objective is to reduce the demands for both the logical and physical I/Os, and this is best achieved through SQL and application tuning. Make sure all statistics are representative of the actual data. Check the LAST_ANALYZED date	If an application that has been running fine for a while suddenly clocks a lot of time on the db file scattered read event and there hasn’t been a code change, you might want to check to see if one or more indexes has been dropped or become unusable. db file scattered read Reference Note# 34558.1
log file parallel write	LGWR waits while writing contents of the redo log buffer cache to the online log files on disk I/O wait on sub system holding the online  redo log files	Reduce the amount of redo being generated Do not leave tablespaces in hot backup mode for longer than necessary Do not use RAID 5 for redo log files Use faster disks for redo log files Ensure that the disks holding the archived redo log files and the online redo log files are separate so as to avoid contention Consider using NOLOGGING or UNRECOVERABLE options in SQL statements	Reference Note# 34583.1
log file sync	Oracle foreground processes are waiting for a COMMIT or ROLLBACK to complete	Tune LGWR to get good throughput to  disk eg: Do not put redo logs on RAID5 Reduce overall number of commits by batching transactions so that there are fewer distinct COMMIT operations	Reference Note# 34592.1 High Waits on log file sync Note# 125269.1 Tuning the Redolog Buffer Cache and Resolving Redo Latch Contention Note# 147471.1
buffer busy waits	Buffer busy waits are common in an I/O- bound Oracle system. The two main cases where this can occur are: Another session is reading the block into the buffer Another session holds the buffer in an incompatible mode to our request These waits indicate read/read, read/write,  or write/write contention. 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. This wait can be intensified by a large block  size as more rows can be contained within the block This wait happens when a session wants to access a database block in the buffer cache but it cannot as the buffer is "busy It is also often due to several processes repeatedly reading the same blocks (eg: if lots of people scan the same index or data  block)	The main way to reduce buffer busy waits is to reduce the total I/O on the system Depending on the block type, the actions will differ Data Blocks Eliminate HOT blocks from the application. Check for repeatedly scanned / unselective indexes. Try rebuilding the object with a higher PCTFREE so that you reduce the number of rows per block.  Check for 'right- hand-indexes' (indexes that get inserted into at the same point by many processes).  Increase INITRANS and MAXTRANS and reduce PCTUSED This will make the table less dense . Reduce the number of rows per block Segment Header Increase of number of FREELISTs   and FREELIST GROUPs Undo Header Increase the number of Rollback Segments	A process that waits on the buffer busy waits event publishes the reason code in the P3 parameter of the wait event. The Oracle Metalink note # 34405.1 provides a table of reference - codes 130 and 220 are the most common. Resolving intense and random buffer busy wait performance problems. Note# 155971.1
free buffer waits	This means we are waiting for a free buffer but there are none available in the cache because there are too many dirty buffers in  the cache Either the buffer cache is too small or the DBWR is slow in writing modified buffers to disk DBWR is unable to keep up to the write requests Checkpoints happening too fast – maybe due  to high database activity and under-sized  online redo log files Large sorts and full table scans are filling the  cache with modified blocks faster than the  DBWR is able to write to disk If the  number of dirty buffers that need to be  written to disk is larger than the number that DBWR can write per batch, then these waits  can be observed	Reduce checkpoint frequency  - increase the size of the online redo log files Examine the size of the buffer cache – consider increasing the size of the buffer cache in the SGA Set disk_asynch_io = true set   If not using asynchronous I/O increase the number of db writer processes or dbwr slaves   Ensure hot spots do not exist by spreading datafiles over disks and disk controllers Pre-sorting or reorganizing data can help	Understanding and Tuning Buffer Cache and DBWR Note#62172.1 How to Identify a Hot Block within the database Buffer Cache. Note# 163424.1
enqueue waits	This wait event indicates a wait for a lock that is held by another session (or sessions) in an incompatible mode to the requested mode. TX Transaction Lock Generally due to table or application set up issues This indicates contention for row-level lock.  This wait occurs when a transaction tries to update or delete rows that are currently  locked by another transaction. This usually is an application issue. TM DML enqueue lock Generally due to application issues, particularly if foreign key constraints have not been indexed.   ST lock   Database actions that modify the UET$ (used extent) and FET$ (free extent) tables require the ST lock, which includes actions such as drop, truncate, and coalesce.   Contention for the ST lock indicates there are multiple sessions actively performing dynamic disk space allocation or deallocation in dictionary managed tablespaces	Reduce waits and wait times The action to take depends on the lock  type which is causing the most problems Whenever you see an enqueue wait event for the TX enqueue, the first step is to find out who the blocker is and if there are multiple waiters for the same resource Waits for TM enqueue in Mode 3 are primarily due to unindexed foreign key columns. Create indexes on foreign keys  < 10g Following are some of the things you can do to minimize ST lock contention in your database:   Use locally managed tablespaces Recreate all temporary tablespaces using the CREATE TEMPORARY TABLESPACE TEMPFILE… command.	Maximum number of enqueue resources that can be concurrently locked is controlled by the ENQUEUE_RESOURCES parameter. Reference Note# 34566.1 Tracing sessions waiting on an enqueue Note# 102925.1 Details of V$LOCK view and lock modes Note:29787.1
Cache buffer chain latch	This latch is acquired when searching for data blocks Buffer cache is a chain of blocks and each chain is protected by a child latch when it needs to be scanned Hot blocks are another common cause of cache buffers chains latch contention. This happens when multiple sessions repeatedly access  one or more blocks that are protected by the same child cache buffers chains latch.  SQL statements with high BUFFER_GETS (logical reads) per EXECUTIONS are the main culprits Multiple concurrent sessions are executing the same inefficient SQL that is going after the same data set	Reducing contention for the cache buffer chains latch will usually require reducing logical I/O rates by tuning and minimizing the I/O requirements of  the SQL involved. High I/O rates could be a sign of a hot block (meaning a block highly accessed).   Exporting the table, increasing the PCTFREE significantly, and importing the data. This minimizes the number of  rows per block, spreading them over many blocks. Of course, this is at the expense of storage and full table scans operations will be slower Minimizing the number of records per block in the table For indexes, you can rebuild them with higher PCTFREE values, bearing in mind that this may increase the height of the index. Consider reducing the block size  Starting in Oracle9i Database, Oracle supports multiple block sizes. If the current block size is 16K, you may move the table or recreate the index in a tablespace with an 8K block size. This too will negatively impact full table scans operations. Also, various  block sizes increase management complexity.	The default number of hash latches is usually 1024 The number of hash latches can be adjusted by the parameter _DB_BLOCKS_HASH_LATCHES What are latches and what causes latch contention
Cache buffer LRU chain latch	Processes need to get this latch when they need to move buffers based on the LRU block replacement policy in the buffer cache The cache buffer lru chain latch is acquired in order to introduce a new block into the buffer cache and when writing a buffer back to disk, specifically when trying  to scan the LRU (least recently used) chain containing all the dirty blocks in the buffer cache. Competition for the cache buffers lru chain latch is symptomatic of intense buffer cache  activity caused by inefficient SQL statements. Statements that repeatedly scan  large unselective indexes or perform full table scans are the prime culprits.  Heavy contention for this latch is generally due to heavy buffer cache activity which can be caused, for example, by:  Repeatedly scanning large unselective indexes	Contention in this latch can be avoided implementing multiple buffer pools or increasing the number of LRU latches with the  parameter DB_BLOCK_LRU_LATCHES (The default value is generally  sufficient for most systems). Its possible to reduce contention for the cache buffer lru chain latch by increasing the size of the buffer cache and thereby reducing the rate at which new blocks are introduced into the buffer cache
Direct Path Reads	These waits are associated with direct read operations which read data directly into the sessions PGA bypassing the SGA The "direct path read" and "direct path write" wait events are related to operations that are performed in PGA like sorting, group by operation, hash join In DSS type systems, or during heavy batch periods, waits on "direct path read" are quite normal However, for an OLTP system these waits are significant These wait events can occur during sorting operations which is not surprising as direct path reads and writes usually occur in connection with temporary tsegments SQL statements with functions that require sorts, such as ORDER BY, GROUP BY, UNION, DISTINCT, and ROLLUP, write sort runs to the temporary tablespace when the input size is larger than the work area in the PGA	Ensure the OS asynchronous IO is configured correctly. Check for IO heavy sessions / SQL and see if the amount of IO can be reduced. Ensure no disks are IO bound. Set your PGA_AGGREGATE_TARGET to appropriate value (if the parameter WORKAREA_SIZE_POLICY = AUTO) Or set *_area_size manually (like sort_area_size and then you have to set WORKAREA_SIZE_POLICY = MANUAL Whenever possible use UNION ALL instead of UNION, and where applicable use HASH JOIN instead of SORT MERGE and NESTED LOOPS instead of HASH JOIN.  Make sure the optimizer selects the right driving table. Check to see if the composite index’s columns can be rearranged to match the ORDER BY clause to avoid sort entirely. Also, consider automating the SQL work areas using PGA_AGGREGATE_TARGET in Oracle9i Database. Query V$SESSTAT> to identify sessions with high "physical reads direct"	Default size of HASH_AREA_SIZE  is twice that of SORT_AREA_SIZE Larger HASH_AREA_SIZE will influence optimizer to go for hash joins instead of nested loops Hidden parameter DB_FILE_DIRECT_IO_COUNT can impact the direct path read performance.It sets the maximum I/O buffer size of direct read and write operations. Default is 1M in 9i How to identify resource intensive SQL statements?
Direct Path  Writes	These are waits that are associated with direct write operations that write data from users’ PGAs to data files or temporary tablespaces Direct load operations (eg: Create Table as  Select (CTAS) may use this) Parallel DML operations Sort IO (when a sort does not fit in memory	If the file indicates a temporary tablespace check for unexpected disk sort operations. Ensure <Parameter:DISK_ASYNCH_IO> is TRUE . This is unlikely to reduce wait times from the wait event timings but may reduce sessions elapsed times (as synchronous direct IO is not accounted for in wait event timings). Ensure the OS asynchronous IO is configured correctly. Ensure no disks are IO bound
Latch Free Waits	This wait indicates that the process is waiting for a latch that is currently busy (held by another process). When you see a latch free wait event in the V$SESSION_WAIT view, it means the process failed to obtain the latch in the willing-to-wait mode after spinning _SPIN_COUNT times and went to sleep. When processes compete heavily for latches, they will also consume more CPU resources because of spinning. The result is a higher response time	If the TIME spent waiting for latches is significant then it is best to determine which latches are suffering from contention.	A latch is a kind of low level lock. Latches apply only to memory structures in the SGA. They do not apply to database objects. An Oracle SGA has many latches, and they exist to protect various memory structures from potential corruption  by concurrent access. The time spent on latch waits is an effect, not a cause; the cause is that you are doing too many block gets, and block gets require cache buffer chain latching What are Latches and what causes Latch contention Database Lock and Latch Information Knowledge Browser Product Page
Library cache latch	The library cache latches protect the cached SQL statements and objects definitions held in the library cache within the shared pool. The library cache latch must be acquired in order to add a new statement to the library cache Application is making heavy use of literal SQL- use of bind variables will reduce this latch considerably	Latch is to ensure that the application is reusing as much as possible SQL statement representation. Use bind variables whenever possible in the application You can reduce the library cache latch hold time by properly setting the SESSION_CACHED_CURSORS parameter Consider increasing shared pool	Larger shared pools tend to have long free lists and processes that need to allocate space in them must  spend extra time scanning the long free lists while holding the shared pool latch if your database is not yet on Oracle9i Database, an oversized shared pool can increase the contention for the shared pool latch.
Shared pool latch	The shared pool latch is used to protect critical operations when allocating and freeing memory in the shared pool Contentions for the shared pool and library cache latches are mainly due to intense hard  parsing. A hard parse applies to new cursors and cursors that are aged out and must be re-executed 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.	Ways to reduce the shared pool latch are, avoid hard parses when possible, parse once, execute many. Eliminating 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. The workaround is to set the initialization parameter CURSOR_SHARING to FORCE. This allows statements that differ in literal  values but are otherwise identical to share a cursor and therefore reduce latch contention, memory usage, and  hard parse.	<Note 62143.1> explains how to identify and correct problems with the shared pool, and shared pool latch.
Row cache objects latch	This latch comes into play when user processes are attempting to  access the cached data dictionary values.	It is not common to have contention in this latch and the only way to reduce contention for this latch is by increasing the size of the shared pool (SHARED_POOL_SIZE). Use Locally Managed tablespaces for your application objects especially indexes Review and amend your database logical design , a good example is to merge or decrease the number of indexes on tables with heavy inserts	Configuring the library cache to an acceptable size usually ensures that the data  dictionary cache is also properly sized. So tuning Library Cache will tune Row Cache indirectly