Tuning Memory for the Flash Cache. For each database block moved from the buffer cache to the flash cache, a small amount of metadata about the block is kept in the buffer cache. For a single instance database, the metadata consumes approximately 100 bytes. For an Oracle Real Application Clusters (Oracle RAC) database, it is closer to 200 bytes. Is this true for auto management and memorytarget is set. So it will go max upto sgamaxsize and rest will be taken by PGA say I set sgamaxsize to 1.5 gb and memorytarget is 2.5 gb Then sgatarget is can increase up to 1.5 max.

Oracle Database 11g represents a major milestone in Oracle's drive toward self-managing databases. It automates many routine administrative tasks, and considerably simplifies key DBA functions, such as performance diagnostics, SQL tuning, and space and memory management. It also provides several advisors that guide DBAs in managing key components of the database by giving specific recommendations along with potential benefit. Furthermore, Oracle Database 11g proactively sends alerts when a problem is anticipated, thus facilitating proactive rather than reactive database management.

This chapter contains the following topics:

Installing Oracle Database 11g and Getting Started

The Oracle Universal Installer is a GUI tool for installing Oracle software. It automates all installation tasks, performs comprehensive prerequisite checks (such as operating system version, software patches, and capacity), installs selected software components, and performs all postinstall configuration.

The installation process is self-contained to automatically set up the required infrastructure for routine monitoring and administration. The Oracle Enterprise Manager Database Management Console is automatically configured to let you to get started with database administrative tasks without any manual configuration. The Oracle Enterprise Manager Database Console provides all essential functionality for managing a single database, including alert notification, job scheduling, and software management. In addition, all Oracle Database server components such as the database, listener, management framework, and so on, are configured for automated startup and shutdown.

See Also:

'Configuration Management' for more information about Oracle Enterprise Manager

This section includes the following topics:

Simplified Database Creation

The Database Creation Assistant (DBCA) is a GUI tool for database creation. It lets you create all possible configurations of the database, be it a standalone database, an Oracle Real Application Clusters database, or a standby database. During the database creation process, the DBCA guides you in setting up an automated disk-based backup and registering the database with a LDAP server, if available. A database created using the DBCA is fully setup and ready to use in all respects.

Instant Client

The Instant Client is the simplest way to deploy a full Oracle Client application built with OCI, OCCI, JDBC-OCI, or ODBC drivers. It provides the necessary Oracle Client libraries in a small set of files. Installation is as easy as copying a few shared libraries to a directory on the client computer. If this directory is accessible through the operating system library path variable (for instance, LD_LIBRARY_PATH or PATH) then the application will operate in the Instant Client mode. Instant Client deployment does not require the ORACLE_HOME environment, nor does it require the large number of code and data files provided in a full Oracle Client install, thereby significantly reducing the client application disk space needs. There is no loss in functionality or performance for an application deployed using Instant Client when compared to the same application running in a full ORACLE_HOME environment.

See Also:

• Chapter 24, 'SQL' and Chapter 25, 'Supported Application Development Languages' for more information about JDBC, OCI, and OCCI

• Oracle Call Interface Programmer's Guide for more information about Instant Client

Automated Upgrades

With the Database Upgrade Assistant (DBUA), you can upgrade any database configuration, including Oracle Real Application Clusters (Oracle RAC) and standby, just by answering a few simple questions. It automatically checks that adequate resources are available, ensures adherence to the best practices – such as backing up the database before beginning the upgrade process, replacing the obsolete and deprecate initialization parameters, and so on – and, verifies the successful completion of the operation.

The upgrade process is restartable, allowing it to automatically resume from the point of interruption. You can also get a time estimation of how long the upgrade process is likely to take.

Basic Initialization Parameters

The Oracle Database provides a number of initialization parameters to optimize its operation in diverse environments. Only a few of these parameters need to be explicitly set, because the default values are adequate in the majority of cases.

There are approximately 30 basic parameters. The remainder of the parameters are preserved to allow expert DBAs to adapt the behavior of the Oracle Database to meet unique requirements without overwhelming those who have no such requirements.

Data Loading, Transfer, and Archiving

Data Pump enables very high-speed data and metadata loading and unloading to and from the Oracle Database. It automatically manages and schedules multiple, parallel streams of load or unload for maximum throughput.

The transportable tablespace feature lets you quickly move a tablespace across Oracle databases. This can be much faster than performing either an export/import or unload/load of the same data, because transporting a tablespace only requires the copying of datafiles and integrating the tablespace structural information. You can also use transportable tablespaces to move index data, thereby avoiding the index rebuilds you would have to perform when importing or loading table data.

Data Pump functionality together with cross-platform transportable tablespace feature provides powerful, easy to use, and high performance tools for moving data in and out of the database.

See Also:

Intelligent Infrastructure

Oracle Database has a sophisticated self-management infrastructure that allows the database to learn about itself and use this information to adapt to workload variations or to automatically remedy any potential problem. The self-management infrastructure includes the following:

Automatic Workload Repository

The Automatic Workload Repository (AWR) is a built-in repository that contains performance statistics used by Oracle Database for problem detection and self-tuning purposes. At regular intervals, Oracle Database makes a snapshot of vital statistics and workload information and stores them in the AWR. The data contained in the snapshots is then analyzed by the Automatic Database Diagnostic Monitor (ADDM). The difference between snapshots is compared to determine which SQL statements to capture based on the effect on the system load. This reduces the number of SQL statements that need to be captured over time. By default, the snapshots are taken once every hour and retained in the AWR for 8 days, after which they are automatically purged. You can change both the frequency and the retention period of snapshots.

Snapshots from specific time periods can be preserved in a baseline for comparison with other similar workload periods. The snapshots contained in a baseline are excluded from the automatic AWR purging process and are retained indefinitely. There are several types of available baselines in Oracle Database: fixed baselines, moving window baselines, and baseline templates. A fixed baseline corresponds to a fixed, contiguous time period in the past. Fixed baselines captured when the system is operating at an optimal level can be compared with other baselines or snapshots captured during periods of poor performance to analyze performance degradation over time. A moving window baseline corresponds to all AWR data that exists within the AWR retention period. This is useful when using adaptive thresholds because the AWR data in the entire AWR retention period can be used to compute metric threshold values. Baseline templates can be used to create baselines for contiguous time periods in the future. There are two types of baseline templates: single and repeating. A single baseline template can be used to create a baseline for a single contiguous time period in the future. This is useful if you know beforehand of a time period that you want to capture in the future. A repeating baseline template can be used to create and drop baselines based on a repeating time schedule. This is useful if you want Oracle Database to automatically capture a contiguous time period on an ongoing basis.

AWR forms the foundation for all self-management functionality of Oracle Database. It is the source of information that gives Oracle Database a historical perspective on how the database is being used, and enables ADDM to accurately diagnose and resolve potential performance problems.

See Also:

Oracle Database Performance Tuning Guide for information about the Automatic Workload Repository

