Dynamically Changing Cache Sizes → You can dynamically reconfigure the sizes of the shared pool, the large pool, the buffer cache, and the process-private memory. Memory for the shared pool, large pool, java pool, and buffer cache is allocated in units of granules. Generally speaking, on most platforms, the size of a granule is 4 MB if the total SGA size is less than 128 MB, and it is 16 MB for larger SGAs. There may be some platform
KEEP Pool → consistent gets from the older values, and use the results to compute the hit ratio. A buffer pool
Sizing the Shared Pool → individually, use the following query: SELECT namespace, pins, pinhits, reloads, invalidations FROM V … library cache hit ratio, use the following formula: Library Cache Hit Ratio = sum(pinhits) / sum(pins … library cache's use of shared pool memory and predict how the library cache will behave in shared pools … since the most
Interpreting Shared Pool Statistics → Shared pool statistics indicate adjustments that can be made. The following sections describe some of your choices. Increasing Memory Allocation Increasing the amount of memory for the shared pool increases the amount of memory available to both the library cache and the dictionary cache. Allocating Additional Memory for the Library Cache To ensure that shared SQL areas remain in the cache after their
Keeping Large Objects to Prevent Aging → loaded and aged, the free memory can become fragmented. Use the PL/SQL package DBMS_SHARED_POOL to … the loss of sequence numbers. To use the DBMS_SHARED_POOL package to pin a SQL or PL/SQL area
Information Stored in the Database Audit Trail → The database audit trail, stored in the SYS.AUD$ table, contains different types of information, depending on the events audited and the auditing options set. The following information is always included in each audit trail record: Operating system login user name User name Session identifier Terminal identifier Name of the schema object accessed Operation performed or attempted Completion code of
Using Audit Trail Views to Investigate Suspicious Activities → This section offers examples that demonstrate how to examine and interpret the information in the audit trail. Consider the following situation. You would like to audit the database for the following suspicious activities: Passwords, tablespace settings, and quotas for some database users are being altered without authorization. A high number of deadlocks are occurring, most likely because of users
Using the Buffer Cache Effectively → To use the buffer cache effectively, SQL statements for the application should be tuned to avoid
Sizing the Buffer Cache → buffer pool is small to begin with. To use V$DB_CACHE_ADVICE, the parameter DB_CACHE_ADVICE should … use of additional buffers that contribute little or nothing to the cache hit ratio. In the example … delta of these statistics over an interval while your application is running, then use them to
Buffer Pool Hit Ratios → The data in V$SYSSTAT reflects the logical and physical reads for all buffer pools within one set of statistics. To determine the hit ratio for the buffer pools individually, you must query the V$BUFFER_POOL_STATISTICS view. This view maintains statistics for each pool on the number of logical reads and writes. The buffer pool hit ratio can be determined using the following formula: hit ratio = 1
What Information is Contained in the Audit Trail? → Oracle can write records to either the database audit trail, an operating system file, or both. This section describes the makeup of this audit trail information.
Managing the Audit Trail → This section describes various aspects of managing audit trail information, and contains the following topics: Enabling and Disabling Auditing Setting Auditing Options Auditing in a Multi-Tier Environment Turning Off Audit Options Controlling the Growth and Size of the Audit Trail Protecting the Audit Trail
Viewing Database Audit Trail Information → available. They must be created by you. You can later delete them if you decide not to use auditing.
Deleting the Audit Trail Views → If you disable auditing and no longer need the audit trail views, delete them by connecting to the database as SYS and running the script file CATNOAUD.SQL. The name and location of the CATNOAUD.SQL script are operating system dependent.
Information Stored in an Operating System File → The operating system file that contains the audit trail can contain any of the following: Audit records generated by the operating system Database audit trail records Database actions that are always audited Audit records for administrative users ( SYS ) Audit trail records written to an operating system audit trail may contain encoded information, but this information can be decoded using data dictionary
Understanding Memory Allocation Issues → Oracle stores information in memory caches and on disk. Memory access is much faster than disk access. Disk scans (physical I/O) take a significant amount of time, compared with memory access, typically in the order of 10 milliseconds. Physical I/O also increases the CPU resources required, because of the path length in device drivers and operating system event schedulers. For this reason, it is more
Application Considerations → needs. Conversely, tuning the application's use of the caches can greatly reduce resource requirements … . Efficient use of the Oracle memory caches also reduces the load on related resources, such as the
Iteration During Configuration → Configuring memory allocation involves distributing available memory to Oracle memory structures, depending on the needs of the application. The distribution of memory to Oracle structures can affect the amount of physical I/O necessary for Oracle to operate. Having a good first initial memory configuration also provides an indication of whether the I/O system is effectively configured. It might be
Interpreting and Using the Buffer Cache Advisory Statistics → basis, there might be little purpose in keeping the block in memory for very long following its use. A … are doing full table scans or operations that do not use the buffer cache. Increasing Memory … specifies the size of the default cache for the database's standard block size. To create and use