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
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!

Report message to a moderator

Re: Limit resource creation [message #377191 is a reply to message #377187] Sun, 21 December 2008 09:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68666
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is nothing to limit CPU usage as CPU is there to be used.
But you can distribute the CPU among sessions, read:

Database Concepts
Chapter 14 Manageability
Section Workload Management
Subsection Overview of the Database Resource Manager

And follow the links.

Regards
Michel

Report message to a moderator

Re: Limit resource creation [message #377243 is a reply to message #377191] Mon, 22 December 2008 01:53 Go to previous messageGo to next message
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!

Report message to a moderator

Re: Limit resource creation [message #377245 is a reply to message #377243] Mon, 22 December 2008 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68666
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Having no idea of what plan you are trying to implement I can't say if the code matches the plan.

In addition, each line of a script should be commented and the script itself should have a header explaining what it tries to achieve.

Regards
Michel

Report message to a moderator

Re: Limit resource creation [message #377251 is a reply to message #377245] Mon, 22 December 2008 02:38 Go to previous message
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 Cool

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!

Report message to a moderator

Previous Topic: ORA-00379
Next Topic: DBCA widnow is very slow
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jul 05 02:47:50 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.