OraFAQ Forum: Performance Tuning » Can we manually assign certain query transaction between RAC nodes ?

Home » RDBMS Server » Performance Tuning » Can we manually assign certain query transaction between RAC nodes ? (11g R2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Can we manually assign certain query transaction between RAC nodes ? [message #643123]

Tue, 29 September 2015 22:24

carillpower
Messages: 48
Registered: April 2009
Location: malaysia

Member

Hi and greeting to all gurus,

I would like to check on a doubt Im not sure this function exists or not.

Is there any way that we can assign manually certain query to run in only specific node ?

Example like now I have 3 nodes in RAC environment, then there's a query X located in my apps server. The apps server connected to Oracle DB using normal listener with multiple VIP .

How can I manually assign the query X to run specifically on node 2 ONLY ?

Is this possible to be configure, maybe putting something like hint ?

Kindly thanks an advance for any of your opinion.

Thanks Smile

Report message to a moderator

Re: Can we manually assign certain query transaction between RAC nodes ? [message #643124 is a reply to message #643123]

Tue, 29 September 2015 22:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>How can I manually assign the query X to run specifically on node 2 ONLY ?
WHY?
What problem is solved by implementing this requirement?

What can be changed to accomplish this?
What can't be changed?

Is application 3-tier?
Does application utilize Connection Pooling?

What should result if query X runs on node other than 2?

Report message to a moderator

Re: Can we manually assign certain query transaction between RAC nodes ? [message #643126 is a reply to message #643124]

Tue, 29 September 2015 22:59

carillpower
Messages: 48
Registered: April 2009
Location: malaysia

Member

Hi BlackSwan,

Thanks for your quick reply.

Currently our apps server are access directly using listener using multiple VIP. Below are the configuration :-

(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.3)(PORT=1622))
(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.4)(PORT=1622))
(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.5)(PORT=1622))
(FAILOVER=on)
(LOAD_BALANCE=off)
)
(
CONNECT_DATA=(SERVER=DEDICATED)
( SERVICE_NAME= PROD )
)
)

I've check under gv$session notice that all queries that comes from apps server are connected to instance 1. Im just thinking if there's a way that we could hardcoded certain query statement in apps server to make the connection to other than instance 1.

Thanks an advance for your thoughtful opinion.

Thanks Smile

Report message to a moderator

