Si vous avez la chance d’avoir des licenses Oracle Diagnostic Pack, AWR n’a pas de secret pour vous ! Vous avez, sans doute, déjà configuré le framework qui stocke les statistiques d’utilisation de vos bases Oracle 10g et 11g à l’aide de dbms_workload_repository.modify_snapshot_settings. La procédure permet en effet de changer les seuils de capture des ordres SQL les plus consommateurs. Vous pouvez ainsi jouer sur le niveau d’information collectées à propos des ordres SQL les plus consomateurs selon « Elapsed Time », « CPU Time », « User I/O Wait Time », « Gets », « Reads », « Physical Reads », « Executions », « Parse Calls », « Sharable Memory » ou « Version Count ». Mais savez-vous qu’il est également possible de forcer les snapshots AWR à capturer les ordres SQL de votre choix ? C’est le propos de la procédure dbms_workload_repository.add_colored_sql qui permet, littéralement et poétiquement, de colorier vos ordres SQL. Dans ce qui suit, vous trouverez un exemple d’utilisation de ce package…
Requête de votre application et SQL_ID
Pour les besoins de l’exemple, nous avons besoin d’une requête de l’application peu consommatrice de sorte qu’elle ne serait, en temps normal et avec les paramétrages par défaut pas collectée par AWR. Voici un exemple de requête sur le schéma SCOTT :
var empno number;
exec :empno:=7788;
select ename
from scott.emp
where empno = :empno;
col sql_text format a55
col sql_id format a15 new_value sqlid
select sql_id, substr(sql_text,1,55) sql_text
from v$sql
where regexp_like(sql_text, 'scott.*[:]empno');
SQL_ID SQL_TEXT
--------------- --------------------------------------------------
chz451r0u50fa select ename from scott.emp where empno = :empn
Coloriez votre requête
Vous pouvez alors colorier votre requête et vérifier dans la vue dba_hist_colored_sql que le SQL_ID est enregistré:
exec dbms_workload_repository.add_colored_sql(sql_id=>'&&sqlid')
SQL> select * from dba_hist_colored_sql;
DBID SQL_ID CREATE_TI
---------- --------------- ---------
394277766 chz451r0u50fa 04-FEB-10
Capturer un cliché
Attendez que le cliché AWR soit généré automatiquement ou forcez la capture d’un nouveau cliché comme ci-dessous:
exec dbms_workload_repository.create_snapshot
Visualiser les détails d’exécution
Pour visualiser le détail d’exécution, vous pouvez utiliser le script awrsqrpt.sql comme ci-dessous:
SQL> @?/rdbms/admin/awrsqrpt
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
394277766 BLACK 1 BLACK
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text
Type Specified: text
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 394277766 1 BLACK BLACK arkzoyd-easy
team
Using 394277766 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressingwithout
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
BLACK BLACK 1 04 Feb 2010 14:00 1
[...]
17 04 Feb 2010 22:00 1
18 04 Feb 2010 22:09 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 17
Begin Snapshot Id specified: 17
Enter value for end_snap: 18
End Snapshot Id specified: 18
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: chz451r0u50fa
SQL ID specified: chz451r0u50fa
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_17_18.txt. To use this name,
pressto continue, otherwise enter an alternative.
Enter value for report_name: sql_chz451r0u50fa.txt
Using the report name sql_chz451r0u50fa.txt
WORKLOAD REPOSITORY SQL Report
Snapshot Period Summary
DB Name DB Id Instance Inst Num Startup Time Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
BLACK 394277766 BLACK 1 04-Feb-10 19:03 11.2.0.1.0 NO
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 17 04-Feb-10 22:00:44 24 1.5
End Snap: 18 04-Feb-10 22:09:43 24 1.4
Elapsed: 8.99 (mins)
DB Time: 0.01 (mins)
SQL Summary DB/Inst: BLACK/BLACK Snaps: 17-18
Elapsed
SQL Id Time (ms)
------------- ----------
chz451r0u50fa 6
Module: sqlplus@arkzoyd-easyteam (TNS V1-V3)
select ename from scott.emp where empno = :empno
-------------------------------------------------------------
SQL ID: chz451r0u50fa DB/Inst: BLACK/BLACK Snaps: 17-18
-> 1st Capture and Last Capture Snap IDs
refer to Snapshot IDs witin the snapshot range
-> select ename from scott.emp where empno = :empno
Plan Hash Total Elapsed 1st Capture Last Capture
# Value Time(ms) Executions Snap ID Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1 2949544139 6 1 18 18
-------------------------------------------------------------
Plan 1(PHV: 2949544139)
-----------------------
Plan Statistics DB/Inst: BLACK/BLACK Snaps: 17-18
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 6 5.6 1.0
CPU Time (ms) 4 4.0 0.7
Executions 1 N/A N/A
Buffer Gets 40 40.0 0.2
Disk Reads 1 1.0 1.7
Parse Calls 1 1.0 0.1
Rows 1 1.0 N/A
Use r I/O Wait Time (ms) 0 N/A N/A
Cluster Wait Time (ms) 0 N/A N/A
Application Wait Time (ms) 0 N/A N/A
Concurrency Wait Time (ms) 0 N/A N/A
Invalidations 0 N/A N/A
Version Count 1 N/A N/A
Sharable Mem(KB) 14 N/A N/A
-------------------------------------------------------------
Execution Plan
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 2 (0)| 00:00:01 |
| 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Full SQL Text
SQL ID SQL Text
------------ -----------------------------------------------------------------
chz451r0u50f select ename from scott.emp where empno = :empno
Report written to sql_chz451r0u50fa.txt
Décolorer les ordres SQL
Pour décolorer vos ordres SQL, utilisez la procédure dbms_workload_repository.remove_colored_sql:
select * from dba_hist_colored_sql;
DBID SQL_ID CREATE_TI
---------- ------------- ---------
394277766 chz451r0u50fa 04-FEB-10
exec dbms_workload_repository.remove_colored_sql(-
sql_id=>'chz451r0u50fa')
select * from dba_hist_colored_sql;
no rows selected
Cet outil d’Oracle 11g permet de suivre facilement des indicateurs clés sur les requêtes pertinentes pour votre application. Il ne vous reste plus qu’à explorer les vues DBA_HIST_* avec soin…
