In this post, I continue on the topic of examining SQL plans. I will talk about one DBMS_XPLAN function, DISPLAY_CURSOR (because it’s probably the most useful one when troubleshooting ongoing performance issues, and also because other functions have a lot of similiarity to it). I will discuss frequently used options and some common problems.
As already mentioned in my previous post on the subject, using DBMS_XPLAN to display rowsource stats requires a bit of preparation. Namely, one needs to either set STATISTICS_LEVEL parameter to ALL (can be done on the session level), or use gather_plan_statistics hint in the query, and then run the query.
Once the statement is executed, the plan with row source statistics can be obtained in a convenient format using DBMS_XPLAN.display_cursor.
DBMS_XPLAN.display_cursor takes three parameters, all of which are optional:
Continue reading “DBMS_XPLAN.DISPLAY_CURSOR”
Let’s start with some basic concepts. AWR reports deal with several kinds of time. The simplest kind is the elapsed time , it’s just the interval of time between the start and end snapshots. Another important quantity is DB time, which is defined as time in user calls during that period. It can be (and for a busy system typically is) greater than the elapsed time. However, the reason for that is not the number of CPUs as some experts incorrectly state (apparently, they confuse it with CPU time that we’ll discuss below, e.g. here), it’s that this time is a sum over all active user processes which are using CPU or waiting for something. Note that it only counts time spent in user calls, i.e. background processes are not included in that. Continue reading “AWR reports: interpreting CPU usage”
Since this is a very popular subject on the OTN forum, I decided to put together a few points about analyzing AWR reports. Continue reading “Reading AWR reports”