Hello.
Prologue.
There are a couple of questions that have been interesting to clarify with the public involved for many years.
But. In my swampy, low-lying region, there are also people in the subject: there are few and the situation in the IT direction, at most enterprises, is not conducive. So, therefore, I will try to satisfy my curiosity with the help of Habr. The question is about the approach to handling awr | statspack data from an observable-maintained oracle database.
So to the point.
Tie
We are talking about the analysis of cases with the degradation of the productivity of the subdivision and / or anomalies in the consumption of some resources, in / for the work of subd. In the general case, here, of course, there can be many factors, and it can easily come to considering any hanganalize, system state, event traces. I mean either the beginning of the analysis of cases, when the work goes with more or less widely used tools for analyzing the state of the subd. Or such cases in which a detailed examination of the work of the subd code is not required, i.e. incident factors - installed without using anything like oradebug;
Well. Ie, these are factors of the type , , :;
In my impression, the classic of the genre, in the processing of awr data: this is looking at awr reports. They look at awr reports: for a reason, for some reason. This need, always, comes from work (or not work) accompanied by subd. Well, and accordingly: they look with a certain goal-setting - to understand what caused such work (or not work) of the observed subd.
In addition to simple awr reports, there are also compare periodawr reports. Well. It so happens that people have: SE and no diagnostic pack, then: statpack, as an option, and it also has reports. One of the critically important prerequisites is the effectiveness of this approach to the analysis of some changes in the state of the subdivision: this is the degree of understanding of the oracle subdivision, as a system for servicing skl-teams, of the one who is engaged in this consideration.
..: , -. (event oracle-) -. (statistic oracle-) - .
: , - oracle-, , . ( ) , - .
, : OWI-based + , , + - — -. . , , , , ( ) — . : oracle ( oracle) zero administrative level; addm — . — , , /, — . : .
. : -/ awr-, — , — /. .
: , , — . — , . , , — , .
, , — , — . , , .
, , : , , , . (, , ), (, — ) .
, - , — -, . - — , , , : , — , .
: — /, — , — , , , : — . : , - .
..: , , , , : " " — . . , , - — , , / ..
, : , , , — . , - : , .
, , , : . . : - / , . -, etl-, . — , . — . .. — , , , .
— , : , . , awr|-, : , , — , . .., , : — . , ( ) — .
: N . , , — , , . . .
" : , … " EM-, EMG. . . , - -: , .. , , - , ash- - . , / — , . :
- — . , - — . — . — .
- — . . em-: -, jvm
ORACLE_HOME— .. . xml-. - — . , , N xml-. — , -, xml-, .
..: , . — , .
, . , .
- : awr|- — . .. /, , ( : ) — : /. .. — .
- em-. . — . , . , -, awr-, - — , , / . . — , spviewer. — : / , /.
- , , . . , , , , . , , .
. , , . awr-, : . . awr-: , , , DBA_HIST_* .
, SYS.WRH$_* ; : - sql-, awr, - OWI- . .., - , , em-, , , , , - (dba_hist_osstat) .
, , . , , , — //. , , em-, .
, — , , , , , awr|statspack-. . , awr|statspack-, em- ( ). — , : , - (. ).
, . : oracle-awr-report. . Why not, . , cmod u+x oracle-awr-report.py, sed- — .
, .
.. , emgc, at a glance , ;
, , . , , . : -, . — , /. ..: — , . ( ) — :
1
dmesg, - — : . , , , - , - ( -, -, ) — . -. /-, /-, , -: .
, , , : ? : ?.
, : : — , - . . em- — - - -.
, , sys.dba_hist_sqlstat. , -, -, awr-. :
, , Y- : , . , , sys.dba_hist_sqlstat, . , — , — / . , sys.dba_hist_osstat, , :
2
. : - , awr|statspack-, em-. , — , .
, , .
, .
. : , — , - — , : — / -, — sys.dba_hist_osstat. -: sys.dba_hist_sqlstat
, -. : — , .
..: user-, 2: - , sys.dba_hist_sqlstat — , awr- ( snap_id) - — . ..: , ..: . -, sys.dba_hist_sqlstat — - - (sys.dba_hist_sqlstat.cpu_time_delta) awr- ( snap_id).
, , - — , . ..: -: - , awr-, ..: , , -. . — — snap_id awr-. : .
sys.dba_hist_osstat, , , , — . , (-) -. , - -: , , , -, . Top-N (), , -.
(, ) 1-. . , , — -, -. (, — ) - ( ) . , — -.
, ;
rFSA-, cran-r
Attribute-importance .
, .. -, - -, - -, , - , , , — , . , , , -, :
3
: - /. , - /. , - /. , , , - — .
. — . : . . : user- -, , , ( - — ). ..: attribute-importance - .
: sys.dba_hist_stat_name, sys.dba_hist_sysstat. (cran-r randomForest) , :
, 5- . :
4
. , , : . , , -, , , . , , CR-. .., : , , , , . - — / , . : sys.dba_hist_seg_stat.
:
5
. — , , — .
: . , sys.dba_hist_sqltext — sql_id-, , - ( COMMAND_TYPE).
, sys.dba_hist_sqlstat — , - : . , -, , - - , .
, . : — , . : — , . , attribute-importance , , , — - . .. / . -.
, , , -, , , ml- / . : . — , , / , /cran-r; . , , .
ml- — , .. . . cran-r; -. : , , , oracle-. : why so.
/ : . , — . — , .
awr| — , , - -, , - . , ad-hoc , / ( ): . mmon- / , .. -, -. , awr-. , sys.WR[IHM]_* -, — / awr- , / .
: awr-. . . / / , , / , , — . , . , : , , ceph. , : atop-, .
- , , — - , , , . . : , - . , , , , -. , .
-, ( ). - - — , . , , , .
, , , .