Home » RDBMS Server » Server Administration » Limit resource creation (Oracle 10.2.0.1, Solaris 10)
Limit resource creation [message #377187] |
Sun, 21 December 2008 08:05 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
trantuananh24hg
Messages: 744 Registered: January 2007 Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
OS: Solaris 10
CPU: 2 processors, 8 branchs.
RAM: 16GB
Hi all!
I've got something.. Our application (AS 10) will bring reporting results which is to be called by some session to HTML (imagines). Of course, any sql(s) statement is very, very slow, may be 5 or 6 sessions execute concurrency to make database's CPU over 100%. And I've to create a limit profile, grant all of reporting user to use this profile. But, when I granted successfully, I realized that, the CPU is still over 100%, because resource parameters in profile did not decreased CPU usage, they are:
Quote: |
SESSIONS_PER_USER Specify the number of concurrent sessions to which you want to limit the user.
CPU_PER_SESSION Specify the CPU time limit for a session, expressed in hundredth of seconds.
CPU_PER_CALL Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.
CONNECT_TIME Specify the total elapsed time limit for a session, expressed in minutes.
IDLE_TIME Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.
LOGICAL_READS_PER_SESSION Specify the permitted number of data blocks read in a session, including blocks read from memory and disk.
LOGICAL_READS_PER_CALL Specify the permitted number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).
PRIVATE_SGA Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA)
|
Eg:
CPU_PER_SESSION Specify the CPU time limit for a session, expressed in hundredth of seconds. does not limit CPU usage, but time limit.
May you have some idea to guide me about limitation CPU usage to grant to schema users?
Thank you!
|
|
|
|
Re: Limit resource creation [message #377243 is a reply to message #377191] |
Mon, 22 December 2008 01:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
trantuananh24hg
Messages: 744 Registered: January 2007 Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Thank you, Michel!
I've realized that, and I've just created a resource manager to force some schema users using this area.
I post my code at here. May you tell me if I have mistake?
/* ccs_admin, ccs_common, ccs_hcm, ccs_baocao resource manager*/
PROMPT --++Create a pending area++--
prompt
EXECUTE dbms_resource_manager.create_pending_area();
PROMPT --++Create resource plans++--
prompt
EXECUTE dbms_resource_manager.create_plan('Daytime','Plan for daytime processing activities');
EXECUTE dbms_resource_manager.create_plan('Night_Wknd','Plan for nights and weekend processing activities');
EXECUTE dbms_resource_manager.create_plan('Reporting','Plan for reporting activities');
PROMPT
PROMPT --++Create resource consumer groups++--
prompt
EXECUTE dbms_resource_manager.create_consumer_group('oltp_users','data entry specialists');
EXECUTE dbms_resource_manager.create_consumer_group('batch_processes','nightly batch jobs');
EXECUTE dbms_resource_manager.create_consumer_group('reporting_users','data entry limitation');
PROMPT
PROMPT --++Create resource plan directives++--
prompt --------++Day time++-------------------
prompt
EXECUTE dbms_resource_manager.create_plan_directive(-
'daytime','oltp_users','daytime rules for OLTP users',-
cpu_p1=>70,parallel_degree_limit_p1=>0);
EXECUTE dbms_resource_manager.create_plan_directive(-
'daytime','batch processes','daytime rules for batch users',-
cpu_p1=>40,parallel_degree_limit_p1=>0);
EXECUTE dbms_resource_manager.create_plan_directive(-
'daytime','other_groups','daytime rules for other',-
cpu_p2=>90,parallel_degree_limit_p1=>0);
EXECUTE dbms_resource_manager.create_plan_directive(-
'daytime','reporting_users','daytime rules for reporting users',-
cpu_p1=>50,parallel_degree_limit_p1=>0);
PROMPT
PROMPT ---------++Nights/Weekends++------------
prompt
EXECUTE dbms_resource_manager.create_plan_directive(-
'Night_Wknd','oltp_users','Night/Weekend rules for OLTP users',-
cpu_p1=>20,parallel_degree_limit_p1=>0);
EXECUTE dbms_resource_manager.create_plan_directive(-
'Night_Wknd','batch_processes','Night/Weekend rules for batch users',-
cpu_p1=>80,parallel_degree_limit_p1=>0);
EXECUTE dbms_resource_manager.create_plan_directive(-
'Night_Wknd','other_groups','Night/Weekend rules for others',-
cpu_p2=>90,parallel_degree_limit_p1=>0);
EXECUTE dbms_resource_manager.create_plan_directive(-
'Night_Wknd','reporting_users','Night/Weekend rules for reporting users',-
cpu_p2=>70,parallel_degree_limit_p1=>0);
PROMPT
PROMPT --++Confirm group and plan configuration++---
prompt
PROMPT ----------++Validate pending area++---------
execute dbms_resource_manager.validate_pending_area();
PROMPT
PROMPT ----------++Clear pending area++------------
prompt
--execute dbms_resource_manager.clear_pending_area();
PROMPT
PROMPT ----------++Sudmit pending area++-----------
prompt
EXECUTE dbms_resource_manager.submit_pending_area();
PROMPT
PROMPT --++Assign users to consumer groups++-------
prompt
PROMPT ---------++OLTP++---------------------------
prompt
EXECUTE dbms_resource_manager_privs.grant_switch_consumer_group(-
'CCS_ADMIN','oltp_users',FALSE);
EXECUTE dbms_resource_manager_privs.grant_switch_consumer_group(-
'CCS_BAOCAO','reporting_users',FALSE);
EXECUTE dbms_resource_manager_privs.grant_switch_consumer_group(-
'CCS_COMMON','oltp_users',FALSE);
EXECUTE dbms_resource_manager_privs.grant_switch_consumer_group(-
'CCS_HCM','oltp_users',FALSE);
EXECUTE dbms_resource_manager_privs.grant_switch_consumer_group(-
'CCS_HNI','oltp_users',FALSE);
PROMPT
PROMPT --------++BATCH++---------------------------
prompt
EXECUTE dbms_resource_manager_privs.grant_switch_consumer_group(-
'CCS_ADMIN','batch_processes',FALSE);
EXECUTE dbms_resource_manager_privs.grant_switch_consumer_group(-
'CCS_BAOCAO','batch_processes',FALSE);
EXECUTE dbms_resource_manager_privs.grant_switch_consumer_group(-
'CCS_COMMON','batch_processes',FALSE);
PROMPT
PROMPT ---------++OLTP/BATCH++-----------------------
prompt
EXECUTE dbms_resource_manager_privs.grant_switch_consumer_group(-
'CCS_ADMIN','oltp_users',FALSE);
EXECUTE dbms_resource_manager_privs.grant_switch_consumer_group(-
'CCS_HCM','reporting_users',FALSE);
EXECUTE dbms_resource_manager_privs.grant_switch_consumer_group(-
'CCS_HNI','reporting_users',FALSE);
PROMPT
PROMPT --++Force system to run pending area++---------
--alter system set resource_manager_plan=&&rsgmgr
Thanks again!
|
|
|
|
Re: Limit resource creation [message #377251 is a reply to message #377245] |
Mon, 22 December 2008 02:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
trantuananh24hg
Messages: 744 Registered: January 2007 Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Oh, no problem if you have not idea about my rsgmgr plan
I've re-running after I've edited because some code in my rsgmgr plan exceeds 100% CPU p1.
And now, it's running.
Thank you for you reply!
|
|
|
Goto Forum:
Current Time: Sat Aug 24 22:17:36 CDT 2024
|