Segments Shrink in Oracle 10g

Segments Shrink in Oracle 10g

Shrink segments online and in real time.

Segments that undergo significant data manipulation language (DML) activity, such as UPDATE and DELETE operations, can become sparsely populated, with chunks of free space within their data blocks. Besides simply wasting space, sparsely populated segments can also lead to poor performance, because operations such as a full table scan will need to scan more blocks than necessary to retrieve data.

Before Oracle Database 10g, you could reclaim the free segment space by dropping the table, re-creating it, and then reloading the data. You could also use the ALTER TABLE MOVE command to move the table to a different tablespace. Both of these processes, however, must occur with the table offline. Online table reorganization is another way to reclaim space, but it requires significant disk space.

Now Shrink

With Oracle Database 10g, you can now shrink segments directly, without taking the tablespaces or the objects offline. The process of shrinking a segment includes two key phases:

• Segment data is compacted. Through a series of INSERT and DELETE statements (during which DML-compatible locks are held on individual rows or blocks of the table), the segment data is moved as far to the beginning of the segment as possible. Given that rowids change, you must enable row movement and also disable any triggers based on rowid for table segments you want to shrink.
• High-water mark (HWM) is adjusted to an appropriate location (exclusive locks are held on the data at this point), and unused space is deallocated from the segment, so it is available for the tablespace to reallocate to other objects as needed.

The shrink capability is implemented in Oracle Database 10g as an optional SHRINK SPACE clause on the ALTER… SQL statements for the associated object. The SHRINK SPACE clause performs both phases of the shrink process. You can also use the optional COMPACT clause in conjunction with the SHRINK SPACE clause to perform just the first phase—the compacting—by itself, to defer the locking of the second phase, for example, and then issue the SHRINK SPACE clause (without COMPACT) later to complete the process, as in

ALTER TABLE JOHN.PRODUCT

SHRINK SPACE COMPACT

and later, run

ALTER TABLE JOHN.PRODUCT

SHRINK SPACE

But how do you identify which segments to shrink? And how do you make this process a regular part of maintaining system performance? Fortunately, with Oracle Database 10g, segment usage data is captured, by default, along with all the other statistical data captured by the Automatic Workload Repository (AWR) infrastructure. The segment usage information is easy to obtain with the advisor infrastructure (DBMS_ADVISOR)—specifically, the Segment Advisor, a simple-to-use new feature provided with Oracle Database 10g that identifies which segments have significant free space, and, therefore, are good candidates for segment shrinking.

Getting Started with the Oracle Database 10g Segment Advisor

You can run the Segment Advisor against specific objects (tables, indexes, and materialized views), against an entire tablespace, or against multiple tablespaces.

As with the other advisors provided by Oracle Database 10g, you can launch the Segment Advisor by using Oracle Enterprise Manager Database Control or the DBMS_ADVISOR built-in PL/SQL package.

You can launch the Segment Advisor from several places in the Enterprise Manager Database Control (or Grid Control) browser, such as Advisor Central (from the Enterprise Manager home page), or from the specific database object management page (the Tables or Index page, for example) by selecting Run Segment Advisor from the drop-down menu, with the specific table, index, or materialized view selected.

You can proactively run the Segment Advisor against the specific table you suspect may have a segment usage issue. For example, you might run the Segment Advisor if you’ve just purged 5,000 old accounts from a 25,000-customer table or run it against a complete tablespace you use as working storage, to stage or cleanse data for a data warehouse.

Whether you launch the Segment Advisor from Advisor Central or within the context of a specific object or tablespace, you initiate a four-page sequence of configuration pages in which you define a Segment Advisor task for submission to the job subsystem.

The four pages of the Segment Advisor wizard step you through the settings for defining the Segment Advisor task’s parameters, including the following:

Advisor mode. Can be run in Limited or Comprehensive mode. In Comprehensive mode, when the task runs, the Segment Advisor samples the objects being analyzed, in addition to using already gathered statistics on the objects from the AWR. Limited mode relies on existing statistics only—so if your statistics aren’t current (or don’t exist at all), Segment Advisor will generate no recommendations.

Time. Limited or unlimited time for analysis by the Segment Advisor task.

Schedule. Whether to run the task immediately or schedule it for later, such as during a maintenance window (this is the default), and whether the task repeats. You can also change the system-generated task name on this page.

Review. Includes a list of your Segment Advisor settings and a Submit button to submit the new task to the job scheduler.

The task is also stored as an object in the AWR (the default retention is for 30 days), so you can rerun it anytime later.

Shrink the Segment

Once the task completes its analysis, you can review the advisor’s findings on the Recommendations page, available from Advisor Central. The page lists all the segments (table, index, and so on) that constitute the object under review. The default view (“View Segments Recommended to Shrink”) lists any segments that have free space you can reclaim.

In the example the Segment Advisor recommends shrinking several segments from different tablespaces (owned by different users). The page shows the segment name, the space allocated to the segment, the used space, the reclaimable space, and recommendations such as “Perform shrink, estimated savings is 14284326 bytes.”

Two shrink options are available on this page. In this example, with an index segment selected, “Compact Segments and Release Space” is equivalent to:

ALTER INDEX <INDEX_NAME> SHRINK SPACE

and “Compact Segments” is equivalent to

ALTER INDEX <INDEX_NAME> SHRINK

SPACE COMPACT

You can choose to implement the recommendation directly from this page, selecting as many tables, indexes, or other listed objects as you like and then clicking on the Schedule Implementation button.

The next page lets you set the time for shrinking the segment or segments selected. Your selection then goes to the job subsystem as a series of SQL statements executed immediately or per your schedule.

Whether you implement a recommendation or simply select Shrink Segment from the drop-down menu on another page of Enterprise Manager, the appropriate SQL statements are submitted to the job subsystem to shrink the segment.

You can also select “View other Segments” to view the status of all other segments and obtain this same level of detail, but in the case of segments in which the advisor doesn’t find extra space, recommendations include information such as “The free space in the object is less than the size of the last extent” or “The object has less than 1% free space, it is not worth shrinking.”

Using the DBMS_ADVISOR Built-in Package

