How to make status column to False in DBA_SCHEDULER_WINDOWS

++ To disable status column to FALSE from DBA_SCHEDULER_WINDOWS

exec DBMS_SCHEDULER.DISABLE ( '"MONDAY_WINDOW"');
exec DBMS_SCHEDULER.DISABLE ( '"TUESDAY_WINDOW"');
exec DBMS_SCHEDULER.DISABLE ( '"WEDNESDAY_WINDOW"');
exec DBMS_SCHEDULER.DISABLE ( '"THURSDAY_WINDOW"');
exec DBMS_SCHEDULER.DISABLE ( '"FRIDAY_WINDOW"');
exec DBMS_SCHEDULER.DISABLE ( '"SATURDAY_WINDOW"');
exec DBMS_SCHEDULER.DISABLE ( '"SUNDAY_WINDOW"');

++ To disable active column to FALSE from DBA_SCHEDULER_WINDOWS

exec DBMS_SCHEDULER.CLOSE_WINDOW ('"MONDAY_WINDOW"');
exec DBMS_SCHEDULER.CLOSE_WINDOW ('"TUESDAY_WINDOW"');
exec DBMS_SCHEDULER.CLOSE_WINDOW ('"WEDNESDAY_WINDOW"');
exec DBMS_SCHEDULER.CLOSE_WINDOW ('"THURSDAY_WINDOW"');
exec DBMS_SCHEDULER.CLOSE_WINDOW ('"FRIDAY_WINDOW"');
exec DBMS_SCHEDULER.CLOSE_WINDOW ('"SATURDAY_WINDOW"');
exec DBMS_SCHEDULER.CLOSE_WINDOW ('"SUNDAY_WINDOW"');

SQL> select WINDOW_NAME,ENABLED,ACTIVE,RESOURCE_PLAN,NEXT_START_DATE, DURATION from DBA_SCHEDULER_WINDOWS;

WINDOW_NAME          ENABL ACTIV RESOURCE_PLAN                  NEXT_START_DATE                               DURATION       
-------------------- ----- ----- ------------------------------ --------------------------------------------- ---------------
MONDAY_WINDOW        FALSE FALSE                                20-JUL-15 01.00.00 AM AFRICA/JOHANNESBURG     +000 06:00:00
TUESDAY_WINDOW       FALSE FALSE                                21-JUL-15 04.00.00 AM AFRICA/JOHANNESBURG     +000 03:00:00
WEDNESDAY_WINDOW     FALSE FALSE                                22-JUL-15 04.00.00 AM AFRICA/JOHANNESBURG     +000 03:00:00
THURSDAY_WINDOW      FALSE FALSE                                23-JUL-15 04.00.00 AM AFRICA/JOHANNESBURG     +000 03:00:00
FRIDAY_WINDOW        FALSE FALSE                                24-JUL-15 04.00.00 AM AFRICA/JOHANNESBURG     +000 03:00:00
SATURDAY_WINDOW      FALSE FALSE                                18-JUL-15 04.00.00 AM AFRICA/JOHANNESBURG     +000 03:00:00
SUNDAY_WINDOW        FALSE FALSE                                19-JUL-15 02.00.00 AM AFRICA/JOHANNESBURG                    

How to disable DEFAULT_MAINTENANCE_PLAN from DBA_SCHEDULER_WINDOWS

select WINDOW_NAME,WINDOW_PRIORITY,ENABLED,RESOURCE_PLAN,NEXT_START_DATE, DURATION from DBA_SCHEDULER_WINDOWS

Output:
WINDOW_NAME        WIND ENABL RESOURCE_PLAN                  NEXT_START_DATE                              DURATION
------------------ ---- ----- ------------------------------ -------------------------------------------- --------------------
MONDAY_WINDOW      LOW  TRUE                                 20-JUL-15 01.00.00 AM AFRICA/JOHANNESBURG    +000 06:00:00
TUESDAY_WINDOW     LOW  TRUE  DEFAULT_MAINTENANCE_PLAN       21-JUL-15 04.00.00 AM AFRICA/JOHANNESBURG    +000 03:00:00
WEDNESDAY_WINDOW   LOW  TRUE  DEFAULT_MAINTENANCE_PLAN       22-JUL-15 04.00.00 AM AFRICA/JOHANNESBURG    +000 03:00:00
THURSDAY_WINDOW    LOW  TRUE  DEFAULT_MAINTENANCE_PLAN       23-JUL-15 04.00.00 AM AFRICA/JOHANNESBURG    +000 03:00:00
FRIDAY_WINDOW      LOW  TRUE  DEFAULT_MAINTENANCE_PLAN       24-JUL-15 04.00.00 AM AFRICA/JOHANNESBURG    +000 03:00:00
SATURDAY_WINDOW    LOW  TRUE  DEFAULT_MAINTENANCE_PLAN       18-JUL-15 04.00.00 AM AFRICA/JOHANNESBURG    +000 03:00:00
SUNDAY_WINDOW      LOW  TRUE  DEFAULT_MAINTENANCE_PLAN       19-JUL-15 02.00.00 AM AFRICA/JOHANNESBURG    +000 06:00:00


