Database Reference
In-Depth Information
19:17 7.6 5.1 94.9 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
19:18 6.1 4.4 95.6 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
19:19 5.7 5.0 95.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
19:20 6.0 7.2 92.8 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
19:21 4.8 4.5 95.5 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
19:22 4.9 5.7 94.3 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Once you've selected the 5-minute interval you want to focus on, it's time to take a look at the detailed
information provided by the Top SQL table (Figure
4-17
). If it indicates that few SQL statements are responsible for
a large part of the activity (for example, the activity of a single SQL statement is a double-digit percentage), you've
identified the SQL statements that you have to further analyze. For example, according to Figure
4-17
, seven queries
are, in total, responsible for more than 90% of the activity. So, to reduce the load on the system, you have to focus on
them.
To display the data shown in Figure
4-17
without Enterprise Manager, you can use the
ash_top_sqls.sql
script.
Note that the script requires three parameters as input. The first two specify the period (in this case, beginning and
end timestamps) which the data is displayed for. The third specifies whether the script restricts the output to a specific
session (this isn't the case when
all
is specified). The following outputs show data that is equivalent to that shown in
Figure
4-17
:
SQL> @ash_top_sqls.sql 2014-02-04_19:10:02.174 2014-02-04_19:15:02.174 all
Activity% DB Time CPU% UsrIO% Wait% SQL Id SQL Type
--------- ------- ----- ------ ----- ------------- --------------
24.6 744 4.2 95.8 0.0 c13sma6rkr27c SELECT
20.6 625 0.3 99.7 0.0 8dq0v1mjngj7t SELECT
12.4 377 1.1 98.9 0.0 7hk2m2702ua0g SELECT
12.0 362 1.9 98.1 0.0 bymb3ujkr3ubk INSERT
8.3 252 3.6 96.4 0.0 0yas01u2p9ch4 INSERT
6.9 208 1.4 98.6 0.0 0bzhqhhj9mpaa INSERT
5.9 180 2.2 97.8 0.0 8z3542ffmp562 SELECT
3.4 102 5.9 94.1 0.0 5mddt5kt45rg3 UPDATE
2.4 74 2.7 97.3 0.0 f9u2k84v884y7 UPDATE
0.8 25 100.0 0.0 0.0 0w2qpuc6u2zsp PL/SQL EXECUTE
If no particular SQL statement stands out, it obviously means that the activity is produced by many SQL
statements. Hence, it's a sign that major changes in the applications might be necessary to improve performance.
When you see such a case, I advise you to take a look at the activity aggregated according to other dimensions. By
default, the Top Activity page shows the Top Sessions table (Figure
4-18
). But with the dropdown list at the top of that
table, you can also aggregate the data according to other dimensions, like Top Services, Top Modules, Top Actions
(Figure
4-19
), and Top Clients. Sometimes this helps in identifying the part of the application or the client causing
high load. Note, however, that some of these dimensions provide useful information only when the application you're
analyzing has been correctly instrumented by defining the session attributes described in Chapter 2.
