AWR scripts

Questi script sono gli awr di un database 10.2:

/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrddinp.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrddrpi.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrddrpt.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrextr.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrinfo.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrinpnm.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrinput.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrload.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrrpt.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrrpti.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrsqrpi.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/awrsqrpt.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/catawrtb.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/catawrvw.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/catnoawr.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/dbmsawr.sql
/sbrdbms/app/oracle/product/10.2.0/db_rac/rdbms/admin/prvtawr.plb

AWR: %Total Call Time

Nella sezione "Top 5 Timed Events" la colonna "%Total Call Time" è calcolato come rapporto tra il tempo dell'evento d'attesa ed il "DB Time".

Nell'esempio che segue, il DB Time speso in "database user-call" è 463909, mentre il tempo di attesa sull'evento "PX Deq Credit: send blkd" è 222393.

 %Total Call Time of PX Deq Credit: send blkd => 222393/463909*100 = 47.9

Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
PX Deq Credit: send blkd          1,555,214     222,393    143   47.9      Other
db file scattered read            1,878,085      30,323     16    6.5   User I/O
db file sequential read           3,073,012      17,900      6    3.9   User I/O
CPU time                                         17,118           3.7
db file parallel read               203,998       6,165     30    1.3   User I/O
          -------------------------------------------------------------




-> Total time in database user-calls (DB Time): 463909.1s
-> Statistics including the word "background" measure background process
   time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name

Statistic Name                                       Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time                            462,631.7         99.7
DB CPU                                               17,117.8          3.7
parse time elapsed                                      410.4           .1
sequence load elapsed time                              392.7           .1
hard parse elapsed time                                 220.0           .0
PL/SQL execution elapsed time                             8.3           .0
connection management call elapsed time                   5.0           .0
PL/SQL compilation elapsed time                           2.1           .0
failed parse elapsed time                                 1.0           .0
hard parse (sharing criteria) elapsed time                0.6           .0
hard parse (bind mismatch) elapsed time                   0.2           .0
repeated bind elapsed time                                0.1           .0
DB time                                             463,909.1          N/A
background elapsed time                               2,561.9          N/A
background cpu time                                     806.3          N/A
          -------------------------------------------------------------

Ho notato che esiste una piccola discrepanza tra il "DB Time" riportato al top del report AWR e quello riportato più sotto nello stesso output. Infatti

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      9671 10-Aug-11 00:00:24       151       2.3
  End Snap:      9676 10-Aug-11 05:00:27       156       2.2
   Elapsed:              300.04 (mins)
   DB Time:            7,731.82 (mins)

mentre

-> Total time in database user-calls (DB Time): 463909.1s


7731 minuti sono 463860 secondi (7731*60 ). La discrepanza è di 49 secondi.

Metrics

Le metriche mantengono traccia di differenti eventi durante la vita del database [1]. Queste sono visibili da GV$METRICNAME.METRIC_NAME.

Sono statistiche che misurano la variazione dei cambiamenti in statistiche di performance comulative [2]. L'idea di base è la seguente [5]:

Tra di esse troviamo: "CPU Usage Per Sec", "Elapsed Time Per User Call", "Host CPU Utilization", etc.

Prendo un valore V1, all'istante T1 ed uno V2, all'istante T2. A questo punto, la metrica è definita come rapporto (V2 – V1) / (T2 – T1). AWR le raccoglie automaticamente. Frequenza della raccolta e retention dei dati, sono specificati in DBA_HIST_WR_CONTROL

SELECT snap_interval, retention FROM dba_hist_wr_control;

SNAP_INTERVAL
-------------------------------------
RETENTION
-------------------------------------
+00000 01:00:00.0
+00007 00:00:00.0

(profondità di 7 giorni, con raccolta ogni ora) e la modifica è possibileattraverso la funzione DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS

BEGIN
  dbms_workload_repository.modify_snapshot_settings(
  interval => 60,
  retention => 10*24*60
  );
