Wondering, can hints work in PL SQL packages?

Recently, I have to tune a long-running query in a PLSQL package because it causes a "snapshot too old" issue. I got the query out of the package and tuned it individually. I used the required hints for my case to tune that query and its running time significantly decreased. But I am not sure whether hints work in PL/SQL package as well. Could please clarify whether they can work in PL/SQL packages or not?

Thanks in advance

Regards

CodePudding user response：

Why wouldn't they work? It is not the package itself that contains hints; it is just a container for your functions, procedures, types, ... Code you have within these program units (can) have hints, and they work just the same as they work in pure SQL.

On the other hand, just being curious: what does that procedure - that raised the "snapshot too old" error - do? Is there, by any chance, a loop with a COMMIT within the loop? If so, don't do that because committing in the loop often causes such an error (which means that maybe you don't need any hints). Or, even better, see if that piece of code can be rewritten so that it doesn't use a loop at all because row-by-row processing can be really slow.

Therefore, consider posting that code, someone might enhance it.

CodePudding user response：

Hints work with SQL queries whether it is adhoc or in sub program (Package/function/Procedure) .

Hints are the additional instruction for optimizer to consider while executing SQL