Like I said previously, I had the same problem yesterday.
I'm finding it very hard to get any type of plan stability with the load.
Oracle wants to take the partitioned tables (size varies from 10Gb to 300Gb
and the process is the same for all tables) and hash them into memory. Of
course I don't have 300Gb of disk space set aside for temp space. Tried
analyzing out the ying yang but always the hash. I'm using a pga_aggregate
target of 512Mb.
So I have to issue the following alter session
alter session set hash_join_enabled = false optimizer_index_caching = 90
optimizer_index_cost_adj = 5
This gives me a nested loop index access, and is essentially a rule based
approach but is very fast. The only problem is it sometimes picks the wrong
index for the index probe(all bitmaps) and that's where I got the pq Deq
wait problem. I'm going the check out stored outlines next.
Mike
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at
http://www.freelists.org/archives/oracle-l/
FAQ is at
http://www.freelists.org/help/fom-serve/cache/1.html
Received on Wed Jun 09 2004 - 08:10:27 CDT