Automatic Maintenance Tasks

By analyzing the information stored in AWR, the database can identify the need to perform routine maintenance tasks. The automated maintenance tasks infrastructure (known as AutoTask) enables Oracle Database to automatically schedule such operations. AutoTask schedules automatic maintenance tasks to run in a set of Oracle Scheduler windows known as maintenance windows. Maintenance windows are those windows that are members of the Oracle Scheduler window group MAINTENANCE_WINDOW_GROUP.

By default, MAINTENANCE_WINDOW_GROUP contains one window for each day of the week. Weekday windows (Monday through Friday) are configured to be open (active) for 4 hours starting at 10:00 p.m. Weekend windows (Saturday and Sunday) begin at 6:00 a.m. and remain open for 20 hours. You can customize all attributes of these maintenance windows, including start and end time, frequency, days of the week, and so on. You can also add and remove maintenance windows from the group.

The following are the tasks that AutoTask automatically schedules in these maintenance windows:

• Optimizer statistics gathering

• Automatic Segment Advisor

• SQL Tuning Advisor

Using Oracle Enterprise Manager or PL/SQL package procedures, you can adjust which of these tasks run in which maintenance windows.

Limiting Automatic Maintenance Task Resource Allocation

The impact of automated maintenance tasks on normal database operations is limited by the default Database Resource Manager resource plan. You can modify the default plan, or create your own resource plans and activate them either at the systemwide level or at the individual maintenance window level. AutoTask runs all automatic maintenance tasks as Oracle Scheduler jobs that belong to particular resource consumer groups. Resource plans then limit CPU resources that are allocated to these resource consumer groups. Because your user applications can be assigned to resource consumer groups, you can adjust the resource allocation for maintenance tasks not only relative to other maintenance tasks, but also relative to your applications.

See Also:

• Oracle Database Administrator's Guide and Oracle Database 2 Day DBA for instructions for managing automatic maintenance tasks

• Oracle Database Administrator's Guide for information about Automatic Segment Advisor

• Oracle Database Performance Tuning Guide for information about SQL Tuning Advisor

Fault Diagnosability Infrastructure

Oracle Database includes an advanced fault diagnosability infrastructure for preventing, detecting, diagnosing, and resolving problems. The problems that are targeted are critical errors such as those caused by database code bugs, metadata corruption, and customer data corruption. The goals of the advanced fault diagnosability infrastructure are the following:

• Detecting problems proactively

• Limiting damage and interruptions after a problem is detected

• Reducing problem diagnostic time

• Reducing problem resolution time

• Simplifying customer interaction with Oracle Support

The keys to achieving these goals are the following technologies:

• The Health Monitor, which performs deeper analysis of a critical error upon detection, creates health check reports and adds these reports to the diagnostic data collected for the error. The DBA can also manually invoke health checks and obtain reports.

• First-failure data capture, which captures comprehensive diagnostic data upon the first occurrence of a critical error

• Standardized trace and dump formats for easier analysis

• Incident packaging service, which enables the DBA to automatically package all diagnostic information surrounding a critical error into an archive suitable for transmission to Oracle Support.

• Data Recovery Advisor, which displays data corruption problems, assesses the extent of the problems, and recommends repair options

• SQL Test Case Builder, which helps Oracle Support reproduce customer problems that are related to SQL failures

• Support Workbench, which is a guided workflow that assists you with capturing critical error diagnostic information, transmitting it to Oracle Support, and filing a service request

See Also:

Oracle Database Administrator's Guide for more information on the fault diagnosability infrastructure and on the Support Workbench

This section further discusses two components of this new infrastructure:

Automatic Diagnostic Repository

The Automatic Diagnostic Repository (ADR) is a file-based repository for database diagnostic data such as traces, the alert log, health monitor reports, and more. It has a unified directory structure across multiple instances and multiple products. Beginning with Oracle Database 11g, the database, Automatic Storage Management (ASM), and other Oracle products or components store all diagnostic data in the ADR. Each instance of each product stores diagnostic data underneath its own ADR home directory. For example, in an Oracle Real Application Clusters environment with shared storage and ASM, each database instance and each ASM instance has a home directory within the ADR. ADR's unified directory structure, consistent diagnostic data formats across products and instances, and a unified set of tools enable customers and Oracle Support to correlate and analyze diagnostic data across multiple instances.

Incident Packaging Service

A DBA can automatically and easily gather all diagnostic data (traces, health check reports, SQL test cases, and more) pertaining to a critical error and package the data into a zip file suitable for transmission to Oracle Support. Because all diagnostic data relating to a critical error are tagged with that error's incident number, the DBA does not have to search through trace files and other files to determine the files that are required for analysis; the incident packaging service identifies all required files automatically and adds them to the package.

See Also:

• Oracle Database Administrator's Guide for more information about these components

• Oracle Database Net Services Administrator's Guide and Oracle Database Net Services Reference for information on ADR usage

Server-Generated Alerts

For problems that cannot be resolved automatically and require administrators to be notified, such as running out of space, the Oracle Database provides server-generated alerts. Oracle Database can monitor itself and send out alerts to notify you of any problem in an efficient and timely manner.

Monitoring activities take place as the database performs its regular operation. This ensures that the database is aware of problems the moment they arise. The alerts produced by Oracle Database not only notify the problem, they also provide recommendations on how the reported problem can be resolved. This ensures quick problem resolution and helps prevent potential failures.

Advisor Framework

Oracle Database includes a number of advisors for different sub-systems in the database to automatically determine how the operation of the corresponding subcomponents could be further optimized. The SQL Tuning Advisor and the SQL Access Advisor, for example, provide recommendations for running SQL statements faster. Memory advisors help size the various memory components without resorting to trial-and-error techniques. The Segment Advisor handles space-related issues, such as recommending wasted-space reclamation and analyzing growth trends, while the Undo Advisor guides you in sizing the undo tablespace correctly. The various advisors are discussed more throughout this chapter.

To ensure the consistency and uniformity in the way advisors function and allow them to interact with each other seamlessly, Oracle Database includes an advisor framework. The advisor framework provides a consistent manner in which advisors are invoked and results are reported. Although these advisors are primarily used by the database to optimize its own performance, they can be invoked by administrators to get more insight into the functioning of a particular subcomponent.

See Also:

Oracle Database 2 Day DBA for more information about using advisors

Hang Manager

Active entities that attempt to obtain restrictive access to shared resources or request services from other Oracle Database processes, sessions, and transactions are in danger of hanging. A hang chain is a chain of processes with each one waiting on a resource held by the next, with a single process serving as the root of the hang.

Hangs in Oracle Database can cost a great deal in terms of system unavailability. Specifically, hangs lead to the following problems:

• Extended system outages. These outages may occur frequently before a fix is found, which adds to the total downtime.

• Analyzing the hang to determine where the problem lies can be lengthy, complex, and prone to error.

