Re: Query Using 20 Parallel Sessions

On Feb 8, 8:05 am, "Dereck L. Dietz" <diet..._at_ameritech.net> 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. Received on Fri Feb 08 2008 - 09:23:20 CST