Now to disable DEFAULT_MAINTENANCE_PLAN from DBA_SCHEDULER_WINDOWS

SQL> alter system set resource_manager_plan='' scope=both

SQL>execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN',''); and 
SQL>execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');

SQL>execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN',''); 
SQL>execute dbms_scheduler.set_attribute('SUNDAY_WINDOW','RESOURCE_PLAN','');
SQL>execute dbms_scheduler.set_attribute('MONDAY_WINDOW','RESOURCE_PLAN',''); 
SQL>execute dbms_scheduler.set_attribute('TUESDAY_WINDOW','RESOURCE_PLAN','');
SQL>execute dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','RESOURCE_PLAN',''); 
SQL>execute dbms_scheduler.set_attribute('THURSDAY_WINDOW','RESOURCE_PLAN','');
SQL>execute dbms_scheduler.set_attribute('FRIDAY_WINDOW','RESOURCE_PLAN','');

SQL> select WINDOW_NAME,WINDOW_PRIORITY,ENABLED,RESOURCE_PLAN,NEXT_START_DATE, DURATION from DBA_SCHEDULER_WINDOWS;

WINDOW_NAME          WIND ENABL RESOURCE_PLAN NEXT_START_DATE                            DURATION       
-------------------- ---- ----- -------------------------------------------------------- ---------------
MONDAY_WINDOW        LOW  TRUE                20-JUL-15 01.00.00 AM AFRICA/JOHANNESBURG  +000 06:00:00
TUESDAY_WINDOW       LOW  TRUE                21-JUL-15 04.00.00 AM AFRICA/JOHANNESBURG  +000 03:00:00
WEDNESDAY_WINDOW     LOW  TRUE                22-JUL-15 04.00.00 AM AFRICA/JOHANNESBURG  +000 03:00:00
THURSDAY_WINDOW      LOW  TRUE                23-JUL-15 04.00.00 AM AFRICA/JOHANNESBURG  +000 03:00:00
FRIDAY_WINDOW        LOW  TRUE                24-JUL-15 04.00.00 AM AFRICA/JOHANNESBURG  +000 03:00:00
SATURDAY_WINDOW      LOW  TRUE                18-JUL-15 04.00.00 AM AFRICA/JOHANNESBURG  +000 03:00:00

SUNDAY_WINDOW        LOW  TRUE                19-JUL-15 02.00.00 AM AFRICA/JOHANNESBURG  +000 06:00:00
Ref: 
Resource Manager and Sql Tuning Advisory DEFAULT_MAINTENANCE_PLAN (Doc ID 786346.1)

Now To make status column to False in DBA_SCHEDULER_WINDOWS 

Follow the below link
<< http://oracletechdba.blogspot.com/2015/07/how-to-make-status-column-to-false-in.html >>

Oracle database I/O performance issue

We have night batch process slowly taking time and when we check the AWR report we see most of the time DB File Sequential read and when checking the query its going for index scan that directs me to check the I/O performance of the system. 

I am glancing how i went throught AWR.

check the Avg wait(ms) for "db file sequential read" in Top 5 Timed Foreground Events" session and there Avg wait is 27,Then further go through "Wait Event Histogram"

In below "Wait Event Histogram" you can see the % of waits are more b/w 32ms and 1s buckets for above top wait events and further walk through the AWR on tablespace I/O.                   

You can see the Av Rd(ms) and Av Buf Wt(ms) is more.

From this it look we have I/O issue making slowness of the database.

and when we check the OS level for the disk it gives 100% disk busy.

Further to fix the issue we have to check the for datafile I/O session in AWR and if possible move the datafile to another disk where there is low i/o.

As well as we have to tune the top I/O related queries.

Consider increasing DB buffer cache.

Reference: How to Tell if the I/O of the Database is Slow (Doc ID 1275596.1)

Please go thought the metalink note for more details.

optimizer_skip_scan_enabled performance issue and wrong stats in explain plan

Index skip scan performance issue and wrong stats during explain plan.

When you execute the below query it takes days to get the result and the plan show index skip scan with approximately 1hr estimated output but real output takes days ☹,So we disables the index skip scan and can see the result in ~15 in this case

SELECT AC_BRANCH, COUNT(*)

FROM ACTB_HISTORY

WHERE MODULE IN ('RT','UP')

AND CUST_GL = 'A'

AND trn_dt between '01-jan-2014' and '20-oct-2014'

AND USER_ID <> ‘’

AND AMOUNT_TAG = 'TXN'

GROUP BY AC_BRANCH

/

Disable the index scan and execute the plan.

SQL> alter session set "_optimizer_skip_scan_enabled"=FALSE;

SQL> EXECUTE THE QUERY