The Hang Manager is an Oracle Database infrastructure that can detect hangs, analyze them, and then obtain the required diagnostic data from Oracle. The Hang Manager is enabled by default in Oracle RAC databases and Automatic Storage Management (ASM) instances. Hang manager data is output to trace files.

Performance Diagnostics and Troubleshooting

Building upon the data captured in AWR, the Automatic Database Diagnostic Monitor (ADDM) lets Oracle Database diagnose its own performance and determine how identified problems could be resolved. ADDM runs automatically after each AWR statistics capture, making the performance diagnostic data readily available.

ADDM examines data captured in AWR and performs analysis to determine the major issues on the system on a proactive basis. In many cases, it recommends solutions and quantifies expected benefits. ADDM takes a holistic approach to the performance of the system, using time as a common currency between components. ADDM identifies those areas of the system that are consuming the most time. ADDM drills down to identify the root cause of problems, rather than just the symptoms, and reports the impact that the problem is having on the system overall. If a recommendation is made, it reports the benefits that can be expected in terms of time. The use of time throughout allows the impact of several problems or recommendations to be compared.

ADDM focuses on activities that the database is spending most time on and then drills down through a sophisticated problem classification tree. Some common problems detected by ADDM include the following:

• CPU bottlenecks

• Poor connection management

• Excessive parsing

• Lock contention

• I/O capacity

• Undersizing of Oracle Database memory structures; for example, PGA, buffer cache, log buffer

• High load SQL statements

• High PL/SQL and Java time

• High checkpoint load and cause; for example, small log files, aggressive MTTR setting

• Oracle RAC-specific issues

Besides reporting potential performance issues, ADDM also documents non-problem areas of the system. The subcomponents, such as I/O and memory, that are not significantly impacting system performance are pruned from the classification tree at an early stage and are listed so that you can quickly see that there is little to be gained by performing actions in those areas.

You no longer need to first collect huge volumes of diagnostic data and spend hours analyzing them in order to find out answers to performance issues. You can simply follow the recommendation made by ADDM with just a few mouse clicks.

Application and SQL Tuning

Oracle Database completely automates the SQL tuning process. ADDM identifies SQL statements consuming unusually high system resources and therefore causing performance problems. In addition, the top SQL statements in terms of CPU and shared memory consumption are automatically captured in AWR. Thus, the identification of high load SQL statements happens automatically in Oracle Database and requires no intervention.

After identifying the top resource-consuming SQL statements, Oracle Database can automatically analyze them and recommend solutions using the Automatic SQL Tuning Advisor. Automatic SQL Tuning is exposed with an advisor, called the SQL Tuning Advisor. The SQL Tuning Advisor takes one or more SQL statements as input and produces well-tuned plans along with tuning advice. You do not need to do anything other than invoke the SQL Tuning Advisor.

The solution comes right from the optimizer and not from external tools using pre-defined heuristics. This provides several advantages: a) the tuning is done by the system component that is ultimately responsible for the execution plans and SQL performance, b) the tuning process is fully cost-based, and it naturally accounts for any changes and enhancements done to the query optimizer, c) the tuning process considers the past execution statistics of a SQL statement and customizes the optimizer settings for that statement, and d) it collects auxiliary information in conjunction with the regular statistics based on what is considered useful by the query optimizer.

The recommendation of the Automatic SQL Tuning Advisor can fall into one of the following categories

• Statistics Analysis: The Automatic SQL Tuning Advisor checks each query object for missing or stale statistics and makes recommendations to gather relevant statistics. It also collects auxiliary information to supply missing statistics or correct stale statistics in case recommendations are not implemented. Because Oracle Database automatically gathers optimizer statistics, this should not be the problem unless the automatic statistics gathering functionality has been disabled.

• SQL Profiling: The Automatic SQL Tuning Advisor verifies its own estimates and collects auxiliary information to remove estimation errors. It also collects auxiliary information in the form of customized optimizer settings (for example, first rows or all rows) based on past execution history of the SQL statement. It builds a SQL profile using the auxiliary information and makes a recommendation to create it. It then enables the query optimizer (under normal mode) to generate a well-tuned plan. The most powerful aspect of SQL profiles is that they enable tuning of queries without requiring any syntactical changes and thereby proving a unique database –resident solution to tune the SQL statements embedded in packaged applications.

• Access Path Analysis: The Automatic SQL Tuning Advisor considers whether a new index can be used to significantly improve access to each table in the query and when appropriate makes recommendations to create such indexes.

• SQL Structure Analysis: The Automatic SQL Tuning Advisor tries to identify SQL statements that lend themselves to bad plans and makes relevant suggestions to restructure them. The suggested restructuring can be syntactic as well as semantic changes to the SQL code.

Both access path and SQL structure analysis can be useful in tuning the performance of an application under development or a homegrown production application where the administrators and developers have access to application code.

The SQL Access Advisor can automatically analyze the schema design for a given workload and recommend indexes, function-based indexes, partitions, and materialized views to create, retain, or drop as appropriate for the workload. For single statement scenarios, the advisor only recommends adjustments that affect the current statement. For complete business workloads, the advisor makes recommendations after considering the impact on the entire workload.

While generating recommendations, the SQL Access Advisor considers the impact of adding new indexes, partitions, and materialized views on data manipulation activities, such as insert, update, and delete, in addition to the performance improvement they are likely to provide for queries. After the SQL Access Advisor has filtered the workload, but while it is still identifying all possible solutions, you can asynchronously interrupt the process to get the best solution up to that point in time.

The SQL Access Advisor provides an easy to use interface and requires very little system knowledge. It can be run without affecting production systems, because the data can be gathered from the production system and taken to another computer where the SQL Access Advisor can be run.

See Also:

Oracle Database Performance Tuning Guide for more information about the SQL Tuning Advisor and the SQL Access Advisor

Memory Management

Oracle Database memory management allows for dynamic resizing of system global area (SGA) and program global area (PGA) memory components, either automatically or manually.

Automatic Memory Management

By default, new database installations are configured to automatically tune the various components of the SGA and PGA. You can make simple high-level adjustments to memory allocation by changing one database parameter: MEMORY_TARGET. As you allocate more system memory to the database with this parameter, the database automatically adjusts various component sizes for optimal database performance.

The performance of each component is monitored by the Oracle database instance. The instance uses internal views and statistics to determine how to optimally distribute memory among the automatically-sized components. Thus, as the workload changes, memory is redistributed to ensure optimal performance with the new workload. The database arrives at optimal distribution by taking into consideration long term and short terms trends.

You can exercise some control over the size of the auto-tuned components by specifying minimum values for each component. This can be useful in cases where you know that an application needs a minimum amount of memory in certain components to function properly.

The sizes of the automatically-tuned components are remembered across shutdowns if a server parameter file (SPFILE) is used. Thus, the system picks up where it left off from the last shutdown.

Manual Memory Management and Memory Advisors

