OraFAQ Forum: Performance Tuning » Performance Implications of a Generic Lookup Table Design

Home » RDBMS Server » Performance Tuning » Performance Implications of a Generic Lookup Table Design

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Performance Implications of a Generic Lookup Table Design [message #216602]

Mon, 29 January 2007 11:45

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

Report message to a moderator

Re: Performance Implications of a Generic Lookup Table Design [message #216604 is a reply to message #216602]

Mon, 29 January 2007 11:59

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Performance Implications of a Generic Lookup Table Design [message #216613 is a reply to message #216604]

Mon, 29 January 2007 12:41

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

Report message to a moderator

Re: Performance Implications of a Generic Lookup Table Design [message #216828 is a reply to message #216602]

Tue, 30 January 2007 11:11

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

Report message to a moderator

Re: Performance Implications of a Generic Lookup Table Design [message #216829 is a reply to message #216602]

Tue, 30 January 2007 11:17

BlackSwan
Messages: 26766
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.

Report message to a moderator

Previous Topic:	Optimizing Queries Against UNIONed View
Next Topic:	Cost in the Explain Plan

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 27 03:17:47 CST 2024