The Segment Advisor wizard provided by Enterprise Manager Database Control (and Grid Control) uses the functionality of the DBMS_ADVISOR built-in PL/SQL package of the Oracle database. If you prefer, you can call the various subroutines of this package from the command line or by using scripts. Listing 1 shows a script that calls DBMS_ADVISOR and creates a Segment Advisor task.

The DBMS_ADVISOR built-in PL/SQL package, new with Oracle Database 10g, lets you create the complete array of advisor tasks, such as SQL Tuning Advisor and SQL Access Advisor, many of which previous Talking Tuning columns have discussed. The Segment Advisor is another subsystem (or set of procedures) available in that package.

Code Listing 1: Script for creating a Segment Advisor task on a table

VARIABLE ID NUMBER;

BEGIN

DECLARE

TASK_ID NUMBER;

NAME VARCHAR2(100) ;

DESCR VARCHAR2(500) ;

OBJID NUMBER;

BEGIN

NAME := ” ;

DESCR := ‘SEGMENT ADVISOR ON A TABLE’;

DBMS_ADVISOR.CREATE_TASK(‘SEGMENT ADVISOR’, :ID, NAME, DESCR, NULL);

DBMS_ADVISOR.CREATE_OBJECT(NAME, ‘TABLE’, ‘JOHN’,’PRODUCT’, NULL, NULL, OBJID);

DBMS_ADVISOR.SET_TASK_PARAMETER(NAME, ‘RECOMMEND_ALL’, ‘TRUE’);

DBMS_ADVISOR.EXECUTE_TASK(NAME);

END;

END;

Conclusion

Whether you use Enterprise Manager or the DBMS_ADVISOR package, the Segment Advisor is easy to run and lets you quickly find areas in your database where you can reclaim space. It finds the pockets of reclaimable space in segments and then, rather than making you unload the table and rebuild it offline, lets you shrink segments online and in place. No additional storage is needed, because the operation effectively does an INSERT and a DELETE right in the object itself.

Oracle DBMS_RESOURCE_MANAGER

Version 10.2