If you want to exercise more precise control over allocation for multiple memory components, you can enable manual memory management. You can then take advantage of a set of memory advisors, which graphically display current component sizes and the estimated affect of changing these sizes.

The Shared Pool Advisor determines the optimal shared pool size by tracking its use by the library cache. The amount of memory available for the library cache can drastically affect the parse rate of an Oracle database instance. The shared pool advisor statistics provide information about library cache memory, letting you predict how changes in the size of the shared pool can affect aging out of objects in the shared pool.

The Buffer Cache Advisor determines the optimal size of the buffer cache. When manually configuring memory for a new instance, it is difficult to know the correct size for the buffer cache. Typically, you make a first estimate for the cache size, run a representative workload on the instance, and then examine the relevant statistics to see whether the cache is under- or over-configured. A number of statistics can be used to examine buffer cache activity. These include the V$DB_CACHE_ADVICE view and the buffer cache hit ratio.

The Java Pool Advisor provides information about library cache memory used for Java, and predicts how changes in the size of the Java pool can affect the parse rate.

The Streams Pool Advisor determines the optimal size of the Streams pool. The view V$STREAMS_POOL_ADVICE gives estimates of the amount of bytes spilled and unspilled for the different values of the STREAMS_POOL_SIZE parameter. You can use this to tune the STREAMS_POOL_SIZE parameter for Streams and for logical standby. AWR reports on the V$STREAMS_POOL_ADVICE view and CPU usage to help you tune Streams performance.

The Program Global Area (PGA) Advisor helps you determine an appropriate setting for PGA_AGGREGATE_TARGET, which is the total amount of memory to allocate for all PGAs for server and background processes.

See Also:

• Oracle Database Performance Tuning Guide for more information about memory advisors

• Oracle Database Administrator's Guide for information about the various initialization parameters for manual and automatic memory management, and for information about server parameter files

Space Management

Oracle Database automatically manages its space consumption, sends alerts on potential space problems, and recommends possible solutions. Oracle Database features that help you to easily manage space include the following:

• See Also:

  Oracle Database Storage Administrator's Guide

Automatic Undo Management

Earlier releases of Oracle Database used rollback segments to store undo. Space management for these rollback segments was complex. Automatic undo management eliminates the complexities of managing rollback segments by automatically managing space in an undo tablespace. Automatic undo management also optimally tunes the length of time that undo is retained before being overwritten. This automatic tuning of undo retention improves the success rate of long running queries and of certain Oracle Flashback features, which may require the presence of old undo information.

Although you can configure the database to use rollback segments, automatic undo management is the default. An autoextending undo tablespace is automatically created upon database installation.

Automatic tuning of undo retention generally achieves better results with a fixed size undo tablespace. If you want to change the undo tablespace to fixed size for this or other reasons, the Undo Advisor can help you determine the proper fixed size to allocate. You provide the desired undo retention period for your long-running queries or Oracle Flashback operations, and the Undo Advisor suggests the required undo tablespace size. The Undo Advisor makes its recommendations based on system activity statistics, including the longest running query and undo generation rate. Advisor information includes the following:

• Current undo retention

• Current undo tablespace size

• Longest query duration

• Best undo retention possible

• Undo tablespace size necessary for current undo retention

See Also:

• Oracle Database 2 Day DBA for information about managing undo and running the Undo Advisor

• Oracle Database Administrator's Guide for more information about the undo tablespace and on undo retention

Oracle-Managed Files

With Oracle-managed files, you do not need to directly manage the files comprising an Oracle database. Oracle Database uses standard file system interfaces to create and delete files as needed. This automates the routine task of creation and deletion of database files.

Free Space Management

Oracle Database allows for managing free space within a table with bitmaps, as well as traditional dictionary based space management. The bitmapped implementation eliminates much space-related tuning of tables, while providing improved performance during peak loads. Additionally, Oracle Database provides automatic extension of data files, so the files can grow automatically based on the amount of data in the files. Database administrators do not need to manually track and reorganize the space usage in all the database files.

Proactive Space Management

Oracle Database introduces a nonintrusive and timely check for space utilization monitoring. It automatically monitors space utilization during normal space allocation and de-allocation operations and alerts you if the free space availability falls below the pre-defined thresholds. Space monitoring functionality is set up out of box, causes no performance impact, and is uniformly available across all tablespace types. Also, the same functionality is available both through Oracle Enterprise Manager as well as SQL. Because the monitoring is performed at the same time as space is allocated and freed up in the database, this guarantees immediate availability of space usage information whenever you need it.

Notification is performed using server-generated alerts. The alerts are triggered when certain space-related events occur in the database. For example, when the space usage threshold of a tablespace is crossed or when a resumable session encounters an out of space situation, then an alert is raised. An alert is sent instantaneously to take corrective measures. You may choose to get paged with the alert information and add space to the tablespace to allow the suspended operation to continue from where it left off.

The database comes with a default set of alert thresholds. You can override the default for a given tablespace or set a new default for the entire database through Oracle Enterprise Manager.

Intelligent Capacity Planning

Space may get overallocated because of the difficulty to predict the space requirement of an object or the inability to predict the growth trend of an object. On tables that are heavily updated, the resulting segment may have a lot of internal fragmentation and maybe even row chaining. These issues can result in a wide variety of problems from poor performance to space wastage. Oracle Database offers several features to address these challenges.

Oracle Database can predict the size of a given table based on its structure and estimated number of rows. This is a powerful 'what if' tool that allows estimation of the size of an object before it is created or rebuilt. If tablespaces have different extent management policies, then the tool will help decide the tablespace that will cause least internal fragmentation.

The growth trend report takes you to the next step of capacity planning: planning for growth. Most database systems grow over time. Planning for growth is an important aspect of provisioning resources. To aid this process, Oracle Database tracks historical space utilization in the AWR and uses this information to predict the future resource requirements.

Space Reclamation

Oracle Database provides in-place reorganization of data for optimal space utilization by shrinking it. Shrinking of a segment makes unused space available to other segments in the tablespace and may improve the performance of queries and DML operations.

The segment shrink functionality both compacts the space used in a segment and then deallocates it from the segment. The deallocated space is returned to the tablespace and is available to other objects in the tablespace. Sparsely populated tables may cause a performance problem for full table scans. By performing shrink, data in the table is compacted and the high water mark of the segment is pushed down. This makes full table scans read less blocks run faster.

Segment shrink is an online operation – the table being shrunk is open to queries and DML while the segment is being shrunk. Additionally, segment shrink is performed in place. This is an advantage over online table redefinition for compaction and reclaiming space. You can schedule segment shrink for one or all the objects in the database as nightly jobs without requiring any additional space to be provided to the database.

Segment shrink works on heaps, IOTs, IOT overflow segments, LOBs, LOB segments, materialized views, and indexes with row movement enabled in tablespaces with automatic segment space management. When segment shrink is performed on tables with indexes on them, the indexes are automatically maintained when rows are moved around for compaction. User-defined triggers are not fired, however, because compaction is a purely physical operation and does not impact the application.

