Re: Thoughts on comments
Date: Fri, 22 Mar 2013 06:29:43 -0700 (PDT)
Message-ID: <1363958983.44168.YahooMailNeo_at_web162005.mail.bf1.yahoo.com>
Good Morning,
I would like to address on the topic of "Will there be a performance impact?" as I have seen the occasion where there was one.
The performance impact can exist and did exist in a highly partitioned, VLDB, where wide tables, (125-400 columns wide) were involved. I can't remember the exact *name* for the wait event but Oracle listed it specifically and it was visible even in OEM10g, listed directtly back to the comments, (a good search on wait events on comments doesn't result in much of anything, every site has "comments"... :))
The impact averaged out around 30% added wait on obj$, tab$, seg$ and col$, (there may have been a few others that were involved, but it was three years ago, apologies, the memory isn't what it used to be!) that resulted in us removing about 90% of the comments. These comments, I need to be clear, did not assist in anyway, (column_nameDR_CD, comment="Address Code".) The waits were eliminated by removing these from the tables in question, but it was a massive undertaking in a 24X7 VLDB hybrid database.
Here is my rule of thumb with comments:
1. Make sure the comments offer value:
Column Comment Value SHP_FLG Flag for Shipping None FLAG1 Ship to Flag YN ValuableLST_NM Last Name Customer Very Little NM_0012 Name of Shipper More Valuable
2. Use them when you feel they are needed, not for EVERY column, it should not be a contest of who can add the most comments or the highest detailed comments- keep them simple.
3. Take care when using them in a partitioned table.
4. How often do we go into companies, identify a huge performance issue or design issue and we hear, "We never thought about this when it was initially designed...", "We thought it would only be temporary", "We didn't know the business would make it the center of all their processing!."
Use the 555 rule when doing any database design.
How will the code/object/report serve the database 5 weeks/5 months/5 years from now?
Hope this helps,
Kellyn Pot'Vin
Senior Technical Consultant
Enkitec
DBAKevlar.com
RMOUG Director of Training Days 2013
~Tombez sept fois, se relever huit!
From: Mark W. Farnham <mwf_at_rsiz.com> To: rajugaru.vij_at_gmail.com; 'z b' <zimsbait_at_gmail.com> Cc: oracle-l_at_freelists.org
Sent: Thursday, March 21, 2013 10:56 PM
Subject: RE: Thoughts on comments
desc does not show the comments. The comments are kept in user_tab_comments and user_col_comments (also dba_ <similar> as usual).
http://asktom.oracle.com/pls/apex/f?p0:11:0::::P11_QUESTION_ID:2330142045 43
is useful.
I personally rarely use table and column comments, and when I do it (so far) has been on tables and columns I'm creating to demonstrate or test some feature, where I want to keep the column names very short for ad hoc query typing yet want to be able to understand what is in the columns easily. Other than that I don't usually have comments in the database unless the tool being used to manage the data model automagically populates them.
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of rajugaru.vij_at_gmail.com
Sent: Friday, March 22, 2013 12:37 AM
To: z b
Cc: oracle-l_at_freelists.org
Subject: Re: Thoughts on comments
Noob question,
If I describe the object, will these comments be visible?
------Original Message------
From: z b
To: raju raju
Cc: oracle-l_at_freelists.org
Subject: Re: Thoughts on comments
Sent: Mar 22, 2013 10:02 AM
> Will that increase dictionary space?
Obviously.
> Will that have any performance issues?
Not sure. It's metadata, so tools like Toad and SQLDeveloper can see it. Don't think it would impact normal queries.
> How to use comments? I mean to add and retrieve. Would be helpful
The idea is the database will be "self-documenting". This is what ER Diagrams are for, not necessarily metadata adds like "comments".
On Fri, Mar 22, 2013 at 12:29 AM, <rajugaru.vij_at_gmail.com> wrote:
> Hi,
>
> Nice, I never saw any comment on any of the objects or columns in my
database.
>
> Questions:
>
> Will that increase dictionary space?
> Will that have any performance issues?
> How to use comments? I mean to add and retrieve. Would be helpful
>
> Thanks
> Sent on my BlackBerryR
>
> -----Original Message-----
> From: z b <zimsbait_at_gmail.com>
> Sender: oracle-l-bounce_at_freelists.org
> Date: Fri, 22 Mar 2013 00:25:21
> To: <oracle-l_at_freelists.org>
> Reply-To: zimsbait_at_gmail.com
> Subject: Thoughts on comments
>
> Listers,
>
> Just wanted to get a general opinion on using comments in the
> database. For example COMMENT ON COLUMN table.column "No comment".
>
> Does anyone do this as a regular best practice? I would prefer to see
> this abstraction in a data model, maybe not pushed to the database
> layer, especially since not all RDBMS engines have "comment"
> functionality.
>
> The thought is we should do this for every column, every table, every
> oracle db. This ends up being several million comments added to the
> our various instances.
> --
> http://www.freelists.org/webpage/oracle-l
>
>
Sent on my BlackBerryR--
http://www.freelists.org/webpage/oracle-l
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Mar 22 2013 - 14:29:43 CET