DEGREE parameter NOT working while gathering INDEX STATS [message #305028] |
Fri, 07 March 2008 11:18  |
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 #305042 is a reply to message #305041] |
Fri, 07 March 2008 13:34   |
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   |
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
|
|
|
|