General Note: Resource Manager requires a massive over-demand on CPU before the expected behaviour starts to show: Generally speaking a 300% or 400% CPU load to see that the actual split gets closer to expectation, with the lower-privileged processes losing time in a wait state whose name includes ‘resmgr: ….{something}..’ Source {ORACLE_HOME}/rdbms/admin/dbmsrmad.sql First Available 8.1.5 Constants Name Data Type Value client_machine VARCHAR2(30) ‘CLIENT_MACHINE’ client_os_user VARCHAR2(30) ‘CLIENT_OS_USER’ client_program VARCHAR2(30) ‘CLIENT_PROGRAM’ module_name VARCHAR2(30) ‘MODULE_NAME’ module_name_action VARCHAR2(30) ‘MODULE_NAME_ACTION’ oracle_user VARCHAR2(30) ‘ORACLE_USER’ service_module VARCHAR2(30) ‘SERVICE_MODULE’ service_module_action VARCHAR2(30) ‘SERVICE_MODULE_ACTION’ service_name VARCHAR2(30) ‘SERVICE_NAME’ Dependencies dba_rsrc_consumer_groups dbms_sql dba_rsrc_consumer_group_privs dbms_sys_error dba_rsrc_group_mappings dbms_sys_sql dba_rsrc_plans default_consumer_group dba_users gv_$rsrc_consumer_group dbms_assert resource_consumer_group$ dbms_prvtrmie resource_plan$ dbms_resource_manager_privs resource_plan_directive$ dbms_rmin v_$rsrc_consumer_group_cpu_mth CLEAR_PENDING_AREA Clears the work area for the resource manager dbms_resource_manager.clear_pending_area; exec dbms_resource_manager.clear_pending_area; CREATE_CONSUMER_GROUP Create entries that define resource consumer groups dbms_resource_manager.create_consumer_group( consumer_group IN VARCHAR2, comment        IN VARCHAR2, cpu_mth        IN VARCHAR2 DEFAULT ‘ROUND-ROBIN’); – alternate cpu_mth is RUN-TO-COMPLETION See Demo CREATE_PENDING_AREA Creates a work area for changes to resource manager objects dbms_resource_manager.create_pending_area; See Demo CREATE_PLAN Create entries that define resource plans dbms_resource_manager.create_plan( plan                      IN VARCHAR2, comment                   IN VARCHAR2, cpu_mth                   IN VARCHAR2 DEFAULT ‘EMPHASIS’, active_sess_pool_mth      IN VARCHAR2 DEFAULT ‘ACTIVE_SESS_POOL_ABSOLUTE’, parallel_degree_limit_mth IN VARCHAR2 DEFAULT ‘PARALLEL_DEGREE_LIMIT_ABSOLUTE’, queueing_mth              IN VARCHAR2 DEFAULT ‘FIFO_TIMEOUT’); cpu_mth: Use ‘EMPHASIS’ for multi-level plans and ‘RATIO’ for single level plans See Demo CREATE_PLAN_DIRECTIVE Create resource plan directives dbms_resource_mananger.create_plan_directive( plan                     IN VARCHAR2, group_or_subplan         IN VARCHAR2, comment                  IN VARCHAR2, cpu_p1                   IN NUMBER DEFAULT NULL, cpu_p2                   IN NUMBER DEFAULT NULL, cpu_p3                   IN NUMBER DEFAULT NULL, cpu_p4                   IN NUMBER DEFAULT NULL, cpu_p5                   IN NUMBER DEFAULT NULL, cpu_p6                   IN NUMBER DEFAULT NULL, cpu_p7                   IN NUMBER DEFAULT NULL, cpu_p8                   IN NUMBER DEFAULT NULL, active_sess_pool_p1      IN NUMBER DEFAULT NULL, queueing_p1              IN NUMBER DEFAULT NULL, parallel_degree_limit_p1 IN NUMBER DEFAULT NULL, switch_group             IN VARCHAR2 DEFAULT NULL, switch_time              IN NUMBER   DEFAULT NULL, switch_estimate          IN BOOLEAN  DEFAULT FALSE, max_est_exec_time        IN NUMBER DEFAULT NULL, undo_pool                IN NUMBER DEFAULT NULL, max_idle_time            IN NUMBER DEFAULT NULL, max_idle_blocker_time    IN NUMBER DEFAULT NULL, switch_time_in_call      IN NUMBER DEFAULT NULL); See Demo CREATE_SIMPLE_PLAN Create a single-level resource plan containing up to eight consumer groups in one step dbms_resource_manager.create_simple_plan( SIMPLE_PLAN     IN VARCHAR2 DEFAULT, CONSUMER_GROUP1 IN VARCHAR2 DEFAULT, GROUP1_CPU      IN NUMBER   DEFAULT, CONSUMER_GROUP2 IN VARCHAR2 DEFAULT, GROUP2_CPU      IN NUMBER   DEFAULT, CONSUMER_GROUP3 IN VARCHAR2 DEFAULT, GROUP3_CPU      IN NUMBER   DEFAULT, CONSUMER_GROUP4 IN VARCHAR2 DEFAULT, GROUP4_CPU      IN NUMBER   DEFAULT, CONSUMER_GROUP5 IN VARCHAR2 DEFAULT, GROUP5_CPU      IN NUMBER   DEFAULT, CONSUMER_GROUP6 IN VARCHAR2 DEFAULT, GROUP6_CPU      IN NUMBER   DEFAULT, CONSUMER_GROUP7 IN VARCHAR2 DEFAULT, GROUP7_CPU      IN NUMBER   DEFAULT, CONSUMER_GROUP8 IN VARCHAR2 DEFAULT, GROUP8_CPU      IN NUMBER   DEFAULT); TBD DELETE_CONSUMER_GROUP Delete entries that define resource consumer groups dbms_resource_manager.delete_consumer_group( consumer_group IN VARCHAR2); See Demo DELETE_PLAN Deletes the specified plan as well as all the plan directives to which it refers dbms_resource_manager.delete_plan(plan IN VARCHAR2); exec dbms_resource_manager.delete_plan(‘UW_PLAN’); DELETE_PLAN_CASCADE Deletes the specified plan as well as well as its descendants (plan directives, subplans, consumer groups) dbms_resource_manager.delete_plan_cascade(plan IN VARCHAR2); See Demo DELETE_PLAN_DIRECTIVE Delete resource plan directives dbms_resource_manager.delete_plan_directive( plan             IN VARCHAR2, group_or_subplan IN VARCHAR2); See Demo SET_CONSUMER_GROUP_MAPPING Adds, deletes, or modifies entries that map sessions to consumer groups, based on the session’s login and runtime attributes dbms_resource_manager.set_consumer_group_mapping( attribute      IN VARCHAR2, value          IN VARCHAR2, consumer_group IN VARCHAR2 DEFAULT NULL); TBD SET_CONSUMER_GROUP_MAPPING_PRI Creates the session attribute mapping priority list dbms_resource_manager.set_consumer_group_mapping_pri( explicit              IN NUMBER, oracle_user           IN NUMBER, service_name          IN NUMBER, client_os_user        IN NUMBER, client_program        IN NUMBER, client_machine        IN NUMBER, module_name           IN NUMBER, module_name_action    IN NUMBER, service_module        IN NUMBER, service_module_action IN NUMBER); TBD SET_INITIAL_CONSUMER_GROUP Assigns the initial resource consumer group for a user dbms_resource_manager.set_initial_consumer_group( user           IN VARCHAR2, consumer_group IN VARCHAR2); See Demo SUBMIT_PENDING_AREA Submits pending changes for the resource manager dbms_resource_manager.submit_pending_area; See Demo SWITCH_CONSUMER_GROUP_FOR_SESS Changes the resource consumer group of a specific session dbms_resource_manager.switch_consumer_group_for_sess( session_id     IN NUMBER, session_serial IN NUMBER, consumer_group IN VARCHAR2); TBD SWITCH_CONSUMER_GROUP_FOR_USER Changes the resource consumer group for all sessions with a given user name dbms_resource_manager.switch_consumer_group_for_user( user           IN VARCHAR2, consumer_group IN VARCHAR2); exec dbms_resource_manager.switch_consumer_group_for_user( ‘UWCLASS’, ‘rpt_writers_grp’); SWITCH_PLAN Sets the current resource manager plan dbms_resource_manager.switch_plan( plan_name                     IN VARCHAR2, sid                           IN VARCHAR2 DEFAULT ‘*’, allow_scheduler_plan_switches IN BOOLEAN  DEFAULT TRUE); TBD UPDATE_CONSUMER_GROUP Update entries that define resource consumer groups dbms_resource_manager.update_consumer_group( consumer_group IN VARCHAR2, new_comment    IN VARCHAR2 DEFAULT NULL, new_cpu_mth    IN VARCHAR2 DEFAULT NULL); See Demo UPDATE_PLAN Update entries that define resource plans dbms_resource_manager.update_plan( plan                          IN VARCHAR2, new_comment                   IN VARCHAR2 DEFAULT NULL, new_cpu_mth                   IN VARCHAR2 DEFAULT NULL, new_active_sess_pool_mth      IN VARCHAR2 DEFAULT NULL, new_parallel_degree_limit_mth IN VARCHAR2 DEFAULT NULL, new_queueing_mth              IN VARCHAR2 DEFAULT NULL); TBD UPDATE_PLAN_DIRECTIVE Update resource plan directives dbms_resource_manager.update_plan_directive( plan                         IN VARCHAR2, group_or_subplan             IN VARCHAR2, new_comment                  IN VARCHAR2 DEFAULT NULL, new_cpu_p1                   IN NUMBER DEFAULT NULL, new_cpu_p2                   IN NUMBER DEFAULT NULL, new_cpu_p3                   IN NUMBER DEFAULT NULL, new_cpu_p4                   IN NUMBER DEFAULT NULL, new_cpu_p5                   IN NUMBER DEFAULT NULL, new_cpu_p6                   IN NUMBER DEFAULT NULL, new_cpu_p7                   IN NUMBER DEFAULT NULL, new_cpu_p8                   IN NUMBER DEFAULT NULL, new_active_sess_pool_p1      IN NUMBER DEFAULT NULL, new_queueing_p1              IN NUMBER DEFAULT NULL, new_parallel_degree_limit_p1 IN NUMBER DEFAULT NULL, new_switch_group             IN VARCHAR2 DEFAULT NULL, new_switch_time              IN NUMBER DEFAULT NULL, new_switch_estimate          IN BOOLEAN DEFAULT FALSE, new_max_est_exec_time        IN NUMBER DEFAULT NULL, new_undo_pool                IN NUMBER DEFAULT NULL, new_max_idle_time            IN NUMBER DEFAULT NULL, new_max_idle_blocker_time    IN NUMBER DEFAULT NULL, new_switch_time_in_call      IN NUMBER DEFAULT NULL); TBD VALIDATE_PENDING_AREA Validates pending changes for the resource manager dbms_resource_manager.validate_pending_area; See Demo Demos Create Plan 1 Demonstration conn / as sysdba desc dba_users SELECT username, initial_rsrc_consumer_group FROM dba_users ORDER BY 1; GRANT select ON dba_rsrc_consumer_groups TO uwclass; — create pointy-haired boss user CREATE USER phb IDENTIFIED BY phb DEFAULT TABLESPACE uwdata TEMPORARY TABLESPACE temp QUOTA 1M ON uwdata; GRANT create session TO phb; SELECT username, initial_rsrc_consumer_group FROM dba_users ORDER BY 1; desc dba_rsrc_consumer_group_privs SELECT * FROM dba_rsrc_consumer_group_privs; set linesize 121 col plan format a20 col cpu_method format a10 col status format a10 col comments format a40 desc dba_rsrc_plans SELECT plan, cpu_method, comments, status, mandatory FROM dba_rsrc_plans; col cpu_method format a15 col consumer_group format a25 col comments format a45 desc dba_rsrc_consumer_groups SELECT * FROM dba_rsrc_consumer_groups; col value format a20 desc dba_rsrc_group_mappings SELECT * FROM dba_rsrc_group_mappings; exec dbms_resource_manager_privs.grant_system_privilege(‘UWCLASS’, ‘ADMINISTER_RESOURCE_MANAGER’, FALSE); conn uwclass/uwclass – create a pending area exec dbms_resource_manager.create_pending_area; – create two consumer groups: Workers and Managers exec dbms_resource_manager.create_consumer_group(‘Workers’, ‘Those that do actual work’); exec dbms_resource_manager.create_consumer_group(‘Managers’, ‘Those that don”t but take all the credit’); SELECT * FROM dba_rsrc_consumer_groups; exec dbms_resource_manager.update_consumer_group(‘Managers’, ‘The Cartesian products of the IT world’); SELECT * FROM dba_rsrc_consumer_groups; – create resource management plan exec dbms_resource_manager.create_plan(‘UW_PLAN’,'Demo Resource Plan’, ‘RATIO’); – create plan directives exec dbms_resource_manager.create_plan_directive(plan=>’UW_PLAN’,  group_or_subplan=>’Workers’, comment=>’Can Grab All The CPU’, cpu_p1=>100); exec dbms_resource_manager.create_plan_directive(plan=>’UW_PLAN’, group_or_subplan=>’Managers’, comment=>’Give Managers Little Weight’, cpu_p1=>1); – validate the pending area exec dbms_resource_manager.validate_pending_area; exec dbms_resource_manager.create_plan_directive(plan=>’UW_PLAN’, group_or_subplan=>’OTHER_GROUPS’, comment=>’Testing’, cpu_p2=>0); – validate the pending area exec dbms_resource_manager.validate_pending_area; – oops … go back and redo correctly – delete plan directives exec dbms_resource_manager.delete_plan_directive(‘UW_PLAN’, ‘Managers’); – recreate plan directives exec dbms_resource_manager.create_plan_directive(plan=>’UW_PLAN’, group_or_subplan=>’Managers’, comment=>’Infinite Weight’, cpu_p1=>0); – revalidate the pending area exec dbms_resource_manager.validate_pending_area; – submit the pending area exec dbms_resource_manager.submit_pending_area; SELECT plan, cpu_method, comments, status, mandatory FROM dba_rsrc_plans; SELECT * FROM dba_rsrc_consumer_groups; exec dbms_resource_manager_privs.grant_switch_consumer_group( grantee_name=>’PHB’, consumer_group=>’Managers’, grant_option=>FALSE); exec dbms_resource_manager.set_initial_consumer_group(user => ‘SCHEDULE’, consumer_group=>’Managers’); alter system set resource_manager_plan = ‘UW_PLAN’; conn schedule/schedule /* So PHB is in a group that should have no CPU. Does this mean PHB can’t do anything? */ SELECT COUNT(*) FROM all_tables; /* PHB is fine. There’s plenty of free CPU so even though the group that PHB group belongs to “looks” like it should have no CPU, that doesn’t have any effect in this case. */ Create Plan 2 Demonstration /* Resource Manager is only effective (from a CPU point of view) once CPU utilization starts to max out. Oracle takes the attitude that if there is spare CPU capacity, there is no need to limit usage. Only when the resource becomes scarce does Oracle restrict access. */ BEGIN – create pending area dbms_resource_manager.create_pending_area; dbms_resource_manager.create_plan(plan=>’TEST2′, comment=>’Use 10g Feature’); dbms_resource_manager.create_plan_directive(plan=>’TEST2′, group_or_subplan=>’HAVES’, comment=>’Testing’, cpu_p1=>100); dbms_resource_manager.create_plan_directive(plan=>’TEST2′, group_or_subplan=>’NOTS’, comment=>’Testing’, cpu_p1=>0, max_est_exec_time=>0); /* Prevent the have Managers from running any operation that has an estimated execution time > 0. */ dbms_resource_manager.create_plan_directive(plan=>’TEST2′, group_or_subplan=>’OTHER_GROUPS’, comment=>’Testing’, cpu_p2=>100); dbms_resource_manager.validate_pending_area; dbms_resource_manager.submit_pending_area; dbms_resource_manager_privs.grant_switch_consumer_group( grantee_name=>’PHB’, consumer_group=>’Managers’, grant_option=>FALSE); dbms_resource_manager.set_initial_consumer_group(user => ‘SCHEDULE’, consumer_group=>’Managers’); END; / ALTER SYSTEM SET resource_manager_plan = test2; conn schedule/schedule SELECT COUNT(*) FROM huge_table; ERROR at line 1: ORA-07455: estimated execution time (56 secs), exceeds limit (0 secs) /* However, the problem here is that Oracle kinda rounds *down* the estimate execution time so providing I’m not being too greedy with my resources, Oracle can be a little lenient … */ SELECT COUNT(*) FROM small_table; Drop Plan Demonstration conn / as sysdba – create pending area exec dbms_resource_manager.create_pending_area; – remove admin privilege from uwclass exec dbms_resource_manager_privs.revoke_system_privilege( ‘UWCLASS’, ‘ADMINISTER_RESOURCE_MANAGER’); — delete resource plan exec dbms_resource_manager.delete_plan_cascade(‘UW_PLAN’); — validate pending area exec dbms_resource_manager.validate_pending_area; SELECT username, initial_rsrc_consumer_group FROM dba_users ORDER BY 1; – switch consumer group for user SCHEDULES exec dbms_resource_manager.switch_consumer_group_for_user( ‘SCHEDULE’, ‘DEFAULT_CONSUMER_GROUP’); BEGIN dbms_resource_manager.create_pending_area; dbms_resource_manager.delete_plan_cascade(‘UW_PLAN’); dbms_resource_manager.validate_pending_area; END; / — submit pending area exec dbms_resource_manager.submit_pending_area; – revoke system privilege from user exec dbms_resource_manager_privs.revoke_system_privilege(‘UWCLASS’);

Database Resource Manager (DRM)

Although you can manage resources via profiles a better option is to use the resource manager, by creating resource plans which specify how much of your resources should go to the various consumer groups, you can prioritize users and jobs. The resource manager can kill long running jobs, switch jobs to higher priority all automatically, the DBA can also manually switch users between resource groups.

There are four elements to the database resource manager (DRM)

• resource consumer group – group similar users together who have a similar resource needs.
• resource plan – lays out how resource consumer groups are allocated resources, only one plan can be active and can have up to 8 levels but no sub plans
• resource allocation method – dictates the specific method you choose to use to allocate resources like CPU.
• resource plan directive – links a resource plan to a specific resource consumer group, can have sub-levels.

In order to administer the db resource manager you must require the administer_resource_manager privilege, by default the dba role has this privilege.

There are two API’s that can be used to administer the db resource manager, Enterprise Manager also uses these API’s.

DBMS_RESOURCE_MANAGER_PRIVS	used to put/remove users into consumer groups and grant the system privilege
DBMS_RESOURCE_MANAGER	used to create consumer groups, plans and directives, switching active groups

The following steps are what you need to use the resource manager

1. Create a pending area
2. Create a resource consumer group
3. Create a resource plan
4. Create a plan directive
5. Validate the pending area
6. Submit the pending area

Pending Area

You need to a pending area to validate changes before you implement them, it serves as a work area, when you submit the pending it will be stored in the data dictionary, until then you make the changes to the pending area.

Creating	exec dbms_resource_manager.create_pending_area;
Clearing	exec dbms_resource_manager.clear_pending_area;

Resource Consumer Group

A resource group is a container were you can group together similar users that use similar resources. Users are initially assigned one group but can be switched to another group. You need the following three parameters to create the group

• consumer_group – the name of the consumer group
• comment – any useful comment
• cpu_mth – values are round-robin (default) or run-to-completion

There are a number of default groups available

sys_group	is for the database administrators
default_consumer_group	users who have not been specifically granted membership of any other group, by default all users (not sys or system) are in this group and membership is active when they first create a session
other_groups	used as a catch-all for any session not listed in a group
low_group	is used for low priority sessions
auto_task_consumer_group	used for running system maintenance jobs

 

creating	dbms_resource_manager.create_consumer_group( consumer_group=> ‘developers’, comment=> ‘application developers’);
modifying	dbms_resource_manager.update_consumer_group( consumer_group=>’developers’, cpu_mth=>’run-to-completion’);
removing	dbms_resource_manager.delete_consumer_group( consumer_group=>’developers’);
displaying	select consumer_group, status, from dba_rsrc_consumer_group;

Resource Plans and Plan Directives

The resource plan lays out how resource consumer groups are allocated resources, they can be multi-level (contains sub-plans) or single-level, resource plans can set limits of the following

CPU_MTH	allocate cpu usage among the resource consumer groups, the default is emphasis (multi-level) and it uses percentages to allocate CPU among various consumer groups. The alternative method called ratio (multi-level and single-level) uses ratios instead. Note: CPU_MTH option is the only option that can be added to a sub-plan
ACTIVE_SESS_POOL_MTH	determines the limit on number of active sessions in a resource consumer group
PARALLEL_DEGREE_LIMIT_MTH	determines the degree of parallelism within the consumer group, however a user can requests all and you have no control, only the consumer group is limited not the users inside the group.
QUEUEING_MTH	determines the order in which queued sessions will execute.

There are a number of default resource plans that already exist

internal_plan (default)	one directive that states other_groups can have 100% cpu time, means all users are equal
system_plan	sys_group level1 100% other_groups level2 100% low_group level3 100%
internal_quiesce	freezes all sessions except those of the sys_group, basically set all groups to zero sessions

There are a number of plan directive resources that can be controlled

CPU	You can use multiple levels of CPU resource allocation to prioritize CPU usage.
SESSIONS	limit the number of open sessions within the resource group
DEGREE_OF_PARALLELISM	used to run commands in parallel but you have no control over this, so if a user requests 50 he gets parallel processes but you can limit the total parallel execution servers within a group
AUTOMATIC CONSUMER GROUP SWITCHING	you can specify that under some conditions the database will automatically switch sessions to another consumer group.
UNDO USAGE	transactions will hang for all users of that group once the undo has reached its limit.
IDLE TIME LIMIT	can be idle time and idle time / if it holds open any record locks. The session is terminated and any transactions are rollback
EXECUTION TIME	can limit a jobs execution time basically cancels the job

 

Displaying plan in use	select value from v$parameter where name = ‘resource_manager_plan’; select * from v$rsrc_plan;
creating	exec dbms_resource_manager.create_plan( plan=>’day’, comment=>’plan for normal working hours’ cpu_mth=>’ratio’);
removing	exec dbms_resource_manager.delete_plan(plan=>’day’); exec dbms_resource_manager.delete_plan_cascade(plan=>’day’); Note: the cascade option will remove all sub-plans as well
Modifying	exec dbms_resource_manager.update_plan( plan=>’day’, new_comment=>’New Comment’);
Creating plan directive	exec dbms_resource_manager.create_plan_directive(plan=>’day’, group_or_subplan=>’developers’, cpu_p2=>50); exec dbms_resource_manager.create_plan_directive ( plan=>’prod_plan’, group_or_subgroup=>’dss_group’, comment=>’Limit idle time’, max_idle_time=>900, max_idle_blocker_time=>300);
updating plan directive	exec dbms_resource_manager.update_plan_directive(plan=>’day’, group_or_subplan=>’developers’, new_switch_estimate=>false);
delete plan directive	exec dbms_resource_manager.delete_plan_directive(plan=>’day’, group_or_subplan=>’developers’);
activating the plan	alter system set resource_manager_plan = daytime;
forcing the plan	alter system set resource_manager_plan = ‘force:daytime’; Note: restricts the setting from being changed by the scheduler, it has to be done manually

Switching groups

Switch User	exec dbms_resource_manager_privs.grant_switch_consumer_group(‘vallep’,'developers’,false);
Switch role	exec dbms_resource_manager_privs.grant_switch_consumer_group(‘prog_role’,'developers’,false);
Revoke switch	exec dbms_resource_manager_privs.revoke_switch_consumer_group(‘prog_role’,'developers’);
Switch all users	exec dbms_resource_manager.switch_consumer_group_for_user(user=>’TEST’, consumer_group=>’OLTP’);
Switch particular session	exec dbms_resource_manager.switch_consumer_group_for_sess(session_id=>209, session_serial=>10223, consumer_group=>’OLTP’);
use dbms_session	exec dbms_session.switch_current_consumer_group(‘marketing’, original_group, false); Note: (original_group stores old group info) Do some stuff exec dbms_session.switch_current_consumer_group(original_group, junk, false);
Setup initial group	exec dbms_resource_manager.set_initial_consumer_group(‘TEST’, ‘OLTP’);
Display resource group currently assigned to	select username, resource_consumer_group from v$session;

 

Useful Views
DBA_RSRC_PLANS	what you put into action
DBA_RSRC_CONSMER_GROUP	groups are associated with above plans, users are put into
DBA_RSRC_PLAN_DIRECTIVES	allocates the resources via the consumer groups
DBA_RSRC_CONSUMER_GROUP_PRIVS	all users consumer privileges
USER_RSRC_MANAGER_GROUP_PRIVS	what consumer groups a user can access
DBA_RSRC_MANAGER_SYSTEM_PRIVS	who has access to resource manager
USER_RSRC_MANAGER_SYSTEM_PRIVS	does the user have resource manager privilege
DBA_RSRC_GROUP_MAPPINGS	user mapping priority
DBA_RSRC_MAPPING_PRIORITY	priority mappings when a user is in more than one consumer group
V$RSRC_PLAN	shows all currently active resource plans

Example – putting it all together

clearing and creating the pending area

exec dbms_resource_manager.clear_pending_area; exec dbms_resource_manager.create_pending_area; Note: by clearing the pending area you are actually deleting it.

create the consumer groups

exec dbms_resource_manager.create_plan (plan=>’DAYTIME’,comment=>’plan for normal working hours’); exec dbms_resource_manager.create_plan_directive(plan=>’DAYTIME’,group_or_subplan=>’SYS_GROUP’, cpu_p1=>100,comment=>’give sys_group users top priority’); exec dbms_resource_manager.create_plan_directive(plan=>’DAYTIME’,group_or_subplan=>’OLTP’, cpu_p2=>100,comment=>’give oltp users next priority’); exec dbms_resource_manager.create_plan_directive(plan=>’DAYTIME’,group_or_subplan=>’DDS’, cpu_p3=>50,comment=>’give dds half at next priority’); exec dbms_resource_manager.create_plan_directive(plan=>’DAYTIME’,group_or_subplan=>’BATCH’, cpu_p3=>50,comment=>’give batch half at next priority’); exec dbms_resource_manager.create_plan_directive(plan=>’DAYTIME’,group_or_subplan=>’other_groups’, cpu_p4=>100,comment=>’anything left they can have it!’);

validate and submit the resource plan

exec dbms_resource_manager.validate_pending_area; exec dbms_resource_manager.submit_pending_area; Note when you submit the pending area you are actually validating, submitting and clearing the pnding area

getting the resource plan in to action

alter system set resource_manager_plan=daytime; (dynamic) select * from v$rsrc_plan;

Tuning Oracle’s Buffer Cache

Introduction

Oracle maintains its own buffer cache inside the system global area (SGA) for each instance. A properly sized buffer cache can usually yield a cache hit ratio over 90%, meaning that nine requests out of ten are satisfied without going to disk.

If a buffer cache is too small, the cache hit ratio will be small and more physical disk I/O will result. If a buffer cache is too big, then parts of the buffer cache will be under-utilized and memory resources will be wasted.

Checking The Cache Hit Ratio

Oracle maintains statistics of buffer cache hits and misses. The following query will show you the overall buffer cache hit ratio for the entire instance since it was started:

     SELECT (P1.value + P2.value - P3.value) / (P1.value + P2.value)
     FROM   v$sysstat P1, v$sysstat P2, v$sysstat P3
     WHERE  P1.name = 'db block gets'
     AND    P2.name = 'consistent gets'
     AND    P3.name = 'physical reads'

You can also see the buffer cache hit ratio for one specific session since that session started:

     SELECT (P1.value + P2.value - P3.value) / (P1.value + P2.value)
     FROM   v$sesstat P1, v$statname N1, v$sesstat P2, v$statname N2,
            v$sesstat P3, v$statname N3
     WHERE  N1.name = 'db block gets'
     AND    P1.statistic# = N1.statistic#
     AND    P1.sid = <enter SID of session here>
     AND    N2.name = 'consistent gets'
     AND    P2.statistic# = N2.statistic#
     AND    P2.sid = P1.sid
     AND    N3.name = 'physical reads'
     AND    P3.statistic# = N3.statistic#
     AND    P3.sid = P1.sid

You can also measure the buffer cache hit ratio between time X and time Y by collecting statistics at times X and Y and computing the deltas.

Adjusting The Size Of The Buffer Cache

The db_block_buffers parameter in the parameter file determines the size of the buffer cache for the instance. The size of the buffer cache (in bytes) is equal to the value of the db_block_buffers parameter multiplied by the data block size.

You can change the size of the buffer cache by editing the db_block_buffers parameter in the parameter file and restarting the instance.

Determining If The Buffer Cache Should Be Enlarged

If you set the db_block_lru_extended_statistics parameter to a positive number in the parameter file for an instance and restart the instance, Oracle will populate a dynamic performance view called v$recent_bucket. This view will contain the same number of rows as the setting of the db_block_lru_extended_statistics parameter. Each row will indicate how many additional buffer cache hits there might have been if the buffer cache were that much bigger.

For example, if you set db_block_lru_extended_statistics to 1000 and restart the instance, you can see how the buffer cache hit ratio would have improved if the buffer cache were one buffer bigger, two buffers bigger, and so on up to 1000 buffers bigger than its current size. Following is a query you can use, along with a sample result:

     SELECT   250 * TRUNC (rownum / 250) + 1 || ' to ' || 
              250 * (TRUNC (rownum / 250) + 1) "Interval", 
              SUM (count) "Buffer Cache Hits"
     FROM     v$recent_bucket
     GROUP BY TRUNC (rownum / 250)

     Interval           Buffer Cache Hits
     --------------- --------------------
     1 to 250                       16083
     251 to 500                     11422
     501 to 750                       683
     751 to 1000                      177

This result set shows that enlarging the buffer cache by 250 buffers would have resulted in 16,083 more hits. If there were about 30,000 hits in the buffer cache at the time this query was performed, then it would appear that adding 500 buffers to the buffer cache might be worthwhile. Adding more than 500 buffers might lead to under-utilized buffers and therefore wasted memory.

There is overhead involved in collecting extended LRU statistics. Therefore you should set the db_block_lru_extended_ statistics parameter back to zero as soon as your analysis is complete.

In Oracle7, the v$recent_bucket view was named X$KCBRBH. Only the SYS user can query X$KCBRBH. Also note that in X$KCBRBH the columns are called indx and count, instead of rownum and count.

Determining If The Buffer Cache Is Bigger Than Necessary

If you set the db_block_lru_statistics parameter to true in the parameter file for an instance and restart the instance, Oracle will populate a dynamic performance view called v$current_bucket. This view will contain one row for each buffer in the buffer cache, and each row will indicate how many of the overall cache hits have been attributable to that particular buffer.

By querying v$current_bucket with a GROUP BY clause, you can get an idea of how well the buffer cache would perform if it were smaller. Following is a query you can use, along with a sample result:

     SELECT   1000 * TRUNC (rownum / 1000) + 1 || ' to ' || 
              1000 * (TRUNC (rownum / 1000) + 1) "Interval",
              SUM (count) "Buffer Cache Hits"
     FROM     v$current_bucket
     WHERE    rownum > 0 
     GROUP BY TRUNC (rownum / 1000)

     Interval     Buffer Cache Hits
     ------------ ----------------- 
     1 to 1000               668415   
     1001 to 2000            281760   
     2001 to 3000            166940   
     3001 to 4000             14770    
     4001 to 5000              7030     
     5001 to 6000               959

This result set shows that the first 3000 buffers are responsible for over 98% of the hits in the buffer cache. This suggests that the buffer cache would be almost as effective if it were half the size; memory is being wasted on an oversized buffer cache.

There is overhead involved in collecting LRU statistics. Therefore you should set the db_block_lru_statistics parameter back to false as soon as your analysis is complete.

In Oracle7, the v$current_bucket view was named X$KCBCBH. Only the SYS user can query X$KCBCBH. Also note that in X$KCBCBH the columns are called indx and count, instead of rownum and count.

Full Table Scans

When Oracle performs a full table scan of a large table, the blocks are read into the buffer cache but placed at the least recently used end of the LRU list. This causes the blocks to be aged out quickly, and prevents one large full table scan from wiping out the entire buffer cache.

Full table scans of large tables usually result in physical disk reads and a lower buffer cache hit ratio. You can get an idea of full table scan activity at the data file level by querying v$filestat and joining to SYS.dba_data_files. Following is a query you can use and sample results:

     SELECT   A.file_name, B.phyrds, B.phyblkrd
     FROM     SYS.dba_data_files A, v$filestat B
     WHERE    B.file# = A.file_id
     ORDER BY A.file_id

     FILE_NAME                            PHYRDS   PHYBLKRD
     -------------------------------- ---------- ----------
     /u01/oradata/PROD/system01.dbf        92832     130721
     /u02/oradata/PROD/temp01.dbf           1136       7825
     /u01/oradata/PROD/tools01.dbf          7994       8002
     /u01/oradata/PROD/users01.dbf           214        214
     /u03/oradata/PROD/rbs01.dbf           20518      20518
     /u04/oradata/PROD/data01.dbf         593336    9441037
     /u05/oradata/PROD/data02.dbf        4638037    4703454
     /u06/oradata/PROD/index01.dbf       1007638    1007638
     /u07/oradata/PROD/index02.dbf       1408270    1408270

PHYRDS shows the number of reads from the data file since the instance was started. PHYBLKRD shows the actual number of data blocks read. Usually blocks are requested one at a time. However, Oracle requests blocks in batches when performing full table scans. (The db_file_multiblock_read_count parameter controls this batch size.)

In the sample result set above, there appears to be quite a bit of full table scan activity in the data01.dbf data file, since 593,336 read requests have resulted in 9,441,037 actual blocks read.

Spotting I/O Intensive SQL Statements

The v$sqlarea dynamic performance view contains one row for each SQL statement currently in the shared SQL area of the SGA for the instance. v$sqlarea shows the first 1000 bytes of each SQL statement, along with various statistics. Following is a query you can use:

     SELECT   executions, buffer_gets, disk_reads, 
              first_load_time, sql_text
     FROM     v$sqlarea
     ORDER BY disk_reads

EXECUTIONS indicates the number of times the SQL statement has been executed since it entered the shared SQL area. BUFFER_GETS indicates the collective number of logical reads issued by all executions of the statement. DISK_READS shows the collective number of physical reads issued by all executions of the statement. (A logical read is a read that resulted in a cache hit or a physical disk read. A physical read is a read that resulted in a physical disk read.)

You can review the results of this query to find SQL statements that perform lots of reads, both logical and physical. Consider how many times a SQL statement has been executed when evaluating the number of reads.

Conclusion

This brief document gives you the basic information you need in order to optimize the buffer cache size for your Oracle database. Also, you can zero in on SQL statements that cause a lot of I/O, and data files that experience a lot of full table scans.

(Follow dbspecialists.com/)

What do you have to do to be a good DBA ?

From time to time, people on the Oracle forum, or comp.databases.oracle.server newsgroup ask the question: “What do you have to do to be a good DBA ?”

Joel Goodman and Harald van Breederode – a pair of highly skilled and very experienced instructors at Oracle University – recently asked themselves a slightly different question: “How do you have to change to stay a good DBA in the modern environment ?”

Historically, the Oracle DBA skill set was database-centric, usually limited to software installation, database creation, day-to-day maintenance, performance monitoring, tuning and most of all, backup and  recovery. Since  the arrival of Oracle 10g,  the  technology within  the DBA arena has changed, due to the increase in automated monitoring, tuning and manageability
features within  the Oracle  kernel,  and  also expanded  into  areas  that were  formerly  the  responsibility of OS, storage, and network administrators. Due to these changes, the knowledge required by the typical Oracle DBA has increased, requiring additional skills and in some cases job responsibilities. This shift from the traditional DBA, which we call DBA 1.0, toward the mod-
ern, post Oracle 9i DBA, which we call DBA 2.0, has occurred gradually over the past two major releases of the Oracle Database Server.

This expansion of the multi-skilled roles, DBA or otherwise, is countered by the ever-increasing strict partitioning of job roles and access restrictions in financial environments. It’s been two years since I was at a client or on a job where I had actually had any access to the OS for example or had any dba level access to the database (in theory at least). ..

Customers Select Oracle® Exadata for Extreme Performance of Data Warehouse and OLTP Applications

News Facts

Customers across the globe have chosen Oracle® Exadata to deliver extreme performance for their data warehousing and online transaction processing (OLTP) environments.

The Oracle Exadata product family includes Oracle Database Machine and Oracle Exadata Storage Server with Oracle Exadata Storage Server software.

Announced on September 15, 2009, Oracle Exadata V2 was developed with Sun Microsystems.

The following organizations are among those that have selected Oracle Exadata: Amtrak, Allegro Group, Automobile Association of the UK, Banca Transilvania, CTC, Garanti Bank, Generale de Sante, Giant Eagle, HISCOM (Hokuriku Coca Cola), Integrated Health Information Systems Pte Ltd/National Healthcare Group, Singapore, KnowledgeBase Marketing, Loyalty Partner Solutions, M-Tel, MTN Group, Nagase, NS Solutions, NTT Data, OK Systems, Philippines Savings Bank, Research in Motion, SoftBank Mobile, Screwfix, Thomson Reuters, True Corporation Plc, TUI and Yamazaki Baking.

Oracle Exadata V2 Increases Oracle’s Performance and Price-Performance Leadership

The Exadata Hybrid Columnar Compression in Oracle Exadata V2 delivers industry leading 10x average table compression for data warehouse data; with corresponding increase in table scan performance.

This latest version also features Exadata Smart Flash Cache, the industry’s first intelligent integration of flash with a database, delivering over 1 million I/Os per second and enabling extreme OLTP; plus consolidation of data warehouse and OLTP workloads onto the same system.

Boost Productivity – The application includes Task Optimization tools that allow utilities to reduce the number of steps and associated “clicks” in their most common processes – improving efficiency and ensuring consistency in task execution. The Task Optimization tools allow utilities to model their business processes, including configuring their own user interfaces, to suit their unique work processes and needs, while ensuring all data entered is properly validated for accuracy. This enables the system to be even more intuitive and user friendly, helping to reduce required training and improve productivity and efficiency.

With Oracle Exadata V2, Oracle Exadata Storage Servers now deliver 50 Gigabytes per second of raw, uncompressed I/O bandwidth; approximately 5x greater than major competitors and 2-3x better on a price/performance basis.

Supporting Quotes

“Banca Transilvania has a long history and strong partnership with Oracle. We expect that the 30 times performance improvement of the Oracle Database Machine will enable faster access to business information and will allow us to launch new products exactly when the market needs them, which will be a direct benefit to our customers.” — Marius Ursuti, Director IT, Banca Transilvania

“We saw significant improvements in the tests we did on Exadata. The minimum improvement was 27x with an average of 470x improvement on the queries we tested compared to our current system. This was achieved with no tuning and after removing all indexes. In fact, eliminating indexes is going to save us on half the disk capacity.” — Mark Win, Director, Business Intelligence, Integrated Health Information Systems Pte Ltd/National Healthcare Group, Singapore

“We chose Oracle Exadata because they could deliver a complete system that is simple and fast to implement, very cost-effective, and scalable as our data warehouse grows,” said Suwicha Pornawalai, Director of Information Technology (Application), True Corporation Plc. “The Oracle Database Machine and Exadata Storage Servers can help us tap deeper into our customer database in a cost efficient manner. In doing so, we are able to better understand our customers and develop innovative products and services that best fit their usage behavior. The Oracle Database Machine helps us enhance customers’ satisfaction with a 360-degree view of customers across our five businesses: TrueMove, TrueOnline, TrueVisions, TrueMoney, and TrueLife.”

“TUI chose the Sun Oracle Database Machine because it was the only platform that would meet both our needs for all of our database requirements, including OLTP, as well as be the best platform for a high performance data warehouse. The performance and scalability of the Sun Oracle Database Machine will allow our business users to provide all answers for complex queries which will result in a higher service level and a more efficient unique selling proposition for our business.” — Eli Lysen, Senior Manager ICT, TUI

“This is further proof that Oracle Exadata has revolutionized the data warehouse industry,” said Willie Hardie, vice president of Database Product Marketing, Oracle. “V2 with its intelligent Exadata Smart Flash Cache will do the same for our customers OLTP applications.”

(Oracle OpenWorld, San Francisco, Calif. – October 13, 2009)