Forum Stats

  • 3,824,844 Users
  • 2,260,430 Discussions
  • 7,896,328 Comments

Discussions

Provide a way for DBA to prevent use of the /* +APPEND */ hint

user10507778
user10507778 Member Posts: 2 Blue Ribbon
edited Oct 30, 2019 6:44PM in Database Ideas - Ideas

Vendor software is rebuilding changed rows first by a) deleting the rows from a table,  b) then re-inserting them using the /* +APPEND */ hint.  Which inserts the data above the high water mark (HWM), making the space freed up by the delete unusable.  This causes the table to grow each event, even if row counts stay same.  In some cases we have a 30G table, that only contains 1.5G of data.  The other 28G is not usuable.   We can't control if the vendor will change their code.  So I would like to see a way to prevent/override the append hint from doing a direct path load.

  • DB level:  I don't know if an init parameter at the DB level would work, or if that could impact vanilla Oracle processing.
  • Session level:  I don't know if we could configure the vendor process to set a session level parameter.
  • Schema level:  Maybe a schema level permission to force it to use existing free space, i.e. "grant disable_append_hint to vendor_schema".

I know implementing this at a more generic level, might prevent legitimate usage by the schema, say if they are pairing it with a truncate instead of a delete.  But having this option would allow us to control misuse by a schema, if we deemed any added impact to be acceptable.

William Robertsonberx
7 votes

Active · Last Updated

Comments

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Oct 30, 2019 11:19PM

    If they really are using "/* +APPEND */" then that is just a comment, not a hint as there is a space between the "/*" and the "+".

    Also if the table is 30G in size but contains only 1.5G data, you can do a table reorganisation using various methods, here's a few:

    . Expdp/impdp

    . dbms_redefinition

    . CTAS

    . ALTER TABLE ... MOVE

    ...

    If you want to switch off ALL hints then you could make use of the hidden parameter "_optimizer_ignore_hints", note how ever use of these parameters should be used under the advisement or Oracle Suppoer.

    For example for session level:

    alter session set "_optimizer_ignore_hints" = true;

    ...

    It would be far better to speak with the vendor and as them why they are doing "it" this way in the first place. I doublt there is much of a performance gain with loading in direct path mode any way on what would apear to be small data sets..., but again you would need to do some testing yourself, or better, get the vendor to justify why they are using this hint.

  • mtefft
    mtefft Member Posts: 844 Gold Badge

    You could add a do-nothing trigger to fire on insert. That is one of the disqualifying conditions against direct-path insert.

    See https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/INSERT.html#GUID-903F8043-0254-4EE9-ACC1-CB8AC0AF3…

    or similar reference for the version of interest.

    William Robertson
  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond

    I would work with the third party vendor. You are not their only customer with this problem.

  • FatMartinR
    FatMartinR Member Posts: 190 Blue Ribbon

    So, just shrink the table.      ALTER TABLE ... SHRINK SPACE.

    https://oracle-base.com/articles/misc/alter-table-shrink-space-online

  • user10507778
    user10507778 Member Posts: 2 Blue Ribbon

    Thank you all for your comments.  For additional info, we have the data partitioned into weekly buckets, and we do a partition shrink every week.  It usually takes 2 hours to reclaim the space in the specific weekly partition.   The only reason I went to weekly partitions was to be able to get the recurring shrinks done in a "reasonable amount of time".  The usage issue has been brought to the vendor, but we have no control over if/when they might re-engineer the process.