Home » RDBMS Server » Performance Tuning » Performance Implications of a Generic Lookup Table Design
Performance Implications of a Generic Lookup Table Design [message #216602] Mon, 29 January 2007 11:45 Go to next message
artmt
Messages: 32
Registered: October 2006
Location: Boston
Member
I am trying to decide between the following designs:

Option 1

SELECT * FROM STATE;

State_Code	Code_Desc
NY 		NEW YORK
MA		MASSACHUSETTS


SELECT * FROM COUNTRY;

State_Code	Code_Desc
US		UNITED STATES
UK		UNITED KINGDOM

Option 2

SELECT * FROM GEN_LOOKUP;

Lookup_Type	Lookup_Code	Code_Desc
STATE		NY		NEW YORK
COUNTRY		US		UNITED STATES

Given that the lookup data is fairly static how serious are the concerns about the contention around the “hot blocks”?
Can the problem be effectively dealt with by using smaller blocks and minimizing rows per block?
Any other concerns?

Thanks
Art
Re: Performance Implications of a Generic Lookup Table Design [message #216604 is a reply to message #216602] Mon, 29 January 2007 11:59 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>how serious are the concerns about the contention around the “hot blocks”?
Exactly what concerns do you refer & what "contention"?
With Oracle readers never block writers & writers never block readers.
If a block is in fact "hot", it will exist on the LRU list & be served from the SGA.
>Can the problem be effectively dealt with by using smaller blocks and minimizing rows per block?
Please quantify the problem you think exists.
Re: Performance Implications of a Generic Lookup Table Design [message #216613 is a reply to message #216604] Mon, 29 January 2007 12:41 Go to previous messageGo to next message
artmt
Messages: 32
Registered: October 2006
Location: Boston
Member
As far as I understand the problem that is rumored to exist is caused by the contention for buffer chain latches.
To me it makes sense that if all commonly requested lookup data is residing in a handful of blocks in buffer cashe, then there should be some potential for contention.
What I am trying to find out is whether there is any truth to such rumors, and if so should I be sufficiently concerned to make this a design consideration.

Art
Re: Performance Implications of a Generic Lookup Table Design [message #216828 is a reply to message #216602] Tue, 30 January 2007 11:11 Go to previous messageGo to next message
srinivas4u2
Messages: 66
Registered: June 2005
Member
Art,

It's very difficult to tell which would be better by looking at the structures of two tables. One needs to know what kind of system it is , what kind of transactions would be made, how the data in the table is going o be used, how big the tables would be etc.

Also, as anacedent has pointed, readers are not going to be blocked in Oracle.

Thanks,
Srinivas
Re: Performance Implications of a Generic Lookup Table Design [message #216829 is a reply to message #216602] Tue, 30 January 2007 11:17 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Besides Option #1 IMO is flawed because no common column exists between STATE & COUNTRY tables to allow them to be joined.
Previous Topic: Optimizing Queries Against UNIONed View
Next Topic: Cost in the Explain Plan
Goto Forum:
  


Current Time: Sat Dec 10 20:22:56 CST 2016

Total time taken to generate the page: 0.08271 seconds