Home » SQL & PL/SQL » SQL & PL/SQL » DEGREE parameter NOT working while gathering INDEX STATS
DEGREE parameter NOT working while gathering INDEX STATS [message #305028] Fri, 07 March 2008 11:18 Go to next message
Ram99
Messages: 3
Registered: March 2008
Junior Member
Hi

I tried to gather index stats using DBMS_STATS.GATHER_INDEX_STATS.
I used DEGREE parameter so that index stats are gathered in parallel. But when
I check the sql it runs as folowing:
SELECT /*+ no_parallel_index(t,"XX_BUILD_B17") dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_expand index(t,"XXGDW_BUILD_B17") */

but gathering table stats using DEGREE parameter is working fine and is gathering stats in PARALLEL. DBMS_STATS.GATHER_TABLE_STATS.

The initialisation parameters are set as follows:

parallel_min_servers integer 0
parallel_max_servers integer 8
cpu_count integer 16
parallel_threads_per_cpu integer 2


Any thoughts on why index stats gathering is not happening in PARALLEL??

Thanks
Ram
Re: DEGREE parameter NOT working while gathering INDEX STATS [message #305029 is a reply to message #305028] Fri, 07 March 2008 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you should ask Oracle.
Are you sure the query you pick up means that the statistics are not computed in parallel?

Regards
Michel
Re: DEGREE parameter NOT working while gathering INDEX STATS [message #305031 is a reply to message #305028] Fri, 07 March 2008 12:10 Go to previous messageGo to next message
Ram99
Messages: 3
Registered: March 2008
Junior Member
Yes Michel. I am sure sure the query I picked up means that the statistics are not computed in parallel.

Thanks
Ram
Re: DEGREE parameter NOT working while gathering INDEX STATS [message #305032 is a reply to message #305031] Fri, 07 March 2008 12:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ah! You know Oracle internals.
Like I said you should talk with Oracle, we are not at your level.

Regards
Michel
Re: DEGREE parameter NOT working while gathering INDEX STATS [message #305033 is a reply to message #305028] Fri, 07 March 2008 12:33 Go to previous messageGo to next message
Ram99
Messages: 3
Registered: March 2008
Junior Member
Great Michel. Thank you.
Re: DEGREE parameter NOT working while gathering INDEX STATS [message #305034 is a reply to message #305033] Fri, 07 March 2008 12:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you tell us what do "dbms_stats", "use_weak_name_resl" and so on hints mean and what are their effects?

Regards
Michel
Re: DEGREE parameter NOT working while gathering INDEX STATS [message #305037 is a reply to message #305028] Fri, 07 March 2008 12:56 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Interesting link. Check this out
http://archive.netbsd.se/?ml=oracle-l&a=2006-03&t=1884519
http://www.oracle.com/technology/oramag/oracle/05-may/o35sql.html
Google always helps.

Regards

Raj
Re: DEGREE parameter NOT working while gathering INDEX STATS [message #305041 is a reply to message #305037] Fri, 07 March 2008 13:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In the first link Wolfgang Breitling said what I meant: there maybe a reason for this hint.
And I add the other hints may say it is still executed in parallel but in another code path than the one use by the classical index parallel.

Is the second link "Filter Data Before You Get It" article from Jonathan Gennick? If this is the case, I don't see the relation with the question.

Regards
Michel
Re: DEGREE parameter NOT working while gathering INDEX STATS [message #305042 is a reply to message #305041] Fri, 07 March 2008 13:34 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
Can you tell us what do "dbms_stats", "use_weak_name_resl" and so on hints mean and what are their effects?

I gave the second link for your above quote. Ofcourse it didn't explain clearly what the hint (use_weak_name_resl) will do, but this is the only link where I found it's been used on its own. I do agree it doesn't add any value to this thread for this issue but still I thought it's worthing adding it.

Regards

Raj
Re: DEGREE parameter NOT working while gathering INDEX STATS [message #305045 is a reply to message #305028] Fri, 07 March 2008 15:48 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
These are just thoughts, not neccessarily expert opinion. But.

1. Is the index a functional index where the function has not been parallel_enabled? That *might* force serial build.

2. Im not an expert in this matter, but is it possible that if you only have 1 freelist and 1 freelist group on the index, oracle might think its better building with noparallel?

3. What happens if you alter the index and set the degree on the index itself before building?

4. Parallel max servers of 8 is pretty low for a 16cpu processor. Could it be that other processes are using them? a degree of 4 on an index build might use to up to 8 slaves.

[Updated on: Fri, 07 March 2008 15:48]

Report message to a moderator

Re: DEGREE parameter NOT working while gathering INDEX STATS [message #305046 is a reply to message #305045] Fri, 07 March 2008 15:53 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It has nothing to do with index rebuilt here, it is just gathering statistics.

Generally speaking, your point 4 is wrong. The most important point in parallelism is NOT cpu but io subsystem.

Regards
Michel
Previous Topic: Need to write an Select for the following result
Next Topic: How to multiple columns into single row
Goto Forum:
  


Current Time: Sat Dec 10 09:07:56 CST 2016

Total time taken to generate the page: 0.10321 seconds