Database Reference
In-Depth Information
the
cdb
views are also available. The following excerpt of the output generated by the
system_stats_logging.sql
script
shows an example of querying the view. By querying the view, you can find out to know which operations were performed,
when they were started, and how long they took:
SQL> VARIABLE now VARCHAR2(14)
SQL> BEGIN
2 SELECT to_char(sysdate,'YYYYMMDDHH24MISS') INTO :now FROM dual;
3 dbms_stats.delete_system_stats();
4 dbms_stats.gather_system_stats('noworkload');
5 END;
6 /
SQL> SELECT operation, start_time,
2 (end_time-start_time) DAY(1) TO SECOND(0) AS duration
3 FROM dba_optstat_operations
4 WHERE start_time > to_date(:now,'YYYYMMDDHH24MISS')
5 ORDER BY start_time;
OPERATION START_TIME DURATION
-------------------- --------------------------------- -----------
delete_system_stats 25-SEP-13 16.59.47.679829 +02:00 +0 00:00:00
gather_system_stats 25-SEP-13 16.59.47.688208 +02:00 +0 00:00:02
In addition, as of version 12.1, you can see the parameters with which an operation was executed. The following
query illustrates this:
SQL> SELECT x.*
2 FROM dba_optstat_operations o,
3 XMLTable('/params/param'
4 PASSING XMLType(notes)
5 COLUMNS name VARCHAR2(20) PATH '@name',
6 value VARCHAR2(20) PATH '@val') x
7 WHERE start_time > to_date(:now,'YYYYMMDDHH24MISS')
8 AND operation = 'gather_system_stats';
NAME VALUE
-------------------- ----------
gathering_mode noworkload
interval 60
statid
statown
stattab
In version 12.1 it's also possible to extract details about a specific operation through the
report_single_stats_operation
function of the
dbms_stats
package. Different formats (text, HTML, and XML)
are supported as output. The following query illustrates how to generate a text report:
SQL> SELECT dbms_stats.report_single_stats_operation(opid => id,
2 detail_level => 'all',
