Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Oracle 9.2.0.5 "10046" Tracing Causes Server Crash on Windows 2000?

RE: Oracle 9.2.0.5 "10046" Tracing Causes Server Crash on Windows 2000?

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Tue, 3 Aug 2004 13:38:35 -0400
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09EFEAFD@bosmail00.bos.il.pqe>


Donald,

In the environment you describe, it makes sense that there aren't any = negatives to running parallel.

I think that the situation that Cary was describing, where there are = lots of users, all trying to utilize parallel operations, is what folks = need to look out for. If you have lots of users, enabling parallel = operations is a good way to light your CPUs on fire and make the = performance problems MUCH worse.

Parallel operation, by design, is a CPU hog. Instead of tuning, you're = saying, "I have a huge amount of work to do, so, let's chop it up into = little pieces and and do lots of little chunks of work at the same time, = then glue it all back together." That's great if you have the CPU = resources to spare. If you've got a 8 (or even 16 or 32 or more) CPU = box, but you're supporting 100s of users, then the numbers may not work = in your favor.

In your environment, with a small number of users, it's not really an = issue. You sohuld still consider though, depending on the types of = queries you're running, it still may be possible to tune SQL, turn off = parallel, and get better performance. If you can tune the SQL and/or = tune the app, and eliminate unnecessary work, that's always going to be = a win over ignoring the fact that you're doing unnecessary work and = using parallel operations to blast through that extra work.

-Mark

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Freeman, Donald Sent: Tuesday, August 03, 2004 1:13 PM
To: oracle-l_at_freelists.org
Subject: RE: Oracle 9.2.0.5 "10046" Tracing Causes Server Crash on Windows 2000?

We haven't seen any negative results from using parallel. The db is a =
=3D

datawarehouse with no more than 4 concurrent users connecting through =
=3D

Cognos during the day and no activity at night outside the Informatica =
=3D

ETL process.=3D20

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Cary Millsap Sent: Tuesday, August 03, 2004 12:09 PM
To: oracle-l_at_freelists.org
Subject: RE: Oracle 9.2.0.5 "10046" Tracing Causes Server Crash on Windows 2000?

Bugs and other undocumented behaviors notwithstanding, using parallel features produces one specific and very important risk that you should understand.

When you N-way parallelize a process, you convert a single process into =
=3D
=3D3D

N
distinct processes (at least N--in Oracle, it's actually 2N+1). Each of these processes now competes for CPU time, memory busses, latches, =3D3D locks,
and everything else that a process competes for. What I see so =3D3D frequently is
the following...

What the user wants: My user action runs in 80 seconds, so I'll 4-way parallelize it in attempt to make it run in 20 seconds.

What really happens: Yesterday 100 users made 100 database connections =
=3D
=3D3D

at a
time, which used 80% of the CPU capacity on the server. Today, the same users are making 900 database connections at a time (2*4+1 sessions for =
=3D
=3D3D

each
of the same 100 users). CPU utilization is locked perpetually at 100%, =
=3D
=3D3D

and
the system is deadlocked.

In many cases, the original 80-second user can be fixed to run in a =
=3D3D

second
or less by eliminating wasteful work. When this is the case, the end =
=3D3D

result
is that users are ecstatic about response time, and the next server =
=3D3D

upgrade
can be deferred by several months because the system is running at significantly reduced CPU utilization.

So, what's the best way to tune a parallel operation? Deactivate the parallelization, and optimize the remaining serial operation. :)

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Upcoming events:
- Performance Diagnosis 101: 8/10 Boston, 9/14 San Francisco, 10/5 =3D3D Charlotte
- SQL Optimization 101: 7/26 Washington DC, 8/16 Minneapolis, 9/20 =3D3D Hartford
- Hotsos Symposium 2005: March 6-10 Dallas - Visit www.hotsos.com for schedule details...

-----Original Message-----
From: oracle-l-bounce_at_freelists.org =3D3D [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Freeman, Donald
Sent: Tuesday, August 03, 2004 10:20 AM
To: oracle-l_at_freelists.org
Subject: RE: Oracle 9.2.0.5 "10046" Tracing Causes Server Crash on =3D3D Windows
2000?

We were hoping to improve query performance with parallel although it =
=3D
=3D3D
=3D3D3D

may not be necessary in all circumstances. Regarding parallel is it =3D
=3D3D3D

better to pick and choose what activities are performed in parallel? I =
=3D
=3D3D
=3D3D3D

think we've basically defaulted it on after our performance improved in =
=3D
=3D3D
=3D3D3D

the areas we were concerned about. What about V$PARAMETER error? Is =3D
=3D3D3D

that significant? It keeps showing up in the alert log.=3D3D3D20

We had two occassions last week when I had to reboot the server. The =
=3D
=3D3D
=3D3D3D

first was recorded in the logs I provided. The second was when I =
=3D3D3D

arrived in the morning Friday and couldn't log in. I couldn't find any =
=3D
=3D3D
=3D3D3D

errors recorded anywhere. We installed 9.2.0.5 on Tuesday the 27th.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Tanel P=3D3D3DF5der Sent: Tuesday, August 03, 2004 10:46 AM
To: oracle-l_at_freelists.org
Subject: Re: Oracle 9.2.0.5 "10046" Tracing Causes Server Crash on Windows 2000?

It seems that, you are using parallel query and =3D3D3D cursor_sharing=3D3D3D3Dforce|similar
here, it might be that the problem occurs when all of these features "collide". Do you need your query to run in parallel? (parallel option triggers lots of bugs, including data/query result corruption ones, in =
=3D
=3D3D
=3D3D3D

my
experience).

Tanel.

> I am finally getting to practice some of the tracing functionality =3D
=3D3D3D

that =3D3D3D3D
> is discussed here and ran into something I haven't seen dicussed here. =
=3D
=3D3D
=3D3D3D

 =3D3D3D3D
> We had a complaint about a query that performs inconsistently and I =
=3D
=3D3D
=3D3D3D
=3D3D3D3D

> enabled my session trigger on user logon to capture it. The server =
=3D
=3D3D
=3D3D3D
=3D3D3D3D

> failed at a point shown below. The first entry is the alert log =
=3D3D3D

entry. =3D3D3D3D



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Tue Aug 03 2004 - 12:34:30 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US