The first place you’ll likely begin looking for resource bottlenecks is in the Oracle database software using Oracle Enterprise Manager.
Oracle’s dynamic performance views provide insight into bottlenecks within your Oracle database. Prior to the introduction of Oracle’s Automatic Workload Repository (AWR), the Automatic Database Diagnostics Monitor (ADDM), and Oracle Enterprise Manager Grid Control in Oracle Database 10g, querying the performance views often was the first step database administrators performed in determining bottlenecks. All of these performance views have names that begin with V$, and, from Oracle9i on, there are also global views (for all nodes in a Real Application Clusters or RAC database) that begin with GV$.
V$SYSTEM_EVENT – Provides aggregated, system wide information about the resources for which the whole instance is waiting
V$SESSION_EVENT - Provides cumulative list of events waited for in each session
V$SESSION_WAIT - Provides detailed, session-specific information about the resources for which individual sessions are currently waiting or last waited for
V$SESSION – Provides session information for each current session including event currently or last waited for
You may find that your problem has a simple source, such as a lower-than-expected database buffer cache hit ratio. Since the cache is not working at its optimal level, you could simply increase the initialization parameter DB_BLOCK_BUFFERS to increase the size of the cache and possibly improve the hit ratio. You can monitor the performance of the buffer cache hit ratio in V$METRICNAME.
The Automatic Workload Repository (AWR) captures and stores information about resource utilization by Oracle workloads. By default, statistics are captured every 30 minutes and are stored for 7 days. These statistics are accessible through views, but Enterprise Manager provides a much simpler-to-use interface.
Oracle’s Automatic Database Diagnostic Monitor ADDM automatically identifies and reports on resource bottlenecks, such as CPU contention, locking issues, or poor performance from specific SQL statements. For tuning your applications, you’ll likely look to the SQL Advisor – it combines the functionality of the SQL Tuning Advisor, the SQL Access Advisor, and the new Partition Advisor. The SQL Advisor leverages information on CPU and I/O consumption captured in the AWR and identifies high impact SQL statements indicated by the ADDM to make recommendations.
Memeory Advizor - For optimal setting of MEMORY_TARGET for automatic memory management in Oracle Database 11g.and optimal setting of SGA_TARGET for shared memory management.
Segment Advizor – for storage management and space allocation.
Undo Advisor – for managing transactions.
Mean Time to Recovery (MTTR) – optimize the setup of Oracle, including log files.
The performance of your Oracle database is based on how it uses the machine resources that are available. These machine resources include processing power or CPU, memory, disk I/O, and network bandwidth.
The slowest access is to disk and, as a result, the most common database performance issues are I/O related.
Since the introduction of Enterprise Manager 10g, a performance analyzer called Automatic PerformanceMonitoring (APM) has been included.
The number of blocks in one multiblock I/O is determined by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT.
The main destinations of the I/O operations Oracle performs are the following:
• Redo logs
• Data contained in tables
• Indexes on the tables
• The data dictionary, which goes in the SYSTEM tablespace
• Sort activity, which goes in the TEMP tablespace of the user performing the sort
• Rollback information, which is spread across the datafiles of the tablespace containing the database’s rollback segments
• Archived redo logs, which go to the archived log destination (assuming the database is in ARCHIVELOG mode)
Use disk-striping technologies to spread I/O evenly across multiple spindles.
One of the most powerful ways to reduce performance bottlenecks due to disk I/O is the use of RAID disk arrays. (Redundant Array of Inexpensive (or Independent) Disks.
Use tablespaces to clearly segregate and target different types of I/O - Separate table I/O from index I/O by placing these structures in different tablespaces. Youcan then place the datafiles for these tablespaces on various disks to provide better performance for concurrent access.
Place redo logs and redo log mirrors on the two least-busy devices.
Distribute “system overhead” evenly over the available drives. For example, if the application generates a lot of data changes versus data reads, the I/O to the rollback segments may increase due to higher writes for changes and higher reads for consistent read functionality. Sort activity can also affect disk I/O (TEMP – tablespace). Oracle constantly queries and updates the data dictionary stored in the SYSTEM tablespace, and this information is cached in the shared pool section of the SGA.
Use a different device for archiving and redo log files. (183)