Operating System - HP-UX
1823998 Members
3912 Online
109667 Solutions
New Discussion юеВ

Oracle Expert needed....server=dedicated question

 
SOLVED
Go to solution
Rita C Workman
Honored Contributor

Oracle Expert needed....server=dedicated question

Greetings all,

I am by no means an Oracle person. Just know enough to keep myself to keep myself from getting into too much mischief..

Here's my quesiton:
Getting some complaints from end users that our biggest Oracle database is running slow....everything looked fine, but in checking with some DBA staff discovered a bunch of DEDICATED connections by some folks.
Now I thought I remembered something about DEDICATED connections getting some sort of preferential processing privileges....am I mistaken?

Thanks,
Rita
14 REPLIES 14
Steven E. Protter
Exalted Contributor

Re: Oracle Expert needed....server=dedicated question

I don't recall anything like that.

Oracle tries to get as much work done as it can.

There are other reasons the database could be slow. If system peformance is optimal, I'd download the oracle stats pack for your database, install it or better yet make your dba install it.

Then you can get some good measure of internal database performance.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Patrick Wallek
Honored Contributor

Re: Oracle Expert needed....server=dedicated question

Howdy Rita,

Have a look at the attached doc from Oracle Metalink. I think #2 under the "Types of Connection Requests" section may interest you.

I don't know if it'll help or not. Hopefully it will.

Andy Beal
Frequent Advisor

Re: Oracle Expert needed....server=dedicated question

The way you phrased that, your not expecting dedicated connections. That makes me wonder how many dispatchers you have running. There's a default limit to the number of connections on a dispatcher, and once reached new connections will start going dedicated. If memory serves it's 1000 then this happens. Just a thought, and something I learned early after we migrated to Oracle.

Andy
Patrick Wallek
Honored Contributor
Solution

Re: Oracle Expert needed....server=dedicated question

More reading pleasure -- Oracle Net Performance Tuning attached.
Rita C Workman
Honored Contributor

Re: Oracle Expert needed....server=dedicated question

OK, let me explain a little farther...

We run MTS (multi-threaded). How many dispatchers are set up...I'd have to ask the Head DBA guy.
My understanding is that with multi-threaded, Oracle dispatchers wait for a user to make a request and then run the job, then the next request and so forth, ... thus distributing the resources evenly to end user requests.
Now with a dedicated connection, it's a little different...the user connects as server=dedicated and he gets a dispatcher from Oracle. So he's connected, got a dispatcher, and sits....he(or she) isn't running anything yet, just connected, but that dispatcher belongs to that dedicated connection and they will hold it until they got out.
See my point......
If I see several dedicated connections sitting there...wouldn't this hold up some measure of Oracle resources? Wouldn't that leave the simple MTS request to wait until a dispatcher is freed up...and possibly causing a slowdown to the end-user community.

Did that make any sense...
Rita
Steven E. Protter
Exalted Contributor

Re: Oracle Expert needed....server=dedicated question

You are making sense.

From experience. If the user isn't doing anything, running sql whatever, havinig an open connection does not effect performance.

One nasty sql job that needs to be tuned up can do this to you Rita.

Our dba sometimes spends half his day working with developers to keep nasty sql code out of production. On a development server we commonly see performance spikes sometimes bad enough that Glance picks them up.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Andy Beal
Frequent Advisor

Re: Oracle Expert needed....server=dedicated question

Dedicated connections will chew up more memory resources. My point about the number of MTS dispatchers is if they are overloaded MTS isn't doing you any good, dedicated connections will be faster. It's posisble that the dedicated people aren't complaining, but the ones connecting to MTS are.
Rita C Workman
Honored Contributor

Re: Oracle Expert needed....server=dedicated question

OK...I think I got some information from Patrick's attachment that helped:

Point 1:
The Oracle Listener performs many tasks, but the ones that play a role in the connection process are the creation of the dedicated server process, or the redirection to a dispatcher process.
Creation of any process on a computer consumes memory and CPU, but as it pertains to this article, it consumes time.
When the database is configured to allow only dedicated connections, the Listener receives a connect request and passes the task of creating the dedicated server process to the operating system. Once the time to create this process has elapsed, the Listener can then redirect the client to it. A redirect packet is then sent to the client if the dedicated server process did not inherit the same port number then the Listener, otherwise a resend packet is sent. The client then connects to the dedicated server process and continues with the login to the database.

This Listener connection process can be sped up if the server process can be pre-created. If the process can be created prior to the connection request, the Listener will not have to wait to send the redirect or resend packets, but send them right after the connect request comes in. This is possible if the Oracle Database is configured for Shared Server. In a Shared Server configuration,
processes called dispatchers are created by the process monitor (PMON) and register their network protocol information with the listener. By making themselves available, the Listener will redirect the connect request to the dispatcher rather then wait to create a dedicated server process. Such a configuration can decrease the connect time considerably.
.... In other words it takes longer to connect via Dedicated than non-Dedicated ...

Point 2:

The next bottleneck the Listener could encounter is too many concurrent connection requests. The Listener process, like any other has limits - it is constrained by CPU speed and available memory. Although system growth is usually faster than Moore's Law (that PCs double in processing power every few years), there are techniques that can assist in scaling the Listener.
... I am running this rather large database on an old K box with on 3.5Gb RAM, and yes there is even a java app running for their batch processing....So I am tight on resources on this box until they replace it...

Look like I got another hit on this post...so I'm going to submit this and go back and do more reading...

Point a 'coming !
Rita




Rita C Workman
Honored Contributor

Re: Oracle Expert needed....server=dedicated question

Andy,

Exactly ... !!! And since the Dedicated connections are the ever greedy Developer/Programmers, and the MTS are the poor end users...You nailed it !!

Rgrds,
Rita
Yogeeraj_1
Honored Contributor

Re: Oracle Expert needed....server=dedicated question

hi rita,

Please allow me add the following too:

Briefly, MTS works by having a shared pool of server processes that service a larger pool of end users. It is very much like connection pooling ├в since process creation and management are some of the most expensive operations you can ask an operating system to perform, MTS is very beneficial in a large-scale system. So, I might have 100 users but only five or ten shared servers.

When a shared server gets a request to run an update, or execute a stored procedure, then that shared server is dedicated to that task until completion. No one else will use that shared server until that update completes or that stored procedure finishes execution. Thus, when using MTS your goal must be to have very short statements. MTS is designed to scale up OLTP systems ├в a system characterized by statements that execute with sub-second response times. You├в ll have a single row update, insert a couple of line items, and query records by primary key. You won├в t (or shouldn├в t) run a batch process that takes many seconds or minutes to complete.

If all our statements execute very rapidly, then MTS works well. We can effectively share a number of processes amongst a larger community of users. If on the other hand, we have sessions that monopolise a shared server for extended periods of time then we will see apparent database ├в hangs├в .

Note that you can have a combination of both MTS and dedicated server modes. Simply you should not execute long running transactions under MTS.

Hope this helps too!

b
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Oracle Expert needed....server=dedicated question

[sorry the above post did not come out well. did a copy paste from my M$ word :(. Hope this one is OK]

hi rita,

Please allow me add the following too:

Briefly, MTS works by having a shared pool of server processes that service a larger pool of end users. It is very much like connection pooling ├в since process creation and management are some of the most expensive operations you can ask an operating system to perform, MTS is very beneficial in a large-scale system. So, I might have 100 users but only five or ten shared servers.

When a shared server gets a request to run an update, or execute a stored procedure, then that shared server is dedicated to that task until completion. No one else will use that shared server until that update completes or that stored procedure finishes execution. Thus, when using MTS your goal must be to have very short statements. MTS is designed to scale up OLTP systems ├в a system characterized by statements that execute with sub-second response times. You├в ll have a single row update, insert a couple of line items, and query records by primary key. You won├в t (or shouldn├в t) run a batch process that takes many seconds or minutes to complete.

If all our statements execute very rapidly, then MTS works well. We can effectively share a number of processes amongst a larger community of users. If on the other hand, we have sessions that monopolise a shared server for extended periods of time then we will see apparent database ├в hangs├в .

Note that you can have a combination of both MTS and dedicated server modes. Simply you should not execute long running transactions under MTS.

Hope this helps too!

B
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Michael Schulte zur Sur
Honored Contributor

Re: Oracle Expert needed....server=dedicated question

Hi Rita,

In a MTS environment you have two levels. The dispatch level and the server level. Don't worry about the dispatcher. It can handle a lot, since it only dispatches requests to the shared server. Dedicated server however grab what they can. Memory, cpu, disk, you name it. So it is indeed preferred. Depending on your oracle version, you can classify user into resource groups and limit the amount of resources, they can use.

my 2 euro cents. ;-)

Michael
Rita C Workman
Honored Contributor

Re: Oracle Expert needed....server=dedicated question

Thank you all for your help. I think I have a little better grasp on this topic.

Unfortunately that doesn't necessarily solve my problem. But that's just the old standby...."it must be the operating systems fault....it's not my problem" (so sayeth the programmers & DBA's)

Someday's you feel like...let 'em farm out my job. I could use the rest.

Thanks,
Rita

...Consider this post closed...
Brian_274
Frequent Advisor

Re: Oracle Expert needed....server=dedicated question

Hi Rita,
It seems to me that you believe the initial connection is the problem. Many reasons can be the cause of your problem. To help you solve this you need to give us a little more information. What version of oracle, what platform. Was any update performed on production recently? Are you using locally managed or dictionary managed tablespaces. Maybe your tables or indexes are fragmented. How many people usually connect to your database and how many are connecting now. Do you run anything like stats pack or some other type of stats collection tool? What are the worst queries running on the box at the time of the performance hit? Does the db run slow all the time or just certain times during the day. Look at the v$sql view for the number of disk reads the queries are doing. In a perfect world you want to have no disks reads and have all your data in memory, naturally that doesn't happen. Realalistically you want only a couple thousand or so depending on the amount of data being return in each query. If a query is going 100,000 disks each execution that's going to slow down the server. So if you have time update this with some of the above information and we'll be able to help you out more.
Brian.