Note:

Segment shrink can be performed only on tables with row movement enabled. Applications that explicitly track rowids of objects cannot be shrunk, because the application tracks the physical location of rows in the objects.

To easily identify candidate segments for shrinking, Oracle Database automatically runs the Segment Advisor to evaluate the entire database. The Segment Advisor performs growth trend analysis on individual objects to determine if there will be any additional space left in the object in seven days. It then uses the reclaim space target to select candidate objects to shrink.

Note:

The Segment Advisor does not evaluate undo and temporary tablespaces.

In addition to using the pre-computed statistics in the workload repository, the Segment Advisor performs sampling of the objects under consideration to refine the statistics for the objects. Although this operation is more resource intensive, it can be used to perform a more accurate analysis.

Although segment shrink reduces row chaining, and Oracle Database recommends online redefinition to remove chained rows, the Segment Advisor actually detects certain chained rows that are above a threshold. For example, if a row size increases during an update such that it no longer fits into the block, then the Segment Advisor recommends that the segment be reorganized to improve I/O performance.

Note:

The Segment Advisor does not detect chained rows created by inserts.

See Also:

• 'Row Chaining and Migrating' for more information about row chaining

• Oracle Database Administrator's Guide and Oracle Database 2 Day DBA for more information about using the Segment Advisor

Automatic Storage Management

Automatic Storage Management (ASM) provides a vertical integration of the file system and volume manager specifically built for Oracle database files. ASM distributes I/O load across all available resources to optimize performance while removing the need for manual I/O tuning; spreading out the database files avoids hotspots. ASM helps you manage a dynamic database environment by enabling you to increase a database's size without having to shutdown the database to adjust the storage allocation.

ASM lets you define a pool of storage, called a disk group, and then the Oracle kernel manages the file naming and placement of the database files on that disk group. You can change the storage allocation, such as by adding or removing disks, by using SQL statements such as CREATE DISKGROUP, ALTER DISKGROUP, and DROP DISKGROUP. You can also manage disk groups with Oracle Enterprise Manager and Database Configuration Assistant (DBCA).

Oracle Database provides a simplified management interface for storage resources. ASM eliminates the need for manual I/O performance tuning. It virtualizes storage to a set of disk groups and provides redundancy options to enable a high level of protection. ASM facilitates nonintrusive storage configuration changes with automatic rebalancing. It spreads database files across all available storage to optimize performance and resource utilization. ASM reduces your storage administrative overhead by automating manual storage and thereby increasing your ability to manage larger databases and more of them with increased efficiency.

The following are some of the basic ASM concepts:

• Automatic Storage Management Instances

  The ASM instance is a special Oracle instance that manages the disks in disk groups. The ASM instance must be configured and running to enable the database instance to access ASM files. This configuration is done automatically if Database Configuration Assistant was used for database creation. An ASM instance cannot mount a database. The ASM instance simply coordinates data layout for database instances. Database instances direct the I/O to disks in disk groups without going through an ASM instance.

• Disk Groups

  A disk group is one or more ASM disks managed as a logical unit. The data structures in a disk group are self contained and consume some of the disk space in a disk group. ASM disks can be added or dropped from a disk group while the database is running. ASM rebalances the data to ensure an even I/O load to all disks in a disk group even as the disk group configuration changes.

• Automatic Storage Management Files

  When the database requests it, ASM creates files. ASM assigns each file a fully qualified name ending in a dotted pair of numbers. You can create more user-friendly alias names for the ASM filenames. To see alias names for ASM files, query the V$ASM_ALIAS data dictionary view from an ASM instance. In general, users need not be aware of file names.

• Automatic Storage Management Disks

  Storage is added and removed from disk groups in units of ASM disks. ASM disks can be entire physical disks, Logical Unit Numbers (LUNs) from a storage array, or pre-created files in a NAS filer. ASM disks should be independent of each other to obtain optimal I/O performance. For instance, with a storage array, you might specify a LUN that represents a hardware mirrored pair of physical disks to ASM as a single ASM disk.

See Also:

Oracle Database Storage Administrator's Guide for information about ASM

Backup and Recovery

Oracle Database provides several features that help you to easily manage backup and recovery. These include the following:

Recovery Manager

Oracle Recovery Manager (RMAN) is a powerful tool that simplifies, automates, and improves the performance of backup and recovery operations. RMAN enables one time backup configuration, automatic management of backups and archived logs based on a user-specified recovery window, restartable backups and restores, and test restore/recovery. RMAN implements a recovery window to control when backups expire. This lets you establish a period of time during which it is possible to discover logical errors and fix the affected objects by doing a database or tablespace point-in-time recovery. RMAN also automatically expires backups that are no longer required to restore the database to a point-in-time within the recovery window. Control file autobackup also allows for restoring or recovering a database, even when a RMAN repository is not available.

DBCA can automatically schedule an on disk backup procedure. All you do is specify the time window for the automatic backups to run. A unified storage location for all recovery related files and activities in an Oracle database, called the flash recovery area, can be defined with the initialization parameter DB_RECOVERY_FILE_DEST. All files needed to completely recover a database from a media failure, such as control files, archived log files, Flashback logs, RMAN backups, and so on, are part of the flash recovery area.

Allocating sufficient space to the flash recovery area ensures faster, simpler, and automatic recovery of the Oracle database. Flash recovery actually manages the files stored in this location in an intelligent manner to maximize the space utilization and avoid out of space situations to the extent possible. Based on the specified RMAN retention policy, the flash recovery area automatically deletes obsolete backups and archive logs that are no longer required based on that configuration.

Incremental backups let you back up only the changed blocks since the previous backup. When the block change tracking feature is enabled, Oracle Database tracks the physical location of all database changes. RMAN automatically uses the change tracking file to determine which blocks need to be read during an incremental backup and directly accesses that block to back it up. It reduces the amount of time needed for daily backups, saves network bandwidth when backing up over a network, and reduces the backup file storage.

Incremental backups can be used for updating a previously made backup. With incrementally updated backups, you can merge the image copy of a datafile with a RMAN incremental backup, resulting in an updated backup that contains the changes captured by the incremental backup. This eliminates the requirement to make a whole database backup repeatedly. You can make a full database backup once for a given database and use incremental backups subsequently to keep the full back up updated. A backup strategy based on incrementally updated backups can help keep the time required for media recovery of your database to a minimum.

See Also:

Mean Time to Recovery

Oracle Database allows for better control over database downtime by letting you specify the mean time to recover (MTTR) from system failures in number of seconds. A user-specified MTTR, coupled with dynamic initialization parameters, helps improve database availability. After you set a time limit for how long a system failure recovery can take, Oracle Database automatically and transparently makes sure that the system can restart in that time frame, regardless of the application activity running on the system at the time of the failure. This provides the fastest possible up time after a system failure.

