Re: 10g - Need advise on large lookup table and optimizing io

Thanks for the feedback Jonathan.
I'm currently testing this right now, and it seems like a good idea. However I'm testing another solution also which I think might be even better.

This large lookup table is joined into a 3 table total query that pulls lots of data out of our database. The index is currently there to assist this so that the table does not have to read, but because our queries select an avg of 50-70 million records in a 24hr time span, this index is very contened for... The average waits on these index files avg from 30ms - 150ms.

If I move the column EMAIL into a customer specific table..then this large table is not needed and now every customer query is only 2 table without the need to join on this large table... It performs better.

I've even tested setting a parallel degree of 2 for each table in the 2 table SELECT query and the performance and IO reductions from the 3 table query is enourmous... We have CPU queues that are generally at 7-8 while the machine has 12 CPUs...

But I do have a few concerns about the memory requirements,PGA and how memory is allocated since these queries come from shared/MTS connections while other things in our system are a mix of dedicate/shared connections..

everyone's feedback is always appreciated..

BTW, read your "Snark Research" posting on your website. Really good stuff, but some pieces of memory allocation,shared/MTS connections and (in my case parallelism) are still unclear. I posted a thread about parallelism concerns...if you have some time to quickly browse it I'd appreciate it (posted Jan 25 3:06 pm)

http://groups.google.com/group/comp.databases.oracle.server/browse_frm/thread/141d529cdb53a383/2608a7fb9b977212#2608a7fb9b977212

thanks again.
-peter Received on Fri Jan 27 2006 - 10:38:42 CST