Home » RDBMS Server » Performance Tuning » Call from specified Machine not assigned to Database resource consumer. (Release 11.2.0.3.0 - 64bit)
Call from specified Machine not assigned to Database resource consumer. [message #595093] Fri, 06 September 2013 10:15 Go to next message
VIP2013
Messages: 84
Registered: June 2013
Member
I am using Release 11.2.0.3.0 - 64bit Production version of oracle. Now we are having 3-tier architecture, (firewal/web/app/DB).
Now i saw , some of the 'sql' queries, running till ~10hrs in my database and those are part of application(module JDBC THIN CLIENT). After had a talk java guys, they ask to kill the sessions specific to those queries. They are part of search TO, in which user put some large values for the date range and went to other TAB, but these queries gets running infinitely in the database, and user is not interested in the result set.

So how to avoid these things, as because in past, our database has suffered resource contention leading to application slowness. So i was planing to set different timeouts using 'database resource consumer group' for online user request and batch request depending on the app server(that is by machine names) request.

So i have done below set up in my local to test one scenario, in which i will try give a database call from difference machine, and it should get timeout after the specified duration. But its not working , as expected. The calls from the specified machine are not getting assigned to the created 'Consumer group'.
Let me know, if i am missing anything.


Begin
-- create the pending area
dbms_resource_manager.create_pending_area();
END;
/
BEGIN
-- Create the consumer group
dbms_resource_manager.create_consumer_group(
    CONSUMER_GROUP=>'ONLINE_USERS_LIMITED_EXEC_TIME',
    COMMENT=>'This is the consumer group that has limited execution time per statement'
    );
END;
/

BEGIN
--  We need a consumer group that maps to the desired machine:
  dbms_resource_manager.set_consumer_group_mapping(
    attribute => dbms_resource_manager.client_machine,
    value => 'PAC\LR9XY7T8',
    consumer_group =>'ONLINE_USERS_LIMITED_EXEC_TIME'
  );
END;
/

BEGIN
  dbms_resource_manager.set_consumer_group_mapping(
    attribute => dbms_resource_manager.client_machine,
    value => 'LR9XY7T8',
    consumer_group =>'ONLINE_USERS_LIMITED_EXEC_TIME'
  );
END;
/

BEGIN
  -- Now create a resource plan:
  dbms_resource_manager.create_plan(
    PLAN=> 'LIMITED_EXEC_TIME',
    COMMENT=>'Kill statement after exceeding total execution time'
  );
END;
/
-- Now let's create a plan directive for that special user group , the plan will cancel the current SQL if it runs for more than 120 sec
-- You can define multiple directives depending on requirement

BEGIN
dbms_resource_manager.create_plan_directive(
    PLAN=> 'LIMITED_EXEC_TIME',
    GROUP_OR_SUBPLAN=>'ONLINE_USERS_LIMITED_EXEC_TIME',
    COMMENT=>'Kill statement after exceeding total execution time',
    SWITCH_GROUP=>'CANCEL_SQL',
    SWITCH_TIME=>60,
    SWITCH_ESTIMATE=>false
  );

  --Basically for each plan we need to define the plan directives for the OTHER_GROUPS also, what that means is determine limits for all other sessions other than those bound by the consumer group ONLINE_USERS_LIMITED_EXEC_TIME.

  dbms_resource_manager.create_plan_directive(
    PLAN=> 'LIMITED_EXEC_TIME',
    GROUP_OR_SUBPLAN=>'OTHER_GROUPS',
    COMMENT=>'leave others alone',
    CPU_P1=>100
  );
  END;
/


exec dbms_resource_manager.validate_pending_area(); 

exec dbms_resource_manager.submit_pending_area(); 

exec dbms_resource_manager.create_pending_area(); 

alter system set RESOURCE_MANAGER_PLAN='LIMITED_EXEC_TIME';


After this when i am verifying calls from machine, 'LR9XY7T8' they are belongs to the consumer group 'OTHER_GROUPS' and sql query not getting timed out within 60 seconds as mentioned.
 

[Updated on: Fri, 06 September 2013 10:16]

Report message to a moderator

Re: Call from specified Machine not assigned to Database resource consumer. [message #595094 is a reply to message #595093] Fri, 06 September 2013 10:19 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
problem & fix are external to Oracle.
USER is root cause & needs to be fixed or prevented from being within 2 meters of any keyboard.
Re: Call from specified Machine not assigned to Database resource consumer. [message #595095 is a reply to message #595094] Fri, 06 September 2013 10:25 Go to previous messageGo to next message
VIP2013
Messages: 84
Registered: June 2013
Member
you are right. But currently i am planning to restrict the 'CPU utilization/Paralle degree limit/ No of Active session in a group/max idle time for a session/max execution time.. etc. all these by applying database consumer group to specific machines. So just want to know, why the above test scenario is not working. What is the fault with the steps, can you please help me to findout same?

[Updated on: Fri, 06 September 2013 10:26]

Report message to a moderator

Re: Call from specified Machine not assigned to Database resource consumer. [message #595096 is a reply to message #595095] Fri, 06 September 2013 10:29 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
with 3-tier application & connection pooling, you can't fix application problem at the database level.
Re: Call from specified Machine not assigned to Database resource consumer. [message #595097 is a reply to message #595096] Fri, 06 September 2013 11:05 Go to previous messageGo to next message
VIP2013
Messages: 84
Registered: June 2013
Member
i got below statement in one of the implementation of 'database Consumer group'.

http://www.pythian.com/blog/oracle-limiting-query-runtime-without-killing-the-session/

It lets one switch into lower priority groups and kill a query while leaving the session running using parameter 'SWITCH_TIME'. It is like running a SELECT statement in sqlplus, and pressing ctrl-c. The session is still alive, yet the query is canceled. Resource Manager also allows one to define how much CPU a certain user or group will require, how much I/O in MB, Paralle degree limit, No of Active session in a group, max idle time for a session,max execution time.. etc

So i thought, its going to help me...
Re: Call from specified Machine not assigned to Database resource consumer. [message #595098 is a reply to message #595097] Fri, 06 September 2013 11:13 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
Let us know how well this approach works out for you.
Re: Call from specified Machine not assigned to Database resource consumer. [message #595099 is a reply to message #595093] Fri, 06 September 2013 12:07 Go to previous messageGo to next message
John Watson
Messages: 4611
Registered: January 2010
Location: Global Village
Senior Member
I can see possible issues with your code:
First, as this is a three tier application you should use SWITCH_FOR_CALL, not just SWITCH_TIME. You don't want to downgrade the session permanently, just for the one call.
Second, you need to look at your session mapping priority. By default, client_machine is almost bottom (see dba_rsrc_mapping_priority). Perhaps you need to raise the priority of client_machine as a session mapping attribute..
Re: Call from specified Machine not assigned to Database resource consumer. [message #595151 is a reply to message #595099] Sat, 07 September 2013 16:47 Go to previous messageGo to next message
VIP2013
Messages: 84
Registered: June 2013
Member
Something surprising is happening. So when i am putting below procedure just before enabling the RESOURCE_MANAGER_PLAN as below, then i can see the user 'test' in the new 'resource consumer group' which is executed from machine 'LR9XY7T8'.

exec dbms_resource_manager_privs.grant_switch_consumer_group('test','ONLINE_USERS_LIMITED_EXEC_TIME',false);
alter system set RESOURCE_MANAGER_PLAN='LIMITED_EXEC_TIME';

USERNAME                        RESOURCE_CONSUMER_GROUP
-----------                     ------------------------------
TEST                           ONLINE_USERS_LIMITED_EXEC_TIME 

But then i am trying to test a negative scenario . Rollbackk all the above change.
And i am putting machine name as 'ABCD' in 'set_consumer_group_mapping' which doesnot exists at all.

BEGIN
  dbms_resource_manager.set_consumer_group_mapping(
    attribute => dbms_resource_manager.client_machine,
    value => 'ABCD',
    consumer_group =>'ONLINE_USERS_LIMITED_EXEC_TIME'
  );
END;
/

in this case too, user 'test' belongs to the new defined consumer group , even if there is no machine exists as 'ABCD' in this LAN. changing 
the priority for 'client_machine' using DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI is not helping me.

USERNAME                        RESOURCE_CONSUMER_GROUP
-----------                     ------------------------------
TEST                           ONLINE_USERS_LIMITED_EXEC_TIME 


Re: Call from specified Machine not assigned to Database resource consumer. [message #595152 is a reply to message #595151] Sat, 07 September 2013 17:00 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2411
Registered: May 2013
Location: World Wide on the Web
Senior Member
Per your original question, you need to set the maximum estimated execution time to let the queries run only if they are estimated to executed in less than the specified time. Please have a look at this thread, Barbara has demonstrated it prefectly!



[Edit MC: fix url]

[Updated on: Sun, 08 September 2013 00:39] by Moderator

Report message to a moderator

Re: Call from specified Machine not assigned to Database resource consumer. [message #595155 is a reply to message #595152] Sun, 08 September 2013 00:45 Go to previous message
VIP2013
Messages: 84
Registered: June 2013
Member
Yes, Gone through the thread. But below is my requirement at this point of time.

--We are having three tier architecture, 2 web server + 10 app server + 1 DB. and we do have some online request + batch request coming to the database from the app servers. And for all of tha application request(online+batch), oracle user is same that is 'app_user'. So i cant restrict the timout for them by deviding them using oracle user, because timeout should be fifferent for online and batch. So one thing comes into my mind, during the database call, i am getting different client_machine name i.e. app server names from which request are coming. So if i can group the app server machines into two bunch, one for online and other for batch request and put them into two different database consumer group, then i can restrict different time out for each of them.

Issue:
------
Current issue i am facing, issue with assigning machines into different consumer group. Even if i am giving particular machine name in the procedure 'dbms_resource_manager.set_consumer_group_mapping' and expecting the consumer group will be mapped to that particular machine during run time, if any request comes from that machine. but its not working that way . The consumer group gets assigned to the oracle user, which is mentioned in 'dbms_resource_manager_privs.grant_switch_consumer_group' procedure, even if machine name is different.

So just want to know, if my understanding of deviding machines based on database consumer groups is implementable as per the current oracle version 11.2.0.3.0. Or issue with my script somewhere?

[Updated on: Sun, 08 September 2013 00:54]

Report message to a moderator

Previous Topic: Database crash Due To CPU Starvation
Next Topic: Need your help in tuning the query
Goto Forum:
  


Current Time: Wed Oct 01 23:39:32 CDT 2014

Total time taken to generate the page: 0.59454 seconds