The smaller the online logfiles are, the more aggressively DBWRs do incremental checkpoints, which means more physical writes. This may adversely affect the run-time performance of the database. Furthermore, if you set FAST_START_MTTR_TARGET, then the smallest logfile size may drive incremental checkpointing more aggressively than needed by the MTTR.

The Logfile Size Advisor determines the optimal smallest logfile size from the current FAST_START_MTTR_TARGET setting and the MTTR statistics. A smallest logfile size is considered optimal if it does not drive incremental checkpointing more aggressively than needed by FAST_START_MTTR_TARGET.

The MTTR Advisor helps you evaluate the effect of different MTTR settings on system performance in terms of extra physical writes. When MTTR advisor is enabled, after the system runs a typical workload, you can query V$MTTR_TARGET_ADVICE to see the ratio of the estimated number of cache writes under other MTTR settings to the number of cache writes under the current MTTR. For instance, a ratio of 1.2 indicates 20% more cache writes.

By looking at the different MTTR settings and their corresponding cache write ratio, you can decide which MTTR value fits your recovery and performance needs. V$MTTR_TARGET_ADVICE also gives the ratio on total physical writes, including direct writes, and the ratio on total I/O, including reads.

See Also:

Oracle Database Backup and Recovery User's Guide for information about using the MTTR Advisor

Self Service Error Correction

Oracle Flashback technology lets you view and rewind data back and forth in time. You can query past versions of schema objects, query historical data, perform change analysis, or perform self-service repair to recover from logical corruptions while the database is online.

This revolutionizes recovery by just operating on the changed data. The time it takes to recover the error is equal to the amount of time it took to make the mistake.

See Also:

Configuration Management

Oracle Enterprise Manager has several powerful configuration management facilities that help detect configuration changes and differences and enforce best practice configuration parameter settings. These capabilities also encompass the underlying hosts and operating systems.

Oracle Enterprise Manager continuously monitors the configuration of all Oracle systems for such things as best practice parameter settings, security set-up, storage and file space conditions, and recommended feature usage. Non-conforming systems are automatically flagged with a detailed explanation of the specific-system configuration issue. For example, Oracle Enterprise Manager advises you to use new functionality such as automatic undo management or locally managed tablespaces if they are not being used. This automatic monitoring of system configurations promotes best practices configuration management, reduces administrator workload and the risk of availability, performance, or security compromises.

Oracle Enterprise Manager also automatically alerts you to new critical patches – such as important security patches – and flags all systems that require that patch. In addition, you can invoke the Oracle Enterprise Manager patch wizard to find out what interim patches are available for that installation.

Workload Management

Oracle Database provides the following resource management features:

Overview of the Database Resource Manager

The Database Resource Manager provides the ability to prioritize work within the Oracle database system. High priority users get resources, so as to minimize response time for online workers, for example, while lower priority users, such as batch jobs or reports, could take longer. This allows for more granular control over resources and provides features such as automatic consumer group switching, maximum active sessions control, query execution time estimation and undo pool quotas for consumer groups.

You can specify the maximum number of concurrently active sessions for each consumer group. When this limit is reached, the Database Resource Manager queues all subsequent requests and runs them only after existing active sessions complete.

The Database Resource Manager solves many resource allocation problems that an operating system does not manage so well:

• Excessive overhead. This results from operating system context switching between Oracle database server processes when the number of server processes is high.

• Inefficient scheduling. The operating system deschedules Oracle database servers while they hold latches, which is inefficient.

• Inappropriate allocation of resources. The operating system distributes resources equally among all active processes and is unable to prioritize one task over another.

• Inability to manage database-specific resources.

With the Database Resource Manager, you can do the following:

• Guarantee certain users a minimum amount of processing resources regardless of the load on the system and the number of users.

• Distribute available processing resources by allocating percentages of CPU time or I/O requests per second to different users and applications.

  For example, in a data warehouse, a higher percentage of CPU may be given to ROLAP (relational on-line analytical processing) applications than to batch jobs. If I/O resource management is enabled with a shared storage configuration, then you could also the maximum number of I/O requests per second that can be issued by this database, or the maximum megabytes of I/O per second.

• Limit the degree of parallelism of any operation performed by members of a group of users.

• Create an active session pool.

  This pool consists of a specified maximum number of user sessions allowed to be concurrently active within a group of users. Additional sessions beyond the maximum are queued for execution, but you can specify a timeout period, after which queued jobs terminate.

• Allow automatic switching of users from one group to another group based on administrator-defined criteria.

  If a member of a particular group of users creates a session that runs for longer than a specified amount of time or uses a larger amount of I/O (in MB) or a higher number of I/O requests than allocated, then this session can be automatically switched to another group of users with different resource requirements.

• Prevent the execution of operations that are estimated to run for a longer time than a predefined limit.

  Note:

  Switching users or preventing operations could be based on amount of I/O, as well as amount of CPU time.

  See Also:

  Oracle Database Administrator's Guide for more information about automatic switching

• Create an undo pool.

  This pool consists of the amount of undo space that can be consumed in by a group of users.

• Configure an instance to use a particular method of allocating resources.

  You can dynamically change the method, for example, from a daytime setup to a nighttime setup, without having to shut down and restart the instance.

• Identify sessions that would block a quiesce from completing.

It is thus possible to balance one user's resource consumption against that of other users and to partition system resources among tasks of varying importance, to achieve overall enterprise goals.

Database Resource Manager Concepts

Resources are allocated to users according to a resource plan specified by the database administrator. The following terms are used in specifying a resource plan:

A resource plan specifies how the resources are to be distributed among various users (resource consumer groups).

Resource consumer groups let you group user sessions together by resource requirements. Resource consumer groups are different from user roles; one database user can have different sessions assigned to different resource consumer groups.

Resource allocation methods determine what policy to use when allocating for any particular resource. Resource allocation methods are used by resource plans and resource consumer groups.

Resource plan directives are a means of assigning consumer groups to particular plans and partitioning resources among consumer groups by specifying parameters for each resource allocation method.

The Database Resource Manager also allows for creation of plans within plans, called subplans. Subplans allow further subdivision of resources among different users of an application.

Levels provide a mechanism to specify distribution of unused resources among available users. Up to eight levels of resource allocation can be specified.

See Also:

• Oracle Database Administrator's Guide for information about using the Database Resource Manager

• Oracle Database Performance Tuning Guide for information about how to tune resource plans

Overview of Services

Services represent groups of applications or a subset of a large application with common attributes, service level thresholds, and priorities. Application functions can be divided into workloads identified by services. For example, the Oracle E*Business suite can define a service for each module, such as general ledger, accounts receivable, order entry, and so on. Oracle Mail can define services for IMAP processes, postman, garbage collector, monitors, and so on. A service can span one or more instances of an Oracle database or multiple databases in a cluster, and a single instance can support multiple services.

