Im folgenden ein kleines Beispiel zu: dbms_sqltune.report_sql_monitor
Es erlaubt Reporting in Text- oder HTML-Format von SQL-Statements.
Einstellungen:
show parameter statistics_level (sollte ‚TYPICAL‘ oder ‚ALL‘ sein)
show parameter management_pack (DIAGNOSTIC+TUNING muss sein)
SQL>
NAME TYPE VALUE
———————————— ———– ——————————
statistics_level string TYPICAL
SQL>
NAME TYPE VALUE
———————————— ———– ——————————
control_management_pack_access string DIAGNOSTIC+TUNING
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
— MONITOR hint, sonst werden nur Statements gemonitort, die mehr als 5 Sekunden CPU/IO Zeit haben
select /*+ MONITOR */ count(*) from user_tables, user_tables;
SELECT sql_id, status, sql_text
FROM v$sql_monitor
WHERE username = ‚SCOTT‘;
select dbms_sqltune.report_sql_monitor (sql_id => ‚3spb2u6z8dq2h‘, report_level=>’ALL‘, type => ‚TEXT‘) as report from dual;
spool /tmp/html_report.html
select dbms_sqltune.report_sql_monitor (sql_id => ‚3spb2u6z8dq2h‘, report_level=>’ALL‘, type => ‚HTML‘) as report from dual;
spool off
— ein anderes Beispiel mit session_id
— select dbms_sqltune.report_sql_monitor (session_id=>sys_context(‚userenv‘,’sid‘), report_level=>’ALL‘, type => ‚TEXT‘) as report from dual;
#####################################################################################################################
Ergebnis type => ‚TEXT‘:
SQL>
select dbms_sqltune.report_sql_monitor (sql_id => ‚3spb2u6z8dq2h‘, report_level=>’ALL‘, type => ‚TEXT‘) as report from dual;
SQL> SQL Monitoring Report
SQL Text
——————————
select /*+ MONITOR */ count(*) from user_tables, user_tables
Global Information
——————————
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SCOTT (33:18)
SQL ID : 3spb2u6z8dq2h
SQL Execution ID : 16777216
Execution Started : 05/20/2012 08:45:38
First Refresh Time : 05/20/2012 08:45:38
Last Refresh Time : 05/20/2012 08:45:39
Duration : 1s
Module/Action : SQL*Plus/-
Service : SYS$USERS
Program : sqlplus@vmware1 (TNS V1-V3)
Fetch Calls : 1
Global Stats
================================================================
| Elapsed | Cpu | IO | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
================================================================
| 0.91 | 0.85 | 0.06 | 1 | 4625 | 53 | 11MB |
================================================================
SQL Plan Monitoring Details (Plan Hash Value=477042497)
============================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
============================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +1 | 1 | 1 | | | | | |
| 1 | SORT AGGREGATE | | 1 | | 1 | +1 | 1 | 1 | | | | | |
| 2 | HASH JOIN RIGHT OUTER | | 1M | 2051 | 1 | +1 | 1 | 16 | | | 1M | | |
| 3 | TABLE ACCESS FULL | SEG$ | 2517 | 22 | 1 | +1 | 1 | 5943 | | | | | |
| 4 | HASH JOIN RIGHT OUTER | | 1M | 2025 | 1 | +1 | 1 | 16 | | | 1M | | |
| 5 | INDEX FULL SCAN | I_USER2 | 33 | 1 | 1 | +1 | 1 | 94 | | | | | |
| 6 | HASH JOIN RIGHT OUTER | | 1M | 2020 | 1 | +1 | 1 | 16 | | | 3M | | |
| 7 | INDEX FAST FULL SCAN | I_OBJ1 | 12815 | 11 | 1 | +1 | 1 | 73367 | | | | | |
| 8 | HASH JOIN RIGHT OUTER | | 1M | 2005 | 1 | +1 | 1 | 16 | | | 2M | | |
| 9 | INDEX FAST FULL SCAN | I_OBJ1 | 12815 | 11 | 1 | +1 | 1 | 73367 | | | | | |
| 10 | HASH JOIN | | 1M | 1990 | 1 | +1 | 1 | 16 | | | 1M | | |
| 11 | TABLE ACCESS FULL | OBJ$ | 1831 | 43 | 1 | +1 | 1 | 6 | | | | | |
| 12 | HASH JOIN | | 1M | 1942 | 1 | +1 | 1 | 11128 | | | 815K | | |
| 13 | TABLE ACCESS FULL | TAB$ | 1093 | 201 | 1 | +1 | 1 | 2782 | 53 | 11MB | | 100.00 | direct path read (1) |
| 14 | MERGE JOIN CARTESIAN | | 5464 | 1737 | 1 | +1 | 1 | 28 | | | | | |
| 15 | HASH JOIN RIGHT OUTER | | 1093 | 253 | 1 | +1 | 1 | 4 | | | 2M | | |
| 16 | INDEX FAST FULL SCAN | I_OBJ1 | 12815 | 11 | 1 | +1 | 1 | 73367 | | | | | |
| 17 | HASH JOIN RIGHT OUTER | | 1093 | 242 | 1 | +1 | 1 | 4 | | | 1M | | |
| 18 | INDEX FULL SCAN | I_USER2 | 33 | 1 | 1 | +1 | 1 | 94 | | | | | |
| 19 | HASH JOIN OUTER | | 1093 | 240 | 1 | +1 | 1 | 4 | | | 761K | | |
| 20 | HASH JOIN | | 1093 | 229 | 1 | +1 | 1 | 4 | | | 1M | | |
| 21 | TABLE ACCESS FULL | TS$ | 5 | 3 | 1 | +1 | 1 | 7 | | | | | |
| 22 | HASH JOIN RIGHT OUTER | | 1093 | 225 | 1 | +1 | 1 | 4 | | | 1M | | |
| 23 | TABLE ACCESS FULL | SEG$ | 2517 | 22 | 1 | +1 | 1 | 5943 | | | | | |
| 24 | NESTED LOOPS | | 1093 | 203 | 1 | +1 | 1 | 4 | | | | | |
| 25 | MERGE JOIN CARTESIAN | | 1831 | 44 | 1 | +1 | 1 | 6 | | | | | |
| 26 | HASH JOIN | | 1 | 1 | 1 | +1 | 1 | 1 | | | 391K | | |
| 27 | MERGE JOIN CARTESIAN | | 1 | 1 | 1 | +1 | 1 | 1 | | | | | |
| 28 | HASH JOIN | | 1 | 1 | 1 | +1 | 1 | 1 | | | 394K | | |
| 29 | FIXED TABLE FULL | X$KSPPI | 1 | | 1 | +1 | 1 | 1 | | | | | |
| 30 | FIXED TABLE FULL | X$KSPPCV | 100 | | 1 | +1 | 1 | 2399 | | | | | |
| 31 | BUFFER SORT | | 1 | 1 | 1 | +1 | 1 | 1 | | | | | |
| 32 | FIXED TABLE FULL | X$KSPPI | 1 | | 1 | +1 | 1 | 1 | | | | | |
| 33 | FIXED TABLE FULL | X$KSPPCV | 100 | | 1 | +1 | 1 | 2399 | | | | | |
| 34 | BUFFER SORT | | 1831 | 44 | 1 | +1 | 1 | 6 | | | | | |
| 35 | TABLE ACCESS FULL | OBJ$ | 1831 | 43 | 1 | +1 | 1 | 6 | | | | | |
| 36 | TABLE ACCESS CLUSTER | TAB$ | 1 | 1 | 1 | +1 | 6 | 4 | | | | | |
| 37 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 | +1 | 6 | 4 | | | | | |
| 38 | INDEX FAST FULL SCAN | I_OBJ1 | 12815 | 11 | 1 | +1 | 1 | 73367 | | | | | |
| 39 | BUFFER SORT | | 5 | 1726 | 1 | +1 | 4 | 28 | | | | | |
| 40 | TABLE ACCESS FULL | TS$ | 5 | 1 | 1 | +1 | 1 | 7 | | | | | |
============================================================================================================================================================================
Ergebnis type => ‚HTML‘:
