Peter Ramm's Oracle performance analysis stuff

SQL Translation Framework is one of the more hidden features of database release 12.1. It allows to completely replace SQL text with replacement text before processing this SQL. Primary aim for SQL Translation Framework is migrating client applications from other SQL dialects, but it is also a sharp weapon for performance troubleshooting. Compared with SQL plan baselines or SQL profiles it is not restricted to execution plan or optimizer hints, instead allows to replace the complete SQL text. This way this framework allows to fix any possible issues that may be solved by changes on SQL text without any change on the application executing the SQL.  You can rewrite the whole SQL-statement, e.g. add missing JOIN- or WHERE-conditions, extract parts into WITH-clause etc. Limitations for SQL text replacements are only: the result structure (number, order and types of result columns) must remain stable the bind variables must also remain stable regarding number, alias, order ...

Using literals instead of bind variables in SQLs may have several drawbacks: Performance slows down because of: worse cache hit ratio higher parse effort Growing SQL-area in shared pool pushes other major memory objects outside, especially DB-cache Each executed SQL has to be hard parsed separately Risk of SQL-injection may exist (depending from source of literal) Methods to find heavy executed SQLs with missing bind variables 1. Scan active session history for different SQL-ID but same execution plan WITH Ret AS (SELECT :Consider_Days_Back Days FROM DUAL) SELECT x.SQL_Plan_Hash_Value, x.Different_SQL_IDs, x.Last_Used_SQL_ID, u.UserName, x.First_Occurrence, x.Last_Occurrence, x.Elapsed_Secs FROM ( SELECT h.SQL_Plan_Hash_Value, COUNT(DISTINCT h.SQL_ID) Different_SQL_IDs, MAX(h.SQL_ID) KEEP (DENSE_RANK LAST ORDER BY h.Sample_Time) Last_Used_SQL_ID, User_ID, MIN(h.Sample_Time) First_Occurrence, MAX(h....