Friday, September 23, 2011

GATHER_STATS_JOB is removed in 11g


GATHER_STATS_JOB is removed in 11g

Oracle Database 10g introduced the concept automated maintenance task execution during maintenance windows implemented via a WEEKNIGHT_WINDOW and WEEKEND_WINDOW schedule. This included statistics collection by means of the GATHER_STATS_JOB.


In Oracle Database 11g, the WEEKNIGHT_WINDOW and WEEKEND_WINDOW schedules (defined in Oracle Database 10g) are replaced with daily maintenance windows (such as  SUNDAY_WINDOW, MONDAY_WINDOW etc). These were replaced in order to add increased flexibility and manageability. One can query dba_scheduler_windows to check the window definitions.


Automatic Maintenance Tasks (including the automated Optimizer Statistics Gathering task) are defined to execute within these daily windows.



'Automatic Maintenance Tasks Management ' is an 11g new feature and was implemented to increase the flexibility of statistics collection and to avoid potential resourcing issue when maintenance jobs run alongside user operations.  Maintenance operations can potentially use a lot of resource which may, in extreme cases, affect other jobs. To address this, in 11g, maintenance operations are closely linked to resource manager to manage the resources that are used and share them more efficiently.



From the
Oracle Database New Features Guide
11g Release 1 (11.1)
Part Number B28279-03

"This feature ensures that work during maintenance operations is not affected and that user activity gets the necessary resources to complete."

By spreading the workload over multiple weeknights and managing the resource usage, this feature provides the flexibility to allow maintenance jobs to complete in a timely fashion and for user operations to be unaffected.




Monday, September 19, 2011

DBMS_JOB MGMT_JOB_ENGINE.apply_purge_policies() causes high CPU

DBMS_JOB MGMT_JOB_ENGINE.apply_purge_policies() causes high CPU 




The dbms_job MGMT_JOB_ENGINE.apply_purge_policies() in the Repository Database consumes very high CPU resources when it is being executed. This can inturn affect other operations in the repository database like Loader, causing a high backlog.

This issue was investigated in Bug 8517252 : SQL FROM MGMT_JOB_ENGINE.APPLY_PURGE_POLICIES() CAUSING HIGH CPU USAGE.

The SQL causing the high cpu consumption was found to be : 
SELECT execution_id
FROM MGMT_JOB_EXEC_SUMMARY e, MGMT_JOB j
WHERE e.job_id = j.job_id AND j.is_corrective_action = 0
AND status IN (5,4,3,18,8)
AND (CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE) - e.start_time) > (:tf)
AND ROWNUM < 500 ORDER BY start_time desc


To resolve this issue, apply Patch 8517252 on top of the 10.2.0.5 OMS.
This is a generic patch applicable to any operating system.


Friday, September 16, 2011

Automatic Shared Memory Management ( ASMM ) In Oracle 10g

Automatic Shared Memory Management ( ASMM ) In Oracle 10g


Concept


In Oracle version 9.0.1, some SGA parameters have been defined as 'Dynamic' parameters. The "ALTER SYSTEM" command may be used to grow/shrink their current values.

The whole SGA size is limited by the SGA_MAX_SIZE parameter that is the defined upper limit. Each SGA parameter is allocated in terms of Granules that is the Allocation Unit. The Granule size will depend on the SGA_MAX_SIZE value and hardware platform.

Depending on the 9i version, the following parameters have been defined as dynamic parameters:

9.0.1: Shared Pool and Default Buffer Cache
9.2.0: Shared Pool, Default Buffer Cache, Large Pool

When you grow/shrink the dynamic size of one of the above parameters, the freed memory won't be reallocated to another dynamic component automatically. You must do it manually if needed.

In 10G, the ASMM has been introduced to relieve DBAs from sizing some parts of the SGA by them.

When enabled, it lets Oracle decide of the right size for some components of the SGA:

  • Database buffer cache (Default pool)
  • Shared pool
  • Large pool
  • Java pool
  • 10gR2 the streams pool is included

They are called auto-tuned parameters.

The main objectives to justify this new functionality are:



Distribute the available memory depending of the current Workload. The Automatic Shared Memory Management feature uses a new background process named Memory Manager (MMAN). MMAN serves as the SGA Memory Broker and coordinates the sizing of the memory components. The SGA Memory Broker keeps track of the sizes of the components and pending resizes operations.
.
Enhance the memory usage depending of the activity. Avoid the memory errors like ORA-4031.

