The number of times a dictionary table is repeatedly called by various processes is known as recursive hint. Recursive hint is occurred because of the small size of data dictionary cache.

In Oracle, the term “recursive hints” usually refers to hints used in SQL statements to influence the behavior of recursive SQL statements generated by the database during the processing of the original SQL statement. Recursive SQL statements are those generated by the database to perform certain internal operations, such as managing hierarchical queries, handling integrity constraints, or managing materialized views.

The most common use of recursive hints is in the context of hierarchical queries where the CONNECT BY clause is used to specify the relationship between parent and child rows. Recursive hints can be used to influence how the database processes such queries, providing control over the optimization and execution of recursive SQL.

For example, one common recursive hint is the /*+ NO_CYCLES */ hint, which is used to avoid infinite loops in hierarchical queries by specifying that cycles should not be detected.

Here’s an example of a recursive query with a hint:

SELECT /*+ NO_CYCLES */ employee_id, manager_id

FROM employees

START WITH employee_id = 100

CONNECT BY PRIOR employee_id = manager_id;

In this example, the /*+ NO_CYCLES */ hint is used to instruct Oracle not to detect cycles in the hierarchy.

It’s important to note that the use of hints should be approached with caution, as they directly influence the execution plan chosen by the Oracle optimizer. Hints should be used judiciously and thoroughly tested to ensure they improve performance as intended.