you CAN get result in 10mins but still the plan shows wrong status in time but the cost is reduced.

Wait Event: Row cache objects latch

Wait Event: Row cache objects latch

Possible Causes:

• This latch comes into play when user processes are attempting to  access the cached data dictionary values.

Actions:

• 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 especiallyindexes
• Review and amend your database logical design , a good example is to merge or decrease the number of indexes on tables with heavy inserts

Ref:

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

WAITEVENT: "latch: row cache objects" Reference Note (Doc ID 1550722.1)
gavinsoorma.com

Wait Event: Shared pool latch

Wait Event: Shared pool latch

Possible Causes:

• 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.

Actions:

• 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, andhard parse.

Ref:
<Note 62143.1> explains how to identify and correct problems with the shared pool, and shared pool latch.

gavinsoorma.com

Wait Event: Library cache latch

Wait Event: Library cache latch

Possible Causes:

• 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

Actions:

• 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

Ref:

• 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
• An oversized shared pool can increase the contention for the shared pool latch.

FAQ: What are Latches and What Causes Latch Contention (Doc ID 22908.1)
Database Lock and Latch Information Knowledge Browser Product Page (Doc ID 267887.1)
gavinsoorma.com

Wait Event: Latch Free Waits

Wait Event: Latch Free Waits

Possible Causes:

• 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 theV$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

Actions:

• If the TIME spent waiting for latches is significant then it is best to determine which latches are suffering from contention.

Ref:

• 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 corruptionby 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

FAQ: What are Latches and What Causes Latch Contention (Doc ID 22908.1)
Database Lock and Latch Information Knowledge Browser Product Page (Doc ID 267887.1)
gavinsoorma.com

Wait Event: Direct Path Writes

Wait Event: Direct Path  Writes

Possible Causes:

• 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

Actions:

• 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

Ref:
WAITEVENT: "direct path write" Reference Note (Doc ID 50416.1)
gavinsoorma.com

Wait Event: Direct Path Reads

Wait Event: Direct Path Reads

Possible Causes:

• 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

Actions:

• 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"

Ref:
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 ("TOP SQL") (Doc ID 232443.1)

gavinsoorma.com

Wait Event: Cache buffer LRU chain latch

Wait Event: Cache buffer LRU chain latch

Possible Causes:

• Processes need to get this latch when they need to move buffers based on the LRU block replacement policy in the buffer cacheThe 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 cacheactivity 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

Actions:

• 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 generallysufficient for most systems).
• Its possible to reduce contention for the cache bufferlru 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

Ref:
WAITEVENT: "latch free" Reference Note (Doc ID 34576.1)
gavinsoorma.com

Wait Event: Cache buffer chain latch

Wait Event: Cache buffer chain latch

Possible Causes:

• 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

Actions:

• 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, variousblock sizes increase management complexity.

Ref:
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 - 22908.1

gavinsoorma.com

Wait Event: enqueue waits

Wait Event: enqueue waits

Possible Causes:

• 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 currentlylocked 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$ (usedextent) 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

Actions:

• 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 tablespacesRecreate all temporary tablespaces using the CREATE TEMPORARY TABLESPACE TEMPFILE… command.

Ref:
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

gavinsoorma.com

Wait Event: free buffer waits

Wait Event: free buffer waits

Possible Causes:

• 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

Actions:

• 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

Ref:

Understanding and Tuning Buffer Cache and DBWR Note# 62172.1
How to Identify a Hot Block within the database Buffer Cache. Note# 163424.1
gavinsoorma.com

Wait Event: buffer busy waits

Wait Event: buffer busy waits

Possible Causes:

• Buffer busy waits are common in an I/O-bound Oracle system.
• The two main cases where this can occurare:
• 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)

Actions:

• 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

Ref:
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

Wait Event: log file sync

Wait Event: log file sync

Possible Causes:

• Oracle foreground processes are waiting for a COMMIT or ROLLBACK to complete

Actions

• Tune LGWR to get good throughput todisk eg: Do not put redo logs on RAID5
• Reduce overall number of commits by batching transactions so that there are fewer distinct COMMIT operations

Ref:
Reference Note# 34583.1

Wait Event: log file parallel write

Wait Event: log file parallel write

Possible Causes:

• 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

Actions

• 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

Ref:
Reference Note# 34583.1

Wait Event: db file scattered reads

Wait Event: db file scattered reads
Possible Causes:

• 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

Actions:

• 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

Remarks:

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.
Reg:
db file scattered read Reference Note# 34558.1
gavinsoorma.com

Wait Event:db file sequential reads

Wait Event:db file sequential reads

Possible Causes:

• 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

Actions:

• Check indexes on the table to ensure that the right index is being used

SQLPLUS hangs from client server

sqlplus is hanging from client server

vmstat output:

Free memory in the server:

Client Server was up and running more than one year.

Solution:

Restarted the server, when I try sqlplus it was working, Now we can start the application server to connect to the database and start the application.

$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 1 09:44:01 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: