La procédure dbms_workload_repository.modify_snapshot_settings vous permet de jouer sur le niveau d’informations 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 l’objectif de la procédure dbms_workload_repository.add_colored_sql qui permet 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:=7777; 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 --------------- -------------------------------------------------- dfr596r1u50fb 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 ---------- --------------- --------- 394245816 dfr596r1u50fb 04-NOV-20
Capturez 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
Visualisez 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 ----------- ------------ -------- ------------ 394245816 ORCL 1 ORCL 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 ------------ -------- ------------ ------------ ------------ * 394245816 1 ORCL ORCL hsot-pc1 Using 394245816 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. Pressing without 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 ------------ ------------ --------- ------------------ ----- ORCL ORCL 1 04 Nov 2020 14:00 1 [...] 17 04 Nov 2020 22:00 1 18 04 Nov 2020 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: dfr596r1u50fb SQL ID specified: dfr596r1u50fb Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrsqlrpt_1_17_18.txt. To use this name, press to continue, otherwise enter an alternative. Enter value for report_name: sql_dfr596r1u50fb.txt Using the report name sql_dfr596r1u50fb.txt WORKLOAD REPOSITORY SQL Report Snapshot Period Summary DB Name DB Id Instance Inst Num Startup Time Release RAC ------------ ----------- ------------ -------- --------------- ----------- --- ORCL 394245816 ORCL 1 04-Nov-20 19:03 11.2.0.4.0 NO Snap Id Snap Time Sessions Curs/Sess --------- ------------------- -------- --------- Begin Snap: 17 04-Nov-20 22:00:44 24 1.5 End Snap: 18 04-Nov-20 22:09:43 24 1.4 Elapsed: 8.99 (mins) DB Time: 0.01 (mins) SQL Summary DB/Inst: ORCL/ORCL Snaps: 17-18 Elapsed SQL Id Time (ms) ------------- ---------- dfr596r1u50fb 6 Module: sqlplus@hsot-pc1 (TNS V1-V3) select ename from scott.emp where empno = :empno ------------------------------------------------------------- SQL ID: dfr596r1u50fb DB/Inst: ORCL/ORCL 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: ORCL/ORCL 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_dfr596r1kjhfb.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 dfr596r1u50fb 04-NOV-20 exec dbms_workload_repository.remove_colored_sql(-sql_id=>'dfr596r1u50fb')
Cet outil d’Oracle permet de suivre facilement des indicateurs clés sur les requêtes pertinentes pour votre application.