The number of instances offering the service is transparent to the application. Services provide a single system image to manage competing applications, and they allow each workload to be managed as a single unit.

Middle tier applications and clients select a service by specifying the service name as part of the connection in the TNS connect data. For example, data sources for the Web server or the application server are set to route to a service. Using Net Easy*Connection, this connection includes the service name and network address. For example, service:IP.

Server side work, such as the Scheduler, parallel execution, and Oracle Streams Advanced Queuing set the service name as part of the workload definition. For the Scheduler, jobs are assigned to job classes, and job classes run within services. For parallel execution and parallel DML, the query coordinator connects to a service, and the parallel execution processes inherit the service for the duration of the query. For Oracle Streams Advanced Queuing, streams queues are accessed using services. Work running under a service inherits the thresholds and attributes for the service and is measured as part of the service.

The Database Resource Manager binds services to consumer groups and priorities. This lets services be managed in the database in the order of their importance. For example, you can define separate services for high priority online users and lower priority internal reporting applications. Likewise, you can define gold, silver, and bronze services to prioritize the order in which requests are serviced for the same application.

When planning the services for a system, include the priority of each service relative to the other services. In this way, the Database Resource Manager can satisfy the highest priority services first, followed by the next priority services, and so on.

This section includes the following topics:

Workload Management with Services

AWR lets you analyze the performance of workloads using the aggregation dimension for service. AWR automatically maintains response time and CPU consumption metrics, performance and resource statistics wait events, threshold-based alerts, and performance indexes for all services.

Service, module, and action tags identify operations within a service at the server. (MODULE and ACTION are set by the application) End to end monitoring enables aggregation and tracing at service, module, and action levels to identify high load operations. Oracle Enterprise Manager administers the service quality thresholds for response time and CPU consumption, monitors the top services, and provides drill down to the top modules and top actions for each service.

With AWR, performance management by the service aggregation makes sense when monitoring by sessions may not. For example, in systems using connection pools or transaction processing monitors, the sessions are shared, making accountability difficult.

The service, module, and action tags provide major and minor boundaries to discriminate the work and the processing flow. This aggregation level lets you tune groups of SQL that run together (at service, module, and action levels). These statistics can be used to manage service quality, to assess resource consumption, to adjust priorities of services relative to other services, and to point to places where tuning is required. With Oracle Real Application Clusters (Oracle RAC), services can be provisioned on different instances based on their current performance.

Connect time routing and run-time routing algorithms balance the workload across the instances offering a service. The metrics for server-side connection load balancing are extended to include service performance. Connections are shared across instances according to the current service performance. Using service performance for load balancing accommodates nodes of different sizes and workloads with competing priorities. It also prevents sending work to nodes that are hung or failed.

AWR maintains metrics for service performance continuously. These metrics are available when routing run-time requests from mid-tier servers and TP monitors to Oracle RAC. For example, Oracle JDBC connection pools use the service data when routing the run-time requests to instances offering a service.

High Availability with Services

Oracle RAC use services to enable uninterrupted database operations. Services are tightly integrated with the Oracle Clusterware high availability framework that supports Oracle RAC. When a failure occurs, the service continues uninterrupted on the nodes and instances unaffected by the failure. Those elements of the services affected by the failure are recovered fast by Oracle Clusterware, and the recovering sessions are balanced across the surviving system automatically. For planned outages, Oracle RAC provides interfaces to relocate, disable, and enable services. Relocate migrates the service to another instance, and, as an option, the sessions are disconnected. To prevent the Oracle Clusterware system from responding to an unplanned failure that happens during maintenance or repair, the service is disabled on the node doing maintenance at the beginning of the planned outage. It is then enabled at the end of the outage. These service-based operations, in combination with schema pre-compilation (DBMS_SCHEMA_COPY) on a service basis, minimize the downtime for many planned outages. For example, application upgrades, operating system upgrades, hardware upgrades and repairs, Oracle patches approved for rolling upgrade, and parameter changes can be implemented by isolating one or more services at a time.The continuous service built into Oracle RAC is extended to applications and mid-tier servers. When the state of a service changes, (for example, up, down, or not restarting), the new status is notified to interested subscribers through events and callouts. Applications can use this notification to achieve very fast detection of failures, balancing of connection pools following failures, and balancing of connection pools again when the failed components are repaired. For example, when the service at an instance starts, the event and callouts are used to immediately trigger work at the service. When the service at an instance stops, the event is used to interrupt applications using the service at that instance. Using the notification eliminates the client waiting on TCP timeouts. The events are integrated with Oracle JDBC connection pools, Oracle Data Provider for .Net Connection Pools, and Oracle Call Interface, including Transparent Application Failover (TAF).

With Oracle Data Guard, production services are offered at the production site. Other standby sites can offer reporting services when operating in read only mode. Oracle RAC and Data Guard Broker are integrated, so that when running failover, switchover, and protection mode changes, the production services are torn down at the original production site and built up at the new production site. There is a controlled change of command between Oracle Clusterware managing the services locally and Data Guard managing the transition. When the Data Guard transition is complete, Oracle Clusterware resumes management of the high availability operation automatically.

See Also:

Oracle Scheduler

Oracle Database includes a feature rich job scheduler. You can schedule jobs to run at a designated date and time (such as every weeknight at 11:00pm), or upon the occurrence of a designated event (such as when inventory drops below a certain level). You can define custom calendars such as the last workday of every fiscal quarter.

You create and manipulate Scheduler objects such as jobs, programs, and schedules with the DBMS_SCHEDULER package or with Oracle Enterprise Manager. Because Scheduler objects are standard database objects, you can control access to them with system and object privileges.

Program objects (or programs) contain metadata about the command that the Scheduler will run, including default values for any arguments. Schedule objects (schedules) contain information about run date and time and recurrence patterns. Job objects (jobs) associate a program with a schedule, and are the principal object that you work with in the Scheduler. You can create multiple jobs that refer to the same program but that run at different schedules. A job can override the default values of program arguments, so multiple jobs can refer to the same program but provide different argument values.

The Scheduler provides comprehensive job logging in Oracle Enterprise Manager and in a variety of views available from SQL*Plus. You can configure a job to raise an event when a specified job state change occurs. Your application can process the event and take appropriate action. For example, the Scheduler can page or send an e-mail to the DBA if a job terminates abnormally.

The Scheduler also includes chains, which are named groups of steps that work together to accomplish a task. Steps in the chain can be a program, subchain or an event, and you specify rules that determine when each step runs and what the dependencies between steps are. An example of a chain is to run programs A and B, and only run program C if programs A and B complete successfully, otherwise run program D.

The Scheduler is integrated with the Database Resource Manager. You can associate Scheduler jobs with resource consumer groups, and you can create Scheduler objects called windows that automatically activate different resource plans at different times. Running jobs can then see a change in the resources that are allocated to them when there is a change in resource plan. A Scheduler job can name a window as its schedule instead of a schedule object. Such a job runs when the named window opens. Additionally, windows can be grouped into window groups, and a job can name a window group as its schedule. Such a job runs whenever any of the windows in the named window group opens.

