Start dropping hints

The most common reason for database performance degradation over time is data growth.   Databases have a habit of getting larger rather than smaller and the database server has to work smarter to sift out and operate on the minimal set of data to satisfy queries.   Ask yourself, "if this database were smaller would it run faster?"

The Oracle database calculates the best execution plan - the plan that attracts the least work - through the use of optimizer statistics.   These statistics tell the optimizer how large tables are, how many rows they contain, minimum and maximum values, cardinality (selectivity), etc.  Without these statistics the optimizer is blind and has to rely on some very basic assumptions.

I often work with customers who are upgrading or migrating their databases and discover that many do not maintain optimizer statistics, sometimes on purpose.  The primary concern is usually around performance regressions, to the degree that they also try and peg the database back by setting compatibility and optimizer version to a previous release.

Of course even with good statistics the optimizer may not choose the optimal or even a good plan but that is the exception.  The accepted goal is to have less than 1% of your SQL using execution plan influencing features, such as Plan Management or SQL Profiles.   The most common method to influence the execution plan is to use hints in the SQL statement.  In practice hints are used not because the optimizer cannot produce a good plan, but because it is compromised by inadequate statistics gathering and maintenance.

Adding correct hints to a SQL statement is a skilled task and should be treated as a short term measure and only used if a good plan cannot be produced using correct statistics.  The need for a hint in these circumstances may even be reason to raise a bug.  A hint is a countermeasure for an optimizer limitation or bug.

It should be possible to preserve performance in spite of growing data volumes and shifting selectivity purely by leveraging the optimizer and feeding it good statistics.  If you have a lot of hinted SQL and out of date, missing or inadequate statistics - note later database releases introduce more effective statistics gathering and recording - then you can easily test the impact of dropping those hints and letting the optimizer do its best with new statistics by enabling pending statistics and setting _optimizer_ignore_hints at session level. This is most easily performed using a SQL Tuning Set and SQL Performance Analyzer.

Give it a try, you may be surprised.  I have seen customers getting orders of magnitude improvements.  Not only can they simplify their SQL but database performance becomes managed by the optimizer and the DBAs.