Re: Can we manually assign certain query transaction between RAC nodes ? [message #643127 is a reply to message #643126]

Tue, 29 September 2015 23:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

By my count I ask 7 questions & you decided to not answer most of them.
Therefore I decide to not waste more of my time on this thread.

Report message to a moderator

Re: Can we manually assign certain query transaction between RAC nodes ? [message #643136 is a reply to message #643126]

Wed, 30 September 2015 01:52

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

You appear to be tryng to use the outdated method of load balancing across your nodes by coding the addresses of each node listener in your tnsnames.ora entry, but because you have load_balance=off all your connections will go to the first address listed.
You should be going through the SCAN listener(s) which will balance correctly. I did describe it (very simply) here a long time ago,
use of SCAN

Report message to a moderator

Re: Can we manually assign certain query transaction between RAC nodes ? [message #643137 is a reply to message #643127]

Wed, 30 September 2015 02:30

carillpower
Messages: 48
Registered: April 2009
Location: malaysia

Member

Dear BlackSwan,

Thanks for your quick reply, I thought the main reason and configuration I've stated would summarize all your doubt.

Apologize on that assumption, my bad.

Below are your clarification needs :-

>How can I manually assign the query X to run specifically on node 2 ONLY ?
WHY?
As per stated above

What problem is solved by implementing this requirement?
This is an idea I've thought other than configure SCAN, didn't confirm the implementation would solve the problem or not but I believe it will. The idea should be similar to MySQL Proxy .

What can be changed to accomplish this?
SQL / PLSQL environment

What can't be changed?
None

Is application 3-tier?
Yes

Does application utilize Connection Pooling?
Yes

What should result if query X runs on node other than 2?
Output result should be the same, but if query X running on node 2 ONLY, then the cpu process for that SPID will hit on node 2 only and never stack on other node cpu process list

Hi John,

Thanks for sharing your point. Yet out of curiosity just thought is there any other way except using SCAN would achieve the goal ?

Thanks and advance for sharing the expertise here.

Thanks Smile

Report message to a moderator

Re: Can we manually assign certain query transaction between RAC nodes ? [message #643138 is a reply to message #643137]

Wed, 30 September 2015 02:50

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Quote:

Yet out of curiosity just thought is there any other way except using SCAN would achieve the goal ?

I did mention the load_balance attribute of your TNS connect string. Have you read up n it yet? However, no DBA will ever recommend that rather than using the SCAN.

Report message to a moderator

Re: Can we manually assign certain query transaction between RAC nodes ? [message #643139 is a reply to message #643138]

Wed, 30 September 2015 03:25

carillpower
Messages: 48
Registered: April 2009
Location: malaysia

Member

Hi John,

Yes I noted on the load_balance command.

Thanks for sharing the knowledge.

Thanks Smile

Report message to a moderator

Re: Can we manually assign certain query transaction between RAC nodes ? [message #643150 is a reply to message #643139]

Wed, 30 September 2015 09:35

bpeasland
Messages: 51
Registered: February 2015
Location: United States

Member

The only way to get a SQL statement to run on a specific RAC node is to leverage _services_ and to have that service running on the node of interest. This means that your connections will be on that node as well. What you can't do is connect to one node and force the run of the SQL statement on another node.

Cheers,
Brian

Report message to a moderator

Re: Can we manually assign certain query transaction between RAC nodes ? [message #643170 is a reply to message #643150]

Wed, 30 September 2015 23:19

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

I am not aware of any simple way to make a single SQL statement run on a specific node. That said...

I am not recommending either of these. And I note that one has been superseded by the other. And I am not sure either REALLY does what you want. And I can't believe they would play well with any transaction design cause you have to figure out how to get an answer back from them. But, I would not want you to go away from us without getting an answer to your original question...

DBMS_JOB.SUBMIT( 
   job       OUT    BINARY_INTEGER,
   what      IN     VARCHAR2, NEXT_DATE IN DATE DEFAULTSYSDATE, 
   interval  IN     VARCHAR2 DEFAULT 'NULL',
   no_parse  IN     BOOLEAN DEFAULT FALSE,
   instance  IN     BINARY_INTEGER DEFAULT ANY_INSTANCE,   --------( see the nice instance parameter )
   force     IN     BOOLEAN DEFAULT FALSE);

and

DBMS_JOB.INSTANCE ( 
   job        IN BINARY_INTEGER,
   instance   IN BINARY_INTEGER,
   force      IN BOOLEAN DEFAULT FALSE);

moving up on the world

  DBMS_SCHEDULER.create_job_class (
    job_class_name => '<job class name>',
    service        => '<service name>');    --------- ( see the service parameter )

and DBMS_SCHEDULER not wanting to be outdone by its older sibling

TYPE job_definition IS OBJECT (
  job_name                       VARCHAR2(100),
  job_class                      VARCHAR2(32),
  job_style                      VARCHAR2(11),
  program_name                   VARCHAR2(100),
  job_action                     VARCHAR2(4000),
  job_type                       VARCHAR2(20),
  schedule_name                  VARCHAR2(65),
  repeat_interval                VARCHAR2(4000),
  schedule_limit                 INTERVAL DAY TO SECOND,
  start_date                     TIMESTAMP WITH TIME ZONE,
  end_date                       TIMESTAMP WITH TIME ZONE,
  event_condition                VARCHAR2(4000),
  queue_spec                     VARCHAR2(100),
  number_of_arguments            NUMBER,
  arguments                      SYS.JOBARG_ARRAY,
  job_priority                   NUMBER,
  job_weight                     NUMBER,
  max_run_duration               INTERVAL DAY TO SECOND,
  max_runs                       NUMBER,
  max_failures                   NUMBER,
  logging_level                  NUMBER,
  restartable                    VARCHAR2(5),
  stop_on_window_close           VARCHAR2(5),
  raise_events                   NUMBER,
  comments                       VARCHAR2(240),
  auto_drop                      VARCHAR2(5),
  enabled                        VARCHAR2(5),
  follow_default_timezone        VARCHAR2(5),
  parallel_instances             VARCHAR2(5),
  aq_job                         VARCHAR2(5),
  instance_id                    NUMBER,         ------------- ( see me here too )
  credential_name                VARCHAR2(65),
  destination                    VARCHAR2(4000),
  database_role                  VARCHAR2(20),
  allow_runs_in_restricted_mode  VARCHAR2(5),
  restart_on_recovery            BOOLEAN,
  restart_on_failure             BOOLEAN
)

A job is not quite the same as a single statement but you could make it so if you really need it to be. Everyone here is of course wondering why.

Good luck. Kevin

Report message to a moderator

Re: Can we manually assign certain query transaction between RAC nodes ? [message #643315 is a reply to message #643170]

Sun, 04 October 2015 20:23

carillpower
Messages: 48
Registered: April 2009
Location: malaysia

Member

Hi Kevin & Brian,

Thanks for your expertise.

Seems that the original objective looks hard to archive yet your knowledge sharing means a world to me and others who read this.

Thanks again and thumbs up! Smile

Report message to a moderator

Re: Can we manually assign certain query transaction between RAC nodes ? [message #643340 is a reply to message #643315]

Mon, 05 October 2015 09:36

bpeasland
Messages: 51
Registered: February 2015
Location: United States

Member

Note that not being able to have a SQL statement run on a specific node does not mean that you can't enjoy the benefits of that node. For example, lets say the SQL statement is being run on node 1, but node 2 has already processed this SQL statement before. The blocks in the buffer cache can be shipped from node 2 to node 1 without have to be re-read from disk. This is called a global cache transfer. Parallel SQL statements in Oracle RAC can be spread over multiple nodes. This can be a reason why you don't necessarily want to pin the SQL statement to one specific node.

Cheers,
Brian

Report message to a moderator

Previous Topic:	Explain plan depends on Oracle client?
Next Topic:	Any good Way to get dNFS IOPS?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 13:28:41 CST 2025