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

user10507778

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.

Gaz in Oz

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

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.

BPeaslandDBA

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

FatMartinR

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

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

user10507778

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.