Re: Query Using 20 Parallel Sessions

On Feb 8, 8:05 am, "Dereck L. Dietz" <diet...@xxxxxxxxxxxxx> wrote:
Oracle 10.2.0.3.0
Windows Server 2003

We have a query going against a 100 million plus row partitioned table using
a bitmap index on a column with only 3 distinct values.  When the query runs
it only takes about 2 minutes and uses about 20 parallel sessions.  This
query is trying to retrieve specific rows where a value in a column is 0 so
the number of rows returned will not be huge.

The person who runs this query has been informed that this is causing a
"problem" because of it using 20 parallel sessions and has come to me to
resolve the problem.

I personally believe it is just Oracle working as designed and chosing the
most efficient access path and method.

Any comment/suggestions as to what my response should be?

Thanks

Note: this post is not in disagreement with the post by David
Fitzjarrell. However, if there are multiple sessions connected to the
database server at the same time, you might find that one session is
trying to use all of the resources available on the server, leaving
little available capacity for the other sessions...

If you have a copy of "Expert Oracle Database Architecture", take a
look at chapter 14.
"Parallel execution is essentially a nonscalable solution... In a
system where resources must be shared by many concurrent transactions,
such as an OLTP system, you would likely observe increased response
times [more sessions waiting, increased wait events] due to this."

"Cost-Based Oracle Fundamentals" page 30:
"Parallel scans use direct path reads to bypass the data buffer and
read blocks directly into local (PGA) memory... But if the block in
the data buffer is dirty (newer than the block on disk), then you
might think a direct read would not see the latest version, and may
therefore get the wrong result. To solve this problem, a parallel
query will first issue a segment checkpoint to get all dirty blocks
written to disk before it reads... This could lead to a performance
problem in rare cases that mixed a large data buffer, a busy OLTP
system, and parallel execution for reports..."

Essentially repeating what David Fitzjarrell stated: are people
reporting that there are performance problems with the database
application, or is someone just reporting that 100% of available
server capacity (CPU or disk) is being used.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
.

Relevant Pages

  • Re: Single-Threading / Performance issues
    ... Apparently not since at least 3 physical disks are required for RAID-5. ... This is normal but the percent disk utilization metric isn't much use ... might want to set SQL Server 'max server memory' if you have other apps ... > From Query Analyser, I run my import proc - which first copies data ...
    (microsoft.public.sqlserver.server)
  • Re: MailMerge hangs and crashes with Access on Server
    ... Since I am fairly new to working in this environment, I am not sure what you mean by an "Access group" with help to restructuring the query? ... "Peter Jamieson" wrote: ... All I know is that the performance of queries depends to a large extent on whether, for example, a join is performed on the client side, or on the server side, incurring no network traffic and potentially benefitting from caching on the server. ... There are no dialog boxes, and actually, I have now split the database, and have kept the Word templates and the Access frontend locally on my machine, and moved the backend to the server. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Performance Benchmarks?
    ... Are attribute relationships defined properly on the dimensions? ... If you run Profiler against the server, how long is spent in "Query ... I have created aggregations (Partitions tab in cube design). ...
    (microsoft.public.sqlserver.olap)
  • High CPU in client (Excel, OWC, Proclarity, etc.) accessing Analysis Services
    ... the OWC10 with 3 dimensions on the row axis, ... The largest size of any of these 4 dimensions < 360 members. ... Performance Guide to optimize the query, the cube, the server, etc. ...
    (microsoft.public.sqlserver.olap)
  • Re: SQL Server Performance Issue
    ... when your run the UDF ... Dual-Core Woodcrest 1.6Ghz, 2GB RAM, IIS, SQL Server). ... Physical Disk Reads and Writes when i execute this example UDF query, ... Server has a SATA RAID1 Disk Mirroring ...
    (comp.databases.ms-sqlserver)