Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
=====Den Oracle Resource Manager ab 11g verwenden=====

=== Session Handling ===

Eine Session höher prioriseren:

<code sql>
BEGIN
DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS (
     session_id      => '213'
   , session_serial  => '2132131'
   , consumer_group  => 'HIGH_PRIO' );
END;

</code>


==== Abfragen ====
Script:

<code sql>
SET linesize 130 pagesize 100 recsep OFF

prompt .... Viewing Consumer Groups Granted to Users or Roles

select grantee
	,   granted_group
	,   grant_option
	,   initial_group
 from dba_rsrc_consumer_group_privs
order by  grantee
/

prompt .. Viewing Plan Schema Information

column plan          format a27 heading "Plan"
column status        format a10 heading "Status"
column comments      format a100 heading "Comment" WORD_WRAPPED 
column cpu_method    format a10 heading "CPU_METHOD"
column mgmt_method   format a10 heading "CPU_METHOD"
column parallel      format a20 fold_after 
 
select plan
     , status 
	  , cpu_method
	  , mgmt_method
	  , parallel_degree_limit_mth as parallel
	  , comments	  
 from dba_rsrc_plans 
order by status 
/

prompt .. show user waiting with resource limit

select sid
     , serial#
     , username
	  , resource_consumer_group 
 from v$session
where event like 'resmgr%'
/
</code>

Aktuellste Version siehe auch  [[http://orapowershell.codeplex.com/SourceControl/latest#sql/resource_manager.sql|resource_manager.sql]]


==== Quellen ====

Doku:

  * http://docs.oracle.com/cd/B28359_01/server.111/b28310/dbrm004.htm#ADMIN11886
  * http://docs.oracle.com/cd/E11882_01/server.112/e17120/dbrm.htm#ADMIN027
  * http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_resmgr.htm#CFAGGDBD


Netz:
  * http://www.oracle.com/technetwork/database/performance/resource-manager-twp-133705.pdf
  * http://oracle-base.com/articles/10g/resource-manager-enhancements-10g.php
  * http://www.oracle.com/webfolder/technetwork/de/community/dbadmin/tipps/resource_manager/index.html
  * http://jhdba.wordpress.com/2011/10/19/resource-manager-plans-during-the-automatic-maintenance-window/