END;

Sono collezionate ogni minuto, storicizzate in memoria e quindi salvate nelle tabelle WRH$ (H=History) di AWR (il tablespace su cui risiedono è SYSAUX). Complessivamente, sono disponibili le seguenti [3][5]:

Table: Global

VIEW/TABLE
GV$METRIC
GV$METRIC_HISTORY

Table: Information

VIEW/TABLE
GV$METRICGROUP
GV$METRICNAME
DBA_HIST_METRIC_NAME
WRH$_METRIC_NAME

Sono disponibili per [5]

» System
» Sessions
» Services
» Events
» Files

e raggruppate come di seguito:

select GROUP_ID, GROUP_NAME from V$METRICGROUP order by GROUP_ID

  GROUP_ID GROUP_NAME
---------- -------------------------------------------
         0 Event Metrics
         1 Event Class Metrics
         2 System Metrics Long Duration
         3 System Metrics Short Duration
         4 Session Metrics Long Duration
         5 Session Metrics Short Duration
         6 Service Metrics
         7 File Metrics Long Duration
         9 Tablespace Metrics Long Duration
        10 Service Metrics (Short)

Ed in V$METRICNAME.METRIC_UNIT, troviamo l'unità di misura in cui viene espressa quella particolare metrica. Sono espresse per

» Valori assoluti
» Percentuali
» Per secondi e per transactioni

Ad esempio:

select group_id id, metric_name, metric_unit from v$metricname where group_id=1

  ID METRIC_NAME                         METRIC_UNIT
---- -------------------------------     -----------
   1 Total Wait Counts                   Waits
   1 Total Time Waited                   CentiSeconds
   1 Database Time Spent Waiting (%)     % (TimeWaited / DBTime)
   1 Average Users Waiting Counts        Users

I valori degli utli 10 minuti sono visibili dalle GV$*, quelle degli utlimi 10+60 minuti dalle GV$*_HISTORY e quelle con retention superiore dalle DBA_HIST_* (le sottostanti tabelle sono le WRH$*). Le viste di sistema disponibili sono:

Table: Events

GROUP_ID	VIEW/TABLE
0	GV$EVENTMETRIC
1	GV$WAITCLASSMETRIC
	GV$WAITCLASSMETRIC_HISTORY
	WRH$_WAITCLASSMETRIC_HISTORY

Table: System

GROUP_ID	VIEW/TABLE
2/3	GV$SYSMETRIC
	GV$SYSMETRIC_HISTORY
	GV$SYSMETRIC_SUMMARY
	DBA_HIST_SYSMETRIC_HISTORY
	DBA_HIST_SYSMETRIC_SUMMARY
	WRH$_SYSMETRIC_HISTORY
	WRH$_SYSMETRIC_SUMMARY

Table: Session

GROUP_ID	VIEW/TABLE
4/5	V$SESSMETRIC
	DBA_HIST_SESSMETRIC_HISTORY
	WRH$_SESSMETRIC_HISTORY

Table: Service

GROUP_ID	VIEW/TABLE
6/10	GV$SERVICEMETRIC
	GV$SERVICEMETRIC_HISTORY

Table: Files

GROUP_ID	VIEW/TABLE
7	GV$FILEMETRIC
	GV$FILEMETRIC_HISTORY
	DBA_HIST_FILEMETRIC_HISTORY
	WRH$_FILEMETRIC_HISTORY

Per il GROPU_ID 9 della V$METRICGROUP, non ho trovato associazioni. Suppongo, visto che si tratta di tablespace, che le corrispondenti metriche si potrebbero associare a quelle per "Files".

GROUP_ID	VIEW/TABLE
9

[1] Oracle Metrics
[2] Database Metrics
[3] AUTOMATED WORKLOAD REPOSITORY
[4] AWR Metrics
[5] Automatic Workload Repository


Post update 2011/06/16