1

I was going through ADDM Reports in Oracle instance of my Project, and I found a SQL Statement appearing in ADDM snapshot consistently, below is one of the recommendations from report by Oracle:

Rationale
       The SQL spent only 0% of its database time on CPU, I/O and Cluster 
       waits. Therefore, the SQL Tuning Advisor is not applicable in this case. 
       Look at performance data for the SQL to find potential improvements.
   Rationale
      Database time for this SQL was divided as follows: 8% for SQL execution, 
      92% for parsing, 0% for PL/SQL execution and 0% for Java execution.
   Rationale
      SQL statement with SQL_ID "aq58n0q3st7xq" was executed 4 times and had 
      an average elapsed time of 4.5 seconds.
   Rationale
      At least 7 distinct execution plans were utilized for this SQL statement 
      during the analysis period.
   Rationale
      Waiting for event "cursor: pin S wait on X" in wait class "Concurrency" 
      accounted for 44% of the database time spent in processing the SQL 
      statement with SQL_ID "aq58n0q3st7xq".

SQL statement is

   SELECT /*+ opt_param('_optimizer_use_feedback','false') */ DISTINCT 
            col_1, col_2 FROM table_name WHERE :B1 = 0 
            OR (col_3 BETWEEN 0 AND :B1 )

We also had a CPU bottleneck in the same hour, and first recommendation in report was to tune Top SQL Statements.

Being in a consulting project, I talked to my Manager about this. Their inputs were that since it is executing just for 4.5 secs and only 4 times so it is not a problem.

This makes me wonder whether this is actually a problem or not. If yes, then what would be the solution ?

M W
  • 13
  • 4
  • 92% of the time was spent on parsing. Since you are using a bind variable (as you should), parsing is done - it will not be done again when the query is rerun. It *really* doesn't look like a problem! –  Feb 02 '17 at 15:01
  • It is consistently coming in reports, everyday during work hours – M W Feb 02 '17 at 15:07
  • There will always be a top worse performing SQL :) But if this is the worse and it's not a problem, then you're in good shape. – BobC Feb 02 '17 at 16:05
  • CPU bottleneck in the same hour, and first recommendation in report was to tune Top SQL Statements. - Was this recommendation wrong ? – M W Feb 02 '17 at 16:23

0 Answers0