I have seen a lot of database reports before, and until recently there have been two serious performance problems, very representative, so recorded.
One is to report the Oracle process to the CPU of the database server 100%
SQL ordered by Gets is the data in memory, unit of time, is the main source of CPU consumption, when debugging SQL, most of the time it is to measure performance. Here is the actual AWR report, pretty scary:
SQL ordered by Gets
- Resources reported for PL/SQL code includes the resources used by the "all" statements called by the code.
- Total Buffer Gets: ###############
- Captured SQL account for 5.0% of total
Buffer Gets |
executions |
Gets per Exec |
%total |
CPU Time (s) |
Elapsed Time (s) |
SQL Id |
SQL Module |
SQL Text |
1,606,380,390 |
84 |
19,123,576.07 |
-0.84 |
37923.62 |
207407.54 |
78tpfukr4m00p |
JDBC Thin Client |
Select SUM (temp. Miduser) Total ... |
1,479,957,445 |
0 |
|
-0.77 |
26647.48 |
43778.62 |
2mxgaus0t6pxz |
JDBC Thin Client |
SELECT Device ID sbid, device ID SBBM, set ... |
1,280,365,092 |
73 |
17,539,247.84 |
-0.67 |
30263.02 |
159789.71 |
2qrj96dwj2xx1 |
JDBC Thin Client |
Select SUM (temp. Miduser) Total ... |
1,163,229,240 |
518 |
2,245,616.29 |
-0.61 |
11775.72 |
30158.95 |
cq0adyp396538 |
JDBC Thin Client |
SELECT COUNT (*) from dm_fl_obj ... |
465,618,308 |
261 |
1,783,978.19 |
-0.24 |
11004.49 |
27015.23 |
acqqmk9b7vc4a |
JDBC Thin Client |
SELECT COUNT (*) from dm_fl_obj ... |
448,728,301 |
21st |
21,368,014.33 |
-0.23 |
10698.43 |
43934.26 |
24gvfb62gt1yd |
JDBC Thin Client |
Select SUM (temp. Miduser) Total ... |
386,047,329 |
24 |
16,085,305.38 |
-0.20 |
9286.27 |
45034.84 |
Fx8tp01tpwgkq |
JDBC Thin Client |
Select SUM (temp. Miduser) Total ... |
294,571,906 |
13 |
22,659,377.38 |
-0.15 |
7002.73 |
26477.59 |
383ut78zg00hq |
JDBC Thin Client |
Select SUM (temp. Miduser) Total ... |
274,341,527 |
14 |
19,595,823.36 |
-0.14 |
6415.83 |
26625.21 |
Cqhbuuz14x7gh |
JDBC Thin Client |
Select SUM (temp. Miduser) Total ... |
176,633,305 |
10 |
17,663,330.50 |
-0.09 |
4160.82 |
26360.28 |
9xc9701y82st9 |
JDBC Thin Client |
Select SUM (temp. Miduser) Total ... |
Once the database server IO load has reached its limit
SQL ordered by Reads go to disk to fetch data, in units of times, if too large, IO will cause the entire database to be slow and is a RAC environment that may cause the database to restart.
Oracle via AWR SQL ordered by gets and SQL ordered by Reads