Solution : https://service.sap.com/sap/support/notes/1093485 (SAP Service marketplace login required)
Summary :
Excessive SQL parsing times in Oracle 10g can significantly hinder performance. Key indicators include high values for AVG_HARD_PARSE_TIME in V$SQLSTATS and elevated CPU usage during parsing. In detailed scenarios, parsing may consume over 5% of CPU or total database time, with PARSE_CALLS also peaking. Oracle's Cost Based Optimizer (CBO) might suffer delays in optimizing complex SQL queries, potentially leading to parsing durations that exceed actual execution times. Mitigation steps involve SQL simplification, adjusting the optimizer's configurations (referencing specific Oracle notes and parameters adjustments), and addressing known Oracle bugs that exacerbate parsing times.
Key words :
resource_type total_s parse_s percent------------- ---------- ---------- ----------elapsed time 56365827 43106360 76, resource_type total_s parse_s percent------------- ---------- ---------- ----------elapsed time 2543118 16369 0, cbo checks fewer access combinations, complex bw query requires 60 minutes, select 'elapsed time' resource_type, = 'parse time elapsed'union, component bc-db-ora, sql net break/reset, approaches deliver satisfactory results, oracle cost based optimizer
Related Notes :
| 1235042 | Reparse no longer required to change type of host variables |
| 1037651 | Oracle 10.2.0.2: Long parsing times for complex joins |
| 971261 | Oracle 9.2 / 10.2: Hanging queries with STAR_TRANSFORMATION |
| 846364 | Parse of star query may spin |
| 825653 | Oracle: Common misconceptions |
| 712624 | High CPU consumption by Oracle |