SQL ordered by Physical Reads (UnOptimized) or (Optimized) ??

Interestingly from 11.2 onwards there is "SQL ordered by Physical Reads (UnOptimized)" Section in AWR Reports and that is for Smart Flash Cache Database, also note Optimized Read Requests are read requests that are satisfied from the Smart Flash Cache ( or the Smart Flash Cache in OracleExadata V2). A very important point is that the concept and use of  'Smart Flash Cache' in Exadata V2 is different from  'Smart Flash Cache' in Database Smart Flash Cache. Also see that the 'Physical Read Reqs' column in the 'SQL ordered by Physical Reads (UnOptimized)' section is the number of I/O requests and not the number of blocks returned. Be careful not to confuse these with the Physical Reads statistics from the AWR section 'SQL ordered by Reads', which counts database blocks read from the disk not actual I/Os (a single I/O operation  may return many blocks from disk).

In this context Optimized reads will be the one which will be served by the smart flash cache

LNS wait on SENDREQ Wait Event

LNS wait on SENDREQ means that redo data has not been written to all ASYNC and SYNC redo transport destinations and that the processes on primary databases are waiting. This is basically a network related wait event which means that tuning the network may be beneficial to remove this wait event. The LNS wait on SENDREQ can happen with either the SYNC or ASYNC LGWR attributes.  When using ASYNC transport mode in Oracle 10g r2 and beyond, Oracle recommends allowing for sufficient I/O bandwidth for LNS read I/Os to the online redo logs of the production database.

As per Oracle Doc LNS wait on SENDREQ means :- Total time spent waiting for redo data to be written to all ASYNC and SYNC redo transport destinations.

There can be multiple ways to tune it :-

1) The very first way to tune is to optimize the network, You can set SDU (SESSION DATA UNIT) parameter within an Oracle Net connect descriptor or globally within the sqlnet.ora. For Data Guard broker configurations configure the DEFAULT_SDU_SIZE parameter in the sqlnet.ora file:

DEFAULT_SDU_SIZE=32767

2) Tuning tNS layer for the tcp.nodelay parameter in the protocol.ora file

3) Get a faster network transport such as dark fibre.

4) Change the size of the online redo logs to make the archived redo log smaller, thereby creating smaller archived redo logs.  This will create more frequent, smaller redo log transports that will complete faster.

Monitoring Synchronous Redo Transport Response Time

The V$REDO_DEST_RESP_HISTOGRAM view contains response time data for each redo transport destination. This response time data is maintained for redo transport messages sent via the synchronous redo transport mode.

The data for each destination consists of a series of rows, with one row for each response time. To simplify record keeping, response times are rounded up to the nearest whole second for response times less than 300 seconds. Response times greater than 300 seconds are round up to 600, 1200, 2400, 4800, or 9600 seconds.

Each row contains four columns: FREQUENCY, DURATION, DEST_ID, and TIME.

The FREQUENCY column contains the number of times that a given response time has been observed. The DURATION column corresponds to the response time. The DEST_ID column identifies the destination. The TIME column contains a timestamp taken when the row was last updated.

The response time data in this view is useful for identifying synchronous redo transport mode performance issues that can affect transaction throughput on a redo source database. It is also useful for tuning the NET_TIMEOUT attribute.

Wait Event "resmgr:cpu quantum"

Wait Event "resmgr:cpu quantum"

The wait event resmgr:cpu quantum is a normal wait event used by the Oracle Resource Manager to control CPU distribution. The resmgr:cpu quantum only occurs when the resource manager is enabled and the resource manage is "throttling" CPU consumption.

You can also detect an overloaded CPU when you see the “resmgr:cpu quantum” event in a top-5 timed event on a AWR or STATSPACK report.

The "resmgr:cpu quantum" only applies when the Oracle resource manager is deployed, and there are other ways to detect an overloaded CPU:

1. - UNIX/Linux - vmstat when the runqueue column (r) exceed the cpu_count for the database.

2. - Windows:  When the processor queue length is greater than zero.

If there 100% CPU Utilization then its important to check Processor Queue Length in the system monitor and task manager. In Windows, it's the "Processor Queue Length", and it's displayed in the system monitor and task manager.

Microsoft notes:

"Processor Queue Length (System) This is the instantaneous length of the processor queue in units of threads. All processors use a single queue in which threads wait for processor cycles.

After a processor is available for a thread waiting in the processor queue, the thread can be switched onto a processor for execution. A processor can execute only a single thread at a time. Note that faster CPUs can handle longer queue lengths than slower CPUs."

"The number of threads in the processor queue. Shows ready threads only, not threads that are running. Even multiprocessor computers have a single queue for processor time; thus, for multiprocessors, you need to divide this value by the number of processors servicing the workload. A sustained processor queue of less than two threads per processor is normally acceptable, depending upon the workload."