Configuration


The ASMM is driven by one init parameter: SGA_TARGET.
When set to 0, the ASMM is disabled and you run with the old method, so you need to define the above auto-tuned parameters by yourself.
The default value for SGA_TARGET is 0 so ASMM disabled.

The conditions to enable the ASMM mechanism are:
STATISTICS_LEVEL=TYPICAL or ALL
SGA_TARGET > 0
When you use a value greater than 0, the ASMM is enabled and the memory will be spread between all components: auto-tuned and manual parameters.

The SGA_TARGET value will therefore define the memory size sharable between auto-tuned and manual parameters.
The manual parameters are:
DB__CACHE_SIZE
DB_nK_CACHE_SIZE (non default block size)
LOG_BUFFER
FIXED SGA
STREAMS_POOL_SIZE
Amonst these manual parameters, some of them are modifiable or fixed (defined at startup only):
Modifiable: DB__CACHE_SIZE, STREAMS_POOL_SIZE
Fixed: DB_nK_CACHE_SIZE, FIXED SGA, LOG_BUFFER

Based on workload information, automatic shared memory tuning:

  • Captures statistics periodically in the background
  • Uses the different memory advisories
  • Performs "what-if" analysis to determine best distribution of memory
  • Moves memory to where it is most needed
  • Has no need to configure parameters for the worst-case scenario
  • Resurrects component sizes from last shutdown if SPFILE is used

Tuesday, September 13, 2011

Memory Management in Oracle


Memory Management In Oracle

Overtime Oracle has invested a great deal of time and effort in managing the memory more efficiently and transparently for the end-user.

Therefore it is advisable to use the automation features as much as possible.

Oracle 9i

Starting from Oracle 9i Oracle introduced the parameters: 
workarea_size_policy = [AUTO | MANUAL]
pga_aggregate_target =

This allows you define 1 pool for the PGA memory, which will be shared across sessions. When you often receive ORA-4030 errors, then this can be an indication that this value is specified too low. 


Oracle 10g

Automatic Shared Memory Management (ASMM) was introduced in 10g. The automatic shared memory management feature is enabled by setting the SGA_TARGET parameter to a non-zero value.

This feature has the advantage that you can share memory resources among the different components.  Resources will be allocated and deallocated as needed by Oracle automatically.

Automatic PGA Memory management is still available through the 'workarea_size_policy' and
 'pga_aggregate_target' parameters.



Oracle 11g

Automatic Memory Management (AMM) is being introduced in 11g. This enables automatic tuning of PGA and SGA with use of two new parameters named MEMORY_MAX_TARGET and MEMORY_TARGET.

Friday, September 2, 2011

Cursor_Sharing Parameter in Oracle


Cursor_Sharing Parameter in Oracle

CURSOR_SHARING determines what kind of SQL statements can share the same cursors. It is an init.ora parameter which decides whether a SQL send from user is a candidate for fresh parsing or will use an existing plan.

It has three values

FORCE, SIMILAR, EXACT (Default)

EXACT

Setting CURSOR_SHARING to EXACT allows SQL statements to share the SQL area only when their texts match exactly. This is the default behavior. Using this setting, similar statements cannot shared; only textually exact statements can be shared.

SIMILAR and FORCE

When CURSOR_SHARING is used set to SIMILAR or FORCE , Oracle first checks the shared pool to see if there is an identical statement in the shared pool. If an identical statement is not found, then Oracle searches for a similar statement in the shared pool. If the similar statement is there, then the parse checks continue to verify the executable form of the cursor can be used. If the statement is not there, then a hard parse is necessary to generate the executable form of the statement.

Using CURSOR_SHARING = SIMILAR (or FORCE) can significantly improve cursor sharing on some applications that have many similar statements, resulting in reduced memory usage, faster parses, and reduced latch contention.
(Source: Oracle Wiki)


The difference between SIMILAR and FORCE is that SIMILAR forces similar statements to share the SQL area without deteriorating execution plans. Setting CURSOR_SHARING to FORCE forces similar statements to share the executable SQL area, potentially deteriorating execution plans. Hence, FORCE should be used as a last resort, when the risk of suboptimal plans is outweighed by the improvements in cursor sharing.

Note:  The cursor_sharing=similar option has been deprecated in Oracle 11g and will be removed in version 12 per Oracle Metalink Note 1169017.1
Soruce (Oracle Metalink Note)