Home > problem-cause-solution > Why not any sql work parallel in database?

Why not any sql work parallel in database?


Problem

Environment:

Exadata X2-2

Oracle: 11.2.0.2.3

Linux version:2.6.18-274.18.1.0.1.el5

My customer came to me that the UPDATE statement which used to finish around 30 minutes, now lasts longer and does not finish.

After investigating the issue, the problem turns out to be parallelism problem.

Rephrasing the problem: Sessions cannot work parallel.

For a SQL like below, Oracle EM SQL Monitor detailed screenshot is given:

select /*+ PARALLEL (T 16) */ *from MYSCHEMA.MYTABLE;

Cause

Who knows …

I checked below areas for discovering for the cause, but nothing found:

– execution plan: There exists PX COORDINATOR lines, no problem.

– parallel hint in the query:Syntax correct, it must work.

– parallel_max_servers: It is 256.

– select * from v$px_process: No rows returned.

– DEGREE value of  table: 16.

– Checking parallelism value for consumer group: 16

– Disabling resource plans: Disabling resource plan does not change the behavior.

– alert.log: No parameter change or internal error in the log.

Click on image, for bigger size.

Solution

Killing user processes, make it work!

It got the parallelism.

Advertisements
  1. Ozgur
    July 11, 2012 at 10:30 am

    Antagonist contradictions were not supposed to be in IT world, even though we alll know what Alexander the Great has done in Gordion. Never sacrifice the answer of question “why” for the ease of solution.
    Enjoy!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Don Charisma

because anything is possible with Charisma

Carol no Mundo!

Aventuras de uma intercambista a trabalho pelo mundo!

Blog do PHP

Oracle DBA for rookies!

nimaidba

Welcome to the world of Oracle with me....

Tech

News and reviews from the world of gadgets, gear, apps and the web

WordPress.com

WordPress.com is the best place for your personal blog or business site.

Gurcan Orhan's ODI and DWH Blog

Some words about Oracle Data Integrator and Data Warehousing.

%d bloggers like this: