~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Findings and Recommendations ----------------------------
Finding 1: PL/SQL Execution Impact is .18 active sessions, 50% of total activity. ----------------------------------------------------- PL/SQL execution consumed significant database time.
Recommendation 1: SQL Tuning Estimated benefit is .18 active sessions, 50% of total activity. ---------------------------------------------------------------- Action Tune the entry point PL/SQL "OWI.LIBRARY_CACHE_LATCH.DO_PARSE" of type "PACKAGE" and ID 87652. Refer to the PL/SQL documentation for addition information. Rationale 104 seconds spent in executing PL/SQL "SYS.DBMS_SQL.OPEN_CURSOR#1" of type "PACKAGE" and ID 5063. Rationale 74 seconds spent in executing PL/SQL "SYS.DBMS_SQL.CLOSE_CURSOR" of type "PACKAGE" and ID 5063.
Finding 2: Soft Parse Impact is .13 active sessions, 36.77% of total activity. -------------------------------------------------------- Soft parsing of SQL statements was consuming significant database time.
Recommendation 1: Application Analysis Estimated benefit is .13 active sessions, 36.77% of total activity. ------------------------------------------------------------------- Action Investigate application logic to keep open the frequently used cursors. Note that cursors are closed by both cursor close calls and session disconnects.
Recommendation 2: Database Configuration Estimated benefit is .13 active sessions, 36.77% of total activity. ------------------------------------------------------------------- Action Consider increasing the session cursor cache size by increasing the value of parameter "session_cached_cursors". Rationale The value of parameter "session_cached_cursors" was "50" during the analysis period.
Finding 3: Shared Pool Latches Impact is .06 active sessions, 16.34% of total activity. -------------------------------------------------------- Contention for latches related to the shared pool was consuming significant database time. Waits for "library cache: mutex X" amounted to 12% of database time. Waits for "cursor: mutex S" amounted to 1% of database time. Waits for "cursor: pin S" amounted to 2% of database time.
No recommendations are available.
Symptoms That Led to the Finding: --------------------------------- Wait class "Concurrency" was consuming significant database time. Impact is .06 active sessions, 16.47% of total activity.
Finding 4: CPU Usage Impact is .02 active sessions, 5.61% of total activity. ------------------------------------------------------- Time spent on the CPU by the instance was responsible for a substantial part of database time.
Recommendation 1: Application Analysis Estimated benefit is .02 active sessions, 5.61% of total activity. ------------------------------------------------------------------ Action Parsing SQL statements were consuming significant CPU. Please refer to other findings in this task about parsing for further details.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |