Tuning PX Deq Credit: send blkd during Parallel Job Executions
Not a DBA, need some assistance,
1) I am looking into possibilities to tune my parallel job processes. I have read to look into the pga_aggregate_size. Presently the size of PGA_aggregate_size is set to 125GB (the avilable memory in the box is 228GB).
Total 100 connections are processing parallel jobs and are waiting on PX Deq Credit: send blkd.
Do you think the value set to pga_aggregate_size is correct? Any suggestions.
2) in 10g, I can not see any value set to my db_cache_size or shared_pool_size. Is it ok? For your reference I am pasting parameter details below ..
NAME TYPE VALUE
------------------------------------ ----------- bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_block_size integer 8192
db_cache_size big integer 0
db_keep_cache_size big integer 0
db_recovery_file_dest_size big integer 2G
db_recycle_cache_size big integer 0
hash_area_size integer 131072
java_max_sessionspace_size integer 0
java_pool_size big integer 0
large_pool_size big integer 0
max_dump_file_size string UNLIMITED
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
olap_page_pool_size big integer 0
parallel_execution_message_size integer 2152
sga_max_size big integer 44128M
shared_pool_reserved_size big integer 184M
shared_pool_size big integer 0
sort_area_retained_size integer 0
sort_area_size integer 65536
streams_pool_size big integer 0
workarea_size_policy string AUTO
Any help will be sincerely appreciated. Would like to reduce the PX Deq Credit: send blkd.
It is hard to believe in such a large environment you do not have a DBA at hand.
When you have parallel jobs it is normal to see "PX Deq Credit: send blkd" event at the top or your list.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
You cannot parallelize every single statement in Oracle. There are many restrictions imposed by Oracle.
Why don't you post the slow performing "parallel Query" statements here?
Click Here to Expand Forum to Full Width