Skip to main content

Example

  • Imagine, you have been informed on 26th May about some performance issues from yesterday
  • You decided to check this by the graphic of events from ASH (active session history).
  • If your system is oversized or idle (as in our case), it can be not so easy to recognize a problem on the event level.
  • In such cases we recommend to make a check on the segment level.
  • On this graphic you can easily find 2 problems. The first one was between 3 and 5 o’clock pm and the second one was between 7 and 8 o’clock pm.
  • Let’s analyze the first issue. TuTool provides a very useful feature for finding SQLs caused peaks of logical or physical reads at the data segment level. This is the output of the corresponding script.
  • The above SQL made a lot of buffer gets to find one row. But it is difficult to say if the FTS (full table scan) in it’s execution plan was a good choice of the optimizer or not, because this SQL has an aggregation.
  • So we need to check the run time statistics in it‘s execution plan to decide about this.
  • Unfortunately, AWR doesn’t provide any run time statistics in execution plans.
  • So the next opportunity to get run time statistics would have been an SQL monitoring report, but we couldn’t find any. Because our SQL is too fast to be monitored.
  • So we have to execute the problematic SQL to get the run time statistics.
  • Let’s extract bind values to the above plan from awr. For this purpose we need 3 input parameters, that we can take from the last awr output.
  • The above script extracts a SQL text additionally to the bind values.
  • Now we can place the found bind values into the prescript of TuTool and execute the SQL.
  • FTS with 878 buffer gets for the sake of only 3 rows isn’t the fastest solution. Perhaps there is no appropriate index on the table T1. Let’s check it.
  • There is one invisible index on the column A. For a case we can check, if this column is selective.
  • Yes, this column seems to be selective. We can now allow the using of invisible indexes in our session and execute the problematic SQL once more.
  • The second execution with the index access is much better than the first one with FTS. Now we have two possibilities to solve our problem: either to make the index I_T1 generally visible or to make it visible only for our SQL.
  • Let’s implement the second solution. We can create a SQL profile with one hidden hint USE_INVISIBLE_INDEXES for that. For this purpose one only needs to input the hint, the tool makes the rest itself.