See Also:

Oracle Database Administrator's Guide for a detailed overview of the Scheduler and for information about how to use and administer the Scheduler

What Can the Scheduler Do?

The Scheduler provides complex enterprise scheduling functionality. You can use this functionality to do the following:

Schedule Job Execution

The most basic capability of a job scheduler is to schedule the execution of a job. The Scheduler supports both time-based and event-based scheduling.

Time-Based Scheduling

Time-based scheduling enables users to specify a fixed date and time (for example, Jan. 23rd 2006 at 1:00 AM), a repeating schedule (for example, every Monday), or a defined rule (for example the last Sunday of every other month or the fourth Thursday in November which defines Thanksgiving).

Users can create new composite schedules with minimum effort by combining existing schedules. For example if a HOLIDAY and WEEKDAY schedule were already defined, a WORKDAY schedule can be easily created by excluding the HOLIDAY schedule from the WEEKDAY schedule.

Companies often use a fiscal calendar as opposed to a regular calendar and thus have the requirement to schedule jobs on the last workday of their fiscal quarter. The Scheduler supports user-defined frequencies which enables users to define not only the last workday of every month but also the last workday of every fiscal quarter.

Event-Based Scheduling

Event-based scheduling as the name implies triggers jobs based on real-time events. Events are defined as any state changes or occurrences in the system such as the arrival of a file. Scheduling based on events enables you to handle situations where a precise time is not known in advance for when you would want a job to execute.

Define Multi-Step Jobs

The Scheduler has support for single or multi-step jobs. Multi-step jobs are defined using a Chain. A Chain consists of multiple steps combined using dependency rules. Since each step represents a task, Chains enable users to specify dependencies between tasks, for example execute task C one hour after the successful completion of task A and task B.

Schedule Job Processes that Model Business Requirements

Flash Cache And Auto-tuned Memory_target Game

The Scheduler enables job processing in a way that models your business requirements. It enables limited computing resources to be allocated appropriately among competing jobs, thus aligning job processing with your business needs. Jobs that share common characteristic and behavior can be grouped into larger entities called job classes. You can prioritize among the classes by controlling the resources allocated to each class. This lets you ensure that critical jobs have priority and enough resources to complete. Jobs can also be prioritized within a job class.

The Scheduler also provides the ability to change the prioritization based on a schedule. Because the definition of a critical job can change across time, the Scheduler lets you define different class priorities at different times.

Manage and Monitor Jobs

There are multiple states that a job undergoes from its creation to its completion. All Scheduler activity is logged, and information, such as the status of the job and the time to completion, can be easily tracked. This information is stored in views. It can be queried with Oracle Enterprise Manager or a SQL query. The views provide information about jobs and their execution that can help you schedule and manage your jobs better. For example, you can easily track all jobs that failed for user scott.

In order to facilitate the monitoring of jobs, users can also flag the Scheduler to raise an event if unexpected behavior occurs and indicate the actions that should be taken if the specified event occurs. For example if a job failed an administrator should be notified.

Flash Cache And Auto-tuned Memory_target Free

Execute and Manage Jobs in a Clustered Environment

A cluster is a set of database instances that cooperates to perform the same task. Oracle Real Application Clusters provides scalability and reliability without any change to your applications. The Scheduler fully supports execution of jobs in such a clustered environment. To balance the load on your system and for better performance, you can also specify the service where you want a job to run.

See Also:

• Oracle Database Administrator's Guide for more information about transferring files with the DBMS_SCHEDULER package and also the DBMS_FILE_TRANSFER package

• Oracle Database SQL Language Reference for more information about fixed user database links

Flashcache is a disk cache component for the Linux kernel, initially developed by Facebook since April 2010, and released as open source in 2011. Since January 2013, there is a fork of Flashcache, named EnhanceIO and developed by sTec, Inc.^[1]

Flashcache works by using flash memory, a USB flash drive, SD card, CompactFlash or any kind of portable flash mass storage system as a write-back persistent cache. An internal SSD can also be used for increasing performance.

Overview[edit]

Using flash memory (NAND memory devices) for caching allows Linux kernel to service random disk IO with better performance than without the cache. This caching applies to all disk content, not just the page file or system binaries. Flash memory based devices are usually a magnitude faster than spinning HDDs for random IO, but with less advantage or even slower in sequential read/writes. By default, flashcache caches all full blocksize IOs, but can be configured to only cache random IO whilst ignoring sequential IO.^[2]

Similar technology exists in Microsoft Windows as ReadyBoost since Windows Vista.

Implementation[edit]

Flashcache is built on top of the Linux kernel's device mapper. The data structure of the cache is a set-associativehash table, in which the cache is divided up into a number of fixed-size sets (buckets), using linear probing within a set to find blocks. The device mapper layer breaks up all I/O requests into blocksize chunks before passing the requests to the cache layer.^{[citation needed]}

When a write request happens, the corresponding cache block is marked dirty; dirty cache blocks are written lazily to disk in the background. There are a few parameters to control the write-back policy: dirty-threshold, idleness and contiguity with other dirty blocks about to be written back.^{[citation needed]}

Limitations[edit]

There are a few limitations, imposed by the implementation of flashcache:^{[citation needed]}

Atomicity

Cache block writes are currently non-atomic.

TRIM support

ATA TRIM command to optimize flash memory are not yet supported.

Cache pollution protection

A process can be marked non-cacheable to prevent flashcache cache its requests; however, if a process that marked itself non-cacheable dies, flashcache has no way of cleaning up.

Alignment

Relying on the device mapper resulted in caching performance issues and no caching of writes that are not multiple of 4 KiB. Primarily, this affects the Xen hypervisor. Thus, EnhanceIO has moved away from the device mapper integration, yielding higher performance for unoptimal use cases.^{[citation needed]}

Write-around read latency impact

in write-around mode all writes bypass the cache for high consistency. The current implementation will fetch reads through the SSD device and then deliver them to the actual reader. This means that previously uncached blocks will always need to go to the SSD device first, causing a constant write IO. Not an issue on enterprise SSD or highend PCIe devices as facebook uses, but degrades performance on lower end SSD.

Write-around read cache warm-up phase

in write-around mode FlashCache has no information to compare the age of cached pages over the on-disk ones. (1) Because the device could have been mounted outside of FlashCache (2) Because no writes are tracked in this mode. This results in an empty cache after each volume activation (i.e.: reboot). Performance will be degraded until all hot areas have been cached.

See also[edit]

• Cache Acceleration Software (Intel's product)

References[edit]

1. ^EnhanceIO: New Solid State Drive Caching For Linux
2. ^Mohan Srinivasan. 'Flashcache : A Write Back Block Cache for Linux'.

External links[edit]

• Performance Comparison among EnhanceIO, bcache and dm-cache (LKML)