FAQ
I am working on a nearly 20 year old legacy system. The OLTP peaks at about
100,000 executions/minute and we expect this to continue to increase. When
the system first came out, they had to make sure there were no constraints,
unique indexes, triggers, synonyms or anything to make sure the system would
perform. I can understand that 20 years ago. We just started adding some
unique constraints. I wanted to make the unique constraints into primary
keys. One of the guys is worried about dictionary contention from the
primary keys. I have worked on some pretty large OLTPs and never saw any
issues with this.
has anyone seen this? We are on 10.2.0.5 (I forget the PSU level) on HP-UX.
If I was going to test this, I'm not really sure what I would look for. I
don't have a way to simulate the volume of users and executions we get in
prod and then just check the waits.
Any suggestions on what to look for? I'll need to show some test cases.

Search Discussions

  • Tim Gorman at Oct 19, 2011 at 8:26 pm
    Don't ask us. Prove it to yourself...
    SQL trace the transactions involving the tables with the constraints in question, aggregate the trace information using your favorite aggregator (i.e. TKPROF, TRCANLZR, Hotsos/MethodR Profiler, etc) and look at the recursive SQL belonging to SYS. If using TKPROF, be sure not to specify SYS=NO. Also, if using TKPROF, be sure to specify SORT=PRSELA,EXEELA,FCHELA or the equivalent.

    If anything belonging to SYS shows up as significant, then there might be "dictionary overhead", but I'd first look more closely at the SYS-generated recursive SQL statements to see what part of the data dictionary is involved. It might have nothing to do with constraints or indexes or whatever it is you're seeking.

    I strongly believe that nothing significant will show up. But don't take anyone else's word for it -- prove it for yourself.

    Hope this helps...



    -----Original Message-----
    From: Dba DBA
    Sent: Wednesday, October 19, 2011 02:08 PM
    To: 'ORACLE-L'
    Subject: primary keys and dictionary overhead

    I am working on a nearly 20 year old legacy system. The OLTP peaks at about100,000 executions/minute and we expect this to continue to increase. Whenthe system first came out, they had to make sure there were no constraints,unique indexes, triggers, synonyms or anything to make sure the system wouldperform. I can understand that 20 years ago. We just started adding someunique constraints. I wanted to make the unique constraints into primarykeys. One of the guys is worried about dictionary contention from theprimary keys. I have worked on some pretty large OLTPs and never saw anyissues with this.has anyone seen this? We are on 10.2.0.5 (I forget the PSU level) on HP-UX.If I was going to test this, I'm not really sure what I would look for. Idon't have a way to simulate the volume of users and executions we get inprod and then just check the waits.Any suggestions on what to look for? I'll need to show some test cases.--http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Bobak, Mark at Oct 19, 2011 at 8:27 pm
    Could you ask him to clarify why he thinks there would be more "dictionary overhead" for a PK than a UK? Yes, there's a certain amount of overhead associated with looking up constraints in the dictionary when executing DML, but, why would it be any worse for PK than UK?

    -Mark


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Dba DBA
    Sent: Wednesday, October 19, 2011 4:09 PM
    To: ORACLE-L
    Subject: primary keys and dictionary overhead

    I am working on a nearly 20 year old legacy system. The OLTP peaks at about
    100,000 executions/minute and we expect this to continue to increase. When the system first came out, they had to make sure there were no constraints, unique indexes, triggers, synonyms or anything to make sure the system would perform. I can understand that 20 years ago. We just started adding some unique constraints. I wanted to make the unique constraints into primary keys. One of the guys is worried about dictionary contention from the primary keys. I have worked on some pretty large OLTPs and never saw any issues with this.
    has anyone seen this? We are on 10.2.0.5 (I forget the PSU level) on HP-UX.
    If I was going to test this, I'm not really sure what I would look for. I don't have a way to simulate the volume of users and executions we get in prod and then just check the waits.
    Any suggestions on what to look for? I'll need to show some test cases.


    --
    http://www.freelists.org/webpage/oracle-l




    --
    http://www.freelists.org/webpage/oracle-l
  • Powell, Mark at Oct 19, 2011 at 8:33 pm
    No unique indexes? How can you have an OLTP system that performs without unique indexes?


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Dba DBA
    Sent: Wednesday, October 19, 2011 4:09 PM
    To: ORACLE-L
    Subject: primary keys and dictionary overhead

    I am working on a nearly 20 year old legacy system. The OLTP peaks at about 100,000 executions/minute and we expect this to continue to increase. When the system first came out, they had to make sure there were no constraints, unique indexes, triggers, synonyms or anything to make sure the system would perform. I can understand that 20 years ago. We just started adding some unique constraints. I wanted to make the unique constraints into primary keys. One of the guys is worried about dictionary contention from the primary keys. I have worked on some pretty large OLTPs and never saw any issues with this.
    has anyone seen this? We are on 10.2.0.5 (I forget the PSU level) on HP-UX.
    If I was going to test this, I'm not really sure what I would look for. I don't have a way to simulate the volume of users and executions we get in prod and then just check the waits.
    Any suggestions on what to look for? I'll need to show some test cases.


    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • David Fitzjarrell at Oct 19, 2011 at 8:54 pm
    Apparently a few vendors did that 20+ years ago and claimed it was for performance reasons.  Never mind that their code was suspiciously absent of bind variables forcing a hard parse for every run of a query where only the string literal value was changed.  Never mind that their idea of referential integrity was 'enforced' in the application code and not the database.  Never mind that they also enforced uniqueness in the same way.  Which explains why migrating from one of these types of applications to one which uses database constraints requires far more data cleanup than should be necessary.

    It's funny sometimes what vendors do in the name of  'performance'.
    David Fitzjarrell


    From: "Powell, Mark" <mark.powell2@hp.com>
    To: ORACLE-L <oracle-l@freelists.org>
    Sent: Wednesday, October 19, 2011 1:31 PM
    Subject: RE: primary keys and dictionary overhead


    No unique indexes?  How can you have an OLTP system that performs without unique indexes?


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Dba DBA
    Sent: Wednesday, October 19, 2011 4:09 PM
    To: ORACLE-L
    Subject: primary keys and dictionary overhead

    I am working on a nearly 20 year old legacy system. The OLTP peaks at about 100,000 executions/minute and we expect this to continue to increase. When the system first came out, they had to make sure there were no constraints, unique indexes, triggers, synonyms or anything to make sure the system would perform. I can understand that 20 years ago. We just started adding some unique constraints. I wanted to make the  unique constraints into primary keys. One of the guys is worried about dictionary contention from the primary keys. I have worked on some pretty large OLTPs and never saw any issues with this.
    has anyone seen this? We are on 10.2.0.5 (I forget the PSU level) on HP-UX.
    If I was going to test this, I'm not really sure what I would look for. I don't have a way to simulate the volume of users and executions we get in prod and then just check the waits.
    Any suggestions on what to look for? I'll need to show some test cases.


    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Chitale, Hemant Krishnarao at Oct 20, 2011 at 2:00 am
    Apparently a few vendors did that 20+ years ago and claimed it was for performance reasons. Never mind that their code was suspiciously absent of bind variables forcing a hard parse for every run of a query where only the string literal value was changed.

    Oracle V7 was released around 1991 or so. That was the first time a "Shared Pool" was introduced. Awareness of Binds was still sometime in the future.


    Hemant K Chitale
    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of David Fitzjarrell
    Sent: Thursday, October 20, 2011 4:53 AM
    To: mark.powell2@hp.com; ORACLE-L
    Subject: Re: primary keys and dictionary overhead

    Apparently a few vendors did that 20+ years ago and claimed it was for performance reasons.  Never mind that their code was suspiciously absent of bind variables forcing a hard parse for every run of a query where only the string literal value was changed.  Never mind that their idea of referential integrity was 'enforced' in the application code and not the database.  Never mind that they also enforced uniqueness in the same way.  Which explains why migrating from one of these types of applications to one which uses database constraints requires far more data cleanup than should be necessary.

    It's funny sometimes what vendors do in the name of  'performance'.
    David Fitzjarrell



    This email and any attachments are confidential and may also be privileged. If you are not the addressee, do not disclose, copy, circulate or in any other way use or rely on the information contained in this email or any attachments. If received in error, notify the sender immediately and delete this email and any attachments from your system. Emails cannot be guaranteed to be secure or error free as the message and any attachments could be intercepted, corrupted, lost, delayed, incomplete or amended. Standard Chartered PLC and its subsidiaries do not accept liability for damage caused by this email or any attachments and may monitor email traffic.

    Standard Chartered PLC is incorporated in England with limited liability under company number 966425 and has its registered office at 1 Aldermanbury Square, London, EC2V 7SB.

    Standard Chartered Bank ("SCB") is incorporated in England with limited liability by Royal Charter 1853, under reference ZC18. The Principal Office of SCB is situated in England at 1 Aldermanbury Square, London EC2V 7SB. In the United Kingdom, SCB is authorised and regulated by the Financial Services Authority under FSA register number 114276.

    If you are receiving this email from SCB outside the UK, please click http://www.standardchartered.com/global/email_disclaimer.html to refer to the information on other jurisdictions.
    --
    http://www.freelists.org/webpage/oracle-l
  • Subodh Deshpande at Oct 20, 2011 at 8:10 am
    20+ years ago it was mostly Oracle 5 with SQL forms 2.0, reports 2.0, sql
    calc, Pro*c and if I remember correctly then at that time there were no
    constraints such as unique, primary etc on tables.forget refereal integrity
    constraints..
    But indexes were very much there..I do not remember where I used/seen any
    unique indexs on tables..not null columns were allowed..
    and yes it is absolutely right...all the business logic was enforced using
    application code.

    Oracle 7 was introduced (rather I remember that I used) some where after
    1996.

    We should be thankful to the fact that atleast some sort of RDBMS was
    present 20 years ago..
    cause in 1989 when I learned cobol we were writing to files...not to
    tables....
    GWBASIC was a toy,,DBASE III+, fox*pro, clipper were known to us..

    At that time I first came across a well structured salary computation
    program using shell programming in Unix environment..
    all the logic of day-today business entry, DSS, MIS was applied using shell
    programming in UNIX..

    During the same time I was fallen in love with DBASEIII+, clipper etc..more
    and when I seen Oracle 5 and 'AMRUT' (Advanced Manufacturing Resource
    Utilisation) ERP application, I realised how foolish I was...

    And all the applications of AMRUT were performing very well...during the Y2K
    we migrated most of the application to Oracle 7 and D2K without more
    trouble...we faced the probems in Pro*c, SQL*Reports..

    Today, if migration of such applications is necessary then understand the
    business logic and built new application..

    thanks..subodh


    On 20 October 2011 07:29, Chitale, Hemant Krishnarao
    wrote:
    Apparently a few vendors did that 20+ years ago and claimed it was for
    performance reasons. Never mind that their code was suspiciously absent of
    bind variables forcing a hard parse for every run of a query where only the
    string literal value was changed.


    Oracle V7 was released around 1991 or so. That was the first time a
    "Shared Pool" was introduced. Awareness of Binds was still sometime in the
    future.


    Hemant K Chitale
    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of David Fitzjarrell
    Sent: Thursday, October 20, 2011 4:53 AM
    To: mark.powell2@hp.com; ORACLE-L
    Subject: Re: primary keys and dictionary overhead

    Apparently a few vendors did that 20+ years ago and claimed it was for
    performance reasons. Never mind that their code was suspiciously absent of
    bind variables forcing a hard parse for every run of a query where only the
    string literal value was changed. Never mind that their idea of referential
    integrity was 'enforced' in the application code and not the database.
    Never mind that they also enforced uniqueness in the same way. Which
    explains why migrating from one of these types of applications to one which
    uses database constraints requires far more data cleanup than should be
    necessary.

    It's funny sometimes what vendors do in the name of 'performance'.
    David Fitzjarrell



    This email and any attachments are confidential and may also be privileged.
    If you are not the addressee, do not disclose, copy, circulate or in any
    other way use or rely on the information contained in this email or any
    attachments. If received in error, notify the sender immediately and delete
    this email and any attachments from your system. Emails cannot be
    guaranteed to be secure or error free as the message and any attachments
    could be intercepted, corrupted, lost, delayed, incomplete or amended.
    Standard Chartered PLC and its subsidiaries do not accept liability for
    damage caused by this email or any attachments and may monitor email
    traffic.

    Standard Chartered PLC is incorporated in England with limited liability
    under company number 966425 and has its registered office at 1 Aldermanbury
    Square, London, EC2V 7SB.

    Standard Chartered Bank ("SCB") is incorporated in England with limited
    liability by Royal Charter 1853, under reference ZC18. The Principal Office
    of SCB is situated in England at 1 Aldermanbury Square, London EC2V 7SB. In
    the United Kingdom, SCB is authorised and regulated by the Financial
    Services Authority under FSA register number 114276.

    If you are receiving this email from SCB outside the UK, please click
    http://www.standardchartered.com/global/email_disclaimer.html to refer to
    the information on other jurisdictions.
    --
    http://www.freelists.org/webpage/oracle-l


    --
    =============================================
    TRUTH WINS AT LAST, DO NOT FORGET TO SMILE TODAY
    =============================================


    --
    http://www.freelists.org/webpage/oracle-l
  • Joel Patterson at Oct 20, 2011 at 2:47 pm
    Don't forget RBASE, the 'relational' dbase :)


    Joel Patterson
    Database Administrator
    904 727-2546

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Subodh Deshpande
    Sent: Thursday, October 20, 2011 4:10 AM
    To: oracle-l-freelists
    Subject: Re: primary keys and dictionary overhead

    20+ years ago it was mostly Oracle 5 with SQL forms 2.0, reports 2.0, sql
    calc, Pro*c and if I remember correctly then at that time there were no
    constraints such as unique, primary etc on tables.forget refereal integrity
    constraints..
    But indexes were very much there..I do not remember where I used/seen any
    unique indexs on tables..not null columns were allowed..
    and yes it is absolutely right...all the business logic was enforced using
    application code.

    Oracle 7 was introduced (rather I remember that I used) some where after
    1996.

    We should be thankful to the fact that atleast some sort of RDBMS was
    present 20 years ago..
    cause in 1989 when I learned cobol we were writing to files...not to
    tables....
    GWBASIC was a toy,,DBASE III+, fox*pro, clipper were known to us..

    At that time I first came across a well structured salary computation
    program using shell programming in Unix environment..
    all the logic of day-today business entry, DSS, MIS was applied using shell
    programming in UNIX..

    During the same time I was fallen in love with DBASEIII+, clipper etc..more
    and when I seen Oracle 5 and 'AMRUT' (Advanced Manufacturing Resource
    Utilisation) ERP application, I realised how foolish I was...

    And all the applications of AMRUT were performing very well...during the Y2K
    we migrated most of the application to Oracle 7 and D2K without more
    trouble...we faced the probems in Pro*c, SQL*Reports..

    Today, if migration of such applications is necessary then understand the
    business logic and built new application..

    thanks..subodh


    On 20 October 2011 07:29, Chitale, Hemant Krishnarao
    wrote:
    Apparently a few vendors did that 20+ years ago and claimed it was for
    performance reasons. Never mind that their code was suspiciously absent of
    bind variables forcing a hard parse for every run of a query where only the
    string literal value was changed.


    Oracle V7 was released around 1991 or so. That was the first time a
    "Shared Pool" was introduced. Awareness of Binds was still sometime in the
    future.


    Hemant K Chitale
    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of David Fitzjarrell
    Sent: Thursday, October 20, 2011 4:53 AM
    To: mark.powell2@hp.com; ORACLE-L
    Subject: Re: primary keys and dictionary overhead

    Apparently a few vendors did that 20+ years ago and claimed it was for
    performance reasons. Never mind that their code was suspiciously absent of
    bind variables forcing a hard parse for every run of a query where only the
    string literal value was changed. Never mind that their idea of referential
    integrity was 'enforced' in the application code and not the database.
    Never mind that they also enforced uniqueness in the same way. Which
    explains why migrating from one of these types of applications to one which
    uses database constraints requires far more data cleanup than should be
    necessary.

    It's funny sometimes what vendors do in the name of 'performance'.
    David Fitzjarrell



    This email and any attachments are confidential and may also be privileged.
    If you are not the addressee, do not disclose, copy, circulate or in any
    other way use or rely on the information contained in this email or any
    attachments. If received in error, notify the sender immediately and delete
    this email and any attachments from your system. Emails cannot be
    guaranteed to be secure or error free as the message and any attachments
    could be intercepted, corrupted, lost, delayed, incomplete or amended.
    Standard Chartered PLC and its subsidiaries do not accept liability for
    damage caused by this email or any attachments and may monitor email
    traffic.

    Standard Chartered PLC is incorporated in England with limited liability
    under company number 966425 and has its registered office at 1 Aldermanbury
    Square, London, EC2V 7SB.

    Standard Chartered Bank ("SCB") is incorporated in England with limited
    liability by Royal Charter 1853, under reference ZC18. The Principal Office
    of SCB is situated in England at 1 Aldermanbury Square, London EC2V 7SB. In
    the United Kingdom, SCB is authorised and regulated by the Financial
    Services Authority under FSA register number 114276.

    If you are receiving this email from SCB outside the UK, please click
    http://www.standardchartered.com/global/email_disclaimer.html to refer to
    the information on other jurisdictions.
    --
    http://www.freelists.org/webpage/oracle-l


    --
    =============================================
    TRUTH WINS AT LAST, DO NOT FORGET TO SMILE TODAY
    =============================================


    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Niall Litchfield at Oct 20, 2011 at 8:48 am
    Oracle 7 was also the first release with declarative referential integrity.
    So up until that point not only were string literals the standard way to
    program against databases, ensuring data integrity was a job for the
    database programmer in code rather than in database. Then you have the
    education cycle, the adoption cycle and the release cycle. It seems to me
    that any commercial product released before about 1995/1996 that used
    declarative referential integrity and bind variables would have been way
    ahead of its time - and almost certainly hit bugs with the new features.

    On Thu, Oct 20, 2011 at 2:59 AM, Chitale, Hemant Krishnarao <
    Hemant.Chitale@sc.com> wrote in response to David:
    Apparently a few vendors did that 20+ years ago and claimed it was for
    performance reasons. Never mind that their code was suspiciously absent of
    bind variables forcing a hard parse for every run of a query where only the
    string literal value was changed.


    Oracle V7 was released around 1991 or so. That was the first time a
    "Shared Pool" was introduced. Awareness of Binds was still sometime in the
    future.


    Hemant K Chitale
    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of David Fitzjarrell
    Sent: Thursday, October 20, 2011 4:53 AM
    To: mark.powell2@hp.com; ORACLE-L
    Subject: Re: primary keys and dictionary overhead

    Apparently a few vendors did that 20+ years ago and claimed it was for
    performance reasons. Never mind that their code was suspiciously absent of
    bind variables forcing a hard parse for every run of a query where only the
    string literal value was changed. Never mind that their idea of referential
    integrity was 'enforced' in the application code and not the database.
    Never mind that they also enforced uniqueness in the same way. Which
    explains why migrating from one of these types of applications to one which
    uses database constraints requires far more data cleanup than should be
    necessary.

    It's funny sometimes what vendors do in the name of 'performance'.
    David Fitzjarrell



    This email and any attachments are confidential and may also be privileged.
    If you are not the addressee, do not disclose, copy, circulate or in any
    other way use or rely on the information contained in this email or any
    attachments. If received in error, notify the sender immediately and delete
    this email and any attachments from your system. Emails cannot be
    guaranteed to be secure or error free as the message and any attachments
    could be intercepted, corrupted, lost, delayed, incomplete or amended.
    Standard Chartered PLC and its subsidiaries do not accept liability for
    damage caused by this email or any attachments and may monitor email
    traffic.

    Standard Chartered PLC is incorporated in England with limited liability
    under company number 966425 and has its registered office at 1 Aldermanbury
    Square, London, EC2V 7SB.

    Standard Chartered Bank ("SCB") is incorporated in England with limited
    liability by Royal Charter 1853, under reference ZC18. The Principal Office
    of SCB is situated in England at 1 Aldermanbury Square, London EC2V 7SB. In
    the United Kingdom, SCB is authorised and regulated by the Financial
    Services Authority under FSA register number 114276.

    If you are receiving this email from SCB outside the UK, please click
    http://www.standardchartered.com/global/email_disclaimer.html to refer to
    the information on other jurisdictions.
    --
    http://www.freelists.org/webpage/oracle-l


    --
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info


    --
    http://www.freelists.org/webpage/oracle-l
  • Subodh Deshpande at Oct 20, 2011 at 10:13 am
    first I will share my experience..
    yes, DBA was just responsible for backup purpose..mostly used as support
    team group member..as database operator..

    lead programmer or senior programmer used to take care of overall
    functioning of applications...
    atleast in a month entire root backup(OS level as well as application level)
    process was carried out..using standard scripts

    We (our entire team) used to face slowness in functioning because oracle was
    new to us at that time, hence architecture of oracle was not known
    completely..slowness in application was because of limited use tablespaces,
    system tablespace used to come into picture when usertablspace becomes
    full..we were having lot of problems due to hardware and space.because both
    were very costly at that time..the problems of slowness were not because of
    coding..but because of hardware used and space constraints..

    Time to time our management used to take the descision of migrating the
    application from lower version to higher versions, cause oracle itself is
    very keen on client shoud use the latest versions..

    Upto oracle 7 it was client server technology..when oracle 8i, java, php,
    asp, apache server were introduced webbased application era was started..

    now we all know and use oracle 10g 11g , forms and reports 10g, 10gas,
    weblogic server etc.

    now I coming to you.
    Quote
    Never mind that their code was suspiciously absent of bind variables forcing
    a hard parse for every run of a query where only the
    string literal value was changed
    UnQuote

    if the forms is based on some base tables and if it inserts only one record
    at a time then it is going to happen..these type of forms are generally used
    to insert master data..

    if the form is inserting multiple records using bases tables or even
    procdures multiple records will be inserted..again same sql will be
    fired..these type of forms used for inserting transaction data..

    in all these forms when the you move from one field to another business
    logic, database logic code must be present..

    if you have docs for these it is very easy to do a reverse engineering..if
    not then enter wrong data, error will be thrown (on Vt100 terminals press f7
    key to check the error, base table used and query used, columns used..)

    The only thing which is not changed is SQL..so you should be able to check,
    spool or migrate the data at any time using sql also..

    But remember one thing 20+ yes back application will not run on todays
    present hardware, software..study is important..
    And also business logic has hardly changed..there are buyers and hence
    sellers..hence product exists.
    Todays standard procedures such as intdent, inventory requisition, POs,
    invoices, and account entries have not changed at all..

    so knowing the logic its possible to change the application for today..but
    the past 20+ yrs application can not be used as it is today..

    thanks..subodh


    On 20 October 2011 14:17, Niall Litchfield wrote:

    Oracle 7 was also the first release with declarative referential integrity.
    So up until that point not only were string literals the standard way to
    program against databases, ensuring data integrity was a job for the
    database programmer in code rather than in database. Then you have the
    education cycle, the adoption cycle and the release cycle. It seems to me
    that any commercial product released before about 1995/1996 that used
    declarative referential integrity and bind variables would have been way
    ahead of its time - and almost certainly hit bugs with the new features.

    On Thu, Oct 20, 2011 at 2:59 AM, Chitale, Hemant Krishnarao <
    Hemant.Chitale@sc.com> wrote in response to David:
    Apparently a few vendors did that 20+ years ago and claimed it was for
    performance reasons. Never mind that their code was suspiciously absent of
    bind variables forcing a hard parse for every run of a query where only the
    string literal value was changed.


    Oracle V7 was released around 1991 or so. That was the first time a
    "Shared Pool" was introduced. Awareness of Binds was still sometime in the
    future.


    Hemant K Chitale
    -----Original Message-----
    From: oracle-l-bounce@freelists.org [mailto:
    oracle-l-bounce@freelists.org]
    On Behalf Of David Fitzjarrell
    Sent: Thursday, October 20, 2011 4:53 AM
    To: mark.powell2@hp.com; ORACLE-L
    Subject: Re: primary keys and dictionary overhead

    Apparently a few vendors did that 20+ years ago and claimed it was for
    performance reasons. Never mind that their code was suspiciously absent of
    bind variables forcing a hard parse for every run of a query where only the
    string literal value was changed. Never mind that their idea of
    referential
    integrity was 'enforced' in the application code and not the database.
    Never mind that they also enforced uniqueness in the same way. Which
    explains why migrating from one of these types of applications to one which
    uses database constraints requires far more data cleanup than should be
    necessary.

    It's funny sometimes what vendors do in the name of 'performance'.
    David Fitzjarrell



    This email and any attachments are confidential and may also be
    privileged.
    If you are not the addressee, do not disclose, copy, circulate or in any
    other way use or rely on the information contained in this email or any
    attachments. If received in error, notify the sender immediately and delete
    this email and any attachments from your system. Emails cannot be
    guaranteed to be secure or error free as the message and any attachments
    could be intercepted, corrupted, lost, delayed, incomplete or amended.
    Standard Chartered PLC and its subsidiaries do not accept liability for
    damage caused by this email or any attachments and may monitor email
    traffic.

    Standard Chartered PLC is incorporated in England with limited liability
    under company number 966425 and has its registered office at 1
    Aldermanbury
    Square, London, EC2V 7SB.

    Standard Chartered Bank ("SCB") is incorporated in England with limited
    liability by Royal Charter 1853, under reference ZC18. The Principal Office
    of SCB is situated in England at 1 Aldermanbury Square, London EC2V 7SB. In
    the United Kingdom, SCB is authorised and regulated by the Financial
    Services Authority under FSA register number 114276.

    If you are receiving this email from SCB outside the UK, please click
    http://www.standardchartered.com/global/email_disclaimer.html to refer to
    the information on other jurisdictions.
    --
    http://www.freelists.org/webpage/oracle-l


    --
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info


    --
    http://www.freelists.org/webpage/oracle-l


    --
    =============================================
    TRUTH WINS AT LAST, DO NOT FORGET TO SMILE TODAY
    =============================================


    --
    http://www.freelists.org/webpage/oracle-l
  • Niall Litchfield at Oct 20, 2011 at 12:36 pm
    It's one of those irregular verbs.
    I am experienced
    You are mature
    They are old.


    On Thu, Oct 20, 2011 at 1:30 PM, Andy Klock wrote:

    Please don't take this the wrong way, but you are all old.



    --
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info


    --
    http://www.freelists.org/webpage/oracle-l
  • David Fitzjarrell at Oct 20, 2011 at 4:00 pm
    I don't disagree; I started my career with Oracle 6.0.24 where primary keys and unique indexes were available,as well as hot backups (yes, it was 'bleeding edge' technology then).  That was 1989, and Oracle 7 was about to be unleashed.  I've worked with every release since then (currently on 11.2.0.2) and have been fortunate to have worked on some major projects for major companies in the span of my career.  Having been on both sides of the application fence (development and support) I've been familiar with what various releases of Oracle had available in terms of constraints, indexing, etc. which is why it surprises me (actually galls me but, hey, I want to be nice) that vendors can STILL write crappy (yes, you read that correctly) code that 'enforces' uniqueness and referential integrity outside of the database engine and fails to utilize bind variables, even for 'singleton' inserts.  I could name at least one vendor that still does this today (but
    I won't).  It doesn't matter how many inserts you perform with a given statement, if it will be run repeatedly it should be using bind variables.  Granted with Oracle 6 and Forms 2..3 this wasn't possible but there is no reason in this day and age to continue to write applications as if they are using the first release of Access when they are pointed to a fairly current release of Oracle.  My complaint was not directed at the releases of Oracle prior to Oracle 7, it is directed at current vendors who won't enter the modern age by continuing to write antiquated code so out of touch with reality that even Mr. Peabody and his boy Sherman would need a trip in the WAYBAC machine to view the source.

    Don't take my 'rant' the wrong way -- I've worked along side vendors more than willing to take advice from the field in order to improve their product and increase their  understanding of the database.  There is also the 'flip side' where the vendor code is like 'scripture' and can't be modified by the unwashed ('the DBA').  I suppose we'll be dealing with this ad infinitum/ad nauseam.  And application code isn't the only victim in this; we've been fighting a battle with 'false facts' about Oracle for years and it seems as though the battle  is slowly being won as the influence of 'he who shall remain  nameless' is diminishing; let's hope this trend finds its way into the application realm so that some day the sun will shine, the birds will sing, water will be pure, bind variables will be abundant and queries will never need tuning. <play "Happy Working Song" here>

    My two and one-half cents.  You can keep the change.


    David Fitzjarrell

    From: Andy Klock <andyklock@gmail.com>
    To: "deshpande.subodh@gmail.com" <deshpande.subodh@gmail.com>
    Cc: "oracledbaquestions@gmail.com" <oracledbaquestions@gmail.com>; "niall.litchfield@gmail.com" <niall.litchfield@gmail.com>; "Hemant.Chitale@sc.com" <Hemant.Chitale@sc.com>; "oratune@yahoo.com" <oratune@yahoo.com>; "oracle-l@freelists.org" <oracle-l@freelists.org>
    Sent: Thursday, October 20, 2011 5:30 AM
    Subject: Re: primary keys and dictionary overhead


    Please don't take this the wrong way, but you are all old.
  • Joel Patterson at Oct 20, 2011 at 5:20 pm
    I know two... say financial apps that do not use foreign key constraints.... (oh you might find one or two). and the reason I am told this is so, is that the same app and tables can work in multiple DBMS's.

    Hmmm.... I'm with Tom Kyte in that I'll analogize from his expert oracle book. Their 'different', so code for it.

    But apparently, it is not necessary to take advantage of the individual strengths and differences in a DBMS as it is working for these vendors to have one basic set of code, and not individual sets.

    So the point is that -- well, some of these guys have their reasons. Of course we all have our reasons for agreeing or disagreeing, the original architects are probably not even available anymore.

    Joel Patterson
    Database Administrator
    904 727-2546

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of David Fitzjarrell
    Sent: Thursday, October 20, 2011 11:58 AM
    To: Andy Klock; deshpande.subodh@gmail.com
    Cc: oracledbaquestions@gmail.com; niall.litchfield@gmail.com; Hemant.Chitale@sc.com; oracle-l@freelists.org; Luba Marshalkina
    Subject: Re: primary keys and dictionary overhead

    I don't disagree; I started my career with Oracle 6.0.24 where primary keys and unique indexes were available,as well as hot backups (yes, it was 'bleeding edge' technology then).  That was 1989, and Oracle 7 was about to be unleashed.  I've worked with every release since then (currently on 11.2.0.2) and have been fortunate to have worked on some major projects for major companies in the span of my career.  Having been on both sides of the application fence (development and support) I've been familiar with what various releases of Oracle had available in terms of constraints, indexing, etc. which is why it surprises me (actually galls me but, hey, I want to be nice) that vendors can STILL write crappy (yes, you read that correctly) code that 'enforces' uniqueness and referential integrity outside of the database engine and fails to utilize bind variables, even for 'singleton' inserts.  I could name at least one vendor that still does this today (but
    I won't).  It doesn't matter how many inserts you perform with a given statement, if it will be run repeatedly it should be using bind variables.  Granted with Oracle 6 and Forms 2..3 this wasn't possible but there is no reason in this day and age to continue to write applications as if they are using the first release of Access when they are pointed to a fairly current release of Oracle.  My complaint was not directed at the releases of Oracle prior to Oracle 7, it is directed at current vendors who won't enter the modern age by continuing to write antiquated code so out of touch with reality that even Mr. Peabody and his boy Sherman would need a trip in the WAYBAC machine to view the source.

    Don't take my 'rant' the wrong way -- I've worked along side vendors more than willing to take advice from the field in order to improve their product and increase their  understanding of the database.  There is also the 'flip side' where the vendor code is like 'scripture' and can't be modified by the unwashed ('the DBA').  I suppose we'll be dealing with this ad infinitum/ad nauseam.  And application code isn't the only victim in this; we've been fighting a battle with 'false facts' about Oracle for years and it seems as though the battle  is slowly being won as the influence of 'he who shall remain  nameless' is diminishing; let's hope this trend finds its way into the application realm so that some day the sun will shine, the birds will sing, water will be pure, bind variables will be abundant and queries will never need tuning. <play "Happy Working Song" here>

    My two and one-half cents.  You can keep the change.


    David Fitzjarrell

    From: Andy Klock <andyklock@gmail.com>
    To: "deshpande.subodh@gmail.com" <deshpande.subodh@gmail.com>
    Cc: "oracledbaquestions@gmail.com" <oracledbaquestions@gmail.com>; "niall.litchfield@gmail.com" <niall.litchfield@gmail.com>; "Hemant.Chitale@sc.com" <Hemant.Chitale@sc.com>; "oratune@yahoo.com" <oratune@yahoo.com>; "oracle-l@freelists.org" <oracle-l@freelists.org>
    Sent: Thursday, October 20, 2011 5:30 AM
    Subject: Re: primary keys and dictionary overhead


    Please don't take this the wrong way, but you are all old.
    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Andy Klock at Oct 20, 2011 at 5:34 pm
    I got an automatic reject from Scott Adams stating that my "old" reply
    didn't go through because it was sent from an account that isn't subscribed
    to Oracle-L. I'd been going through my day relieved that you weren't aware
    of how old I thought you guys are. The joke is on me.
    Andy

    From: Andy Klock <andyklock@gmail.com>
    To: "deshpande.subodh@gmail.com" <deshpande.subodh@gmail.com>
    Cc: "oracledbaquestions@gmail.com" <oracledbaquestions@gmail.com>; "
    niall.litchfield@gmail.com" <niall.litchfield@gmail.com>; "
    Hemant.Chitale@sc.com" <Hemant.Chitale@sc.com>; "oratune@yahoo.com" <
    oratune@yahoo.com>; "oracle-l@freelists.org" <oracle-l@freelists.org>
    Sent: Thursday, October 20, 2011 5:30 AM
    Subject: Re: primary keys and dictionary overhead


    Please don't take this the wrong way, but you are all old.
    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • David Fitzjarrell at Oct 20, 2011 at 6:13 pm
    Age is a state of mind and, given the state mine has attained over the  years I'm sure that I'm old, but I just don't care anymore.  :D

    David Fitzjarrell


    From: Andy Klock <andy@oracledepot.com>
    To: Joel.Patterson@crowley.com
    Cc: oratune@yahoo.com; deshpande.subodh@gmail.com; oracledbaquestions@gmail.com; niall.litchfield@gmail.com; Hemant.Chitale@sc.com; oracle-l@freelists.org; witneyl@yahoo.com
    Sent: Thursday, October 20, 2011 10:33 AM
    Subject: Re: primary keys and dictionary overhead

    I got an automatic reject from Scott Adams stating that my "old" reply
    didn't go through because it was sent from an account that isn't subscribed
    to Oracle-L. I'd been going through my day relieved that you weren't aware
    of how old I thought you guys are. The joke is on me.
    Andy

    From: Andy Klock <andyklock@gmail.com>
    To: "deshpande.subodh@gmail.com" <deshpande.subodh@gmail.com>
    Cc: "oracledbaquestions@gmail.com" <oracledbaquestions@gmail.com>; "
    niall.litchfield@gmail.com" <niall.litchfield@gmail.com>; "
    Hemant.Chitale@sc.com" <Hemant.Chitale@sc.com>; "oratune@yahoo.com" <
    oratune@yahoo.com>; "oracle-l@freelists.org" <oracle-l@freelists.org>
    Sent: Thursday, October 20, 2011 5:30 AM
    Subject: Re: primary keys and dictionary overhead


    Please don't take this the wrong way, but you are all old.
    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Subodh Deshpande at Oct 21, 2011 at 6:55 am
    hello,
    I think any sensible person will agree to your second paragraph..

    Quote
    There is also the 'flip side' where the vendor code is like 'scripture' and
    can't be modified by the unwashed ('the DBA').
    UnQuote

    If some code is not functioning proprely, then it should be changed..but
    this kind of mentality is developed when there is lack of documentation and
    the courage to change the code if it is need to change at that time..If your
    users are facing errors and you still think it can not be changed..then
    whats the use of such application..actually we used to have a team to take
    the feedback from endusers about whether they are facing any errors in using
    the application, do they require any new feature..and or do they want any
    other areas that can be bring under this application..

    About change in code,Actually everybody including an enduser is also
    entitiled to recommend the change he/she wants..finally the code is meant to
    run the application..so that the enduser can work and or apply in day today
    life..not to remain the idle code in the application. Ofcourse the enduser
    will always speak his/her language..that is why you have SPRs..we used to
    have meetings on SPRs and also used to communicate the concerned person..

    Lack of proper understandng, poor logical thinking, lack of courage to
    change develops this particualr attitude...
    In India we say 'Baba Wakya Pramanam' means I am doing this cause my
    forefathers have told me to do so...is useless..in todays era like it was in
    ancient era too..

    lets hope that following will happen..especially 'he who shall remain
    nameless'

    Quote
    And application code isn't the only victim in this; we've been fighting a
    battle with 'false facts' about Oracle for years and it seems as though the
    battle is slowly being won as the influence of 'he who shall remain
    nameless' is diminishing; let's hope this trend finds its way into the
    application realm so that some day the sun will shine, the birds will sing,
    water will be pure, bind variables will be abundant and queries will never
    need tuning. <play "Happy Working Song" here>
    UnQuote

    If you are using any application interface then the fields acts as bind
    variables..no alternatives to this...and using a database just by SQL is not
    correct way..it will affect the databse affect itself because of the
    architecture itself..performance and sometimes availability also..and this
    is why a 'userfriendly' application is developed with proper practices of
    QA/QC..all the six-sigma, CMM-lelvels, ISO, ITIL-V3 are to maintain
    'userfiendlyness' not to add the 'cherry on cake' of certain DBA, team
    member and or certain company;s profile :)

    my two cents..:)

    thanks..subodh
    On 20 October 2011 21:28, David Fitzjarrell wrote:

    I don't disagree; I started my career with Oracle 6.0.24 where primary
    keys and unique indexes were available,as well as hot backups (yes, it was
    'bleeding edge' technology then). That was 1989, and Oracle 7 was about to
    be unleashed. I've worked with every release since then (currently on
    11.2.0.2) and have been fortunate to have worked on some major projects for
    major companies in the span of my career. Having been on both sides of the
    application fence (development and support) I've been familiar with what
    various releases of Oracle had available in terms of constraints, indexing,
    etc. which is why it surprises me (actually galls me but, hey, I want to be
    nice) that vendors can STILL write crappy (yes, you read that correctly)
    code that 'enforces' uniqueness and referential integrity outside of the
    database engine and fails to utilize bind variables, even for 'singleton'
    inserts. I could name at least one vendor that still does this today (but I
    won't). It doesn't matter how many inserts you perform with a given
    statement, if it will be run repeatedly it should be using bind variables.
    Granted with Oracle 6 and Forms 2..3 this wasn't possible but there is no
    reason in this day and age to continue to write applications as if they
    are using the first release of Access when they are pointed to a fairly
    current release of Oracle. My complaint was not directed at the releases of
    Oracle prior to Oracle 7, it is directed at current vendors who won't enter
    the modern age by continuing to write antiquated code so out of touch with
    reality that even Mr. Peabody and his boy Sherman would need a trip in the
    WAYBAC machine to view the source.

    Don't take my 'rant' the wrong way -- I've worked along side vendors more
    than willing to take advice from the field in order to improve their product
    and increase their understanding of the database. There is also the 'flip
    side' where the vendor code is like 'scripture' and can't be modified by the
    unwashed ('the DBA'). I suppose we'll be dealing with this ad
    infinitum/ad nauseam. And application code isn't the only victim in this;
    we've been fighting a battle with 'false facts' about Oracle for years and
    it seems as though the battle is slowly being won as the influence of 'he
    who shall remain nameless' is diminishing; let's hope this trend finds its
    way into the application realm so that some day the sun will shine, the
    birds will sing, water will be pure, bind variables will be abundant and
    queries will never need tuning. <play "Happy Working Song" here>

    My two and one-half cents. You can keep the change.


    David Fitzjarrell


    *From:* Andy Klock <andyklock@gmail.com>
    *To:* "deshpande.subodh@gmail.com" <deshpande.subodh@gmail.com>
    *Cc:* "oracledbaquestions@gmail.com" <oracledbaquestions@gmail.com>; "
    niall.litchfield@gmail.com" <niall.litchfield@gmail.com>; "
    Hemant.Chitale@sc.com" <Hemant.Chitale@sc.com>; "oratune@yahoo.com" <
    oratune@yahoo.com>; "oracle-l@freelists.org" <oracle-l@freelists.org>
    *Sent:* Thursday, October 20, 2011 5:30 AM

    *Subject:* Re: primary keys and dictionary overhead

    Please don't take this the wrong way, but you are all old.


    --
    =============================================
    TRUTH WINS AT LAST, DO NOT FORGET TO SMILE TODAY
    =============================================


    --
    http://www.freelists.org/webpage/oracle-l
  • Dba DBA at Oct 21, 2011 at 4:13 pm
    Thank you for the responses. I have been busy and unable to catch up the
    last few days. First off I know to test it myself. I know I can run a
    tkprof. However, I don't think that is sufficient. I can't recreate the
    volumes that we get in production. So I was looking for a little more
    information to see if anyone has seen anything. There is a limit to the
    level of volume tests I can run. Has anyone written a paper or a
    presentation on this?
    As far as UK constraints. We use unique indexes. We don't define them as
    constraints. Functionally its the same thing, but they don't show up in the
    DBA_CONSTRAINTS views. I am not sure how much of a difference that makes in
    data dictionary activity. I have not checked yet.

    As far as the email about a few vendors who insisted doing this and then
    didn't use bind variables. We use bind variables. James Morle was one of the
    guys who worked on this database. One of the guys who is still here worked
    with him and they both decided to pull the constraints and everything out.
    No synonyms. Only grants to public. I think he wrote about some of his
    experiences on another application back then in the Tales From the Oak Table
    book. It might be his own book from the late 1990s. I can't remember. I was
    still in college when they first built this database and had never even
    heard of Oracle.
  • Sidney Chen at Oct 23, 2011 at 4:20 am
    two question:
    1. what's your motivation to add unique constraints into primary keys? it's
    from business request, or it's because you believe it the constraint will
    benifit the performance?
    2. There must be a reason why previous guy decide to pull out the
    constraints, I think there should be a benchmark report to address this
    decision, see if you can get the document. James Mole did not mention
    constraint related design in his exceptional book "Scaling Oracle 8i", but
    he did emphasize the importance of the benchmark in a whole chapter.
  • Subodh Deshpande at Oct 24, 2011 at 5:53 am
    hello,
    Unique key and PK are somewhat explained in this OTN thread.


    https://forums.oracle.com/forums/thread.jspa?threadID=374915

    in nutshell the difference in unique and PK is
    Unique = No duplicate , but allow any number of NULLs
    Primary key = No duplicate and no NULLs
    I think, this sort of design need arises when the application has planty of
    UPDATES than INSERTS..
    i.e. it will come with some default unique data. which will be updated in
    future.eg CRM data, PO schedules etc..

    thanks..subodh
    On 23 October 2011 09:49, Sidney Chen wrote:

    two question:
    1. what's your motivation to add unique constraints into primary keys? it's
    from business request, or it's because you believe it the constraint will
    benifit the performance?
    2. There must be a reason why previous guy decide to pull out the
    constraints, I think there should be a benchmark report to address this
    decision, see if you can get the document. James Mole did not mention
    constraint related design in his exceptional book "Scaling Oracle 8i", but
    he did emphasize the importance of the benchmark in a whole chapter.


    --
    Regards
    Sidney Chen


    --
    http://www.freelists.org/webpage/oracle-l


    --
    =============================================
    TRUTH WINS AT LAST, DO NOT FORGET TO SMILE TODAY
    =============================================


    --
    http://www.freelists.org/webpage/oracle-l
  • Robert Freeman at Oct 23, 2011 at 6:52 pm

    As far as UK constraints. We use unique indexes. We don't define them as
    constraints. Functionally its the same thing, but they don't show up in the
    DBA_CONSTRAINTS views. I am not sure how much of a difference that makes in
    data dictionary activity. I have not checked yet.
    The definition of a constraint *IS* a big deal. A constraint (to quite Ton Kyte) is a semantic thing, it tells people (and the database) things. To quote Tom on this topic, he says
    the following :

    if you have a unique constraint, a data integrity constraint that says "thou are unique" - it only
    makes sense to use.....

    a UNIQUE CONSTRAINT


    a unique index is NOT a unique constraint, it is a unique index. the optimizer will use
    constraints as well as available indexes and so on when deciding on what to do.

    Assert all constraints - if we choose to use a unique index to enforce it, great - but don't create
    a unique index and tell me you have a constraint, you don't. You missed out.
    From: http://asktom.oracle.com/pls/asktom/f?p0:11:0::::P11_QUESTION_ID:8743855576462


    Robert G. Freeman
    Master Principal Consultant, Oracle Corporation, Oracle ACE
    Author of various books on RMAN, New Features and this shorter signature line.
    Blog: http://robertgfreeman.blogspot.com


    Note: THIS EMAIL IS NOT AN OFFICIAL ORACLE SUPPORT COMMUNICATION. It is just the opinion of one Oracle employee. I can be wrong, have been wrong in the past and will be wrong in the future. If your problem is a critical production problem, you should always contact Oracle support for assistance. Statements in this email in no way represent Oracle Corporation or any subsidiaries and reflect only the opinion of the author of this email.


    ________________________________
    From: Dba DBA <oracledbaquestions@gmail.com>
    To: ORACLE-L <oracle-l@freelists.org>
    Sent: Friday, October 21, 2011 9:12 AM
    Subject: Re: primary keys and dictionary overhead

    Thank you for the responses. I have been busy and unable to catch up the
    last few days. First off I know to test it myself. I know I can run a
    tkprof. However, I don't think that is sufficient. I can't recreate the
    volumes that we get in production. So I was looking for a little more
    information to see if anyone has seen anything. There is a limit to the
    level of volume tests I can run. Has anyone written a paper or a
    presentation on this?
    As far as UK constraints. We use unique indexes. We don't define them as
    constraints. Functionally its the same thing, but they don't show up in the
    DBA_CONSTRAINTS views. I am not sure how much of a difference that makes in
    data dictionary activity. I have not checked yet.

    As far as the email about a few vendors who insisted doing this and then
    didn't use bind variables. We use bind variables. James Morle was one of the
    guys who worked on this database. One of the guys who is still here worked
    with him and they both decided to pull the constraints and everything out.
    No synonyms. Only grants to public. I think he wrote about some of his
    experiences on another application back then in the Tales From the Oak Table
    book. It might be his own book from the late 1990s. I can't remember. I was
    still in college when they first built this database and had never even
    heard of Oracle.
  • Robert Freeman at Oct 23, 2011 at 7:45 pm
    Comments in brackets mine to add clarity...
    If some [vendor supplied COTS type] code is not functioning properly, then it should be changed..but this kind of mentality is developed when there is lack of documentation and the courage to change the code if it is need to change at that time..
    Seat of your pants computing, gotta love it.....


    I would disagree with this statement for many reasons. There are some very good and valid reasons not to go fudding around with vendor supplied code. These include:

    1. The potential to loose vendor support.
    2. The potential to loose track of all of the changes you have made to a code tree that is not your code tree.
    3. The potential to accidentally rewrite the code/SQL in such a way that it's functionality is no longer correct.
    4. The problems involved with future upgrades of the vendor package because of the changes that have been made.
    5. The potential legal issues revolving around changing the vendor code (ie: reverse engineering violating contractual agreements).


    I would agree that if there are problems with vendor supplied code that those problems need to be addressed. However from an Enterprise point of view (read -  NOT SEAT OF YOUR FRIGGIN PANTS) this needs to be dealt with by the vendor and through the support channels that you have established with the vendor. This isn't about courage. It's about running an Enterprise in a mature, methodical and common sense kind of way.


    If your response is that the vendor does not supply is with good support - then who's fault is that really? Who picked out the vendor and didn't ask questions about future support? Who didn't pay for support? Who is responsible for having bought this package in the first place? All of the reasons to have the change vendor supplied code, in the end, really boil down to some very bad decision making at some point on the part of the people who actually bought that code.

    There are unfortunate times when you have to bite the bullet and do what you can. Perhaps the vendor is out of business or perhaps they no longer provide support for the version you are on (or my favorite is that we have our own internal application that we lost the code tree too). Regardless, the reasons that you get in these positions are generally due to poor poor laxidasial management of our Enterprise assets. The same attitude that would lead one to *carelessly* change vendor code is the same attitude that makes it a requirement to do so. In my opinion.

    Lack of proper understandng, poor logical thinking, lack of courage to change develops this particualr attitude... In India we say 'Baba Wakya Pramanam' means I am doing this cause my
    forefathers have told me to do so...is useless..in todays era like it was in ancient era too..
    I think that our forefathers had some pretty damned good ideas that should not be so lightly thrown out. I also think that they should also not be just followed blindly without thinking. The real danger is that we don't respect the benefit of our forefathers thinking while at the same time realizing that times do, in fact, change and that their thinking was predicated on truths that may no longer exist.


    One thing I do see, a lot, is this gulf between what I see as the professional IT types and the seat-of-their pants IT types. The professional IT person is typically very cautious, most especially the older ones who came from the days when computing power really cost something. The seat of the pants types have no fear, because they don't stop to think. Development methodologies such as Agile (which I love by the way) really encourage these types, and yet they only seem to accept the parts of it that appeal to their ADHD personalities. They are quick to dismiss the rigor and methodical beauty and *stability* that traditional data processing techniques offer. Examples? I've seen plenty of agile projects that have failed because the development team didn't want to be bothered to gather requirements ahead of time to any real degree, or because the momentum of the planned iterations was such that they could not see (or would not see) the snowballing path of
    death that they were creating for themselves. I kind of come from a mix of the "old-school" and the "new-school", and as such I can see the benefits of each, and the downsides of each approach. I tend to fall in the middle then.

    I think that it's not fair to label those with a cautious approach as lacking proper understanding, or question their ability to think logically or lack courage. Typically the difference between those that you describe and those who do not posses those qualities is experience or personality (or both).There is a place for a seat-of-your-pants thinker and a place for the traditional take your time and think about what your going to do traditionalist.... The best managers, in my opinion, are in-between and can pretty quickly determine which approach is the most appropriate.

    and using a database just by SQL is not correct way
    Wow... this is a very dangerous statement to make and I hope you can please clarify your point-of-view here. Using SQL set processing in the database is so often almost always the right choice that I have to believe that I'm not understanding your point correctly here. I have seen case after case after case where SQL set processing trumps anything procedural in terms of performance. Just because we HAVE PL/SQL does not mean we need to be using PL/SQL. Just because we have Java or C#, or whatever programming language does not mean that the data processing needs, or should, happen at that layer.

    I will grant that there are specific use cases where processing might need to happen at a different layer (for example, true Big Data) than the relational database itself. There are also clearly cases where specific types of processing require different database architectures (ie: OLTP vs. Data warehouses) but at the end of the day, you will get much better data processing performance out of a database by doing that processing at the database layer most of the time. There are always exceptions to every rule, of course.... but they are exceptions, not general cases.

    For me, what concerns me is the laid back approach I see with respect to data processing. More and more I see developers who for various reasons want to go play with relational alternatives (ie NoSQL and in memory kinds of databases) for many reasons (lack of agility, licensing, lack of knowledge, etc). IMHO, all this does is make the stack more complex in the end. The problem is that the seat-of-your-pants folks don't seem to get that all the time.... they are just working with a cool stack (never mind if it's buggy and bleading edge).

    And that is where the voice of your forefathers rightly should be stepping in and saying "Hold on there folks", making you step back and assess the decisions being made.

    My opinion... YMMV.....

    Robert



    Robert G. Freeman
    Master Principal Consultant, Oracle Corporation, Oracle ACE
    Author of various books on RMAN, New Features and this shorter signature line.
    Blog: http://robertgfreeman.blogspot.com


    Note: THIS EMAIL IS NOT AN OFFICIAL ORACLE SUPPORT COMMUNICATION. It is just the opinion of one Oracle employee. I can be wrong, have been wrong in the past and will be wrong in the future. If your problem is a critical production problem, you should always contact Oracle support for assistance. Statements in this email in no way represent Oracle Corporation or any subsidiaries and reflect only the opinion of the author of this email.


    ________________________________
    From: Subodh Deshpande <deshpande.subodh@gmail.com>
    To: David Fitzjarrell <oratune@yahoo.com>
    Cc: Andy Klock <andyklock@gmail.com>; "oracledbaquestions@gmail.com" <oracledbaquestions@gmail.com>; "niall.litchfield@gmail.com" <niall.litchfield@gmail.com>; "Hemant.Chitale@sc.com" <Hemant.Chitale@sc.com>; "oracle-l@freelists.org" <oracle-l@freelists.org>; Luba Marshalkina <witneyl@yahoo.com>
    Sent: Thursday, October 20, 2011 11:50 PM
    Subject: Re: primary keys and dictionary overhead

    hello,
    I think any sensible person will agree to your second paragraph..

    Quote
    There is also the 'flip side' where the vendor code is like 'scripture' and
    can't be modified by the unwashed ('the DBA').
    UnQuote

    If some code is not functioning proprely, then it should be changed..but
    this kind of mentality is developed when there is lack of documentation and
    the courage to change the code if it is need to change at that time..If your
    users are facing errors and you still think it can not be changed..then
    whats the use of such application..actually we used to have a team to take
    the feedback from endusers about whether they are facing any errors in using
    the application, do they require any new feature..and or do they want any
    other areas that can be bring under this application..

    About change in code,Actually everybody including an enduser is also
    entitiled to recommend the change he/she wants..finally the code is meant to
    run the application..so that the enduser can work and or apply in day today
    life..not to remain the idle code in the application. Ofcourse the enduser
    will always speak his/her language..that is why you have SPRs..we used to
    have meetings on SPRs and also used to communicate the concerned person..

    Lack of proper understandng, poor logical thinking, lack of courage to
    change develops this particualr attitude...
    In India we say 'Baba Wakya Pramanam' means I am doing this cause my
    forefathers have told me to do so...is useless..in todays era like it was in
    ancient era too..

    lets hope that following will happen..especially 'he who shall remain
    nameless'

    Quote
    And application code isn't the only victim in this; we've been fighting a
    battle with 'false facts' about Oracle for years and it seems as though the
    battle  is slowly being won as the influence of 'he who shall remain
    nameless' is diminishing; let's hope this trend finds its way into the
    application realm so that some day the sun will shine, the birds will sing,
    water will be pure, bind variables will be abundant and queries will never
    need tuning. <play "Happy Working Song" here>
    UnQuote

    If you are using any application interface then the fields acts as bind
    variables..no alternatives to this...and using a database just by SQL is not
    correct way..it will affect the databse affect itself because of the
    architecture itself..performance and sometimes availability also..and this
    is why a 'userfriendly' application is developed with proper practices of
    QA/QC..all the six-sigma, CMM-lelvels, ISO, ITIL-V3 are to maintain
    'userfiendlyness' not to add the 'cherry on cake' of certain DBA, team
    member and or certain company;s profile :)

    my two cents..:)

    thanks..subodh
    On 20 October 2011 21:28, David Fitzjarrell wrote:

    I don't disagree; I started my career with Oracle 6.0.24 where primary
    keys and unique indexes were available,as well as hot backups (yes, it was
    'bleeding edge' technology then).  That was 1989, and Oracle 7 was about to
    be unleashed.  I've worked with every release since then (currently on
    11.2.0.2) and have been fortunate to have worked on some major projects for
    major companies in the span of my career.  Having been on both sides of the
    application fence (development and support) I've been familiar with what
    various releases of Oracle had available in terms of constraints, indexing,
    etc. which is why it surprises me (actually galls me but, hey, I want to be
    nice) that vendors can STILL write crappy (yes, you read that correctly)
    code that 'enforces' uniqueness and referential integrity outside of the
    database engine and fails to utilize bind variables, even for 'singleton'
    inserts.  I could name at least one vendor that still does this today (but I
    won't).  It doesn't matter how many inserts you perform with a given
    statement, if it will be run repeatedly it should be using bind variables.
    Granted with Oracle 6 and Forms 2..3 this wasn't possible but there is no
    reason in this day and age to continue to write applications as if they
    are using the first release of Access when they are pointed to a fairly
    current release of Oracle.  My complaint was not directed at the releases of
    Oracle prior to Oracle 7, it is directed at current vendors who won't enter
    the modern age by continuing to write antiquated code so out of touch with
    reality that even Mr. Peabody and his boy Sherman would need a trip in the
    WAYBAC machine to view the source.

    Don't take my 'rant' the wrong way -- I've worked along side vendors more
    than willing to take advice from the field in order to improve their product
    and increase their  understanding of the database.  There is also the 'flip
    side' where the vendor code is like 'scripture' and can't be modified by the
    unwashed ('the DBA').  I suppose we'll be dealing with this ad
    infinitum/ad nauseam.  And application code isn't the only victim in this;
    we've been fighting a battle with 'false facts' about Oracle for years and
    it seems as though the battle  is slowly being won as the influence of 'he
    who shall remain  nameless' is diminishing; let's hope this trend finds its
    way into the application realm so that some day the sun will shine, the
    birds will sing, water will be pure, bind variables will be abundant and
    queries will never need tuning. <play "Happy Working Song" here>

    My two and one-half cents.  You can keep the change.


    David Fitzjarrell


    *From:* Andy Klock <andyklock@gmail.com>
    *To:* "deshpande.subodh@gmail.com" <deshpande.subodh@gmail.com>
    *Cc:* "oracledbaquestions@gmail.com" <oracledbaquestions@gmail.com>; "
    niall.litchfield@gmail.com" <niall.litchfield@gmail.com>; "
    Hemant.Chitale@sc.com" <Hemant.Chitale@sc.com>; "oratune@yahoo.com" <
    oratune@yahoo.com>; "oracle-l@freelists.org" <oracle-l@freelists.org>
    *Sent:* Thursday, October 20, 2011 5:30 AM

    *Subject:* Re: primary keys and dictionary overhead

    Please don't take this the wrong way, but you are all old.


    --
    =============================================
    TRUTH WINS AT LAST, DO NOT FORGET TO SMILE TODAY
    =============================================


    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Chitale, Hemant Krishnarao at Oct 24, 2011 at 3:48 am
    Robert :
    Suggestions :

    1. Convert this to a Blog Post

    2. Write this up as an Article worth publishing in a few journals



    I agree with

    "I think that our forefathers had some pretty damned good ideas that
    should not be so lightly thrown out"

    "The professional IT person is typically very cautious"

    "Using SQL set processing in the database is so often almost always the
    right choice"

    "...what concerns me is the laid back approach I see with respect to
    data processing."

    "...that is where the voice of your forefathers rightly should be
    stepping in and saying "Hold on there folks", making you step back and
    assess the decisions being made"


    Hemant K Chitale





    From: Robert Freeman
    Sent: Monday, October 24, 2011 3:44 AM
    To: deshpande.subodh@gmail.com; David Fitzjarrell
    Cc: Andy Klock; oracledbaquestions@gmail.com;
    niall.litchfield@gmail.com; Chitale, Hemant Krishnarao;
    oracle-l@freelists.org; Luba Marshalkina
    Subject: Re: primary keys and dictionary overhead



    Comments in brackets mine to add clarity...


    If some [vendor supplied COTS type] code is not functioning properly,
    then it should be changed..but this kind of mentality is developed when
    there is lack of documentation and the courage to change the code if it
    is need to change at that time..



    Seat of your pants computing, gotta love it.....



    I would disagree with this statement for many reasons. There are some
    very good and valid reasons not to go fudding around with vendor
    supplied code. These include:



    1. The potential to loose vendor support.

    2. The potential to loose track of all of the changes you have made to a
    code tree that is not your code tree.

    3. The potential to accidentally rewrite the code/SQL in such a way that
    it's functionality is no longer correct.

    4. The problems involved with future upgrades of the vendor package
    because of the changes that have been made.

    5. The potential legal issues revolving around changing the vendor code
    (ie: reverse engineering violating contractual agreements).



    I would agree that if there are problems with vendor supplied code that
    those problems need to be addressed. However from an Enterprise point of
    view (read - NOT SEAT OF YOUR FRIGGIN PANTS) this needs to be dealt
    with by the vendor and through the support channels that you have
    established with the vendor. This isn't about courage. It's about
    running an Enterprise in a mature, methodical and common sense kind of
    way.



    If your response is that the vendor does not supply is with good support
    - then who's fault is that really? Who picked out the vendor and didn't
    ask questions about future support? Who didn't pay for support? Who is
    responsible for having bought this package in the first place? All of
    the reasons to have the change vendor supplied code, in the end, really
    boil down to some very bad decision making at some point on the part of
    the people who actually bought that code.



    There are unfortunate times when you have to bite the bullet and do what
    you can. Perhaps the vendor is out of business or perhaps they no longer
    provide support for the version you are on (or my favorite is that we
    have our own internal application that we lost the code tree too).
    Regardless, the reasons that you get in these positions are generally
    due to poor poor laxidasial management of our Enterprise assets. The
    same attitude that would lead one to *carelessly* change vendor code is
    the same attitude that makes it a requirement to do so. In my opinion.


    Lack of proper understandng, poor logical thinking, lack of courage
    to change develops this particualr attitude... In India we say 'Baba
    Wakya Pramanam' means I am doing this cause my
    forefathers have told me to do so...is useless..in todays era like it
    was in ancient era too..

    I think that our forefathers had some pretty damned good ideas that
    should not be so lightly thrown out. I also think that they should also
    not be just followed blindly without thinking. The real danger is that
    we don't respect the benefit of our forefathers thinking while at the
    same time realizing that times do, in fact, change and that their
    thinking was predicated on truths that may no longer exist.



    One thing I do see, a lot, is this gulf between what I see as the
    professional IT types and the seat-of-their pants IT types. The
    professional IT person is typically very cautious, most especially the
    older ones who came from the days when computing power really cost
    something. The seat of the pants types have no fear, because they don't
    stop to think. Development methodologies such as Agile (which I love by
    the way) really encourage these types, and yet they only seem to accept
    the parts of it that appeal to their ADHD personalities. They are quick
    to dismiss the rigor and methodical beauty and *stability* that
    traditional data processing techniques offer. Examples? I've seen plenty
    of agile projects that have failed because the development team didn't
    want to be bothered to gather requirements ahead of time to any real
    degree, or because the momentum of the planned iterations was such that
    they could not see (or would not see) the snowballing path of death that
    they were creating for themselves. I kind of come from a mix of the
    "old-school" and the "new-school", and as such I can see the benefits of
    each, and the downsides of each approach. I tend to fall in the middle
    then.



    I think that it's not fair to label those with a cautious approach as
    lacking proper understanding, or question their ability to think
    logically or lack courage. Typically the difference between those that
    you describe and those who do not posses those qualities is experience
    or personality (or both). There is a place for a seat-of-your-pants
    thinker and a place for the traditional take your time and think about
    what your going to do traditionalist.... The best managers, in my
    opinion, are in-between and can pretty quickly determine which approach
    is the most appropriate.


    and using a database just by SQL is not correct way
    Wow... this is a very dangerous statement to make and I hope you can
    please clarify your point-of-view here. Using SQL set processing in the
    database is so often almost always the right choice that I have to
    believe that I'm not understanding your point correctly here. I have
    seen case after case after case where SQL set processing trumps anything
    procedural in terms of performance. Just because we HAVE PL/SQL does not
    mean we need to be using PL/SQL. Just because we have Java or C#, or
    whatever programming language does not mean that the data processing
    needs, or should, happen at that layer.



    I will grant that there are specific use cases where processing might
    need to happen at a different layer (for example, true Big Data) than
    the relational database itself. There are also clearly cases where
    specific types of processing require different database architectures
    (ie: OLTP vs. Data warehouses) but at the end of the day, you will get
    much better data processing performance out of a database by doing that
    processing at the database layer most of the time. There are always
    exceptions to every rule, of course.... but they are exceptions, not
    general cases.



    For me, what concerns me is the laid back approach I see with respect to
    data processing. More and more I see developers who for various reasons
    want to go play with relational alternatives (ie NoSQL and in memory
    kinds of databases) for many reasons (lack of agility, licensing, lack
    of knowledge, etc). IMHO, all this does is make the stack more complex
    in the end. The problem is that the seat-of-your-pants folks don't seem
    to get that all the time.... they are just working with a cool stack
    (never mind if it's buggy and bleading edge).



    And that is where the voice of your forefathers rightly should be
    stepping in and saying "Hold on there folks", making you step back and
    assess the decisions being made.



    My opinion... YMMV.....



    Robert







    Robert G. Freeman
    Master Principal Consultant, Oracle Corporation, Oracle ACE
    Author of various books on RMAN, New Features and this shorter signature
    line.
    Blog: http://robertgfreeman.blogspot.com

    Note: THIS EMAIL IS NOT AN OFFICIAL ORACLE SUPPORT COMMUNICATION. It is
    just the opinion of one Oracle employee. I can be wrong, have been wrong
    in the past and will be wrong in the future. If your problem is a
    critical production problem, you should always contact Oracle support
    for assistance. Statements in this email in no way represent Oracle
    Corporation or any subsidiaries and reflect only the opinion of the
    author of this email.


    This email and any attachments are confidential and may also be privileged. If you are not the addressee, do not disclose, copy, circulate or in any other way use or rely on the information contained in this email or any attachments. If received in error, notify the sender immediately and delete this email and any attachments from your system. Emails cannot be guaranteed to be secure or error free as the message and any attachments could be intercepted, corrupted, lost, delayed, incomplete or amended. Standard Chartered PLC and its subsidiaries do not accept liability for damage caused by this email or any attachments and may monitor email traffic.

    Standard Chartered PLC is incorporated in England with limited liability under company number 966425 and has its registered office at 1 Aldermanbury Square, London, EC2V 7SB.

    Standard Chartered Bank ("SCB") is incorporated in England with limited liability by Royal Charter 1853, under reference ZC18. The Principal Office of SCB is situated in England at 1 Aldermanbury Square, London EC2V 7SB. In the United Kingdom, SCB is authorised and regulated by the Financial Services Authority under FSA register number 114276.

    If you are receiving this email from SCB outside the UK, please click http://www.standardchartered.com/global/email_disclaimer.html to refer to the information on other jurisdictions.
  • Nuno Souto at Oct 24, 2011 at 10:12 am

    Robert Freeman wrote,on my timestamp of 24/10/2011 6:43 AM:

    If your response is that the vendor does not supply is with
    good support - then who's fault is that really?
    Why, the DBA's of course!
    (expensive little things, they are!...)
    Who picked out the vendor and didn't ask questions about future support?
    Damagement and project damagement.
    Who didn't pay for support?
    Damagement.
    Who is responsible for having bought thispackage in the first place?
    Project Damagement.
    All of the reasons to have the change vendorsupplied code, in the end,
    really boil down to some very bad decision making at some point on
    the part of the people who actually bought that code.
    And of course that was the fault of the "expensive dbas".
    Rotten scoundrels!
    There are unfortunate times when you have to bite the bullet and do what you can.
    Narh! We'll just ask the dumb DBA to go in and fix it. If he fails, we can
    always blame him and his/her cost!
    Perhaps the vendor is out of business or perhaps they no longer provide
    support for the version you are on (or my favorite is that we have our
    own internal application that we lost the code tree too).
    Regardless, the reasons that you get in these positions are generally
    due to poor poor laxidasial management of our Enterprise assets.
    The same attitude that would lead one to *carelessly* change vendor code
    is the same attitude that makes it a requirement to do so. In my opinion.
    Now,now, Robert! You're making too much sense!
    Next, youre gonna claim it's never been the "expensive dba's" fault?
    That's enough frivolity out of you, young man!
    ;-)


    --
    Cheers
    Nuno Souto
    in sunny Sydney, Australia
    dbvision@iinet.net.au
    --
    http://www.freelists.org/webpage/oracle-l
  • Bill thater at Oct 20, 2011 at 10:25 pm
    Age is mind over matter if you don't mind it don't matter me I mind ;-(
    Bill "shrek"Thater
    Shrekdba@Gmail.com
    sent from my phone

    From: Niall Litchfield
    Sent: Thursday, October 20, 2011 7:37 AM
    To: Andy Klock
    Cc: deshpande.subodh@gmail.com; oracledbaquestions@gmail.com;
    Hemant.Chitale@sc.com; oratune@yahoo.com; oracle-l@freelists.org
    Subject: Re: primary keys and dictionary overhead
    It's one of those irregular verbs.
    I am experienced
    You are mature
    They are old.


    On Thu, Oct 20, 2011 at 1:30 PM, Andy Klock wrote:

    Please don't take this the wrong way, but you are all old.



    --
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info


    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Bill thater at Oct 24, 2011 at 10:59 am
    Of course don't Dba meanDoes bloodyAmything?
    Bill "shrek"Thater
    Shrekdba@Gmail.com
    sent from my phone

    From: Nuno Souto
    Sent: Monday, October 24, 2011 5:13 AM
    To: oracle-l@freelists.org
    Subject: Re: primary keys and dictionary overhead
    Robert Freeman wrote,on my timestamp of 24/10/2011 6:43 AM:
    If your response is that the vendor does not supply is with
    good support - then who's fault is that really?
    Why, the DBA's of course!
    (expensive little things, they are!...)
    Who picked out the vendor and didn't ask questions about future support?
    Damagement and project damagement.
    Who didn't pay for support?
    Damagement.
    Who is responsible for having bought thispackage in the first place?
    Project Damagement.
    All of the reasons to have the change vendorsupplied code, in the end,
    really boil down to some very bad decision making at some point on
    the part of the people who actually bought that code.
    And of course that was the fault of the "expensive dbas".
    Rotten scoundrels!
    There are unfortunate times when you have to bite the bullet and do what you can.
    Narh! We'll just ask the dumb DBA to go in and fix it. If he fails, we can
    always blame him and his/her cost!
    Perhaps the vendor is out of business or perhaps they no longer provide
    support for the version you are on (or my favorite is that we have our
    own internal application that we lost the code tree too).
    Regardless, the reasons that you get in these positions are generally
    due to poor poor laxidasial management of our Enterprise assets.
    The same attitude that would lead one to *carelessly* change vendor code
    is the same attitude that makes it a requirement to do so. In my opinion.
    Now,now, Robert! You're making too much sense!
    Next, youre gonna claim it's never been the "expensive dba's" fault?
    That's enough frivolity out of you, young man!
    ;-)


    --
    Cheers
    Nuno Souto
    in sunny Sydney, Australia
    dbvision@iinet.net.au
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 19, '11 at 8:10p
activeOct 24, '11 at 10:59a
posts25
users14
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase