Thursday 28 January 2016

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

Database Checkpoints (SQL Server)

For performance reasons, the Database Engine performs modifications to database pages in memory—in the buffer cache—and does not write these pages to disk after every change. Rather, the Database Engine periodically issues a checkpoint on each database. A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also, records information about the transaction log.

Types of Checkpoints :-

Indirect Checkpoint :- Issued in the background to meet a user-specified target recovery time for a given database. The default is 0, which indicates that the database will use automatic checkpoints, whose frequency depends on the recovery interval setting of the server instance.

Automatic Checkpoints :- Issued automatically in the background to meet the upper time limit suggested by the recovery interval server configuration option. Automatic checkpoints run to completion. Automatic checkpoints are throttled based on the number of outstanding writes and whether the Database Engine detects an increase in write latency above 20 milliseconds.  

Manual Checkpoint :- Issued when you execute a Transact-SQL CHECKPOINT command. The manual checkpoint occurs in the current database for your connection. By default, manual checkpoints run to completion. Throttling works the same way as for automatic checkpoints. Optionally, the checkpoint_duration parameter specifies a requested amount of time, in seconds, for the checkpoint to complete.

Internal Checkpoint :- Issued by various server operations such as backup and database-snapshot creation to guarantee that disk images match the current state of the log.